package bill;
import i18n.I18NResourceBundle;
import java.io.BufferedWriter;
import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.FileWriter;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.HashMap;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Properties;
import javax.xml.xpath.XPath;
import javax.xml.xpath.XPathConstants;
import javax.xml.xpath.XPathExpressionException;
import javax.xml.xpath.XPathFactory;
import mail.MailHandler;
import order.LMOrder;
import org.apache.commons.net.ftp.FTPClient;
import org.apache.commons.net.ftp.FTPReply;
import org.w3c.dom.Document;
import org.w3c.dom.Node;
import org.w3c.dom.NodeList;
import trades.LMTrade;
import utils.ConfigHandler;
import utils.DBHandler;
import utils.GenericObject;
import utils.LessMoreUtils;
import vendor.Vendor;
import crm.Customer;
import edi.edireader.converter.EDItoXML;
import filter.GenericFileFilter;
import freightorder.FreightOrderUF;
import freightorder.FreightService;
public class ImportEDIInvoicDHL {
Properties params;
Properties paramsConst;
Connection dbConn;
PreparedStatement stmt;
Customer c = null;
FTPClient ftp = null;
BufferedWriter out;
String fileName = "";
String filePathEDI = "";
String filePathXML = "";
String filePathLog = "";
String mailAddressError = "";
String mailAddressErrorSevere = "";
GenericFileFilter filter = null;
public ImportEDIInvoicDHL() {
params = ConfigHandler.readConfigFile();
dbConn = DBHandler.getDbConnection(params);
ftp = new FTPClient();
InputStream in = I18NResourceBundle.class
.getResourceAsStream("constants.properties");
paramsConst = new Properties();
filter = new GenericFileFilter(new String[] {
"msg", "MSG" });
filePathEDI = params.getProperty("DHL_FTP_LOCALDIR_EDI");
filePathXML = params.getProperty("DHL_FTP_LOCALDIR_XML");
filePathLog = params.getProperty("DHL_FTP_LOCALDIR_LOG");
// filePathLog = "c:/bios/log/";
mailAddressError = params.getProperty("DHL_MAIL_ERROR");
mailAddressErrorSevere =
params.getProperty("DHL_MAIL_ERROR_SEVERE");
fileName = filePathLog + LessMoreUtils.getCurrentDateAndTime().replaceAll(":",
"_")
+ "_ImportEDI.txt";
try {
paramsConst.load(in);
out = new BufferedWriter(new FileWriter(fileName, true));
out.write("Startar EdiImport");
out.newLine();
} catch (IOException e) {
e.printStackTrace();
} finally {
try {
in.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
public static void main(String args[]) {
ImportEDIInvoicDHL vip = new ImportEDIInvoicDHL();
if(args.length > 0)
vip.manuallyProcessSingleFile(args[0]);
else
vip.run();
}
private void run() {
try {
boolean loginOK = false;
int reply = 0;
String message = "";
// filePathEDI = "c:/bios/edi/";
// filePathXML = "c:/bios/xml/";
// filePathLog = "c:/bios/log/";
String host = params.getProperty("DHL_FTP_HOST");
String username = params.getProperty("DHL_FTP_USERNAME");
String password = params.getProperty("DHL_FTP_PASSWORD");
ftp.connect(host);
reply = ftp.getReplyCode();
if (!FTPReply.isPositiveCompletion(reply)) {
ftp.disconnect();
message += "FTP server refused connection.\n";
} else {
message += "Connected to " + host + ".\n";
message += ftp.getReplyString() + "\n";
// Login to FTP-account
loginOK = ftp.login(username, password);
if (loginOK) {
// set mode to passive
ftp.enterLocalPassiveMode();
File dir = new File(filePathEDI);
List<String> localFiles = new ArrayList(Arrays.asList(dir
.list(filter)));
// List<String> ftpFiles = new ArrayList(Arrays.asList(dir.list(filter)));
List<String> ftpFiles = new ArrayList(Arrays.asList(ftp
.listNames()));
ftpFiles.removeAll(localFiles); // Ta bort alla filer från
// ftpFiles som finns lokalt
// download the files to Lessmore server
for (int i = 0; i < ftpFiles.size(); i++) {
if (ftpFiles.get(i).indexOf(".msg") > -1
|| ftpFiles.get(i).indexOf(".MSG") > -1) {
File file1 = new File(filePathEDI, ftpFiles.get(i));
FileOutputStream fos = new FileOutputStream(file1);
ftp.retrieveFile(ftpFiles.get(i), fos);
fos.close();
}
}
// process the files on Lessmore server
for (int i = 0; i < ftpFiles.size(); i++) {
if (ftpFiles.get(i).indexOf(".msg") > -1
|| ftpFiles.get(i).indexOf(".MSG") > -1) {
File file1 = new File(filePathEDI, ftpFiles.get(i));
// File file1 = new File("c:\\tx_78998087.msg");
Document doc = convertEdiFile(file1);
if(doc != null) {
out.write("Konvertering av Edi fil gick bra");
out.newLine();
if (readXmlFile(doc)) {
out.write("Läsning av xml dokument gick bra");
out.newLine();
} else {
out.write("Läsning av xml dokument misslyckades");
out.newLine();
// ************** alert someone
sendAlert(
"Det blev fel vid bearbetning av importfilen "
+ file1.getName(),
mailAddressError);
}
} else {
out.write("Konvertering av Edi fil misslyckades");
out.newLine();
}
}
} // end of process files on Lessmore server
} // end login OK
} // end remote connection refused
} catch (Exception e) {
e.printStackTrace();
try {
out.write("ImportError, run-method, Error: " + e);
out.newLine();
sendAlert("Fel i import Error: " + e, mailAddressError);
} catch (IOException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}
} finally {
try {
out.write("Avslutar EDIImport");
out.newLine();
out.close();
MailHandler.sendMail(params, "Resultat av ftpimport",
mailAddressError, mailAddressError, "FTP import",
fileName);
} catch (Exception e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}
try {
if (!dbConn.isClosed())
DBHandler.closeDbConnection(dbConn);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
} // end of run-method
private Document convertEdiFile(File importFile) {
Document doc = null;
try {
out.write(importFile.getName());
out.newLine();
EDItoXML theObject = new EDItoXML(importFile.getAbsolutePath());
String xmlString = theObject.run();
doc = xml.DocumentHelper
.getXMLDocumentFromString(xmlString);
xml.DocumentHelper.streamToFile(doc, filePathXML
+ LessMoreUtils.firstSubString(importFile.getName(), ".")
+ ".xml");
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return doc;
}
private boolean readXmlFile(Document doc) {
boolean ok = true;
boolean okInsertOrder = false;
boolean sendAlert = false;
String article = "DHL Frakt";
double unitPrice = 0.0;
double supplementCharge = 0;
double supplementChargeAmount = 0;
String senderAddress = "";
String receiverAddress = "";
String infoText = "";
int orderType = 10;
try {
XPathFactory xPathFactory = XPathFactory.newInstance();
XPath xPath = xPathFactory.newXPath();
NodeList lineNodes = (NodeList) xPath.evaluate(
"/ediroot/interchange/group/transaction/loop[@Id='SG22']",
doc, XPathConstants.NODESET);
int nodeCount = lineNodes.getLength();
Node lineNode = null;
if (nodeCount > 0) {
for (int x = 0; x < lineNodes.getLength(); x++) {
String senderReference = "";
String shipmentNo = "";
unitPrice = 0;
lineNode = (Node) lineNodes.item(x);
String lineNo = (String) xPath.evaluate(
"segment[@Id='LIN']/element", lineNode,
XPathConstants.STRING);
out.write("Bearbetar rad:"+lineNo);
out.newLine();
int pacNo = 0;
String pacNoStr = (String) xPath.evaluate(
"loop[@Id='SG27']/segment[@Id='PAC']/element", lineNode,
XPathConstants.STRING);
if(!"".equals(pacNoStr))
pacNo = Integer.valueOf(pacNoStr).intValue();
// Shipmentref
NodeList shipmentRefNodes = (NodeList) xPath.evaluate(
"loop[@Id='SG26']", lineNode,
XPathConstants.NODESET);
for (int i = 0; i < shipmentRefNodes.getLength(); i++) {
Node node = (Node) shipmentRefNodes.item(i);
String ref = (String) xPath.evaluate(
"segment/element/subelement[@Sequence='2']",
node, XPathConstants.STRING);
if ("AAS".equals((String) xPath.evaluate(
"segment/element/subelement[@Sequence='1']",
node, XPathConstants.STRING)))
shipmentNo = ref;
else
senderReference = ref;
}
// END Shipmentref
// Chargeable quantity
NodeList chargeableQuantNodes = (NodeList) xPath.evaluate(
"segment[@Id='QTY']", lineNode,
XPathConstants.NODESET);
for (int i = 0; i < chargeableQuantNodes.getLength(); i++) {
Node node = (Node) chargeableQuantNodes.item(i);
String quantity = (String) xPath.evaluate(
"element/subelement[@Sequence='2']", node,
XPathConstants.STRING);
}
// END Chargeable quantity
// Measure
String KGM = "";
String MTQ = "";
String MTR = "";
NodeList lineMea = (NodeList) xPath.evaluate(
"segment[@Id='MEA']", lineNode,
XPathConstants.NODESET);
for (int i = 0; i < lineMea.getLength(); i++) {
Node node = (Node) lineMea.item(i);
String meaSeq1 = (String) xPath.evaluate(
"element/subelement[@Sequence='1']", node,
XPathConstants.STRING);
String meaSeq2 = (String) xPath.evaluate(
"element/subelement[@Sequence='2']", node,
XPathConstants.STRING);
if("KGM".equals(meaSeq1))
KGM = meaSeq2;
else if("MTQ".equals(meaSeq1))
MTQ = meaSeq2;
else if("MTR".equals(meaSeq1))
MTR = meaSeq2;
}
// END Measure
String moaString = "";
if(!"".equals(KGM))
moaString += ", Vikt "+KGM+ " kg";
if(!"".equals(MTQ))
moaString += ", Volym "+MTQ+ " m3";
if(!"".equals(MTR))
moaString += ", Längdmeter "+MTR+ " m";
// Dates
String date1 = "";
String date2 = "";
NodeList lineDates = (NodeList) xPath.evaluate(
"segment[@Id='DTM']", lineNode,
XPathConstants.NODESET);
for (int i = 0; i < lineDates.getLength(); i++) {
Node node = (Node) lineDates.item(i);
String dateIdentifier = (String) xPath.evaluate(
"element/subelement[@Sequence='1']", node,
XPathConstants.STRING);
String tempDate = (String) xPath.evaluate(
"element/subelement[@Sequence='2']", node,
XPathConstants.STRING);
if("ZGR".equals(dateIdentifier))
date2 = tempDate;
else
date1 = tempDate;
}
// END Dates
// Cost
unitPrice = 0.0;
NodeList costNodes = (NodeList) xPath.evaluate(
"loop[@Id='SG23']", lineNode,
XPathConstants.NODESET);
for (int i = 0; i < costNodes.getLength(); i++) {
Node node = (Node) costNodes.item(i);
String identifier = (String) xPath
.evaluate(
"segment/element/subelement[@Sequence='1']",
node, XPathConstants.STRING);
String cost = (String) xPath
.evaluate(
"segment/element/subelement[@Sequence='2']",
node, XPathConstants.STRING);
if ("203".equals(identifier))
identifier = identifier;
unitPrice = unitPrice + LessMoreUtils.parseDouble(cost);
}
// END Cost
// Avs/Mott
NodeList PartyQualifierNodes = (NodeList) xPath.evaluate(
"loop[@Id='SG31']", lineNode,
XPathConstants.NODESET);
for (int i = 0; i < PartyQualifierNodes.getLength(); i++) {
Node node = (Node) PartyQualifierNodes.item(i);
String ref = (String) xPath
.evaluate(
"segment/element[@Id='NAD03']/subelement[@Sequence='1']",
node, XPathConstants.STRING);
String adr = (String) xPath
.evaluate(
"segment/element[@Id='NAD03']/subelement[@Sequence='2']",
node, XPathConstants.STRING);
String zipCity = (String) xPath
.evaluate(
"segment/element[@Id='NAD03']/subelement[@Sequence='3']",
node, XPathConstants.STRING);
if ("CZ".equals((String) xPath.evaluate(
"segment/element[@Id='NAD01']", node,
XPathConstants.STRING)))
senderAddress = ref + " " + adr + " " + zipCity;
else if ("CN".equals((String) xPath.evaluate(
"segment/element[@Id='NAD01']", node,
XPathConstants.STRING)))
receiverAddress = ref + " " + adr + " " + zipCity;
}
// END Avs/Mott
if(!"".equals(LessMoreUtils.parseNull(senderReference))) {
FreightOrderUF foUF = new FreightOrderUF(dbConn,
senderReference);
if (!"".equals(foUF.getShipmentOrderNo())) {
// Create order & Trade
okInsertOrder = false;
Vendor freightVendor = new Vendor(dbConn, foUF.getShipmentVendorID());
FreightService fs = new FreightService(
dbConn,
LessMoreUtils.isValidInt(foUF.getServiceID()) ? foUF
.getServiceID()
: -1);
if (fs.getServiceID() > -1) {
String unitPriceStr = "";
if(pacNo == 0) {
unitPriceStr = ", Tillägg: " + unitPrice;
} else {
supplementCharge = fs.getSupplementCharge();
supplementChargeAmount = unitPrice
* supplementCharge / 100;
unitPrice += supplementChargeAmount;
unitPriceStr = ", Fraktkostnad: " + unitPrice;
}
LMOrder lmOrder = new LMOrder();
lmOrder.setDateCreated(date2);
lmOrder.setCustID(foUF.getCustID());
lmOrder.setOrderType(orderType);
lmOrder.setRefOrderID(Integer.valueOf(
foUF.getShipmentOrderNo()).intValue());
lmOrder.setArticle(article);
lmOrder.setSku("DHL");
lmOrder.setQty(1);
lmOrder.setVatCode(1);
lmOrder.setUnit("st");
lmOrder.setUnitPrice(unitPrice);
lmOrder.setInfoText("Ordernummer: " +foUF.getShipmentOrderNo()+", " +
"Sändningsnummer: " +LessMoreUtils.parseNull(shipmentNo)+LessMoreUtils.parseNull(", Mottagare: "+receiverAddress) +
LessMoreUtils.parseNull(moaString)+ unitPriceStr);
lmOrder.setTotalPrice(unitPrice);
lmOrder.setBilled(-1);
okInsertOrder = lmOrder.insertToDB(dbConn);
// save as trade
if (okInsertOrder) {
out.write("Orderrad postad i DB");
out.newLine();
LMTrade lmTrade = new LMTrade();
lmTrade.setCustID(foUF.getCustID());
lmTrade.setVendorID(freightVendor.getRefCompID());
lmTrade.setMemberGroupID(-1);
lmTrade.setRegDate(lmOrder.getDateCreated());
lmTrade.setValue(lmOrder.getTotalPrice());
lmTrade.setCurrency("SEK");
lmTrade.setMargin(0.0999);
lmTrade.setTradeType("INVOICE");
lmTrade.setComments(article + " (LMorderID: "
+ lmOrder.getOrderID() + ")");
boolean ok2 = lmTrade.saveToDB(dbConn);
if(ok2) {
out.write("Trade postad i DB");
out.newLine();
} else {
out.write("Trade ej postad i DB");
out.newLine();
}
} else {
out.write("Orderrad ej postad i DB");
out.newLine();
}
} else {
out.write("FraktServiceobjekt ej funnet " + foUF.getServiceID());
out.newLine();
}
} else {
out.write("Fraktorderobjekt ej funnet " + LessMoreUtils.parseNull(senderReference) + " för sändningsnummer "+shipmentNo);
out.newLine();
}
} //if(!"".equals(LessMoreUtils.parseNull(senderReference))) {
out.flush();
} // END for lineNodes
} // END nodeCount
ok = true;
} catch (XPathExpressionException e) {
// TODO Auto-generated catch block
ok = false;
try {
out.write("ImportError, readImportedFile-method, Error: " + e);
out.newLine();
} catch (IOException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}
e.printStackTrace();
} catch (FileNotFoundException e) {
// TODO Auto-generated catch block
ok = false;
try {
out.write("ImportError, readImportedFile-method, Error: " + e);
out.newLine();
} catch (IOException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}
e.printStackTrace();
} catch (IOException e) {
// TODO Auto-generated catch block
ok = false;
try {
out.write("ImportError, readImportedFile-method, Error: " + e);
out.newLine();
} catch (IOException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}
e.printStackTrace();
} catch (Exception e) {
ok = false;
try {
out.write("ImportError, readImportedFile-method, Error: " + e);
out.newLine();
} catch (IOException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}
e.printStackTrace();
}
if (sendAlert)
sendAlert("Granskning av import behövs "
+ LessMoreUtils.getCurrentDate(), mailAddressError);
return ok;
}
private void sendAlert(String messageString, String receiver) {
MailHandler.sendMail(params, messageString, mailAddressError, receiver,
"Import-alert!");
}
public boolean manuallyProcessSingleFile(String fileName) {
boolean result = false;
try {
out.write(fileName);
out.newLine();
Document doc = xml.DocumentHelper.parseXmlFile(filePathXML+fileName);
if (readXmlFile(doc)) {
out.write("Läsning av xml fil gick bra");
out.newLine();
result = true;
} else {
// ************** alert someone
sendAlert(
"Det blev fel vid bearbetning av importfilen "
+ fileName,
mailAddressError);
}
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
finally {
try {
out.write("Avslutar EDIImport");
out.newLine();
out.close();
MailHandler.sendMail(params, "Resultat av ftpimport",
mailAddressError, mailAddressError, "FTP import",
this.fileName);
} catch (Exception e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}
try {
if (!dbConn.isClosed())
DBHandler.closeDbConnection(dbConn);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
return result;
}
}