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