I had written one B2B callout utility which will read the incoming PO-850 document and parse the EDI file and based on the data in EDI document it will prepare a Excel document which will send information regarding the Purchase order. It will handle PO documents send from SDQ and Non-SDQ trading partners. The utility will create a excel sheet and send across the file to support team who can validate whether the values that has been inserted in the Order management system are the same from the 850 edi file send across by trading partner. It will help the customer validate /test the system for 850 inbound flows.
I will be sharing the code in the blog so that you can customize it according to your requirement.
There are some assumptions which I make regarding the edi file that will be send across by the trading partner.
Assumptions for utility to work properly:-
1. The Utility expects the EDI document to use “*” as element delimiter
2. Each line is separated by line separator based on the environment.
3. Each line contains specific Segment information. Refer below for more info on segment name that are expected at start of each line in the document.
Segments that can be present at the starting of each line
ISA,GS*,ST*,BEG,CUR,REF,PER,TAX,FOB,CTP,PAM,CSH,TC2,SAC,ITD,DIS,INC,DTM,LDT,LIN,SI*,PID,MEA,PWK,PKG,TD1,TD5,TD3,TD4,MAN,PCT,CTB,TXI,AMT,N9*,N1*,LM*,SPI,ADV,PO1,CTT,SE*,GE*,IEA,//SDQ,//PO4,
// segments inside PO1
LIN,SI*,CUR,CN1,PO3,CTP,PAM,MEA,PID,PWK,PO4,REF,PER,//SAC_1,IT8,CSH,ITD,DIS,INC,TAX,FOB,SDQ,IT3,DTM,TC2,TD1,TD5,TD3,TD4,PCT,MAN,MSG,SPI,TXI,CTB,QTY,SCH,PKG,LS*,SLN,LM*,
MTX, ADV
Required Jars
The utility uses 3 external java libraries
1. javax.mail_1.4.jar (for sending notification)
2. poi-3.7-20101029.jar (for creating excel file)
3. b2b.jar (for callout code)
Pre-req tasks
To test the inbound utility we need to add 2 jars to the server library -javax.mail_1.4.jar(for sending emails in java) & poi-3.7-20101029.jar(for creating excel file).
Main steps are
1. Copy the jar files javax.mail_1.4.jar & poi-3.7-20101029.jar To $ORACLE_HOME/Oracle_SOA/soa/modules/oracle.soa.ext_11.1.1
2. Set Ant environment variables & go to $ORACLE_HOME/Oracle_SOA/soa/modules/oracle.soa.ext_11.1.1 then Run "ant".
3. Restart Oracle Weblogic SOA managed Server.
The custom jars/classes are loaded at server startup so that the jars are available during runtime.
Output
Hope this helps J
Java Code is available below
package com.geo.b2b.callout;
import com.geo.b2b.email.EmailUtility;
import java.io.BufferedReader;
import java.io.DataInputStream;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.ResourceBundle;
import java.util.StringTokenizer;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.mail.MessagingException;
import javax.mail.internet.AddressException;
import oracle.tip.b2b.callout.Callout;
import oracle.tip.b2b.callout.CalloutContext;
import oracle.tip.b2b.callout.CalloutMessage;
import oracle.tip.b2b.callout.exception.CalloutDomainException;
import oracle.tip.b2b.callout.exception.CalloutSystemException;
import oracle.tip.b2b.system.DiagnosticService;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
/**
* Converts EDI input to Excel readable format and sends email to business user
* This will be used as a transport callout class when edi document send by
* trading partner is processed by Oracle B2B.
*
*@author George Thomas
*@version 1.0
*
*/
public class POTransportCallout implements Callout {
// private final static Logger logger = Logger.getLogger(POTransportCallout.class .getName());
static String pathsep = System.getProperty("file.separator");
static String elementSep = "\\*";
//private static final String CRLF = "\r\n";
private static String CRLF = System.getProperty("line.separator");
private static final String SENDER_ID = "SENDER_ID";
private static final String RECEIVER_ID = "RECEIVER_ID";
private static final String PO_NUMBER = "PO_NUMBER";
private static final String PO_DATE = "PO_DATE";
private static final String DOC_VERSION = "DOC_VERSION";
private static final String FUNC_CODE = "FUNC_CODE";
private static final String SEGMENT_NAME = "SEGMENT NAME";
private static final String ASSIGNED_ID = "ASSIGNED ID";
private static final String QUANTITY = "QUANTITY";
private static final String UOM = "UOM";
private static final String UNIT_PRICE = "UNIT PRICE";
private static final String PRODUCT_ID_QUALIFIER = "PRODUCT ID QUALIFIER";
private static final String PRODUCT_ID = "PRODUCT ID";
private static final String PO_SDQ = "PO-SDQ";
private static final String ID_CODE_QUALIFIER = "ID CODE QUALIFIER";
private static final String ID_CODE = "ID CODE";
private static final String BASIS_UPC = "BASIS UPC";
private static final String SEGMENT_ISA = "ISA*";
private static final String SEGMENT_BEG = "BEG*";
private static final String SEGMENT_GS = "GS*";
private static final String SEGMENT_PO1 = "PO1*";
private static final String SEGMENT_SDQ = "SDQ*";
//private static String outputFolder = "C:\\temp\\B2B\\";
private static String outputFolder = "/tmp/B2B/";
/**
* This methos implements the Callout execute method which will perform the required
* custom actions that need to be performed before the b2B processing.
* @param calloutContext
* @param input
* @param output
* @throws CalloutDomainException
* @throws CalloutSystemException
*/
public void execute(CalloutContext calloutContext, List input,
List output) throws CalloutDomainException,
CalloutSystemException {
b2blog(" Callout execute() called - Start");
try {
CalloutMessage cmIn = (CalloutMessage)input.get(0);
String inputPayload = cmIn.getBodyAsString();
String ediPayload = cmIn.getBodyAsString();
b2blog((new StringBuilder()).append("Callout execute() - All Parameter = ").append(cmIn.getParameters()).toString());
b2blog((new StringBuilder()).append("Callout execute() - CalloutMessage body = ").append(inputPayload).toString());
b2blog((new StringBuilder()).append("Callout execute() -calling process850EDI"));
String checkStr = checkFormat(ediPayload);
if (checkStr == null) {
b2blog((new StringBuilder()).append("Callout execute() -passed format test"));
String fileName = process850EDI(ediPayload);
b2blog((new StringBuilder()).append("Callout execute() -calling sendMail "));
EmailUtility.sendMail("New PO-850 EDI document recieved",
"Please find the attached details",
fileName);
b2blog((new StringBuilder()).append("Callout execute() - Mail send successfully"));
} else {
b2blog((new StringBuilder()).append("Callout execute() - Format Error Mail send successfully"));
EmailUtility.sendMail("PO-850 EDI document not formatted properly", "Please check with suport " +
"team on the format of the recieved file. The detailed issue stack trace can be seen below \n" +checkStr);
}
CalloutMessage cmOut = new CalloutMessage(inputPayload);
output.add(cmOut);
b2blog((new StringBuilder()).append("Callout execute() - End Callout Testing"));
} catch (Exception e) {
// b2blog("Exception: "+ e.printStackTrace())
// StackTraceElement[] err = e.getStackTrace();
b2blog((new StringBuilder()).append("Callout execute() - Exception = ").append(e.getMessage()).toString());
try {
EmailUtility.sendMail("B2B Callout failed", "Please ask technical team to look into the issue .The error stack trace can be found below ::\n "+e.getMessage());
} catch (AddressException f) {
//ignore
} catch (MessagingException f) {
// swallow
}
e.printStackTrace();
throw new CalloutDomainException(e);
}
}
/**
* this method checks format of the input edi payload ,in case its not meeting the formatting conditions laid down by the utility
* it will append the line numbers and starting segment value to the return value.
* @param ediPayload
* @return
*/
private static String checkFormat(String ediPayload) {
String ediStr = ediPayload.replaceAll("~", CRLF);
StringBuilder errorRows = new StringBuilder();
StringTokenizer ediToken = new StringTokenizer(ediStr, CRLF);
List segments = new ArrayList();
segments.add("ISA");
segments.add("GS*");
segments.add("ST*");
segments.add("BEG");
segments.add("CUR");
segments.add("REF");
segments.add("PER");
segments.add("TAX");
segments.add("FOB");
segments.add("CTP");
segments.add("PAM");
segments.add("CSH");
segments.add("TC2");
segments.add("SAC");
segments.add("ITD");
segments.add("DIS");
segments.add("INC");
segments.add("DTM");
segments.add("LDT");
segments.add("LIN");
segments.add("SI*");
segments.add("PID");
segments.add("MEA");
segments.add("PWK");
segments.add("PKG");
segments.add("TD1");
segments.add("TD5");
segments.add("TD3");
segments.add("TD4");
segments.add("MAN");
segments.add("PCT");
segments.add("CTB");
segments.add("TXI");
segments.add("AMT");
segments.add("N9*");
segments.add("N1*");
segments.add("LM*");
segments.add("SPI");
segments.add("ADV");
segments.add("PO1");
segments.add("CTT");
segments.add("SE*");
segments.add("GE*");
segments.add("IEA");
//segments.add("SDQ");
//segments.add("PO4");
//adding segments inside PO1
segments.add("LIN");
segments.add("SI*");
segments.add("CUR");
segments.add("CN1");
segments.add("PO3");
segments.add("CTP");
segments.add("PAM");
segments.add("MEA");
segments.add("PID");
segments.add("PWK");
segments.add("PO4");
segments.add("REF");
segments.add("PER");
//segments.add("SAC_1");
segments.add("IT8");
segments.add("CSH");
segments.add("ITD");
segments.add("DIS");
segments.add("INC");
segments.add("TAX");
segments.add("FOB");
segments.add("SDQ");
segments.add("IT3");
segments.add("DTM");
segments.add("TC2");
segments.add("TD1");
segments.add("TD5");
segments.add("TD3");
segments.add("TD4");
segments.add("PCT");
segments.add("MAN");
segments.add("MSG");
segments.add("SPI");
segments.add("TXI");
segments.add("CTB");
segments.add("QTY");
segments.add("SCH");
segments.add("PKG");
segments.add("LS*");
segments.add("SLN");
segments.add("LM*");
segments.add("MTX");
segments.add("ADV");
int errorCount = 0;
int lineNumber = 0;
while (ediToken.hasMoreElements()) {
++lineNumber;
String ediLineValue = ediToken.nextToken();
if (ediLineValue != null && !ediLineValue.equals("") &&
ediLineValue.length() > 2) {
String startSegment = ediLineValue.substring(0, 3);
if (!segments.contains(startSegment)) {
++errorCount;
errorRows.append("Line no::" + lineNumber + "\t");
errorRows.append("Segment name::" + startSegment + "\n");
b2blog((new StringBuilder()).append("Check formatting - Line no = ").append(lineNumber).append("Segment name:: ").append(startSegment).toString());
}
} else {
++errorCount;
errorRows.append("Line no::" + lineNumber + "\t");
errorRows.append("Segment name::" + "NA" + "\n");
b2blog((new StringBuilder()).append("Check formatting - Line no = ").append(lineNumber).append("Segment name:: ").append("NA").toString());
}
}
errorRows.append("\n No. of Lines having issues ::").append(errorCount);
b2blog((new StringBuilder()).append("Check formatting - No. of Lines having issues :: = ").append(errorCount));
if(errorCount>0){
return errorRows.toString();
}else
return null;
}
/**
* This method uses the b2B diagnostic service for logging the messages to the
* server logs.
* @param message
*/
private static void b2blog(Object message) {
DiagnosticService.log(3, -2,
(new StringBuilder()).append("POTransportCallout ::").append(message).toString());
//b2blog(message.toString());
}
/**
* pre processing method used for running as stand alone program to process
* 850 EDI document
* @param inputFileName
* @return ediStr
*/
public String preProcess(String inputFileName) {
FileInputStream fstream;
String ediStr = null;
StringBuffer ediDoc = new StringBuffer();
// Get the object of DataInputStream
try {
fstream = new FileInputStream(inputFileName);
InputStream in = new DataInputStream(fstream);
BufferedReader br = new BufferedReader(new InputStreamReader(in));
String strLine;
//Read File Line By Line
while ((strLine = br.readLine()) != null) {
ediDoc.append(strLine + "\n");
b2blog(strLine);
}
ediStr = ediDoc.toString();
in.close();
} catch (FileNotFoundException fileEx) {
fileEx.printStackTrace();
} catch (IOException ioEx) {
ioEx.printStackTrace();
}
return ediStr;
}
/**
* this method processes the 850 EDI document and poplulates the different
* values required for writing the excel result.
* @param ediPayload
* @return
*/
public String process850EDI(String ediPayload) throws Exception {
b2blog((new StringBuilder()).append("Callout process850EDI() - Start process850EDI= "));
String fileName = null;
boolean sdqFlag = false;
String docVersion = null;
String senderID = null;
String receiverID = null;
String PONumber = null;
String funcCode = null;
String PODate = null;
Map poDetails = new HashMap();
List orderList = new ArrayList();
Map poSDQMap = new HashMap();
String ediStr = null;
ediStr = ediPayload.replaceAll("~", CRLF);
try {
if (ediStr.contains(SEGMENT_SDQ)) {
sdqFlag = true;
}
StringTokenizer ediToken = new StringTokenizer(ediStr, CRLF);
int lineCount = ediToken.countTokens();
b2blog(" PO lineCount ::" + lineCount);
// int fieldCounter = 0;
int poCount = 0;
int sdqPOId = 0;
int sdqCount = 0;
while (ediToken.hasMoreElements()) {
String ediLineValue = ediToken.nextToken();
if (ediLineValue.contains(SEGMENT_ISA)) {
String[] isaToken = ediLineValue.split(elementSep);
senderID = isaToken[6];
receiverID = isaToken[8];
poDetails.put(SENDER_ID, senderID);
poDetails.put(RECEIVER_ID, receiverID);
}
if (ediLineValue.contains(SEGMENT_GS)) {
String[] gsToken = ediLineValue.split(elementSep);
funcCode = gsToken[1];
docVersion = gsToken[8];
poDetails.put(FUNC_CODE, funcCode);
poDetails.put(DOC_VERSION, docVersion);
}
if (ediLineValue.contains(SEGMENT_BEG)) {
String[] begToken = ediLineValue.split(elementSep);
PONumber = begToken[3];
PODate = begToken[5];
poDetails.put(PO_NUMBER, PONumber);
poDetails.put(PO_DATE, PODate);
}
if (ediLineValue.contains(SEGMENT_PO1)) {
poCount++;
sdqPOId = poCount;
sdqCount = 0;
//b2blog(new StringBuilder().append(" PO poCount ::"+poCount));
String[] poToken = ediLineValue.split(elementSep);
Map orderfieldsMap = new HashMap();
int tokenCount = poToken.length;
// b2blog(" PO tokenCount ::"+tokenCount);
for (int poelementCount = 0; poelementCount < tokenCount;
poelementCount++) {
String poElementValue = poToken[poelementCount];
// b2blog(new StringBuilder().append(" PO poelementCount ::"+poelementCount));
//b2blog(new StringBuilder().append(" PO poElementValue ::"+poElementValue));
orderfieldsMap.put(poelementCount, poElementValue);
}
orderList.add(orderfieldsMap);
}
if (sdqFlag) {
/* if(ediLineValue.contains("CTP")){
b2blog(new StringBuilder().append(" PO poCount ::"+poCount);
String[] cttToken = ediLineValue.split(elementSep);
}else*/
if (ediLineValue.contains(SEGMENT_SDQ)) {
sdqCount++;
Map sdqMap = new HashMap();
String[] sdqToken = ediLineValue.split(elementSep);
int sdqTokenCount = sdqToken.length;
//b2blog(new StringBuilder().append(" PO sdqTokenCount ::"+sdqTokenCount));
for (int sdqelementCount = 0;
sdqelementCount < sdqTokenCount;
sdqelementCount++) {
String sdqElementValue = sdqToken[sdqelementCount];
//b2blog(new StringBuilder().append(" PO sdqelementCount ::"+sdqelementCount));
//b2blog(new StringBuilder().append(" PO sdqElementValue ::"+sdqElementValue));
sdqMap.put(sdqelementCount, sdqElementValue);
}
poSDQMap.put(sdqPOId + "-" + sdqCount, sdqMap);
} else {
//b2blog(new StringBuilder().append(" SDQ ediLineValue ::"+ediLineValue));
}
} else {
//b2blog(new StringBuilder().append(" ediLineValue ::"+ediLineValue));
}
}
b2blog((new StringBuilder()).append("Callout process850EDI() - going to call writeResultXLS "));
if (sdqFlag) {
fileName =
writeSDQResult(poDetails, orderList, sdqFlag, poSDQMap);
} else {
fileName = writeResult(poDetails, orderList);
}
} catch (Exception e) {
e.printStackTrace();
b2blog((new StringBuilder()).append("Callout process850EDI() - ERROR").append(e.getMessage()));
throw e;
}
b2blog((new StringBuilder()).append("Callout process850EDI() - end process850EDI"));
return fileName;
}
/**
* main method
* @param args
*/
public static void main(String[] args) throws MessagingException,
AddressException, Exception {
String inputFileName =
"D:\\Geo\\EDIdocs\\850\\Geo850.txt";
POTransportCallout callout = new POTransportCallout();
String ediStr = callout.preProcess(inputFileName);
String checkStr = checkFormat(ediStr);
if (checkStr.isEmpty()) {
String fileName = callout.process850EDI(ediStr);
b2blog((new StringBuilder()).append("checkStr is empty "));
} else {
//EmailUtility emailUtil = new EmailUtility();
EmailUtility.sendMail("PO-850 EDI document not formatted properly",
checkStr);
}
}
/**
* write Excel result for NON-SDQ trading partners
* @param poDetails
* @param resultList
* @return
*/
public static String writeResult(Map poDetails,
List resultList) throws Exception {
b2blog((new StringBuilder()).append("Callout writeResult() - Start writeResult= "));
String outputFile = null;
try {
String senderId = (String)poDetails.get(SENDER_ID);
int xlRowCounter = 0;
// Create a New XL Document
HSSFWorkbook wb = new HSSFWorkbook();
// Make a worksheet in the XL document created
HSSFSheet sheet = wb.createSheet();
xlRowCounter = createHeaders(poDetails, sheet, xlRowCounter);
HSSFRow row1 = sheet.createRow(++xlRowCounter);
HSSFCell cell10 = row1.createCell(0);
cell10.setCellType(HSSFCell.CELL_TYPE_STRING);
// Type some content
cell10.setCellValue(SEGMENT_NAME);
HSSFCellStyle style = wb.createCellStyle();
// style.setFillBackgroundColor(HSSFColor.GREY_80_PERCENT.index);
style.setFillBackgroundColor(HSSFColor.DARK_RED.index);
cell10.setCellStyle(style);
HSSFCell cell11 = row1.createCell(1);
cell11.setCellType(HSSFCell.CELL_TYPE_STRING);
// Type some content
cell11.setCellValue(ASSIGNED_ID);
cell11.setCellStyle(style);
HSSFCell cell12 = row1.createCell(2);
cell12.setCellType(HSSFCell.CELL_TYPE_STRING);
// Type some content
cell12.setCellValue(QUANTITY);
cell12.setCellStyle(style);
HSSFCell cell13 = row1.createCell(3);
cell13.setCellType(HSSFCell.CELL_TYPE_STRING);
// Type some content
cell13.setCellValue(UOM);
cell13.setCellStyle(style);
HSSFCell cell14 = row1.createCell(4);
cell14.setCellType(HSSFCell.CELL_TYPE_STRING);
// Type some content
cell14.setCellValue(UNIT_PRICE);
cell14.setCellStyle(style);
HSSFCell cell15 = row1.createCell(5);
cell15.setCellType(HSSFCell.CELL_TYPE_STRING);
// Type some content
cell15.setCellValue(BASIS_UPC);
cell15.setCellStyle(style);
boolean evenFlag = false;
Iterator it1 = resultList.iterator();
//int i =2;
while (it1.hasNext()) {
HSSFRow row = sheet.createRow(++xlRowCounter);
Map recordMap = (Map)it1.next();
//int j =0;
//while(it2.hasNext()){
for (int j = 0; j < recordMap.size(); j++) {
// Create row at index zero ( Top Row)
if (j % 2 == 0) {
evenFlag = true;
} else {
evenFlag = false;
}
if (j >= 6 && evenFlag) {
HSSFCell cell1Name = row1.createCell(j);
cell1Name.setCellType(HSSFCell.CELL_TYPE_STRING);
// Type some content
cell1Name.setCellValue(PRODUCT_ID_QUALIFIER);
cell1Name.setCellStyle(style);
} else if (j >= 6 && !evenFlag) {
HSSFCell cell1QualName = row1.createCell(j);
cell1QualName.setCellType(HSSFCell.CELL_TYPE_STRING);
// Type some content
cell1QualName.setCellValue(PRODUCT_ID);
cell1QualName.setCellStyle(style);
}
// Create a cell at index zero ( Top Left)
HSSFCell cell = row.createCell(j);
// Lets make the cell a string type
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
String value = (String)recordMap.get(j);
cell.setCellValue(value);
}
// i++;
}
sheet.autoSizeColumn(10);
//String outputFile = null;
b2blog((new StringBuilder()).append("Callout writeResult() - Going to write a file").append(outputFile));
if (senderId != null)
outputFile =
outputFolder + senderId.trim() + "_callout_" + new Date().getTime() +
".xls";
else
outputFile =
outputFolder + "NA" + "_callout_" + new Date().getTime() +
".xls";
FileOutputStream fOut = new FileOutputStream(outputFile);
// Write the XL sheet
wb.write(fOut);
fOut.flush();
// Done Deal..
fOut.close();
b2blog(new StringBuilder().append("File Created ..::").append(outputFile));
} catch (Exception e) {
b2blog(new StringBuilder().append("!!BANG!! xlCreate() : " + e));
throw e;
}
b2blog((new StringBuilder()).append("Callout writeResult() - End writeResult"));
return outputFile;
}
/**
* create Headers for Excel sheet
* @param poDetails
* @param sheet
* @param xlRowCounter
* @return xlRowCounter
*/
private static int createHeaders(Map poDetails, HSSFSheet sheet,
int xlRowCounter) {
b2blog((new StringBuilder()).append("Callout createHeaders() - Start createHeaders= "));
String senderId = (String)poDetails.get(SENDER_ID);
String receiverId = (String)poDetails.get(RECEIVER_ID);
String docVersion = (String)poDetails.get(DOC_VERSION);
String PONumber = (String)poDetails.get(PO_NUMBER);
String funcCode = (String)poDetails.get(FUNC_CODE);
String PODate = (String)poDetails.get(PO_DATE);
;
HSSFRow headerrow1 = sheet.createRow(xlRowCounter);
HSSFCell hcell10 = headerrow1.createCell(0);
hcell10.setCellType(HSSFCell.CELL_TYPE_STRING);
hcell10.setCellValue(SENDER_ID);
HSSFCell hcell11 = headerrow1.createCell(1);
hcell11.setCellType(HSSFCell.CELL_TYPE_STRING);
hcell11.setCellValue(senderId);
HSSFRow headerrow2 = sheet.createRow(++xlRowCounter);
HSSFCell hcell20 = headerrow2.createCell(0);
hcell20.setCellType(HSSFCell.CELL_TYPE_STRING);
hcell20.setCellValue(RECEIVER_ID);
HSSFCell cell21 = headerrow2.createCell(1);
cell21.setCellType(HSSFCell.CELL_TYPE_STRING);
cell21.setCellValue(receiverId);
HSSFRow headerrow3 = sheet.createRow(++xlRowCounter);
HSSFCell hcell30 = headerrow3.createCell(0);
hcell30.setCellType(HSSFCell.CELL_TYPE_STRING);
hcell30.setCellValue(FUNC_CODE);
HSSFCell cell31 = headerrow3.createCell(1);
cell31.setCellType(HSSFCell.CELL_TYPE_STRING);
cell31.setCellValue(funcCode);
HSSFRow headerrow4 = sheet.createRow(++xlRowCounter);
HSSFCell hcell40 = headerrow4.createCell(0);
hcell40.setCellType(HSSFCell.CELL_TYPE_STRING);
hcell40.setCellValue(DOC_VERSION);
HSSFCell cell41 = headerrow4.createCell(1);
cell41.setCellType(HSSFCell.CELL_TYPE_STRING);
cell41.setCellValue(docVersion);
HSSFRow headerrow5 = sheet.createRow(++xlRowCounter);
HSSFCell hcell50 = headerrow5.createCell(0);
hcell50.setCellType(HSSFCell.CELL_TYPE_STRING);
hcell50.setCellValue(PO_NUMBER);
HSSFCell cell51 = headerrow5.createCell(1);
cell51.setCellType(HSSFCell.CELL_TYPE_STRING);
cell51.setCellValue(PONumber);
HSSFRow headerrow6 = sheet.createRow(++xlRowCounter);
HSSFCell hcell60 = headerrow6.createCell(0);
hcell60.setCellType(HSSFCell.CELL_TYPE_STRING);
hcell60.setCellValue(PO_DATE);
HSSFCell cell61 = headerrow6.createCell(1);
cell61.setCellType(HSSFCell.CELL_TYPE_STRING);
cell61.setCellValue(PODate);
//seperating the header form PO details
xlRowCounter++;
b2blog((new StringBuilder()).append("Callout createHeader() - end createHeader "));
return xlRowCounter;
}
/**
* write Excel Result for SDQ trading partners
* @param poDetails
* @param resultList
* @param sdqFlag
* @param sdqMap
* @return
*/
public static String writeSDQResult(Map poDetails, List resultList,
boolean sdqFlag,
Map sdqMap) throws Exception {
b2blog((new StringBuilder()).append("Callout writeSDQResult() - start writeSDQResult "));
String outputFile = null;
try {
String senderId = (String)poDetails.get(SENDER_ID);
int xlRowCounter = 0;
// Create a New XL Document
HSSFWorkbook wb = new HSSFWorkbook();
// Make a worksheet in the XL document created
HSSFSheet sheet = wb.createSheet();
HSSFRow sdqrow = null;
xlRowCounter = createHeaders(poDetails, sheet, xlRowCounter);
HSSFRow row1 = sheet.createRow(++xlRowCounter);
HSSFCell cell10 = row1.createCell(0);
cell10.setCellType(HSSFCell.CELL_TYPE_STRING);
// Type some content
cell10.setCellValue(SEGMENT_NAME);
HSSFCell cell11 = row1.createCell(1);
cell11.setCellType(HSSFCell.CELL_TYPE_STRING);
cell11.setCellValue(ASSIGNED_ID);
HSSFCell cell12 = row1.createCell(2);
cell12.setCellType(HSSFCell.CELL_TYPE_STRING);
cell12.setCellValue(QUANTITY);
HSSFCell cell13 = row1.createCell(3);
cell13.setCellType(HSSFCell.CELL_TYPE_STRING);
cell13.setCellValue(UOM);
HSSFCell cell14 = row1.createCell(4);
cell14.setCellType(HSSFCell.CELL_TYPE_STRING);
cell14.setCellValue(UNIT_PRICE);
HSSFCell cell15 = row1.createCell(5);
cell15.setCellType(HSSFCell.CELL_TYPE_STRING);
cell15.setCellValue(BASIS_UPC);
boolean evenFlag = false;
Iterator it1 = resultList.iterator();
// int i =xlRowCounter;
int poCount = 0;
while (it1.hasNext()) {
HSSFRow row = sheet.createRow(++xlRowCounter);
Map recordMap = (Map)it1.next();
int columnCount = recordMap.size();
poCount++;
for (int j = 0; j < recordMap.size(); j++) {
// Create row at index zero ( Top Row)
if (j % 2 == 0) {
evenFlag = true;
} else {
evenFlag = false;
}
if (j >= 6 && evenFlag) {
HSSFCell cell1Name = row1.createCell(j);
cell1Name.setCellType(HSSFCell.CELL_TYPE_STRING);
cell1Name.setCellValue(PRODUCT_ID_QUALIFIER);
} else if (j >= 6 && !evenFlag) {
HSSFCell cell1QualName = row1.createCell(j);
cell1QualName.setCellType(HSSFCell.CELL_TYPE_STRING);
cell1QualName.setCellValue(PRODUCT_ID);
}
HSSFCell cell = row.createCell(j);
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
String value = (String)recordMap.get(j);
cell.setCellValue(value);
}
int sdqCounter = 0;
if (sdqMap != null) {
HSSFCell cell1Name = row1.createCell(columnCount + 1);
cell1Name.setCellType(HSSFCell.CELL_TYPE_STRING);
cell1Name.setCellValue(PO_SDQ);
HSSFCell cell1UOMName = row1.createCell(columnCount + 2);
cell1UOMName.setCellType(HSSFCell.CELL_TYPE_STRING);
cell1UOMName.setCellValue(UOM);
HSSFCell cell1QualName = row1.createCell(columnCount + 3);
cell1QualName.setCellType(HSSFCell.CELL_TYPE_STRING);
cell1QualName.setCellValue(ID_CODE_QUALIFIER);
}
for (int s = columnCount + 1; s < sdqMap.size(); s++) {
sdqCounter++;
evenFlag = false;
Map sdqValuemap =
(Map)sdqMap.get(poCount + "-" + sdqCounter);
int valueCounter = 0;
if (sdqValuemap != null && sdqCounter > 1) {
sdqrow = sheet.createRow(++xlRowCounter);
}
if (sdqValuemap != null) {
for (int v = 0; v < sdqValuemap.size(); v++) {
if ((s + v) % 2 == 0) {
evenFlag = true;
} else {
evenFlag = false;
}
if ((s + v) >= columnCount + 4 && evenFlag) {
HSSFCell cellIdName = null;
if (sdqCounter > 1) {
cellIdName = row1.createCell(s + v);
} else {
cellIdName = row1.createCell(s + v);
}
cellIdName.setCellType(HSSFCell.CELL_TYPE_STRING);
cellIdName.setCellValue(ID_CODE);
} else if ((s + v) >= columnCount + 4 &&
!evenFlag) {
HSSFCell cell1QuantityName = null;
if (sdqCounter > 1) {
cell1QuantityName = row1.createCell(s + v);
} else {
cell1QuantityName = row1.createCell(s + v);
}
cell1QuantityName.setCellType(HSSFCell.CELL_TYPE_STRING);
cell1QuantityName.setCellValue(QUANTITY);
}
HSSFCell sdqcell = null;
if (sdqCounter > 1) {
sdqcell = sdqrow.createCell(s + v - 1);
} else {
sdqcell = row.createCell(s + v);
}
sdqcell.setCellType(HSSFCell.CELL_TYPE_STRING);
String value =
(String)sdqValuemap.get(valueCounter);
sdqcell.setCellValue(value);
valueCounter++;
}
} else
break;
}
// xlRowCounter++;
}
sheet.autoSizeColumn(10);
b2blog((new StringBuilder()).append("Callout writeSDQResult() - going to write file ").append(outputFile));
if (senderId != null)
outputFile =
outputFolder + senderId.trim() + "_callout_" + new Date().getTime() +
".xls";
else
outputFile =
outputFolder + "NA" + "_callout_" + new Date().getTime() +
".xls";
FileOutputStream fileOut = new FileOutputStream(outputFile);
// Write the XL sheet
wb.write(fileOut);
fileOut.flush();
fileOut.close();
b2blog(new StringBuilder().append("File Created .. :: ").append(outputFile));
} catch (Exception e) {
b2blog(new StringBuilder().append("!!! create failed()writeSDQResult : ").append(e.getStackTrace()));
throw e;
}
b2blog((new StringBuilder()).append("Callout writeSDQResult() - end writeSDQResult "));
return outputFile;
}
}
package com.geo.b2b.email;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;
import java.util.StringTokenizer;
import javax.activation.DataHandler;
import javax.activation.DataSource;
import javax.activation.FileDataSource;
import javax.mail.Authenticator;
import javax.mail.BodyPart;
import javax.mail.Message;
import javax.mail.MessagingException;
import javax.mail.Multipart;
import javax.mail.PasswordAuthentication;
import javax.mail.Session;
import javax.mail.Transport;
import javax.mail.internet.AddressException;
import javax.mail.internet.InternetAddress;
import javax.mail.internet.MimeBodyPart;
import javax.mail.internet.MimeMessage;
import javax.mail.internet.MimeMultipart;
public class EmailUtility
{
static String password = "password";
public static void sendMail(String mailServer, String from, String to,
String subject, String messageBody,
String[] attachments) throws MessagingException, AddressException{
// Setup mail server
Properties props = new Properties();
props.put("mail.smtp.host", mailServer);
props.put("mail.smtp.socketFactory.port", "465");
props.put("mail.smtp.socketFactory.class", "javax.net.ssl.SSLSocketFactory");
// props.put("mail.smtp.auth", "false");
props.put("mail.smtp.port", "465");
props.put("mail.smtp.startssl.enable","true");
props.put( "mail.smtp.auth", "true");
Authenticator smtpAuthenticator = null;
smtpAuthenticator = new Authenticator() {
public PasswordAuthentication getPasswordAuthentication() {
return new PasswordAuthentication("username", password);
}
};
Session session = Session.getInstance(props, smtpAuthenticator);
session.setDebug(true);
// Get a mail session
// Define a new mail message
Message message = new MimeMessage(session);
message.setFrom(new InternetAddress(from));
List recipients = new ArrayList();
StringTokenizer st = new StringTokenizer(to,",");
while (st.hasMoreTokens()) {
recipients.add(st.nextToken());
}
InternetAddress[] addressTo = new javax.mail.internet.InternetAddress[recipients.size()];
for (int i = 0; i < recipients.size(); i++){
addressTo[i] = new InternetAddress((String)recipients.get(i));
}
message.setRecipients(javax.mail.Message.RecipientType.TO, addressTo);
message.addRecipients(Message.RecipientType.TO, addressTo);
message.setSubject(subject);
BodyPart messageBodyPart = new MimeBodyPart();
messageBodyPart.setText(messageBody);
Multipart multipart = new MimeMultipart();
multipart.addBodyPart(messageBodyPart);
addAtachments(attachments, multipart);
message.setContent(multipart);
Transport.send(message);
}
public static void sendMail (String subject, String messageBody,
String attachments) throws MessagingException,
AddressException {
String server= "geo.com";
String from = "TSUPPORT@geo.com";
String to = "geo @gmail.com";
String[] filenames = {attachments};
sendMail(server,from,to,subject,messageBody,filenames);
}
public static void sendMail (String subject, String messageBody) throws MessagingException,
AddressException {
String server= "geo.com";
String from = "TSUPPORT@geo.com";
String to = "geo @gmail.com";
String[] filenames = {};
sendMail(server,from,to,subject,messageBody,filenames);
}
protected static void addAtachments(String[] attachments, Multipart multipart)
throws MessagingException, AddressException{
for(int i = 0; i<= attachments.length -1; i++){
String filename = attachments[i];
MimeBodyPart attachmentBodyPart = new MimeBodyPart();
DataSource source = new FileDataSource(filename);
attachmentBodyPart.setDataHandler(new DataHandler(source));
attachmentBodyPart.setFileName(filename);
multipart.addBodyPart(attachmentBodyPart);
}
}
}
