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();
		
		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;
		
		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;
			}
			
			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();

						//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 "
								+"	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" );
							
							if( custCode.equalsIgnoreCase( prvCustCode ) && dt.equalsIgnoreCase( previousDay  ) )
							{
								recCounter++;
							}
							else
							{
								recCounter = 1;
							}
							dnldFileName = ( uniformCode == null ? "" : uniformCode.trim() )  + "_" + dt + "_" + ( new java.text.DecimalFormat( "000" ) ).format( recCounter );
							prvCustCode = custCode;	
							previousDay	= dt;						
							tranFileMip.put( tranId, dnldFileName );
						}
						
						rs.close();
						rs = null;
						pstmt.close();
						pstmt = null;
						
						//end - select all the undownloaded invoice, if found, then download for each.
						
						pstmt = conn.prepareStatement( "update invoice set download_flag = 'Y' where invoice_id = ?" );
						
						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();					
								pstmt.setString( 1, 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 "";
	}	
}
