/********************************************************
	Title : ReplOrderIC
	Date  : 12/06/2012
	Developer: Akhilesh sikarwar
 ********************************************************/

package ibase.webitm.ejb.wms;

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.util.ArrayList;
import org.w3c.dom.Document;
import org.w3c.dom.Node;
import org.w3c.dom.NodeList;

@javax.ejb.Stateless
public class ReplOrderIC extends ValidatorEJB implements ReplOrderICLocal,ReplOrderICRemote
{

	public String wfValData() throws RemoteException, ITMException
	{
		return "";
	}
	public String wfValData(String xmlString, String xmlString1, String xmlString2, String objContext, String editFlag, String xtraParams) throws RemoteException, ITMException
	{
		Document dom = null;
		Document dom1 = null;
		Document dom2 = null;
		String errString = null;
		GenericUtility genericUtility = GenericUtility.getInstance();
		try
		{
			System.out.println("xmlString [" + xmlString + "]");
			System.out.println("xmlString1 [" + xmlString1 + "]");
			System.out.println("xmlString2 [" + xmlString2 + "]");
			if(xmlString != null && xmlString.trim().length()!=0)
			{
				dom = genericUtility.parseString(xmlString); 
			}
			if(xmlString1 != null && xmlString1.trim().length()!=0)
			{
				dom1 = genericUtility.parseString(xmlString1); 
			}
			if(xmlString2 != null && xmlString2.trim().length()!=0)
			{
				dom2 = genericUtility.parseString(xmlString2); 
			}
			errString = wfValData( dom, dom1, dom2, objContext, editFlag, xtraParams );
			System.out.println ( "ErrString: " + errString);
		}
		catch(Exception e)
		{
			System.out.println ( "Exception: ReplOrderIC: wfValData(String xmlString): " + e.getMessage() + ":" );
			errString = genericUtility.createErrorString(e);
			e.printStackTrace();
		}
		System.out.println ( "Returning from ReplOrderIC wfValData" );
		return ( errString ); 
	}

	public String wfValData(Document dom, Document dom1, Document dom2, String objContext,String editFlag, String xtraParams ) throws RemoteException, ITMException
	{
		String errString = "";
		String siteCode = "";
		String itemCode = "";
		String noArtStr = "";
		ResultSet rs = null;
		Connection conn = null;
		PreparedStatement pstmt = null;
		GenericUtility genericUtility = GenericUtility.getInstance();


		StringBuffer errStringXml = new StringBuffer("<?xml version=\"1.0\"?>\r\n<Root><Errors>");
		String errorType = "", errCode = "";
		ArrayList<String> errList = new ArrayList<String>();
		ArrayList<String> errFields = new ArrayList<String>();
		NodeList parentList = null;
		NodeList childList = null;
		int noOfChilds ;
		String childNodeName = "";
		Node childNode =null;
		String userId = "";
		String editFlagdet = "";
		int cnt = 0;
		String columnValue = "";
		String columnValue1 = "";
		String lotNo = "";
		String locCode = "";
		String lotSl = "";
		String sql = "";
		String qtyStr = "";
		int currentFormNo = 0;
		//Changed by Rohan on 05-03-12 to get quantity in double
		double qty = 0.0;
		ConnDriver connDriver = null;

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

			userId = genericUtility.getValueFromXTRA_PARAMS( xtraParams, "loginCode" );


			if ( objContext != null && objContext.trim().length() > 0 )
			{
				currentFormNo = Integer.parseInt( objContext );
			}
			switch(currentFormNo)
			{
			case 2 :
			{
				parentList = dom.getElementsByTagName( "Detail2" );
				for(int i =0; i< parentList.getLength();i++)
				{
					editFlagdet = getCurrentUpdateFlag(dom.getElementsByTagName("Detail2").item(i));
				}

				childList = parentList.item( 0 ).getChildNodes();
				noOfChilds = childList.getLength();
				System.out.println("EDIT FLAg IS["+editFlagdet+"]");
				if(editFlagdet.trim().equals("A") && editFlag.trim().equals("E"))
				{
					errList.add( "VMADDNAL" );
					errFields.add( childNodeName.toLowerCase() );
				}
				else
				{
					for (int ctr = 0; ctr < noOfChilds; ctr++)
					{        

						childNode = childList.item( ctr );
						childNodeName = childNode.getNodeName();
						if ( childNode != null && childNode.getFirstChild() != null )
						{
							columnValue = childNode.getFirstChild().getNodeValue();
						}
						/*SWITCH(CURRENTFORMNO)
				{
				CASE 2 :
				{
					PARENTLIST = DOM.GETELEMENTSBYTAGNAME( "DETAIL2" );
					CHILDLIST = PARENTLIST.ITEM( 0 ).GETCHILDNODES();
					NOOFCHILDS = CHILDLIST.GETLENGTH();
					FOR (INT CTR = 0; CTR < NOOFCHILDS; CTR++)
					{	
						CHILDNODE = CHILDLIST.ITEM( CTR );
						CHILDNODENAME = CHILDNODE.GETNODENAME();
						IF ( CHILDNODE != NULL && CHILDNODE.GETFIRSTCHILD() != NULL )
						{
							COLUMNVALUE = CHILDNODE.GETFIRSTCHILD().GETNODEVALUE();
						}


						EDITFLAGDET =GETCURRENTUPDATEFLAG(CHILDNODE);
						IF(EDITFLAG.EQUALSIGNORECASE("E") && EDITFLAGDET.EQUALSIGNORECASE("A"))
						{
							ERRLIST.ADD( "VMADDNAL" );
							ERRFIELDS.ADD( CHILDNODENAME.TOLOWERCASE() );
						}
						ELSE
						{
						SYSTEM.OUT.PRINT("EDITFLAG------------------------>"+EDITFLAGDET);*/
						if ( childNodeName.equalsIgnoreCase("item_code") )
						{
							itemCode = genericUtility.getColumnValue("item_code", dom);
							siteCode = genericUtility.getColumnValue("site_code", dom1);
							if(itemCode != null && itemCode.trim().length() > 0)
							{
								sql = "select count(*) from stock where item_code = ? and site_code = ? ";
								pstmt = conn.prepareStatement(sql);
								pstmt.setString(1, itemCode);
								pstmt.setString(2, siteCode);
								rs = pstmt.executeQuery();
								if( rs.next() )
								{
									cnt = rs.getInt(1);
									if(cnt == 0)
									{
										errList.add( "VTINVITM" );
										errFields.add( childNodeName.toLowerCase() );	
									}
								}
								rs.close();
								rs = null;
								pstmt.close();
								pstmt = null;
							}
							else
							{
								errList.add( "VMITEMCD" );
								errFields.add( childNodeName.toLowerCase() );
							}
						}

						else if ( childNodeName.equalsIgnoreCase("no_art") )
						{
							noArtStr = genericUtility.getColumnValue("no_art", dom);
							if(noArtStr == null || noArtStr.trim().length() == 0)
							{
								errList.add( "VTARTNULL" );
								errFields.add( childNodeName.toLowerCase() );
							}
						}

						else if ( childNodeName.equalsIgnoreCase("loc_code") )
						{
							locCode = genericUtility.getColumnValue("loc_code", dom);
							siteCode = genericUtility.getColumnValue("site_code", dom);
							itemCode = genericUtility.getColumnValue("item_code", dom);
							if(itemCode != null && itemCode.trim().length() > 0)
							{
								if(locCode != null && locCode.trim().length() > 0)
								{
									sql = "select count(*) from stock where item_code = ? and site_code = ? and loc_code = ? ";
									pstmt = conn.prepareStatement(sql);
									pstmt.setString(1, itemCode);
									pstmt.setString(2, siteCode);
									pstmt.setString(3, locCode);
									rs = pstmt.executeQuery();
									if( rs.next() )
									{
										cnt = rs.getInt(1);
										if(cnt == 0)
										{
											errList.add( "VXSTKLOC" );
											errFields.add( childNodeName.toLowerCase() );	
										}
									}
									rs.close();
									rs = null;
									pstmt.close();
									pstmt = null;
								}
								else
								{
									errList.add( "VMLOC4" );
									errFields.add( childNodeName.toLowerCase() );
								}
							}
							else
							{
								errList.add( "VMITEMCD" );
								errFields.add( childNodeName.toLowerCase() );
							}

						}

						else if ( childNodeName.equalsIgnoreCase("lot_no") )
						{
							lotNo = genericUtility.getColumnValue("lot_no", dom);
							siteCode = genericUtility.getColumnValue("site_code", dom);
							itemCode = genericUtility.getColumnValue("item_code", dom);
							if(itemCode != null && itemCode.trim().length() > 0)
							{
								if(lotNo != null && lotNo.trim().length() > 0)
								{
									sql = "select count(*) from stock where item_code = ? and site_code = ? and lot_no = ? ";
									pstmt = conn.prepareStatement(sql);
									pstmt.setString(1, itemCode);
									pstmt.setString(2, siteCode);
									pstmt.setString(3, lotNo);
									rs = pstmt.executeQuery();
									if( rs.next() )
									{
										cnt = rs.getInt(1);
										if(cnt == 0)
										{
											errList.add( "VTINVLOT" );
											errFields.add( childNodeName.toLowerCase() );	
										}
									}
									rs.close();
									rs = null;
									pstmt.close();
									pstmt = null;
								}
								else
								{
									errList.add( "VTLOTNO" );
									errFields.add( childNodeName.toLowerCase() );
								}
							}
							else
							{
								errList.add( "VMITEMCD" );
								errFields.add( childNodeName.toLowerCase() );
							}

						}

						else if ( childNodeName.equalsIgnoreCase("lot_sl") )
						{
							lotSl = genericUtility.getColumnValue("lot_sl", dom);
							lotNo = genericUtility.getColumnValue("lot_no", dom);
							locCode = genericUtility.getColumnValue("loc_code", dom);
							siteCode = genericUtility.getColumnValue("site_code", dom);
							itemCode = genericUtility.getColumnValue("item_code", dom);
							if(itemCode != null && itemCode.trim().length() > 0)
							{
								if(lotNo != null && lotNo.trim().length() > 0)
								{
									sql = "select count(*) from stock where item_code = ? and site_code = ? and loc_code = ? and lot_no = ? and lot_sl = ? ";
									pstmt = conn.prepareStatement(sql);
									pstmt.setString(1, itemCode);
									pstmt.setString(2, siteCode);
									pstmt.setString(3, locCode);
									pstmt.setString(4, lotNo);
									pstmt.setString(5, lotSl);
									rs = pstmt.executeQuery();
									if( rs.next() )
									{
										cnt = rs.getInt(1);
										if(cnt == 0)
										{
											errList.add( "VTINVLOTSL" );
											errFields.add( childNodeName.toLowerCase() );	
										}
									}
									rs.close();
									rs = null;
									pstmt.close();
									pstmt = null;
								}
								else
								{
									errList.add( "VTLOTNO" );
									errFields.add( childNodeName.toLowerCase() );
								}
							}
							else
							{
								errList.add( "VMITEMCD" );
								errFields.add( childNodeName.toLowerCase() );
							}

						}

						else if ( childNodeName.equalsIgnoreCase("quantity") )
						{

							qtyStr = genericUtility.getColumnValue("quantity", dom);
							if(qtyStr == null || qtyStr.trim().length() == 0)
							{
								errList.add( "COQNTYN" );
								errFields.add( childNodeName.toLowerCase() );
							}
							else
							{
								//Changed by Rohan on 05-03-12 to get quantity in double
								//int qty = Integer.parseInt(qtyStr);
								qty = Double.parseDouble(qtyStr);
								lotSl = genericUtility.getColumnValue("lot_sl", dom);
								lotNo = genericUtility.getColumnValue("lot_no", dom);
								locCode = genericUtility.getColumnValue("loc_code", dom);
								siteCode = genericUtility.getColumnValue("site_code", dom);
								itemCode = genericUtility.getColumnValue("item_code", dom);

								sql = "select count(*) from stock where item_code = ? and site_code = ? and loc_code = ? and lot_no = ? and lot_sl = ? and quantity >= ?";
								pstmt = conn.prepareStatement(sql);
								pstmt.setString(1, itemCode);
								pstmt.setString(2, siteCode);
								pstmt.setString(3, locCode);
								pstmt.setString(4, lotNo);
								pstmt.setString(5, lotSl);
								//Changed by Rohan on 05-03-12 to get quantity in double
								//pstmt.setInt(6, qty);
								pstmt.setDouble(6, qty);
								rs = pstmt.executeQuery();
								if( rs.next() )
								{
									cnt = rs.getInt(1);
									if(cnt == 0)
									{
										errList.add( "VTQTYA1" );
										errFields.add( childNodeName.toLowerCase() );	
									}
								}
								rs.close();
								rs = null;
								pstmt.close();
								pstmt = null;
							}
						}
					}
				}
			}
			}

			int errListSize = errList.size();
			cnt =0;
			String errFldName = null;
			if ( errList != null && errListSize > 0 )
			{
				for (cnt = 0; cnt < errListSize; cnt++ )
				{
					errCode = (String)errList.get(cnt);
					errFldName = (String)errFields.get(cnt);
					System.out.println("errCode .........."+errCode);
					//String errMsg = hashMap.get(errCode)!=null ? hashMap.get(errCode).toString():"";
					//System.out.println("errMsg .........."+errMsg);
					errString = getErrorString( errFldName, errCode, userId );
					errorType =  errorType( conn , errCode );
					if ( errString.length() > 0)
					{
						String bifurErrString = errString.substring( errString.indexOf("<Errors>") + 8,errString.indexOf("<trace>"));
						bifurErrString =bifurErrString+errString.substring( errString.indexOf("</trace>") + 8,errString.indexOf("</Errors>"));
						errStringXml.append(bifurErrString);
						System.out.println("errStringXml .........."+errStringXml);
						errString = "";
					}
					if ( errorType.equalsIgnoreCase("E"))
					{
						break;
					}
				}
				errList.clear();
				errList = null;
				errFields.clear();
				errFields = null;

				errStringXml.append("</Errors></Root>\r\n");
			}
			else
			{
				errStringXml = new StringBuffer( "" );
			}
			errString = errStringXml.toString();
		}//END TRY
		catch(Exception e)
		{
			System.out.println("Exception in ReplOrderIC  == >");
			e.printStackTrace();
		}
		finally
		{
			try
			{
				if( conn != null && !conn.isClosed() )
				{
					if( rs != null )
					{
						rs.close();
						rs = null;
					}
					if( pstmt != null )
					{
						pstmt.close();
						pstmt = null;
					}
					conn.close();
				}
			}
			catch(Exception e)
			{
				System.out.println( "Exception :ReplOrderIC:wfValData :==>\n"+e.getMessage());
				throw new ITMException(e);
			}
		}
		return errString;
	}
	private String errorType( Connection conn , String errorCode )
	{
		String msgType = "";
		PreparedStatement pstmt = null ; 
		ResultSet rs = null;
		try
		{			
			String  sql = " SELECT MSG_TYPE FROM MESSAGES WHERE MSG_NO =   ? ";

			pstmt = conn.prepareStatement( sql );			
			pstmt.setString(1, errorCode);			
			rs = pstmt.executeQuery();
			while( rs.next() )
			{
				msgType = rs.getString("MSG_TYPE");
			}			
		}
		catch (Exception ex)
		{
			ex.printStackTrace();
		}		
		finally
		{
			try
			{
				if ( rs != null )
				{
					rs.close();
					rs = null;
				}
				if ( pstmt != null )
				{
					pstmt.close();
					pstmt = null;
				}
			}
			catch ( Exception e )
			{
				e.printStackTrace();
			}
		}		
		return msgType;
	}

	public String itemChanged() throws RemoteException, ITMException
	{
		return "";
	}
	public String itemChanged(String xmlString, String xmlString1, String xmlString2, String objContext, String currentColumn, String editFlag, String xtraParams) throws RemoteException, ITMException
	{
		Document dom = null;
		Document dom1 = null;
		Document dom2 = null;
		String errString = null;
		GenericUtility genericUtility = GenericUtility.getInstance();
		try
		{
			if (xmlString != null && xmlString.trim().length()!=0)
			{
				dom = genericUtility.parseString(xmlString); 
			}
			if (xmlString1 != null && xmlString1.trim().length()!=0)
			{
				dom1 = genericUtility.parseString(xmlString1); 
			}
			if (xmlString2 != null && xmlString2.trim().length()!=0)
			{
				dom2 = genericUtility.parseString(xmlString2); 
			}
			errString = itemChanged( dom, dom1, dom2, objContext, currentColumn, editFlag, xtraParams );
			System.out.println ( "ErrString :" + errString);
		}
		catch (Exception e)
		{
			System.out.println ( "Exception :ReplOrderIC :itemChanged(String,String):" + e.getMessage() + ":" );
			errString = genericUtility.createErrorString(e);
		}
		System.out.println ( "returning from ReplOrderIC itemChanged" );
		return errString;
	}
	public String itemChanged( Document dom, Document dom1, Document dom2, String objContext, String currentColumn, String editFlag, String xtraParams ) throws RemoteException,ITMException
	{
		System.out.print("editFlag------------------------>"+editFlag);
		int currentFormNo = 0;
		int childNodeListLength = 0;
		int ctr = 0;
		String siteCode = "";
		String saleOrder = "";
		String siteDescr = "";
		String itemCode = "";
		String itemDescr= "";
		String sql = "";
		String columnValue = "";
		String childNodeName = "";
		String descr = "";
		NodeList parentNodeList = null;
		NodeList childNodeList = null;
		Node parentNode = null;
		Node childNode = null;
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		StringBuffer valueXmlString = new StringBuffer();
		GenericUtility genericUtility = GenericUtility.getInstance();
		try
		{
			columnValue = genericUtility.getColumnValue( currentColumn, dom );
			ConnDriver connDriver = null;
			connDriver = new ConnDriver();
			conn = connDriver.getConnectDB("DriverValidator");
			if( objContext != null && objContext.trim().length() > 0 )
			{
				currentFormNo = Integer.parseInt( objContext );
			}
			valueXmlString = new StringBuffer( "<?xml version=\"1.0\"?>\r\n<Root>\r\n<Header>\r\n<editFlag>" );
			valueXmlString.append( editFlag ).append( "</editFlag>\r\n</Header>\r\n" );

			switch ( currentFormNo )
			{
			case 2:
			{
				parentNodeList = dom.getElementsByTagName("Detail2");
				parentNode = parentNodeList.item(0);
				childNodeList = parentNode.getChildNodes();
				valueXmlString.append("<Detail2>");
				childNodeListLength = childNodeList.getLength();
				do
				{   
					childNode = childNodeList.item(ctr);
					childNodeName = childNode.getNodeName();
					ctr ++;
				}while(ctr < childNodeListLength  && ! childNodeName.equals(currentColumn));

				if (currentColumn.trim().equalsIgnoreCase("itm_default"))
				{

					siteCode = genericUtility.getColumnValue("site_code", dom1);
					saleOrder = genericUtility.getColumnValue("sale_order", dom1);
					siteDescr = genericUtility.getColumnValue("site_descr", dom1);
					System.out.println("in itm_default-------------->"+siteCode+""+saleOrder+"");
					valueXmlString.append("<site_code>").append("<![CDATA[" + siteCode + "]]>").append("</site_code>");
					valueXmlString.append("<site_descr>").append("<![CDATA[" + siteDescr + "]]>").append("</site_descr>");
					valueXmlString.append("<sale_order>").append("<![CDATA[" + saleOrder + "]]>").append("</sale_order>");
				}

				else if( currentColumn.trim().equalsIgnoreCase( "item_code" ) )
				{
					itemCode = genericUtility.getColumnValue("item_code", dom);
					sql = "select descr from item where item_code = ? ";
					pstmt = conn.prepareStatement(sql);
					pstmt.setString(1, itemCode);
					rs = pstmt.executeQuery();
					if( rs.next() )
					{
						itemDescr = checkNull(rs.getString(1));
					}
					rs.close();
					rs = null;
					pstmt.close();
					pstmt = null;
					valueXmlString.append( "<item_descr><![CDATA[" ).append(itemDescr).append( "]]></item_descr>\r\n" );
				}
				valueXmlString.append( "</Detail2>\r\n" );
				System.out.println("------------------------COMPLETE DESCRIPTION-------------------");
				break;
			}
			}
		}
		catch(Exception e)
		{
			System.out.println( "Exception :REPL ORDER :itemChanged(Document,String):" + e.getMessage() + ":" );
			valueXmlString = valueXmlString.append( genericUtility.createErrorString( e ) );
		}
		finally
		{
			try
			{
				if( conn != null && ! conn.isClosed() )
				{
					if( rs != null )
					{
						rs.close();
						rs = null;
					}
					if( pstmt != null )
					{
						pstmt.close();
						pstmt = null;
					}

					conn.close();
				}
				//System.out.println("------------------------INSIDE FINALLY-------------------");
			}
			catch(Exception e)
			{
				System.out.println( "Exception :REPL ORDER:itemChanged :==>\n"+e.getMessage());
				throw new ITMException(e);
			}
		}
		valueXmlString.append( "</Root>\r\n" );	
		System.out.println( "\n****ValueXmlString :" + valueXmlString.toString() + ":********" );
		return valueXmlString.toString();
	}

	private String checkNull( String input )
	{
		if ( input == null )
		{
			input = "";
		}
		return input;
	}
	private String getCurrentUpdateFlag(Node currDetail)
	{
		NodeList currDetailList = null;
		String updateStatus = "",nodeName = "";
		int currDetailListLength = 0;

		currDetailList = currDetail.getChildNodes();
		currDetailListLength = currDetailList.getLength();
		for (int i=0;i< currDetailListLength;i++)
		{
			nodeName = currDetailList.item(i).getNodeName();
			if (nodeName.equalsIgnoreCase("Attribute"))
			{
				updateStatus
				=currDetailList.item(i).getAttributes().getNamedItem("updateFlag").getNodeValue();
				break;
			}
		}
		return updateStatus;
	}

}