Index: ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/service/JasperReportManagerImpl.java =================================================================== diff -u -r35866 -r35872 --- ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/service/JasperReportManagerImpl.java (.../JasperReportManagerImpl.java) (revision 35866) +++ ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/service/JasperReportManagerImpl.java (.../JasperReportManagerImpl.java) (revision 35872) @@ -172,6 +172,7 @@ import com.forgon.disinfectsystem.jasperreports.util.ForeignTousseApplicationReportHelper; import com.forgon.disinfectsystem.jasperreports.util.ForeignTousseSupplierChargeReportHelper; import com.forgon.disinfectsystem.jasperreports.util.FormDefinitionHelper; +import com.forgon.disinfectsystem.jasperreports.util.GodownEntryStatisticReportHelper; import com.forgon.disinfectsystem.jasperreports.util.GoodsAmountSummaryByGradeHelper; import com.forgon.disinfectsystem.jasperreports.util.InformationOfDepartmentOperationMonitoringHelper; import com.forgon.disinfectsystem.jasperreports.util.InstrumentRepairReportHelper; @@ -406,6 +407,8 @@ private UseRecordEnterReportHelper useRecordEnterReportHelper; @Autowired private RinserStoveUseCountReportHelper rinserStoveUseCountReportHelper; + @Autowired + private GodownEntryStatisticReportHelper godownEntryStatisticReportHelper; /** * 利用sql语句做数据源创建JasperPrint对象 * @param realPath 报表的jasper文件在服务器本地机子的全路径 @@ -1775,306 +1778,9 @@ String title, String author, String orgUnit, String sheetId, String unit, String timeStart, String timeEnd, String goodsName, String filterSql1, String filterSql2,String suplierName,String goodsType,String disposableGoodsType, String businessType) { - - String supplierWhere = " 1=1 "; - String materialSupplierWhere = " 1=1 "; - if(StringTools.isNotEmpty(suplierName)){ - supplierWhere = " gei.supplierName = '" + suplierName + "'"; - materialSupplierWhere = " mei.supplierName = '" + suplierName + "'"; - } - - //按科室过滤仓库的条件 - Set warehouseIds = wareHouseManager.getCurrentUserOrgUnitWarehouseIds(); - String disposableGoodsWarehouseWhere = SqlBuilder.build_number_IN_Statement("gei.warehouseID", SqlBuilder.IN, warehouseIds); - String materialwarehouseWhere = SqlBuilder.build_number_IN_Statement("mei.warehouseID", SqlBuilder.IN, warehouseIds); - String disposableGoodsTypeWhere = " 1=1 "; - if(StringTools.isNotBlank(disposableGoodsType)){ - disposableGoodsTypeWhere = " dgs.type = '" + disposableGoodsType + "' "; - } - - List mainList = new ArrayList(); - String query1 = ""; - String query2 = ""; - if (dbConnection.isSqlServer()) { - query1 = "dg.name + CASE WHEN dg.specification is null THEN '' ELSE '['+dg.specification+']' END as showname, "; - query2 = "name + CASE WHEN specification is null THEN '' ELSE '['+specification+']' END as showname, "; - } - // Oracle或者其他数据库 - else { - query1 = "dg.name||decode(dg.specification,null,'','['||dg.specification||']') as showname, "; - query2 = "name||decode(specification,null,'','['||specification||']') as showname, "; - } - String geBusinessTypeSql = null; - String meBusinessTypeSql = null; - if(StringUtils.isBlank(businessType)){ - geBusinessTypeSql = ""; - meBusinessTypeSql = ""; - }else if("入库加退库".equals(businessType)){ - geBusinessTypeSql = " and (" + GodownEntryUtil.getGodownEntryTypeSqlWhere("ge", GodownEntry.TYPE_OUT) + " or " + GodownEntryUtil.getGodownEntryTypeSqlWhere("ge", GodownEntry.TYPE_IN) + ") "; - meBusinessTypeSql = " and (" + MaterialEntryUtil.getMaterialEntryTypeSqlWhere("me", MaterialEntry.TYPE_OUT) + " or " + MaterialEntryUtil.getMaterialEntryTypeSqlWhere("me", MaterialEntry.TYPE_IN) + ") "; - }else if("入库".equals(businessType)){ - geBusinessTypeSql = " and " + GodownEntryUtil.getGodownEntryTypeSqlWhere("ge", GodownEntry.TYPE_IN); - meBusinessTypeSql = " and " + MaterialEntryUtil.getMaterialEntryTypeSqlWhere("me", MaterialEntry.TYPE_IN); - }else if("退库".equals(businessType)){ - geBusinessTypeSql = " and " + GodownEntryUtil.getGodownEntryTypeSqlWhere("ge", GodownEntry.TYPE_OUT); - meBusinessTypeSql = " and " + MaterialEntryUtil.getMaterialEntryTypeSqlWhere("me", MaterialEntry.TYPE_OUT); - }else{ - geBusinessTypeSql = ""; - meBusinessTypeSql = ""; - } - String dispasableGoodsSql = "select null as mid,did,showname,sum(totalprice) as totalprice,batchnumber,serialNumber from " - + "(select dg.id as did," + query1 + "sum(gei.amount) as totalamount,gei.batchnumber as batchnumber,gei.cost, " - + "sum(case when ge.type='退库单' then -gei.amount else gei.amount end)*gei.cost as totalprice" - + ",ge.serialNumber as serialNumber " - + "from " + GodownEntryItem.class.getSimpleName() + " gei inner join " + GodownEntry.class.getSimpleName() + " ge " - + " on ge.id = gei.godownentry_id inner join " - + DisposableGoodsStock.class.getSimpleName() + " dg on gei.diposablegoods_id = dg.id " - + " inner join " + DisposableGoods.class.getSimpleName() + " dgs on dg.disposableGoodsID = dgs.id " - + " where " + disposableGoodsTypeWhere - + " and " + supplierWhere - + geBusinessTypeSql - + " and ge.time >= " + dateQueryAdapter.dateAdapter(timeStart) - + " and ge.time <= " + dateQueryAdapter.dateAdapter(timeEnd) - + " and " + disposableGoodsWarehouseWhere - + filterSql1 - + " group by dg.id,dg.name,dg.specification,gei.batchnumber,gei.cost,ge.serialNumber " - + ") temp1 group by showname,did,batchnumber,serialNumber "; - - String materialSql = "select mid,null as did,showname,totalprice,null as batchnumber,serialNumber from " - + "(select mid,"+ query2 + "sum(totalprice) as totalprice,serialNumber from " - + "(select md.id as mid,md.name,md.specification,mei.amount as amount," - + "mei.cost as price" - + ",case when me.type='退库单' then -mei.amount else mei.amount end * mei.cost as totalprice" - + ",me.serialNumber as serialNumber " - + "from materialentry me,materialentryitem mei,materialdefinition md " - + "where me.id = mei.materialentry_id and mei.materialdefinition_id = md.id " - + " and " + materialSupplierWhere - + meBusinessTypeSql - + "and me.time >= " + dateQueryAdapter.dateAdapter(timeStart) - + " and me.time <= " + dateQueryAdapter.dateAdapter(timeEnd) - + " and " + materialwarehouseWhere - + filterSql2 + ") temp2 group by mid,name,specification,serialNumber " - + ") temp3 order by did,showname"; - String sql = ""; - if("disposableGoods".equals(goodsType)){ - sql = dispasableGoodsSql; - }else if("material".equals(goodsType)){ - sql = materialSql; - }else{ - sql = dispasableGoodsSql; - sql += "union all "; - sql += materialSql; - } - ResultSet rs = objectDao.executeSql(sql); - if(rs != null){ - try { - while (rs.next()) { - String mid = rs.getString(1); - String did = rs.getString(2); - String showname = rs.getString(3); - Double totalprice = rs.getDouble(4); - String batchnumber = rs.getString(5); - String serialNumber = rs.getString(6); - - GodownEntryStatistic_main main = new GodownEntryStatistic_main(); - main.setMid(mid); - main.setDid(did); - main.setShowname(showname); - main.setTitle(title); - main.setAuthor(author); - main.setSheetld(sheetId); - main.setTimeEnd(timeEnd); - main.setTimeStart(timeStart); - main.setUnit(unit); - main.setOrgUnit(orgUnit); - main.setBatchnumber(batchnumber); - main.setGodownEntryNumber(serialNumber); - mainList.add(main); - } - } catch (SQLException e) { - e.printStackTrace(); - } finally { - DatabaseUtil.closeResultSetAndStatement(rs); - } - } - - - for (GodownEntryStatistic_main main : mainList) { - - List childBeans = getGodownEntryStatisticChildBean( - timeStart, timeEnd, main.getDid(), main.getMid(), - main.getBatchnumber(),main.getGodownEntryNumber(),suplierName,goodsType,warehouseIds, businessType); - main.setChildList(childBeans); - } - for (GodownEntryStatistic_main main : mainList) { - for(GodownEntryStatistic_child child : main.getChildList()){ - Integer childTotalamount = child.getChildTotalamount(); - if(childTotalamount == null){ - childTotalamount = 0; - } - Double childTotalprice = child.getChildTotalprice(); - if(childTotalprice == null){ - childTotalprice = 0.0d; - } - Integer mainTotalAmount = main.getMainTotalAmount()==null?MathTools.ZERO_INTEGER:main.getMainTotalAmount(); - Double mainTotalPrice = main.getMainTotalPrice()==null?MathTools.ZERO_DOUBLE:main.getMainTotalPrice(); - main.setMainTotalAmount(mainTotalAmount+childTotalamount); - main.setMainTotalPrice(mainTotalPrice+childTotalprice); - } - } - return mainList; + return godownEntryStatisticReportHelper.getGodownEntryStatisticBean(title, author, orgUnit, sheetId, unit, timeStart, timeEnd, goodsName, filterSql1, filterSql2, suplierName, goodsType, disposableGoodsType, businessType); } - private List getGodownEntryStatisticChildBean( - String timeStart, String timeEnd, String did, String mid, - String batchnumber,String serialNumber,String filterSupplierName,String goodsType,Set warehouseIds, String businessType) { - - String supplierWhere = " 1=1 "; - String materialSupplierWhere = " 1=1 "; - if(StringTools.isNotEmpty(filterSupplierName)){ - supplierWhere = " gei.supplierName = '" + filterSupplierName + "'"; - materialSupplierWhere = " mei.supplierName = '" + filterSupplierName + "'"; - } - //按科室过滤仓库的条件 - String disposableGoodsWarehouseWhere = SqlBuilder.build_number_IN_Statement("gei.warehouseID", SqlBuilder.IN, warehouseIds); - String materialwarehouseWhere = SqlBuilder.build_number_IN_Statement("mei.warehouseID", SqlBuilder.IN, warehouseIds); - String geBusinessTypeSql = null; - String meBusinessTypeSql = null; - if(StringUtils.isBlank(businessType)){ - geBusinessTypeSql = ""; - meBusinessTypeSql = ""; - }else if("入库加退库".equals(businessType)){ - geBusinessTypeSql = " and (" + GodownEntryUtil.getGodownEntryTypeSqlWhere("ge", GodownEntry.TYPE_OUT) + " or " + GodownEntryUtil.getGodownEntryTypeSqlWhere("ge", GodownEntry.TYPE_IN) + ") "; - meBusinessTypeSql = " and (" + MaterialEntryUtil.getMaterialEntryTypeSqlWhere("me", MaterialEntry.TYPE_OUT) + " or " + MaterialEntryUtil.getMaterialEntryTypeSqlWhere("me", MaterialEntry.TYPE_IN) + ") "; - }else if("入库".equals(businessType)){ - geBusinessTypeSql = " and " + GodownEntryUtil.getGodownEntryTypeSqlWhere("ge", GodownEntry.TYPE_IN); - meBusinessTypeSql = " and " + MaterialEntryUtil.getMaterialEntryTypeSqlWhere("me", MaterialEntry.TYPE_IN); - }else if("退库".equals(businessType)){ - geBusinessTypeSql = " and " + GodownEntryUtil.getGodownEntryTypeSqlWhere("ge", GodownEntry.TYPE_OUT); - meBusinessTypeSql = " and " + MaterialEntryUtil.getMaterialEntryTypeSqlWhere("me", MaterialEntry.TYPE_OUT); - }else{ - geBusinessTypeSql = ""; - meBusinessTypeSql = ""; - } - String dispasableGoodsSql = "select batchnumber,totalamount,price,totalprice as totalprice,expDate,supplierName," - +"sterileBatchNumber,manufacturer,certification,id,time from" - + " (select " - + "dg.id as did," - + "sum(case when ge.type='"+ GodownEntry.TYPE_OUT +"' then -gei.amount else gei.amount end) as totalamount,gei.batchnumber,gei.cost as price," - + "sum(case when ge.type='"+ GodownEntry.TYPE_OUT +"' then -gei.amount else gei.amount end)*gei.cost as totalprice,"+dateQueryAdapter.dateToVarchar("ds.expDate")+" as expDate,gei.supplierName as supplierName," - +"ds.sterileBatchNumber as sterileBatchNumber,ds.manufacturer as manufacturer,dg.certification as certification,ge.id as id,ge.time as time " - + "from " - + "godownentryitem gei,godownentry ge," + DisposableGoodsStock.class.getSimpleName() + " dg," + DisposableGoodsBatchStock.class.getSimpleName() + " ds " - + "where ge.id = gei.godownentry_id " - + "and gei.diposablegoods_id = dg.id " - + "and " + supplierWhere - + "and dg.id = ds.diposablegoods_id " - + "and gei.batchnumber = ds.batchnumber " - + geBusinessTypeSql - + "and ge.time >= " - + dateQueryAdapter.dateAdapter(timeStart) - + " and ge.time <= " - + dateQueryAdapter.dateAdapter(timeEnd) - + " and " + disposableGoodsWarehouseWhere - + " and dg.id = " - + did - + " and ds.batchNumber = '" - + batchnumber - + "' and ge.serialNumber = '"+serialNumber - + "' group by dg.id,dg.name,dg.specification,gei.batchnumber,gei.cost,ds.expDate,gei.supplierName,ds.sterileBatchNumber," - +"ds.manufacturer,dg.certification,ge.id,ge.time " - + ") temp1 "; - - String materialSql = "select '' as batchnumber,amount,price,totalprice,'' as expDate,supplierName,'' as sterileBatchNumber," - +"'' as manufacturer,'' as certification,id,time from " - + "(select " - + "sum(amount) as amount, " - + "sum(totalprice) as totalprice, " - + "sum(totalprice)/sum(amount) as price,supplierName,id,time " - + "from " - + "( " - + "select " - + "md.id as mid, " - + "md.name, " - + "md.specification, " - + "case when me.type='"+ MaterialEntry.TYPE_OUT +"' then -mei.amount else mei.amount end as amount, " - + "mei.cost as price, " - + "case when me.type='"+ MaterialEntry.TYPE_OUT +"' then -mei.cost else mei.cost end *mei.amount as totalprice,mei.supplierName as supplierName,me.id as id,me.time as time " - + "from materialentry me,materialentryitem mei,materialdefinition md " - + "where me.id = mei.materialentry_id " - + "and mei.materialdefinition_id = md.id " - + " and " + materialSupplierWhere - + meBusinessTypeSql - + "and me.time >= " - + dateQueryAdapter.dateAdapter(timeStart) - + "and me.time <= " - + dateQueryAdapter.dateAdapter(timeEnd) - + " and " + materialwarehouseWhere - + " and md.id = "+ mid +" and me.serialNumber = '"+serialNumber - + "') temp2 " + " group by mid,name,specification,supplierName,id,time " + ") temp3"; - - String sql = ""; - if("disposableGoods".equals(goodsType)){ - sql = dispasableGoodsSql; - }else if("material".equals(goodsType)){ - sql = materialSql; - }else{ - sql = dispasableGoodsSql; - sql += "union all "; - sql += materialSql; - } - ResultSet rs = objectDao.executeSql(sql); - List list = new ArrayList(); - if(rs != null){ - try { - while (rs.next()) { - GodownEntryStatistic_child child = new GodownEntryStatistic_child(); - String batchnumber1 = rs.getString(1); - Integer totalamount = rs.getInt(2); - Double price = rs.getDouble(3); - Double totalprice = rs.getDouble(4); - String expDate = rs.getString(5); - - if(StringUtils.isNotBlank(expDate) && !"1900-01-01 00:00:00.0".equals(expDate)){ - expDate = DateTools.getFormatDateStr(rs.getDate(5),"yyyy-MM-dd"); - }else{ - expDate = ""; - } - String supplierName = rs.getString(6); - String sterileBatchNumber = rs.getString(7); - String manufacturer = rs.getString(8); - String certification = rs.getString(9); - String id = rs.getString(10); - String time = DateTools.getFormatDateStr(rs.getTimestamp(11),"yyyy-MM-dd"); - child.setId(id); - child.setChildBatchnumber(batchnumber1); - child.setChildPrice(price); - child.setChildTotalamount(totalamount); - child.setChildTotalprice(totalprice); - child.setExpiryDate(expDate); - child.setSupplier(supplierName); - child.setSterilizationBatchNumber(sterileBatchNumber); - child.setManufacturer(manufacturer); - child.setRegistration(certification); - child.setTime(time); - //利用批次号是否为null判断是否一次性物品 - if(StringUtils.isNotBlank(batchnumber1)){ - child.setIsDiposable(1); - }else{ - child.setIsDiposable(0); - } - child.setSerialNumber(serialNumber); - list.add(child); - } - } catch (SQLException e) { - e.printStackTrace(); - } finally { - DatabaseUtil.closeResultSetAndStatement(rs); - } - } - return list; - } - @Override public List getwashWorkloadList(String startTime, String endTime, String querySupplyRoom, String queryDateType) { return washWorkloadReprotHelper.getWashWorkloadList(startTime, endTime, querySupplyRoom, queryDateType); Index: ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/util/GodownEntryStatisticReportHelper.java =================================================================== diff -u --- ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/util/GodownEntryStatisticReportHelper.java (revision 0) +++ ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/util/GodownEntryStatisticReportHelper.java (revision 35872) @@ -0,0 +1,364 @@ +package com.forgon.disinfectsystem.jasperreports.util; + +import java.sql.ResultSet; +import java.sql.SQLException; +import java.util.ArrayList; +import java.util.List; +import java.util.Set; +import org.apache.commons.lang.StringUtils; +import org.springframework.beans.factory.annotation.Autowired; +import org.springframework.stereotype.Component; +import com.forgon.databaseadapter.service.DateQueryAdapter; +import com.forgon.disinfectsystem.basedatamanager.warehouse.service.WareHouseManager; +import com.forgon.disinfectsystem.diposablegoods.util.GodownEntryUtil; +import com.forgon.disinfectsystem.diposablegoods.util.MaterialEntryUtil; +import com.forgon.disinfectsystem.entity.assestmanagement.DisposableGoods; +import com.forgon.disinfectsystem.entity.assestmanagement.DisposableGoodsBatchStock; +import com.forgon.disinfectsystem.entity.assestmanagement.DisposableGoodsStock; +import com.forgon.disinfectsystem.entity.assestmanagement.GodownEntry; +import com.forgon.disinfectsystem.entity.assestmanagement.GodownEntryItem; +import com.forgon.disinfectsystem.entity.materialmanager.MaterialEntry; +import com.forgon.disinfectsystem.jasperreports.javabeansource.GodownEntryStatistic_child; +import com.forgon.disinfectsystem.jasperreports.javabeansource.GodownEntryStatistic_main; +import com.forgon.tools.MathTools; +import com.forgon.tools.SqlBuilder; +import com.forgon.tools.date.DateTools; +import com.forgon.tools.db.DatabaseUtil; +import com.forgon.tools.db.InitDbConnection; +import com.forgon.tools.hibernate.ObjectDao; +import com.forgon.tools.string.StringTools; + +/** + * 入库物品统计报表 + */ +@Component +public class GodownEntryStatisticReportHelper { + @Autowired + private DateQueryAdapter dateQueryAdapter; + @Autowired + private ObjectDao objectDao; + @Autowired + private InitDbConnection dbConnection; + @Autowired + private WareHouseManager wareHouseManager; + /** + * 获取入库统计报表的数据 + * @param title + * @param author + * @param orgUnit + * @param sheetId + * @param unit + * @param timeStart + * @param timeEnd + * @param goodsName + * @param filterSql1 + * @param filterSql2 + * @param suplierName + * @param goodsType 物品类型,可选值为disposableGoods表示一次性物品,material表示材料,其他值两者都查 + * @param disposableGoodsType 一次性物品类型 + * @param businessType 业务类型 + * @return + */ + public List getGodownEntryStatisticBean( + String title, String author, String orgUnit, String sheetId, + String unit, String timeStart, String timeEnd, String goodsName, + String filterSql1, String filterSql2,String suplierName,String goodsType,String disposableGoodsType, String businessType) { + + String supplierWhere = " 1=1 "; + String materialSupplierWhere = " 1=1 "; + if(StringTools.isNotEmpty(suplierName)){ + supplierWhere = " gei.supplierName = '" + suplierName + "'"; + materialSupplierWhere = " mei.supplierName = '" + suplierName + "'"; + } + + //按科室过滤仓库的条件 + Set warehouseIds = wareHouseManager.getCurrentUserOrgUnitWarehouseIds(); + String disposableGoodsWarehouseWhere = SqlBuilder.build_number_IN_Statement("gei.warehouseID", SqlBuilder.IN, warehouseIds); + String materialwarehouseWhere = SqlBuilder.build_number_IN_Statement("mei.warehouseID", SqlBuilder.IN, warehouseIds); + String disposableGoodsTypeWhere = " 1=1 "; + if(StringTools.isNotBlank(disposableGoodsType)){ + disposableGoodsTypeWhere = " dgs.type = '" + disposableGoodsType + "' "; + } + + List mainList = new ArrayList(); + String query1 = ""; + String query2 = ""; + if (dbConnection.isSqlServer()) { + query1 = "dg.name + CASE WHEN dg.specification is null THEN '' ELSE '['+dg.specification+']' END as showname, "; + query2 = "name + CASE WHEN specification is null THEN '' ELSE '['+specification+']' END as showname, "; + } + // Oracle或者其他数据库 + else { + query1 = "dg.name||decode(dg.specification,null,'','['||dg.specification||']') as showname, "; + query2 = "name||decode(specification,null,'','['||specification||']') as showname, "; + } + String geBusinessTypeSql = null; + String meBusinessTypeSql = null; + if(StringUtils.isBlank(businessType)){ + geBusinessTypeSql = ""; + meBusinessTypeSql = ""; + }else if("入库加退库".equals(businessType)){ + geBusinessTypeSql = " and (" + GodownEntryUtil.getGodownEntryTypeSqlWhere("ge", GodownEntry.TYPE_OUT) + " or " + GodownEntryUtil.getGodownEntryTypeSqlWhere("ge", GodownEntry.TYPE_IN) + ") "; + meBusinessTypeSql = " and (" + MaterialEntryUtil.getMaterialEntryTypeSqlWhere("me", MaterialEntry.TYPE_OUT) + " or " + MaterialEntryUtil.getMaterialEntryTypeSqlWhere("me", MaterialEntry.TYPE_IN) + ") "; + }else if("入库".equals(businessType)){ + geBusinessTypeSql = " and " + GodownEntryUtil.getGodownEntryTypeSqlWhere("ge", GodownEntry.TYPE_IN); + meBusinessTypeSql = " and " + MaterialEntryUtil.getMaterialEntryTypeSqlWhere("me", MaterialEntry.TYPE_IN); + }else if("退库".equals(businessType)){ + geBusinessTypeSql = " and " + GodownEntryUtil.getGodownEntryTypeSqlWhere("ge", GodownEntry.TYPE_OUT); + meBusinessTypeSql = " and " + MaterialEntryUtil.getMaterialEntryTypeSqlWhere("me", MaterialEntry.TYPE_OUT); + }else{ + geBusinessTypeSql = ""; + meBusinessTypeSql = ""; + } + String dispasableGoodsSql = "select null as mid,did,showname,sum(totalprice) as totalprice,batchnumber,serialNumber from " + + "(select dg.id as did," + query1 + "sum(gei.amount) as totalamount,gei.batchnumber as batchnumber,gei.cost, " + + "sum(case when ge.type='退库单' then -gei.amount else gei.amount end)*gei.cost as totalprice" + + ",ge.serialNumber as serialNumber " + + "from " + GodownEntryItem.class.getSimpleName() + " gei inner join " + GodownEntry.class.getSimpleName() + " ge " + + " on ge.id = gei.godownentry_id inner join " + + DisposableGoodsStock.class.getSimpleName() + " dg on gei.diposablegoods_id = dg.id " + + " inner join " + DisposableGoods.class.getSimpleName() + " dgs on dg.disposableGoodsID = dgs.id " + + " where " + disposableGoodsTypeWhere + + " and " + supplierWhere + + geBusinessTypeSql + + " and ge.time >= " + dateQueryAdapter.dateAdapter(timeStart) + + " and ge.time <= " + dateQueryAdapter.dateAdapter(timeEnd) + + " and " + disposableGoodsWarehouseWhere + + filterSql1 + + " group by dg.id,dg.name,dg.specification,gei.batchnumber,gei.cost,ge.serialNumber " + + ") temp1 group by showname,did,batchnumber,serialNumber "; + + String materialSql = "select mid,null as did,showname,totalprice,null as batchnumber,serialNumber from " + + "(select mid,"+ query2 + "sum(totalprice) as totalprice,serialNumber from " + + "(select md.id as mid,md.name,md.specification,mei.amount as amount," + + "mei.cost as price" + + ",case when me.type='退库单' then -mei.amount else mei.amount end * mei.cost as totalprice" + + ",me.serialNumber as serialNumber " + + "from materialentry me,materialentryitem mei,materialdefinition md " + + "where me.id = mei.materialentry_id and mei.materialdefinition_id = md.id " + + " and " + materialSupplierWhere + + meBusinessTypeSql + + "and me.time >= " + dateQueryAdapter.dateAdapter(timeStart) + + " and me.time <= " + dateQueryAdapter.dateAdapter(timeEnd) + + " and " + materialwarehouseWhere + + filterSql2 + ") temp2 group by mid,name,specification,serialNumber " + + ") temp3 order by did,showname"; + String sql = ""; + if("disposableGoods".equals(goodsType)){ + sql = dispasableGoodsSql; + }else if("material".equals(goodsType)){ + sql = materialSql; + }else{ + sql = dispasableGoodsSql; + sql += "union all "; + sql += materialSql; + } + ResultSet rs = objectDao.executeSql(sql); + if(rs != null){ + try { + while (rs.next()) { + String mid = rs.getString(1); + String did = rs.getString(2); + String showname = rs.getString(3); + Double totalprice = rs.getDouble(4); + String batchnumber = rs.getString(5); + String serialNumber = rs.getString(6); + + GodownEntryStatistic_main main = new GodownEntryStatistic_main(); + main.setMid(mid); + main.setDid(did); + main.setShowname(showname); + main.setTitle(title); + main.setAuthor(author); + main.setSheetld(sheetId); + main.setTimeEnd(timeEnd); + main.setTimeStart(timeStart); + main.setUnit(unit); + main.setOrgUnit(orgUnit); + main.setBatchnumber(batchnumber); + main.setGodownEntryNumber(serialNumber); + mainList.add(main); + } + } catch (SQLException e) { + e.printStackTrace(); + } finally { + DatabaseUtil.closeResultSetAndStatement(rs); + } + } + + + for (GodownEntryStatistic_main main : mainList) { + + List childBeans = getGodownEntryStatisticChildBean( + timeStart, timeEnd, main.getDid(), main.getMid(), + main.getBatchnumber(),main.getGodownEntryNumber(),suplierName,goodsType,warehouseIds, businessType); + main.setChildList(childBeans); + } + for (GodownEntryStatistic_main main : mainList) { + for(GodownEntryStatistic_child child : main.getChildList()){ + Integer childTotalamount = child.getChildTotalamount(); + if(childTotalamount == null){ + childTotalamount = 0; + } + Double childTotalprice = child.getChildTotalprice(); + if(childTotalprice == null){ + childTotalprice = 0.0d; + } + Integer mainTotalAmount = main.getMainTotalAmount()==null?MathTools.ZERO_INTEGER:main.getMainTotalAmount(); + Double mainTotalPrice = main.getMainTotalPrice()==null?MathTools.ZERO_DOUBLE:main.getMainTotalPrice(); + main.setMainTotalAmount(mainTotalAmount+childTotalamount); + main.setMainTotalPrice(mainTotalPrice+childTotalprice); + } + } + return mainList; + } + private List getGodownEntryStatisticChildBean( + String timeStart, String timeEnd, String did, String mid, + String batchnumber,String serialNumber,String filterSupplierName,String goodsType,Set warehouseIds, String businessType) { + + String supplierWhere = " 1=1 "; + String materialSupplierWhere = " 1=1 "; + if(StringTools.isNotEmpty(filterSupplierName)){ + supplierWhere = " gei.supplierName = '" + filterSupplierName + "'"; + materialSupplierWhere = " mei.supplierName = '" + filterSupplierName + "'"; + } + //按科室过滤仓库的条件 + String disposableGoodsWarehouseWhere = SqlBuilder.build_number_IN_Statement("gei.warehouseID", SqlBuilder.IN, warehouseIds); + String materialwarehouseWhere = SqlBuilder.build_number_IN_Statement("mei.warehouseID", SqlBuilder.IN, warehouseIds); + String geBusinessTypeSql = null; + String meBusinessTypeSql = null; + if(StringUtils.isBlank(businessType)){ + geBusinessTypeSql = ""; + meBusinessTypeSql = ""; + }else if("入库加退库".equals(businessType)){ + geBusinessTypeSql = " and (" + GodownEntryUtil.getGodownEntryTypeSqlWhere("ge", GodownEntry.TYPE_OUT) + " or " + GodownEntryUtil.getGodownEntryTypeSqlWhere("ge", GodownEntry.TYPE_IN) + ") "; + meBusinessTypeSql = " and (" + MaterialEntryUtil.getMaterialEntryTypeSqlWhere("me", MaterialEntry.TYPE_OUT) + " or " + MaterialEntryUtil.getMaterialEntryTypeSqlWhere("me", MaterialEntry.TYPE_IN) + ") "; + }else if("入库".equals(businessType)){ + geBusinessTypeSql = " and " + GodownEntryUtil.getGodownEntryTypeSqlWhere("ge", GodownEntry.TYPE_IN); + meBusinessTypeSql = " and " + MaterialEntryUtil.getMaterialEntryTypeSqlWhere("me", MaterialEntry.TYPE_IN); + }else if("退库".equals(businessType)){ + geBusinessTypeSql = " and " + GodownEntryUtil.getGodownEntryTypeSqlWhere("ge", GodownEntry.TYPE_OUT); + meBusinessTypeSql = " and " + MaterialEntryUtil.getMaterialEntryTypeSqlWhere("me", MaterialEntry.TYPE_OUT); + }else{ + geBusinessTypeSql = ""; + meBusinessTypeSql = ""; + } + String dispasableGoodsSql = "select batchnumber,totalamount,price,totalprice as totalprice,expDate,supplierName," + +"sterileBatchNumber,manufacturer,certification,id,time from" + + " (select " + + "dg.id as did," + + "sum(case when ge.type='"+ GodownEntry.TYPE_OUT +"' then -gei.amount else gei.amount end) as totalamount,gei.batchnumber,gei.cost as price," + + "sum(case when ge.type='"+ GodownEntry.TYPE_OUT +"' then -gei.amount else gei.amount end)*gei.cost as totalprice,"+dateQueryAdapter.dateToVarchar("ds.expDate")+" as expDate,gei.supplierName as supplierName," + +"ds.sterileBatchNumber as sterileBatchNumber,ds.manufacturer as manufacturer,dg.certification as certification,ge.id as id,ge.time as time " + + "from " + + "godownentryitem gei,godownentry ge," + DisposableGoodsStock.class.getSimpleName() + " dg," + DisposableGoodsBatchStock.class.getSimpleName() + " ds " + + "where ge.id = gei.godownentry_id " + + "and gei.diposablegoods_id = dg.id " + + "and " + supplierWhere + + "and dg.id = ds.diposablegoods_id " + + "and gei.batchnumber = ds.batchnumber " + + geBusinessTypeSql + + "and ge.time >= " + + dateQueryAdapter.dateAdapter(timeStart) + + " and ge.time <= " + + dateQueryAdapter.dateAdapter(timeEnd) + + " and " + disposableGoodsWarehouseWhere + + " and dg.id = " + + did + + " and ds.batchNumber = '" + + batchnumber + + "' and ge.serialNumber = '"+serialNumber + + "' group by dg.id,dg.name,dg.specification,gei.batchnumber,gei.cost,ds.expDate,gei.supplierName,ds.sterileBatchNumber," + +"ds.manufacturer,dg.certification,ge.id,ge.time " + + ") temp1 "; + + String materialSql = "select '' as batchnumber,amount,price,totalprice,'' as expDate,supplierName,'' as sterileBatchNumber," + +"'' as manufacturer,'' as certification,id,time from " + + "(select " + + "sum(amount) as amount, " + + "sum(totalprice) as totalprice, " + + "sum(totalprice)/sum(amount) as price,supplierName,id,time " + + "from " + + "( " + + "select " + + "md.id as mid, " + + "md.name, " + + "md.specification, " + + "case when me.type='"+ MaterialEntry.TYPE_OUT +"' then -mei.amount else mei.amount end as amount, " + + "mei.cost as price, " + + "case when me.type='"+ MaterialEntry.TYPE_OUT +"' then -mei.cost else mei.cost end *mei.amount as totalprice,mei.supplierName as supplierName,me.id as id,me.time as time " + + "from materialentry me,materialentryitem mei,materialdefinition md " + + "where me.id = mei.materialentry_id " + + "and mei.materialdefinition_id = md.id " + + " and " + materialSupplierWhere + + meBusinessTypeSql + + "and me.time >= " + + dateQueryAdapter.dateAdapter(timeStart) + + "and me.time <= " + + dateQueryAdapter.dateAdapter(timeEnd) + + " and " + materialwarehouseWhere + + " and md.id = "+ mid +" and me.serialNumber = '"+serialNumber + + "') temp2 " + " group by mid,name,specification,supplierName,id,time " + ") temp3"; + + String sql = ""; + if("disposableGoods".equals(goodsType)){ + sql = dispasableGoodsSql; + }else if("material".equals(goodsType)){ + sql = materialSql; + }else{ + sql = dispasableGoodsSql; + sql += "union all "; + sql += materialSql; + } + ResultSet rs = objectDao.executeSql(sql); + List list = new ArrayList(); + if(rs != null){ + try { + while (rs.next()) { + GodownEntryStatistic_child child = new GodownEntryStatistic_child(); + String batchnumber1 = rs.getString(1); + Integer totalamount = rs.getInt(2); + Double price = rs.getDouble(3); + Double totalprice = rs.getDouble(4); + String expDate = rs.getString(5); + + if(StringUtils.isNotBlank(expDate) && !"1900-01-01 00:00:00.0".equals(expDate)){ + expDate = DateTools.getFormatDateStr(rs.getDate(5),"yyyy-MM-dd"); + }else{ + expDate = ""; + } + String supplierName = rs.getString(6); + String sterileBatchNumber = rs.getString(7); + String manufacturer = rs.getString(8); + String certification = rs.getString(9); + String id = rs.getString(10); + String time = DateTools.getFormatDateStr(rs.getTimestamp(11),"yyyy-MM-dd"); + child.setId(id); + child.setChildBatchnumber(batchnumber1); + child.setChildPrice(price); + child.setChildTotalamount(totalamount); + child.setChildTotalprice(totalprice); + child.setExpiryDate(expDate); + child.setSupplier(supplierName); + child.setSterilizationBatchNumber(sterileBatchNumber); + child.setManufacturer(manufacturer); + child.setRegistration(certification); + child.setTime(time); + //利用批次号是否为null判断是否一次性物品 + if(StringUtils.isNotBlank(batchnumber1)){ + child.setIsDiposable(1); + }else{ + child.setIsDiposable(0); + } + child.setSerialNumber(serialNumber); + list.add(child); + } + } catch (SQLException e) { + e.printStackTrace(); + } finally { + DatabaseUtil.closeResultSetAndStatement(rs); + } + } + return list; + } +}