package ibase.webitm.ejb.mfg;

import ibase.system.config.ConnDriver;
import ibase.webitm.ejb.ValidatorEJB;
import ibase.webitm.utility.GenericUtility;
import ibase.webitm.utility.ITMException;

import java.rmi.RemoteException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import org.w3c.dom.Document;

@javax.ejb.Stateless
public class WorkOrdRcpDetPostSave extends ValidatorEJB implements WorkOrdRcpDetPostSaveLocal,WorkOrdRcpDetPostSaveRemote
{
	public String postSave(String xmlString,String editFlag,String tranId,  String xtraParams,Connection conn) throws RemoteException,ITMException
	{

		System.out.println(">>>>>>>>>>>>>>CONNECTION"+conn);
		System.out.println("------------ WorkOrdRcpPostSave postSave method called-----------------tranId : "+ tranId);		
		Document dom = null;
		String errString="";

		GenericUtility genericUtility = GenericUtility.getInstance();



		

		try
		{
			if(xmlString != null && xmlString.trim().length()!=0)
			{
				dom = genericUtility.parseString(xmlString); 
				System.out.println("xmlString d" + xmlString);
			}
			
			tranId = GenericUtility.getInstance().getColumnValue("tran_id",dom);

			System.out.println("------------ WorkOrdRcpPostSave postSave method called-----------------tranId from dom: "+ tranId);
			
			conn = null;
			ConnDriver connDriver = new ConnDriver();
			conn = connDriver.getConnectDB("DriverITM");
			connDriver= null;
			errString = postSave(dom,tranId,editFlag,xtraParams,conn);


		}
		catch(Exception e)
		{
			System.out.println("Exception : WorkOrdRcpPostSave : postSave : ==>\n"+e.getMessage());
			throw new ITMException(e);
		}		
		return errString;
	}

	public String postSave(Document dom,String tranId,String editflag,String xtraParams,Connection conn)
	{
		System.out.println("post save dom data1 ---------------:"+dom);
		String sql = "", error = "";
		int quantity = 0, grossWeight = 0, tareWeight = 0, netWeight = 0, noArt = 0;
		int cnt1 = 0;
		String lotSl="",locCode="";
		int cnt = 0;

		PreparedStatement pstmt = null;
		ResultSet rs=null;
		try
		{

			
			
			sql = "select count(*) from workorder_rcp_det where tran_id = ? and lot_sl is not null";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, tranId);
			rs = pstmt.executeQuery();

			if(rs.next()){
			
				cnt = rs.getInt(1);
				
			}
			pstmt.close();
			pstmt = null;
			rs.close();
			rs = null;
			
			if(cnt > 0)
			{
				System.out.println("ENTER FOR SETTING    >>>");
				locCode = " ";
				lotSl = " ";
			}
			
			sql = "select sum(coalesce(quantity,0)), sum(coalesce(gross_weight,0)), sum(coalesce(tare_weight,0)), " +
					" sum(coalesce(net_weight,0)), sum(coalesce(no_art,0)) from workorder_rcp_det where tran_id = ? ";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, tranId);
			rs = pstmt.executeQuery();

			if(rs.next()){
				quantity = rs.getInt(1);
				grossWeight = rs.getInt(2);
				tareWeight = rs.getInt(3);
				netWeight = rs.getInt(4);
				noArt = rs.getInt(5);
				
			}
			pstmt.close();
			pstmt = null;
			rs.close();
			rs = null;

			

			sql = "update workorder_receipt set quantity = ?, gross_weight = ?, tare_weight = ?, net_weight = ?, no_art = ?, lot_sl = ?, loc_code = ? where tran_id = ?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, quantity);
			pstmt.setInt(2, grossWeight);
			pstmt.setInt(3, tareWeight);
			pstmt.setInt(4, netWeight);
			pstmt.setInt(5, noArt);
			pstmt.setString(6, lotSl);
			pstmt.setString(7, locCode);
			pstmt.setString(8, tranId);

			cnt1 = pstmt.executeUpdate();
			System.out.println(">>>>>>>successfully updated record bom post save cnt1:" + cnt1);
			conn.commit();
			pstmt.close();
			pstmt = null;

		}catch(Exception e)
		{
			try {
				System.out.println(">>>>>>>>>>>>In catch Before rollback>>>");
				conn.rollback();
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
				error=e1.getMessage();
			}
			System.out.println("Exception ::"+e);
			e.printStackTrace();
			error=e.getMessage();

		}	
		finally
		{
			try {
				System.out.println(">>>>>>>>>>>>In finally Before Commit>>>");
				if(pstmt != null)
					pstmt.close();
				if(conn != null)
				{
					conn.close();	
				}

				if(rs != null)
					rs.close();

				pstmt = null;
				rs = null;
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
				error=e.getMessage();
			}		
		}

		return error;

	}

}
