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.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.util.ArrayList;
import java.util.Date;
import javax.ejb.Stateless;
import org.w3c.dom.Document;

@Stateless
public class PODProcess extends ProcessEJB implements PODProcessLocal,PODProcessRemote
{	 
	  ArrayList<String> podRemainInvIDList=new ArrayList<String>(); 
	 
	   
	 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>();
		Connection conn = null;
		ConnDriver connDriver = null;
		GenericUtility genericUtility = null;
		ProofOfDelivery podObject=null;
		ITMDBAccessEJB itmDBAccessEJB = 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();
			itmDBAccessEJB = 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;
			}
		
		}
		//%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
		if(custCodeFrm.length() > 0 && custCodeTo.length() > 0 && siteCode.length() > 0){			
		sql="select invoice_id from invoice where cust_code__bil between ? and  ? and site_code = ? "
				+ "and sysdate - conf_date > 517";
		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. 
		}			
		System.out.println("podProcessInvIDList---->>["+podProcessInvIDList+"]");
		System.out.println("Size of podProcessInvIDList---->>["+podProcessInvIDList.size()+"]");
		podNotConfList=getNotConfirmedInvIdFromHdr(conn);
		if(podNotConfList.contains("Error"))
		{
			//handle error if array list contain error. 
		}
		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);
				}
			}
		}
		
		podProcessInvIDList.clear();
		podProcessInvIDList.add("618ID00040");
		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+"]");
		
		//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.
		
		String str=schemeApply(conn, podProcessInvIDList,priceList,xtraParams,siteCode);
		
		
		
		}
		}//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 schemeApply(Connection conn,ArrayList<String> podProcessInvIDListL,String priceListL,String xtraParams,String siteCode)
{
	ResultSet rs=null;
	PreparedStatement pstmt=null;
	ProofOfDelivery pod=null;
	ProofOfDeliveryConf podC=null;
	String sql="",lotNo="",lotSl="",itemCode="",itemCodeOrd="",itemActive="",detail2xmlString="";
	double invRate=0,invQty=0,discount=0,priceListRate=0,detail3Amt=0,detail3AmtT=0,debitNoteAmtDetail=0;
	double debitNoteAmtHdr=0;
	int lineNotrace=0,lineNo=0;
	pod=ProofOfDelivery.getInstance();
	podC=ProofOfDeliveryConf.getInstance();
	try{
	System.out.println("priceListL------>>["+priceListL+"]");	
	for(String invoiceId :podProcessInvIDListL)
	{
		
	sql="select rate,quantity,lot_no,lot_sl,item_code,inv_line_no,discount,item_code__ord " +
			"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();
		
		
		//Discount need discussion -----------------@@@@@--------
		if(discount > 0)
		{
			invRate=invRate - (invRate * discount/100);
			System.out.println("Discount for invoice id--->>["+invoiceId+"]"+"inv Rate-->>["+invRate+"]");
		}
		
		//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)
		{
			return "ITEMNOTACTIVE"; // item code not active
		}
		
		//check if scheme is apply or not
		if(isSchemeApply(conn,itemCode))
		{
			//java code if scheme is apply
			System.out.println("-------Scheme is apply -----------");
		}
		else{
			System.out.println("-------Scheme not apply -----------");
			if(priceListL.length() <=0)
			{
				return "PRICELIST"; //priceList not define in site master. 
			}
			else{
				java.util.Date tranDate=new Date();
				priceListRate=getPriceListRate(priceListL, tranDate,itemCode,lotNo,"", conn);				
				System.out.println("priceListRate----->>["+priceListRate+"]");
				if(priceListRate < 0)
				{
					// return error messages  
				}
				else
				{
					debitNoteAmtDetail=podC.getDebitNoteAmount(invRate,invQty);						
					debitNoteAmtHdr=debitNoteAmtHdr+debitNoteAmtDetail;
					detail3Amt= (priceListRate - invRate) * invQty;
					System.out.println("Debit note amount-------->>["+detail3Amt+"]");
					detail3AmtT=detail3AmtT + detail3Amt;
					lineNo++;
					//detail2xmlString=detail2xmlString + podC.getDetailsXmlString(invoiceId,lineNotrace,lineNo,invRate,invQty,lotNo,lotSl,itemCode, detail3Amt);
					
					
				}
			} //end priceListL if
			
			
		} //end isSchemeApply
		
		
		
	} //end while invoice trace
	
	System.out.println("Final detail2xmlString----->>["+detail2xmlString+"]");
	System.out.println("Final debitNoteAmtHdr---------->> : "+debitNoteAmtHdr);
	System.out.println("Final detail3AmtT in debit note table---------->> : "+detail3AmtT);
	//errString=genDebitNote(tranId,invoiceId,custCode,siteCode,xtraParams,0,currCode,ExchRate,detail2xmlString,debitNoteAmtHdr,detail3AmtT,conn);
	} //end invoice id for loop
   }catch(Exception e)
   {
	   System.out.println("Exception : ");
	   e.printStackTrace();
   }
	return "";
}
// 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 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);
					
				}
				
			}
			
		}
		
		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;
	
}
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: DistDiscountEJB: pickRate: 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
		{
			/*	------------------Code not transferred, it's calling an external function
			siteCode = 
			locCode = 
			lotNo = 
			lotSl = 
			-------------------*/
			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;
}




}
