
package ibase.webitm.utility.wms;
import ibase.scheduler.utility.interfaces.Schedule;
import java.rmi.RemoteException;
import java.util.*;
import java.text.*;
import java.util.Date;
import java.sql.*;
import java.io.*;

import org.omg.CORBA.ORB;
import org.w3c.dom.*;

import java.util.Properties;

import javax.swing.text.NumberFormatter;
import javax.xml.parsers.*;
import javax.ejb.*;
import javax.naming.InitialContext;

import ibase.webitm.utility.ITMException;
import ibase.webitm.ejb.*;
import ibase.webitm.ejb.dis.DistCommon;
import ibase.webitm.ejb.dis.InvAllocTraceBean;
import ibase.webitm.utility.GenericUtility;
import ibase.webitm.utility.TransIDGenerator;

import ibase.utility.BaseException;
import ibase.utility.CommonConstants;
import ibase.utility.UserInfoBean;
import ibase.ejb.*;
import ibase.system.config.*;

import java.text.DateFormat;
import java.text.NumberFormat;
import java.text.SimpleDateFormat;
import java.util.*;
import java.math.*;
import java.net.InetAddress;
import ibase.webitm.utility.ITMException;
import ibase.webitm.utility.TransIDGenerator;
import ibase.webitm.ejb.*;
import ibase.webitm.utility.GenericUtility;
import ibase.ejb.*;
import ibase.system.config.*;
import ibase.utility.*;

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

import java.rmi.RemoteException;
import javax.ejb.*;
import javax.naming.InitialContext;
import java.text.*;
import javax.xml.parsers.*;
import org.w3c.dom.*;
@javax.ejb.Stateless

public class LocationStockUpdate implements Schedule         
{

	//GenericUtility genericUtility = GenericUtility.getInstance();
	//ITMDBAccessEJB itmDBAccessEJB = new ITMDBAccessEJB();
	//ConnDriver connDriver = new ConnDriver();
	//CommonConstants commonConstants = new CommonConstants();
	String chgUser = null;
	String chgTerm = null;
	static long count_records=0;
	boolean isError = false;
	static int Lineno =0;

	public String schedulePriority( String wrkflwPriority )throws Exception
	{
		return "";
	}
	public String schedule(HashMap map)throws Exception
	{
		return "";
	}
	public String schedule(String scheduleParamXML)throws Exception
	{
		String siteCode = "";
		ibase.utility.UserInfoBean userInfo = null;
		try
		{
			System.out.println("************ ["+scheduleParamXML+"]");
			userInfo = new ibase.utility.UserInfoBean( scheduleParamXML );
			siteCode = userInfo.getSiteCode();
			System.out.println("Site code = "+siteCode);
			locationSchdule(siteCode,scheduleParamXML);
		}
		catch(Exception e)
		{
			throw new Exception(e);
		}
		return "";
	}
	public int locationSchdule(String siteCode , String scheduleParamXML) throws RemoteException,ITMException
	{
		
		String sql = "";
		String updatesql = "";
		String locCode = "";
		String locPhyArea = "";
		String locPhyRow = "";
		String locPhyCol = "";
		String locPhysicalStack = "";
		String locPhyStack = ""; 
		String invStat = "";
		int count = 0;
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		GenericUtility genericUtility = GenericUtility.getInstance();
		ITMDBAccessEJB itmDBAccess = new ITMDBAccessEJB();

		try
		{
			ConnDriver connDriver = new ConnDriver(); 
			conn = connDriver.getConnectDB("DriverITM");
			connDriver = null;
			conn.setAutoCommit(false);
			DistCommon distComm = new DistCommon();

			System.out.println(" Calling stock occupency process *************$$$$$$$$**********************");

			sql = "  SELECT LOC_CODE, INV_STAT FROM LOCATION ";
			pstmt = conn.prepareStatement(sql);
			rs=pstmt.executeQuery();
			while(rs.next())
			{
				locCode = checkNull(rs.getString("LOC_CODE"));
				invStat = checkNull(rs.getString("INV_STAT")); 			
				System.out.println("locCode:->"+locCode);
				System.out.println("invStat:->"+invStat);
				
                if(locCode != null && locCode.trim().length() > 0  )
                {
                	locPhyArea = invStat;
                	System.out.println("locPhyArea::::"+locPhyArea);

                	locPhyRow = locCode.substring(locCode.indexOf(locCode), locCode.indexOf(locCode)+3);
                	System.out.println("locPhyrow::::"+locPhyRow);

                	locPhyCol = locCode.substring(locCode.indexOf(locCode)+3, locCode.indexOf(locCode)+5);
                	System.out.println("locPhycol::::"+locPhyCol);

                	locPhyStack = locCode.substring(locCode.indexOf(locCode)+5);
                	System.out.println("locPhystack::::"+locPhyStack);

                	updatesql = " UPDATE LOCATION SET LOC_PHY_AREA = ?, LOC_PHY_ROW = ?, LOC_PHY_COL = ?,LOC_PHY_STACK = ? WHERE LOC_CODE = ? ";
                	pstmt = conn.prepareStatement(updatesql);					
                	pstmt.setString(1,invStat.trim());
                	pstmt.setString(2,locPhyRow.trim());
                	pstmt.setString(3,locPhyCol.trim());
                	pstmt.setString(4,locPhyStack.trim());
                	pstmt.setString(5,locCode);
                	count = pstmt.executeUpdate();
                	if( count > 0)
                	{
                		System.out.println("update count:::"+count) ;
                	}
                	pstmt.close();
                	pstmt = null; 
               }
			}
		}
		catch(Exception e)
		{
			isError = true;
			try 
			{
				conn.rollback();
			}
			catch (SQLException e1) 
			{
				e1.printStackTrace();
			}
			System.out.println("******Exception"+e.getMessage());
		}
		finally
		{

			try
			{
				if(!isError)
				{						
					conn.commit();					
				}
				else
				{
					conn.rollback();
				}
				if(conn != null)
				{
					if(pstmt!=null)
					{
						pstmt.close();
						pstmt=null;
					}
					if(rs!=null)
					{
						rs.close();
						rs=null;
					}
					conn.commit();
					conn.close();
				}
			}
			catch(Exception ex)
			{
				isError = true;
				System.out.println("Exception is "+ex.getMessage());
			}
		}
		return count;
	} //verify vendor schdule()

	private String checkNull(String input)
	{
		if (input==null)
		{
			input="";
		}
		return input;
	}
}







