/**
 DEVELOPED BY RITESH TIWARI ON 24/03/14 
 PURPUSE : GENERATE WO ISSUE PARTIALLY (DI3LSUP001)
 */
package ibase.webitm.ejb.mfg;
import ibase.system.config.ConnDriver;
import ibase.utility.CommonConstants;
import ibase.webitm.ejb.ITMDBAccessEJB;
import ibase.webitm.ejb.ProcessEJB;
import ibase.webitm.utility.GenericUtility;
import ibase.webitm.utility.ITMException;
import ibase.webitm.utility.TransIDGenerator;
import java.rmi.RemoteException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.ejb.Stateless;
import org.w3c.dom.Document;
import org.w3c.dom.Node;
import org.w3c.dom.NodeList;

@Stateless
public class WoIssPartialPrc extends ProcessEJB implements WoIssPartialPrcLocal,WoIssPartialPrcRemote 
{
	String batchQuantityStr="";
	
	public String process() throws RemoteException,ITMException
	{
		return "";
	}
	public String process(String xmlString, String xmlString2, String windowName, String xtraParams) throws RemoteException,ITMException
	{
		Document detailDom = null;
		Document headerDom = null;
		GenericUtility genericUtility = GenericUtility.getInstance();
		String retStr = "";
		
		
		try
		{				
			System.out.println("xmlString[process]::::::::::;;;"+xmlString);
			System.out.println("xmlString2[process]::::::::::;;;"+xmlString2);
			System.out.println("windowName[process]::::::::::;;;"+windowName);
			System.out.println("xtraParams[process]:::::::::;;;"+xtraParams);
		}
		catch(Exception e)
		{
			System.out.println(e.getMessage());
			e.printStackTrace();
		}
		try
		{	
			if(xmlString != null && xmlString.trim().length()!=0)
			{
				headerDom = genericUtility.parseString(xmlString); 
				System.out.println("headerDom" + headerDom);
			}
			if(xmlString2 != null && xmlString2.trim().length()!=0)
			{
				detailDom = genericUtility.parseString(xmlString2); 
				System.out.println("detailDom" + detailDom);
			}
			retStr = process(headerDom, detailDom, windowName, xtraParams);
		}
		catch (Exception e)
		{			
			System.out.println("Exception :WoIssPartialPrc :process(String xmlString, String xmlString2, String windowName, String xtraParams):" + e.getMessage() + ":");
			e.printStackTrace();
		}
		return retStr;
	
	} //end of confirm method
	public String process(Document headerDom, Document detailDom, String windowName,String xtraParams) throws RemoteException, ITMException 
	{
		System.out.println("WoIssPartialPrc process called........");
		String errString = null,sql="";
		//String batchQuantityStr="";
		String deptCode="",operationStr="";
		String  worder = "";
		Connection conn = null;
		ConnDriver connDriver = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		ITMDBAccessEJB itmDBAccessEJB = null;
		String childNodeName = "";
		Node parentNode = null;
		NodeList childNodeList = null;
		NodeList parentNodeList = null;
		int parentNodeListLength = 0;
		int childNodeListLength = 0;
		Node childNode = null;
		int cnt = 0,operation=0;
		double batchQuantity=0.0,quantity=0.0;
		GenericUtility genericUtility = null;
		//batchQuantityStr="";
		try
		{
			connDriver = null;
			genericUtility = new GenericUtility();
			itmDBAccessEJB = new ITMDBAccessEJB();
			connDriver = new ConnDriver();
			conn = connDriver.getConnectDB("DriverITM");
			connDriver = null;            			
			conn.setAutoCommit(false);
			parentNodeList = detailDom.getElementsByTagName("Detail2");
			parentNodeListLength = parentNodeList.getLength(); 
			System.out.println("parentNodeListLength------------------->"+parentNodeListLength);
			batchQuantityStr = checkNull(genericUtility.getColumnValue("quantity", headerDom));
			worder = checkNull(genericUtility.getColumnValue("work_order", headerDom));
			deptCode = checkNull(genericUtility.getColumnValue("dept_code", headerDom));
			operationStr = checkNull(genericUtility.getColumnValue("operation", headerDom));
			
			if(worder == null || worder.trim().length()  <= 0)
			{
				System.out.println("worder:: is null "+ worder);
				errString = itmDBAccessEJB.getErrorString("","VTWKORD1    ","","",conn);
				return errString;
			}
			if(deptCode == null || deptCode.trim().length()  <= 0)
			{
				System.out.println("deptCode  is null:: "+ worder);
				errString = itmDBAccessEJB.getErrorString("","VTDEPTCD ","","",conn);
				return errString;
			}
			if(operationStr != null && operationStr.trim().length()  > 0)
			{
				operation = Integer.parseInt(operationStr);
			}else
			{
				System.out.println("operationStr  is null:: "+ worder);
				errString = itmDBAccessEJB.getErrorString("","VTOPNULL","","",conn);
				return errString;
			}
			if(worder != null && worder.trim().length()  > 0)
			{
				sql = "select count(*) from workorder where work_order = ? and  status = 'R' ";   
				pstmt = conn.prepareStatement(sql);
				pstmt.setString(1,worder);
			    rs = pstmt.executeQuery();
			    if(rs.next())
			    {
			    	cnt = rs.getInt(1);
			    }
			    if(rs != null)
			    	rs.close();
			    rs = null;
			    if(pstmt != null)
			    	pstmt.close();
			    pstmt = null;
			    if(cnt == 0)
			    {
			    	errString = itmDBAccessEJB.getErrorString("","VPWOINV    ","","",conn);
					return errString;
			    }
			}
			sql = "select quantity from workorder where work_order = ? ";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1,worder);
		    rs = pstmt.executeQuery();
		    if(rs.next())
		    {
		    	quantity = rs.getDouble(1);
		    }
		    if(rs != null)
		    	rs.close();
		    rs = null;
		    if(pstmt != null)
		    	pstmt.close();
		    pstmt = null;
		    if(batchQuantityStr != null && batchQuantityStr.trim().length()  > 0)
			{
				batchQuantity = Double.parseDouble(batchQuantityStr);
				System.out.println(" batchQuantity ::"+batchQuantity);
			    if(batchQuantity > quantity)
			    {
			    	errString = itmDBAccessEJB.getErrorString("","VPBATNOT    ","","",conn);
			    	return errString;
			    }	
			    else if(batchQuantity <= 0)
			    {
			    	errString = itmDBAccessEJB.getErrorString("","VPBATQTY2    ","","",conn);
			    	return errString;
			    }
			}
		    if(deptCode != null && deptCode.trim().length()  > 0)
			{
		    	sql ="select count(1) from department where dept_code = ?  ";

				pstmt = conn.prepareStatement(sql);
				pstmt.setString(1,deptCode);
				rs = pstmt.executeQuery();
				if(rs.next())
				{
					cnt = rs.getInt(1);
				}
				rs.close();rs= null;
				pstmt.close();pstmt= null;
				if( cnt==0)
				{
					errString =itmDBAccessEJB.getErrorString("","VMDEP1","","",conn); 
					return errString;
				}
				sql ="select count(1) from WORDER_ROUTE where work_order = ? and dept_code = ? ";

				pstmt = conn.prepareStatement(sql);
				pstmt.setString(1,worder);
				pstmt.setString(2,deptCode);
				rs = pstmt.executeQuery();
				if(rs.next())
				{
					cnt = rs.getInt(1);
				}
				rs.close();rs= null;
				pstmt.close();pstmt= null;
				if( cnt==0)
				{
					errString =itmDBAccessEJB.getErrorString("","VMDEPCDWO","","",conn); 
					return errString;
				}
			}
		    if(operation == 0 )
			{
		    	errString =itmDBAccessEJB.getErrorString("","VMITEMCZ","","",conn); 
				return errString;
			}
			if(operation > 0 )
			{

				sql ="select count(1) from workorder_bill where work_order = ? and operation = ?  ";
				pstmt = conn.prepareStatement(sql);
				pstmt.setString(1,worder);
				pstmt.setInt(2,operation);
				rs = pstmt.executeQuery();
				if(rs.next())
				{
					cnt = rs.getInt(1);
				}
				rs.close();rs= null;
				pstmt.close();pstmt= null;
				if( cnt==0)
				{
					errString =itmDBAccessEJB.getErrorString("","VMOPERINV","","",conn); 
					return errString;
				}
			} 
			if(errString == null)
			{
				errString = generateWoIssue(quantity,worder,deptCode,operation,batchQuantity,xtraParams,conn);
			}
			if((errString != null ) &&  (errString.indexOf("VPSUCC1") > -1))
			{
				System.out.println("--process completed--");
				errString = itmDBAccessEJB.getErrorString("","VPSUCC1    ","","",conn);
			}
			else if(errString != null && errString.indexOf("VTBATQTY") > -1)
			{
				System.out.println("batch quantity not valid)]]");;
				errString = itmDBAccessEJB.getErrorString("","VTBATQTYNT","","",conn);
			}
			else 
			{
				conn.rollback();
				System.out.println("--process not completed--");
				errString = itmDBAccessEJB.getErrorString("","VTPRCERR ","","",conn);
			}
		}
		catch( Exception e)
		{			
				try 
				{
					conn.rollback();
				} 
				catch (SQLException ex) 
				{
					Logger.getLogger(WoIssPartialPrc.class.getName()).log(Level.SEVERE, null, ex);
				}
				e.printStackTrace();
		}
		finally
		{		
				try
				{
					if(errString != null && errString.trim().length() > 0)
					{
						if(errString.indexOf("VPSUCC1") > -1 )
						{
							conn.commit();
							System.out.println("--transaction commited--");
						}
						else
						{
							conn.rollback();
							System.out.println("--transaction rollback--");
						}
					}
					if(pstmt != null)
					{
						pstmt.close();
						pstmt = null;					
					}		
					if( conn != null && ! conn.isClosed() )
					{
						conn.close();
						conn = null;
					}
					
					if(rs != null)
					{
						rs.close();
						rs = null;
					}
				}
				catch(Exception e)
				{
					System.out.println("Exception : "+e);e.printStackTrace();
					throw new ITMException(e);
				}
			}
		return errString;
	}
	private String generateWoIssue(double woQty,String worder,String deptCode,int operation,double batchQuantity,String xtraParams,Connection conn) throws RemoteException, ITMException
	{	
		String  userId = "";
		String  termId = "";
		String chgUser = "";
		String chgTerm = "";
		String sql = "",insertHed="", insertDetail ="",stockSql="",sql2="";
		PreparedStatement pstmt = null,pstmt1=null,pstmt2=null;
		ResultSet rs = null,rs1 = null,rs2 = null;
		GenericUtility genericUtility = null;
		Timestamp currDate = null;
		String retString  = null;
		String empCode = "";
		String stocklocCode="";
		String siteCode="",locGroup="",itemCodeHdr="",orderType="", woirType="";
		String keyString="",keyCol="",tranSer1="",lineType="";
		String tranId = "";
		String locCode = "",lotNoInv="",lotSlInv="";
		Timestamp currDate1 =new java.sql.Timestamp(System.currentTimeMillis()) ;
		Object date1 = null;
		int upd = 0,lineNo=0,updDet=0;
		double  rate=0,minPotencyPerc=0,quantity=0,potencyAdj=0,grossRate=0,issueQtyBill=0,allocQty=0,issQty=0;
		String expLev="",itemCode="",lotNo="",lotSl="",lotNoIss="",unit="",matchPotency="",adjPotency="",calcPotency="";
        double issuedQty = 0;
        double propReqQty = 0d,issueQtyDiff = 0d,extraQty=0d;
		ArrayList list  = new ArrayList();
		boolean flag = false;
		DecimalFormat df = new DecimalFormat("0.000");  // DecimalFormat df = new DecimalFormat("0.000");  //cpatil on 11/11/14
		CommonConstants commonConstants = new CommonConstants();
		
		  String itemCodeInv=""; double quantityInv=0,issueQtyInv=0;  // added by cpatil
		  double noArt = 0,netWeight = 0, grossWeight=0, stkQty=0;   // added by cpatil on 02/04/15 urse		  
		try   
		{
			
			System.out.println("@@@@@@@ generateWoIssue function called::");
			genericUtility = GenericUtility.getInstance();
			SimpleDateFormat sdf1 = new SimpleDateFormat(genericUtility.getDBDateFormat());
//			currDate =  java.sql.Timestamp.valueOf(sdf1.format(new java.util.Date()).toString() + " 00:00:00.0");
			userId  = genericUtility.getValueFromXTRA_PARAMS(xtraParams, "loginCode");  System.out.println("--login code--"+userId);
			termId  = genericUtility.getValueFromXTRA_PARAMS(xtraParams, "termId"); System.out.println("--term id--"+termId);
			chgUser = genericUtility.getValueFromXTRA_PARAMS(xtraParams,"chgUser");
			chgTerm = genericUtility.getValueFromXTRA_PARAMS(xtraParams,"chgTerm");
			
			sql = "SELECT EMP_CODE FROM USERS WHERE CODE = ? ";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, userId);
			rs = pstmt.executeQuery();
			if ( rs.next() )
			{
				empCode = checkNull(rs.getString("EMP_CODE"));
			}
			if(rs != null)
				rs.close();
			rs = null;
			if(pstmt != null)
				pstmt.close();
			pstmt = null;
			
			
			sql =" select SITE_CODE,LOC_GROUP,ITEM_CODE,ORDER_TYPE,lot_no,lot_sl,lot_no__issue  from  workorder  where work_order= ? ";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1,worder);
			rs = pstmt.executeQuery();
			if(rs.next())
			{ 
				siteCode		= rs.getString("SITE_CODE");
				locGroup		= rs.getString("LOC_GROUP");
				itemCodeHdr	    = rs.getString("ITEM_CODE");
				orderType		= rs.getString("ORDER_TYPE");
				lotNo           = checkNull(rs.getString("lot_no"));
				lotSl           = checkNull(rs.getString("lot_sl"));
				lotNoIss        = checkNull(rs.getString("lot_no__issue"));
			}
			rs.close();
			rs=null;
			pstmt.close();
			pstmt =null;

			woirType = orderType.substring(0,1)+"I";

			sql = "SELECT KEY_STRING, TRAN_ID_COL, REF_SER FROM TRANSETUP WHERE lower(TRAN_WINDOW) = 'w_wo_iss_issue'";
			pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery();
			if(rs.next())
			{ 
				keyString = rs.getString(1);
				keyCol = rs.getString(2);
				tranSer1 = rs.getString(3);				
			}
			rs.close();
			rs=null;
			pstmt.close();
			pstmt =null;

			System.out.println("keyString :"+ keyString);
			System.out.println("keyCol :"+ keyCol);
			System.out.println("tranSer1 :"+ tranSer1);
			System.out.println("woirType :"+ woirType);
			String xmlValues = "";
			String tranDateStr = getCurrdateAppFormat();
			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 + "<woir_type>" + woirType + "</woir_type>";
			xmlValues = xmlValues + "<tran_date>" + tranDateStr + "</tran_date>"; 
			xmlValues = xmlValues +"</Detail1></Root>";
			System.out.println("xmlValues  :["+xmlValues+"]");
			TransIDGenerator tg = new TransIDGenerator(xmlValues, "BASE", commonConstants.DB_NAME);
			tranId = tg.generateTranSeqID(tranSer1, keyCol, keyString, conn);
			System.out.println("@@@@ generated tranId :["+tranId+"]");
			
			date1 = sdf1.parse(currDate1.toString());
			currDate1 =	java.sql.Timestamp.valueOf(sdf1.format(date1).toString() + " 00:00:00.0");
			System.out.println("currDate !"+currDate1);
			

			insertHed =" insert into  workorder_iss (TRAN_ID,TRAN_DATE,EFF_DATE,WORK_ORDER,OPERATION,SITE_CODE, "
					+" REMARKS,CHG_DATE,CHG_USER,CHG_TERM,DEPT_CODE,TRAN_TYPE, "
					+" LOC_GROUP,ITEM_CODE,WOIR_TYPE,STATUS,CONFIRMED ,LOT_NO__FG,LOT_SL__FG) "
					+" values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?) ";
			pstmt = conn.prepareStatement(insertHed);
			pstmt.setString(1,tranId); 
			pstmt.setTimestamp(2,currDate1);
			pstmt.setTimestamp(3,currDate1);
			pstmt.setString(4,worder); 
			pstmt.setInt(5,operation);
			pstmt.setString(6,siteCode); 
		//	pstmt.setString(7,"issue generated partially for work order "+worder);
			pstmt.setString(7,"issue generated partially for WO "+worder+" for Qty " +batchQuantityStr);
			pstmt.setTimestamp(8,currDate1);
			pstmt.setString(9,chgUser);
			pstmt.setString(10,termId);
			pstmt.setString(11,deptCode);
			pstmt.setString(12,"I");           
			pstmt.setString(13,locGroup);
			pstmt.setString(14,itemCodeHdr);
			pstmt.setString(15,woirType);
			pstmt.setString(16,"O");
			pstmt.setString(17,"N");
			pstmt.setString(18,lotNo);
			pstmt.setString(19,lotSl);
			upd = pstmt.executeUpdate();

			pstmt.close();
			pstmt= null;
			System.out.println("@@@@@@@@insertQueryIss["+upd+"]");
			sql = " select  operation,exp_lev,item_code,quantity,unit,alloc_qty," +
					" site_code,line_type,match_potency,min_potency_perc,adj_potency," +
					" potency_adj,iss_qty  from  workorder_bill " +
					" where work_order = ? and operation = ? " +
					" and alloc_qty >=  (case when iss_qty is null then 0 else iss_qty end)  " +
					" and item_code not in (select item_code from workorder where work_order = '"+ worder +"')  " ;
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1,worder);
			pstmt.setInt(2,operation);
			rs = pstmt.executeQuery();
			while(rs.next())
			{
				// lineNo ++ ;  // comment lineno
				operation	=rs.getInt("operation");
				expLev		=rs.getString("EXP_LEV");
				itemCode	=rs.getString("item_code");
				quantity	=rs.getDouble("quantity");
				unit		=rs.getString("unit");
				allocQty	=rs.getDouble("alloc_qty");
				siteCode	=rs.getString("site_code");
				lineType	=rs.getString("line_type");
				matchPotency    =rs.getString("match_potency");
				minPotencyPerc  =rs.getDouble("min_potency_perc");
				adjPotency      =rs.getString("adj_potency");
				potencyAdj   	=rs.getDouble("potency_adj");
				issQty 		 	=rs.getDouble("iss_qty");

				issueQtyBill = quantity + potencyAdj ;

				System.out.println("@@@@@@ workorder_bill-qty -- item wise -->["+issueQtyBill+"]");
			
				
				
				sql =" select nvl(sum(nvl(quantity,0) + nvl(potency_adj,0)),0) " 
						+" from workorder_iss a, workorder_issdet b "
						+" where  a.tran_id = b.tran_id "
						+" and 	a.work_order= ? "
						+" and b.exp_lev = ?  " 
						+" and a.operation = ? ";
				pstmt1 = conn.prepareStatement(sql);
				pstmt1.setString(1,worder);
				pstmt1.setString(2,expLev);
				pstmt1.setInt(3,operation);
				rs1 = pstmt1.executeQuery();
				if(rs1.next())
				{
					issuedQty	=rs1.getDouble(1);
				}
				rs1.close();rs1=null;
				pstmt1.close();pstmt1=null;
				System.out.println("@@@@@@ issuedQty for the item--->["+issuedQty+"]");
				
				/*
					propReqQty = (issueQtyBill/woQty)*batchQuantity;
					propReqQty = Double.parseDouble(df.format(propReqQty));
				
					System.out.println("@@@@@@ propReqQty for the item--->["+propReqQty+"]");
				
					issueQtyDiff  =   issueQtyBill - issuedQty ;
					issueQtyDiff = Double.parseDouble(df.format(issueQtyDiff));
			
					System.out.println("@@@@@@ issueQtyDiff for the item--->["+issueQtyDiff+"]");
				 */
					/*
					if(propReqQty > issueQtyDiff)
					{
						list.add(itemCode);
						list.add(issuedQty);
						list.add(issueQtyBill);
						flag = true;
						break;  // added break condition on 05/11/14 by cpatil  
						
					}
					*/
					if(flag == false)
					{
						//sql2 = " SELECT LOC_CODE,LOT_NO,LOT_SL FROM INV_ALLOC_DET  WHERE TRAN_ID IN (SELECT TRAN_ID FROM INV_ALLOCATE WHERE WORK_ORDER  = ? ) AND ITEM_CODE = ? ";
						sql2 = " SELECT LOC_CODE,LOT_NO,LOT_SL,ITEM_CODE,SITE_CODE,  quantity ,  issue_qty  " +
								" FROM INV_ALLOC_DET  " +
								" WHERE TRAN_ID IN (SELECT TRAN_ID FROM INV_ALLOCATE WHERE WORK_ORDER  = ? ) " +
								" and  'Y'  <>   case when deallocated is null then 'N' else deallocated end " +
								" and  exp_lev = ? ";
						pstmt2 = conn.prepareStatement(sql2);
						pstmt2.setString(1,worder);
						pstmt2.setString(2,expLev);
						//pstmt2.setString(2,itemCode);
						rs2 = pstmt2.executeQuery();
						while(rs2.next())
						{
							lineNo++;
							locCode 	=rs2.getString("LOC_CODE");
							lotNoInv	=rs2.getString("LOT_NO");
							lotSlInv	=rs2.getString("LOT_SL");
							itemCodeInv	=rs2.getString("ITEM_CODE");  
							siteCode	=rs2.getString("SITE_CODE");
							quantityInv	=rs2.getDouble("quantity");
							issueQtyInv	=rs2.getDouble("issue_qty");
						
						String propReqQtyStr = df.format(propReqQty);
						propReqQty = Double.parseDouble(propReqQtyStr);
						System.out.println("propReqQty @@@::["+propReqQty+"]for item["+itemCode + "]quantityInv["+quantityInv+"]issueQtyInv["+issueQtyInv+"]");
						/*
						stockSql ="select a.lot_no, a.loc_code, a.lot_sl, a.rate, a.gross_rate,a.supp_code__mfg " 
								+" ,(a.quantity-a.alloc_qty) as stockBalQty "
								+" from stock a, invstat b, item c   " 
								+" Where a.inv_stat = b.inv_stat and a.item_code = c.item_code  "
								+" and a.item_code = '" + itemCode + "' "
								+" and a.site_code  = '"+siteCode+"' "
								+" and b.stat_type <> 'S'   and b.available = 'Y' "
								+" and a.loc_code in ( select loc_code from location where loc_group ='"+locGroup+"' ) "
								+" and lot_no in ('" +lotNoIss+ "')"
								+" and  ( a.quantity - a.alloc_qty ) > 0 ";	
						*/
						
						noArt = 0; 
						netWeight = 0; 
						grossWeight= 0 ;
						stkQty = 0;
						stockSql ="select a.NO_ART , a.NET_WEIGHT , a.GROSS_WEIGHT, a.quantity "  //added on 02/04/15 by cpatil
							+" from stock a, invstat b, item c   " 
							+" Where a.inv_stat = b.inv_stat and a.item_code = c.item_code  "
							+" and a.item_code = '" + itemCodeInv + "' "
							+" and a.site_code  = '"+siteCode+"' "
							+" and b.stat_type <> 'S'   and b.available = 'Y' "
							+" and a.loc_code  ='"+locCode+"'  "
							+" and a.lot_no = ('" +lotNoInv+ "') "
							+" and   a.quantity  > 0 ";	
					
					pstmt1 = conn.prepareStatement(stockSql);
					rs1 = pstmt1.executeQuery();
					if(rs1.next())
					{
						noArt = rs1.getDouble(1);          // added by cpatil on 02/04/15
						netWeight = rs1.getDouble(2);      // added by cpatil on 02/04/15
						grossWeight= rs1.getDouble(3);    // added by cpatil on 02/04/15
						stkQty= rs1.getDouble(4);    // added by cpatil on 02/04/15
					}
					rs1.close();rs1=null;
					pstmt1.close();pstmt1=null;
						
						
						
						stockSql ="select a.lot_no, a.loc_code, a.lot_sl, a.rate, a.gross_rate,a.supp_code__mfg " 
								+" ,(a.quantity-a.alloc_qty) as stockBalQty "
								+" from stock a, invstat b, item c   " 
								+" Where a.inv_stat = b.inv_stat and a.item_code = c.item_code  "
								+" and a.item_code = '" + itemCodeInv + "' "
								+" and a.site_code  = '"+siteCode+"' "
								+" and b.stat_type <> 'S'   and b.available = 'Y' "
								+" and a.loc_code  ='"+locCode+"'  "
								+" and a.lot_no = ('" +lotNoInv+ "')"
								+" and  ( a.quantity - a.alloc_qty ) > 0 ";	
						
						pstmt1 = conn.prepareStatement(stockSql);
						rs1 = pstmt1.executeQuery();
						if(rs1.next())
						{
						//	String stock_lot_no		= rs1.getString(1); 
						//	String stock_loc_code		= rs1.getString(2);
						//	String stock_lot_sl		= rs1.getString(3);
							rate				= rs1.getDouble(4);
							grossRate			= rs1.getDouble(5);
						//	String suppCodeMfg			= rs1.getString(6);
						//	double stockBalQty         = rs1.getDouble(7);
						}
						rs1.close();rs1=null;
						pstmt1.close();pstmt1=null;
						
						// for quantity check
						System.out.println("@@@@@@090315 quantityInv["+quantityInv+"]woQty["+woQty+"]batchQuantity["+batchQuantity+"]issueQtyInv["+issueQtyInv+"]issuedQty["+issuedQty+"]");
						propReqQty = (quantityInv/woQty)*batchQuantity;
						propReqQty = Double.parseDouble(df.format(propReqQty));
					
						System.out.println("@@@@@@1 propReqQty for the item--->["+propReqQty+"]");
					
						//issueQtyDiff  =  quantityInv -  issuedQty   ;  //comment this condition on 09/03/15
						issueQtyDiff  =  quantityInv -  issueQtyInv   ;
						
						issueQtyDiff = Double.parseDouble(df.format(issueQtyDiff));
				
						System.out.println("@@@@@@ issueQtyDiff for the item--->["+issueQtyDiff+"]");

						if(propReqQty > issueQtyDiff)
						{
							System.out.println("propReqQty["+propReqQty+"]issueQtyDiff["+issueQtyDiff+"]");
							list.add(itemCode);
							list.add(issuedQty);
							list.add(issueQtyBill);
							flag = true;      // remove comment on 05/03/15 for validate over issue quantity   //comment by cpatil for remove validation on 11/11/14 as per shailesh n manoharan sir discussion. 
							break;            // remove comment on 05/03/15 for validate over issue quantity    //comment by cpatil for remove validation on 11/11/14 as per shailesh n manoharan sir discussion.
							// added break condition on 05/11/14 by cpatil  
							
						}
						
						// end
						
						//	NO_ART ,  NET_WEIGHT , GROSS_WEIGHT   added   urse                       
						
						System.out.println("@@@@@@@@@ before calculation : netWeight["+netWeight+"]grossWeight["+grossWeight+"]noArt["+noArt+"]stkQty["+stkQty+"]propReqQty["+propReqQty+"]");
						
						netWeight =  ( netWeight * propReqQty ) / stkQty ;
						
						grossWeight =  ( grossWeight * propReqQty ) / stkQty ;
						
						noArt =   new Double( Math.ceil( ( noArt * propReqQty ) / stkQty )) ;
						
						System.out.println("@@@@@@@@@ after calculation : netWeight["+netWeight+"]grossWeight["+grossWeight+"]noArt["+noArt+"]");
						
						
						System.out.println("@@@@@@@@@ locGroup work order["+locGroup+"]locCode["+locCode+"]");
						
						String locGroupLoc = "";
						String locSql =" select loc_group from location where loc_code = ?  ";	
						pstmt1 = conn.prepareStatement(locSql);
						pstmt1.setString(1,locCode);
						rs1 = pstmt1.executeQuery();
						if(rs1.next())
						{
							locGroupLoc		= checkNull(rs1.getString(1)); 
						}
						rs1.close();rs1=null;
						pstmt1.close();pstmt1=null;
						System.out.println("@@@@@@@@@ locGroup work order["+locGroup+"]locGroupLoc["+locGroupLoc+"]");
						if(! (checkNull(locGroup).equalsIgnoreCase(locGroupLoc)) )
						{
							retString = "VMLOCGRPIN";
							System.out.println("@@@@@@@@@ locGroup miss match in  work order["+locGroup+"]location["+locGroupLoc+"]");
							return retString ;
						}
						
						
						
						insertDetail ="insert into workorder_issdet (tran_id,line_no,exp_lev,item_code,loc_code,lot_no," +
								" lot_sl,quantity,unit,rate,gross_rate,calc_potency, potency_adj," +
								" match_potency,min_potency_perc,adj_potency,work_order" +
								" , NO_ART ,  NET_WEIGHT , GROSS_WEIGHT )" +
								" values ( ?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,? " +
								" ,? ,? ,? )";
						pstmt1 = conn.prepareStatement(insertDetail);
						pstmt1.setString(1,tranId); 
						pstmt1.setInt(2,lineNo);
						pstmt1.setString(3,expLev);
						pstmt1.setString(4,itemCodeInv);    // added by cpatil instead of itemCode
						pstmt1.setString(5,locCode);
						pstmt1.setString(6,lotNoInv);
						pstmt1.setString(7,lotSlInv);
						pstmt1.setDouble(8,propReqQty);
						pstmt1.setString(9,unit);
						pstmt1.setDouble(10,rate);
						pstmt1.setDouble(11,grossRate);
						pstmt1.setString(12,"N");
						pstmt1.setDouble(13,extraQty);	  
						pstmt1.setString(14,matchPotency);
						pstmt1.setDouble(15,minPotencyPerc);
						pstmt1.setString(16,adjPotency); 
						pstmt1.setString(17,worder);
						
						pstmt1.setDouble(18,noArt);              // added by cpatil on 02/04/15
						pstmt1.setDouble(19,netWeight);          // added by cpatil on 02/04/15
						pstmt1.setDouble(20,grossWeight);        // added by cpatil on 02/04/15
						
					    updDet = updDet + pstmt1.executeUpdate();
						pstmt1.close();pstmt1=null;
						
						sql = " update inv_alloc_det set issue_qty = (CASE WHEN issue_qty IS NULL THEN 0 ELSE issue_qty END) + ? ,"//Add case condition for removing null value
								+ " dealloc_qty = (CASE WHEN dealloc_qty IS NULL THEN 0 ELSE dealloc_qty END) + ?"
								+ " where tran_id  in (select tran_id from inv_allocate where work_order = ? )"
								+ " and item_code = ? and lot_sl= ? and lot_no = ? and site_code= ? and loc_code = ? " 
								+ " and exp_lev = ? ";
						pstmt1 = conn.prepareStatement(sql);
						pstmt1.setDouble(1,propReqQty);
						pstmt1.setDouble(2,propReqQty); 
						pstmt1.setString(3,worder); 
						pstmt1.setString(4,itemCodeInv);   // added by cpatil instead of itemCode
						pstmt1.setString(5,lotSlInv);
						pstmt1.setString(6,lotNoInv);
						pstmt1.setString(7,siteCode);
						pstmt1.setString(8,locCode);
						pstmt1.setString(9,expLev);
						int cnt=pstmt1.executeUpdate();
						System.out.println("Count after PSTM is:- %%%%%%%%%%%%%%%%%%%%%%%%%%%%%["+cnt+"]");
						pstmt1.close();pstmt1=null;
						}
						rs2.close();
						rs2=null;
						pstmt2.close();
						pstmt2=null;
					}
				}	
				System.out.println("detail record :: inserted "+updDet);
				if(flag)
					retString = "VTBATQTY";
				else if(updDet > 0)
					retString = "VPSUCC1";
		}
		catch(Exception e)
		{
			e.printStackTrace();
			retString = e.getMessage();
			throw new ITMException(e);
		}
		finally
		{
			try
			{
				if(pstmt != null)
				{
					pstmt.close();
					pstmt = null;
				}
				if(rs != null)
				{
					rs.close();
					rs = null;
				}
				if(pstmt2 != null)
				{
					pstmt2.close();
					pstmt2 = null;
				}
				if(rs2 != null)
				{
					rs2.close();
					rs2 = null;
				}
			}
			catch(Exception e)
			{
				e.printStackTrace();
				retString = e.getMessage();
				throw new ITMException(e);
			}
		}
		System.out.println("Returning Result [" + retString + "]");
		return retString;
	}
	private String checkNull(String input) 
	{
		if(input == null)
		{
			input = "";
		}
		return input;
	}
	
	private String getCurrdateAppFormat()
	{
		String s = "";
		GenericUtility genericUtility = GenericUtility.getInstance();
		try
		{
			java.util.Date date = null;
			Timestamp timestamp = new Timestamp(System.currentTimeMillis());
			System.out.println(genericUtility.getDBDateFormat());
			SimpleDateFormat simpledateformat = new SimpleDateFormat(genericUtility.getDBDateFormat());
			date = simpledateformat.parse(timestamp.toString());
			timestamp = Timestamp.valueOf(simpledateformat.format(date).toString() + " 00:00:00.0");
			s = (new SimpleDateFormat(genericUtility.getApplDateFormat())).format(timestamp).toString();
		}
		catch(Exception exception)
		{
			System.out.println("Exception in  getCurrdateAppFormat " + exception.getMessage());
		}
		return s;
	}	
}