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[] {});
|
}
|
}
|
