整体优化

zm

2024-07-31 cc9a20784a66626d8a1d5ceb6ac9dd2df387e367

整体优化

 src/main/java/com/product/contract/config/CmnConst.java

@@ -16,6 +16,7 @@
    public static final String LX_PROJECT_CONTRACT_TERMINATION = "product_project_contract_termination"; //合同终止表
    public static final String LX_PROJECT_CONTRACT_FILE = "product_project_contract_file"; //合同归档表
    public static final String PRODUCT_PROJECT_PERFORMANCE_COMMISSION="product_project_performance_commission";
    public static final String PRODUCT_PROJECT_PERFORMANCE_COMMISSION_SUB="product_project_performance_commission_sub";
    public static final String LX_PROJECT_TASK_ALLOCATION = "product_project_task_allocation"; //任务分配表
    public static final String LX_PROJECT_TASK_ALLOCATION_SUB = "product_project_task_allocation_sub"; //任务分配表
    public static final String LX_PROJECT_TASK_PROGRESS = "product_project_task_progress"; //任务进度表

 src/main/java/com/product/contract/service/ProjectCommissionService.java

@@ -1,5 +1,6 @@
package com.product.contract.service;

import com.product.admin.service.OrganizationCacheService;
import com.product.contract.config.CmnConst;
import com.product.core.config.CoreConst;
import com.product.core.dao.BaseDao;
@@ -55,29 +56,58 @@
    }
    
    /**
     * 	
     * 合同回款业绩相关数据带出
     * @return
     */
    public DataTableEntity getUnCommissionProject(String projectUUID) {
    public FieldSetEntity getUnCommissionProject(String projectUUID){
           String sql ="SELECT\n" +
                   "a.uuid contract_uuid,\n" +  //合同名称
                   "a.project_uuid,\n" +  //合同名称
                   "a.contract_num,\n" +        //合同编号
                   "a.collection_amount receivable_amount,\n" +   //累计回款
                   "(a.collection_amount-IF(b.this_settlement IS NULL, 0, b.this_settlement)) this_settlement\n" + //#本次结算回款额
                   "\n" +
                   "FROM product_project_contract_info a\n" +
                   "LEFT JOIN \n" +
                   "(SELECT SUM(this_settlement) this_settlement,contract_uuid,project_uuid FROM product_project_performance_commission GROUP BY contract_uuid,project_uuid) b\n" +
                   "on a.uuid=b.contract_uuid and a.project_uuid=b.project_uuid\n" +
                   "where a.project_uuid=?";
        FieldSetEntity fs = baseDao.getFieldSetEntityBySQL(sql, new String[]{projectUUID}, false);
        fs.setTableName(CmnConst.PRODUCT_PROJECT_PERFORMANCE_COMMISSION);
        DataTableEntity dt = getUnCommissionProjectSub(projectUUID);
        fs.addSubDataTable(dt);
        return fs;
    }
    /**
     *     合同回款业绩相关子表字段带出
     * @return
     */
    private DataTableEntity getUnCommissionProjectSub(String projectUUID) {
        StringBuilder sb = new StringBuilder();
        sb.append("SELECT * FROM ( \n");
        sb.append("  SELECT A.business_type,B.dict_label AS business_type_name,'1' AS project_role, project_leader AS project_user \n");
        sb.append("  FROM product_project_business_sub A \n");
        sb.append("  LEFT JOIN product_sys_dict B ON B.dict_value = A.business_type AND B.dict_name LIKE 'business_type_%' \n");
        sb.append("  WHERE project_uuid = ? \n");
        sb.append("  UNION ALL \n");
        sb.append("  SELECT A.business_type,B.dict_label AS business_type_name,'2' AS project_role, business_sponsor AS project_user \n");
        sb.append("  FROM product_project_business_sub A \n");
        sb.append("  LEFT JOIN product_sys_dict B ON B.dict_value = A.business_type AND B.dict_name LIKE 'business_type_%' \n");
        sb.append("  WHERE project_uuid = ? \n");
        sb.append(") A \n");
        sb.append("ORDER BY business_type");
        DataTableEntity dt = baseDao.listTable(sb.toString(), new Object[] {projectUUID, projectUUID});
        sb.append(" SELECT '项目组长' project_role_name,'1' AS project_role, A.group_leader AS project_user,'0.01' bonus_ratio,b.workload actual_human_days,b.workload/C.work_days human_days_ratio ");
        sb.append(" FROM product_project_business A ");
        sb.append(" LEFT JOIN (SELECT  project_uuid,sum(workload)workload FROM product_project_schedule_weekly_sub GROUP BY project_uuid) B on A.uuid=b.project_uuid ");
        sb.append(" LEFT JOIN (SELECT  project_uuid,sum(work_days)work_days FROM product_project_business_plan GROUP BY project_uuid) C on A.uuid=C.project_uuid ");
        sb.append(" WHERE A.uuid = ? ");
        sb.append(" UNION ALL ");
        sb.append(" SELECT '项目成员' project_role_name,'2' AS project_role, A.team_members AS project_user,'0.05' bonus_ratio,B.workload,B.workload/C.work_days human_days_ratio ");
        sb.append(" FROM product_project_business A LEFT JOIN");
        sb.append(" (SELECT a.apply_user,b.project_uuid,sum(workload) workload FROM  product_project_schedule_weekly a LEFT JOIN product_project_schedule_weekly_sub b on b.main_uuid = a.uuid GROUP BY a.apply_user,b.project_uuid) B");
        sb.append(" on A.team_members=b.apply_user and A.uuid =B.project_uuid LEFT JOIN");
        sb.append(" (SELECT project_uuid,project_head,sum(work_days) work_days FROM  product_project_business_plan GROUP BY project_uuid,project_head) C ");
        sb.append(" on A.team_members=C.project_head and A.uuid =C.project_uuid ");
        sb.append("  WHERE A.uuid = ? ");
        sb.append(" UNION ALL ");
        sb.append(" SELECT '销售人员' project_role_name,'3' AS project_role, A.sale AS project_user,'0' bonus_ratio,null actual_human_days,1 human_days_ratio  ");
        sb.append(" FROM product_project_business A  ");
        sb.append("  WHERE A.uuid = ? ");
        DataTableEntity dt = baseDao.listTable(sb.toString(), new Object[] {projectUUID, projectUUID,projectUUID});
        if (!BaseUtil.dataTableIsEmpty(dt)) {
            DataTableEntity dtNew = new DataTableEntity();
            
            for (int i = 0; i < dt.getRows(); i++) {
                FieldSetEntity fse = dt.getFieldSetEntity(i);
                fse.setTableName(CmnConst.PRODUCT_PROJECT_PERFORMANCE_COMMISSION_SUB);
                String projectRole = fse.getString("project_role");
                String projectUser = fse.getString("project_user");
                if (BaseUtil.strIsNull(projectRole) || BaseUtil.strIsNull(projectUser)) {
@@ -88,23 +118,20 @@
                        String [] userArray = projectUser.split(",");
                        for (int j = 0; j < userArray.length; j++) {
                            FieldSetEntity fseNew = fse.clones();
//                            FieldSetEntity fseStaff = OrganizationCacheService.getStaffCacheByUserIdStatic(userArray[j]);
//                            fseNew.setValue("project_user_name", fseStaff.getString("show_name"));
//                            fseNew.setValue("project_user", userArray[j]);
//                            fse.setValue("project_role_name", "项目组员");
                            FieldSetEntity fseStaff = OrganizationCacheService.getStaffCacheByUserIdStatic(userArray[j]);
                            fseNew.setValue("project_user_name", fseStaff.getString("show_name"));
                            fseNew.setValue("project_user", userArray[j]);
                            dtNew.addFieldSetEntity(fseNew);
                        }
                    }else {
//                        FieldSetEntity fseStaff = OrganizationCacheService.getStaffCacheByUserIdStatic(projectUser);
//                        fse.setValue("project_user_name", fseStaff.getString("show_name"));
//                        fse.setValue("project_role_name", "项目组员");
//                        dtNew.addFieldSetEntity(fse);
                        FieldSetEntity fseStaff = OrganizationCacheService.getStaffCacheByUserIdStatic(projectUser);
                        fse.setValue("project_user_name", fseStaff.getString("show_name"));
                        dtNew.addFieldSetEntity(fse);
                    }
                }else {
//                    FieldSetEntity fseStaff = OrganizationCacheService.getStaffCacheByUserIdStatic(projectUser);
//                    fse.setValue("project_user_name", fseStaff.getString("show_name"));
//                    fse.setValue("project_role_name", "项目组长");
//                    dtNew.addFieldSetEntity(fse);
                    FieldSetEntity fseStaff = OrganizationCacheService.getStaffCacheByUserIdStatic(projectUser);
                    fse.setValue("project_user_name", fseStaff.getString("show_name"));
                    dtNew.addFieldSetEntity(fse);
                }
            }
            return dtNew;