整体优化

zm

2024-08-01 1f287fee728260d70450e019d9867ecd8692681f

整体优化

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

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

import com.product.admin.service.OrganizationCacheService;
import com.product.common.lang.StringUtils;
import com.product.contract.config.CmnConst;
import com.product.core.config.CoreConst;
import com.product.core.dao.BaseDao;
@@ -84,21 +85,18 @@
     */
    private DataTableEntity getUnCommissionProjectSub(String projectUUID) {
        StringBuilder sb = new StringBuilder();
        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,ROUND(b.workload/C.work_days, 2)  human_days_ratio ");
        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,ROUND(b.workload/C.work_days, 2)  human_days_ratio,ROUND(C.work_days/D.sum_work_days, 2) jh_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(" LEFT JOIN (SELECT project_uuid,sum(work_days) sum_work_days FROM  product_project_business_plan GROUP BY project_uuid) D on A.uuid =D.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,ROUND(b.workload/C.work_days, 2) 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(" SELECT '项目成员' project_role_name,'2' AS project_role,A.team_members AS project_user,'0.05' bonus_ratio,null workload,null human_days_ratio,null jh_days_ratio ");
        sb.append(" FROM product_project_business A");
        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(" SELECT '销售人员' project_role_name,'3' AS project_role, A.sale AS project_user,'0' bonus_ratio,null actual_human_days,1 human_days_ratio ,1 jh_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});
@@ -114,6 +112,12 @@
                    continue;
                }
                if ("2".equals(projectRole)) {
                    String sql ="SELECT B.workload actual_human_days,ROUND(b.workload/C.work_days, 2) human_days_ratio,ROUND(C.work_days/D.sum_work_days, 2) jh_days_ratio  FROM  \n" +
                            " (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   \n" +
                            "LEFT JOIN (SELECT project_uuid,project_head,sum(work_days) work_days FROM  product_project_business_plan GROUP BY project_uuid,project_head) C  \n" +
                            "on B.apply_user =  C.project_head and b.project_uuid =C.project_uuid \n" +
                            "LEFT JOIN (SELECT project_uuid,sum(work_days) sum_work_days FROM  product_project_business_plan GROUP BY project_uuid) D on b.project_uuid =D.project_uuid     \n" +
                            "WHERE  b.project_uuid = ? AND B.apply_user=?";
                    if (projectUser.contains(",")) {
                        String [] userArray = projectUser.split(",");
                        for (int j = 0; j < userArray.length; j++) {
@@ -121,11 +125,24 @@
                            FieldSetEntity fseStaff = OrganizationCacheService.getStaffCacheByUserIdStatic(userArray[j]);
                            fseNew.setValue("project_user_name", fseStaff.getString("show_name"));
                            fseNew.setValue("project_user", userArray[j]);

                            FieldSetEntity fsSub = baseDao.getFieldSetEntityBySQL(sql, new String[]{projectUUID, userArray[j]}, false);
                            if (!FieldSetEntity.isEmpty(fsSub)){
                                fseNew.setValue("actual_human_days",fsSub.getString("actual_human_days"));
                                fseNew.setValue("human_days_ratio",fsSub.getString("human_days_ratio"));
                                fseNew.setValue("jh_days_ratio",fsSub.getString("jh_days_ratio"));
                            }
                            dtNew.addFieldSetEntity(fseNew);
                        }
                    }else {
                        FieldSetEntity fseStaff = OrganizationCacheService.getStaffCacheByUserIdStatic(projectUser);
                        fse.setValue("project_user_name", fseStaff.getString("show_name"));
                        FieldSetEntity fsSub = baseDao.getFieldSetEntityBySQL(sql, new String[]{projectUUID, projectUser}, false);
                        if (!FieldSetEntity.isEmpty(fsSub)){
                            fse.setValue("workload",fsSub.getString("workload"));
                            fse.setValue("human_days_ratio",fsSub.getString("human_days_ratio"));
                            fse.setValue("jh_days_ratio",fsSub.getString("jh_days_ratio"));
                        }
                        dtNew.addFieldSetEntity(fse);
                    }
                }else {