package ibase.webitm.ejb.mfg;

import java.rmi.RemoteException;
import ibase.system.config.ConnDriver;
import ibase.webitm.ejb.*;
import ibase.webitm.ejb.dis.StockUpdate;
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
			{
		System.out.println("QCTransferConf confirm called..............");
		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();

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

			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);
						System.out.println("@@@@ confirmQcTransfer::errCode["+errCode+"]");
						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))) 
				{
					conn.rollback();
					System.out.println("@@@@ Transaction rollback...["+errString+"]::errCode["+errCode+"]");
					conn.close();
					conn = null;
				}
				else 
				{
					conn.commit(); // test
					System.out.println("@@@@ Transaction commit....["+errString+"]::errCode["+errCode+"]");
					conn.close();
					conn = null;
				}
			}
			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;
		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

		try 
		{
			StockUpdate stockUpdate = new StockUpdate();

			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" +
					" 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");
			}
			rs.close();
			rs = null;
			pstmt.close();
			pstmt = null;


			sql = " Select status,Quantity from qc_order where qorder_no = ? ";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, qorderNo);
			rs = pstmt.executeQuery();
			if (rs.next())
			{
				cnt++;
				status = rs.getString("status");
				qcQty= rs.getDouble("Quantity");
			}
			rs.close();
			rs = null;
			pstmt.close();
			pstmt = null;


			if ( cnt == 0)
			{
				errCode = "QCTRFORD";   //'QC order is not found'
				return errCode;
			}
			else if("C".equalsIgnoreCase(status)) 
			{
				errCode = "QCTRFORD";    //'QC order is CONFIRMED cannot Transfer Quantity'
				return errCode;
			}

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

			System.out.println("@@@@ tranDate new validation start--------- ");

			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 errCode;
					}

				}
			}
			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;


				if(qcQty < qty )
				{
					errCode = "QCTRFQTY";    //'QC Transfer quantity more then QC Order Quantity'
					return errCode;
				}
			}


			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);
				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"));
					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");
				}
				rs.close();
				rs = null;
				pstmt.close();
				pstmt = null;

				if( cnt == 0 )
				{	
					errCode = "QCTRFSTK";   //'Item Not Found in Stock...'
					System.out.println("@@@@@@@@@@@@  [QCTRFSTK]");
					return errCode;
				}//End if

				if( errCode == null || errCode.trim().length() == 0 )
				{	
					sql = " Select unit From item where item_code = ? ";
					pstmt =  conn.prepareStatement(sql);
					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
					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);
					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;


					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 ( qcQty > qty )
				{	
					sql = " Update qc_order set quantity = (quantity - ? ) where qorder_no = ? ";
					pstmt = conn.prepareStatement(sql);
					pstmt.setDouble(1, qty);
					pstmt.setString(2, qorderNo);
					updCnt = pstmt.executeUpdate();
					pstmt.close();
					pstmt = null;
				}
				else
				{
					sql = " Update qc_order set quantity = (quantity - ? ), status = 'C' where qorder_no = ? " ;
					pstmt = conn.prepareStatement(sql);
					pstmt.setDouble(1, qty);
					pstmt.setString(2, 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 errCode;
				} //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);
					pstmt.setDouble(1, qty);
					pstmt.setString(2, qorderNo);
					pstmt.setString(3, lotNo);
					pstmt.setString(4, lotSl);
					updCnt = pstmt.executeUpdate();
					pstmt.close();
					pstmt = null;

					//	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 errCode;
					}//End if
				} //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;
	}
	 */
}