/**
 For DB2 database condition added in sql statement - by ritesh on 03/dec/13 for supreme.
 */
package ibase.webitm.ejb.dis.adv;

import java.sql.*;
import java.rmi.RemoteException;
import javax.ejb.SessionContext;
import javax.ejb.CreateException;
//import javax.ejb.SessionBean;// commented for ejb3
import ibase.system.config.ConnDriver;
import ibase.webitm.utility.ITMException;
import ibase.webitm.utility.TransIDGenerator;
import ibase.webitm.ejb.ITMDBAccessEJB;
import ibase.webitm.utility.*;
import ibase.webitm.ejb.ActionHandlerEJB;
import org.w3c.dom.Document;
import ibase.system.config.*;
import ibase.utility.CommonConstants;
import java.util.Properties;
import javax.naming.InitialContext;
import ibase.webitm.ejb.sys.GenerateEDI;
//import ibase.webitm.ejb.sys.GenerateEDIHome;//commented for ejb3
import ibase.webitm.ejb.sys.GenerateEDILocal;//added for ejb3
import javax.ejb.Stateless;//added for ejb3

@Stateless//added for ejb3
public class DistOrderAmdConf extends ActionHandlerEJB implements DistOrderAmdConfLocal , DistOrderAmdConfRemote //SessionBean //commented for ejb3
{
    /*public void ejbCreate() throws RemoteException,CreateException{
    }
    public void ejbRemove(){

    }
    public void ejbActivate(){

    }
    public void ejbPassivate(){

    }
    public void setSessionContext(SessionContext se){

    }*/

    public String confirm(String tranId,String xtraParams, String forcedFlag) throws RemoteException,ITMException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
		ResultSet rs1 = null;
        String sql = "";
        ConnDriver connDriver = null;
		String loginEmpCode = null;
        ibase.webitm.utility.GenericUtility genericUtility = null;
		Document dom = null;
		String errCode = null;
		int count=0;
		ITMDBAccessEJB itmDBAccessEJB = null;
		String errString = null;
		int upd = 0;
        try
		{
            itmDBAccessEJB = new ITMDBAccessEJB();
			genericUtility = ibase.webitm.utility.GenericUtility.getInstance();
            connDriver = new ConnDriver();
            conn = connDriver.getConnectDB("DriverITM");
            conn.setAutoCommit(false);
			loginEmpCode = genericUtility.getValueFromXTRA_PARAMS(xtraParams, "loginEmpCode");
			//check if there is record in detail
			int countDet = 0;
			if( errString == null || errString.trim().length() == 0 )
			{
				sql = " select count( 1 ) cnt from distordamd where amd_no = ? AND CONFIRMED = 'Y' ";
				if ("db2".equalsIgnoreCase(CommonConstants.DB_NAME ) ||  "mysql".equalsIgnoreCase(CommonConstants.DB_NAME ))
				{
					sql = sql + " for update ";
				}System.out.println("executing Sql "+sql);
				pstmt = conn.prepareStatement( sql );
				pstmt.setString(1,tranId.trim());
				rs = pstmt.executeQuery();
				
				if( rs.next() )
				{
					countDet = rs.getInt( "cnt" );
				}
				rs.close();
				rs = null;
				pstmt.close();
				pstmt = null;				
								
				if( countDet > 0 )
				{
					errString = itmDBAccessEJB.getErrorString("","VTCONFMD","","",conn);
				}
			}
			if( errString == null || errString.trim().length() == 0 )
			{
				String	distOrder = null ;
				String	purcOrder = null ;
				String	remark = null ;
				String	remarks1 = null ;
				String	remarks2 = null ;
				String	transMode = null ;
				String	totAmt = null ;
				String	netAmt = null ;
				String	taxAmt = null ;
											
				sql = " select dist_order, "
					+"	purc_order, "
					+"	tot_amt, "
					+"	net_amt, "
					+"	tax_amt, "
					+"	remarks, "
					+"	remarks1, "
					+"	remarks2, "
					+"	trans_mode "
					+"	from "
					+"	distordamd "
					+"	where "
					+"	amd_no = ? ";
				if ("db2".equalsIgnoreCase(CommonConstants.DB_NAME ) ||  "mysql".equalsIgnoreCase(CommonConstants.DB_NAME ))
				{
					sql = sql + " for update ";
				}System.out.println("executing Sql "+sql);
					pstmt = conn.prepareStatement( sql );
					pstmt.setString(1,tranId.trim());
					rs = pstmt.executeQuery();
					if( rs.next() )
					{
						distOrder = rs.getString( "dist_order" );
						purcOrder = rs.getString( "purc_order" ) == null ? "" : rs.getString( "purc_order" );
						remark = rs.getString( "remarks" ) == null ? "" : rs.getString( "remarks" );
						remarks1 = rs.getString( "remarks1" ) == null ? "" : rs.getString( "remarks1" );
						remarks2 = rs.getString( "remarks2" ) == null ? "" : rs.getString( "remarks2" );
						transMode = rs.getString( "trans_mode" ) == null ? "" : rs.getString( "trans_mode" );
						netAmt = rs.getString( "net_amt" ) == null ? "" : rs.getString( "net_amt" );
						taxAmt = rs.getString( "tax_amt" ) == null ? "" : rs.getString( "tax_amt" );
						totAmt = rs.getString( "tot_amt" ) == null ? "" : rs.getString( "tot_amt" );
					}
					rs.close();
					rs = null;	
					pstmt.close();
					pstmt = null;	
					
					sql = " select count( 1 ) cnt from distorder where dist_order = ? AND CONFIRMED = 'Y' ";
					if ("db2".equalsIgnoreCase(CommonConstants.DB_NAME ) ||  "mysql".equalsIgnoreCase(CommonConstants.DB_NAME ))
					{
						sql = sql + " for update ";
					}System.out.println("executing Sql "+sql);
					pstmt = conn.prepareStatement( sql );
					pstmt.setString(1,distOrder.trim());
					rs = pstmt.executeQuery();
					
					if( rs.next() )
					{
						countDet = rs.getInt( "cnt" );
					}
					rs.close();
					rs = null;
					pstmt.close();
					pstmt = null;				
										
					if( countDet == 0 )
					{
						errString = itmDBAccessEJB.getErrorString("","NVCONFIRM","","",conn);
					}
					else
					{
						sql = " update distorder set "
							+"		purc_order = ?, "
							+"		remarks1 = ?, "
							+"		remarks = ?, "
							+"		remarks2 = ?, "
							+"		net_amt = ?, "
							+"		tot_amt = ?, "
							+"		tax_amt = ?, "
							+"		trans_mode = ?, "
							+"		emp_code__aprv = ? "
							+"	where dist_order = ? ";
						
						pstmt = conn.prepareStatement( sql );
						
						pstmt.setString(1,purcOrder.trim());
						pstmt.setString(2,remarks1.trim());
						pstmt.setString(3,remark.trim());
						pstmt.setString(4,remarks2.trim());
						pstmt.setString(5,netAmt.trim());
						pstmt.setString(6,totAmt.trim());
						pstmt.setString(7,taxAmt.trim());
						pstmt.setString(8,transMode.trim());
						pstmt.setString(9,loginEmpCode.trim());
						pstmt.setString(10,distOrder.trim());
											
						pstmt.executeUpdate();
						pstmt.close();
						pstmt = null;

						String distOrderamd = null;
						String lineNoDistord = null;
						String qtyOrder = null;
						String rate = null;
						String rateClg = null;
						String remarks = null;
						String packInstr = null;
						String taxClass = null;
						String taxChap = null;
						String taxEnv = null;
						String itemCode = null;
						String custSpecNo = null;
						String	totAmtDet = null ;
						String	netAmtDet = null ;
						String	taxAmtDet = null ;
						String  status = null;   // added by ritesh on 20/dec/13 for DI3HSUP004
						
						sql = " select hdr.dist_order, dtl.line_no_distord, dtl.qty_order, dtl.rate, "
							+"	dtl.rate__clg,dtl.remarks, dtl.pack_instr,dtl.cust_spec__no, "
							+"	dtl.tot_amt, dtl.net_amt,dtl.tax_amt, "
							+"	dtl.tax_class, dtl.tax_chap, dtl.tax_env "
							+"  ,dtl.status "      // added by ritesh on 20/dec/13 FOR DI3HSUP004
							+"	from distordamd hdr, distordamd_det dtl "
							+"	where  hdr.amd_no = dtl.amd_no "
							+"	and hdr.amd_no = ?"
							+"	and dtl.line_no_distord is not null "
							+"	and ( hdr.dist_order, dtl.line_no_distord ) " 
							+"	in( select dist_order, line_no "
							+"	from distorder_det dist "
							+"	where trim( dist.dist_order) = trim(hdr.dist_order) )";
						if ("db2".equalsIgnoreCase(CommonConstants.DB_NAME ) ||  "mysql".equalsIgnoreCase(CommonConstants.DB_NAME ))
						{
							sql = sql + " for update ";
						}System.out.println("executing Sql "+sql);
						pstmt = conn.prepareStatement( sql );
						pstmt.setString(1,tranId.trim());
						rs = pstmt.executeQuery();
						while( rs.next() )
						{
							distOrderamd = rs.getString( "dist_order" );
							lineNoDistord = rs.getString( "line_no_distord" ) == null ? "" : rs.getString( "line_no_distord" );
							qtyOrder = rs.getString( "qty_order" ) == null ?  "" : rs.getString( "qty_order" );
							rate = rs.getString( "rate" ) == null ? "" : rs.getString( "rate" );
							rateClg = rs.getString( "rate__clg" ) == null ? "" : rs.getString( "rate__clg" );
							remarks = rs.getString( "remarks" ) == null ? "" : rs.getString( "remarks" );
							packInstr = rs.getString( "pack_instr" ) == null ? "" : rs.getString( "pack_instr" );
							taxClass = rs.getString( "tax_class" ) == null ?  "" : rs.getString( "tax_class" );
							taxChap = rs.getString( "tax_chap" ) == null ?  "" : rs.getString( "tax_chap" );
							taxEnv = rs.getString( "tax_env" ) == null ?  "" : rs.getString( "tax_env" );
							netAmtDet = rs.getString( "net_amt" ) == null ? "" : rs.getString( "net_amt" );
							taxAmtDet = rs.getString( "tax_amt" ) == null ? "" : rs.getString( "tax_amt" );
							totAmtDet = rs.getString( "tot_amt" ) == null ? "" : rs.getString( "tot_amt" );
							custSpecNo = rs.getString( "cust_spec__no" ) == null ?  "" : rs.getString( "cust_spec__no" );
							status = rs.getString( "status" ) == null ?  "" : rs.getString( "status" ); // added by ritesh on 20/dec/13 FOR DI3HSUP004
						
							sql = "update distorder_det set dist_order = ?, "
								+"	line_no = ?, "
								+"	qty_order = ?, " 
								+"	rate = ?, " 
								+"	rate__clg = ?, " 
								+"	remarks = ?, " 
								+"	pack_instr = ?, " 
								+"	tax_class = ?, " 
								+"	tax_chap = ?, " 
								+"	tax_env = ?, " 
								+"	net_amt = ?, "
								+"	tot_amt = ?, "
								+"	tax_amt = ?, "		
								+"	cust_spec__no = ?, "
								+"  qty_confirm = ? "
								+"  ,status = ? "   // added by ritesh on 20/dec/13 FOR DI3HSUP004
								+"	where dist_order = ? "
								+"	and line_no = ? "	;
							
							pstmt = conn.prepareStatement( sql );
							pstmt.setString(1,distOrderamd.trim());
							pstmt.setString(2,lineNoDistord.trim());
							pstmt.setString(3,qtyOrder.trim());
							pstmt.setString(4,rate.trim());
							pstmt.setString(5,rateClg.trim());
							pstmt.setString(6,remarks.trim());
							pstmt.setString(7,packInstr.trim());
							pstmt.setString(8,taxClass.trim());
							pstmt.setString(9,taxChap.trim());
							pstmt.setString(10,taxEnv.trim());
							pstmt.setString(11,netAmtDet.trim());
							pstmt.setString(12,totAmtDet.trim());
							pstmt.setString(13,taxAmtDet.trim());
							pstmt.setString(14,custSpecNo.trim());
							pstmt.setString(15,qtyOrder.trim());
							pstmt.setString(16,status.trim());  // added by ritesh on 20/dec/13 FOR DI3HSUP004
							pstmt.setString(17,distOrderamd.trim());
							pstmt.setString(18,lineNoDistord.trim());
							
														
							pstmt.executeUpdate();
								
						}	
						rs.close();
						rs = null;
						pstmt.close();
						pstmt = null;				
						
						sql = " select hdr.dist_order, dtl.line_no_distord, dtl.qty_order, dtl.rate, dtl.item_code , "
							+"	dtl.rate__clg,dtl.remarks, dtl.pack_instr, dtl.tax_class, dtl.tax_chap, dtl.tax_env "
							+"	from distordamd hdr, distordamd_det dtl "
							+"	where  hdr.amd_no = dtl.amd_no "
							+"	and hdr.amd_no = ? "
							+"	and dtl.line_no_distord is null "
							+"	and ( hdr.dist_order ) " 
							+"	in( select dist_order "
							+"	from distorder_det dist "
							+"	where trim( dist.dist_order) = trim(hdr.dist_order) )";
						if ("db2".equalsIgnoreCase(CommonConstants.DB_NAME ) ||  "mysql".equalsIgnoreCase(CommonConstants.DB_NAME ))
						{
							sql = sql + " for update ";
						}System.out.println("executing Sql "+sql);
						pstmt = conn.prepareStatement( sql );
						pstmt.setString(1,tranId.trim());
						rs = pstmt.executeQuery();
						
						while( rs.next() )
						{
							distOrder = rs.getString( "dist_order" );
							itemCode = rs.getString( "item_code" );
							qtyOrder = rs.getString( "qty_order" ) == null ? "" :  rs.getString( "qty_order" );
							rate = rs.getString( "rate" ) == null ? "" : rs.getString( "rate" );
							rateClg = rs.getString( "rate__clg" ) == null ? "" : rs.getString( "rate__clg" );
							remarks = rs.getString( "remarks" ) == null ? "" : rs.getString( "remarks" );
							packInstr = rs.getString( "pack_instr" ) == null ? "" : rs.getString( "pack_instr" );
							taxClass = rs.getString( "tax_class" ) == null ? "" : rs.getString( "tax_class" );
							taxChap = rs.getString( "tax_chap" ) == null ? "" : rs.getString( "tax_chap" );
							taxEnv = rs.getString( "tax_env" ) == null ? "" : rs.getString( "tax_env" );
													
							sql = " select (max(line_no) + 1) line_no from distorder_det where dist_order = ? ";
							if ("db2".equalsIgnoreCase(CommonConstants.DB_NAME ) ||  "mysql".equalsIgnoreCase(CommonConstants.DB_NAME ))
							{
								sql = sql + " for update ";
							}System.out.println("executing Sql "+sql);
							pstmt = conn.prepareStatement( sql );
							pstmt.setString(1,distOrder.trim());
							rs1 = pstmt.executeQuery();
							String lineNo = null;
							if ( rs1.next() )
							{
								lineNo = rs1.getString( "line_no" );
							}
							sql = "insert into distorder_det ( dist_order,line_no,item_code,qty_order,rate,rate_clg,remarks,pack_instr,tax_class,tax_chap,tax_env,qty_confirm ) "
								+"	values ( ?,?,?,?,?,?,?,?,?,?,?,?) ";
							pstmt = conn.prepareStatement( sql );
							pstmt.setString(1,distOrder.trim());
							pstmt.setInt(2,Integer.parseInt(lineNo.trim()));
							pstmt.setString(3,itemCode.trim());
							pstmt.setString(4,qtyOrder.trim());
							pstmt.setString(5,rate.trim());
							pstmt.setString(6,rateClg.trim());
							pstmt.setString(7,remarks.trim());
							pstmt.setString(8,packInstr.trim());
							pstmt.setString(9,taxClass.trim());
							pstmt.setString(10,taxChap.trim());
							pstmt.setString(11,taxEnv.trim());
							pstmt.setString(12,qtyOrder.trim());
							pstmt.executeUpdate();
							rs1.close();
							rs1 = null;
								
						}
						rs.close();
						rs = null;		
						pstmt.close();
						pstmt = null;				
																		
						sql = "update distordamd set conf_date = ?,confirmed = 'Y' where amd_no = ?";
						pstmt = conn.prepareStatement( sql );
					    pstmt.setTimestamp( 1, new java.sql.Timestamp( System.currentTimeMillis() ) );
						pstmt.setString(2,tranId.trim());
						pstmt.executeUpdate();
						pstmt.close();
						pstmt = null;				
					}
			}
			//call edi generation
			if( errString == null || errString.trim().length() == 0 )
			{
				errCode = null;
				GenerateEDILocal genEDIRemote = null;
				
				AppConnectParm appConnect = new AppConnectParm();
				Properties props = appConnect.getProperty();
				InitialContext ctx = new InitialContext(props);
				
				//GenerateEDIHome genEDIHome = ( GenerateEDIHome )ctx.lookup( "GenerateEDI" );// commented for ejb3
				genEDIRemote = ( GenerateEDILocal )ctx.lookup( "ibase/GenerateEDI/local" );// added for ejb3
				//GenerateEDI genEDIRemote = (GenerateEDI)genEDIHome.create();//commented for ejb3
				
				//errCode = genEDIRemote.genEDI( "w_distordamd", tranId, "E", xtraParams, conn );
				//Method changed by manoj dtd 18/12/2013 as per no. of arguments used
				errCode = genEDIRemote.genEDI( "w_distordamd", tranId,  xtraParams, conn );
				if( "SUCCESS".equalsIgnoreCase( errCode ) )
				{
					errCode = null;
				}
				
			}
			if( errCode != null && errCode.trim().length() > 0 )
			{
				errString = itmDBAccessEJB.getErrorString("", errCode, "", "", conn );
			}
			//end calling edi generation
			
			if( errString != null && errString.trim().length() > 0 )
			{
				
				conn.rollback();
				return errString;
			}
			else
			{
				conn.commit();
				errString = itmDBAccessEJB.getErrorString("","VTCNFSUCC","");
			}
		}
		catch(ITMException ie)
		{
			try
			{
				conn.rollback();
			}
			catch(Exception t)
			{
				t.printStackTrace();
				throw new ITMException (t);
			}
			ie.printStackTrace();
			errString = itmDBAccessEJB.getErrorString("","VTDESNCONF","","",conn);
			return errString;
		}
		catch(Exception e)
		{
			try
			{
				conn.rollback();
			}
			catch(Exception t)
			{
				t.printStackTrace();
				throw new ITMException ( t );
			}
			e.printStackTrace();
			errString = itmDBAccessEJB.getErrorString("","VTDESNCONF","","",conn);
			return errString;
		}
		finally
		{
			try{
				if( pstmt != null )
				{
					pstmt.close();
				}
				pstmt = null;
				if(conn != null)
				{
					conn.close();
				}
				conn = null;
			}
			catch(Exception e)
			{
				e.printStackTrace();
				throw new ITMException ( e );
			}
		}
		return errString;
	}
}