package ibase.webitm.ejb.fin;

import java.rmi.RemoteException;

import ibase.system.config.AppConnectParm;
import ibase.system.config.ConnDriver;
import ibase.webitm.ejb.ITMDBAccessEJB;
import ibase.webitm.ejb.MasterStatefulLocal;
import ibase.webitm.ejb.ProcessEJB;
import ibase.webitm.ejb.ValidatorEJB;
import ibase.webitm.utility.GenericUtility;
import ibase.webitm.utility.ITMException;
import ibase.webitm.ejb.ValidatorEJB;
import ibase.webitm.ejb.dis.ProofOfDelivery;

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

import javax.ejb.Stateless;
import javax.naming.InitialContext;
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;

@Stateless
public class StockRevaluationAdjPrc extends ProcessEJB implements StockRevaluationAdjPrcLocal,StockRevaluationAdjPrcRemote{
	String stockRevalAdjTranID="";
	boolean isError=false,saveRecord=false;
	FinCommon finCommonObj=new FinCommon();
public String process(String xmlString, String xmlString2, String windowName, String xtraParams) throws RemoteException,ITMException
	{
			
			System.out.println("enter in process for StockRevaluationAdjPrc.............");
			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 :StockRevaluationAdjPrc :process " + 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
{
	isError=false;
	saveRecord=false;
	Connection conn = null;
	ConnDriver connDriver = null;
	GenericUtility genericUtility = null;	
	ITMDBAccessEJB itmdbAccess = null;
	ValidatorEJB vdt=null;
	PreparedStatement pstmt=null;
	ResultSet rs=null;		
	int cnt=0,lineNo=0,itemSerCount=0,serCount=0;
	boolean isRecordFound=false;	
	double quantityOld=0,rateOld=0;
	Set<String> itemSerSet=new HashSet<String>();
	String siteCode="",itemSerFrm="",itemSerTo="",itemCodeFrm="",itemCodeTo="",errorString="",errCode="";
	String sql="",autoConfirm="",priceList="",reasonCode="",userId="",itemCode="",itemSeries="",lotNo="",lotSl="";
	String headerXml="",detail2Xml="",locCode="";
	try{
		
		genericUtility = GenericUtility.getInstance();
		itmdbAccess=new ITMDBAccessEJB();		
	    vdt=new ValidatorEJB();
		connDriver = new ConnDriver();
		conn = connDriver.getConnectDB("DriverITM");
		connDriver = null;
		userId = vdt.getValueFromXTRA_PARAMS(xtraParams,"loginCode");
	   siteCode=checkNull(genericUtility.getColumnValue("site_code", dom));
	   itemSerFrm=checkNull(genericUtility.getColumnValue("item_ser__from", dom));
	   itemSerTo=checkNull(genericUtility.getColumnValue("item_ser__to", dom));	   
	   itemCodeFrm=checkNull(genericUtility.getColumnValue("item_code__from", dom));
	   itemCodeTo=checkNull(genericUtility.getColumnValue("item_code_to", dom));
	   
	   autoConfirm=checkNull(genericUtility.getColumnValue("auto_confirm", dom));
	   priceList=checkNull(genericUtility.getColumnValue("price_list", dom));
	   reasonCode=checkNull(genericUtility.getColumnValue("reason_code", dom));	
	   
	   System.out.println("autoConfirm----->>["+autoConfirm+"]");
	 //Validation for Site code
 		if (siteCode.length() == 0 ){							
 			errCode = "VTSITECNE";
 			errorString = vdt.getErrorString("site_code",errCode,userId);
 			return errorString;
 			
 		}else{							
 			cnt=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 item series From
 		if (itemSerFrm.length() == 0 ){							
 			errCode = "VTISERFNN";
 			errorString = vdt.getErrorString("item_ser__from",errCode,userId);
 			return errorString;
 		
 		}else{
 			System.out.println("itemSerFrm--->>["+itemSerFrm+"]"); 			
 				cnt=getDBRowCount(conn,"itemser","item_ser",itemSerFrm);
 				if(cnt == 0){
 				System.out.println("itemSerFrm not exist validation fire");
 				errCode = "VTISERFNE";
 				errorString = vdt.getErrorString("item_ser__from",errCode,userId);	
 				return errorString; 					
 			} 			
 		}
 		
 		//Validation for item series To 
 		if (itemSerTo.length() == 0 ){							
 			errCode = "VTISERTNN";
 			errorString = vdt.getErrorString("item_ser__to",errCode,userId);
 			return errorString;
 		
 		}else{							
 			System.out.println("itemSerTo --->>["+itemSerTo+"]"); 			
 			cnt=getDBRowCount(conn,"itemser","item_ser",itemSerTo);
			if(cnt == 0){
			System.out.println("itemSerTo not exist validation fire");
			errCode = "VTISERTNE";
			errorString = vdt.getErrorString("item_ser__to",errCode,userId);	
			return errorString; 			  
			}	 					
 		 }
 		
 		//Validation for item code from  
 		if (itemCodeFrm.length() == 0 ){							
 			errCode = "VTITMCFNN";
 			errorString = vdt.getErrorString("item_code__from",errCode,userId);
 			return errorString;
 		
 		}else{							
 			System.out.println("itemCodeFrm --->>["+itemCodeFrm+"]"); 			
 			cnt=getDBRowCount(conn,"item","item_code",itemCodeFrm);
			if(cnt == 0){
			System.out.println("itemCodeFrm not exist validation fire");
			errCode = "VTITMCFNE";
			errorString = vdt.getErrorString("item_code__from",errCode,userId);	
			return errorString; 			  
			}	 					
 		 }
 		
 		//Validation for item code To 
 		if (itemCodeTo.length() == 0 ){							
 			errCode = "VTITMCTNN";
 			errorString = vdt.getErrorString("item_code_to",errCode,userId);
 			return errorString;
 		
 		}else{							
 			System.out.println("itemCodeTo --->>["+itemCodeTo+"]"); 			
 			cnt=getDBRowCount(conn,"item","item_code",itemCodeTo);
			if(cnt == 0){
			System.out.println("itemCodeTo not exist validation fire");
			errCode = "VTITMCTNE";
			errorString = vdt.getErrorString("item_code_to",errCode,userId);	
			return errorString; 			  
			}	 					
 		 }
 		
 		//Validation for Reason code
 		if (reasonCode.length() == 0 ){							
 			errCode = "VTRESNCNN";
 			errorString = vdt.getErrorString("reason_code",errCode,userId);
 			return errorString;
 		}else{ 			
 			cnt=getDBRowCount(conn,"gencodes","fld_name",reasonCode);
 			if(cnt == 0){
			errCode = "VTRESNCNE";
			errorString = vdt.getErrorString("reason_code",errCode,userId);	
			return errorString; 
 			}
 			
 		}
 		//Validation FOR Price List
 		if(priceList.length() == 0){
 			errCode = "VTPRCLNN";
			errorString = vdt.getErrorString("price_list",errCode,userId);	
			return errorString; 
 		}else{
 			String priceListL="";
 			sql="select price_list from site where site_code=?";
			pstmt=conn.prepareStatement(sql);
			pstmt.setString(1,siteCode);
			rs=pstmt.executeQuery();
			if(rs.next()){
				priceListL=checkNull(rs.getString(1));
			}
			System.out.println("priceListL--->>["+priceListL+"]");
			System.out.println("priceList--->>["+priceList+"]");
			if(!(priceListL.equalsIgnoreCase(priceList))){
				errCode = "VTPRCLNDS";
				errorString = vdt.getErrorString("price_list",errCode,userId);	
				return errorString; 
			}
			if(rs!=null){
				rs.close();
				rs=null;
			}
			if(pstmt!=null){
				pstmt.close();
				pstmt=null;
			}
 		}
 			
 		//actual logic stock re-valuation adjustment
 		
 		if(siteCode.length() > 0 && itemSerFrm.length() > 0 && itemSerTo.length() > 0 && priceList.length() > 0){
 			if(itemCodeFrm.length() > 0 && itemCodeTo.length() > 0){
 				
 				System.out.println("in actual logic--------------");
 				sql="select item_ser from stock where site_code = ? and item_ser between ? and ? "
 						+ "and item_code between ? and  ?";
 				pstmt=conn.prepareStatement(sql);
 				pstmt.setString(1, siteCode);
 				pstmt.setString(2, itemSerFrm);
 				pstmt.setString(3, itemSerTo);
 				pstmt.setString(4, itemCodeFrm);
 				pstmt.setString(5, itemCodeTo);
 				
 				
 				rs=pstmt.executeQuery();
 				while (rs.next()){
 					itemSerSet.add(rs.getString(1));
 				}
 				itemSerCount=itemSerSet.size();
 				System.out.println("size of itemSerCount---->>["+itemSerCount+"]");
 				if(rs!=null){
 					rs.close();
 					rs=null;
 				}
 				if(pstmt!=null){
 					pstmt.close();
 					pstmt=null;
 				}
 				for(String unqItemSer : itemSerSet){ 
 				serCount++;
 				lineNo=0;
 				sql="select item_code,quantity,rate,item_ser,lot_no,lot_sl,loc_code from stock "
 						+ "where site_code = ? and item_ser = ? and item_code between ? and  ? and quantity > 0 and lot_no is not null and lot_no <> ' ' ";
 				
 				pstmt=conn.prepareStatement(sql);
 				pstmt.setString(1, siteCode);
 				pstmt.setString(2, unqItemSer);
 				pstmt.setString(3, itemCodeFrm);
 				pstmt.setString(4, itemCodeTo);
 				
 				headerXml=getHeaderXml(conn,siteCode,unqItemSer,priceList,reasonCode);
 				
 				rs=pstmt.executeQuery();
 				while (rs.next()){
 					lineNo++;
 					isRecordFound=true;
 					itemCode=checkNull(rs.getString(1));
 					quantityOld=rs.getDouble(2);
 					rateOld=rs.getDouble(3);
 					itemSeries=checkNull(rs.getString(4));
 					lotNo=checkNull(rs.getString(5));
 					lotNo = lotNo.length() == 0 ? " " : lotNo.trim();
 					lotSl=checkNull(rs.getString(6)); 		
 					locCode=checkNull(rs.getString(7));
 					detail2Xml=detail2Xml+getDetail2Xml(conn,unqItemSer,siteCode,priceList,itemCode,locCode,lotNo,lotSl,lineNo);				
 					
 				}//end while 			
 				if ( rs != null ){
				rs.close();
				rs = null;
 				}
 				if( pstmt != null ){
				pstmt.close();
				pstmt = null;
 				}
 				System.out.println("detail2Xml String---->>["+detail2Xml+"]");
 				
 				errorString =saveRecordStockRevAdj(conn,headerXml,detail2Xml,siteCode);
 				System.out.println("After Saving record errorString--->>["+errorString+"]");
 				if (errorString.indexOf("Success") > -1)
				{
 					System.out.println("Record saved Successfully......");
 					System.out.println("autoConfirm----->>["+autoConfirm+"]");
 					if("Y".equalsIgnoreCase(autoConfirm)){
 					StockValAdjTranConf objStockAdj=new StockValAdjTranConf();
 					errorString=objStockAdj.confirm(stockRevalAdjTranID,xtraParams,"");
 					System.out.println("After Confirming record errorString--->>["+errorString+"]"); 
 					if(errorString!=null && errorString.indexOf("STKVACONF") > -1){
 						
 					}else{
 						return errorString;
 					}
 					}else{
 						saveRecord=true;
 					}
				}else{
					conn.rollback();
					/*errCode = "VTERROR";//process fail (not save record)
 					errorString = vdt.getErrorString("",errCode,userId);*/	
 					return errorString; 
				}
 				
 				
 				if(! isRecordFound){
 					conn.rollback();
 					errCode = "VTDNFIN";// data not found
 					errorString = vdt.getErrorString("",errCode,userId);	
 					return errorString; 
 				}
 				
 				
 				
 				} //end for loop of set
 				
 				System.out.println("No of Item series process--->>"+serCount+"]");
 				System.out.println("End of process errorString--->>"+serCount+"]");
 				System.out.println("saveRecord--->>"+saveRecord+"]");
 				if(errorString!=null && errorString.indexOf("STKVACONF") > -1 || saveRecord){
				conn.commit();
				errCode = "VTPRCCSF";
				errorString = vdt.getErrorString("",errCode,userId);	
				return errorString; 
				}
 				
 			} //if end
 		}
 		
		
	}// end try
	catch(Exception e){
		
		e.printStackTrace();
		isError=true;
	}
	finally{
		try{
			if(isError){
			System.out.println("isError true................");
 			conn.rollback();
			/*errCode = "VTERROR";// Process fail
			errorString = vdt.getErrorString("",errCode,userId);	
			return errorString;*/ 
	 		}
			if(conn!=null){
				conn.close();
				conn=null;
			}
		}
		catch(Exception e){
			
		}
	}
	return errorString;
}

protected int getDBRowCount(Connection conn, String table_name, String whrCondCol, String whrCondVal)
{
		int count=-1;
		
		if(conn!=null){
			
			ResultSet rs=null;
			PreparedStatement pstmt = null;
			
			String sql="select count(1) from "+table_name+" where "+whrCondCol+" = ?";
			System.out.println("SQL in getDBRowCount method : "+sql);
			try
			{
				pstmt = conn.prepareStatement(sql);
				pstmt.setString(1,whrCondVal);
				rs = pstmt.executeQuery();
				if(rs.next())
				{
					count = rs.getInt(1);
				}
				if(pstmt!=null){
					pstmt.close();
					pstmt = null;
				}
				if(rs!=null){
					rs.close();
					rs = null;
				}
			}
			catch(Exception e)
			{
				System.out.println("SQL Exception In getDBRowCount method of StockRevaluationAdjPrc Class : "+e.getMessage());
				e.printStackTrace();
				isError=true;
			}
			
		}
			
	return count;
}

public int updateStockRateAsPerPriceListRate(Connection conn,Map<String,String> updateStockMap){
	PreparedStatement pstmt=null;
	String sql="";
	int count=0;
	try{
		sql="update stock set rate = ? where item_code= ? and site_code = ? and loc_code = ? "
				+ "and lot_no = ? and lot_sl = ?";
		
		pstmt=conn.prepareStatement(sql);
		pstmt.setDouble(1, Double.parseDouble(updateStockMap.get("price_list_rate")));
		pstmt.setString(2,updateStockMap.get("item_code"));
		pstmt.setString(3,updateStockMap.get("site_code"));
		pstmt.setString(4,updateStockMap.get("loc_code"));
		pstmt.setString(5,updateStockMap.get("lot_no"));
		pstmt.setString(6,updateStockMap.get("lot_sl"));
		
		 count=pstmt.executeUpdate();
		
	}
	catch(Exception e){
		System.out.println("Exception in updateStockRateAsPerPriceListRate----------");
		isError=true;
		e.printStackTrace();
	}
	return count;
	
}

private String getHeaderXml(Connection conn,String siteCodeL,String unqItemSerL,String priceListL,String reasonCodeL) throws ITMException, Exception{
	
	StringBuffer xmlBuffer = new StringBuffer();	
	GenericUtility genutility=null;
	SimpleDateFormat sdf=null;	
	java.sql.Timestamp currDate = null;
	String currAppdate ="", tranType = "";	
	
	String sql = "select fld_value from gencodes where mod_name = 'W_STOCKVALADJTRAN' AND fld_name = 'TRAN_TYPE'";
	PreparedStatement pst = conn.prepareStatement(sql);
	ResultSet rs = pst.executeQuery();
	while (rs.next()) {
		tranType = checkNull(rs.getString(1));
	}
	pst.close();
	pst = null;
	rs.close();
	rs = null;
	
	genutility=GenericUtility.getInstance();
	sdf=new SimpleDateFormat(genutility.getApplDateFormat());
	
	currDate = new java.sql.Timestamp(System.currentTimeMillis()) ;
	currAppdate = sdf.format(currDate);
	xmlBuffer.append("<?xml version='1.0' encoding='ISO-8859-1'?>\n");
	xmlBuffer.append("<DocumentRoot>");
	xmlBuffer.append("<description>").append("Datawindow Root").append("</description>");
	xmlBuffer.append("<group0>");
	xmlBuffer.append("<description>").append("Group0 description").append("</description>");
	xmlBuffer.append("<Header0>");
	xmlBuffer.append("<objName><![CDATA[").append("stockvaladjtran").append("]]></objName>");
	xmlBuffer.append("<pageContext><![CDATA[").append("1").append("]]></pageContext>");
	xmlBuffer.append("<objContext><![CDATA[").append("1").append("]]></objContext>");
	xmlBuffer.append("<editFlag><![CDATA[").append("A").append("]]></editFlag>");
	xmlBuffer.append("<focusedColumn><![CDATA[").append("").append("]]></focusedColumn>");
	xmlBuffer.append("<action><![CDATA[").append("SAVE").append("]]></action>");
	xmlBuffer.append("<elementName><![CDATA[").append("").append("]]></elementName>");
	xmlBuffer.append("<keyValue><![CDATA[").append("1").append("]]></keyValue>");
	xmlBuffer.append("<taxKeyValue><![CDATA[").append("	").append("]]></taxKeyValue>");
	xmlBuffer.append("<saveLevel><![CDATA[").append("1").append("]]></saveLevel>");
	xmlBuffer.append("<forcedSave><![CDATA[").append(true).append("]]></forcedSave>");
	xmlBuffer.append("<taxInFocus><![CDATA[").append(false).append("]]></taxInFocus>");
	xmlBuffer.append("<description>").append("Header0 members").append("</description>");
	
	xmlBuffer.append("<Detail1 objContext =\"1\"").append(" objName=\"stockvaladjtran\" domID=\"1\" dbID=\"\">");
	xmlBuffer.append("<attribute selected=\"N\" updateFlag=\"A\" status=\"N\" pkNames=\"\"/>");
	xmlBuffer.append("<tran_id/>");	
	
    xmlBuffer.append("<tran_date>").append("<![CDATA[").append(currAppdate).append("]]></tran_date>\r\n");
    xmlBuffer.append("<tran_type>").append("<![CDATA[").append(tranType).append("]]></tran_type>\r\n");
    xmlBuffer.append("<eff_date>").append("<![CDATA[").append(currAppdate).append("]]></eff_date>\r\n");
    xmlBuffer.append("<site_code>").append("<![CDATA[").append(siteCodeL).append("]]></site_code>\r\n");
    xmlBuffer.append("<item_ser>").append("<![CDATA[").append(unqItemSerL).append("]]></item_ser>\r\n");
    xmlBuffer.append("<price_list>").append("<![CDATA[").append(priceListL).append("]]></price_list>\r\n");	
    xmlBuffer.append("<reas_code>").append("<![CDATA[").append(reasonCodeL).append("]]></reas_code>\r\n");
    xmlBuffer.append("<remarks>").append("<![CDATA[").append("Stock re-valuation Adj.").append("]]></remarks>\r\n");
	xmlBuffer.append("<confirmed>").append("<![CDATA[").append("N").append("]]></confirmed>\r\n");
	
	
	xmlBuffer.append("</Detail1>\r\n");
	
	return xmlBuffer.toString();
	
	
}

private String getDetail2Xml(Connection conn,String itemSer,String siteCode,String priceList,String itemCode,String locCodeL,String lotNo,String lotSl,int lineNo){
	
	StringBuffer detail2xml=new StringBuffer();
	PreparedStatement pstmt1=null;
	ResultSet rs1=null;
	String dbTranID="",retString="" ,acctCctrCode="",acctCctrCodeInvRev="";  
	double oldRate=0,newRate=0,quantity=0,amountOld,amountNew=0,amountDiff=0;
	try{
	
	detail2xml.append("<Detail2 objContext =\"2\"").append(" objName=\"stockvaladjtran\" domID=\"1\" dbID=\"\">");
	detail2xml.append("<attribute selected=\"N\" updateFlag=\"A\" status=\"N\" pkNames=\"\"/>");
	detail2xml.append("<tran_id/>");
	detail2xml.append("<line_no>").append("<![CDATA[").append(lineNo).append("]]></line_no>");		
	detail2xml.append("<item_code>").append("<![CDATA[").append(itemCode).append("]]></item_code>\r\n");    
	detail2xml.append("<loc_code>").append("<![CDATA[").append(locCodeL).append("]]></loc_code>\r\n");
	detail2xml.append("<lot_no>").append("<![CDATA[").append(lotNo).append("]]></lot_no>\r\n");
	detail2xml.append("<lot_sl>").append("<![CDATA[").append(lotSl).append("]]></lot_sl>\r\n");	
	quantity=getRecordFromStock(conn, "quantity" ,siteCode, itemCode, locCodeL, lotNo, lotSl);
	System.out.println("old quantity-------->["+quantity+"]");	
	detail2xml.append("<quantity>").append("<![CDATA["+oldRate+"]]>").append("</quantity>"); 
	
	oldRate=getRecordFromStock(conn, "rate" ,siteCode, itemCode, locCodeL, lotNo, lotSl);
	System.out.println("old rate-------->["+oldRate+"]");	
	detail2xml.append("<rate__old>").append("<![CDATA["+oldRate+"]]>").append("</rate__old>"); 
	java.util.Date tranDate=new Date();
	newRate=getPriceListRate(priceList, tranDate, itemCode, lotNo, "", conn);
	newRate = newRate == -1 ? 0 : newRate; 
	detail2xml.append("<rate>").append("<![CDATA["+newRate+"]]>").append("</rate>");
	
	amountOld= oldRate * quantity;
	amountNew= newRate * quantity;
	amountDiff=amountNew - amountOld;
	System.out.println(" amountOld-------->["+amountOld+"]");
	System.out.println(" amountNew-------->["+amountNew+"]");
	System.out.println(" amountDiff-------->["+amountDiff+"]");
	
	
	detail2xml.append("<amount__old>").append("<![CDATA["+amountOld+"]]>").append("</amount__old>");
	detail2xml.append("<amount>").append("<![CDATA["+amountNew+"]]>").append("</amount>");
	detail2xml.append("<amount__diff>").append("<![CDATA["+amountDiff+"]]>").append("</amount__diff>");
	
	 acctCctrCode=getAcctCctrCodeFromStock(conn, siteCode, itemCode, locCodeL, lotNo, lotSl);
	 System.out.println("acctCctrCode----->>["+acctCctrCode+"]");
	 String acctCctrCodeL[]=acctCctrCode.split(":");	 
	 acctCctrCodeInvRev=finCommonObj.getFromAcctDetr(itemCode,itemSer,"STKADJ", conn);
	 System.out.println("acctCctrCodeInvRev----->>["+acctCctrCodeInvRev+"]");
	 String acctCctrCodeS[]=acctCctrCodeInvRev.split(",");
	 if (acctCctrCodeL.length > 0 && acctCctrCodeS.length > 0) {
		 detail2xml.append("<acct_code__inv>").append("<![CDATA[").append(acctCctrCodeL[0].trim()).append("]]></acct_code__inv>\r\n");
		 detail2xml.append("<cctr_code__inv>").append("<![CDATA[").append(acctCctrCodeL[1].trim()).append("]]></cctr_code__inv>\r\n");
		 detail2xml.append("<acct_code__inrev>").append("<![CDATA[").append(acctCctrCodeS[0].trim()).append("]]></acct_code__inrev>\r\n");	
		 detail2xml.append("<cctr_code__inrev>").append("<![CDATA[").append(acctCctrCodeS[1].trim()).append("]]></cctr_code__inrev>\r\n");
	 } else {
		 detail2xml.append("<acct_code__inv>").append("<![CDATA[").append("]]></acct_code__inv>\r\n");
		 detail2xml.append("<cctr_code__inv>").append("<![CDATA[").append("]]></cctr_code__inv>\r\n");
		 detail2xml.append("<acct_code__inrev>").append("<![CDATA[").append("]]></acct_code__inrev>\r\n");	
		 detail2xml.append("<cctr_code__inrev>").append("<![CDATA[").append("]]></cctr_code__inrev>\r\n");
	 }
	
	detail2xml.append("</Detail2>");
	
	}
	catch(Exception e){
		isError=true;
		e.printStackTrace();
		
	}

	return detail2xml.toString();

}
private double getRecordFromStock(Connection conn,String parameter ,String siteCode,String itemCode,String locCodeL,String lotNo,String lotSl)
{
	ResultSet rs=null;
	PreparedStatement pstmt=null;
	String sql="";
	double rate=0;
	try{
		sql="select "+parameter+" 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, locCodeL);
		pstmt.setString(4, lotNo);
		pstmt.setString(5, lotSl);
		
		rs=pstmt.executeQuery();
		if(rs.next()){
			rate=rs.getDouble(1);
		}
		if(rs!=null){
			rs.close();
			rs=null;			
		}
		if(pstmt!=null){
			pstmt.close();
			pstmt=null;
		}
	}
	catch(Exception e){
		isError=true;
		e.printStackTrace();
		return -1;
	}
	return rate;
}

private String getAcctCctrCodeFromStock(Connection conn,String siteCode,String itemCode,String locCodeL,String lotNo,String lotSl)
{
	ResultSet rs=null;
	PreparedStatement pstmt=null;
	String sql="",acctCode="",cctrCode="";
	double rate=0;
	try{
		sql="select acct_code__inv,cctr_code__inv 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, locCodeL);
		pstmt.setString(4, lotNo);
		pstmt.setString(5, lotSl);
		
		rs=pstmt.executeQuery();
		if(rs.next()){
			acctCode=checkNull(rs.getString(1));
			cctrCode=checkNull(rs.getString(2));			
		}
		if(rs!=null){
			rs.close();
			rs=null;			
		}
		if(pstmt!=null){
			pstmt.close();
			pstmt=null;
		}
	}
	catch(Exception e){
		e.printStackTrace();
		isError=true;		
	}
	return acctCode+":"+cctrCode;
}
private String saveRecordStockRevAdj(Connection conn,String headerXml,String detailXml,String siteCode)
{
	String retString="",dbTranID="",xmlString="";
	xmlString=headerXml + detailXml +"</Header0></group0></DocumentRoot>";
	System.out.println("All xml Header And detail--->>["+xmlString+"]");
	try{
	retString=saveData(siteCode, xmlString, conn);
	System.out.println("Stock reval. adj. Data saved successfully. retString : [["+retString+"]]");
	if (retString.indexOf("Success") > -1)
	{
		System.out.println("retString.indexOf(Success) > -1)");
		String[] arrayForTranId = retString.split("<TranID>");
		int endIndex = arrayForTranId[1].indexOf("</TranID>");

		dbTranID = arrayForTranId[1].substring(0,endIndex);
		//retString ="";
		System.out.println("@@@@@@3: retString"+retString);
		System.out.println("DebitNoteTranID------>>>["+dbTranID+"]");
		if(dbTranID != null && dbTranID.trim().length() > 0)
		{
			
			System.out.println("tran_id__drn generated successfully----------");	
			stockRevalAdjTranID=dbTranID;
		}	
	
	}
	}
	catch(Exception e)
	{
		isError=true;
		e.printStackTrace();
	}
	return retString;
}
private String saveData(String siteCode, String xmlString, Connection conn) throws ITMException
{

	System.out.println("saving data...........");
	InitialContext ctx = null;
	String retString = null;
	MasterStatefulLocal masterStateful = null; // for ejb3
	try
	{
		AppConnectParm appConnect = new AppConnectParm();
		ctx = new InitialContext(appConnect.getProperty());
		masterStateful = (MasterStatefulLocal)ctx.lookup("ibase/MasterStatefulEJB/local");

		String [] authencate = new String[2];
		authencate[0] = "";
		authencate[1] = "";
		System.out.println("xmlString in masterStateful :::: " + xmlString);
		retString = masterStateful.processRequest(authencate, siteCode, true, xmlString, true, conn);
		masterStateful = null;			
		
	}
	catch(ITMException itme)
	{
		isError=true;
		System.out.println("ITMException :StockRevaluationAdjPrc :saveData :==>");
		throw itme;
	}
	catch(Exception e)
	{
		isError=true;
		System.out.println("Exception :StockRevaluationAdjPrc :saveData :==>");
		throw new ITMException(e);
	}
	return retString;

}
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;
			//	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)
	{
		isError=true;
		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();
}

}
