package ibase.webitm.ejb.dis;

import ibase.system.config.ConnDriver;
import ibase.webitm.ejb.ITMDBAccessEJB;
import ibase.webitm.ejb.ProcessEJB;
import ibase.webitm.ejb.ValidatorEJB;
import ibase.webitm.ejb.fin.MiscDrCrRcpConf;
import ibase.webitm.utility.GenericUtility;
import ibase.webitm.utility.ITMException;
import ibase.webitm.ejb.ValidatorEJB;

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.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.HashSet;
import java.util.Iterator;
import java.util.Map;

import javax.ejb.Stateless;
import javax.xml.parsers.DocumentBuilder;
import javax.xml.parsers.DocumentBuilderFactory;

import org.w3c.dom.Document;
import org.w3c.dom.Element;
import org.w3c.dom.Node;
import org.w3c.dom.NodeList;

import com.sun.jmx.snmp.Timestamp;

@Stateless
public class PODProcess extends ProcessEJB implements PODProcessLocal,PODProcessRemote
{  
	 String siteCodeG="",invTypeG="";
	   
	 public String process(String xmlString, String xmlString2, String windowName, String xtraParams) throws RemoteException,ITMException
	{
			
			System.out.println("enter in process()");
			String retStr = "";
			Document detailDom = null;
			Document headerDom = null;
			
			GenericUtility genericUtility = GenericUtility.getInstance();			
			try
			{	
				if(xmlString != null && xmlString.trim().length()!=0)
				{
					headerDom = genericUtility.parseString(xmlString); 
					System.out.println("xmlString--->>" + xmlString);
				}
				if(xmlString2 != null && xmlString2.trim().length()!=0)
				{
					detailDom = genericUtility.parseString(xmlString2); 
					System.out.println("xmlString2 --->>" + xmlString2);
				}

				retStr = process(headerDom, detailDom, windowName, xtraParams);

			}
			catch (Exception e)
			{
				System.out.println("Exception :PODProcess :process(String xmlString, String xmlString2, String windowName, String xtraParams):" + e.getMessage() + ":");
				e.printStackTrace();
				retStr = e.getMessage();
			}
			return retStr;

		}
	  
	  
public String process() throws RemoteException,ITMException
{
		  return "";
}

public String process(Document dom, Document dom2, String windowName, String xtraParams) throws RemoteException,ITMException
{
		System.out.println("enter in process(dom) "); 
		ArrayList <String>inv45dayList=new ArrayList<String>();
		ArrayList<String> podProcessInvIDList=new ArrayList<String>(); 
		ArrayList<String> podNotConfList=new ArrayList<String>();
		ArrayList<String> podTraceInvList=new ArrayList<String>();
		ArrayList<String> miscDrCrInvList=new ArrayList<String>();
		Connection conn = null;
		ConnDriver connDriver = null;
		GenericUtility genericUtility = null;
		ProofOfDelivery podObject=null;
		ITMDBAccessEJB itmdbAccess = null;
		ValidatorEJB vdt=null;
		PreparedStatement pstmt=null;
		ResultSet rs=null;		
		int cnt=0;
		double ExchRate=0;
		String siteCode="",custCodeFrm="",custCodeTo="",invType="",errorString="",errCode="",userId="",custCode="";
		String sql="",invoiceId="",priceList="",currCode="";
		try{
			genericUtility = GenericUtility.getInstance();
			itmdbAccess=new ITMDBAccessEJB();
			podObject=ProofOfDelivery.getInstance();
		    vdt=new ValidatorEJB();
			connDriver = new ConnDriver();
			conn = connDriver.getConnectDB("DriverITM");
			connDriver = null;
			userId = vdt.getValueFromXTRA_PARAMS(xtraParams,"loginCode");
		   siteCode=genericUtility.getColumnValue("site_code", dom);
		   custCodeFrm=genericUtility.getColumnValue("cust_code_from", dom);
		   custCodeTo=genericUtility.getColumnValue("cust_code_to", dom);
		   invType=genericUtility.getColumnValue("inv_type", dom);
		
		   siteCode=siteCode==null ? "" :siteCode.trim();
		   custCodeFrm=custCodeFrm==null ? "" :custCodeFrm.trim();
		   custCodeTo=custCodeTo==null ? "" :custCodeTo.trim();
		   invType=invType==null ? "" :invType.trim();
		   System.out.println("SiteCode-133-->>["+siteCode+"]");
		   System.out.println("custCodeFrm--->>["+custCodeFrm+"]");
		   System.out.println("custCodeTo--->>["+custCodeTo+"]");
		   System.out.println("invType--->>["+invType+"]");
		
		//Validation for Site code
		if (siteCode.length() == 0 ){							
			errCode = "VTSITECNE";
			errorString = vdt.getErrorString("site_code",errCode,userId);
			return errorString;
			
		}else{							
			cnt=podObject.getDBRowCount(conn,"site","site_code",siteCode);
			if(cnt == 0){
			System.out.println("site_code not exist validation fire");
			errCode = "VTSITENEX";
			errorString = vdt.getErrorString("site_code",errCode,userId);	
			return errorString;
			}		
		  }
		//Validation for Customer Code From
		if (custCodeFrm.length() == 0 ){							
			errCode = "VTCUSTCNE";
			errorString = vdt.getErrorString("cust_code_from",errCode,userId);
			return errorString;
		
		}else{							
			cnt=podObject.getDBRowCount(conn,"customer","cust_code",custCodeFrm);
			if(cnt == 0){
			System.out.println("custCodeFrom not exist validation fire");
			errCode = "VTCUSTNEX";
			errorString = vdt.getErrorString("cust_code_from",errCode,userId);	
			return errorString;
			}		
		}
		
		//Validation for Customer Code To 
		if (custCodeTo.length() == 0 ){							
			errCode = "VTCUSTCNE";
			errorString = vdt.getErrorString("cust_code_to",errCode,userId);
			return errorString;
		
		}else{							
			cnt=podObject.getDBRowCount(conn,"customer","cust_code",custCodeTo);
			if(cnt == 0){
			System.out.println("custCodeTo not exist validation fire");
			errCode = "VTCUSTNEX";
			errorString = vdt.getErrorString("cust_code_to",errCode,userId);	
			return errorString;
			}		
		 }
		//Validation for invoice type. only DM or IS invoice type allowed.		
		if(invType.length() == 0){
			errCode = "VTINVTNN";
			errorString = vdt.getErrorString("inv_type",errCode,userId);
			return errorString;
			
		}else{
			if(!("DM".equalsIgnoreCase(invType) || "IS".equalsIgnoreCase(invType))){
			errCode = "VTINVTI";
			errorString = vdt.getErrorString("inv_type",errCode,userId);
			return errorString;
			}			
		}		
		if(custCodeFrm.length() > 0 && custCodeTo.length() > 0 && siteCode.length() > 0){
			siteCodeG=siteCode;
			invTypeG=invType;
		sql="select invoice_id from invoice where cust_code__bil between ? and  ? and site_code = ? "
				+ "and sysdate - conf_date > 570";
		pstmt=conn.prepareStatement(sql);
		pstmt.setString(1, custCodeFrm);
		pstmt.setString(2, custCodeTo);
		pstmt.setString(3, siteCode);
		
		rs=pstmt.executeQuery();
		while(rs.next()){			
		invoiceId=rs.getString(1);
		invoiceId=invoiceId==null ? "" :invoiceId.trim();			
		inv45dayList.add(invoiceId);			
		}
	    rs.close();
	    rs=null;
	    pstmt.close();
	    pstmt=null;
	    System.out.println("Size of arrayList inv45dayList----->>["+inv45dayList.size()+"]");	    
		podProcessInvIDList=getDoneInvoiceIdHDR( conn, inv45dayList);	
		
		if(podProcessInvIDList.contains("Error")){
		//handle error if array list contain error. 			
		errCode="VTPRCNCP";
		errorString =itmdbAccess.getErrorString("", errCode, "", "", conn);	
		return errorString;
		}			
		System.out.println("podProcessInvIDList---->>["+podProcessInvIDList+"]");
		System.out.println("Size of podProcessInvIDList---->>["+podProcessInvIDList.size()+"]");
		podNotConfList=getNotConfirmedInvIdFromHdr(conn);
		if(podNotConfList.contains("Error")){
		errCode="VTPRCNCP";
		errorString =itmdbAccess.getErrorString("", errCode, "", "", conn);	
		return errorString;
		}
		System.out.println("podNotConfList---->>["+podNotConfList+"]");
		System.out.println("Size of podNotConfList---->>["+podNotConfList.size()+"]");
		
		//check in podNotConfList if invoice id  in podProcessInvIDList present or not.
		//if present then remove from podProcessInvIDList.
		if(podNotConfList.size() > 0 && podProcessInvIDList.size() > 0)
		{
			for(String id : podNotConfList)
			{
				if(podProcessInvIDList.contains(id))
				{
					System.out.println("id removed------>>["+id+"]");
					podProcessInvIDList.remove(id);
				}
			}
		}
		
		//Need to be discussion. ------------------------------------------@@@@@@@@@@@@@@
		/*podProcessInvIDList.clear();
		podProcessInvIDList.add("618ID00036");
		podProcessInvIDList.add("618ID00037");
		podProcessInvIDList.add("618ID00038");
		podProcessInvIDList.add("618ID00039");*/
		
		podTraceInvList=getDoneInvoiceIDFromPodTrace(conn);
		System.out.println("podTraceInvList---->>["+podTraceInvList+"]");
		if(podTraceInvList.size() > 0){
			
			for(String podPrcId : podProcessInvIDList){
				if(podTraceInvList.contains(podPrcId)){
					podProcessInvIDList.remove(podPrcId);
					
				}
			}			
		}
		System.out.println("podProcessInvIDList-->>["+podProcessInvIDList+"]");
		miscDrCrInvList=getDoneInvoiceIDFromMiscDrCrRcp(conn);
		System.out.println("miscDrCrInvList---->>["+miscDrCrInvList+"]");
		Iterator<String> it = podProcessInvIDList.iterator();
		if(miscDrCrInvList.size() > 0){
			while (it.hasNext()) {
				String podPrcId=it.next();
				if(miscDrCrInvList.contains(podPrcId)){
					System.out.println("---in misc arrayList------");
					it.remove();
					podProcessInvIDList.remove(podPrcId);
					
				}
				
			}
		}
		//podProcessInvIDList.clear(); //remove on final----------------------------7826464876----------------
		
		
		//podProcessInvIDList.add("618ID00044");
		System.out.println("Final invoice Id ready for debit note---->>["+podProcessInvIDList+"]");	
		
		
		priceList=podObject.getNameOrDescrForCode(conn, "site", "price_list", "site_code", siteCode);
	    priceList=priceList==null ? "" :priceList.trim();
		System.out.println("priceList----->>["+priceList+"]");
		if(priceList.length() <= 0){
		errCode="VTPRCLND"; //Price list not define in site master.
		errorString = itmdbAccess.getErrorString("", errCode, "", "", conn);
		return errorString;
		}
		//Check in invoice_trace if  item_code_ord (scheme_code) is available or not.
		//if yes then checked in bomdet.if scheme code found then for that particular item scheme is apply.		
		errorString=generateDebitNote(conn, podProcessInvIDList,priceList,xtraParams,siteCode,invType);
		System.out.println("Return string from generateDebitNote----->>["+errorString+"]");
		
		
		}
		}//end try
		catch(Exception e)
		{
			System.out.println("Exception in PODProcess Class---------");
			e.printStackTrace();
		}
		
		finally{
			try{
			if(conn !=null)
			{
				conn.close();
			}
			}
			catch(Exception e)
			{
				e.printStackTrace();
			}
		}
		return errorString;

}

private String generateDebitNote(Connection conn,ArrayList<String> podProcessInvIDListL,String priceListL,String xtraParams,String siteCode,String invType)
{
	ResultSet rs=null;
	Connection conn1=null;
	PreparedStatement pstmt=null; 
	ProofOfDelivery pod=null;
	ProofOfDeliveryConf podC=null;
	Map<String,String> invoiceDetailsMap=null;
	HashMap<String,String> podTraceMap=new HashMap();
	HashMap<String,String> schemeMap=new HashMap();
	HashMap<String,String> partialMap=new HashMap();
	Map<String,Double> schemeQty=null;	
	String sql="",lotNo="",lotSl="",itemCode="",itemCodeOrd="",itemActive="",detail2xmlString="",errString="";
	String custCode="",currCode="",partial="N",QtyInvTrace="",scheme="N",errCode="";
	double invRate=0,invQty=0,discount=0,priceListRate=0,detail3Amt=0,detail3AmtT=0,debitNoteAmtDetail=0;
	double debitNoteAmtHdr=0,ExchRate=0,actualRate=0,chargQty=0,freeQty=0,netPTS=0;
	int lineNotrace=0,lineNo=0,noOfInvoicePrc=0,totalInvoice=0;
	pod=ProofOfDelivery.getInstance();
	podC=ProofOfDeliveryConf.getInstance();
	ITMDBAccessEJB itmdbAccess1=new ITMDBAccessEJB();
	ConnDriver connDriver1 = new ConnDriver();
	try{
	conn1 = connDriver1.getConnectDB("DriverITM");
	connDriver1 = null;
	System.out.println("priceListL123------>>["+priceListL+"]");	
	totalInvoice=podProcessInvIDListL.size();
	System.out.println("total invoice for process------>>["+totalInvoice+"]");
	if(totalInvoice > 0){
	for(String invoiceId :podProcessInvIDListL)
	{
	detail2xmlString="";
	lineNo=0;
	System.out.println("invoice ID in for loop------->>["+invoiceId+"]");	
	sql="select rate,quantity,lot_no,lot_sl,item_code,inv_line_no,discount,item_code__ord,invoice_id " +
			"from invoice_trace where invoice_id = ? order by inv_line_no";
	pstmt=conn.prepareStatement(sql);
	pstmt.setString(1, invoiceId);
	rs=pstmt.executeQuery();
	
	while(rs.next())
	{
		invRate=rs.getDouble(1);
		invQty=rs.getDouble(2);
		lotNo=rs.getString(3);
		lotSl=rs.getString(4);
		itemCode=rs.getString(5);
		lineNotrace=rs.getInt(6);
		discount=rs.getDouble(7);
		itemCodeOrd=rs.getString(8);
		
		lotNo=lotNo==null ? "" :lotNo.trim();
		lotSl=lotSl==null ? "" :lotSl.trim();
		itemCode=itemCode==null ? "" :itemCode.trim();
		itemCodeOrd=itemCodeOrd==null ? "" :itemCodeOrd.trim();
		QtyInvTrace=Double.toHexString(invQty);
		
		//Discount need discussion -----------------@@@@@--------
		if(discount > 0)
		{
			invRate=invRate - (invRate * discount/100);			
			System.out.println("Discount for invoice id--->>["+invoiceId+"]"+"inv Rate-->>["+invRate+"]");
		}
		//check if any partially entry exist in POD HDR or not.
		//if yes then minus from actual invoice  qty.
		partial="N";
		scheme="N";
		if(pod.isPartialyInvoiceChk(conn, invoiceId,"PRC")){
			System.out.println("@#@#partial condition found for invoice id----->>["+invoiceId+"]");
			System.out.println("Actual inv qty in inv trace---->>["+invQty+"]");
			System.out.println("inv. qty done in POD screen---->>["+pod.partialQty+"]");
			invQty = invQty - pod.partialQty;	
			partial="Y"; 
			partialMap.put(invoiceId+","+itemCode, scheme);
			System.out.println("Actual inv qty---->>["+invQty+"]");
		}
		partialMap.put(invoiceId+","+itemCode, partial);		
		
		//Check if item code Active or not."N" or null then return error message. 
		itemActive=pod.getColumnDescr(conn,"active","item","item_code",itemCode);						
		itemActive=itemActive==null ? "" :itemActive.trim();
		if(itemActive.equalsIgnoreCase("N") || itemActive.length()<=0)
		{			
			errCode="VTITMCNAC"; //Item code not active  
			errString = itmdbAccess1.getErrorString("", errCode, "", "", conn);
			errString=newErrorMessage(errString, invoiceId);
			return errString; // item code not active
		}
		java.util.Date tranDate=new Date();
		priceListRate=getPriceListRate(priceListL, tranDate,itemCode,lotNo,"", conn);				
		System.out.println("priceListRate----->>["+priceListRate+"]");
		if(priceListRate <= 0)
		{
			errCode="VTPRCLNE";  
			errString = itmdbAccess1.getErrorString("", errCode, "", "", conn);
			errString=newErrorMessage(errString, invoiceId);
			return errString; 			
		}
		
		//check if scheme is apply or not
		if(isSchemeApply(conn,itemCode)){
			//java code if scheme is apply
		System.out.println("-------Scheme is apply -----------");
		schemeQty=schemeApply(conn,invoiceId,itemCode);
		System.out.println("schemeQty map---->>["+schemeQty+"]");
		if(schemeQty.size() > 0){
			if(schemeQty.get("success") < 0){
				errCode="VTPRCNCP";  	// Scheme related error or sql error.
				errString = itmdbAccess1.getErrorString("", errCode, "", "", conn);
				errString=newErrorMessage(errString, invoiceId);
				return errString; 			
			}
			else if(schemeQty.get("success") == 0){
				errCode="VTSCHME";  	//scheme is expired for particular item,please check in bomdet
				errString = itmdbAccess1.getErrorString("", errCode, "", "", conn);
				errString=newErrorMessage(errString, invoiceId);
				return errString; 				
			}
			else if(schemeQty.get("success") == 1){
			scheme="Y";
			schemeMap.put(invoiceId+","+itemCode, scheme);
			chargQty=schemeQty.get("charge_qty");
			freeQty=schemeQty.get("free_qty");
			
			netPTS=chargQty / (chargQty - freeQty) * priceListRate;			
			
			System.out.println("Net PTS rate----->>["+netPTS+"]");
			actualRate=netPTS - invRate;
			detail3Amt= actualRate * invQty;
			lineNo++;
			detail2xmlString=detail2xmlString + podC.getDetailsXmlString(conn,invoiceId,lineNotrace,lineNo,actualRate,invQty,lotNo,lotSl,itemCode, detail3Amt);
			}
		}			
		}else{
			System.out.println("-------Scheme not apply -----------");				
			schemeMap.put(invoiceId+","+itemCode, scheme);
			actualRate=priceListRate - invRate;
			detail3Amt= actualRate * invQty;
					
			System.out.println("Debit note amount-------->>["+detail3Amt+"]");
			System.out.println("Actual rate after deducting inv rate-------->>["+detail3Amt+"]");
			detail3AmtT=detail3AmtT + detail3Amt;
			lineNo++;
			detail2xmlString=detail2xmlString + podC.getDetailsXmlString(conn,invoiceId,lineNotrace,lineNo,actualRate,invQty,lotNo,lotSl,itemCode, detail3Amt);
		
		} //end isSchemeApply
	
		
	} //end while invoice trace
	rs.close();
	rs=null;
	pstmt.close();
	pstmt=null;
	System.out.println("Detail2 xml-------->>[["+detail2xmlString+"]");
	if(detail3AmtT > 0){
	invoiceDetailsMap=pod.getDetailsFromInvoice(conn, invoiceId);
	custCode=invoiceDetailsMap.get("cust_code").toString();
	currCode =pod.checkNull(invoiceDetailsMap.get("curr_code")); //xmlStringAll
	ExchRate=Double.parseDouble(invoiceDetailsMap.get("exch_rate"));
	errString=podC.genDebitNote("PRC",invoiceId,custCode,siteCode,xtraParams,currCode,ExchRate,detail2xmlString,detail3AmtT,conn);
	System.out.println("------Error string after DN save--->>["+errString+"]");
	if (errString.indexOf("Success") > -1)
	{							
		System.out.println("Tran id for Misc. debit note------->>["+(podC.debitNoteTranID).trim()+"]");
		MiscDrCrRcpConf confDebitNote=new MiscDrCrRcpConf();
		errString=confDebitNote.confirm((podC.debitNoteTranID).trim(),xtraParams,"",conn);		
		System.out.println("Error String on DB Confirmation: "+errString);	
		if((errString != null ) &&  errString.indexOf("CONFSUCCES") > -1)
		{
			int cnt=insertDataInPodTrace(conn1, invoiceId, podC.xmlStringAll, schemeMap, partialMap,"Y");
			noOfInvoicePrc++;
			 if(noOfInvoicePrc==totalInvoice){					
					errCode="VTPRCCSF";  	
					errString = itmdbAccess1.getErrorString("", errCode, "", "", conn);				
					return errString;  
			 }
			 if(cnt > 0){
				 conn1.commit();
			 }
			 
			 
			conn.commit();
			/*errCode="VTPRCCSF";  	
			errString = itmdbAccess1.getErrorString("", errCode, "", "", conn);				
			return errString; */
			
			
			//return "Success";
		}else{
			conn.rollback();
			int cnt=insertDataInPodTrace(conn1, invoiceId, podC.xmlStringAll, schemeMap, partialMap,"N");	
			if(cnt > 1)
				conn1.commit();
			errString=newErrorMessage(errString, invoiceId);
			return errString;
		}
	}else{	
		conn.rollback();
		int cnt=insertDataInPodTrace(conn1, invoiceId, podC.xmlStringAll, schemeMap, partialMap,"N");
		if(cnt > 1)
			conn1.commit();
		errString=newErrorMessage(errString, invoiceId);
		return errString;	
	}
	
	}else {// end detail3AmtT > 0 condition
		System.out.println("Debit note gennerated for Rs.0- Invoice ID----->> ["+invoiceId+"]");
		int cnt=insertDataInPodTrace(conn1, invoiceId, podC.xmlStringAll, schemeMap, partialMap,"Y");
		if(cnt > 1)
			conn1.commit();
	}
	
	
	} //end invoice id for loop
	
	}else{
		errCode="VTDNFFP";  //No data found for processing	
		errString = itmdbAccess1.getErrorString("", errCode, "", "", conn);				
		return errString;
	}
	
   }catch(Exception e)
   {
	   System.out.println("Exception : ");
	   e.printStackTrace();
	   return "Error";
   }
	finally{
		if(conn1!=null){
			try{
			conn1.close();
			conn1=null;
			}
			catch(Exception e)
			{
				e.printStackTrace();
			}
		}
	}
	return errString;
}

private int checkPODDoneAlready (Connection conn,String invoiceIdL)
{
	ResultSet rs=null,rs1=null;
	PreparedStatement pstmt=null,pstmt1=null;
	String sql="",remarks="",invID="",itemCodeMisc="",itemCodeInv;
	double invQty=0,miscQty=0;
	int count=0,lineInvT=0,lineMiscInvT=0;
	
	try{
		sql="SELECT d.item_code,d.quantity,h.remarks,d.line_no__invtrace FROM  misc_drcr_rcp h, misc_drcr_rdet d  "
				+ "where remarks like 'Auto%POD%' and h.tran_id=d.tran_id";
		//sql="SELECT remarks FROM misc_drcr_rcp where remarks like 'Auto%POD%'";
		pstmt=conn.prepareStatement(sql);		
		rs=pstmt.executeQuery();
		while(rs.next()){
			itemCodeMisc=rs.getString(1);
			miscQty=rs.getDouble(2);
			remarks=rs.getString(3).trim();
			lineMiscInvT=rs.getInt(4);
			itemCodeMisc=itemCodeMisc==null ? "" :itemCodeMisc.trim();
			invID=remarks.substring(46, remarks.length());
			System.out.println("Invoice id after subString----->>["+invID+"]");
			
			if(invID.equalsIgnoreCase(invoiceIdL.trim())){
				sql="select invoice_id,inv_line_no,item_code,quantity from invoice_trace where invoice_id = ?";
				pstmt1=conn.prepareStatement(sql);	
				pstmt1.setString(1, invID.trim());
				rs1=pstmt1.executeQuery();
				while(rs1.next()){
					lineInvT=rs1.getInt(2);
					itemCodeInv=rs1.getString(3);
					invQty=rs1.getDouble(4);
					itemCodeInv=itemCodeInv==null ? "" :itemCodeInv.trim();
					System.out.println("itemCodeMisc--->>["+itemCodeMisc+"  itemCodeInv-->>["+itemCodeInv+"]");
					System.out.println("lineMiscInvT--->>["+lineMiscInvT+"  lineInvT-->>["+lineInvT+"]");
					if(itemCodeMisc.equalsIgnoreCase(itemCodeInv) && lineMiscInvT == lineInvT){
						if(invQty == miscQty){
							return 1;
						}
						
					}
					
				}
				
				
			}
		}
		rs.close();
		rs=null;
		pstmt.close();
		pstmt=null;
		
	}
	catch(Exception e){
		System.out.println("----Exception in checkPODDoneAlready----");
		e.printStackTrace();
		return -1;
	}
	return count;
}
// Return array list whoes invoice id not confirmed in spl_sales_por_hdr table.
private ArrayList<String> getNotConfirmedInvIdFromHdr(Connection conn)
{
	ArrayList<String> podNotConfListL=new ArrayList<String>();
	PreparedStatement pstmt=null;
	ResultSet rs=null;
	String sql="",invID="";
	try
	{
		sql="select invoice_id from spl_sales_por_hdr where confirmed = ? ";
		pstmt=conn.prepareStatement(sql);		
		pstmt.setString(1, "N");
		rs=pstmt.executeQuery();		
		while(rs.next())
		{	
			invID=rs.getString(1);
			invID=invID==null ? "" :invID.trim();
			podNotConfListL.add(invID);				
		}
		System.out.println("Invoice id Not confirmed in HDR table--->>[["+podNotConfListL+"]]");
		rs.close();
		rs=null;
		pstmt.close();
		pstmt=null;
	}
	catch(Exception e)
	{
		System.out.println("-----Exception in getNotConfirmedInvIdFromHdr method-----");
		podNotConfListL.add("Error");
		e.printStackTrace();
	}
	
	return podNotConfListL;
	
}
private int updatePodTraceSuccess(Connection conn,String success,String invID)
{
	String sql="";
	int lineNo=0,count=0;
	double dbAmt=0.0,debitNoteHeaderL=0;
	PreparedStatement pstmt=null;
	
	try{
	sql="update podtrace set success = ? where invoice_id = ?";
	pstmt = conn.prepareStatement(sql);
	pstmt.setString(1,success);
	pstmt.setString(2, invID);
	count=pstmt.executeUpdate();
	}//end try
	catch(Exception e){
		e.printStackTrace();
		return 0;
	}
	
	System.out.println("update updatePodTraceSuccess@@@----->>["+count+"]");
	return count;
	
}
private ArrayList<String> getDoneInvoiceIdHDR(Connection conn,ArrayList<String> inv45dayListL)
{
	System.out.println("--------------checkPODDoneOrNot method------------------");
	System.out.println("ArrayList invIdList--->>[["+inv45dayListL+"]]");
	ArrayList<String> podDoneInvIDList=new ArrayList<String>();
	ArrayList<String> podProcessInvIDListL=new ArrayList<String>();	
	
	String sql="",invoiceIdL="",tranIdL="";
	PreparedStatement pstmt=null,pstmt1=null;
	ResultSet rs=null,rs1=null;
	
	try{		
		//Get all records whoes POD is already generated. 
		sql="select tran_id,invoice_id from spl_sales_por_hdr where confirmed = ? ";
		pstmt=conn.prepareStatement(sql);		
		pstmt.setString(1, "Y");
		rs=pstmt.executeQuery();
		
		while(rs.next())
		{	
			tranIdL=rs.getString(1);
			invoiceIdL=rs.getString(2);
			invoiceIdL=invoiceIdL==null ? "" :invoiceIdL.trim();
			tranIdL=tranIdL==null ? "" :tranIdL.trim();
			podDoneInvIDList.add(invoiceIdL);		
			
		}		
		System.out.println("ArrayList podDoneInvIDList--->>[["+podDoneInvIDList+"]]");
		System.out.println("ArrayList podDoneInvIDList size--->>[["+podDoneInvIDList.size()+"]]");
		rs.close();
		rs=null;
		pstmt.close();
		pstmt=null;
		if(inv45dayListL.size() > 0 && podDoneInvIDList.size() > 0){
			System.out.println("---both arraylist contains elements-----------");
			for (String invId : inv45dayListL) {  
				
				if(!(podDoneInvIDList.contains(invId))){					
					podProcessInvIDListL.add(invId);
					
				}
				
			}
			
		}else{
			return inv45dayListL;
		}
		
		System.out.println("podProcessInvIDList----->>[["+podProcessInvIDListL+"]");
		System.out.println("podProcessInvIDList----->>[["+podProcessInvIDListL.size()+"]");
	}
	catch(Exception e)
	{
		System.out.println("---------Exception in checkPODDoneOrNot method------------------");
		podProcessInvIDListL.add("Error");
		e.printStackTrace();		
		
	}
	finally{
		try{
			if(rs!=null)
			{
				rs.close();rs=null;
			}
			if(pstmt!=null)
			{
				pstmt.close();
				pstmt=null;
			}
			
			
		}
		catch(Exception e)
		{
			System.out.println("Exception in finally checkPODDoneOrNot method-------");
			e.printStackTrace();
		}
	}
	return podProcessInvIDListL;
	
}
private boolean isSchemeApply(Connection conn,String itemCodeL)
{
	String sql="";
	ResultSet rsL=null;
	PreparedStatement pstmtL=null;
	int countL=0;
	try{
		sql="select count(*) from bomdet where item_Code = ? ";
		pstmtL=conn.prepareStatement(sql);
		pstmtL.setString(1, itemCodeL);
		rsL=pstmtL.executeQuery();
		if(rsL.next()){
			countL=rsL.getInt(1);
		}
		System.out.println("count in isSchemeApply-->>["+countL+"] itemCode--->>["+itemCodeL+"]");
		rsL.close();
		rsL=null;
		pstmtL.close();
		pstmtL=null;
		if(countL > 0)
			return true;
		
		
		
	}
	catch(Exception e)
	{
		System.out.println("----Exception : in isSchemeApply method-------");
		e.printStackTrace();
	}
	
	return false;
	
}

private Map<String,Double> schemeApply(Connection conn,String invoiceID,String itemCode)
{
	ResultSet rs=null;
	PreparedStatement pstmt=null;
	HashMap <String,Double>schemeQtyMap=new HashMap<String,Double>();
	
	String sql="",nature="";
	int count=0;
	double quantity=0;
	
	try{
		sql="select count(*) from bomdet where item_code= ? and eff_from <= sysdate and "
				+ "valid_upto >= sysdate order by nature";
		pstmt=conn.prepareStatement(sql);
		pstmt.setString(1, itemCode);
		rs=pstmt.executeQuery();
		if(rs.next()){
			count=rs.getInt(1);
		}		
		rs.close();
		rs=null;
		pstmt.close();
		pstmt=null;
		
		if(count > 0){
		
		sql="select qty_per,nature from bomdet where item_code= ? and eff_from <= sysdate and "
				+ "valid_upto >= sysdate order by nature";
		pstmt=conn.prepareStatement(sql);
		pstmt.setString(1, itemCode);	
		
		rs=pstmt.executeQuery();
		while(rs.next()){
			quantity=rs.getDouble(1);	
			nature=rs.getString(2);
			 if("C".equalsIgnoreCase(nature.trim())){				 
				 schemeQtyMap.put("charge_qty", quantity);
				 System.out.println("charge qty added in map------>>["+schemeQtyMap.get("charge_qty")+"]");
			 }
			 if("F".equalsIgnoreCase(nature.trim())){
				 schemeQtyMap.put("free_qty", quantity); 
				 System.out.println("free qty added in map------>>["+schemeQtyMap.get("free_qty")+"]");
			 }
		} //end while
		rs.close();
		rs=null;
		pstmt.close();
		pstmt=null;
		}else{
			System.out.println("----data not found in bomdet---------");
			schemeQtyMap.put("success", new Double(0));
		}
		
		schemeQtyMap.put("success", new Double(1));
	}
	catch(Exception e)
	{
		System.out.println("Exception in scheme apply ---------");
		e.printStackTrace();
		schemeQtyMap.put("success", new Double(-1));
		return schemeQtyMap;
		
	}
	return schemeQtyMap;
}
private int insertDataInPodTrace(Connection conn,String invID,String xmlDataAll,Map<String,String> schemeMapL,Map<String,String> partialMapL,String success)
{	
	PreparedStatement pstmt=null;
	ResultSet rs=null;
	String sql="",itemCode="",quantity="",rate="",amount="",scheme="",partial="",lineNoS="";
	int count=0;
	long tranID=0;
	invID=invID==null ? "" :invID.trim();
	try{
		//DocumentBuilderFactory dbFactory = DocumentBuilderFactory.newInstance();
		//DocumentBuilder dBuilder = dbFactory.newDocumentBuilder();
		Document doc = new ValidatorEJB ().parseString(xmlDataAll);
		doc.getDocumentElement().normalize();
		System.out.println("Root element :" + doc.getDocumentElement().getNodeName());
		NodeList nList = doc.getElementsByTagName("Detail2");
		System.out.println("in continue----------------"+nList.getLength());
		for (int temp = 0; temp < nList.getLength(); temp++) {
			 
			Node nNode = nList.item(temp);		
			if (nNode.getNodeType() == Node.ELEMENT_NODE) {				 
				Element eElement = (Element) nNode;					
				itemCode=eElement.getElementsByTagName("item_code").item(0).getTextContent();
				quantity=eElement.getElementsByTagName("quantity").item(0).getTextContent();
				rate=eElement.getElementsByTagName("rate").item(0).getTextContent();
				amount=eElement.getElementsByTagName("amount").item(0).getTextContent();
				lineNoS=eElement.getElementsByTagName("line_no").item(0).getTextContent();
				scheme=schemeMapL.get(invID+","+itemCode.trim());
				partial=partialMapL.get(invID+","+itemCode.trim());
				System.out.println("scheme in insert pod trace---->>["+scheme+"]");				
				
				
				sql="select tran_id from podtrace order by tran_id DESC";
				pstmt=conn.prepareStatement(sql);
				rs=pstmt.executeQuery();
				if(rs.next()){
					tranID=Long.parseLong(rs.getString(1).trim());
				}else{
					tranID=10000;
				}
				rs.close();rs=null;
				pstmt.close();
				pstmt=null;
				sql="insert into podtrace (tran_id,line_no,tran_date,invoice_id,site_code,item_code,inv_type,db_qty,"
						+ "db_rate,debit_note_amt,partial,scheme,chg_date,chg_user,chg_term,success) "
						+ "values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)"; 
				pstmt=conn.prepareStatement(sql);
				pstmt.setString(1, Long.toString(++tranID));
				pstmt.setInt(2, Integer.parseInt(lineNoS));
				pstmt.setTimestamp(3, new java.sql.Timestamp(new java.util.Date().getTime()) );
				pstmt.setString(4, invID);
				pstmt.setString(5, siteCodeG);		
				pstmt.setString(6,itemCode);
				pstmt.setString(7, invTypeG);		
				pstmt.setDouble(8, Double.parseDouble(quantity));
				pstmt.setDouble(9, Double.parseDouble(rate));
				
				pstmt.setDouble(10, Double.parseDouble(amount));
				pstmt.setString(11, partial);
				pstmt.setString(12, scheme);		
				pstmt.setTimestamp(13, new java.sql.Timestamp(new java.util.Date().getTime()) );
				pstmt.setString(14, "BASE");
				pstmt.setString(15, "System");
				pstmt.setString(16, success);
				
				count=pstmt.executeUpdate();
				
				pstmt.close();
				
	 
			}//end if
		} //end for
		
		
		
		
		
		
	}
	catch(Exception e)
	{
		e.printStackTrace();
		System.out.println("Exception in insertDataInPodTrace method----");
		return -1;
	}
	return count;
}

private ArrayList<String> getDoneInvoiceIDFromPodTrace(Connection conn)
{
	PreparedStatement pstmt=null;
	ResultSet rs=null;
	String sql="";
	HashSet<String> unqInvID=new HashSet <String>();
	ArrayList<String> InvIDList=new ArrayList <String>();
	try{
		sql="select invoice_id from podtrace where success = ?";
		pstmt=conn.prepareStatement(sql);
		pstmt.setString(1, "Y");
		rs=pstmt.executeQuery();
		while(rs.next()){
			InvIDList.add(rs.getString(1).trim());
		}
		if(InvIDList.size() > 0){
			unqInvID.addAll(InvIDList);
			InvIDList.clear();
			InvIDList.addAll(unqInvID);
		}
	}
	catch(Exception e)
	{
		e.printStackTrace();
	}
	return InvIDList;
}
private ArrayList<String> getDoneInvoiceIDFromMiscDrCrRcp(Connection conn)
{
	PreparedStatement pstmt=null,pstmt1=null;
	ResultSet rs=null,rs1=null;
	String sql="",remarks="",subInvId="";
	double sumQuantityMisc=0,sumQuantityInv=0;
	HashSet<String> unqInvID=new HashSet <String>();
	ArrayList<String> tranIDList=new ArrayList <String>();
	ArrayList<String> InvIDList=new ArrayList <String>();
	ArrayList<String> InvIDFinalList=new ArrayList <String>();
	try{
		sql="select tran_id,remarks from misc_drcr_rcp where  remarks like 'Auto%POD%'";
		pstmt=conn.prepareStatement(sql);
		
		rs=pstmt.executeQuery();
		while(rs.next()){
			tranIDList.add(rs.getString(1));
			remarks=rs.getString(2);
			subInvId=remarks.substring(46, remarks.length());
			InvIDList.add(subInvId);
			
		}	
		if(InvIDList.size() > 0){
			unqInvID.addAll(InvIDList);
			InvIDList.clear();
			InvIDList.addAll(unqInvID);
		}
	}
	catch(Exception e)
	{
		e.printStackTrace();
	}
	return InvIDList;
}
private  String newErrorMessage(String errString,String invid) 
{
	System.out.println("In newErrorMessage method--->>["+errString+"]");
	String trace="",message="",desc="",redirect="",domID="";
	Node parentNode = null;
	Node childNode = null;
	NodeList childNodeList = null;
	StringBuffer newStr=new StringBuffer();
	try{
	DocumentBuilderFactory dbFactory = DocumentBuilderFactory.newInstance();
	DocumentBuilder dBuilder = dbFactory.newDocumentBuilder();
	Document doc = new ValidatorEJB ().parseString(errString);
	doc.getDocumentElement().normalize();
	System.out.println("Root element :" + doc.getDocumentElement().getNodeName());
	NodeList parentNodeList = doc.getElementsByTagName("error");
	  parentNode = parentNodeList.item(0);
	  childNodeList = parentNode.getChildNodes();
	System.out.println("childNodeList length----------------"+parentNodeList.getLength());
	  for (int i = 0; i < childNodeList.getLength(); i++) {			 
		  childNode = childNodeList.item(i);
		  if(childNode ==null){
			  System.out.println("child Node is null..............");
		  }
		 // System.out.println("ChildNode Name--->>"+childNode.getFirstChild().getTextContent());
		  if ("message".equalsIgnoreCase(childNode.getNodeName())) {
			  message=childNode.getFirstChild() == null ? "" : childNode.getFirstChild().getNodeValue();			 
		  }
		  else if ("description".equalsIgnoreCase(childNode.getNodeName())) {
			  desc=childNode.getFirstChild() == null ? "" : childNode.getFirstChild().getNodeValue();
		  }
		  else if ("trace".equalsIgnoreCase(childNode.getNodeName())) {
			  trace=childNode.getFirstChild() == null ? "" : childNode.getFirstChild().getNodeValue();
		  }
		  else if ("redirect".equalsIgnoreCase(childNode.getNodeName())) {
			  redirect=childNode.getFirstChild() == null ? "" : childNode.getFirstChild().getNodeValue();
		  }
		  else if ("detailDomId".equalsIgnoreCase(childNode.getNodeName())) {
			  domID=childNode.getFirstChild() == null ? "" : childNode.getFirstChild().getNodeValue();
		  }
		
		  
		
	}
	  trace=trace+ " Invoice Id : "+invid;
	
	newStr.append("<?xml version=\"1.0\" encoding=\"ISO-8859-1\"?>"
			+ "<Errors><error column_name=\"acct_code\" id=\"VMACCT1\" type=\"E\">"
					+ "<message><![CDATA["+message+"]]></message>"
					+ "<description><![CDATA["+desc+"]]></description>");
			newStr.append("<trace><![CDATA["+trace +"]]></trace>");
			newStr.append("<redirect><![CDATA["+redirect+"]]></redirect>");
			newStr.append("<detailDomId><![CDATA["+domID+"]]></detailDomId>");
			
			newStr.append("</error></Errors>");
			
			System.out.println("Final xml in newErrorMessage--->>[["+newStr.toString()+"]]");
			
	}
	catch(Exception e){
		e.printStackTrace();
	}
	return newStr.toString();
}
public double getPriceListRate(String priceList, java.util.Date tranDate, String itemCode, String lotNo, String type, Connection connectionObject) throws RemoteException,ITMException
{
	double rate = 0;
	String siteCode = "";
	String locCode = "";
	String lotSl = "";

	String errCode = "";
	Statement stmt;
	ResultSet rs = null;
	String sql = "";

	//boolean connectionState = false;
	try
	{
		
		stmt = connectionObject.createStatement();
		sql = "SELECT LIST_TYPE FROM PRICELIST WHERE PRICE_LIST = '"+priceList+"'";
		rs = stmt.executeQuery(sql);
		if (!rs.next())
		{
			return -1;
		}
		else
		{
			type = rs.getString(1);
		}
		if (type.equalsIgnoreCase("L"))	// List Price
		{
			sql = "SELECT RATE FROM PRICELIST WHERE PRICE_LIST = '"+priceList+"' AND ITEM_CODE = ''"+itemCode+"' AND LIST_TYPE = 'L' AND EFF_FROM <= '"+tranDate+"' AND VALID_UPTO >= '"+tranDate+"'";
			rs = stmt.executeQuery(sql);
			if (!rs.next())
			{
				return -1;	 // Denotes Error
			}
			else
			{
				rate = rs.getDouble(1);
			}
		}
		else if (type.equalsIgnoreCase("D"))	// Despatch
		/* Selecting rate from pricelist  for L, if not found picking up from batch */
		{
			try
			{
				sql = "SELECT RATE FROM PRICELIST WHERE PRICE_LIST = '"+priceList+"' AND ITEM_CODE = '"+itemCode+"' AND LIST_TYPE = 'L'	AND EFF_FROM <= '"+tranDate+"' AND VALID_UPTO >= '"+tranDate+"'";
				rs = stmt.executeQuery(sql);
				if (!rs.next())
				{
					rate = 0;
					sql = "SELECT RATE FROM PRICELIST WHERE PRICE_LIST = '"+priceList+"' AND ITEM_CODE = '"+itemCode+"' AND LIST_TYPE = 'B' AND LOT_NO__FROM <= '"+lotNo+"' AND LOT_NO__TO >= '"+lotNo+"' AND EFF_FROM <= '"+tranDate+"' AND VALID_UPTO >= '"+tranDate+"'";
					rs = stmt.executeQuery(sql);
					if (!rs.next())
					{
						return -1;	 // Denotes Error
					}
					else
					{
						rate = rs.getDouble(1);
					}
				}
				else
				{
					rate = rs.getDouble(1);
				}
			}
			catch(SQLException ie)
			{
				System.out.println("Exception: PODProcess: getPriceListRate: type D: ==>"+ie);
				ie.printStackTrace();
				return -1;
			}
		}
		else if (type.equalsIgnoreCase("B"))	// Batch Price
		{
			rate = 0;
			sql = "SELECT RATE FROM PRICELIST WHERE PRICE_LIST = '"+priceList+"' AND ITEM_CODE = '"+itemCode+"' AND LIST_TYPE = 'B' AND LOT_NO__FROM <= '"+lotNo+"' AND LOT_NO__TO >= '"+lotNo+"' AND EFF_FROM <= sysdate AND VALID_UPTO >= sysdate";
			System.out.println("price list sql---->>["+sql+"]");
			rs = stmt.executeQuery(sql);
			System.out.println("After excution-----------");
			if (!rs.next())
			{
				return -1;	 // Denotes Error
			}
			else
			{
				rate = rs.getDouble(1);
			}
		}
		else if (type.equalsIgnoreCase("M") || type.equalsIgnoreCase("N"))	// Discount Price
		{
			sql = "SELECT RATE FROM PRICELIST WHERE PRICE_LIST = '"+priceList+"' AND ITEM_CODE = '"+itemCode+"' AND LIST_TYPE = '"+type+"' AND EFF_FROM <= '"+tranDate+"' AND VALID_UPTO >= '"+tranDate+"'";
			rs = stmt.executeQuery(sql);
			if (!rs.next())
			{
				return -1;	 // Denotes Error
			}
			else
			{
				rate = rs.getDouble(1);
			}
		}
		else if (type.equalsIgnoreCase("I"))	// Inventory
		{
			
			rate = 0;
			//	To check is lot no is null or not and fetch accordingly
			if (lotSl.trim().length() == 0 || lotSl.length() == 0)
			{
				sql = "SELECT RATE FROM STOCK WHERE ITEM_CODE = '"+itemCode+"' AND SITE_CODE = '"+siteCode+"' AND LOC_CODE = '"+locCode+"' AND LOT_NO = '"+lotNo+"'";
				rs = stmt.executeQuery(sql);
				if (!rs.next())
				{
					return -1;
				}
				else
				{
					rate = rs.getDouble(1);
				}
			}
			else
			{
				sql = "SELECT RATE FROM STOCK WHERE ITEM_CODE = '"+itemCode+"' AND SITE_CODE = '"+siteCode+"' AND LOC_CODE = '"+locCode+"' AND LOT_NO = '"+lotNo+"' AND LOT_SL '"+lotSl+"'";
				rs = stmt.executeQuery(sql);
				if (rs.next())
				{
					rate = rs.getDouble(1);
				}
				else
				{
					return -1;
				}
			}
		}
		if (rs != null) 
		{
			rs.close();
		}
	}
	catch(Exception e)
	{
		System.out.println("Exception :PODProcess :getPriceListRate:" + e.getMessage() + ":");
		e.printStackTrace();
		throw new ITMException(e);
	}
	
	return rate;
}




}
