package ibase.webitm.ejb.sys;

import ibase.webitm.utility.*;
import ibase.webitm.ejb.*;
import ibase.webitm.bean.*;
import ibase.utility.CommonConstants;
import ibase.system.config.*;
import ibase.utility.UserInfoBean;
import ibase.scheduler.utility.interfaces.Schedule;
import java.util.*;
import java.rmi.*;
import javax.ejb.*;
import javax.rmi.*;
import java.io.File;
import java.io.FileWriter;
import java.io.*;
import java.lang.String;

import org.apache.log4j.*;
import org.w3c.dom.*;

public class DownloadCSVBatchBean  implements Schedule
{
	protected ibase.utility.UserInfoBean userInfo = null;
		
	public void downloadTransaction( String objName, String downloadLoc, String entityCode, String entityType, String siteCodeDnld, String custCodeDnld ) throws RemoteException, ITMException
	{
		//String entityType = null;
		//String entityCode = null;
		String templUpld = null;
		String templTypeUpld = null;
		String templDnld = null;
		String templTypeDnld = null;
		String objEximpFmt = null;
		
		TranXmlData client = null;
		String dnldFileName = null;
		String xmlToCsvStr = null;
		
		HashMap tranFileMip = new HashMap();
		// 11/09/09 manoharan DI89SUN223
		HashMap tranSeqMap = new HashMap();
		// end 11/09/09 manoharan
		
		String tranId = null;
		String custCode = null;
		String uniformCode = null;
		String dt = null;
		String prvCustCode = null;
		String previousDay = null;
		int recCounter = 0;
		
		String fileName = null;
								
		boolean isTemplateExist = false;
				
		siteCodeDnld = ( siteCodeDnld != null && siteCodeDnld.equalsIgnoreCase( "null" ) ) ? "" : siteCodeDnld.trim();
		custCodeDnld = ( custCodeDnld != null && custCodeDnld.equalsIgnoreCase( "null" ) ) ? "" : custCodeDnld.trim();
		
		//entityCode = userInfo.getEntityCode();
		//entityType = userInfo.getUserType();
		
		//entityType = "E";
		//entityCode = "E03498";
		String sql = null;
		//objName = "invoice";
		//downloadLoc = "G:\\rcp\\JBoss-2.4.4\\DOWNLOAD\\daily"; 
		sql = " select OBJ_NAME, TEMPL_UPLD, TEMPL_TYPE_UPLD, TEMPL_DNLD, TEMPL_TYPE_DNLD"
			+" from OBJECT_EXIM_FMT "
			+" where OBJ_NAME  = '" + objName + "'"
			+"	and OBJ_CONTEXT = '" + entityType.trim() + "-" + entityCode.trim() + "'";
		//System.out.println( "SQL :: " + sql );
		java.sql.Connection conn = null;
		java.sql.PreparedStatement pstmt = null;
		java.sql.ResultSet rs = null;
		// 11/09/09 manoharan to store and get the sequence (file suffix) in invoice table
		java.sql.PreparedStatement pstmtSeq = null;
		java.sql.ResultSet rsSeq = null;
		java.sql.Timestamp tranDate = null;
		// end 11/09/09 manoharan to store and get the sequence (file suffix) in invoice table
		
		ConnDriver connDriver = new ConnDriver();
		try
		{
			conn = connDriver.getConnectDB( "DriverITM" );
			conn.setAutoCommit( false );
			
			pstmt = conn.prepareStatement( sql );
			rs = pstmt.executeQuery();
			if( rs.next() )
			{
				objName = rs.getString( "OBJ_NAME" );
				templUpld = rs.getString( "TEMPL_UPLD" );
				templTypeUpld = rs.getString( "TEMPL_TYPE_UPLD" );
				templDnld = rs.getString( "TEMPL_DNLD" );
				templTypeDnld = rs.getString( "TEMPL_TYPE_DNLD" );
				
				isTemplateExist = true;
			}
			System.out.println( " isTemplateExist :: [" + isTemplateExist + "]" );
			System.out.println( " objName :: [" + objName + "]" );
			System.out.println( " templUpld :: [" + templUpld + "]" );
			System.out.println( " templTypeUpld :: [" + templTypeUpld + "]" );
			System.out.println( " templDnld :: [" + templDnld + "]" );
			System.out.println( " templTypeDnld :: [" + templTypeDnld + "]" );
			rs.close();
			rs = null;
			pstmt.close();
			pstmt = null;
		}catch( Exception ex )
		{
			ex.printStackTrace();
		}
		if( isTemplateExist )
		{
			GenericUtility genericUtility = GenericUtility.getInstance();
			
			String xlsFileName = null;
			String csvData = null;
			String templFileName = null;
			templFileName = CommonConstants.JBOSSHOME;
			fileName = CommonConstants.JBOSSHOME + File.separator + "DOWNLOAD" + File.separator + objName;
			//ibase.webitm.utility.XML2XLSCustomXfm xlsConverter = new ibase.webitm.utility.XML2XLSCustomXfm();
			//System.out.println( " templDnld :: " + CommonConstants.JBOSSHOME + File.separator + "DWNLOAD" + File.separator + "template" + File.separator + templDnld );
			//System.out.println( " fileName :: " + fileName );
			try
			{
				if( templTypeDnld.equalsIgnoreCase( "C" ) )
				{
					ibase.webitm.utility.XML2CSVCustomConverter csvConverter = null;
					csvConverter = new ibase.webitm.utility.XML2CSVCustomConverter();

					if( objName.equalsIgnoreCase( "INVOICE" ) )
					{
						String str = null;
						client = new TranXmlData();
						
						// 11/09/09 manoharan DI89SUN223 to store the sequence number (file suffix) in invoice table and increment for the same day 
						pstmtSeq = conn.prepareStatement("SELECT MAX(case when download_file_seq is null then 0 else download_file_seq end) as download_file_seq FROM INVOICE WHERE CUST_CODE = ? AND TRAN_DATE = ? AND download_flag = 'Y'");
						// end 11/09/09 manoharan to store the sequence number (file suffix) in invoice table and increment for the same day
						
						//start - select all the undownloaded invoice, if found, then download for each.
						sql = "SELECT iv.INVOICE_ID TRAN_ID, iv.CUST_CODE, cu.UNIFORM_CODE, "
								+"		to_char( iv.tran_date, 'ddmmyyyy' ) dt , iv.tran_date tran_date"
								+"	FROM INVOICE iv, CUSTOMER cu, site_customer sc  "
								+" WHERE iv.cust_code = cu.cust_code( + ) "
								+"		and iv.site_code = sc.site_code "
								+"		and iv.cust_code = sc.cust_code "
								+"		and sc.download_YN = 'Y' "								
								+"		and ( iv.download_flag is null or iv.download_flag = 'N' ) ";
						if( siteCodeDnld != null && siteCodeDnld.trim().length() > 0 && custCodeDnld != null && custCodeDnld.trim().length() > 0 )
						{
							sql = sql + " and iv.site_code = '" + siteCodeDnld + "' and iv.cust_code = '" + custCodeDnld + "' ";
						}	
						sql = sql + " order by iv.cust_code  ";
						pstmt = conn.prepareStatement( sql );
						rs = pstmt.executeQuery();
												
						prvCustCode = null;
						recCounter = 0;
						while( rs.next() )
						{
							
							tranId = rs.getString( "TRAN_ID" );
							custCode = rs.getString( "CUST_CODE" );
							uniformCode = rs.getString( "UNIFORM_CODE" );
							dt = rs.getString( "dt" );
							tranDate = rs.getTimestamp( "tran_date" );
							if( custCode.equalsIgnoreCase( prvCustCode ) && dt.equalsIgnoreCase( previousDay  ) )
							{
								recCounter++;
							}
							else
							{
								// 11/09/09 DI89SUN223 manoharan to store the sequence number (file suffix) in invoice table and increment for the same day 
								//recCounter = 1;
								pstmtSeq.setString(1,custCode);
								pstmtSeq.setTimestamp(2,tranDate);
								rsSeq = pstmtSeq.executeQuery();
																if( rsSeq.next() )
								{
									recCounter = rsSeq.getInt("download_file_seq");
								}
								rsSeq.close();
								rsSeq = null;
								//if (recCounter == 0 )
								//{
									recCounter++;// = 1;
								//}
								// end 11/09/09 manoharan to store the sequence number (file suffix) in invoice table and increment for the same day
							}
							dnldFileName = ( uniformCode == null ? "" : uniformCode.trim() )  + "_" + dt + "_" + ( new java.text.DecimalFormat( "000" ) ).format( recCounter );
							prvCustCode = custCode;	
							previousDay	= dt;						
							tranFileMip.put( tranId, dnldFileName );
							// 11/09/09 DI89SUN223 manoharan to store the sequence number (file suffix) in invoice table and increment for the same day
							tranSeqMap.put(tranId, new Integer(recCounter));
							// end 11/09/09 manoharan to store the sequence number (file suffix) in invoice table and increment for the same day
						}
						
						rs.close();
						rs = null;
						pstmt.close();
						pstmt = null;
						// 11/09/09 DI89SUN223 manoharan to store the sequence (file suffix) in invoice table and increment the same
						pstmtSeq.close();
						pstmtSeq = null;
						// end 11/09/09 manoharan to store the sequence (file suffix) in invoice table and increment the same
						
						//end - select all the undownloaded invoice, if found, then download for each.
						
						// 11/09/09 DI89SUN223 manoharan to store the sequence (file suffix) in invoice table and increment the same
						//pstmt = conn.prepareStatement( "update invoice set download_flag = 'Y' where invoice_id = ?" );
						pstmt = conn.prepareStatement( "update invoice set download_flag = 'Y', download_file_seq = ? where invoice_id = ?" );
						// end 11/09/09 manoharan to store the sequence (file suffix) in invoice table and increment the same
						
						Iterator tranItr = tranFileMip.keySet().iterator();
						
						
						while( tranItr.hasNext() )
						{
							try
							{

								tranId = ( String ) tranItr.next();
								dnldFileName = ( String )tranFileMip.get( tranId );
								
								xmlToCsvStr = client.getTranXml( "invoice",
													java.sql.Timestamp.valueOf( "2008-11-01 00:00:00" ),
													java.sql.Timestamp.valueOf( "2008-11-01 00:00:00" ),
													tranId, tranId, conn );
								
								csvData = csvConverter.convert( xmlToCsvStr,  
														CommonConstants.JBOSSHOME + File.separator + "DOWNLOAD" 
														+ File.separator + "template" 
														+ File.separator + templDnld + ".xml",
														null
													);
								FileWriter fw = new FileWriter( downloadLoc + File.separator + dnldFileName + ".csv" );

								fw.write( csvData );

								fw.close();
								fw = null;
								//Changed by Rupali [update the Download_flag after each invoice files downloading].Start
								//pstmt.addBatch();					
								// 11/09/09 DI89SUN223 manoharan to store the sequence (file suffix) in invoice table and increment the same
								recCounter = (( Integer )tranSeqMap.get( tranId )).intValue();
								// end 11/09/09 manoharan to store the sequence (file suffix) in invoice table and increment the same

								pstmt.setInt( 1, recCounter );
								pstmt.setString( 2, tranId );
								int rowCnt = pstmt.executeUpdate();
								if(rowCnt >= 1){
									conn.commit();
								}
								else{
									conn.rollback();
								}
							}catch( Exception ex )
							{
								//ignore this exception as if one file throws exception next file should download.
							}
							//Changed by Rupali [update the Download_flag after each invoice files downloading].End
							
						}
						//Changed by Rupali [update the Download_flag after each invoice files downloading].Start
						//pstmt.executeBatch();
						pstmt.close();
						pstmt = null;
						/*
						try{
							conn.commit();
						}catch( Exception ex ){}
						*/
						//Changed by Rupali [update the Download_flag after each invoice files downloading].End
					}
					else
					{
						/*
						csvData = csvConverter.convert( xmlDataDnld,  
												CommonConstants.JBOSSHOME + File.separator + "DOWNLOAD" 
												+ File.separator + "template" 
												+ File.separator + templDnld + ".xml",
												null
											);
						*/
					}
				}
			}
			catch( Exception pte )
			{
				try{
					conn.rollback();
				}catch( Exception ex ){}
				pte.printStackTrace();
			}
		}
	}
	
	public String schedule( HashMap map )throws RemoteException, ITMException
	{
		return "";
	}
	public String schedule( String scheduleParamXML )throws RemoteException, ITMException
	{
		String userInfoList = null;
		String isIntractive = null;
		String objName = null;
		String downloadLoc = null;
		
		ArrayList paramListArr = new ArrayList();

		try
		{
			//APPLICATION_CONTEXT = CommonConstants.UPLOAD_LOC + File.separator + "webapps" + File.separator + "ibase" + File.separator ;						
			//System.out.println(" scheduleParamXML  [" + scheduleParamXML + "]");
			Document userInfoDom = new ibase.utility.GenericUtility().parseString( scheduleParamXML );			
			NodeList paramList = userInfoDom.getElementsByTagName( "ACTUALPARAMETER" );
			//
			int noOfParam = paramList.getLength();
			for( int paramCnt = 0; paramCnt < noOfParam ; paramCnt++ )
			{
				paramListArr.add( ( String )( paramList.item( paramCnt ).getFirstChild().getNodeValue() ) );
			}
			
			//for( int i = 0 ; i < paramListArr.size(); i++ )
			//{
			//	System.out.println( "My param :: " + (String)paramListArr.get( i ) ); 
			//}
			//
			
			/*
			System.out.println(" RootNode [ " + rootNode+ " ] ");
			NodeList propNodeList = rootNode.getChildNodes();			
			System.out.println(" propNodeList [ " + propNodeList+ " ] ");
			int noOfProps = propNodeList.getLength();			
			for( int propCtr = 0; propCtr < noOfProps; propCtr++ )
			{				
				Node propNode = propNodeList.item( propCtr );
				System.out.println(" propNode [" +propNode+"]");
				if( propNode != null && propNode.getFirstChild() != null )
				{
					String propName = propNode.getNodeName();
					System.out.println(" propName [" +propName+"]");
					if ( "UserInfoStr".equalsIgnoreCase( propName ) )
					{
						userInfoList = propNode.getFirstChild().getNodeValue();
						System.out.println(" userInfoList [" + userInfoList + "]");
					}
				    else if ( "downloadLoc".equalsIgnoreCase( propName ) )
					{
						downloadLoc = propNode.getFirstChild().getNodeValue();
						System.out.println(" isIntractive [" + isIntractive + "]");
					}					
					else if ( "objName".equalsIgnoreCase( propName ) )
					{
						objName = propNode.getFirstChild().getNodeValue();
						System.out.println(" objName [" + objName + "]");
					}
					else if ( "entityCode".equalsIgnoreCase( propName ) )
					{
						entityCode = propNode.getFirstChild().getNodeValue();
						System.out.println(" entityCode [" + entityCode + "]");
					}
					else if ( "entityType".equalsIgnoreCase( propName ) )
					{
						entityType = propNode.getFirstChild().getNodeValue();
						System.out.println(" entityType [" + entityType + "]");
					}
				}
			}
			*/
			//downloadTransaction( objName, downloadLoc, entityCode, entityType, null );
			/*
			List and order of arguments :
			1. location,
			2. Entity Code
			3. Entity Type 
			4.  Stite Code
			5. Cust code
			*/
			downloadTransaction( (String)paramListArr.get( 0 ), (String)paramListArr.get( 1 ), (String)paramListArr.get( 2 ), (String)paramListArr.get( 3 ), (String)paramListArr.get( 4 ), (String)paramListArr.get( 5 ) );
		}
		catch(Exception e)
		{
			//System.out.println("DownloadCSVBatchEJB :==>Schedule");
			e.printStackTrace();
		}
		
		return "";
	}	
}
