// pgFaxVendorRpt
//
// Date range selection page used to generate the fax vendor data extract.
// 
// Mick Davis, KPMG  4/6/99
//

package Apras;

import spider.*;
import spider.database.*;
import spider.visual.*;
import spider.session.*;
import spider.util.*;
import spider.html.*;
import java.io.*;
import java.util.*;
// import kpmg.*;

import spider.event.*;

//[[SPIDER_CLASS BEGIN
public class pgFaxVendorRpt extends UtilPage
//]]SPIDER_CLASS END
{

	CSpString accountName;

	//[[SPIDER_EVENTS BEGIN
	
	//[[SPIDER_EVENT<onBeforeLoadEvent ()>
	protected int onBeforeLoadEvent ()
	{

		int command = PROCEED;

		command = super.checkForValidUserID();
		if (command == STOP)
		{	
			return(command);	
		}

		return (command);
	}
	//]]SPIDER_EVENT<onBeforeLoadEvent ()>

	//[[SPIDER_EVENT<onBeforeDisplayEvent ()>
	protected int onBeforeDisplayEvent ()
	{
		// I. Wang 11/30/99
		// Stop the proxy server from caching this page
		CSpHttp.write("Cache-Control: private\n");		
		// End I. Wang 11/30/99 
		
		int command = PROCEED;
		if (CKpmgUtil.DEBUGSTAT) CKpmgUtil.debug("Getting the vendor name.");

		try
		{

			// Create an instance of the doAcct dataobject so we can get the account name
			CSpSelect doAcct = (CSpSelect)CSpider.getDataObject("doAccount");
			doAcct.clearDynamicCriteria();
			doAcct.addDynamicCriterion("ACCOUNT_ACCT_NUMBER", CSpCriteriaSQLObject.EQUAL_TO_STR_OPERATOR, CSpider.getUserSessionObject("AccountNumber"));
			command = doAcct.execute();
			if (doAcct.succeeded() && (command == PROCEED_WITH_BUILTIN_HANDLING))
			{
				int numRows = doAcct.getNumOfRows();
				if (CKpmgUtil.DEBUGSTAT) CKpmgUtil.debug("Num Rows: " + numRows);
				if( numRows > 0 )
				{	
					accountName = (CSpString)doAcct.getValue(0, "ACCOUNT_ACCT_NAME");
				}
			} else
			{
				if (CKpmgUtil.DEBUGSTAT) CKpmgUtil.debug("Retrieve of vendor name failed!");
				CAprasMsg msg = new CAprasMsg();
				boolean success = msg.Display_System_Error("W034");
				return(STOP);
			}

		} catch (Exception e)
		{
				if (CKpmgUtil.DEBUGSTAT) CKpmgUtil.debug("Retrieve of vendor name failed!" + e);
				CAprasMsg msg = new CAprasMsg();
				boolean success = msg.Display_System_Error("W034");
				return(STOP);
		}	

		return (PROCEED_WITH_BUILTIN_HANDLING);
	}
	//]]SPIDER_EVENT<onBeforeDisplayEvent ()>

	//[[SPIDER_EVENT<onBeforeDisplayEvent (CSpVisual visualObject)>
	protected int onBeforeDisplayEvent (CSpVisual visualObject)
	{

		try
		{
			if (visualObject.getName().equals("stAccountNum"))  
			{
				this.setDisplayFieldValue("stAccountNum", CSpider.getUserSessionObject("AccountNumber"));
			}

			if (visualObject.getName().equals("stAccountName"))  
			{
				this.setDisplayFieldValue("stAccountName", accountName);
			}
		} catch (Exception e)
		{
			if (CKpmgUtil.DEBUGSTAT) CKpmgUtil.debug("Exception:" + e);
			return(STOP);
		}

		return (PROCEED_WITH_BUILTIN_HANDLING);
	}
	//]]SPIDER_EVENT<onBeforeDisplayEvent (CSpVisual visualObject)>

	//[[SPIDER_EVENT<onbtnRetrieveWebEvent>
	public int onbtnRetrieveWebEvent( String args[] )
	{
		int command = PROCEED_WITH_BUILTIN_HANDLING;


		CSpDatetime fromDate;	
		CSpDatetime toDate;

		// validate the dates entered
		try
		{
			fromDate = this.getDisplayFieldValue("fromDate").datetimeValue();
			toDate = this.getDisplayFieldValue("toDate").datetimeValue();
		} catch (CSpDatetimeParsingException e)
		{
			if (CKpmgUtil.DEBUGSTAT) CKpmgUtil.debug("Error parsing datetime values..." + e);
			CAprasMsg msg = new CAprasMsg();
			boolean success = msg.Display_System_Error("W033", "", "pgFaxVendorRpt");
			return(STOP);
		} catch (NumberFormatException e)
		{
			if (CKpmgUtil.DEBUGSTAT) CKpmgUtil.debug("Error parsing datetime values..." + e);
			CAprasMsg msg = new CAprasMsg();
			boolean success = msg.Display_System_Error("W033", "", "pgFaxVendorRpt");
			return(STOP);
		} catch (Exception e)
		{
			if (CKpmgUtil.DEBUGSTAT) CKpmgUtil.debug("Other error: " + e);
			return(STOP);
		}
 

		// toDate cannot be greater than fromDate + 6 days
		try
		{

			if (CKpmgUtil.isDateGreater(toDate, fromDate, 0))
			{
				if (CKpmgUtil.DEBUGSTAT) CKpmgUtil.debug("To date is less than from date.");
				CAprasMsg msg = new CAprasMsg();
				boolean success = msg.Display_User_Error("W039", "", "pgFaxVendorRpt");
				return(STOP);
			}				


			
			if (CKpmgUtil.isDateGreater(fromDate, toDate, 6)) 
			{
				if (CKpmgUtil.DEBUGSTAT) CKpmgUtil.debug("To date is greater than from date + 6.");
				CAprasMsg msg = new CAprasMsg();
				boolean success = msg.Display_User_Error("W038", "The Fax Vendor Data Extract is limited to one weeks worth of data at a time.", "pgFaxVendorRpt");
				return(STOP);
			} else
			{
				if (CKpmgUtil.DEBUGSTAT) CKpmgUtil.debug("To date is NOT greater than from date + 6.");
				if (CKpmgUtil.DEBUGSTAT) CKpmgUtil.debug("Changing end date to include all day...");
				toDate.setHours(23);
				toDate.setMinutes(59);
				toDate.setSeconds(59);
				toDate.setMilliseconds(999);
			}
		}
		catch (Exception e)
		{

			if (CKpmgUtil.DEBUGSTAT) CKpmgUtil.debug ("Error trying to compare dates.");
		}		

		// ============================================================================ //
		// LCB:2000/05/31 - Issue 1766 The Fax Vendor report does a tablespace scan
		// ============================================================================ //
		// As per Jo Ann Cramer for this Query execute:
		// 	SET CURRENT DEGREE = 'ANY'
		CSpDBResult 		myResult;
		//CSpDBResultTable 	myResultTable;
		CSpTransaction 		myTrans = null;
		int 				myErrorCode;
		String 				mySQL;
		int 				mySqlCode;
		int 				myReturnCode;
		CAprasMsg msg = new CAprasMsg();
		
		try
		{
			myTrans = new CSpTransaction ();
			if (myTrans.begin ("dsDB2"))
			{
				//myTrans.execute (stpGetNewId);
				if (myTrans.succeeded ())
				{
					// Set the returned Id as the one to use in the Insert
					mySQL = "SET CURRENT DEGREE = 'ANY'";
					myResult = myTrans.executeImmediate("dsDB2", mySQL);
					myErrorCode =  myResult.getResultStatus().getErrorCode();

					if ((myErrorCode == CSpDataObject.SUCCESS) || (myErrorCode == CSpDataObject.END_OF_FETCH))
					{
						if (CKpmgUtil.DEBUGSTAT) CKpmgUtil.debug ("The statement SET CURRENT DEGREE = 'ANY' succeeded. myErrorCode = " + myErrorCode);

//========
						// ---------------------------------------------------
						// Create a dataobject and get the data.
						CSpSelect doData;
						boolean result = true;
						doData = (CSpSelect)CSpider.getDataObject("doFaxVendorTransmissions");
						doData.clearDynamicCriteria();
						result = doData.addDynamicCriterion("VENDOR_ACCT_NUMBER", CSpCriteriaSQLObject.EQUAL_TO_STR_OPERATOR, CSpider.getUserSessionObject("AccountNumber"));
						if (!result)
						{
							boolean success = msg.Display_System_Error("W035","","pgFaxVendorRpt");
							return(STOP);
						}			
						result = doData.addDynamicCriterion("APPLICATION_APPL_STATUS_TS", CSpCriteriaSQLObject.GREATER_THAN_EQUAL_TO_STR_OPERATOR, fromDate);
						if (!result)
						{
							boolean success = msg.Display_System_Error("W035","","pgFaxVendorRpt");
							return(STOP);
						}			
						result = doData.addDynamicCriterion("APPLICATION_APPL_STATUS_TS", CSpCriteriaSQLObject.LESS_THAN_EQUAL_TO_STR_OPERATOR, toDate);
						if (!result)
						{
							boolean success = msg.Display_System_Error("W035","","pgFaxVendorRpt");
							return(STOP);
						}			

						//command = doData.execute();
						command = myTrans.execute(doData);
						//if (doData.succeeded() && (command == PROCEED_WITH_BUILTIN_HANDLING))
						if (myTrans.succeeded() && (command == PROCEED_WITH_BUILTIN_HANDLING))
						{
							int numRows = doData.getNumOfRows();
							if (CKpmgUtil.DEBUGSTAT) CKpmgUtil.debug("Num Rows: " + numRows);
							if (numRows > 0)
							{
								try
								{
									// create the extract file
									String extractFileName = this.createExtractFile(doData);
									if (CKpmgUtil.DEBUGSTAT) CKpmgUtil.debug("Extract file: " + extractFileName);
									CSpCommonPage pgData = CSpider.getCommonPage("pgFaxVendorRptData");
									// create a page session object for the file name
									CSpider.putUserSessionObject("FILENAME", new CSpString(extractFileName));
									pgData.load();
								} catch (Exception e)
								{
									if (CKpmgUtil.DEBUGSTAT) CKpmgUtil.debug("Error creating extract file: " + e);
									boolean success = msg.Display_System_Error("W035","","pgFaxVendorRpt");
									return(STOP);
								}
							} else
							{
								if (CKpmgUtil.DEBUGSTAT) CKpmgUtil.debug("No data found...");
								boolean success = msg.Display_User_Error("W036","", "pgFaxVendorRpt");
							}
				
						} else
						{
	
							if (CKpmgUtil.DEBUGSTAT) CKpmgUtil.debug("Retrieve of fax vendor data failed!");
							CSpDBResultStatus status = doData.getLastResults().getResultStatus ();
   							if (CKpmgUtil.DEBUGSTAT) CKpmgUtil.debug("dbError: " + status.getErrorCode () + " (" + status.getErrorMessage () + ")");
			   				if (CKpmgUtil.DEBUGSTAT) CKpmgUtil.debug("vendErrCode: " + status.getVendorErrorCode1 () + " (" + status.getVendorErrorMessage1 ());
			   				if (CKpmgUtil.DEBUGSTAT) CKpmgUtil.debug("vendErrCode2: " + status.getVendorErrorCode2 () + " (" + status.getVendorErrorMessage2 ());
							boolean success = msg.Display_System_Error("W037","", "pgFaxVendorRpt");
							return(STOP);
						}
//=====
						// ============================================================================ //
						// LCB:2000/05/31 - Issue 1766 The Fax Vendor report does a tablespace scan
						// ============================================================================ //
						// As per Jo Ann Cramer for this Query execute:
						// 	SET CURRENT DEGREE = '1'
						mySQL = "SET CURRENT DEGREE = '1'";
						myTrans = CSpDataObject.executeImmediate("dsDB2", mySQL);
						myErrorCode =  myTrans.getResultStatus().getErrorCode();
						if ((myErrorCode == CSpDataObject.SUCCESS) || (myErrorCode == CSpDataObject.END_OF_FETCH))
						{
							if (CKpmgUtil.DEBUGSTAT) CKpmgUtil.debug ("The statement SET CURRENT DEGREE = 'ANY' succeeded. myErrorCode = " + myErrorCode);
						}
						// ============================================================================ //
					}
				}
			}
		}
		catch (Execption ex)
		{
			CSpLog.exception (this, "The Fax Vendor Report Transaction Failed:", ex);
			if (CKpmgUtil.DEBUGSTAT) CKpmgUtil.debug ("The transaction did not complete successfully. myErrorCode = " + myErrorCode);

			return(STOP);
			
		}
		finally
		{
			// Note that this code is executed regardless if an exception
			// occurred or not.
			if ( (myTrans != null) && (myTrans.isActive()) )
			{
				if (myTrans.succeeded ())
				{
					myTrans.commit ();
				}
				else
				{
					myTrans.rollback ();
				}
			}
		}



		return(command);
	}
	//]]SPIDER_EVENT<onbtnRetrieveWebEvent>

	//[[SPIDER_EVENT<onValidationFailureEvent (int failureType, CSpVisual visualObject)>
	protected int onValidationFailureEvent (int failureType, CSpVisual visualObject)
	{

		return (STOP_PROCESSING);
	}
	//]]SPIDER_EVENT<onValidationFailureEvent (int failureType, CSpVisual visualObject)>

	//]]SPIDER_EVENTS END


	// Create a comma delimited file from the dataObject data.
	String createExtractFile(CSpDataObject doData) throws Exception
	{

		String fileName;
		String pathName;
		try
		{
			if (CKpmgUtil.DEBUGSTAT) CKpmgUtil.debug(CSpider.getWebEnvVar(CSpVars.PATH_INFO).stringValue());
			if (CKpmgUtil.DEBUGSTAT) CKpmgUtil.debug(CSpider.getWebEnvVar(CSpVars.PATH_TRANSLATED).stringValue());
			String currFile = CSpider.getWebEnvVar(CSpVars.PATH_TRANSLATED).stringValue();
			int endOfPath = currFile.lastIndexOf("\\");
			endOfPath = currFile.lastIndexOf("\\", endOfPath -1 ) + 1;
			pathName = currFile.substring(0, endOfPath);
			if (CKpmgUtil.DEBUGSTAT) CKpmgUtil.debug("pathName: " + pathName);
		} catch (Exception e)
		{
			if (CKpmgUtil.DEBUGSTAT) CKpmgUtil.debug("Error getting home dir: " + e);
			pathName = "";
		}
		fileName = CSpider.getUserSessionObject("UserId").stringValue() + ".CSV";
		if (CKpmgUtil.DEBUGSTAT) CKpmgUtil.debug("Extract File Name: " + pathName + fileName);
		try
		{
			FileWriter extractFile = new FileWriter(pathName + fileName);
			String recordOutput = "";
			int numRows = doData.getNumOfRows();
			for (int i = 0; i < numRows; i++)
			{
				if (CKpmgUtil.DEBUGSTAT) CKpmgUtil.debug("Writing record: " + i);
				recordOutput = createExtractString(doData, i);
				extractFile.write(recordOutput);
			}
			extractFile.close();

		} catch (Exception e)
		{
			if (CKpmgUtil.DEBUGSTAT) CKpmgUtil.debug("Exception:" + e);
			throw e;
		}

		return(fileName);
	}


	String createExtractString(CSpDataObject doData, int recNum) throws Exception
	{

		String extractString = doData.getValue(recNum, "APPLICATION_APPL_STATUS_TS") + ", ";
		extractString = extractString + "'" + doData.getValue(recNum, "APPLICATION_APPL_DISTRICT_CODE") + "', ";
		extractString = extractString + "'" + doData.getValue(recNum, "APPLICATION_APPL_ID") + "', ";
		extractString = extractString + "'" + doData.getValue(recNum, "APPLICATION_PERMIT_YEAR") + "-";
		extractString = extractString + doData.getValue(recNum, "APPLICATION_PERMIT_JULIAN_DATE") + "-";
		extractString = extractString + doData.getValue(recNum, "APPLICATION_PERMIT_DIST_CODE") + "-";
		extractString = extractString + doData.getValue(recNum, "APPLICATION_PERMIT_NUMBER") + "-";
		extractString = extractString + doData.getValue(recNum, "APPLICATION_PERMIT_VERSION") + "', ";
		extractString = extractString + "'" + doData.getValue(recNum, "APPLICATION_APPL_APPLCNT_FEIN") + "', ";
		extractString = extractString + "'" + doData.getValue(recNum, "APPLICATION_APPL_APPLCNT_NAME") + "', ";
		extractString = extractString + "'" + doData.getValue(recNum, "APPLICATION_APPL_APPLCNT_ADDR1") + "', ";
		extractString = extractString + "'" + doData.getValue(recNum, "APPLICATION_APPL_APPLCNT_ADDR2") + "', ";
		extractString = extractString + "'" + doData.getValue(recNum, "APPLICATION_APPL_APPLCNT_CITY") + "', ";
		extractString = extractString + "'" + doData.getValue(recNum, "APPLICATION_APPL_APPLCNT_STATE") + "', ";
		extractString = extractString + "'" + doData.getValue(recNum, "APPLICATION_APPL_APPLCNT_ZIP") + "', ";
		extractString = extractString + "'" + doData.getValue(recNum, "APPL_VEHICLE_VEH_REG_STATE") + "', ";
		extractString = extractString + "'" + doData.getValue(recNum, "APPL_VEHICLE_VEH_REG_NUM") + "', ";
		extractString = extractString + "'" + doData.getValue(recNum, "APPLICATION_APPL_RETURN_FAX") + "', ";
		extractString = extractString + "'" + doData.getValue(recNum, "APPL_LOAD_APPL_LD_TYPE") + "', ";
		extractString = extractString + "'" + doData.getValue(recNum, "APPL_LOAD_APPL_LD_SER_NUM") + "'" + System.getProperty("line.separator");


		return(extractString);
	}

}