1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16 package org.kuali.ole.module.purap.document.dataaccess.impl;
17
18 import org.apache.ojb.broker.query.Criteria;
19 import org.apache.ojb.broker.query.QueryByCriteria;
20 import org.apache.ojb.broker.query.QueryFactory;
21 import org.apache.ojb.broker.query.ReportQueryByCriteria;
22 import org.kuali.ole.OLEConstants;
23 import org.kuali.ole.deliver.service.DateFormatHelper;
24 import org.kuali.ole.module.purap.PurapConstants;
25 import org.kuali.ole.module.purap.PurapPropertyConstants;
26 import org.kuali.ole.module.purap.businessobject.AutoClosePurchaseOrderView;
27 import org.kuali.ole.module.purap.businessobject.PurchaseOrderItem;
28 import org.kuali.ole.module.purap.document.PurchaseOrderDocument;
29 import org.kuali.ole.module.purap.document.dataaccess.PurchaseOrderDao;
30 import org.kuali.ole.sys.OLEPropertyConstants;
31 import org.kuali.rice.core.api.config.property.ConfigContext;
32 import org.kuali.rice.core.api.util.type.KualiDecimal;
33 import org.kuali.rice.core.framework.persistence.ojb.dao.PlatformAwareDaoBaseOjb;
34 import org.springframework.transaction.annotation.Transactional;
35
36 import java.sql.Date;
37 import java.text.SimpleDateFormat;
38 import java.util.ArrayList;
39 import java.util.Collection;
40 import java.util.List;
41
42
43
44
45 @Transactional
46 public class PurchaseOrderDaoOjb extends PlatformAwareDaoBaseOjb implements PurchaseOrderDao {
47 private static org.apache.log4j.Logger LOG = org.apache.log4j.Logger.getLogger(PurchaseOrderDaoOjb.class);
48
49 public Integer getPurchaseOrderIdForCurrentPurchaseOrderByRelatedDocId(Integer accountsPayablePurchasingDocumentLinkIdentifier) {
50 Criteria criteria = new Criteria();
51 criteria.addEqualTo("accountsPayablePurchasingDocumentLinkIdentifier", accountsPayablePurchasingDocumentLinkIdentifier);
52 criteria.addEqualTo(PurapPropertyConstants.PURCHASE_ORDER_CURRENT_INDICATOR, "Y");
53
54 Collection<PurchaseOrderDocument> poList = getPersistenceBrokerTemplate().getCollectionByQuery(new QueryByCriteria(PurchaseOrderDocument.class, criteria));
55 for (PurchaseOrderDocument purchaseOrderDocument : poList) {
56
57 return purchaseOrderDocument.getPurapDocumentIdentifier();
58 }
59
60 return null;
61 }
62
63 public PurchaseOrderDocument getCurrentPurchaseOrder(Integer id) {
64 Criteria criteria = new Criteria();
65 criteria.addEqualTo(PurapPropertyConstants.PURAP_DOC_ID, id);
66 criteria.addEqualTo(PurapPropertyConstants.PURCHASE_ORDER_CURRENT_INDICATOR, "Y");
67
68 return (PurchaseOrderDocument) getPersistenceBrokerTemplate().getObjectByQuery(new QueryByCriteria(PurchaseOrderDocument.class, criteria));
69 }
70
71
72
73
74
75 public String getDocumentNumberForPurchaseOrderId(Integer id) {
76 Criteria criteria = new Criteria();
77 criteria.addEqualTo(PurapPropertyConstants.PURAP_DOC_ID, id);
78
79 return getDocumentNumberUsingPurchaseOrderCriteria(criteria);
80 }
81
82
83
84
85 public String getDocumentNumberForCurrentPurchaseOrder(Integer id) {
86 Criteria criteria = new Criteria();
87 criteria.addEqualTo(PurapPropertyConstants.PURAP_DOC_ID, id);
88 criteria.addEqualTo(PurapPropertyConstants.PURCHASE_ORDER_CURRENT_INDICATOR, "Y");
89
90 return getDocumentNumberUsingPurchaseOrderCriteria(criteria);
91 }
92
93
94
95
96 public String getOldestPurchaseOrderDocumentNumber(Integer id) {
97 Criteria criteria = new Criteria();
98 criteria.addEqualTo(PurapPropertyConstants.PURAP_DOC_ID, id);
99 ReportQueryByCriteria rqbc = QueryFactory.newReportQuery(PurchaseOrderDocument.class, criteria);
100 rqbc.setAttributes(new String[]{OLEPropertyConstants.DOCUMENT_NUMBER});
101
102
103
104
105
106 rqbc.addOrderByDescending(OLEPropertyConstants.DOCUMENT_NUMBER);
107
108 String oldestDocumentNumber = null;
109 java.util.Iterator iter = getPersistenceBrokerTemplate().getReportQueryIteratorByQuery(rqbc);
110 while (iter.hasNext()) {
111 final Object[] results = (Object[]) iter.next();
112 oldestDocumentNumber = (String) results[0];
113 }
114
115 return oldestDocumentNumber;
116 }
117
118
119
120
121
122
123
124 protected String getDocumentNumberUsingPurchaseOrderCriteria(Criteria criteria) {
125 List<String> returnList = getDocumentNumbersUsingPurchaseOrderCriteria(criteria);
126
127 if (returnList.isEmpty()) {
128 return null;
129 }
130
131 if (returnList.size() > 1) {
132
133 String errorMsg = "Expected single document number for given criteria but multiple (at least 2) were returned";
134 LOG.error(errorMsg);
135 throw new RuntimeException();
136
137 } else {
138
139 return returnList.get(0);
140 }
141 }
142
143
144
145
146
147
148
149 protected List<String> getDocumentNumbersUsingPurchaseOrderCriteria(Criteria criteria) {
150 ReportQueryByCriteria rqbc = new ReportQueryByCriteria(PurchaseOrderDocument.class, criteria);
151 List<String> returnList = new ArrayList<String>();
152
153 rqbc.addOrderByAscending(OLEPropertyConstants.DOCUMENT_NUMBER);
154
155 List<PurchaseOrderDocument> poDocs = (List<PurchaseOrderDocument>) getPersistenceBrokerTemplate().getCollectionByQuery(rqbc);
156
157 for (PurchaseOrderDocument poDoc : poDocs) {
158 returnList.add(poDoc.getDocumentNumber());
159 }
160
161 return returnList;
162 }
163
164
165
166
167 public boolean itemExistsOnPurchaseOrder(Integer poItemLineNumber, String docNumber) {
168 boolean existsInPo = false;
169
170 Criteria criteria = new Criteria();
171 criteria.addEqualTo("documentNumber", docNumber);
172 criteria.addEqualTo("itemLineNumber", poItemLineNumber);
173
174 ReportQueryByCriteria rqbc = new ReportQueryByCriteria(PurchaseOrderItem.class, criteria);
175
176 rqbc.addOrderByAscending(OLEPropertyConstants.DOCUMENT_NUMBER);
177
178 List<PurchaseOrderItem> poItems = (List<PurchaseOrderItem>) getPersistenceBrokerTemplate().getCollectionByQuery(rqbc);
179 if (!poItems.isEmpty()) {
180 existsInPo = true;
181 }
182
183 return existsInPo;
184 }
185
186
187
188
189 public List<AutoClosePurchaseOrderView> getAllOpenPurchaseOrders(List<String> excludedVendorChoiceCodes) {
190 LOG.debug("getAllOpenPurchaseOrders() started");
191 Criteria criteria = new Criteria();
192 criteria.addIsNull(PurapPropertyConstants.RECURRING_PAYMENT_TYPE_CODE);
193 criteria.addEqualTo(PurapPropertyConstants.TOTAL_ENCUMBRANCE, new KualiDecimal(0));
194 criteria.addEqualTo(PurapPropertyConstants.PURCHASE_ORDER_CURRENT_INDICATOR, true);
195 for (String excludeCode : excludedVendorChoiceCodes) {
196 criteria.addNotEqualTo(PurapPropertyConstants.VENDOR_CHOICE_CODE, excludeCode);
197 }
198 QueryByCriteria qbc = new QueryByCriteria(AutoClosePurchaseOrderView.class, criteria);
199 if (LOG.isDebugEnabled()) {
200 LOG.debug("getAllOpenPurchaseOrders() Query criteria is " + criteria.toString());
201 }
202 List<AutoClosePurchaseOrderView> l = (List<AutoClosePurchaseOrderView>) getPersistenceBrokerTemplate().getCollectionByQuery(qbc);
203 LOG.debug("getAllOpenPurchaseOrders() ended.");
204 return l;
205 }
206
207
208
209
210 public List<AutoClosePurchaseOrderView> getAllOpenPurchaseOrders(List<String> excludedVendorChoiceCodes, java.sql.Date poCloseFromDate,Date poCloseToDate) {
211 LOG.debug("getAllOpenPurchaseOrders() started");
212 Criteria criteria = new Criteria();
213 criteria.addIsNull(PurapPropertyConstants.RECURRING_PAYMENT_TYPE_CODE);
214 criteria.addEqualTo(PurapPropertyConstants.TOTAL_ENCUMBRANCE, new KualiDecimal(0));
215 criteria.addEqualTo(PurapPropertyConstants.PURCHASE_ORDER_CURRENT_INDICATOR, true);
216
217
218
219 String dbVendor = getProperty("db.vendor");
220 if (dbVendor.equals("mysql")) {
221 if (poCloseFromDate != null) {
222 criteria.addGreaterOrEqualThan(PurapPropertyConstants.PO_CREATE_DATE, poCloseFromDate);
223 }
224 if (poCloseToDate != null) {
225 criteria.addLessOrEqualThan(PurapPropertyConstants.PO_CREATE_DATE, poCloseToDate);
226 }
227 }
228 else {
229 if (poCloseFromDate != null) {
230
231 SimpleDateFormat formatter=new SimpleDateFormat("MM/dd/yyyy");
232 String date = formatter.format(poCloseFromDate);
233 criteria.addGreaterOrEqualThan(PurapPropertyConstants.PO_CREATE_DATE, formatDateForOracle(date));
234
235 }
236 if (poCloseToDate != null) {
237
238 SimpleDateFormat formatter=new SimpleDateFormat("MM/dd/yyyy");
239 String date = formatter.format(poCloseToDate);
240 criteria.addLessOrEqualThan( PurapPropertyConstants.PO_CREATE_DATE ,formatDateForOracle(date));
241
242 }
243 }
244 for (String excludeCode : excludedVendorChoiceCodes) {
245 criteria.addNotEqualTo(PurapPropertyConstants.VENDOR_CHOICE_CODE, excludeCode);
246 }
247 QueryByCriteria qbc = new QueryByCriteria(AutoClosePurchaseOrderView.class, criteria);
248 if (LOG.isDebugEnabled()) {
249 LOG.debug("getAllOpenPurchaseOrders() Query criteria is " + criteria.toString());
250 }
251 List<AutoClosePurchaseOrderView> l = (List<AutoClosePurchaseOrderView>) getPersistenceBrokerTemplate().getCollectionByQuery(qbc);
252 LOG.debug("getAllOpenPurchaseOrders() ended.");
253 return l;
254 }
255
256
257
258
259 public List<AutoClosePurchaseOrderView> getAutoCloseRecurringPurchaseOrders(List<String> excludedVendorChoiceCodes) {
260 LOG.debug("getAutoCloseRecurringPurchaseOrders() started.");
261 Criteria criteria = new Criteria();
262 criteria.addNotNull(PurapPropertyConstants.RECURRING_PAYMENT_TYPE_CODE);
263
264
265 for (String excludeCode : excludedVendorChoiceCodes) {
266 criteria.addNotEqualTo(PurapPropertyConstants.VENDOR_CHOICE_CODE, excludeCode);
267 }
268 QueryByCriteria qbc = new QueryByCriteria(AutoClosePurchaseOrderView.class, criteria);
269 if (LOG.isDebugEnabled()) {
270 LOG.debug("getAutoCloseRecurringPurchaseOrders() Query criteria is " + criteria.toString());
271 }
272 List<AutoClosePurchaseOrderView> l = (List<AutoClosePurchaseOrderView>) getPersistenceBrokerTemplate().getCollectionByQuery(qbc);
273
274
275 LOG.debug("getAutoCloseRecurringPurchaseOrders() ended.");
276
277 return l;
278 }
279
280 public List<PurchaseOrderDocument> getPendingPurchaseOrdersForFaxing() {
281 LOG.debug("Getting pending purchase orders for faxing");
282 Criteria criteria = new Criteria();
283 QueryByCriteria qbc = new QueryByCriteria(PurchaseOrderDocument.class, criteria);
284 List<PurchaseOrderDocument> l = (List<PurchaseOrderDocument>) getPersistenceBrokerTemplate().getCollectionByQuery(qbc);
285
286 return l;
287 }
288
289 protected String getProperty(String property) {
290 return ConfigContext.getCurrentContextConfig().getProperty(property);
291 }
292
293 private String formatDateForOracle(String overdueNoticeToDate) {
294 String forOracle = DateFormatHelper.getInstance().generateDateStringsForOracle(overdueNoticeToDate);
295 return forOracle;
296 }
297 }