package com.gpc.server.customerstatement; //java imports import com.gpc.backofficecommon.JNDILocator; import com.gpc.backofficecommon.JNDILocatorConstants; import com.gpc.backofficecommon.constants.schema.*; import com.gpc.common.*; import com.gpc.common.constants.CustomerStatementConstants; import com.gpc.common.constants.refvalues.RefARStatementForm; import com.gpc.common.constants.refvalues.RefStatementFormat; import com.gpc.common.exception.ApplicationException; import com.gpc.common.util.EJBRemoteExceptionHelper; import com.gpc.common.util.StringUtils; import com.gpc.ejb.rpt.SBReport; import com.gpc.ejb.rpt.SBReportHome; import com.gpc.server.ServerApplicationContext; import com.gpc.server.util.JDBCUtil; import com.gpc.server.util.ReportPrinter; import com.gpc.server.util.ServerUtil; import com.gpc.tams.io.TsiFileConstants; import com.gpc.tams.io.TsoFileReader; import com.gpc.tams.io.TsoFileWriter; import com.gpc.valueobjects.customerstatement.*; import com.gpc.valueobjects.profile.StoreProfileVO; import com.gpc.valueobjects.report.FwoPrintSettings; import com.gpc.valueobjects.report.FwoReportRequest; import com.gpc.valueobjects.report.FwoReportResponse; import com.gpc.valueobjects.report.criteria.FwoQuestionAnswer; import com.gpc.valueobjects.report.criteria.FwoQuestionAnswerMap; import com.gpc.valueobjects.report.criteria.FwoReportCriteria; import com.gpc.common.constants.refvalues.RefStatementType; import net.sf.jasperreports.engine.JRException; import net.sf.jasperreports.engine.JasperManager; import org.apache.commons.io.FileUtils; import org.apache.log4j.Logger; import javax.ejb.CreateException; import java.io.*; import java.rmi.RemoteException; import java.sql.*; import java.util.Date; import java.util.Hashtable; import java.util.*; import java.util.Locale; public class StatementPrinter { private static final Logger logger = Logger.getLogger(StatementPrinter.class); private static final String UPDATE_CUSTOMER_STATEMENT_SQL = "UPDATE " + Sch_Tables.CUSTOMER_STATEMENT + " SET " + Sch_CustomerStatement.PRINTED + "=? " + "WHERE " +Sch_CustomerStatement.LOC + "=? " + " AND " + Sch_CustomerStatement.CUSTOMER_ID + "=? " + " AND " + Sch_CustomerStatement.STATEMENT_DATE + "=? " + " AND " + Sch_CustomerStatement.STATEMENT_TYPE + "=? "; private StatementListener statementListener; private static final String STATEMENT_TYPE = "STATEMENT_TYPE"; private static final String STATEMENT_DATE = "STATEMENT_DATE"; private static final String CUSTOMER_ID = "CUSTOMER_ID"; private static final String MESSAGE = "MESSAGE"; private static final String XEROX = "Xerox"; private static final String CUSTOMER_NUM = "CUSTOMER_NUM"; private static final String REF_LANGUAGE_CD = "REF_LANGUAGE_CD"; private static final String REF_COUNTRY_CD = "REF_COUNTRY_CD"; private static final String STATEMENT_EMAIL_ADDRESS = "STATEMENT_EMAIL_ADDRESS"; private static final String VIEW_ARCHIVED_STATEMENT = "VIEW_ARCHIVED_STATEMENT"; private static final String DEFAULT_LANGUAGE_CD = "EN"; private static final String DEFAULT_COUNTRY_CD = "US"; private static final String ALTROM_LOCATION = "ALTROM_LOCATION"; private static final String STATEMENT_LOGO_CA = "napaCA"; private static final String STATEMENT_LOGO_US = "napaUS"; private static final String STATEMENT_LOGO_ALTROM = "altromLogo"; private static final String REF_STATEMENT_TYPE_ID = "REF_STATEMENT_TYPE_ID"; private static final String PRINT_AND_EMAIL_CUSTOMER_STATEMENTS = "PRINT_AND_EMAIL_CUSTOMER_STATEMENTS"; private static File tamsDir = new File(TsiFileConstants.BASE_DIRECTORY, "tams"); private static File scriptDir = new File(tamsDir, "scripts"); private static final String OS_NAME = "os.name"; private boolean emailAndPrintStatements = false; public StatementPrinter(StatementListener listener) { this.statementListener = listener; } /** * @param statement * @param printer */ private void printStatement(Connection conn, Hashtable params, FwoPrintSettings prtSettings, final boolean isConsolidatedPastDue, final boolean isClosingStatement,final boolean emailInterimStatements) throws ApplicationException { int numOfCopies = 1; logger.info("printStatement()"); Integer noOfCopies = StatementsUtil.findNumberofCopiesPerCustomer( (Integer) params.get("loc"), (Integer) params.get("customerID")); String emailAndPrintStatementsValue = fetchStatementPrintAndEmail((Integer) params.get("CUSTOMER_NUM")); String statementType = fetchStatementType((Integer) params.get("CUSTOMER_NUM")); int statementTypeValue = Integer.parseInt(statementType); emailAndPrintStatements = shouldEmailOrPrintStatements(emailAndPrintStatementsValue, statementTypeValue); if (noOfCopies != null) { numOfCopies = 1 + noOfCopies.intValue(); } int arStatementFormID = ServerApplicationContext.getInstance() .getProfile(Profile.SERVER, ((Integer) params.get("loc")).intValue()).getARProfile() .getRefARStatementFormID().intValue(); final String statementEmailAddress = getParamValue(params, STATEMENT_EMAIL_ADDRESS); final String viewArchivedStatement = getParamValue(params, VIEW_ARCHIVED_STATEMENT); if (arStatementFormID == RefARStatementForm.NEW_FORM && StringUtils.isEmpty(statementEmailAddress)) { printPinFeedStatement(params, prtSettings, numOfCopies); } else if (arStatementFormID == RefARStatementForm.LASER_LETTERHEAD || arStatementFormID == RefARStatementForm.LASER_PLAIN_PAPER || arStatementFormID == RefARStatementForm.LASER_USER_DEFINED_LOGO || StringUtils.isNotEmpty(statementEmailAddress) || StringUtils.isNotEmpty(viewArchivedStatement)) { if (emailAndPrintStatements) { printLaserStatement(conn, params, prtSettings, numOfCopies, isConsolidatedPastDue, isClosingStatement, emailInterimStatements); } } } public boolean shouldEmailOrPrintStatements(String emailAndPrintValue, int statementTypeValue) { boolean emailAndPrintStatement = false; if (emailAndPrintValue.equalsIgnoreCase("Y") && statementTypeValue != RefStatementType.BALANCE_FORWARD && statementTypeValue != RefStatementType.OPEN_ITEM) { emailAndPrintStatement = true; } else { emailAndPrintStatement = false; } return emailAndPrintStatement; } private void printPinFeedStatement(Hashtable params, FwoPrintSettings prtSettings,int noOfCopies) { FwoReportRequest fwoReportRequest = new FwoReportRequest(); fwoReportRequest.setLOC((Integer)params.get("loc")); fwoReportRequest.setPrintSettings(prtSettings); fwoReportRequest.setReportObjectClass( "com.gpc.server.report.customerstatement.PinFeedPrePrintedStatementForm"); StoreProfileVO spvo = ApplicationContext.getInstance().getProfile(Profile.SERVER, ((Integer)params.get("loc")).intValue()).getStoreProfile(); Locale locale = new Locale(spvo.getRefLanguageCd(), spvo.getRefCountryCd()); fwoReportRequest.setLocale(locale); FwoReportCriteria fwoReportCriteria = new FwoReportCriteria(); fwoReportCriteria.setLOC((Integer)params.get("loc")); FwoQuestionAnswer fwoQACust = new FwoQuestionAnswer(); fwoQACust.setQuestionKey(CUSTOMER_ID); fwoQACust.setAnswerType(FwoQuestionAnswer.ANSWER_TYPE_SINGLE); fwoQACust.setDataType(FwoQuestionAnswer.DATA_TYPE_NUM); fwoQACust.setAnswerValues(params.get("customerID").toString()); FwoQuestionAnswer fwoQAStmtType = new FwoQuestionAnswer(); fwoQAStmtType.setQuestionKey(STATEMENT_TYPE); fwoQAStmtType.setAnswerType(FwoQuestionAnswer.ANSWER_TYPE_SINGLE); fwoQAStmtType.setDataType(FwoQuestionAnswer.DATA_TYPE_ALPHANUM); fwoQAStmtType.setAnswerValues(params.get("statementType").toString()); FwoQuestionAnswer fwoQAStmtDate = new FwoQuestionAnswer(); fwoQAStmtDate.setQuestionKey(STATEMENT_DATE); fwoQAStmtDate.setAnswerType(FwoQuestionAnswer.ANSWER_TYPE_SINGLE); fwoQAStmtDate.setDataType(FwoQuestionAnswer.DATA_TYPE_DATE); fwoQAStmtDate.setAnswerValues(params.get("statementDate").toString()); FwoQuestionAnswer fwoQAStmtMessage = new FwoQuestionAnswer(); fwoQAStmtMessage.setQuestionKey(MESSAGE); fwoQAStmtMessage.setAnswerType(FwoQuestionAnswer.ANSWER_TYPE_SINGLE); fwoQAStmtMessage.setDataType(FwoQuestionAnswer.DATA_TYPE_ALPHANUM); Object message = params.get("message"); fwoQAStmtMessage.setAnswerValues(message == null ? "" : message.toString()); FwoQuestionAnswerMap fwoQuestionAnswerMap = new FwoQuestionAnswerMap(); fwoQuestionAnswerMap.addQuestionAnswer(fwoQACust); fwoQuestionAnswerMap.addQuestionAnswer(fwoQAStmtType); fwoQuestionAnswerMap.addQuestionAnswer(fwoQAStmtDate); fwoQuestionAnswerMap.addQuestionAnswer(fwoQAStmtMessage); fwoReportCriteria.setQuestionAnswerMap(fwoQuestionAnswerMap); fwoReportRequest.setReportCriteria(fwoReportCriteria); JNDILocator jndiLocator = JNDILocator.getInstance(); SBReportHome sbRptHome = (SBReportHome) jndiLocator.getEJBHome(SBReportHome.class, JNDILocatorConstants.SBREPORT_RMISTR); SBReport sbReport = null; try { sbReport = sbRptHome.create(); FwoReportResponse response = sbReport.runReport(fwoReportRequest); for(int i = 0;i < noOfCopies ;i++ ) { sbReport.printFile(response.getServerPfFileParent(), response.getServerPfFileName(),prtSettings); } } catch (RemoteException re) { EJBRemoteExceptionHelper.HandleRemoteException(re); } catch (CreateException ce) { logger.error(ce, ce); } } private void printLaserStatement(Connection conn, Hashtable params, FwoPrintSettings prtSettings, int noOfCopies, final boolean isConsolidatedPastDue, final boolean isClosingStatement, final boolean emailInterimStatements) throws ApplicationException { InputStream jasperReport=null; //construct pdf file String baseFileName = null; final String statementEmailAddress = getParamValue(params, STATEMENT_EMAIL_ADDRESS); final String viewArchivedStatement = getParamValue(params, VIEW_ARCHIVED_STATEMENT); StoreProfileVO storeProfile = ApplicationContext.getInstance().getProfile(Profile.SERVER, ((Integer)params.get("loc")).intValue()).getStoreProfile(); boolean useEmailCustomer = (storeProfile.getUseEmailCustomer() != null && storeProfile.getUseEmailCustomer().equals(TsoConstant.GENERIC_Y)); if(useEmailCustomer && StringUtils.isNotEmpty(statementEmailAddress)) { baseFileName = storeProfile.getStoreNum() + "_" + params.get(CUSTOMER_NUM) + "_" + ((java.sql.Date) params.get("statementDate")).toString(); params.put("isEmailPDF", "Y"); params.put("printStoreNameOnStmt", Boolean.TRUE); } else { baseFileName = "Statement_" + params.get("customerID") + params.get("statementType") + "_" + ((java.sql.Date)params.get("statementDate")).toString(); params.put("isEmailPDF", "N"); } StoreProfileVO spvo = ApplicationContext.getInstance().getProfile(Profile.SERVER, ((Integer)params.get("loc")).intValue()).getStoreProfile(); Locale locale = new Locale(spvo.getRefLanguageCd(), spvo.getRefCountryCd()); final Locale customerLocale = getCustomerLocale(params); Locale logoLocale = (customerLocale != null && !customerLocale.equals("")) ? customerLocale : locale; Integer logoLOC = (params.get("loc") != null) ? (Integer) params.get("loc") : 1; params.put("statementLogo",getStatementLogo(logoLocale,logoLOC)); String pdfFileName = baseFileName + ".pdf"; File pdfFile = new File(TsiFileConstants.TEMP_DIRECTORY, pdfFileName); if (pdfFile.exists()) { pdfFile.delete(); } JasperManager jm = new JasperManager(); try { if (isConsolidatedPastDue) { jasperReport = this.getClass().getClassLoader() .getResourceAsStream("CustomerStatement_ConsolidatedPastDue.jasper"); } else { jasperReport = this.getClass().getClassLoader().getResourceAsStream("CustomerStatement.jasper"); } jm.runReportToPdfStream(jasperReport, new FileOutputStream(pdfFile), params, conn); if (params.get("isEmailPDF").equals("Y") || emailInterimStatements) { if(pdfFile.exists()) { if (!isClosingStatement) { StatementsUtil.sendCustomerStatementEmail(pdfFile, statementEmailAddress, customerLocale); } else { copyPdfStatementToTempDirectory(pdfFile); } } } else if (StringUtils.isNotEmpty(viewArchivedStatement) && pdfFile.exists()) { renameArchivedStatemnetCopy(pdfFile); } else if(prtSettings!=null){ for(int i = 0;i < noOfCopies ;i++ ) { ReportPrinter.getInstance().printPdfFile(pdfFile, (Integer)params.get("loc"), prtSettings, "CustomerStatement"); } } else{ logger.error("Unable to find printer settings: " + params.get("customerID")); } if (!(((Boolean) params.get("PRINT_ARCHIVE")).booleanValue()) && ((String) params.get("statementType")).equals(CustomerStatementConstants.CLOSING)) { boolean rtnVal = updateCustomerStatementPrintedField(conn, (Integer) params.get("customerID"), (java.sql.Date) params.get("statementDate"), (Integer) params.get("loc")); if (!rtnVal) { logger.error("Unable to mark customer statement printed for customer: " + params.get("customerID")); } } } catch (JRException e) { logger.error("JRException e: "+e.toString(), e); throw new ApplicationException(e); } catch(IOException e) { logger.error("IOException: " + e.toString(), e); throw new ApplicationException(e); } catch (InterruptedException e) { logger.error("InterruptedException: " + e.toString(), e); throw new ApplicationException(e); } catch (SQLException e) { logger.error("SQLException: " + e.toString(), e); throw new ApplicationException(e); } finally { //delete the files after printed if (pdfFile.exists()) { pdfFile.delete(); } } } private void renameArchivedStatemnetCopy(File pdfFile) { File archivedStatementCopy = new File(TsiFileConstants.TEMP_DIRECTORY, TsiFileConstants.ARCHIVED_STATEMENT_COPY); if (archivedStatementCopy.exists()) { archivedStatementCopy.delete(); } pdfFile.renameTo(archivedStatementCopy); } private String getPrinterIP(Integer printerId, Integer loc)throws SQLException { String printerIP = null; Connection conn = null; Statement stmt = null; ResultSet rs = null; StringBuffer sql = new StringBuffer(); sql.append("SELECT IP_ADDRESS AS PRINTER_IP "); sql.append(" FROM PRINTER WHERE PRINTER.ID = ").append(printerId); sql.append(" AND PRINTER.LOC = ").append(loc); logger.info("SQL = " + sql.toString()); try { conn = ServerUtil.getConnection(); stmt = conn.createStatement(); rs = stmt.executeQuery(sql.toString()); if (rs.next()) { if (rs.getObject("PRINTER_IP") != null) { printerIP = rs.getString("PRINTER_IP"); } } } catch (SQLException e){ logger.error(e, e); throw e; } finally { JDBCUtil.closeResultSet(rs); JDBCUtil.closeStatement(stmt); ServerUtil.releaseConnection(conn); } return printerIP; } private boolean updateCustomerStatementPrintedField(Connection conn, Integer customerID, java.sql.Date statementDate, Integer loc) throws SQLException { PreparedStatement prepStmt = null; boolean rtnVal = false; try { prepStmt = conn.prepareStatement(UPDATE_CUSTOMER_STATEMENT_SQL); prepStmt.setString(1, TsoConstant.GENERIC_Y); prepStmt.setInt(2, loc.intValue()); prepStmt.setInt(3, customerID.intValue()); prepStmt.setDate(4, statementDate); prepStmt.setString(5, CustomerStatementConstants.CLOSING); int result = prepStmt.executeUpdate(); logger.debug(" result = "+result); rtnVal = true; // added this so we could at least get a log entry for any errors. } catch (Exception e){ logger.error(e, e); } finally { JDBCUtil.closePreparedStatement(prepStmt); return rtnVal; // this shouldn't be here, it will swallow any exceptions thrown, but I don't // want to risk changes to the calling logic. } } /** * @param request * @param printer */ public void printAllStatements(BaseStatementRequestVO request, FwoPrintSettings prtSettings, Integer processId) throws ApplicationException { logger.info("printAllStatements()"); Connection conn = null; Statement stmt = null; ResultSet rs = null; Hashtable htParams = null; String language = null; List customerWithActivityList = null; Integer mailProcessedCount=0; Integer totalRequestCount=0; boolean isClosingRequest = (request instanceof ClosingStatementRequestVO); boolean emailInterimStatements = false; String emailAddressFromDialog = ""; if(request instanceof InterimStatementRequestVO) { emailInterimStatements = ((InterimStatementRequestVO)request).sendEmailStatements(); } try { conn = ServerUtil.getConnection(); conn.setAutoCommit(false); stmt = conn.createStatement(); rs = stmt.executeQuery(buildStatementsToPrintQuery(request.getLoc(), request.getStatementDate(), request.getStatementType(), request.getStatementType().equals(CustomerStatementConstants.CLOSING), request.isIncludeZeroBalanceStatements(), request.getStatementType().equals(CustomerStatementConstants.CLOSING) ? CustomerStatementConstants.AGING_CATEGORY_ALL : ((InterimStatementRequestVO)request).getAgingCategory().byteValue())); if(request.isIncludeZeroBalanceStatements()){ customerWithActivityList = StatementsUtil.getCustomerListWithActivity(request. getLoc().intValue(), isClosingRequest); } if (rs.next()) { do { if (isProcessCancelled(processId)) { break; } totalRequestCount++; htParams = createKeyParams(createStatementVO(rs, request.getMessage()), language); htParams = attachLocalizedStatementLabels(htParams, request.getLoc()); if (emailInterimStatements || isClosingRequest) { fetchCustomerDetailsByCustomerId(htParams); if (request instanceof InterimStatementRequestVO) { emailAddressFromDialog = ((InterimStatementRequestVO) request).getEmailAddress(); if (StringUtils.isNotEmpty(emailAddressFromDialog)) { htParams.put(STATEMENT_EMAIL_ADDRESS, emailAddressFromDialog); } String emailAddress = htParams.get(STATEMENT_EMAIL_ADDRESS) == null ? "" : htParams.get(STATEMENT_EMAIL_ADDRESS).toString(); if (StringUtils.isEmpty(emailAddress)) { logger.error("STATEMENT_EMAIL_ADDRESS not updated for: Customer ID " + htParams.get("customerID")); continue; } } } //add "isDuplex" to let jasper report know if the printer supports duplex printing. htParams.put("isDuplex", prtSettings != null ? prtSettings.getDuplex() : Boolean.FALSE); boolean onlineStatement = rs.getInt(Sch_CustomerStatement.REF_STATEMENT_FORMAT_ID) == RefStatementFormat.ONLINE; // If closing statements are being printed and current statement // is an online statement - flag the statement as printed but don't // send it to the physical printer final String statementEmailAddress = (String) htParams.get(STATEMENT_EMAIL_ADDRESS); final boolean statementEmailAddressExists = StringUtils.isNotEmpty(statementEmailAddress); if (isClosingRequest && onlineStatement && !statementEmailAddressExists) { boolean rtnVal = updateCustomerStatementPrintedField(conn, (Integer) htParams.get("customerID"), (java.sql.Date) htParams.get("statementDate"), (Integer) htParams.get("loc")); if (!rtnVal) { logger.error("customer statement not updated for: Customer ID " + htParams.get("customerID")); } } else { Integer customerId = (Integer) htParams.get("customerID"); if (request.isIncludeZeroBalanceStatements() && isCustomerHavingZeroBalance(rs.getInt("ID"), rs.getInt("LOC")) && customerWithActivityList != null && !customerWithActivityList.contains(customerId)) { logger.warn("Skipping Non Active/ZeroBalance: CustomerID " + htParams.get("customerID")); continue; } mailProcessedCount++; printStatement(conn, htParams, prtSettings, request.isConsolidatePastDue(), isClosingRequest,emailInterimStatements); } } while (rs.next()); writeEmailProcessedDetail(processId,mailProcessedCount,totalRequestCount); conn.commit(); } } catch (SQLException e) { logger.error(e.toString(), e); try { conn.rollback(); } catch (SQLException sqle) { logger.error(sqle.toString(), sqle); } } catch (ApplicationException e) { try { conn.rollback(); } catch (SQLException sqle) { logger.error(sqle.toString(), sqle); } throw new ApplicationException(e); } finally { JDBCUtil.closeResultSet(rs); JDBCUtil.closeStatement(stmt); try { conn.setAutoCommit(true); } catch (SQLException e) { logger.error(e.toString(), e); } ServerUtil.releaseConnection(conn); } } /** * writeEmailProcessedDetail method is to add the mailed list to the file. * @param processId * @param mailSharedCount * @param mailRequestCount */ private void writeEmailProcessedDetail(Integer processId, Integer mailSharedCount, Integer mailRequestCount) { try { TsoFileWriter.appendFile(TsiFileConstants.FINAL_CUSTSTMT_DIRECTORY,"emailProcessed.txt" ,String.valueOf(processId)+"_"+mailSharedCount+"_"+mailRequestCount); } catch (IOException e) { logger.error("writeEmailProcessedDetail file write Expection", e); } } /** * getStatementEmailedData is to fetch the Email Processed details from the file * and do the clean up process. * @param processId * @return emailProcessedData */ public String getStatementEmailedData(Integer processId){ File emailProcessedFile = new File(TsiFileConstants.FINAL_CUSTSTMT_DIRECTORY + "/emailProcessed.txt"); String emailProcessedData =""; String removeProcessedData=""; if(emailProcessedFile.exists()) { try { TsoFileReader reader = new TsoFileReader(emailProcessedFile); while (reader.hasNextLine()) { String lineIterator = reader.readNextLine(); if (lineIterator.contains(String.valueOf(processId))) { emailProcessedData = lineIterator; } else { removeProcessedData = lineIterator + "\n"; } } } catch (IOException e) { logger.error("getStatementEmailedData file write Expection", e); } try { if(StringUtils.isNotEmpty(removeProcessedData)) { TsoFileWriter.createFile(TsiFileConstants.FINAL_CUSTSTMT_DIRECTORY, "emailProcessed.txt" , removeProcessedData); }else{ TsoFileWriter.deleteFile(TsiFileConstants.FINAL_CUSTSTMT_DIRECTORY, "emailProcessed.txt"); } } catch (IOException e) { logger.error("getStatementEmailedData file write Expection", e); } } return emailProcessedData; } public boolean isCustomerHavingZeroBalance(int customerStamentId, int location){ StringBuffer sql = new StringBuffer(); boolean isCustomerHavingZeroBalance = false; Connection connection = null; Statement statement = null; ResultSet resultSet = null; try{ sql.append("SELECT CUSTOMER_ID,SUM(") .append(Sch_CustomerStatementIncludedCustomer.TOTAL_OWED) .append(") AS TOTAL FROM "); sql.append(Sch_Tables.CUSTOMER_STATEMENT_INCLUDED_CUSTOMER) .append(" WHERE ") .append(Sch_Tables.CUSTOMER_STATEMENT_INCLUDED_CUSTOMER).append(FwiConstant.PERIOD); sql.append(Sch_CustomerStatementIncludedCustomer.CUSTOMER_STATEMENT_ID); sql.append(" = ").append(customerStamentId); sql.append(" AND ").append(Sch_Tables.CUSTOMER_STATEMENT_INCLUDED_CUSTOMER) .append(FwiConstant.PERIOD); sql.append(Sch_CustomerStatementIncludedCustomer.LOC).append(" = ").append(location) .append(" GROUP BY CUSTOMER_ID") .append(" HAVING TOTAL = 0"); logger.debug("isCustomerHavingZeroBalance======"+sql.toString()); connection = ServerUtil.getConnection(); statement = connection.createStatement(); resultSet = statement.executeQuery(sql.toString()); if(resultSet.next()){ isCustomerHavingZeroBalance = true; } }catch(SQLException sqlException){ logger.error(sqlException.getMessage(),sqlException); }finally{ JDBCUtil.closeResultSet(resultSet); JDBCUtil.closeStatement(statement); ServerUtil.releaseConnection(connection); return isCustomerHavingZeroBalance; } } private String buildStatementsToPrintQuery(Integer loc, Date stmDate, String stmtType, boolean isClosing, boolean incZeroBalStmt, byte agingCategory) { StringBuffer sql = new StringBuffer(); sql.append("SELECT "); sql.append(Sch_Tables.CUSTOMER_STATEMENT).append(FwiConstant.PERIOD).append(Sch_CustomerStatement.ID).append(FwiConstant.COMMA); sql.append(Sch_Tables.CUSTOMER_STATEMENT).append(FwiConstant.PERIOD).append(Sch_CustomerStatement.LOC).append(FwiConstant.COMMA); sql.append(Sch_Tables.CUSTOMER_STATEMENT).append(FwiConstant.PERIOD).append(Sch_CustomerStatement.CUSTOMER_ID).append(FwiConstant.COMMA); sql.append(Sch_CustomerStatement.STATEMENT_DATE).append(FwiConstant.COMMA); sql.append(Sch_CustomerStatement.STATEMENT_TYPE).append(FwiConstant.COMMA); sql.append(Sch_Tables.CUSTOMER_STATEMENT).append(FwiConstant.PERIOD).append(Sch_CustomerStatement.REF_STATEMENT_FORMAT_ID).append(FwiConstant.COMMA); sql.append("IF (").append(Sch_Tables.CUSTOMER_AR).append(FwiConstant.PERIOD).append(Sch_CustomerAR.AR_TERMS_ID); sql.append(" IS NOT NULL) THEN (SELECT ").append(Sch_ARTerms.REF_STATEMENT_FREQUENCY_ID).append(" FROM ").append(Sch_Tables.AR_TERMS); sql.append(" WHERE ").append(Sch_Tables.AR_TERMS).append(FwiConstant.PERIOD).append(Sch_ARTerms.ID).append(FwiConstant.EQUAL); sql.append(Sch_Tables.CUSTOMER_AR).append(FwiConstant.PERIOD).append(Sch_CustomerAR.AR_TERMS_ID); sql.append(" AND ").append(Sch_Tables.AR_TERMS).append(FwiConstant.PERIOD).append(Sch_ARTerms.LOC).append(FwiConstant.EQUAL); sql.append(loc).append(") ELSE (SELECT ").append(Sch_ARTerms.REF_STATEMENT_FREQUENCY_ID).append(" FROM ").append(Sch_Tables.AR_TERMS); sql.append(FwiConstant.COMMA).append(Sch_Tables.AR_PROFILE).append(" WHERE ").append(Sch_Tables.AR_TERMS).append(FwiConstant.PERIOD); sql.append(Sch_ARTerms.ID).append(FwiConstant.EQUAL).append(Sch_Tables.AR_PROFILE).append(FwiConstant.PERIOD); sql.append(Sch_ARProfile.DEFAULT_AR_TERMS_ID).append(" AND ").append(Sch_Tables.AR_TERMS).append(FwiConstant.PERIOD); sql.append(Sch_ARTerms.LOC).append(FwiConstant.EQUAL).append(Sch_Tables.AR_PROFILE).append(FwiConstant.PERIOD); sql.append(Sch_ARProfile.LOC).append(" AND ").append(Sch_Tables.AR_TERMS).append(FwiConstant.PERIOD).append(Sch_ARTerms.LOC); sql.append(FwiConstant.EQUAL).append(loc).append(") ENDIF AS ").append(Sch_ARTerms.REF_STATEMENT_FREQUENCY_ID); sql.append(" FROM ").append(Sch_Tables.CUSTOMER_STATEMENT).append(FwiConstant.COMMA); sql.append(Sch_Tables.CUSTOMER_AR); sql.append(" WHERE ").append(Sch_CustomerStatement.STATEMENT_DATE).append(FwiConstant.EQUAL).append(FwiConstant.SINGLEQUOTE); sql.append(new java.sql.Date(stmDate.getTime())).append(FwiConstant.SINGLEQUOTE); sql.append(" AND ").append(Sch_CustomerStatement.STATEMENT_TYPE).append(FwiConstant.EQUAL).append(FwiConstant.SINGLEQUOTE); sql.append(stmtType).append(FwiConstant.SINGLEQUOTE); sql.append(" AND ").append(Sch_CustomerStatement.FINALIZED).append(FwiConstant.EQUAL).append(FwiConstant.SINGLEQUOTE); sql.append(TsoConstant.GENERIC_N).append(FwiConstant.SINGLEQUOTE); sql.append(" AND ").append(Sch_CustomerStatement.PRINTED).append(FwiConstant.EQUAL).append(FwiConstant.SINGLEQUOTE); sql.append(TsoConstant.GENERIC_N).append(FwiConstant.SINGLEQUOTE); sql.append(" AND ").append(Sch_Tables.CUSTOMER_STATEMENT).append(FwiConstant.PERIOD).append(Sch_CustomerStatement.LOC); sql.append(FwiConstant.EQUAL).append(loc); if(isClosing) { ARStatementMonitorVO arStmtMonitorVO = ARStatementMonitorDBI.getARStatementMonitor(loc); sql.append(" AND ").append(Sch_CustomerStatement.BATCH_NUMBER).append(FwiConstant.EQUAL); sql.append(arStmtMonitorVO.getBatchNumber()); } if (!incZeroBalStmt) { sql.append(" AND (SELECT SUM(").append(Sch_CustomerStatementIncludedCustomer.TOTAL_OWED).append(") FROM "); sql.append(Sch_Tables.CUSTOMER_STATEMENT_INCLUDED_CUSTOMER).append(" WHERE "); sql.append(Sch_Tables.CUSTOMER_STATEMENT).append(FwiConstant.PERIOD).append(Sch_CustomerStatement.ID); sql.append(FwiConstant.EQUAL).append(Sch_Tables.CUSTOMER_STATEMENT_INCLUDED_CUSTOMER).append(FwiConstant.PERIOD); sql.append(Sch_CustomerStatementIncludedCustomer.CUSTOMER_STATEMENT_ID); sql.append(" AND ").append(Sch_Tables.CUSTOMER_STATEMENT).append(FwiConstant.PERIOD).append(Sch_CustomerStatement.LOC); sql.append(FwiConstant.EQUAL).append(Sch_Tables.CUSTOMER_STATEMENT_INCLUDED_CUSTOMER).append(FwiConstant.PERIOD); sql.append(Sch_CustomerStatementIncludedCustomer.LOC).append(" ) <> 0"); } //interim statements have Aging Category on All as default, but users have options to select Current, Period 1, etc. if (agingCategory == CustomerStatementConstants.AGING_CATEGORY_CURRENT) { //has Current Balance <> 0 or Period1Balance <> 0 or Period2Balance <> 0 or Period3Balance <> 0. sql.append(" AND (SELECT (").append(Sch_CustomerStatementIncludedCustomer.CURRENT_DUE); sql.append("+").append(Sch_CustomerStatementIncludedCustomer.PERIOD1_BAL); sql.append("+").append(Sch_CustomerStatementIncludedCustomer.PERIOD2_BAL); sql.append("+").append(Sch_CustomerStatementIncludedCustomer.PERIOD3_BAL); sql.append(") FROM ").append(Sch_Tables.CUSTOMER_STATEMENT_INCLUDED_CUSTOMER); sql.append(" WHERE "); sql.append(Sch_Tables.CUSTOMER_STATEMENT).append(FwiConstant.PERIOD).append(Sch_CustomerStatement.ID); sql.append(FwiConstant.EQUAL).append(Sch_Tables.CUSTOMER_STATEMENT_INCLUDED_CUSTOMER).append(FwiConstant.PERIOD); sql.append(Sch_CustomerStatementIncludedCustomer.CUSTOMER_STATEMENT_ID); sql.append(" AND ").append(Sch_Tables.CUSTOMER_STATEMENT).append(FwiConstant.PERIOD).append(Sch_CustomerStatement.LOC); sql.append(FwiConstant.EQUAL).append(Sch_Tables.CUSTOMER_STATEMENT_INCLUDED_CUSTOMER).append(FwiConstant.PERIOD); sql.append(Sch_CustomerStatementIncludedCustomer.LOC).append(" ) <> 0"); } else if (agingCategory == CustomerStatementConstants.AGING_CATEGORY_PERIOD1) { //has Period1Balance <> 0 or Period2Balance <> 0 or Period3Balance <> 0. sql.append(" AND (SELECT (").append(Sch_CustomerStatementIncludedCustomer.PERIOD1_BAL); sql.append("+").append(Sch_CustomerStatementIncludedCustomer.PERIOD2_BAL); sql.append("+").append(Sch_CustomerStatementIncludedCustomer.PERIOD3_BAL); sql.append(") FROM ").append(Sch_Tables.CUSTOMER_STATEMENT_INCLUDED_CUSTOMER); sql.append(" WHERE "); sql.append(Sch_Tables.CUSTOMER_STATEMENT).append(FwiConstant.PERIOD).append(Sch_CustomerStatement.ID); sql.append(FwiConstant.EQUAL).append(Sch_Tables.CUSTOMER_STATEMENT_INCLUDED_CUSTOMER).append(FwiConstant.PERIOD); sql.append(Sch_CustomerStatementIncludedCustomer.CUSTOMER_STATEMENT_ID); sql.append(" AND ").append(Sch_Tables.CUSTOMER_STATEMENT).append(FwiConstant.PERIOD).append(Sch_CustomerStatement.LOC); sql.append(FwiConstant.EQUAL).append(Sch_Tables.CUSTOMER_STATEMENT_INCLUDED_CUSTOMER).append(FwiConstant.PERIOD); sql.append(Sch_CustomerStatementIncludedCustomer.LOC).append(" ) <> 0"); } else if (agingCategory == CustomerStatementConstants.AGING_CATEGORY_PERIOD2) { //has Period2Balance <> 0 or Period3Balance <> 0. sql.append(" AND (SELECT (").append(Sch_CustomerStatementIncludedCustomer.PERIOD2_BAL); sql.append("+").append(Sch_CustomerStatementIncludedCustomer.PERIOD3_BAL); sql.append(") FROM ").append(Sch_Tables.CUSTOMER_STATEMENT_INCLUDED_CUSTOMER); sql.append(" WHERE "); sql.append(Sch_Tables.CUSTOMER_STATEMENT).append(FwiConstant.PERIOD).append(Sch_CustomerStatement.ID); sql.append(FwiConstant.EQUAL).append(Sch_Tables.CUSTOMER_STATEMENT_INCLUDED_CUSTOMER).append(FwiConstant.PERIOD); sql.append(Sch_CustomerStatementIncludedCustomer.CUSTOMER_STATEMENT_ID); sql.append(" AND ").append(Sch_Tables.CUSTOMER_STATEMENT).append(FwiConstant.PERIOD).append(Sch_CustomerStatement.LOC); sql.append(FwiConstant.EQUAL).append(Sch_Tables.CUSTOMER_STATEMENT_INCLUDED_CUSTOMER).append(FwiConstant.PERIOD); sql.append(Sch_CustomerStatementIncludedCustomer.LOC).append(" ) <> 0"); } else if (agingCategory == CustomerStatementConstants.AGING_CATEGORY_PERIOD3) { //has Period3Balance <> 0. sql.append(" AND (SELECT ").append(Sch_CustomerStatementIncludedCustomer.PERIOD3_BAL); sql.append(" FROM ").append(Sch_Tables.CUSTOMER_STATEMENT_INCLUDED_CUSTOMER); sql.append(" WHERE "); sql.append(Sch_Tables.CUSTOMER_STATEMENT).append(FwiConstant.PERIOD).append(Sch_CustomerStatement.ID); sql.append(FwiConstant.EQUAL).append(Sch_Tables.CUSTOMER_STATEMENT_INCLUDED_CUSTOMER).append(FwiConstant.PERIOD); sql.append(Sch_CustomerStatementIncludedCustomer.CUSTOMER_STATEMENT_ID); sql.append(" AND ").append(Sch_Tables.CUSTOMER_STATEMENT).append(FwiConstant.PERIOD).append(Sch_CustomerStatement.LOC); sql.append(FwiConstant.EQUAL).append(Sch_Tables.CUSTOMER_STATEMENT_INCLUDED_CUSTOMER).append(FwiConstant.PERIOD); sql.append(Sch_CustomerStatementIncludedCustomer.LOC).append(" ) <> 0"); } sql.append(" AND ").append(Sch_Tables.CUSTOMER_AR).append(FwiConstant.PERIOD).append(Sch_CustomerAR.CUSTOMER_ID).append(FwiConstant.EQUAL); sql.append(Sch_Tables.CUSTOMER_STATEMENT).append(FwiConstant.PERIOD).append(Sch_CustomerStatement.CUSTOMER_ID); sql.append(" AND ").append(Sch_Tables.CUSTOMER_AR).append(FwiConstant.PERIOD).append(Sch_CustomerAR.LOC).append(FwiConstant.EQUAL); sql.append(Sch_Tables.CUSTOMER_STATEMENT).append(FwiConstant.PERIOD).append(Sch_CustomerStatement.LOC); sql.append(" ORDER BY PRINT_SEQUENCE"); logger.debug(" ------------ getStatementsToPrintQuery: sql = \n"+sql.toString()); return sql.toString(); } private BaseStatementVO createStatementVO (ResultSet rs, String message) throws SQLException { BaseStatementVO statement = new BaseStatementVO(); statement.setId(rs.getInt(Sch_CustomerStatement.ID)); statement.setLoc(rs.getInt(Sch_CustomerStatement.LOC)); statement.setCustomerID(rs.getInt(Sch_CustomerStatement.CUSTOMER_ID)); statement.setStatementDate(new Date(rs.getDate(Sch_CustomerStatement.STATEMENT_DATE).getTime())); statement.setStatementType(rs.getString(Sch_CustomerStatement.STATEMENT_TYPE)); statement.setStatementFrequency(rs.getByte(Sch_ARTerms.REF_STATEMENT_FREQUENCY_ID)); statement.setMessage(message); return statement; } /** * @param statements * @param prtSettings * Take a List of BaseStatementVO and call the printing feature. */ public void reprintCurrentStatements(Integer loc, List statements, FwoPrintSettings prtSettings, Integer processId) throws ApplicationException { logger.info("reprintStatements()"); Connection conn = null; Hashtable params = null; try { ARStatementMonitorVO monitorVO = ARStatementMonitorDBI.getARStatementMonitor(loc); conn = ServerUtil.getConnection(); for (int i = 0; i < statements.size(); i++) { if (isProcessCancelled(processId)) { break; } params = createKeyParams((BaseStatementVO)statements.get(i), monitorVO.getMessage()); params = attachLocalizedStatementLabels(params, loc); printStatement(conn, params, prtSettings, false, false,false); } } finally { ServerUtil.releaseConnection(conn); } } public void reprintArchivedStatement(ArchivedStatementReprintRequestVO archivedStatement, FwoPrintSettings prtSettings) throws ApplicationException { logger.info("reprintArchivedStatement()"); Connection conn = null; Hashtable params = null; try { conn = ServerUtil.getConnection(); params = createKeyParams(archivedStatement); params = attachLocalizedStatementLabels(params, new Integer(archivedStatement.getLoc())); if(archivedStatement.sendEmailStatements()) { fetchCustomerDetailsByCustomerId(params); if(StringUtils.isNotEmpty(archivedStatement.getEmailAddress()) && archivedStatement.getEmailStatements()) { params.put("STATEMENT_EMAIL_ADDRESS", archivedStatement.getEmailAddress()); } } else if (archivedStatement.isViewStatement()) { params.put("VIEW_ARCHIVED_STATEMENT", VIEW_ARCHIVED_STATEMENT); } printStatement(conn, params, prtSettings, false, false,false); } finally { ServerUtil.releaseConnection(conn); } } public String fetchCustomerDetailsByCustomerIdForDisplay(final Hashtable params) throws ApplicationException { logger.info("fetchCustomerDetailsByCustomerIdForDisplay()"); String statementEmailAddress = ""; if (params.containsKey("customerID")) { fetchCustomerDetailsByCustomerId(params); statementEmailAddress = getParamValue(params, STATEMENT_EMAIL_ADDRESS); } else if (params.containsKey("customerNum")) { int customerNumber = ((Integer) params.get("customerNum")).intValue(); statementEmailAddress = fetchStatementEmailByCustomerNumber(customerNumber); } return statementEmailAddress; } /** * @return java.util.List */ public List getOrderedStatementList() { return null; } public static Hashtable attachLocalizedStatementLabels(Hashtable params, Integer loc) { fetchCustomerLocaleDetailsByCustomerId(params); Locale customerLocale= getCustomerLocale(params); ResourceBundleReader rbr = ApplicationContext.getInstance().getResourceBundleReader(); StoreProfileVO spvo = ApplicationContext.getInstance().getProfile(Profile.SERVER, loc.intValue()).getStoreProfile(); Locale storeLocale = new Locale(spvo.getRefLanguageCd(), spvo.getRefCountryCd()); Locale locale = (customerLocale != null && !customerLocale.equals("")) ? customerLocale : storeLocale; params.put("countryCode",locale.getCountry()); params.put("languageCode", locale.getLanguage()); params.put("lblBillTo", rbr.getLocalizedText(ResourceBundleReader.UI, CustomerStatementConstants.BILL_TO, locale)); params.put("lblStatement", rbr.getLocalizedText(ResourceBundleReader.UI, CustomerStatementConstants.STATEMENT, locale)); params.put("lblAcctNo", rbr.getLocalizedText(ResourceBundleReader.UI, CustomerStatementConstants.ACCT, locale)); params.put("lblSalesMan", rbr.getLocalizedText(ResourceBundleReader.UI, CustomerStatementConstants.SM, locale)); params.put("lblPage", rbr.getLocalizedText(ResourceBundleReader.UI, CustomerStatementConstants.PAGE, locale)); params.put("lblDate", rbr.getLocalizedText(ResourceBundleReader.UI, CustomerStatementConstants.DATE, locale)); params.put("lblType", rbr.getLocalizedText(ResourceBundleReader.UI, CustomerStatementConstants.TYPE, locale)); params.put("lblReference", rbr.getLocalizedText(ResourceBundleReader.UI, CustomerStatementConstants.REFERENCE, locale)); params.put("lblAmount", rbr.getLocalizedText(ResourceBundleReader.UI, CustomerStatementConstants.AMOUNT, locale)); params.put("lblPO", rbr.getLocalizedText(ResourceBundleReader.UI, CustomerStatementConstants.PO, locale)); params.put("lblBalance", rbr.getLocalizedText(ResourceBundleReader.UI, CustomerStatementConstants.BALANCE, locale)); params.put("lblCr", rbr.getLocalizedText(ResourceBundleReader.UI, CustomerStatementConstants.CR, locale)); params.put("lblCurrent", rbr.getLocalizedText(ResourceBundleReader.UI, CustomerStatementConstants.CURRENT, locale)); params.put("lblPastDue", rbr.getLocalizedText(ResourceBundleReader.UI, CustomerStatementConstants.PAST_DUE, locale)); params.put("lblPastDue30", rbr.getLocalizedText(ResourceBundleReader.UI, CustomerStatementConstants.PAST_DUE_30, locale)); params.put("lblPastDue60", rbr.getLocalizedText(ResourceBundleReader.UI, CustomerStatementConstants.PAST_DUE_60, locale)); params.put("lblPastDue90", rbr.getLocalizedText(ResourceBundleReader.UI, CustomerStatementConstants.PAST_DUE_90, locale)); params.put("lblPastDue7", rbr.getLocalizedText(ResourceBundleReader.UI, CustomerStatementConstants.PAST_DUE_7, locale)); params.put("lblPastDue14", rbr.getLocalizedText(ResourceBundleReader.UI, CustomerStatementConstants.PAST_DUE_14, locale)); params.put("lblPastDue21", rbr.getLocalizedText(ResourceBundleReader.UI, CustomerStatementConstants.PAST_DUE_21, locale)); params.put("lblTerms", rbr.getLocalizedText(ResourceBundleReader.UI, CustomerStatementConstants.TERMS, locale)); params.put("lblStore", rbr.getLocalizedText(ResourceBundleReader.UI, CustomerStatementConstants.STORE, locale)); params.put("lblCreditLimit", rbr.getLocalizedText(ResourceBundleReader.UI, CustomerStatementConstants.CREDIT_LIMIT, locale)); params.put("lblTotalOwed", rbr.getLocalizedText(ResourceBundleReader.UI, CustomerStatementConstants.TOTAL_OWED, locale)); params.put("lblTotalDue", rbr.getLocalizedText(ResourceBundleReader.UI, CustomerStatementConstants.TOTAL_DUE, locale)); params.put("lblTotalNowDue", rbr.getLocalizedText(ResourceBundleReader.UI, CustomerStatementConstants.TOTAL_NOW_DUE, locale)); params.put("lblClosingDate", rbr.getLocalizedText(ResourceBundleReader.UI, CustomerStatementConstants.CLOSING_DATE, locale)); params.put("lblAmountEnclosed", rbr.getLocalizedText(ResourceBundleReader.UI, CustomerStatementConstants.AMOUNT_ENCLOSED, locale)); params.put("lblTaxSummary", rbr.getLocalizedText(ResourceBundleReader.UI, CustomerStatementConstants.TAX_SUMMARY, locale)); params.put("lblARSummary", rbr.getLocalizedText(ResourceBundleReader.UI, CustomerStatementConstants.SUMMARY, locale)); params.put("lblContinuedOnPage", rbr.getLocalizedText(ResourceBundleReader.UI, CustomerStatementConstants.CONT_ON_PAGE, locale)); params.put("lblTotalDating", rbr.getLocalizedText(ResourceBundleReader.UI, CustomerStatementConstants.TOTAL_DATING, locale)); params.put("lblFutureDatedItems", rbr.getLocalizedText(ResourceBundleReader.UI, CustomerStatementConstants.FUTURE_DATED_ITEMS, locale)); return params; } private Hashtable createKeyParams(BaseStatementVO statement, String message) { Hashtable htKeyParams = new Hashtable(); htKeyParams.put("PRINT_ARCHIVE", Boolean.FALSE); htKeyParams.put("loc", new Integer(statement.getLoc())); htKeyParams.put("customerID", new Integer(statement.getCustomerID())); // logger.debug("_______________ passing date as = "+new java.sql.Date(statement.getStatementDate().getTime()).toString()); htKeyParams.put("statementDate", new java.sql.Date(statement.getStatementDate().getTime())); htKeyParams.put("statementType", statement.getStatementType()); htKeyParams.put("message", statement.getMessage()); htKeyParams.put("statementFrequency", new Integer(statement.getStatementFrequency())); logger.debug("######### htKeyParams = "+htKeyParams); return htKeyParams; } private Hashtable createKeyParams(ArchivedStatementReprintRequestVO archivedStatement) { Hashtable htKeyParams = new Hashtable(); htKeyParams.put("PRINT_ARCHIVE", (!archivedStatement.isViewStatement() && !archivedStatement.getEmailStatements()) ? Boolean.TRUE: Boolean.FALSE); htKeyParams.put("loc", new Integer(archivedStatement.getLoc())); htKeyParams.put("customerID", new Integer(archivedStatement.getCustomerID())); htKeyParams.put("statementDate", new java.sql.Date(archivedStatement.getStatementClosingDate().getTime())); htKeyParams.put("statementType", CustomerStatementConstants.CLOSING); htKeyParams.put("message", ""); //message is blank when printing archived statements htKeyParams.put("statementFrequency", new Integer(archivedStatement.getStatementFrequency())); logger.debug("######### htKeyParams = "+htKeyParams); return htKeyParams; } private boolean isProcessCancelled(Integer processId) { return this.statementListener.isProcessCancelled(processId); } private static void fetchCustomerDetailsByCustomerId(final Hashtable params) { final String methodSpec = "StatementPrinter.getCustomerDetailsByCustomerId - "; logger.debug(methodSpec); Connection connection = null; Statement statement = null; ResultSet resultSet = null; if(params.get("customerID") instanceof Integer && params.get("loc") instanceof Integer) { try { final Integer location = (Integer) params.get("loc"); final Integer customerId = (Integer) params.get("customerID"); connection = ServerUtil.getConnection(); statement = connection.createStatement(); final StringBuffer query = new StringBuffer(); query.append("SELECT ") .append(" CUSTOMER.CUSTOMER_NUM, ") .append(" CUSTOMER.REF_LANGUAGE_CD, ") .append(" CUSTOMER.REF_COUNTRY_CD, ") .append(" CUSTOMER_AR.STATEMENT_EMAIL_ADDRESS ") .append(" FROM ") .append(" CUSTOMER, CUSTOMER_AR ") .append(" WHERE ") .append(" CUSTOMER.ID = CUSTOMER_AR.CUSTOMER_ID AND ") .append(" CUSTOMER.LOC = CUSTOMER_AR.LOC AND ") .append(" CUSTOMER_AR.CUSTOMER_ID = " + customerId.intValue()).append(" AND ") .append(" CUSTOMER.LOC = ").append("'" + location.intValue() + "' "); logger.debug(methodSpec + "getCustomerDetailsByCustomerId SQL = " + query.toString()); resultSet = statement.executeQuery(query.toString()); if (resultSet.next()) { if( resultSet.getString("REF_LANGUAGE_CD")!= null && !resultSet.getString("REF_LANGUAGE_CD").equals("") && resultSet.getString("REF_COUNTRY_CD")!= null && !resultSet.getString("REF_COUNTRY_CD").equals("")) { params.put("REF_LANGUAGE_CD", resultSet.getString("REF_LANGUAGE_CD")); params.put("REF_COUNTRY_CD", resultSet.getString("REF_COUNTRY_CD")); }else { String storeQuery = "Select REF_LANGUAGE_CD,REF_COUNTRY_CD from store_profile"; resultSet = statement.executeQuery(storeQuery.toString()); params.put("REF_LANGUAGE_CD", resultSet.getString("REF_LANGUAGE_CD")); params.put("REF_COUNTRY_CD", resultSet.getString("REF_COUNTRY_CD")); } params.put("CUSTOMER_NUM", (Integer) resultSet.getObject("CUSTOMER_NUM")); String email = resultSet.getString(STATEMENT_EMAIL_ADDRESS); if (email != null && !email.trim().isEmpty()) { params.put(STATEMENT_EMAIL_ADDRESS, email.trim()); } } } catch (SQLException sqlException) { logger.error(sqlException.getMessage(), sqlException); } finally { JDBCUtil.closeResultSet(resultSet); JDBCUtil.closeStatement(statement); ServerUtil.releaseConnection(connection); } } } private static void fetchCustomerLocaleDetailsByCustomerId(final Hashtable params) { final String methodSpec = "StatementPrinter.fetchCustomerLocaleDetailsByCustomerId - "; logger.debug(methodSpec); Connection connection = null; Statement statement = null; ResultSet resultSet = null; if (params.get("customerID") instanceof Integer && params.get("loc") instanceof Integer) { try { final Integer location = (Integer) params.get("loc"); final Integer customerId = (Integer) params.get("customerID"); connection = ServerUtil.getConnection(); statement = connection.createStatement(); final StringBuffer query = new StringBuffer(); query.append("SELECT ") .append(" CUSTOMER.CUSTOMER_NUM, ") .append(" CUSTOMER.REF_LANGUAGE_CD, ") .append(" CUSTOMER.REF_COUNTRY_CD ") .append(" FROM ") .append(" CUSTOMER, CUSTOMER_AR ") .append(" WHERE ") .append(" CUSTOMER.ID = CUSTOMER_AR.CUSTOMER_ID AND ") .append(" CUSTOMER.LOC = CUSTOMER_AR.LOC AND ") .append(" CUSTOMER_AR.CUSTOMER_ID = " + customerId.intValue()).append(" AND ") .append(" CUSTOMER.LOC = ").append("'" + location.intValue() + "' "); logger.debug(methodSpec + "fetchCustomerLocaleDetailsByCustomerId SQL = " + query.toString()); resultSet = statement.executeQuery(query.toString()); if (resultSet.next()) { if (resultSet.getString("REF_LANGUAGE_CD") != null && !resultSet.getString("REF_LANGUAGE_CD").equals("") && resultSet.getString("REF_COUNTRY_CD") != null && !resultSet.getString("REF_COUNTRY_CD").equals("")) { params.put("REF_LANGUAGE_CD", resultSet.getString("REF_LANGUAGE_CD")); params.put("REF_COUNTRY_CD", resultSet.getString("REF_COUNTRY_CD")); } else { String storeQuery = "Select REF_LANGUAGE_CD,REF_COUNTRY_CD from store_profile"; resultSet = statement.executeQuery(storeQuery.toString()); params.put("REF_LANGUAGE_CD", resultSet.getString("REF_LANGUAGE_CD")); params.put("REF_COUNTRY_CD", resultSet.getString("REF_COUNTRY_CD")); } params.put("CUSTOMER_NUM", (Integer) resultSet.getObject("CUSTOMER_NUM")); } } catch (SQLException sqlException) { logger.error(sqlException.getMessage(), sqlException); } finally { JDBCUtil.closeResultSet(resultSet); JDBCUtil.closeStatement(statement); ServerUtil.releaseConnection(connection); } } } /** * This method creates a locale based on customer language & country * * @param Hashtable contains the customer attributes * * @return {@link Locale} value object contains Locale details */ private static Locale getCustomerLocale(Hashtable params) { final String customerLanguageCd = getParamValue(params, REF_LANGUAGE_CD); final String customerCountryCd = getParamValue(params, REF_COUNTRY_CD); final String languageCd = StringUtils.isNotEmpty(customerLanguageCd) ? customerLanguageCd : DEFAULT_LANGUAGE_CD; final String countryCd = StringUtils.isNotEmpty(customerCountryCd) ? customerCountryCd : DEFAULT_COUNTRY_CD; return new Locale(languageCd, countryCd); } private static String getParamValue(Hashtable params, String paramKey) { Object object = params.get(paramKey); String paramValue = null; if (object instanceof String) { paramValue = (String) object; } return paramValue; } private boolean copyPdfStatementToTempDirectory(File sourceStatementPdfFile) { if (sourceStatementPdfFile != null) { try { File path = new File(TsiFileConstants.FINAL_CUSTSTMT_DIRECTORY); boolean hasPath = path.exists(); if (!hasPath) { hasPath = path.mkdir(); } if (hasPath) { File pdfFinalFile = new File(TsiFileConstants.FINAL_CUSTSTMT_DIRECTORY, sourceStatementPdfFile.getName()); FileUtils.copyFile(sourceStatementPdfFile, pdfFinalFile); return true; } } catch (IllegalArgumentException illegalArgumentException) { logger.error("StatementPrinter::copyPdfStatementToTempDirectory - IllegalArgumentException coping " + sourceStatementPdfFile.getName() + " to " + TsiFileConstants.FINAL_CUSTSTMT_DIRECTORY, illegalArgumentException); } catch (IOException ioException) { logger.error("StatementPrinter::copyPdfStatementToTempDirectory - IOException coping " + sourceStatementPdfFile.getName() + " to " + TsiFileConstants.FINAL_CUSTSTMT_DIRECTORY, ioException); } catch (SecurityException securityException) { logger.error("StatementPrinter::copyPdfStatementToTempDirectory - SecurityException coping " + sourceStatementPdfFile.getName() + " to " + TsiFileConstants.FINAL_CUSTSTMT_DIRECTORY, securityException); } catch (Exception exception) { logger.error("StatementPrinter::copyPdfStatementToTempDirectory - SecurityException coping " + sourceStatementPdfFile.getName() + " to " + TsiFileConstants.FINAL_CUSTSTMT_DIRECTORY, exception); } } else { logger.error("StatementPrinter::copyStatementPdf - invalid parameter(s)"); } return false; } public static boolean deletePdfStatementFile(File sourceStatementPdfFile) { if (sourceStatementPdfFile != null) { try { if (sourceStatementPdfFile.exists()) { if (sourceStatementPdfFile.canWrite()) { return sourceStatementPdfFile.delete(); } else { logger.error("StatementPrinter.deletePdfStatementFile: file has invalid permissions!!! : " + sourceStatementPdfFile.getName()); } } } catch (SecurityException securityException) { logger.error("StatementPrinter.deletePdfStatementFile - SecurityException deleting " + sourceStatementPdfFile.getName(), securityException); } catch (Exception exception) { logger.error("StatementPrinter.deletePdfStatementFile - Exception deleting " + sourceStatementPdfFile.getName(), exception); } } else { logger.error("StatementPrinter::deletePdfStatementFile - invalid sourceStatementPdfFile parameter"); } return false; } public static void emailPdfStatementFile(File sourceStatementPdfFile, String statementEmailAddress, Locale customerLocale) { if (sourceStatementPdfFile != null && StringUtils.isNotEmpty(statementEmailAddress) && statementEmailAddress !=null && customerLocale != null) { try { if (sourceStatementPdfFile.exists()) { StatementsUtil.sendCustomerStatementEmail(sourceStatementPdfFile, statementEmailAddress, customerLocale); deletePdfStatementFile(sourceStatementPdfFile); } } catch (SecurityException securityException) { logger.error("StatementPrinter.emailPdfStatementFile - SecurityException emailing statement " + sourceStatementPdfFile.getName(), securityException); } catch (Exception exception) { logger.error("StatementPrinter.emailPdfStatementFile - Exception emailing statement " + sourceStatementPdfFile.getName(), exception); } } else { logger.error("StatementPrinter::emailPdfStatementFile - invalid parameter(s)"); } } public static void cleanPdfStatementTempDirectory() { try { File directory = new File(TsiFileConstants.FINAL_CUSTSTMT_DIRECTORY); boolean hasDirectory = directory.exists(); if (hasDirectory) { FileUtils.cleanDirectory(directory); } } catch (IllegalArgumentException illegalArgumentException) { logger.error("StatementPrinter::cleanPdfStatementTempDirectory - IllegalArgumentException deleting all temp file(s) " + TsiFileConstants.FINAL_CUSTSTMT_DIRECTORY, illegalArgumentException); } catch (IOException ioException) { logger.error("StatementPrinter::cleanPdfStatementTempDirectory - IOException deleting all temp file(s) " + TsiFileConstants.FINAL_CUSTSTMT_DIRECTORY, ioException); } catch (SecurityException securityException) { logger.error("StatementPrinter::cleanPdfStatementTempDirectory - SecurityException deleting all temp file(s) " + TsiFileConstants.FINAL_CUSTSTMT_DIRECTORY, securityException); } catch (Exception exception) { logger.error("StatementPrinter::cleanPdfStatementTempDirectory - Exception deleting all temp file(s) " + TsiFileConstants.FINAL_CUSTSTMT_DIRECTORY, exception); } } public static String fetchStatementEmailByCustomerNumber(int customerNumber) { final String methodSpec = "StatementPrinter.fetchStatementEmailByCustomerNumber - "; logger.debug(methodSpec); Connection connection = null; java.sql.Statement statement = null; ResultSet resultSet = null; String email= null; try { connection = ServerUtil.getConnection(); statement = connection.createStatement(); String query = "SELECT CUSTOMER_AR.STATEMENT_EMAIL_ADDRESS " + "FROM CUSTOMER " + "JOIN CUSTOMER_AR " + "ON CUSTOMER.ID = CUSTOMER_AR.CUSTOMER_ID " + "AND CUSTOMER.LOC = CUSTOMER_AR.LOC " + "WHERE CUSTOMER.CUSTOMER_NUM = " + customerNumber; logger.debug(methodSpec + "fetchStatementEmailByCustomerNumber SQL = " + query.toString()); resultSet = statement.executeQuery(query.toString()); if (resultSet.next()) { if (resultSet.getString(STATEMENT_EMAIL_ADDRESS) != null && !resultSet.getString(STATEMENT_EMAIL_ADDRESS).trim().isEmpty()) { email = resultSet.getString(STATEMENT_EMAIL_ADDRESS); } } } catch (SQLException sqlException) { logger.error(sqlException.getMessage(), sqlException); } finally { JDBCUtil.closeResultSet(resultSet); JDBCUtil.closeStatement(statement); ServerUtil.releaseConnection(connection); } return email; } public static String getAltromLocation(Integer loc) { final String methodSpec = "StatementPrinter.getAltromLocation - "; logger.debug(methodSpec); Connection connection = null; java.sql.Statement statement = null; ResultSet resultSet = null; String atromLoc = "N"; try { connection = ServerUtil.getConnection(); statement = connection.createStatement(); String query = "SELECT ALTROM_LOCATION FROM STORE_PROFILE WHERE LOC = " +loc; logger.debug(methodSpec + "getAltromLocation SQL = " + query.toString()); resultSet = statement.executeQuery(query.toString()); if (resultSet.next() && resultSet.getString(ALTROM_LOCATION) != null && !resultSet.getString(ALTROM_LOCATION).trim().isEmpty()) { atromLoc = resultSet.getString(ALTROM_LOCATION); } } catch (SQLException sqlException) { logger.error(sqlException.getMessage(), sqlException); } finally { JDBCUtil.closeResultSet(resultSet); JDBCUtil.closeStatement(statement); ServerUtil.releaseConnection(connection); } return atromLoc; } public static String getStatementLogo(Locale customerLocale,Integer loc) { String statementLogo = STATEMENT_LOGO_US; String getAltromLoc = getAltromLocation(loc); if (getAltromLoc.equalsIgnoreCase("Y") && customerLocale.getCountry().equalsIgnoreCase(TsoConstant.COUNTRY_CD_CA)) { statementLogo = STATEMENT_LOGO_ALTROM; } else if ((getAltromLoc.equalsIgnoreCase("N") && customerLocale.getLanguage().equalsIgnoreCase(TsoConstant.LANGUAGE_CD_FR) && customerLocale.getCountry().equalsIgnoreCase(TsoConstant.COUNTRY_CD_CA)) || (customerLocale.getLanguage().equalsIgnoreCase(TsoConstant.LANGUAGE_CD_FR) && !customerLocale.getCountry().equalsIgnoreCase(TsoConstant.COUNTRY_CD_CA))) { statementLogo = STATEMENT_LOGO_CA; } return statementLogo; } public static String fetchStatementType(int customerNumber) { final String methodSpec = "StatementPrinter.fetchStatementEmailByCustomerNumber - "; logger.debug(methodSpec); Connection connection = null; java.sql.Statement statement = null; ResultSet resultSet = null; String statementtype = null; try { connection = ServerUtil.getConnection(); statement = connection.createStatement(); String query = "SELECT CUSTOMER_AR.REF_STATEMENT_TYPE_ID " + "FROM CUSTOMER " + "JOIN CUSTOMER_AR " + "ON CUSTOMER.ID = CUSTOMER_AR.CUSTOMER_ID " + "AND CUSTOMER.LOC = CUSTOMER_AR.LOC " + "WHERE CUSTOMER.CUSTOMER_NUM = " + customerNumber; logger.debug(methodSpec + "fetchStatementEmailByCustomerNumber SQL = " + query.toString()); resultSet = statement.executeQuery(query.toString()); if (resultSet.next()) { if (resultSet.getString(REF_STATEMENT_TYPE_ID) != null && !resultSet.getString(REF_STATEMENT_TYPE_ID).trim().isEmpty()) { statementtype = resultSet.getString(REF_STATEMENT_TYPE_ID); } } } catch (SQLException sqlException) { logger.error(sqlException.getMessage(), sqlException); } finally { JDBCUtil.closeResultSet(resultSet); JDBCUtil.closeStatement(statement); ServerUtil.releaseConnection(connection); } return statementtype; } public static String fetchStatementPrintAndEmail(int customerNumber) { final String methodSpec = "StatementPrinter.fetchStatementEmailByCustomerNumber - "; logger.debug(methodSpec); Connection connection = null; java.sql.Statement statement = null; ResultSet resultSet = null; String print = null; try { connection = ServerUtil.getConnection(); statement = connection.createStatement(); String query = "SELECT CUSTOMER.PRINT_AND_EMAIL_CUSTOMER_STATEMENTS " + "FROM CUSTOMER " + "JOIN CUSTOMER_AR " + "ON CUSTOMER.ID = CUSTOMER_AR.CUSTOMER_ID " + "AND CUSTOMER.LOC = CUSTOMER_AR.LOC " + "WHERE CUSTOMER.CUSTOMER_NUM = " + customerNumber; logger.debug(methodSpec + "fetchStatementEmailByCustomerNumber SQL = " + query.toString()); resultSet = statement.executeQuery(query.toString()); if (resultSet.next()) { if (resultSet.getString(PRINT_AND_EMAIL_CUSTOMER_STATEMENTS) != null && !resultSet.getString(PRINT_AND_EMAIL_CUSTOMER_STATEMENTS).trim().isEmpty()) { print = resultSet.getString(PRINT_AND_EMAIL_CUSTOMER_STATEMENTS); } } } catch (SQLException sqlException) { logger.error(sqlException.getMessage(), sqlException); } finally { JDBCUtil.closeResultSet(resultSet); JDBCUtil.closeStatement(statement); ServerUtil.releaseConnection(connection); } return print; } }