入库统计报表

354798ggg

2023-12-01 75339563cebc86759a1e8addab9ab5382b4fd1a1

入库统计报表

 product-server-project-management/src/main/java/com/product/project/management/config/SystemCode.java

@@ -21,6 +21,7 @@
    PROJeCT_BUDGET_CLEAR_FAIL_HAS_OUTBOUND("预算清理失败，已经存在出库数据！", ModuleEnum.QXLW_PROJECT.getValue() + "008"),

    PROJeCT_OUT_CANCELOUTBOUND_ERROR("出库撤销失败！", ModuleEnum.QXLW_PROJECT.getValue() + "009"),
    PROJECT_INVENTORY_DATA_QUERY_FAIL("材料入库数据查询失败：", ModuleEnum.QXLW_PROJECT.getValue() + "010")

    ;


 product-server-project-management/src/main/java/com/product/project/management/controller/ProjectStatisticAnalysisController.java

@@ -13,6 +13,7 @@
import com.product.module.sys.version.ApiVersion;
import com.product.project.management.config.Cmnconst;
import com.product.project.management.config.SystemCode;
import com.product.project.management.service.ProjectInventoryReportService;
import com.product.project.management.service.ProjectStatisticAnalysisService;
import com.product.util.BaseUtil;
import com.product.util.ResultInfo;
@@ -23,6 +24,9 @@
    
    @Autowired
    ProjectStatisticAnalysisService projectStatisticAnalysisService;
	
    @Autowired
    ProjectInventoryReportService projectInventoryReportService;

    /**
     * èŽ·å–项目分析数据
@@ -50,4 +54,31 @@
            return this.error(SystemCode.PROJECT_INFO_OPERATE_ERROR.getValue(), SystemCode.PROJECT_INFO_OPERATE_ERROR.getText() + e.getMessage());
        }
    }
    
    /**
     *     ææ–™å…¥åº“统计
     *
     * @param request
     * @return
     */
    @RequestMapping(value = "/inventory-report/{version}", method = RequestMethod.POST)
    @ApiVersion(1)
    public String inventoryReport(HttpServletRequest request) {
        try {
            //获取参数
            FieldSetEntity fse = BaseUtil.getFieldSetEntity(request);

            //判断查询年份是否为空
            if (BaseUtil.strIsNull(fse.getString("query_year"))) {
                return this.error(SystemCode.SYSTEM_FORM_COUNT.getValue(), SystemCode.SYSTEM_FORM_COUNT.getText());
            }
            return OK_List(projectInventoryReportService.inventoryStatisticReport(fse.getDate("query_year","yyyy")));
        } catch (BaseException e) {
            e.printStackTrace();
            return this.error(e);
        } catch (Exception e) {
            e.printStackTrace();
            return this.error(SystemCode.PROJECT_INVENTORY_DATA_QUERY_FAIL.getValue(), SystemCode.PROJECT_INVENTORY_DATA_QUERY_FAIL.getText() + e.getMessage());
        }
    }
}

 product-server-project-management/src/main/java/com/product/project/management/service/ProjectInventoryReportService.java

¶Ô±ÈÐÂÎļþ
@@ -0,0 +1,66 @@
package com.product.project.management.service;


import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;

import com.product.core.dao.BaseDao;
import com.product.core.entity.DataTableEntity;
import com.product.core.service.support.AbstractBaseService;

/**
 *     é¡¹ç›®å…¥åº“统计报表
 * @author 86151
 *
 */
@Component
public class ProjectInventoryReportService extends AbstractBaseService{

    @Autowired
    BaseDao baseDao;
	
    static final String [] monthName = new String [] {"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sept","Oct","Nov","Dece"};
	
    /**
     *     å…¥åº“统计报表
     * @param queryYear
     * @return
     */
    public DataTableEntity inventoryStatisticReport(String queryYear) {
		
		
		
        StringBuilder sb = new StringBuilder(1024);
        //原始数据
        sb.append(" WITH rawData AS(")
        .append(" SELECT A.material_code,  MONTH(B.inbound_date)inbound_month, A.inbound_number ")
        .append(" FROM product_project_purchase_inbound_sub A ")
        .append(" LEFT JOIN product_project_purchase_inbound B ON A.inbound_uuid = B.uuid ")
        .append(" WHERE YEAR(inbound_date) =  ").append(queryYear)
        .append(" ), ");
		
        //行专列数据
        sb.append(" convertData AS( ")
        .append(" SELECT material_code, ");
        for (int i = 0; i < monthName.length; i++) {
            sb.append("SUM(CASE WHEN inbound_month = ").append(i+1).append(" THEN inbound_number END) AS ").append(monthName[i]).append(",");
        }
        sb.append(" ROUND(SUM(inbound_number)/1000, 1) AS YearSummary ")
        .append(" FROM rawData ")
        .append(" GROUP BY material_code ")
        .append(" ORDER BY material_code ")
        .append(" ) ");
		
        //底部汇总
        sb.append(" SELECT * FROM convertData ")
        .append(" UNION ALL ")
        .append(" SELECT '月总计(t)', ");
        for (int i = 0; i < monthName.length; i++) {
            sb.append("ROUND(SUM(").append(monthName[i]).append(")/1000, 1),");
        }
        sb.append(" SUM(YearSummary) ")
        .append(" FROM convertData ");
		
        return baseDao.listTable(sb.toString(), new Object[] {});
    }
}

 product-server-project-management/src/main/java/com/product/project/management/service/ProjectStatisticAnalysisService.java

@@ -40,13 +40,16 @@
        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,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,11)) itemize ")
        .append("    SELECT A.project_uuid,A.material_type,A.material_code,A.outbound_weight,B.itemize ")
        .append("    FROM( ")
        .append("      SELECT A.project_uuid,B.material_type,B.material_code,B.outbound_weight,substring(C.itemize_code,1,11) 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 ")
        .append("    LEFT JOIN product_project_item B ON A.itemize = B.itemize_code ")
        .append("    WHERE B.project_uuid = ? ")
        .append(" )A GROUP BY project_uuid,itemize,material_type,material_code ")
        .append(" )");
        //出库信息操作（getItemizeSum[钢筋和架立筋左侧汇总]；allitemize[所有三级分项]；intermediateData1[钢筋架立筋按分项汇总]；intermediateData2[钢筋网片按分项和材料汇总]；intermediateData3[报表成型]）
@@ -131,7 +134,7 @@
        .append(" SELECT '合计',ROUND(SUM(outbound_weight)/1000,1) 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});
        DataTableEntity dtItemizeInfo = baseDao.listTable(outboundInfo.toString() + operateInfo.toString(), new Object[]{project_uuid, project_uuid});
        if (!BaseUtil.dataTableIsEmpty(dtItemizeInfo)) {

            Map<String, FieldSetEntity> convertData = new HashMap<>();
@@ -176,7 +179,7 @@
            }
            
            //计算汇总
            DataTableEntity dtTotalInfo = baseDao.listTable(outboundInfo.toString() + totalSum.toString(), new Object[]{project_uuid});
            DataTableEntity dtTotalInfo = baseDao.listTable(outboundInfo.toString() + totalSum.toString(), new Object[]{project_uuid, project_uuid});
            FieldSetEntity fseConvertData = new FieldSetEntity(Cmnconst.PRODUCT_PROJECT_QXLW);
            if (!BaseUtil.dataTableIsEmpty(dtTotalInfo)) {
                for (int i = 0; i < dtTotalInfo.getRows(); i++) {