
/*
    Window Name : W_file_to_batch_load
*/


package ibase.webitm.ejb.sys; 

import ibase.webitm.ejb.dis.*;
import javax.xml.parsers.*;
import org.w3c.dom.*;
import java.io.*;
import java.rmi.RemoteException;
import java.util.*;
import java.util.Date;
import java.text.SimpleDateFormat; 
import java.sql.*;
import org.w3c.dom.*;
import javax.xml.parsers.*;
import javax.ejb.*;
import javax.naming.InitialContext;
import ibase.webitm.utility.ITMException;
import ibase.webitm.ejb.*;
import ibase.webitm.utility.GenericUtility;
import ibase.webitm.utility.TransIDGenerator;
import ibase.utility.CommonConstants;
import ibase.ejb.*;
import ibase.system.config.*;
import java.util.*;
import java.lang.*; 
import javax.annotation.*;
import javax.ejb.Stateless; // added for ejb3

@Stateless // added for ejb3

public class CreateBatchPrc extends ProcessEJB implements  CreateBatchPrcLocal , CreateBatchPrcRemote // SessionBean
{
	Connection conn = null;
	ITMDBAccessEJB itmDBAccessEJB = null;
	CommonConstants commonConstants;
	GenericUtility genericUtility = GenericUtility.getInstance();
	String userId = "";
	String logInSite = "";
	int tran_sl = 0;
	String win_name= "";
	String batch_id = "";
	String xtraParam = "";
	@PostConstruct
 	public void createCall() throws RemoteException	//, CreateException
	{
		try
		{
			itmDBAccessEJB = new ITMDBAccessEJB();
			ConnDriver conndriver = new ConnDriver();
 			conn = conndriver.getConnectDB("DriverITM");
 			conn.setAutoCommit(false);
 			commonConstants  = new CommonConstants();
			System.out.println("CreateBatchPrcEJB ejbCreate called.........");
		}
		catch (Exception e)
		{
			System.out.println("Exception :CreateBatchPrcEJB :ejbCreate :==>"+e);
			e.printStackTrace();
			//throw new CreateException();
		}
	}
	/*public void ejbRemove()
	{
	}
	public void ejbActivate() 
	{
	}
	public void ejbPassivate() 
	{
	}*/
	public String process() throws RemoteException,ITMException
	{
		return "";
	}

	
	//process()
	public String process(String xmlString, String xmlString2, String windowName, String xtraParams) throws RemoteException,ITMException
	{
		xtraParam = xtraParams;
		String retStr = "";
		Document detailDom = null;
		Document headerDom = null;
		GenericUtility genericUtility = GenericUtility.getInstance();
		try
		{	
			if(xmlString != null && xmlString.trim().length()!=0)
			{
				headerDom = genericUtility.parseString(xmlString); 
			}
			if(xmlString2 != null && xmlString2.trim().length()!=0)
			{
				detailDom = genericUtility.parseString(xmlString2); 
			}
			retStr = process(headerDom, detailDom, windowName, xtraParams);  
		}
		catch (Exception e)
		{
			System.out.println("Exception :CreateBatchPrcEJB :process(String xmlString, String xmlString2, String windowName, String xtraParams):" + e.getMessage() + ":");
			e.printStackTrace();
			throw new ITMException(e);
		}
		return retStr;
	}
	
	public String process(Document headerDom, Document detailDom, String windowName, String xtraParams) throws RemoteException,ITMException
	{
		String childNodeName = "";
		String errCode = null;
		String errString = null;
		int parentNodeListLength = 0;
		int childNodeListLength = 0;
		NodeList parentNodeList = null;
		NodeList childNodeList = null;
		java.io.File file = null;
		Node parentNode = null;
		Node childNode = null;	
		String filePath = "";
		String winName = "";
		xtraParam = xtraParams;
		userId = genericUtility.getValueFromXTRA_PARAMS(xtraParams,"userId");
		logInSite = genericUtility.getValueFromXTRA_PARAMS(xtraParams,"loginSiteCode");
		try
		{
			parentNodeList = headerDom.getElementsByTagName("Detail1");
			parentNodeListLength = parentNodeList.getLength(); 
			System.out.println("ParentNodeListLength:::::::::::::::::::::::"+parentNodeListLength);
			for (int selectedRow = 0; selectedRow < parentNodeListLength; selectedRow++)
			{
				parentNode = parentNodeList.item(selectedRow);
				childNodeList = parentNode.getChildNodes();
				childNodeListLength = childNodeList.getLength();
				System.out.println("ChildNodeListLength:::::::::::::: "+ childNodeListLength);
				for (int childRow = 0; childRow < childNodeListLength; childRow++)
				{
					System.out.println("Inside for loop...........");
					childNode = childNodeList.item(childRow);
					childNodeName = childNode.getNodeName();
					
					if (childNodeName.equals("window_name"))
					{
						winName = (childNode.getFirstChild().getNodeValue()).trim();
						System.out.println("winName::::::::::: "+ winName);	
					}		
					if (childNodeName.equals("file_path"))
					{
						filePath = (childNode.getFirstChild().getNodeValue()).trim();
						System.out.println("file_path:::::::: "+ filePath);
					}
					
				}													
			}
			 
			win_name = winName;
			file = new java.io.File(filePath.trim()); 
			if(file.exists())
			{
				System.out.println("FILE FOUND");
				errString = gfCreateBatch(file,winName);
				System.out.println("errString"+errString);
			}
			else
			{
				System.out.println("File Not Found");
				errCode = "VBFILEOPEN";
				errString =	itmDBAccessEJB.getErrorString("",errCode,"");
				System.out.println("errString"+errString);
			
			}
		
						
		}//try end	
		catch(Exception e)
		{
			System.out.println("Exception :process(String xmlString2, String xmlString2, String windowName, String xtraParams):" + e.getMessage() + ":");
			throw new ITMException(e);
		}
		
		return errString;
	}


//FUNCTION GFCREATEBATCH
	public String gfCreateBatch(java.io.File  filePath,String winName)throws RemoteException,ITMException
	{
		int fildNos = 0;
		int fildNod = 0;
		int objSld = 0;
		int lisubPos = 0;
		int colWidth =0; 
		int store = 0;
		int lineNo = 0;
		int pos = 0;
		int pos1 = 0;
		int pos2 = 0;
		int pos4 =0;
		int liObjs = 0;
		int row = 0;
		int row1 = 0;
		int liObj [] = new int[1000]; 
		int liRecWidth [] = new int[1000]; 
		int brow = 0;
		long  fpos = 0;
		int hdr = 1;
		long  flen = 0;
		String constantVal = "";
		String dateFormat ="";
		String suffix = " ";
		String prefix = "";
		String alignment = "";
		String version = "";
		String identifier = "";
		String fldData= "";
		String totFunc=""; 
		String func = "";
		String retCode= "";
		String entity = "";
		String entitySource ="";
		String entitySourceRef = "";
		String winNameLoad = "";
		String SubjectSstr = "";
		String entityAddnl= "";
		String transer = "";
		String xfrtax = "";
		String statOption = "";
		String edimedium = " ";
		String columnSep = "";
		String nullInter = "";
		String query = " ";
		String sql = " ";
		String errCode = "";
		String batchNo = "";
		String errString = "";
		String sqlbins = "";
		String lsHeader[] = new String[1000]; 
		PreparedStatement spstmt = null;
		PreparedStatement pstmt = null;
		PreparedStatement pstBatchDet = null;
		ResultSet rs = null;
		Statement stmt1 = null;
		ResultSet rs1 = null;
		long start = System.currentTimeMillis();
		System.out.println("Going To Insert in BatchLoad ...................");
		// GETS THE MAXIMUM DATAWINDOW CONTROL PRESENT AND DEFINED IN EDI FOR THE SPECIFIED WINDOW
		query ="SELECT MAX(EDI_CONTROL_DET.OBJ_SL__SOURCE) + 1 FROM EDI_CONTROL_DET,EDI_CONTROL "
				+" WHERE ( EDI_CONTROL_DET.WIN_NAME = ? ) AND "
				+"( EDI_CONTROL.WIN_NAME = EDI_CONTROL_DET.WIN_NAME ) AND "
				+"( EDI_CONTROL.LINE_NO = EDI_CONTROL_DET.LINE_NO ) AND "
				+"( EDI_CONTROL.EDI_MEDIUM = 2 )";
		try
		{
		 	spstmt = conn.prepareStatement(query);
			spstmt.setString(1,winName.trim());
			rs = spstmt.executeQuery(); 
			if(rs.next())
			{
				liObjs = rs.getInt(1);	
			}
			rs.close();
			spstmt.close();
		}
		catch(SQLException ex)
		{
			try{conn.rollback();}catch(Exception ee1){}
			System.out.println("SQLException :[CreateBatchEJB][Excuting Query Failed1]" + query + ex.getMessage());
		}
		catch(Exception ex1)
		{
			try{conn.rollback();}catch(Exception ee2){}
			System.out.println("Exception ::"+query +ex1.getMessage());
			ex1.printStackTrace();
		}
		
		query="SELECT LINE_NO, ENTITY, ENTITY_SOURCE, ENTITY_SOURCE_REF, " 
				+"WIN_NAME__LOAD, SUBJECT_STR, ENTITY_ADDNL, REF_SER, XFR_TAX, STAT_OPTION, "
				+"EDI_MEDIUM,COLUMN_SEPRATOR,INTERPRET_NULL"
				+" FROM EDI_CONTROL WHERE WIN_NAME = ? AND EDI_MEDIUM = 2 ";
		try
		{		
			spstmt = conn.prepareStatement(query);
			spstmt.setString(1,winName);
			rs = spstmt.executeQuery();
			while (rs.next())
    		{ 
				batchNo = generateBatchNo();
				batch_id = batchNo;
				String ins = "";
				//INSERTING A RECORD IN BATCHLOAD
				lineNo = rs.getInt(1);
				entity = rs.getString(2);
				entitySource = rs.getString(3);
				entitySourceRef = rs.getString(4);
				winNameLoad = rs.getString(5);
				SubjectSstr = rs.getString(6);
				entityAddnl = rs.getString(7);
				transer = rs.getString(8);
				xfrtax = rs.getString(9);
				statOption = rs.getString(10);
				edimedium = rs.getString(11);
				columnSep = rs.getString(12);
				nullInter = rs.getString(13);
				/*System.out.println("lineNo"+lineNo);
				System.out.println("entity"+entity);
				System.out.println("entitySource"+entitySource);
				System.out.println("entitySourceRef"+entitySourceRef);
				System.out.println("winNameLoad"+winNameLoad);
				System.out.println("SubjectSstr"+SubjectSstr);
				System.out.println("entityAddnl"+entityAddnl);
				System.out.println("transer"+transer);
				System.out.println("statOption"+xfrtax);
				System.out.println("edimedium"+edimedium);
				System.out.println("columnSep"+columnSep);
				System.out.println("nullInter"+nullInter);*/
				
				//INSERT A ROW IN BATCHLOAD TABLE FOR TRAN_SL = 1
				ins = "INSERT INTO BATCHLOAD ( BATCH_ID,TRAN_SL,WIN_NAME, LOAD_STAT, EDI_ADDR, CHG_DATE, CHG_USER, CHG_TERM, LOAD_MODE ) "
						+"	 VALUES(?, ?,?,?, ?,?, ?,?, ?)"; 
			 	java.sql.Timestamp ts=new java.sql.Timestamp(System.currentTimeMillis());
			 	SimpleDateFormat sdt=new SimpleDateFormat(genericUtility.getDBDateFormat());
				try
				{
					pstmt = conn.prepareStatement(ins);
					pstmt.setString(1,batchNo);
					pstmt.setInt(2,1);
					pstmt.setString(3,winName);
					pstmt.setString(4,"N");
					pstmt.setString(5,"");
					pstmt.setDate(6,java.sql.Date.valueOf(sdt.format(ts)));
					pstmt.setString(7,userId);
					pstmt.setString(8,userId);
					pstmt.setString(9,"0");
					pstmt.executeUpdate();
					pstmt.close();
					System.out.println("Insert Batchload For Header :::::::::::::::::::::::::::::::::::::::::1");
					
				}
				catch(SQLException se2)
				{
					try{conn.rollback();}catch(Exception ee3){}
					System.out.println("SQLException :[CreateBatchEJB][Excuting Query Failed2]" + ins + se2.getMessage());
				}
				catch(Exception ex2)
				{
					try{conn.rollback();}catch(Exception ee6){}
					System.out.println("Exception ::"+ex2.getMessage());
					ex2.printStackTrace();
				}//INSERTING END
				
				// FOR LOOP DEFINED TO GET THE LENGTH OF EACH RECORD IN SPECIFIED DATAWINDOW CONTROL
				for (row = 1 ;row<= liObjs ; row++) 
				{
					try
					{
						//stmt = conn.createStatement();
						int temp = row - 1 ;
						sql = "SELECT EDI_CONTROL_DET.OBJ_SL__SOURCE,SUM(EDI_CONTROL_DET.COL_WIDTH) "
							+"FROM EDI_CONTROL_DET,EDI_CONTROL "
							+" WHERE EDI_CONTROL_DET.WIN_NAME = ?  AND "
							+" ( EDI_CONTROL.WIN_NAME = EDI_CONTROL_DET.WIN_NAME ) AND "
							+" ( EDI_CONTROL.LINE_NO = EDI_CONTROL_DET.LINE_NO ) AND "
							+" ( EDI_CONTROL.EDI_MEDIUM = 2 )   AND OBJ_SL__SOURCE = ?"
							+" GROUP BY OBJ_SL__SOURCE ";
							spstmt = conn.prepareStatement(sql);
							spstmt.setString(1,winName);
							spstmt.setInt(2,temp);
							
							rs1 = spstmt.executeQuery();	
							while(rs1.next())
							{
								liObj [row] = rs1.getInt(1); 
								liRecWidth [row] = rs1.getInt(2); 
							
							}
							rs1.close();
							
					}
					catch(SQLException se4)
					{
						try{conn.rollback();}catch(Exception ee4){}
						System.out.println("SQLException :[CreateBatchEJB][Excuting Query Failed3]" + sql + se4.getMessage());
					}
					catch(Exception ex4)
					{
						try{conn.rollback();}catch(Exception ee7){}
						System.out.println("Exception ::"+ex4.getMessage());
						ex4.printStackTrace();
					}
					
				}//END OF FOR
				
			
				
				// FOR LOOP DEFINED TO HOLD THE IDENTIFIERS OF EACH RECORD OF SPECIFIED DATAWINDOW CONTROL IN AN ARRAY
				for (row = 1; row <= liObjs;row++)
				{
					try
					{
						int temp = row - 1;
						sql = " SELECT CONSTANT_VAL	FROM EDI_CONTROL_DET WHERE"
						+ " WIN_NAME = ? AND FLD_NO__DEST = 0 	AND OBJ_SL__SOURCE =?	AND ROWNUM = 1 "
						+" ORDER BY OBJ_SL__SOURCE,FLD_NO__DEST";
						spstmt = conn.prepareStatement(sql);
						spstmt.setString(1,winName);
						spstmt.setInt(2,temp);
						rs1 = spstmt.executeQuery();	
						while(rs1.next())
						{
							lsHeader [row] = rs1.getString(1); 
							if (lsHeader[row].length() == 0)
							{
								lsHeader[row] = "@@";
							}
			
						}
						rs1.close();
						
					}
					catch(SQLException se5)
					{
					try{conn.rollback();}catch(Exception ee8){}
						System.out.println("SQLException :[CreateBatchEJB][Excuting Query Failed4]" + sql + se5.getMessage());
					}
					catch(Exception ex5)
					{
						conn.rollback();
						System.out.println("Exception ::"+ex5.getMessage());
						ex5.printStackTrace();
					}
			
				}//END FOR
				
				//DO LOOP DEFINED TO CONTINUE TILL THE ENTIRE FILE IS READ
				RandomAccessFile rf= null;
				rf = new RandomAccessFile(filePath,"r");
				flen = rf.length();
				sql ="";
				fpos = 0;
				objSld = 0;
			
				//PREPARING STSTEMENT FOR 
				sqlbins = "INSERT INTO BATCHLOADDET	(BATCH_ID,TRAN_SL,OBJ_SL,OBJ_SSL,FLD_NO,FLD_DATA) "
						+"	 VALUES(?, ?,?,?, ?,?)"; 
				pstBatchDet = conn.prepareStatement(sqlbins);
					while(fpos < flen)
					{
											
						for (row = 1 ;row <=  liObjs;row++)
						{
							// NOTE THAT CONDITION CAN BE CHECKED WITH ONLY THE HEADER (row = 1)
							if (row ==1)
							{
								while(true)
								{
									boolean booln = gfCheckcon(winName,lineNo,fldData = read(fpos,liRecWidth[row],rf));
									if (!(booln ) )
									{
										fpos = fpos + liRecWidth[row];
										System.out.println("calculate done for hdr...........");
									}
									else
									{
										System.out.println("enter else");
										break;
									}
								}
							}
											
							int temp = row - 1;
							sql = "SELECT A.FLD_NO__SOURCE, A.CONSTANT_VAL, A.OBJ_SL__DEST, A.FLD_NO__DEST, A.SUBJECT_POS, "
							+"A.COL_WIDTH, A.DATE_FORMAT, A.SUFFIX, A.PREFIX,  A.ALIGNMENT, A.VERSION, A.TRAN_IDENTIFIER "
							+" FROM EDI_CONTROL_DET A, EDI_CONTROL B WHERE A.WIN_NAME = B.WIN_NAME 	AND A.LINE_NO = B.LINE_NO "
							+"	AND B.WIN_NAME = ?	AND A.OBJ_SL__SOURCE = ?"
							+" AND B.EDI_MEDIUM = 2 ORDER BY A.OBJ_SL__SOURCE,A.FLD_NO__DEST";
							try
							{
								spstmt = conn.prepareStatement(sql);
								spstmt.setString(1,winName);
								spstmt.setInt(2,temp);
								rs1 = spstmt.executeQuery();
								while(rs1.next())
								{
									 fildNos = rs1.getInt(1);
									 constantVal = rs1.getString(2);
									 objSld = rs1.getInt(3);
									 fildNod = rs1.getInt(4);
									 lisubPos = rs1.getInt(5);
									 colWidth = rs1.getInt(6);
									 dateFormat = rs1.getString(7); 
									 suffix = rs1.getString(8); 
									 prefix = rs1.getString(9);
									 alignment = rs1.getString(10);
									 version = rs1.getString(11);
									 identifier = rs1.getString(12);
									 fldData = read(fpos,colWidth,rf);								 
									 fpos = fpos + colWidth;
									 
									 /*System.out.println("version:"+version);
									 System.out.println("fildNos:"+fildNos);
									 System.out.println("constantVal:"+constantVal);
									 System.out.println("objSld:"+objSld);
									 System.out.println("fildNod:"+fildNod);
									 System.out.println("lisubPos:"+lisubPos);
									 System.out.println("colWidth:"+colWidth);
									 System.out.println("dateFormat:"+dateFormat);
									 System.out.println("suffix:"+suffix);
									 System.out.println("prefix:"+prefix);
									 System.out.println("alignment:"+alignment);
									 System.out.println("version:"+version);
									 System.out.println("identifier:"+identifier);
									 System.out.println("fldData:"+fldData);
									 System.out.println("fpos:"+fpos);
									 version = "abc";*/
									 if(version != null)
									 {
									 		System.out.println("VersionCheck:::");
									 	
											if (!(version.trim().equals(fldData.trim())))
											{
												System.out.println("Version::::"+version);
									 			System.out.println("fldData::::"+fldData);
												errCode = "VTEDIVER";
												errString =	itmDBAccessEJB.getErrorString("",errCode,"");
												return errString; 
												
											}
									}
									else if (identifier != null)
									{
								
										if (colWidth ==0 || (fldData.trim().length()==0)) 
										{
											fldData = constantVal.trim();
										}
										if (!(identifier.trim().equals(fldData.trim())))
										{
											System.out.println("identifier::::"+identifier);
									 		System.out.println("fldData::::"+fldData);
											errCode = "VTEDIIDT"; 
											errString =	itmDBAccessEJB.getErrorString("",errCode,"");
											return errString;
			 												
										}
									 
									}
						
									if ((suffix != null) && (suffix.length() > 0))
									{
										fldData = fldData.substring(0,colWidth - suffix.length());
										
									}
									else if(prefix != null && (prefix.length() > 0))
									{
										fldData = fldData.substring(prefix.length(),prefix.length()+colWidth);
									}
									else if (fldData.trim().equals(""))
									{
										fldData = "<NULL>";
									}
									else if (dateFormat != null && (dateFormat.length() > 0))
									{
										
										fldData = fldData.substring(0,0+4)+"-"+fldData.substring(4,4+2)+"-"+fldData.substring(6,6+2);
									}
									
										// INSERT INTO BATCHLOADDET ONLY IF THE SOURCE COLUMN IS NOT ZERO
										if (fildNos!= 0 )
										{
											
									 		//System.out.println("INSERTING INTO ITEM SQL :: "+sqlbins);
							 				try
											{	
												/*System.out.println("BatchNo:"+batchNo);
												System.out.println("TRAN_SL:"+hdr);
												System.out.println("OBJ_SL:"+objSld);
												System.out.println("OBJ_SSL:"+brow);
												System.out.println("FLD_NO:"+fildNos);
												System.out.println("FLD_DATA:"+fldData);*/
												pstBatchDet.setString(1,batchNo);
												pstBatchDet.setInt(2,hdr);
												pstBatchDet.setInt(3,objSld);
												pstBatchDet.setInt(4,brow);
												pstBatchDet.setInt(5,fildNos);
												pstBatchDet.setString(6,fldData);
												pstBatchDet.addBatch();
												//pstBatchDet.executeUpdate();
												System.out.println("SuccessEntryBLD Header("+hdr+")ObjSsl("+brow+")ObjSL("+objSld+")FP("+fpos+")Len("+flen+")"); 
												store = objSld;
										
                     						}
											catch(SQLException se6)
											{
												conn.rollback();
								 				System.out.println("SQLException :[CreateBatchEJB][Excuting Query Failed5]" + sqlbins + se6.getMessage());
											}
											catch(Exception ex6)
											{
												conn.rollback();
												System.out.println("Exception1 ::"+ex6.getMessage());
												ex6.printStackTrace();
											}
										}
									}//END OF WHILE INNER LOOP
									rs1.close(); 
									spstmt.close();
							}
							catch(SQLException se25)
							{
								conn.rollback();
								System.out.println("SQLException :[CreateBatchEJB][Excuting Query Failed27]"+sql );
								 se25.printStackTrace();
							}
							catch(Exception ex25)
							{
								conn.rollback();
								System.out.println("Exception23::"+ex25.getMessage());
								ex25.printStackTrace();
							}
							// FOR LOOP DEFINED TO CONTINUE READ THE NEXT STRING AND TO CHECK IF IT MATCHES WITH ANY SPECIFIED IDENTIFIERS 
				
							for (row1 = 1 ; row1 <=liObjs;row1++)
							{
								int temp11 = row1 - 1;
								String selsql = " SELECT COL_WIDTH,OBJ_SL__DEST 	FROM EDI_CONTROL_DET "
												+" WHERE WIN_NAME = ?	AND OBJ_SL__SOURCE = ?" 
												+" AND FLD_NO__DEST = 0 	AND ROWNUM = 1 	ORDER BY OBJ_SL__SOURCE,FLD_NO__DEST";
								try
								{
									spstmt = conn.prepareStatement(selsql);
									spstmt.setString(1,winName);
									spstmt.setInt(2,temp11);		
									rs1= spstmt.executeQuery();			
									if(rs1.next())
									{
										colWidth = rs1.getInt(1);
										objSld =  rs1.getInt(2);	
									}
									rs1.close();
									spstmt.close();
								}//end try
								catch(SQLException se15)
								{
									conn.rollback();
									System.out.println("SQLException :[CreateBatchEJB][Excuting Query Failed7]"+selsql );
									 se15.printStackTrace();
								}
								catch(Exception ex15)
								{
									conn.rollback();
									System.out.println("Exception3::"+ex15.getMessage());
									ex15.printStackTrace();
								}
								
								
								fldData = read(fpos,colWidth,rf);	
								//System.out.println("lsHeader[row1]:"+lsHeader[row1]);
								//System.out.println("fldData:"+fldData.trim());
								if(fldData.trim().equals(lsHeader[row1].trim()))
								{
									
									row = row1 - 1;
									//INSERT ANOTHER RECORD FOR NEXT HEADER IN BATCHLOAD
									if (row1 ==1)
									{
										hdr = hdr + 1;
										brow = 1;
										String sqlins = " ";
										sqlins = "INSERT INTO BATCHLOAD ( BATCH_ID,TRAN_SL,WIN_NAME, LOAD_STAT, EDI_ADDR, CHG_DATE, CHG_USER, CHG_TERM, LOAD_MODE )"
										+"	VALUES 	( ?,?,?,?,?,?,?, ?,?)"; 
										try
										{
											pstmt = conn.prepareStatement(sqlins);
											pstmt.setString(1,batchNo);
											pstmt.setInt(2,hdr);
											pstmt.setString(3,winName );
											pstmt.setString(4,"N");
											pstmt.setString(5," ");
											pstmt.setTimestamp(6,new java.sql.Timestamp(System.currentTimeMillis()));
											pstmt.setString(7,userId);
											pstmt.setString(8,userId);
											pstmt.setString(9,"0");
											pstmt.executeUpdate();
											pstmt.close();
											System.out.println("Insert Batchload For Header ::::::::::::::::::::::::::::::::::::::::"+hdr);
										}
										catch(SQLException se22)
										{
											conn.rollback();
											System.out.println("SQLException :[CreateBatchEJB][Excuting Query Failed6]" + sqlins + se22.getMessage());
										}
										catch(Exception ex22)
										{
											conn.rollback();
											System.out.println("Exception2 ::"+ex22.getMessage());
											ex22.printStackTrace();
										}//INSERTING END
							
									}
									else
									{
								// IF THE STRING MATCHES WITH THE EXISTING DATAWINDOW CONTROL THEN INCREMENT THE 
								// RECORD READ OF THAT CONTROL ELSE REINITIALISE THE RECORD READ OF THAT CONTROL TO 1.
										if (objSld == store)
										{
											brow = brow + 1;
										}
										else 
										{
											brow = 1;
										}
									}
								}
					 
							 }//end for
						}//endfor
					
			
			   	}//END OF FILE READ
					
	  		}//OUTER END OF WHILE
		  	rs.close();
		  	pstBatchDet.executeBatch(); 
		  	udateBatchLoadNOR(batchNo, winName);
		}//END OF OUTER TRY
		catch(SQLException se12)
		{	try{conn.rollback();}catch(Exception ee11){}
				System.out.println("SQLException :[CreateBatchEJB][Excuting Query Failed9]"  + sql + se12.getMessage());
		}
		catch(Exception ex12)
		{
			try{conn.rollback();}catch(Exception ee9){}
			System.out.println("Exception5::"+ex12.getMessage());
			ex12.printStackTrace();
		}
		
		
		finally
		{
			try
			{
				System.out.println("Closing Connection......");
				conn.commit();
				spstmt.close();
			}catch(SQLException se){}
		}
		System.out.println("Successfully data load in to batchload!!!!!!!!!!!!!!!!!!!!!!!!!");
		  	try
			{
				//String batchNo = "CP114-000000008";
				InitialContext ctx1 = null;
				AppConnectParm appConnect = new AppConnectParm();
				ctx1 = new InitialContext(appConnect.getProperty());
				MasterApplyHome maObjHome = (MasterApplyHome)ctx1.lookup("MasterApply");
				MasterApply maObj = maObjHome.create();
				System.out.println("batchNo::::::::::::::"+ batchNo);
				maObj.applyMasterTable(batchNo,xtraParam);
			}
			catch(Exception exc){	System.out.println(exc.getMessage());}
	
	  	long end = System.currentTimeMillis();
		System.out.println("                         [Loading Details]                   ");
		System.out.println("__________________________________________________");
		System.out.println("Load  in ["+ (end-start) +"+]MilliSecond");
		System.out.println("WinDow name:::"+win_name);
		System.out.println("BatchId name:::"+batch_id);
		System.out.println("Total TranSl(Header):::"+tran_sl);
		System.out.println("___________________________________________________");
		
	return	errString;
}//END OF FUNCTION GFCREATEBATCH

// FUNCTION GF_CHECK_CONDITION
boolean gfCheckcon(String winName,int lineNo,String data)
{
	int colNo = 0;
	int count = 0;
	int pos = 0;
	int fldNod = 0;
	int  colWidth = 0;
	String compValue = "";
	String oper = "";
	String ediMethod = "";
	String totFunc = "";
	String retCode = "";
	String mcoltype = "";
	String constantVal ="";
	String chkValue = "";
	boolean flag;
	flag = true;
	ResultSet lrs = null;
	PreparedStatement pst = null;
	PreparedStatement pst1 = null;
	ResultSet lrs1 = null;
	
	String sql1 = "";
	String sql ="select col_no, comp_operator, comp_value, edi_method 	from edi_control_values a, edi_control b "
				+"where a.win_name = b.win_name 	and a.line_no = b.line_no 	and b.win_name = ? "
				+"and b.line_no =? "
				+"and b.edi_medium = 2 ";
	try
	{
		pst = conn.prepareStatement(sql);
		pst.setString(1,winName);
		pst.setInt(2,lineNo);
		lrs = pst.executeQuery();	
		while(lrs.next())
		{
			colNo = lrs.getInt(1);
			oper = lrs.getString(2);
			compValue =lrs.getString(3);
			ediMethod = lrs.getString(4);
			oper = oper.trim();
	 		compValue = compValue.trim();
	 		if (compValue.trim().length()== 0 )
	 		{
	 			try
	 			{ 			
	 			    compValue = "";
	 				sql1 = "SELECT CONSTANT_VAL, FLD_NO__DEST, COL_WIDTH FROM EDI_CONTROL_DET "
						+"WHERE WIN_NAME = ? "
						+" AND OBJ_SL__SOURCE =0 AND OBJ_SL__DEST = 0 "
						+"ORDER BY OBJ_SL__SOURCE,FLD_NO__DEST";
						pos = 1;
						pst1 = conn.prepareStatement(sql1);
						pst1.setString(1,winName);
						lrs1 = pst1.executeQuery();	
						while(lrs1.next())
						{
							constantVal = lrs1.getString(1);
							fldNod = lrs1.getInt(2);
							colWidth = lrs1.getInt(3);
							chkValue = data.substring(pos,pos+colWidth);
							pos = pos + colWidth;
							chkValue = chkValue;
						}
						pst1.close();
					//System.out.println("Query Successfully execute...............");
				}
				catch(SQLException se1)
				{		
						conn.rollback();
						System.out.println("SQLException :[CreateBatchEJB][Excuting Query Failed10]" + sql + se1.getMessage());
				}
				catch(Exception ex1)
				{
					conn.rollback();
					System.out.println("Exception6 ::"+ex1.getMessage());
					ex1.printStackTrace();
				}
				
				if (ediMethod != null && (ediMethod.trim().length()) > 0 )
				{
					if (retCode.equals("N")) 
					{
						flag = false;
					}
					else
					{
						chkValue = retCode;
					}
				}	
				if (oper.equals("=" ))
				{
					if (compValue.equals(chkValue))
					{
						flag = true;
					}
					else
					{
						flag = false;
					}
				}
				else if (oper.equals(">" )) 
				{ 
					if (compValue.compareTo(chkValue)<0)
					{
						flag = true;
					}
					else
					{
						flag = false;
					}
				}
				else if (oper.equals("<" ))
				{
					if (compValue.compareTo(chkValue)>0) 
					{
						flag = true;
					}
					else
					{
						flag = false;
					}
				}		
				else if (oper.toLowerCase().equals("in"))
				{
				 	if (compValue.indexOf(chkValue) > 0 )
					{
						flag = true;
					}
					else
					{
						flag = false;
					}
				}
				else if (oper.toLowerCase().equals("not in")) 
				{
					if (compValue.indexOf(chkValue) == 0 )
					{
						flag = true;
					}
					else
					{
						flag = false;
					}
				}
			
				else if (oper.equals("<>"))
				{
					if (compValue != chkValue) 
					{
						flag = true;
					}
					else
					{
						flag = false;
					}
				}
			}
		}//end while
		pst.close();
		
	}	
	catch(SQLException se)
	{
		System.out.println("SQLException :[CreateBatchEJB][Excuting Query Failed11]" + sql + se.getMessage());
	}
	catch(Exception ex)
	{
		System.out.println("Exception7::"+ex.getMessage());
		ex.printStackTrace();
	}
	return flag;
}

//READ SPECIFIC NO OF CHARACTER FORM FILE
String read(long l,int len,RandomAccessFile refRf)
{
	String str = "";
	try
	{
		refRf.seek(l);
		for(int i=1;i<= len;i++)
		{
			char c =(char)refRf.readByte();
			str = str + c;
		}
	}
	catch(Exception e){System.out.println("Exception in reading File"+e.getMessage());}
return str;
}

//GENERATING BATCHID
public  String  generateBatchNo()
{
	System.out.println("Generating in batchload!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!");
	Connection conn1 = null;
	Statement lstmt = null;
	ResultSet lrs = null;
	String keyStringQuery = null;
	String tranId = null;
	try
	{ 
	    ConnDriver conndriver = new ConnDriver();
     	conn1 = conndriver.getConnectDB("DriverITM");
    	keyStringQuery = "SELECT KEY_STRING, TRAN_ID_COL, REF_SER FROM TRANSETUP WHERE UPPER(TRAN_WINDOW) = 'I-BL'";
	    System.out.println("keyStringQuery::"+keyStringQuery);
	    lstmt = conn1.createStatement();
		lrs = lstmt.executeQuery(keyStringQuery);
		String tranSer = "";
		String keyString = "";
		String keyCol = "";
		if (lrs.next())
		{
			keyString = lrs.getString(1);
			keyCol = lrs.getString(2);
			tranSer = lrs.getString(3);				
		}
		else
		{
			lstmt.close();
			keyStringQuery = "SELECT KEY_STRING, TRAN_ID_COL, REF_SER FROM TRANSETUP WHERE UPPER(TRAN_WINDOW) = 'GENERAL'";
		    System.out.println("keyStringQuery::"+keyStringQuery);
		    lstmt = conn1.createStatement();
			lrs = lstmt.executeQuery(keyStringQuery);
		
			if (lrs.next())
			{
				keyString = lrs.getString(1);
				keyCol = lrs.getString(2);
				tranSer = lrs.getString(3);				
			}
		}
		System.out.println("keyString :"+keyString);
		System.out.println("keyCol :"+keyCol);
		System.out.println("tranSer :"+tranSer);
		String xmlValues = "";
		xmlValues ="<?xml version=\"1.0\" encoding=\"utf-8\"?><Root>";
		xmlValues = xmlValues + "<Header></Header>";
		xmlValues = xmlValues + "<Detail1>";
		xmlValues = xmlValues +"</Detail1></Root>";
		System.out.println("xmlValues  :["+xmlValues+"]");
		TransIDGenerator tg = new TransIDGenerator(xmlValues, "SUN", commonConstants.DB_NAME);
		tranId = tg.generateTranSeqID(tranSer, keyCol, keyString, conn1);
		System.out.println("tranId ::"+tranId);
		
		}
		catch(SQLException se)
		{				
			System.out.println("SQLException :Generating id[failed] : " + "\n" +se.getMessage());
		}
		catch(Exception ex)
		{
			System.out.println("Exception8:Generating id [failed]:" + "\n" +ex.getMessage());
		}
		finally
		{
			if (lstmt != null)
			{
				
				lstmt = null;
			}
			
		}
	
		return tranId;
}
private void udateBatchLoadNOR(String batchNo,String winName )
{
	PreparedStatement pstmt = null;
	ResultSet rs = null;
	PreparedStatement psUpdateBl = null;
	
	
	
	String sqlTotTranSl = "SELECT COUNT(*)  FROM BATCHLOAD WHERE BATCH_ID = ? AND  WIN_NAME = ?";
	String sqlCountTotDetRec = "SELECT COUNT(*)  FROM BATCHLOADDET WHERE BATCH_ID = ? AND TRAN_SL = ?";
	String	update = "UPDATE  BATCHLOAD SET  NO_OF_RECORDS   = ?  WHERE BATCH_ID =? AND TRAN_SL=? ";	
	
	int totTranSl = 0;
	int totalDetRec = 0;
	
	try
	{
		pstmt = conn.prepareStatement(sqlTotTranSl);
		pstmt.setString(1,batchNo.trim()); 
		pstmt.setString(2,winName.trim()); 
		rs = pstmt.executeQuery(); 
		if(rs.next())
		{
			totTranSl = rs.getInt(1);
		}
		rs.close();
		pstmt.close();
	}
	catch(SQLException se)
	{
		System.out.println("SQLException :[CreateBatchPrcEJB][udateBatchLoad()][Excuting Query Failed(count tot transl from batchload)]" + sqlTotTranSl + se.getMessage());
	}
	catch(Exception ex)
	{
		System.out.println("Exception3[CreateBatchPrcEJB][udateBatchLoad()][Excuting Query Failed(count tot transl from batchload)] ::"+sqlTotTranSl +ex.getMessage());
		ex.printStackTrace();
	}
	System.out.println("TOTALHEADER IN BATCHLOAD ::::"+totTranSl);
	for(int transl =1 ;transl<=totTranSl;transl++)
	{
	  	try
		{
			totalDetRec = 0; 
			pstmt = conn.prepareStatement(sqlCountTotDetRec);
			pstmt.setString(1,batchNo.trim()); 
			pstmt.setInt(2,transl); 
			rs = pstmt.executeQuery(); 
			if(rs.next())
			{
				totalDetRec = rs.getInt(1);
			}
			rs.close();
			pstmt.close();
			
		}
		catch(SQLException se1)
		{
			System.out.println("SQLException :[CreateBatchPrcEJB][udateBatchLoad()][Excuting Query Failed(count det rec)]" + sqlCountTotDetRec + se1.getMessage());
		}
		catch(Exception ex1)
		{
			System.out.println("Exception3[CreateBatchPrcEJB][udateBatchLoad()] ::"+sqlCountTotDetRec +ex1.getMessage());
			ex1.printStackTrace();
		}
		try
		{ 
			psUpdateBl = conn.prepareStatement(update);
			psUpdateBl.setInt(1,totalDetRec);
			psUpdateBl.setString(2,batchNo.trim());
			psUpdateBl.setInt(3,transl);
			psUpdateBl.executeUpdate();
			psUpdateBl.close();
		}
		catch(SQLException seUpdate)
		{
			System.out.println("SQLException(Update Batchload) :[domCreation][Excuting Query Failed ]" + update + seUpdate.getMessage());
		}
		catch(Exception exUpdate)
		{
			System.out.println("Exception3(Update Batchload) ::"+update +exUpdate.getMessage());
			exUpdate.printStackTrace();
		}
	}
}
	




	
}//END OF EJB

	