DateFormat caldf = new SimpleDateFormat("MM/dd/yyyy");
Calendar todaydate = Calendar.getInstance();
System.out.println("Custom Task " + caldf.format(todaydate.getTime()));
log.info("Custom Task " + caldf.format(todaydate.getTime()));
CustomParamTaskResultImpl result = new CustomParamTaskResultImpl();
result.setExecutionWasSuccessful(false);
try
{
String type = new String();
String date = new String();
//Get values
QueryMultiBoResult namedResult = tririgaws.runNamedQueryMultiBo(null, "triHelper", "cstSAPOutbound","tgt - Classloader - SAPOutbound - Manager Query", null, 1, 10);
log.info("Query Total: " + namedResult.getTotalResults());
//List<String[]> dataset = new ArrayList<String[]>();
for (QueryMultiBoResponseHelper helper : namedResult.getQueryMultiBoResponseHelpers()) {
int columnCount = helper.getQueryMultiBoResponseColumns().length;
String[] row = new String[columnCount];
for (QueryMultiBoResponseColumn column : helper.getQueryMultiBoResponseColumns()) {
int columnIndex = column.getIndex();
row[columnIndex] = column.getValue();
log.info("Column: [" + column.getIndex() + "] " + column.getLabel() + " Value: " + column.getValue());
//Setting Type
if(column.getIndex() == 0){
type = column.getValue();
}
//Setting Date
if(column.getIndex() == 2 ){
log.info("Column Index " + column.getIndex());
if (!column.getValue().isEmpty()){
log.info("Date is not empty");
date = column.getValue();
}
else{
log.info("Date Is EMPTY!!");
}
}
}
//dataset.add(row);
}
log.info("Type: " + type);
//Set date to yesterday's date value not provided
if(date.length() < 2 ){
Date yesterday = new Date(new Date().getTime() - 86400000);
date = caldf.format(yesterday);
}else{
log.info("date has value");
}
log.info("Date: " + date);
//Setting up filter for
Date start = new SimpleDateFormat("MM/dd/yyyy").parse(date);
Filter filterFrom = new Filter();
filterFrom.setDataType(335);
filterFrom.setFieldName("triFinalCompletionDA");
filterFrom.setOperator(10);
filterFrom.setValue(Long.toString(start.getTime()));
start = new Date((Long) (start.getTime()));
log.info("filter date : "+ start);
log.info("filter value : "+ Long.toString(start.getTime()));
//Setting up filter for MultiBoQuery
Filter[] datefilter = new Filter[1];
datefilter[0] = filterFrom;
//TODO CONTRACTS and CHANGE ORDERS
if (type.equalsIgnoreCase("CONTRACT")){
System.out.println("Contract Called");
log.info("Contract Called");
//TODO Setting up file writer for Contracts DTL file
String condtlpath = "\\\\dtcnit" + File.separator + "FTP" + File.separator + "users" + File.separator + "tarftp" + File.separator + "Prod" + File.separator + "OUT"
+ File.separator + "FSSP.MM.TCPM.POREQ.DTL.UNSORTED";
File condtlfile = new File(condtlpath);
if(condtlfile.exists()){
condtlfile.delete();
log.info("Contract File found and deleted!");
}
try {
condtlfile.createNewFile();
log.info("New Contract file created!");
} catch (IOException e2) {
log.info("Cannot create new Contract file!");
e2.printStackTrace();
}
FileWriter condtlfw = null;
try {
condtlfw = new FileWriter(condtlpath);
log.info("File Writer for Contract initialized");
} catch (IOException e1) {
log.info("ERROR! File Writer for Contract failed to initialized");
e1.printStackTrace();
}
//CONTRACT DTL QUERY
QueryMultiBoResult CON_DTL = tririgaws.runNamedQueryMultiBo(null, "triCostItem", "triContractCostBreakdownItems" , "tgt - Classloader - Integrations OUTBOUND - SAP Contract - DTL" , datefilter, 1, 999999);
int contotaldtl = CON_DTL.getTotalResults();
log.info("Contract DTL Records Found: " + contotaldtl);
//List<String[]> condtlDS = new ArrayList<String[]>();
for (QueryMultiBoResponseHelper condtlhelper : CON_DTL.getQueryMultiBoResponseHelpers()) {
int columnCount = condtlhelper.getQueryMultiBoResponseColumns().length;
String[] row = new String[columnCount];
for (QueryMultiBoResponseColumn condtlcolumn : condtlhelper.getQueryMultiBoResponseColumns()) {
int columnIndex = condtlcolumn.getIndex();
row[columnIndex] = condtlcolumn.getValue();
//testing fw
//formatting for Contract ID
if(columnIndex == 0){
String contractid = condtlcolumn.getValue();
if(contractid.length() < 12){
contractid = contractid.substring(0) + String.format("%12s","").substring(contractid.length());
}
else if(contractid.length() >= 12){
contractid = (contractid.substring(contractid.length()-12));
}
condtlfw.write(contractid);
}
//formatting for PO Number
else if(columnIndex == 1){
String POnum = condtlcolumn.getValue();
if (POnum == null) {
POnum = "0000000000";
}
else if(POnum.length() < 10) {
POnum = ("0000000000" + POnum).substring(POnum.length());
}
else if (POnum.length() >= 10) {
POnum = POnum.substring(POnum.length() - 10);
}
condtlfw.write(POnum);
}
//formatting for Change Order Number
else if(columnIndex == 2){
String changeOrderNumber = condtlcolumn.getValue();
if (changeOrderNumber == null){
changeOrderNumber = "000";
} else if (changeOrderNumber.length() < 3){
changeOrderNumber = ("000" + changeOrderNumber).substring(changeOrderNumber.length());
} else if (changeOrderNumber.length() >= 3){
changeOrderNumber = changeOrderNumber.substring(changeOrderNumber.length()-3);
}
condtlfw.write(changeOrderNumber);
}
//formatting for Line Number
else if(columnIndex == 3){
String lineNumber = condtlcolumn.getValue();
if (lineNumber.length() < 6) {
lineNumber = ("000000" + lineNumber).substring(lineNumber.length());
} else if (lineNumber.length() >= 6) {
lineNumber = lineNumber.substring(lineNumber.length() - 6);
}
condtlfw.write(lineNumber);
}
//formatting for Location
else if(columnIndex == 4){
String loc = condtlcolumn.getValue();
if (loc.length() < 4) {
loc = ("0000" + loc).substring(loc.length());
} else if (loc.length() >= 4) {
loc = loc.substring(loc.length() - 4);
}
condtlfw.write(loc);
}
//formatting for Project Number
else if(columnIndex == 5){
String projNum = condtlcolumn.getValue();
if (projNum.length() < 8){
projNum = ("00000000" + projNum).substring(projNum.length());
}else if (projNum.length() >= 8) {
projNum = projNum.substring(projNum.length() - 8);
}
condtlfw.write(projNum);
}
//formatting for CSI codes
else if(columnIndex == 6){
String csiCode = condtlcolumn.getValue();
if (csiCode.length() < 6){
csiCode = ("00000000" + csiCode).substring(csiCode.length());
}else if (csiCode.length() >= 6) {
csiCode = csiCode.substring(csiCode.length() - 6);
}
condtlfw.write(csiCode);
}
//formatting for Amount
else if(columnIndex == 7){
String amount = condtlcolumn.getValue();
amount = amount.replaceAll("[^\\d.]", "");
DecimalFormat df = new DecimalFormat("000000000000.00");
String formatamount = df.format(Double.parseDouble(amount));
condtlfw.write(formatamount);
}
//Formatting for CSI Description
else if(columnIndex == 8){
String csiDescription = condtlcolumn.getValue();
if(csiDescription.length() < 30){
csiDescription = csiDescription.substring(0) + String.format("%30s","").substring(csiDescription.length());
}
else if(csiDescription.length() >= 30){
csiDescription = csiDescription.substring(0,30);
}
condtlfw.write(csiDescription);
}
//Formatting for last Extract Date
else if(columnIndex == 9){
String lastExtractDate;
DateFormat dateFormat = new SimpleDateFormat("yyyyMMdd");
Date today = new Date();
lastExtractDate = dateFormat.format(today);
condtlfw.write(lastExtractDate);
}
//Formatting for site
else if(columnIndex == 10){
String site = condtlcolumn.getValue();
if (site.length() < 4) {
site = ("0000" + site).substring(site.length());
} else if (site.length() >= 4) {
site = site.substring(site.length() - 4);
}
condtlfw.write(site);
}
//Formatting for WBS
else if (columnIndex == 11){
String wbs = condtlcolumn.getValue();
if(wbs.length() < 21){
wbs = wbs.substring(0) + String.format("%21s","").substring(wbs.length());
}
else if(wbs.length() >= 21){
wbs = (wbs.substring(wbs.length() - 21));
}
condtlfw.write(wbs);
}
//Formatting for Cost Center
else if(columnIndex == 12){
String costCenter = String.format("%8s","");
condtlfw.write(costCenter);
}
//Formatting for Internal Order
else if(columnIndex == 13){
String internalOrder = String.format("%8s","");
condtlfw.write(internalOrder);
}
}//end of for
//condtlDS.add(row);
//testing fw
condtlfw.write(System.getProperty("line.separator"));
}
//CHANGE ORDER DTL QUERY
QueryMultiBoResult COS_DTL = tririgaws.runNamedQueryMultiBo(null, "triCostItem", "triChangeOrderCostBreakdownItems", "tgt - Classloader - Integrations OUTBOUND - SAP Change Order - DTL", datefilter, 1, 999999);
int costotaldtl = COS_DTL.getTotalResults();
log.info("Change Order DTL Records Found: " + costotaldtl);
//List<String[]> cosdtlDS = new ArrayList<String[]>();
for (QueryMultiBoResponseHelper cosdtlhelper : COS_DTL.getQueryMultiBoResponseHelpers()) {
int columnCount = cosdtlhelper.getQueryMultiBoResponseColumns().length;
String[] row = new String[columnCount];
for (QueryMultiBoResponseColumn cosdtlcolumn : cosdtlhelper.getQueryMultiBoResponseColumns()) {
int columnIndex = cosdtlcolumn.getIndex();
row[columnIndex] = cosdtlcolumn.getValue();
//adding null filter
//testing fw
//formatting for Contract ID
if(columnIndex == 0){
String contractid = cosdtlcolumn.getValue();
if(contractid.length() < 12){
contractid = contractid.substring(0) + String.format("%12s","").substring(contractid.length());
}
else if(contractid.length() >= 12){
contractid = (contractid.substring(contractid.length()-12));
}
condtlfw.write(contractid);
}
//formatting for PO Number
else if(columnIndex == 1){
String POnum = cosdtlcolumn.getValue();
if (POnum == null) {
POnum = "0000000000";
}
else if(POnum.length() < 10) {
POnum = ("0000000000" + POnum).substring(POnum.length());
}
else if (POnum.length() >= 10) {
POnum = POnum.substring(POnum.length() - 10);
}
condtlfw.write(POnum);
}
//formatting for Change Order Number
else if(columnIndex == 2){
String changeOrderNumber = cosdtlcolumn.getValue();
if (changeOrderNumber == null){
changeOrderNumber = "000";
} else if (changeOrderNumber.length() < 3){
changeOrderNumber = ("000" + changeOrderNumber).substring(changeOrderNumber.length());
} else if (changeOrderNumber.length() >= 3){
changeOrderNumber = changeOrderNumber.substring(changeOrderNumber.length()-3);
}
condtlfw.write(changeOrderNumber);
}
//formatting for Line Number
else if(columnIndex == 3){
String lineNumber = cosdtlcolumn.getValue();
if (lineNumber.length() < 6) {
lineNumber = ("000000" + lineNumber).substring(lineNumber.length());
} else if (lineNumber.length() >= 6) {
lineNumber = lineNumber.substring(lineNumber.length() - 6);
}
condtlfw.write(lineNumber);
}
//formatting for Location
else if(columnIndex == 4){
String loc = cosdtlcolumn.getValue();
if (loc.length() < 4) {
loc = ("0000" + loc).substring(loc.length());
} else if (loc.length() >= 4) {
loc = loc.substring(loc.length() - 4);
}
condtlfw.write(loc);
}
//formatting for Project Number
else if(columnIndex == 5){
String projNum = cosdtlcolumn.getValue();
if (projNum.length() < 8){
projNum = ("00000000" + projNum).substring(projNum.length());
}else if (projNum.length() >= 8) {
projNum = projNum.substring(projNum.length() - 8);
}
condtlfw.write(projNum);
}
//formatting for CSI codes
else if(columnIndex == 6){
String csiCode = cosdtlcolumn.getValue();
if (csiCode.length() < 6){
csiCode = ("00000000" + csiCode).substring(csiCode.length());
}else if (csiCode.length() >= 6) {
csiCode = csiCode.substring(csiCode.length() - 6);
}
condtlfw.write(csiCode);
}
//formatting for Amount
else if(columnIndex == 7){
String amount = cosdtlcolumn.getValue();
amount = amount.replaceAll("[^\\d.]", "");
DecimalFormat df = new DecimalFormat("000000000000.00");
String formatamount = df.format(Double.parseDouble(amount));
condtlfw.write(formatamount);
}
//Formatting for CSI Description
else if(columnIndex == 8){
String csiDescription = cosdtlcolumn.getValue();
if(csiDescription.length() < 30){
csiDescription = csiDescription.substring(0) + String.format("%30s","").substring(csiDescription.length());
}
else if(csiDescription.length() >= 30){
csiDescription = csiDescription.substring(0,30);
}
condtlfw.write(csiDescription);
}
//Formatting for last Extract Date
else if(columnIndex == 9){
DateFormat dateFormat = new SimpleDateFormat("yyyyMMdd");
Date today = new Date();
String lastExtractDate = dateFormat.format(today);
condtlfw.write(lastExtractDate);
}
//Formatting for site
else if(columnIndex == 10){
String site = cosdtlcolumn.getValue();
if (site.length() < 4) {
site = ("0000" + site).substring(site.length());
} else if (site.length() >= 4) {
site = site.substring(site.length() - 4);
}
condtlfw.write(site);
}
//Formatting for WBS
else if (columnIndex == 11){
String wbs = cosdtlcolumn.getValue();
if(wbs.length() < 21){
wbs = wbs.substring(0) + String.format("%21s","").substring(wbs.length());
}
else if(wbs.length() >= 21){
wbs = (wbs.substring(wbs.length()-21));
}
condtlfw.write(wbs);
}
//Formatting for Cost Center
else if(columnIndex == 12){
String costCenter = String.format("%8s","");
condtlfw.write(costCenter);
}
//Formatting for Internal Order
else if(columnIndex == 13){
String internalOrder = String.format("%8s","");
condtlfw.write(internalOrder);
}
// log.info("Column: [" + column.getIndex() + "] " + column.getLabel() + " Value: " + column.getValue());
}
//cosdtlDS.add(row);
//testing fw
condtlfw.write(System.getProperty("line.separator"));
}
//closing condtlfw
condtlfw.flush();
condtlfw.close();
int totaldtl = 0;
BigDecimal DTLTotal = new BigDecimal ("0.00");
// if (costotaldtl + contotaldtl > 0){
stripDuplicatesFromFile(condtlpath);
totaldtl = count(condtlpath);
DTLTotal = sum(condtlpath, type, false);
log.info("Contract DTL completed");
// }
// else{
// File errorfile = new File(condtlpath.substring(0, condtlpath.length() - 9) + ".ERROR");
// condtlfile.renameTo(errorfile);
// condtlfile.delete();
// log.info("Contract DTL errored out!");
// }
//TODO Setting up file writer for Contracts HDR file
String conhdrpath = "\\\\dtcnit" + File.separator + "FTP" + File.separator + "users" + File.separator + "tarftp" + File.separator + "Prod" + File.separator + "OUT"
+ File.separator + "FSSP.MM.TCPM.POREQ.HDR.UNSORTED";
File conhdrfile = new File(conhdrpath);
if(conhdrfile.exists()){
conhdrfile.delete();
log.info("Contract File found and deleted!");
}
try {
conhdrfile.createNewFile();
log.info("New Contract file created!");
} catch (IOException e2) {
log.info("Cannot create new Contract file!");
e2.printStackTrace();
}
FileWriter conhdrfw = null;
try {
conhdrfw = new FileWriter(conhdrpath);
log.info("File Writer for Contract initialized");
} catch (IOException e1) {
log.info("ERROR! File Writer for Contract failed to initialized");
e1.printStackTrace();
}
//TODO CONTRACT HDR QUERY
QueryMultiBoResult CON_HDR = tririgaws.runNamedQueryMultiBo(null, "triCostItem", "triContractCostBreakdownItems" , "tgt - Classloader - Integrations OUTBOUND - SAP Contract - HDR" , datefilter, 1, 999999);
int contotalhdr = CON_HDR.getTotalResults();
log.info("Contract HDR Records found: " + contotalhdr);
//List<String[]> conhdrDS = new ArrayList<String[]>();
for (QueryMultiBoResponseHelper conhdrhelper : CON_HDR.getQueryMultiBoResponseHelpers()) {
int columnCount = conhdrhelper.getQueryMultiBoResponseColumns().length;
String[] row = new String[columnCount];
for (QueryMultiBoResponseColumn conhdrcolumn : conhdrhelper.getQueryMultiBoResponseColumns()) {
int columnIndex = conhdrcolumn.getIndex();
row[columnIndex] = conhdrcolumn.getValue();
//log.info("Column Value: " + conhdrcolumn.getValue());
//formatting for Contract ID
if(columnIndex == 0){
String contractid = conhdrcolumn.getValue();
if(contractid.length() < 12){
contractid = contractid.substring(0) + String.format("%12s","").substring(contractid.length());
}
else if(contractid.length() >= 12){
contractid = (contractid.substring(contractid.length()-12));
}
conhdrfw.write(contractid);
}
//formatting for PO Number
else if(columnIndex == 1){
String POnum = conhdrcolumn.getValue();
if (POnum == null) {
POnum = "0000000000";
}
else if(POnum.length() < 10) {
POnum = ("0000000000" + POnum).substring(POnum.length());
}
else if (POnum.length() >= 10) {
POnum = POnum.substring(POnum.length() - 10);
}
conhdrfw.write(POnum);
}
//formatting for Change Order Number
else if(columnIndex == 2){
String changeOrderNumber = conhdrcolumn.getValue();
if (changeOrderNumber == null){
changeOrderNumber = "000";
} else if (changeOrderNumber.length() < 3){
changeOrderNumber = ("000" + changeOrderNumber).substring(changeOrderNumber.length());
} else if (changeOrderNumber.length() >= 3){
changeOrderNumber = changeOrderNumber.substring(changeOrderNumber.length()-3);
}
conhdrfw.write(changeOrderNumber);
}
//formatting for Vendor Number
else if(columnIndex == 3){
String vendorNumber = conhdrcolumn.getValue();
if (vendorNumber == null) {
vendorNumber = "000000000";
} else if (vendorNumber.length() < 9){
vendorNumber = ("000000000" + vendorNumber).substring(vendorNumber.length());
} else if (vendorNumber.length() >= 9){
vendorNumber = vendorNumber.substring(vendorNumber.length() - 9);
}
conhdrfw.write(vendorNumber);
}
//Formatting for last Extract Date
else if(columnIndex == 4){
DateFormat dateFormat = new SimpleDateFormat("yyyyMMdd");
Date today = new Date();
String lastExtractDate = dateFormat.format(today);
conhdrfw.write(lastExtractDate);
}
}
conhdrfw.write(System.getProperty("line.separator"));
//conhdrDS.add(row);
}
//TODO CHANGE ORDER HEADER QUERY
QueryMultiBoResult COS_HDR = tririgaws.runNamedQueryMultiBo(null, "triCostItem", "triChangeOrderCostBreakdownItems", "tgt - Classloader - Integrations OUTBOUND - SAP Change Orders - HDR", datefilter, 1, 999999);
int costotalhdr = COS_HDR.getTotalResults();
log.info("Change Order HDR Records Found: " + costotalhdr);
//List<String[]> coshdrDS = new ArrayList<String[]>();
for (QueryMultiBoResponseHelper coshdrhelper : COS_HDR.getQueryMultiBoResponseHelpers()) {
int columnCount = coshdrhelper.getQueryMultiBoResponseColumns().length;
String[] row = new String[columnCount];
for (QueryMultiBoResponseColumn coshdrcolumn : coshdrhelper.getQueryMultiBoResponseColumns()) {
int columnIndex = coshdrcolumn.getIndex();
row[columnIndex] = coshdrcolumn.getValue();
//log.info("Column Value: " + coshdrcolumn.getValue());
//formatting for Contract ID
if(columnIndex == 0){
String contractid = coshdrcolumn.getValue();
if(contractid.length() < 12){
contractid = contractid.substring(0) + String.format("%12s","").substring(contractid.length());
}
else if(contractid.length() >= 12){
contractid = (contractid.substring(contractid.length()-12));
}
conhdrfw.write(contractid);
}
//formatting for PO Number
else if(columnIndex == 1){
String POnum = coshdrcolumn.getValue();
if (POnum == null) {
POnum = "0000000000";
}
else if(POnum.length() < 10) {
POnum = ("0000000000" + POnum).substring(POnum.length());
}
else if (POnum.length() >= 10) {
POnum = POnum.substring(POnum.length() - 10);
}
conhdrfw.write(POnum);
}
//formatting for Change Order Number
else if(columnIndex == 2){
String changeOrderNumber = coshdrcolumn.getValue();
if (changeOrderNumber == null){
changeOrderNumber = "000";
} else if (changeOrderNumber.length() < 3){
changeOrderNumber = ("000" + changeOrderNumber).substring(changeOrderNumber.length());
} else if (changeOrderNumber.length() >= 3){
changeOrderNumber = changeOrderNumber.substring(changeOrderNumber.length()-3);
}
conhdrfw.write(changeOrderNumber);
}
//formatting for Vendor Number
else if(columnIndex == 3){
String vendorNumber = coshdrcolumn.getValue();
if (vendorNumber == null) {
vendorNumber = "000000000";
} else if (vendorNumber.length() < 9){
vendorNumber = ("000000000" + vendorNumber).substring(vendorNumber.length());
} else if (vendorNumber.length() >= 9){
vendorNumber = vendorNumber.substring(vendorNumber.length() - 9);
}
conhdrfw.write(vendorNumber);
}
//Formatting for last Extract Date
else if(columnIndex == 4){
DateFormat dateFormat = new SimpleDateFormat("yyyyMMdd");
Date today = new Date();
String lastExtractDate = dateFormat.format(today);
conhdrfw.write(lastExtractDate);
}
}
conhdrfw.write(System.getProperty("line.separator"));
//coshdrDS.add(row);
}
//closing conhdrfw
conhdrfw.flush();
conhdrfw.close();
int totalhdr = 0;
// File file = new File(condtlpath.substring(0, condtlpath.length() - 9) + ".ERROR");
// if( (contotalhdr + costotalhdr) > 0 && !file.exists()){
stripDuplicatesFromFile(conhdrpath);
totalhdr = count(conhdrpath);
log.info("Contract HDR File Completed");
// }
// else{
// File errorfile = new File(conhdrpath.substring(0, conhdrpath.length() - 9) + ".ERROR");
// conhdrfile.renameTo(errorfile);
// conhdrfile.delete();
// log.info("Contract HDR File errored out!");
// }
log.info("Total DTL records: " + totaldtl);
log.info("Total HDR records: " + totalhdr);
//Creating Contract CTL file
DateFormat dateFormat = new SimpleDateFormat("MM/dd/yyyy HH:mm:ss");
Date reportdate = new Date();
String Sdate = dateFormat.format(reportdate).substring(0, 10);
String time = dateFormat.format(reportdate).substring(11, 16);
String hdrcount = ("0000000" + Integer.toString(totalhdr)).substring(Integer.toString(totalhdr).length());
String dtlcount = ("0000000" + Integer.toString(totaldtl)).substring(Integer.toString(totaldtl).length());
String dtltotal = ("000000000000000" + DTLTotal.toString()).substring(DTLTotal.toString().length());
File ctlfile = null;
// File conerror = new File(condtlpath.substring(0, condtlpath.length() - 9) + ".ERROR");
// File conerror1 = new File(conhdrpath.substring(0, conhdrpath.length() - 9) + ".ERROR");
// if(!conerror.exists() && !conerror1.exists()){
condtlpath = sort(condtlpath);
conhdrpath = sort(conhdrpath);
ctlfile = new File("\\\\dtcnit" + File.separator + "FTP" + File.separator + "users" + File.separator + "tarftp" + File.separator + "Prod" + File.separator + "OUT" + File.separator + "FSSP.MM.TCPM.POREQ.CTL");
// }else{
// ctlfile = new File("\\\\dtcnit" + File.separator + "FTP" + File.separator + "users" + File.separator + "tarftp" + File.separator + "Prod" + File.separator + "OUT" + File.separator + "FSSP.MM.TCPM.POREQ.CTL.ERROR");
// File errorfile = new File(conhdrpath + ".ERROR");
// boolean success = conhdrfile.renameTo(errorfile);
// if(success){
// conhdrfile.delete();
// }
// File errorfile1 = new File(condtlpath + ".ERROR");
// boolean success1 = condtlfile.renameTo(errorfile1);
// if(success1){
// condtlfile.delete();
// }
// }
FileWriter ctlfw = new FileWriter(ctlfile);
ctlfw.write("TCPM - Date: " + Sdate + System.getProperty("line.separator"));
ctlfw.write("Time: " + time + System.getProperty("line.separator"));
ctlfw.write("Data type Rec Count Amount" + System.getProperty("line.separator"));
ctlfw.write(" PO Header Lines " + hdrcount + System.getProperty("line.separator"));
ctlfw.write(" PO Detail Lines " + dtlcount + " " + dtltotal);
ctlfw.flush();
ctlfw.close();
//send to sftp - CFI ftp
// String SFTPHOST = "ftp.gocfi.com";
// int SFTPPORT = 22;
// String SFTPUSER = "tarftp";
// String SFTPPASS = "Tar_00274";
// String SFTPWORKINGDIR = "/PROD/";
//Target FTP
String SFTPHOST = "emft.target.com";
int SFTPPORT = 22;
String SFTPUSER = "svxtririga04";
String SFTPPASS = "tririga@123";
String SFTPWORKINGDIR = "/fssetl41";
Session session = null;
Channel channel = null;
ChannelSftp channelSftp = null;
try{
JSch jsch = new JSch();
session = jsch.getSession(SFTPUSER,SFTPHOST,SFTPPORT);
session.setPassword(SFTPPASS);
java.util.Properties config = new java.util.Properties();
config.put("StrictHostKeyChecking", "no");
session.setConfig(config);
session.connect();
channel = session.openChannel("sftp");
channel.connect();
channelSftp = (ChannelSftp)channel;
channelSftp.cd(SFTPWORKINGDIR);
File ctl = new File(ctlfile.getPath());
channelSftp.put(new FileInputStream(ctl), ctl.getName());
File hdr = new File(condtlpath);
channelSftp.put(new FileInputStream(hdr), hdr.getName());
File dtl = new File(condtlpath);
channelSftp.put(new FileInputStream(dtl), dtl.getName());
}catch(Exception ex){
ex.printStackTrace();
}
log.info("End of Contract");
}//end of Contracts and Change Orders
//TODO Payments
else if (type.equalsIgnoreCase("PAYMENT")){
System.out.println("Payment Called");
log.info("Payment Called");
//TODO Setting up file writer for Payment DIS file
String paydispath = "\\\\dtcnit" + File.separator + "FTP" + File.separator + "users" + File.separator + "tarftp" + File.separator + "Prod" + File.separator + "OUT"
+ File.separator + "FSSP.AP.TCPM.POINVOICE.DIS.UNSORTED";
File paydisfile = new File(paydispath);
if(paydisfile.exists()){
paydisfile.delete();
log.info("Payment File found and deleted!");
}
try {
paydisfile.createNewFile();
log.info("New Payment file created!");
} catch (IOException e2) {
log.info("Cannot create new ContPaymentract file!");
e2.printStackTrace();
}
FileWriter paydisfw = null;
try {
paydisfw = new FileWriter(paydisfile);
log.info("File Writer for Payment initialized");
} catch (IOException e1) {
log.info("ERROR! File Writer for Payment failed to initialized");
e1.printStackTrace();
}
//TODO PAYMENT DIS FILE QUERY
QueryMultiBoResult PAY_DIS = tririgaws.runNamedQueryMultiBo(null, "triCostItem", "triContractInvoiceLineItem", "tgt - Classloader - Integrations OUTBOUND - SAP Payment Request - DIS", datefilter, 1, 999999);
// int paytotaldis = PAY_DIS.getTotalResults();
//List<String[]> paydisDS = new ArrayList<String[]>();
for (QueryMultiBoResponseHelper paydishelper : PAY_DIS.getQueryMultiBoResponseHelpers()) {
int columnCount = paydishelper.getQueryMultiBoResponseColumns().length;
String[] row = new String[columnCount];
for (QueryMultiBoResponseColumn paydiscolumn : paydishelper.getQueryMultiBoResponseColumns()) {
int columnIndex = paydiscolumn.getIndex();
row[columnIndex] = paydiscolumn.getValue();
//Formatting Vendor Number
if(columnIndex == 0){
String vendorNumber = paydiscolumn.getValue();
if (vendorNumber == null) {
vendorNumber = "000000000";
} else if (vendorNumber.length() < 9){
vendorNumber = ("000000000" + vendorNumber).substring(vendorNumber.length());
} else if (vendorNumber.length() >= 9){
vendorNumber = vendorNumber.substring(vendorNumber.length() - 9);
}
paydisfw.write(vendorNumber);
}
//Formatting Invoice
else if(columnIndex == 1 ){
String invoice = paydiscolumn.getValue();
if(invoice == null){
invoice = String.format("%25s", "");
}
else if (invoice.length() < 25){
invoice = invoice.substring(0) + String.format("%25s","").substring(invoice.length());
}
else if (invoice.length() >= 25){
invoice = invoice.substring(invoice.length()-3);
}
paydisfw.write(invoice);
}
//Formatting Invoice Type
else if(columnIndex == 2 ){
String invoiceType = paydiscolumn.getValue();
if(invoiceType == null){
invoiceType = String.format("%2s", "");
}
else if (invoiceType.length() < 2){
invoiceType = invoiceType.substring(0) + String.format("%2s","").substring(invoiceType.length());
}
else if (invoiceType.length() >= 2){
invoiceType = invoiceType.substring(invoiceType.length()-2);
}
paydisfw.write(invoiceType);
}
//Formatting Line Number
else if(columnIndex == 3 ){
String lineNumber = paydiscolumn.getValue();
if (lineNumber.length() < 5) {
lineNumber = ("00000" + lineNumber).substring(lineNumber.length());
} else if (lineNumber.length() >= 5) {
lineNumber = lineNumber.substring(lineNumber.length() - 5);
}
paydisfw.write(lineNumber);
}
//formatting for Rate
else if(columnIndex == 4){
String rate = paydiscolumn.getValue();
rate = rate.replaceAll("[^\\d.]", "");
DecimalFormat df = new DecimalFormat("000000000000.00");
String formatrate = df.format(Double.parseDouble(rate));
paydisfw.write(formatrate);
}
}
//paydisDS.add(row);
//testing fw
paydisfw.write(System.getProperty("line.separator"));
}
//testing fw
paydisfw.flush();
paydisfw.close();
int totaldis = 0;
BigDecimal DISTotal = new BigDecimal("0.00");
// if( paytotaldis > 0){
stripDuplicatesFromFile(paydispath);
totaldis = count(paydispath);
DISTotal = sum(paydispath, type, false);
log.info("Payment DIS file completed");
// }else{
// File errorfile = new File(paydispath.substring(0, paydispath.length() - 9) + ".ERROR");
// paydisfile.renameTo(errorfile);
// paydisfile.delete();
// log.info("Payment DIS file errored out!");
// }
//Setting up file writer for Payment INV file
String payinvpath = "\\\\dtcnit" + File.separator + "FTP" + File.separator + "users" + File.separator + "tarftp" + File.separator + "Prod" + File.separator + "OUT"
+ File.separator + "FSSP.AP.TCPM.POINVOICE.INV.UNSORTED";
File payinvfile = new File(payinvpath);
if(payinvfile.exists()){
payinvfile.delete();
log.info("Payment File found and deleted!");
}
try {
payinvfile.createNewFile();
log.info("New Payment file created!");
} catch (IOException e2) {
log.info("Cannot create new Payment file!");
e2.printStackTrace();
}
FileWriter payinvfw = null;
try {
payinvfw = new FileWriter(payinvfile);
log.info("File Writer for Payment initialized");
} catch (IOException e1) {
log.info("ERROR! File Writer for Payment failed to initialized");
e1.printStackTrace();
}
//PAYMENT INV FILE QUERY
QueryMultiBoResult PAY_INV = tririgaws.runNamedQueryMultiBo(null, "triPayment", "triContractInvoice", "tgt - Classloader - Integrations OUTBOUND - SAP Payment Request - INV", datefilter, 1, 999999);
//List<String[]> payinvDS = new ArrayList<String[]>();
// int paytotalinv = PAY_INV.getTotalResults();
for (QueryMultiBoResponseHelper payinvhelper : PAY_INV.getQueryMultiBoResponseHelpers()) {
int columnCount = payinvhelper.getQueryMultiBoResponseColumns().length;
String[] row = new String[columnCount];
for (QueryMultiBoResponseColumn payinvcolumn : payinvhelper.getQueryMultiBoResponseColumns()) {
int columnIndex = payinvcolumn.getIndex();
row[columnIndex] = payinvcolumn.getValue();
//log.info("Column: [" + column.getIndex() + "] " + column.getLabel() + " Value: " + column.getValue());
//Formatting Vendor Number
if(columnIndex == 0){
String vendorNumber = payinvcolumn.getValue();
if (vendorNumber == null) {
vendorNumber = "000000000";
} else if (vendorNumber.length() < 9){
vendorNumber = ("000000000" + vendorNumber).substring(vendorNumber.length());
} else if (vendorNumber.length() >= 9){
vendorNumber = vendorNumber.substring(vendorNumber.length() - 9);
}
payinvfw.write(vendorNumber);
}
//Formatting Invoice
else if(columnIndex == 1 ){
String invoice = payinvcolumn.getValue();
if(invoice == null){
invoice = String.format("%25s", "");
}
else if (invoice.length() < 25){
invoice = invoice.substring(0) + String.format("%25s","").substring(invoice.length());
}
else if (invoice.length() >= 25){
invoice = invoice.substring(invoice.length()-3);
}
payinvfw.write(invoice);
}
//Formatting Invoice Type
else if(columnIndex == 2 ){
String invoiceType = payinvcolumn.getValue();
if(invoiceType == null){
invoiceType = String.format("%2s", "");
}
else if (invoiceType.length() < 2){
invoiceType = invoiceType.substring(0) + String.format("%2s","").substring(invoiceType.length());
}
else if (invoiceType.length() >= 2){
invoiceType = invoiceType.substring(invoiceType.length()-2);
}
payinvfw.write(invoiceType);
}
//Formatting PO Number
else if(columnIndex == 3 ){
String POnum = payinvcolumn.getValue();
if (POnum == null) {
POnum = "0000000000";
}
else if(POnum.length() < 10) {
POnum = ("0000000000" + POnum).substring(POnum.length());
}
else if (POnum.length() >= 10) {
POnum = POnum.substring(POnum.length() - 10);
}
payinvfw.write(POnum);
}
//formatting for Rate
else if(columnIndex == 4){
String rate = payinvcolumn.getValue();
rate = rate.replaceAll("[^\\d.]", "");
DecimalFormat df = new DecimalFormat("000000000000.00");
String formatrate = df.format(Double.parseDouble(rate));
payinvfw.write(formatrate);
//log.info("Rate: "+formatrate);
}
//formatting for date
else if(columnIndex == 5){
String docdate = payinvcolumn.getValue();
String month = docdate.substring(0, 2);
String day = docdate.substring(3, 5);
String century = docdate.substring(6, 8);
String year = docdate.substring(8, 10);
docdate = century + year + month + day;
payinvfw.write(docdate.toString());
}
}
//payinvDS.add(row);
//testing fw
payinvfw.write(System.getProperty("line.separator"));
}
//testing fw
payinvfw.flush();
payinvfw.close();
int totalinv = 0;
BigDecimal INVTotal = new BigDecimal ("0.00");
// File file = new File(paydispath.substring(0, paydispath.length() - 9) + ".ERROR");
// if( paytotalinv > 0 && !file.exists()){
stripDuplicatesFromFile(payinvpath);
totalinv = count(payinvpath);
INVTotal = sum(payinvpath, type, true);
log.info("Payment INV file completed");
// }else{
// File errorfile = new File(payinvpath.substring(0, payinvpath.length() - 9) + ".ERROR");
// payinvfile.renameTo(errorfile);
// payinvfile.delete();
// log.info("Payment INV file errored out!");
// }
//Creating Payment CTL file
DateFormat dateFormat = new SimpleDateFormat("MM/dd/yyyy HH:mm:ss");
Date reportdate = new Date();
String Sdate = dateFormat.format(reportdate).substring(0, 10);
String time = dateFormat.format(reportdate).substring(11, 16);
String discount = ("0000000" + Integer.toString(totaldis)).substring(Integer.toString(totaldis).length());
String invcount = ("0000000" + Integer.toString(totalinv)).substring(Integer.toString(totalinv).length());
String INVtotal1 = String.format("%.2f", INVTotal);
String invtotal = ("000000000000000" + INVtotal1).substring(INVtotal1.length());
String DIStotal1 = String.format("%.2f", DISTotal);
String distotal = ("000000000000000" + DIStotal1).substring(DIStotal1.length());
File ctlfile = null;
// File payerror = new File(payinvpath.substring(0, payinvpath.length() - 9) + ".ERROR");
// File payerror1 = new File(paydispath.substring(0, paydispath.length() - 9) + ".ERROR");
// if( (invtotal.equals(distotal)) && totaldis > 0 && totalinv > 0 && !payerror.exists() && !payerror1.exists())
if( (invtotal.equals(distotal))) //check if totals match
{
paydispath = sort(paydispath);
payinvpath = sort(payinvpath);
ctlfile = new File("\\\\dtcnit" + File.separator + "FTP" + File.separator + "users" + File.separator + "tarftp" + File.separator + "Prod" + File.separator + "OUT" + File.separator + "FSSP.AP.TCPM.POINVOICE.CTL");
}
else{
ctlfile = new File("\\\\dtcnit" + File.separator + "FTP" + File.separator + "users" + File.separator + "tarftp" + File.separator + "Prod" + File.separator + "OUT" + File.separator + "FSSP.AP.TCPM.POINVOICE.CTL.ERROR");
File errorfile = new File(payinvpath + ".ERROR");
boolean success = payinvfile.renameTo(errorfile);
log.info("inv error file renamed: " + payinvpath);
File errorfile1 = new File(paydispath + ".ERROR");
boolean success1 = paydisfile.renameTo(errorfile1);
log.info("inv error file renamed: " + paydispath);
if(success){
payinvfile.delete();
}
if(success1){
paydisfile.delete();
}
}
FileWriter ctlfw = new FileWriter(ctlfile);
ctlfw.write("TCPM - Date: " + Sdate + System.getProperty("line.separator"));
ctlfw.write("Time: " + time + System.getProperty("line.separator"));
ctlfw.write("Data type Rec Count Amount"+ System.getProperty("line.separator"));
ctlfw.write(" PO Invoice Lines " + invcount + " " + invtotal + System.getProperty("line.separator"));
ctlfw.write(" PO Distrib Lines " + discount + " " + distotal);
ctlfw.flush();
ctlfw.close();
if( (invtotal.equals(distotal))) //check if totals match
{
//send to sftp - CFI ftp
// String SFTPHOST = "ftp.gocfi.com";
// int SFTPPORT = 22;
// String SFTPUSER = "tarftp";
// String SFTPPASS = "Tar_00274";
// String SFTPWORKINGDIR = "/PROD/";
//Target FTP
String SFTPHOST = "emft.target.com";
int SFTPPORT = 22;
String SFTPUSER = "svxtririga04";
String SFTPPASS = "tririga@123";
String SFTPWORKINGDIR = "/fssetl41";
Session session = null;
Channel channel = null;
ChannelSftp channelSftp = null;
try{
JSch jsch = new JSch();
session = jsch.getSession(SFTPUSER,SFTPHOST,SFTPPORT);
session.setPassword(SFTPPASS);
java.util.Properties config = new java.util.Properties();
config.put("StrictHostKeyChecking", "no");
session.setConfig(config);
session.connect();
channel = session.openChannel("sftp");
channel.connect();
channelSftp = (ChannelSftp)channel;
channelSftp.cd(SFTPWORKINGDIR);
File ctl = new File(ctlfile.getPath());
channelSftp.put(new FileInputStream(ctl), ctl.getName());
File hdr = new File(payinvpath);
channelSftp.put(new FileInputStream(hdr), hdr.getName());
File dtl = new File(paydispath);
channelSftp.put(new FileInputStream(dtl), dtl.getName());
}catch(Exception ex){
ex.printStackTrace();
}
}
log.info("End of Payment");
}//end of Payments
//Retainage
else if (type.equalsIgnoreCase("RETAINAGE")){
System.out.println("Retainage Called");
log.info("Retainage Called");
//TODO Setting up file writer for Retainage DIS file
String retdispath = "\\\\dtcnit" + File.separator + "FTP" + File.separator + "users" + File.separator + "tarftp" + File.separator + "Prod" + File.separator + "OUT"
+ File.separator + "FSSP.AP.TCPM.FIINVOICE.DIS.UNSORTED";
File retdisfile = new File(retdispath);
if(retdisfile.exists()){
retdisfile.delete();
log.info("Retainage File found and deleted!");
}
try {
retdisfile.createNewFile();
log.info("New Retainage file created!");
} catch (IOException e2) {
log.info("Cannot create new Retainage file!");
e2.printStackTrace();
}
FileWriter retdisfw = null;
try {
retdisfw = new FileWriter(retdisfile);
log.info("File Writer for Retainage initialized");
} catch (IOException e1) {
log.info("ERROR! File Writer for Retainage failed to initialized");
e1.printStackTrace();
}
QueryMultiBoResult RET_DIS = tririgaws.runNamedQueryMultiBo(null, "triCostItem", "triContractInvoiceLineItem", "tgt - Classloader - Integrations OUTBOUND - SAP Retainage - DIS", datefilter, 1, 999999);
// int rettotaldis = RET_DIS.getTotalResults();
int rowcount = 0;
for (QueryMultiBoResponseHelper retdishelper : RET_DIS.getQueryMultiBoResponseHelpers()) {
int columnCount = retdishelper.getQueryMultiBoResponseColumns().length;
String[] row = new String[columnCount];
boolean sign = false;
String vendorNumber = ""; //9
String loc = "";
String invoice = ""; //25
String invoiceType = ""; //1
String invoiceDate = ""; //8
rowcount = rowcount + 1;
String seqnum = Integer.toString(rowcount); //3
String description = ""; //50
String amount = ""; //16
String companycode = ""; //4
String wbs = ""; //21
String costcode = "063100"; //6
String profitcenter = ""; //4
String costcenter = String.format("%8s",""); //8
String internalorder = String.format("%8s","");//8
for (QueryMultiBoResponseColumn retdiscolumn : retdishelper.getQueryMultiBoResponseColumns()) {
int columnIndex = retdiscolumn.getIndex();
row[columnIndex] = retdiscolumn.getValue();
//log.info("Column: [" + column.getIndex() + "] " + column.getLabel() + " Value: " + column.getValue());
//Formatting Vendor Number
if(columnIndex == 0){
vendorNumber = retdiscolumn.getValue();
if (vendorNumber == null) {
vendorNumber = "000000000";
} else if (vendorNumber.length() < 9){
vendorNumber = ("000000000" + vendorNumber).substring(vendorNumber.length());
} else if (vendorNumber.length() >= 9){
vendorNumber = vendorNumber.substring(vendorNumber.length() - 9);
}
}
//Formatting Location
else if(columnIndex == 1 ){
loc = retdiscolumn.getValue();
if (loc.length() < 4) {
loc = ("0000" + loc).substring(loc.length());
} else if (loc.length() >= 4) {
loc = loc.substring(loc.length() - 4);
}
}
//Formatting Invoice
else if(columnIndex == 2 ){
invoice = retdiscolumn.getValue();
if (invoice == null) {
invoice = "";
} else if (invoice.length() <= 18) {
invoice = invoice + "-R-" + loc;
} else if (invoice.length() > 18) {
invoice = invoice.substring(0, 18) + "-R-" + loc;
}
if(invoice.length() < 25){
invoice = invoice + String.format("%25s", "").substring(invoice.length());
}
else{
invoice = invoice.substring(0,25);
}
}
//formatting for Amount
else if(columnIndex == 3){
amount = retdiscolumn.getValue();
if(amount.contains("-")){
sign = true;
}
amount = amount.replaceAll("[^\\d.]", "");
DecimalFormat df = null;
if(sign){
df = new DecimalFormat("-000000000000.00");
}else{
df = new DecimalFormat("+000000000000.00");
}
amount = df.format(Double.parseDouble(amount));
}
//Formatting Invoice Type
else if(columnIndex == 4 ){
invoiceType = retdiscolumn.getValue();
if(invoiceType == null){
invoiceType = String.format("%1s", "");
}
if(sign){
invoiceType = "C";
}
else{
invoiceType = " ";
}
}
//Formatting Invoice Date
else if(columnIndex == 5 ){
invoiceDate = retdiscolumn.getValue();
if (invoiceDate == null) {
invoiceDate = "";
} else {
String month = invoiceDate.substring(0, 2);
String day = invoiceDate.substring(3, 5);
String century = invoiceDate.substring(6, 8);
String year = invoiceDate.substring(8, 10);
invoiceDate = century + year + month + day;
}
if(seqnum.length() < 3){
seqnum = ("000" + seqnum).substring(seqnum.length());
}
}
//Formatting Description
else if(columnIndex == 6 ){
description = retdiscolumn.getValue();
if (description == null) {
description = String.format("%50s","");
}
else if (description.length() < 50) {
description = description + String.format("%50s", "").substring(description.length());
} else if (description.length() >= 50) {
description = description.substring(0,50);
}
}
//Formatting Company Code
else if(columnIndex == 7 ){
companycode = retdiscolumn.getValue();
if (companycode.length() < 4) {
companycode = ("0000" + companycode).substring(companycode.length());
} else if (companycode.length() >= 4) {
companycode = companycode.substring(companycode.length() - 4);
}
}
//Formatting WBS
else if(columnIndex == 8){
wbs = retdiscolumn.getValue();
if (wbs == null) {
wbs = String.format("%21s", "");
} else if (wbs.length() >= 12) {
wbs = wbs.substring(0, wbs.length() - 6) + "063100";
wbs = wbs + String.format("%21s", "").substring(wbs.length());
} else {
wbs = wbs + "063100" + String.format("%21s","").substring(wbs.length() + 6);
}
}
//Writing Cost Code -- did not need since static value
// else if(columnIndex == 9){
//
// }
//
//Formatting Profit Center
else if(columnIndex == 10){
profitcenter = retdiscolumn.getValue();
if(profitcenter == null){
profitcenter = String.format("%4s","");
}else if (profitcenter.length() >= 4){
profitcenter = profitcenter.substring(profitcenter.length()-4);
}else if (profitcenter.length() < 4){
profitcenter = ("0000" + profitcenter).substring(profitcenter.length());
}
}
}
//payinvDS.add(row);
//testing fw
retdisfw.write(vendorNumber);
retdisfw.write(invoice);
retdisfw.write(invoiceType);
retdisfw.write(invoiceDate);
retdisfw.write(seqnum);
retdisfw.write(description);
retdisfw.write(amount);
retdisfw.write(companycode);
retdisfw.write(wbs);
retdisfw.write(costcode);
retdisfw.write(profitcenter);
retdisfw.write(costcenter);
retdisfw.write(internalorder);
retdisfw.write(System.getProperty("line.separator"));
}
//testing fw
retdisfw.flush();
retdisfw.close();
int totaldis = 0;
BigDecimal DISTotal = new BigDecimal ("0.00");
// if(rettotaldis > 0 ){
stripDuplicatesFromFile(retdispath);
totaldis = count(retdispath);
DISTotal = sum(retdispath, type, false);
log.info("Retainage file completed");
// }else{
// File errorfile = new File(retdispath.substring(0, retdispath.length() - 9) + ".ERROR");
// retdisfile.renameTo(errorfile);
// retdisfile.delete();
// log.info("Retainage file errored out!");
// }
//Setting up file writer for RETAINAGE INV file
String retinvpath = "\\\\dtcnit" + File.separator + "FTP" + File.separator + "users" + File.separator + "tarftp" + File.separator + "Prod" + File.separator + "OUT"
+ File.separator + "FSSP.AP.TCPM.FIINVOICE.INV.UNSORTED";
File retinvfile = new File(retinvpath);
if(retinvfile.exists()){
retinvfile.delete();
log.info("Retainage File found and deleted!");
}
try {
retinvfile.createNewFile();
log.info("New Retainage file created!");
} catch (IOException e2) {
log.info("Cannot create new Retainage file!");
e2.printStackTrace();
}
FileWriter retinvfw = null;
try {
retinvfw = new FileWriter(retinvfile);
log.info("File Writer for Retainage initialized");
} catch (IOException e1) {
log.info("ERROR! File Writer for Retainage failed to initialized");
e1.printStackTrace();
}
//RETAINAGE INV FILE QUERY
QueryMultiBoResult RET_INV = tririgaws.runNamedQueryMultiBo(null, "triPayment", "triContractInvoice", "tgt - Classloader - Integrations OUTBOUND - SAP Retainage - INV", datefilter, 1, 999999);
// int rettotalinv = RET_INV.getTotalResults();
// log.info("Total INV Records: "+ rettotalinv);
//List<String[]> payinvDS = new ArrayList<String[]>();
for (QueryMultiBoResponseHelper retinvhelper : RET_INV.getQueryMultiBoResponseHelpers()) {
int columnCount = retinvhelper.getQueryMultiBoResponseColumns().length;
String[] row = new String[columnCount];
boolean sign = false;
String loc = "";
String vendorNumber = "";
String invoice = "";
String invoiceType = "";
String invoiceDate = "";
String amount = "";
String currency = "";
String source = "TCPM ";
String retainageFL = "Y";
String lastExtractDate = "";
for (QueryMultiBoResponseColumn retinvcolumn : retinvhelper.getQueryMultiBoResponseColumns()) {
int columnIndex = retinvcolumn.getIndex();
row[columnIndex] = retinvcolumn.getValue();
//log.info("Column: [" + retinvcolumn.getIndex() + "] " + retinvcolumn.getLabel() + " Value: " + retinvcolumn.getValue());
//Formatting Vendor Number
if(columnIndex == 0){
vendorNumber = retinvcolumn.getValue();
if (vendorNumber == null) {
vendorNumber = "000000000";
} else if (vendorNumber.length() < 9){
vendorNumber = ("000000000" + vendorNumber).substring(vendorNumber.length());
} else if (vendorNumber.length() >= 9){
vendorNumber = vendorNumber.substring(vendorNumber.length() - 9);
}
}
//Formatting Location
else if(columnIndex == 1 ){
loc = retinvcolumn.getValue();
if (loc.length() < 4) {
loc = ("0000" + loc).substring(loc.length());
} else if (loc.length() >= 4) {
loc = loc.substring(loc.length() - 4);
}
}
//Formatting Invoice
else if(columnIndex == 2 ){
invoice = retinvcolumn.getValue();
if (invoice == null) {
invoice = "";
} else if (invoice.length() <= 18) {
invoice = invoice + "-R-" + loc;
} else if (invoice.length() > 18) {
invoice = invoice.substring(0, 18) + "-R-" + loc;
}
if(invoice.length() < 25){
invoice = invoice + String.format("%25s", "").substring(invoice.length());
}
else{
invoice = invoice.substring(0,25);
}
}
//formatting for Amount
else if(columnIndex == 3){
amount = retinvcolumn.getValue();
if(amount.contains("-")){
sign = true;
}
amount = amount.replaceAll("[^\\d.]", "");
DecimalFormat df = null;
if(sign){
df = new DecimalFormat("-000000000000.00");
}else{
df = new DecimalFormat("+000000000000.00");
}
amount = df.format(Double.parseDouble(amount));
}
//Formatting Invoice Type
else if(columnIndex == 4 ){
invoiceType = retinvcolumn.getValue();
if(invoiceType == null){
invoiceType = String.format("%1s", "");
}
if(sign){
invoiceType = "C";
}
else{
invoiceType = " ";
}
}
//Formatting Invoice Date
else if(columnIndex == 5 ){
invoiceDate = retinvcolumn.getValue();
if (invoiceDate == null) {
invoiceDate = "";
} else {
String month = invoiceDate.substring(0, 2);
String day = invoiceDate.substring(3, 5);
String century = invoiceDate.substring(6, 8);
String year = invoiceDate.substring(8, 10);
invoiceDate = century + year + month + day;
}
}
//Formatting Currency
else if(columnIndex == 6 ){
currency = retinvcolumn.getValue();
if (currency == null) {
currency = " ";
}
else if (currency.toUpperCase().contains("US")) {
currency = "USD";
} else if (currency.toUpperCase().contains("CAN")) {
currency = "CAD";
}
}
//Formatting last extract date
else if(columnIndex == 7 ){
DateFormat dateFormat = new SimpleDateFormat("yyyyMMdd");
Date reportdate = new Date();
lastExtractDate = dateFormat.format(reportdate);
}
}
//payinvDS.add(row);
retinvfw.write(vendorNumber);
retinvfw.write(invoice);
retinvfw.write(invoiceType);
retinvfw.write(invoiceDate);
retinvfw.write(amount);
retinvfw.write(currency);
retinvfw.write(source);
retinvfw.write(retainageFL);
retinvfw.write(lastExtractDate);
//testing fw
retinvfw.write(System.getProperty("line.separator"));
}
//testing fw
retinvfw.flush();
retinvfw.close();
int totalinv = 0;
BigDecimal INVTotal = new BigDecimal("0.00");
// File file = new File (retdispath.substring(0, retdispath.length() - 9) + ".ERROR");
// if(rettotalinv > 0 && !file.exists()){
stripDuplicatesFromFile(retinvpath);
totalinv = count(retinvpath);
INVTotal= sum(retinvpath, type, true);
// }else{
// File errorfile = new File(retinvpath.substring(0, retinvpath.length() - 9) + ".ERROR");
// retinvfile.renameTo(errorfile);
// retinvfile.delete();
// log.info("Retainage INV error!");
// }
//Creating Retainage CTL file
DateFormat dateFormat = new SimpleDateFormat("MM/dd/yyyy HH:mm:ss");
Date reportdate = new Date();
String Sdate = dateFormat.format(reportdate).substring(0, 10);
String time = dateFormat.format(reportdate).substring(11, 16);
String discount = ("0000000" + Integer.toString(totaldis)).substring(Integer.toString(totaldis).length());
String invcount = ("0000000" + Integer.toString(totalinv)).substring(Integer.toString(totalinv).length());
String INVtotal1 = String.format("%.2f", INVTotal);
String invtotal = ("000000000000000" + INVtotal1).substring(INVtotal1.length());
String DIStotal1 = String.format("%.2f", DISTotal);
String distotal = ("000000000000000" + DIStotal1).substring(DIStotal1.length());
File ctlfile = null;
// File reterror = new File(retdispath.substring(0, retdispath.length() - 9) + ".ERROR");
// File reterror1 = new File(retinvpath.substring(0, retinvpath.length() - 9) + ".ERROR");
// if( invtotal.equals(distotal) && totalinv > 0 && totaldis > 0 && !reterror.exists() && !reterror1.exists())
if(invtotal.equals(distotal)) //check if totals match
{
retdispath = sort(retdispath);
retinvpath = sort(retinvpath);
ctlfile = new File("\\\\dtcnit" + File.separator + "FTP" + File.separator + "users" + File.separator + "tarftp" + File.separator + "Prod" + File.separator + "OUT" + File.separator + "FSSP.AP.TCPM.FIINVOICE.CTL");
}
else{
ctlfile = new File("\\\\dtcnit" + File.separator + "FTP" + File.separator + "users" + File.separator + "tarftp" + File.separator + "Prod" + File.separator + "OUT" + File.separator + "FSSP.AP.TCPM.FIINVOICE.CTL.ERROR");
File errorfile = new File(retinvpath + ".ERROR");
boolean success = retinvfile.renameTo(errorfile);
log.info("inv error file renamed: " + retinvfile);
File errorfile1 = new File(retdispath + ".ERROR");
boolean success1 = retdisfile.renameTo(errorfile1);
log.info("inv error file renamed: " + retdisfile);
if(success){
retinvfile.delete();
}
if(success1){
retdisfile.delete();
}
}
FileWriter ctlfw = new FileWriter(ctlfile);
ctlfw.write("TCPM - Date: " + Sdate + System.getProperty("line.separator"));
ctlfw.write("Time: " + time + System.getProperty("line.separator"));
ctlfw.write("Data type Rec Count Amount"+ System.getProperty("line.separator"));
ctlfw.write(" FI Invoice Lines " + invcount + " " + invtotal + System.getProperty("line.separator"));
ctlfw.write(" FI Distrib Lines " + discount + " " + distotal);
ctlfw.flush();
ctlfw.close();
if(invtotal.equals(distotal)) //check if totals match
{
//send to sftp - CFI ftp
// String SFTPHOST = "ftp.gocfi.com";
// int SFTPPORT = 22;
// String SFTPUSER = "tarftp";
// String SFTPPASS = "Tar_00274";
// String SFTPWORKINGDIR = "/PROD/";
// Target FTP
String SFTPHOST = "emft.target.com";
int SFTPPORT = 22;
String SFTPUSER = "svxtririga04";
String SFTPPASS = "tririga@123";
String SFTPWORKINGDIR = "/fssetl41";
Session session = null;
Channel channel = null;
ChannelSftp channelSftp = null;
try{
JSch jsch = new JSch();
session = jsch.getSession(SFTPUSER,SFTPHOST,SFTPPORT);
session.setPassword(SFTPPASS);
java.util.Properties config = new java.util.Properties();
config.put("StrictHostKeyChecking", "no");
session.setConfig(config);
session.connect();
channel = session.openChannel("sftp");
channel.connect();
channelSftp = (ChannelSftp)channel;
channelSftp.cd(SFTPWORKINGDIR);
File ctl = new File(ctlfile.getPath());
channelSftp.put(new FileInputStream(ctl), ctl.getName());
File hdr = new File(retinvpath);
channelSftp.put(new FileInputStream(hdr), hdr.getName());
File dtl = new File(retdispath);
channelSftp.put(new FileInputStream(dtl), dtl.getName());
}catch(Exception ex){
ex.printStackTrace();
}
}
log.info("End of Retainage");
}//end of Retainage
result.setExecutionWasSuccessful(true);
} catch (ProjectDoesNotExistException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (ModuleDoesNotExistException e) {