From 9f2f4d3598c5775b88f0b1a07a8ac0a0d9789351 Mon Sep 17 00:00:00 2001 From: zm <2369059705qq.com> Date: 星期五, 02 八月 2024 17:38:19 +0800 Subject: [PATCH] 合同台账优化 --- src/main/java/com/product/contract/service/ProjectCommissionService.java | 102 ++++++++++++++++++++++++++++++++++++-------------- 1 files changed, 73 insertions(+), 29 deletions(-) diff --git a/src/main/java/com/product/contract/service/ProjectCommissionService.java b/src/main/java/com/product/contract/service/ProjectCommissionService.java index ae4b490..ac1c2c1 100644 --- a/src/main/java/com/product/contract/service/ProjectCommissionService.java +++ b/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; -- Gitblit v1.9.2