package ibase.webitm.ejb.dis;

import ibase.system.config.ConnDriver;
import ibase.webitm.ejb.ValidatorEJB;
import ibase.webitm.utility.GenericUtility;
import ibase.webitm.utility.ITMException;

import java.rmi.RemoteException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.HashMap;
import java.util.Map;

import javax.ejb.Stateless;
import org.w3c.dom.Document;
import org.w3c.dom.Node;
import org.w3c.dom.NodeList;

@Stateless
public class ProofOfDelivery extends ValidatorEJB implements ProofOfDeliveryLocal,ProofOfDeliveryRemote
{
	String str,confirmedInv="";
	
	public String wfValData(String xmlString, String xmlString1, String xmlString2,String objContext, String editFlag, String xtraParams) throws RemoteException,ITMException
	{
		System.out.println("------------ wfvalData method called-----------------");
		System.out.println("xtraParams --->>>  [["+xtraParams+"  ]]");
		String errString = null;
		Document dom = null;
		Document dom1 = null;
		Document dom2 = null;
		try
		{
			dom = parseString(xmlString);
			dom1 = parseString(xmlString1);
			dom2 = parseString(xmlString2);
			errString = wfValData(dom,dom1,dom2,objContext,editFlag,xtraParams);
			
		}
		catch(Exception e)
		{
			System.out.println("Exception : ProofOfDelivery.java : wfValData(String xmlString) : ==>\n"+e.getMessage());
			throw new ITMException(e);
		}
		
		return errString;
	} //end of wfValData 
	
	
	public String wfValData(Document dom, Document dom1, Document dom2,String objContext, String editFlag, String xtraParams) throws RemoteException,ITMException
	{
		NodeList parentNodeList = null;
		NodeList childNodeList = null;
		Node parentNode = null;
		Node childNode = null;
		int ctr=0;
		String childNodeName = null;
		String errString = "",errorType="",loginSite="",status="",invoiceID="",sreturnTranID="",sql1="";
		Connection conn = null;
		PreparedStatement pstmt;
		ResultSet rs=null;
		int cnt=0;
		int currentFormNo=0;
		int childNodeListLength;
		ConnDriver connDriver = new ConnDriver();
		//ITMDBAccessEJB itmDBAccessEJB = new ITMDBAccessEJB();
		ArrayList <String> errList = new ArrayList<String>();
		ArrayList <String>errFields = new ArrayList <String> ();
		StringBuffer errStringXml = new StringBuffer("<?xml version=\"1.0\"?>\r\n<Root><Errors>"); 
		
		try
		{

			String userId=null,errCode=null;
			GenericUtility genericUtility = GenericUtility.getInstance();
			
			conn = connDriver.getConnectDB("DriverITM");
			
			userId = genericUtility.getValueFromXTRA_PARAMS(xtraParams,"loginCode");
			System.out.println("user ID form XtraParam : "+userId +"Edit Flag -->>: "+editFlag);
			
			if(objContext != null && objContext.trim().length()>0)
			{
				currentFormNo = Integer.parseInt(objContext);
			}
			switch(currentFormNo)
			{
				case 1 :
				parentNodeList = dom.getElementsByTagName("Detail1");
				parentNode = parentNodeList.item(0);
				childNodeList = parentNode.getChildNodes();
				childNodeListLength = childNodeList.getLength();
				
				for(ctr = 0; ctr < childNodeListLength; ctr++)
				{
					childNode = childNodeList.item(ctr);
					childNodeName = childNode.getNodeName();
					System.out.println("Child name --->> "+childNodeName);				
					
					if (childNodeName.equalsIgnoreCase("invoice_id"))
					{
						String invoiceConf="";	
						invoiceID = genericUtility.getColumnValue("invoice_id",dom);
						str=invoiceID;
						System.out.println("-----invoice id : "+invoiceID+" str--->"+str);
						if(invoiceID==null || invoiceID.trim().length() == 0)
						{
							errCode = "VTINVIDNN";
							errList.add( errCode );
							errFields.add( childNodeName.toLowerCase() );
						}
						else
						{
							cnt= getDBRowCount(conn,"invoice","invoice_id",invoiceID);
							int cnt1= getDBRowCount(conn,"spl_sales_por_hdr","invoice_id",invoiceID);
							invoiceConf=getNameOrDescrForCode(conn, "invoice", "confirmed", "invoice_id", invoiceID);
							System.out.println("invoice Confirmed Status--------->> : "+invoiceConf);
							String conf= getNameOrDescrForCode(conn,"spl_sales_por_hdr","confirmed","invoice_id",invoiceID);
							conf=conf==null ? "" : conf.trim();							
							if(invoiceConf.trim().equalsIgnoreCase("N"))
							{
								//If invoice Id not confirmed in invoice master then show error 
								errCode = "VTINVNCON"; 
								errList.add( errCode );
								errFields.add( childNodeName.toLowerCase());
							}
							status=getNameOrDescrForCode(conn, "spl_sales_por_hdr", "wf_status", "invoice_id", invoiceID);
							status=status==null ? "" : status.trim();
							System.out.println("Status for reject ---->>$$$$["+status+"]");
							
							
							if (!(editFlag.equals("E")))
							{
								if(!(status.equals("R")))
								{
									
									 if(cnt1 > 0 && (!isPartialyInvoiceChk(conn,invoiceID)))
									{
										//If POD for invoiceID already exist in spl_sales_por_hdr then show error
										errCode = "VTINVIDAC"; 
										errList.add( errCode );
										errFields.add( childNodeName.toLowerCase() );
									}	
									 else if(isPartialyInvoiceChkHdr(conn,invoiceID))
									{
										//system has unconfirmed POD transaction for this invoice.  
										System.out.println("--------Unconfirmed POD transaction--------");
										errCode = "VTINVDUNF"; 
										errList.add( errCode );
										errFields.add( childNodeName.toLowerCase() );
									}
								}								
							}		
						
							//------------------------------------------------------
							if(cnt == 0)
							{
								errCode = "VTINVIDNE"; 
								errList.add( errCode );
								errFields.add( childNodeName.toLowerCase() );
							}
						}
					}	
					if (childNodeName.equalsIgnoreCase("site_code"))
					{
						String siteCode="";
						int count=0;
						siteCode = genericUtility.getColumnValue("site_code",dom);
						siteCode=siteCode==null ? "" : siteCode.trim();
						if(siteCode.length()== 0){
							errCode = "VTSITECNE"; 
							errList.add( errCode );
							errFields.add( childNodeName.toLowerCase() );
						}else{
							count=getDBRowCount(conn,"site","site_code",siteCode);
							if(count==0){
							errCode = "VTSITENEX"; 
							errList.add( errCode );
							errFields.add( childNodeName.toLowerCase() );
							}
							
						}
					}
					if (childNodeName.equalsIgnoreCase("cust_code"))
					{
						String custCode="";
						int count=0;
						custCode = genericUtility.getColumnValue("cust_code",dom);
						custCode=custCode==null ? "" : custCode.trim();
						if(custCode.length()== 0){
							errCode = "VTCUSTCNE"; 
							errList.add( errCode );
							errFields.add( childNodeName.toLowerCase() );
						}else{
							count=getDBRowCount(conn,"customer","cust_code",custCode);
							if(count==0){
							errCode = "VTCUSTNEX"; 
							errList.add( errCode );
							errFields.add( childNodeName.toLowerCase() );
							}
							
						}
					}
					
					if (childNodeName.equalsIgnoreCase("wf_status"))
					{
						String wfStatus="";
						wfStatus = genericUtility.getColumnValue("wf_status",dom);
						wfStatus= wfStatus==null ?"":wfStatus.trim();
						if (wfStatus.equalsIgnoreCase("R"))
						{							
							errCode = "VTREJNOTA"; 
							errList.add( errCode );
							errFields.add( childNodeName.toLowerCase() );
						}
					
					}
					
				 } //end for loop 
				break;
				case 2 :
				parentNodeList = dom.getElementsByTagName("Detail2");
				parentNode = parentNodeList.item(0);
				childNodeList = parentNode.getChildNodes();
				childNodeListLength = childNodeList.getLength();
				for(ctr = 0; ctr < childNodeListLength; ctr++)
				{
					childNode = childNodeList.item(ctr);
					childNodeName = childNode.getNodeName();
					
					
					if(childNodeName.equalsIgnoreCase("quantity__resale"))
					{
						String qtyResale="",qtyInv="",invoiceId="",itemCodeSret="";
						double qtyResalD=0.0;
						qtyResale=genericUtility.getColumnValue("quantity__resale", dom);
						qtyInv=genericUtility.getColumnValue("quantity__inv", dom);
						itemCodeSret =genericUtility.getColumnValue("item_code", dom);
						itemCodeSret=itemCodeSret==null ? "" : itemCodeSret.trim();
						System.out.println("itemCodeSret--->>["+itemCodeSret+"]");
						
						try 
						{						
							qtyInv=qtyInv == null ? "0.0" : qtyInv;
							System.out.println("quantity__resale---->>"+qtyResale);
							qtyResale=checkSpaceNull(qtyResale); //qtyResale == null ? "0.0" : qtyResale;
							qtyResalD=Double.parseDouble(checkSpaceNull(qtyResale));
							System.out.println("qtyInv---->>"+qtyInv+" qtyResaled after parsing :"+qtyResalD);
							
							if (qtyResale == null  || qtyResale.trim().length() == 0 || qtyResalD < 0)
							{
								errCode = "VTPODQNN"; 
								errList.add( errCode );
								errFields.add( childNodeName.toLowerCase() );
							}
							else
							{	
								System.out.println("------IN ELSE-----------");	
								String tranID="";
								double quantyInvD=0.0,podInvD=0.0,totalInvQty=0.0;
								double qtyInvD=0.0,qtyResaleD=0.0;
								qtyInvD=Double.parseDouble(checkSpaceNull(qtyInv));
								qtyResaleD=Double.parseDouble(qtyResale);
								PreparedStatement pstmt1=null;
								ResultSet rs1=null;								
								System.out.println("invoice Id dom---->>"+str);
								sreturnTranID=getSalesReturnTranID(conn, "sreturn", "tran_id", "invoice_id", str);
								sreturnTranID=sreturnTranID==null ? "" : sreturnTranID.trim();
								System.out.println("sreturnTranID --------->>["+sreturnTranID+"]");
								if(sreturnTranID.length() > 0)
								{
									String sreturnItemCode=""; 
									double sreturnQuantity=0.0;
									sql1="select item_code,quantity from sreturndet where tran_id = ?";
									pstmt=conn.prepareStatement(sql1);
									pstmt.setString(1, sreturnTranID);
									rs=pstmt.executeQuery();
									while(rs.next())
									{
										sreturnItemCode=rs.getString(1);
										sreturnQuantity=rs.getDouble(2);
										System.out.println("sreturnItemCode -->["+sreturnItemCode+"] sreturnQuantity -->["+sreturnQuantity+"]");
										if(sreturnItemCode.trim().equalsIgnoreCase(itemCodeSret))
										{
											qtyInvD=qtyInvD - sreturnQuantity;
											//qtyResalD=qtyResalD+isTotalAmountPodQtyGrt(conn,str,qtyInvD);
											System.out.println("POD qty with new entry value---------->>"+qtyResalD);
											System.out.println("Actual quantity after sales return---->>["+qtyInvD+"]");
											if(isTotalAmountPodQtyGrt(conn,str,qtyInvD,qtyResalD,itemCodeSret)) //VTSLRETER
											{
												//Invoice quantity for item(s) is returned.Please check sales return.
												errCode = "VTSLRETER"; 
												errList.add( errCode );
												errFields.add( childNodeName.toLowerCase());
											}
										}
									} //end while 
											
								}								
									System.out.println("in isPartialyInvoiceChk condition------------ ");
									//sql1="select tran_id from spl_sales_por_hdr where invoice_id = ?";	
									//totalInvQty=isTotalAmountPodQtyGrt(conn,str,qtyInvD);			
									//totalInvQty = totalInvQty+ qtyResalD;
								System.out.println("qtyInvD---->> "+qtyInvD+"  qtyResaleD : "+qtyResaleD);
								System.out.println("Final totalInvQty ----->"+totalInvQty );
								if ( isTotalAmountPodQtyGrt(conn,str,qtyInvD,qtyResaleD,itemCodeSret))
								{
									System.out.println("the total qty condition true---------");
									errCode = "VTPODGRT"; 
									errList.add( errCode );
									errFields.add( childNodeName.toLowerCase());
								}
								if(qtyResaleD > qtyInvD)
								{
									//POD quantity can not be greater than invoice quantity
									errCode = "VTPODGRT"; 
									errList.add( errCode );
									errFields.add( childNodeName.toLowerCase());
								}
								
							}
						}
						catch(Exception e)
						{
							System.out.println("Exception : quantity__resale column (Detail) ");
							e.printStackTrace();
						}
						
					}	
					if(childNodeName.equalsIgnoreCase("rate__resale"))
					{
						String rateResale="";
						double rateResaleD=0.0;
						rateResale=genericUtility.getColumnValue("rate__resale", dom);
						rateResale=rateResale == null ? "0.0" : rateResale.trim();
						rateResaleD=Double.parseDouble(checkSpaceNull(rateResale));
						System.out.println("rateResaleD------->> "+rateResaleD);
						if (rateResale == null  || rateResale.length() == 0  || rateResaleD <= 0)
						{
							errCode = "VTRSALENN"; 
							errList.add( errCode );
							errFields.add( childNodeName.toLowerCase() );
						}
					}	
					if(childNodeName.equalsIgnoreCase("item_code"))
					{
						String itemCode="",regulatedPrice="",itemActive="";
						itemCode=genericUtility.getColumnValue("item_code", dom);	
						itemCode=itemCode==null ? "" :itemCode.trim();
						System.out.println("itemCode------->> "+itemCode);
						if(itemCode.length() > 0){
						regulatedPrice=getColumnDescr(conn,"regulated_price","item","item_code",itemCode);
						itemActive=getColumnDescr(conn,"active","item","item_code",itemCode);						
						itemActive=itemActive==null ? "" :itemActive.trim();
						regulatedPrice=regulatedPrice==null ? "" :regulatedPrice.trim();
							
						if(regulatedPrice.length() < 0){
						errCode = "VTREGPRCN"; //regulated price not define in item master
						errList.add( errCode );
						errFields.add( childNodeName.toLowerCase() );	
						}
						else if(itemActive.length() < 0){
						errCode = "VTITMCNAC"; //item code not active in item master
						errList.add( errCode );
						errFields.add( childNodeName.toLowerCase() );	
						}	
						}						
						
					}
					if(childNodeName.equalsIgnoreCase("bil_date"))
					{
						String billDate="",billNo="",sql="",invID="",custCodeBil="",orgDate1="";
						Timestamp orgDate=null;
						Date d1=null,d2=null;
						invID = genericUtility.getColumnValue("invoice_id",dom1);
						billDate=genericUtility.getColumnValue("bil_date", dom);
						billNo=genericUtility.getColumnValue("bill_no", dom);
						invID=invID==null ? "" :invID.trim();
						billDate=billDate==null ? "" :billDate.trim();
						billNo=billNo==null ? "" :billNo.trim();
						
						sql="select cust_code__bil,tran_date from invoice where invoice_id = ?";
						pstmt=conn.prepareStatement(sql);
						pstmt.setString(1, invID);
						rs=pstmt.executeQuery();
						if(rs.next())
						{
							custCodeBil=rs.getString(1);
							orgDate=rs.getTimestamp(2);
						}
						rs.close();
						rs=null;
						pstmt.close();
						pstmt=null;
						
						if(billDate.length() > 0 && orgDate !=null){
						SimpleDateFormat sdf = new SimpleDateFormat("dd/mm/yy");
						orgDate1=sdf.format(orgDate);
						d1=sdf.parse(orgDate1);
						d2=sdf.parse(billDate);
						long diffDate=Math.abs(d2.getTime() - d1.getTime());
						long diffDays = diffDate / (24 * 60 * 60 * 1000);
						System.out.println("Difference in days--------->>["+diffDays+"]");
						if(diffDays > 365)
						{
							errCode = "VTBLBDNRP"; //Bill no., bill date can not be repeated for this invoice
							errList.add( errCode );
							errFields.add( childNodeName.toLowerCase() );	
						}
						
						
						}						
						
					}
				
				}
				break;
											
			} //end switch
			
			int errListSize = errList.size();
			cnt =0;
			String errFldName = null;
			if ( errList != null && errListSize > 0 )
			{
				for (cnt = 0; cnt < errListSize; cnt++ )
				{
					errCode = (String)errList.get(cnt);
					errFldName = (String)errFields.get(cnt);
					System.out.println("errCode .........."+errCode);
					errString = getErrorString( errFldName, errCode, userId );
					errorType =  errorType( conn, errCode );
					if ( errString.length() > 0)
					{
						String bifurErrString = errString.substring( errString.indexOf("<Errors>") + 8,errString.indexOf("<trace>"));
						bifurErrString =bifurErrString+errString.substring( errString.indexOf("</trace>") + 8,errString.indexOf("</Errors>"));
						errStringXml.append(bifurErrString);
						System.out.println("errStringXml .........."+errStringXml);
						errString = "";
					}
					if ( errorType.equalsIgnoreCase("E"))
					{
						break;
					}
				}
				errList.clear();
				errList = null;
				errFields.clear();
				errFields = null;
				
				errStringXml.append("</Errors></Root>\r\n");
			}
			else
			{
				errStringXml = new StringBuffer( "" );
			}	
			
		} //end try
		catch (SQLException se) {
			System.out.println("SQLException ::"+se);
			se.printStackTrace();
            throw new ITMException(se);
		}
		catch(Exception e)
		{
			System.out.println("Exception ::"+e);
			e.printStackTrace();
            throw new ITMException(e);
		}
		finally
		{
			try
			{
				if(conn!=null)
				{
					conn.close();
					conn = null;
				}
				if(connDriver!=null){
					connDriver = null;
				}	
				if(rs !=null)
				{
					rs.close();
					rs=null;
				}		
				
			}
			catch(Exception d)
			{
			  d.printStackTrace();
			}
		}
		
		System.out.println("ErrString ::[ "+errStringXml.toString()+" ]");
		return errStringXml.toString();
		
	}
	
	public String itemChanged(String xmlString,String xmlString1,String xmlString2, String objContext, String currentColumn, String editFlag, String xtraParams) throws RemoteException,ITMException
	{
		System.out.println("------------------ itemChanged called------------------");
		Document dom = null;
		Document dom1 = null;
		Document dom2 = null;
		String valueXmlString = "";
		try
		{   
			dom = parseString(xmlString);
			dom1 = parseString(xmlString1);
			dom2 = parseString(xmlString2);
			valueXmlString = itemChanged(dom,dom1,dom2,objContext,currentColumn,editFlag,xtraParams);
		}
		catch(Exception e)
		{
			System.out.println("Exception : [ProofOFDelivery ][itemChanged(String,String)] :==>\n"+e.getMessage());
		}
		return valueXmlString;
	}
	
	public String itemChanged(Document dom, Document dom1,Document dom2, String objContext, String currentColumn, String editFlag, String xtraParams) throws RemoteException,ITMException
	{
		System.out.println("@@@@@@@ itemChanged called");
		StringBuffer valueXmlString = new StringBuffer();
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		GenericUtility genericUtility = GenericUtility.getInstance();
		ConnDriver connDriver = new ConnDriver();
		int currentFormNo =0;
		String columnValue = null;
		NodeList parentNodeList = null;
		NodeList childNodeList = null;
		Node parentNode = null;
		Node childNode = null;
		int ctr = 0;
		try
		{			
			conn = connDriver.getConnectDB("DriverITM");
			conn.setAutoCommit(false);
			connDriver=null;
			if(objContext != null && objContext.trim().length()>0)
			{
				currentFormNo = Integer.parseInt(objContext.trim());
			}
			valueXmlString = new StringBuffer("<?xml version=\"1.0\"?><Root><header><editFlag>");
			valueXmlString.append(editFlag).append("</editFlag></header>");
			System.out.println("-------- currentFormNo : "+currentFormNo);
			
			switch(currentFormNo)
			{	
				case 1 :
				valueXmlString.append("<Detail1>");				
				System.out.println("[" + currentColumn + "] ==> '" + columnValue + "'");
				System.out.println("editFlag =>" +editFlag);
				
				if( currentColumn.trim().equalsIgnoreCase( "itm_default" ) )
				{
					System.out.println("------------in itm_default--------->");
					String confirm="N",wfStatus="O";
					 Calendar currentDate = Calendar.getInstance();
					 SimpleDateFormat simpleDateFormatObj = new SimpleDateFormat(genericUtility.getApplDateFormat());
					 String tranDate = simpleDateFormatObj.format(currentDate.getTime());						 
					 valueXmlString.append("<tran_date>").append("<![CDATA[" + tranDate + "]]>").append("</tran_date>");
					 valueXmlString.append("<confirmed>").append("<![CDATA[" + confirm + "]]>").append("</confirmed>");
					 valueXmlString.append("<wf_status>").append("<![CDATA[" + wfStatus + "]]>").append("</wf_status>");
				}			
									
				if((currentColumn.trim().equalsIgnoreCase("invoice_id")))
				{
					String invoiceID ="",siteCode="",custCode="",sql="",siteDescr="",custName="",currencyCode="",prdCode="";
					double amount=0.0,exchRate=0.0;
					invoiceID = genericUtility.getColumnValue("invoice_id",dom);
					siteCode = genericUtility.getColumnValue("site_code",dom);
					custCode = genericUtility.getColumnValue("cust_code",dom);					
					invoiceID=invoiceID==null ? "" :invoiceID.trim();
					siteCode=siteCode==null ? "" :siteCode.trim();
					custCode=custCode==null ? "" :custCode.trim();
					System.out.println("invoiceID -----   :->> "+invoiceID);
					System.out.println("siteCode -----   :->> "+siteCode);
					System.out.println("custCode -----   :->> "+custCode);
	
					if(invoiceID.length()>0 )
					{						
						sql="select site_code,cust_code,net_amt,curr_code,exch_rate,prd_code from invoice where invoice_id = ?";
						pstmt=conn.prepareStatement(sql);
						pstmt.setString(1, invoiceID);
						rs=pstmt.executeQuery();
						if(rs.next())
						{
							if(siteCode.length() <= 0){
							siteCode=rs.getString(1);
							siteCode=siteCode==null ? "" :siteCode.trim();
							System.out.println("siteCode -----  rs :->> "+siteCode);
							}
							if(custCode.length() <= 0){
							custCode=rs.getString(2);
							custCode=custCode==null ? "" :custCode.trim();
							}
							amount=rs.getDouble(3);
							currencyCode=rs.getString(4);
							exchRate=rs.getDouble(5);	
							prdCode=rs.getString(6);
						}
						
						rs.close();pstmt.close();
						rs=null;pstmt=null;
						if(siteCode.length() > 0)
						{
							siteDescr=getNameOrDescrForCode(conn, "SITE", "DESCR", "SITE_CODE", siteCode);
						}
						
						if(custCode.length() > 0)
						{
							custName=getNameOrDescrForCode(conn, "customer", "cust_name", "cust_code", custCode);
						}
						System.out.println("period code--->>"+prdCode);
						valueXmlString.append("<site_code>").append("<![CDATA[" + siteCode + "]]>").append("</site_code>");
						valueXmlString.append("<cust_code>").append("<![CDATA[" + custCode + "]]>").append("</cust_code>");
						valueXmlString.append("<amount>").append("<![CDATA[" + amount + "]]>").append("</amount>");
						valueXmlString.append("<exch_rate>").append("<![CDATA[" + exchRate + "]]>").append("</exch_rate>");
						valueXmlString.append("<curr_code>").append("<![CDATA[" + currencyCode + "]]>").append("</curr_code>");
						valueXmlString.append("<site_descr>").append("<![CDATA[" + siteDescr + "]]>").append("</site_descr>");						
						valueXmlString.append("<cust_name>").append("<![CDATA[" + custName+ "]]>").append("</cust_name>");
					
						valueXmlString.append("<prd_code>").append("<![CDATA[" + prdCode+ "]]>").append("</prd_code>");
						System.out.println("siteCode : "+siteCode+" cust code : "+custCode+" amount : "+amount+"exchnage rate  : "+exchRate);					
					}
					else
					{
						System.out.println("------invoice id null-----");
						//valueXmlString.append("<project_descr>").append("").append("</project_descr>");
						//valueXmlString.append("<site_code>").append("").append("</site_code>");
						//valueXmlString.append("<cust_code>").append("").append("</cust_code>");
						valueXmlString.append("<amount>").append("").append("</amount>");
						valueXmlString.append("<exch_rate>").append("").append("</exch_rate>");
						valueXmlString.append("<curr_code>").append("").append("</curr_code>");
						//valueXmlString.append("<site_descr>").append("").append("</site_descr>");						
						//valueXmlString.append("<cust_name>").append("").append("</cust_name>");					
						valueXmlString.append("<prd_code>").append("").append("</prd_code>");											
						
					}
				}	
				
				else if (currentColumn.trim().equalsIgnoreCase("site_code"))
				{
					String siteCode="",siteDescr1="";
					siteCode = genericUtility.getColumnValue("site_code",dom);
					siteCode=siteCode==null ? "" :siteCode.trim();
					System.out.println("siteCode ----->>["+siteCode+"]");
					if(siteCode.length() > 0){
					siteDescr1=getNameOrDescrForCode(conn, "SITE", "DESCR", "SITE_CODE", siteCode);
					}
					valueXmlString.append("<site_descr>").append("<![CDATA[" + siteDescr1 + "]]>").append("</site_descr>");
					
				}
				else if (currentColumn.trim().equalsIgnoreCase("cust_code"))
				{
					String custCode="",custName1="";					
					custCode = genericUtility.getColumnValue("cust_code",dom);
					custCode=custCode==null ? "" :custCode.trim();
					System.out.println("custCode ----->>["+custCode+"]");
					if(custCode.length() > 0){
					custName1=getNameOrDescrForCode(conn, "customer", "cust_name", "cust_code", custCode);
					}
					valueXmlString.append("<cust_name>").append("<![CDATA[" + custName1+ "]]>").append("</cust_name>");
					
				}
				    valueXmlString.append("</Detail1>");
				  break;
				case 2:
					parentNodeList = dom.getElementsByTagName("Detail2");
					parentNode = parentNodeList.item(0);
					childNodeList = parentNode.getChildNodes();
					valueXmlString.append("<Detail2>");
					int childListLength = childNodeList.getLength();					
					/*if (currentColumn.trim().equals("quantity__resale"))
					{
						System.out.println("-------item chnage quantity__resale-------------");
						String rateResale="",rateInv="",quantityInv="",podQty="";
						double rateResaleD=0.0,rateInvoiceD=0.0,diffAmt=0.0,quantityInvD=0.0,podQtyD=0.0;
						double actualPrice=0.0,PODprice=0.0;
					
						rateInv=genericUtility.getColumnValue("rate__inv",dom);
						quantityInv=genericUtility.getColumnValue("quantity__inv",dom);
						
						rateResale=genericUtility.getColumnValue("rate__resale",dom);					
						podQty=genericUtility.getColumnValue("quantity__resale",dom);
						
						
						rateInvoiceD=Double.parseDouble(checkSpaceNull(rateInv));						
						quantityInvD=Double.parseDouble(checkSpaceNull(quantityInv));
						actualPrice= rateInvoiceD * quantityInvD;
						System.out.println("rateInvoiceD ["+rateInvoiceD+"] *  quantityInvD["+quantityInvD+"] = "+actualPrice);
						
						rateResaleD=Double.parseDouble(checkSpaceNull(rateResale));
						podQtyD=Double.parseDouble(checkSpaceNull(podQty));
						System.out.println("rateResaleD -->>: "+rateResaleD+" podQtyD : "+podQtyD);
						PODprice= rateResaleD * podQtyD;
						
						System.out.println("POD Amount ------>>"+PODprice);
						
						diffAmt= PODprice - actualPrice ;
						System.out.println("Diff Amount ------>>"+PODprice);
						valueXmlString.append("<amount>").append("<![CDATA["+diffAmt+"]]>").append("</amount>");	
						
					}
					
					if (currentColumn.trim().equals("rate__resale"))
					{
						System.out.println("-------item chnage rate__resale-------------");
						String rateResale="",rateInv="",quantityInv="",podQty="";
						double rateResaleD=0.0,rateInvoiceD=0.0,diffAmt=0.0,quantityInvD=0.0,podQtyD=0.0;
						double actualPrice=0.0,PODprice=0.0;
					
						rateInv=genericUtility.getColumnValue("rate__inv",dom);
						quantityInv=genericUtility.getColumnValue("quantity__inv",dom);
						
						rateResale=genericUtility.getColumnValue("rate__resale",dom);					
						podQty=genericUtility.getColumnValue("quantity__resale",dom);
						
						
						rateInvoiceD=Double.parseDouble(checkSpaceNull(rateInv));						
						quantityInvD=Double.parseDouble(checkSpaceNull(quantityInv));
						actualPrice= rateInvoiceD * quantityInvD;
						System.out.println("rateInvoiceD ["+rateInvoiceD+"] *  quantityInvD["+quantityInvD+"] = "+actualPrice);
						
						rateResaleD=Double.parseDouble(checkSpaceNull(rateResale));
						podQtyD=Double.parseDouble(checkSpaceNull(podQty));
						System.out.println("rateResaleD -->>: "+rateResaleD+" podQtyD : "+podQtyD);
						PODprice= rateResaleD * podQtyD;
						
						System.out.println("POD Amount ------>>"+PODprice);
						
						diffAmt= PODprice - actualPrice ;
						System.out.println("Diff Amount ------>>"+PODprice);
						valueXmlString.append("<amount>").append("<![CDATA["+diffAmt+"]]>").append("</amount>");						
					
					}*/
					if (currentColumn.trim().equals("bil_date"))
					{
						String sql="",invoiceID="",stkBillDate="",invoiceDate="";
						Date d1=null,d2=null;
						Timestamp invDateT=null;
						long diffDate=0;					
						invoiceID = genericUtility.getColumnValue("invoice_id",dom1);					
						stkBillDate=genericUtility.getColumnValue("bil_date",dom);
						invoiceID=invoiceID==null ? "" :invoiceID.trim(); 						
						stkBillDate=stkBillDate==null ? "" :stkBillDate.trim(); 
						System.out.println("Invoice Id in bill date item change ------>>["+invoiceID+"]");						
						System.out.println("Stockist bill date item change ------>>["+stkBillDate+"]");
						if (invoiceID.length() > 0 && stkBillDate.length() > 0){											 
						sql="select tran_date from invoice where invoice_id = ?";
						pstmt=conn.prepareStatement(sql);
						pstmt.setString(1, invoiceID);
						rs=pstmt.executeQuery();
						if(rs.next()){
						invDateT=rs.getTimestamp(1);
						}
						System.out.println("rs date --->>["+invDateT+"]");
						if(invDateT !=null){							
						SimpleDateFormat format = new SimpleDateFormat("dd/mm/yy");
						invoiceDate = format.format(invDateT);	
						System.out.println("tranDate ------>>["+invoiceDate+"]");
						d1=format.parse(invoiceDate);
						d2=format.parse(stkBillDate);						
						 
						diffDate=Math.abs(d2.getTime() - d1.getTime());
						long diffDays = diffDate / (24 * 60 * 60 * 1000);						
						System.out.print("Difference in days ------->>>>>>>>>>>["+diffDays+"]");	
						valueXmlString.append("<transit_day>").append("<![CDATA["+diffDays+"]]>").append("</transit_day>");								
						}						
					
						}						
						
					}
					valueXmlString.append("</Detail2>");
				}// end switch 
			valueXmlString.append("</Root>");			
			
			
		} //end try-------------------------------------------------------------
		catch(Exception e)
		{
			e.printStackTrace();
			System.out.println("Exception ::"+e.getMessage());
			throw new ITMException(e);
		}
		finally
		{
			try
			{
				if(conn!=null)
				{
					conn.close();
					conn = null;
				}
				if(rs != null)
				{
					rs.close();
					rs = null;
				}
				if(pstmt != null)
				{
					pstmt.close();
					pstmt = null;
				}				
			}
			catch(Exception d)
			{
			  d.printStackTrace();
			}			
		}
		
		return valueXmlString.toString();
	}
	private String checkSpaceNull(String str)
	{
		if(str == null || str.trim().length() == 0)
		{
			str="0.0";
		}
		return str;
	}
	
	private int getDBRowCount(Connection conn, String table_name, String whrCondCol, String whrCondVal)
	{
			int count=-1;
			
			if(conn!=null){
				
				ResultSet rs=null;
				PreparedStatement pstmt = null;
				
				String sql="select count(1) from "+table_name+" where "+whrCondCol+" = ?";
				System.out.println("SQL in getDBRowCount method : "+sql);
				try
				{
					pstmt = conn.prepareStatement(sql);
					pstmt.setString(1,whrCondVal);
					rs = pstmt.executeQuery();
					if(rs.next())
					{
						count = rs.getInt(1);
					}
				}
				catch(SQLException e)
				{
					System.out.println("SQL Exception In getDBRowCount method of ProofOfDelivery Class : "+e.getMessage());
					e.printStackTrace();
				}
				catch(Exception ex)
				{
					System.out.println("Exception In getDBRowCount method of ProofOfDelivery Class : "+ex.getMessage());
					ex.printStackTrace();
				}finally{
					
					try{
						
						if(pstmt!=null){
							pstmt.close();
							pstmt = null;
						}
						if(rs!=null){
							rs.close();
							rs = null;
						}
					}catch (SQLException se) {
						se.printStackTrace();
					}
				}
			}else{
				try {
					throw new SQLException("Connection passed to ProofOfDelivery.getDBRowCount() method is null");
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
				
		return count;
	}
	
	
	private boolean isTotalAmountPodQtyGrt(Connection conn,String invID,double actualInvQty,double currentPodQty,String itemCodeSret)
	{
		System.out.println("---in method isTotalAmountPodQtyGrt@@@@@@@ ------invoice ID :["+invID+"]");
		System.out.println("actualInvQty----->>"+actualInvQty+" currentPodQty----->>"+currentPodQty);
		Map<String,Double> itemMap=new HashMap<String,Double>();
		PreparedStatement pstmt2 = null,pstmt1=null,pstmt3=null ; 
		ResultSet rs2 = null,rs1=null,rs3=null;
		String sql1="",tranID="",confirmed="",itemCode="";
		int lineNo=0,count=0;	
		Double totalParsingQty=0.0;
		double qtyInv=0.0,podQty=0.0,totalPodQty=0.0,consumeQty=0.0,finalPodqty=0.0;
		boolean isItemCodeMatch=false;
		 sql1="select tran_id,confirmed from spl_sales_por_hdr where invoice_id = ? and confirmed = ? and wf_status <> 'R'";
		try
		{			
			pstmt2=conn.prepareStatement(sql1);
			pstmt2.setString(1, invID);
			pstmt2.setString(2, "Y");
			rs2 = pstmt2.executeQuery();
			while(rs2.next())
			{
				tranID=rs2.getString(1);
				confirmed=rs2.getString(2);
				tranID=tranID==null ? "" : tranID.trim();
				System.out.println("Confirmed POD---->["+confirmed+"]");			
					
				System.out.println("tran id counter ------->"+count);
				if(tranID.length() > 0 )
				{					
					sql1="";
					sql1="select line_no,item_code,quantity__inv,quantity__resale from spl_sales_por_det where tran_id = ?";
					pstmt1=conn.prepareStatement(sql1);
					pstmt1.setString(1, tranID);
					rs1=pstmt1.executeQuery();
					
					while(rs1.next())
					{
						lineNo=rs1.getInt(1);
						itemCode=rs1.getString(2);
						qtyInv=rs1.getDouble(3);
						podQty=rs1.getDouble(4);
						System.out.println("Line no @@@@ ---->"+lineNo+ " qtyInv-->>"+qtyInv+" podQty->>"+podQty+" itemCode =["+itemCode+"]");
						
					
						itemCode=itemCode==null ? "" : itemCode.trim();
						sql1="";
						/*sql1="select sum(quantity__resale) from spl_sales_por_det where item_code = ? and tran_id = ?";
						pstmt3=conn.prepareStatement(sql1);
						pstmt3.setString(1, itemCode);
						pstmt3.setString(2, tranID);
						rs3=pstmt3.executeQuery();
						if(rs3.next())
						{
							totalPodQty=rs3.getDouble(1);
						}*/
						
						if(itemMap !=null)
						{
							for (String key : itemMap.keySet()) 
							{
								System.out.println("(In map$$$) Item Code--->>"+key+" itemCode--->>"+itemMap.get(key));
								if(key.equalsIgnoreCase(itemCode))
								{
									System.out.println("item code match---------");
									//totalPodQty=itemMap.get(key);
									itemMap.put(itemCode, (itemMap.get(key)+ podQty));
									System.out.println("item code="+itemCode+ " TotalPODqty="+(itemMap.get(key)+podQty));
									isItemCodeMatch=true;								}
								
							}	
						}
							if(!(isItemCodeMatch))
								itemMap.put(itemCode,podQty);
							isItemCodeMatch=false;
						  
						
					}
					
					rs1.close();rs1=null;
					pstmt1.close();pstmt1=null;
				}
			}
			System.out.println("Item code in hashMap ---------->>");
			  for (String key : itemMap.keySet()) 
			  {
				  System.out.println("Item code -->> " + key);
			  }
			  System.out.println("PODqty FINAL in hashMap ---------->>");
			  for (Double key : itemMap.values()) 
			  {
				  System.out.println("f values  = " + key);		
				//  itemMap.get(itemCodeSret);
				  finalPodqty=  itemMap.get(itemCodeSret) + currentPodQty;
				  System.out.println("Final pod Quantity------>>"+finalPodqty+" Actual POD qty--->>"+actualInvQty);
				  if(finalPodqty > actualInvQty)
				  {
					  System.out.println("-------------------POD quantity is greater--------------------------------------------------------");
					  return true;
				  }
			  }
			
			  
			//System.out.println("TranID in getTotalAmountPodQty --->>["+tranID+"] TotalPODqty -->["+totalPodqty+"]");
			
			/*if (confirmed.equalsIgnoreCase("Y"))
				 return false;*/
			
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
		finally
		{
			try{
			if (rs2!=null)
			{
				rs2.close();
				rs2=null;
			}
			if(pstmt2!=null)
			{
				pstmt2.close();
				pstmt2=null;
			}
			}
			catch(Exception e)
			{
				System.out.println("Exception : In finally block ");
				e.printStackTrace();
			}
		}
		
		return false;
	}
	
	
	
	private boolean isPartialyInvoiceChk(Connection conn,String invID)
	{
		System.out.println("---in method isPartialyInvoiceChk ------invoice ID :["+invID+"]");
		PreparedStatement pstmt2 = null,pstmt1=null ; 
		ResultSet rs2 = null,rs1=null;
		String sql1="",tranID="",confirmed="";
		int lineNo=0,count=1;		
		
		double qtyInv=0.0,podQty=0.0,totalPodQty=0.0;
		
		 sql1="select tran_id,confirmed from spl_sales_por_hdr where invoice_id = ? and wf_status <> 'R'  order by confirmed";
		try
		{			
			pstmt2=conn.prepareStatement(sql1);
			pstmt2.setString(1, invID);
			rs2 = pstmt2.executeQuery();
			while(rs2.next())
			{
				tranID=rs2.getString(1);
				confirmed=rs2.getString(2);
				tranID=tranID==null ? "" : tranID.trim();
				confirmed=confirmed==null ? "" :confirmed.trim();
								
				
				System.out.println("tran id counter ------->"+count);
				if(tranID.length() > 0 && confirmed.equalsIgnoreCase("Y"))
				{
					count++;
					sql1="";
					sql1="select line_no,item_code,quantity__inv,quantity__resale from spl_sales_por_det where tran_id = ?";
					pstmt1=conn.prepareStatement(sql1);
					pstmt1.setString(1, tranID);
					rs1=pstmt1.executeQuery();
					while(rs1.next())
					{
						lineNo=rs1.getInt(1);
						qtyInv=rs1.getDouble(3);
						podQty=rs1.getDouble(4);
						System.out.println("Line no ---->"+lineNo+ " qtyInv-->>"+qtyInv+" podQty->>"+podQty);
						totalPodQty=totalPodQty + podQty;
						if( podQty < qtyInv)
						       return true;		
					}
					System.out.println("Total POD quantity --------->>"+totalPodQty);
					System.out.println("Confirmed POD---->["+confirmed+"]");
				
					rs1.close();rs1=null;
					pstmt1.close();pstmt1=null;
				}
				if (confirmed.equalsIgnoreCase("N"))
					return true;
			}
			
			System.out.println("TranID in isPartialyInvoiceChk --->>["+tranID+"] confirmed -->["+confirmed+"]");
			
			/*if (confirmed.equalsIgnoreCase("Y"))
				 return false;*/	
			
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
		finally
		{
			try{
			if (rs2!=null)
			{
				rs2.close();
				rs2=null;
			}
			if(pstmt2!=null)
			{
				pstmt2.close();
				pstmt2=null;
			}
			}
			catch(Exception e)
			{
				System.out.println("Exception : In finally block ");
				e.printStackTrace();
			}
		}
		
		return false;
	
	}
	
	private String errorType( Connection conn , String errorCode )
	{
		String msgType = "";
		PreparedStatement pstmt = null ; 
		ResultSet rs = null;
		try
		{			
			String  sql = " SELECT MSG_TYPE FROM MESSAGES WHERE MSG_NO =   ? ";
			
			pstmt = conn.prepareStatement( sql );			
			pstmt.setString(1, checkNull(errorCode));			
			rs = pstmt.executeQuery();
			while( rs.next() )
			{
				msgType = rs.getString("MSG_TYPE");
			}			
		}
		catch (Exception ex)
		{
			ex.printStackTrace();
		}		
		finally
		{
			try
			{
				if ( rs != null )
				{
					rs.close();
					rs = null;
				}
				if ( pstmt != null )
				{
					pstmt.close();
					pstmt = null;
				}
			}
			catch ( Exception e )
			{
				e.printStackTrace();
			}
		}		
		return msgType;
	}
	private String checkNull(String input)	
	{
		if (input == null)
		{
			input="";
		}
		return input;
	}
	
	
	private String getNameOrDescrForCode(Connection conn, String table_name, String descr_col_name,String whrCondCol, String whrCondVal)
	{
			String descr = null;
			
			if(conn!=null){
				
				ResultSet rs=null;
				PreparedStatement pstmt = null;
				
				String sql="SELECT "+descr_col_name+" FROM "+table_name+" WHERE "+whrCondCol+" = ?";
				
				System.out.println("SQL in getNameOrDescrForCode method : "+sql);
				try
				{
					pstmt = conn.prepareStatement(sql);
					pstmt.setString(1,whrCondVal);
					rs = pstmt.executeQuery();
					if(rs.next())
					{
						descr = rs.getString(descr_col_name);
					}
					
					descr=descr==null ?"" :descr;
				}
				catch(SQLException e)
				{
					System.out.println("SQL Exception In getNameOrDescrForCode method of ProofOfDelivery Class : "+e.getMessage());
					e.printStackTrace();
				}
				catch(Exception ex)
				{
					System.out.println("Exception In getNameOrDescrForCode method of ProofOfDelivery Class : "+ex.getMessage());
					ex.printStackTrace();
				}finally{
					
					try{
						
						if(pstmt!=null){
							pstmt.close();
							pstmt = null;
						}
						if(rs!=null){
							rs.close();
							rs = null;
						}
					}catch (SQLException se) {
						se.printStackTrace();
					}
				}
			}else{
				try {
					throw new SQLException("Connection passed to ProofOfDelivery.getNameOrDescrForCode() method is null");
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
				
		return descr;
	}
	
	
	
	
	private boolean isPartialyInvoiceChkHdr(Connection conn,String invID)
	{
		System.out.println("---in method isPartialyInvoiceChkHdr ------invoice ID :["+invID+"]");
		PreparedStatement pstmt2 = null,pstmt1=null ; 
		ResultSet rs2 = null,rs1=null;
		String sql1="",tranID="",confirmed="";
		int lineNo=0,count=1;
		invID=invID==null ?  ""  : invID.trim();
		
		if (invID.length() <= 0)
		{
			return false;
		}
		double qtyInv=0.0,podQty=0.0;
		
		 sql1="select tran_id,confirmed from spl_sales_por_hdr where invoice_id = ? and wf_status <> 'R' order by confirmed";
		try
		{			
			pstmt2=conn.prepareStatement(sql1);
			pstmt2.setString(1, invID);
			rs2 = pstmt2.executeQuery();
			while(rs2.next())
			{
				tranID=rs2.getString(1);
				confirmed=rs2.getString(2);
				tranID=tranID==null ? "" : tranID.trim();
				confirmed=confirmed==null ? "" :confirmed.trim();
				System.out.println("Confirmed POD---->["+confirmed+"]");
				
				if(confirmed.equalsIgnoreCase("N"))							
					return true;		
					
				System.out.println("tran id counter ------->"+count);				
			}
			
			System.out.println("TranID in isPartialyInvoiceChk --->>["+tranID+"] confirmed -->["+confirmed+"]");			
		
			
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
		finally
		{
			try{
			if (rs2!=null)
			{
				rs2.close();
				rs2=null;
			}
			if(pstmt2!=null)
			{
				pstmt2.close();
				pstmt2=null;
			}
			}
			catch(Exception e)
			{
				System.out.println("Exception : In finally block ");
				e.printStackTrace();
			}
		}
		
		return false;
	
		
	}
	private String getColumnDescr(Connection conn, String columnName ,String tableName, String columnName2, String value) 
	{

			PreparedStatement pstmt = null ;
			ResultSet rs = null ; 
			String sql = "";
			String findValue = "";
			try
			{			
				sql = "SELECT " + columnName + " from " + tableName + " where " + columnName2 +"= ?";
				pstmt = conn.prepareStatement(sql);
				pstmt.setString(1,value);
				rs = pstmt.executeQuery();
				if(rs.next())
				{					
					findValue = rs.getString(1);
				}
				rs.close();
				rs = null;
				pstmt.close();
				pstmt = null;	        
			}
			catch(Exception e)
			{
				System.out.println("Exception in getColumnDescr ");
				e.printStackTrace();
			}
			System.out.println("returning String from getColumnDescr " + findValue);
			return findValue;
		 
	}
	
	private String getSalesReturnTranID(Connection conn, String table_name, String tranID,String whrCondCol, String whrCondVal)
	{
			String descr = null;
			
			if(conn!=null){
				
				ResultSet rs=null;
				PreparedStatement pstmt = null;
				
				String sql="SELECT "+tranID+" FROM "+table_name+" WHERE "+whrCondCol+" = ? and confirmed = ?";
				//select tran_id from sreturn where invoice_id = '10DS086300' and confirmed ='Y';
				System.out.println("SQL in getSalesReturnTranID method : "+sql);
				try
				{
					pstmt = conn.prepareStatement(sql);
					pstmt.setString(1,whrCondVal);
					pstmt.setString(2,"Y");
					rs = pstmt.executeQuery();
					if(rs.next())
					{
						descr = rs.getString(tranID);
					}
					
					descr=descr==null ?"" :descr;
				}
				catch(SQLException e)
				{
					System.out.println("SQL Exception In getNameOrDescrForCode method of ProofOfDelivery Class : "+e.getMessage());
					e.printStackTrace();
				}
				catch(Exception ex)
				{
					System.out.println("Exception In getNameOrDescrForCode method of ProofOfDelivery Class : "+ex.getMessage());
					ex.printStackTrace();
				}finally{
					
					try{
						
						if(pstmt!=null){
							pstmt.close();
							pstmt = null;
						}
						if(rs!=null){
							rs.close();
							rs = null;
						}
					}catch (SQLException se) {
						se.printStackTrace();
					}
				}
			}else{
				try {
					throw new SQLException("Connection passed to ProofOfDelivery.getNameOrDescrForCode() method is null");
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
				
		return descr;
	}
	

}
