package ibase.webitm.ejb.dis;

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

import java.rmi.RemoteException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import 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
{
	public String wfValData(String xmlString, String xmlString1, String xmlString2,String objContext, String editFlag, String xtraParams) throws RemoteException,ITMException
	{
		System.out.println("------------ wfvalData method called-----------------");
		System.out.println("xtraParams --->>>  [["+xtraParams+"  ]]");
		String errString = null;
		Document dom = null;
		Document dom1 = null;
		Document dom2 = null;
		try
		{
			dom = parseString(xmlString);
			dom1 = parseString(xmlString1);
			dom2 = parseString(xmlString2);
			errString = wfValData(dom,dom1,dom2,objContext,editFlag,xtraParams);
			
		}
		catch(Exception e)
		{
			System.out.println("Exception : ProofOfDelivery.java : wfValData(String xmlString) : ==>\n"+e.getMessage());
			throw new ITMException(e);
		}
		
		return errString;
	} //end of wfValData 
	
	
	public String wfValData(Document dom, Document dom1, Document dom2,String objContext, String editFlag, String xtraParams) throws RemoteException,ITMException
	{
		NodeList parentNodeList = null;
		NodeList childNodeList = null;
		Node parentNode = null;
		Node childNode = null;
		int ctr=0;
		String childNodeName = null;
		String errString = "",errorType="",loginSite="";
		Connection conn = null;
		PreparedStatement pstmt;
		ResultSet rs=null;
		int cnt=0;
		int currentFormNo=0;
		int childNodeListLength;
		ConnDriver connDriver = new ConnDriver();
		//ITMDBAccessEJB itmDBAccessEJB = new ITMDBAccessEJB();
		ArrayList <String> errList = new ArrayList<String>();
		ArrayList <String>errFields = new ArrayList <String> ();
		StringBuffer errStringXml = new StringBuffer("<?xml version=\"1.0\"?>\r\n<Root><Errors>"); 
		
		try
		{

			String userId=null,errCode=null;
			GenericUtility genericUtility = GenericUtility.getInstance();
			conn = connDriver.getConnectDB("DriverITM");
			
			userId = genericUtility.getValueFromXTRA_PARAMS(xtraParams,"loginCode");
			System.out.println("user ID form XtraParam : "+userId);
			
			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 invoiceID="",invoiceConf="";
						invoiceID = genericUtility.getColumnValue("invoice_id",dom);
						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("invoiceConf--------->> 12121: "+invoiceConf);
							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() );
							}
							if(cnt1 > 0)
							{
								//If POD for invoiceID already exist in spl_sales_por_hdr then show error 
								errCode = "VTINVIDAC"; 
								errList.add( errCode );
								errFields.add( childNodeName.toLowerCase() );
							}
							if(cnt == 0)
							{
								errCode = "VTINVIDNE"; 
								errList.add( errCode );
								errFields.add( childNodeName.toLowerCase() );
							}
						}
					}						
					
					if (childNodeName.equalsIgnoreCase("wf_status"))
					{

						String wfStatus="";
						wfStatus = genericUtility.getColumnValue("wf_status",dom);
						wfStatus= wfStatus==null ?"":wfStatus;
						if (wfStatus.trim().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="";
						qtyResale=genericUtility.getColumnValue("quantity__resale", dom);
						qtyInv=genericUtility.getColumnValue("quantity__inv", dom);
						qtyInv=qtyInv == null ? "0.0" : qtyInv;
						System.out.println("quantity__resale---->>"+qtyResale);
						System.out.println("qtyInv---->>"+qtyInv);
						if(qtyResale !=null && qtyResale.trim().length() >0)
						{						
							double qtyInvD=0.0,qtyResaleD=0.0;
							qtyInvD=Double.parseDouble(checkSpaceNull(qtyInv));
							qtyResaleD=Double.parseDouble(qtyResale);
							System.out.println("qtyInvD---->>"+qtyInvD+"  qtyResaleD"+qtyResaleD);
							if(qtyResaleD > qtyInvD)
							{
								//POD quantity can not be greater than invoice quantity
								errCode = "VTPODGRT"; 
								errList.add( errCode );
								errFields.add( childNodeName.toLowerCase() );
							}
						}
					}					
				
				}
				break;
											
			} //end switch
			
			int errListSize = errList.size();
			cnt =0;
			String errFldName = null;
			if ( errList != null && errListSize > 0 )
			{
				for (cnt = 0; cnt < errListSize; cnt++ )
				{
					errCode = (String)errList.get(cnt);
					errFldName = (String)errFields.get(cnt);
					System.out.println("errCode .........."+errCode);
					errString = getErrorString( errFldName, errCode, userId );
					errorType =  errorType( conn, errCode );
					if ( errString.length() > 0)
					{
						String bifurErrString = errString.substring( errString.indexOf("<Errors>") + 8,errString.indexOf("<trace>"));
						bifurErrString =bifurErrString+errString.substring( errString.indexOf("</trace>") + 8,errString.indexOf("</Errors>"));
						errStringXml.append(bifurErrString);
						System.out.println("errStringXml .........."+errStringXml);
						errString = "";
					}
					if ( errorType.equalsIgnoreCase("E"))
					{
						break;
					}
				}
				errList.clear();
				errList = null;
				errFields.clear();
				errFields = null;
				
				errStringXml.append("</Errors></Root>\r\n");
			}
			else
			{
				errStringXml = new StringBuffer( "" );
			}
		
		
			
		} //end try
		catch (SQLException se) {
			System.out.println("SQLException ::"+se);
			se.printStackTrace();
            throw new ITMException(se);
		}
		catch(Exception e)
		{
			System.out.println("Exception ::"+e);
			e.printStackTrace();
            throw new ITMException(e);
		}
		finally
		{
			try
			{
				if(conn!=null)
				{
					conn.close();
					conn = null;
				}
				if(connDriver!=null){
					connDriver = null;
				}	
				
			}
			catch(Exception d)
			{
			  d.printStackTrace();
			}
		}
		
		System.out.println("ErrString ::[ "+errStringXml.toString()+" ]");
		return errStringXml.toString();
		
	}
	
	public String itemChanged(String xmlString,String xmlString1,String xmlString2, String objContext, String currentColumn, String editFlag, String xtraParams) throws RemoteException,ITMException
	{
		System.out.println("------------------ itemChanged called------------------");
		Document dom = null;
		Document dom1 = null;
		Document dom2 = null;
		String valueXmlString = "";
		try
		{   
			dom = parseString(xmlString);
			dom1 = parseString(xmlString1);
			dom2 = parseString(xmlString2);
			valueXmlString = itemChanged(dom,dom1,dom2,objContext,currentColumn,editFlag,xtraParams);
		}
		catch(Exception e)
		{
			System.out.println("Exception : [ProofOFDelivery ][itemChanged(String,String)] :==>\n"+e.getMessage());
		}
		return valueXmlString;
	}
	
	public String itemChanged(Document dom, Document dom1,Document dom2, String objContext, String currentColumn, String editFlag, String xtraParams) throws RemoteException,ITMException
	{
		System.out.println("@@@@@@@ itemChanged called");
		StringBuffer valueXmlString = new StringBuffer();
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		GenericUtility genericUtility = GenericUtility.getInstance();
		ConnDriver connDriver = new ConnDriver();
		int currentFormNo =0;
		String columnValue = null;
		NodeList parentNodeList = null;
		NodeList childNodeList = null;
		Node parentNode = null;
		Node childNode = null;
		int ctr = 0;
		try
		{			
			conn = connDriver.getConnectDB("DriverITM");
			conn.setAutoCommit(false);
			connDriver=null;
			if(objContext != null && objContext.trim().length()>0)
			{
				currentFormNo = Integer.parseInt(objContext.trim());
			}
			valueXmlString = new StringBuffer("<?xml version=\"1.0\"?><Root><header><editFlag>");
			valueXmlString.append(editFlag).append("</editFlag></header>");
			System.out.println("-------- currentFormNo : "+currentFormNo);
			
			switch(currentFormNo)
			{	
				case 1 :
				valueXmlString.append("<Detail1>");				
				System.out.println("[" + currentColumn + "] ==> '" + columnValue + "'");
				System.out.println("editFlag =>" +editFlag);
				
				if( currentColumn.trim().equalsIgnoreCase( "itm_default" ) )
				{
					System.out.println("------------in itm_default--------->");
					String confirm="N",wfStatus="O";
					 Calendar currentDate = Calendar.getInstance();
					 SimpleDateFormat simpleDateFormatObj = new SimpleDateFormat(genericUtility.getApplDateFormat());
					 String tranDate = simpleDateFormatObj.format(currentDate.getTime());						 
					 valueXmlString.append("<tran_date>").append("<![CDATA[" + tranDate + "]]>").append("</tran_date>");
					 valueXmlString.append("<confirmed>").append("<![CDATA[" + confirm + "]]>").append("</confirmed>");
					 valueXmlString.append("<wf_status>").append("<![CDATA[" + wfStatus + "]]>").append("</wf_status>");
				}			
									
				if((currentColumn.trim().equalsIgnoreCase("invoice_id")))
				{
					String invoiceID ="",siteCode="",custCode="",sql="",siteDescr="",custName="",currencyCode="",prdCode="";
					double amount=0.0,exchRate=0.0;
					invoiceID = genericUtility.getColumnValue("invoice_id",dom);
					System.out.println("invoiceID -----   :->> "+invoiceID);
	
					if(invoiceID != null && invoiceID.trim().length()>0 )
					{						
						sql="select site_code,cust_code,net_amt,curr_code,exch_rate,prd_code from invoice where invoice_id = ?";
						pstmt=conn.prepareStatement(sql);
						pstmt.setString(1, invoiceID);
						rs=pstmt.executeQuery();
						if(rs.next())
						{
							siteCode=rs.getString(1)==null ? "" :rs.getString(1);
							custCode=rs.getString(2)==null ? "" : rs.getString(2);
							amount=rs.getDouble(3);
							currencyCode=rs.getString(4)==null ? "" : rs.getString(4);
							exchRate=rs.getDouble(5);	
							prdCode=rs.getString(6)==null ? "" :rs.getString(6);
						}
						
						rs.close();pstmt.close();
						rs=null;pstmt=null;
						if(siteCode.trim().length() > 0)
						{
							siteDescr=getNameOrDescrForCode(conn, "SITE", "DESCR", "SITE_CODE", siteCode);
						}
						
						if(custCode.trim().length() > 0)
						{
							custName=getNameOrDescrForCode(conn, "customer", "cust_name", "cust_code", custCode);
						}
						System.out.println("period code--->>"+prdCode);
						valueXmlString.append("<site_code>").append("<![CDATA[" + siteCode + "]]>").append("</site_code>");
						valueXmlString.append("<cust_code>").append("<![CDATA[" + custCode + "]]>").append("</cust_code>");
						valueXmlString.append("<amount>").append("<![CDATA[" + amount + "]]>").append("</amount>");
						valueXmlString.append("<exch_rate>").append("<![CDATA[" + exchRate + "]]>").append("</exch_rate>");
						valueXmlString.append("<curr_code>").append("<![CDATA[" + currencyCode + "]]>").append("</curr_code>");
						valueXmlString.append("<site_descr>").append("<![CDATA[" + siteDescr + "]]>").append("</site_descr>");						
						valueXmlString.append("<cust_name>").append("<![CDATA[" + custName+ "]]>").append("</cust_name>");
					
						valueXmlString.append("<prd_code>").append("<![CDATA[" + prdCode+ "]]>").append("</prd_code>");
						System.out.println("siteCode : "+siteCode+" cust code : "+custCode+" amount : "+amount+"exchnage rate  : "+exchRate);					
					}
					else
					{
						//valueXmlString.append("<project_descr>").append("").append("</project_descr>");
						valueXmlString.append("<site_code>").append("<![CDATA[]]>").append("</site_code>");
						valueXmlString.append("<cust_code>").append("<![CDATA[]]>").append("</cust_code>");
						valueXmlString.append("<amount>").append("<![CDATA[]]>").append("</amount>");
						valueXmlString.append("<exch_rate>").append("<![CDATA[]]>").append("</exch_rate>");
						valueXmlString.append("<curr_code>").append("<![]]>").append("</curr_code>");
						valueXmlString.append("<site_descr>").append("<![CDATA[]]>").append("</site_descr>");						
						valueXmlString.append("<cust_name>").append("<![CDATA[]]>").append("</cust_name>");
						
					}
				}					
				
				    valueXmlString.append("</Detail1>");
				  break;
				case 2:
					parentNodeList = dom.getElementsByTagName("Detail2");
					parentNode = parentNodeList.item(0);
					childNodeList = parentNode.getChildNodes();
					valueXmlString.append("<Detail2>");
					int childListLength = childNodeList.getLength();					
					if (currentColumn.trim().equals("quantity__resale"))
					{
						System.out.println("-------item chnage quantity__resale-------------");
						String rateResale="",rateInv="",quantityInv="",podQty="";
						double rateResaleD=0.0,rateInvoiceD=0.0,diffAmt=0.0,quantityInvD=0.0,podQtyD=0.0;
						double actualPrice=0.0,PODprice=0.0;
					
						rateInv=genericUtility.getColumnValue("rate__inv",dom);
						quantityInv=genericUtility.getColumnValue("quantity__inv",dom);
						
						rateResale=genericUtility.getColumnValue("rate__resale",dom);					
						podQty=genericUtility.getColumnValue("quantity__resale",dom);
						
						
						rateInvoiceD=Double.parseDouble(checkSpaceNull(rateInv));						
						quantityInvD=Double.parseDouble(checkSpaceNull(quantityInv));
						actualPrice= rateInvoiceD * quantityInvD;
						System.out.println("rateInvoiceD ["+rateInvoiceD+"] *  quantityInvD["+quantityInvD+"] = "+actualPrice);
						
						rateResaleD=Double.parseDouble(checkSpaceNull(rateResale));
						podQtyD=Double.parseDouble(checkSpaceNull(podQty));
						System.out.println("rateResaleD -->>: "+rateResaleD+" podQtyD : "+podQtyD);
						PODprice= rateResaleD * podQtyD;
						
						System.out.println("POD Amount ------>>"+PODprice);
						
						diffAmt= PODprice - actualPrice ;
						System.out.println("Diff Amount ------>>"+PODprice);
						valueXmlString.append("<amount>").append("<![CDATA["+diffAmt+"]]>").append("</amount>");	
						
					}
					valueXmlString.append("</Detail2>");
				}// end switch
			valueXmlString.append("</Root>");			
			
			
		} //end try-------------------------------------------------------------
		catch(Exception e)
		{
			e.printStackTrace();
			System.out.println("Exception ::"+e.getMessage());
			throw new ITMException(e);
		}
		finally
		{
			try
			{
				if(conn!=null)
				{
					conn.close();
					conn = null;
				}
				if(rs != null)
				{
					rs.close();
					rs = null;
				}
				if(pstmt != null)
				{
					pstmt.close();
					pstmt = null;
				}				
			}
			catch(Exception d)
			{
			  d.printStackTrace();
			}			
		}
		
		return valueXmlString.toString();
	}
	private String checkSpaceNull(String str)
	{
		if(str == null || str.trim().length() == 0)
		{
			str="0.0";
		}
		return str;
	}
	
	private int getDBRowCount(Connection conn, String table_name, String whrCondCol, String whrCondVal)
	{
			int count=-1;
			
			if(conn!=null){
				
				ResultSet rs=null;
				PreparedStatement pstmt = null;
				
				String sql="select count(1) from "+table_name+" where "+whrCondCol+" = ?";
				System.out.println("SQL in getDBRowCount method : "+sql);
				try
				{
					pstmt = conn.prepareStatement(sql);
					pstmt.setString(1,whrCondVal);
					rs = pstmt.executeQuery();
					if(rs.next())
					{
						count = rs.getInt(1);
					}
				}
				catch(SQLException e)
				{
					System.out.println("SQL Exception In getDBRowCount method of SalesEnquiry Class : "+e.getMessage());
					e.printStackTrace();
				}
				catch(Exception ex)
				{
					System.out.println("Exception In getDBRowCount method of SalesEnquiry 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 SalesEnquiry.getDBRowCount() method is null");
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
				
		return count;
	}
	
	
	private String errorType( Connection conn , String errorCode )
	{
		String msgType = "";
		PreparedStatement pstmt = null ; 
		ResultSet rs = null;
		try
		{			
			String  sql = " SELECT MSG_TYPE FROM MESSAGES WHERE MSG_NO =   ? ";
			
			pstmt = conn.prepareStatement( sql );			
			pstmt.setString(1, checkNull(errorCode));			
			rs = pstmt.executeQuery();
			while( rs.next() )
			{
				msgType = rs.getString("MSG_TYPE");
			}			
		}
		catch (Exception ex)
		{
			ex.printStackTrace();
		}		
		finally
		{
			try
			{
				if ( rs != null )
				{
					rs.close();
					rs = null;
				}
				if ( pstmt != null )
				{
					pstmt.close();
					pstmt = null;
				}
			}
			catch ( Exception e )
			{
				e.printStackTrace();
			}
		}		
		return msgType;
	}
	private String checkNull(String input)	
	{
		if (input == null)
		{
			input="";
		}
		return input;
	}
	
	
	private String getNameOrDescrForCode(Connection conn, String table_name, String descr_col_name,String whrCondCol, String whrCondVal)
	{
			String descr = null;
			
			if(conn!=null){
				
				ResultSet rs=null;
				PreparedStatement pstmt = null;
				
				String sql="SELECT "+descr_col_name+" FROM "+table_name+" WHERE "+whrCondCol+" = ?";
				
				System.out.println("SQL in getNameOrDescrForCode method : "+sql);
				try
				{
					pstmt = conn.prepareStatement(sql);
					pstmt.setString(1,whrCondVal);
					rs = pstmt.executeQuery();
					if(rs.next())
					{
						descr = rs.getString(descr_col_name);
					}
					
					descr=descr==null ?"" :descr;
				}
				catch(SQLException e)
				{
					System.out.println("SQL Exception In getNameOrDescrForCode method of ProofOfDelivery Class : "+e.getMessage());
					e.printStackTrace();
				}
				catch(Exception ex)
				{
					System.out.println("Exception In getNameOrDescrForCode method of ProofOfDelivery Class : "+ex.getMessage());
					ex.printStackTrace();
				}finally{
					
					try{
						
						if(pstmt!=null){
							pstmt.close();
							pstmt = null;
						}
						if(rs!=null){
							rs.close();
							rs = null;
						}
					}catch (SQLException se) {
						se.printStackTrace();
					}
				}
			}else{
				try {
					throw new SQLException("Connection passed to ProofOfDelivery.getNameOrDescrForCode() method is null");
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
				
		return descr;
	}
	
	private void getStockistPercentage(String allowPercentage,double rateInvoice,double qtyInvoice,double podRate,double podQty)
	{
		System.out.println("-------- in getDebitNoteAmount method -------------");
		double allowPercentageD=0.0,invoiceAmt=0.0,stockistAmt=0.0,actualAllowAmt=0.0,percentageAmtInv=0.0,debitNoteCaclAmount=0.0;
				
					
		allowPercentage=allowPercentage==null ? "0.0" :allowPercentage;			
		allowPercentageD=Double.parseDouble(allowPercentage);	
		
		invoiceAmt= rateInvoice * qtyInvoice;//10 *100
		percentageAmtInv= invoiceAmt * allowPercentageD/100; //1000 * 10/100
		actualAllowAmt= invoiceAmt + percentageAmtInv; //1100
		System.out.println("percentageAmtInv------>>"+percentageAmtInv);
		System.out.println("getDebitNoteAmount -->> actualAllowAmt---------->>"+actualAllowAmt);
		stockistAmt= podRate * podQty; //12 * 100 = 1200
		
		if(stockistAmt > actualAllowAmt)			
			debitNoteCaclAmount= stockistAmt - actualAllowAmt; //1200 -1100 =100			
		else
			debitNoteCaclAmount=0.0;
		
		
		
		System.out.println("--------debitNoteCaclAmount------ :Debit note amount "+debitNoteCaclAmount);
		
		
	}

}
