001package org.kuali.ole.select.document.service.impl; 002 003import org.apache.commons.lang.time.StopWatch; 004import org.kuali.ole.OLEConstants; 005import org.kuali.ole.deliver.service.DateFormatHelper; 006import org.kuali.ole.module.purap.PurapConstants; 007import org.kuali.ole.select.OleSelectConstant; 008import org.kuali.ole.select.businessobject.OlePurchaseOrderItem; 009import org.kuali.ole.select.document.OlePurchaseOrderDocument; 010import org.kuali.ole.select.lookup.DocData; 011import org.kuali.ole.sys.OLEKeyConstants; 012import org.kuali.rice.core.api.config.property.ConfigContext; 013import org.kuali.rice.core.api.util.type.KualiDecimal; 014import org.kuali.rice.core.api.util.type.KualiInteger; 015import org.kuali.rice.core.framework.persistence.jdbc.dao.PlatformAwareDaoBaseJdbc; 016import org.kuali.rice.krad.util.GlobalVariables; 017 018import java.math.BigDecimal; 019import java.text.ParseException; 020import java.text.SimpleDateFormat; 021import java.util.*; 022 023/** 024 * Created by premkb on 9/7/15. 025 */ 026public class ReceivingQueueDAOServiceimpl extends PlatformAwareDaoBaseJdbc implements org.kuali.ole.select.document.service.ReceivingQueueDAOService{ 027 028 private static final org.apache.log4j.Logger LOG = org.apache.log4j.Logger.getLogger(ReceivingQueueDAOServiceimpl.class); 029 private String dbVendor = ConfigContext.getCurrentContextConfig().getProperty(OLEConstants.DB_VENDOR); 030 031 032 @Override 033 public List<Map<String, Object>> getPODetails(Map<String,Object> criteria) { 034 String query = "SELECT PO.FDOC_NBR AS FDOC_NBR,PO.PO_ID AS PO_ID,PO.VNDR_NM AS VNDR_NM ,DHR.DOC_HDR_STAT_CD AS DOC_HDR_STAT_CD,POITM.OLE_DOCUMENT_UUID as BIBID " + 035 ",PO.PO_CRTE_DT AS PO_CRTE_DT,POITM.PO_ITM_ID AS PO_ITM_ID,POITM.ITM_UNIT_PRC AS ITM_UNIT_PRC,POITM.ITM_ORD_QTY AS ITM_ORD_QTY,POITM.OLE_DNT_CLM AS OLE_DNT_CLM" + 036 ",POITM.ITM_DESC AS ITM_DESC,POITM.OLE_NUM_PRTS AS OLE_NUM_PRTS,POITM.OLE_CLM_DT AS OLE_CLM_DT " + 037 ",POITM.OLE_REQ_RCPT_STATUS_ID AS OLE_REQ_RCPT_STATUS_ID,POITM.OLE_FOR_UNT_CST AS OLE_FOR_UNT_CST" + 038 ",(SELECT OLE_REQ_RCPT_STATUS_CD FROM OLE_PUR_REQ_RCPT_STATUS_T WHERE OLE_REQ_RCPT_STATUS_ID=POITM.OLE_REQ_RCPT_STATUS_ID AND OLE_REQ_RCPT_STATUS_DOC_TYP='PO')AS REPSTATCD " + 039 ",POITM.OLE_NO_COPIES_RCVD AS OLE_NO_COPIES_RCVD,POITM.OLE_NO_PARTS_RCVD AS OLE_NO_PARTS_RCVD" + 040 ",(SELECT TITLE FROM OLE_E_RES_REC_T WHERE E_RES_REC_ID=COPY.E_RES_REC_ID)AS TITLE "+ 041 "FROM PUR_PO_T PO, KREW_DOC_HDR_T DHR,OLE_PUR_PO_TYP_T POTYP,PUR_PO_ITM_T POITM" + 042 ",(SELECT DISTINCT PO_ITM_ID,E_RES_REC_ID FROM OLE_COPY_T) COPY " + 043 "WHERE PO.FDOC_NBR=DHR.DOC_HDR_ID AND PO.OLE_PO_TYPE_ID=POTYP.OLE_PO_TYPE_ID AND PO.FDOC_NBR=POITM.FDOC_NBR AND COPY.PO_ITM_ID=POITM.PO_ITM_ID " + 044 "AND PO.FDOC_NBR NOT IN (SELECT NOTE.FDOC_NBR FROM OLE_PUR_PO_ITM_NTE_T NOTE,OLE_NTE_TYP_T NOTETYPE WHERE NOTETYPE.OLE_NTE_TYP_ID=NOTE.OLE_NTE_TYP_ID " + 045 "AND NOTETYPE.OLE_NTE_TYPE='Special Processing Instruction Note') " + 046 "AND PO.PO_ID NOT IN (SELECT PO_ID FROM PUR_RCVNG_LN_T RCV,KREW_DOC_HDR_T RDHR WHERE RCV.PO_ID=PO.PO_ID AND RCV.FDOC_NBR=RDHR.DOC_HDR_ID AND RDHR.DOC_HDR_STAT_CD NOT IN ('E','X','F')) " + 047 "AND POITM.ITM_TYP_CD='ITEM' " + 048 "AND PO.PO_CUR_IND='Y' " + 049 getQueryCriteriaString(criteria,"bibIds")+ 050 getQueryCriteriaString(criteria,"purchaseOrderNumber")+ 051 getQueryCriteriaString(criteria,"purchaseOrderStatus")+ 052 getQueryCriteriaString(criteria,"vendorName")+ 053 getQueryCriteriaString(criteria,"purchaseOrderType")+ 054 getQueryCriteriaString(criteria,"poCreateFromDate")+ 055 getQueryCriteriaString(criteria,"poCreateToDate")+ 056 getQueryCriteriaString(criteria,"claimFilter")+ 057 getQueryCriteriaString(criteria,"title")+ 058 getResultSetLimit(); 059 if (LOG.isInfoEnabled()) { 060 LOG.info("receiving climbing query ----->"+query); 061 } 062 return getSimpleJdbcTemplate().queryForList(query); 063 } 064 065 private String getQueryCriteriaString(Map<String,Object> criteria,String criteriaString){ 066 String queryCriteriaString = ""; 067 if((criteriaString.equals("bibIds")&&criteria.get("bibIds")!=null)){ 068 queryCriteriaString="AND POITM.OLE_DOCUMENT_UUID IN ('"+criteria.get("bibIds").toString()+"') "; 069 } 070 if(criteriaString.equals("purchaseOrderNumber")&&criteria.get("purchaseOrderNumber")!=null){ 071 queryCriteriaString="AND PO.PO_ID IN ('"+criteria.get("purchaseOrderNumber").toString().replaceAll(",","','")+"') "; 072 } 073 if(criteriaString.equals("purchaseOrderStatus")&&criteria.get("purchaseOrderStatus")!=null){ 074 queryCriteriaString="AND DHR.APP_DOC_STAT='"+criteria.get("purchaseOrderStatus").toString()+"' "; 075 }else if(criteriaString.equals("purchaseOrderStatus")){//To add app doc status only once to the query 076 queryCriteriaString="AND DHR.APP_DOC_STAT='"+ PurapConstants.PurchaseOrderStatuses.APPDOC_OPEN+"' "; 077 } 078 if(criteriaString.equals("vendorName")&&criteria.get("vendorName")!=null){ 079 queryCriteriaString="AND PO.VNDR_NM='"+criteria.get("vendorName").toString()+"' "; 080 } 081 if(criteriaString.equals("purchaseOrderType")&&criteria.get("purchaseOrderType")!=null){ 082 queryCriteriaString="AND PO.OLE_PO_TYPE_ID=(SELECT OLE_PO_TYPE_ID FROM OLE_PUR_PO_TYP_T WHERE OLE_PO_TYPE='"+criteria.get("purchaseOrderType").toString()+"') "; 083 } 084 if(criteriaString.equals("poCreateFromDate")&&criteria.get("poCreateFromDate")!=null){ 085 queryCriteriaString="AND PO.PO_CRTE_DT >=(DATE '"+criteria.get("poCreateFromDate").toString()+"') "; 086 } 087 if(criteriaString.equals("poCreateToDate")&&criteria.get("poCreateToDate")!=null){ 088 queryCriteriaString="AND PO.PO_CRTE_DT <=(DATE '"+criteria.get("poCreateToDate").toString()+"') "; 089 } 090 if(criteriaString.equals("claimFilter")&&criteria.get("claimFilter")!=null&&(boolean)criteria.get("claimFilter")){ 091 queryCriteriaString="AND POITM.OLE_CLM_DT <=(DATE '"+getFormattedCurrentDate()+"') AND POITM.OLE_DNT_CLM='N' "; 092 } 093 if(criteriaString.equals("title")&&criteria.get("title")!=null){ 094 queryCriteriaString=" AND COPY.E_RES_REC_ID IN (SELECT E_RES_REC_ID FROM OLE_E_RES_REC_T WHERE TITLE='"+criteria.get("title").toString()+"' ) "; 095 } 096 return queryCriteriaString; 097 } 098 099 private String getResultSetLimit(){ 100 101 String queryCriteriaString=""; 102 if(dbVendor.equals(OLEConstants.MYSQL)){ 103 queryCriteriaString=" LIMIT 1000"; 104 }else{ 105 queryCriteriaString=" AND ROWNUM <=1000"; 106 } 107 return queryCriteriaString; 108 } 109 @Override 110 public List<OlePurchaseOrderDocument> getPODocumentList(Map<String, Object> criteria) { 111 List<Map<String, Object>> resultSets=null; 112 StopWatch executeQueryTimeWatch = new StopWatch(); 113 executeQueryTimeWatch.start(); 114 resultSets=getPODetails(criteria); 115 executeQueryTimeWatch.stop(); 116 List<OlePurchaseOrderDocument> olePurchaseOrderDocumentList= new ArrayList<>(); 117 olePurchaseOrderDocumentList=buildPODocumentWithPOItem(resultSets); 118 return olePurchaseOrderDocumentList; 119 } 120 121 122 private List<OlePurchaseOrderDocument> buildPODocumentWithPOItem(List<Map<String, Object>> resultSets){ 123 List<OlePurchaseOrderDocument> olePurchaseOrderDocumentList=new ArrayList<>(); 124 //OlePurchaseOrderDocument olePurchaseOrderDocument=null; 125 StopWatch poBuildWatch = new StopWatch(); 126 poBuildWatch.start(); 127 for (Map<String, Object> resultSet:resultSets) { 128 OlePurchaseOrderDocument olePurchaseOrderDocument=new OlePurchaseOrderDocument(); 129 List<OlePurchaseOrderItem> olePurchaseOrderItemList=new ArrayList<>(); 130 olePurchaseOrderDocument.setDocumentNumber(resultSet.get("FDOC_NBR").toString()); 131 olePurchaseOrderDocument.setVendorName(resultSet.get("VNDR_NM").toString()); 132 olePurchaseOrderDocument.setPurapDocumentIdentifier(Integer.parseInt(resultSet.get("PO_ID").toString())); 133 OlePurchaseOrderItem olePurchaseOrderItem = new OlePurchaseOrderItem(); 134 olePurchaseOrderItem.setItemIdentifier(Integer.parseInt(resultSet.get("PO_ITM_ID").toString())); 135 if (resultSet.get("BIBID")!=null) {//For EResoruce bibid will be null 136 olePurchaseOrderItem.setItemTitleId(resultSet.get("BIBID").toString()); 137 olePurchaseOrderItem.setBibUUID(resultSet.get("BIBID").toString()); 138 } 139 if (resultSet.get("TITLE")!=null) { 140 DocData docData=new DocData(); 141 docData.setTitle(resultSet.get("TITLE").toString()); 142 olePurchaseOrderItem.setDocData(docData); 143 } 144 if (resultSet.get("ITM_DESC")!=null) { 145 olePurchaseOrderItem.setItemDescription(resultSet.get("ITM_DESC").toString()); 146 } 147 if (resultSet.get("OLE_DNT_CLM")!=null) { 148 olePurchaseOrderItem.setDoNotClaim(Boolean.parseBoolean(resultSet.get("OLE_DNT_CLM").toString())); 149 } 150 if (resultSet.get("ITM_UNIT_PRC")!=null) { 151 olePurchaseOrderItem.setItemUnitPrice(new BigDecimal(resultSet.get("ITM_UNIT_PRC").toString())); 152 } 153 if (resultSet.get("OLE_NUM_PRTS")!=null) { 154 olePurchaseOrderItem.setItemNoOfParts(new KualiInteger(new Double(resultSet.get("OLE_NUM_PRTS").toString()).intValue())); 155 } 156 if (resultSet.get("OLE_NO_COPIES_RCVD")!=null) { 157 olePurchaseOrderItem.setNoOfCopiesReceived(resultSet.get("OLE_NO_COPIES_RCVD").toString()); 158 } 159 if (resultSet.get("OLE_NO_PARTS_RCVD")!=null) { 160 olePurchaseOrderItem.setNoOfPartsReceived(resultSet.get("OLE_NO_PARTS_RCVD").toString()); 161 } 162 if(resultSet.get("OLE_REQ_RCPT_STATUS_ID")!=null){ 163 olePurchaseOrderItem.setReceiptStatusId(Integer.parseInt(resultSet.get("OLE_REQ_RCPT_STATUS_ID").toString())); 164 } 165 if(resultSet.get("OLE_FOR_UNT_CST")!=null){ 166 olePurchaseOrderItem.setItemForeignUnitCost(new KualiDecimal(new Double(resultSet.get("OLE_FOR_UNT_CST").toString()).intValue())); 167 } 168 if(resultSet.get("REPSTATCD")!=null){ 169 if(resultSet.get("REPSTATCD").toString().equals(org.kuali.ole.sys.OLEConstants.PO_RECEIPT_STATUS_FULLY_RECEIVED)){ 170 GlobalVariables.clear(); 171 GlobalVariables.getMessageMap().putInfo(OleSelectConstant.RECEIVING_QUEUE_SEARCH, 172 OLEKeyConstants.ERROR_NO_PURCHASEORDERS_FOUND_FOR_FULLY_RECEIVED); 173 } 174 } 175 olePurchaseOrderItem.setItemPoQty(new KualiInteger(new Double(resultSet.get("ITM_ORD_QTY").toString()).intValue())); 176 olePurchaseOrderItemList.add(olePurchaseOrderItem); 177 olePurchaseOrderDocument.setOlePurchaseOrderItemList(olePurchaseOrderItemList); 178 olePurchaseOrderDocument.setItems(olePurchaseOrderItemList); 179 olePurchaseOrderItem.setPurchaseOrder(olePurchaseOrderDocument); 180 olePurchaseOrderItem.setOlePurchaseOrderDocument(olePurchaseOrderDocument); 181 olePurchaseOrderDocumentList.add(olePurchaseOrderDocument); 182 } 183 poBuildWatch.stop(); 184 return olePurchaseOrderDocumentList; 185 } 186 187 private String getFormattedCurrentDate(){ 188 String currentDate=null; 189 SimpleDateFormat simpleDateFormat = null; 190 String outputDateFormat = "yyyy-MM-dd"; 191 simpleDateFormat = new SimpleDateFormat(outputDateFormat); 192 currentDate = simpleDateFormat.format(new Date()); 193 return currentDate; 194 } 195}