/********************************************************
	Title 	 : BomAmdConf[MF3GSUN024]
	Date  	 : 06/01/14
	Developer: Chandrashekar

********************************************************/
package ibase.webitm.ejb.mfg.adv;

import java.rmi.RemoteException;


import ibase.system.config.ConnDriver;
import ibase.webitm.ejb.*;
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.Date;

@Stateless
public class BomAmdConf extends ActionHandlerEJB implements
BomAmdConfLocal, BomAmdConfRemote
{
	public String confirm(String amdNo, String xtraParams, String forcedFlag)
			throws RemoteException, ITMException
			{
		System.out.println("BomAmdConf confirm called..............");
		String confirmed = "";
		String sql = "",sql1="",sql2="";
		Connection conn = null;
		PreparedStatement pstmt = null,pstmt1=null;
		
		String errString = null;
		ResultSet rs = null,rs1=null;
		String bomcode = "", loginEmpCode="";
		String supplycodemfg="",issVarienceType="",issvarvaltype="",locusagetype="",mfgref="",bomlineno="";
		double issVarience = 0;

		int cnt = 0,updCnt=0,updCnt1=0;

		ITMDBAccessEJB itmDBAccessLocal = new ITMDBAccessEJB();
		Timestamp sysDate = null,amddate=null,validupto=null;
		GenericUtility genericUtility = GenericUtility.getInstance();

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

			if (amdNo != null && amdNo.trim().length() > 0) 
			{
				System.out.println("@@@@@amdNo"+amdNo+"]");

				sql = "	select confirmed from  bomamd_hdr where amd_no = ? ";
				pstmt = conn.prepareStatement(sql);
				pstmt.setString(1, amdNo);
				rs = pstmt.executeQuery();
				if (rs.next()) 
				{

					confirmed = rs.getString("confirmed");

				}
				rs.close();
				rs = null;
				pstmt.close();
				pstmt = null;

				if ("Y".equalsIgnoreCase(confirmed)) 
				{	
					System.out.println("transaction already confirmed");				
					errString = itmDBAccessLocal.getErrorString("","VTCONF8","");
				
				}else
				{
					System.out.println("transaction not confirmed");

					loginEmpCode =  genericUtility.getValueFromXTRA_PARAMS(xtraParams, "loginEmpCode");							
					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 bom_code,valid_upto,amd_date"
							+ " from bomamd_hdr where amd_no = ? ";
					pstmt = conn.prepareStatement(sql);
					pstmt.setString(1, amdNo);
					rs = pstmt.executeQuery();
					if (rs.next())
					{			
						bomcode = rs.getString("bom_code");				
						validupto = rs.getTimestamp("valid_upto");
						amddate = rs.getTimestamp("amd_date");				

					}
					rs.close();
					rs = null;
					pstmt.close();
					pstmt = null;

					sql1 = " select count(*) from bomamd_det where amd_no = ? ";
					pstmt = conn.prepareStatement(sql1);
					pstmt.setString(1, amdNo);
					rs = pstmt.executeQuery();
					if (rs.next())
					{
						cnt = rs.getInt(1);

					}
					rs.close();
					rs = null;
					pstmt.close();
					pstmt = null;
					System.out.println("@@@@@@ inside cnt " + cnt);
					if (cnt > 0)
					{
						System.out.println("@@@@@@ inside cnt1 " + cnt);
						sql2 = " select bom_code,line_no__bom,"						 
								+ "supp_code__mfg,mfg_ref,iss_varience_type," 
								+"iss_var_valtype,loc_usage_type ,iss_varience "
								+ "from   bomamd_det where  amd_no = ? ";	

						pstmt1 = conn.prepareStatement(sql2);
						pstmt1.setString(1, amdNo);
						rs1 = pstmt1.executeQuery();
						while (rs1.next())
						{
							System.out.println("@@@@@@ inside while ");
							bomcode = checknull(rs1.getString("bom_code"));									
							bomlineno = checknull(rs1.getString("line_no__bom"));									
							supplycodemfg = checknull(rs1.getString("supp_code__mfg"));									
							mfgref = checknull(rs1.getString("mfg_ref"));									
							issVarienceType = checknull(rs1.getString("iss_varience_type"));									
							issvarvaltype = checknull(rs1.getString("iss_var_valtype"));									
							locusagetype = checknull(rs1.getString("loc_usage_type"));
							locusagetype = checknull(rs1.getString("loc_usage_type"));
							//added by kunal on 04/feb/14 add issVarience in bom amendment 
							issVarience = rs1.getDouble("iss_varience");



							System.out.println("bomlineno[" + bomlineno + "]");

							if (bomlineno != null && bomlineno.trim().length() > 0) 
							{	
								sql = " update bomdet set supp_code__mfg=?,mfg_ref=?,iss_varience_type=?,iss_var_valtype=?,loc_usage_type=? , iss_varience = ? where bom_code = ?	and line_no = ? ";
								pstmt = conn.prepareStatement(sql);
								pstmt.setString(1, supplycodemfg);
								pstmt.setString(2, mfgref);
								pstmt.setString(3, issVarienceType);
								pstmt.setString(4, issvarvaltype);
								pstmt.setString(5, locusagetype);
								pstmt.setDouble(6, issVarience);
								pstmt.setString(7, bomcode);
								pstmt.setString(8, bomlineno);

								updCnt = pstmt.executeUpdate();
								pstmt.close();
								pstmt = null;
								System.out.println("@@@updCnt["+updCnt+"]");
								if (!(updCnt == 1)) 
								{

									errString = itmDBAccessLocal.getErrorString("","VTBOMDTLN1","");

								}									
							}

						}
						pstmt1.close();
						pstmt1 = null;
					}
					else
					{

						errString = itmDBAccessLocal.getErrorString("","VTBOMDT1","");

					}
					sql = " update bom set amd_no=?,amd_date=? ,valid_upto=? where bom_code = ? ";
					pstmt = conn.prepareStatement(sql);
					pstmt.setString(1, amdNo);
					pstmt.setTimestamp(2, amddate);
					pstmt.setTimestamp(3, validupto);
					pstmt.setString(4, bomcode);
					updCnt1 = pstmt.executeUpdate();
					pstmt.close();
					pstmt = null;
					if (updCnt1>0) 
					{	
						sql = " update bomamd_hdr set confirmed = 'Y',conf_date = ?, emp_code__aprv = ? where amd_no = ? ";
						pstmt = conn.prepareStatement(sql);
						pstmt.setTimestamp(1, sysDate);
						pstmt.setString(2, loginEmpCode);
						pstmt.setString(3, amdNo);
						cnt = pstmt.executeUpdate();
						pstmt.close();
						pstmt = null;
						System.out.println("@@@@@@ cnt...[" + cnt + "]");
						if (cnt>0) 
						{	

							errString = itmDBAccessLocal.getErrorString("","VTCNFSUCC","");


						}else
						{	
							errString = itmDBAccessLocal.getErrorString("","VTNCONFT","");
						}

					}
					else
					{	

						errString = itmDBAccessLocal.getErrorString("","VTNCONFT","");

					}

				}
			}


			// end if errstrng
		} catch (Exception e) 
		{
			e.printStackTrace();
			throw new ITMException(e);
		} 
		finally
		{		
			try
			{
				if(errString != null && errString.trim().length() > 0)
				{
					System.out.println("--going to commit tranaction--");
					if(errString.indexOf("VTCNFSUCC") > -1)

					{
						conn.commit();
						System.out.println("--transaction commited--");
					}
					else
					{
						conn.rollback();
						System.out.println("--transaction rollback--");
					}
				}

				if(rs != null)
				{
					rs.close();
					rs = null;
				}
				if(pstmt != null)
				{
					pstmt.close();
					pstmt = null;
				}
				conn.close();
			}
			catch(Exception e)
			{
				System.out.println("Exception : "+e);e.printStackTrace();
				throw new ITMException(e);
			}
		}



		return errString;
	
	}
	private String checknull(String string)
	{
		if (string == null) 
		{
			string = "";
		}
		return string;
	}


}