project/qxlw.git

			@@ -5,6 +5,7 @@
			import java.util.List;
			import java.util.Map;

			import com.alibaba.fastjson.JSONArray;
			import org.springframework.beans.factory.annotation.Autowired;
			import org.springframework.stereotype.Component;

			@@ -21,172 +22,277 @@
			import cn.hutool.json.JSONObject;

			@Component("qxlw-project-service")
			public class ProjectInfoService extends AbstractBaseService{
			public class ProjectInfoService extends AbstractBaseService {

			@Autowired
			BaseDao baseDao;

			/**
			* 通过项目类型获取项目分项（获取模板分项）
			* @param projectType
			* @return
			*/
			public DataTableEntity getProjectTypeItem(String projectType) {
			DataTableEntity dtProjectItem = baseDao.listTable(Cmnconst.PRODUCT_PROJECT_BASE_PROJECT_ITEM, "itemize_type=?", new Object[] {projectType}, new Object[] {Cmnconst.ITEMIZE, Cmnconst.ITEMIZE_CODE, Cmnconst.ITEMIZE_TYPE, Cmnconst.ITEMIZE_PARENT_CODE});
			if (!BaseUtil.dataTableIsEmpty(dtProjectItem)) {
			DataTableEntity newDtProjectItem = BaseUtil.dataTableToTreeTable(dtProjectItem, Cmnconst.ITEMIZE_CODE, Cmnconst.ITEMIZE_PARENT_CODE, null);
			return newDtProjectItem;
			}
			return null;
			}

			/**
			* 通过项目UUID获取项目分项（获取保存的子表分项--多个树）
			* @param uuid
			* @return
			*/
			public FieldSetEntity getProjectItem(String uuid) {
			FieldSetEntity fseProject = baseDao.getFieldSetEntity(Cmnconst.PRODUCT_PROJECT_QXLW, uuid, true);
			DataTableEntity newDtProjectItem = BaseUtil.dataTableToTreeTable(fseProject.getSubDataTable(Cmnconst.PRODUCT_PROJECT_ITEM), Cmnconst.ITEMIZE_CODE, Cmnconst.ITEMIZE_PARENT_CODE, null);
			Map<String, DataTableEntity>dtSubTable = fseProject.getSubData();
			dtSubTable.put(Cmnconst.PRODUCT_PROJECT_ITEM, newDtProjectItem);
			return fseProject;
			}

			/**
			* 通过项目UUID获取项目分项（获取保存的子表分项--单个树）
			* @param uuid
			* @return
			*/
			public DataTableEntity getProjectAllItem(String uuid) {
			DataTableEntity dtProjectItemize = baseDao.listTable(Cmnconst.PRODUCT_PROJECT_ITEM, "project_uuid=?", new Object[] {uuid}, new Object[] {CoreConst.UUID, Cmnconst.ITEMIZE, Cmnconst.ITEMIZE_CODE, Cmnconst.ITEMIZE_PARENT_CODE});
			if (!BaseUtil.dataTableIsEmpty(dtProjectItemize)) {
			DataTableEntity newDtProjectItem = BaseUtil.dataTableToTreeTable(dtProjectItemize,Cmnconst.ITEMIZE_CODE, Cmnconst.ITEMIZE_PARENT_CODE, null);
			return newDtProjectItem;
			}
			return null;
			}

			/**
			* 获取所有项目信息
			* @return
			*/
			public DataTableEntity listProject() {
			return baseDao.listTable(Cmnconst.PRODUCT_PROJECT_QXLW, null, null, new Object[] {CoreConst.UUID, Cmnconst.PROJECT_NAME});
			}

			/**
			* 项目数据分析
			* @param project_uuid
			* @return
			*/
			public JSONObject statistAnalysis(String project_uuid) {

			JSONObject returnJson = new JSONObject();

			//获取数据库原始数据
			StringBuilder sb = new StringBuilder();
			sb.append("WITH outbound AS(")
			.append(" SELECT A.project_uuid,A.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(" WHERE project_uuid = ? ")
			.append(" GROUP BY A.project_uuid,A.project_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 ");
			DataTableEntity dtItemizeInfo = baseDao.listTable(sb.toString(), new Object[] {project_uuid});
			if(!BaseUtil.dataTableIsEmpty(dtItemizeInfo)) {

			Map<String, FieldSetEntity> convertData = new HashMap<>();
			String [] materialTypes = new String[3];

			//通过Java循环将"材料"和"出库量"数据行转列
			for (int i = 0; i < dtItemizeInfo.getRows(); i++) {
			FieldSetEntity fseItemize = dtItemizeInfo.getFieldSetEntity(i);

			//获取材料编码，材料类型，出库量
			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 {
			materialTypes[fseItemize.getInteger(Cmnconst.MATERIAL_TYPE)]+=","+materialCode;
			@Autowired
			BaseDao baseDao;

			/**
			* 通过项目类型获取项目分项（获取模板分项）
			*
			* @param projectType
			* @return
			*/
			public DataTableEntity getProjectTypeItem(String projectType) {
			DataTableEntity dtProjectItem = baseDao.listTable(Cmnconst.PRODUCT_PROJECT_BASE_PROJECT_ITEM, "itemize_type=?", new Object[]{projectType}, new Object[]{Cmnconst.ITEMIZE, Cmnconst.ITEMIZE_CODE, Cmnconst.ITEMIZE_TYPE, Cmnconst.ITEMIZE_PARENT_CODE});
			if (!BaseUtil.dataTableIsEmpty(dtProjectItem)) {
			DataTableEntity newDtProjectItem = BaseUtil.dataTableToTreeTable(dtProjectItem, Cmnconst.ITEMIZE_CODE, Cmnconst.ITEMIZE_PARENT_CODE, null);
			return newDtProjectItem;
			}
			return null;
			}

			/**
			* 通过项目UUID获取项目分项（获取保存的子表分项--多个树）
			*
			* @param uuid
			* @return
			*/
			public FieldSetEntity getProjectItem(String uuid) {
			FieldSetEntity fseProject = baseDao.getFieldSetEntity(Cmnconst.PRODUCT_PROJECT_QXLW, uuid, true);
			DataTableEntity newDtProjectItem = BaseUtil.dataTableToTreeTable(fseProject.getSubDataTable(Cmnconst.PRODUCT_PROJECT_ITEM), Cmnconst.ITEMIZE_CODE, Cmnconst.ITEMIZE_PARENT_CODE, null);
			Map<String, DataTableEntity> dtSubTable = fseProject.getSubData();
			dtSubTable.put(Cmnconst.PRODUCT_PROJECT_ITEM, newDtProjectItem);
			return fseProject;
			}

			/**
			* 通过项目UUID获取项目分项（获取保存的子表分项--单个树）
			*
			* @param uuid
			* @return
			*/
			public DataTableEntity getProjectAllItem(String uuid) {
			DataTableEntity dtProjectItemize = baseDao.listTable(Cmnconst.PRODUCT_PROJECT_ITEM, "project_uuid=?", new Object[]{uuid}, new Object[]{CoreConst.UUID, Cmnconst.ITEMIZE, Cmnconst.ITEMIZE_CODE, Cmnconst.ITEMIZE_PARENT_CODE});
			if (!BaseUtil.dataTableIsEmpty(dtProjectItemize)) {
			DataTableEntity newDtProjectItem = BaseUtil.dataTableToTreeTable(dtProjectItemize, Cmnconst.ITEMIZE_CODE, Cmnconst.ITEMIZE_PARENT_CODE, null);
			return newDtProjectItem;
			}
			return null;
			}

			/**
			* 获取所有项目信息
			*
			* @return
			*/
			public DataTableEntity listProject() {
			return baseDao.listTable(Cmnconst.PRODUCT_PROJECT_QXLW, null, null, new Object[]{CoreConst.UUID, Cmnconst.PROJECT_NAME});
			}

			/**
			* 项目数据分析
			*
			* @param project_uuid
			* @return
			*/
			public JSONObject statistAnalysis(String project_uuid) {

			JSONObject returnJson = new JSONObject();

			//数据库出库信息汇总
			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[4];

			//通过Java循环将"材料"和"出库量"数据行转列
			for (int i = 0; i < dtItemizeInfo.getRows(); i++) {
			FieldSetEntity fseItemize = dtItemizeInfo.getFieldSetEntity(i);

			//获取材料编码，材料类型，出库量
			String materialCode = fseItemize.getString(Cmnconst.MATERIAL_CODE);
			String outboundWeight = fseItemize.getString(Cmnconst.OUTBOUND_WEIGHT);
			Integer materialType = fseItemize.getInteger(Cmnconst.MATERIAL_TYPE);

			//收集获取所有表头列（材料编码）,架立筋和声测管没有表头
			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);
			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.TOTAL_OUTBOUND_WEIGHT, fseItemize.getString(Cmnconst.TOTAL_OUTBOUND_WEIGHT));
			fseConvertData.setValue(materialCode, outboundWeight);
			convertData.put(itemize, fseConvertData);

			//收集行转列数据
			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.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);
			});
			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]);
			}
			}
			}
			return returnJson;
			}

			/**
			* 删除子项验证（已经生成预算的子项不能删除）
			* @param uuid
			*/
			public void deleteItemizeValidate(String uuid) {
			DataTableEntity dtBudget = baseDao.listTable(Cmnconst.PRODUCT_PROJECT_BUDGET, "budget_code=?", new Object[] {uuid});
			if (!BaseUtil.dataTableIsEmpty(dtBudget)) {
			throw new BaseException(SystemCode.PROJECT_MANAGEMENT_ITEMIZE_DEL_FAIL.getValue(), SystemCode.PROJECT_MANAGEMENT_ITEMIZE_DEL_FAIL.getText());
			}
			}

			/**
			* 获取分项预算信息
			* @param projectUUID 项目UUID
			* @param itemizeCode 分项编码
			* @param capge 当前页
			* @param pageSize 页大小
			* @return
			*/
			public DataTableEntity listItemizeBudget(String projectUUID, String itemizeCode, Integer capge, Integer pageSize) {

			String filter;
			List <String> params= new ArrayList<>();
			if (BaseUtil.strIsNull(itemizeCode)) {
			filter = "project_itemize_uuid IN (SELECT uuid FROM product_project_item WHERE project_uuid=?)";
			params.add(projectUUID);
			}else {
			filter = "project_itemize_uuid IN (SELECT uuid FROM product_project_item WHERE project_uuid=? AND itemize_code like ?)";
			params.add(projectUUID);
			params.add(itemizeCode+"%");
			}
			DataTableEntity dtItemizeBudget = baseDao.listTable(Cmnconst.PRODUCT_PROJECT_BUDGET, filter, params.toArray(), null, null, pageSize, capge);
			if (!BaseUtil.dataTableIsEmpty(dtItemizeBudget)) {
			baseDao.loadPromptData(dtItemizeBudget);
			}
			return dtItemizeBudget;
			}

			//将数据转为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]);
			}
			}
			}
			return returnJson;
			}

			/**
			* 删除子项验证（已经生成预算的子项不能删除）
			*
			* @param uuid
			*/
			public void deleteItemizeValidate(String uuid) {
			DataTableEntity dtBudget = baseDao.listTable(Cmnconst.PRODUCT_PROJECT_BUDGET, "budget_code=?", new Object[]{uuid});
			if (!BaseUtil.dataTableIsEmpty(dtBudget)) {
			throw new BaseException(SystemCode.PROJECT_MANAGEMENT_ITEMIZE_DEL_FAIL.getValue(), SystemCode.PROJECT_MANAGEMENT_ITEMIZE_DEL_FAIL.getText());
			}
			}

			/**
			* 获取分项预算信息
			*
			* @param projectUUID 项目UUID
			* @param itemizeCode 分项编码
			* @param capge 当前页
			* @param pageSize 页大小
			* @return
			*/
			public DataTableEntity listItemizeBudget(String projectUUID, String itemizeCode, Integer capge, Integer pageSize) {

			String filter;
			List<String> params = new ArrayList<>();
			if (BaseUtil.strIsNull(itemizeCode)) {
			filter = "project_itemize_uuid IN (SELECT uuid FROM product_project_item WHERE project_uuid=?)";
			params.add(projectUUID);
			} else {
			filter = "project_itemize_uuid IN (SELECT uuid FROM product_project_item WHERE project_uuid=? AND itemize_code like ?)";
			params.add(projectUUID);
			params.add(itemizeCode + "%");
			}
			DataTableEntity dtItemizeBudget = baseDao.listTable(Cmnconst.PRODUCT_PROJECT_BUDGET, filter, params.toArray(), null, null, pageSize, capge);
			if (!BaseUtil.dataTableIsEmpty(dtItemizeBudget)) {
			baseDao.loadPromptData(dtItemizeBudget);
			}
			return dtItemizeBudget;
			}

			/**
			* 更具项目分项uuid获取项目分项下的材料
			* @param uuid
			* @param capge
			* @param pageSize
			* @return
			*/
			public DataTableEntity listMaterialV(String uuid, Integer capge, Integer pageSize) {
			return baseDao.listTable("product_project_base_material_v","project_itemize_uuid=?",new String[]{uuid},null,null,pageSize, capge);
			}
			/**
			* 更具仓库和材料获取库存
			* @return
			*/
			public FieldSetEntity findInventoryV(String warehouse_uuid, String material_uuid) {
			return baseDao.getFieldSetByFilter("product_project_inventory_v","warehouse_uuid=? and uuid=?",new String[]{warehouse_uuid,material_uuid},false);
			}
			/**
			* 根据项目uuid获取项目分项树结构
			* @param uuid
			* @return
			*/

			public JSONArray getTreeProjectItem(String uuid) {
			DataTableEntity dtProjectItemize = baseDao.listTable(Cmnconst.PRODUCT_PROJECT_ITEM, "project_uuid=?", new Object[]{uuid}, new Object[]{CoreConst.UUID, Cmnconst.ITEMIZE, Cmnconst.ITEMIZE_CODE, Cmnconst.ITEMIZE_PARENT_CODE});
			DataTableEntity newDtProjectItem = BaseUtil.dataTableToTreeTable(dtProjectItemize, Cmnconst.ITEMIZE_CODE, Cmnconst.ITEMIZE_PARENT_CODE, null);
			JSONArray objects = BaseUtil.dataTableEntityToJson(newDtProjectItem);
			JSONObject json = new JSONObject();
			json.set("itemize", "项目分部分项");
			json.set("children", objects);
			json.set("type", 1);
			JSONArray array = new JSONArray();
			array.add(json);
			return array;
			}
			}