package ibase.webitm.ejb.dis;

import java.sql.*;
import ibase.webitm.ejb.*;
import ibase.webitm.utility.*;
import ibase.utility.CommonConstants;
import ibase.webitm.utility.GenericUtility;
import ibase.webitm.utility.TransIDGenerator;
import java.util.HashMap;
import java.io.*;

public class InvAllocTraceBean
{
	FileWriter f = null;
	public String updateInvallocTrace(HashMap invallocTraceMap, Connection conn) throws ITMException,Exception
	{
		try
		{
			f = new FileWriter("C:\\invalloc.log", true); 
		}
		catch(Exception e){System.out.println(e.getMessage());
		writeException(e);}
		String errString = "";
		String errCode = "" ;
		String sql = null ;
		String sqlUpdate = null ;
		String keyStringQuery = null ;
		CommonConstants commonConstants = new CommonConstants();
		String refSer = (String)invallocTraceMap.get("ref_ser");
		String refId = (String)invallocTraceMap.get("ref_id");
		String refLine = (String)invallocTraceMap.get("ref_line");
		String siteCode = (String)invallocTraceMap.get("site_code");
		String itemCode = (String)invallocTraceMap.get("item_code");
		String locCode = (String)invallocTraceMap.get("loc_code");
		String lotNo = (String)invallocTraceMap.get("lot_no");
		String lotSl = (String)invallocTraceMap.get("lot_sl");
		double allocQty = ((Double)invallocTraceMap.get("alloc_qty")).doubleValue();
		String chgUser = (String)invallocTraceMap.get("chg_user");
	    String chgTerm = (String)invallocTraceMap.get("chg_term");
	    String chgWin = (String)invallocTraceMap.get("chg_win");
	    if(lotNo == null)
		lotNo = " ";
		if(lotSl == null)
		lotSl = " ";
	    
		java.sql.Date chgDate = new java.sql.Date(System.currentTimeMillis());
		java.sql.Date tranDate = new java.sql.Date(System.currentTimeMillis());
		PreparedStatement pstmt = null ;
		ResultSet rSet = null ;
		Statement stmt = null ;
		String tranId = null ;
	   
	    try
	    {
	    	keyStringQuery = "SELECT KEY_STRING, TRAN_ID_COL, REF_SER FROM TRANSETUP WHERE UPPER(TRAN_WINDOW) = 'T_ALLOCTRACE'";
	    	System.out.println("keyStringQuery--------->>"+keyStringQuery);
	 
			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);				
			}
			rSet.close();
			stmt.close();
			rSet = null;
			stmt = null;
			f.write(keyString + " " + keyCol + " " + tranSer1+ "\r\n");
			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>";
			xmlValues = xmlValues +	"<tran_id></tran_id>";
			xmlValues = xmlValues + "<site_code>" + siteCode + "</site_code>";
			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);
			f.write("trace tran id ... " +tranId + "\r\n");
			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("siteCodeShip="+ siteCode);
			System.out.println("locCode="+ locCode);
			System.out.println("lotNo="+ lotNo);
			System.out.println("LOT_SL="+ lotSl);
			System.out.println("ALLOC_QTY="+ allocQty);
			System.out.println("chgWin="+ chgWin);
			System.out.println("chgUser="+ chgUser);
			System.out.println("chgTerm="+ chgTerm);
			System.out.println("chgDate="+ chgDate);
			f.write("trans id="+ tranId+"\n");
			f.write("tranDate="+ tranDate+"\n");
			f.write("refSer="+ refSer+"\n");
			f.write("refId="+ refId+"\n");
			f.write("refLine="+ refLine+"\n");
			f.write("itemCode="+ itemCode+"\n");
			f.write("locCode="+ locCode+"\n");
			f.write("lotNo="+ lotNo+"\n");
			f.write("lotSl="+ lotSl+"\n");
			f.write("ALLOC_QTY="+ allocQty+"\n");
			
			
		
			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("Allocate trace Updated...............................");
		
			sqlUpdate = "UPDATE STOCK SET ALLOC_QTY =(CASE WHEN ALLOC_QTY IS NULL THEN 0 ELSE ALLOC_QTY END) + ? "
					  +"WHERE ITEM_CODE = '"+itemCode+"' AND SITE_CODE = '"+ siteCode+"' AND LOC_CODE ='"+locCode+"'  AND LOT_NO = '"+lotNo+"' AND LOT_SL = '"+lotSl+"'";
			pstmt = conn.prepareStatement(sqlUpdate);
			pstmt.setDouble(1,allocQty) ;
			//pstmt.setString(2,itemCode.trim()) ;
			//pstmt.setString(3, siteCode.trim()) ;
			///pstmt.setString(4,locCode.trim()) ;
			//pstmt.setString(5,lotNo.trim()) ;
			//pstmt.setString(6,lotSl.trim()) ; 
			
//			if(allocQty >= 0)  // Changed by Alka 14/03/2007 allocQty had to be updated in stock for any case.
//			{
				System.out.println("sqlUpdate "+ sqlUpdate);
				int i = pstmt.executeUpdate() ;
				System.out.println("Records updated "+ i);
				if(i>0)
				{
					f.write("sqlUpdate="+ sqlUpdate+"\n"+"Stock update success fully........................."+i+"\n");
				}
				else
				{
					throw new ITMException(new Exception("Unable to update stock for [" + sqlUpdate + "]"));
				}
					
//			}
			System.out.println("Updated End.") ;
			f.write("Updated End..........");
		}
		catch(SQLException e)
		{
			f.write("TRANSATION ROLL BACK IN updateInvallocTrace"+"\n");
			try{conn.rollback();}catch(Exception ee2){}
			writeException(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)
		{
			writeException(e);
			System.out.println("Exception :updateInvallocTrace :"  + sqlUpdate + "\n" +e.getMessage());
			errString = e.getMessage();
			e.printStackTrace();
			try
			{
				f.write("TRANSATION ROLL BACK IN updateInvallocTrace"+"\n");
				try{conn.rollback();}catch(Exception ee2){}
			
			}
			catch(Exception e1)
			{
				writeException(e1);
				errString = e1.getMessage();
				e = e1;
				
			}
			return errString;	
		}
		finally
		{
			try
			{
				f.close();
				if(conn != null)
				{	
					if(pstmt != null)
					{
						pstmt.close();
						pstmt = null;
					}
					if(stmt != null)
					{
						stmt.close();
						stmt = null;
					}
				//	conn.close();
				//	conn = null;
				}
			}
			catch(Exception e)
			{
				try{conn.rollback();f.write("TRANSATION ROLL BACK IN updateInvallocTrace"+"\n");}catch(Exception ee2){}
				errString = e.getMessage(); 
				e.printStackTrace();
				writeException(e);
				return errString;	
			}
			return errString;	
		}
		
	}
	private void writeException(Exception e) throws Exception
	{
		try
		{
			f.write(e.getMessage() + "\r\n");
			PrintStream t = new PrintStream(new FileOutputStream(new File("C:\\invalloc.log"),true));
			e.printStackTrace(t);	
		}	
		catch(Exception t){throw t;}
	}
}