package ibase.webitm.ejb.mfg;


import java.util.*;
import java.sql.*;


import org.w3c.dom.*;

import java.text.SimpleDateFormat;

import ibase.system.config.ConnDriver;
import ibase.utility.CommonConstants;

import ibase.webitm.ejb.*;
import ibase.webitm.ejb.dis.InvAllocTrace;
import ibase.webitm.ejb.dis.StockAllocationPrc;
import ibase.webitm.ejb.dis.StockAllocationPrc.*;
import ibase.webitm.ejb.dis.StockUpdate;
import ibase.webitm.utility.ITMException;
import ibase.webitm.utility.GenericUtility;

import ibase.webitm.utility.TransIDGenerator;

public class MrpWizPrsSaveBean 
{

	
	public String preSaveForm(String xmlString1 )throws ITMException
	{
		Document dom = null;
		System.out.println("@@@@@@@@@---------MrpWizPrsSaveBean EJB called...");
		try
		{
			System.out.println("@@@@@@@xmlString1["+xmlString1+"]@@@@@@@");
			if (xmlString1 != null && xmlString1.trim().length() > 0)
			{
				dom = GenericUtility.getInstance().parseString(xmlString1);
				executepreSaveForm(dom);
			}			
		}
		catch(Exception e)
		{
			System.out.println("Exception :MrpWizPrsSaveBean ::" + e.getMessage() + ":");
			e.printStackTrace();
			throw new ITMException(e);
		}
		return "";
	}

	private String executepreSaveForm(Document dom )throws ITMException
	{
		PreparedStatement pstmt = null;
		String sql = "", itemCode = "", effDate = "", itemSer = "";
		int updCnt = 0;
		//java.util.Date effDate1 = null, preDate = null;
		GenericUtility genericUtility = GenericUtility.getInstance();
		NodeList hdrDom = null;
		//Node currDetail = null;

		String saleOrder="",lineNo="", siteCode="", lotNo="", locCode="", lotSl="", tranSer="", runningSupplyStr="", qtyReqdStr="",allocQtyStr="";
		double runningSupply=0,qtyReqd=0;
		String tranId="",refId="",refLineNo="",unit="",errString="";
		double quantity=0,qtyAllocated=0,totAllocQty=0,allocQty=0;
		int cnt=0;
		Connection conn = null;
		ConnDriver connDriver = new ConnDriver(); 
		String supplyStr="",availableSupplyStr="";
		
		InvAllocTrace invallocTrace = new InvAllocTrace();
		StockAllocationPrc stockAllocationPrc = new StockAllocationPrc()  ;
		
		
		try
		{
			conn = connDriver.getConnectDB("DriverITM");
			
			NodeList hdrDommList = dom.getElementsByTagName("Detail4");
			System.out.println("len===["+hdrDommList.getLength()+"]");

			for (int dtlCtr = 0; dtlCtr < hdrDommList.getLength(); dtlCtr++)
			{
				Node detailListNode = hdrDommList.item(dtlCtr);
				NodeList detail4List= detailListNode.getChildNodes(); 
				System.out.println("@@@@@@@@@node name[" + detailListNode.getNodeName()+"]");

				if("Detail4".equalsIgnoreCase(detailListNode.getNodeName()))
				{
					System.out.println("@@@@inside detail4----------------");
					for (int cntr = 0; cntr < detail4List.getLength(); cntr++) 
					{
						Node	detail4Node = detail4List.item(cntr); 
						NodeList detail4NodeList = detail4Node.getChildNodes(); 
						System.out.println("@@@@@@@@@refrenceNode ["+detail4NodeList.getLength()+"]node name[" + detail4Node.getNodeName()+"]"); 

						if("sale_order".equalsIgnoreCase( detail4Node.getNodeName()))
						{
							saleOrder =  detail4List.item(cntr).getFirstChild().getNodeValue();
						}
						if("line_no".equalsIgnoreCase( detail4Node.getNodeName()))
						{
							lineNo = detail4List.item(cntr).getFirstChild().getNodeValue();
						}
						if("site_code".equalsIgnoreCase( detail4Node.getNodeName()))
						{
							siteCode =  detail4List.item(cntr).getFirstChild().getNodeValue();
						}
						if("item_code".equalsIgnoreCase( detail4Node.getNodeName()))
						{
							itemCode = detail4List.item(cntr).getFirstChild().getNodeValue();
						}
						if("lot_no".equalsIgnoreCase( detail4Node.getNodeName()))
						{
							lotNo =  detail4List.item(cntr).getFirstChild().getNodeValue();
						}
						if("lot_sl".equalsIgnoreCase( detail4Node.getNodeName()))
						{
							lotSl = detail4List.item(cntr).getFirstChild().getNodeValue();
						}
						if("loc_code".equalsIgnoreCase( detail4Node.getNodeName()))
						{
							locCode =  detail4List.item(cntr).getFirstChild().getNodeValue();
						}
						if("tran_ser".equalsIgnoreCase( detail4Node.getNodeName()))
						{
							tranSer = detail4List.item(cntr).getFirstChild().getNodeValue();
						}
						if("running_supply".equalsIgnoreCase( detail4Node.getNodeName()))
						{
							runningSupplyStr =  detail4List.item(cntr).getFirstChild().getNodeValue();
						}
						if("qty_reqd".equalsIgnoreCase( detail4Node.getNodeName()))
						{
							qtyReqdStr = detail4List.item(cntr).getFirstChild().getNodeValue();
						}
						if("alloc_qty".equalsIgnoreCase( detail4Node.getNodeName()))
						{
							allocQtyStr = detail4List.item(cntr).getFirstChild().getNodeValue();
						}
						if("supply".equalsIgnoreCase( detail4Node.getNodeName()))
						{
							supplyStr = detail4List.item(cntr).getFirstChild().getNodeValue();   
						}
						if("available_supply".equalsIgnoreCase( detail4Node.getNodeName()))
						{
							availableSupplyStr = detail4List.item(cntr).getFirstChild().getNodeValue();
						}

					}
				}		
				System.out.println("@@@@ctr["+dtlCtr+"]saleOrder["+saleOrder+"]-lineNo["+lineNo+"]-qtyReqd["+qtyReqdStr+"]@@@");
				System.out.println("@@@@ctr["+dtlCtr+"]siteCode["+siteCode+"]-itemCode["+itemCode+"]-lotNo["+lotNo+"]-lotSl["+lotSl+"]-locCode["+locCode+"]-tranSer["+tranSer+"]allocQtyStr["+allocQtyStr+"]@@@");
				
				if( runningSupplyStr.trim().length() > 0)
				{
					runningSupply = runningSupplyStr==null?0:Double.parseDouble(runningSupplyStr);
				}

				if( qtyReqdStr.trim().length() > 0)
				{
					qtyReqd = qtyReqdStr==null?0:Double.parseDouble(qtyReqdStr);
				}
				if( allocQtyStr.trim().length() > 0)
				{
					allocQty = allocQtyStr==null?0:Double.parseDouble(allocQtyStr);
				}
				
				/*
				HashMap stkUpdMap = new HashMap();
				
				stkUpdMap.put("sorder_no", saleOrder);
				stkUpdMap.put("site_code", siteCode);
				stkUpdMap.put("item_code", itemCode);
				stkUpdMap.put("lot_no", lotNo);
				stkUpdMap.put("lot_sl", lotSl);
				stkUpdMap.put("loc_code", locCode);
				stkUpdMap.put("tran_ser", tranSer);
				stkUpdMap.put("line_no", lineNo);
				stkUpdMap.put("quantity", allocQty);
				*/
				
				invallocTrace.setRefSer("MRPWIZ");
				invallocTrace.setRefId(saleOrder);
				invallocTrace.setRefLine(lineNo);
				invallocTrace.setSiteCode(siteCode);
				invallocTrace.setItemCode(itemCode);
				invallocTrace.setLocCode(locCode);
				invallocTrace.setLotNo(lotNo);
				invallocTrace.setLotSl(lotSl);
				invallocTrace.setAllocQty(allocQty);
				//invallocTrace.setChgUser(chgUser);
				//invallocTrace.setChgTerm(chgTerm);
				//invallocTrace.setChgWin("W_SORDALLOC" );
				
				errString = stockAllocationPrc.updateInvallocTrace(invallocTrace, conn);
				
				if (errString != null && errString.trim().length() > 0)
				{
					System.out.println("@@@@@@rolledback errString :::"+ errString );
					conn.rollback();
					return errString;
				}
				

			}//end if
		}//try end
		catch (SQLException sqx)
		{
			System.out.println("The SQLException occurs in MrpWizPrsSaveBean :"+sqx);
			sqx.printStackTrace();
			throw new ITMException(sqx);
		}
		catch(Exception e)
		{
			System.out.println("The SQLException occurs in MrpWizPrsSaveBean  :"+e);			
			e.printStackTrace();
			throw new ITMException(e);
		}
		finally 
		{
			try
			{
				
				if (errString == null || errString.trim().length() == 0)
				{
					System.out.println("@@@@@@commit errString :::"+ errString );
					conn.commit();
					//return errString;
				}
				
				if (pstmt!= null)
				{
					pstmt = null;
				}
				if( conn != null )
				{
					conn.close();
					conn=null;
				}
			}
			catch(Exception e)
			{
				System.out.println("The SQLException occurs in MrpWizPrsSaveBean  :"+e);			
				e.printStackTrace();
				throw new ITMException(e);
			}
		}
		return "";
	}

/*
	private String generateTranId( String windowName, String siteCode, Connection conn )throws ITMException
	{
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String selSql = "";
		String tranId = "";
		String tranSer = "";
		String keyString = "";
		String keyCol = "";
		String xmlValues = "";
		java.sql.Timestamp currDate = null;
		GenericUtility genericUtility = GenericUtility.getInstance();

		try
		{

			SimpleDateFormat sdfAppl = new SimpleDateFormat(genericUtility.getApplDateFormat());

			currDate = new java.sql.Timestamp(System.currentTimeMillis());
			String currDateStr = sdfAppl.format(currDate);

			selSql = "SELECT KEY_STRING, TRAN_ID_COL, REF_SER FROM TRANSETUP WHERE TRAN_WINDOW = ? ";  // w_mrp_allocate
			pstmt = conn.prepareStatement(selSql);
			pstmt.setString( 1, windowName );
			rs = pstmt.executeQuery();
			if (rs.next())
			{
				keyString = rs.getString("KEY_STRING");
				keyCol = rs.getString("TRAN_ID_COL");
				tranSer = rs.getString("REF_SER");
			}
			rs.close();rs = null;
			pstmt.close();pstmt = null;    
			System.out.println("keyString :"+keyString);
			System.out.println("keyCol :"+keyCol);
			System.out.println("tranSer :"+tranSer);

			xmlValues ="<?xml version=\"1.0\" encoding=\"utf-8\"?><Root>";
			xmlValues = xmlValues + "<Header></Header>";
			xmlValues = xmlValues + "<Detail1>";
			xmlValues = xmlValues +        "<tran_id></tran_id>";
			xmlValues = xmlValues +        "<site_code>" + siteCode + "</site_code>";
			xmlValues = xmlValues +        "<tran_date>" + currDateStr + "</tran_date>";
			xmlValues = xmlValues + "</Detail1></Root>";
			System.out.println("xmlValues  :["+xmlValues+"]");
			TransIDGenerator tg = new TransIDGenerator(xmlValues, "BASE", CommonConstants.DB_NAME);
			tranId = tg.generateTranSeqID(tranSer, keyCol, keyString, conn);
			System.out.println("tranId :"+tranId);
		}
		catch (SQLException ex)
		{
			System.out.println("Exception ::" +selSql+ ex.getMessage() + ":");
			ex.printStackTrace();
			throw new ITMException(ex);
		}
		catch (Exception e)
		{
			System.out.println("Exception ::" + e.getMessage() + ":");
			e.printStackTrace();
			throw new ITMException(e);
		}
		finally
		{
			try
			{
				if (rs != null)
				{
					rs.close();
					rs = null;
				}
				if (pstmt != null)
				{
					pstmt.close();
					pstmt = null;
				}
			}
			catch(Exception e){}
		}
		return tranId;
	}//generateTranTd()

*/

	
	/*
	private String updateInvallocTrace(InvAllocTrace invallocTrace, Connection conn) throws ITMException
	{
		String errString = "";
		String errCode = "" ;
		String sql = null;
		String sqlUpdate = null;
		String keyStringQuery = null;
		java.sql.Date tranDate = invallocTrace.getTranDate();
		String refSer = invallocTrace.getRefSer();
		String refId = invallocTrace.getRefId();
		String refLine = invallocTrace.getRefLine();
		String siteCode = invallocTrace.getSiteCode();
		String itemCode = invallocTrace.getItemCode();
		String locCode = invallocTrace.getLocCode();
		String lotNo = invallocTrace.getLotNo();
		String lotSl = invallocTrace.getLotSl();
		double allocQty = invallocTrace.getAllocQty();
		String chgUser = invallocTrace.getChgUser();
		String chgTerm = invallocTrace.getChgTerm();
		String chgWin = invallocTrace.getChgWin();
		java.sql.Date chgDate = new java.sql.Date(System.currentTimeMillis());
		//Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rSet = null ;
		Statement stmt = null;
		String tranId = null;
		GenericUtility genericUtility = GenericUtility.getInstance();
		ITMDBAccessEJB itmDBAccessEJB = new ITMDBAccessEJB();
		
		try
		{
			keyStringQuery = "SELECT KEY_STRING, TRAN_ID_COL, REF_SER FROM TRANSETUP WHERE UPPER(TRAN_WINDOW) = 'T_ALLOCTRACE'";
			System.out.println("keyStringQuery--------->>"+keyStringQuery);
			//ConnDriver connDriver = new ConnDriver();
			//conn = connDriver.getConnectDB("DriverITM");
			//connDriver = null;
			stmt = conn.createStatement();
			rSet = stmt.executeQuery(keyStringQuery);
			System.out.println("keyString :"+rSet.toString());
			String tranSer1 = "";
			String keyString = "";
			String keyCol = "";
			if (rSet.next())
			{
				keyString = rSet.getString(1);
				keyCol = rSet.getString(2);
				tranSer1 = rSet.getString(3);				
			}

			java.util.Date date = null;
			Timestamp timestamp = new Timestamp(System.currentTimeMillis());
			System.out.println(genericUtility.getDBDateFormat());

			SimpleDateFormat simpledateformat = new SimpleDateFormat(genericUtility.getDBDateFormat());
			date = simpledateformat.parse(timestamp.toString());
			timestamp = Timestamp.valueOf(simpledateformat.format(date).toString() + " 00:00:00.0");
			String tranDateStr = (new SimpleDateFormat(genericUtility.getApplDateFormat())).format(timestamp).toString();
			System.out.println("keyString :"+keyString);
			System.out.println("keyCol :"+keyCol);
			System.out.println("tranSer1 :"+tranSer1);

			String xmlValues = "";
			xmlValues ="<?xml version=\"1.0\" encoding=\"utf-8\"?><Root>";
			xmlValues = xmlValues + "<Header></Header>";
			xmlValues = xmlValues + "<Detail1>";
			//Added by Manoj dtd 10/09/2012 to generate key
			xmlValues = xmlValues + "<dummy>D</dummy>";
			xmlValues = xmlValues + "<site_code>" + siteCode + "</site_code>";
			//Added by Manoj dtd 10/09/2012 to generate key
			xmlValues = xmlValues + "<tran_date>" + tranDateStr + "</tran_date>";
			xmlValues = xmlValues +"</Detail1></Root>";
			System.out.println("xmlValues  :["+xmlValues+"]");
			TransIDGenerator tg = new TransIDGenerator(xmlValues, "BASE", commonConstants.DB_NAME);
			tranId = tg.generateTranSeqID(tranSer1, keyCol, keyString, conn);
			System.out.println("tranId :"+tranId);

			sql = "INSERT INTO INVALLOC_TRACE (TRAN_ID, TRAN_DATE, REF_SER, REF_ID,"
					+"REF_LINE,ITEM_CODE, SITE_CODE, LOC_CODE,LOT_NO, LOT_SL, ALLOC_QTY, CHG_WIN," 
					+"CHG_USER, CHG_TERM, CHG_DATE )VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";

			pstmt = conn.prepareStatement(sql);

			System.out.println("trans id="+tranId);
			System.out.println("tranDate="+tranDate);
			System.out.println("refSer="+refSer);
			System.out.println("refId="+refId);
			System.out.println("refLine="+refLine);
			System.out.println("itemCode="+itemCode);
			System.out.println("siteCode="+siteCode);
			System.out.println("locCode="+locCode);
			System.out.println("lotNo="+lotNo);
			System.out.println("LOT_SL="+lotSl);
			System.out.println("LLOC_QTY="+allocQty);
			System.out.println("chgWin="+chgWin);
			System.out.println("chgUser="+chgUser);
			System.out.println("chgTerm="+chgTerm);
			System.out.println("chgDate="+chgDate);

			pstmt.setString(1,tranId);
			pstmt.setDate(2,tranDate);
			pstmt.setString(3,refSer);
			pstmt.setString(4,refId);
			pstmt.setString(5,refLine);
			pstmt.setString(6,itemCode );
			pstmt.setString(7,siteCode);
			pstmt.setString(8,locCode);
			pstmt.setString(9,lotNo );
			pstmt.setString(10,lotSl);
			pstmt.setDouble(11,allocQty);
			pstmt.setString(12,chgWin);
			pstmt.setString(13,chgUser);
			pstmt.setString(14,chgTerm);
			pstmt.setDate(15,chgDate);
			pstmt.executeUpdate();
			System.out.println("insertion of sql inside updateInvallocTrace success on date "+ chgDate);

			System.out.println("Stock Updated...............................");
			if(allocQty >= 0)
			{

				sqlUpdate = "UPDATE STOCK SET ALLOC_QTY =(CASE WHEN ALLOC_QTY IS NULL THEN 0 ELSE ALLOC_QTY END) + ? "
						+"WHERE ITEM_CODE = ? AND SITE_CODE = ? AND LOC_CODE = ? AND LOT_NO = ? AND LOT_SL = ?";
				pstmt = conn.prepareStatement(sqlUpdate);
				pstmt.setDouble(1,allocQty);
				pstmt.setString(2,itemCode);
				pstmt.setString(3, siteCode);
				pstmt.setString(4,locCode);
				pstmt.setString(5,lotNo);
				pstmt.setString(6,lotSl);
				pstmt.executeUpdate();
			}
			System.out.println("Updated End.");
		}
		catch(SQLException e)
		{
			System.out.println("SQLException :updateInvallocTrace : "  + sqlUpdate + "\n" +e.getMessage());
			System.out.println("ALLOC_QTY : " + allocQty);
			System.out.println("ITEM_CODE : " + itemCode);
			System.out.println("SITE_CODE : " + siteCode);
			System.out.println("LOC_CODE : " + locCode);
			System.out.println("LOT_NO : " + lotNo);
			System.out.println("LOT_SL : " + lotSl);
			errString = e.getMessage();
			e.printStackTrace();
			return errString;

		}
		catch(Exception e)
		{
			System.out.println("manohar :updateInvallocTrace connection rollback:"  + sqlUpdate + "\n" +e.getMessage());
			errString = e.getMessage();
			e.printStackTrace();
			try
			{
				conn.rollback();
			}
			catch(Exception e1)
			{
				e = e1;
			}
			return errString;	

		}
		finally
		{
			try
			{
				//if(conn != null)
				//{	
				if(pstmt != null)
				{
					pstmt.close();
					pstmt = null;
				}
				if(stmt != null)
				{
					stmt.close();
					stmt = null;
				}
				//conn.close();
				//conn = null;
				//}
			}
			catch(Exception e)
			{
				errString = e.getMessage(); 
				e.printStackTrace();
				return errString;	
			}
			return errString;	
		}

	}
	
	*/
	
	
	
	
	
	
	
	
}