package ibase.webitm.ejb.dis.servlet;
/*
   ServletName:TrackInfoServlet(D14BSUN003)
   Developer:Mahendra Jadhav
   Date: 25/06/2014
*/
import java.io.*;

import java.net.URLDecoder;
import java.net.URLEncoder;
import java.rmi.RemoteException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.Enumeration;
import javax.naming.*;
import javax.activation.*;
import javax.servlet.*;
import javax.servlet.http.*;
import javax.xml.parsers.DocumentBuilder;
import javax.xml.parsers.DocumentBuilderFactory;

import org.w3c.dom.*;

import java.util.Calendar;
import java.util.HashMap;
import java.util.Locale;
import java.util.StringTokenizer;
import java.util.ArrayList;

import ibase.system.config.*;
import ibase.utility.BaseLogger;
import ibase.utility.CommonConstants;
import ibase.utility.UserInfoBean;
import ibase.webitm.ejb.*;
import ibase.webitm.utility.*;
import java.sql.Timestamp;

import java.util.Date;


public class TrackInfoServlet extends HttpServlet
{
	ServletContext servletContext = null;
	ServletConfig servletConfig = null;

	protected InitialContext ctx = null;
	final static String XTRA_PARAMS_SEPARATOR = "~~";
	GenericUtility genericUtility = null;
	int currentFormNo = 0;
	StringBuffer valueXmlString = new StringBuffer();
	String columnValue = null;
	NodeList parentNodeList = null;
	Node parentNode = null; 
	Node childNode = null;
	NodeList childNodeList = null;
	String childNodeName = null;
	int childNodeListLength = 0;
	int ctr=0;
	SimpleDateFormat sdf;
    String result = null;
    Document dom = null;
    String sql="" ,ipAddress="";
    String objName="",tranId="" ,tran_Date = "" ,siteCode="",siteAdd1="",siteAdd2="",siteCity="",sitePin="",countCodeSite="",shipmentId="" ,shipment_date="" ;
    String noArt="",unitShip="",tranCode="",bolNo="",scac="",custCodeDlv="",drugLicNo="",dlvAdd1="",dlvAdd2="" ;
    String dlvCity="",dlvPin="",countCodeDlv="",planStart="",planEnd="",siteCodeDlv="";
    String lineNo="",refSer="",refId="",invoiceId="",itemCode="",itemDescr="",itemCodeNDC="",itemCodeUPC="",itemStrength="",lotNo="",exp_date="",lotSl="";
	String locCode="",convQtyStduom="",unitSTD="",unit="";
	String custProd="",prod_date="",sscc_18="";
	String xmlString="",windowName="";
	
	String distOrderIssId="",trackId="",unitStd="",packCode="";
	String distOrder ="",siteCodeShip ="",distRoute  ="",lr_no ="",lorryNo ="",volume ="",frtAmt ="",taxAmt ="",netAmt ="",locCodeGit ="";
	String confPassword ="",orderType ="",transType ="",exchRate ="",empCodeAprv ="",qcRead ="",gpNo ="",locCodeGitbf ="",printStat ="",issueRef ="",parentTranId ="";
	String revTran ="",projCode ="",siteCodeBil ="",palletWt ="",udfStr1 ="",crTerm ="",dlvTerm ="",lableType ="",eff_date="",lr_date="",transMode="",amount="",gp_date="",outsideInspection="";
	String siteCode_asn="",suppCode_asn="",asnRequired="",asnCreated="";
	double grossWtHD=0.0,tareWtHD=0.0,netWtHD=0.0;
	double grossWtDT=0.0,tareWtDT=0.0,netWtDT=0.0;
	double quantityOrd=0.0,quantity=0.0,quantityStduom=0.0,packQty=0.0;
	Date tranDate=null;
	Date planStartDate=null;
	Date planEndDate=null;
	Date expDate=null;
	Date prodDate=null;
	Date effDate=null;
	Date lrDate=null;
	Date shipmentDate=null;
	
	
	public void init(ServletConfig servletConfig) throws ServletException
	{
		this.servletConfig = servletConfig;
		this.servletContext = servletConfig.getServletContext();
	}

	
	
	
	public void doPost(HttpServletRequest request, HttpServletResponse response)throws ServletException,IOException
	{
		System.out.println("inside dopost method 14-08-2014 03:10 !!!!!!!!! @@@");
		String msg="",despId="",tran_hist_id="",callType="";
		int detailsTICnt=0;
		int headerTICnt=0;
		int detailsTHCnt=0;
		int headerTHCnt=0;
		int length=0;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		Connection conn =null;
		ConnDriver connDriver = new ConnDriver();
		request.setCharacterEncoding("UTF-8");
		boolean flag=false;
		HashMap<String, String> tempMap = new HashMap<String, String>();
		int cnt=0;
		
		PrintWriter out = response.getWriter();
		xmlString=request.getParameter("xmlData");
		System.out.println("xml !!@@@@@@!!! "+xmlString);
	
		
       try
       {
    	          	   
   		   		conn = connDriver.getConnectDB("DriverITM");
   		   		genericUtility = GenericUtility.getInstance();
   		   	    sdf = new SimpleDateFormat(genericUtility.getApplDateFormat());
   		        dom = parseString(xmlString);
        	    parentNodeList = dom.getElementsByTagName("Detail1");
        		parentNode = parentNodeList.item(0);
        	    objName= checkNull(this.genericUtility.getColumnValue("objName", dom));
        		System.out.println("objName !!! "+objName);
        		windowName="w_"+objName;
        		parentNodeList = dom.getElementsByTagName("Detail1");
        		parentNode = parentNodeList.item(0);
        	
				if(objName.trim().equalsIgnoreCase("tnt_tran_info"))
				{	
					tranId= checkNull(this.genericUtility.getColumnValue("tran_id", dom));
					System.out.println("tranId "+tranId);
					tran_Date= checkNull(genericUtility.getColumnValue("tran_date", dom));
					System.out.println("tranDate "+tran_Date);
					tranDate=sdf.parse(tran_Date);
					System.out.println("tranDate !!!!!!! :"+tranDate);
					siteCode= checkNull(genericUtility.getColumnValue("site_code", dom));
					siteAdd1= checkNull(genericUtility.getColumnValue("site_add1", dom));
					siteAdd2= checkNull(genericUtility.getColumnValue("site_add2", dom));
					siteCity= checkNull(genericUtility.getColumnValue("site_city", dom));
					sitePin= checkNull(genericUtility.getColumnValue("site_pin", dom));
					countCodeSite= checkNull(genericUtility.getColumnValue("count_code__site", dom));
					shipmentId= checkNull(genericUtility.getColumnValue("shipment_id", dom));
					shipment_date= genericUtility.getColumnValue("shipment_date", dom);
					shipmentDate = shipment_date == null ? null : sdf.parse(genericUtility.getColumnValue("shipment_date", dom));
					System.out.println("shipmentDate  !!! "+shipmentDate);
					noArt= checkNull(genericUtility.getColumnValue("no_art", dom));
					grossWtHD= Double.parseDouble(genericUtility.getColumnValue("gross_weight", dom));
					netWtHD= Double.parseDouble(genericUtility.getColumnValue("net_weight", dom));
					tareWtHD= Double.parseDouble(genericUtility.getColumnValue("tare_weight", dom));
					unitShip= checkNull(genericUtility.getColumnValue("unit_ship", dom));
					tranCode= checkNull(genericUtility.getColumnValue("tran_code", dom));
					bolNo= checkNull(genericUtility.getColumnValue("bol_no", dom));
					scac= checkNull(genericUtility.getColumnValue("scac",dom));
					custCodeDlv= checkNull(genericUtility.getColumnValue("cust_code__dlv", dom));
					drugLicNo= checkNull(genericUtility.getColumnValue("drug_lic_no", dom));
					dlvAdd1= checkNull(genericUtility.getColumnValue("dlv_add1", dom));
					dlvAdd2= checkNull(genericUtility.getColumnValue("dlv_add2", dom));
					dlvCity= checkNull(genericUtility.getColumnValue("dlv_city", dom));
					dlvPin= checkNull(genericUtility.getColumnValue("dlv_pin", dom));
					countCodeDlv= checkNull(genericUtility.getColumnValue("count_code__dlv", dom));
					planStart = genericUtility.getColumnValue("plan_start", dom);
					planStartDate = planStart == null ? null : sdf.parse(genericUtility.getColumnValue("plan_start", dom));
					System.out.println("planStartDate  !!!"+planStartDate);
					planEnd= genericUtility.getColumnValue("plan_end", dom);
					planEndDate = planEnd == null ? null : sdf.parse(genericUtility.getColumnValue("plan_end", dom));
					System.out.println("planEndDate  !!!"+planEndDate);
					siteCodeDlv = checkNull(genericUtility.getColumnValue("site_code__dlv", dom));
					callType=checkNull(genericUtility.getColumnValue("callType", dom));
					System.out.println("callType  !!!!!! "+callType);
					siteCode_asn=checkNull(genericUtility.getColumnValue("site_code__asn", dom));
					System.out.println("siteCode_asn !!! "+siteCode_asn);
					suppCode_asn=checkNull(genericUtility.getColumnValue("supp_code__asn", dom));
					System.out.println("suppCode_asn !!! "+suppCode_asn);
					asnRequired=checkNull(genericUtility.getColumnValue("asn_required", dom));
					System.out.println("asnRequired !!! "+asnRequired);
					asnCreated=checkNull(genericUtility.getColumnValue("asn_created", dom));
					System.out.println("asnCreated !!! "+asnCreated);
					
					System.out.println("siteCode "+siteCode);
					System.out.println("siteAdd1 "+siteAdd1);
					System.out.println("siteCity "+siteCity);
					System.out.println("sitePin "+sitePin);
					System.out.println("countCodeSite "+countCodeSite);
					System.out.println("shipmentId "+shipmentId);
					System.out.println("siteCode_asn "+siteCode_asn);
					System.out.println("suppCode_asn "+suppCode_asn);
					System.out.println("asnRequired "+asnRequired);
					System.out.println("asnCreated "+asnCreated);
					
			
				}
					tranId=generateTranId(windowName ,xmlString,conn);
					System.out.println("generated tranId!!!!!!!!!!!!!! "+tranId);
					
					
				if(tranId != null)
				{
					System.out.println("execute TNT_TRAN_INFO insert query starts!!!!!");
					sql = "Insert into TNT_TRAN_INFO(TRAN_ID,TRAN_DATE,SITE_CODE,SITE_ADD1,SITE_ADD2,SITE_CITY,SITE_PIN,COUNT_CODE__SITE,SHIPMENT_ID,SHIPMENT_DATE,NO_ART,GROSS_WEIGHT,NET_WEIGHT,TARE_WEIGHT,UNIT__SHIP,TRAN_CODE,BOL_NO,SCAC,CUST_CODE__DLV,DRUG_LIC_NO,DLV_ADD1,DLV_ADD2,DLV_CITY,DLV_PIN,COUNT_CODE__DLV,PLAN_START,PLAN_END,SITE_CODE__DLV,SITE_CODE__ASN,SUPP_CODE__ASN,ASN_REQUIRED,ASN_CREATED)";
				    sql = sql + "values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
				    pstmt = conn.prepareStatement(sql);
				    pstmt.setString(1, tranId);
				    pstmt.setTimestamp(2,new java.sql.Timestamp(tranDate.getTime()));
				    pstmt.setString(3, siteCode);
				    pstmt.setString(4, siteAdd1);
				    pstmt.setString(5, siteAdd2);
				    pstmt.setString(6, siteCity);
				    pstmt.setString(7, sitePin);
				    pstmt.setString(8, countCodeSite);
				    pstmt.setString(9, shipmentId);
				    shipmentDate=shipmentDate==null?null:new java.sql.Timestamp(shipmentDate.getTime());
				    pstmt.setTimestamp(10,(Timestamp) shipmentDate);
				    pstmt.setString(11, noArt);
				    pstmt.setDouble(12, grossWtHD);
				    pstmt.setDouble(13, netWtHD);
				    pstmt.setDouble(14, tareWtHD);
				    pstmt.setString(15, unitShip);
				    pstmt.setString(16, tranCode);
				    pstmt.setString(17, bolNo);
				    pstmt.setString(18, scac);
				    pstmt.setString(19, custCodeDlv);
				    pstmt.setString(20, drugLicNo);
				    pstmt.setString(21, dlvAdd1);
				    pstmt.setString(22, dlvAdd2);
				    pstmt.setString(23, dlvCity);
				    pstmt.setString(24, dlvPin);
				    pstmt.setString(25, countCodeDlv);
				    planStartDate=planStartDate==null?null:new java.sql.Timestamp(planStartDate.getTime());
				    planEndDate=planEndDate==null?null:new java.sql.Timestamp(planEndDate.getTime());
				    pstmt.setTimestamp(26,(Timestamp) planStartDate );
				    pstmt.setTimestamp(27, (Timestamp) planEndDate);
				    pstmt.setString(28, siteCodeDlv);
				    pstmt.setString(29, siteCode_asn);
				    pstmt.setString(30, suppCode_asn);
				    pstmt.setString(31, asnRequired);
				    pstmt.setString(32, asnCreated);
				   
				    headerTICnt=pstmt.executeUpdate();
				    System.out.println("headerCnt !!!!!! "+headerTICnt);
					pstmt.close();
					
		
				}
				else
				{
					System.out.println("tranId generated null value");
				}
				
				System.out.println("Parsing datils information !!!!");
				parentNodeList = dom.getElementsByTagName("Detail2");
				parentNode = parentNodeList.item(0);
				length=dom.getElementsByTagName("Detail2").getLength(); 
				System.out.println("length  :: "+length);
				
				for(int i=0 ; i<length ; i++)
				{
				
						lineNo= genericUtility.getColumnValueFromNode("line_no", dom.getElementsByTagName("Detail2").item(i));
						System.out.println("lineNo :"+lineNo);
						refSer=genericUtility.getColumnValueFromNode("ref_ser", dom.getElementsByTagName("Detail2").item(i));
						refId=genericUtility.getColumnValueFromNode("ref_id", dom.getElementsByTagName("Detail2").item(i));
						invoiceId=genericUtility.getColumnValueFromNode("invoice_id", dom.getElementsByTagName("Detail2").item(i));
						itemCode=genericUtility.getColumnValueFromNode("item_code", dom.getElementsByTagName("Detail2").item(i));
						itemDescr=genericUtility.getColumnValueFromNode("item_descr", dom.getElementsByTagName("Detail2").item(i));
						itemCodeNDC=genericUtility.getColumnValueFromNode("item_code__ndc", dom.getElementsByTagName("Detail2").item(i));
						itemCodeUPC=genericUtility.getColumnValueFromNode("item_code__upc", dom.getElementsByTagName("Detail2").item(i));
						itemStrength=genericUtility.getColumnValueFromNode("item_strength", dom.getElementsByTagName("Detail2").item(i));
						lotNo=genericUtility.getColumnValueFromNode("lot_no", dom.getElementsByTagName("Detail2").item(i));
						exp_date=genericUtility.getColumnValueFromNode("exp_date", dom.getElementsByTagName("Detail2").item(i));
						planStartDate = genericUtility.getColumnValue("plan_start", dom) == null ? null : sdf.parse(genericUtility.getColumnValue("plan_start", dom));
						lotSl=genericUtility.getColumnValueFromNode("lot_sl", dom.getElementsByTagName("Detail2").item(i));
						quantityOrd=genericUtility.getColumnValueFromNode("quantity__ord", dom.getElementsByTagName("Detail2").item(i))==null?0.0:Double.parseDouble(genericUtility.getColumnValueFromNode("quantity__ord", dom.getElementsByTagName("Detail2").item(i)));
						quantity=genericUtility.getColumnValueFromNode("quantity", dom.getElementsByTagName("Detail2").item(i))==null?0.0:Double.parseDouble(genericUtility.getColumnValueFromNode("quantity", dom.getElementsByTagName("Detail2").item(i)));
						locCode=genericUtility.getColumnValueFromNode("loc_code", dom.getElementsByTagName("Detail2").item(i));
						convQtyStduom=genericUtility.getColumnValueFromNode("conv__qty_stduom", dom.getElementsByTagName("Detail2").item(i));
						unitSTD=genericUtility.getColumnValueFromNode("unit__std", dom.getElementsByTagName("Detail2").item(i));
						unit=genericUtility.getColumnValueFromNode("unit", dom.getElementsByTagName("Detail2").item(i));
						quantityStduom=genericUtility.getColumnValueFromNode("quantity__stduom", dom.getElementsByTagName("Detail2").item(i))==null?0.0:Double.parseDouble(genericUtility.getColumnValueFromNode("quantity__stduom", dom.getElementsByTagName("Detail2").item(i)));
						packQty=genericUtility.getColumnValueFromNode("pack_qty", dom.getElementsByTagName("Detail2").item(i))==null?0.0:Double.parseDouble(genericUtility.getColumnValueFromNode("pack_qty", dom.getElementsByTagName("Detail2").item(i)));
						grossWtDT=genericUtility.getColumnValueFromNode("gross_weight", dom.getElementsByTagName("Detail2").item(i))==null?0.0:Double.parseDouble(genericUtility.getColumnValueFromNode("gross_weight", dom.getElementsByTagName("Detail2").item(i)));
						netWtDT=genericUtility.getColumnValueFromNode("net_weight", dom.getElementsByTagName("Detail2").item(i))==null?0.0:Double.parseDouble(genericUtility.getColumnValueFromNode("net_weight", dom.getElementsByTagName("Detail2").item(i)));
						tareWtDT=genericUtility.getColumnValueFromNode("tare_weight", dom.getElementsByTagName("Detail2").item(i))==null?0.0:Double.parseDouble(genericUtility.getColumnValueFromNode("tare_weight", dom.getElementsByTagName("Detail2").item(i)));
						custProd=genericUtility.getColumnValueFromNode("cust_pord", dom.getElementsByTagName("Detail2").item(i));
						prod_date=genericUtility.getColumnValueFromNode("pord_date", dom.getElementsByTagName("Detail2").item(i));
						sscc_18=genericUtility.getColumnValueFromNode("sscc_18", dom.getElementsByTagName("Detail2").item(i));
						System.out.println("refSer  position "+ i +"is ::"+refSer);
						System.out.println("refId  position "+ i +"is ::"+refId);
						System.out.println("invoiceId  position "+ i +"is ::"+invoiceId);
						System.out.println("itemCode  position "+ i +"is ::"+itemCode);
						System.out.println("itemDescr  position "+ i +"is ::"+itemDescr);
						System.out.println("itemCodeNDC  position "+ i +"is ::"+itemCodeNDC);
						System.out.println("itemCodeUPC  position "+ i +"is ::"+itemCodeUPC);
						System.out.println("itemStrength  position "+ i +"is ::"+itemStrength);
						System.out.println("lotNo  position "+ i +"is ::"+lotNo);
						System.out.println("expDate  position "+ i +"is ::"+exp_date);
						System.out.println("lotSl  position "+ i +"is ::"+lotSl);
						System.out.println("quantityOrd  position "+ i +"is ::"+quantityOrd);
						System.out.println("quantity  position "+ i +"is ::"+quantity);
						System.out.println("locCode  position "+ i +"is ::"+locCode);
						System.out.println("convQtyStduom  position "+ i +"is ::"+convQtyStduom);
						System.out.println("unitSTD  position "+ i +"is ::"+unitSTD);
						System.out.println("unit  position "+ i +"is ::"+unit);
						System.out.println("quantityStduom  position "+ i +"is ::"+quantityStduom);
						System.out.println("packQty  position "+ i +"is ::"+packQty);
						System.out.println("grossWtDt  position "+ i +"is ::"+grossWtDT);
						System.out.println("netWtDt  position "+ i +"is ::"+netWtDT);
						System.out.println("tareWtDt  position "+ i +"is ::"+tareWtDT);
						System.out.println("custProd  position "+ i +"is ::"+custProd);
						System.out.println("prodDate  position "+ i +"is ::"+prodDate);
						System.out.println("sscc_18  position "+ i +"is ::"+sscc_18);
						
						if(tranId != null && lineNo != null)
						{
				            System.out.println("execute insert TNT_TRAN_INFODET query !!!!!!");
				    		sql = "Insert into TNT_TRAN_INFODET(TRAN_ID,LINE_NO,REF_SER,REF_ID,INVOICE_ID,ITEM_CODE,ITEM_DESCR,ITEM_CODE__NDC,ITEM_CODE__UPC,ITEM_STRENGTH,LOT_NO,EXP_DATE,LOT_SL,QUANTITY__ORD,QUANTITY,LOC_CODE,CONV__QTY_STDUOM,UNIT__STD,UNIT,QUANTITY__STDUOM,PACK_QTY,GROSS_WEIGHT,NET_WEIGHT,TARE_WEIGHT,CUST_PORD,PORD_DATE,SSCC_18)";
						    sql = sql + "values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
						    pstmt = conn.prepareStatement(sql);
						    pstmt.setString(1, tranId);
						    pstmt.setString(2, lineNo);
						    pstmt.setString(3, refSer);
						    pstmt.setString(4, refId);
						    pstmt.setString(5, invoiceId);
						    pstmt.setString(6, itemCode);
						    pstmt.setString(7, itemDescr);
						    pstmt.setString(8, itemCodeNDC);
						    pstmt.setString(9, itemCodeUPC);
						    pstmt.setString(10, itemStrength);
						    pstmt.setString(11, lotNo);
						    pstmt.setString(12, exp_date);
						    pstmt.setString(13, lotSl);
						    pstmt.setDouble(14, quantityOrd);
						    pstmt.setDouble(15, quantity);
						    pstmt.setString(16, locCode);
						    pstmt.setString(17, convQtyStduom);
						    pstmt.setString(18, unitSTD);
						    pstmt.setString(19, unit);
						    pstmt.setDouble(20, quantityStduom);
						    pstmt.setDouble(21, packQty);
						    pstmt.setDouble(22, grossWtDT);
						    pstmt.setDouble(23, netWtDT);
						    pstmt.setDouble(24, tareWtDT);
						    pstmt.setString(25, custProd);
						    pstmt.setString(26, prod_date);
						    pstmt.setString(27, sscc_18);
						 	detailsTICnt = pstmt.executeUpdate();
						 	pstmt.close();
						 	System.out.println("detailsTICnt !!!!!! "+detailsTICnt);
						 	flag=true;
						 	/*
						 	 * invoice id get from tnt_tran_infodet
						 	 * */
						 	System.out.println("Servlet call from : "+callType.trim());
						 	System.out.println("invoiceId : "+invoiceId);
						 	if(callType.trim().equalsIgnoreCase("CH") && invoiceId != null)
						 	{
						 		
						 		    flag=false;
							 		/*sql="select desp_id from despatch where gp_no=?";
									
							 		pstmt = conn.prepareStatement(sql);
							 		pstmt.setString(1, invoiceId);
							 		rs = pstmt.executeQuery();
									if (rs.next()) 
									{
										despId=rs.getString("desp_id");	
									}
									rs.close();
									rs = null;
									pstmt.close();
									pstmt = null;*/
						 		    
						 		   sql="select desp_id from despatchdet where invoice_id=?";
									
							 		pstmt = conn.prepareStatement(sql);
							 		pstmt.setString(1, invoiceId);
							 		rs = pstmt.executeQuery();
									if (rs.next()) 
									{
										despId=rs.getString("desp_id");	
									}
									rs.close();
									rs = null;
									pstmt.close();
									pstmt = null;
								 	
									System.out.println("For TH : despId :"+despId);
								
									sql="select tran_id from tnt_tran_infodet where ref_id=? and ref_ser='S-DSP'";
									
									pstmt = conn.prepareStatement(sql);
									pstmt.setString(1, despId);
									rs = pstmt.executeQuery();
									if (rs.next()) 
									{
									    tran_hist_id=rs.getString("tran_id");  		
									}
									rs.close();
									rs = null;
									pstmt.close();
									pstmt = null;
									
								
									sql="select count(*) from TNT_TRAN_HIST where tran_id=?";
									
									pstmt = conn.prepareStatement(sql);
									pstmt.setString(1, tran_hist_id);
									rs = pstmt.executeQuery();
									if (rs.next()) 
									{
									    cnt=rs.getInt(1);  		
									}
									rs.close();
									rs = null;
									pstmt.close();
									pstmt = null;
									
									if(cnt > 0)
									{
										System.out.println("tran id already exist in tnt_tran_hist table!!!!!");
										tran_hist_id="";
									}
								
								
							 	
									System.out.println("For TH : tran_hist_id :"+tran_hist_id);
								
									if(tran_hist_id != "")
									{	
									
										sql="select TRAN_DATE ,SITE_CODE,SITE_ADD1,SITE_ADD2,SITE_CITY,SITE_PIN,COUNT_CODE__SITE,"+
											"SHIPMENT_ID,SHIPMENT_DATE,NO_ART,GROSS_WEIGHT,NET_WEIGHT,TARE_WEIGHT,UNIT__SHIP,TRAN_CODE,"+
											"BOL_NO,SCAC,CUST_CODE__DLV,DRUG_LIC_NO,DLV_ADD1,DLV_ADD2,DLV_CITY,DLV_PIN,COUNT_CODE__DLV,PLAN_START, "+	
											"PLAN_END,SITE_CODE__DLV from tnt_tran_info where tran_id=?";
											
											pstmt = conn.prepareStatement(sql);
											pstmt.setString(1, tran_hist_id);
											rs = pstmt.executeQuery();
											if (rs.next()) 
											{
												tranDate = rs.getDate("TRAN_DATE");
												siteCode = checkNull(rs.getString("SITE_CODE"));
												siteAdd1 = checkNull(rs.getString("SITE_ADD1"));
												siteAdd2 = checkNull(rs.getString("SITE_ADD2"));
												siteCity = checkNull(rs.getString("SITE_CITY"));
												sitePin = checkNull(rs.getString("SITE_PIN"));
												countCodeSite = checkNull(rs.getString("COUNT_CODE__SITE"));
												shipmentId = checkNull(rs.getString("SHIPMENT_ID"));
												//shipment_date = checkNull(rs.getString("SHIPMENT_DATE"));
												//shipmentDate = shipment_date == null ? null : sdf.parse(rs.getString("SHIPMENT_DATE"));
												shipmentDate=rs.getDate("SHIPMENT_DATE");
												noArt = checkNull(rs.getString("NO_ART"));
											    grossWtHD = rs.getDouble("GROSS_WEIGHT");
										        netWtHD = rs.getDouble("NET_WEIGHT");
											    tareWtHD =rs.getDouble("TARE_WEIGHT");
												unitShip = checkNull(rs.getString("UNIT__SHIP"));
												tranCode = checkNull(rs.getString("TRAN_CODE"));
												bolNo = checkNull(rs.getString("BOL_NO"));
												scac = checkNull(rs.getString("SCAC"));
												custCodeDlv = checkNull(rs.getString("CUST_CODE__DLV"));
												drugLicNo = checkNull(rs.getString("DRUG_LIC_NO"));
												dlvAdd1 = checkNull(rs.getString("DLV_ADD1"));
												dlvAdd2 = checkNull(rs.getString("DLV_ADD2"));
												dlvCity = checkNull(rs.getString("DLV_CITY"));
												dlvPin = checkNull(rs.getString("DLV_PIN"));
												countCodeDlv = checkNull(rs.getString("COUNT_CODE__DLV"));
												planStartDate = rs.getDate("PLAN_START");
												planEndDate = rs.getDate("PLAN_END");
												siteCodeDlv = checkNull(rs.getString("SITE_CODE__DLV"));
												
										 
										
										
										
												System.out.println("start execute insert TNT_TRAN_HIST !!");
												sql = "Insert into tarodev.TNT_TRAN_HIST(TRAN_ID,TRAN_DATE,SITE_CODE,SITE_ADD1,SITE_ADD2,SITE_CITY,SITE_PIN,COUNT_CODE__SITE,SHIPMENT_ID,SHIPMENT_DATE,NO_ART,GROSS_WEIGHT,NET_WEIGHT,TARE_WEIGHT,UNIT__SHIP,TRAN_CODE,BOL_NO,SCAC,CUST_CODE__DLV,DRUG_LIC_NO,DLV_ADD1,DLV_ADD2,DLV_CITY,DLV_PIN,COUNT_CODE__DLV,PLAN_START,PLAN_END,SITE_CODE__DLV)";
											    sql = sql + "values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
											    pstmt = conn.prepareStatement(sql);
											    pstmt.setString(1, tran_hist_id);
											    pstmt.setTimestamp(2,new java.sql.Timestamp(tranDate.getTime()));
											    pstmt.setString(3, siteCode);
											    pstmt.setString(4, siteAdd1);
											    pstmt.setString(5, siteAdd2);
											    pstmt.setString(6, siteCity);
											    pstmt.setString(7, sitePin);
											    pstmt.setString(8, countCodeSite);
											    pstmt.setString(9, shipmentId);
											    shipmentDate=shipmentDate==null?null:new java.sql.Timestamp(shipmentDate.getTime());
											    pstmt.setTimestamp(10,(Timestamp) shipmentDate);
											    pstmt.setString(11, noArt);
											    pstmt.setDouble(12, grossWtHD);
											    pstmt.setDouble(13, netWtHD);
											    pstmt.setDouble(14, tareWtHD);
											    pstmt.setString(15, unitShip);
											    pstmt.setString(16, tranCode);
											    pstmt.setString(17, bolNo);
											    pstmt.setString(18, scac);
											    pstmt.setString(19, custCodeDlv);
											    pstmt.setString(20, drugLicNo);
											    pstmt.setString(21, dlvAdd1);
											    pstmt.setString(22, dlvAdd2);
											    pstmt.setString(23, dlvCity);
											    pstmt.setString(24, dlvPin);
											    pstmt.setString(25, countCodeDlv);
											    planStartDate=planStartDate==null?null:new java.sql.Timestamp(planStartDate.getTime());
											    planEndDate=planEndDate==null?null:new java.sql.Timestamp(planEndDate.getTime());
											    pstmt.setTimestamp(26,(Timestamp) planStartDate );
											    pstmt.setTimestamp(27, (Timestamp) planEndDate);
											    pstmt.setString(28, siteCodeDlv);
											   
											    headerTHCnt=pstmt.executeUpdate();
											    System.out.println("headerCnt !!!!!! "+headerTHCnt);
												pstmt.close();
												
																		
											}
											rs.close();
											rs = null;
											pstmt.close();
											pstmt = null; 
											
											System.out.println("start execute insert TNT_TRAN_HIST_det !!");
									
											sql="select REF_SER,REF_ID,INVOICE_ID,ITEM_CODE,ITEM_DESCR,ITEM_CODE__NDC,ITEM_CODE__UPC,"+
												"ITEM_STRENGTH ,LOT_NO,EXP_DATE,LOT_SL,QUANTITY__ORD,QUANTITY,LOC_CODE,CONV__QTY_STDUOM,"+
												"UNIT__STD,UNIT,QUANTITY__STDUOM,PACK_QTY,GROSS_WEIGHT,NET_WEIGHT,TARE_WEIGHT,CUST_PORD,PORD_DATE ,SSCC_18"+
												" from tnt_tran_infodet where tran_id=?";
												
												pstmt = conn.prepareStatement(sql);
												pstmt.setString(1, tran_hist_id);
												rs = pstmt.executeQuery();
												while (rs.next()) 
												{
													cnt++;
											
													refSer = checkNull(rs.getString("REF_SER"));
													refId = checkNull(rs.getString("REF_ID"));
													invoiceId = checkNull(rs.getString("INVOICE_ID"));
												    itemCode = checkNull(rs.getString("ITEM_CODE"));
												    itemDescr = checkNull(rs.getString("ITEM_DESCR"));
													itemCodeNDC = checkNull(rs.getString("ITEM_CODE__NDC"));
													itemCodeUPC = checkNull(rs.getString("ITEM_CODE__UPC"));
													itemStrength = checkNull(rs.getString("ITEM_STRENGTH"));
												    lotNo = checkNull(rs.getString("LOT_NO"));
													expDate = rs.getDate("EXP_DATE");
												    lotSl = checkNull(rs.getString("LOT_SL"));
													quantityOrd = rs.getDouble("QUANTITY__ORD");
												    quantity = rs.getDouble("QUANTITY");
												    locCode = checkNull(rs.getString("LOC_CODE"));
													convQtyStduom = checkNull(rs.getString("CONV__QTY_STDUOM"));
													unitStd = checkNull(rs.getString("UNIT__STD"));
												    unit = checkNull(rs.getString("UNIT"));
													quantityStduom = rs.getDouble("QUANTITY__STDUOM");
												    packQty = rs.getDouble("PACK_QTY");
												    grossWtHD = rs.getDouble("GROSS_WEIGHT");
												    netWtHD = rs.getDouble("NET_WEIGHT");
												    tareWtHD = rs.getDouble("TARE_WEIGHT");
													custProd = checkNull(rs.getString("CUST_PORD"));
													prodDate = rs.getDate("PORD_DATE");
													sscc_18 = checkNull(rs.getString("SSCC_18"));
													
													sql = "Insert into TNT_TRAN_HIST_DET(TRAN_ID,LINE_NO,REF_SER,REF_ID,INVOICE_ID,ITEM_CODE,ITEM_DESCR,ITEM_CODE__NDC,ITEM_CODE__UPC,ITEM_STRENGTH,LOT_NO,EXP_DATE,LOT_SL,QUANTITY__ORD,QUANTITY,LOC_CODE,CONV__QTY_STDUOM,UNIT__STD,UNIT,QUANTITY__STDUOM,PACK_QTY,GROSS_WEIGHT,NET_WEIGHT,TARE_WEIGHT,CUST_PORD,PORD_DATE,SSCC_18)";
												    sql = sql + "values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
												    pstmt = conn.prepareStatement(sql);
												    pstmt.setString(1, tran_hist_id);
												    pstmt.setString(2, lineNo);
												    pstmt.setString(3, refSer);
												    pstmt.setString(4, refId);
												    pstmt.setString(5, invoiceId);
												    pstmt.setString(6, itemCode);
												    pstmt.setString(7, itemDescr);
												    pstmt.setString(8, itemCodeNDC);
												    pstmt.setString(9, itemCodeUPC);
												    pstmt.setString(10, itemStrength);
												    pstmt.setString(11, lotNo);
												    pstmt.setString(12, exp_date);
												    pstmt.setString(13, lotSl);
												    pstmt.setDouble(14, quantityOrd);
												    pstmt.setDouble(15, quantity);
												    pstmt.setString(16, locCode);
												    pstmt.setString(17, convQtyStduom);
												    pstmt.setString(18, unitSTD);
												    pstmt.setString(19, unit);
												    pstmt.setDouble(20, quantityStduom);
												    pstmt.setDouble(21, packQty);
												    pstmt.setDouble(22, grossWtDT);
												    pstmt.setDouble(23, netWtDT);
												    pstmt.setDouble(24, tareWtDT);
												    pstmt.setString(25, custProd);
												    pstmt.setString(26, prod_date);
												    pstmt.setString(27, sscc_18);
												 	detailsTHCnt = pstmt.executeUpdate();
												 	pstmt.close();
												 	System.out.println("detailsTHCnt !!!!!! "+detailsTHCnt);
													
											
												}
												rs.close();
												rs = null;
												pstmt.close();
												pstmt = null;
												System.out.println("For TH :detailsTHCnt !!!"+detailsTHCnt);
												System.out.println("For TH :headerTHCnt !!!"+detailsTHCnt);
												System.out.println("tran_hist_id :"+tran_hist_id);
												System.out.println("tranId :["+tranId+"]");
										if(detailsTHCnt > 0 && headerTHCnt > 0)
										{
											
											sql="update tnt_tran_info set tran_id__hist=? where tran_id =?";
											pstmt = conn.prepareStatement(sql);
											pstmt.setString(1, tran_hist_id);
											pstmt.setString(2, tranId);
											pstmt.executeUpdate();
											flag=true;
											//conn.commit();
											
										}
						          }//end of if where tran_hist_Id not blank
						 	
						     }//end of  if coding throuhg channel pattren 	
						}
				    	else
				    	{
				    		System.out.println("tranId and lineNo not generated !!");
				    	} 
				    }// end of for loop	 	
				 	
				 	if(detailsTICnt > 0 && headerTICnt > 0 && flag != false) 
				 	{
				 		conn.commit();
				 		out.println("success");
				 		System.out.println("==============SUCCESS===================");
				 	}
				 	else
				 	{
				 		conn.rollback();
				 		out.println("Failed");
				 		System.out.println("==============FAILED===================");
				 	}
				
					
    	   
       }
       catch (Exception e)
		{
        	 try 
        	 {
				conn.rollback();
			 } 
        	 catch (SQLException e1)
        	 {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			 } 
			e.printStackTrace();
			out.println("Failed");
			//errString = e.getMessage();
			//throw new ITMException(e);
		}
       finally
       {
    	   	try 
    	   	{
    			   if(conn != null)
				   conn.close();
    			   
    			   if(pstmt != null)
    				   pstmt.close();
    			   
			} catch (Exception e)
			{
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
    	   
       }
        	
	}//end of do method
		

	public Document parseString(String xmlString) throws RemoteException,ITMException
	{
		Document dom1 = null;
		try
		{
			//Changed by Monif on 2/23/2009 [For special char parsing issue].Start
			ibase.webitm.utility.GenericUtility genericUtility = new ibase.webitm.utility.GenericUtility();
			xmlString = genericUtility.setXmlDec( xmlString );			
			//Changed by Monif on 2/23/2009 [For special char parsing issue].End
			DocumentBuilderFactory dbf = DocumentBuilderFactory.newInstance();
			dbf.setNamespaceAware(true);
			dbf.setIgnoringComments(true);
			DocumentBuilder db = dbf.newDocumentBuilder();
			OutputStreamWriter errorWriter =   new OutputStreamWriter(System.err, CommonConstants.ENCODING); //$NON-NLS-1$
			//Changed by Monif on 6/3/2008 [TO support Character encoding].
			//ByteArrayInputStream baos = new ByteArrayInputStream(xmlString.getBytes());
			ByteArrayInputStream baos = new ByteArrayInputStream(xmlString.getBytes( CommonConstants.ENCODING ));
		
			dom1 = db.parse(baos);
		}
		catch(Exception e)
		{
			dom1 = null;
			System.out.println("Exception : [ValidatorEJB][parseString] :==>\n"+e.getMessage()); //$NON-NLS-1$
			e.printStackTrace();
			throw new ITMException(e);
		}
		return dom1;
	}
	

	private String checkNull(String str)
	{
		if(str == null)
		{
			return "";
		}
		else
		{
			return str ;
		}

	}
	
	
	public String generateTranId(String windowName ,String xmlValues ,Connection conn)throws ITMException
	{
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String selSql = "";
		String tranId = "";
		String tranSer = "";
		String keyString = "";
		String keyCol = "";
			

		java.sql.Date effDate = null;
		java.sql.Timestamp currDate = null;
		GenericUtility genericUtility = GenericUtility.getInstance();

		try
		{
			System.out.println("generating trans id !!!!!!!!!");
			SimpleDateFormat sdfAppl = new SimpleDateFormat(genericUtility.getApplDateFormat());
			currDate = new java.sql.Timestamp(System.currentTimeMillis());
			String currDateStr = sdfAppl.format(currDate);
			selSql = "SELECT KEY_STRING, TRAN_ID_COL, REF_SER FROM TRANSETUP WHERE TRAN_WINDOW = ? ";
			//System.out.println("selSql :"+selSql);
			pstmt = conn.prepareStatement(selSql);
			pstmt.setString( 1, windowName );
			rs = pstmt.executeQuery();
			if (rs.next())
			{
				keyString = rs.getString("KEY_STRING");
				keyCol = rs.getString("TRAN_ID_COL");
				tranSer = rs.getString("REF_SER");
			}

			rs.close();rs = null;
			pstmt.close();pstmt = null;
			System.out.println("keyString :"+keyString);
			System.out.println("keyCol :"+keyCol);
			System.out.println("tranSer :"+tranSer);
			
        	TransIDGenerator tg = new TransIDGenerator(xmlValues, "BASE", CommonConstants.DB_NAME);
			tranId = tg.generateTranSeqID(tranSer, keyCol, keyString, conn);
			System.out.println("tranId is :"+tranId);
		}
		catch (SQLException ex)
		{
			System.out.println("Exception ::" +selSql+ ex.getMessage() + ":");
			ex.printStackTrace();
			throw new ITMException(ex);
		}
		catch (Exception e)
		{
			System.out.println("Exception ::" + e.getMessage() + ":");
			e.printStackTrace();
			throw new ITMException(e);
		}
		finally
		{
			try
			{
				if (rs != null)
				{
					rs.close();
					rs = null;
				}
				if (pstmt != null)
				{
					pstmt.close();
					pstmt = null;
				}
			}

			catch(Exception e)
			{
			}
		}
		return tranId;
	}//generateTranTd()
	
	
	
		
}