1 package org.kuali.ole.select.document.service.impl;
2
3 import org.kuali.ole.OLEConstants;
4 import org.kuali.ole.gl.GeneralLedgerConstants;
5 import org.kuali.ole.gl.batch.service.RunDateService;
6 import org.kuali.ole.gl.businessobject.Entry;
7 import org.kuali.ole.gl.businessobject.OriginEntryFull;
8 import org.kuali.ole.select.document.service.OLEReEncumberRecurringOrdersJobService;
9 import org.kuali.ole.sys.context.SpringContext;
10 import org.kuali.rice.core.api.config.property.ConfigContext;
11 import org.kuali.rice.core.api.datetime.DateTimeService;
12 import org.kuali.rice.core.api.util.jaxb.KualiDecimalAdapter;
13 import org.kuali.rice.core.api.util.type.KualiDecimal;
14 import org.kuali.rice.core.framework.persistence.jdbc.dao.PlatformAwareDaoBaseJdbc;
15 import org.kuali.rice.coreservice.api.CoreServiceApiServiceLocator;
16 import org.kuali.rice.coreservice.api.parameter.Parameter;
17 import org.kuali.rice.coreservice.api.parameter.ParameterKey;
18
19 import java.io.BufferedWriter;
20 import java.io.File;
21 import java.io.FileWriter;
22 import java.math.BigDecimal;
23 import java.sql.Timestamp;
24 import java.util.ArrayList;
25 import java.util.Date;
26 import java.util.List;
27 import java.util.Map;
28
29
30
31
32 public class OLEReEncumberRecurringOrdersJobServiceImpl extends PlatformAwareDaoBaseJdbc implements OLEReEncumberRecurringOrdersJobService {
33
34 protected DateTimeService dateTimeService;
35 protected RunDateService runDateService;
36
37 @Override
38 public void retrieveReEncumberRecuringOrders() {
39
40 String paramaterValue = getParameter(OLEConstants.REENCUMBER_RECURRING_ORDERS);
41 java.sql.Date today = new java.sql.Date(getDateTimeService().getCurrentTimestamp().getTime());
42 String[] value = paramaterValue.split(",");
43 List<String> entriesList = new ArrayList<>();
44 List<Map<String, Object>> poItemData = new ArrayList<Map<String, Object>>();
45 if (value.length > 0 && value[0].equalsIgnoreCase(OLEConstants.PO)) {
46
47 String query = "select * from GL_ENTRY_T where FIN_BALANCE_TYP_CD='EX' and FDOC_NBR in (select FDOC_NBR from PUR_PO_T where PO_ID in (select PO_ID from AP_PMT_RQST_T where PO_ID in (select PO_ID from PUR_PO_T where RECUR_PMT_TYP_CD is not null)))";
48 poItemData = getSimpleJdbcTemplate().queryForList(query);
49 if (poItemData.size() > 0) {
50 try {
51 entriesList.clear();
52 for (Map<String, Object> poDataMap : poItemData) {
53
54 Entry entry = new Entry();
55 String poId = (String)poDataMap.get(OLEConstants.DOC_NBR);
56 String er = "SELECT OLE_EXCHANGE_RT FROM OLE_EXCHANGE_RT_T where OLE_CURR_TYP_ID = (select OLE_CURR_TYP_ID from PUR_VNDR_DTL_T where VNDR_HDR_GNRTD_ID = (select VNDR_HDR_GNRTD_ID from PUR_PO_T where PO_ID ="+ poId+"))";
57 List<Map<String, Object>> vendorMap = new ArrayList<Map<String, Object>>();
58 vendorMap = getSimpleJdbcTemplate().queryForList(er);
59 BigDecimal exchangeRate = (BigDecimal)vendorMap.get(0).get(OLEConstants.EXC_RATE);
60 entry.setUniversityFiscalYear(Integer.parseInt(poDataMap.get(OLEConstants.GL_UNIV_FIS_YR).toString()));
61 entry.setChartOfAccountsCode(poDataMap.get(OLEConstants.GL_CHART_CD).toString());
62 entry.setAccountNumber(poDataMap.get(OLEConstants.GL_ACCOUNT_NBR).toString());
63 entry.setSubAccountNumber(poDataMap.get(OLEConstants.GL_SUB_ACCT_NBR).toString());
64 entry.setFinancialObjectCode(poDataMap.get(OLEConstants.GL_OBJ_CD).toString());
65 entry.setFinancialSubObjectCode(poDataMap.get(OLEConstants.GL_SUB_OBJ_CD).toString());
66 entry.setFinancialBalanceTypeCode(poDataMap.get(OLEConstants.GL_BAL_TYP_CD).toString());
67 entry.setFinancialObjectTypeCode(poDataMap.get(OLEConstants.GL_OBJ_TYP_CD).toString());
68 entry.setUniversityFiscalPeriodCode(poDataMap.get(OLEConstants.GL_UNIV_FISC_PERIOD_CD).toString());
69 entry.setFinancialDocumentTypeCode(poDataMap.get(OLEConstants.GL_FIN_DOC_TYP_CD).toString());
70 entry.setFinancialSystemOriginationCode(poDataMap.get(OLEConstants.GL_FIN_SYS_ORG_CD).toString());
71 entry.setDocumentNumber(poDataMap.get(OLEConstants.GL_DOC_NBR).toString());
72 entry.setTransactionLedgerEntrySequenceNumber(Integer.parseInt(poDataMap.get(OLEConstants.TRANS_LED_SEQ_NO).toString()));
73 entry.setTransactionLedgerEntryDescription(poDataMap.get(OLEConstants.GL_TRANS_LED_ENTRY_DESC).toString());
74 KualiDecimal transactionLedgerEntryAmount = new KualiDecimal((BigDecimal) poDataMap.get(OLEConstants.GL_TRANS_LED_ENTRY_AMT));
75 transactionLedgerEntryAmount = transactionLedgerEntryAmount.multiply(new KualiDecimal(exchangeRate));
76 if (paramaterValue.contains("+")) {
77 String amount = paramaterValue.replaceAll("[^0-9]", "");
78 KualiDecimalAdapter kualiDecimalAdapter = new KualiDecimalAdapter();
79 KualiDecimal dollar = kualiDecimalAdapter.unmarshal(amount);
80 if (paramaterValue.contains("$")) {
81 transactionLedgerEntryAmount = transactionLedgerEntryAmount.add(dollar);
82 } else {
83 transactionLedgerEntryAmount = transactionLedgerEntryAmount.add((transactionLedgerEntryAmount.multiply(dollar)).divide(new KualiDecimal(100)));
84 }
85 } else {
86 String amount = paramaterValue.replaceAll("[^0-9]", "");
87 KualiDecimalAdapter kualiDecimalAdapter = new KualiDecimalAdapter();
88 KualiDecimal dollar = kualiDecimalAdapter.unmarshal(amount);
89 if (paramaterValue.contains("$")) {
90 transactionLedgerEntryAmount = transactionLedgerEntryAmount.subtract(dollar);
91 } else {
92 transactionLedgerEntryAmount = transactionLedgerEntryAmount.subtract((transactionLedgerEntryAmount.multiply(dollar)).divide(new KualiDecimal(100)));
93 }
94 }
95 entry.setTransactionLedgerEntryAmount(transactionLedgerEntryAmount);
96 entry.setTransactionDebitCreditCode(poDataMap.get(OLEConstants.GL_TRANS_DEB_CRE_CD).toString());
97 entry.setTransactionDate(today);
98 OriginEntryFull originEntryFullentry = new OriginEntryFull(entry);
99 entriesList.add(originEntryFullentry.getLine());
100 }
101 String fileDirectory = ConfigContext.getCurrentContextConfig().getProperty(org.kuali.ole.OLEConstants.STAGING_DIRECTORY) + OLEConstants.REENCUMBER_FILE_DIRECTORY;
102 new File(fileDirectory).mkdir();
103 Date runDate = calculateRunDate(getDateTimeService().getCurrentDate());
104 String filePath = fileDirectory +OLEConstants.REENCUMBER_FILE_PATH+runDate+ GeneralLedgerConstants.BatchFileSystem.EXTENSION;
105 File file = new File(filePath);
106 file.createNewFile();
107 BufferedWriter bwr = new BufferedWriter(new FileWriter(file));
108 if (entriesList != null && entriesList.size() > 0) {
109 for (String entry : entriesList) {
110 bwr.write(entry);
111 bwr.write("\n");
112 }
113 }
114 bwr.flush();
115 bwr.close();
116 } catch (Exception e) {
117 e.printStackTrace();
118 }
119 }
120
121 } else if (value.length > 0 && value[0].equalsIgnoreCase(OLEConstants.INVOICE)) {
122 List<Map<String, Object>> invoiceItemData = new ArrayList<Map<String, Object>>();
123 String query = "SELECT GE1.FDOC_REF_NBR,GE1.UNIV_FISCAL_YR,GE1.FIN_COA_CD,GE1.ACCOUNT_NBR,GE1.SUB_ACCT_NBR,GE1.FIN_OBJECT_CD,GE1.FIN_SUB_OBJ_CD,GE1.FIN_BALANCE_TYP_CD,\n" +
124 "GE1.FIN_OBJ_TYP_CD,GE1.UNIV_FISCAL_PRD_CD,GE1.FDOC_TYP_CD,GE1.FS_ORIGIN_CD,GE1.TRN_ENTR_SEQ_NBR,GE1.TRN_LDGR_ENTR_DESC,GE1.TRN_DEBIT_CRDT_CD,\n" +
125 "SUM(GE2.AMT) amt FROM GL_ENTRY_T GE1,(SELECT (SELECT FDOC_NBR FROM PUR_PO_T POT WHERE POT.PO_ID=GE.FDOC_REF_NBR) AS GEFDOC_REF_NBR,\n" +
126 "SUM(TRN_LDGR_ENTR_AMT) AS AMT,GE.ACCOUNT_NBR AS ACCNBR,GE.SUB_ACCT_NBR AS SUBACCNBR,GE.FIN_OBJECT_CD AS OBJCD FROM GL_ENTRY_T GE,AP_PMT_RQST_T PREQ,PUR_PO_T PO WHERE GE.FIN_BALANCE_TYP_CD='AC' AND GE.TRN_DEBIT_CRDT_CD='D' \n" +
127 "AND GE.FDOC_NBR=PREQ.FDOC_NBR AND PO.PO_ID=PREQ.PO_ID AND PO.RECUR_PMT_TYP_CD IS NOT NULL GROUP BY PREQ.PO_ID,GE.FDOC_REF_NBR,GE.ACCOUNT_NBR,GE.SUB_ACCT_NBR,GE.FIN_OBJECT_CD) GE2 \n" +
128 "WHERE GE1.FDOC_NBR=GE2.GEFDOC_REF_NBR AND GE1.ACCOUNT_NBR=GE2.ACCNBR \n" +
129 "GROUP BY\n" +
130 "GE1.FDOC_REF_NBR,GE1.UNIV_FISCAL_YR,GE1.FIN_COA_CD,GE1.ACCOUNT_NBR,GE1.SUB_ACCT_NBR,GE1.FIN_OBJECT_CD,GE1.FIN_SUB_OBJ_CD,GE1.FIN_BALANCE_TYP_CD,\n" +
131 "GE1.FIN_OBJ_TYP_CD,GE1.UNIV_FISCAL_PRD_CD,GE1.FDOC_TYP_CD,GE1.FS_ORIGIN_CD,GE1.TRN_ENTR_SEQ_NBR,GE1.TRN_LDGR_ENTR_DESC,GE1.TRN_DEBIT_CRDT_CD\n" +
132 "ORDER BY GE1.ACCOUNT_NBR,GE1.SUB_ACCT_NBR,GE1.FIN_OBJECT_CD";
133
134
135 invoiceItemData = getSimpleJdbcTemplate().queryForList(query);
136 if (invoiceItemData.size() > 0) {
137 try {
138 entriesList.clear();
139 for (Map<String, Object> invoiceDataMap : invoiceItemData) {
140
141 Entry entry = new Entry();
142 String poId = (String)invoiceDataMap.get(OLEConstants.DOC_NBR);
143 String er = "SELECT OLE_EXCHANGE_RT FROM OLE_EXCHANGE_RT_T where OLE_CURR_TYP_ID = (select OLE_CURR_TYP_ID from PUR_VNDR_DTL_T where VNDR_HDR_GNRTD_ID = (select distinct VNDR_HDR_GNRTD_ID from AP_PMT_RQST_T where PO_ID ="+ poId+"))";
144 List<Map<String, Object>> vendorMap = new ArrayList<Map<String, Object>>();
145 vendorMap = getSimpleJdbcTemplate().queryForList(er);
146 BigDecimal exchangeRate = (BigDecimal)vendorMap.get(0).get(OLEConstants.EXC_RATE);
147 entry.setUniversityFiscalYear(Integer.parseInt(invoiceDataMap.get(OLEConstants.GL_UNIV_FIS_YR).toString()));
148 entry.setChartOfAccountsCode(invoiceDataMap.get(OLEConstants.GL_CHART_CD).toString());
149 entry.setAccountNumber(invoiceDataMap.get(OLEConstants.GL_ACCOUNT_NBR).toString());
150 entry.setSubAccountNumber(invoiceDataMap.get(OLEConstants.GL_SUB_ACCT_NBR).toString());
151 entry.setFinancialObjectCode(invoiceDataMap.get(OLEConstants.GL_OBJ_CD).toString());
152 entry.setFinancialSubObjectCode(invoiceDataMap.get(OLEConstants.GL_SUB_OBJ_CD).toString());
153 entry.setFinancialBalanceTypeCode(invoiceDataMap.get(OLEConstants.GL_BAL_TYP_CD).toString());
154 entry.setFinancialObjectTypeCode(invoiceDataMap.get(OLEConstants.GL_OBJ_TYP_CD).toString());
155 entry.setUniversityFiscalPeriodCode(invoiceDataMap.get(OLEConstants.GL_UNIV_FISC_PERIOD_CD).toString());
156 entry.setFinancialDocumentTypeCode(invoiceDataMap.get(OLEConstants.GL_FIN_DOC_TYP_CD).toString());
157 entry.setFinancialSystemOriginationCode(invoiceDataMap.get(OLEConstants.GL_FIN_SYS_ORG_CD).toString());
158
159 entry.setTransactionLedgerEntrySequenceNumber(Integer.parseInt(invoiceDataMap.get(OLEConstants.TRANS_LED_SEQ_NO).toString()));
160 entry.setTransactionLedgerEntryDescription(invoiceDataMap.get(OLEConstants.GL_TRANS_LED_ENTRY_DESC).toString());
161 KualiDecimal transactionLedgerEntryAmount = new KualiDecimal((BigDecimal) invoiceDataMap.get(OLEConstants.GL_TOTAL_INV_AMT));
162 transactionLedgerEntryAmount = transactionLedgerEntryAmount.multiply(new KualiDecimal(exchangeRate));
163 if (paramaterValue.contains("+")) {
164 String amount = paramaterValue.replaceAll("[^0-9]", "");
165 KualiDecimalAdapter kualiDecimalAdapter = new KualiDecimalAdapter();
166 KualiDecimal dollar = kualiDecimalAdapter.unmarshal(amount);
167 if (paramaterValue.contains("$")) {
168 transactionLedgerEntryAmount = transactionLedgerEntryAmount.add(dollar);
169 } else {
170 transactionLedgerEntryAmount = transactionLedgerEntryAmount.add((transactionLedgerEntryAmount.multiply(dollar)).divide(new KualiDecimal(100)));
171 }
172 } else {
173 String amount = paramaterValue.replaceAll("[^0-9]", "");
174 KualiDecimalAdapter kualiDecimalAdapter = new KualiDecimalAdapter();
175 KualiDecimal dollar = kualiDecimalAdapter.unmarshal(amount);
176 if (paramaterValue.contains("$")) {
177 transactionLedgerEntryAmount = transactionLedgerEntryAmount.subtract(dollar);
178 } else {
179 transactionLedgerEntryAmount = transactionLedgerEntryAmount.subtract((transactionLedgerEntryAmount.multiply(dollar)).divide(new KualiDecimal(100)));
180 }
181 }
182
183 entry.setTransactionLedgerEntryAmount(transactionLedgerEntryAmount);
184 entry.setTransactionDebitCreditCode(invoiceDataMap.get(OLEConstants.GL_TRANS_DEB_CRE_CD).toString());
185 entry.setTransactionDate(today);
186
187 OriginEntryFull originEntryFullentry = new OriginEntryFull(entry);
188 entriesList.add(originEntryFullentry.getLine());
189
190 }
191 String fileDirectory = ConfigContext.getCurrentContextConfig().getProperty(org.kuali.ole.OLEConstants.STAGING_DIRECTORY) + OLEConstants.REENCUMBER_FILE_DIRECTORY;
192 new File(fileDirectory).mkdir();
193 Date runDate = calculateRunDate(getDateTimeService().getCurrentDate());
194 String filePath = fileDirectory + OLEConstants.REENCUMBER_FILE_PATH + runDate + GeneralLedgerConstants.BatchFileSystem.EXTENSION;
195 File file = new File(filePath);
196 file.createNewFile();
197 BufferedWriter bwr = new BufferedWriter(new FileWriter(file));
198 if (entriesList != null && entriesList.size() > 0) {
199 for (String entry : entriesList) {
200 bwr.write(entry);
201 bwr.write("\n");
202 }
203 }
204 bwr.flush();
205 bwr.close();
206 } catch (Exception e) {
207 e.printStackTrace();
208 }
209 }
210
211 }
212
213 }
214
215
216 public String getParameter(String name) {
217 ParameterKey parameterKey = ParameterKey.create(OLEConstants.APPL_ID_OLE, org.kuali.ole.OLEConstants.SELECT_NMSPC, org.kuali.ole.OLEConstants.SELECT_CMPNT, name);
218 Parameter parameter = CoreServiceApiServiceLocator.getParameterRepositoryService().getParameter(parameterKey);
219 return parameter != null ? parameter.getValue() : null;
220 }
221
222 public DateTimeService getDateTimeService() {
223 if(dateTimeService == null) {
224 return SpringContext.getBean(DateTimeService.class);
225 }
226 return dateTimeService;
227 }
228
229
230 public RunDateService getRunDateService() {
231 if(runDateService == null) {
232 return SpringContext.getBean(RunDateService.class);
233 }
234 return runDateService;
235 }
236
237 public java.sql.Date calculateRunDate(java.util.Date currentDate) {
238 return new java.sql.Date(getRunDateService().calculateRunDate(currentDate).getTime());
239 }
240 }
241