Index: ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/service/JasperReportManagerImpl.java =================================================================== diff -u -r35250 -r35278 --- ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/service/JasperReportManagerImpl.java (.../JasperReportManagerImpl.java) (revision 35250) +++ ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/service/JasperReportManagerImpl.java (.../JasperReportManagerImpl.java) (revision 35278) @@ -14862,248 +14862,389 @@ } return price; } - - @Override - public List getForeignTousseApplicationReport( - String timeType, String startDay, String endDay, String goodsName, String deptName , String patientName , String deliverStatus, String companyName, String departOfPatient, boolean noDisplayTerminatedIP, String customColumnName1, Map parametMap, Long materialId, boolean showNoInvoie, String tousseDefinitionName, String used, Long customTimePeriodId) { - boolean filterConpanyName = false;//是否过滤供应商 - if(StringUtils.isNotBlank(companyName)){ - filterConpanyName = true; + /** + * 外来器械申请单统计报表上下文 + */ + class ForeignTousseApplicationReportVo{ + /** + * 查询接收人 + */ + private boolean queryReceiveMan; + /** + * 查询归还人 + */ + private boolean queryReturnMan; + /** + * 查询归还时间 + */ + private boolean queryReturnTime; + /** + * DGSETYY-61新增配置项所配置的值 用来增加额外的查询列 目前支持值为receiveMan,returnMan,returnTime 不区分大小写 + */ + private String extendedColumnConfigOfForeignTousseApplicationReport; + /** + * 是否显示大小包 + */ + private boolean enableTheSizeDisplayOfTheForeignTousseApplicationReport; + /** + * 查询材料明细 + */ + private boolean enableShowForeignTousseApplicationReportMaterials; + /** + * 显示材料汇总 + */ + private boolean enableShowForeignTousseApplicationReportSummaryOfMaterials; + /** + * 查询语句 + */ + private String querySql = ""; + public boolean isQueryReceiveMan() { + return queryReceiveMan; } - List list = new LinkedList(); - if (StringUtils.isNotBlank(endDay)) { - endDay += ":59"; + public void setQueryReceiveMan(boolean queryReceiveMan) { + this.queryReceiveMan = queryReceiveMan; } - if (StringUtils.isNotBlank(startDay)) { - startDay += ":00"; + public boolean isQueryReturnMan() { + return queryReturnMan; } - String customColumnName1Sql = null; - if(StringUtils.isNotBlank(customColumnName1)){ - customColumnName1Sql = ",f."+customColumnName1; - }else{ - customColumnName1 = "ascriptionDepartment"; - customColumnName1Sql = ",f.ascriptionDepartment"; + public void setQueryReturnMan(boolean queryReturnMan) { + this.queryReturnMan = queryReturnMan; } - StringBuilder sqlBuilder = new StringBuilder(); - String queryMaterialNameSql = null; - String joinMaterialInstanceSql = null; - String groupByMaterialInstanceSql = null; - boolean enableShowForeignTousseApplicationReportMaterials = CssdUtils.getSystemSetConfigByNameBool("enableShowForeignTousseApplicationReportMaterials", false); - String materialInstanceSql = null; - if(DatabaseUtil.isPoIdValid(materialId) || enableShowForeignTousseApplicationReportMaterials){ - joinMaterialInstanceSql = " left join MaterialInstance mi on mi.tousse_id=td.id "; - }else{ - joinMaterialInstanceSql = ""; + public boolean isQueryReturnTime() { + return queryReturnTime; } - if(DatabaseUtil.isPoIdValid(materialId)){ - materialInstanceSql = " and td.id in(select tousse_id from MaterialInstance where materialDefinition_id="+ materialId +") "; - }else{ - materialInstanceSql = ""; + public void setQueryReturnTime(boolean queryReturnTime) { + this.queryReturnTime = queryReturnTime; } - String tousseSql = null; - if(StringUtils.isNotBlank(tousseDefinitionName)){ - tousseSql = " and td.name='" + tousseDefinitionName + "' "; - }else{ - tousseSql = ""; + public String getExtendedColumnConfigOfForeignTousseApplicationReport() { + return extendedColumnConfigOfForeignTousseApplicationReport; } - if(enableShowForeignTousseApplicationReportMaterials){//查询材料明细 - queryMaterialNameSql = ",mi.materialName,sum(mi.count) count "; - groupByMaterialInstanceSql = ",mi.materialName "; - }else{ - queryMaterialNameSql = ""; - groupByMaterialInstanceSql = ""; + public void setExtendedColumnConfigOfForeignTousseApplicationReport( + String extendedColumnConfigOfForeignTousseApplicationReport) { + this.extendedColumnConfigOfForeignTousseApplicationReport = extendedColumnConfigOfForeignTousseApplicationReport; } - String showNoInvoieSql = null; - if(!showNoInvoie){ - showNoInvoieSql = " and tit.invoice_id is not null "; - }else{ - showNoInvoieSql = ""; + public boolean isEnableTheSizeDisplayOfTheForeignTousseApplicationReport() { + return enableTheSizeDisplayOfTheForeignTousseApplicationReport; } - String usedSql = null; - if("否".equals(used)){ - usedSql = " and tit.useRecord_id is null"; - }else if("是".equals(used)){ - usedSql = " and tit.useRecord_id is not null "; - }else{ - usedSql = ""; + public void setEnableTheSizeDisplayOfTheForeignTousseApplicationReport( + boolean enableTheSizeDisplayOfTheForeignTousseApplicationReport) { + this.enableTheSizeDisplayOfTheForeignTousseApplicationReport = enableTheSizeDisplayOfTheForeignTousseApplicationReport; } - String timeSql = null; - startDay = dateQueryAdapter.dateAdapter(startDay); - endDay = dateQueryAdapter.dateAdapter(endDay); - String timeFieldName = ""; - if("发货时间".equals(timeType)){ - timeSql = String.format("and (tit.invoiceSendTime2 is not null and tit.invoiceSendTime2 " - + "between %s and %s or tit.invoiceSendTime2 is null " - + "and tit.invoiceSendTime between %s and %s)", - startDay,endDay,startDay,endDay); - }else if("申请时间".equals(timeType)){ - timeFieldName = "i.applicationTime"; - timeSql = String.format("and (i.applicationTime between %s and %s)", startDay, endDay); - }else{ - timeFieldName = "f.receiveTime"; - timeSql = String.format("and (f.receiveTime between %s and %s)", startDay, endDay); + public boolean isEnableShowForeignTousseApplicationReportMaterials() { + return enableShowForeignTousseApplicationReportMaterials; } - boolean enableShowForeignTousseApplicationReportSummaryOfMaterials = CssdUtils.getSystemSetConfigByNameBool("enableShowForeignTousseApplicationReportSummaryOfMaterials", false); - String querySummaryOfMaterialsSql = ""; - if(enableShowForeignTousseApplicationReportSummaryOfMaterials){ - if(StringUtils.isBlank(joinMaterialInstanceSql)){ - joinMaterialInstanceSql = " left join MaterialInstance mi on mi.tousse_id=td.id "; - } - querySummaryOfMaterialsSql = ",sum(case when mi.isImplant='是' then mi.count else 0 end) implantAmount ,sum(case when mi.isImplant='是' then 0 else mi.count end) toolAmount "; + public void setEnableShowForeignTousseApplicationReportMaterials( + boolean enableShowForeignTousseApplicationReportMaterials) { + this.enableShowForeignTousseApplicationReportMaterials = enableShowForeignTousseApplicationReportMaterials; } - boolean enableTheSizeDisplayOfTheForeignTousseApplicationReport = CssdUtils.getSystemSetConfigByNameBool("enableTheSizeDisplayOfTheForeignTousseApplicationReport", false); - String sizeSql = ""; - if(enableTheSizeDisplayOfTheForeignTousseApplicationReport){ - sizeSql = String.format(",(select count(distinct cb.id) from %s cb join " - + " ClassifyBasket_RecyclingRecord cr on cb.id=cr.ClassifyBasket_ID " - + " join %s rr on rr.id=cr.RecyclingRecord_ID " - + " where rr.recyclingApplication_id=f.id and rr.recyclingTimes is null " - + " and cb.basketSize='大包') bigTousseAmount, (select count(distinct cb.id) from %s cb join " - + " ClassifyBasket_RecyclingRecord cr on cb.id=cr.ClassifyBasket_ID" - + " join %s rr on rr.id=cr.RecyclingRecord_ID " - + " where rr.recyclingApplication_id=f.id and rr.recyclingTimes is null " - + " and cb.basketSize='小包') smallTousseAmount " - , ClassifyBasket.class.getSimpleName(), - RecyclingRecord.class.getSimpleName(), - ClassifyBasket.class.getSimpleName(), - RecyclingRecord.class.getSimpleName()); + public boolean isEnableShowForeignTousseApplicationReportSummaryOfMaterials() { + return enableShowForeignTousseApplicationReportSummaryOfMaterials; } - String customTimePeriodSql = ""; - if(DatabaseUtil.isPoIdValid(customTimePeriodId)){ - CustomTimePeriod customTimePeriod = (CustomTimePeriod)objectDao.getById(CustomTimePeriod.class.getSimpleName(), customTimePeriodId); + public void setEnableShowForeignTousseApplicationReportSummaryOfMaterials( + boolean enableShowForeignTousseApplicationReportSummaryOfMaterials) { + this.enableShowForeignTousseApplicationReportSummaryOfMaterials = enableShowForeignTousseApplicationReportSummaryOfMaterials; + } + + public String getQuerySql() { + return querySql; + } + public void setQuerySql(String querySql) { + this.querySql = querySql; + } + /** + * 初始化信息 + * @param timeType 时间类型 + * @param startDay 开始时间(仅年月日,如yyyy-MM-dd) + * @param endDay 结束时间(仅年月日,如yyyy-MM-dd) + * @param goodsName 外来器械包名称 + * @param deptName 科室名称 + * @param patientName 病人姓名 + * @param deliverStatus 发货状态 + * @param companyName 供应商 + * @param departOfPatient 病人所属科室 + * @param customColumnName1 自定义列名1(外来器械申请单统计报表的病人所属科室列配置的fieldName 即departColumnConfigOfforeignTousseApplicationReport配置的fieldName) + * @param materialId 材料定义(用于查询包中包含了这种材料的包) + * @param showNoInvoie 显示未发货的数据 + * @param tousseDefinitionName 物品名称 + * @param customTimePeriodId 自定义时间段id + * @return + */ + public void initializationInformation(String timeType, String startDay, String endDay, String goodsName, String deptName , String patientName , String deliverStatus, String companyName, String departOfPatient, boolean noDisplayTerminatedIP, String customColumnName1, Map parametMap, Long materialId, boolean showNoInvoie, String tousseDefinitionName, String used, Long customTimePeriodId){ + this.extendedColumnConfigOfForeignTousseApplicationReport = CssdUtils.getSystemSetConfigByName("extendedColumnConfigOfForeignTousseApplicationReport"); + this.enableShowForeignTousseApplicationReportMaterials = CssdUtils.getSystemSetConfigByNameBool("enableShowForeignTousseApplicationReportMaterials", false); + this.enableShowForeignTousseApplicationReportSummaryOfMaterials = CssdUtils.getSystemSetConfigByNameBool("enableShowForeignTousseApplicationReportSummaryOfMaterials", false); + this.enableTheSizeDisplayOfTheForeignTousseApplicationReport = CssdUtils.getSystemSetConfigByNameBool("enableTheSizeDisplayOfTheForeignTousseApplicationReport", false); + //额外列的处理 + String queryExtendedColumnSQL = ""; + if(StringUtils.isNotBlank(this.extendedColumnConfigOfForeignTousseApplicationReport)){ + JSONArray arr = JSONArray.fromObject(this.extendedColumnConfigOfForeignTousseApplicationReport); + for (int i = 0; i < arr.size(); i++) { + String extendedColumnName = arr.get(i).toString().trim(); + if(StringUtils.isBlank(extendedColumnName)){ + continue; + } + if("receiveman".equals(extendedColumnName.toLowerCase())){ + queryExtendedColumnSQL += ",f.receiveMan "; + this.queryReceiveMan = true; + parametMap.put("queryReceiveMan", true); + }else if("returnman".equals(extendedColumnName.toLowerCase())){ + queryExtendedColumnSQL += ",f.returnMan "; + this.queryReturnMan = true; + parametMap.put("queryReturnMan", true); + }else if("returntime".equals(extendedColumnName.toLowerCase())){ + queryExtendedColumnSQL += ",f.returnTime "; + this.queryReturnTime = true; + parametMap.put("queryReturnTime", true); + } + } + } + if (StringUtils.isNotBlank(endDay)) { + endDay += ":59"; + } + if (StringUtils.isNotBlank(startDay)) { + startDay += ":00"; + } + String customColumnName1Sql = null; + if(StringUtils.isNotBlank(customColumnName1)){ + customColumnName1Sql = ",f."+customColumnName1; + }else{ + customColumnName1 = "ascriptionDepartment"; + customColumnName1Sql = ",f.ascriptionDepartment"; + } + StringBuilder sqlBuilder = new StringBuilder(); + String queryMaterialNameSql = null; + String joinMaterialInstanceSql = null; + String groupByMaterialInstanceSql = null; + + String materialInstanceSql = null; + if(DatabaseUtil.isPoIdValid(materialId) || this.enableShowForeignTousseApplicationReportMaterials){ + joinMaterialInstanceSql = " left join MaterialInstance mi on mi.tousse_id=td.id "; + }else{ + joinMaterialInstanceSql = ""; + } + if(DatabaseUtil.isPoIdValid(materialId)){ + materialInstanceSql = " and td.id in(select tousse_id from MaterialInstance where materialDefinition_id="+ materialId +") "; + }else{ + materialInstanceSql = ""; + } + String tousseSql = null; + if(StringUtils.isNotBlank(tousseDefinitionName)){ + tousseSql = " and td.name='" + tousseDefinitionName + "' "; + }else{ + tousseSql = ""; + } + if(this.enableShowForeignTousseApplicationReportMaterials){//查询材料明细 + queryMaterialNameSql = ",mi.materialName,sum(mi.count) count "; + groupByMaterialInstanceSql = ",mi.materialName "; + }else{ + queryMaterialNameSql = ""; + groupByMaterialInstanceSql = ""; + } + String showNoInvoieSql = null; + if(!showNoInvoie){ + showNoInvoieSql = " and tit.invoice_id is not null "; + }else{ + showNoInvoieSql = ""; + } + String usedSql = null; + if("否".equals(used)){ + usedSql = " and tit.useRecord_id is null"; + }else if("是".equals(used)){ + usedSql = " and tit.useRecord_id is not null "; + }else{ + usedSql = ""; + } + String timeSql = null; + startDay = dateQueryAdapter.dateAdapter(startDay); + endDay = dateQueryAdapter.dateAdapter(endDay); + String timeFieldName = ""; if("发货时间".equals(timeType)){ - String startTime = customTimePeriod.getStartTime(); - String endTime = customTimePeriod.getEndTime(); - if(dbConnection.isSqlServer()){ - String[] startTimeArr = startTime.split(":"); - String startTimeHour = startTimeArr[0]; - String startTimeMinute = startTimeArr[1]; - String[] endTimeArr = endTime.split(":"); - String endTimeHour = endTimeArr[0]; - String endTimeMinute = endTimeArr[1]; - customTimePeriodSql = " and (tit.invoiceSendTime2 is not null and (datepart(hour,tit.invoiceSendTime2)="+ startTimeHour +" and datepart(MINUTE,tit.invoiceSendTime2)>="+ startTimeMinute - + " or datepart(hour,tit.invoiceSendTime2)="+ endTimeHour +" and datepart(MINUTE,tit.invoiceSendTime2)<= " + endTimeMinute - + " or datepart(hour,tit.invoiceSendTime2)>"+ startTimeHour +" and datepart(hour,tit.invoiceSendTime2)< " + endTimeHour + " ) or tit.invoiceSendTime2 is null and " - + " (datepart(hour,tit.invoiceSendTime)="+ startTimeHour +" and datepart(MINUTE,tit.invoiceSendTime)>="+ startTimeMinute - + " or datepart(hour,tit.invoiceSendTime)="+ endTimeHour +" and datepart(MINUTE,tit.invoiceSendTime)<= " + endTimeMinute - + " or datepart(hour,tit.invoiceSendTime)>"+ startTimeHour +" and datepart(hour,tit.invoiceSendTime)< " + endTimeHour + ")) "; - }else if(dbConnection.isOracle()){ - customTimePeriodSql = " and (tit.invoiceSendTime2 is not null and TO_CHAR(tit.invoiceSendTime2, 'hh24:mi:ss') BETWEEN '" - + startTime +":00' AND '" - + endTime +":00' or tit.invoiceSendTime2 is null and tit.invoiceSendTime BETWEEN '" - + startTime +":00' AND '" - + endTime + ":00')"; + timeSql = String.format("and (tit.invoiceSendTime2 is not null and tit.invoiceSendTime2 " + + "between %s and %s or tit.invoiceSendTime2 is null " + + "and tit.invoiceSendTime between %s and %s)", + startDay,endDay,startDay,endDay); + }else if("申请时间".equals(timeType)){ + timeFieldName = "i.applicationTime"; + timeSql = String.format("and (i.applicationTime between %s and %s)", startDay, endDay); + }else{ + timeFieldName = "f.receiveTime"; + timeSql = String.format("and (f.receiveTime between %s and %s)", startDay, endDay); + } + + String querySummaryOfMaterialsSql = ""; + if(this.enableShowForeignTousseApplicationReportSummaryOfMaterials){ + if(StringUtils.isBlank(joinMaterialInstanceSql)){ + joinMaterialInstanceSql = " left join MaterialInstance mi on mi.tousse_id=td.id "; + } + querySummaryOfMaterialsSql = ",sum(case when mi.isImplant='是' then mi.count else 0 end) implantAmount ,sum(case when mi.isImplant='是' then 0 else mi.count end) toolAmount "; + } + + String sizeSql = ""; + if(this.enableTheSizeDisplayOfTheForeignTousseApplicationReport){ + sizeSql = String.format(",(select count(distinct cb.id) from %s cb join " + + " ClassifyBasket_RecyclingRecord cr on cb.id=cr.ClassifyBasket_ID " + + " join %s rr on rr.id=cr.RecyclingRecord_ID " + + " where rr.recyclingApplication_id=f.id and rr.recyclingTimes is null " + + " and cb.basketSize='大包') bigTousseAmount, (select count(distinct cb.id) from %s cb join " + + " ClassifyBasket_RecyclingRecord cr on cb.id=cr.ClassifyBasket_ID" + + " join %s rr on rr.id=cr.RecyclingRecord_ID " + + " where rr.recyclingApplication_id=f.id and rr.recyclingTimes is null " + + " and cb.basketSize='小包') smallTousseAmount " + , ClassifyBasket.class.getSimpleName(), + RecyclingRecord.class.getSimpleName(), + ClassifyBasket.class.getSimpleName(), + RecyclingRecord.class.getSimpleName()); + } + String customTimePeriodSql = ""; + if(DatabaseUtil.isPoIdValid(customTimePeriodId)){ + CustomTimePeriod customTimePeriod = (CustomTimePeriod)objectDao.getById(CustomTimePeriod.class.getSimpleName(), customTimePeriodId); + if("发货时间".equals(timeType)){ + String startTime = customTimePeriod.getStartTime(); + String endTime = customTimePeriod.getEndTime(); + if(dbConnection.isSqlServer()){ + String[] startTimeArr = startTime.split(":"); + String startTimeHour = startTimeArr[0]; + String startTimeMinute = startTimeArr[1]; + String[] endTimeArr = endTime.split(":"); + String endTimeHour = endTimeArr[0]; + String endTimeMinute = endTimeArr[1]; + customTimePeriodSql = " and (tit.invoiceSendTime2 is not null and (datepart(hour,tit.invoiceSendTime2)="+ startTimeHour +" and datepart(MINUTE,tit.invoiceSendTime2)>="+ startTimeMinute + + " or datepart(hour,tit.invoiceSendTime2)="+ endTimeHour +" and datepart(MINUTE,tit.invoiceSendTime2)<= " + endTimeMinute + + " or datepart(hour,tit.invoiceSendTime2)>"+ startTimeHour +" and datepart(hour,tit.invoiceSendTime2)< " + endTimeHour + " ) or tit.invoiceSendTime2 is null and " + + " (datepart(hour,tit.invoiceSendTime)="+ startTimeHour +" and datepart(MINUTE,tit.invoiceSendTime)>="+ startTimeMinute + + " or datepart(hour,tit.invoiceSendTime)="+ endTimeHour +" and datepart(MINUTE,tit.invoiceSendTime)<= " + endTimeMinute + + " or datepart(hour,tit.invoiceSendTime)>"+ startTimeHour +" and datepart(hour,tit.invoiceSendTime)< " + endTimeHour + ")) "; + }else if(dbConnection.isOracle()){ + customTimePeriodSql = " and (tit.invoiceSendTime2 is not null and TO_CHAR(tit.invoiceSendTime2, 'hh24:mi:ss') BETWEEN '" + + startTime +":00' AND '" + + endTime +":00' or tit.invoiceSendTime2 is null and tit.invoiceSendTime BETWEEN '" + + startTime +":00' AND '" + + endTime + ":00')"; + }else{ + customTimePeriodSql = ""; + } }else{ - customTimePeriodSql = ""; + customTimePeriodSql = getTimeFieldQueryStatement(dbConnection, customTimePeriod.getStartTime(), customTimePeriod.getEndTime(), timeFieldName); } + } + String sql = String + .format(" select * from (select tit.doctorName tiDoctorName %s , i.applicationtime,case when tit.invoiceSendTime2 is not null then tit.invoiceSendTime2 else tit.invoiceSendTime end invoiceSendTime,f.receiveTime,tit.tousseName,tit.price,f.id,ti.supplierName,i.applicant,i.remark,f.patient,f.surgery,f.hospitalNumber,i.depart,i.deliverStatus %s,f.doctor,f.processType,count(distinct tit.id) amount,f.bedNumber,tit.useRecord_id %s %s %s from %s f join %s i on f.id=i.id join %s ti on ti.recyclingApplication_ID=i.id join %s tit on tit.foreignTousseApp_id=f.id join TousseDefinition td on td.id = tit.tousseDefinition_id %s where (tit.id is null or td.parentID is null and ti.tousseDefinitionId=tit.tousseDefinition_id or ti.tousseDefinitionId=td.parentID) %s %s %s %s %s ", + queryExtendedColumnSQL, + customColumnName1Sql, + queryMaterialNameSql, + querySummaryOfMaterialsSql, + sizeSql, + ForeignTousseApplication.class.getSimpleName(), + InvoicePlan.class.getSimpleName(), + TousseItem.class.getSimpleName(), + TousseInstance.class.getSimpleName(), + joinMaterialInstanceSql, + materialInstanceSql, + showNoInvoieSql, + tousseSql, + timeSql, + usedSql); + sqlBuilder.append(sql); + String goodsNameSql = ""; + if (StringUtils.isNotBlank(goodsName)) { + goodsNameSql = String + .format(" and f.id=(select t.recyclingApplication_ID from %s t where t.recyclingApplication_ID=f.id and t.tousseName = '%s')",TousseItem.class.getSimpleName(),goodsName); + sqlBuilder.append(goodsNameSql); + } + String deptNameSql = ""; + if (StringUtils.isNotBlank(deptName)) { + deptNameSql = " and i.depart = '" + deptName + "' "; + sqlBuilder.append(deptNameSql); + } + String patientNameSql = ""; + if (StringUtils.isNotBlank(patientName)) { + patientNameSql = " and f.patient = '" + patientName + "' "; + sqlBuilder.append(patientNameSql); + } + String deliverStatusSql = ""; + if (StringUtils.isNotBlank(deliverStatus)) { + deliverStatusSql = " and i.deliverStatus = '" + deliverStatus + "' "; + sqlBuilder.append(deliverStatusSql); + } + String departOfPatientSql = ""; + if(StringUtils.isNotBlank(departOfPatient)){ + departOfPatientSql = " and f.ascriptionDepartment ='" + departOfPatient + "' "; + sqlBuilder.append(departOfPatientSql); + } + String filterConpanyNameSql = ""; + if(StringUtils.isNotBlank(companyName)){ + filterConpanyNameSql = " and ti.SupplierName='" + companyName+ "' "; + sqlBuilder.append(filterConpanyNameSql); + } + String noDisplayTerminatedIPSql = ""; + if(noDisplayTerminatedIP){ + noDisplayTerminatedIPSql = " and (i.deliverStatus<>'"+ InvoicePlan.STATUS_END +"' or i.deliverStatus is null) "; + sqlBuilder.append(noDisplayTerminatedIPSql); + } + sqlBuilder.append(customTimePeriodSql); + sqlBuilder.append(" group by tit.doctorName,tit.invoiceSendTime,tit.invoiceSendTime2,f.receiveTime,tit.tousseName,tit.price,f.id,ti.supplierName,i.applicant,i.remark,f.patient,f.surgery,f.hospitalNumber,i.depart,i.deliverStatus,f.bedNumber,tit.useRecord_id,i.applicationtime,tit.foreignTousseApp_id "); + sqlBuilder.append(customColumnName1Sql); + sqlBuilder.append(queryExtendedColumnSQL); + sqlBuilder.append(groupByMaterialInstanceSql); + sqlBuilder.append(",f.doctor,f.processType "); + if(!"发货时间".equals(timeType) && !"是".equals(used) && showNoInvoie){//此处的数据是装配的数据,不存在发货时间 不存在已使用 + sqlBuilder.append(" union all select null tiDoctorName "+ queryExtendedColumnSQL +",i.applicationtime,null invoiceSendTime,f.receiveTime " + + " ,td.name tousseName,td.price,f.id,ti.supplierName,i.applicant,i.remark,f.patient,f.surgery,f.hospitalNumber,i.depart,i.deliverStatus " + + customColumnName1Sql + + ",f.doctor,f.processType,sum(ti.amount) amount,f.bedNumber,null useRecord_id " + + queryMaterialNameSql + + querySummaryOfMaterialsSql + + sizeSql + + " from "+ + ForeignTousseApplication.class.getSimpleName() + +" f join "+ + InvoicePlan.class.getSimpleName() + +" i on f.id=i.id join "+ + TousseItem.class.getSimpleName() + +" ti on ti.recyclingApplication_ID=i.id join "+ + TousseDefinition.class.getSimpleName() + +" td on td.id = ti.tousseDefinitionId " + + joinMaterialInstanceSql + + " where not exists (select id from "+ + TousseInstance.class.getSimpleName() + +" where foreignTousseApp_id=f.id) " + + timeSql + + tousseSql + + deptNameSql + + goodsNameSql + + patientNameSql + + showNoInvoieSql + + deliverStatusSql + + departOfPatientSql + + materialInstanceSql + + customTimePeriodSql + + filterConpanyNameSql + + noDisplayTerminatedIPSql + + " group by f.receiveTime"+ queryExtendedColumnSQL +",td.name,td.price,f.id,ti.supplierName " + + " ,i.applicant,i.remark,f.patient,f.surgery,f.hospitalNumber,i.depart,i.deliverStatus,f.bedNumber,i.applicationtime,f.id " + + customColumnName1Sql + + groupByMaterialInstanceSql + + ",f.doctor,f.processType,ti.id " + + " ) t order by t.id asc "); }else{ - customTimePeriodSql = getTimeFieldQueryStatement(dbConnection, customTimePeriod.getStartTime(), customTimePeriod.getEndTime(), timeFieldName); + sqlBuilder.append(") t order by t.id asc "); } + this.querySql = sqlBuilder.toString(); } - String sql = String - .format(" select * from (select tit.doctorName tiDoctorName, i.applicationtime,case when tit.invoiceSendTime2 is not null then tit.invoiceSendTime2 else tit.invoiceSendTime end invoiceSendTime,f.receiveTime,tit.tousseName,tit.price,f.id,ti.supplierName,i.applicant,i.remark,f.patient,f.surgery,f.hospitalNumber,i.depart,i.deliverStatus %s,f.doctor,f.processType,count(distinct tit.id) amount,f.bedNumber,tit.useRecord_id %s %s %s from %s f join %s i on f.id=i.id join %s ti on ti.recyclingApplication_ID=i.id join %s tit on tit.foreignTousseApp_id=f.id join TousseDefinition td on td.id = tit.tousseDefinition_id %s where (tit.id is null or td.parentID is null and ti.tousseDefinitionId=tit.tousseDefinition_id or ti.tousseDefinitionId=td.parentID) %s %s %s %s %s ", - customColumnName1Sql, - queryMaterialNameSql, - querySummaryOfMaterialsSql, - sizeSql, - ForeignTousseApplication.class.getSimpleName(), - InvoicePlan.class.getSimpleName(), - TousseItem.class.getSimpleName(), - TousseInstance.class.getSimpleName(), - joinMaterialInstanceSql, - materialInstanceSql, - showNoInvoieSql, - tousseSql, - timeSql, - usedSql); - sqlBuilder.append(sql); - String goodsNameSql = ""; - if (StringUtils.isNotBlank(goodsName)) { - goodsNameSql = String - .format(" and f.id=(select t.recyclingApplication_ID from %s t where t.recyclingApplication_ID=f.id and t.tousseName = '%s')",TousseItem.class.getSimpleName(),goodsName); - sqlBuilder.append(goodsNameSql); - } - String deptNameSql = ""; - if (StringUtils.isNotBlank(deptName)) { - deptNameSql = " and i.depart = '" + deptName + "' "; - sqlBuilder.append(deptNameSql); - } - String patientNameSql = ""; - if (StringUtils.isNotBlank(patientName)) { - patientNameSql = " and f.patient = '" + patientName + "' "; - sqlBuilder.append(patientNameSql); - } - String deliverStatusSql = ""; - if (StringUtils.isNotBlank(deliverStatus)) { - deliverStatusSql = " and i.deliverStatus = '" + deliverStatus + "' "; - sqlBuilder.append(deliverStatusSql); - } - String departOfPatientSql = ""; - if(StringUtils.isNotBlank(departOfPatient)){ - departOfPatientSql = " and f.ascriptionDepartment ='" + departOfPatient + "' "; - sqlBuilder.append(departOfPatientSql); - } - String filterConpanyNameSql = ""; - if(filterConpanyName){ - filterConpanyNameSql = " and ti.SupplierName='" + companyName+ "' "; - sqlBuilder.append(filterConpanyNameSql); - } - String noDisplayTerminatedIPSql = ""; - if(noDisplayTerminatedIP){ - noDisplayTerminatedIPSql = " and (i.deliverStatus<>'"+ InvoicePlan.STATUS_END +"' or i.deliverStatus is null) "; - sqlBuilder.append(noDisplayTerminatedIPSql); - } - sqlBuilder.append(customTimePeriodSql); - sqlBuilder.append(" group by tit.doctorName,tit.invoiceSendTime,tit.invoiceSendTime2,f.receiveTime,tit.tousseName,tit.price,f.id,ti.supplierName,i.applicant,i.remark,f.patient,f.surgery,f.hospitalNumber,i.depart,i.deliverStatus,f.bedNumber,tit.useRecord_id,i.applicationtime,tit.foreignTousseApp_id "); - sqlBuilder.append(customColumnName1Sql); - sqlBuilder.append(groupByMaterialInstanceSql); - sqlBuilder.append(",f.doctor,f.processType "); - if(!"发货时间".equals(timeType) && !"是".equals(used) && showNoInvoie){//此处的数据是装配的数据,不存在发货时间 不存在已使用 - sqlBuilder.append(" union all select null tiDoctorName,i.applicationtime,null invoiceSendTime,f.receiveTime " - + " ,td.name tousseName,td.price,f.id,ti.supplierName,i.applicant,i.remark,f.patient,f.surgery,f.hospitalNumber,i.depart,i.deliverStatus " - + customColumnName1Sql - + ",f.doctor,f.processType,sum(ti.amount) amount,f.bedNumber,null useRecord_id " - + queryMaterialNameSql - + querySummaryOfMaterialsSql - + sizeSql - + " from "+ - ForeignTousseApplication.class.getSimpleName() - +" f join "+ - InvoicePlan.class.getSimpleName() - +" i on f.id=i.id join "+ - TousseItem.class.getSimpleName() - +" ti on ti.recyclingApplication_ID=i.id join "+ - TousseDefinition.class.getSimpleName() - +" td on td.id = ti.tousseDefinitionId " - + joinMaterialInstanceSql - + " where not exists (select id from "+ - TousseInstance.class.getSimpleName() - +" where foreignTousseApp_id=f.id) " - + timeSql - + tousseSql - + deptNameSql - + goodsNameSql - + patientNameSql - + showNoInvoieSql - + deliverStatusSql - + departOfPatientSql - + materialInstanceSql - + customTimePeriodSql - + filterConpanyNameSql - + noDisplayTerminatedIPSql - + " group by f.receiveTime,td.name,td.price,f.id,ti.supplierName " - + " ,i.applicant,i.remark,f.patient,f.surgery,f.hospitalNumber,i.depart,i.deliverStatus,f.bedNumber,i.applicationtime,f.id " - + customColumnName1Sql - + groupByMaterialInstanceSql - + ",f.doctor,f.processType,ti.id " - + " ) t order by t.id asc "); - }else{ - sqlBuilder.append(") t order by t.id asc "); - } - + } + @Override + public List getForeignTousseApplicationReport( + String timeType, String startDay, String endDay, String goodsName, String deptName , String patientName , String deliverStatus, String companyName, String departOfPatient, boolean noDisplayTerminatedIP, String customColumnName1, Map parametMap, Long materialId, boolean showNoInvoie, String tousseDefinitionName, String used, Long customTimePeriodId) { + + //构建上下文并赋值一些属性 + ForeignTousseApplicationReportVo reportVo = new ForeignTousseApplicationReportVo(); + reportVo.initializationInformation(timeType, startDay, endDay, goodsName, deptName , patientName , deliverStatus, companyName, departOfPatient, noDisplayTerminatedIP, customColumnName1, parametMap, materialId, showNoInvoie, tousseDefinitionName, used, customTimePeriodId); + + List list = new LinkedList(); ResultSet rs = null; try { - rs = objectDao.executeSql(sqlBuilder.toString()); + System.out.println(); + rs = objectDao.executeSql(reportVo.getQuerySql()); Map voMap = new LinkedHashMap(); SimpleDateFormat sdf = new SimpleDateFormat(Constants.DATEFORMAT_YYYYMMDDHHMM); Map materialCountMap = new HashMap(); @@ -15117,7 +15258,7 @@ vo = voMap.get(id); }else{ vo = new ForeignTousseApplicationVO(); - if(enableTheSizeDisplayOfTheForeignTousseApplicationReport){ + if(reportVo.isEnableTheSizeDisplayOfTheForeignTousseApplicationReport()){ vo.setBigTousseAmount(rs.getInt("bigTousseAmount")); vo.setSmallTousseAmount(rs.getInt("smallTousseAmount")); } @@ -15134,6 +15275,18 @@ if(applicationtime != null){ vo.setApplicationTimeStr(Constants.SIMPLEDATEFORMAT_YYYYMMDDHHMM.format(applicationtime)); } + if(reportVo.isQueryReceiveMan()){ + vo.setReceiveMan(StringTools.defaultIfBlank(rs.getString("ReceiveMan"),"")); + } + if(reportVo.isQueryReturnMan()){ + vo.setReturnMan(StringTools.defaultIfBlank(rs.getString("ReturnMan"),"")); + } + if(reportVo.isQueryReturnTime()){ + Date returnTime = rs.getTimestamp("returnTime"); + if(returnTime != null){ + vo.setReturnTimeStr(Constants.SIMPLEDATEFORMAT_YYYYMMDDHHMM.format(returnTime)); + } + } vo.setApplicant(StringTools.defaultIfBlank(rs.getString("applicant"),"")); vo.setRemark(StringTools.defaultIfBlank(rs.getString("remark"),"")); vo.setPatient(StringTools.defaultIfBlank(rs.getString("patient"),"")); @@ -15167,7 +15320,7 @@ String supplierName = rs.getString("supplierName"); Long useRecord_id = rs.getLong("useRecord_id"); int materialCount = 0; - if(!enableShowForeignTousseApplicationReportMaterials){ + if(!reportVo.isEnableShowForeignTousseApplicationReportMaterials()){ tousseItemVO = new TousseItemVO(); }else { materialCount = rs.getInt("count"); @@ -15183,7 +15336,7 @@ tousseItemVOMap.put(key, tousseItemVO); } } - if(enableShowForeignTousseApplicationReportMaterials){ + if(reportVo.isEnableShowForeignTousseApplicationReportMaterials()){ if(materialCount > 0){ String materialName = rs.getString("materialName"); @@ -15202,7 +15355,7 @@ } } } - if(enableShowForeignTousseApplicationReportSummaryOfMaterials){ + if(reportVo.isEnableShowForeignTousseApplicationReportSummaryOfMaterials()){ tousseItemVO.setToolAmount(MathTools.add(tousseItemVO.getToolAmount(), rs.getInt("toolAmount")).intValue()); tousseItemVO.setImplantAmount(MathTools.add(tousseItemVO.getImplantAmount(), rs.getInt("implantAmount")).intValue()); } @@ -15235,7 +15388,7 @@ tousseItems.add(new TousseItemVO()); } } - if(enableShowForeignTousseApplicationReportMaterials){ + if(reportVo.isEnableShowForeignTousseApplicationReportMaterials()){ String totalMaterialDetails = null; for (Entry entry : materialCountMap.entrySet()) { String materialDetails = entry.getKey(); Index: ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/javabeansource/ForeignTousseApplicationVO.java =================================================================== diff -u -r33252 -r35278 --- ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/javabeansource/ForeignTousseApplicationVO.java (.../ForeignTousseApplicationVO.java) (revision 33252) +++ ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/javabeansource/ForeignTousseApplicationVO.java (.../ForeignTousseApplicationVO.java) (revision 35278) @@ -119,7 +119,10 @@ private String returnMan;// 归还人 private Date returnTime;// 归还时间 - + /** + * 归还时间字符串 + */ + private String returnTimeStr; private String packageStatus;// 包状态 private String readers = ""; @@ -752,5 +755,13 @@ public void setSmallTousseAmount(Integer smallTousseAmount) { this.smallTousseAmount = smallTousseAmount; } + + public String getReturnTimeStr() { + return returnTimeStr; + } + + public void setReturnTimeStr(String returnTimeStr) { + this.returnTimeStr = returnTimeStr; + } }