/*
 * Author:Wasim Ansari
 * Date:10-Mar-15
 * Request ID:D14JKAT004 (Delivery Date Ammendment)
 */

package ibase.webitm.ejb.wms;

import ibase.system.config.AppConnectParm;
import ibase.system.config.ConnDriver;
import ibase.utility.CommonConstants;
import ibase.webitm.ejb.ITMDBAccessEJB;
import ibase.webitm.ejb.MasterStatefulLocal;
import ibase.webitm.ejb.ValidatorEJB;
import ibase.webitm.ejb.dis.DistCommon;
import ibase.webitm.ejb.dis.POrderAmdConf;
import ibase.webitm.ejb.dis.POrderAmdConfRemote;
import ibase.webitm.ejb.fin.FinCommon;
import ibase.webitm.utility.GenericUtility;
import ibase.webitm.utility.ITMException;
import ibase.webitm.utility.TransIDGenerator;

import java.net.InetAddress;
import java.rmi.RemoteException;
import java.sql.BatchUpdateException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Date;
import java.util.GregorianCalendar;
import java.util.Properties;

import javax.ejb.Stateless;
import javax.naming.InitialContext;

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

@Stateless
public class DDPorderWizAmdPosEJB extends ValidatorEJB implements DDPorderWizAmdPosEJBRemote, DDPorderWizAmdPosEJBLocal 
{
	GenericUtility genericUtility = GenericUtility.getInstance();
	private String tranId = "";
	
	
    public DDPorderWizAmdPosEJB()
    {
       
    }

	@Override
	public String postSave(String xmlStringAll, String dcrId, String editFlag, String xtraParams, Connection conn) throws RemoteException, ITMException 
	{
		String retString="";
		
		System.out.println("PostSave Action Called for Ammendment Wizard:::");
		
		try 
		{
			retString = generateAmmend(xmlStringAll, xtraParams, "", conn);
		}
		catch (Exception e)
		{
			e.printStackTrace();
			throw new ITMException(e);
		}
		
		return retString;
	}
	
		
	public String generateAmmend(String xmlstring, String xtraParams, String forcedFlag, Connection conn) throws RemoteException,ITMException
	{
		System.out.println("In generate Ammendment");
		
		String retString = "";
		boolean isError = false;
		String errString = "";
		
		String sql = "";
		PreparedStatement pstmt = null;
		ResultSet rs = null;	
		
		String loginEmpCode = "";
		String userId = "";
		String loginSite = "";
		
		FinCommon finCommon = null;
		Document dom = null;
		
		String newdlvDate = "";
		String dlvDate = "";
		String dlvDateO = "";
	
		String itemCode = "";
		String suppCode = "";
		String purcOrder = "";
		
		String lineNo = "";
		String lineNoOrd = "";
		String siteCode = "";
		String siteCodeO = "";
		String indNo = "";
		String quantity = "";
		String quantityO = "";
		String unit = "";
		String rate = "";
		String rateO = "";
		String discount = ""; 
		String taxAmt = "";
		String totAmt = "";
		String locCode = "";
		//String reqDate = "";
		//String reqDateO = "";
		Timestamp reqDate = null;
		Timestamp reqDateO = null;
		String dlvQty = "";
		String taxChap = "";
		String taxClass = "";
		String taxEnv = "";
		String remarks = "";
		String workOrder = "";
		String unitRate = "";
		String conqStduom = "";
		String conrtStduom = "";
		String unitStd = "";
		String qtyStduom = ""; 
		String rateStduom = "";
		String packCode = "";
		String noArt = "";
		String packInstr = ""; 
		String acctCodedr = "";
		String cctrCodedr = "";
		String acctCodecr = "";
		String cctrCodecr = "";
		String acctCodedrO = "";
		String cctrCodedrO = "";
		String acctCodecrO = "";
		String cctrCodecrO = "";
		
		String disctType = "";
		String status = "";
		String empCodeQc = "";
		String empCodeQcO = "";
		String specificInstr = ""; 
		String specificInstrO = "";
		String suppCodeMnfr = "";
		String suppCodeMnfrO = "";
		String rateClg = "";
		String reason = "";
		String specialInstr = ""; 
		String specialInstrO = "";
		String benType = "";
		String benTypeO = "";
		String licNo = "";
		String licNoO= "";
		String formNO = "";
		String formNOold= "";
		String dutyPaid = "";
		String dutyPaidO = "";
		String deptCode = "";
		
		
		String amdDate = "";
		String amdType = "";
		String pordType = "";
		//String ordDate = "";
		Timestamp ordDate = null;
		String siteCodeDlv = "";
		String siteCodeDlvOld = "";
		String siteCodeOrd = "";
		String siteCodeOrdOld = "";
		String siteCodeBill = "";
		String siteCodeBillOld = "";
		
		String deptCodeO = "";
		String empCode = "";
		String empCodeO = "";
		String orderDb = "";
		String itemSer = "";
		String itemSerO = "";
		String taxOpt = "";
		String taxOptO = "";
		String crTerm = "";
		String crTermO = "";
		String ordAmt = "";
		String ordAmtO = "";
		
		String taxAmtO = "";
		String currCode = "";
		String currCodeO = "";
		String exchrate = "";
		String exchrateO = "";
		taxChap = "";
		String taxChapO = "";
		
		String taxClassO = "";
		
		String taxEnvO = "";
		
		
		//String taxDate= "";
		Timestamp taxDate = null;
		//String taxDateO = "";
		Timestamp taxDateO = null;
		String proCode= "";
		String proCodeO = "";
		String salesPers= "";
		String salesPersO = "";
		String commPerc= "";
		String commPercO = "";
		String commPercOn= "";
		String commPercOnO = "";
		String currCodeComm= "";
		String currCodeCommO = "";
		String quotNo= "";
		String quotNoOld = "";
		String tranCode= "";
		String tranCodeO = "";
		String frtAmt= "";
		String frtAmtO = "";
		String currCodeFrt= "";
		String currCodeFrtO = "";
		String frtTerm= "";
		String frtTermtO = "";
		String dlvTerm= "";
		String dlvTermO = "";
		String insAmt= "";
		String insAmtO = "";
		String currCodeIns= "";
		String currCodeInsO = "";
		String empCodeApr= "";
		String empCodeAprO = "";
		//String refDate= "";
		Timestamp refDate = null;
		//String refDateO = "";
		Timestamp refDateO = null;
		String confirmed = "";
		//String confDate = "";
		Timestamp confDate = null;
		String wflStatus = "";
		String parentTranId = "";
		String revTran = "";
		String insTerm = "";
		String suppCodeO = "";
		String analCode = "";
		String frtAmtQty = "";
		String frtAmtFix = "";
		String frtTypeO = "";
		String frtRateO = "";
		String frtAmtQtyO = "";
		String frtAmtFixO = "";
		String effFor = ""; 
		String lineNumber = "";
		
		String ordSite = "";
		String id = "";
		String empcode = "";
		String fFlag = "";
		
		java.sql.Timestamp currDate = null;
		
		String amdNo = "";
		Timestamp today = new Timestamp(System.currentTimeMillis());
		MasterStatefulLocal masterStatefulLocal = null;
		
		try
		{
			AppConnectParm appConnect = new AppConnectParm();
			Properties p = appConnect.getProperty();
			InitialContext ctx = new InitialContext(p);
			GenericUtility genericUtility = GenericUtility.getInstance();
			
			finCommon = new FinCommon();
			System.out.println("XTRA PARAMS ["+xtraParams+"]");
			System.out.println("Getting XML String ["+xmlstring+"]");
			
			dom = genericUtility.parseString(xmlstring);
			
			NodeList detail2NodeList = dom.getElementsByTagName("Detail2");
			int detail2NodeListlen = detail2NodeList.getLength();
			for(int ctrH = 0; ctrH < detail2NodeListlen ; ctrH++)
			{
				NodeList childNodeList = detail2NodeList.item(ctrH).getChildNodes();
				int detail2ListLen = childNodeList.getLength();
				
				for(int ctr = 0 ;ctr < detail2ListLen; ctr++)
				{
					Node childNode = childNodeList.item(ctr);
					
					if(childNode == null || childNode.getNodeType() != childNode.ELEMENT_NODE)
					{
						continue;
					}
					
					if(childNode != null && "dlv_date".equalsIgnoreCase(childNode.getNodeName()) && childNode.getFirstChild() != null)
					{
						dlvDate = childNode.getFirstChild().getNodeValue();
						newdlvDate = dlvDate;
						System.out.println("New Dlv Date="+dlvDate);
					}
					
					else if(childNode != null && "item_code".equalsIgnoreCase(childNode.getNodeName()) && childNode.getFirstChild() != null)
					{
						itemCode = childNode.getFirstChild().getNodeValue();
						System.out.println("Item Code="+itemCode);
					}
					
					else if(childNode != null && "supp_code".equalsIgnoreCase(childNode.getNodeName()) && childNode.getFirstChild() != null)
					{
						suppCode = childNode.getFirstChild().getNodeValue();
						System.out.println("Supplier Code="+suppCode);
					}
					
					else if(childNode != null && "purc_order".equalsIgnoreCase(childNode.getNodeName()) && childNode.getFirstChild() != null)
					{
						purcOrder = childNode.getFirstChild().getNodeValue();
						System.out.println("Purchase Order="+purcOrder);
					}
					
					else if(childNode != null && "site_code".equalsIgnoreCase(childNode.getNodeName()) && childNode.getFirstChild() != null)
					{
						loginSite = childNode.getFirstChild().getNodeValue();
						System.out.println("Site Code="+loginSite);
					}
					
					else if(childNode != null && "line_no".equalsIgnoreCase(childNode.getNodeName()) && childNode.getFirstChild() != null)
					{
						lineNumber = childNode.getFirstChild().getNodeValue();
						System.out.println("Line Number="+lineNumber);
					}
					
					else if(childNode != null && "emp_code".equalsIgnoreCase(childNode.getNodeName()) && childNode.getFirstChild() != null)
					{
						empcode = childNode.getFirstChild().getNodeValue();
						System.out.println("Emp Code="+empcode);
					}
					
					else if(childNode != null && "extra1".equalsIgnoreCase(childNode.getNodeName()) && childNode.getFirstChild() != null)
					{
						id = childNode.getFirstChild().getNodeValue();
						System.out.println("Login ID="+id);
					}
				
				 }
			//}//end for loop		
				
			
							currDate = new java.sql.Timestamp(System.currentTimeMillis());
							SimpleDateFormat sdf = new SimpleDateFormat(genericUtility.getApplDateFormat());
							String date = sdf.format(currDate);
							
							System.out.println("Your Site Code="+loginSite);
							System.out.println("Todays Date="+date);
							
							amdNo = generateTranId("w_porderamd", loginSite, date , conn);
							System.out.println("Your Amd Number is="+amdNo);
							
							sql = "select * from porder where purc_order = ? ";
							pstmt = conn.prepareStatement(sql);
							pstmt.setString(1,purcOrder);
							rs = pstmt.executeQuery();
							
							while (rs.next())
							{
								//amdDate = "16-FEB-15";
								purcOrder = checkNullAndTrim(rs.getString("purc_order"));
								ordDate = rs.getTimestamp("ord_date");
								pordType = checkNullAndTrim(rs.getString("pord_type"));
								suppCode = checkNullAndTrim(rs.getString("supp_code"));
								siteCodeDlv = checkNullAndTrim(rs.getString("site_code__dlv"));
								siteCodeDlvOld = siteCodeDlv;
								siteCodeOrd = checkNullAndTrim(rs.getString("site_code__ord"));
								siteCodeOrdOld = siteCodeOrd;
								siteCodeBill = checkNullAndTrim(rs.getString("site_code__bill"));
								siteCodeBillOld = siteCodeBill;
								deptCode = checkNullAndTrim(rs.getString("dept_code"));
								deptCodeO = deptCode;
								empCode = checkNullAndTrim(rs.getString("emp_code"));
								empCodeO = empCode;
								orderDb = checkNullAndTrim(rs.getString("order_db"));
								itemSer = checkNullAndTrim(rs.getString("item_ser"));
								itemSerO = itemSer;
								taxOpt = checkNullAndTrim(rs.getString("tax_opt"));
								taxOptO = taxOpt;
								crTerm = checkNullAndTrim(rs.getString("cr_term"));
								crTermO = crTerm;
								ordAmt = checkNullAndTrim(rs.getString("ord_amt"));
								ordAmtO = ordAmt;
								taxAmt = checkNullAndTrim(rs.getString("tax_amt"));
								taxAmtO = taxAmt;
								currCode = checkNullAndTrim(rs.getString("curr_code"));
								currCodeO = currCode;
								exchrate = checkNullAndTrim(rs.getString("exch_rate"));
								exchrateO = exchrate;
								taxChap = checkNullAndTrim(rs.getString("tax_chap"));
								taxChapO = taxChap;
								taxClass= checkNullAndTrim(rs.getString("tax_class"));
								taxClassO = taxClass;
								taxEnv= checkNullAndTrim(rs.getString("tax_env"));
								taxEnvO = taxEnv;
								remarks = checkNullAndTrim(rs.getString("remarks"));
								taxDate= rs.getTimestamp("tax_date");
								taxDateO = taxDate;
								proCode= checkNullAndTrim(rs.getString("proj_code"));
								proCodeO = proCode;
								salesPers= checkNullAndTrim(rs.getString("sales_pers"));
								salesPersO = salesPers;
								commPerc= checkNullAndTrim(rs.getString("comm_perc"));
								commPercO = commPerc;
								commPercOn= checkNullAndTrim(rs.getString("comm_perc__on"));
								commPercOnO = commPercOn;
								currCodeComm= checkNullAndTrim(rs.getString("curr_code__comm"));
								currCodeCommO = currCodeComm;
								quotNo= checkNullAndTrim(rs.getString("quot_no"));
								quotNoOld = quotNo;
								tranCode= checkNullAndTrim(rs.getString("tran_code"));
								tranCodeO = tranCode;
								frtAmt= checkNullAndTrim(rs.getString("frt_amt"));
								frtAmtO = frtAmt;
								currCodeFrt= checkNullAndTrim(rs.getString("curr_code__frt"));
								currCodeFrtO = currCodeFrt;
								frtTerm= checkNullAndTrim(rs.getString("frt_term"));
								frtTermtO = frtTerm;
								dlvTerm= checkNullAndTrim(rs.getString("dlv_term"));
								dlvTermO = dlvTerm;
								insAmt= checkNullAndTrim(rs.getString("insurance_amt"));
								insAmtO = insAmt;
								currCodeIns= checkNullAndTrim(rs.getString("curr_code__ins"));
								currCodeInsO = currCodeIns;
								empCodeApr= checkNullAndTrim(rs.getString("emp_code__aprv"));
								empCodeAprO = empCodeApr;
								refDate= rs.getTimestamp("ref_date");
								refDateO = refDate;
								confirmed = checkNullAndTrim(rs.getString("confirmed"));
								confDate = rs.getTimestamp("conf_date");
								status = checkNullAndTrim(rs.getString("status"));
								wflStatus = checkNullAndTrim(rs.getString("workflow_status"));
								parentTranId = checkNullAndTrim(rs.getString("parent__tran_id"));
								revTran = checkNullAndTrim(rs.getString("rev__tran"));
								insTerm = checkNullAndTrim(rs.getString("ins_term"));
								suppCodeO = suppCode;
								analCode = checkNullAndTrim(rs.getString("anal_code"));
								frtAmtQty = checkNullAndTrim(rs.getString("frt_amt__qty"));
								frtAmtFix = checkNullAndTrim(rs.getString("frt_amt__fixed"));
								frtTypeO = checkNullAndTrim(rs.getString("frt_type"));
								frtRateO = checkNullAndTrim(rs.getString("frt_rate"));
								frtAmtQtyO = checkNullAndTrim(rs.getString("frt_amt__qty"));
								frtAmtFixO = checkNullAndTrim(rs.getString("frt_amt__fixed"));
							}
							
							rs.close(); rs = null;
							pstmt.close(); pstmt = null;
							
							
							sql = "insert into poamd_hdr (amd_no,amd_date,amd_type,purc_order,ord_date,pord_type,supp_code,site_code__dlv,site_code__dlv__o,"
									+ "site_code__ord,site_code__ord__o,site_code__bill,site_code__bill__o,dept_code,dept_code__o,emp_code,emp_code__o,"
									+ "order_db,item_ser,item_ser__o,tax_opt,tax_opt__o,cr_term,cr_term__o,"
									+ "ord_amt,ord_amt__o,tax_amt,tax_amt__o,"
									+ "curr_code,curr_code__o,exch_rate,exch_rate__o,tax_chap,tax_chap__o,tax_class,tax_class__o,tax_env,tax_env__o,"
									+ "remarks,tax_date,tax_date__o,proj_code,proj_code__o,sales_pers,sales_pers__o,comm_perc,comm_perc__o,"
									+ "comm_perc__on,comm_perc__on__o,quot_no,quot_no__o,tran_code,tran_code__o,frt_amt,frt_amt__o,dlv_term,dlv_term__o,"
									+ "insurance_amt,insurance_amt__o,curr_code__ins,curr_code__ins__o,emp_code__aprv,emp_code__aprv__o,ref_date,ref_date__o,"
									+ "confirmed,conf_date,chg_date,status,workflow_status,parent__tran_id,rev__tran,ins_term,supp_code__o,anal_code,frt_amt__qty,"
									+ "frt_amt__fixed,frt_type__o,frt_rate__o,frt_amt__qty__o,frt_amt__fixed__o,effective_for )"
									+ " values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,"
									+ "?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,? ) ";
							
							pstmt = conn.prepareStatement(sql);

							pstmt.setString(1, amdNo);
							pstmt.setTimestamp(2, today);
							pstmt.setString(3, amdType);
							pstmt.setString(4, purcOrder);
							//pstmt.setString(5, ordDate);
							pstmt.setTimestamp(5, ordDate);
							pstmt.setString(6, pordType);
							pstmt.setString(7, suppCode);
						   //pstmt.setString(8, siteCode);
							pstmt.setString(8, siteCodeDlv);
							pstmt.setString(9, siteCodeDlvOld);
							pstmt.setString(10, siteCodeOrd);
							pstmt.setString(11, siteCodeOrdOld);
							pstmt.setString(12, siteCodeBill);
							pstmt.setString(13, siteCodeBillOld);
							pstmt.setString(14, deptCode);
							pstmt.setString(15, deptCodeO);
							pstmt.setString(16, empCode);
							pstmt.setString(17, empCodeO);
							pstmt.setString(18, orderDb);
							pstmt.setString(19, itemSer);
							pstmt.setString(20, itemSerO);
							pstmt.setString(21, taxOpt);
							pstmt.setString(22, taxOptO);
							pstmt.setString(23, crTerm);
							pstmt.setString(24, crTermO);
							pstmt.setString(25, ordAmt);
							pstmt.setString(26, ordAmtO);
							pstmt.setString(27, taxAmt);
							pstmt.setString(28, taxAmtO);
							pstmt.setString(29, currCode);
							pstmt.setString(30, currCodeO);
							pstmt.setString(31, exchrate);
							pstmt.setString(32, exchrateO);
							pstmt.setString(33, taxChap);
							pstmt.setString(34, taxChapO);
							pstmt.setString(35, taxClass);
							pstmt.setString(36, taxClassO);
							pstmt.setString(37, taxEnv);
							pstmt.setString(38, taxEnvO);
							pstmt.setString(39, remarks);
							//pstmt.setString(40, taxDate);
							pstmt.setTimestamp(40, taxDate);
							//pstmt.setString(41, taxDateO);
							pstmt.setTimestamp(41, taxDateO);
							pstmt.setString(42, proCode);
							pstmt.setString(43, proCodeO);
							pstmt.setString(44, salesPers);
							pstmt.setString(45, salesPersO);
							pstmt.setString(46, commPerc);
							pstmt.setString(47, commPercO);
							pstmt.setString(48, commPercOn);
							pstmt.setString(49, commPercOnO);
							pstmt.setString(50, quotNo);
							pstmt.setString(51, quotNoOld);
							pstmt.setString(52, tranCode);
							pstmt.setString(53, tranCodeO);
							pstmt.setString(54, frtAmt);
							pstmt.setString(55, frtAmtO);
							pstmt.setString(56, dlvTerm);
							pstmt.setString(57, dlvTermO);
							pstmt.setString(58, insAmt);
							pstmt.setString(59, insAmtO);
							pstmt.setString(60, currCodeIns);
							pstmt.setString(61, currCodeInsO);
							pstmt.setString(62, empCodeApr);
							pstmt.setString(63, empCodeAprO);
							//pstmt.setString(64, refDate);
							pstmt.setTimestamp(64, refDate);
							//pstmt.setString(65, refDateO);
							pstmt.setTimestamp(65, refDateO);
							confirmed = "N";
							pstmt.setString(66, confirmed);
							//pstmt.setString(67, confDate);
							pstmt.setTimestamp(67, confDate);
							pstmt.setTimestamp(68, today);
							pstmt.setString(69, status);
							pstmt.setString(70, wflStatus);
							pstmt.setString(71, parentTranId);
							pstmt.setString(72, revTran);
							pstmt.setString(73, insTerm);
							pstmt.setString(74, suppCodeO);
							pstmt.setString(75, analCode);
							pstmt.setString(76, frtAmtQty);
							pstmt.setString(77, frtAmtFix);
							pstmt.setString(78, frtTypeO);
							pstmt.setString(79, frtRateO);
							pstmt.setString(80, frtAmtQtyO);
							pstmt.setString(81, frtAmtFixO);
							pstmt.setString(82, effFor);
							
							pstmt.executeUpdate();
							
							pstmt.close(); pstmt = null;
							
						System.out.println("Record inserted successfully in header....");
						
						
					  //sql = "select * from porddet where purc_order = ?";
						lineNumber = "   "+lineNumber;
						lineNumber = lineNumber.substring(lineNumber.length()-3);
						System.out.println("New Line Number="+lineNumber);
						
						sql = "select * from porddet where purc_order = ? and line_no = ?";
						pstmt = conn.prepareStatement(sql);
						pstmt.setString(1,purcOrder);
						pstmt.setString(2,lineNumber);
						rs = pstmt.executeQuery();
						
						Timestamp detdlvDate = null;
						
						
						if(rs.next())
						{
							lineNo = checkNullAndTrim(rs.getString("line_no"));
							siteCode = checkNullAndTrim(rs.getString("site_code"));
							siteCodeO = siteCode;
							indNo = checkNullAndTrim(rs.getString("ind_no"));
							itemCode = checkNullAndTrim(rs.getString("item_code"));
							quantity = checkNullAndTrim(rs.getString("quantity"));
							quantityO = quantity;
							unit = checkNullAndTrim(rs.getString("unit"));
							rate = checkNullAndTrim(rs.getString("rate"));
							rateO = rate;
							discount = checkNullAndTrim(rs.getString("discount"));
							taxAmt = checkNullAndTrim(rs.getString("tax_amt"));
							totAmt = checkNullAndTrim(rs.getString("tot_amt"));
							locCode = checkNullAndTrim(rs.getString("loc_code"));
							reqDate = rs.getTimestamp("req_date");
							reqDateO = reqDate;
							//dlvDate = checkNullAndTrim(rs.getString("dlv_date"));
							detdlvDate = rs.getTimestamp("dlv_date");
							dlvQty = checkNullAndTrim(rs.getString("dlv_qty"));
							taxChap = checkNullAndTrim(rs.getString("tax_chap"));
							taxClass = checkNullAndTrim(rs.getString("tax_class"));
							taxEnv = checkNullAndTrim(rs.getString("tax_env"));
							remarks = checkNullAndTrim(rs.getString("remarks"));
							workOrder = checkNullAndTrim(rs.getString("work_order"));
							unitRate = checkNullAndTrim(rs.getString("unit__rate"));
							conqStduom = checkNullAndTrim(rs.getString("conv__qty_stduom"));
							conrtStduom = checkNullAndTrim(rs.getString("conv__rtuom_stduom"));
							unitStd = checkNullAndTrim(rs.getString("unit__std"));
							qtyStduom = checkNullAndTrim(rs.getString("quantity__stduom"));
							rateStduom = checkNullAndTrim(rs.getString("rate__stduom"));
							packCode = checkNullAndTrim(rs.getString("pack_code"));
							noArt = checkNullAndTrim(rs.getString("no_art"));
							packInstr = checkNullAndTrim(rs.getString("pack_instr"));
							acctCodedr = checkNullAndTrim(rs.getString("acct_code__dr"));
							cctrCodedr = checkNullAndTrim(rs.getString("cctr_code__dr"));
							acctCodecr = checkNullAndTrim(rs.getString("acct_code__cr"));
							cctrCodecr = checkNullAndTrim(rs.getString("cctr_code__cr"));
							disctType = checkNullAndTrim(rs.getString("discount_type"));
							status = checkNullAndTrim(rs.getString("status"));
							taxClassO = taxClass;
							taxChapO = taxChap;
							taxEnvO = taxEnv;
							empCodeQc = checkNullAndTrim(rs.getString("emp_code__qcaprv"));
							empCodeQcO = empCodeQc;
							dlvDateO = dlvDate;
							specificInstr = checkNullAndTrim(rs.getString("specific_instr"));
							specificInstrO = specificInstr;
							suppCodeMnfr = checkNullAndTrim(rs.getString("supp_code__mnfr"));
							suppCodeMnfrO = suppCodeMnfr;
							rateClg = checkNullAndTrim(rs.getString("rate__clg"));
							specialInstr = checkNullAndTrim(rs.getString("special_instr"));
							specialInstrO = specialInstr;
							benType = checkNullAndTrim(rs.getString("benefit_type"));
							benTypeO = benType;
							licNo = checkNullAndTrim(rs.getString("licence_no"));
							licNoO = licNo;
							formNO = checkNullAndTrim(rs.getString("form_no"));
							formNOold = formNO;
							dutyPaid = checkNullAndTrim(rs.getString("duty_paid"));
							dutyPaidO = dutyPaid;
							acctCodedrO = acctCodedr;
							cctrCodedrO = cctrCodedr;
							acctCodecrO = acctCodecr;
							cctrCodecrO = cctrCodecr;
							deptCode = checkNullAndTrim(rs.getString("dept_code"));
						}
						
						rs.close(); rs = null;
						pstmt.close(); pstmt = null;
						
						sql ="insert into poamd_det (amd_no,line_no,purc_order,line_no__ord,site_code,ind_no,item_code,quantity,quantity__o,"
								+ "unit,rate,rate__o,discount,tax_amt,tot_amt,loc_code,req_date,req_date__o,dlv_date,dlv_qty,tax_class,tax_chap,"
								+ "tax_env,remarks,work_order,unit__rate,conv__qty_stduom,conv__rtuom_stduom,unit__std,quantity__stduom,"
								+ "rate__stduom,pack_code,no_art,pack_instr,acct_code__dr,cctr_code__dr,acct_code__cr,cctr_code__cr,discount_type,"
								+ "status,tax_class__o,tax_chap__o,tax_env__o,emp_code__qcaprv,emp_code__qcaprv__o,dlv_date__o,specific_instr,"
								+ "specific_instr__o,supp_code_mnfr,supp_code_mnfr__o,rate__clg,special_instr,special_instr__o,"
								+ "benefit_type,benefit_type__o,licence_no,licence_no__o,form_no,form_no__o,duty_paid,duty_paid__o,"
								+ "acct_code__dr__o,cctr_code__dr__o,acct_code__cr__o,cctr_code__cr__o,dept_code )"
								+ "values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,"
								+ "?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,? )";
						
						pstmt = conn.prepareStatement(sql);

						pstmt.setString(1, amdNo);
						pstmt.setString(2, lineNo);
						pstmt.setString(3, purcOrder);
					  //pstmt.setString(4, lineNoOrd);
						lineNoOrd = "   "+lineNo;
						lineNoOrd = lineNoOrd.substring(lineNoOrd.length()-3);
						pstmt.setString(4, lineNoOrd);
						pstmt.setString(5, siteCode);
						pstmt.setString(6, indNo);
						pstmt.setString(7, itemCode);
						pstmt.setString(8, quantity);
						pstmt.setString(9, quantityO);
						pstmt.setString(10, unit);
						pstmt.setString(11, rate);
						pstmt.setString(12, rateO);
						pstmt.setString(13, discount);
						pstmt.setString(14, taxAmt);
						pstmt.setString(15, totAmt);
						pstmt.setString(16, locCode);
						pstmt.setTimestamp(17, reqDate);
						pstmt.setTimestamp(18, reqDateO);
						
						System.out.println("Date is="+newdlvDate);
						newdlvDate = genericUtility.getValidDateString( newdlvDate,genericUtility.getApplDateFormat(),genericUtility.getDBDateTimeFormat());
						System.out.println("After Date is="+newdlvDate);
						
						pstmt.setTimestamp(19, Timestamp.valueOf(newdlvDate));
						
						pstmt.setString(20, dlvQty);
						pstmt.setString(21, taxClass);
						pstmt.setString(22, taxChap);
						pstmt.setString(23, taxEnv);
						pstmt.setString(24, remarks);
						pstmt.setString(25, workOrder);
						pstmt.setString(26, unitRate);
						pstmt.setString(27, conqStduom);
						pstmt.setString(28, conrtStduom);
						pstmt.setString(29, unitStd);
						pstmt.setString(30, qtyStduom);
						pstmt.setString(31, rateStduom);
						pstmt.setString(32, packCode);
						pstmt.setString(33, noArt);
						pstmt.setString(34, packInstr);
						pstmt.setString(35, acctCodedr);
						pstmt.setString(36, cctrCodedr);
						pstmt.setString(37, acctCodecr);
						pstmt.setString(38, cctrCodecr);
						pstmt.setString(39, disctType);
						pstmt.setString(40, status);
						pstmt.setString(41, taxClassO);
						pstmt.setString(42, taxChapO);
						pstmt.setString(43, taxEnvO);
						pstmt.setString(44, empCodeApr);
						pstmt.setString(45, empCodeAprO);
						//pstmt.setString(46, dlvDateO);
						pstmt.setTimestamp(46, detdlvDate);
						pstmt.setString(47, specialInstr);
						pstmt.setString(48, specialInstrO);
						pstmt.setString(49, suppCodeMnfr);
						pstmt.setString(50, suppCodeMnfrO);
						pstmt.setString(51, rateClg);
						pstmt.setString(52, specialInstr);
						pstmt.setString(53, specialInstrO);
						pstmt.setString(54, benType);
						pstmt.setString(55, benTypeO);
						pstmt.setString(56, licNo);
						pstmt.setString(57, licNoO);
						pstmt.setString(58, formNO);
						pstmt.setString(59, formNOold);
						pstmt.setString(60, dutyPaid);
						pstmt.setString(61, dutyPaidO);
						pstmt.setString(62, acctCodedrO);
						pstmt.setString(63, cctrCodedrO);
						pstmt.setString(64, acctCodecrO);
						pstmt.setString(65, cctrCodecrO);
						pstmt.setString(66, deptCode);
					
						pstmt.executeUpdate();
						
						pstmt.close(); pstmt = null;
						conn.commit();
						System.out.println("Record inserted successfully in detail ....");
						
						/*************************************************************************/
							//String ordSite = "DDUK";//need to be taken from header
							//String id = "BASE";//need to be taken from header
							//String empcode = "99999";//need to be taken from header
							//String fFlag = "";
							
				            if(xtraParams != null)
				            {
				            	xtraParams = getXtraParams(id,loginSite,empcode,conn);
				            }
				            System.out.println("XTRA params is="+xtraParams);
				            
				            POrderAmdConfRemote poamdConf = null;
				            InitialContext contxt = new InitialContext( new AppConnectParm().getProperty() );
							poamdConf = (ibase.webitm.ejb.dis.POrderAmdConfRemote) contxt.lookup("ibase/POrderAmdConf/remote");
							String retStr = poamdConf.confirm(amdNo, xtraParams, fFlag);
				         
				            System.out.println("Return String="+retStr);
				            
				        /*************************************************************************/
				         
			}//end for loop		            
		}
		
		catch(Exception e)
		{
			isError = true;
			errString = "ERROR";
			try
			{
				conn.rollback();
			}
			catch(Exception e1)
			{
				e1.printStackTrace();
			}
			e.printStackTrace();
			throw new ITMException(e);
		}
		finally
		{
			try
			{
				if ( rs != null )
				{
					rs.close();
					rs = null;
				}
				if( pstmt != null )
				{
					pstmt.close();
					pstmt = null;
				}
			}
			catch( Exception e)
			{
				e.printStackTrace();
				throw new ITMException(e);
			}
		}
		System.out.println("errString from generatePorder =["+errString+"]");
		return retString;			
                
	}
	
	private static String checkNullAndTrim(String input)
	{
		if (input==null)
		{
			input="";
		}
		return input.trim();
	}
	
	private String generateTranId( String windowName, String siteCode, String tranDateStr, Connection conn )throws ITMException
	{
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String selSql = "";
		String tranId = "";
		String tranSer = "";
		String keyString = "";
		String keyCol = "";
		String xmlValues = "";
		String paySiteCode = "";
		String effectiveDate = "";

		java.sql.Date effDate = null;

		try
		{
			selSql = "SELECT KEY_STRING, TRAN_ID_COL, REF_SER FROM TRANSETUP WHERE TRAN_WINDOW = ? ";
			//System.out.println("selSql :"+selSql);
			pstmt = conn.prepareStatement(selSql);
			pstmt.setString( 1, windowName );
			rs = pstmt.executeQuery();
			if (rs.next())
			{
				keyString = rs.getString("KEY_STRING");
				keyCol = rs.getString("TRAN_ID_COL");
				tranSer = rs.getString("REF_SER");
			}
			rs.close();rs = null;
			pstmt.close();pstmt = null;
			System.out.println("keyString :"+keyString);
			System.out.println("keyCol :"+keyCol);
			System.out.println("tranSer :"+tranSer);

			xmlValues ="<?xml version=\"1.0\" encoding=\"utf-8\"?><Root>";
			xmlValues = xmlValues + "<Header></Header>";
			xmlValues = xmlValues + "<Detail1>";
			xmlValues = xmlValues +		"<tran_id></tran_id>";
			xmlValues = xmlValues +		"<site_code>" + siteCode + "</site_code>";
			xmlValues = xmlValues +		"<site_code__ord__o>" + siteCode + "</site_code__ord__o>";
			xmlValues = xmlValues +		"<amd_date>" + tranDateStr + "</amd_date>"; 
			xmlValues = xmlValues + "</Detail1></Root>";
			System.out.println("xmlValues  :["+xmlValues+"]");
			TransIDGenerator tg = new TransIDGenerator(xmlValues, "BASE", CommonConstants.DB_NAME);
			tranId = tg.generateTranSeqID(tranSer, keyCol, keyString, conn);
			System.out.println("tranId :"+tranId);

		}
		catch (SQLException ex)
		{
			System.out.println("Exception ::" +selSql+ ex.getMessage() + ":");
			ex.printStackTrace();
			throw new ITMException(ex);
		}
		catch (Exception e)
		{
			System.out.println("Exception ::" + e.getMessage() + ":");
			e.printStackTrace();
			throw new ITMException(e);
		}
		finally
		{
			try
			{
				if (rs != null)
				{
					rs.close();
					rs = null;
				}
				if (pstmt != null)
				{
					pstmt.close();
					pstmt = null;
				}
			}
			catch(Exception e)
			{

			}
		}
		return tranId;
	}//generateTranTd()
	
	public String getXtraParams( String loginCode, String siteCode,String empCode,Connection  conn)throws Exception
	{
		String remotehost = "";
		String entityCode = "";
		String profileId = "";
		String userType = "";
		String userLang = "";
		String userCountry = "";	
		StringBuffer xtraParamsBuff = new StringBuffer();
		
		String sql = "";
		PreparedStatement pstmt = null ; 
		ResultSet rs = null;
			
		
		try
		{
				sql = "SELECT USR_LEV,USER_TYPE,ENTITY_CODE,PROFILE_ID,LOGGER_TYPE,USER_LANG,"+
					  "USER_COUNTRY,TRANS_DB, USER_THEME FROM USERS WHERE CODE = ? ";
				pstmt = conn.prepareStatement(sql);			
				pstmt.setString(1,loginCode);			
				rs = pstmt.executeQuery();
				while(rs.next())
				{
					entityCode = rs.getString("ENTITY_CODE");
					profileId  = rs.getString("PROFILE_ID");
					userType   = rs.getString("USER_TYPE");
					userLang   = rs.getString("USER_LANG");
					userCountry= rs.getString("USER_COUNTRY");
				}			
				
				remotehost =  InetAddress.getLocalHost().getHostAddress();
				String XTRA_PARAMS_SEPARATOR = "~~";
				
				xtraParamsBuff.append("loginCode=" + loginCode);
				xtraParamsBuff.append(XTRA_PARAMS_SEPARATOR).append("loginEmpCode=" + empCode);
				xtraParamsBuff.append(XTRA_PARAMS_SEPARATOR).append("loginSiteCode=" + siteCode);
				xtraParamsBuff.append(XTRA_PARAMS_SEPARATOR).append("entityCode=" + entityCode);
				xtraParamsBuff.append(XTRA_PARAMS_SEPARATOR).append("profileId=" + profileId);
				xtraParamsBuff.append(XTRA_PARAMS_SEPARATOR).append("userType=" + userType);
				xtraParamsBuff.append(XTRA_PARAMS_SEPARATOR).append("runMode=I");
				xtraParamsBuff.append(XTRA_PARAMS_SEPARATOR).append("user_lang=" + userLang);
				xtraParamsBuff.append(XTRA_PARAMS_SEPARATOR).append("user_country=" + userCountry);
				xtraParamsBuff.append(XTRA_PARAMS_SEPARATOR).append("charEnc=" + CommonConstants.ENCODING);
				
				xtraParamsBuff.append(XTRA_PARAMS_SEPARATOR).append("termId="+remotehost);
		
				String chgTerm = remotehost;
				if( chgTerm != null && chgTerm.length() > 15 )
				{
					chgTerm = chgTerm.substring(0, 15);
				}
				else if( chgTerm == null || chgTerm.length() == 0 )
				{
					chgTerm = remotehost;
				}
				xtraParamsBuff.append(XTRA_PARAMS_SEPARATOR).append("chgTerm=" + chgTerm );
				
				System.out.println("xtraParamsBuff ["+xtraParamsBuff+"]");
				
				
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
		finally
		{
			try
			{
				if(rs != null)
				{
					rs.close();
					rs = null;
				}
				if(pstmt != null)
				{
					pstmt.close();
					pstmt = null;
				}
			}
			catch(Exception e)
			{
				e.printStackTrace();
			}
		}
		return xtraParamsBuff.toString();
	}
	
	public String getUserInfo( String xtraParams )throws ITMException
	{
		StringBuffer userInfoStr = new StringBuffer();
		String userId = "";
		String loginEmpCode = "";
		String loginSiteCode = "";
		String entityCode = "";
		String profileId = "";
		String userType = "";
		
		String chgTerm = "";
		try
		{
			GenericUtility genericUtility = GenericUtility.getInstance();
			userId = genericUtility.getValueFromXTRA_PARAMS(xtraParams,"loginCode");
			loginEmpCode = genericUtility.getValueFromXTRA_PARAMS(xtraParams,"loginEmpCode");
			loginSiteCode = genericUtility.getValueFromXTRA_PARAMS(xtraParams,"loginSiteCode");
			entityCode = genericUtility.getValueFromXTRA_PARAMS(xtraParams,"entityCode");
			profileId = genericUtility.getValueFromXTRA_PARAMS(xtraParams,"profileId");
			userType = genericUtility.getValueFromXTRA_PARAMS(xtraParams,"userType");
			
			chgTerm = genericUtility.getValueFromXTRA_PARAMS(xtraParams,"chgTerm");
			
			System.out.println("xtraParams  is @@@@@ " + xtraParams);

			userInfoStr.append("<UserInfo>");
			userInfoStr.append("<loginCode>").append("<![CDATA["+userId+"]]>").append("</loginCode>\r\n");
			userInfoStr.append("<empCode>").append("<![CDATA["+loginEmpCode+"]]>").append("</empCode>\r\n");
			userInfoStr.append("<siteCode>").append("<![CDATA["+loginSiteCode+"]]>").append("</siteCode>\r\n");
			userInfoStr.append("<entityCode>").append("<![CDATA["+entityCode+"]]>").append("</entityCode>\r\n");
			userInfoStr.append("<profileId>").append("<![CDATA["+profileId+"]]>").append("</profileId>\r\n");
			userInfoStr.append("<userType>").append("<![CDATA["+userType+"]]>").append("</userType>\r\n");
			
			userInfoStr.append("<remoteHost>").append("<![CDATA["+chgTerm+"]]>").append("</remoteHost>\r\n");
			userInfoStr.append("</UserInfo>");
		}
		catch ( Exception e )
		{
			throw new ITMException(e);
		}
		return userInfoStr.toString();
	}
}
