product-server-project-base/src/main/java/com/product/base/controller/BaseItemizeController.java
ÎļþÃû´Ó product-server-project-base/src/main/java/com/product/base/controller/ProjectItemController.java ÐÞ¸Ä @@ -10,18 +10,18 @@ import com.product.base.config.BaseErrorCode; import com.product.base.config.CmnConst; import com.product.base.service.ProjectItemService; import com.product.base.service.BaseItemizeService; import com.product.core.controller.support.AbstractBaseController; import com.product.core.entity.FieldSetEntity; import com.product.module.sys.version.ApiVersion; import com.product.util.BaseUtil; @RequestMapping("/api/project") @RequestMapping("/api/base-itemize") @RestController public class ProjectItemController extends AbstractBaseController{ public class BaseItemizeController extends AbstractBaseController{ @Autowired ProjectItemService projectItemService; BaseItemizeService baseItemizeService; /** * è·åææ项ç®åç±» @@ -40,7 +40,7 @@ if (BaseUtil.strIsNull(fse.getString(CmnConst.CPAGE)) || BaseUtil.strIsNull(fse.getString(CmnConst.PAGESIZE))) { return error(BaseErrorCode.SYSTEM_CPAGES_NOT_NULL.getValue(), BaseErrorCode.SYSTEM_CPAGES_NOT_NULL.getText()); } return OK_List(projectItemService.listProjectType(fse)); return OK_List(baseItemizeService.listProjectType(fse)); } catch (Exception e) { return this.error(BaseErrorCode.PROJECT_TYPE_OPERATE_FAIL.getValue(), BaseErrorCode.PROJECT_TYPE_OPERATE_FAIL.getText()+e.getMessage()); } @@ -63,7 +63,7 @@ if (BaseUtil.strIsNull(fse.getUUID())) { return error(BaseErrorCode.SYSTEM_FORM_COUNT.getValue(), BaseErrorCode.SYSTEM_FORM_COUNT.getText()); } return OK_List(projectItemService.getProjectType(fse.getUUID())); return OK_List(baseItemizeService.getProjectType(fse.getUUID())); } catch (Exception e) { return this.error(BaseErrorCode.PROJECT_TYPE_OPERATE_FAIL.getValue(), BaseErrorCode.PROJECT_TYPE_OPERATE_FAIL.getText()+e.getMessage()); } @@ -83,7 +83,7 @@ FieldSetEntity fse=BaseUtil.getFieldSetEntity(request); //ä¿å boolean succ = projectItemService.saveProjectType(fse); boolean succ = baseItemizeService.saveProjectType(fse); if (succ) { return OK(); } @@ -99,7 +99,7 @@ * @param response * @return */ @RequestMapping(value="/project-item/tree/{version}", method = RequestMethod.POST) @RequestMapping(value="/itemize/tree/{version}", method = RequestMethod.POST) @ApiVersion(1) public String listProjectItemTree(HttpServletRequest request, HttpServletResponse response) { try { @@ -110,7 +110,7 @@ if (BaseUtil.strIsNull(fse.getUUID())) { return error(BaseErrorCode.SYSTEM_FORM_COUNT.getValue(), BaseErrorCode.SYSTEM_FORM_COUNT.getText()); } return BaseUtil.success(projectItemService.listProjectItemTree(fse.getUUID())); return BaseUtil.success(baseItemizeService.listProjectItemTree(fse.getUUID())); } catch (Exception e) { return this.error(BaseErrorCode.PROJECT_ITEM_OPERATE_FAIL.getValue(), BaseErrorCode.PROJECT_ITEM_OPERATE_FAIL.getText()+e.getMessage()); } @@ -122,14 +122,14 @@ * @param response * @return */ @RequestMapping(value="/project-item/save/{version}", method = RequestMethod.POST) @RequestMapping(value="/itemize/save/{version}", method = RequestMethod.POST) @ApiVersion(1) public String saveProjectItem(HttpServletRequest request, HttpServletResponse response) { try { //è·ååæ° FieldSetEntity fse=BaseUtil.getFieldSetEntity(request); //ä¿å return BaseUtil.success(projectItemService.saveProjectItem(fse)); return BaseUtil.success(baseItemizeService.saveProjectItem(fse)); } catch (Exception e) { return this.error(BaseErrorCode.PROJECT_ITEM_SAVE_FAIL.getValue(), BaseErrorCode.PROJECT_ITEM_SAVE_FAIL.getText()+e.getMessage()); } @@ -141,7 +141,7 @@ * @param response * @return */ @RequestMapping(value="/project-item/delete/{version}", method = RequestMethod.POST) @RequestMapping(value="/itemize/delete/{version}", method = RequestMethod.POST) @ApiVersion(1) public String deleteProjectItem(HttpServletRequest request, HttpServletResponse response) { try { @@ -152,7 +152,7 @@ return error(BaseErrorCode.SYSTEM_FORM_COUNT.getValue(), BaseErrorCode.SYSTEM_FORM_COUNT.getText()); } //å é¤éªè¯ return BaseUtil.success(projectItemService.deleteProjectItem(fse.getUUID())); return BaseUtil.success(baseItemizeService.deleteProjectItem(fse.getUUID())); } catch (Exception e) { return this.error(BaseErrorCode.PROJECT_ITEM_SAVE_FAIL.getValue(), BaseErrorCode.PROJECT_ITEM_SAVE_FAIL.getText()+e.getMessage()); } product-server-project-base/src/main/java/com/product/base/controller/MaterialManagerController.java
@@ -33,7 +33,7 @@ MaterialManagerService materialManagerService; /** * è·åææææä¿¡æ¯ * è·åææææç±»å * @param request * @param response * @return product-server-project-base/src/main/java/com/product/base/service/BaseItemizeService.java
ÎļþÃû´Ó product-server-project-base/src/main/java/com/product/base/service/ProjectItemService.java ÐÞ¸Ä @@ -17,7 +17,7 @@ @Component public class ProjectItemService extends AbstractBaseService{ public class BaseItemizeService extends AbstractBaseService{ @Autowired BaseDao baseDao; product-server-project-base/src/main/java/com/product/base/service/MaterialManagerService.java
@@ -46,7 +46,7 @@ if (BaseUtil.strIsNull(fse.getString(CmnConst.MATERIAL_TYPE)) || fse.getInteger(CmnConst.MATERIAL_TYPE)==-1) { dt = baseDao.listTable(CmnConst.PRODUCT_PROJECT_BASE_MATERIAL, null, null, null, null, fse.getInteger(CmnConst.PAGESIZE), fse.getInteger(CmnConst.CPAGE)); }else { dt = baseDao.listTable(CmnConst.PRODUCT_PROJECT_BASE_MATERIAL, "material_type=?", new Object[] {fse.getInteger(CmnConst.MATERIAL_TYPE)}, null, null, fse.getInteger(CmnConst.PAGESIZE), fse.getInteger(CmnConst.CPAGE)); dt = baseDao.listTable(CmnConst.PRODUCT_PROJECT_BASE_MATERIAL, "material_type like ?", new Object[] {"%"+fse.getInteger(CmnConst.MATERIAL_TYPE)+"%"}, null, null, fse.getInteger(CmnConst.PAGESIZE), fse.getInteger(CmnConst.CPAGE)); } baseDao.loadPromptData(dt); product-server-project-management/src/main/java/com/product/project/management/config/Cmnconst.java
@@ -8,6 +8,7 @@ public static final String PRODUCT_PROJECT_QXLW="product_project_qxlw"; //项ç®ä¿¡æ¯ public static final String PRODUCT_PROJECT_ITEM="product_project_item"; //项ç®å项 public static final String PRODUCT_PROJECT_BUDGET="product_project_budget"; //é¢ç® public static final String PRODUCT_PROJECT_BASE_MATERIAL="product_project_base_material"; //å段å public static final String MATERIAL_TYPE="material_type"; @@ -20,6 +21,7 @@ public static final String ITEMIZE_CODE="itemize_code"; public static final String ITEMIZE_TYPE="itemize_type"; public static final String ITEMIZE_PARENT_CODE="itemize_parent_code"; public static final String BUDGET_CODE="budget_code"; //åæ° product-server-project-management/src/main/java/com/product/project/management/service/ProjectBudgetService.java
@@ -41,17 +41,35 @@ List<Object> params = new ArrayList<>(); String projectItem = autoCreateFilter(params, fseParseData); //æ ¹æ®æ¨¡æ¿é¡¹ç®å项æ¥è¯¢æ°æ®åºé¡¹ç®å项 FieldSetEntity fseProjectItem = baseDao.getFieldSetEntityByFilter(Cmnconst.PRODUCT_PROJECT_ITEM, "project_uuid IN (SELECT uuid FROM product_project_qxlw WHERE project_name=?) AND itemize_full_name=?", params.toArray(), false); if(fseProjectItem==null) { sbError.append(String.format("第 %s è¡é¡¹ç®å项 '%s' ä¸åå¨äºç³»ç»ä¸ã", fseParseData.getString("~row~"), projectItem)); continue; } //è®¾ç½®å ³è项ç®å项 fseParseData.setValue("project_itemize_uuid", fseProjectItem.getUUID()); //è·åç¼ç çæææç±»å String budgetCode = fseParseData.getString(Cmnconst.BUDGET_CODE); Integer materialType = 0; if("æ¶ç«ç".equals(budgetCode)) { materialType = 1; }else if("声æµç®¡".equals(budgetCode)) { materialType = 2; }else { //ç½ç FieldSetEntity materialInfo = baseDao.getFieldSetEntityByFilter(Cmnconst.PRODUCT_PROJECT_BASE_MATERIAL, "material_type=3 AND material_name like ?", new Object[] {fseParseData.getString(Cmnconst.MATERIAL_CODE)+"%"}, false); if (materialInfo!=null) { materialType = 3; } } fseParseData.setValue(Cmnconst.MATERIAL_TYPE, materialType); } //å¤ææ¯å¦å å«éè¯¯ä¿¡æ¯ if (!BaseUtil.strIsNull(sbError.toString())) { throw new BaseException(SystemCode.PROJECT_BUDGET_IMPORT_FAIL.getValue(), sbError.toString()); } // baseDao.add(dtParseData); return dtParseData; }
@@ -90,30 +90,62 @@ JSONObject returnJson = new JSONObject(); //è·åæ°æ®åºåå§æ°æ® StringBuilder sb = new StringBuilder(); sb.append("WITH outbound AS(") .append(" SELECT A.project_uuid,D.itemize project_itemize,C.material_type,B.material_code,SUM(B.outbound_weight)outbound_weight ") .append(" FROM product_project_outbound A ") .append(" LEFT JOIN product_project_outbound_sub B ON A.uuid=B.outbound_uuid ") .append(" LEFT JOIN product_project_base_material C ON B.material_uuid = C.uuid ") .append(" LEFT JOIN product_project_item D ON A.project_itemize = D.uuid ") .append(" WHERE A.flow_flag=2 AND A.project_uuid = ? ") .append(" GROUP BY A.project_uuid,D.itemize,C.material_type,B.material_code ") .append(" ) ") .append(" SELECT A.project_uuid,A.project_itemize,A.total_outbound_weight,B.material_type,B.material_code,B.outbound_weight ") .append(" FROM ( ") .append(" SELECT project_uuid,project_itemize,SUM(outbound_weight)total_outbound_weight ") .append(" FROM outbound ") .append(" GROUP BY project_uuid,project_itemize ") .append(" )A ") .append(" RIGHT JOIN outbound B ON A.project_uuid=B.project_uuid AND A.project_itemize=B.project_itemize ") .append(" ORDER BY project_itemize,material_code"); DataTableEntity dtItemizeInfo = baseDao.listTable(sb.toString(), new Object[]{project_uuid}); //æ°æ®åºåºåºä¿¡æ¯æ±æ» StringBuilder outboundInfo = new StringBuilder(512); outboundInfo.append(" WITH outbound AS( ") .append(" SELECT project_uuid,itemize,material_type,material_code,SUM(outbound_weight)outbound_weight FROM ( ") .append(" SELECT ") .append(" A.project_uuid, ") .append(" B.material_type,B.material_code,B.outbound_weight, ") .append(" (SELECT itemize FROM product_project_item WHERE project_uuid = A.project_uuid AND itemize_code = substring(C.itemize_code,1,7)) itemize ") .append(" FROM product_project_outbound A ") .append(" LEFT JOIN product_project_outbound_sub B ON A.uuid=B.outbound_uuid ") .append(" LEFT JOIN product_project_item C ON C.uuid = A.project_itemize ") .append(" WHERE A.flow_flag=2 AND A.project_uuid=? ") .append(" )A GROUP BY project_uuid,itemize,material_type,material_code ") .append(" )"); //åºåºä¿¡æ¯æä½ï¼æ¶ç«çå声æµç®¡ä¸å±ç¤ºåå·æ±æ»æ±åè¡è½¬åï¼æ®éé¢çåç½çå±ç¤ºåå·ï¼æ ¹æ®é¡¹ç®åé¡¹å ³èæ±æ»ï¼ StringBuilder operateInfo = new StringBuilder(1024); operateInfo.append(" ,getItemizeSum AS( ") .append(" SELECT itemize,SUM(outbound_weight)total_outbound_weight FROM outbound WHERE material_type IN (0,1) GROUP BY itemize ") .append(" ),allitemize AS( ") .append(" SELECT itemize FROM outbound GROUP BY itemize ") .append(" ),intermediateData1 AS( ") .append(" SELECT A.itemize,SUM(IF(`material_type`='1',outbound_weight,0)) as 'JLJ',SUM(IF(`material_type`='2',outbound_weight,0)) as 'SCG' ") .append(" FROM ( SELECT itemize FROM outbound GROUP BY itemize )A ") .append(" LEFT JOIN ( ") .append(" SELECT project_uuid,itemize,material_type,CASE WHEN material_type=1 THEN 'æ¶ç«ç' ELSE '声æµç®¡' END AS material_code,SUM(outbound_weight)outbound_weight ") .append(" FROM outbound ") .append(" WHERE material_type IN (1,2) ") .append(" GROUP BY project_uuid,itemize,material_type ") .append(" ) B ON A.itemize = B.itemize ") .append(" GROUP BY A.itemize ") .append(" ),intermediateData2 AS( ") .append(" SELECT project_uuid,itemize,material_type,material_code,outbound_weight FROM outbound WHERE material_type IN (0,3) ") .append(" ),intermediateData3 AS( ") .append(" SELECT A.itemize,A.total_outbound_weight,A.jlj,A.scg,B.material_type,material_code,outbound_weight FROM( ") .append(" SELECT A.itemize,C.total_outbound_weight,B.jlj,B.scg ") .append(" FROM allitemize A ") .append(" LEFT JOIN intermediateData1 B ON A.itemize = B.itemize ") .append(" LEFT JOIN getItemizeSum C ON A.itemize = C.itemize ") .append(" )A ") .append(" LEFT JOIN intermediateData2 B ON A.itemize = B.itemize ") .append(" ) ") .append(" select * from intermediateData3 "); StringBuilder totalSum = new StringBuilder(""); totalSum.append(" SELECT material_code,SUM(outbound_weight)outbound_weight FROM outbound WHERE material_type IN (0,3) GROUP BY material_code ") .append(" UNION ALL ") .append(" SELECT CASE WHEN material_type=1 THEN 'JLJ' WHEN material_type=2 THEN 'SCG' END,SUM(outbound_weight) FROM outbound WHERE material_type IN (1,2) GROUP BY material_type ") .append(" UNION ALL ") .append(" SELECT 'å计',SUM(outbound_weight) FROM outbound WHERE material_type IN (0,1,3) GROUP BY project_uuid "); //è·åSQL解æçåºæ¬æ°æ®ï¼å©ç¨JAVAå°é¡¹ç®å项对åºçä¸åææè¡è½¬å DataTableEntity dtItemizeInfo = baseDao.listTable(outboundInfo.toString() + operateInfo.toString(), new Object[]{project_uuid}); if (!BaseUtil.dataTableIsEmpty(dtItemizeInfo)) { Map<String, FieldSetEntity> convertData = new HashMap<>(); String[] materialTypes = new String[3]; String[] materialTypes = new String[4]; //éè¿Java循ç¯å°"ææ"å"åºåºé"æ°æ®è¡è½¬å for (int i = 0; i < dtItemizeInfo.getRows(); i++) { @@ -123,36 +155,62 @@ String materialCode = fseItemize.getString(Cmnconst.MATERIAL_CODE); String outboundWeight = fseItemize.getString(Cmnconst.OUTBOUND_WEIGHT); Integer materialType = fseItemize.getInteger(Cmnconst.MATERIAL_TYPE); //æ¶éè·åææ表头åï¼ææç¼ç ï¼ if (BaseUtil.strIsNull(materialTypes[materialType])) { materialTypes[materialType] = materialCode; } else { if(materialTypes[fseItemize.getInteger(Cmnconst.MATERIAL_TYPE)].indexOf(materialCode)<0) { materialTypes[fseItemize.getInteger(Cmnconst.MATERIAL_TYPE)] += "," + materialCode; } } //æ¶éè·åææ表头åï¼ææç¼ç ï¼,æ¶ç«çå声æµç®¡æ²¡æ表头 if (!BaseUtil.strIsNull(materialCode)) { if (BaseUtil.strIsNull(materialTypes[materialType])) { materialTypes[materialType] = materialCode; } else { if(materialTypes[fseItemize.getInteger(Cmnconst.MATERIAL_TYPE)].indexOf(materialCode)<0) { materialTypes[fseItemize.getInteger(Cmnconst.MATERIAL_TYPE)] += "," + materialCode; } } } //æ¶éè¡è½¬åæ°æ® String itemize = fseItemize.getString(Cmnconst.PROJECT_ITEMIZE); String itemize = fseItemize.getString(Cmnconst.ITEMIZE); if (convertData.containsKey(itemize)) { convertData.get(itemize).setValue(materialCode, outboundWeight); } else { FieldSetEntity fseConvertData = new FieldSetEntity(Cmnconst.PRODUCT_PROJECT_QXLW); fseConvertData.setValue(Cmnconst.PROJECT_ITEMIZE, itemize); fseConvertData.setValue(Cmnconst.ITEMIZE, itemize); fseConvertData.setValue(Cmnconst.TOTAL_OUTBOUND_WEIGHT, fseItemize.getString(Cmnconst.TOTAL_OUTBOUND_WEIGHT)); fseConvertData.setValue(materialCode, outboundWeight); fseConvertData.setValue("jlj", fseItemize.getString("jlj")); fseConvertData.setValue("scg", fseItemize.getString("scg")); convertData.put(itemize, fseConvertData); } } //计ç®æ±æ» DataTableEntity dtTotalInfo = baseDao.listTable(outboundInfo.toString() + totalSum.toString(), new Object[]{project_uuid}); FieldSetEntity fseConvertData = new FieldSetEntity(Cmnconst.PRODUCT_PROJECT_QXLW); if (!BaseUtil.dataTableIsEmpty(dtTotalInfo)) { for (int i = 0; i < dtTotalInfo.getRows(); i++) { FieldSetEntity fseTotalInfo = dtTotalInfo.getFieldSetEntity(i); String materialCode = fseTotalInfo.getString(Cmnconst.MATERIAL_CODE); String outboundWeight = fseTotalInfo.getString(Cmnconst.OUTBOUND_WEIGHT); if (!BaseUtil.strIsNull(materialCode) && !BaseUtil.strIsNull(outboundWeight)) { if ("å计".equals(materialCode)) { fseConvertData.setValue(Cmnconst.ITEMIZE, "å计"); fseConvertData.setValue(Cmnconst.TOTAL_OUTBOUND_WEIGHT, outboundWeight); }else { fseConvertData.setValue(materialCode, outboundWeight); } } } } //å°æ°æ®è½¬ä¸ºDataTableEntityè¿å if (!convertData.isEmpty()) { DataTableEntity finalConvertData = new DataTableEntity(); convertData.forEach((k, v) -> { finalConvertData.addFieldSetEntity(v); }); finalConvertData.addFieldSetEntity(fseConvertData); returnJson.set("data", BaseUtil.dataTableEntityToJson(finalConvertData)); } //å°è¡¨å¤´æ°æ®è¿å for (int i = 0; i < materialTypes.length; i++) { if (!BaseUtil.strIsNull(materialTypes[i])) { returnJson.set("type" + i, materialTypes[i]);
