Index: ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/service/JasperReportManagerImpl.java =================================================================== diff -u -r35927 -r35928 --- ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/service/JasperReportManagerImpl.java (.../JasperReportManagerImpl.java) (revision 35927) +++ ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/service/JasperReportManagerImpl.java (.../JasperReportManagerImpl.java) (revision 35928) @@ -46,7 +46,6 @@ import com.forgon.directory.model.OrgUnit; import com.forgon.directory.model.Org_OrgGroup; import com.forgon.directory.vo.LoginUserData; -import com.forgon.disinfectsystem.basedatamanager.reportoption.GoodsOptionManager; import com.forgon.disinfectsystem.common.CssdUtils; import com.forgon.disinfectsystem.diposablegoods.service.GodownEntryItemManager; import com.forgon.disinfectsystem.diposablegoods.util.DisposableGoodsUtils; @@ -257,8 +256,6 @@ private InitDbConnection dbConnection; private static final String DIPOSABLEGOODS = "一次性物品"; @Autowired - private GoodsOptionManager goodsOptionManager; - @Autowired private GodownEntryItemManager godownEntryItemManager; @Autowired private MaterialEntryItemManager materialEntryItemManager; @@ -3341,159 +3338,8 @@ @Override public List getTousseSpecificationSizeStatisticsData( String startDay, String endDay, String departmentCode) { - List list = new ArrayList(); - - if (StringUtils.isNotBlank(startDay) - && StringUtils.isNotBlank(endDay) - && StringUtils.isNotBlank(departmentCode)) { - String betweenSql = " and " + dateQueryAdapter.dateAreaSql("i.sendTime", startDay, endDay, true); - String orgUnitCodingSql = String.format(" and i.orgUnitCoding='%s' ", departmentCode); - - //配置的为内院的科室，其他的外院的科室（如果没有配置内院的科室，则全部当成内院科室） - GoodsOption option = goodsOptionManager.getGoodsOption(GoodsOption.MODEL_ADYTUM_DEPARTMENT, AcegiHelper.getCurrentOrgUnitCode()); - String inSql = ""; - String notInSql = ""; - String returnInSql = ""; - String returnNotInSql = ""; - if (option != null && StringUtils.isNotBlank(option.getValue())) { - String[] optionValues = option.getValue().split(";"); - inSql = SqlUtils.get_InSql("i.depart", optionValues); - notInSql = SqlUtils.get_NotInSql("i.depart", optionValues); - returnInSql = SqlUtils.get_InSql("rr.depart", optionValues); - returnNotInSql = SqlUtils.get_NotInSql("rr.depart", optionValues); - } else { - inSql = "and 1=1"; - notInSql = "and 1=0"; - inSql = "and 1=1"; - notInSql = "and 1=0"; - } - String returnBetweenSql = " and " + dateQueryAdapter.dateAreaSql("rr.returntime", startDay, endDay, true); - String groupBySql = " group by td.packageSize "; - String tousseTypeInSql = String.format(" in ('%s','%s','%s','%s')", TousseDefinition.PACKAGE_TYPE_INSIDE, TousseDefinition.PACKAGE_TYPE_CUSTOM, TousseDefinition.PACKAGE_TYPE_DRESSING, TousseDefinition.PACKAGE_TYPE_FOREIGNPROXY); - String packageSizeCaseSql = String.format("case when packageSize='%s' then 1 when packageSize='%s' then 2 when packageSize='%s' then 3 when packageSize='%s' then 4 else 0 end sequence ", - TousseDefinition.PACKAGE_SIZE_HUGE,TousseDefinition.PACKAGE_SIZE_BIG,TousseDefinition.PACKAGE_SIZE_CENTER,TousseDefinition.PACKAGE_SIZE_SMALL); - String returnSql = String.format(" select td.packageSize,-sum(ri.amount) amount " - + "from ReturnGoodsRecord rr inner join ReturnGoodsItem ri on rr.id = ri.returngoodsrecord_id " - + "join TousseDefinition td on td.id = ri.tousseDefinition_id " - + "where rr.handleDepartCode='%s' %s ", - departmentCode,returnBetweenSql); - //1、本院科室 - String sql1 = String.format("select td.packageSize,sum(ii.amount) amount " - + "from Invoice i join InvoiceItem ii on ii.invoice_id=i.id join TousseDefinition td on td.id=ii.tousseDefinitionId " - + "where 1=1 " - + "and ii.tousseType %s %s %s %s group by td.packageSize ", - tousseTypeInSql, - betweenSql, orgUnitCodingSql, inSql); - sql1 = "select packageSize,sum(amount) amount,"+ packageSizeCaseSql +" from (" + sql1 + " union all " + returnSql + " and td.tousseType " + tousseTypeInSql + returnInSql + groupBySql + ")tempTable group by packageSize order by sequence"; - logger.debug("本院器械包" + sql1); - setTousseSpecificationSizeStatisticsBean(list, sql1, "本院器械包"); - //2、外院科室 - sql1 = String.format("select td.packageSize,sum(ii.amount) amount " - + "from Invoice i join InvoiceItem ii on ii.invoice_id=i.id join TousseDefinition td on td.id=ii.tousseDefinitionId " - + "where 1=1 " - + "and ii.tousseType %s %s %s %s group by td.packageSize ", - tousseTypeInSql, - betweenSql, orgUnitCodingSql, notInSql); - sql1 = "select packageSize,sum(amount) amount,"+ packageSizeCaseSql +" from (" + sql1 + " union all " + returnSql + " and td.tousseType " + tousseTypeInSql + returnNotInSql + groupBySql + ")tempTable group by packageSize order by sequence "; - logger.debug("外院器械包" + sql1); - setTousseSpecificationSizeStatisticsBean(list, sql1, "外院器械包"); - - tousseTypeInSql = String.format(" and td.tousseType in ('%s','%s')", TousseDefinition.PACKAGE_TYPE_FOREIGN,TousseDefinition.PACKAGE_TYPE_SPLIT); - //3、植入物器械包 - sql1 = String.format("select t1.packageSize,sum(amount) amount " - + "from(" - + "select td.packageSize,sum(ii.amount) amount " - //+ "from Invoice i join TousseInstance ti on ti.invoice_id=i.id " - + "from Invoice i join InvoiceItem ii on ii.invoice_id=i.id " - + "join TousseDefinition td on td.id=ii.tousseDefinitionId " - + "where 1=1 %s " - + " %s %s group by td.id,td.packageSize" - + ") t1 group by t1.packageSize ", - tousseTypeInSql, betweenSql, orgUnitCodingSql); - sql1 = "select packageSize,sum(amount) amount,"+ packageSizeCaseSql +" from (" + sql1 + " union all " + returnSql + tousseTypeInSql + groupBySql + ")tempTable group by packageSize order by sequence "; - logger.debug("植入物器械包" + sql1); - setTousseSpecificationSizeStatisticsBean(list, sql1, "植入物器械包"); - //4、消毒物品 - sql1 = String.format("select '消毒包' packageSize,sum(ii.amount) amount from Invoice i join InvoiceItem ii on ii.invoice_id=i.id " - + "where ii.tousseType='%s' %s %s", - TousseDefinition.PACKAGE_TYPE_DISINFECTION, betweenSql, orgUnitCodingSql); - sql1 = "select '消毒包' packageSize,sum(amount) amount from (" + sql1 + " union all " + returnSql + " and td.tousseType='"+ TousseDefinition.PACKAGE_TYPE_DISINFECTION +"'" + groupBySql + ")tempTable "; - logger.debug("消毒物品" + sql1); - setTousseSpecificationSizeStatisticsBean(list, sql1, "消毒物品"); - //材料发货,也计算到发货数量的包数和件数上 CDSLQYQYYY-84 - StringBuilder sbr = new StringBuilder(); - sbr.append("select '材料' packageSize,sum(amount) amount from (select sum(ii.amount) amount from ") - .append(MaterialInvoiceItem.class.getSimpleName()) - .append(" ii inner join ") - .append(MaterialInvoice.class.getSimpleName()) - .append(" i on i.id = ii.materialInvoice_id ") - .append("inner join ") - .append(MaterialDefinition.class.getSimpleName()) - .append(" d on ii.materialDefinitionId = d.id where 1=1 ") - .append(betweenSql) - .append(SqlUtils.get_InSql_Extra("i.orgUnitCoding",departmentCode)); - betweenSql = " and " + dateQueryAdapter.dateAreaSql("rr.returnTime", startDay, endDay, true); - sbr.append(" union all select sum(-rmi.amount) amount from ") - .append(ReturnMaterialRecord.class.getSimpleName()) - .append(" rr join ") - .append(ReturnMaterialItem.class.getSimpleName()) - .append(" rmi on rmi.returnMaterialRecord_ID=rr.id where 1=1 ") - .append(SqlUtils.get_InSql_Extra("rr.handleDepartCode",departmentCode)) - .append(betweenSql) - .append(")temp "); - logger.debug("材料发货" + sbr.toString()); - setTousseSpecificationSizeStatisticsBean(list, sbr.toString(), "材料"); - } - return list; + return reportHelper1.getTousseSpecificationSizeStatisticsData(startDay, endDay, departmentCode); } - - /** - * 设置TousseSpecificationSizeStatistics的bean集合（此方法只适用于本类的getTousseSpecificationSizeStatisticsData方法） - * @param list - * @param sql - * @param tousseGroup - */ - private void setTousseSpecificationSizeStatisticsBean(List list, - String sql, String tousseGroup) { - List childs = new ArrayList(); - ResultSet result = objectDao.executeSql(sql); - try { - //packageSize为空字符串或null的map - Map resultMap = new HashMap(); - while(result.next()){ - String packageSize = result.getString("packageSize"); - if(StringUtils.isBlank(packageSize)){ - packageSize = ""; - } - int amount = result.getInt("amount"); - if (amount > 0) { - TousseSpecificationSizeStatistics_child child = null; - if(resultMap.containsKey(packageSize)){//"" 和 null 合并为1行 - child = resultMap.get(packageSize); - child.setAmount(child.getAmount() + amount); - }else{ - child = new TousseSpecificationSizeStatistics_child(); - child.setPackageSize(packageSize); - child.setAmount(amount); - childs.add(child); - if("".equals(packageSize)){ - resultMap.put(packageSize, child); - } - } - } - } - } catch (SQLException e) { - e.printStackTrace(); - }finally { - DatabaseUtil.closeResultSetAndStatement(result); - } - if (CollectionUtils.isNotEmpty(childs)) { - TousseSpecificationSizeStatistics bean1 = new TousseSpecificationSizeStatistics(); - bean1.setTousseGroup(tousseGroup); - bean1.setChild(childs); - list.add(bean1); - } - } @Override public List getWashUnUnqualifiedData(String startTime, String endTime, String reportTypeStr){ return materialWashUnqualifiedReportHelper.getWashUnUnqualifiedData(startTime, endTime, reportTypeStr); Index: ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/util/ReportHelper1.java =================================================================== diff -u -r35927 -r35928 --- ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/util/ReportHelper1.java (.../ReportHelper1.java) (revision 35927) +++ ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/util/ReportHelper1.java (.../ReportHelper1.java) (revision 35928) @@ -25,11 +25,18 @@ import com.forgon.directory.acegi.tools.AcegiHelper; import com.forgon.disinfectsystem.basedatamanager.reportoption.GoodsOptionManager; import com.forgon.disinfectsystem.entity.assestmanagement.DisposableGoodsStock; +import com.forgon.disinfectsystem.entity.basedatamanager.materialdefinition.MaterialDefinition; import com.forgon.disinfectsystem.entity.basedatamanager.reportoption.GoodsOption; import com.forgon.disinfectsystem.entity.basedatamanager.toussedefinition.TousseDefinition; import com.forgon.disinfectsystem.entity.basedatamanager.toussedefinition.TousseInstance; +import com.forgon.disinfectsystem.entity.invoicemanager.MaterialInvoice; +import com.forgon.disinfectsystem.entity.invoicemanager.MaterialInvoiceItem; +import com.forgon.disinfectsystem.entity.returnmaterialrecord.ReturnMaterialItem; +import com.forgon.disinfectsystem.entity.returnmaterialrecord.ReturnMaterialRecord; import com.forgon.disinfectsystem.jasperreports.javabeansource.ApparatusInfusionisType; import com.forgon.disinfectsystem.jasperreports.javabeansource.TousseOverLoadBean; +import com.forgon.disinfectsystem.jasperreports.javabeansource.TousseSpecificationSizeStatistics; +import com.forgon.disinfectsystem.jasperreports.javabeansource.TousseSpecificationSizeStatistics_child; import com.forgon.disinfectsystem.jasperreports.service.TousseOverLoadDetail; import com.forgon.tools.MathTools; import com.forgon.tools.SqlBuilder; @@ -44,6 +51,7 @@ * 一些小报表(超过500行建议换一个文件) * 各类型输液器报表 * 器械包实际负载组合统计报表 + * 器械包规格大小统计报表 */ @Component public class ReportHelper1 { @@ -351,4 +359,166 @@ } return tousseCountCodeKeySbf.toString(); } + /** + * 获取器械包规格大小统计报表的数据. + * @param startDay 查询开始时间 格式'2017-02-02' + * @param endDay 查询结束时间 格式'2017-02-02' + * @param departmentCode 供应室编码 + * @return + */ + public List getTousseSpecificationSizeStatisticsData( + String startDay, String endDay, String departmentCode) { + List list = new ArrayList(); + + if (StringUtils.isNotBlank(startDay) + && StringUtils.isNotBlank(endDay) + && StringUtils.isNotBlank(departmentCode)) { + String betweenSql = " and " + dateQueryAdapter.dateAreaSql("i.sendTime", startDay, endDay, true); + String orgUnitCodingSql = String.format(" and i.orgUnitCoding='%s' ", departmentCode); + + //配置的为内院的科室，其他的外院的科室（如果没有配置内院的科室，则全部当成内院科室） + GoodsOption option = goodsOptionManager.getGoodsOption(GoodsOption.MODEL_ADYTUM_DEPARTMENT, AcegiHelper.getCurrentOrgUnitCode()); + String inSql = ""; + String notInSql = ""; + String returnInSql = ""; + String returnNotInSql = ""; + if (option != null && StringUtils.isNotBlank(option.getValue())) { + String[] optionValues = option.getValue().split(";"); + inSql = SqlUtils.get_InSql("i.depart", optionValues); + notInSql = SqlUtils.get_NotInSql("i.depart", optionValues); + returnInSql = SqlUtils.get_InSql("rr.depart", optionValues); + returnNotInSql = SqlUtils.get_NotInSql("rr.depart", optionValues); + } else { + inSql = "and 1=1"; + notInSql = "and 1=0"; + inSql = "and 1=1"; + notInSql = "and 1=0"; + } + String returnBetweenSql = " and " + dateQueryAdapter.dateAreaSql("rr.returntime", startDay, endDay, true); + String groupBySql = " group by td.packageSize "; + String tousseTypeInSql = String.format(" in ('%s','%s','%s','%s')", TousseDefinition.PACKAGE_TYPE_INSIDE, TousseDefinition.PACKAGE_TYPE_CUSTOM, TousseDefinition.PACKAGE_TYPE_DRESSING, TousseDefinition.PACKAGE_TYPE_FOREIGNPROXY); + String packageSizeCaseSql = String.format("case when packageSize='%s' then 1 when packageSize='%s' then 2 when packageSize='%s' then 3 when packageSize='%s' then 4 else 0 end sequence ", + TousseDefinition.PACKAGE_SIZE_HUGE,TousseDefinition.PACKAGE_SIZE_BIG,TousseDefinition.PACKAGE_SIZE_CENTER,TousseDefinition.PACKAGE_SIZE_SMALL); + String returnSql = String.format(" select td.packageSize,-sum(ri.amount) amount " + + "from ReturnGoodsRecord rr inner join ReturnGoodsItem ri on rr.id = ri.returngoodsrecord_id " + + "join TousseDefinition td on td.id = ri.tousseDefinition_id " + + "where rr.handleDepartCode='%s' %s ", + departmentCode,returnBetweenSql); + //1、本院科室 + String sql1 = String.format("select td.packageSize,sum(ii.amount) amount " + + "from Invoice i join InvoiceItem ii on ii.invoice_id=i.id join TousseDefinition td on td.id=ii.tousseDefinitionId " + + "where 1=1 " + + "and ii.tousseType %s %s %s %s group by td.packageSize ", + tousseTypeInSql, + betweenSql, orgUnitCodingSql, inSql); + sql1 = "select packageSize,sum(amount) amount,"+ packageSizeCaseSql +" from (" + sql1 + " union all " + returnSql + " and td.tousseType " + tousseTypeInSql + returnInSql + groupBySql + ")tempTable group by packageSize order by sequence"; + logger.debug("本院器械包" + sql1); + setTousseSpecificationSizeStatisticsBean(list, sql1, "本院器械包"); + //2、外院科室 + sql1 = String.format("select td.packageSize,sum(ii.amount) amount " + + "from Invoice i join InvoiceItem ii on ii.invoice_id=i.id join TousseDefinition td on td.id=ii.tousseDefinitionId " + + "where 1=1 " + + "and ii.tousseType %s %s %s %s group by td.packageSize ", + tousseTypeInSql, + betweenSql, orgUnitCodingSql, notInSql); + sql1 = "select packageSize,sum(amount) amount,"+ packageSizeCaseSql +" from (" + sql1 + " union all " + returnSql + " and td.tousseType " + tousseTypeInSql + returnNotInSql + groupBySql + ")tempTable group by packageSize order by sequence "; + logger.debug("外院器械包" + sql1); + setTousseSpecificationSizeStatisticsBean(list, sql1, "外院器械包"); + + tousseTypeInSql = String.format(" and td.tousseType in ('%s','%s')", TousseDefinition.PACKAGE_TYPE_FOREIGN,TousseDefinition.PACKAGE_TYPE_SPLIT); + //3、植入物器械包 + sql1 = String.format("select t1.packageSize,sum(amount) amount " + + "from(" + + "select td.packageSize,sum(ii.amount) amount " + //+ "from Invoice i join TousseInstance ti on ti.invoice_id=i.id " + + "from Invoice i join InvoiceItem ii on ii.invoice_id=i.id " + + "join TousseDefinition td on td.id=ii.tousseDefinitionId " + + "where 1=1 %s " + + " %s %s group by td.id,td.packageSize" + + ") t1 group by t1.packageSize ", + tousseTypeInSql, betweenSql, orgUnitCodingSql); + sql1 = "select packageSize,sum(amount) amount,"+ packageSizeCaseSql +" from (" + sql1 + " union all " + returnSql + tousseTypeInSql + groupBySql + ")tempTable group by packageSize order by sequence "; + logger.debug("植入物器械包" + sql1); + setTousseSpecificationSizeStatisticsBean(list, sql1, "植入物器械包"); + //4、消毒物品 + sql1 = String.format("select '消毒包' packageSize,sum(ii.amount) amount from Invoice i join InvoiceItem ii on ii.invoice_id=i.id " + + "where ii.tousseType='%s' %s %s", + TousseDefinition.PACKAGE_TYPE_DISINFECTION, betweenSql, orgUnitCodingSql); + sql1 = "select '消毒包' packageSize,sum(amount) amount from (" + sql1 + " union all " + returnSql + " and td.tousseType='"+ TousseDefinition.PACKAGE_TYPE_DISINFECTION +"'" + groupBySql + ")tempTable "; + logger.debug("消毒物品" + sql1); + setTousseSpecificationSizeStatisticsBean(list, sql1, "消毒物品"); + //材料发货,也计算到发货数量的包数和件数上 CDSLQYQYYY-84 + StringBuilder sbr = new StringBuilder(); + sbr.append("select '材料' packageSize,sum(amount) amount from (select sum(ii.amount) amount from ") + .append(MaterialInvoiceItem.class.getSimpleName()) + .append(" ii inner join ") + .append(MaterialInvoice.class.getSimpleName()) + .append(" i on i.id = ii.materialInvoice_id ") + .append("inner join ") + .append(MaterialDefinition.class.getSimpleName()) + .append(" d on ii.materialDefinitionId = d.id where 1=1 ") + .append(betweenSql) + .append(SqlUtils.get_InSql_Extra("i.orgUnitCoding",departmentCode)); + betweenSql = " and " + dateQueryAdapter.dateAreaSql("rr.returnTime", startDay, endDay, true); + sbr.append(" union all select sum(-rmi.amount) amount from ") + .append(ReturnMaterialRecord.class.getSimpleName()) + .append(" rr join ") + .append(ReturnMaterialItem.class.getSimpleName()) + .append(" rmi on rmi.returnMaterialRecord_ID=rr.id where 1=1 ") + .append(SqlUtils.get_InSql_Extra("rr.handleDepartCode",departmentCode)) + .append(betweenSql) + .append(")temp "); + logger.debug("材料发货" + sbr.toString()); + setTousseSpecificationSizeStatisticsBean(list, sbr.toString(), "材料"); + } + return list; + } + + /** + * 设置TousseSpecificationSizeStatistics的bean集合（此方法只适用于本类的getTousseSpecificationSizeStatisticsData方法） + * @param list + * @param sql + * @param tousseGroup + */ + private void setTousseSpecificationSizeStatisticsBean(List list, + String sql, String tousseGroup) { + List childs = new ArrayList(); + ResultSet result = objectDao.executeSql(sql); + try { + //packageSize为空字符串或null的map + Map resultMap = new HashMap(); + while(result.next()){ + String packageSize = result.getString("packageSize"); + if(StringUtils.isBlank(packageSize)){ + packageSize = ""; + } + int amount = result.getInt("amount"); + if (amount > 0) { + TousseSpecificationSizeStatistics_child child = null; + if(resultMap.containsKey(packageSize)){//"" 和 null 合并为1行 + child = resultMap.get(packageSize); + child.setAmount(child.getAmount() + amount); + }else{ + child = new TousseSpecificationSizeStatistics_child(); + child.setPackageSize(packageSize); + child.setAmount(amount); + childs.add(child); + if("".equals(packageSize)){ + resultMap.put(packageSize, child); + } + } + } + } + } catch (SQLException e) { + e.printStackTrace(); + }finally { + DatabaseUtil.closeResultSetAndStatement(result); + } + if (CollectionUtils.isNotEmpty(childs)) { + TousseSpecificationSizeStatistics bean1 = new TousseSpecificationSizeStatistics(); + bean1.setTousseGroup(tousseGroup); + bean1.setChild(childs); + list.add(bean1); + } + } }