package ibase.webitm.ejb.dis;
import ibase.system.config.ConnDriver;
import ibase.utility.GenericUtility;
import ibase.webitm.ejb.ActionHandlerEJB;
import ibase.webitm.ejb.ITMDBAccessEJB;
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 javax.ejb.Stateless;
import org.w3c.dom.Document;

@Stateless
public class ProofOfDeliveryDefault extends ActionHandlerEJB implements ProofOfDeliveryDefaultLocal,ProofOfDeliveryDefaultRemote
{
	
	  public String actionHandler()  throws RemoteException,ITMException
	  {
	    return "";
	  }
	  public String actionHandler(String actionType, String xmlString, String objContext, String xtraParams) throws RemoteException, ITMException
	  {
		  System.out.println("--------- ProofOfDeliveryDefault111-----------------actionType"+actionType);
		  Document dom=null;
		  String str=null;
		  System.out.println("actionType--->>"+actionType);
		  System.out.println("xmlString---"+xmlString);
		  System.out.println("Porder Value===="+objContext);
		  System.out.println("xtraParams---"+xtraParams);
		  try
		  {
		      GenericUtility genutility=GenericUtility.getInstance();
			  if((xmlString!=null) && (xmlString.trim().length()!=0))
			  {
			     System.out.println("xmlString from Servlet Side=="+xmlString);
				 dom=genutility.parseString(xmlString);
			  }
			  if(actionType.equalsIgnoreCase("Items"))
			  {
			     str=getItems(dom,objContext,xtraParams);
				 
			  }
	
		  }catch(Exception e)
		  {
		  System.out.println("Exception :Porder :actionHandler(String xmlString):" + e.getMessage() + ":");
		      throw new ITMException(e);
		  }
		  return str;
	  }
	  
	  private String getItems(Document dom,String objContext,String xtraParams) throws ITMException
	  {
		  ResultSet rs=null;
			Connection conn=null;
			ConnDriver ConnDriver = new ConnDriver();
			PreparedStatement pstmt=null;
			String retString="",sql="",invoiceID="",lotNo="",lotSl="",itemSerPorm="",itemCode="",itemCodeDesc="",locCode="";
			int invLineNo=0;
			double rate=0.0,quantity=0.0;
			String errString="";
			int updCnt=0;
		  	StringBuffer valueXmlBuff = new StringBuffer("<?xml version=\"1.0\"?>\r\n<Root>\r\n");
		    GenericUtility genutility = GenericUtility.getInstance();	    
		 
		    try
		    {
		    	ITMDBAccessEJB itmDBAccess = new ITMDBAccessEJB();
				ConnDriver connDriver = null;
				connDriver = new ConnDriver();
				conn = connDriver.getConnectDB("DriverValidator");			
				conn.setAutoCommit(false);
				invoiceID = genutility.getColumnValue("invoice_id", dom);
				System.out.println("------invoiceID--------- :"+invoiceID);
				if(objContext !=null)
				{
					sql="select rate,quantity,lot_no,lot_sl,item_ser__prom,item_code,inv_line_no from invoice_trace where invoice_id = ?";
					pstmt=conn.prepareStatement(sql);
					pstmt.setString(1, invoiceID);
					rs=pstmt.executeQuery();
					int lineNo=0;
					while(rs.next())
					{
						lineNo++;
						rate=rs.getDouble(1);
						quantity=rs.getDouble(2);
						lotNo=rs.getString(3)==null ? "" :rs.getString(3);
						lotSl=rs.getString(4)==null ? "" :rs.getString(4);
						itemSerPorm=rs.getString(5)==null ? "" :rs.getString(5);
						itemCode=rs.getString(6)==null ? "" : rs.getString(6);
						invLineNo=rs.getInt(7);
					
						valueXmlBuff.append("<Detail>\r\n");					
						valueXmlBuff.append("<line_no>").append("<![CDATA["+lineNo+"]]>").append("</line_no>");
						valueXmlBuff.append("<line_no__trace>").append("<![CDATA["+invLineNo+"]]>").append("</line_no__trace>");
						valueXmlBuff.append("<rate__inv>").append("<![CDATA["+rate+"]]>").append("</rate__inv>");
						valueXmlBuff.append("<quantity__inv>").append("<![CDATA["+quantity+"]]>").append("</quantity__inv>");
						
						valueXmlBuff.append("<lot_no>").append("<![CDATA["+lotNo+"]]>").append("</lot_no>");
						valueXmlBuff.append("<lot_sl>").append("<![CDATA["+lotSl+"]]>").append("</lot_sl>");
						valueXmlBuff.append("<item_ser__prom>").append("<![CDATA["+itemSerPorm+"]]>").append("</item_ser__prom>");
						valueXmlBuff.append("<item_code>").append("<![CDATA["+itemCode+"]]>").append("</item_code>");
						itemCodeDesc=getNameOrDescrForCode(conn, "item", "descr", "item_code", itemCode);
						locCode=getNameOrDescrForCode(conn, "item", "loc_code", "item_code", itemCode);
						valueXmlBuff.append("<descr>").append("<![CDATA["+itemCodeDesc+"]]>").append("</descr>");
						valueXmlBuff.append("<loc_code>").append("<![CDATA["+locCode+"]]>").append("</loc_code>");
						
						valueXmlBuff.append("</Detail>\r\n");
						//select descr from item where item_code = ?
						System.out.println("------->>>>Line no :"+lineNo+" rate inv :"+rate+"quantity : "+quantity+" Lot No : "+lotNo+" lotSl : "+lotSl);
						System.out.println("------->>>>item_ser__prom : "+itemSerPorm+" itemCodeDesc:"+itemCodeDesc+" loc code "+locCode);
						itemCodeDesc="";locCode="";
					}
					
					pstmt.close();
					pstmt = null;
					rs.close();
					rs = null;
					valueXmlBuff.append("</Root>\r\n");
					retString = valueXmlBuff.toString();	
					
				}//end objContext				 
				
		    }
		    catch(SQLException se)
			{
				System.out.println("SQLException : class ProofOfDeliveryDefault : ");
				retString = GenericUtility.getInstance().createErrorString(se);
				se.printStackTrace();
				try
				{
				  conn.rollback();
				}
				catch(Exception e){
					System.out.println("Exception : Occure during rollback........");e.printStackTrace();
					}
			}
			catch(Exception e)
			{
				System.out.println("Exception : class ProofOfDeliveryDefault : ");
				retString = GenericUtility.getInstance().createErrorString(e);
				e.printStackTrace();
				try
				{
					conn.rollback();
				}
				catch(Exception se){
					System.out.println("Exception : Occure during rollback........");
					se.printStackTrace();
					}
			}			
			finally
			{
			   try{
				    if (pstmt != null)
				    {
				    	pstmt.close();
						pstmt=null;
				    }
					if (rs !=null)
					{
						rs.close();
						rs=null;
					}
					if(conn!=null)
					{		
						conn.close();
						conn = null;
					}
					
			    }catch(Exception e)
				{
					System.out.println("inside Finally Exception ProofOfDeliveryDefault class:submit() ");
					e.printStackTrace();
				}

			}
		    System.out.println("Return String ..in deafault EJB ----------->>>>>>>>>>> \n" + retString);
			  return retString;
		
	  }
	  
	  private String getNameOrDescrForCode(Connection conn, String table_name, String descr_col_name,String whrCondCol, String whrCondVal)
		{
				String descr = null;
				
				if(conn!=null){
					
					ResultSet rs=null;
					PreparedStatement pstmt = null;
					
					String sql="SELECT "+descr_col_name+" FROM "+table_name+" WHERE "+whrCondCol+" = ?";				
					System.out.println("SQL in getNameOrDescrForCode method : "+sql);
					try
					{
						pstmt = conn.prepareStatement(sql);
						pstmt.setString(1,whrCondVal);
						rs = pstmt.executeQuery();
						if(rs.next())
						{
							descr = rs.getString(descr_col_name);
						}
					}
					catch(SQLException e)
					{
						System.out.println("SQL Exception In getNameOrDescrForCode method of ProofOfDelivery Class : "+e.getMessage());
						e.printStackTrace();
					}
					catch(Exception ex)
					{
						System.out.println("Exception In getNameOrDescrForCode method of ProofOfDelivery Class : "+ex.getMessage());
						ex.printStackTrace();
					}finally{
						
						try{
							
							if(pstmt!=null){
								pstmt.close();
								pstmt = null;
							}
							if(rs!=null){
								rs.close();
								rs = null;
							}
						}catch (SQLException se) {
							se.printStackTrace();
						}
					}
				}else{
					try {
						throw new SQLException("==========Connection is null in getNameOrDescrForCode method of ProofOfDeliveryDefault class===========");
					} catch (SQLException e) {
						e.printStackTrace();
					}
				}
					
			return descr;
		}
	  
}
