package ibase.webitm.ejb.dis;

import ibase.system.config.ConnDriver;
import ibase.webitm.ejb.ITMDBAccessEJB;
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="";
	boolean isError=false;
	double partialQty=0,actaulInvQty=0,remainQty=0;
	int lineNoTrace=0;
	GenericUtility genericUtility = GenericUtility.getInstance();
	public static ProofOfDelivery getInstance()
	{
		return new ProofOfDelivery();
	}
	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+"  ]]");
		System.out.println("xmlString1 --->>>  [["+xmlString1+"  ]]");
		System.out.println("xmlString2 --->>>  [["+xmlString2+"  ]]");
		System.out.println("editFlag --->>>  [["+editFlag+"  ]]");
		
		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
	{
		isError=false;
		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="",userId="";
		String errCode="";
		Connection conn = null;
		PreparedStatement pstmt;		
		ResultSet rs=null;
		int cnt=0;
		int currentFormNo=0,miscCount=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>"); 
		 ITMDBAccessEJB itmDBAccessEJB = new ITMDBAccessEJB();
		try
		{					
			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 reject2 ---->>$$$$["+status+"]");
							 // check if selected invoice id already generated debit note							
							if(isRecordPresentInMiscDrCrRcp(conn,invoiceID))
							{
								errCode = "VTPODARI"; //Proof of Delivery already received for this invoice ID
								errList.add( errCode );
								errFields.add( childNodeName.toLowerCase() );
							}
							//end check
							
							if (!(editFlag.equals("E")))
							{
								if(!(status.equals("R")))
								{
									
									 if(cnt1 > 0 && (!isPartialyInvoiceChk(conn,invoiceID,"POD")))
									{
										//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="",invId="",invSite="";
						int count=0;
						siteCode = genericUtility.getColumnValue("site_code",dom);
						invId = genericUtility.getColumnValue("invoice_id",dom);
						siteCode=siteCode==null ? "" : siteCode.trim();
						invId=invId==null ? "" : invId.trim();
						if(siteCode.length()== 0){
							errCode = "VTSITECNE"; 
							errList.add( errCode );
							errFields.add( childNodeName.toLowerCase() );
						}
						else{
							System.out.println("----in else part of site_code-----------");
							count=getDBRowCount(conn,"site","site_code",siteCode);
							if(count==0){
							errCode = "VTSITENEX"; 
							errList.add( errCode );
							errFields.add( childNodeName.toLowerCase() );
							}
							//check if entered site code not in invoice 
							invSite=getColumnDescr(conn,"site_code","invoice","invoice_id",invId);
							invSite=invSite==null ? "" : invSite.trim();
							System.out.println("Site_code ----->>["+siteCode+"]");
							System.out.println("Site_code invID TRUE----->>["+invSite+"]");
							if(!(invSite.equalsIgnoreCase(siteCode)))
							{
								errCode = "VTSITENEI"; //site code not match in invoice;
								errList.add( errCode );
								errFields.add( childNodeName.toLowerCase() );
							}					
							
						}
					}
					if (childNodeName.equalsIgnoreCase("cust_code"))
					{
						String custCode="",invId="",custCodeInv="";
						int count=0;
						custCode = genericUtility.getColumnValue("cust_code",dom);
						invId = genericUtility.getColumnValue("invoice_id",dom);
						custCode=custCode==null ? "" : custCode.trim();
						invId=invId==null ? "" : invId.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() );
							}
							//check if entered customer code not in invoice 
							custCodeInv=getColumnDescr(conn,"cust_code","invoice","invoice_id",invId);
							custCodeInv=custCodeInv==null ? "" : custCodeInv.trim();
							System.out.println("cust_code invID----->>["+custCodeInv+"]");
							if(!(custCodeInv.equalsIgnoreCase(custCode)))
							{
								errCode = "VTCUSTNEI"; //Customer code not match in invoice;
								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 || itemActive.equalsIgnoreCase("N")){
						errCode = "VTITMCNAC"; //item code not active in item master
						errList.add( errCode );
						errFields.add( childNodeName.toLowerCase() );	
						}	
						}						
						
					}
					if(childNodeName.equalsIgnoreCase("aprv_rate"))
					{
						String apprvRate="",rateResale="";
						double aprvRateD=0,rateResaleD=0;
						apprvRate=genericUtility.getColumnValue("aprv_rate", dom);
						rateResale=genericUtility.getColumnValue("rate__resale", dom);
						apprvRate=apprvRate==null ? "0" :apprvRate.trim();
						rateResale=rateResale==null ? "0" :rateResale.trim();
						
						aprvRateD=Double.parseDouble(apprvRate);
						rateResaleD=Double.parseDouble(rateResale);
						System.out.println("Approve rate123---->>>["+aprvRateD+"]");
						System.out.println("rateResaleD---->>>["+rateResaleD+"]");
						
						/*if(aprvRateD > rateResaleD)
						{
							errCode = "VTAPRNGRR"; // approved rate should not be greater than End sale rate.  
							errList.add( errCode );
							errFields.add( childNodeName.toLowerCase() );
						}*/
						
						if(aprvRateD <= 0){
							errCode = "VTAPRRIDN"; //Approved rate not available.Please check  
							errList.add( errCode );//regulated rate in item master or var name in disparm
							errFields.add( childNodeName.toLowerCase() );		
						}
						
					}
					if(childNodeName.equalsIgnoreCase("bill_no"))
					{
						String billNo="";
						billNo=genericUtility.getColumnValue("bill_no", dom);
						billNo=billNo==null ? "" :billNo.trim();
						System.out.println("Bill no--------->>["+billNo+"]");
						if(billNo.length() == 0)
						{
							errCode = "VTBILNNN"; 
							errList.add( errCode );
							errFields.add( childNodeName.toLowerCase() );
						}
					}
					if(childNodeName.equalsIgnoreCase("remarks"))
					{
						String remarks="";
						remarks=checkNull(genericUtility.getColumnValue("remarks", dom));
						System.out.println("remarks-------->>["+remarks+"]");
						if(remarks.length()>100){
							errCode = "VTRMKSG";  //Remarks length exceed than specified.
							errList.add( errCode );
							errFields.add( childNodeName.toLowerCase() );	
						}
						
						
					}
					if(childNodeName.equalsIgnoreCase("bil_date"))
					{
						String billDate="",billNo="",sql="",invID="",custCodeCurr="",invoiceDate="",tranId="";
						boolean isDateAft=false;
						Timestamp invDateT=null;
						int diffDays=0;						
						invID = genericUtility.getColumnValue("invoice_id",dom1);
						custCodeCurr = genericUtility.getColumnValue("cust_code",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();
						custCodeCurr=custCodeCurr==null ? "" :custCodeCurr.trim();
						
						System.out.println("customer Code Current--------->>["+custCodeCurr+"]");
						if(billDate.length() == 0)
						{
							errCode = "VTBILDNN"; 
							errList.add( errCode );
							errFields.add( childNodeName.toLowerCase() );
						}
						else
						{						
							if(invID.length() > 0 && billDate.length() > 0){
							sql="select conf_date from invoice where invoice_id = ?";
							pstmt=conn.prepareStatement(sql);
							pstmt.setString(1, invID);
							rs=pstmt.executeQuery();
							if(rs.next()){
							invDateT=rs.getTimestamp(1);
							}
							rs.close();
							rs=null;
							pstmt.close();
							pstmt=null;				
							
							if(invDateT!=null){
								
								SimpleDateFormat format = new SimpleDateFormat(genericUtility.getApplDateFormat());
								invoiceDate = format.format(invDateT);									
								
								System.out.print("stkBillDate121------->>>>>>>>>>>["+billDate+"]");
								System.out.print("invoice confDate ------->>>>>>>>>>>["+invoiceDate+"]");
								
								sql="select  to_date( ?,'dd-mm-yyyy') -  to_date( ?,'dd-mm-yyyy') from dual";
								pstmt=conn.prepareStatement(sql);
								pstmt.setString(1, billDate);
								pstmt.setString(2, invoiceDate);
								rs=pstmt.executeQuery();
								if(rs.next())
								{
									 diffDays=rs.getInt(1);							
								}
								rs.close();
								rs=null;
								pstmt.close();
								pstmt=null;								
								
								System.out.println("Difference in days for validation--->>["+diffDays+"]");
								editFlag=editFlag==null ? "" : editFlag.trim();
								String currTranId = genericUtility.getColumnValue("tran_id",dom1);
								currTranId=currTranId==null ? "" : currTranId.trim();
								System.out.println("currTranId--->>["+currTranId+"]");
								System.out.println("Edit flag133BD--->>["+editFlag+"]");
								
								if(isBillNoRepeatedInYear(conn,billNo,billDate,custCodeCurr,currTranId))
								{
									System.out.println("------bill and stockist and year matched-----------");
									errCode = "VTBLBDNRP"; 
									errList.add( errCode );
									errFields.add( childNodeName.toLowerCase() );
								}
								
								if(diffDays < 0)
								{
									errCode = "VTINDLSTD"; 
									errList.add( errCode );
									errFields.add( childNodeName.toLowerCase() );	
									
								}
								
							}							
							}
					} //end else					
						
					}
				
				}
				break;
											
			} //end switch
			
			int errListSize = errList.size();
			cnt =0;
			String errFldName = null;
			if ( errList != null && errListSize > 0 )
			{
				for (cnt = 0; cnt < errListSize; cnt++ )
				{
					errCode = errList.get(cnt);
					errFldName = 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;
				}
				if(isError)
				{
					return itmDBAccessEJB.getErrorString("", "VTTNSVD", "", "", conn);
				}
				
			}
			catch(Exception d)
			{
			  d.printStackTrace();
			}
		}
		
		System.out.println("ErrString ::[ "+errStringXml.toString()+" ]");
		return errStringXml.toString();
		
	}
	
	private boolean isBillNoRepeatedInYear(Connection conn,String billNoL,String stockistDateL,String currCustCode,String currTranIdL)
	{
		System.out.println("--------in isBillNoRepeated method-------");
		billNoL=billNoL==null ? "" :billNoL.trim();
		System.out.println("BollNoL-------->>"+billNoL);
		ResultSet rs=null,rs1=null;
		PreparedStatement pstmt=null,pstmt1=null;
		int currStkYear=0,actStkYear=0;
		ArrayList <String> tranIDList=new ArrayList<String>();
		String sql="",sql1="",billNo="",custCode="",tranId="";
		Timestamp stockistDate=null;
		
		try{
		SimpleDateFormat sdf = new SimpleDateFormat((genericUtility.getApplDateFormat()));
		java.util.Date date = sdf.parse(stockistDateL); 
		java.sql.Timestamp stkTimest = new java.sql.Timestamp(date.getTime()); 
		System.out.println("Stockist Date current---->>"+stkTimest);
		SimpleDateFormat sdf1 = new SimpleDateFormat("yyyy");
		String CurrYear=sdf1.format(date);
		System.out.println("Current stk Year------>>["+CurrYear+"]");
		currStkYear=Integer.parseInt(CurrYear);
		
		sql="select tran_id,cust_code from spl_sales_por_hdr";
		pstmt=conn.prepareStatement(sql);			
		rs=pstmt.executeQuery();
		while(rs.next())
		{
			tranId=rs.getString(1);
			tranId=tranId==null ? "" :tranId.trim();
			tranIDList.add(tranId);
		}
		rs.close();
		rs=null;
        pstmt.close();
        pstmt=null;
		System.out.println("Tran ID--->>["+tranIDList+"]");
		if(tranIDList.contains(currTranIdL))
		{
			tranIDList.remove(currTranIdL);
		}
		System.out.println("Tran ID after removeing--->>["+tranIDList+"]");
		for(String tranIdL : tranIDList){
		sql="select tran_id,cust_code from spl_sales_por_hdr where tran_id = ?";
		pstmt=conn.prepareStatement(sql);	
		pstmt.setString(1,tranIdL);
		rs=pstmt.executeQuery();
		while(rs.next()){
		tranId=rs.getString(1);	
		custCode=rs.getString(2);
		
		tranId=tranId==null ?"" :tranId.trim();		
		custCode=custCode==null ?"" :custCode.trim();		
		sql1="select bill_no,bil_date from spl_sales_por_det where tran_id = ?";
		pstmt1=conn.prepareStatement(sql1);	
		pstmt1.setString(1, tranId);
		rs1=pstmt1.executeQuery();
		while (rs1.next()){
			billNo=rs1.getString(1);
			stockistDate=rs1.getTimestamp(2);
			billNo=billNo==null ?"" :billNo.trim();			
			if(stockistDate!=null){	
			if(custCode.equalsIgnoreCase(currCustCode)){
				System.out.println("currCustCode Condition true--->>["+billNo+"]["+currCustCode+"]");
				if(billNo.equalsIgnoreCase(billNoL)){
					System.out.println("billNo Condition true--->>333["+billNo+"]");
					String year1=sdf1.format(stockistDate);
					System.out.println("stkDate Sting-------->>["+year1+"]");
					actStkYear=Integer.parseInt(year1);
					if(currStkYear == actStkYear){
						System.out.println("$$$$---Return true from isBillNoRepeatedInYear method--%%%%");					
						return true;
					}
					}					
				}}	
							
			}
		rs1.close();
		rs1=null;
		pstmt1.close();
		pstmt1=null;

			
		}
		}
		if(rs!=null){
		rs.close();
		rs=null;
		}
		if(pstmt!=null){
		pstmt.close();
		pstmt=null;
		}
	
		}
		catch(Exception e)
		{
			System.out.println("--------Exception : in isBillNoRepeated method----------");
			isError=true;
			e.printStackTrace();
		}
		return false;
		
	}
	
		
		
		
		protected 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();
						isError=true;
					}
				}
					
			return count;
		}
		
	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
	{
		isError=false;
		System.out.println("@@@@@@@ itemChanged called");
		StringBuffer valueXmlString = new StringBuffer();
		Connection conn = null;
		PreparedStatement pstmt = null,pstmt1=null;
		ResultSet rs = null,rs1=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",loginSite="",siteDescr1="";
					 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>");
					 
					 loginSite = getValueFromXTRA_PARAMS(xtraParams, "loginSiteCode");					
					 loginSite=loginSite==null ? "" :loginSite.trim();
						System.out.println("loginSite ----->>["+loginSite+"]");
						if(loginSite.length() > 0){
						siteDescr1=getNameOrDescrForCode(conn, "SITE", "DESCR", "SITE_CODE", loginSite);
						}
						valueXmlString.append("<site_code>").append("<![CDATA[" + loginSite + "]]>").append("</site_code>");
						valueXmlString.append("<site_descr>").append("<![CDATA[" + siteDescr1 + "]]>").append("</site_descr>");
				}			
									
				if((currentColumn.trim().equalsIgnoreCase("invoice_id")))
				{
					String invoiceID ="",siteCode="",custCode="",sql="",siteDescr="",custName="";
					//double amount=0.0,exchRate=0.0;
					Map<String,String> invDetailsMap=null;
					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 )
					{						
						invDetailsMap= getDetailsFromInvoice(conn,invoiceID);
						System.out.println("Map in invoice---->>["+invDetailsMap+"]");
						
						if(invDetailsMap !=null)
						{
						if(siteCode.length() <= 0){
						siteCode=invDetailsMap.get("site_code").toString();
						siteCode=siteCode==null ? "" :siteCode.trim();
						System.out.println("siteCode -----  map :->> "+siteCode);
						}
						if(custCode.length() <= 0){
							custCode=invDetailsMap.get("cust_code").toString();
							custCode=custCode==null ? "" :custCode.trim();
							System.out.println("custCode -----  map :->> "+custCode);
						}						
						
						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--->>"+invDetailsMap.get("prd_code"));
						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[" + invDetailsMap.get("amount") + "]]>").append("</amount>");
						valueXmlString.append("<exch_rate>").append("<![CDATA[" + invDetailsMap.get("exch_rate") + "]]>").append("</exch_rate>");
						valueXmlString.append("<curr_code>").append("<![CDATA[" + checkNull(invDetailsMap.get("curr_code") + "]]>")).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[" + checkNull(invDetailsMap.get("prd_code"))+ "]]>").append("</prd_code>");
						}					
					}
					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("itm_default"))
					{
						String invID="";
						System.out.println("itm_default item chnage called details 2................");
						invID = checkNull(genericUtility.getColumnValue("invoice_id",dom1));						
						valueXmlString.append("<inv_id>").append("<![CDATA["+invID+"]]>").append("</inv_id>");					
						
					}
					if (currentColumn.trim().equals("line_no__trace"))
					{
						String lineNoTrace="";
						String despachID="",approveRate="";
						String sql1="",invoiceID1="",lotNo="",lotSl="",itemSerPorm="",itemCode="",itemCodeDesc="",locCode="";
						int invLineNo=0;
						double rate=0.0,quantity=0.0,discount=0.0,pendQty=0;
						HashMap<String,String> domDetail=new HashMap<String,String>();
						System.out.println("line_no__trace item change........");
						lineNoTrace=checkNull(genericUtility.getColumnValue("line_no__trace",dom));
						invoiceID1 = checkNull(genericUtility.getColumnValue("invoice_id",dom1));
						String id1=checkNull(genericUtility.getColumnValue("inv_id",dom));
						System.out.println("line no trace----->["+lineNoTrace+"]");
						System.out.println("invoiceID----->["+invoiceID1+"]");
						System.out.println("id1----->["+id1+"]");
						
						if(lineNoTrace.length() > 0 && invoiceID1.length() > 0){
						sql1="select rate,quantity,lot_no,lot_sl,item_ser__prom,item_code,inv_line_no,desp_id,discount " +
								"from invoice_trace where invoice_id = ? and inv_line_no = ?";
						pstmt=conn.prepareStatement(sql1);
						pstmt.setString(1, invoiceID1);
						pstmt.setString(2, lineNoTrace);
						rs=pstmt.executeQuery();						
						if(rs.next()){							
						rate=rs.getDouble(1);
						quantity=rs.getDouble(2);
						lotNo=checkNull(rs.getString(3));
						lotSl=checkNull(rs.getString(4));
						itemSerPorm=checkNull(rs.getString(5));
						itemCode=checkNull(rs.getString(6));
						invLineNo=rs.getInt(7);
						despachID=checkNull(rs.getString(8));							
						discount=rs.getDouble(9);		
					}
						if(rs!=null){
						rs.close();
						rs=null;
						}
						if(pstmt!=null){
						pstmt.close();
						pstmt=null;
						}
						System.out.println("despachID ------>> "+despachID);
						System.out.println("discount ------>> "+discount);									
						if(discount > 0){
							rate=rate - (rate * discount/100);
							System.out.println("New rate after discount------>> "+rate+"]");
						}
						valueXmlString.append("<rate__inv>").append("<![CDATA["+rate+"]]>").append("</rate__inv>");
						approveRate=ProofOfDeliveryDefault.getInstance().getApprovedRate(conn,itemCode,rate);
						System.out.println("approveRate final ------>> "+approveRate);							
						valueXmlString.append("<aprv_rate>").append("<![CDATA["+approveRate+"]]>").append("</aprv_rate>");
						valueXmlString.append("<quantity__inv>").append("<![CDATA["+quantity+"]]>").append("</quantity__inv>");
							
						valueXmlString.append("<lot_no>").append("<![CDATA["+lotNo+"]]>").append("</lot_no>");
						valueXmlString.append("<lot_sl>").append("<![CDATA["+lotSl+"]]>").append("</lot_sl>");
						valueXmlString.append("<item_ser__prom>").append("<![CDATA["+itemSerPorm+"]]>").append("</item_ser__prom>");
						valueXmlString.append("<item_code>").append("<![CDATA["+itemCode+"]]>").append("</item_code>");
						itemCodeDesc=getNameOrDescrForCode(conn, "item", "descr", "item_code", itemCode);
						System.out.println("---------EXECUTE query12--------");
						sql1="select loc_code from despatchdet where desp_id = ? and item_code = ?";
						pstmt1 = conn.prepareStatement(sql1);
						pstmt1.setString(1,despachID);
						pstmt1.setString(2,itemCode);
						rs1 = pstmt1.executeQuery();
						System.out.println("-----query executed12345-----");
						if(rs1.next())
						{
							locCode = checkNull(rs1.getString(1)); 
						}		
						if(rs1!=null){
						rs1.close();
						rs1=null;
						}
						if(pstmt1!=null){
						pstmt1.close();
						pstmt1=null;
						}
						System.out.println("Location code -------->> : "+locCode);							
						
						valueXmlString.append("<descr>").append("<![CDATA["+itemCodeDesc+"]]>").append("</descr>");
						valueXmlString.append("<loc_code>").append("<![CDATA["+locCode+"]]>").append("</loc_code>");
						
						
						domDetail.put("quantity__inv", String.valueOf(quantity));
						domDetail.put("lot_no", lotNo==null ? null :lotNo.trim());
						domDetail.put("lot_sl", lotSl==null ? null :lotSl.trim());
						domDetail.put("invoice_id", invoiceID1);						
						domDetail.put("line_no__trace", String.valueOf(invLineNo));
						domDetail.put("item_code", itemCode==null ? "" :itemCode.trim());
						domDetail.put("loc_code", locCode);							
						pendQty=(ProofOfDeliveryDefault.getInstance()).getPendingQty(conn, domDetail); 
						System.out.println("Pending quantity----->>["+pendQty+"]");
						valueXmlString.append("<pend_qty>").append("<![CDATA["+pendQty+"]]>").append("</pend_qty>");
						valueXmlString.append("<pend_temp>").append("<![CDATA["+pendQty+"]]>").append("</pend_temp>");
					
						}
						
					} //end line no trace
					
					if (currentColumn.trim().equals("quantity__resale"))
					{
						System.out.println("-------item chnage quantity__resale-------------");						
						String rateResale="",podQty="",aprvRate="",invQtyS="",pendQtyS="",pendTempS="";
						double rateResaleD=0.0,podQtyD=0.0,debitNoteAmt=0,aprvRateD=0,invQtyD=0,
								pendQtyD=0,tempQty=0,pendTempD=0; 
					
						
						aprvRate=genericUtility.getColumnValue("aprv_rate",dom);						
						rateResale=genericUtility.getColumnValue("rate__resale",dom);					
						podQty=genericUtility.getColumnValue("quantity__resale",dom);							
						invQtyS=genericUtility.getColumnValue("quantity__inv",dom);
						pendQtyS=genericUtility.getColumnValue("pend_qty",dom);
						
						pendTempS=genericUtility.getColumnValue("pend_temp",dom);
						
						
						rateResaleD=Double.parseDouble(checkSpaceNull(rateResale));
						podQtyD=Double.parseDouble(checkSpaceNull(podQty));
						aprvRateD=Double.parseDouble(checkSpaceNull(aprvRate));
						invQtyD=Double.parseDouble(checkSpaceNull(invQtyS));					
						pendQtyD=Double.parseDouble(checkSpaceNull(pendQtyS));
						
						pendTempD=Double.parseDouble(checkSpaceNull(pendTempS));
						System.out.println("aprvRate ------>>"+aprvRateD);
						System.out.println("rateResaleD ------>>"+rateResaleD);
						
						System.out.println("Invoice Qty ------>>"+invQtyD);	
						System.out.println("qty resale(POD) ------>>"+podQtyD);
						System.out.println("pending Qty------>>"+pendQtyD);
						System.out.println("qty pend_temp ------>>"+pendTempD);
						
						/*tempQty =invQtyD - pendQtyD;// 200 - 80 =120 // 0
						tempQty=tempQty + podQtyD;//120 + 30 = 150//120
						
						
						pendQtyD=invQtyD - tempQty; //200 -150 =50 */
						
						if(pendQtyD > 0){
						System.out.println("Pending qty greater than zero..........");
						tempQty= invQtyD - pendTempD; //200 -80 =120
						tempQty=tempQty + podQtyD; //120 +30 =150
						
						pendQtyD=invQtyD - tempQty;  // 200 -150 =50
						}else{
							System.out.println("Pending qty less than zero..........");
							pendQtyD=invQtyD - podQtyD;  // 200 -150 =50
						}
								
					System.out.println("pendQty ------>>"+pendQtyD);
						
						if(rateResaleD > 0.0 && podQtyD > 0.0)
						{							
							debitNoteAmt=(rateResaleD - aprvRateD) * podQtyD;							
						}						  
						valueXmlString.append("<pend_qty>").append("<![CDATA["+pendQtyD+"]]>").append("</pend_qty>");
						System.out.println("debitNoteAmt ------>>"+debitNoteAmt);
						if(debitNoteAmt > 0)
						   valueXmlString.append("<debit_note_amt>").append("<![CDATA["+debitNoteAmt+"]]>").append("</debit_note_amt>");
						else
							valueXmlString.append("<debit_note_amt>").append("<![CDATA[]]>").append("</debit_note_amt>");
						
					}
					
					if (currentColumn.trim().equals("rate__resale"))
					{
						System.out.println("-------item chnage rate__resale-------------");
						String rateResale="",podQty="",aprvRate="";
						double rateResaleD=0.0,rateInvoiceD=0.0,podQtyD=0.0;
						double debitNoteAmt=0,aprvRateD=0;
					
						
						aprvRate=genericUtility.getColumnValue("aprv_rate",dom);
						
						rateResale=genericUtility.getColumnValue("rate__resale",dom);					
						podQty=genericUtility.getColumnValue("quantity__resale",dom);					
						
						rateResaleD=Double.parseDouble(checkSpaceNull(rateResale));
						podQtyD=Double.parseDouble(checkSpaceNull(podQty));
						aprvRateD=Double.parseDouble(checkSpaceNull(aprvRate));
						
						System.out.println("aprvRate  rate__resale------>>"+aprvRate);
						System.out.println("rateResaleD ------>>"+rateResaleD);
						System.out.println("qty resale ------>>"+podQtyD);
						
						if(rateResaleD > 0.0 && podQtyD > 0.0)
						{
							System.out.println("------Inside condition 12121----------");
							debitNoteAmt=(rateResaleD - aprvRateD) * podQtyD;
							System.out.println("debitNoteAmt asasdd------>>"+debitNoteAmt);
						}						  
						System.out.println("debitNoteAmt ------>>"+debitNoteAmt);
						if(debitNoteAmt > 0)
						   valueXmlString.append("<debit_note_amt>").append("<![CDATA["+debitNoteAmt+"]]>").append("</debit_note_amt>");
						else
							valueXmlString.append("<debit_note_amt>").append("<![CDATA[]]>").append("</debit_note_amt>");
					
					}
					if (currentColumn.trim().equals("bil_date"))
					{
						String sql="",invoiceID="",stkBillDate="",invoiceDate="";						
						Timestamp invDateT=null;
						int diffDays=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 conf_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("conf date invoice--->>["+invDateT+"]");
					
						if(invDateT !=null){							
						SimpleDateFormat format = new SimpleDateFormat(genericUtility.getApplDateFormat());
						invoiceDate = format.format(invDateT);	
						System.out.println("confDate 139------>>["+invoiceDate+"]");					
						
						System.out.print("stkBillDate ------->>>>>>>>>>>["+stkBillDate+"]");
						System.out.print("invoice confDate ------->>>>>>>>>>>["+invoiceDate+"]");
						
						sql="select  to_date( ?,'dd-mm-yyyy') -  to_date( ?,'dd-mm-yyyy') from dual";
						pstmt=conn.prepareStatement(sql);
						pstmt.setString(1, stkBillDate);
						pstmt.setString(2, invoiceDate);
						rs=pstmt.executeQuery();
						if(rs.next())
						{
							 diffDays=rs.getInt(1);							
						}
						rs.close();
						rs=null;
						pstmt.close();
						pstmt=null;
						System.out.println("Difference between ----->>["+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());
			isError=true;
			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;
	}
	
	public Map<String,String> getDetailsFromInvoice(Connection conn,String invoiceId)
	{
		HashMap<String,String> invDetailsMap=new HashMap<String, String>();
		ResultSet rs=null;
		PreparedStatement pstmt=null;
		String sql="";
		try{
			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())
			{	
				invDetailsMap.put("site_code", rs.getString(1));
				invDetailsMap.put("cust_code", rs.getString(2));
				invDetailsMap.put("amount", Double.toString((rs.getDouble(3))));
				invDetailsMap.put("curr_code", rs.getString(4));
				invDetailsMap.put("exch_rate", Double.toString(rs.getDouble(5)));
				invDetailsMap.put("prd_code", rs.getString(6));						
				
			}			
			rs.close();
			rs=null;
			pstmt.close();
			pstmt=null;
		}
		catch(Exception e){
			System.out.println("Exception in getDetailsFromInvoice----------");
			e.printStackTrace();
		}
		
		return invDetailsMap;
	}
	
	
	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();
				isError=true;
			}
		}
		
		return false;
	}
	
	
	
	protected boolean isPartialyInvoiceChk(Connection conn,String invID,String transaction)
	{
		System.out.println("---in method isPartialyInvoiceChk ------invoice ID :["+invID+"]");
		PreparedStatement pstmt2 = null,pstmt1=null ; 
		ResultSet rs2 = null,rs1=null;
		String sql1="",tranID="",confirmed="";
		int lineNoTraceL=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 <> ?";
		try
		{			
			pstmt2=conn.prepareStatement(sql1);
			pstmt2.setString(1, invID);
			pstmt2.setString(2, "R");
			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__trace,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())
					{
						lineNoTraceL=rs1.getInt(1);
						qtyInv=rs1.getDouble(3);
						podQty=rs1.getDouble(4);
						System.out.println("Line no ---->"+lineNoTraceL+ " qtyInv-->>"+qtyInv+" podQty->>"+podQty);
						totalPodQty=totalPodQty + podQty;
						if( podQty < qtyInv)
						{
							partialQty= podQty;
							actaulInvQty=qtyInv;
							lineNoTrace=lineNoTraceL;
							remainQty=actaulInvQty-partialQty;
							System.out.println("partialQty--->>["+partialQty+"]");
							System.out.println("actaulInvQty--->>["+actaulInvQty+"]");
							System.out.println("remainQty--->>["+remainQty+"]");
							System.out.println("lineNoTrace--->>["+lineNoTrace+"]");
							
						    return true;
						}
					}
					System.out.println("Total POD quantity --------->>"+totalPodQty);
					System.out.println("Confirmed POD---->["+confirmed+"]");
				
					rs1.close();rs1=null;
					pstmt1.close();pstmt1=null;
				}
				if("POD".equalsIgnoreCase(transaction)){
				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();
				isError=true;
			}
		}
		
		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;
	}
	public String checkNull(String input)	
	{
		if (input == null)
		{
			input="";
		}
		return input.trim();
	}
	
	
	protected 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;
	
		
	}
	protected 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;
		 
	}
	protected boolean isRecordPresentInMiscDrCrRcp(Connection conn,String invoiceID)
	{
		ResultSet rs=null,rs1=null;
		PreparedStatement pstmt=null,pstmt1=null;
		String sql="",itemCodeT="",lotNoT="",lotSlT="",lineNoT="",itemCodeM="",lotNoM="",lotSlM="",lineNoM="";
		double quantityT=0,quantityM=0;
		int count =0,noOfRecordMatched=0,noOfRecords=0;
		try{
			sql="select count(*) from invoice_trace where invoice_id = ?";
			pstmt=conn.prepareStatement(sql);
			pstmt.setString(1, invoiceID);
			rs=pstmt.executeQuery();
			if(rs.next()){
				noOfRecords=rs.getInt(1);
			}
			if(rs!=null){
				rs.close();
				rs=null;
			}
			if(pstmt!=null){
				pstmt.close();
				pstmt=null;
			}
			System.out.println("NoOfRecords---->>["+noOfRecords+"]");		
			
			sql="select sum(quantity),item_code,lot_no,lot_sl,inv_line_no from invoice_trace "
					+ "where invoice_id = ? group by item_code,lot_no,lot_sl,inv_line_no";
			pstmt=conn.prepareStatement(sql);
			pstmt.setString(1, invoiceID);
			rs=pstmt.executeQuery();
			while(rs.next()){
				count++;
				quantityT=rs.getDouble(1);
				itemCodeT=rs.getString(2);
				lotNoT=rs.getString(3);
				lotSlT=rs.getString(4);
				lineNoT=rs.getString(5);
				
				itemCodeT=itemCodeT==null ? "" :itemCodeT.trim();
				lotNoT=lotNoT==null ? "" :lotNoT.trim();
				lotSlT=lotSlT==null ? "" :lotSlT.trim();
				lineNoT=lineNoT==null ? "" :lineNoT.trim();
				System.out.println("count--------------->>["+count+"]");
				System.out.println("itemCodeT-->["+itemCodeT+"] lotNoT->["+lotNoT+"]");
				System.out.println("lotSlT-->["+lotSlT+"] lineNoT->["+lineNoT+"]");
				
				//checked in misc_drcr_rcp 
				
				sql="select sum(d.quantity) as Quantity,d.item_code,d.lot_no,d.lot_sl,d.line_no__invtrace from misc_drcr_rcp h,misc_drcr_rdet d "
						+ "where h.tran_id = d.tran_id  and h.remarks like ? and quantity is not null "
						+ "group by d.item_code,d.lot_no,d.lot_sl,d.line_no__invtrace";
				
				pstmt1=conn.prepareStatement(sql);
				pstmt1.setString(1, "%POD%"+invoiceID);
				rs1=pstmt1.executeQuery();
				while(rs1.next()){
					quantityM=rs1.getDouble(1);
					itemCodeM=rs1.getString(2);
					lotNoM=rs1.getString(3);
					lotSlM=rs1.getString(4);
					lineNoM=rs1.getString(5);
					
					itemCodeM=itemCodeT==null ? "" :itemCodeT.trim();
					lotNoM=lotNoM==null ? "" :lotNoM.trim();
					lotSlM=lotSlM==null ? "" :lotSlM.trim();
					lineNoM=lineNoM==null ? "" :lineNoM.trim();	
					
					System.out.println("QuantityT----->>["+quantityT+"]");
					System.out.println("QuantityM----->>["+quantityM+"]");
					System.out.println("itemCodeM-->["+itemCodeM+"] lotNoM-->["+lotNoM+"]");
					System.out.println("lotSlM-->["+lotSlM+"] lineNoM-->["+lineNoM+"]");
					
					if(itemCodeT.equalsIgnoreCase(itemCodeM) && lotNoT.equalsIgnoreCase(lotNoM)){
						if(lotSlT.equalsIgnoreCase(lotSlM) && lineNoT.equalsIgnoreCase(lineNoM)){
							if(quantityT == quantityM )
							{
								System.out.println("record matched........at count "+count);
								noOfRecordMatched++;
							}
						}
					}
					
				} //inner while end
				if(rs1!=null){
					rs1.close();
					rs1=null;
				}
				if(pstmt1!=null){
					pstmt1.close();
					pstmt1=null;
				}
				
			}//end main while
			System.out.println("noOfRecords End----->>["+noOfRecords+"]");
			System.out.println("noOfRecordMatched----->>["+noOfRecordMatched+"]");
			
			if(noOfRecords == noOfRecordMatched)
			{
				return true;
			}
			if(rs!=null){
				rs.close();
				rs=null;
			}
			if(pstmt!=null){
				pstmt.close();
				pstmt=null;
			}
			
			
		}
		catch(Exception e)
		{
			System.out.println("Exception : checkInMiscDrCrRcp : "+e.getMessage());
			e.printStackTrace();
		}
		return false;
	}
	
	protected 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;
				//getSalesReturnTranID(conn, "sreturn", "tran_id", "invoice_id", str);
				
				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;
	}
	

}
