package ibase.webitm.ejb.fin;

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.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Date;

import javax.ejb.Stateless; // added for ejb3
import javax.swing.text.html.HTMLDocument.HTMLReader.PreAction;

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

@Stateless
public class StockValAdjTran extends ValidatorEJB implements StockValAdjTranLocal,StockValAdjTranRemote{

	GenericUtility genericUtility = GenericUtility.getInstance(); 
	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("Xml String : ["+xmlString+"]");
		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 : StockValAdjTran.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 = "";
		Connection conn = null;
		PreparedStatement pstmt = null ;
		ResultSet rs = null;
		String sql = "";
		String userId="",siteCode = "",errCode="",itemser="",pricelist="",reasCode="",remarks="",amtOld="",cctrCodeInv="",cctrCodeInvrev="";
		String tranId = "",keyFlag="",tranDate = "",effDate="",itemCode="",locCode="",lotNo="",lotSl="",quantity="",acctCodeInv="",acctCodeInvrev="";
		int cnt=0;
		int currentFormNo=0;
		int childNodeListLength;
		ConnDriver connDriver = new ConnDriver();
		
		try
		{
			conn = connDriver.getConnectDB("DriverITM");
			connDriver = null;
			userId = 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();
					
					if(childNodeName.equalsIgnoreCase("tran_id"))
					{
						tranId = genericUtility.getColumnValue("tran_id",dom);
						
						sql = "select key_flag from transetup where tran_window = ?";
						pstmt=conn.prepareStatement(sql);
						pstmt.setString(1,"w_stockvaladjtran");
						rs = pstmt.executeQuery();
						if(rs.next())
						{
							keyFlag = rs.getString(1) == null ? "":rs.getString(1);
						}
						pstmt.close();
						rs.close();
						pstmt = null;
						rs = null;
						
						
						if(keyFlag.equalsIgnoreCase("M"))
						{
							if (tranId == null || tranId.trim().length() == 0)
							{
								errCode = "STKVALTRNU";
								errString = getErrorString("tran_id",errCode,userId);
								break;
							}
							else
							{

								sql = "select count(1) from stock_val_adj where tran_id = ?";
								pstmt=conn.prepareStatement(sql);
								pstmt.setString(1,tranId);
								rs = pstmt.executeQuery();
								if(rs.next())
								{
									cnt = rs.getInt(1);
								}
								pstmt.close();
								rs.close();
								pstmt = null;
								rs = null;
								if(cnt > 0)
								{
									errCode = "STKVALTRNE";
									errString = getErrorString("tran_id",errCode,userId);
									break;
								}
							
							}
						}
					}
					
					else if(childNodeName.equalsIgnoreCase("tran_date"))
					{
						tranDate = genericUtility.getColumnValue("tran_date",dom);
						if (tranDate == null || tranDate.trim().length() == 0 )
						{
							errCode = "STKVALTRDA";
							errString = getErrorString("tran_date",errCode,userId);
							break;
						}
					}
					
					else if(childNodeName.equalsIgnoreCase("eff_date"))
					{
						effDate = genericUtility.getColumnValue("eff_date",dom);
						if (effDate == null || effDate.trim().length() == 0 )
						{
							errCode = "STKVALTRED";
							errString = getErrorString("eff_date",errCode,userId);
							break;
						}
					}
					
					else if(childNodeName.equalsIgnoreCase("site_code"))
					{
						siteCode = genericUtility.getColumnValue("site_code",dom);
						if (siteCode == null || siteCode.trim().length() == 0 )
						{
							errCode = "VTSITECNE";
							errString = getErrorString("site_code",errCode,userId);
							break;
						}
						else
						{
							sql = "select count(1) from site where site_code = ?";
							pstmt=conn.prepareStatement(sql);
							pstmt.setString(1,siteCode);
							rs = pstmt.executeQuery();
							if(rs.next())
							{
								cnt = rs.getInt(1);
							}
							pstmt.close();
							rs.close();
							pstmt = null;
							rs = null;
							if(cnt == 0)
							{
								errCode = "VTSITENEX";
								errString = getErrorString("site_code",errCode,userId);
								break;
							}
						}
					}
					else if(childNodeName.equalsIgnoreCase("item_ser"))
					{
						itemser = genericUtility.getColumnValue("item_ser",dom);
						if (itemser == null || itemser.trim().length() == 0 )
						{
							errCode = "VTISERFNN";
							errString = getErrorString("item_ser",errCode,userId);
							break;
						}
						else
						{

							sql = "select count(1) from stock where item_ser = ?";
							pstmt=conn.prepareStatement(sql);
							pstmt.setString(1,itemser);
							rs = pstmt.executeQuery();
							if(rs.next())
							{
								cnt = rs.getInt(1);
							}
							pstmt.close();
							rs.close();
							pstmt = null;
							rs = null;
							if(cnt == 0)
							{
								errCode = "VTISERFNE";
								errString = getErrorString("item_ser",errCode,userId);
								break;
							}
						
						}
					}
					else if(childNodeName.equalsIgnoreCase("price_list"))
					{
						pricelist = genericUtility.getColumnValue("price_list",dom);
						if (pricelist == null || pricelist.trim().length() == 0 )
						{
							errCode = "STKVALPRLI";
							errString = getErrorString("price_list",errCode,userId);
							break;
						}else
						{
                            sql = "select count(1) from pricelist where price_list = ?";
							pstmt=conn.prepareStatement(sql);
							pstmt.setString(1,pricelist);
							rs = pstmt.executeQuery();
							if(rs.next())
							{
								cnt = rs.getInt(1);
							}
							pstmt.close();
							rs.close();
							pstmt = null;
							rs = null;
							if(cnt == 0)
							{
								errCode = "STKVALPRAE";
								errString = getErrorString("price_list",errCode,userId);
								break;
							}
						   else
							{
						    sql = "select count(1) from site where site_code = ? and price_list = ?";
							pstmt=conn.prepareStatement(sql);
							pstmt.setString(1,siteCode);
							pstmt.setString(2,pricelist);
							rs = pstmt.executeQuery();
							if(rs.next())
							{
								cnt = rs.getInt(1);
							}
							pstmt.close();
							rs.close();
							pstmt = null;
							rs = null;
							if(cnt == 0)
							{
								errCode = "VTPRCLNDS";
								errString = getErrorString("price_list",errCode,userId);
								break;
							}
							
							}
						}
					}
					
					else if(childNodeName.equalsIgnoreCase("reas_code"))
					{
						reasCode = genericUtility.getColumnValue("reas_code",dom);

						if (reasCode == null || reasCode.trim().length() == 0 )
						{
							errCode = "VTRESNCNN";
							errString = getErrorString("reas_code",errCode,userId);
							break;
						}
						else
						{
							sql = "select count(1) from gencodes where fld_name = ?";
							pstmt=conn.prepareStatement(sql);
							pstmt.setString(1,reasCode);
							rs = pstmt.executeQuery();
							if(rs.next())
							{
								cnt = rs.getInt(1);
							}
							pstmt.close();
							rs.close();
							pstmt = null;
							rs = null;
							if(cnt == 0)
							{              
								errCode = "VTRESNCNE";
								errString = getErrorString("reas_code",errCode,userId);
								break;
							}
						}

					}
					
					
				}
				
				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();
						System.out.println("value of child node : "+childNode);
						
						if (childNodeName.equalsIgnoreCase("item_code"))
						{
							itemCode = genericUtility.getColumnValue("item_code",dom);
						    itemser = genericUtility.getColumnValue("item_ser",dom1);
							if (itemCode == null || itemCode.trim().length() == 0 )
							{
								errCode = "STKVALITCO";
								errString = getErrorString("item_code",errCode,userId);
								break;
							}
							else
							{

								sql = "select count(1) from item where item_code = ?";
								pstmt=conn.prepareStatement(sql);
								pstmt.setString(1,itemCode);
								rs = pstmt.executeQuery();
								if(rs.next())
								{
									cnt = rs.getInt(1);
								}
								pstmt.close();
								rs.close();
								pstmt = null;
								rs = null;
								if(cnt == 0)
								{
									errCode = "STKVALITNE";
									errString = getErrorString("item_code",errCode,userId);
									break;
								}
								
								sql = "select count(1) from stock where item_code = ? and item_ser = ?";
								pstmt=conn.prepareStatement(sql);
								pstmt.setString(1,itemCode);
								pstmt.setString(2,itemser);
								rs = pstmt.executeQuery();
								if(rs.next())
								{
									cnt = rs.getInt(1);
								}
								pstmt.close();
								rs.close();
								pstmt = null;
								rs = null;
								
								if(cnt == 0)
								{
									errCode = "VTSTVALITS";
									errString = getErrorString("item_code",errCode,userId);
									break;
								}
							
							  }
						}
						else if(childNodeName.equalsIgnoreCase("loc_code"))
						{
							locCode = genericUtility.getColumnValue("loc_code",dom);
							
							if (locCode == null || locCode.trim().length() == 0 )
							{
								errCode = "STKVALLOCO";
								errString = getErrorString("loc_code",errCode,userId);
								break;
							}
							else
							{

	                            sql = "select count(1) from location where loc_code = ?";
								pstmt=conn.prepareStatement(sql);
								pstmt.setString(1,locCode);
								rs = pstmt.executeQuery();
								if(rs.next())
								{
									cnt = rs.getInt(1);
								}
								pstmt.close();
								rs.close();
								pstmt = null;
								rs = null;
								if(cnt == 0)
								{
									errCode = "STKVALLONE";
									errString = getErrorString("loc_code",errCode,userId);
									break;
								}
							   else
								{
							    sql = "select count(1) from stock where loc_code = ?";
								pstmt=conn.prepareStatement(sql);
								pstmt.setString(1,locCode);
								rs = pstmt.executeQuery();
								if(rs.next())
								{
									cnt = rs.getInt(1);
								}
								pstmt.close();
								rs.close();
								pstmt = null;
								rs = null;
								if(cnt == 0)
								{
									errCode = "STKLOCCONS";
									errString = getErrorString("loc_code",errCode,userId);
									break;
								}
								
								}
							
							}
							
						}
						else if(childNodeName.equalsIgnoreCase("lot_no"))
						{
							lotNo = genericUtility.getColumnValue("lot_no",dom);
							
							if (lotNo == null || lotNo.trim().length() == 0 )
							{
								errCode = "STKVALLONO";
								errString = getErrorString("lot_no",errCode,userId);
								break;
							}
							else
							{
								sql = "select count(1) from stock where lot_no = ?";
								pstmt=conn.prepareStatement(sql);
								pstmt.setString(1,lotNo);
								rs = pstmt.executeQuery();
								if(rs.next())
								{
									cnt = rs.getInt(1);
								}
								pstmt.close();
								rs.close();
								pstmt = null;
								rs = null;
								if(cnt == 0)
								{
									errCode = "STKVALLOTN";
									errString = getErrorString("lot_no",errCode,userId);
									break;
								}
							}
						}
						else if(childNodeName.equalsIgnoreCase("lot_sl"))
						{

							lotSl = genericUtility.getColumnValue("lot_sl",dom);
							
							if (lotSl == null || lotSl.trim().length() == 0 )
							{
								errCode = "STKVALLOSL";
								errString = getErrorString("lot_sl",errCode,userId);
								break;
							}
							else
							{
								sql = "select count(1) from stock where lot_sl = ?";
								pstmt=conn.prepareStatement(sql);
								pstmt.setString(1,lotSl);
								rs = pstmt.executeQuery();
								if(rs.next())
								{
									cnt = rs.getInt(1);
								}
								pstmt.close();
								rs.close();
								pstmt = null;
								rs = null;
								if(cnt == 0)
								{
									errCode = "STKVALLOSE";
									errString = getErrorString("lot_sl",errCode,userId);
									break;
								}
							}
						
						}
						
						else if(childNodeName.equalsIgnoreCase("quantity"))
						{
							quantity = genericUtility.getColumnValue("quantity",dom);
							
							if (quantity == null || quantity.trim().length() == 0 )
							{
								errCode = "STKVALQUAN";
								errString = getErrorString("quantity",errCode,userId);
								break;
							}
							
						}
						
						else if(childNodeName.equalsIgnoreCase("acct_code__inv"))
						{
							acctCodeInv = genericUtility.getColumnValue("acct_code__inv",dom);
							if (acctCodeInv == null || acctCodeInv.trim().length() == 0 )
							{
								errCode = "STKVALACIN";
								errString = getErrorString("acct_code__inv",errCode,userId);
								break;
							}
							else
							{

                                sql = "select count(1) from accounts where acct_code = ?";
								pstmt=conn.prepareStatement(sql);
								pstmt.setString(1,acctCodeInv);
								rs = pstmt.executeQuery();
								if(rs.next())
								{
									cnt = rs.getInt(1);
								}
								pstmt.close();
								rs.close();
								pstmt = null;
								rs = null;
								if(cnt == 0)
								{
									errCode = "STKVALACNE";
									errString = getErrorString("acct_code__inv",errCode,userId);
									break;
								}
							
							
							}
						}
						else if(childNodeName.equalsIgnoreCase("cctr_code__inv"))
						{
							cctrCodeInv = genericUtility.getColumnValue("cctr_code__inv",dom);
							
							if (cctrCodeInv == null || cctrCodeInv.trim().length() == 0 )
							{
								errCode = "STKVALCCCB";
								errString = getErrorString("cctr_code__inv",errCode,userId);
								break;
							}
							else
							{

								sql = "select count(1) from costctr where cctr_code = ?";
								pstmt=conn.prepareStatement(sql);
								pstmt.setString(1,cctrCodeInv);
								rs = pstmt.executeQuery();
								if(rs.next())
								{
									cnt = rs.getInt(1);
								}
								pstmt.close();
								rs.close();
								pstmt = null;
								rs = null;
								if(cnt == 0)
								{
									errCode = "STKVALCCNE";
									errString = getErrorString("cctr_code__inv",errCode,userId);
									break;
								}
							
							}
						}
						else if(childNodeName.equalsIgnoreCase("acct_code__inrev"))
						{
							

							acctCodeInvrev = genericUtility.getColumnValue("acct_code__inrev",dom);
							if (acctCodeInvrev == null || acctCodeInvrev.trim().length() == 0 )
							{
								errCode = "STKVACREN";
								errString = getErrorString("acct_code__inrev",errCode,userId);
								break;
							}
							else
							{

                                sql = "select count(1) from accounts where acct_code = ?";
								pstmt=conn.prepareStatement(sql);
								pstmt.setString(1,acctCodeInvrev);
								rs = pstmt.executeQuery();
								if(rs.next())
								{
									cnt = rs.getInt(1);
								}
								pstmt.close();
								rs.close();
								pstmt = null;
								rs = null;
								if(cnt == 0)
								{
									errCode = "STKVALACRE";
									errString = getErrorString("acct_code__inrev",errCode,userId);
									break;
								}
							
							
							}
										
							
						}
						else if(childNodeName.equalsIgnoreCase("cctr_code__inrev"))
						{
							

							cctrCodeInvrev = genericUtility.getColumnValue("cctr_code__inrev",dom);
							
							if (cctrCodeInvrev == null || cctrCodeInvrev.trim().length() == 0 )
							{
								errCode = "STKVACCNA";
								errString = getErrorString("cctr_code__inrev",errCode,userId);
								break;
							}
							else
							{

								sql = "select count(1) from costctr where cctr_code = ?";
								pstmt=conn.prepareStatement(sql);
								pstmt.setString(1,cctrCodeInvrev);
								rs = pstmt.executeQuery();
								if(rs.next())
								{
									cnt = rs.getInt(1);
								}
								pstmt.close();
								rs.close();
								pstmt = null;
								rs = null;
								if(cnt == 0)
								{
									errCode = "STKVCCNEA";
									errString = getErrorString("cctr_code__inrev",errCode,userId);
									break;
								}
							
							}
						
							
							
						}
						
						
					}
			}
			
		}catch(Exception e)
		{

			System.out.println("Exception ::"+e);
			e.printStackTrace();
			errString=e.getMessage();
            throw new ITMException(e);
		
		}
		finally
		{

			try
			{
				if(conn!=null)
				{
					if(rs != null )
					{
						rs.close();
						rs = null;
					}
					if(pstmt != null )pstmt.close();
					pstmt =null;
					conn.close();
				}
				conn = null;
			}
			catch(Exception d)
			{
			  d.printStackTrace();
			}
		
		}
		System.out.println("ErrString ::[ "+errString+" ]");
		return errString;
	}
	
	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 : [StockValAdjTran][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();
		NodeList parentNodeList = null;
		NodeList childNodeList = null;
		Node parentNode = null;
		Node childNode = null;
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null ;
		String childNodeName = null;
		GenericUtility genericUtility = GenericUtility.getInstance();
		ConnDriver connDriver = new ConnDriver();
		String siteCode = "",sql="",sitedesc="",priclit="";
		int currentFormNo =0;
		String columnValue="",chgTerm="",chgUser="",priceList="",tranDate="",listType="";
		String itemCode="",locCode="",lotNo="",lotsl="",oldRate="",quantity="",itmdesc="",loginSite="";
		Date utranDate;
		double rate = 0,conquan,conOldRate,oldAmount,amount,amtdiff;
		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>");
			chgTerm = genericUtility.getValueFromXTRA_PARAMS(xtraParams, "chg_termr");
			chgUser = genericUtility.getValueFromXTRA_PARAMS(xtraParams, "chg_user");
			System.out.println("-------- currentFormNo : "+currentFormNo);
			
			switch(currentFormNo)
			{
			case 1 :
				parentNodeList = dom.getElementsByTagName("Detail1");
				parentNode = parentNodeList.item(0);
				childNodeList = parentNode.getChildNodes();
				
				valueXmlString.append("<Detail1>");
				int childNodeListLength = childNodeList.getLength();
				
				if( currentColumn.trim().equalsIgnoreCase( "itm_default" ) )
				{
					System.out.println("------------@@@@@@@@in itm_default@@@@@@@@@---------");
					Calendar currentDate = Calendar.getInstance();
					SimpleDateFormat simpleDateFormatObj = new SimpleDateFormat(genericUtility.getApplDateFormat());
					String systemDate = simpleDateFormatObj.format(currentDate.getTime());
					System.out.println("Now the date is :=>  " + systemDate);
						
					loginSite = getValueFromXTRA_PARAMS(xtraParams, "loginSiteCode");
					loginSite = loginSite == null ? "":loginSite.trim();
					valueXmlString.append("<site_code>").append("<![CDATA[" + loginSite + "]]>").append("</site_code>");
					
					sql = "select descr,price_list from site where site_code = ?";
					pstmt=conn.prepareStatement(sql);
					pstmt.setString(1,loginSite);
					rs = pstmt.executeQuery();
					if(rs.next())
					{
						sitedesc = rs.getString(1) == null ? "":rs.getString(1);
						priclit = rs.getString(2) == null ? "":rs.getString(2);
					}
					pstmt.close();
					rs.close();
					pstmt = null;
					rs = null;
					valueXmlString.append("<site_descr>").append("<![CDATA[" + sitedesc + "]]>").append("</site_descr>");
					valueXmlString.append("<price_list>").append("<![CDATA[" + priclit + "]]>").append("</price_list>");
					
					
					
				}
				if((currentColumn.trim().equalsIgnoreCase("site_code")))
				{
					siteCode = genericUtility.getColumnValue("site_code",dom);
					siteCode = siteCode.trim();
					if(siteCode != null && siteCode.trim().length()>0 )
					{
					sql = "select descr from site where site_code = ?";
					pstmt=conn.prepareStatement(sql);
					pstmt.setString(1,siteCode);
					rs = pstmt.executeQuery();
					if(rs.next())
					{
						sitedesc = rs.getString(1) == null ? "":rs.getString(1);
					}
					pstmt.close();
					rs.close();
					pstmt = null;
					rs = null;
					valueXmlString.append("<site_descr>").append("<![CDATA[" + sitedesc + "]]>").append("</site_descr>");
				}
					else
					{
						valueXmlString.append("<site_descr>").append("<![CDATA[]]>").append("</site_descr>");
					}
				}
				
				
				valueXmlString.append("</Detail1>");
				break;
				
			case 2:
				
				valueXmlString.append("<Detail2>");	
				parentNodeList = dom.getElementsByTagName("Detail2");
				parentNode = parentNodeList.item(0);
				childNodeList = parentNode.getChildNodes();
				System.out.println("[" + currentColumn + "] ==> '" + columnValue + "'");
				
				siteCode = checkNull(genericUtility.getColumnValue("site_code",dom1));
				itemCode = checkNull(genericUtility.getColumnValue("item_code",dom));
				locCode  = checkNull(genericUtility.getColumnValue("loc_code",dom));
				lotNo = checkNull(genericUtility.getColumnValue("lot_no",dom));
				lotsl = checkNull(genericUtility.getColumnValue("lot_sl",dom));
				priceList = checkNull(genericUtility.getColumnValue("price_list",dom1));
				tranDate = checkNull(genericUtility.getColumnValue("tran_date",dom1));
				SimpleDateFormat simpleDateFormatObj = new SimpleDateFormat(genericUtility.getApplDateFormat());
				utranDate = simpleDateFormatObj.parse(tranDate);
				
				if (currentColumn.trim().equals("item_code"))
				{
					
					System.out.println("item code >>>>"+itemCode);
					if(itemCode != null && itemCode.length()>0 )
					{
					sql = "select descr from item where item_code = ?";
					pstmt=conn.prepareStatement(sql);
					pstmt.setString(1,itemCode);
					rs = pstmt.executeQuery();
					if(rs.next())
					{
						itmdesc = rs.getString(1) == null ? "":rs.getString(1);
					}
					pstmt.close();
					rs.close();
					pstmt = null;
					rs = null;
					
					if(itemCode.length() > 0 && locCode.length() > 0 && lotNo.length() > 0 && lotsl.length() > 0)
					{

						sql = "select rate from stock where site_code = ? and item_code = ? and loc_code = ? and lot_no = ? and lot_sl = ?";
						pstmt=conn.prepareStatement(sql);
						pstmt.setString(1,siteCode);
						pstmt.setString(2,itemCode);
						pstmt.setString(3,locCode);
						pstmt.setString(4,lotNo);
						pstmt.setString(5,lotsl);
						rs = pstmt.executeQuery();
						if(rs.next())
						{
							oldRate = rs.getString(1) == null ? "":rs.getString(1);
						}
						pstmt.close();
						rs.close();
						pstmt = null;
						rs = null;
						
						
						sql = "select list_type from pricelist where price_list = ?";
						pstmt=conn.prepareStatement(sql);
						pstmt.setString(1,siteCode);
						rs = pstmt.executeQuery();
						if(rs.next())
						{
							listType = rs.getString(1) == null ? "":rs.getString(1);
						}
						pstmt.close();
						rs.close();
						pstmt = null;
						rs = null;
						
						if(listType.trim().length() == 0)
						{
							sql = "select list_type from pricelist_mst where price_list = ?";
							pstmt=conn.prepareStatement(sql);
							pstmt.setString(1,siteCode);
							rs = pstmt.executeQuery();
							if(rs.next())
							{
								listType = rs.getString(1) == null ? "":rs.getString(1);
							}
							pstmt.close();
							rs.close();
							pstmt = null;
							rs = null;
						}
						
					    rate = getPriceListRate(priceList, utranDate, itemCode, lotNo, listType, conn);
						
					    if(rate == 0.0 || rate == 0 || rate == -1)
					    {
					    	rate = 0;
					    }
						valueXmlString.append("<rate__old>").append("<![CDATA[" + oldRate + "]]>").append("</rate__old>");
						valueXmlString.append("<rate>").append("<![CDATA[" + rate + " ]]>").append("</rate>");
						
						
					
					}else
					{
						valueXmlString.append("<rate__old>").append("<![CDATA[]]>").append("</rate__old>");
						valueXmlString.append("<rate>").append("<![CDATA[]]>").append("</rate>");
					}
					
					
					
					valueXmlString.append("<item_descr>").append("<![CDATA[" + itmdesc + "]]>").append("</item_descr>");
				}
					else
					{
						valueXmlString.append("<item_descr>").append("<![CDATA[]]>").append("</item_descr>");
					}
				}
				
//				if (currentColumn.trim().equals("loc_code"))
//				{
//					if(itemCode.length() > 0 && locCode.length() > 0 && lotNo.length() > 0 && lotsl.length() > 0)
//					{
//						sql = "select rate from stock where site_code = ? and item_code = ? and loc_code = ? and lot_no = ? and lot_sl = ?";
//						pstmt=conn.prepareStatement(sql);
//						pstmt.setString(1,siteCode);
//						pstmt.setString(2,itemCode);
//						pstmt.setString(3,locCode);
//						pstmt.setString(4,lotNo);
//						pstmt.setString(5,lotsl);
//						rs = pstmt.executeQuery();
//						if(rs.next())
//						{
//							oldRate = rs.getString(1) == null ? "":rs.getString(1);
//						}
//						pstmt.close();
//						rs.close();
//						pstmt = null;
//						rs = null;
//						
//						
//						sql = "select list_type from pricelist where price_list = ?";
//						pstmt=conn.prepareStatement(sql);
//						pstmt.setString(1,siteCode);
//						rs = pstmt.executeQuery();
//						if(rs.next())
//						{
//							listType = rs.getString(1) == null ? "":rs.getString(1);
//						}
//						pstmt.close();
//						rs.close();
//						pstmt = null;
//						rs = null;
//						
//						if(listType.trim().length() == 0)
//						{
//							sql = "select list_type from pricelist_mst where price_list = ?";
//							pstmt=conn.prepareStatement(sql);
//							pstmt.setString(1,siteCode);
//							rs = pstmt.executeQuery();
//							if(rs.next())
//							{
//								listType = rs.getString(1) == null ? "":rs.getString(1);
//							}
//							pstmt.close();
//							rs.close();
//							pstmt = null;
//							rs = null;
//						}
//						
//					    rate = getPriceListRate(priceList, utranDate, itemCode, lotNo, listType, conn);
//						
//						valueXmlString.append("<rate__old>").append("<![CDATA[" + oldRate + " ]]>").append("</rate__old>");
//						valueXmlString.append("<rate>").append("<![CDATA[" + rate + " ]]>").append("</rate>");
//						
//						
//					}else
//					{
//						valueXmlString.append("<rate__old>").append("<![CDATA[]]>").append("</rate__old>");
//						valueXmlString.append("<rate>").append("<![CDATA[]]>").append("</rate>");
//					}
//				}
				
				if (currentColumn.trim().equals("lot_no"))
				{
					

					if(itemCode.length() > 0 && locCode.length() > 0 && lotNo.length() > 0 && lotsl.length() > 0)
					{
						sql = "select rate from stock where site_code = ? and item_code = ? and loc_code = ? and lot_no = ? and lot_sl = ?";
						pstmt=conn.prepareStatement(sql);
						pstmt.setString(1,siteCode);
						pstmt.setString(2,itemCode);
						pstmt.setString(3,locCode);
						pstmt.setString(4,lotNo);
						pstmt.setString(5,lotsl);
						rs = pstmt.executeQuery();
						if(rs.next())
						{
							oldRate = rs.getString(1) == null ? "":rs.getString(1);
						}
						pstmt.close();
						rs.close();
						pstmt = null;
						rs = null;
						
						
						sql = "select list_type from pricelist where price_list = ?";
						pstmt=conn.prepareStatement(sql);
						pstmt.setString(1,siteCode);
						rs = pstmt.executeQuery();
						if(rs.next())
						{
							listType = rs.getString(1) == null ? "":rs.getString(1);
						}
						pstmt.close();
						rs.close();
						pstmt = null;
						rs = null;
						
						if(listType.trim().length() == 0)
						{
							sql = "select list_type from pricelist_mst where price_list = ?";
							pstmt=conn.prepareStatement(sql);
							pstmt.setString(1,siteCode);
							rs = pstmt.executeQuery();
							if(rs.next())
							{
								listType = rs.getString(1) == null ? "":rs.getString(1);
							}
							pstmt.close();
							rs.close();
							pstmt = null;
							rs = null;
						}
						
					    rate = getPriceListRate(priceList, utranDate, itemCode, lotNo, listType, conn);
						System.out.println(">>>rate"+rate);
					    if(rate == 0.0 || rate == 0 || rate == -1)
					    {
					    	rate = 0;
					    }
						
						valueXmlString.append("<rate>").append("<![CDATA[" + rate + "]]>").append("</rate>");
						
						
					}else
					{
						
						valueXmlString.append("<rate>").append("<![CDATA[]]>").append("</rate>");
					}
					
				
					
				}
				
				if (currentColumn.trim().equals("lot_sl"))
				{
					if(itemCode.length() > 0 && locCode.length() > 0 && lotNo.length() > 0 && lotsl.length() > 0)
					{
						sql = "select rate from stock where site_code = ? and item_code = ? and loc_code = ? and lot_no = ? and lot_sl = ?";
						pstmt=conn.prepareStatement(sql);
						pstmt.setString(1,siteCode);
						pstmt.setString(2,itemCode);
						pstmt.setString(3,locCode);
						pstmt.setString(4,lotNo);
						pstmt.setString(5,lotsl);
						rs = pstmt.executeQuery();
						if(rs.next())
						{
							oldRate = rs.getString(1) == null ? "":rs.getString(1);
						}
						pstmt.close();
						rs.close();
						pstmt = null;
						rs = null;
						
						
						sql = "select list_type from pricelist where price_list = ?";
						pstmt=conn.prepareStatement(sql);
						pstmt.setString(1,siteCode);
						rs = pstmt.executeQuery();
						if(rs.next())
						{
							listType = rs.getString(1) == null ? "":rs.getString(1);
						}
						pstmt.close();
						rs.close();
						pstmt = null;
						rs = null;
						
						if(listType.trim().length() == 0)
						{
							sql = "select list_type from pricelist_mst where price_list = ?";
							pstmt=conn.prepareStatement(sql);
							pstmt.setString(1,siteCode);
							rs = pstmt.executeQuery();
							if(rs.next())
							{
								listType = rs.getString(1) == null ? "":rs.getString(1);
							}
							pstmt.close();
							rs.close();
							pstmt = null;
							rs = null;
						}
						
					    rate = getPriceListRate(priceList, utranDate, itemCode, lotNo, listType, conn);
						
					    if(rate == 0.0 || rate == 0 || rate == -1)
					    {
					    	rate = 0;
					    }
						valueXmlString.append("<rate__old>").append("<![CDATA[" + oldRate + "]]>").append("</rate__old>");
						valueXmlString.append("<rate>").append("<![CDATA[" + rate + "]]>").append("</rate>");
						
						
					}else
					{
						valueXmlString.append("<rate__old>").append("<![CDATA[]]>").append("</rate__old>");
						valueXmlString.append("<rate>").append("<![CDATA[]]>").append("</rate>");
					}
					
				}
				
				if (currentColumn.trim().equals("quantity"))
				{
					
					quantity = checkNull(genericUtility.getColumnValue("quantity",dom));
					System.out.println(">>>>>oldRate"+oldRate);
					oldRate = checkNull(genericUtility.getColumnValue("rate__old",dom));
					conquan = Double.parseDouble(quantity);
					System.out.println(">>>>>conquan"+conquan);
					conOldRate = Double.parseDouble(oldRate);
					System.out.println(">>>>>conOldRate"+conOldRate);
					
					
					if(quantity != null && quantity.length()>0 )
					{
						oldAmount = conquan * conOldRate;
						amount   = conquan * rate;
						amtdiff = amount - oldAmount;
						
						
						valueXmlString.append("<amount__old>").append("<![CDATA[" + oldAmount + "]]>").append("</amount__old>");
						valueXmlString.append("<amount>").append("<![CDATA[" + amount + "]]>").append("</amount>");
						valueXmlString.append("<amount__diff>").append("<![CDATA[" + amtdiff + "]]>").append("</amount__diff>");
						
					}
					else
					{
						valueXmlString.append("<amount__old>").append("<![CDATA[]]>").append("</amount__old>");
						valueXmlString.append("<amount>").append("<![CDATA[]]>").append("</amount>");
						valueXmlString.append("<amount__diff>").append("<![CDATA[]]>").append("</amount__diff>");
					}
					
				}
				valueXmlString.append("</Detail2>");
				
			}// end switch
			
			valueXmlString.append("</Root>");
		}
		catch(Exception e)
		{
            e.printStackTrace();
			System.out.println("Exception ::"+e.getMessage());
			throw new ITMException(e);
		}
		finally
		{
			try
			{
				if(conn!=null)
				{
					if(rs != null)
					{
						rs.close();
						rs = null;
					}
					if(pstmt != null)
					{
						pstmt.close();
						pstmt = null;
					}
					conn.close();
					conn = null;
				}
			}
			catch(Exception d)
			{
			  d.printStackTrace();
			}			
		}
		return valueXmlString.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
		{
			System.out.println("in getPriceListRate method-------------------");
			
			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 <= sysdate AND VALID_UPTO >= sysdate";
				System.out.println("sql in list type L--->>[["+sql+"]]");
				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 <= sysdate AND VALID_UPTO >= sysdate";
					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 <= sysdate AND VALID_UPTO >= sysdate";
						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 <= sysdate AND VALID_UPTO >= sysdate";
				rs = stmt.executeQuery(sql);
				if (!rs.next())
				{
					return -1;	 // Denotes Error
				}
				else
				{
					rate = rs.getDouble(1);
				}
			}
			else if (type.equalsIgnoreCase("I"))	// Inventory
			{
				
				rate = 0;
				
				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;

	}
	
	private String checkNull(String input)
	{
		if(input == null)
		{
		  return "";
		}
		return input.trim();
	}
	
//	private double getRate(String siteCode,String itemCode,String locCode,String lotNo,String lotsl,Connection conn)
//	{
//		String sql = "";
//		PreparedStatement pstmt=null;
//		ResultSet rs = null;
//		
//		
//		if(itemCode.length() > 0 && locCode.length() > 0 && lotNo.length() > 0 && lotsl.length() > 0)
//		{
//			sql = "select rate from stock where site_code = ? and item_code = ? and loc_code = ? and lot_no = ? and lot_sl = ?";
//			pstmt=conn.prepareStatement(sql);
//			pstmt.setString(1,siteCode);
//			pstmt.setString(2,itemCode);
//			pstmt.setString(3,locCode);
//			pstmt.setString(4,lotNo);
//			pstmt.setString(5,lotsl);
//			rs = pstmt.executeQuery();
//			if(rs.next())
//			{
//				oldRate = rs.getString(1) == null ? "":rs.getString(1);
//			}
//			pstmt.close();
//			rs.close();
//			pstmt = null;
//			rs = null;
//			
//			
//			sql = "select list_type from pricelist where price_list = ?";
//			pstmt=conn.prepareStatement(sql);
//			pstmt.setString(1,siteCode);
//			rs = pstmt.executeQuery();
//			if(rs.next())
//			{
//				listType = rs.getString(1) == null ? "":rs.getString(1);
//			}
//			pstmt.close();
//			rs.close();
//			pstmt = null;
//			rs = null;
//			
//			if(listType.trim().length() == 0)
//			{
//				sql = "select list_type from pricelist_mst where price_list = ?";
//				pstmt=conn.prepareStatement(sql);
//				pstmt.setString(1,siteCode);
//				rs = pstmt.executeQuery();
//				if(rs.next())
//				{
//					listType = rs.getString(1) == null ? "":rs.getString(1);
//				}
//				pstmt.close();
//				rs.close();
//				pstmt = null;
//				rs = null;
//			}
//			
//		    rate = getPriceListRate(priceList, utranDate, itemCode, lotNo, listType, conn);
//			
//			valueXmlString.append("<rate__old>").append("<![CDATA[" + oldRate + " ]]>").append("</rate__old>");
//			valueXmlString.append("<rate>").append("<![CDATA[" + rate + " ]]>").append("</rate>");
//			
//			
//		}else
//		{
//			valueXmlString.append("<rate__old>").append("<![CDATA[]]>").append("</rate__old>");
//			valueXmlString.append("<rate>").append("<![CDATA[]]>").append("</rate>");
//		}
//	}
	
	
}
