package ibase.webitm.ejb.dis;

import ibase.system.config.ConnDriver;
import ibase.utility.CommonConstants;
import ibase.webitm.ejb.ITMDBAccessEJB;
import ibase.webitm.ejb.MasterStatefulLocal;
import ibase.webitm.ejb.ProcessEJB;
import ibase.webitm.ejb.dis.DistCommon;
import ibase.webitm.ejb.dis.InvAllocTraceBean;
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.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Map;

import org.w3c.dom.Document;
import org.w3c.dom.Node;
import org.w3c.dom.NodeList;

@javax.ejb.Stateless

public class PorcpProcessEJB extends ProcessEJB implements PorcpProcessLocal,PorcpProcessRemote
{

	public String wfValData() throws RemoteException, ITMException
	{
		return "*********************STUDENT INFO****************************"; 	
	}
	public String getData(String xmlString, String xmlString2, String windowName, String xtraParams) throws RemoteException,ITMException
	{
		GenericUtility genericUtility = GenericUtility.getInstance();
		System.out.println("==================YOGESHM==================");
		String rtrStr = "";
		Document headerDom = null;
		Document detailDom = null;
		System.out.println("getData(xmlString,xmlString2,windowName,xtraParams CALLED..."+ xmlString);
		try
		{
			if(xmlString != null && xmlString.trim().length()!=0)
			{
				System.out.println("XML String :"+xmlString);
				headerDom = genericUtility.parseString(xmlString); 				
			}
			if(xmlString2 != null && xmlString2.trim().length()!=0)
			{
				System.out.println("XML String :"+xmlString2);
				detailDom = genericUtility.parseString(xmlString2); 				
			}
			rtrStr = getData(headerDom, detailDom, windowName, xtraParams);
		}
		catch (Exception e)
		{
			System.out.println("Exception :StockAllocationPrcEJB :getData(String xmlString, String xmlString2, String windowName, String xtraParams):" + e.getMessage() + ":");
			rtrStr = e.getMessage();
		}
		return rtrStr; 
	}

	public String getData(Document headerDom, Document detailDom, String windowName, String xtraParams) throws RemoteException,ITMException
	{
		String siteCode="";
		String tranId="";
		String fromDate="";
		String toDate="";
		String errCode = "";
		String errString = "";
		PreparedStatement pstmt=null;
		String getDataSql=null;
		String resultString = "";
		Connection conn = null;
		ResultSet rs=null;
		StringBuffer test=new StringBuffer();
		ITMDBAccessEJB itmDBAccessEJB = new ITMDBAccessEJB();
		try
		{
			int count = 0;			
			NodeList parentList = null;
			NodeList childList = null;
			int noOfChilds = 0;
			String childNodeName = "";
			String sql = "";
			Node childNode = null;
			GenericUtility genericUtility = GenericUtility.getInstance();
			ConnDriver connDriver = new ConnDriver();
			SimpleDateFormat sdf;
			conn = connDriver.getConnectDB("DriverITM");
			connDriver = null;
			String userId = genericUtility.getValueFromXTRA_PARAMS( xtraParams, "loginCode" );
			siteCode = genericUtility.getColumnValue("site_code",headerDom);
			tranId = genericUtility.getColumnValue("tran_id",headerDom);
			fromDate = genericUtility.getColumnValue("from_date",headerDom);			
			toDate= genericUtility.getColumnValue("to_date",headerDom);
			System.out.println("headerDom:"+headerDom);
			System.out.println("detailDom:"+detailDom);
			System.out.println("windowName:"+windowName);
			System.out.println("xtraParams:"+xtraParams);

			if ( siteCode == null || siteCode.trim().length() == 0 )
			{
				errCode = "BLKSITECD";
				errString = itmDBAccessEJB.getErrorString("","BLKSITECD",userId);
				return errString; 
			}
			else if(siteCode != null || siteCode.trim().length() != 0)
			{
				sql = " SELECT COUNT(*) FROM SITE WHERE SITE_CODE = ? ";
				pstmt =  conn.prepareStatement(sql);
				pstmt.setString(1,siteCode);
				rs = pstmt.executeQuery();
				if(rs.next())
				{
					count =  rs.getInt(1);
				}
				if(count == 0) 
				{
					errCode = "INVSITECD";
					errString = itmDBAccessEJB.getErrorString("","INVSITECD",userId);
					return errString; 
				}
				rs.close();
				rs = null;
				pstmt.close();
				pstmt = null;
			} //end of site_code

			if ( tranId == null || tranId.trim().length() == 0 ) //tran_id
			{
				errCode = "BLKPRCHNO";
				errString = itmDBAccessEJB.getErrorString("","BLKPRCHNO",userId);
				return errString; 
			} 
			else if(tranId != null || tranId.trim().length() != 0)
			{
				sql = " SELECT COUNT(*) FROM PORCP WHERE TRAN_ID = ? ";
				pstmt =  conn.prepareStatement(sql);
				pstmt.setString(1,tranId);
				rs = pstmt.executeQuery();
				if(rs.next())
				{
					count =  rs.getInt(1);
				}
				if(count == 0) 
				{
					errCode = "INVPRCHNO";
					errString = itmDBAccessEJB.getErrorString("","INVPRCHNO",userId);
					return errString; 
				}
				else
				{
					siteCode = genericUtility.getColumnValue("site_code",headerDom);
					tranId = genericUtility.getColumnValue("tran_id",headerDom);
					sql=" SELECT COUNT(TRAN_ID) FROM PORCP WHERE SITE_CODE = ? AND TRAN_ID = ? ";
					pstmt =  conn.prepareStatement(sql);
					pstmt.setString(1,siteCode);
					pstmt.setString(2,tranId);
					rs = pstmt.executeQuery();
					if(rs.next())
					{
						count =  rs.getInt(1);
					}
					if(count == 0) 
					{
						errCode = "INVPRCHNM";
						errString = itmDBAccessEJB.getErrorString("","INVPRCHNM",userId);
						return errString; 
					}
				}
				rs.close();
				rs = null;
				pstmt.close();
				pstmt = null;
			} //end of tran_id

			getDataSql = " SELECT PORCP.TRAN_ID, PORCP.TRAN_DATE, PORCP.TRAN_TYPE, PORCP.SITE_CODE, PORCP.PURC_ORDER, PORCP.SUPP_CODE," 
				+" PORCP.LR_NO, PORCP.LR_DATE FROM PORCP,SUPPLIER WHERE PORCP.SITE_CODE = ? AND PORCP.TRAN_ID = ? AND PORCP.SUPP_CODE = SUPPLIER.SUPP_CODE ";    

			pstmt = conn.prepareStatement(getDataSql);
			pstmt.setString(1,siteCode);
			pstmt.setString(2,tranId);
			rs = pstmt.executeQuery();
			while(rs.next())
			{
				test.append(rs.getString(1)).append("\t");
				test.append(rs.getString(2)).append("\t");
				test.append(rs.getString(3)).append("\t");
				test.append(rs.getString(4)).append("\t");
				test.append(rs.getString(5)).append("\t");
				test.append(rs.getString(6)).append("\t");
				test.append(rs.getString(7)).append("\t");
				test.append(rs.getString(8)).append("\n");					
			}
			rs.close();
			rs = null;
			pstmt.close();
			pstmt = null;
			resultString=test.toString();
		}	
		catch(Exception e)
		{
			System.out.println("Exception occured while getData()");
			e.printStackTrace();
		}
		finally
		{
			try
			{
				if(conn != null)
				{
					if(rs != null) 
					{ 
						rs.close();
						rs = null;
					}
					if(pstmt != null) 
					{
						pstmt.close();
						pstmt = null;
					}
					conn.close();
				}
				conn = null;	
			}
			catch(Exception ex)
			{
				System.out.println("Exception occured while getData()");
				ex.printStackTrace();
			}
		}
		return resultString;	
	} //get data method closed

	public String wfValData(String currXmlDataStr, String hdrXmlDataStr, String allXmlDataStr, String objContext, String editFlag, String xtraParams) throws RemoteException, ITMException
	{
		return null;
	}
	public String wfValData(Document currDom, Document hdrDom, Document allDom, String objContext, String editFlag, String xtraParams) throws RemoteException, ITMException
	{
		return null;
	}
	public String itemChanged() throws RemoteException, ITMException
	{
		return "";
	}
	public String itemChanged(String currXmlDataStr, String hdrXmlDataStr, String allXmlDataStr, String objContext, String currentColumn, String editFlag, String xtraParams) throws RemoteException, ITMException
	{
		return null;
	}

	public String itemChanged( Document currDom, Document hdrDom, Document allDom, String objContext, String currentColumn, String editFlag, String xtraParams ) throws RemoteException,ITMException
	{
		return null;
	}

	public String process(String xmlString, String xmlString2, String windowName, String xtraParams) throws RemoteException,ITMException
	{
		String retStr = "";
		Document detailDom = null;
		Document headerDom = null;

		GenericUtility genericUtility = GenericUtility.getInstance();
		System.out.println("*******************Testing Process Method**********************");
		System.out.println("below genericUtility--------------->>>>>>>>>");
		try
		{  	
			if(xmlString != null && xmlString.trim().length()!=0)
			{
				headerDom = genericUtility.parseString(xmlString); 
				System.out.println("headerDom" + headerDom);
			}
			if(xmlString2 != null && xmlString2.trim().length()!=0)
			{
				detailDom = genericUtility.parseString(xmlString2); 
				System.out.println("detailDom" + detailDom);
			}	
			retStr = process(headerDom, detailDom, windowName, xtraParams);
		}
		catch (Exception e)
		{
			System.out.println("Exception :StockAllocationPrcEJB :process(String xmlString, String xmlString2, String windowName, String xtraParams):" + e.getMessage() + ":");
			e.printStackTrace();
			retStr = e.getMessage();
			throw new ITMException(e);
		}
		return retStr;
	}
	public String process(Document headerDom, Document detailDom, String windowName, String xtraParams) throws RemoteException,ITMException
	{
		Connection conn = null;
		ResultSet rs = null;
		NodeList childNodeList = null;
		Node parentNode = null;
		Node childNode = null;				
		PreparedStatement pstmt = null;	
		String updateSql=null;
		String sql=null;
		String tranType = "";
		String siteCode = "";
		String tranId = "";
		int parentNodeListLength = 0;
		int childNodeListLength = 0;
		NodeList parentNodeList = null;
		String childNodeName = null;	
		int count = 0;
		int tcnt= 0;
		String errCode = "";
		String errString = "";
		GenericUtility genericUtility = GenericUtility.getInstance();
		ITMDBAccessEJB itmDBAccessEJB = new ITMDBAccessEJB();

		try 
		{
			String userId = genericUtility.getValueFromXTRA_PARAMS( xtraParams, "loginCode" );
			ConnDriver connDriver = new ConnDriver();
			conn = connDriver.getConnectDB("DriverITM");
			connDriver = null;
			conn.setAutoCommit(false);
			System.out.println("headerDom:"+headerDom);
			System.out.println("detailDom:"+detailDom);
			System.out.println("windowName:"+windowName);
			System.out.println("xtraParams:"+xtraParams);


			parentNodeList = detailDom.getElementsByTagName("Detail2");
			parentNodeListLength = parentNodeList.getLength(); 

			for(int i=0; i<parentNodeListLength; i++)
			{
				parentNode = parentNodeList.item(i);
				childNodeList = parentNode.getChildNodes();
				childNodeListLength = childNodeList.getLength();
				for (int childRow = 0; childRow < childNodeListLength; childRow++)
				{

					System.out.println("ABC********");
					childNode = childNodeList.item(childRow);
					childNodeName = childNode.getNodeName();
					System.out.println("childNodeName ="+childNodeName);

					if (childNodeName.equals("tran_type"))
					{
						if(childNode.getFirstChild()!=null)
						{
							tranType = (childNode.getFirstChild().getNodeValue()).trim();
							System.out.println("************=tranType"+tranType);
						}
						if(tranType.trim().length() == 0)
						{
							errCode = "BLKTRNTYP";
							errString = itmDBAccessEJB.getErrorString("","BLKTRNTYP",userId);
							return errString; 
						}	
						else if (tranType != null || tranType.trim().length() != 0)
						{
							System.out.println("************ELSE IF=tranType"+tranType);
							System.out.println("************TRANTYPELENGTH="+tranType.trim().length());
							sql = " SELECT COUNT(*) FROM GENCODES WHERE FLD_NAME ='TRAN_TYPE' AND MOD_NAME = 'W_PORCP' AND FLD_VALUE = ? ";
							pstmt = conn.prepareStatement(sql);
							pstmt.setString(1,tranType);
							rs = pstmt.executeQuery();
							if(rs.next())
							{
								count =  rs.getInt(1);
							}
							if(count == 0) 
							{
								errCode = "INVTRNTYP";
								errString = itmDBAccessEJB.getErrorString("","INVTRNTYP",userId);
								return errString; 
							}	
						}
						rs.close();
						rs = null;
						pstmt.close();
						pstmt = null;
						//end of tran_id
					}	
					if(childNodeName.equals("tran_id"))
					{
						if(childNode.getFirstChild()!=null)
						{
							tranId=(childNode.getFirstChild().getNodeValue().trim());
							System.out.println("*************=tranId"+tranId);

						}
					}
					if(childNodeName.equals("site_code"))
					{
						if(childNode.getFirstChild()!=null)
						{
							siteCode=(childNode.getFirstChild().getNodeValue().trim());
							System.out.println("*************=siteCode"+siteCode);
						}
					}
			/*		updateSql= " UPDATE PORCP SET TRAN_TYPE = ?  WHERE  TRAN_ID= ?  AND SITE_CODE = ? ";
					pstmt = conn.prepareStatement(updateSql);									
					pstmt.setString(1,tranType);
					pstmt.setString(2,tranId);
					pstmt.setString(3,siteCode);
					count = pstmt.executeUpdate();
					conn.commit();  */
				}
				updateSql= " UPDATE PORCP SET TRAN_TYPE = ?  WHERE  TRAN_ID= ?  AND SITE_CODE = ? ";
				pstmt = conn.prepareStatement(updateSql);									
				pstmt.setString(1,tranType);
				pstmt.setString(2,tranId);
				pstmt.setString(3,siteCode);
				count = pstmt.executeUpdate();
				conn.commit();  
			}
			if(count > 0)
			{
				System.out.println("******Updated Rows Count="+count);
				errCode = "PRCESSFUL";
				errString = itmDBAccessEJB.getErrorString("","PRCESSFUL",userId);
			}   
		} 
		catch (Exception e)
		{
			System.out.println(e.getMessage());
		}
		finally
		{
			try
			{
				if(conn != null)
				{
					if(rs != null) 
					{
						rs.close();
						rs = null;
					}
					if(pstmt != null) 
					{
						pstmt.close();
						pstmt = null;
					}
					conn.close();
				}
				conn = null;	
			}
			catch (Exception ex)
			{
				ex.printStackTrace();
				System.out.println("************HELLO");
			}
		}
		return errString;
	} //process method
	private String checkNull( String input )
	{
		if( input == null )
		{
			input = "";
		}
		else
		{
			input = input.trim();
		}
		return input;
	}

} // class closed
