Index: ssts-web/src/main/webapp/WEB-INF/spring/applicationContext-service.xml
===================================================================
diff -u -r35792 -r35793
--- ssts-web/src/main/webapp/WEB-INF/spring/applicationContext-service.xml (.../applicationContext-service.xml) (revision 35792)
+++ ssts-web/src/main/webapp/WEB-INF/spring/applicationContext-service.xml (.../applicationContext-service.xml) (revision 35793)
@@ -784,4 +784,6 @@
+
+
\ No newline at end of file
Index: ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/util/RealTimeBulletinBoardWorkloadHelper.java
===================================================================
diff -u
--- ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/util/RealTimeBulletinBoardWorkloadHelper.java (revision 0)
+++ ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/util/RealTimeBulletinBoardWorkloadHelper.java (revision 35793)
@@ -0,0 +1,696 @@
+package com.forgon.disinfectsystem.jasperreports.util;
+
+import java.sql.ResultSet;
+import java.text.SimpleDateFormat;
+import java.util.Calendar;
+import java.util.Date;
+
+import net.sf.json.JSONObject;
+
+import org.apache.commons.lang.StringUtils;
+import org.springframework.beans.factory.annotation.Autowired;
+
+import com.forgon.directory.model.BarcodeDevice;
+import com.forgon.disinfectsystem.basedatamanager.supplyroomconfig.service.SupplyRoomConfigManager;
+import com.forgon.disinfectsystem.entity.assestmanagement.DisposableGoods;
+import com.forgon.disinfectsystem.entity.basedatamanager.container.Container;
+import com.forgon.disinfectsystem.entity.basedatamanager.supplyroomconfig.SupplyRoomConfig;
+import com.forgon.disinfectsystem.entity.basedatamanager.toussedefinition.TousseDefinition;
+import com.forgon.disinfectsystem.entity.basedatamanager.toussedefinition.TousseInstance;
+import com.forgon.disinfectsystem.entity.becleanitem.ClassifiedItem;
+import com.forgon.disinfectsystem.entity.becleanitem.ClassifyBasket;
+import com.forgon.disinfectsystem.entity.invoicemanager.Invoice;
+import com.forgon.disinfectsystem.entity.invoicemanager.InvoicePlan;
+import com.forgon.disinfectsystem.entity.packing.PackingTask;
+import com.forgon.disinfectsystem.entity.packing.ReviewedBasket;
+import com.forgon.disinfectsystem.entity.recyclingrecord.RecyclingRecord;
+import com.forgon.disinfectsystem.entity.sterilizationmanager.proxydisinfection.ProxyDisinfection;
+import com.forgon.disinfectsystem.entity.sterilizationmanager.sterilizationrecord.SterilizationRecord;
+import com.forgon.disinfectsystem.entity.tousseitem.TousseItem;
+import com.forgon.disinfectsystem.reportforms.vo.ReportQueryParams;
+import com.forgon.tools.db.DatabaseUtil;
+import com.forgon.tools.util.SqlUtils;
+/**
+ * 数据看板工作量
+ *
+ */
+public class RealTimeBulletinBoardWorkloadHelper extends ReportHelper{
+ @Autowired
+ protected SupplyRoomConfigManager supplyRoomConfigManager;
+ public JSONObject getRealTimeBulletinBoardWorkloadData() {
+ //获取当天开始时间
+ SupplyRoomConfig systemParamsObj = supplyRoomConfigManager.getSystemParamsObj();
+ SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
+ Date today = new Date();
+ String dateStr = sdf.format(today);
+ String startDate = "";
+ if(systemParamsObj != null && StringUtils.isNotBlank(systemParamsObj.getDayStartTime())){
+ startDate += dateStr + " " + systemParamsObj.getDayStartTime() + ":00";
+ }else{
+ startDate += dateStr + " 00:00:00";
+ }
+ String endDate = dateStr + " 23:59:59";
+ String betweenSql = " between "+ dateQueryAdapter.dateAdapter(startDate) + " and "
+ + dateQueryAdapter.dateAdapter(endDate);
+ String handleDepartCoding = supplyRoomConfigManager.getFirstSupplyRoomConfig().getOrgUnitCoding();
+ String tousseType = "外来器械包,外来器械拆分小包";
+ String tousseTypesql = SqlUtils.getWhereSqlByfilterFieldAndStringValueAndSeparator("td.tousseType", tousseType, ",");
+ ReportQueryParams foreignParams = new ReportQueryParams();
+ foreignParams.haveCombo = false;
+ foreignParams.betweenSql = betweenSql;
+ foreignParams.querySupplyRoom = handleDepartCoding;
+ foreignParams.tousseTypes = tousseType;
+ foreignParams.tousseTypeAndPackageSizeSql = tousseTypesql;
+
+ ReportQueryParams params = new ReportQueryParams();
+ params.haveCombo = false;
+ params.betweenSql = betweenSql;
+ params.querySupplyRoom = handleDepartCoding;
+ String sql = "";
+ String toBeBetweenSql = "";
+ Integer dashboardsQueryCycle = null;
+ if(systemParamsObj.getDashboardsQueryCycle() == null){
+ dashboardsQueryCycle = 7;
+ }else{
+ dashboardsQueryCycle = systemParamsObj.getDashboardsQueryCycle();
+ }
+ if(dashboardsQueryCycle != 0){
+ Calendar calendar = Calendar.getInstance();
+ calendar.setTime(today);
+ calendar.add(Calendar.DAY_OF_MONTH, -dashboardsQueryCycle);
+ String toBeStartDate = sdf.format(calendar.getTime()) + " 00:00:00";
+ String toBeEndDate = sdf.format(today) + " 23:59:59";
+ toBeBetweenSql = " between "+ dateQueryAdapter.dateAdapter(toBeStartDate) + " and "
+ + dateQueryAdapter.dateAdapter(toBeEndDate);
+ }
+ //外来器械申请数量
+ String foreignApplyAmountSql = String.format("select 'foreignApplyAmount' type,sum(amount) amount,0 urgentAmount from ("
+ +dataIndex.getWorkAmountByPackageSQL("申请数量", foreignParams)
+ + ") tl");
+ sql += foreignApplyAmountSql;
+ //申请数量
+ String applyAmountSql = String.format("select 'applyAmount' type,sum(amount) amount,0 urgentAmount from ("
+ +dataIndex.getWorkAmountByPackageSQL("申请数量", params)
+ + ") tl");
+ sql += " union all " + applyAmountSql;
+ //外来器械当天待回收的包数量
+ String foreignToBeRecycledAmountSql = "select 'foreignToBeRecycledAmount' type,sum(amount) amount,0 urgentAmount from ( "
+ + getToBeRecycledAmountSql(handleDepartCoding, tousseTypesql, toBeBetweenSql, true) + ") tb ";
+ sql += " union all " + foreignToBeRecycledAmountSql;
+ //外来器械当天已回收包数量
+ foreignParams.extraQuery = " and rr.recyclingTimes is null ";
+ String foreignRecycledAmountSql = String.format("select 'foreignRecycledAmount' type,sum(amount) amount,0 urgentAmount from ("
+ +dataIndex.getWorkAmountByPackageSQL("回收数量", foreignParams)
+ + ") tl");
+ sql += " union all " + foreignRecycledAmountSql;
+ foreignParams.extraQuery = "";
+ //当天待回收的包数量
+ String toBeRecycledAmountSql = "select 'toBeRecycledAmount' type,sum(amount) amount,0 urgentAmount from ( "
+ + getToBeRecycledAmountSql(handleDepartCoding, "", toBeBetweenSql, false) + ") tb ";
+ sql += " union all " + toBeRecycledAmountSql;
+ //当天已回收包数量
+ String recycledAmountSql = String.format("select 'recycledAmount' type,sum(amount) amount,0 urgentAmount from ("
+ +dataIndex.getWorkAmountByPackageSQL("回收数量", params)
+ + ") tl");
+ sql += " union all " + recycledAmountSql;
+ //当天已清点器械总包数
+ String inventoryEdAmountSql = String.format("select 'inventoryEdAmount' type,sum(amount) amount,0 urgentAmount from ("
+ +dataIndex.getWorkAmountByPackageSQL("清点数量", params)
+ + ") tl ");
+ sql += " union all " + inventoryEdAmountSql;
+ //外来器械当天待清洗器械总包数
+ String foreignToBeWashAmountSql = " select 'foreignTobeWashAmount' type,sum(amount) amount,0 urgentAmount from ( "
+ + getToBeWashAmountSql(handleDepartCoding, tousseTypesql, toBeBetweenSql, true) + ") tb ";
+ sql += " union all " + foreignToBeWashAmountSql;
+ //外来器械当天已清洗器械总包数
+ foreignParams.extraQuery = " and ci.isSencondWashForForeignTousse=0 ";
+ String foreignWashedAmountSql = String.format("select 'foreignWashedAmount' type,sum(amount) amount,0 urgentAmount from ("
+ +dataIndex.getWorkAmountByPackageSQL("清洗数量", foreignParams)
+ + ") tl ");
+ sql += " union all " + foreignWashedAmountSql;
+ foreignParams.extraQuery = "";
+ //当天待清洗器械总包数
+ String toBeWashAmountSql = " select 'tobeWashAmount' type,sum(amount) amount,0 urgentAmount from ( "
+ + getToBeWashAmountSql(handleDepartCoding, "", toBeBetweenSql, false) + ") tb ";
+ sql += " union all " + toBeWashAmountSql;
+ //当天已清洗器械总包数
+ String washedAmountSql = String.format("select 'washedAmount' type,sum(amount) amount,0 urgentAmount from ("
+ +dataIndex.getWorkAmountByPackageSQL("清洗数量", params)
+ + ") tl ");
+ sql += " union all " + washedAmountSql;
+ //外来器械当天待装配的总包数
+ String foreignToBePackAmountSql = " select 'foreignToBePackAmount' type,sum(amount) amount,0 urgentAmount from ( "
+ + getToBePackAmountSql(handleDepartCoding, tousseTypesql, toBeBetweenSql) + ") tb ";
+ sql += " union all " + foreignToBePackAmountSql;
+ //外来器械当天已装配总包数
+ String foreignPackedAmountSql = String.format("select 'foreignPackedAmount' type,sum(amount) amount,0 urgentAmount from ("
+ +dataIndex.getWorkAmountByPackageSQL("配包数量", foreignParams)
+ + ") tl ");
+ sql += " union all " + foreignPackedAmountSql;
+ //当天待装配的总包数
+ String toBePackAmountSql = " select 'toBePackAmount' type,sum(amount) amount,0 urgentAmount from ( "
+ + getToBePackAmountSql(handleDepartCoding, "", toBeBetweenSql) + ") tb ";
+ sql += " union all " + toBePackAmountSql;
+ //当天已装配总包数
+ String packedAmountSql = String.format("select 'packedAmount' type,sum(amount) amount,0 urgentAmount from ("
+ +dataIndex.getWorkAmountByPackageSQL("配包数量", params)
+ + ") tl ");
+ sql += " union all " + packedAmountSql;
+ //当天已审核的总包数
+ String reviewedAmountSql = String.format("select 'reviewedAmount' type,sum(amount) amount,0 urgentAmount from ("
+ +dataIndex.getWorkAmountByPackageSQL("审核数量", params)
+ + ") tl ");
+ sql += " union all " + reviewedAmountSql;
+ //外来器械当天待灭菌的总包数
+ String foreignToBeSterilizationAmountSql = " select 'foreignToBeSterilizationAmount' type,sum(amount) amount,0 urgentAmount from ( "
+ + getToBeSterilizationAmountSql(handleDepartCoding, tousseTypesql, toBeBetweenSql) + ") tb ";
+ sql += " union all " + foreignToBeSterilizationAmountSql;
+ //外来器械当天已灭菌的总包数
+ String foreignSterilizationedAmountSql = String.format("select 'foreignSterilizationedAmount' type,sum(amount) amount,0 urgentAmount from ("
+ +dataIndex.getWorkAmountByPackageSQL("灭菌数量", foreignParams)
+ + ") tl ");
+ sql += " union all " + foreignSterilizationedAmountSql;
+ //当天待灭菌的总包数
+ String toBeSterilizationAmountSql = " select 'toBeSterilizationAmount' type,sum(amount) amount,0 urgentAmount from ( "
+ + getToBeSterilizationAmountSql(handleDepartCoding, "", toBeBetweenSql) + ") tb ";
+ sql += " union all " + toBeSterilizationAmountSql;
+ //当天已灭菌的总包数
+ String sterilizationedAmountSql = String.format("select 'sterilizationedAmount' type,sum(amount) amount,0 urgentAmount from ("
+ +dataIndex.getWorkAmountByPackageSQL("灭菌数量", params)
+ + ") tl ");
+ sql += " union all " + sterilizationedAmountSql;
+ //外来器械当天待发货的总包数
+ String foreignToBeInvoiceAmountSql = " select 'foreignToBeInvoiceAmount' type,sum(amount) amount,0 urgentAmount from ( "
+ + getToBeInvoiceAmountSql(handleDepartCoding, tousseTypesql, toBeBetweenSql) + ") tb ";
+ sql += " union all " + foreignToBeInvoiceAmountSql;
+ //外来器械当天已发货的总包数
+ String foreignInvoicedAmountSql = String.format("select 'foreignInvoicedAmount' type,sum(amount) amount,0 urgentAmount from ("
+ +dataIndex.getWorkAmountByPackageSQL("发货数量", foreignParams)
+ + ") tl ");
+ sql += " union all " + foreignInvoicedAmountSql;
+ //当天待发货的总包数
+ String toBeInvoiceAmountSql = " select 'toBeInvoiceAmount' type,sum(amount) amount,sum(urgentAmount) urgentAmount from ( "
+ + getToBeInvoiceAmountSql(handleDepartCoding, "", toBeBetweenSql) + ") tb ";
+ sql += " union all " + toBeInvoiceAmountSql;
+ //当天已发货的总包数
+ String invoicedAmountSql = String.format("select 'invoicedAmount' type,sum(amount) amount,0 urgentAmount from ("
+ +dataIndex.getWorkAmountByPackageSQL("发货数量", params)
+ + ") tl ");
+ sql += " union all " + invoicedAmountSql;
+ //外来器械当天待签收的包数
+ String foreignToBeSignAmountSql = " select 'foreignToBeSignAmount' type,sum(amount) amount,0 urgentAmount from ( "
+ + getToBeSignAmountSql(handleDepartCoding, tousseTypesql, toBeBetweenSql) + ") tb ";
+ sql += " union all " + foreignToBeSignAmountSql;
+ //外来器械当天已签收的包数
+ String foreignSignedAmountSql = String.format("select 'foreignSignedAmount' type,sum(amount) amount,0 urgentAmount from ("
+ +dataIndex.getWorkAmountByPackageSQL("签收数量", foreignParams)
+ + ") tl ");
+ sql += " union all " + foreignSignedAmountSql;
+ //当天已签收的包数
+ String signedAmountSql = String.format("select 'signedAmount' type,sum(amount) amount,0 urgentAmount from ("
+ +dataIndex.getWorkAmountByPackageSQL("签收数量", params)
+ + ") tl ");
+ sql += " union all " + signedAmountSql;
+ //当天已处理加急物品的总包数
+ String invoicedUrgentAmountSql = " select 'invoicedUrgentAmount' type,sum(amount) amount,0 urgentAmount from ( "
+ + getInvoicedUrgentAmountSql(handleDepartCoding, betweenSql) + ") tb ";
+ sql += " union all " + invoicedUrgentAmountSql;
+ ResultSet rs = null;
+ JSONObject obj = new JSONObject();
+ try {
+ rs = objectDao.executeSql(sql);
+ while (rs.next()) {
+ String type = rs.getString("type");
+ if("toBeInvoiceAmount".equals(type)){
+ obj.put("toBeInvoiceUrgentAmount", rs.getInt("urgentAmount"));//待处理加急数量
+ }
+ obj.put(type, rs.getInt("amount"));
+ }
+ } catch (Exception e) {
+ e.printStackTrace();
+ } finally {
+ DatabaseUtil.closeResultSetAndStatement(rs);
+ }
+ return obj;
+ }
+ /**
+ * 获取待回收的器械包数量sql
+ * @param handleDepartCoding 一级供应室编码
+ * @param tousseTypesql 包类型过滤
+ * @param toBeBetweenSql 时间过滤
+ * @param excludeSecondaryData 排除二次回收数据
+ * @return
+ */
+ private String getToBeRecycledAmountSql(String handleDepartCoding, String tousseTypesql, String toBeBetweenSql, boolean excludeSecondaryData){
+ String sql = " select sum(ti.amount-case when ti.recyclingAmount is null then 0 else ti.recyclingAmount end) amount from "
+ + InvoicePlan.class.getSimpleName()
+ + " po join "+
+ TousseItem.class.getSimpleName()
+ +" ti on ti.recyclingApplication_ID=po.id "
+ + "left join TousseDefinition td on td.id=ti.tousseDefinitionId "
+ + "where po.committedStatus=1 "
+ + tousseTypesql;
+ if(excludeSecondaryData){
+ sql += " and po.type='"+ InvoicePlan.TYPE_FOREIGNTOUSSEAPPLIACTION +"' and po.recyclingStatus in('"
+ + InvoicePlan.RECYCLINGSTATUS_AWAITRECEIVE
+ +"','"
+ + InvoicePlan.RECYCLINGSTATUS_PARTAWAITRECEIVE
+ +"') ";
+ }else{
+ String [] recycleStatus = {
+ InvoicePlan.RECYCLINGSTATUS_AWAITRECYCLE,
+ InvoicePlan.RECYCLINGSTATUS_PARTRECYCLE,
+ InvoicePlan.RECYCLINGSTATUS_AWAITRECEIVE,
+ InvoicePlan.RECYCLINGSTATUS_PARTAWAITRECEIVE,
+ InvoicePlan.RECYCLINGSTATUS_TALLIED
+ };
+ sql += " and po.recyclingStatus in('" + StringUtils.join(recycleStatus, "','") + "')";
+ }
+ sql += SqlUtils.buildFieldNameBetweenSql("po.applicationTime", toBeBetweenSql);
+ sql += "and po.handleDepartCoding='"+ handleDepartCoding +"' and (po.includeRecyclingItems is null or po.includeRecyclingItems =1) ";
+ sql += "and (ti.hideRecycling is null or ti.hideRecycling <> '是') and ti.amount>0 and (ti.amount>ti.recyclingAmount or ti.recyclingAmount is null) ";
+ return sql;
+ }
+ /**
+ * 获取待签收的器械包数量sql
+ * @param handleDepartCoding 一级供应室编码
+ * @param tousseTypesql 包类型过滤
+ * @param toBeBetweenSql 时间过滤
+ * @return
+ */
+ private String getToBeSignAmountSql(String handleDepartCoding, String tousseTypesql, String toBeBetweenSql){
+ String sql = " select 1 amount from "
+ + TousseInstance.class.getSimpleName()
+ +" ti join "
+ + TousseDefinition.class.getSimpleName()
+ +" td on td.id=ti.tousseDefinition_id join "
+ + Invoice.class.getSimpleName()
+ +" i on i.id=ti.invoice_id "
+ + "where ti.status='"
+ + TousseInstance.STATUS_SHIPPED
+ +"' "
+ + SqlUtils.buildFieldNameBetweenSql("i.sendTime", toBeBetweenSql)
+ + tousseTypesql;
+ return sql;
+ }
+ /**
+ * 获取待清洗的器械包数量sql
+ * @param handleDepartCoding 一级供应室编码
+ * @param tousseTypesql 包类型过滤
+ * @param toBeBetweenSql 时间过滤
+ * @return
+ */
+ private String getToBeWashAmountSql(String handleDepartCoding, String tousseTypeSql, String toBeBetweenSql, boolean excludeSecondaryData){
+ String excludeSecondaryDataSql = null;
+ if(excludeSecondaryData){
+ excludeSecondaryDataSql = " and rr.recyclingTimes is null ";
+ }else{
+ excludeSecondaryDataSql = "";
+ }
+ String betweenSql = SqlUtils.buildFieldNameBetweenSql("rr.recyclingTime", toBeBetweenSql);
+ String sql = " select min(ci.tousseAmountForMaterial) amount from "
+ + ClassifyBasket.class.getSimpleName()
+ +" cb,"
+ + ClassifiedItem.class.getSimpleName()
+ +" ci,"
+ + TousseDefinition.class.getSimpleName()
+ +" td,"
+ + TousseDefinition.class.getSimpleName()
+ +" tdc ,"
+ + RecyclingRecord.class.getSimpleName()
+ +" rr, "
+ + Container.class.getSimpleName()
+ +" c, "
+ + BarcodeDevice.class.getSimpleName()
+ +" bd "
+ + " where ci.classifybasket_id = cb.id and td.id=ci.tousseDefinitionID and bd.id=c.id and bd.barcode=cb.containerBarcode and rr.id=ci.recyclingRecordId "
+ + " and rr.orgUnitCoding='"+ handleDepartCoding +"' "
+ + betweenSql
+ + tousseTypeSql
+ + excludeSecondaryDataSql
+ + " and tdc.id=td.ancestorID and ci.itemType = '"
+ + ClassifiedItem.TYPE_MATERIAL
+ +"' and c.status='"+ Container.CONTAINER_STATUS_WASHLOADING +"' "
+ + " and cb.id not in (select cw.ClassifyBasket_ID from ClassifyBasket_WashRecord cw ) "
+ + " and cb.id not in (select classifyBasketId from PackingRecord_ClassifyBasket) "
+ + " group by ci.tousseDefinitionID,ci.recyclingRecordId "
+ + " union all "
+ + " select (ci.amount-case when numOfUnwashedStops is null then 0 else numOfUnwashedStops end) amount from "
+ + ClassifyBasket.class.getSimpleName()
+ +" cb,"
+ + ClassifiedItem.class.getSimpleName()
+ +" ci,"
+ + TousseDefinition.class.getSimpleName()
+ +" td,"
+ + TousseDefinition.class.getSimpleName()
+ +" tdc,"
+ + RecyclingRecord.class.getSimpleName()
+ +" rr,"
+ + Container.class.getSimpleName()
+ +" c,"
+ + BarcodeDevice.class.getSimpleName()
+ +" bd where ci.classifybasket_id = cb.id and td.id=ci.tousseDefinitionID and bd.id=c.id and bd.barcode=cb.containerBarcode and rr.id=ci.recyclingRecordId "
+ + betweenSql
+ + tousseTypeSql
+ + excludeSecondaryDataSql
+ + " and c.status='"+ Container.CONTAINER_STATUS_WASHLOADING +"' and rr.orgUnitCoding='"+ handleDepartCoding +"' "
+ + " and cb.id not in (select cw.ClassifyBasket_ID from ClassifyBasket_WashRecord cw ) "
+ + " and cb.id not in (select classifyBasketId from PackingRecord_ClassifyBasket) "
+ + " and tdc.id=td.ancestorID and ci.itemtype != '"
+ + ClassifiedItem.TYPE_MATERIAL
+ +"' ";
+ return sql;
+ }
+ /**
+ * 获取当天已处理加急物品的总包数sql
+ * @param handleDepartCoding 一级供应室编码
+ * @param betweenSql 时间过滤
+ * @return
+ */
+ private String getInvoicedUrgentAmountSql(String handleDepartCoding, String betweenSql){
+ String sql = "select count(*) amount from "
+ + TousseInstance.class.getSimpleName()
+ + " ti join "
+ + Invoice.class.getSimpleName()
+ +" i on i.id=ti.invoice_id where ti.urgentLevel_id is not null and i.orgUnitCoding='"
+ + handleDepartCoding
+ +"' and i.sendTime "
+ + betweenSql;
+ return sql;
+ }
+ /**
+ * 获取当天待发货的总包数sql
+ * @param handleDepartCoding 一级供应室编码
+ * @param tousseTypesql 包类型过滤
+ * @param toBeBetweenSql 时间过滤
+ * @return
+ */
+ private String getToBeInvoiceAmountSql(String handleDepartCoding, String tousseTypesql, String toBeBetweenSql){
+ String amountSql = "((case when (po.type='"
+ + InvoicePlan.TYPE_BORROWINGSINGLE
+ +"' or po.type='"
+ + InvoicePlan.TYPE_PROXYDISINFECTION
+ +"' or po.type='"
+ + InvoicePlan.TYPE_GOODS_RESERVATION_FORM
+ + "') then ti.amount else case when ti.tousseType = '"
+ + TousseDefinition.PACKAGE_TYPE_INSIDE
+ +"' or ti.tousseType = '"
+ + TousseDefinition.PACKAGE_TYPE_DISINFECTION
+ +"' or ti.tousseType = '"
+ + TousseDefinition.PACKAGE_TYPE_CUSTOM
+ +"' or ti.tousseType = '"
+ +TousseDefinition.PACKAGE_TYPE_FOREIGN
+ +"' then case when ti.invoiceAmountMode is not null and ti.invoiceAmountMode <> 0 "
+ + " then case when ti.invoiceAmountMode = 1 then case when ti.recyclingAmount is null then case when ti.isRecycling='否' then ti.amount else 0 end else ti.recyclingAmount "
+ + " end when ti.invoiceAmountMode = 2 then case when ti.recyclingAmount is null then ti.amount else ti.recyclingAmount end when ti.invoiceAmountMode = 3 "
+ + " then case when ti.recyclingAmount is null then case when ti.isRecycling='是' then 0 else ti.amount end else ti.amount end else ti.amount end "
+ + " else (case when ti.recyclingAmount is not null then ti.recyclingAmount when ti.prepareRecycleAmount is not null then ti.prepareRecycleAmount else ti.amount end ) "
+ + " end else ti.amount end end) - ti.sendoutAmount) ";
+ String commonFilterSql = " and ((po.deliverStatus='"
+ + InvoicePlan.DELIVERSTATUS_AWAITDELIVER
+ +"' or po.deliverStatus='"
+ + InvoicePlan.DELIVERSTATUS_PARTDELIVERED +"') and (po.endStatus is null or po.endStatus!='"
+ + InvoicePlan.STATUS_END +"')) and (po.committedStatus = 1) and po.handleDepartCoding = '"
+ + handleDepartCoding
+ +"' ";
+ commonFilterSql += tousseTypesql + SqlUtils.buildFieldNameBetweenSql("po.applicationTime", toBeBetweenSql);
+ //外来器械申请单
+ String sql = " select (amount - sendOutAmount) amount"
+ + ",(case when (ti.urgentAmount is null or ti.urgentAmount <= ti.sendoutAmount) then 0 else (ti.urgentAmount - ti.sendoutAmount) end) urgentAmount from "
+ + TousseItem.class.getSimpleName()
+ +" ti left join "
+ + TousseDefinition.class.getSimpleName()
+ +" td on ti.tousseDefinitionId=td.id join "
+ + InvoicePlan.class.getSimpleName()
+ +" po on ti.recyclingApplication_ID=po.id where ti.isInvoice = '是' and (ti.isTerminated is null or ti.isTerminated <> 1) and "
+ + amountSql
+ + " > 0 and (( ti.tousseType IN ('"
+ + TousseDefinition.PACKAGE_TYPE_SPLIT
+ +"','"
+ + TousseDefinition.PACKAGE_TYPE_FOREIGN
+ +"') )) "
+ + commonFilterSql
+ + "and po.type='"
+ + InvoicePlan.TYPE_FOREIGNTOUSSEAPPLIACTION
+ +"' and po.id not in (select foreignTousseApp_id from TousseInstance where foreignTousseApp_id is not null) "
+ + "and (amount - sendOutAmount) > 0 union all select 1 amount,(case when tis.urgentLevel_id is null then 0 else 1 end) urgentAmount from "
+ + TousseInstance.class.getSimpleName()
+ +" tis join "
+ + InvoicePlan.class.getSimpleName()
+ +" po on tis.foreignTousseApp_id=po.id left join "
+ + TousseDefinition.class.getSimpleName()
+ +" td on tis.tousseDefinition_id=td.id where 1=1 "
+ + commonFilterSql
+ + "and po.type='"
+ + InvoicePlan.TYPE_FOREIGNTOUSSEAPPLIACTION
+ + "' and (( tis.status IN ('"
+ + TousseInstance.STATUS_PACKED
+ +"','"
+ + TousseInstance.STATUS_REVIEWED
+ +"','"
+ + TousseInstance.STATUS_STERILING
+ +"','"
+ + TousseInstance.STATUS_DELAY_CONFIRM
+ +"','"
+ + TousseInstance.STATUS_STERILED
+ +"') )) ";
+ //外部器械包代理灭菌申请单','自定义器械包申请单 , 代理灭菌单
+ sql += " union all select "
+ +amountSql
+ + " amount ,(case when (ti.urgentAmount is null or ti.urgentAmount <= ti.sendoutAmount) then 0 else (ti.urgentAmount - ti.sendoutAmount) end) urgentAmount from "
+ + TousseItem.class.getSimpleName()
+ +" ti left join "
+ + TousseDefinition.class.getSimpleName()
+ +" td on ti.tousseDefinitionId=td.id join "
+ + InvoicePlan.class.getSimpleName()
+ +" po on ti.recyclingApplication_ID=po.id where 1=1"
+ + commonFilterSql
+ + "and (( po.type IN ('"
+ + InvoicePlan.TYPE_FOREIGNPROXYDISINFECTION
+ +"','"+ InvoicePlan.TYPE_CUSTOM_TOUSSE_APPLIACTION_FORM
+ +"') )) and ti.isInvoice = '是' and (ti.isTerminated is null or ti.isTerminated <> 1) and "
+ + amountSql
+ + "> 0 and (( ti.tousseType IN ('"
+ + TousseDefinition.PACKAGE_TYPE_FOREIGNPROXY
+ +"','"
+ + TousseDefinition.PACKAGE_TYPE_SPLIT
+ +"','"
+ + TousseDefinition.PACKAGE_TYPE_CUSTOM
+ +"','"
+ + TousseDefinition.PACKAGE_TYPE_DRESSING
+ +"','"
+ + TousseDefinition.PACKAGE_TYPE_INSIDE
+ +"','"
+ + DisposableGoods.TYPE_NAME
+ +"','"
+ + TousseDefinition.PACKAGE_TYPE_FOREIGN
+ +"','"
+ + TousseDefinition.PACKAGE_TYPE_DISINFECTION
+ +"') )) and (case when (po.type='"
+ + InvoicePlan.TYPE_BORROWINGSINGLE
+ +"' or po.type='"
+ + InvoicePlan.TYPE_PROXYDISINFECTION
+ +"' or po.type='"
+ + InvoicePlan.TYPE_GOODS_RESERVATION_FORM
+ +"') then ti.amount else case when ti.tousseType = '"
+ + TousseDefinition.PACKAGE_TYPE_INSIDE
+ + "' or ti.tousseType = '"
+ + TousseDefinition.PACKAGE_TYPE_DISINFECTION
+ +"' or ti.tousseType = '"
+ + TousseDefinition.PACKAGE_TYPE_CUSTOM
+ +"' or ti.tousseType = '"
+ + TousseDefinition.PACKAGE_TYPE_FOREIGN
+ +"' then case when ti.invoiceAmountMode is not null and ti.invoiceAmountMode <> 0 then case when ti.invoiceAmountMode = 1 "
+ + "then case when ti.recyclingAmount is null then case when ti.isRecycling='否' then ti.amount else 0 end else ti.recyclingAmount end when ti.invoiceAmountMode = 2 "
+ + "then case when ti.recyclingAmount is null then ti.amount else ti.recyclingAmount end when ti.invoiceAmountMode = 3 then case when ti.recyclingAmount is null "
+ + "then case when ti.isRecycling='是' then 0 else ti.amount end else ti.amount end else ti.amount end else (case when ti.recyclingAmount is not null then ti.recyclingAmount "
+ + "when ti.prepareRecycleAmount is not null then ti.prepareRecycleAmount else ti.amount end )"
+ + " end else ti.amount end end) > 0 ";
+ //'敷料包','器械包'
+ sql += " union all select sum((case when ti.tousseType='"
+ + DisposableGoods.TYPE_NAME
+ +"' then case when (ti.transferScale is not null and ti.transferScale > 0) then ti.amount/ti.transferScale else ti.amount "
+ + "end else (case when (po.type='"
+ + InvoicePlan.TYPE_BORROWINGSINGLE
+ +"' or po.type='"
+ + InvoicePlan.TYPE_PROXYDISINFECTION
+ +"' or po.type='"
+ + InvoicePlan.TYPE_GOODS_RESERVATION_FORM
+ +"') then ti.amount else case when ti.tousseType = '"
+ + TousseDefinition.PACKAGE_TYPE_INSIDE
+ +"' "
+ + "or ti.tousseType = '"
+ + TousseDefinition.PACKAGE_TYPE_DISINFECTION
+ +"' or ti.tousseType = '"
+ + TousseDefinition.PACKAGE_TYPE_CUSTOM
+ +"' or ti.tousseType = '"
+ + TousseDefinition.PACKAGE_TYPE_FOREIGN
+ +"' then case when ti.invoiceAmountMode is not null and ti.invoiceAmountMode <> 0 then case when ti.invoiceAmountMode = 1 then case when ti.recyclingAmount is null then case when ti.isRecycling='否' "
+ + "then ti.amount else 0 end else ti.recyclingAmount end when ti.invoiceAmountMode = 2 then case when ti.recyclingAmount is null then ti.amount else ti.recyclingAmount "
+ + "end when ti.invoiceAmountMode = 3 then case when ti.recyclingAmount is null then case when ti.isRecycling='是' then 0 else ti.amount end else ti.amount end else ti.amount "
+ + "end else (case when ti.recyclingAmount is not null then ti.recyclingAmount when ti.prepareRecycleAmount is not null then ti.prepareRecycleAmount else ti.amount end ) "
+ + "end else ti.amount end end) end) - (case when (ti.transferScale is not null and ti.transferScale > 0) then ti.sendOutAmount/ti.transferScale else ti.sendOutAmount end)) "
+ + "amount ,sum(case when (ti.urgentAmount is null) then 0 else (ti.urgentAmount) end) urgentAmount from "
+ + TousseItem.class.getSimpleName()
+ +" ti join "
+ + InvoicePlan.class.getSimpleName()
+ +" po on ti.recyclingApplication_ID=po.id join "
+ + TousseDefinition.class.getSimpleName()
+ +" td on td.id=ti.tousseDefinitionId where ((po.type <> '"
+ + InvoicePlan.TYPE_BORROWINGSINGLE
+ +"' or (po.type = '"
+ + InvoicePlan.TYPE_BORROWINGSINGLE
+ +"' and (po.borrowConfirmStatus is null or po.borrowConfirmStatus <> '待审核')))) "
+ + commonFilterSql
+ + " and ( po.type NOT IN ('"
+ + InvoicePlan.TYPE_QUALITY_MONITOR_RECYCLE_APPLICATION
+ +"','"
+ + InvoicePlan.TYPE_SECOND_SUPPLY_ROOM
+ +"','"
+ + InvoicePlan.TYPE_EXPENSIVEGOODS_FORM
+ +"','"
+ + InvoicePlan.TYPE_OPERATION_RESERVATION_APPLICATION
+ +"','"
+ + InvoicePlan.TYPE_FOREIGNTOUSSEAPPLIACTION
+ +"','"
+ + InvoicePlan.TYPE_PROXYDISINFECTION
+ +"','"
+ + InvoicePlan.TYPE_FOREIGNPROXYDISINFECTION
+ +"','"
+ + InvoicePlan.TYPE_CUSTOM_TOUSSE_APPLIACTION_FORM
+ +"') ) and (1=1) and ti.isInvoice = '是' and (ti.isTerminated is null or ti.isTerminated <> 1) "
+ + "and ((case when (po.type='"
+ + InvoicePlan.TYPE_BORROWINGSINGLE
+ +"' or po.type='"
+ + InvoicePlan.TYPE_PROXYDISINFECTION
+ +"' or po.type='"
+ + InvoicePlan.TYPE_GOODS_RESERVATION_FORM
+ +"') then ti.amount else case when ti.tousseType = '"
+ + TousseDefinition.PACKAGE_TYPE_INSIDE
+ +"' or ti.tousseType = '"
+ + TousseDefinition.PACKAGE_TYPE_DISINFECTION
+ +"' or ti.tousseType = '"
+ + TousseDefinition.PACKAGE_TYPE_CUSTOM
+ +"' or ti.tousseType = '"
+ + TousseDefinition.PACKAGE_TYPE_FOREIGN
+ +"' then case when ti.invoiceAmountMode is not null and ti.invoiceAmountMode <> 0 then case when ti.invoiceAmountMode = 1 then case when ti.recyclingAmount is null then case when "
+ + "ti.isRecycling='否' then ti.amount else 0 end else ti.recyclingAmount end when ti.invoiceAmountMode = 2 then case when ti.recyclingAmount is null then ti.amount "
+ + "else ti.recyclingAmount end when ti.invoiceAmountMode = 3 then case when ti.recyclingAmount is null then case when ti.isRecycling='是' then 0 else ti.amount end else "
+ + "ti.amount end else ti.amount end else (case when ti.recyclingAmount is not null then ti.recyclingAmount when ti.prepareRecycleAmount is not null then ti.prepareRecycleAmount "
+ + "else ti.amount end ) end else ti.amount end end) - ti.sendOutAmount) > 0 "
+ + "and (( ti.tousseType IN ('"
+ + TousseDefinition.PACKAGE_TYPE_DRESSING
+ +"','"
+ + TousseDefinition.PACKAGE_TYPE_INSIDE
+ +"') )) and ((case when ti.tousseType='"
+ + DisposableGoods.TYPE_NAME
+ +"' then case when (ti.transferScale is not null and ti.transferScale > 0) then ti.amount/ti.transferScale else ti.amount"
+ + " end else (case when (po.type='"
+ + InvoicePlan.TYPE_BORROWINGSINGLE
+ +"' or po.type='"
+ + InvoicePlan.TYPE_PROXYDISINFECTION
+ +"' or po.type='"
+ + InvoicePlan.TYPE_GOODS_RESERVATION_FORM
+ +"') then ti.amount else case when ti.tousseType = '"
+ + TousseDefinition.PACKAGE_TYPE_INSIDE
+ +"' or ti.tousseType = '"
+ + TousseDefinition.PACKAGE_TYPE_DISINFECTION
+ +"' or ti.tousseType = '"
+ + TousseDefinition.PACKAGE_TYPE_CUSTOM
+ +"' or ti.tousseType = '"
+ + TousseDefinition.PACKAGE_TYPE_FOREIGN
+ +"' then case when ti.invoiceAmountMode is not null and ti.invoiceAmountMode <> 0 "
+ + "then case when ti.invoiceAmountMode = 1 then case when ti.recyclingAmount is null then case when ti.isRecycling='否' then ti.amount else 0 end else "
+ + "ti.recyclingAmount end when ti.invoiceAmountMode = 2 then case when ti.recyclingAmount is null then ti.amount else ti.recyclingAmount end when ti.invoiceAmountMode = "
+ + "3 then case when ti.recyclingAmount is null then case when ti.isRecycling='是' then 0 else ti.amount end else ti.amount end else ti.amount end else "
+ + "(case when ti.recyclingAmount is not null then ti.recyclingAmount when ti.prepareRecycleAmount is not null then ti.prepareRecycleAmount else ti.amount end ) "
+ + "end else ti.amount end end) end) - (case when (ti.transferScale is not null and ti.transferScale > 0) then ti.sendOutAmount/ti.transferScale else ti.sendOutAmount end)) "
+ + "> 0 group by tousseName,td.tousseType ";
+ return sql;
+ }
+ /**
+ * 获取当天待灭菌的总包数sql
+ * @param handleDepartCoding 一级供应室编码
+ * @param tousseTypesql 包类型过滤
+ * @param toBeBetweenSql 时间过滤
+ * @return
+ */
+ private String getToBeSterilizationAmountSql(String handleDepartCoding, String tousseTypesql, String toBeBetweenSql){
+ String sql = "select 1 amount from "
+ + ReviewedBasket.class.getSimpleName()
+ +" po join "
+ + TousseInstance.class.getSimpleName()
+ +" ti on ti.reviewBasket_id=po.id join "
+ + TousseDefinition.class.getSimpleName()
+ +" td on ti.tousseDefinition_id=td.id join "
+ + Container.class.getSimpleName()
+ +" c on c.id=po.container_id left join sterilization_reviewed sr on sr.reviewedBasket_id=po.id left join "
+ + SterilizationRecord.class.getSimpleName()
+ +" s on s.id=sr.sterilizationRecord_id where s.id is null "
+ + tousseTypesql
+ + SqlUtils.buildFieldNameBetweenSql("ti.operationTime", toBeBetweenSql)
+ + " and (c.purpose='"
+ + Container.CONTAINER_PURPOSE_DISINFECTION
+ +"'and c.status = '"
+ + Container.CONTAINER_STATUS_STERILIZELOADING
+ +"' or c.purpose='"
+ + Container.CONTAINER_PURPOSE_VIRTUAL
+ +"' and c.status = '"
+ + Container.CONTAINER_STATUS_PACKING
+ +"' and ti.status='"
+ + TousseInstance.STATUS_REVIEWED
+ +"' and not exists (select reviewBasket_id from TousseInstance where reviewBasket_id=ti.reviewBasket_id and status<>'"
+ + TousseInstance.STATUS_REVIEWED
+ +"' ) ) and (c.departCode = '"
+ + handleDepartCoding
+ +"' or po.id in ( select rbs.id from "
+ + ProxyDisinfection.class.getSimpleName()
+ +" pd join "
+ + InvoicePlan.class.getSimpleName()
+ +" ip on ip.id=pd.id join proxyDis_rBasket pr on pr.proxyDisinfection_id=pd.id join "
+ + ReviewedBasket.class.getSimpleName()
+ +" rbs on rbs.id=pr.reviewedBasket_id where ip.handleDepartCoding='"
+ + handleDepartCoding
+ +"')) union all select 1 amount from "
+ + TousseInstance.class.getSimpleName()
+ +" ti join "
+ + TousseDefinition.class.getSimpleName()
+ +" td on ti.tousseDefinition_id=td.id join "
+ + TousseDefinition.class.getSimpleName()
+ +" tdancestor on td.ancestorID=tdancestor.id left join "
+ + InvoicePlan.class.getSimpleName()
+ +" pd on pd.id=ti.proxyDisinfection_id where tdancestor.isTraceable='是' and ti.status='"
+ + TousseInstance.STATUS_REVIEWED
+ +"' "
+ + tousseTypesql
+ + "and ti.comboTousseInstanceId is null and ti.reviewBasket_id is null and (pd.id is null and ti.orgunitcoding='"
+ +handleDepartCoding
+ +"' or pd.id is not null and pd.handleDepartCoding='"
+ + handleDepartCoding
+ +"') and (ti.hasReceived is null or ti.hasReceived='是') ";
+ return sql;
+ }
+ /**
+ * 获取待装配的器械包数量sql
+ * @param handleDepartCoding 一级供应室编码
+ * @param tousseTypesql 包类型过滤
+ * @param toBeBetweenSql 时间过滤
+ * @return
+ */
+ private String getToBePackAmountSql(String handleDepartCoding, String tousseTypesql, String toBeBetweenSql){
+ String packingSql = " select po.unPackAmount amount from "
+ + PackingTask.class.getSimpleName()
+ +" po join "
+ + TousseDefinition.class.getSimpleName()
+ +" td on td.id=po.tousseDefinition_id join "
+ + TousseDefinition.class.getSimpleName()
+ +" tdc on tdc.id=td.ancestorID where po.orgUnitCoding = '"
+ + handleDepartCoding
+ +"' "
+ + tousseTypesql
+ + SqlUtils.buildFieldNameBetweenSql("po.startTime", toBeBetweenSql)
+ + "and (tdc.hidePackingTask is null or tdc.hidePackingTask<>'是') ";
+ return packingSql;
+ }
+
+}
Index: ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/service/JasperReportManagerImpl.java
===================================================================
diff -u -r35792 -r35793
--- ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/service/JasperReportManagerImpl.java (.../JasperReportManagerImpl.java) (revision 35792)
+++ ssts-reports/src/main/java/com/forgon/disinfectsystem/jasperreports/service/JasperReportManagerImpl.java (.../JasperReportManagerImpl.java) (revision 35793)
@@ -180,6 +180,7 @@
import com.forgon.disinfectsystem.jasperreports.util.MonthReportGroupByDisposableGoodsTypeHelper;
import com.forgon.disinfectsystem.jasperreports.util.MonthReportGroupBySterilizationModeHelper;
import com.forgon.disinfectsystem.jasperreports.util.MonthReportHelper;
+import com.forgon.disinfectsystem.jasperreports.util.RealTimeBulletinBoardWorkloadHelper;
import com.forgon.disinfectsystem.jasperreports.util.TousseWorkLoadDataForDLZXYYHelper;
import com.forgon.disinfectsystem.packing.service.PackingManager;
import com.forgon.disinfectsystem.qualitymonitoring.definition.service.QualityMonitoringDefinitionManager;
@@ -309,6 +310,8 @@
private MonthReportGroupByDisposableGoodsTypeHelper monthReportGroupByDisposableGoodsTypeHelper;
@Autowired
private MonthReportGroupBySterilizationModeHelper monthReportGroupBySterilizationModeHelper;
+ @Autowired
+ private RealTimeBulletinBoardWorkloadHelper realTimeBulletinBoardWorkloadHelper;
public void setPackingManager(PackingManager packingManager) {
this.packingManager = packingManager;
}
@@ -24528,692 +24531,14 @@
vo.setSmallAmount(amount);
}
}
- /**
- * 获取当天待发货的总包数sql
- * @param handleDepartCoding 一级供应室编码
- * @param tousseTypesql 包类型过滤
- * @param toBeBetweenSql 时间过滤
- * @return
- */
- private String getToBeInvoiceAmountSql(String handleDepartCoding, String tousseTypesql, String toBeBetweenSql){
- String amountSql = "((case when (po.type='"
- + InvoicePlan.TYPE_BORROWINGSINGLE
- +"' or po.type='"
- + InvoicePlan.TYPE_PROXYDISINFECTION
- +"' or po.type='"
- + InvoicePlan.TYPE_GOODS_RESERVATION_FORM
- + "') then ti.amount else case when ti.tousseType = '"
- + TousseDefinition.PACKAGE_TYPE_INSIDE
- +"' or ti.tousseType = '"
- + TousseDefinition.PACKAGE_TYPE_DISINFECTION
- +"' or ti.tousseType = '"
- + TousseDefinition.PACKAGE_TYPE_CUSTOM
- +"' or ti.tousseType = '"
- +TousseDefinition.PACKAGE_TYPE_FOREIGN
- +"' then case when ti.invoiceAmountMode is not null and ti.invoiceAmountMode <> 0 "
- + " then case when ti.invoiceAmountMode = 1 then case when ti.recyclingAmount is null then case when ti.isRecycling='否' then ti.amount else 0 end else ti.recyclingAmount "
- + " end when ti.invoiceAmountMode = 2 then case when ti.recyclingAmount is null then ti.amount else ti.recyclingAmount end when ti.invoiceAmountMode = 3 "
- + " then case when ti.recyclingAmount is null then case when ti.isRecycling='是' then 0 else ti.amount end else ti.amount end else ti.amount end "
- + " else (case when ti.recyclingAmount is not null then ti.recyclingAmount when ti.prepareRecycleAmount is not null then ti.prepareRecycleAmount else ti.amount end ) "
- + " end else ti.amount end end) - ti.sendoutAmount) ";
- String handleDepartCodingSql = " and po.handleDepartCoding = '"+ handleDepartCoding +"' ";
- String commonFilterSql = " and ((po.deliverStatus='"
- + InvoicePlan.DELIVERSTATUS_AWAITDELIVER
- +"' or po.deliverStatus='"
- + InvoicePlan.DELIVERSTATUS_PARTDELIVERED +"') and (po.endStatus is null or po.endStatus!='"
- + InvoicePlan.STATUS_END +"')) and (po.committedStatus = 1) and po.handleDepartCoding = '"
- + handleDepartCoding
- +"' ";
- commonFilterSql += tousseTypesql + SqlUtils.buildFieldNameBetweenSql("po.applicationTime", toBeBetweenSql);
- //外来器械申请单
- String sql = " select (amount - sendOutAmount) amount"
- + ",(case when (ti.urgentAmount is null or ti.urgentAmount <= ti.sendoutAmount) then 0 else (ti.urgentAmount - ti.sendoutAmount) end) urgentAmount from "
- + TousseItem.class.getSimpleName()
- +" ti left join "
- + TousseDefinition.class.getSimpleName()
- +" td on ti.tousseDefinitionId=td.id join "
- + InvoicePlan.class.getSimpleName()
- +" po on ti.recyclingApplication_ID=po.id where ti.isInvoice = '是' and (ti.isTerminated is null or ti.isTerminated <> 1) and "
- + amountSql
- + " > 0 and (( ti.tousseType IN ('"
- + TousseDefinition.PACKAGE_TYPE_SPLIT
- +"','"
- + TousseDefinition.PACKAGE_TYPE_FOREIGN
- +"') )) "
- + commonFilterSql
- + "and po.type='"
- + InvoicePlan.TYPE_FOREIGNTOUSSEAPPLIACTION
- +"' and po.id not in (select foreignTousseApp_id from TousseInstance where foreignTousseApp_id is not null) "
- + "and (amount - sendOutAmount) > 0 union all select 1 amount,(case when tis.urgentLevel_id is null then 0 else 1 end) urgentAmount from "
- + TousseInstance.class.getSimpleName()
- +" tis join "
- + InvoicePlan.class.getSimpleName()
- +" po on tis.foreignTousseApp_id=po.id left join "
- + TousseDefinition.class.getSimpleName()
- +" td on tis.tousseDefinition_id=td.id where 1=1 "
- + commonFilterSql
- + "and po.type='"
- + InvoicePlan.TYPE_FOREIGNTOUSSEAPPLIACTION
- + "' and (( tis.status IN ('"
- + TousseInstance.STATUS_PACKED
- +"','"
- + TousseInstance.STATUS_REVIEWED
- +"','"
- + TousseInstance.STATUS_STERILING
- +"','"
- + TousseInstance.STATUS_DELAY_CONFIRM
- +"','"
- + TousseInstance.STATUS_STERILED
- +"') )) ";
- //外部器械包代理灭菌申请单','自定义器械包申请单 , 代理灭菌单
- sql += " union all select "
- +amountSql
- + " amount ,(case when (ti.urgentAmount is null or ti.urgentAmount <= ti.sendoutAmount) then 0 else (ti.urgentAmount - ti.sendoutAmount) end) urgentAmount from "
- + TousseItem.class.getSimpleName()
- +" ti left join "
- + TousseDefinition.class.getSimpleName()
- +" td on ti.tousseDefinitionId=td.id join "
- + InvoicePlan.class.getSimpleName()
- +" po on ti.recyclingApplication_ID=po.id where 1=1"
- + commonFilterSql
- + "and (( po.type IN ('"
- + InvoicePlan.TYPE_FOREIGNPROXYDISINFECTION
- +"','"+ InvoicePlan.TYPE_CUSTOM_TOUSSE_APPLIACTION_FORM
- +"') )) and ti.isInvoice = '是' and (ti.isTerminated is null or ti.isTerminated <> 1) and "
- + amountSql
- + "> 0 and (( ti.tousseType IN ('"
- + TousseDefinition.PACKAGE_TYPE_FOREIGNPROXY
- +"','"
- + TousseDefinition.PACKAGE_TYPE_SPLIT
- +"','"
- + TousseDefinition.PACKAGE_TYPE_CUSTOM
- +"','"
- + TousseDefinition.PACKAGE_TYPE_DRESSING
- +"','"
- + TousseDefinition.PACKAGE_TYPE_INSIDE
- +"','"
- + DisposableGoods.TYPE_NAME
- +"','"
- + TousseDefinition.PACKAGE_TYPE_FOREIGN
- +"','"
- + TousseDefinition.PACKAGE_TYPE_DISINFECTION
- +"') )) and (case when (po.type='"
- + InvoicePlan.TYPE_BORROWINGSINGLE
- +"' or po.type='"
- + InvoicePlan.TYPE_PROXYDISINFECTION
- +"' or po.type='"
- + InvoicePlan.TYPE_GOODS_RESERVATION_FORM
- +"') then ti.amount else case when ti.tousseType = '"
- + TousseDefinition.PACKAGE_TYPE_INSIDE
- + "' or ti.tousseType = '"
- + TousseDefinition.PACKAGE_TYPE_DISINFECTION
- +"' or ti.tousseType = '"
- + TousseDefinition.PACKAGE_TYPE_CUSTOM
- +"' or ti.tousseType = '"
- + TousseDefinition.PACKAGE_TYPE_FOREIGN
- +"' then case when ti.invoiceAmountMode is not null and ti.invoiceAmountMode <> 0 then case when ti.invoiceAmountMode = 1 "
- + "then case when ti.recyclingAmount is null then case when ti.isRecycling='否' then ti.amount else 0 end else ti.recyclingAmount end when ti.invoiceAmountMode = 2 "
- + "then case when ti.recyclingAmount is null then ti.amount else ti.recyclingAmount end when ti.invoiceAmountMode = 3 then case when ti.recyclingAmount is null "
- + "then case when ti.isRecycling='是' then 0 else ti.amount end else ti.amount end else ti.amount end else (case when ti.recyclingAmount is not null then ti.recyclingAmount "
- + "when ti.prepareRecycleAmount is not null then ti.prepareRecycleAmount else ti.amount end )"
- + " end else ti.amount end end) > 0 ";
- //'敷料包','器械包'
- sql += " union all select sum((case when ti.tousseType='"
- + DisposableGoods.TYPE_NAME
- +"' then case when (ti.transferScale is not null and ti.transferScale > 0) then ti.amount/ti.transferScale else ti.amount "
- + "end else (case when (po.type='"
- + InvoicePlan.TYPE_BORROWINGSINGLE
- +"' or po.type='"
- + InvoicePlan.TYPE_PROXYDISINFECTION
- +"' or po.type='"
- + InvoicePlan.TYPE_GOODS_RESERVATION_FORM
- +"') then ti.amount else case when ti.tousseType = '"
- + TousseDefinition.PACKAGE_TYPE_INSIDE
- +"' "
- + "or ti.tousseType = '"
- + TousseDefinition.PACKAGE_TYPE_DISINFECTION
- +"' or ti.tousseType = '"
- + TousseDefinition.PACKAGE_TYPE_CUSTOM
- +"' or ti.tousseType = '"
- + TousseDefinition.PACKAGE_TYPE_FOREIGN
- +"' then case when ti.invoiceAmountMode is not null and ti.invoiceAmountMode <> 0 then case when ti.invoiceAmountMode = 1 then case when ti.recyclingAmount is null then case when ti.isRecycling='否' "
- + "then ti.amount else 0 end else ti.recyclingAmount end when ti.invoiceAmountMode = 2 then case when ti.recyclingAmount is null then ti.amount else ti.recyclingAmount "
- + "end when ti.invoiceAmountMode = 3 then case when ti.recyclingAmount is null then case when ti.isRecycling='是' then 0 else ti.amount end else ti.amount end else ti.amount "
- + "end else (case when ti.recyclingAmount is not null then ti.recyclingAmount when ti.prepareRecycleAmount is not null then ti.prepareRecycleAmount else ti.amount end ) "
- + "end else ti.amount end end) end) - (case when (ti.transferScale is not null and ti.transferScale > 0) then ti.sendOutAmount/ti.transferScale else ti.sendOutAmount end)) "
- + "amount ,sum(case when (ti.urgentAmount is null) then 0 else (ti.urgentAmount) end) urgentAmount from "
- + TousseItem.class.getSimpleName()
- +" ti join "
- + InvoicePlan.class.getSimpleName()
- +" po on ti.recyclingApplication_ID=po.id join "
- + TousseDefinition.class.getSimpleName()
- +" td on td.id=ti.tousseDefinitionId where ((po.type <> '"
- + InvoicePlan.TYPE_BORROWINGSINGLE
- +"' or (po.type = '"
- + InvoicePlan.TYPE_BORROWINGSINGLE
- +"' and (po.borrowConfirmStatus is null or po.borrowConfirmStatus <> '待审核')))) "
- + commonFilterSql
- + " and ( po.type NOT IN ('"
- + InvoicePlan.TYPE_QUALITY_MONITOR_RECYCLE_APPLICATION
- +"','"
- + InvoicePlan.TYPE_SECOND_SUPPLY_ROOM
- +"','"
- + InvoicePlan.TYPE_EXPENSIVEGOODS_FORM
- +"','"
- + InvoicePlan.TYPE_OPERATION_RESERVATION_APPLICATION
- +"','"
- + InvoicePlan.TYPE_FOREIGNTOUSSEAPPLIACTION
- +"','"
- + InvoicePlan.TYPE_PROXYDISINFECTION
- +"','"
- + InvoicePlan.TYPE_FOREIGNPROXYDISINFECTION
- +"','"
- + InvoicePlan.TYPE_CUSTOM_TOUSSE_APPLIACTION_FORM
- +"') ) and (1=1) and ti.isInvoice = '是' and (ti.isTerminated is null or ti.isTerminated <> 1) "
- + "and ((case when (po.type='"
- + InvoicePlan.TYPE_BORROWINGSINGLE
- +"' or po.type='"
- + InvoicePlan.TYPE_PROXYDISINFECTION
- +"' or po.type='"
- + InvoicePlan.TYPE_GOODS_RESERVATION_FORM
- +"') then ti.amount else case when ti.tousseType = '"
- + TousseDefinition.PACKAGE_TYPE_INSIDE
- +"' or ti.tousseType = '"
- + TousseDefinition.PACKAGE_TYPE_DISINFECTION
- +"' or ti.tousseType = '"
- + TousseDefinition.PACKAGE_TYPE_CUSTOM
- +"' or ti.tousseType = '"
- + TousseDefinition.PACKAGE_TYPE_FOREIGN
- +"' then case when ti.invoiceAmountMode is not null and ti.invoiceAmountMode <> 0 then case when ti.invoiceAmountMode = 1 then case when ti.recyclingAmount is null then case when "
- + "ti.isRecycling='否' then ti.amount else 0 end else ti.recyclingAmount end when ti.invoiceAmountMode = 2 then case when ti.recyclingAmount is null then ti.amount "
- + "else ti.recyclingAmount end when ti.invoiceAmountMode = 3 then case when ti.recyclingAmount is null then case when ti.isRecycling='是' then 0 else ti.amount end else "
- + "ti.amount end else ti.amount end else (case when ti.recyclingAmount is not null then ti.recyclingAmount when ti.prepareRecycleAmount is not null then ti.prepareRecycleAmount "
- + "else ti.amount end ) end else ti.amount end end) - ti.sendOutAmount) > 0 "
- + "and (( ti.tousseType IN ('"
- + TousseDefinition.PACKAGE_TYPE_DRESSING
- +"','"
- + TousseDefinition.PACKAGE_TYPE_INSIDE
- +"') )) and ((case when ti.tousseType='"
- + DisposableGoods.TYPE_NAME
- +"' then case when (ti.transferScale is not null and ti.transferScale > 0) then ti.amount/ti.transferScale else ti.amount"
- + " end else (case when (po.type='"
- + InvoicePlan.TYPE_BORROWINGSINGLE
- +"' or po.type='"
- + InvoicePlan.TYPE_PROXYDISINFECTION
- +"' or po.type='"
- + InvoicePlan.TYPE_GOODS_RESERVATION_FORM
- +"') then ti.amount else case when ti.tousseType = '"
- + TousseDefinition.PACKAGE_TYPE_INSIDE
- +"' or ti.tousseType = '"
- + TousseDefinition.PACKAGE_TYPE_DISINFECTION
- +"' or ti.tousseType = '"
- + TousseDefinition.PACKAGE_TYPE_CUSTOM
- +"' or ti.tousseType = '"
- + TousseDefinition.PACKAGE_TYPE_FOREIGN
- +"' then case when ti.invoiceAmountMode is not null and ti.invoiceAmountMode <> 0 "
- + "then case when ti.invoiceAmountMode = 1 then case when ti.recyclingAmount is null then case when ti.isRecycling='否' then ti.amount else 0 end else "
- + "ti.recyclingAmount end when ti.invoiceAmountMode = 2 then case when ti.recyclingAmount is null then ti.amount else ti.recyclingAmount end when ti.invoiceAmountMode = "
- + "3 then case when ti.recyclingAmount is null then case when ti.isRecycling='是' then 0 else ti.amount end else ti.amount end else ti.amount end else "
- + "(case when ti.recyclingAmount is not null then ti.recyclingAmount when ti.prepareRecycleAmount is not null then ti.prepareRecycleAmount else ti.amount end ) "
- + "end else ti.amount end end) end) - (case when (ti.transferScale is not null and ti.transferScale > 0) then ti.sendOutAmount/ti.transferScale else ti.sendOutAmount end)) "
- + "> 0 group by tousseName,td.tousseType ";
- return sql;
- }
- /**
- * 获取当天待审核的总包数sql
- * @param handleDepartCoding 一级供应室编码
- * @return
- */
- private String getToBeReviewAmountSql(String handleDepartCoding, String tousseTypesql){
- String sql = "select 'toBeReviewAmount' type, count(*) amount from "
- + TousseInstance.class.getSimpleName()
- +" po join "
- + TousseDefinition.class.getSimpleName()
- +" td on td.id=po.tousseDefinition_id WHERE po.status = '"
- + TousseInstance.STATUS_PACKED
- +"' AND po.orgUnitCoding = '"
- + handleDepartCoding
- +"' and comboTousseInstanceId is null "
- + tousseTypesql
- + "and (td.tousseType in ('"
- + TousseDefinition.PACKAGE_TYPE_INSIDE
- +"','"
- + TousseDefinition.PACKAGE_TYPE_DISINFECTION
- +"','"
- + TousseDefinition.PACKAGE_TYPE_DRESSING
- +"','"
- + TousseDefinition.PACKAGE_TYPE_COMBO
- +"','"
- + TousseDefinition.PACKAGE_TYPE_FOREIGNPROXY
- +"') ) and td.isReview='是' ";
- return sql;
- }
- /**
- * 获取当天待灭菌的总包数sql
- * @param handleDepartCoding 一级供应室编码
- * @param tousseTypesql 包类型过滤
- * @param toBeBetweenSql 时间过滤
- * @return
- */
- private String getToBeSterilizationAmountSql(String handleDepartCoding, String tousseTypesql, String toBeBetweenSql){
- String sql = "select 1 amount from "
- + ReviewedBasket.class.getSimpleName()
- +" po join "
- + TousseInstance.class.getSimpleName()
- +" ti on ti.reviewBasket_id=po.id join "
- + TousseDefinition.class.getSimpleName()
- +" td on ti.tousseDefinition_id=td.id join "
- + Container.class.getSimpleName()
- +" c on c.id=po.container_id left join sterilization_reviewed sr on sr.reviewedBasket_id=po.id left join "
- + SterilizationRecord.class.getSimpleName()
- +" s on s.id=sr.sterilizationRecord_id where s.id is null "
- + tousseTypesql
- + SqlUtils.buildFieldNameBetweenSql("ti.operationTime", toBeBetweenSql)
- + " and (c.purpose='"
- + Container.CONTAINER_PURPOSE_DISINFECTION
- +"'and c.status = '"
- + Container.CONTAINER_STATUS_STERILIZELOADING
- +"' or c.purpose='"
- + Container.CONTAINER_PURPOSE_VIRTUAL
- +"' and c.status = '"
- + Container.CONTAINER_STATUS_PACKING
- +"' and ti.status='"
- + TousseInstance.STATUS_REVIEWED
- +"' and not exists (select reviewBasket_id from TousseInstance where reviewBasket_id=ti.reviewBasket_id and status<>'"
- + TousseInstance.STATUS_REVIEWED
- +"' ) ) and (c.departCode = '"
- + handleDepartCoding
- +"' or po.id in ( select rbs.id from "
- + ProxyDisinfection.class.getSimpleName()
- +" pd join "
- + InvoicePlan.class.getSimpleName()
- +" ip on ip.id=pd.id join proxyDis_rBasket pr on pr.proxyDisinfection_id=pd.id join "
- + ReviewedBasket.class.getSimpleName()
- +" rbs on rbs.id=pr.reviewedBasket_id where ip.handleDepartCoding='"
- + handleDepartCoding
- +"')) union all select 1 amount from "
- + TousseInstance.class.getSimpleName()
- +" ti join "
- + TousseDefinition.class.getSimpleName()
- +" td on ti.tousseDefinition_id=td.id join "
- + TousseDefinition.class.getSimpleName()
- +" tdancestor on td.ancestorID=tdancestor.id left join "
- + InvoicePlan.class.getSimpleName()
- +" pd on pd.id=ti.proxyDisinfection_id where tdancestor.isTraceable='是' and ti.status='"
- + TousseInstance.STATUS_REVIEWED
- +"' "
- + tousseTypesql
- + "and ti.comboTousseInstanceId is null and ti.reviewBasket_id is null and (pd.id is null and ti.orgunitcoding='"
- +handleDepartCoding
- +"' or pd.id is not null and pd.handleDepartCoding='"
- + handleDepartCoding
- +"') and (ti.hasReceived is null or ti.hasReceived='是') ";
- return sql;
- }
- /**
- * 获取待装配的器械包数量sql
- * @param handleDepartCoding 一级供应室编码
- * @param tousseTypesql 包类型过滤
- * @param toBeBetweenSql 时间过滤
- * @return
- */
- private String getToBePackAmountSql(String handleDepartCoding, String tousseTypesql, String toBeBetweenSql){
- String packingSql = " select po.unPackAmount amount from "
- + PackingTask.class.getSimpleName()
- +" po join "
- + TousseDefinition.class.getSimpleName()
- +" td on td.id=po.tousseDefinition_id join "
- + TousseDefinition.class.getSimpleName()
- +" tdc on tdc.id=td.ancestorID where po.orgUnitCoding = '"
- + handleDepartCoding
- +"' "
- + tousseTypesql
- + SqlUtils.buildFieldNameBetweenSql("po.startTime", toBeBetweenSql)
- + "and (tdc.hidePackingTask is null or tdc.hidePackingTask<>'是') ";
- return packingSql;
- }
- /**
- * 获取待清洗的器械包数量sql
- * @param handleDepartCoding 一级供应室编码
- * @param tousseTypesql 包类型过滤
- * @param toBeBetweenSql 时间过滤
- * @return
- */
- private String getToBeWashAmountSql(String handleDepartCoding, String tousseTypeSql, String toBeBetweenSql, boolean excludeSecondaryData){
- String excludeSecondaryDataSql = null;
- if(excludeSecondaryData){
- excludeSecondaryDataSql = " and rr.recyclingTimes is null ";
- }else{
- excludeSecondaryDataSql = "";
- }
- String betweenSql = SqlUtils.buildFieldNameBetweenSql("rr.recyclingTime", toBeBetweenSql);
- String sql = " select min(ci.tousseAmountForMaterial) amount from "
- + ClassifyBasket.class.getSimpleName()
- +" cb,"
- + ClassifiedItem.class.getSimpleName()
- +" ci,"
- + TousseDefinition.class.getSimpleName()
- +" td,"
- + TousseDefinition.class.getSimpleName()
- +" tdc ,"
- + RecyclingRecord.class.getSimpleName()
- +" rr, "
- + Container.class.getSimpleName()
- +" c, "
- + BarcodeDevice.class.getSimpleName()
- +" bd "
- + " where ci.classifybasket_id = cb.id and td.id=ci.tousseDefinitionID and bd.id=c.id and bd.barcode=cb.containerBarcode and rr.id=ci.recyclingRecordId "
- + " and rr.orgUnitCoding='"+ handleDepartCoding +"' "
- + betweenSql
- + tousseTypeSql
- + excludeSecondaryDataSql
- + " and tdc.id=td.ancestorID and ci.itemType = '"
- + ClassifiedItem.TYPE_MATERIAL
- +"' and c.status='"+ Container.CONTAINER_STATUS_WASHLOADING +"' "
- + " and cb.id not in (select cw.ClassifyBasket_ID from ClassifyBasket_WashRecord cw ) "
- + " and cb.id not in (select classifyBasketId from PackingRecord_ClassifyBasket) "
- + " group by ci.tousseDefinitionID,ci.recyclingRecordId "
- + " union all "
- + " select (ci.amount-case when numOfUnwashedStops is null then 0 else numOfUnwashedStops end) amount from "
- + ClassifyBasket.class.getSimpleName()
- +" cb,"
- + ClassifiedItem.class.getSimpleName()
- +" ci,"
- + TousseDefinition.class.getSimpleName()
- +" td,"
- + TousseDefinition.class.getSimpleName()
- +" tdc,"
- + RecyclingRecord.class.getSimpleName()
- +" rr,"
- + Container.class.getSimpleName()
- +" c,"
- + BarcodeDevice.class.getSimpleName()
- +" bd where ci.classifybasket_id = cb.id and td.id=ci.tousseDefinitionID and bd.id=c.id and bd.barcode=cb.containerBarcode and rr.id=ci.recyclingRecordId "
- + betweenSql
- + tousseTypeSql
- + excludeSecondaryDataSql
- + " and c.status='"+ Container.CONTAINER_STATUS_WASHLOADING +"' and rr.orgUnitCoding='"+ handleDepartCoding +"' "
- + " and cb.id not in (select cw.ClassifyBasket_ID from ClassifyBasket_WashRecord cw ) "
- + " and cb.id not in (select classifyBasketId from PackingRecord_ClassifyBasket) "
- + " and tdc.id=td.ancestorID and ci.itemtype != '"
- + ClassifiedItem.TYPE_MATERIAL
- +"' ";
- return sql;
- }
- /**
- * 获取待回收的器械包数量sql
- * @param handleDepartCoding 一级供应室编码
- * @param tousseTypesql 包类型过滤
- * @param toBeBetweenSql 时间过滤
- * @param excludeSecondaryData 排除二次回收数据
- * @return
- */
- private String getToBeRecycledAmountSql(String handleDepartCoding, String tousseTypesql, String toBeBetweenSql, boolean excludeSecondaryData){
- String sql = " select sum(ti.amount-case when ti.recyclingAmount is null then 0 else ti.recyclingAmount end) amount from "
- + InvoicePlan.class.getSimpleName()
- + " po join "+
- TousseItem.class.getSimpleName()
- +" ti on ti.recyclingApplication_ID=po.id "
- + "left join TousseDefinition td on td.id=ti.tousseDefinitionId "
- + "where po.committedStatus=1 "
- + tousseTypesql;
- if(excludeSecondaryData){
- sql += " and po.type='"+ InvoicePlan.TYPE_FOREIGNTOUSSEAPPLIACTION +"' and po.recyclingStatus in('"
- + InvoicePlan.RECYCLINGSTATUS_AWAITRECEIVE
- +"','"
- + InvoicePlan.RECYCLINGSTATUS_PARTAWAITRECEIVE
- +"') ";
- }else{
- String [] recycleStatus = {
- InvoicePlan.RECYCLINGSTATUS_AWAITRECYCLE,
- InvoicePlan.RECYCLINGSTATUS_PARTRECYCLE,
- InvoicePlan.RECYCLINGSTATUS_AWAITRECEIVE,
- InvoicePlan.RECYCLINGSTATUS_PARTAWAITRECEIVE,
- InvoicePlan.RECYCLINGSTATUS_TALLIED
- };
- sql += " and po.recyclingStatus in('" + StringUtils.join(recycleStatus, "','") + "')";
- }
- sql += SqlUtils.buildFieldNameBetweenSql("po.applicationTime", toBeBetweenSql);
- sql += "and po.handleDepartCoding='"+ handleDepartCoding +"' and (po.includeRecyclingItems is null or po.includeRecyclingItems =1) ";
- sql += "and (ti.hideRecycling is null or ti.hideRecycling <> '是') and ti.amount>0 and (ti.amount>ti.recyclingAmount or ti.recyclingAmount is null) ";
- return sql;
- }
- /**
- * 获取待签收的器械包数量sql
- * @param handleDepartCoding 一级供应室编码
- * @param tousseTypesql 包类型过滤
- * @param toBeBetweenSql 时间过滤
- * @return
- */
- private String getToBeSignAmountSql(String handleDepartCoding, String tousseTypesql, String toBeBetweenSql){
- String sql = " select 1 amount from "
- + TousseInstance.class.getSimpleName()
- +" ti join "
- + TousseDefinition.class.getSimpleName()
- +" td on td.id=ti.tousseDefinition_id join "
- + Invoice.class.getSimpleName()
- +" i on i.id=ti.invoice_id "
- + "where ti.status='"
- + TousseInstance.STATUS_SHIPPED
- +"' "
- + SqlUtils.buildFieldNameBetweenSql("i.sendTime", toBeBetweenSql)
- + tousseTypesql;
- return sql;
- }
+
+
+
@Override
public JSONObject getRealTimeBulletinBoardWorkloadData() {
- //获取当天开始时间
- SupplyRoomConfig systemParamsObj = supplyRoomConfigManager.getSystemParamsObj();
- SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
- Date today = new Date();
- String dateStr = sdf.format(today);
- String startDate = "";
- if(systemParamsObj != null && StringUtils.isNotBlank(systemParamsObj.getDayStartTime())){
- startDate += dateStr + " " + systemParamsObj.getDayStartTime() + ":00";
- }else{
- startDate += dateStr + " 00:00:00";
- }
- String endDate = dateStr + " 23:59:59";
- String betweenSql = " between "+ dateQueryAdapter.dateAdapter(startDate) + " and "
- + dateQueryAdapter.dateAdapter(endDate);
- String handleDepartCoding = supplyRoomConfigManager.getFirstSupplyRoomConfig().getOrgUnitCoding();
- String tousseType = "外来器械包,外来器械拆分小包";
- String tousseTypesql = SqlUtils.getWhereSqlByfilterFieldAndStringValueAndSeparator("td.tousseType", tousseType, ",");
- ReportQueryParams foreignParams = new ReportQueryParams();
- foreignParams.haveCombo = false;
- foreignParams.betweenSql = betweenSql;
- foreignParams.querySupplyRoom = handleDepartCoding;
- foreignParams.tousseTypes = tousseType;
- foreignParams.tousseTypeAndPackageSizeSql = tousseTypesql;
-
- ReportQueryParams params = new ReportQueryParams();
- params.haveCombo = false;
- params.betweenSql = betweenSql;
- params.querySupplyRoom = handleDepartCoding;
- String sql = "";
- String toBeBetweenSql = "";
- Integer dashboardsQueryCycle = null;
- if(systemParamsObj.getDashboardsQueryCycle() == null){
- dashboardsQueryCycle = 7;
- }else{
- dashboardsQueryCycle = systemParamsObj.getDashboardsQueryCycle();
- }
- if(dashboardsQueryCycle != 0){
- Calendar calendar = Calendar.getInstance();
- calendar.setTime(today);
- calendar.add(Calendar.DAY_OF_MONTH, -dashboardsQueryCycle);
- String toBeStartDate = sdf.format(calendar.getTime()) + " 00:00:00";
- String toBeEndDate = sdf.format(today) + " 23:59:59";
- toBeBetweenSql = " between "+ dateQueryAdapter.dateAdapter(toBeStartDate) + " and "
- + dateQueryAdapter.dateAdapter(toBeEndDate);
- }
- //外来器械申请数量
- String foreignApplyAmountSql = String.format("select 'foreignApplyAmount' type,sum(amount) amount,0 urgentAmount from ("
- +dataIndex.getWorkAmountByPackageSQL("申请数量", foreignParams)
- + ") tl");
- sql += foreignApplyAmountSql;
- //申请数量
- String applyAmountSql = String.format("select 'applyAmount' type,sum(amount) amount,0 urgentAmount from ("
- +dataIndex.getWorkAmountByPackageSQL("申请数量", params)
- + ") tl");
- sql += " union all " + applyAmountSql;
- //外来器械当天待回收的包数量
- String foreignToBeRecycledAmountSql = "select 'foreignToBeRecycledAmount' type,sum(amount) amount,0 urgentAmount from ( "
- + getToBeRecycledAmountSql(handleDepartCoding, tousseTypesql, toBeBetweenSql, true) + ") tb ";
- sql += " union all " + foreignToBeRecycledAmountSql;
- //外来器械当天已回收包数量
- foreignParams.extraQuery = " and rr.recyclingTimes is null ";
- String foreignRecycledAmountSql = String.format("select 'foreignRecycledAmount' type,sum(amount) amount,0 urgentAmount from ("
- +dataIndex.getWorkAmountByPackageSQL("回收数量", foreignParams)
- + ") tl");
- sql += " union all " + foreignRecycledAmountSql;
- foreignParams.extraQuery = "";
- //当天待回收的包数量
- String toBeRecycledAmountSql = "select 'toBeRecycledAmount' type,sum(amount) amount,0 urgentAmount from ( "
- + getToBeRecycledAmountSql(handleDepartCoding, "", toBeBetweenSql, false) + ") tb ";
- sql += " union all " + toBeRecycledAmountSql;
- //当天已回收包数量
- String recycledAmountSql = String.format("select 'recycledAmount' type,sum(amount) amount,0 urgentAmount from ("
- +dataIndex.getWorkAmountByPackageSQL("回收数量", params)
- + ") tl");
- sql += " union all " + recycledAmountSql;
- //当天已清点器械总包数
- String inventoryEdAmountSql = String.format("select 'inventoryEdAmount' type,sum(amount) amount,0 urgentAmount from ("
- +dataIndex.getWorkAmountByPackageSQL("清点数量", params)
- + ") tl ");
- sql += " union all " + inventoryEdAmountSql;
- //外来器械当天待清洗器械总包数
- String foreignToBeWashAmountSql = " select 'foreignTobeWashAmount' type,sum(amount) amount,0 urgentAmount from ( "
- + getToBeWashAmountSql(handleDepartCoding, tousseTypesql, toBeBetweenSql, true) + ") tb ";
- sql += " union all " + foreignToBeWashAmountSql;
- //外来器械当天已清洗器械总包数
- foreignParams.extraQuery = " and ci.isSencondWashForForeignTousse=0 ";
- String foreignWashedAmountSql = String.format("select 'foreignWashedAmount' type,sum(amount) amount,0 urgentAmount from ("
- +dataIndex.getWorkAmountByPackageSQL("清洗数量", foreignParams)
- + ") tl ");
- sql += " union all " + foreignWashedAmountSql;
- foreignParams.extraQuery = "";
- //当天待清洗器械总包数
- String toBeWashAmountSql = " select 'tobeWashAmount' type,sum(amount) amount,0 urgentAmount from ( "
- + getToBeWashAmountSql(handleDepartCoding, "", toBeBetweenSql, false) + ") tb ";
- sql += " union all " + toBeWashAmountSql;
- //当天已清洗器械总包数
- String washedAmountSql = String.format("select 'washedAmount' type,sum(amount) amount,0 urgentAmount from ("
- +dataIndex.getWorkAmountByPackageSQL("清洗数量", params)
- + ") tl ");
- sql += " union all " + washedAmountSql;
- //外来器械当天待装配的总包数
- String foreignToBePackAmountSql = " select 'foreignToBePackAmount' type,sum(amount) amount,0 urgentAmount from ( "
- + getToBePackAmountSql(handleDepartCoding, tousseTypesql, toBeBetweenSql) + ") tb ";
- sql += " union all " + foreignToBePackAmountSql;
- //外来器械当天已装配总包数
- String foreignPackedAmountSql = String.format("select 'foreignPackedAmount' type,sum(amount) amount,0 urgentAmount from ("
- +dataIndex.getWorkAmountByPackageSQL("配包数量", foreignParams)
- + ") tl ");
- sql += " union all " + foreignPackedAmountSql;
- //当天待装配的总包数
- String toBePackAmountSql = " select 'toBePackAmount' type,sum(amount) amount,0 urgentAmount from ( "
- + getToBePackAmountSql(handleDepartCoding, "", toBeBetweenSql) + ") tb ";
- sql += " union all " + toBePackAmountSql;
- //当天已装配总包数
- String packedAmountSql = String.format("select 'packedAmount' type,sum(amount) amount,0 urgentAmount from ("
- +dataIndex.getWorkAmountByPackageSQL("配包数量", params)
- + ") tl ");
- sql += " union all " + packedAmountSql;
- //当天已审核的总包数
- String reviewedAmountSql = String.format("select 'reviewedAmount' type,sum(amount) amount,0 urgentAmount from ("
- +dataIndex.getWorkAmountByPackageSQL("审核数量", params)
- + ") tl ");
- sql += " union all " + reviewedAmountSql;
- //外来器械当天待灭菌的总包数
- String foreignToBeSterilizationAmountSql = " select 'foreignToBeSterilizationAmount' type,sum(amount) amount,0 urgentAmount from ( "
- + getToBeSterilizationAmountSql(handleDepartCoding, tousseTypesql, toBeBetweenSql) + ") tb ";
- sql += " union all " + foreignToBeSterilizationAmountSql;
- //外来器械当天已灭菌的总包数
- String foreignSterilizationedAmountSql = String.format("select 'foreignSterilizationedAmount' type,sum(amount) amount,0 urgentAmount from ("
- +dataIndex.getWorkAmountByPackageSQL("灭菌数量", foreignParams)
- + ") tl ");
- sql += " union all " + foreignSterilizationedAmountSql;
- //当天待灭菌的总包数
- String toBeSterilizationAmountSql = " select 'toBeSterilizationAmount' type,sum(amount) amount,0 urgentAmount from ( "
- + getToBeSterilizationAmountSql(handleDepartCoding, "", toBeBetweenSql) + ") tb ";
- sql += " union all " + toBeSterilizationAmountSql;
- //当天已灭菌的总包数
- String sterilizationedAmountSql = String.format("select 'sterilizationedAmount' type,sum(amount) amount,0 urgentAmount from ("
- +dataIndex.getWorkAmountByPackageSQL("灭菌数量", params)
- + ") tl ");
- sql += " union all " + sterilizationedAmountSql;
- //外来器械当天待发货的总包数
- String foreignToBeInvoiceAmountSql = " select 'foreignToBeInvoiceAmount' type,sum(amount) amount,0 urgentAmount from ( "
- + getToBeInvoiceAmountSql(handleDepartCoding, tousseTypesql, toBeBetweenSql) + ") tb ";
- sql += " union all " + foreignToBeInvoiceAmountSql;
- //外来器械当天已发货的总包数
- String foreignInvoicedAmountSql = String.format("select 'foreignInvoicedAmount' type,sum(amount) amount,0 urgentAmount from ("
- +dataIndex.getWorkAmountByPackageSQL("发货数量", foreignParams)
- + ") tl ");
- sql += " union all " + foreignInvoicedAmountSql;
- //当天待发货的总包数
- String toBeInvoiceAmountSql = " select 'toBeInvoiceAmount' type,sum(amount) amount,sum(urgentAmount) urgentAmount from ( "
- + getToBeInvoiceAmountSql(handleDepartCoding, "", toBeBetweenSql) + ") tb ";
- sql += " union all " + toBeInvoiceAmountSql;
- //当天已发货的总包数
- String invoicedAmountSql = String.format("select 'invoicedAmount' type,sum(amount) amount,0 urgentAmount from ("
- +dataIndex.getWorkAmountByPackageSQL("发货数量", params)
- + ") tl ");
- sql += " union all " + invoicedAmountSql;
- //外来器械当天待签收的包数
- String foreignToBeSignAmountSql = " select 'foreignToBeSignAmount' type,sum(amount) amount,0 urgentAmount from ( "
- + getToBeSignAmountSql(handleDepartCoding, tousseTypesql, toBeBetweenSql) + ") tb ";
- sql += " union all " + foreignToBeSignAmountSql;
- //外来器械当天已签收的包数
- String foreignSignedAmountSql = String.format("select 'foreignSignedAmount' type,sum(amount) amount,0 urgentAmount from ("
- +dataIndex.getWorkAmountByPackageSQL("签收数量", foreignParams)
- + ") tl ");
- sql += " union all " + foreignSignedAmountSql;
- //当天已签收的包数
- String signedAmountSql = String.format("select 'signedAmount' type,sum(amount) amount,0 urgentAmount from ("
- +dataIndex.getWorkAmountByPackageSQL("签收数量", params)
- + ") tl ");
- sql += " union all " + signedAmountSql;
- //当天已处理加急物品的总包数
- String invoicedUrgentAmountSql = " select 'invoicedUrgentAmount' type,sum(amount) amount,0 urgentAmount from ( "
- + getInvoicedUrgentAmountSql(handleDepartCoding, betweenSql) + ") tb ";
- sql += " union all " + invoicedUrgentAmountSql;
- ResultSet rs = null;
- JSONObject obj = new JSONObject();
- try {
- rs = objectDao.executeSql(sql);
- while (rs.next()) {
- String type = rs.getString("type");
- if("toBeInvoiceAmount".equals(type)){
- obj.put("toBeInvoiceUrgentAmount", rs.getInt("urgentAmount"));//待处理加急数量
- }
- obj.put(type, rs.getInt("amount"));
- }
- } catch (Exception e) {
- e.printStackTrace();
- } finally {
- DatabaseUtil.closeResultSetAndStatement(rs);
- }
- return obj;
+ return realTimeBulletinBoardWorkloadHelper.getRealTimeBulletinBoardWorkloadData();
}
- /**
- * 获取当天已处理加急物品的总包数sql
- * @param handleDepartCoding 一级供应室编码
- * @param betweenSql 时间过滤
- * @return
- */
- private String getInvoicedUrgentAmountSql(String handleDepartCoding, String betweenSql){
- String sql = "select count(*) amount from "
- + TousseInstance.class.getSimpleName()
- + " ti join "
- + Invoice.class.getSimpleName()
- +" i on i.id=ti.invoice_id where ti.urgentLevel_id is not null and i.orgUnitCoding='"
- + handleDepartCoding
- +"' and i.sendTime "
- + betweenSql;
- return sql;
- }
+
@Override
public List getEquipmentData() {
String sql = " select status,dateStr,name from (select 1 orderNum,case when exists (select wr.id from "