package ibase.webitm.ejb.mfg;

import java.rmi.RemoteException;

import ibase.system.config.ConnDriver;
import ibase.webitm.ejb.*;
import ibase.webitm.ejb.dis.DistCommon;
import ibase.webitm.ejb.dis.StockUpdate;
import ibase.webitm.ejb.dis.adv.StockTransferConf;
import ibase.webitm.ejb.sys.CreateRCPXML;

import org.w3c.dom.*;

import java.sql.*;
import java.text.SimpleDateFormat;

import ibase.webitm.utility.GenericUtility;
import ibase.webitm.utility.ITMException;

import javax.ejb.Stateless;

import java.util.Calendar;
import java.util.HashMap;

@Stateless
public class QCTransferConf extends ActionHandlerEJB implements
QCTransferConfLocal, QCTransferConfRemote
{
	

	public String actionHandler() throws RemoteException, ITMException
	{
		return "";
	}

	public String actionHandler(String actionType, String xmlString,
			String objContext, String xtraParams) throws RemoteException,
			ITMException 
			{
		System.out.println("item actionHandler(...) called............");
		String str = "";
		System.out.println("actionType---" + actionType);
		System.out.println("xmlString---" + xmlString);
		System.out.println("objContext---" + objContext);
		System.out.println("xtraParams---" + xtraParams);
		return str;
			}
	public String confirm(String tranId, String xtraParams, String forcedFlag)
			throws RemoteException, ITMException
			{
		Connection conn=null;
		String errString="";
		errString=confirm(tranId,xtraParams,forcedFlag,conn);
		System.out.println("errString----"+errString);
		return errString;
			}
//	public String confirm(String tranId, String xtraParams, String forcedFlag)
	public String confirm(String tranId, String xtraParams, String forcedFlag, Connection conn)
			throws RemoteException, ITMException
			{
		System.out.println("QCTransferConf confirm called...230914...........");
		String confirmed = "";
		String sql = "";
//		Connection conn = null;
		PreparedStatement pstmt = null;
		ITMDBAccessEJB itmDBAccessEJB = null;
		String errString = "",errCode="";
		ResultSet rs = null;
		//int recCnt = 0, cnt = 0;
		ITMDBAccessEJB itmDBAccessLocal = new ITMDBAccessEJB();
		boolean isLocal=false;	

		try 
		{
			
			itmDBAccessEJB = new ITMDBAccessEJB();
			
			if(conn==null){
			ConnDriver connDriver = null;
			connDriver = new ConnDriver();
			conn = connDriver.getConnectDB("DriverITM");
			conn.setAutoCommit(false);
			isLocal=true;
			}

			if (errString == null || errString.trim().length() == 0)
			{
				//if ( tranIdFr.equalsIgnoreCase(tranIdTo) )
				//{
				//	tranId = tranIdFr;
				//}

				if (tranId != null && tranId.trim().length() > 0) 
				{
					sql = "	select confirmed from   qc_transfer where tran_id = ? ";
					pstmt = conn.prepareStatement(sql);
					pstmt.setString(1, tranId);
					rs = pstmt.executeQuery();
					if (rs.next()) 
					{
						confirmed = rs.getString(1);
					}
					rs.close();
					rs = null;
					pstmt.close();
					pstmt = null;

					if ("Y".equalsIgnoreCase(confirmed)) 
					{
						errCode = "QCTRFCONF";   //'QC Transfer Already Confirmed'
						errString = itmDBAccessLocal.getErrorString("",errCode,"");
					}	
					else
					{
						//errCode = confirmQcTransfer(tranId,1, xtraParams,conn);
						errString = confirmQcTransfer(tranId,1, xtraParams,conn);
						System.out.println("@@@@ confirmQcTransfer::errCode["+errString+"]");
						/*Commented by Manoj dtd 03/06/2014
						 if( errCode != null && errCode.trim().length() > 0 )
						{
							errString = itmDBAccessLocal.getErrorString("",errCode,"");
						}*/
					}


				}
				if (errString == null || errString.trim().length() == 0) 
				{
					errCode = "VTCONFIRM";
					errString = itmDBAccessLocal.getErrorString("",errCode,"");
					return errString;
				}

			} // end if errstrng


		} catch (Exception e) 
		{
			e.printStackTrace();
			throw new ITMException(e);
		} 
		finally 
		{
			try 
			{
				if (errString != null && errString.trim().length() > 0	&& !("VTCONFIRM".equalsIgnoreCase(errCode)) ) 
				{
//					if(isLocal){
					conn.rollback();
					System.out.println("@@@@ Transaction rollback...["+errString+"]::errCode["+errCode+"]");
					if(isLocal){
					conn.close();
					conn = null;
					}
				}
				else 
				{
					if(isLocal){
					conn.commit(); // test
					System.out.println("c"+errString+"]::errCode["+errCode+"]");
					conn.close();
					conn = null;
					}else{
						System.out.println("DIDNT COMMIT ********8");
					}
				}
			}
			catch (Exception e) 
			{
				e.printStackTrace();
				throw new ITMException(e);
			}
		}
		return errString;
			}
	


	private String confirmQcTransfer(String tranId, int i, String xtraParams, Connection conn)
			throws RemoteException, ITMException

			{
		PreparedStatement pstmt = null, pstmtSql = null;
		ResultSet rs = null;
		int cnt = 0,updCnt=0,updCnt1=0;
		String retString = "", sql = "", errCode = "";
		Timestamp sysDate = null, tranDate = null;
		GenericUtility genericUtility = GenericUtility.getInstance();
		ITMDBAccessEJB itmDBAccessLocal = new ITMDBAccessEJB();
		String confirmed="",qorderNo="",siteCode="",toLoc="",lotSl="",lotNo="",porcpNo="",fromLoc="",itemCode="";
		String status="",ediOption="",dataStr = "";
		double qty=0,qcQty=0,stkGrossWt=0,stkTareWt=0,stkNetWt=0,qtyPerArt=0,grossWtPerArt=0,tareWtPerArt=0,stockQty=0;
		HashMap stockUpd = new HashMap();

		String available="";
		double aprvLeadTime = 0;
		Timestamp qcOrderDate = null,newQCOrderDate=null; // added by cpatil on 02/11/13
		String qcOrderItem="";
		String qcOrdItemUnit="",byprodUnit="";
		double convertedQty=0,netWeight=0;
		boolean isByProduct=false;
		
		double noArt=0,convertedNoArt=0,convertedNetWeight=0;
		double rate=0;
		double grossRate=0;
		String dimension="";
		boolean isFound=false;
		double noArtQCTransfer=0,netWeightQCTransfer=0;
		try 
		{
			StockUpdate stockUpdate = new StockUpdate();
			DistCommon dcommon=new DistCommon();
			System.out.println("@@@@@@@@@@@@@@@ confirmQcTransfer method called next..............");
			Calendar currentDate = Calendar.getInstance();
			SimpleDateFormat sdf = new SimpleDateFormat(genericUtility.getApplDateFormat());
			String sysDateStr = sdf.format(currentDate.getTime());
			System.out.println("Now the date is :=>  " + sysDateStr);
			sysDate= Timestamp.valueOf(genericUtility.getValidDateString(sysDateStr, genericUtility.getApplDateFormat(),genericUtility.getDBDateFormat()) + " 00:00:00.0");

			sql = " Select confirmed, qorder_no, tran_date ,site_code,loc_code__trf,quantity," +
					" lot_sl,lot_no,porcp_no, loc_code,item_code,case when dimension is null then '' else dimension end as dimension" +
					" , no_art, net_weight " +    //added by cpatil on 10/10/14
					" from qc_transfer where tran_id = ? ";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, tranId);
			rs = pstmt.executeQuery();
			if (rs.next())
			{
				confirmed = rs.getString("confirmed");
				qorderNo= rs.getString("qorder_no");
				tranDate= rs.getTimestamp("tran_date");
				siteCode= rs.getString("site_code");
				toLoc= rs.getString("loc_code__trf");
				qty= rs.getDouble("quantity");
				lotSl= rs.getString("lot_sl");
				lotNo= rs.getString("lot_no");
				porcpNo= rs.getString("porcp_no");
				fromLoc= rs.getString("loc_code");
				itemCode= rs.getString("item_code");
				dimension=rs.getString("dimension");   
				noArtQCTransfer= rs.getDouble("no_art");     // remove comment by cpatil on 10/10/14
				netWeightQCTransfer= rs.getDouble("net_weight");    // remove comment by cpatil on 10/10/14
				
			}
			rs.close();
			rs = null;
			pstmt.close();
			pstmt = null;


			sql = " Select status,Quantity, item_code, net_weight, no_art from qc_order where qorder_no = ? ";  // added net_weight by cpatil on 05/04/14
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, qorderNo);
			rs = pstmt.executeQuery();
			if (rs.next())
			{
				cnt++;
				status = rs.getString("status");
				qcQty= rs.getDouble("Quantity");
				qcOrderItem= rs.getString("item_code");
				netWeight= rs.getDouble("net_weight");
				noArt= rs.getDouble("no_art");
			}
			rs.close();
			rs = null;
			pstmt.close();
			pstmt = null;


			if ( cnt == 0)
			{
				errCode = "QCTRFORD";   //'QC order is not found'
				return itmDBAccessLocal.getErrorString("",errCode,"");
				//return errCode;//Commented by Manoj dtd 03/06/2014
			}
			else if("C".equalsIgnoreCase(status)) 
			{
				errCode = "QCTRFORD";    //'QC order is CONFIRMED cannot Transfer Quantity'
				//return errCode;
				return itmDBAccessLocal.getErrorString("",errCode,"");
				//return errCode;//Commented by Manoj dtd 03/06/2014
			}

			///added by cpatil on 12/12/13 for date validation with approval lead time start

			System.out.println("@@@@ tranDate new validation start----qorderNo["+qorderNo+"]---status["+status+"]-- ");

			if (toLoc != null)
			{
				sql = " Select Available from location a, invstat b "
						+ " Where a.loc_code = ? and a.inv_stat = b.inv_stat ";
				pstmt = conn.prepareStatement(sql);
				pstmt.setString(1, toLoc);
				rs = pstmt.executeQuery();
				if (rs.next())
				{
					available = rs.getString("available");
				}
				rs.close();
				rs = null;
				pstmt.close();
				pstmt = null;

				if("Y".equalsIgnoreCase(available))	
				{
					sql = " Select aprv_lead_time from siteitem where   site_code =  ? and item_code = ? ";
					pstmt = conn.prepareStatement(sql);
					pstmt.setString(1, siteCode);
					pstmt.setString(2, itemCode);
					rs = pstmt.executeQuery();
					if (rs.next()) 
					{
						aprvLeadTime = rs.getDouble("aprv_lead_time");
					}
					rs.close();
					rs = null;
					pstmt.close();
					pstmt = null;

					sql = " select qorder_date from qc_order where qorder_no = ? ";
					pstmt = conn.prepareStatement(sql);
					pstmt.setString(1, qorderNo);
					rs = pstmt.executeQuery();
					if (rs.next())
					{
						qcOrderDate = rs.getTimestamp("qorder_date");
					}
					rs.close();
					rs = null;
					pstmt.close();
					pstmt = null;

					System.out.println("@@@@ site code[" + siteCode+ "]::item code[" + itemCode+ "]::aprvLeadTime[" + aprvLeadTime
							+ "]::qcOrderDate[" + qcOrderDate + "]:::tranDate["+tranDate+"]");

					Calendar cal = Calendar.getInstance();
					cal.setTime(qcOrderDate);
					cal.add(Calendar.DAY_OF_WEEK,(int) Math.round(aprvLeadTime));
					newQCOrderDate = new Timestamp(cal.getTime().getTime());
					System.out.println("@@@@ tranDate ["+ tranDate + "]:::: newQCOrderDate ["+ newQCOrderDate + "]:::::");

					if( tranDate.before(newQCOrderDate)) 
					{
						errCode = "VTAPPRLEAD";
						return itmDBAccessLocal.getErrorString("",errCode,"");
						//return errCode;//Commented by Manoj dtd 03/06/2014
						
					}

				}
			}
			System.out.println("@@@@ tranDate new validation end---------errCode["+ errCode + "]");

			///added by cpatil on 12/12/13 for date validation with approval lead time end

			if( errCode == null || errCode.trim().length() == 0 ) 
			{
				sql = " Select quantity from qc_order where qorder_no = ? ";
				pstmt = conn.prepareStatement(sql);
				pstmt.setString(1, qorderNo);
				rs = pstmt.executeQuery();
				if (rs.next())
				{
					cnt++;
					qcQty= rs.getDouble("Quantity");
				}
				rs.close();
				rs = null;
				pstmt.close();
				pstmt = null;
				sql="SELECT UNIT FROM ITEM WHERE ITEM_CODE=?";
				pstmt=conn.prepareStatement(sql);
				pstmt.setString(1,itemCode);
				rs=pstmt.executeQuery();
				if(rs.next())
				{
					byprodUnit=rs.getString("UNIT");
				}
				rs.close();
				rs=null;
				pstmt.close();
				pstmt=null;
				
				sql="SELECT UNIT FROM ITEM WHERE ITEM_CODE=?";
				pstmt=conn.prepareStatement(sql);
				pstmt.setString(1,qcOrderItem);
				rs=pstmt.executeQuery();
				if(rs.next())
				{
					qcOrdItemUnit=rs.getString("UNIT");
				}
				rs.close();
				rs=null;
				pstmt.close();
				pstmt=null;
				
				//if(!byprodUnit.equalsIgnoreCase(qcOrdItemUnit))
				if(!qcOrderItem.equalsIgnoreCase(itemCode))
				{
					/*  // comment this code as per suggestion of sandip
					convertedQty=roundVal(dcommon.convQtyFactor(byprodUnit, qcOrdItemUnit, "X", qty, conn),3);  // commented by cpatil on 05/04/14  // remove comment on 08/10/14 by cpatil
					
					// modify by cpatil 05/04/14 start  //  // modify by cpatil on 09/04/14
					
					System.out.println("@@@@ qc order Qty[qcQty]["+qcQty+"]qc transfer qty[qty]["+qty+"]netWeight["+netWeight+"]noArt["+noArt+"]");
					
					// convertedQty =   qty * qcQty  / netWeight ;   // comment line as per suggestion on 08/10/14 by cpatil and remove comment from above line of convertedQty calculation 
					
					//convertedNetWeight =  netWeight ;
					convertedNetWeight =  netWeight * convertedQty / qcQty  ;
					
					convertedNoArt =  noArt * qty  / netWeight ;
					
					System.out.println("@@@@convertedQty["+convertedQty+"]convertedNetWeight["+convertedNetWeight+"]convertedNoArt["+convertedNoArt+"]");
					
					//// cpatil end   // modify by cpatil on 09/04/14  end
					
					System.out.println("@@@----qcQty["+qcQty+"]convertedQty["+convertedQty+"]");
					if(qcQty < convertedQty )
					{
						errCode = "QCTRFQTY";    //'QC Transfer quantity more then QC Order Quantity'
						return itmDBAccessLocal.getErrorString("",errCode,"");
						//return errCode;//Commented by Manoj dtd 03/06/2014
					}
					isByProduct=true;
					//itemCode=qcOrderItem;
				 */					
					// added by cpatil start on 10/10/14 as per suggestion and logic of sandip maurya 
					if(byprodUnit.equalsIgnoreCase(qcOrdItemUnit))
					{
						convertedQty=roundVal(dcommon.convQtyFactor(byprodUnit, qcOrdItemUnit, "X", qty, conn),3);  // commented by cpatil on 05/04/14  // remove comment on 08/10/14 by cpatil
					}
					else
					{
						convertedQty =   qty * qcQty  / netWeight ;
					}
					
					System.out.println("@@@----qcQty order ["+qcQty+"]qty qc transfer["+qty+"]");
					System.out.println("@@@@ qc order netWeight["+netWeight+"]qc transfer netWeightQCTransfer["+netWeightQCTransfer+"]");
					System.out.println("@@@@ qc order noArt["+noArt+"]qc transfer noArtQCTransfer["+noArtQCTransfer+"]");
					if( netWeight < netWeightQCTransfer )
					{
						errCode = "QCTRFNTWET";    //'QC Transfer net weight more then QC Order net weight'
						return itmDBAccessLocal.getErrorString("",errCode,"");
					}
					if( noArt < noArtQCTransfer )
					{
						errCode = "QCTRFNOART";    //'QC Transfer no art more then QC Order no art'
						return itmDBAccessLocal.getErrorString("",errCode,"");
					}
					isByProduct=true;
					
					convertedNetWeight = netWeightQCTransfer;	
					convertedNoArt = noArtQCTransfer;
					System.out.println("@@@@convertedQty["+convertedQty+"]convertedNetWeight["+convertedNetWeight+"]convertedNoArt["+convertedNoArt+"]");
					// ended by cpatil 10/10/14
				}
				else
				{
					System.out.println("@@@----qcQty["+qcQty+"]qty["+qty+"]");
					if(qcQty < qty )
					{
						errCode = "QCTRFQTY";    //'QC Transfer quantity more then QC Order Quantity'
						return itmDBAccessLocal.getErrorString("",errCode,"");
						//return errCode;//Commented by Manoj dtd 03/06/2014
					}
				}
			}

			if ( errCode == null || errCode.trim().length() == 0 )
			{		
				cnt=0;
				sql = " Select item_ser,mfg_date,exp_date,retest_date,site_code__mfg,potency_perc,pack_code,inv_stat," +
						" unit,gross_weight,tare_weight, net_weight,pack_instr,dimension,rate,rate__oh,acct_code__inv," +
						" cctr_code__inv,acct_code__oh,cctr_code__oh,supp_code__mfg,grade,gross_rate,conv__qty_stduom," +
						" unit__alt,batch_no,no_art,qty_per_art,gross_wt_per_art,tare_wt_per_art,quantity " +
						" from  Stock" +
						" Where item_code = ? and site_code = ? and loc_code = ? and lot_no = ? and lot_Sl = ? ";
				pstmt = conn.prepareStatement(sql);
				if(isByProduct)
				{
					pstmt.setString(1, qcOrderItem);
				}
				else
				{
					pstmt.setString(1, itemCode);	
				}
				
				pstmt.setString(2, siteCode);
				pstmt.setString(3, fromLoc);
				pstmt.setString(4, lotNo);
				pstmt.setString(5, lotSl);
				rs = pstmt.executeQuery();
				if (rs.next())
				{
					cnt++;
					stockUpd.put("item_ser",rs.getString("item_ser"));
					stockUpd.put("mfg_date", rs.getTimestamp("mfg_date"));
					stockUpd.put("exp_date", rs.getTimestamp("exp_date"));
					stockUpd.put("retest_date", rs.getTimestamp("retest_date"));
					stockUpd.put("site_code__mfg", rs.getString("site_code__mfg"));
					stockUpd.put("potency_perc", rs.getString("potency_perc"));
					stockUpd.put("pack_code", rs.getString("pack_code"));
					stockUpd.put("inv_stat", rs.getString("inv_stat"));
					stockUpd.put("unit", rs.getString("unit"));
					stkGrossWt =  rs.getDouble("gross_weight");   
					stkTareWt =  rs.getDouble("tare_weight");     
					stkNetWt = rs.getDouble("net_weight");       
					stockUpd.put("pack_instr", rs.getString("pack_instr"));
					//stockUpd.put("dimension", rs.getString("dimension")); //Commented by Manoj dtd 30/05/2014 set from qc_transfer
					stockUpd.put("dimension", dimension);
					stockUpd.put("rate", rs.getString("rate"));
					stockUpd.put("rate_oh", rs.getString("rate__oh"));  
					stockUpd.put("acct_code_inv", rs.getString("acct_code__inv"));  
					stockUpd.put("cctr_code_inv", rs.getString("cctr_code__inv")); 
					stockUpd.put("acct_code_oh", rs.getString("acct_code__oh"));    
					stockUpd.put("cctr_code_oh", rs.getString("cctr_code__oh"));    
					stockUpd.put("supp_code__mfg", rs.getString("supp_code__mfg"));
					stockUpd.put("grade", rs.getString("grade"));
					stockUpd.put("gross_rate", rs.getString("gross_rate"));
					stockUpd.put("conv__qty_stduom", rs.getString("conv__qty_stduom"));
					stockUpd.put("unit__alt", rs.getString("unit__alt"));
					stockUpd.put("batch_no", rs.getString("batch_no"));
					stockUpd.put("no_art", rs.getString("no_art"));
					qtyPerArt =  rs.getDouble("qty_per_art");    
					grossWtPerArt =  rs.getDouble("gross_wt_per_art");  
					tareWtPerArt = rs.getDouble("tare_wt_per_art");   
					stockQty = rs.getDouble("quantity");
					rate=rs.getDouble("rate");
					grossRate=rs.getDouble("gross_rate");
				}
				rs.close();
				rs = null;
				pstmt.close();
				pstmt = null;

				if( cnt == 0 )
				{	
					errCode = "QCTRFSTK";   //'Item Not Found in Stock...'
					System.out.println("@@@@@@@@@@@@  [QCTRFSTK]");
					return itmDBAccessLocal.getErrorString("",errCode,"");
					//return errCode;//Commented by Manoj dtd 03/06/2014
				}//End if

				if( errCode == null || errCode.trim().length() == 0 )
				{	
					//Added by manoj dtd 31/05/2014 to set rate for byproduct
					if(isByProduct)
					{
						String considerYield="N";
						double costRate=0;
						sql="select case when consider_yield is null then 'N' else consider_yield end consider_yield from byproduct where item_code__byprod=? and item_code=?";
						pstmt=conn.prepareStatement(sql);
						pstmt.setString(1,itemCode);
						pstmt.setString(2,qcOrderItem);
						rs = pstmt.executeQuery();
						if(rs.next())
						{   
							considerYield=rs.getString(1);
						}
						rs.close();
						rs=null;
						pstmt.close();
						pstmt=null;
						if(considerYield.equalsIgnoreCase("N"))
						{
							
							sql="select cost_rate from siteitem where site_code=? and item_code=?";
							pstmt=conn.prepareStatement(sql);
							pstmt.setString(1,siteCode);
							pstmt.setString(2,itemCode);
							rs=pstmt.executeQuery();
							if(rs.next())
							{
								costRate=rs.getDouble(1);
								isFound=true;
								
							}
							rs.close();
							rs=null;
							pstmt.close();
							pstmt=null;
							
							if(!isFound)
							{
								sql="select cost_rate from item where item_code=?";
								pstmt=conn.prepareStatement(sql);
								pstmt.setString(1,itemCode);
								rs=pstmt.executeQuery();
								if(rs.next())
								{
									costRate=rs.getDouble(1);
								}
								rs.close();
								rs=null;
								pstmt.close();
								pstmt=null;
							}
							stockUpd.put("rate", costRate);
							stockUpd.put("gross_rate", costRate);

						}
						else
						{
							double convQty=0;
							
							convQty=dcommon.convQtyFactor(qcOrdItemUnit,byprodUnit, "X", convertedQty, conn);
							
							System.out.println("convQty-----"+convQty);
							System.out.println("rate before conversion-----"+rate);
							rate=roundVal((rate/convertedQty)*convQty,3);
							System.out.println("rate after conversion-----"+rate);
							grossRate=roundVal((grossRate/convertedQty)*convQty,3);
							stockUpd.put("rate", rate);
							stockUpd.put("gross_rate", grossRate);
							
						}
						
						
						
					}
					sql = " Select unit From item where item_code = ? ";
					pstmt =  conn.prepareStatement(sql);
					if(isByProduct)
					{
						pstmt.setString(1,qcOrderItem);
					}
					else
					{
						pstmt.setString(1,itemCode);
					}
					rs = pstmt.executeQuery();
					if(rs.next())
					{   
						stockUpd.put("unit", rs.getString("unit"));
					}
					rs.close();
					rs = null;
					pstmt.close(); 
					pstmt = null;

					sql = "	Select inv_stat from location Where loc_code = ? ";
					pstmt =  conn.prepareStatement(sql);
					pstmt.setString(1,fromLoc);
					rs = pstmt.executeQuery();
					if(rs.next())
					{   
						stockUpd.put("inv_stat", rs.getString("inv_stat"));
					}
					rs.close();
					rs = null;
					pstmt.close(); 
					pstmt = null;

					stockUpd.put("tran_type", "I");
					stockUpd.put("tran_ser","QC-TRF" );
					stockUpd.put("tranid",tranId );   //pending
					if(isByProduct)
					{
						stockUpd.put("item_code", qcOrderItem);
					}
					else
					{
						stockUpd.put("item_code", itemCode);	
					}
					
					stockUpd.put("site_code", siteCode);
					stockUpd.put("loc_code", fromLoc );
					stockUpd.put("lot_no",lotNo );
					stockUpd.put("lot_sl",lotSl );
					stockUpd.put("tran_date", tranDate);
					if(isByProduct)
					{
						stockUpd.put("quantity",convertedQty );
						stockUpd.put("qty_stduom",convertedQty );
					}
					else
					{
						stockUpd.put("quantity",qty );
						stockUpd.put("qty_stduom",qty );	
					}
					

					if ( qtyPerArt == 0 )		//isnull(lc_qtyperart) or lc_qtyperart = 0 then 
					{
						qtyPerArt = 1;
					}

					stockUpd.put("no_art",Math.ceil(Double.parseDouble(stockUpd.get("qty_stduom").toString())/qtyPerArt));
					if(stockQty != 0 )
					{
						stockUpd.put("net_weight",stkNetWt / stockQty * Double.parseDouble( stockUpd.get("qty_stduom").toString()));
						stockUpd.put("tare_weight", tareWtPerArt *  Double.parseDouble(stockUpd.get("no_art").toString()));
						stockUpd.put("gross_weight", ( Double.parseDouble(stockUpd.get("net_weight").toString()) + Double.parseDouble(stockUpd.get("tare_weight").toString())) );
					}//end if
					// ls_errcode = nvo_dis.gbf_update_stock(lstr_stkupd)
					errCode	= stockUpdate.updateStock(stockUpd,xtraParams, conn);
					if(errCode !=null && errCode.trim().length() >0)  //if(retString !=null && retString.trim().length() >0)
					{
						return errCode;
					}

				}//end if

				if( errCode == null || errCode.trim().length() == 0 )
				{	
					sql = " Select inv_stat from location Where loc_code = ? ";
					pstmt =  conn.prepareStatement(sql);
					pstmt.setString(1,toLoc);
					rs = pstmt.executeQuery();
					if(rs.next())
					{   
						stockUpd.put("inv_stat", rs.getString("inv_stat"));
					}
					rs.close();
					rs = null;
					pstmt.close(); 
					pstmt = null;


					if(isByProduct)
					{
						stockUpd.put("quantity",qty );
						stockUpd.put("qty_stduom",qty );	
						stockUpd.put("item_code",itemCode);
					}
					stockUpd.put("tran_type", "R");
					stockUpd.put("loc_code", toLoc );

					if ( qtyPerArt == 0 )
					{
						qtyPerArt = 1;
					}		

					stockUpd.put("no_art", Math.ceil( Double.parseDouble(stockUpd.get("qty_stduom").toString()) / qtyPerArt));		

					if ( stockQty != 0 )
					{
						stockUpd.put("net_weight",stkNetWt / stockQty *  Double.parseDouble(stockUpd.get("qty_stduom").toString()));
						stockUpd.put("tare_weight", tareWtPerArt *  Double.parseDouble(stockUpd.get("no_art").toString()));
						stockUpd.put("gross_weight", ( Double.parseDouble(stockUpd.get("net_weight").toString()) +  Double.parseDouble(stockUpd.get("tare_weight").toString())));
					}//end if
					//ls_errcode = nvo_dis.gbf_update_stock(lstr_stkupd)
					errCode	= stockUpdate.updateStock(stockUpd,xtraParams, conn);

					if(errCode !=null && errCode.trim().length() >0)  //if(retString !=null && retString.trim().length() >0)
					{
						return errCode;
					}
				} //End if
			} //End if


			if (errCode == null || errCode.trim().length() == 0 )
			{																			//if  gs_run_mode = 'I' then
				
				
				
				
				if(isByProduct)
				{
					if ( qcQty > convertedQty )
					{	
						sql = " Update qc_order set quantity = ( quantity - ? ) " +
							  " , net_weight = ( net_weight - ? ) , no_art = ( no_art - ? ) " +    // modify by cpatil on 09/04/14
							  "  where qorder_no = ? ";
						pstmt = conn.prepareStatement(sql);
						pstmt.setDouble(1, convertedQty );
						pstmt.setDouble(2, convertedNetWeight );
						pstmt.setDouble(3, convertedNoArt );
						pstmt.setString(4, qorderNo);
						updCnt = pstmt.executeUpdate();
						pstmt.close();
						pstmt = null;
					}
					else
					{
						sql = " Update qc_order set quantity = (quantity - ? ) " +
						 //   " , status = 'C' " +                                                 //  modify by cpatil on 20/12/14 -- comment condition
							  " , net_weight = ( net_weight - ? ) , no_art = ( no_art - ? ) " +    // modify by cpatil on 09/04/14
							  " where qorder_no = ? " ;
						pstmt = conn.prepareStatement(sql);
						pstmt.setDouble(1, convertedQty );
						pstmt.setDouble(2, convertedNetWeight );
						pstmt.setDouble(3, convertedNoArt );
						pstmt.setString(4, qorderNo);
						updCnt = pstmt.executeUpdate();
						pstmt.close();
						pstmt = null;
					} //end if
					
				}
				else
				{	
					System.out.println("@@@ noArt["+noArt+"]qty["+qty+"]qcQty["+qcQty+"]netWeight["+netWeight+"]");  // modify by cpatil on 09/04/14 
					
					convertedNoArt = noArt * qty / qcQty ;
					convertedNetWeight = netWeight * qty / qcQty ;
					
					System.out.println("@@@ convertedNoArt["+convertedNoArt+"]convertedNetWeight["+convertedNetWeight+"]");
					
					if ( qcQty > qty )
					{	
						sql = " Update qc_order set quantity = (quantity - ? ) " +
						      " , net_weight = ( net_weight - ? ) , no_art = ( no_art - ? ) " +    // modify by cpatil on 09/04/14
							  " where qorder_no = ? ";
						pstmt = conn.prepareStatement(sql);
						pstmt.setDouble(1, qty);
						pstmt.setDouble(2, convertedNetWeight);
						pstmt.setDouble(3, convertedNoArt);
						pstmt.setString(4, qorderNo);
						updCnt = pstmt.executeUpdate();
						pstmt.close();
						pstmt = null;
					}
					else
					{
						sql = " Update qc_order set quantity = (quantity - ? ) " +   // remove  update status = 'C' on 05/12/14 
							  " , net_weight = ( net_weight - ? ) , no_art = ( no_art - ? ) " +    // modify by cpatil on 09/04/14
							  " where qorder_no = ? " ;
						pstmt = conn.prepareStatement(sql);
						pstmt.setDouble(1, qty);
						pstmt.setDouble(2, convertedNetWeight);
						pstmt.setDouble(3, convertedNoArt);
						pstmt.setString(4, qorderNo);
						updCnt = pstmt.executeUpdate();
						pstmt.close();
						pstmt = null;
					} //end if
				}

				if (updCnt == 0)   //         get_sqlcode() <> 0 then
				{	
					errCode = "QCTRFUPDT";   //'Unable to confirm QC Transfer ' + sqlca.sqlerrtext
					return itmDBAccessLocal.getErrorString("",errCode,"");
					//return errCode;//Commented by Manoj dtd 03/06/2014
				} //End if

				// Update to qc_order_lot table if it QC is split into to different lots
				if( errCode == null || errCode.trim().length() == 0 )
				{
					sql = " update qc_order_lots set quantity = (quantity - ? ) " +
							" where qc_order = ? and lot_no = ? and lot_sl = ? ";
					pstmt = conn.prepareStatement(sql);
					//if(convertedQty>0)
					if(isByProduct)
					{
						pstmt.setDouble(1, convertedQty);
					}
					else
					{
						pstmt.setDouble(1, qty);
					}
					pstmt.setString(2, qorderNo);
					pstmt.setString(3, lotNo);
					pstmt.setString(4, lotSl);
					updCnt = pstmt.executeUpdate();
					pstmt.close();
					pstmt = null;
					//Start added by chandrashekar on 13-Jan-2015
					sql = " update qc_order set qty_passed = (qty_passed  - ? )  where qorder_no = ?" ;
					pstmt = conn.prepareStatement(sql);
					if(isByProduct)
					{
						pstmt.setDouble(1, convertedQty);
					}
					else
					{
						pstmt.setDouble(1, qty);
					}
					pstmt.setString(2, qorderNo);
					updCnt1 = pstmt.executeUpdate();
					pstmt.close();
					pstmt = null;
					//End added by chandrashekar on 13-Jan-2015
					//	if( updCnt == 0 ) 
					//	{
					//		errCode = "QCTRFUPDT";         //'Unable to confirm QC Transfer ' + sqlca.sqlerrtext
					//		return errCode;
					//	}//End if
				} //end if

				// update po receipt as accept on external approval
				if(( errCode == null || errCode.trim().length() == 0 )  && ( porcpNo != null && porcpNo.trim().length() == 0  ))
				{	
					sql = " select count(1) from porcp where tran_id = ? ";
					pstmt =  conn.prepareStatement(sql);
					pstmt.setString(1,porcpNo);
					rs = pstmt.executeQuery();
					if(rs.next())
					{   
						cnt = rs.getInt(1);
					}
					rs.close();
					rs = null;
					pstmt.close(); 
					pstmt = null;

					if( cnt==1 )   // ll_count = 1 then
					{	
						sql = " update porcp set ACCEPT_CRITERIA = 'E' 	where tran_id = ? ";
						pstmt = conn.prepareStatement(sql);
						pstmt.setString(1, qorderNo);
						updCnt = pstmt.executeUpdate();
						pstmt.close();
						pstmt = null;

						if( updCnt == 0 )  // get_sqlcode() <> 0 then
						{
							System.out.println("@@@@@@@@@@@@ no record");
						}//end if
					} //end if
				} //end if

				if( errCode == null || errCode.trim().length() == 0 )
				{	
					sql = " Update qc_transfer Set confirmed = 'Y', conf_date = ? Where tran_id = ?  ";
					pstmt = conn.prepareStatement(sql);
					pstmt.setTimestamp(1, sysDate );
					pstmt.setString(2, tranId);
					updCnt = pstmt.executeUpdate();
					pstmt.close();
					pstmt = null;

					/////
					if( updCnt == 0 )    //get_sqlcode() <> 0 then
					{
						errCode = "QCTRFUPDT"; //'Unable to confirm QC Transfer ' + sqlca.sqlerrtext
						return itmDBAccessLocal.getErrorString("",errCode,"");
						//return errCode;//Commented by Manoj dtd 03/06/2014
					}//End if
					// ADDED BY RITESH ON 23/SEP/2014 START
					
					else
					{
						double holdQty = 0d;
						sql = " SELECT HOLD_QTY FROM STOCK  "
								+ " WHERE ITEM_CODE = ?  "
								+ " AND SITE_CODE = ? "
								+ " AND LOC_CODE = ? "
								+ " AND LOT_NO = ? "
								+ " AND LOT_SL = ? ";
						pstmt =  conn.prepareStatement(sql);
						pstmt.setString(1,itemCode);
						pstmt.setString(2,siteCode);
						pstmt.setString(3,fromLoc);
						pstmt.setString(4,lotNo);
						pstmt.setString(5,lotSl);
						rs = pstmt.executeQuery();
						if(rs.next())
						{
							holdQty = rs.getDouble("HOLD_QTY");
						}
						pstmt.close();
						pstmt = null;
						rs.close();
						rs = null;
						if(holdQty > 0)
						{
							StockTransferConf stktrf= new StockTransferConf();
							stktrf.StockUpdtHoldItems("",holdQty,qty,itemCode,siteCode,fromLoc,lotNo,lotSl,toLoc,lotSl,conn);							
						}
						
						/*if(holdQty > 0)
						{
							sql = "UPDATE STOCK SET HOLD_QTY = HOLD_QTY - ?"
									+ " WHERE ITEM_CODE = ?  "
									+ " AND SITE_CODE = ? "
									+ " AND LOC_CODE = ? "
									+ " AND LOT_NO = ? "
									+ " AND LOT_SL = ? ";
							pstmt = conn.prepareStatement(sql);
							pstmt.setDouble(1, qty );
							pstmt.setString(2,itemCode);
							pstmt.setString(3,siteCode);
							pstmt.setString(4,fromLoc);
							pstmt.setString(5,lotNo);
							pstmt.setString(6,lotSl);
							updCnt = pstmt.executeUpdate();
							pstmt.close();
							pstmt = null;
							System.out.println("hiold_qty updated  in from location "+updCnt);
							sql = "UPDATE STOCK SET HOLD_QTY =  ?"
									+ " WHERE ITEM_CODE = ?  "
									+ " AND SITE_CODE = ? "
									+ " AND LOC_CODE = ? "
									+ " AND LOT_NO = ? "
									+ " AND LOT_SL = ? ";
							pstmt = conn.prepareStatement(sql);
							pstmt.setDouble(1, qty );
							pstmt.setString(2,itemCode);
							pstmt.setString(3,siteCode);
							pstmt.setString(4,toLoc);
							pstmt.setString(5,lotNo);
							pstmt.setString(6,lotSl);
							updCnt = pstmt.executeUpdate();
							pstmt.close();
							pstmt = null;
							System.out.println("hiold_qty updated  in to location "+updCnt);
						}*/
					}
					// ADDED BY RITESH ON 23/SEP/2014 END
				} //end if


				sql = "select edi_option from transetup where tran_window = 'w_qc_transfer' ";
				pstmtSql = conn.prepareStatement(sql);
				rs = pstmtSql.executeQuery();
				if (rs.next())
				{
					ediOption = rs.getString("EDI_OPTION");
				}
				rs.close();
				rs = null;
				pstmtSql.close();
				pstmtSql = null;
				System.out.println("@@@@@@@@@@@@@@@ ediOption  called next..............");
				if ("2".equals(ediOption)) 
				{
					CreateRCPXML createRCPXML = new CreateRCPXML("w_qc_transfer","tran_id");
					dataStr = createRCPXML.getTranXML(tranId, conn);
					System.out.println("dataStr =[ " + dataStr + "]");
					Document ediDataDom = genericUtility.parseString(dataStr);

					E12CreateBatchLoadEjb e12CreateBatchLoad = new E12CreateBatchLoadEjb();
					retString = e12CreateBatchLoad.createBatchLoad(ediDataDom,"w_qc_transfer", "2", xtraParams, conn);
					createRCPXML = null;
					e12CreateBatchLoad = null;

					if (retString != null && "SUCCESS".equalsIgnoreCase(retString)) 
					{
						System.out.println("retString from batchload = [" + retString + "]");
					}
				}

				else 
				{
					CreateRCPXML createRCPXML = new CreateRCPXML("w_qc_transfer","tran_id");
					dataStr = createRCPXML.getTranXML(tranId, conn);
					System.out.println("dataStr =[ " + dataStr + "]");
					Document ediDataDom = genericUtility.parseString(dataStr);

					E12CreateBatchLoadEjb e12CreateBatchLoad = new E12CreateBatchLoadEjb();
					retString = e12CreateBatchLoad.createBatchLoad(ediDataDom,"w_qc_transfer", ediOption, xtraParams, conn);
					createRCPXML = null;
					e12CreateBatchLoad = null;

					if (retString != null && "SUCCESS".equalsIgnoreCase(retString)) 
					{
						System.out.println("retString from batchload = ["+ retString + "]");
					}
				}
				System.out.println("@@@@@@@@@@@@@@@ ediOption  called end..............");

			} //End if

		}  // end try
		catch (Exception e)
		{
			e.printStackTrace();
			System.out.println("Exception ::" + e.getMessage());
			throw new ITMException(e);
		}

		return errCode;
			}
	/*
	private String checkNull(String string)
	{
		if (string == null) 
		{
			string = "";
		}
		return string;
	}
	 */
	private double roundVal(double round,int scale)
	{
		return Math.round(round*Math.pow(10, scale))/Math.pow(10, scale);
	}
}