| /*
|
| * Copyright 2006-2026 HighRadius Corporation
|
| */
|
| package com.highradius.daggr.pdf.parser;
|
|
|
| import java.io.File;
|
| import java.io.FileReader;
|
| import java.io.FileWriter;
|
| import java.text.SimpleDateFormat;
|
| import java.util.ArrayList;
|
| import java.util.Arrays;
|
| import java.util.Calendar;
|
| import java.util.Date;
|
| import java.util.HashMap;
|
| import java.util.List;
|
| import java.util.Map;
|
|
|
| import org.apache.commons.lang.StringUtils;
|
| import org.apache.logging.log4j.ThreadContext;
|
| import org.hibernate.Session;
|
|
|
| import com.highradius.common.emailapi.model.SendEmailRequest;
|
| import com.highradius.common.emailapi.util.HRCEmailUtil;
|
| import com.highradius.common.session.SessionUtil;
|
| import com.highradius.common.util.HRCLog;
|
| import com.highradius.common.util.HRCLogFactory;
|
| import com.highradius.core.utils.HRCCStandardCSVParser; // Standardized framework CSV engine
|
| import com.highradius.core.utils.HRCCSVParser;
|
| import com.highradius.daggr.parser.CustomPDFParser;
|
| import com.highradius.daggr.parser.DaggrMetaData;
|
| import com.highradius.util.FileUtility;
|
| import com.highradius.util.cpa.CpaFileUtil;
|
| import com.highradius.util.cpa.StringConstants;
|
| import com.scraper.agents.base.Util;
|
| import com.scraper.cpa.AgentProperties;
|
| import com.scraper.cpa.BaseHelper;
|
| import com.scraper.sql.cpa.HRPreparedStatement;
|
|
|
| /**
|
| * @author HighRadius Custom Monitoring Integration
|
| * * ClaimInvoiceAdvantageReportt manages comprehensive end-to-end reconciliation between internal
|
| * HighRadius data-extraction processes and downstream client SOAR validation states.
|
| */
|
| public class ClaimInvoiceAdvantageReportt extends CustomPDFParser {
|
| private static final HRCLog LOG = HRCLogFactory.getLog(ClaimInvoiceAdvantageReportt.class);
|
|
|
| private static final String ZIP_FILENAME_ATTR = "ZIPFILENAME";
|
| private static final String FILE_ROW_NUMBER_ATTR = "FILEROWNUMBER";
|
| private static final String MASTER_BASE_NAME = "Advantage_Reconciliation_Master_";
|
|
|
| private List<String> inputHeader = null;
|
| private Map<String, ReconciliationRow> reconciliationMap = null;
|
|
|
| private String targetBucketName = "gcshrc-cpa-prod";
|
| private String targetSourceFolder = "Email Inbox";
|
|
|
| // Runtime context captured to preserve downstream pipeline execution constants
|
| private String generatedMasterLocalPath = "";
|
| private String calculatedFileNameOnly = "";
|
|
|
| @Override
|
| public String parseFileV2(DaggrMetaData metaData, Map<String, String> emailDetailsMap) {
|
| String parsingStatus = STATUS_FAILED_TO_PARSE;
|
| LOG.info("Initializing V2 reconciliation framework processing clock.");
|
|
|
| reconciliationMap = new HashMap<>();
|
| String attachmentName = emailDetailsMap.get("ATTACHMENT");
|
|
|
| if (StringUtils.isNotBlank(attachmentName)) {
|
| attachmentName = attachmentName.replace("\\", "/");
|
| }
|
|
|
| File todaySoarReportFile = CpaFileUtil.getInstance().getFile(metaData.getAccountId(), attachmentName, null);
|
|
|
| HRPreparedStatement hpstmtExtracts = null;
|
| Connection con = null;
|
| HRCCSVParser errorReportReader = null;
|
|
|
| try {
|
| if (todaySoarReportFile == null || !todaySoarReportFile.exists()) {
|
| LOG.error("InvoiceReconciliationAgent: Today's incoming SOAR report file is missing.");
|
| return STATUS_FAILED_TO_PARSE;
|
| }
|
|
|
| Session session = BaseHelper.getDBSession();
|
| con = SessionUtil.getDBConnection(session);
|
| int accountId = metaData.getAccountId();
|
|
|
| // PHASE 1: Load Yesterday's Master Output File from specific Sunday folder partition
|
| loadYesterdayMasterFile(metaData, attachmentName);
|
|
|
| // PHASE 2: Fetch today's database successes (24-hour cycle window)
|
| updateTodaySentClaims(con, accountId);
|
|
|
| // PHASE 4 [SEQUENCING FIX]: Fetch today's new internal HRC failures first
|
| updateTodayInternalFailures(con, accountId);
|
|
|
| // PHASE 3: Read today's incoming SOAR report file and intercept errors (Overrides status last)
|
| errorReportReader = new HRCCSVParser(new FileReader(todaySoarReportFile));
|
| String[] lines;
|
| boolean startLine = false;
|
| int zipFileIdx = -1;
|
| int rowNumIdx = -1;
|
|
|
| while ((lines = errorReportReader.getLine()) != null) {
|
| if (lines.length == 0 || StringUtils.isBlank(lines[0])) {
|
| continue;
|
| }
|
|
|
| if (startLine) {
|
| String zipFilename = (zipFileIdx != -1 && zipFileIdx < lines.length) ? lines[zipFileIdx].trim() : "";
|
| String fileRowNumberStr = (rowNumIdx != -1 && rowNumIdx < lines.length) ? lines[rowNumIdx].trim() : "";
|
|
|
| if (StringUtils.isNotBlank(zipFilename) && StringUtils.isNotBlank(fileRowNumberStr)) {
|
| try {
|
| int targetRowNumber = Integer.parseInt(fileRowNumberStr.trim());
|
| String csvFileName = zipFilename.replaceAll("(?i)\\.zip", ".csv");
|
|
|
| String extractsSql = "select file_paths from arpa_extracts_details where name like ? or file_paths like ? order by extracts_details_id desc";
|
| hpstmtExtracts = new HRPreparedStatement(con, extractsSql);
|
| hpstmtExtracts.setString(1, "%" + csvFileName + "%");
|
| hpstmtExtracts.setString(2, "%" + csvFileName + "%");
|
| ResultSet rsExtracts = hpstmtExtracts.executeQuery();
|
|
|
| String correctS3Path = null;
|
| if (rsExtracts.next()) {
|
| String filePathsJson = rsExtracts.getString("file_paths");
|
| if (StringUtils.isNotBlank(filePathsJson)) {
|
| String[] jsonObjects = filePathsJson.split("\\}");
|
| for (String obj : jsonObjects) {
|
| if (obj.contains(csvFileName)) {
|
| String targetToken = "\"s3Path\":";
|
| int tokenIdx = obj.indexOf(targetToken);
|
| if (tokenIdx == -1) {
|
| targetToken = "\"s3Path\" :";
|
| tokenIdx = obj.indexOf(targetToken);
|
| }
|
| if (tokenIdx != -1) {
|
| int firstQuote = obj.indexOf("\"", tokenIdx + targetToken.length());
|
| int secondQuote = obj.indexOf("\"", firstQuote + 1);
|
| if (firstQuote != -1 && secondQuote != -1) {
|
| correctS3Path = obj.substring(firstQuote + 1, secondQuote).trim();
|
| break;
|
| }
|
| }
|
| }
|
| }
|
| }
|
| }
|
| Util.close(null, hpstmtExtracts, rsExtracts);
|
|
|
| if (StringUtils.isNotBlank(correctS3Path)) {
|
| File targetedSourceFile = CpaFileUtil.getInstance().getFile(accountId, correctS3Path, null);
|
| if (targetedSourceFile != null && targetedSourceFile.exists()) {
|
| interceptAndOverrideSoarErrors(targetedSourceFile, targetRowNumber, csvFileName, zipFilename);
|
| }
|
| }
|
| } catch (Exception ex) {
|
| LOG.error("Error processing row line pointer context: " + ex.getMessage(), ex);
|
| }
|
| }
|
| } else {
|
| if (Arrays.asList(lines).contains("FileRowNumber") || Arrays.asList(lines).contains("ZipFilename") || Arrays.asList(lines).contains("CsvFileName")) {
|
| inputHeader = new ArrayList<>();
|
| for (String headerToken : lines) {
|
| inputHeader.add(headerToken.replace(" ", "").toUpperCase());
|
| }
|
| zipFileIdx = inputHeader.indexOf(ZIP_FILENAME_ATTR);
|
| rowNumIdx = inputHeader.indexOf(FILE_ROW_NUMBER_ATTR);
|
| startLine = true;
|
| }
|
| }
|
| }
|
|
|
| // PHASE 4B: Hydrate descriptive parameters dynamically from customer_claim table
|
| enrichMetadataFromCustomerClaims(con, accountId);
|
|
|
| // PHASE 5: Save state parameters into today's suffix filename inside the weekly Sunday folder
|
| saveUpdatedMasterFile(metaData, attachmentName);
|
|
|
| // PHASE 6: Compile HTML body and trigger secure attachment email transfer pipeline
|
| String reportSubjectName = "Advantage_Claims_Reconciliation_Matrix_Report";
|
| String recipientEmailOverride = com.scraper.cpa.AgentProperties.getProperty("CPA_AGENT.monitor.Notifier.toAddress");
|
| if (StringUtils.isBlank(recipientEmailOverride)) {
|
| recipientEmailOverride = "cpa.notifications@highradius.com";
|
| }
|
|
|
| attachFileToEmail(this.generatedMasterLocalPath, reportSubjectName, recipientEmailOverride, accountId);
|
|
|
| parsingStatus = STATUS_PARSED;
|
|
|
| } catch (Exception e) {
|
| LOG.error("InvoiceReconciliationAgent: Global framework loop execution failed: " + e.getMessage(), e);
|
| parsingStatus = STATUS_FAILED_TO_PARSE;
|
| } finally {
|
| if (errorReportReader != null) {
|
| try { errorReportReader.close(); } catch (Exception ignored) {}
|
| }
|
| }
|
| return parsingStatus;
|
| }
|
|
|
| /**
|
| * Dynamically parses the sequence index integer from a filename layout string.
|
| * e.g., "Deductions-20260608T043013Z-6.zip" -> returns "6"
|
| */
|
| private String extractSequenceFromFilename(String filename) {
|
| if (StringUtils.isBlank(filename)) return "N/A";
|
| try {
|
| int lastHyphen = filename.lastIndexOf('-');
|
| int lastDot = filename.lastIndexOf('.');
|
| if (lastHyphen != -1 && lastDot != -1 && lastHyphen < lastDot) {
|
| String seq = filename.substring(lastHyphen + 1, lastDot).trim();
|
| if (StringUtils.isNumeric(seq)) {
|
| return seq;
|
| }
|
| }
|
| } catch (Exception e) {
|
| LOG.error("Failed to parse sequence from filename: " + filename);
|
| }
|
| return "N/A";
|
| }
|
|
|
| /**
|
| * PHASE 1: Sunday Partition Lookback Loader Engine
|
| */
|
| private void loadYesterdayMasterFile(DaggrMetaData metaData, String attachmentName) {
|
| LOG.debug("InvoiceReconciliationAgent: Commencing execution clock calendar tracking.");
|
| String yesterdayMasterCloudPath = null;
|
|
|
| try {
|
| String cleanName = attachmentName.replace("\\", "/");
|
| String[] rawParts = cleanName.split("/");
|
| List<String> tokens = new ArrayList<>();
|
| for (String part : rawParts) {
|
| if (StringUtils.isNotBlank(part)) tokens.add(part.trim());
|
| }
|
|
|
| if (tokens.size() >= 6) {
|
| this.targetBucketName = tokens.get(0);
|
| this.targetSourceFolder = tokens.get(5);
|
| }
|
|
|
| Date trueCurrentDate = new Date();
|
| Calendar calYesterday = Calendar.getInstance();
|
| calYesterday.setTime(trueCurrentDate);
|
| int dayOfWeek = calYesterday.get(Calendar.DAY_OF_WEEK);
|
|
|
| if (dayOfWeek == Calendar.MONDAY) {
|
| calYesterday.add(Calendar.DATE, -3);
|
| } else if (dayOfWeek == Calendar.SUNDAY) {
|
| calYesterday.add(Calendar.DATE, -2);
|
| } else {
|
| calYesterday.add(Calendar.DATE, -1);
|
| }
|
|
|
| Date yesterdayFileDate = calYesterday.getTime();
|
| String yesterdayDateSuffixStr = new SimpleDateFormat("yyyyMMdd").format(yesterdayFileDate);
|
| String yesterdayFileName = MASTER_BASE_NAME + yesterdayDateSuffixStr + ".csv";
|
|
|
| Calendar calYesterdaySunday = Calendar.getInstance();
|
| calYesterdaySunday.setTime(yesterdayFileDate);
|
| while (calYesterdaySunday.get(Calendar.DAY_OF_WEEK) != Calendar.SUNDAY) {
|
| calYesterdaySunday.add(Calendar.DATE, -1);
|
| }
|
| String yesterdaySundayFolderStr = new SimpleDateFormat("yyyy/MM/dd").format(calYesterdaySunday.getTime());
|
|
|
| yesterdayMasterCloudPath = "/" + this.targetBucketName + "/" + metaData.getAccountId() + "/"
|
| + yesterdaySundayFolderStr + "/" + this.targetSourceFolder + "/" + yesterdayFileName;
|
|
|
| } catch (Exception e) {
|
| LOG.error("InvoiceReconciliationAgent: Lookback path execution clock math failed: " + e.getMessage());
|
| }
|
|
|
| if (StringUtils.isBlank(yesterdayMasterCloudPath)) {
|
| LOG.warn("InvoiceReconciliationAgent: Lookback target empty. Proceeding with clean session.");
|
| return;
|
| }
|
|
|
| File masterFile = null;
|
| try {
|
| LOG.info("InvoiceReconciliationAgent: Requesting file from GCP folder path: " + yesterdayMasterCloudPath);
|
| masterFile = CpaFileUtil.getInstance().getFile(metaData.getAccountId(), yesterdayMasterCloudPath, null);
|
| } catch (Exception cloudException) {
|
| LOG.warn("InvoiceReconciliationAgent: Previous log ledger not found inside this Sunday bucket partition. Starting fresh.");
|
| return;
|
| }
|
|
|
| if (masterFile == null || !masterFile.exists()) {
|
| LOG.info("InvoiceReconciliationAgent: Ledger file missing from disk mount path. Initializing blank layout.");
|
| return;
|
| }
|
|
|
| HRCCSVParser reader = null;
|
| try {
|
| reader = new HRCCSVParser(new FileReader(masterFile));
|
| String[] lines;
|
| boolean headerSkipped = false;
|
| while ((lines = reader.getLine()) != null) {
|
| if (lines.length == 0 || StringUtils.isBlank(lines[0])) continue;
|
| if (!headerSkipped) {
|
| headerSkipped = true;
|
| continue;
|
| }
|
|
|
| ReconciliationRow row = new ReconciliationRow();
|
| row.invoiceNumber = lines[0].trim();
|
| if (lines.length > 1) row.contractNumber = lines[1].trim();
|
| if (lines.length > 2) row.clientName = lines[2].trim();
|
| if (lines.length > 3) row.clientId = lines[3].trim();
|
| if (lines.length > 4) row.customerName = lines[4].trim();
|
| if (lines.length > 5) row.lastUpdatedOn = lines[5].trim();
|
| if (lines.length > 6) row.hrcStatus = lines[6].trim();
|
| if (lines.length > 7) row.soarStatus = lines[7].trim();
|
|
|
| if (lines.length > 8) row.firstHrFileDate = lines[8].trim();
|
| if (lines.length > 9) row.firstHrFileSequence = lines[9].trim();
|
| if (lines.length > 10) row.firstHrFileCreatedUtcDate = lines[10].trim();
|
| if (lines.length > 11) row.firstHrCsvFileName = lines[11].trim();
|
| if (lines.length > 12) row.firstHrZipFilename = lines[12].trim();
|
| if (lines.length > 13) row.firstHrFileRowNumber = lines[13].trim();
|
|
|
| if (lines.length > 14) row.latestHrFileDate = lines[14].trim();
|
| if (lines.length > 15) row.latestHrFileSequence = lines[15].trim();
|
| if (lines.length > 16) row.latestHrFileCreatedUtcDate = lines[16].trim();
|
| if (lines.length > 17) row.latestHrCsvFileName = lines[17].trim();
|
| if (lines.length > 18) row.latestHrZipFilename = lines[18].trim();
|
| if (lines.length > 19) row.latestHrFileRowNumber = lines[19].trim();
|
|
|
| if (lines.length > 20) row.soarLatestHrFileDate = lines[20].trim();
|
| if (lines.length > 21) row.soarLatestHrFileSequence = lines[21].trim();
|
| if (lines.length > 22) row.soarLatestHrFileCreatedUtcDate = lines[22].trim();
|
| if (lines.length > 23) row.soarLatestHrCsvFileName = lines[23].trim();
|
| if (lines.length > 24) row.soarLatestHrZipFilename = lines[24].trim();
|
| if (lines.length > 25) row.soarLatestHrFileRowNumber = lines[25].trim();
|
|
|
| reconciliationMap.put(row.invoiceNumber, row);
|
| }
|
| LOG.info("InvoiceReconciliationAgent: Hydration complete. Restored " + reconciliationMap.size() + " records.");
|
| } catch (Exception e) {
|
| LOG.error("InvoiceReconciliationAgent: Error parsing history data: " + e.getMessage(), e);
|
| } finally {
|
| if (reader != null) {
|
| try { reader.close(); } catch (Exception ignored) {}
|
| }
|
| }
|
| }
|
|
|
| /**
|
| * PHASE 2: Database Success Scan Engine Match Layout Profile Columns
|
| */
|
| private void updateTodaySentClaims(Connection con, int accountId) throws Exception {
|
| String sentClaimsQuery = "select cb.claim_number, cc.customer_name, cb.filename, cb.exported_date "
|
| + "from customer_claim_export_callback cb "
|
| + "join customer_claim cc on cc.customer_claim_id = cb.customer_claim_id "
|
| + "where cc.account_id = ? and cb.exported_date between DATE_SUB(NOW(), INTERVAL 24 HOUR) and NOW()";
|
|
|
| HRPreparedStatement hpstmt = null;
|
| ResultSet rs = null;
|
| try {
|
| hpstmt = new HRPreparedStatement(con, sentClaimsQuery);
|
| hpstmt.setInt(1, accountId);
|
| rs = hpstmt.executeQuery();
|
| String activeTimestampStr = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(new Date());
|
|
|
| while (rs.next()) {
|
| String claimNum = rs.getString("claim_number");
|
| if (StringUtils.isNotBlank(claimNum)) {
|
| ReconciliationRow row = reconciliationMap.get(claimNum);
|
| String zipName = rs.getString("filename");
|
| String inferredCsvName = StringUtils.isNotBlank(zipName) ? zipName.replaceAll("(?i)\\.zip", ".csv") : "N/A";
|
| String fileDateStr = rs.getTimestamp("exported_date") != null ? rs.getTimestamp("exported_date").toString() : activeTimestampStr;
|
| String dynamicSequence = extractSequenceFromFilename(zipName);
|
|
|
| if (row == null) {
|
| row = new ReconciliationRow();
|
| row.invoiceNumber = claimNum;
|
| row.customerName = rs.getString("customer_name");
|
| row.firstHrFileDate = fileDateStr;
|
| row.firstHrFileCreatedUtcDate = fileDateStr;
|
| row.firstHrZipFilename = zipName;
|
| row.firstHrCsvFileName = inferredCsvName;
|
| row.firstHrFileSequence = dynamicSequence;
|
| row.firstHrFileRowNumber = "N/A";
|
| }
|
| row.hrcStatus = "Success";
|
| row.soarStatus = "Sent to SOAR";
|
| row.latestHrFileDate = fileDateStr;
|
| row.latestHrFileCreatedUtcDate = fileDateStr;
|
| row.latestHrZipFilename = zipName;
|
| row.latestHrCsvFileName = inferredCsvName;
|
| row.latestHrFileSequence = dynamicSequence;
|
| row.latestHrFileRowNumber = "N/A";
|
| row.lastUpdatedOn = activeTimestampStr;
|
|
|
| reconciliationMap.put(claimNum, row);
|
| }
|
| }
|
| } finally {
|
| Util.close(null, hpstmt, rs);
|
| }
|
| }
|
|
|
| /**
|
| * PHASE 3: Intercept Downstream Errors and Extract Dynamic Details
|
| * [STABILITY FIX]: Counts all lines to avoid SOAR layout drift, using fallback identifiers if invoice number is missing.
|
| */
|
| private void interceptAndOverrideSoarErrors(File targetedSourceFile, int targetRowNumber, String csvName, String zipName) throws Exception {
|
| HRCCSVParser cloudCsvReader = null;
|
| try {
|
| cloudCsvReader = new HRCCSVParser(new FileReader(targetedSourceFile));
|
| String[] cloudLines;
|
| int currentCloudRow = 0;
|
| boolean cloudHeaderProcessed = false;
|
| int invoiceIdx = -1;
|
| int dateIdx = -1;
|
| int contractIdx = -1;
|
| int customerIdx = -1;
|
|
|
| while ((cloudLines = cloudCsvReader.getLine()) != null) {
|
| // Counts all entries sequentially to align perfectly with downstream SOAR tracking metrics
|
| if (cloudLines.length == 0) continue;
|
|
|
| if (!cloudHeaderProcessed) {
|
| int exactInvoiceIdx = -1; int looseInvoiceIdx = -1;
|
| int exactDateIdx = -1; int looseDateIdx = -1;
|
|
|
| for (int i = 0; i < cloudLines.length; i++) {
|
| String h = cloudLines[i].replace(" ", "").replace("_", "").toUpperCase();
|
| if (h.equals("INVOICENUMBER") || h.equals("CLAIMNUMBER")) {
|
| exactInvoiceIdx = i;
|
| } else if (h.contains("INVOICENUMBER") || h.contains("CLAIM") || h.contains("NUMBER")) {
|
| if (looseInvoiceIdx == -1) looseInvoiceIdx = i;
|
| }
|
|
|
| if (h.equals("INVOICEDATE") || h.equals("CLAIMDATE")) {
|
| exactDateIdx = i;
|
| } else if (h.contains("DATE") || h.contains("TIME")) {
|
| if (looseDateIdx == -1) looseDateIdx = i;
|
| }
|
|
|
| if (h.contains("CONTRACT")) contractIdx = i;
|
| if (h.contains("CUSTOMER")) customerIdx = i;
|
| }
|
| invoiceIdx = (exactInvoiceIdx != -1) ? exactInvoiceIdx : looseInvoiceIdx;
|
| dateIdx = (exactDateIdx != -1) ? exactDateIdx : looseDateIdx;
|
| cloudHeaderProcessed = true;
|
| } else {
|
| currentCloudRow++;
|
| if (currentCloudRow == targetRowNumber) {
|
| String invoiceNum = (invoiceIdx != -1 && invoiceIdx < cloudLines.length) ? cloudLines[invoiceIdx].trim() : "";
|
| String fileDateStr = (dateIdx != -1 && dateIdx < cloudLines.length) ? cloudLines[dateIdx].trim() : "";
|
| String contractNum = (contractIdx != -1 && contractIdx < cloudLines.length) ? cloudLines[contractIdx].trim() : "";
|
| String customerNm = (customerIdx != -1 && customerIdx < cloudLines.length) ? cloudLines[customerIdx].trim() : "";
|
|
|
| String activeTimestampStr = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(new Date());
|
| if (StringUtils.isBlank(fileDateStr)) {
|
| fileDateStr = activeTimestampStr;
|
| }
|
| String dynamicSequence = extractSequenceFromFilename(zipName);
|
|
|
| // FALLBACK STRATEGY: Applies placeholder tracking key if source data row had a blank invoice number
|
| if (StringUtils.isBlank(invoiceNum)) {
|
| invoiceNum = "MISSING-INV-" + zipName.replaceAll("(?i)\\.zip", "") + "-ROW-" + targetRowNumber;
|
| }
|
|
|
| ReconciliationRow row = reconciliationMap.get(invoiceNum);
|
| if (row == null) {
|
| row = new ReconciliationRow();
|
| row.invoiceNumber = invoiceNum;
|
| row.contractNumber = contractNum;
|
| row.customerName = customerNm;
|
| row.firstHrFileDate = fileDateStr;
|
| row.firstHrFileCreatedUtcDate = fileDateStr;
|
| row.firstHrZipFilename = zipName;
|
| row.firstHrCsvFileName = csvName;
|
| row.firstHrFileRowNumber = String.valueOf(targetRowNumber);
|
| row.firstHrFileSequence = dynamicSequence;
|
| }
|
| row.hrcStatus = "Success";
|
| row.soarStatus = "Failure";
|
| row.soarLatestHrFileDate = fileDateStr;
|
| row.soarLatestHrFileCreatedUtcDate = fileDateStr;
|
| row.soarLatestHrCsvFileName = csvName;
|
| row.soarLatestHrZipFilename = zipName;
|
| row.soarLatestHrFileRowNumber = String.valueOf(targetRowNumber);
|
| row.soarLatestHrFileSequence = dynamicSequence;
|
| row.lastUpdatedOn = activeTimestampStr;
|
|
|
| reconciliationMap.put(invoiceNum, row);
|
| break;
|
| }
|
| }
|
| }
|
| } finally {
|
| if (cloudCsvReader != null) {
|
| cloudCsvReader.close();
|
| }
|
| }
|
| }
|
|
|
| /**
|
| * PHASE 4: Core OCR System Failure Exceptions Trace Engine Mapping Profiles
|
| */
|
| private void updateTodayInternalFailures(Connection con, int accountId) throws Exception {
|
| String internalFailsQuery = "select cc.claim_number, cc.customer_name, cc.update_time "
|
| + "from customer_claim cc "
|
| + "where cc.account_id = ? and cc.error_code_id != 2 "
|
| + "and cc.update_time between DATE_SUB(NOW(), INTERVAL 24 HOUR) and NOW()";
|
|
|
| HRPreparedStatement hpstmt = null;
|
| ResultSet rs = null;
|
| try {
|
| hpstmt = new HRPreparedStatement(con, internalFailsQuery);
|
| hpstmt.setInt(1, accountId);
|
| rs = hpstmt.executeQuery();
|
| String activeTimestampStr = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(new Date());
|
|
|
| while (rs.next()) {
|
| String claimNum = rs.getString("claim_number");
|
| if (StringUtils.isNotBlank(claimNum)) {
|
| ReconciliationRow row = reconciliationMap.get(claimNum);
|
| String fileDateStr = rs.getTimestamp("update_time") != null ? rs.getTimestamp("update_time").toString() : activeTimestampStr;
|
|
|
| if (row == null) {
|
| row = new ReconciliationRow();
|
| row.invoiceNumber = claimNum;
|
| row.customerName = rs.getString("customer_name");
|
| row.firstHrFileDate = fileDateStr;
|
| row.firstHrFileCreatedUtcDate = fileDateStr;
|
| row.firstHrZipFilename = "N/A";
|
| row.firstHrCsvFileName = "N/A";
|
| row.firstHrFileSequence = "N/A";
|
| row.firstHrFileRowNumber = "N/A";
|
| }
|
| row.hrcStatus = "Failed";
|
| row.soarStatus = "Failed";
|
| row.latestHrFileDate = fileDateStr;
|
| row.latestHrFileCreatedUtcDate = fileDateStr;
|
| row.lastUpdatedOn = activeTimestampStr;
|
|
|
| reconciliationMap.put(claimNum, row);
|
| }
|
| }
|
| } finally {
|
| Util.close(null, hpstmt, rs);
|
| }
|
| }
|
|
|
| /**
|
| * PHASE 4B: Hydrate descriptive fields (vendor_id, vendor_name, customer_name) dynamically from customer_claim
|
| */
|
| private void enrichMetadataFromCustomerClaims(Connection con, int accountId) {
|
| LOG.info("Executing hydration routine for client descriptive parameters mapping data blocks.");
|
| String hydrationSql = "select vendor_id, vendor_name, customer_name from customer_claim "
|
| + "where account_id = ? and (claim_number = ? or invoice_number = ?) order by customer_claim_id desc limit 1";
|
|
|
| HRPreparedStatement hpstmt = null;
|
| ResultSet rs = null;
|
|
|
| try {
|
| for (String invoiceKey : reconciliationMap.keySet()) {
|
| ReconciliationRow row = reconciliationMap.get(invoiceKey);
|
| if (row == null || StringUtils.isBlank(invoiceKey) || invoiceKey.startsWith("MISSING-INV-")) continue;
|
|
|
| hpstmt = new HRPreparedStatement(con, hydrationSql);
|
| hpstmt.setInt(1, accountId);
|
| hpstmt.setString(2, invoiceKey.trim());
|
| hpstmt.setString(3, invoiceKey.trim());
|
| rs = hpstmt.executeQuery();
|
|
|
| if (rs.next()) {
|
| String aggregatedVendorId = rs.getString("vendor_id");
|
| String aggregatedVendorName = rs.getString("vendor_name");
|
| String aggregatedCustomerName = rs.getString("customer_name");
|
|
|
| if (StringUtils.isNotBlank(aggregatedVendorId)) {
|
| row.clientId = aggregatedVendorId.trim();
|
| }
|
| if (StringUtils.isNotBlank(aggregatedVendorName)) {
|
| row.clientName = aggregatedVendorName.trim();
|
| }
|
| if (StringUtils.isNotBlank(aggregatedCustomerName)) {
|
| row.customerName = aggregatedCustomerName.trim();
|
| }
|
| }
|
| Util.close(null, hpstmt, rs);
|
| }
|
| } catch (Exception ex) {
|
| LOG.error("InvoiceReconciliationAgent: Failed to source parameters from customer_claim: " + ex.getMessage(), ex);
|
| } finally {
|
| Util.close(null, hpstmt, rs);
|
| }
|
| }
|
|
|
| /**
|
| * PHASE 5: Symmetrical Output Synchronizer
|
| */
|
| private void saveUpdatedMasterFile(DaggrMetaData metaData, String attachmentName) {
|
| SimpleDateFormat sdfFolder = new SimpleDateFormat("yyyy/MM/dd");
|
| String todayFileName = MASTER_BASE_NAME + "FreshData.csv";
|
| String todaySundayFolderStr = "2026/01/01";
|
|
|
| try {
|
| Date trueCurrentDate = new Date();
|
| String todayDateSuffixStr = new SimpleDateFormat("yyyyMMdd").format(trueCurrentDate);
|
| todayFileName = MASTER_BASE_NAME + todayDateSuffixStr + ".csv";
|
| this.calculatedFileNameOnly = todayFileName;
|
|
|
| Calendar calTodaySunday = Calendar.getInstance();
|
| calTodaySunday.setTime(trueCurrentDate);
|
| while (calTodaySunday.get(Calendar.DAY_OF_WEEK) != Calendar.SUNDAY) {
|
| calTodaySunday.add(Calendar.DATE, -1);
|
| }
|
| todaySundayFolderStr = sdfFolder.format(calTodaySunday.getTime());
|
|
|
| } catch (Exception e) {
|
| LOG.error("InvoiceReconciliationAgent: Save path formatting execution clock failure: " + e.getMessage());
|
| }
|
|
|
| String providerShortName = "MONITORING";
|
| if (metaData.getDaggrFileTypeParser() != null &&
|
| metaData.getDaggrFileTypeParser().getMapProviderDatatype() != null &&
|
| metaData.getDaggrFileTypeParser().getMapProviderDatatype().getLuServiceProvider() != null) {
|
| providerShortName = metaData.getDaggrFileTypeParser().getMapProviderDatatype().getLuServiceProvider().getShortname();
|
| }
|
|
|
| String baseDirLocal = AgentProperties.getProperty("BaseDirectory_Local");
|
| if (StringUtils.isBlank(baseDirLocal)) {
|
| baseDirLocal = "/tmp/vcm/";
|
| }
|
| if (!baseDirLocal.endsWith("/")) {
|
| baseDirLocal += "/";
|
| }
|
|
|
| this.generatedMasterLocalPath = baseDirLocal + metaData.getAccountId() + StringConstants.FORWARD_SLASH
|
| + this.targetSourceFolder + StringConstants.FORWARD_SLASH + todayFileName;
|
|
|
| File localMasterOutputFile = new File(this.generatedMasterLocalPath);
|
| File parentDir = localMasterOutputFile.getParentFile();
|
| if (parentDir != null && !parentDir.exists()) {
|
| parentDir.mkdirs();
|
| }
|
|
|
| FileWriter writer = null;
|
| try {
|
| writer = new FileWriter(localMasterOutputFile);
|
| writer.write("InvoiceNumber,ContractNumber,ClientName,ClientId,CustomerName,Last Updated on,HRC Status,SOAR Status,"
|
| + "First HR FileDate,First HR FileSequence,First HR FileCreatedUtcDate,First HR CsvFileName,First HR ZipFilename,First HR FileRowNumber,"
|
| + "Latest HR FileDate,Latest HR FileSequence,Latest HR FileCreatedUtcDate,Latest HR CsvFileName,Latest HR ZipFilename,Latest HR FileRowNumber,"
|
| + "SOAR Latest HR FileDate,SOAR Latest HR FileSequence,SOAR Latest HR FileCreatedUtcDate,SOAR Latest HR CsvFileName,SOAR Latest HR ZipFilename,SOAR Latest HR FileRowNumber\n");
|
|
|
| for (ReconciliationRow r : reconciliationMap.values()) {
|
| writer.write(String.format("%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s\n",
|
| sanitizeCsv(r.invoiceNumber), sanitizeCsv(r.contractNumber), sanitizeCsv(r.clientName), sanitizeCsv(r.clientId), sanitizeCsv(r.customerName), sanitizeCsv(r.lastUpdatedOn), sanitizeCsv(r.hrcStatus), sanitizeCsv(r.soarStatus),
|
| sanitizeCsv(r.firstHrFileDate), sanitizeCsv(r.firstHrFileSequence), sanitizeCsv(r.firstHrFileCreatedUtcDate), sanitizeCsv(r.firstHrCsvFileName), sanitizeCsv(r.firstHrZipFilename), sanitizeCsv(r.firstHrFileRowNumber),
|
| sanitizeCsv(r.latestHrFileDate), sanitizeCsv(r.latestHrFileSequence), sanitizeCsv(r.latestHrFileCreatedUtcDate), sanitizeCsv(r.latestHrCsvFileName), sanitizeCsv(r.latestHrZipFilename), sanitizeCsv(r.latestHrFileRowNumber),
|
| sanitizeCsv(r.soarLatestHrFileDate), sanitizeCsv(r.soarLatestHrFileSequence), sanitizeCsv(r.soarLatestHrFileCreatedUtcDate), sanitizeCsv(r.soarLatestHrCsvFileName), sanitizeCsv(r.soarLatestHrZipFilename), sanitizeCsv(r.soarLatestHrFileRowNumber)));
|
| }
|
| writer.flush();
|
| } catch (Exception e) {
|
| LOG.error("InvoiceReconciliationAgent: Error writing local file spreadsheet: " + e.getMessage(), e);
|
| } finally {
|
| if (writer != null) {
|
| try { writer.close(); } catch (Exception ignored) {}
|
| }
|
| }
|
|
|
| try {
|
| if (localMasterOutputFile.exists() && localMasterOutputFile.length() > 0) {
|
| String localNormalizedPathStr = localMasterOutputFile.getPath().replace("\\", "/");
|
| String path = StringUtils.substringAfter(localNormalizedPathStr, "vcm");
|
| if (StringUtils.isBlank(path)) {
|
| path = this.generatedMasterLocalPath;
|
| }
|
|
|
| // Standard framework copy logic copies reports into standard historical partitions
|
| FileUtility.copyProcessingFiletoCloud(metaData.getAccountId(), "Reports", localMasterOutputFile.getName(), localMasterOutputFile, null);
|
| LOG.info("InvoiceReconciliationAgent: Sync to S3 complete for weekly partition track: " + path);
|
| }
|
| } catch (Exception e) {
|
| LOG.error("InvoiceReconciliationAgent: Cloud upload failed: " + e.getMessage(), e);
|
| }
|
| }
|
|
|
| private String sanitizeCsv(String input) {
|
| if (StringUtils.isBlank(input)) return "";
|
| return input.replace(",", " ").replace("\r", "").replace("\n", "").trim();
|
| }
|
|
|
| /**
|
| * PHASE 6: Full Comprehensive HTML Display Matrix Dashboard for the Email Template Summary Body
|
| */
|
| private String htmlBodyReportSummaryBuilder() {
|
| StringBuilder html = new StringBuilder();
|
| html.append("<html><head><style>")
|
| .append("table {border-collapse: collapse; width: 100%; font-family: Arial, sans-serif; font-size: 10px;}")
|
| .append("th {background-color: #003366; color: white; padding: 4px; text-align: left; border: 1px solid #ddd; white-space: nowrap;}")
|
| .append("td {padding: 4px; border: 1px solid #ddd; text-align: left;}")
|
| .append("tr:nth-child(even) {background-color: #f2f2f2;}")
|
| .append("</style></head><body>");
|
|
|
| html.append("<h2 style='color:#003366; font-family: Arial, sans-serif;'>Daily Claims Reconciliation Automation Full Matrix Tracking Summary Dashboard</h2>");
|
| html.append("<p style='font-family: Arial, sans-serif; font-size: 12px;'>Please find the comprehensive report attached to this email notification for full verification matrix analysis details.</p>");
|
| html.append("<table><tr>")
|
| .append("<th>Invoice Number</th><th>Contract Number</th><th>Client Name</th><th>Client ID</th><th>Customer Name</th><th>Last Updated on</th>")
|
| .append("<th>HRC Status</th><th>SOAR Status</th>")
|
| .append("<th>First HRC Zip</th><th>First HRC Csv</th><th>First HRC Seq</th><th>First HRC Row</th><th>First HRC UTC Date</th>")
|
| .append("<th>Latest HRC Zip</th><th>Latest HRC Csv</th><th>Latest HRC Seq</th><th>Latest HRC Row</th><th>Latest HRC UTC Date</th>")
|
| .append("<th>SOAR Latest Zip</th><th>SOAR Latest Csv</th><th>SOAR Latest Seq</th><th>SOAR Latest Row</th><th>SOAR Latest UTC Date</th>")
|
| .append("</tr>");
|
|
|
| for (ReconciliationRow row : reconciliationMap.values()) {
|
| String hrcColor = "Success".equalsIgnoreCase(row.hrcStatus) ? "green" : "red";
|
| String soarColor = "Sent to SOAR".equalsIgnoreCase(row.soarStatus) ? "green" : "red";
|
|
|
| html.append("<tr>")
|
| .append("<td><b>").append(row.invoiceNumber).append("</b></td>")
|
| .append("<td>").append(StringUtils.defaultString(row.contractNumber, "N/A")).append("</td>")
|
| .append("<td>").append(StringUtils.defaultString(row.clientName, "N/A")).append("</td>")
|
| .append("<td>").append(StringUtils.defaultString(row.clientId, "N/A")).append("</td>")
|
| .append("<td>").append(StringUtils.defaultString(row.customerName, "N/A")).append("</td>")
|
| .append("<td>").append(StringUtils.defaultString(row.lastUpdatedOn, "N/A")).append("</td>")
|
| .append("<td style='color:").append(hrcColor).append("; font-weight:bold;'>").append(row.hrcStatus).append("</td>")
|
| .append("<td style='color:").append(soarColor).append("; font-weight:bold;'>").append(row.soarStatus).append("</td>")
|
|
|
| // First HR Execution Metrics
|
| .append("<td>").append(StringUtils.defaultString(row.firstHrZipFilename, "N/A")).append("</td>")
|
| .append("<td>").append(StringUtils.defaultString(row.firstHrCsvFileName, "N/A")).append("</td>")
|
| .append("<td>").append(StringUtils.defaultString(row.firstHrFileSequence, "N/A")).append("</td>")
|
| .append("<td>").append(StringUtils.defaultString(row.firstHrFileRowNumber, "N/A")).append("</td>")
|
| .append("<td>").append(StringUtils.defaultString(row.firstHrFileCreatedUtcDate, "N/A")).append("</td>")
|
|
|
| // Latest HR Execution Metrics
|
| .append("<td>").append(StringUtils.defaultString(row.latestHrZipFilename, "N/A")).append("</td>")
|
| .append("<td>").append(StringUtils.defaultString(row.latestHrCsvFileName, "N/A")).append("</td>")
|
| .append("<td>").append(StringUtils.defaultString(row.latestHrFileSequence, "N/A")).append("</td>")
|
| .append("<td>").append(StringUtils.defaultString(row.latestHrFileRowNumber, "N/A")).append("</td>")
|
| .append("<td>").append(StringUtils.defaultString(row.latestHrFileCreatedUtcDate, "N/A")).append("</td>")
|
|
|
| // Downstream SOAR Execution Metrics
|
| .append("<td>").append(StringUtils.defaultString(row.soarLatestHrZipFilename, "N/A")).append("</td>")
|
| .append("<td>").append(StringUtils.defaultString(row.soarLatestHrCsvFileName, "N/A")).append("</td>")
|
| .append("<td>").append(StringUtils.defaultString(row.soarLatestHrFileSequence, "N/A")).append("</td>")
|
| .append("<td>").append(StringUtils.defaultString(row.soarLatestHrFileRowNumber, "N/A")).append("</td>")
|
| .append("<td>").append(StringUtils.defaultString(row.soarLatestHrFileCreatedUtcDate, "N/A")).append("</td>")
|
| .append("</tr>");
|
| }
|
| html.append("</table></body></html>");
|
| return html.toString();
|
| }
|
|
|
| /**
|
| * Dispatches summary emails with full dashboard matrix logs and appends the
|
| * output CSV master document onto the outgoing mailing stream as an attachment asset.
|
| */
|
| private void attachFileToEmail(String folderPath, String reportName, String email, Integer accountId) {
|
| LOG.debug("Entering into attachFileToEmail processing routine.");
|
|
|
| HRCEmailUtil hrcEmailUtil = (HRCEmailUtil) SpringApplicationContext.getBean("hrcEmailUtil");
|
| String fromAddress = AgentProperties.getProperty("cpa.mail.aggregatedData.notifier.fromAddress");
|
| if (StringUtils.isBlank(fromAddress)) {
|
| fromAddress = "cpa.notifications@notify.highradius.com";
|
| }
|
|
|
| String[] arrayOfToAddresses = null;
|
| if (email == null) {
|
| String defaultNotifierList = AgentProperties.getProperty("CPA_AGENT.monitor.Notifier.toAddress");
|
| arrayOfToAddresses = StringUtils.isNotBlank(defaultNotifierList) ? defaultNotifierList.split(",") : new String[]{"cpa.notifications@highradius.com"};
|
| } else {
|
| arrayOfToAddresses = email.split(",");
|
| }
|
|
|
| String subject = reportName + "_" + new SimpleDateFormat("MM/dd/yyyy").format(new Date());
|
| String compiledHtmlBody = htmlBodyReportSummaryBuilder();
|
|
|
| try {
|
| List<String> to = Arrays.asList(arrayOfToAddresses);
|
| ArrayList<String> attachmentsList = new ArrayList<String>();
|
|
|
| // Verify file presence before adding attachment pointer
|
| File masterAttachmentAsset = new File(folderPath);
|
| if (masterAttachmentAsset.exists() && masterAttachmentAsset.length() > 0) {
|
| attachmentsList.add(folderPath);
|
| } else {
|
| LOG.warn("Master report asset was empty or missing from server workspace. Dispatching text matrix summary only.");
|
| }
|
|
|
| SendEmailRequest sendEmailRequest = new SendEmailRequest(
|
| accountId,
|
| com.highradius.util.cpa.StringConstants.CPA_PRODUCT,
|
| ThreadContext.get(StringConstants.LANGUAGE_CODE)
|
| ).body(compiledHtmlBody).from(fromAddress).to(to).subject(subject).attachMents(attachmentsList);
|
|
|
| hrcEmailUtil.sendEmailWithoutCatchingException(sendEmailRequest);
|
| LOG.info("Reconciliation notification matrix safely dispatched to: " + to.toString());
|
|
|
| } catch (Exception em) {
|
| LOG.error("Failed while sending report matrix mail attachment stack trace error: " + em.getMessage(), em);
|
|
|
| // Fallback block handles mail transmission exceptions (e.g. SMTP 552 Size Overflows)
|
| if (em.getMessage() != null && em.getMessage().contains("552")) {
|
| String internalSubject = "Location of report file";
|
| String locationOverflowNotice = "Please find the report file at \"" + folderPath + "\" location as the data size exceeds standard mail exchange boundaries.";
|
|
|
| try {
|
| List<String> toFallback = Arrays.asList(arrayOfToAddresses);
|
| SendEmailRequest fallbackEmailRequest = new SendEmailRequest(
|
| accountId,
|
| com.highradius.util.cpa.StringConstants.CPA_PRODUCT,
|
| ThreadContext.get(StringConstants.LANGUAGE_CODE)
|
| ).body(locationOverflowNotice).from(fromAddress).to(toFallback).subject(internalSubject);
|
|
|
| hrcEmailUtil.sendEmailWithoutCatchingException(fallbackEmailRequest);
|
| } catch (Exception fallbackEx) {
|
| LOG.error("Critical failure dropping size overflow path notification to exchange server context: " + fallbackEx.getMessage(), fallbackEx);
|
| }
|
| }
|
| }
|
| LOG.debug("Exiting safely from attachFileToEmail operation hook.");
|
| }
|
|
|
| private static class ReconciliationRow {
|
| public String invoiceNumber = "";
|
| public String contractNumber = "";
|
| public String clientName = "";
|
| public String clientId = "";
|
| public String customerName = "";
|
| public String lastUpdatedOn = "";
|
| public String hrcStatus = "";
|
| public String soarStatus = "";
|
|
|
| public String firstHrFileDate = "";
|
| public String firstHrFileSequence = "";
|
| public String firstHrFileCreatedUtcDate = "";
|
| public String firstHrCsvFileName = "";
|
| public String firstHrZipFilename = "";
|
| public String firstHrFileRowNumber = "";
|
|
|
| public String latestHrFileDate = "";
|
| public String latestHrFileSequence = "";
|
| public String latestHrFileCreatedUtcDate = "";
|
| public String latestHrCsvFileName = "";
|
| public String latestHrZipFilename = "";
|
| public String latestHrFileRowNumber = "";
|
|
|
| public String soarLatestHrFileDate = "";
|
| public String soarLatestHrFileSequence = "";
|
| public String soarLatestHrFileCreatedUtcDate = "";
|
| public String soarLatestHrCsvFileName = "";
|
| public String soarLatestHrZipFilename = "";
|
| public String soarLatestHrFileRowNumber = "";
|
| }
|
| }
|