整体优化

zm

2024-08-01 1f287fee728260d70450e019d9867ecd8692681f

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

@@ -1,5 +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;
@@ -31,7 +33,7 @@
      String queryFilter = queryFilterService.getQueryFilter(fse);
      StringBuilder sb = new StringBuilder();
      sb.append("SELECT * FROM( \n");
      sb.append("SELECT A.*,B.project_number,B.project_name AS project_name_label,E.user_name AS apply_label \n");
      sb.append("SELECT A.*,B.project_name AS project_name_label,E.user_name AS apply_label \n");
      sb.append("FROM product_project_performance_commission A \n");
      sb.append("LEFT JOIN product_project_business B ON B.uuid = A.project_uuid \n");
      sb.append("LEFT JOIN product_sys_users E ON E.user_id = A.apply_user \n");
@@ -53,58 +55,100 @@
   public FieldSetEntity findCommission(String uuid) {
      return baseDao.getFieldSetEntity(CmnConst.PRODUCT_PROJECT_PERFORMANCE_COMMISSION, uuid, true);
   }
	

   /**
    * 	
    * 合同回款业绩相关数据带出
    * @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,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,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 ,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});
      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)) {
               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++) {
                     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]);

                     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"));
//                  fse.setValue("project_role_name", "项目组员");
//                  dtNew.addFieldSetEntity(fse);
                  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 {
//               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;