unit Main; { Amendments to program. 100106 RJC Quetzal(343) Fault reports a/c change in Franchise- Connex to MTM. 1007.. RJC Email added to tPerson. 1007.. RJC Service records added to iServiceID. } interface uses Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs, ComCtrls, Grids, DBGrids, RXDBCtrl, Menus, ExtCtrls, RXSplit, ToolWin, StdCtrls, Mask, ToolEdit, SpeedBar, RXCtrls, DBCtrls, Db, DBTables, RxLookup, ImgList, Registry, CurrEdit, Placemnt, inifiles, DualList; type TMainForm = class(TForm) PanelLeft: TPanel; PanelRight: TPanel; RxSplitterMain: TRxSplitter; MainMenu1: TMainMenu; mnuFileItem: TMenuItem; mnuFileExit: TMenuItem; SpeedBar1: TSpeedBar; SpeedbarSection1: TSpeedbarSection; PPTreeView: TTreeView; PageControl1: TPageControl; TabInitiate: TTabSheet; TabBatchStatusSummary: TTabSheet; TabCriticalSummary: TTabSheet; TabCriticalResolution: TTabSheet; RxDBGridCERes: TRxDBGrid; RxDBGridCESummary: TRxDBGrid; RxDBGridBatchStatus: TRxDBGrid; qLastBatch: TQuery; dsLastBatch: TDataSource; qBatchType: TQuery; dsBatchType: TDataSource; TabServices: TTabSheet; RxDBGridServices: TRxDBGrid; qServices: TQuery; dsServices: TDataSource; ImageList1: TImageList; qBatchSummary: TQuery; dsBatchSummary: TDataSource; qCESummary: TQuery; dsCESummary: TDataSource; qBatchSummaryBatchID: TIntegerField; qBatchSummaryBatchStatus: TStringField; qBatchSummaryBatchType: TStringField; qBatchSummaryAmountExGST: TFloatField; qBatchSummaryGSTAmount: TFloatField; qBatchSummaryTotalPayable: TFloatField; qBatchSummaryTotalAdjustments: TFloatField; PanelCEResolve: TPanel; btnCEProcess: TButton; btnCEReject: TButton; PanelBillingPeriod: TPanel; lblBillingPeriod: TLabel; qCEResolution: TQuery; dsCEResolution: TDataSource; TabAdjustment: TTabSheet; RxDBGridAdjustment: TRxDBGrid; TabDubiousSummary: TTabSheet; PanelDubiousSummary: TPanel; btnReleaseRevenue: TButton; RxDBGridDubiousSummary: TRxDBGrid; TabDubiousDetail: TTabSheet; RxDBGridDubiousDetail: TRxDBGrid; TabExpendRelease: TTabSheet; PanelExpRelService: TPanel; PanelExpRelBatch: TPanel; PanelExpRelBtn: TPanel; RxDBGridRelExpServiceID: TRxDBGrid; RxDBGridRelExpSummary: TRxDBGrid; btnRelExpenditure: TButton; RxSplitterRelExpend: TRxSplitter; qAdjustment: TQuery; dsAdjustment: TDataSource; qDubSummary: TQuery; dsDubSummary: TDataSource; qDubDetail: TQuery; dsDubDetail: TDataSource; PanelExpRelTransactSummary: TPanel; PanelExpRelServDetails: TPanel; qRelExpTransactSummary: TQuery; dsRelExpTransactSummary: TDataSource; mnuOptionsItem: TMenuItem; mnuToolsItem: TMenuItem; mnuReportsItem: TMenuItem; mnuUpdateItem: TMenuItem; mnuHelpItem: TMenuItem; qRelExpService: TQuery; dsRelExpService: TDataSource; TabAdminSummary: TTabSheet; TabAdminDetail: TTabSheet; RxDBGridAFSummary: TRxDBGrid; PanelAdminSummary: TPanel; btnAdminAcceptAll: TButton; btnAdminReCalculate: TButton; qAdminSummary: TQuery; qAdminDetail: TQuery; dsAdminSummary: TDataSource; dsAdminDetail: TDataSource; PanelAFDetail: TPanel; RxDBGridAFDetail: TRxDBGrid; lblAFDAcctFee: TLabel; lblAFDDiscountFee: TLabel; lblAFDTotalFee: TLabel; qAdminDetailBatchID: TIntegerField; qAdminDetailServiceID: TStringField; qAdminDetailDateFrom: TDateTimeField; qAdminDetailDateTo: TDateTimeField; qAdminDetailTotalExGST: TFloatField; qAdminDetailTotalGST: TFloatField; qAdminDetailTotalIncGST: TFloatField; qAdminDetailTransactionGroup: TStringField; lblAFDFeeRate: TLabel; lblAFDDiscountRate: TLabel; lblAFDTotalRate: TLabel; qTree: TQuery; RxDBGridBatch: TRxDBGrid; lblNotice: TLabel; dblInvoiceNoName: TDBText; edtInvoiceNo: TEdit; edtAccountNo: TEdit; dblAccountNoName: TDBText; dblOpeningBalanceName: TDBText; dblPaymentsReceivedName: TDBText; dblTotalRevenueName: TDBText; dblTotalCreditsName: TDBText; dblGSTName: TDBText; dblIssueDate: TDBText; dblOrderNumberName: TDBText; edtOrderNumber: TEdit; dtpIssueDate: TDateTimePicker; dtpPayByDate: TDateTimePicker; dtpPaymentDate: TDateTimePicker; edtRequisitionNumber: TEdit; dblRequisitionNumberName: TDBText; dblPaymentDateName: TDBText; lblToBePaidBy: TLabel; dtpCallStartDate: TDateTimePicker; dtpCallEndDate: TDateTimePicker; dtpRentStartDate: TDateTimePicker; dtpRentEndDate: TDateTimePicker; lblCallStartDate: TLabel; lblCallEndDate: TLabel; lblRentStartDate: TLabel; lblRentEndDate: TLabel; dblGSTAdjustmentsName: TDBText; dblTotalAdjustmentsName: TDBText; dblRoundingName: TDBText; dblTotalPayableName: TDBText; qry: TQuery; edtOpeningBalance: TCurrencyEdit; edtPaymentsReceived_: TCurrencyEdit; edtTotalRevenue: TCurrencyEdit; edtTotalCredits: TCurrencyEdit; edtGST: TCurrencyEdit; edtGSTAdjustments: TCurrencyEdit; edtTotalAdjustments: TCurrencyEdit; edtRounding: TCurrencyEdit; edtTotalPayable: TCurrencyEdit; cbBillingPeriod: TComboBox; DESPanel: TPanel; lblDESResolution: TLabel; btnDESApply: TButton; btnDERApplyAll: TButton; cbDESResolution: TRxDBLookupCombo; qDESResolution: TQuery; dsDESResolution: TDataSource; TabTransactionSummary: TTabSheet; PanelTransactions: TPanel; RxDBGridTransactions: TRxDBGrid; lblTransactions: TLabel; cbBatchTransactions: TComboBox; qTransactions: TQuery; dsTransactions: TDataSource; qBatchSummaryBatchStatusID: TIntegerField; qCESummaryBatchID: TIntegerField; qCESummaryBatchType: TStringField; qCESummaryTotalExGST: TFloatField; qCESummaryTotalGST: TFloatField; qCESummaryTotalIncGST: TFloatField; qCESummaryCriticalValue: TFloatField; qCEResolutionBatchID: TIntegerField; qCEResolutionServiceID: TStringField; qCEResolutionStatusID: TIntegerField; qCEResolutionTransactionType: TStringField; qCEResolutionTotalExGST: TFloatField; qCEResolutionTotalGST: TFloatField; qCEResolutionTotalIncGST: TFloatField; qCEResolutionRecordCount: TIntegerField; qCEResolutionCriticalType: TStringField; Label1: TLabel; MainFormStorage: TFormStorage; qAdminSummaryBatchID: TIntegerField; qAdminSummaryServiceID: TStringField; qAdminSummaryCode: TStringField; qAdminSummaryDateFrom: TDateTimeField; qAdminSummaryDateTo: TDateTimeField; qAdminSummaryRent: TFloatField; qAdminSummaryCalls: TFloatField; qAdminSummaryOther: TFloatField; qAdminSummaryAdmin: TFloatField; qAdminSummaryDiscountRate: TFloatField; qAdminSummaryNonStdRevAdminFee: TFloatField; qAdminSummaryNonStdExpAdminFee: TFloatField; btnDERecalculate: TButton; MainPrinterSetupDialog: TPrinterSetupDialog; mnuFilePrinterSetup: TMenuItem; mnuFilePrint: TMenuItem; N1: TMenuItem; mnuUpdateSystemdata: TMenuItem; PanelInitiate: TPanel; edtBatchID: TEdit; lblBatchID: TLabel; btnNewBatch: TButton; btnAddTransaction: TButton; btnOkay: TButton; btnCancel: TButton; sbtnAddTransaction: TSpeedItem; sbtnExit: TSpeedItem; mnuViewItem: TMenuItem; mnuViewAdjustmentsItem: TMenuItem; mnuViewBatchStatusItem: TMenuItem; sbtnCloseTab: TSpeedItem; qTransactionsServiceID: TStringField; qTransactionsStatusID: TIntegerField; qTransactionsRent: TFloatField; qTransactionsCall: TFloatField; qTransactionsOther: TFloatField; qTransactionsReason: TStringField; qRelExpTransactSummaryBatchID: TIntegerField; qRelExpTransactSummaryServiceID: TStringField; qRelExpTransactSummaryDateFrom: TDateTimeField; qRelExpTransactSummaryDateTo: TDateTimeField; qRelExpTransactSummaryRent: TFloatField; qRelExpTransactSummaryCalls: TFloatField; qRelExpTransactSummaryOther: TFloatField; qRelExpTransactSummaryTotal: TFloatField; DBText1: TDBText; edtTenderNo: TEdit; lblTenderNo: TLabel; TabDCdetail: TTabSheet; PanelDCdetail: TPanel; RxDBGridDCdetail: TRxDBGrid; dbNavDCdetail: TDBNavigator; dbNavDC: TDBNavigator; qDCdetail: TQuery; dsDCdetail: TDataSource; qDCdetailBatchID: TIntegerField; qDCdetailServiceID: TStringField; qDCdetailCode: TStringField; qDCdetailAmountExGST: TFloatField; qDCdetailVar01: TStringField; qDCdetailVar02: TStringField; qDCdetailVar03: TStringField; qDCdetailVar04: TStringField; qDCdetailVar05: TStringField; qDCdetailVar06: TStringField; qDCdetailVar07: TStringField; qDCdetailVar08: TStringField; qDCdetailVar09: TStringField; qDCdetailVar10: TStringField; qDCdetailVar11: TStringField; qDCdetailVar12: TStringField; qDCdetailVar13: TStringField; qDCdetailVar14: TStringField; qDCdetailVar15: TStringField; qDCdetailCallCode: TStringField; qDCdetailDialledNumber: TStringField; qDCdetailRateDescription: TStringField; qDCdetailTransactionType: TStringField; sbtnEdit: TSpeedItem; sbtnPrint: TSpeedItem; qAdjustmentBatchID: TIntegerField; qAdjustmentBatchType: TStringField; qAdjustmentTotalExGST: TFloatField; qAdjustmentTotalGST: TFloatField; qAdjustmentTotalIncGST: TFloatField; qAdjustmentCriticalValue: TFloatField; sbtnSelectAll: TSpeedItem; qTransactionsAdminFee: TBooleanField; qTransactionsCode: TStringField; qTransactionsGST: TFloatField; qTransactionsTotalIncGST: TFloatField; qTransactionsTotalExGST: TFloatField; qAdminSummarySubTotal: TFloatField; qAdminSummaryTotal: TFloatField; qRelExpTransactSummaryAdmin: TFloatField; qRelExpTransactSummarySubTotal: TFloatField; qTransactionsShipTo: TStringField; qAdminSummaryShipTo: TStringField; qAdminDetailTransactionType: TStringField; qTransactionsAuto: TQuery; dsTransactionsAuto: TDataSource; rbBatchTransactions: TRadioButton; rbBatchTransactionsAuto: TRadioButton; RxDBGridTransactionsAuto: TRxDBGrid; qTransactionsAutoBatchID: TIntegerField; qTransactionsAutoServiceID: TStringField; qTransactionsAutoAmountExGST: TFloatField; qTransactionsAutoVar01: TStringField; qTransactionsAutoVar02: TStringField; qTransactionsAutoVar03: TStringField; qTransactionsAutoVar04: TStringField; qTransactionsAutoVar05: TStringField; qTransactionsAutoVar06: TStringField; qTransactionsAutoVar07: TStringField; qTransactionsAutoVar08: TStringField; qTransactionsAutoVar09: TStringField; qTransactionsAutoVar10: TStringField; qTransactionsAutoVar11: TStringField; qTransactionsAutoVar12: TStringField; qTransactionsAutoVar13: TStringField; qTransactionsAutoVar14: TStringField; qTransactionsAutoVar15: TStringField; qTransactionsAutoReason: TStringField; qTransactionsAutoStatusID: TIntegerField; qTransactionsAutoCode: TStringField; sbtnPrinterSetup: TSpeedItem; qDubSummaryBatchID: TIntegerField; qDubSummaryServiceID: TIntegerField; qDubSummaryBatchTypeID: TIntegerField; qDubSummaryRecordCount: TIntegerField; qDubSummaryRent: TFloatField; qDubSummaryOther: TFloatField; qDubSummaryTotalExGST: TFloatField; qDubSummaryTotalGST: TFloatField; qDubSummaryTotalIncGST: TFloatField; qDubSummaryAdmin: TFloatField; qDubSummaryDubiousTypeID: TIntegerField; qDubSummaryDID: TIntegerField; qDubSummaryService: TStringField; qDubSummaryDubiousError: TStringField; qDubSummaryResolution: TStringField; qDubSummaryCalls: TFloatField; qDubSummaryPreviousRent2: TFloatField; qDubSummaryPreviousCalls2: TFloatField; TimeBomb: TTimer; mnuHelpAbout: TMenuItem; qTransactionsFromDate: TStringField; qTransactionsToDate: TStringField; mnuFileCloseTab: TMenuItem; TabCompleted: TTabSheet; PanelCompleted: TPanel; DBNavCompleted: TDBNavigator; RxDBGridCompletedPayable: TRxDBGrid; qCompletedPayable: TQuery; dsCompletedPayable: TDataSource; lblTestData: TLabel; mnuToolsSelectMainDatabaseItem: TMenuItem; mnuToolsSelectTestDatabaseItem: TMenuItem; RxDBGridMultiManual: TRxDBGrid; MuliManualDualListDialog: TDualListDialog; qMultiManual: TQuery; dsMultiManual: TDataSource; qMultiManualServiceID: TStringField; qMultiManualRent: TFloatField; qMultiManualCall: TFloatField; qMultiManualOther: TFloatField; qMultiManualGSTAmt: TFloatField; PanelDCExpense: TPanel; rbDCExpense: TRadioButton; rbDCRevenue: TRadioButton; qMultiManualBatchTypeID: TIntegerField; navMultiManual: TDBNavigator; qMultiManualID: TIntegerField; mnuViewRefreshItem: TMenuItem; uqMultiManual: TUpdateSQL; qLastBatchBatch: TIntegerField; qLastBatchPeriod: TStringField; qLastBatchAccountNo: TStringField; qLastBatchInvoice: TStringField; qLastBatchTotalPayable: TFloatField; qLastBatchOpeningBalance: TFloatField; qLastBatchPaymentsReceived: TFloatField; qLastBatchTotalAdjustments: TFloatField; qLastBatchGSTAmount: TFloatField; qLastBatchGSTAdjustment: TFloatField; qMultiManualFromDate: TStringField; qMultiManualToDate: TStringField; qMultiManualRateDescription: TStringField; edtPaymentsReceived: TEdit; qCompletedPayableBatchID: TIntegerField; qCompletedPayablePayableDate: TDateTimeField; qCompletedPayablePlatinumFileName: TStringField; qCompletedPayableReleased: TStringField; qCompletedPayableSupplierName: TStringField; qCompletedPayableCentre: TStringField; qCompletedPayableActivity: TStringField; qCompletedPayableElement: TStringField; qCompletedPayableSubledger: TStringField; qCompletedPayablePayableDetailID: TIntegerField; qCompletedPayablePayableID: TIntegerField; qCompletedPayablePeriod: TStringField; qCompletedPayableAmountExGST: TFloatField; qCompletedPayableGSTAmount: TFloatField; qCompletedPayableAmountIncGST: TFloatField; TabCompletedDetail: TTabSheet; PanelCompletedDetail: TPanel; DBNavCompletedDetail: TDBNavigator; btnFilter: TButton; RxDBGridCompletedDetail: TRxDBGrid; qCompletedDetail: TQuery; dsCompletedDetail: TDataSource; cbBatchCompleted: TComboBox; rbPayable: TRadioButton; rbInvoice: TRadioButton; btnCompletedApply: TButton; qCompletedInvoice: TQuery; dsCompletedInvoice: TDataSource; RxDBGridCompletedInvoice: TRxDBGrid; mnuToolsWebData: TMenuItem; lblStart: TLabel; lblEnd: TLabel; sbtnFile: TSpeedItem; mnuFileSaveAsItem: TMenuItem; Import1: TMenuItem; TelstraInvoice1: TMenuItem; procedure FormCreate(Sender: TObject); procedure FormClose(Sender: TObject; var Action: TCloseAction); procedure GetUserName(var sUserName: string); procedure GetSupplierName(const nBatchNumber: integer; var sName: string); procedure GetBatchTypeName(const nBatchNumber: integer; var sName: string; var nBatchTypeID: integer); procedure CheckEnvironment(var sUserName: String; var lSuccessful: boolean); procedure RxDBGridServicesGetCellParams(Sender: TObject; Field: TField; AFont: TFont; var Background: TColor; Highlight: Boolean); procedure PaintPPTreeView(const sBillingPeriod: string); procedure cbBillingPeriodChange(Sender: TObject); procedure PPTreeViewChange(Sender: TObject; Node: TTreeNode); procedure RefreshTreeItem(const Node: TTreeNode); procedure ProcessBilling; // procedure InitiateScreen(const sBatchTypeID,sBatchID: string); // procedure PopulateBatchScreen( const sBatchID: string); procedure InitiateScreen; procedure PopulateBatchScreen; procedure btnOkayClick(Sender: TObject); procedure SaveBatch( var nBatchNumber, nBatchStatusNumber: LongInt); // procedure GetBatchStatus( var nBatchNumber, nBatchStatusNumber: LongInt); // procedure ShowPreviousBatch( const sBatch, sBatchType: string ); procedure ShowPreviousBatch; procedure OpenTab(OpenTabSheet : TTabSheet); procedure CloseTab(CloseTabSheet : TTabSheet); procedure ClearTabSheets; procedure SetBillingPeriodBatch; procedure GetSQL(const SQLType: string; var slSQL: TStringList); procedure btnCEProcessClick(Sender: TObject); procedure btnCERejectClick(Sender: TObject); procedure RxDBGridCEResTitleClick(Column: TColumn); procedure RxDBGridCESummaryTitleClick(Column: TColumn); procedure RxDBGridBatchStatusTitleClick(Column: TColumn); procedure RxDBGridServicesTitleClick(Column: TColumn); procedure RxDBGridAdjustmentTitleClick(Column: TColumn); procedure RxDBGridDubiousSummaryTitleClick(Column: TColumn); procedure RxDBGridAFSummaryTitleClick(Column: TColumn); procedure RxDBGridRelExpSummaryCellClick(Column: TColumn); procedure RxDBGridDubiousSummaryDblClick(Sender: TObject); procedure ResolveDubious(Sender: TObject); procedure RxDBGridDubiousSummaryCellClick(Column: TColumn); procedure RxDBGridTransactionsGetCellParams(Sender: TObject; Field: TField; AFont: TFont; var Background: TColor; Highlight: Boolean); procedure RxDBGridTransactionsAutoGetCellParams(Sender: TObject; Field: TField; AFont: TFont; var Background: TColor; Highlight: Boolean); procedure RxDBGridBatchStatusDblClick(Sender: TObject); procedure RxDBGridAFSummaryDblClick(Sender: TObject); procedure RxDBGridCESummaryDblClick(Sender: TObject); procedure RxDBGridRelExpSummaryKeyUp(Sender: TObject; var Key: Word; Shift: TShiftState); procedure RxDBGridTransactionsTitleClick(Column: TColumn); procedure RxDBGridTransactionsAutoTitleClick(Column: TColumn); procedure RxDBGridAFDetailTitleClick(Column: TColumn); procedure RxDBGridDCdetailTitleClick(Column: TColumn); procedure RxDBGridDCdetailGetCellParams(Sender: TObject; Field: TField; AFont: TFont; var Background: TColor; Highlight: Boolean); procedure RxDBGridRelExpSummaryTitleClick(Column: TColumn); procedure RxDBGridTransactionsDblClick(Sender: TObject); procedure RefreshResolutionChoices; procedure btnReleaseRevenueClick(Sender: TObject); procedure cbBatchTransactionsChange(Sender: TObject); procedure cbBatchCompletedChange(Sender: TObject); procedure btnCancelClick(Sender: TObject); procedure btnAdminAcceptAllClick(Sender: TObject); procedure btnAdminReCalculateClick(Sender: TObject); procedure btnRelExpenditureClick(Sender: TObject); procedure btnDERecalculateClick(Sender: TObject); procedure ActionPrinterSetup(Sender: TObject); procedure ActionPrintCurrentTab(Sender: TObject); procedure mnuUpdateSystemDataClick(Sender: TObject); procedure ActionExit(Sender: TObject); procedure btnNewBatchClick(Sender: TObject); procedure mnuViewAdjustmentsItemClick(Sender: TObject); procedure mnuViewBatchStatusItemClick(Sender: TObject); procedure FormKeyDown(Sender: TObject; var Key: Word; Shift: TShiftState); procedure ActionCloseTab(Sender: TObject); // procedure InitiateTab_Change(Sender: TObject); // procedure ManualDCInsert(slManual: TStringList); procedure btnAddTransactionClick(Sender: TObject); procedure EditSystemTables; procedure sbtnEditClick(Sender: TObject); procedure sbtnSelectAllClick(Sender: TObject); procedure rbBatchTransactionsClick(Sender: TObject); procedure mnuHelpAboutClick(Sender: TObject); procedure btnFilterClick(Sender: TObject); procedure RxDBGridCompletedPayableTitleClick(Column: TColumn); procedure mnuToolsSelectMainDatabaseItemClick(Sender: TObject); procedure mnuToolsSelectTestDatabaseItemClick(Sender: TObject); procedure rbDCRevenueClick(Sender: TObject); procedure rbDCExpenseClick(Sender: TObject); procedure qMultiManualUpdateRecord(DataSet: TDataSet; UpdateKind: TUpdateKind; var UpdateAction: TUpdateAction); procedure mnuViewRefreshItemClick(Sender: TObject); procedure rbPayableClick(Sender: TObject); procedure SetBatchCompleted(var slSQL: TStringList); procedure mnuToolsWebDataClick(Sender: TObject); procedure ActionFileCurrentTab(Sender: TObject); procedure TelstraInvoice1Click(Sender: TObject); private { Private declarations } BATCHSTAT_INIT : LongInt; // Batch status ID= Initiated. BATCHSTAT_IMPORT : LongInt; // Batch status ID= Imported data. BATCHSTAT_CERRS : LongInt; // Batch status ID= Critical errors. BATCHSTAT_CPASS : LongInt; // Batch status ID= Critical check passed. BATCHSTAT_TRANSNS : LongInt; // Batch status ID= Transactions created. BATCHSTAT_ADMINRVW : LongInt; // Batch status ID= Review admin fees. BATCHSTAT_ADMINOK : LongInt; // Batch status ID= Review admin fees. BATCHSTAT_PAYEXP : LongInt; // Batch status ID= Released expenditure // for payment. BATCHSTAT_DCHECK : LongInt; // Batch status ID= Dubious check. BATCHSTAT_DCHECKOK : LongInt; // Batch status ID= Dubious checks passed. BATCHSTAT_RELEASED : LongInt; // Batch status ID= Released. OperatorID : string; // Network Name - Used for mult-user data entry. n_User_ID : Integer; // tUser.ID for the operator. APPLIEDGSTRATE : real; TestDatabaseFlag : boolean; sInitiateBatchID : string; sInitiateBatchTypeID : string; nProgramID : integer; // Program ID to steer program code without // depending on tBatchType.Description. procedure DisplayInitiateEditBoxes; procedure DisplayInitiateGrid; procedure ViewDCExpense(var slSQL: TStringList); procedure ViewDCRevenue(var slSQL: TStringList); function TestForDCRevenue(const nBatchID: integer): boolean; protected { Protected declarations } procedure SetTesting(const Value: boolean); procedure SetSelectedBatchID( Value: integer ); procedure SetBatchInit( Value: integer ); procedure SetBatchImport( Value: integer ); procedure SetBatchCerr( Value: integer ); procedure SetBatchCpass( Value: integer ); procedure SetBatchTransns( Value: integer ); procedure SetBatchAdminRvw( Value: integer ); procedure SetBatchAdminOk( Value: integer ); procedure SetBatchPayExt( Value: integer ); procedure SetBatchDcheck( Value: integer ); procedure SetBatchDcheckOk( Value: integer ); procedure SetBatchReleased( Value: integer ); procedure SetGSTRate( Value: real ); procedure SelectAutoColumnHeaders(const nBatch: integer); procedure SetOperatorID(const Value: string); procedure SetCaption(const Value: string); procedure SetUserID(const Value: integer); procedure SetCompletedFilter(const Value: TStringList); procedure SetTotalIncGST(const Value: currency); procedure SetTotalGST(const Value: currency); procedure SetCompletedBatchIDValues; procedure SaveMultiManualData; published // procedure StartTime; // procedure EndTime; procedure ProcessWinMessages; property TestDatabase: boolean write SetTesting; property BillingBatchID: Integer write SetSelectedBatchID; property OPERATORNAME: string read OperatorID write SetOperatorID; property OPERATORFULLNAME: string write SetCaption; property OPERATORNUMBER: integer read n_User_ID write SetUserID; property GST_RATE: real read APPLIEDGSTRATE write SetGSTRate; property BATCH_STATUS_INIT: integer read BATCHSTAT_INIT write SetBatchInit; property BATCH_STATUS_IMPORT: integer read BATCHSTAT_IMPORT write SetBatchImport; property BATCH_STATUS_CERRS: integer read BATCHSTAT_CERRS write SetBatchCerr; property BATCH_STATUS_CPASS: integer read BATCHSTAT_CPASS write SetBatchCpass; property BATCH_STATUS_TRANSNS: integer read BATCHSTAT_TRANSNS write SetBatchTransns; property BATCH_STATUS_ADMINRVW: integer read BATCHSTAT_ADMINRVW write SetBatchAdminRvw; property BATCH_STATUS_ADMINOK: integer read BATCHSTAT_ADMINOK write SetBatchAdminOk; property BATCH_STATUS_PAYEXP: integer read BATCHSTAT_PAYEXP write SetBatchPayExt; property BATCH_STATUS_DCHECK: integer read BATCHSTAT_DCHECK write SetBatchDcheck; property BATCH_STATUS_DCHECKOK: integer read BATCHSTAT_DCHECKOK write SetBatchDcheckOk; property BATCH_STATUS_RELEASED: integer read BATCHSTAT_RELEASED write SetBatchReleased; property CompletedFilter: TStringList write SetCompletedFilter; property TotalIncGST: currency write SetTotalIncGST; property TotalGST: currency write SetTotalGST; public { Public declarations } end; const WINNUM = 34; // Number of elements in this (aWin) array. // - values declared in ConstantValues.prg var MainForm: TMainForm; // DM : DMT; aWinForm: Array[0..WINNUM] of Boolean; // True if form open. OperatorID : string; // Network Name - Used for multiple users. s_User_ID : string; // ID number (string) for OperatorID. JobID : string; // Job Name - Used for multiple data entry. TestDataFlag : boolean; SelectedBatchID : Integer; InitiateNode : TTreeNode; sDefaultRevAdmRate : string; // Default Admin Fee rates displayed in the sDefaultExpAdmRate : string; // TabAdminDetail sheet. implementation uses About, AdminCharges, AnnualCharges, AnnualData, AccessRights, CodeFile, ConstantValues, CriticalTests, DateFunctions, DataMod, DCEdit, DubiousTests, FMXUtils, // Located in Delphi's Demos\Doc\Filmanex folder. GenFns, GenericPrint, InitFns, LoginFrm, Manual, MultiManual, OptusFixed, OptusMobile, OptusMobileData, Quetzal, QuoteSystem, ReleaseTransactions, SaveToTransaction, Security, Services, Telmax, TelstraFixed, TelstraFixedEBS, TelstraFixedOrigEBS, TelstraMobile, TelstraMobileEBS, TelstraMobileData, TelstraMobileDataEBS, Vodafone, Filter; {$R *.DFM} var AddManualForm : TManualForm; FilterForm : TFilterForm; aAccess : array[0..16] of boolean; // Holds access rights of the Operator. // frmSystem : TfrmSystem; // System tables editing window. TreeViewBeingUpdated : boolean; InitiatingForm : boolean; // InitiateTabUpdated : boolean; CloseDisabled : boolean; // SelectedBatchID : Integer; dDateToday : TDateTime; lNewBatch : boolean; lManualEntry : boolean; lManualMultipleEntry : boolean; lAutomaticEntry : boolean; lAnnualChargesEntry : boolean; lSACCEntry : boolean; lTelmaxEntry : boolean; sNewBatchTypeID : string; sNewBatchStatusID : string; lNewManualInvoice : boolean; sExeDirectory : string; procedure TMainForm.ActionExit(Sender: TObject); begin // PPTreeView.SaveToFile('PPTreeViewFile.txt'); Close; end; // procedure MenuFileExitItemClick(). procedure TMainForm.ProcessWinMessages; begin // Take the opportunity to let Windows do its stuff. Application.ProcessMessages; end; procedure TMainForm.FormCreate(Sender: TObject); var qFormCreate : TQuery; // LoginParams : TStringList; // Required for eventual security. begin TreeViewBeingUpdated := False; InitiatingForm := True; sExeDirectory := ExtractFilePath(ParamStr(0)); GetUserName(MainForm.OperatorID); GetOperatorReference(OperatorID, s_User_ID); n_User_ID := StrToInt(s_User_ID); if SetAccessRights(True, aAccess) then begin // True=Prompt for password. // Make sure any 2-digit year date is valid by setting a pivot such that the // century change is 50 years away - ie. currently in the centre of the range. TwoDigitYearCenturyWindow := StrToInt(Right(IntToStr(Year(Date)+50),2)); GetValidBatchStatusEntries(SELF); // Create a list of Billing Periods and set the default to the most recent. qFormCreate := TQuery.Create(SELF); qFormCreate.DatabaseName := 'dbPPdata'; qFormCreate.SQL.Add('SELECT DISTINCT tBatch.BillingPeriod'); qFormCreate.SQL.Add('FROM tBatch'); qFormCreate.SQL.Add('ORDER BY tBatch.BillingPeriod DESC'); qFormCreate.Open; while not qFormCreate.Eof do begin cbBillingPeriod.Items.Add(qFormCreate['BillingPeriod']); qFormCreate.Next; end; cbBillingPeriod.Text := cbBillingPeriod.Items[0]; qFormCreate.Close; PaintPPTreeView(cbBillingPeriod.Text); qFormCreate.SQL.Clear; qFormCreate.SQL.Add('SELECT *'); qFormCreate.SQL.Add('FROM tParameter'); qFormCreate.SQL.Add('WHERE tParameter.Parm LIKE ''AdminFeeExpenditureRate'''); qFormCreate.Open; if qFormCreate.Bof and qFormCreate.Eof then sDefaultExpAdmRate := '0.00' else sDefaultExpAdmRate := FloatToStrF(100*qFormCreate['ParmNumber'],ffFixed,5,2); qFormCreate.Close; qFormCreate.SQL.Clear; qFormCreate.SQL.Add('SELECT *'); qFormCreate.SQL.Add('FROM tParameter'); qFormCreate.SQL.Add('WHERE tParameter.Parm LIKE ''AdminFeeRevenueRate'''); qFormCreate.Open; if qFormCreate.Bof and qFormCreate.Eof then sDefaultRevAdmRate := '0.00' else sDefaultRevAdmRate := FloatToStrF(100*qFormCreate['ParmNumber'],ffFixed,5,2); qFormCreate.Close; qFormCreate.Free; APPLIEDGSTRATE := 0.1000; // Default GST rate. GetGSTRate; // Read GST rate from tParameter - ref GenFns. SetBillingPeriodBatch; dDateToday := Date; lNewManualInvoice := False; TestDatabaseFlag := TestForTestDB; if TestDatabaseFlag and not aAccess[DEVELOPER] then begin TestDatabaseFlag := False; DM.SetDatabaseSet(TestDatabaseFlag); end; lblTestData.Visible := TestDatabaseFlag; InitiatingForm := False; CloseDisabled := False; end else begin TimeBomb.Enabled := True; end; end; // FormCreate. procedure TMainForm.FormClose(Sender: TObject; var Action: TCloseAction); begin if not CloseDisabled then begin if (FilterForm <> nil) then begin FilterForm.Hide; FilterForm.Free; end; LogOffUser(s_User_ID); DM.dbPPdata.Connected := False; end; // if not CloseDisabled then begin. end; // FormClose. procedure TMainForm.SetTesting(const Value: boolean); begin TestDatabaseFlag := Value; end; // SetTesting. procedure TMainForm.SetOperatorID(const Value: string); begin OperatorID := Value; end; // SetOperatorID. procedure TMainForm.SetCaption(const Value: string); begin Caption := 'VicTrack Pre Processor Operator: ' + Value; end; // SetOperatorID. procedure TMainForm.SetUserID(const Value: integer); begin if (Value > 0) then begin n_user_id := Value; s_user_id := IntToStr(n_user_id); end; end; // SetUserID. procedure TMainForm.cbBillingPeriodChange(Sender: TObject); begin if (Length(Trim(cbBillingPeriod.Text)) = 7) then PaintPPTreeView(cbBillingPeriod.Text); end; // cbBillingPeriodChange. procedure TMainForm.SetSelectedBatchID( Value: integer ); begin if Value <> Null then begin SelectedBatchID := Value; end; end; // SetSelectedBatchID. procedure TMainForm.SetBatchInit( Value: integer ); begin if Value <> Null then begin MainForm.BATCHSTAT_INIT := Value; end; end; // SetBatchInit. procedure TMainForm.SetBatchImport( Value: integer ); begin if Value <> Null then begin MainForm.BATCHSTAT_IMPORT := Value; end; end; // SetBatchImport. procedure TMainForm.SetBatchCerr( Value: integer ); begin if Value <> Null then begin MainForm.BATCHSTAT_CERRS := Value; end; end; // SetBatchCerr. procedure TMainForm.SetBatchCpass( Value: integer ); begin if Value <> Null then begin MainForm.BATCHSTAT_CPASS := Value; end; end; // SetBatchCpass. procedure TMainForm.SetBatchTransns( Value: integer ); begin if Value <> Null then begin MainForm.BATCHSTAT_TRANSNS := Value; end; end; // SetBatchTransns. procedure TMainForm.SetBatchAdminRvw( Value: integer ); begin if Value <> Null then begin MainForm.BATCHSTAT_ADMINRVW := Value; end; end; // SetBatchAdminRvw. procedure TMainForm.SetBatchAdminOk( Value: integer ); begin if Value <> Null then begin MainForm.BATCHSTAT_ADMINOK := Value; end; end; // SetBatchAdminOk. procedure TMainForm.SetBatchPayExt( Value: integer ); begin if Value <> Null then begin MainForm.BATCHSTAT_PAYEXP := Value; end; end; // SetBatchPayExt. procedure TMainForm.SetBatchDcheck( Value: integer ); begin if Value <> Null then begin MainForm.BATCHSTAT_DCHECK := Value; end; end; // SetBatchDcheck. procedure TMainForm.SetBatchDcheckOk( Value: integer ); begin if Value <> Null then begin MainForm.BATCHSTAT_DCHECKOK := Value; end; end; // SetBatchDcheckOk. procedure TMainForm.SetBatchReleased( Value: integer ); begin if Value <> Null then begin MainForm.BATCHSTAT_RELEASED := Value; end; end; // SetBatchReleased. procedure TMainForm.SetGSTRate( Value: real ); begin if Value <> Null then begin MainForm.APPLIEDGSTRATE := Value; end; end; // SetGSTRate. procedure TMainForm.SetCompletedFilter(const Value: TStringList); begin if TabCompleted.TabVisible then begin qCompletedPayable.Close; qCompletedPayable.SQL := Value; qCompletedPayable.Open; PageControl1.ActivePage := TabCompleted; end; end; // SetCompletedFilter. procedure TMainForm.SetTotalIncGST(const Value: currency); begin if TabInitiate.Visible and edtTotalPayable.Visible then begin edtTotalPayable.Value := Value; end; end; // SetTotalIncGST. procedure TMainForm.SetTotalGST(const Value: currency); begin if TabInitiate.Visible and edtGST.Visible then begin edtGST.Value := Value; end; end; // SetTotalGST. procedure TMainForm.GetUserName(var sUserName: string); var Reg : TRegistry; lFound : Boolean; sTempUserName : string; begin sTempUserName := 'unknown'; lFound := False; Reg := TRegistry.Create; Reg.RootKey := HKEY_LOCAL_MACHINE; // Windows 95/98. if Reg.OpenKeyReadOnly('\Network\Logon') then begin if Reg.ValueExists('Username') then begin try sTempUserName := Reg.ReadString('Username'); lFound := True; except lFound := False; end; // try..except. end; // if Reg.ValueExists('Username') then. end; // if Reg.OpenKeyReadOnly('\Network\Logon') then. (Windows 95/98) // Windows NT. if not lFound then begin Reg.RootKey := HKEY_CURRENT_USER; if Reg.OpenKeyReadOnly('\Volatile Environment') then begin if Reg.ValueExists('LOGIN_NAME') then begin // Netware networking variables. try // Netware networking variables. sTempUserName := Reg.ReadString('LOGIN_NAME'); lFound := True; except lFound := False; end; // try..except. end // if Reg.ValueExists('LOGIN_NAME') then. else if Reg.ValueExists('NWUSERNAME') then begin // Netware networking variables. try sTempUserName := Reg.ReadString('NWUSERNAME'); lFound := True; except lFound := False; end; // try..except. end; // if Reg.ValueExists('LOGIN_NAME') then. end; end; // if not lFound then. (Windows NT) Reg.Free; // Windows 2000. if not lFound then CheckEnvironment(sTempUserName, lFound); if not lFound then sTempUserName := 'unknown'; sUserName := sTempUserName; end; // GetUserName. procedure TMainForm.PaintPPTreeView(const sBillingPeriod: string); const White_ = 0; Yellow_ = 2; Red_ = 4; Green_ = 6; var slQueryStringList : TStringList; RootName : string; NodeName : string; MyTreeNode1 : TTreeNode; MyTreeNode2 : TTreeNode; MyImageIndex : integer; NodeImageIndex : integer; ThisState : integer; begin slQueryStringList := TStringList.Create; slQueryStringList.Add('SELECT tDataSourceType.DataSource, BH.ID, '+ 'tBatchType.ProgramID, '); slQueryStringList.Add(' tBatchType.Description, BH.BatchStatusID, '+ 'tBatchType.ID AS BatchTypeID'); slQueryStringList.Add('FROM (tBatchType LEFT JOIN tDataSourceType '+ 'ON tBatchType.DataSourceTypeID = tDataSourceType.ID)'); slQueryStringList.Add('LEFT JOIN (SELECT tBatch.BatchTypeID AS BTypeID, '+ 'tBatch.BatchStatusID, tBatch.ID'); slQueryStringList.Add(' FROM tBatch'); slQueryStringList.Add(' WHERE tBatch.BillingPeriod LIKE '''+ sBillingPeriod+''') AS BH ON tBatchType.ID = BH.BTypeID'); slQueryStringList.Add('WHERE tBatchType.Active = 1 OR BH.ID IS NOT NULL'); slQueryStringList.Add('ORDER BY tDataSourceType.DataSource, '+ 'tBatchType.Description'); with qTree do begin Close; SQL := slQueryStringList; Open; end; // qTree. TreeViewBeingUpdated := True; PPTreeView.Items.BeginUpdate; MyTreeNode1 := PPTreeView.Items[0]; // Initiate only. RootName := ''; NodeImageIndex := Green_; // Initiate only. PPTreeView.Items.Clear; // Add the invoice entry part (at the top of the tree). with PPTreeView.Items do begin with qTree do begin while not Eof do begin if not VarIsNull(FieldValues['DataSource']) and (((Pos('Semi Manual',FieldValues['DataSource'])>0) and aAccess[IMPORTMANUAL]) or ((Pos('Keyed',FieldValues['DataSource'])>0) and aAccess[IMPORTKEYED]) or ((Pos('Automatic',FieldValues['DataSource'])>0) and aAccess[IMPORTAUTO])) then begin if (FieldByName('DataSource').AsString <> RootName) then begin if (Length(RootName) > 0) then begin MyTreeNode1.ImageIndex := NodeImageIndex; MyTreeNode1.SelectedIndex := NodeImageIndex + 1; end; // if (Length(RootName) > 0) then begin. RootName := FieldByName('DataSource').AsString; MyTreeNode1 := Add(nil, RootName); NodeImageIndex := Green_; end; // if (qTree['DataSource'] <> RootName) then begin. NodeName := FieldByName('Description').AsString; if not VarIsNull(FieldValues['id']) then begin NodeName := NodeName +' '+ IntToStr(FieldByName('id').AsInteger); end; MyTreeNode2 := AddChild(MyTreeNode1,NodeName); if VarIsNull(FieldValues['BatchStatusID']) then begin ThisState := 0; end else begin ThisState := FieldValues['BatchStatusID']; end; // if VarIsNull(qTree['BatchStatusID']) then begin. case ThisState of 0: MyImageIndex := Yellow_; 11..99: MyImageIndex := Green_; else MyImageIndex := Red_; end; // case ThisState of. MyTreeNode2.ImageIndex := MyImageIndex; MyTreeNode2.SelectedIndex := MyImageIndex + 1; if (NodeImageIndex = Green_) then NodeImageIndex := MyImageIndex else if (NodeImageIndex = Yellow_) and (MyImageIndex <> Green_) then NodeImageIndex := MyImageIndex; end; // not empty 'DataSource'. Next; end; // while not qryTree.Eof do begin. end; // qTree. if (Length(RootName) > 0) then begin MyTreeNode1.ImageIndex := NodeImageIndex; MyTreeNode1.SelectedIndex := NodeImageIndex + 1; end; // if (Length(RootName) > 0) then begin. // Now add the remaining elements of the tree. MyImageIndex := White_; if aAccess[BATCHREVIEW] then begin MyTreeNode1 := Add(nil, 'Batch Review'); MyTreeNode1.ImageIndex := MyImageIndex; MyTreeNode1.SelectedIndex := MyImageIndex + 1; if aAccess[VIEWDATA] or aAccess[PROCESSIMPORT] then begin MyTreeNode2 := AddChild(MyTreeNode1,'Critical Errors'); MyTreeNode2.ImageIndex := MyImageIndex; MyTreeNode2.SelectedIndex := MyImageIndex + 1; MyTreeNode2 := AddChild(MyTreeNode1,'Adjustment Review'); MyTreeNode2.ImageIndex := MyImageIndex; MyTreeNode2.SelectedIndex := MyImageIndex + 1; MyTreeNode2 := AddChild(MyTreeNode1,'Data Collection'); MyTreeNode2.ImageIndex := MyImageIndex; MyTreeNode2.SelectedIndex := MyImageIndex + 1; MyTreeNode2 := AddChild(MyTreeNode1,'Fees'); MyTreeNode2.ImageIndex := MyImageIndex; MyTreeNode2.SelectedIndex := MyImageIndex + 1; MyTreeNode2 := AddChild(MyTreeNode1,'Dubious Checks'); MyTreeNode2.ImageIndex := MyImageIndex; MyTreeNode2.SelectedIndex := MyImageIndex + 1; end; // VIEWDATA or PROCESSIMPORT. end; // BATCHREVIEW. if aAccess[REFDATA] or aAccess[REFDATAVIEW] then begin MyTreeNode1 := Add(nil, 'Reference Data'); MyTreeNode1.ImageIndex := MyImageIndex; MyTreeNode1.SelectedIndex := MyImageIndex + 1; if aAccess[REFDATAVIEW] then begin MyTreeNode2 := AddChild(MyTreeNode1,'View'); MyTreeNode2.ImageIndex := MyImageIndex; MyTreeNode2.SelectedIndex := MyImageIndex + 1; end; // REFDATAVIEW. if aAccess[REFDATA] then begin MyTreeNode2 := AddChild(MyTreeNode1,'Edit'); MyTreeNode2.ImageIndex := MyImageIndex; MyTreeNode2.SelectedIndex := MyImageIndex + 1; end; // REFDATA. end; // REFDATA or REFDATAVIEW. MyTreeNode1 := Add(nil, 'Completed Data'); MyTreeNode1.ImageIndex := MyImageIndex; MyTreeNode1.SelectedIndex := MyImageIndex + 1; end; // with PPTreeView.Items do begin. mnuToolsSelectMainDatabaseItem.Visible := aAccess[DEVELOPER]; mnuToolsSelectTestDatabaseItem.Visible := mnuToolsSelectMainDatabaseItem.Visible; PPTreeView.Items.EndUpdate; if not InitiatingForm then begin PPTreeView.SetFocus; end; // if not InitiatingForm then begin. TreeViewBeingUpdated := False; ClearTabSheets; slQueryStringList.Free; end; // PaintPPTreeView. procedure TMainForm.PPTreeViewChange(Sender: TObject; Node: TTreeNode); var slStringList : TStringList; NodeName : string; lFound : boolean; DisplayTab : boolean; // ParentNodeName : string; begin if not TreeViewBeingUpdated then begin DisplayTab := True; NodeName := Node.Text; if not qTree.Active then begin slStringList := TStringList.Create; slStringList.Add('SELECT tDataSourceType.DataSource, BH.ID, '+ 'tBatchType.ProgramID, '); slStringList.Add(' tBatchType.Description, BH.BatchStatusID, '+ 'tBatchType.ID AS BatchTypeID'); slStringList.Add('FROM (tBatchType LEFT JOIN tDataSourceType '+ 'ON tBatchType.DataSourceTypeID = tDataSourceType.ID)'); slStringList.Add('LEFT JOIN (SELECT tBatch.BatchTypeID AS BTypeID, '+ 'tBatch.BatchStatusID, tBatch.ID'); slStringList.Add(' FROM tBatch'); slStringList.Add(' WHERE tBatch.BillingPeriod LIKE '''+ cbBillingPeriod.Text+''') AS BH ON tBatchType.ID = BH.BTypeID'); slStringList.Add('ORDER BY tDataSourceType.DataSource, '+ 'tBatchType.Description'); qTree.Close; qTree.SQL := slStringList; qTree.Open; slStringList.Free; end; // if not qTree.Active then begin. if (NodeName = 'Batch Review') then begin if qBatchSummary.Active then begin qBatchSummary.Close; qBatchSummary.Open; end else begin OpenTab(TabBatchStatusSummary); end; // if qryBatchSummary.Active then begin. PageControl1.ActivePage := TabBatchStatusSummary; PageControl1.Visible := True; end // Batch Review. else if (NodeName = 'Critical Errors') then begin if qCESummary.Active then begin qCESummary.Close; qCESummary.Open; end else begin OpenTab(TabCriticalSummary); end; // if qryCESummary.Active then begin. PageControl1.ActivePage := TabCriticalSummary; PageControl1.Visible := True; end // Critical Errors. else if (NodeName = 'Adjustment Review') then begin if qAdjustment.Active then begin qAdjustment.Close; qAdjustment.Open; end else begin OpenTab(TabAdjustment); end; // if qAdjustment.Active then begin. PageControl1.ActivePage := TabAdjustment; PageControl1.Visible := True; end // Adjustment Review. else if (NodeName = 'Dubious Checks') then begin if qDubSummary.Active then begin qDubSummary.Close; qDubSummary.Open; end else begin OpenTab(TabDubiousSummary); end; // if qDubSummary.Active then begin. PageControl1.ActivePage := TabDubiousSummary; PageControl1.Visible := True; end // Dubious Checks. else if (NodeName = 'Fees') then begin if qAdminSummary.Active then begin qAdminSummary.Close; qAdminSummary.Open; end else begin OpenTab(TabAdminSummary); end; // if qAdminSummary.Active then begin. PageControl1.ActivePage := TabAdminSummary; PageControl1.Visible := True; end // Fees. else if (NodeName = 'Data Collection') then begin if qTransactions.Active then begin qTransactions.Close; qTransactions.Open; if qTransactionsAuto.Active then begin qTransactionsAuto.Close; qTransactionsAuto.Open; end; end else if (Length(Trim(cbBatchTransactions.Text)) = 0) then begin if rbBatchTransactions.Checked then MessageDlg('The tDataCollection table is empty.', mtInformation, [mbOk], 0) else MessageDlg('No Automatic data in tDataCollection.', mtInformation, [mbOk], 0); DisplayTab := False; end else begin OpenTab(TabTransactionSummary); end; // if qTransactions.Active then begin. if DisplayTab then begin PageControl1.ActivePage := TabTransactionSummary; PageControl1.Visible := True; end; end // Data Collection. else if (NodeName = 'View') and (Node.Parent.Text = 'Reference Data') then begin if qServices.Active then begin qServices.Close; qServices.Open; end else begin OpenTab(TabServices); end; // if qServices.Active then begin. PageControl1.ActivePage := TabServices; PageControl1.Visible := True; end // View - Reference Data. else if (NodeName = 'Completed Data') then begin if qCompletedPayable.Active then begin qCompletedPayable.Close; qCompletedPayable.Open; end else begin if (TabCompleted.TabIndex = -1) then begin FilterForm := TFilterForm.Create(SELF); FilterForm.BillingPeriod := cbBillingPeriod.Text; // FilterForm.SQLText := SetCompletedBatchIDValues; end; OpenTab(TabCompleted); end; // if qServices.Active then begin. PageControl1.ActivePage := TabCompleted; PageControl1.Visible := True; Application.ProcessMessages; // FilterForm.Show; end // Completed. else if (NodeName = 'Edit') and (Node.Parent.Text = 'Reference Data') then begin EditSystemTables; end // Reference Data - Edit. else begin with qTree do // Entering Invoice data... lFound := Locate('DataSource', NodeName, [loCaseInsensitive]); if lFound then begin // A menu choice made - no further action. nProgramID := 0; end else begin with qTree do lFound := Locate('Description', NodeName, [loCaseInsensitive]); if not lFound then begin qTree.First; while not qTree.Eof do begin if not VarIsNull(qTree['id']) then begin if (NodeName = qTree.FieldByName('Description').AsString+' '+ IntToStr(qTree['id'])) then begin lFound := True; break; end; // This is this one. end; // if not VarIsNull(qTree['id']) then begin. qTree.Next; end; // while not qTree.Eof do begin. end; // if not lFound then begin. if lFound then begin // Look to the inputing routines etc. nProgramID := qTree.FieldByName('ProgramID').AsInteger; InitiateNode := Node; ProcessBilling; RefreshTreeItem(Node); // Ensures BatchID is in the NodeName. PageControl1.Visible := True; end else begin nProgramID := 0; end; // if lFound then begin. end; // if not lFound then begin. end; // if 'DataSource' lFound. end; // if not TreeViewBeingUpdated then begin. end; // PPTreeViewChange. procedure TMainForm.RefreshTreeItem(const Node: TTreeNode); var NodeName : string; lFound : boolean; begin // Adds BatchID to the tree entry when a new batch initiated. NodeName := Node.Text; with qTree do lFound := Locate('Description', NodeName, [loCaseInsensitive]); if lFound and not VarIsNull(qTree['id']) then begin NodeName := NodeName +' '+ IntToStr(qTree['id']); Node.Text := NodeName; end; // if lFound then begin. end; // RefreshTreeItem. procedure TMainForm.ProcessBilling; begin sInitiateBatchTypeID := IntToStr(qTree.FieldByName('BatchTypeID').AsInteger); sInitiateBatchID := ''; if VarIsNull(qTree['BatchStatusID']) or lNewManualInvoice then lNewManualInvoice := False else begin sInitiateBatchID := IntToStr(qTree.FieldByName('ID').AsInteger); end; OpenTab(TabInitiate); InitiateScreen; if not lNewBatch then PopulateBatchScreen; end; // ProcessBilling. procedure TMainForm.DisplayInitiateEditBoxes; begin if lManualEntry then begin RxDBGridBatch.Visible := False; btnNewBatch.Visible := aAccess[PROCESSIMPORT] and not lManualMultipleEntry; btnAddTransaction.Visible := aAccess[PROCESSIMPORT]; btnAddTransaction.Enabled := (not lNewBatch); dtpIssueDate.Date := Date; // Issue Date. end else begin RxDBGridBatch.Visible := True; btnNewBatch.Visible := False; if lAutomaticEntry and not (lSACCEntry or lTelmaxEntry or lAnnualChargesEntry) then begin btnAddTransaction.Visible := False; btnAddTransaction.Enabled := False; end else begin btnAddTransaction.Visible := aAccess[PROCESSIMPORT]; btnAddTransaction.Enabled := (not lNewBatch); end; ShowPreviousBatch; end; RxDBGridMultiManual.Visible := False; navMultiManual.Visible := False; lblBatchID.Visible := True; edtBatchID.Visible := True; edtBatchID.Text := sInitiateBatchID; dblAccountNoName.Visible := qBatchType.FieldByName('AccountNo').AsBoolean; edtAccountNo.Text := qBatchType.FieldByName('ValidAccountNo').AsString; edtAccountNo.Visible := dblAccountNoName.Visible; lblTenderNo.Visible := qBatchType.FieldByName('TenderNumber').AsBoolean; edtTenderNo.Text := qBatchType.FieldByName('ValidTenderNo').AsString; edtTenderNo.Visible := lblTenderNo.Visible; edtTenderNo.ReadOnly := not (lManualEntry or lAutomaticEntry); dblInvoiceNoName.Visible := qBatchType.FieldByName('InvoiceNo').AsBoolean; edtInvoiceNo.Text := ''; edtInvoiceNo.Visible := dblInvoiceNoName.Visible; dblTotalRevenueName.Visible := qBatchType.FieldByName('TotalRevenue').AsBoolean; edtTotalRevenue.Value := 0; edtTotalRevenue.Visible := dblTotalRevenueName.Visible; dblTotalPayableName.Visible := qBatchType.FieldByName('TotalPayable').AsBoolean; edtTotalPayable.Value := 0; edtTotalPayable.Visible := dblTotalPayableName.Visible; dblTotalCreditsName.Visible := qBatchType.FieldByName('TotalCredits').AsBoolean; edtTotalCredits.Value := 0; edtTotalCredits.Visible := dblTotalCreditsName.Visible; dblOpeningBalanceName.Visible := qBatchType.FieldByName('OpeningBalance').AsBoolean; edtOpeningBalance.Value := 0; edtOpeningBalance.Visible := dblOpeningBalanceName.Visible; dblPaymentsReceivedName.Visible := qBatchType.FieldByName('PaymentsReceived').AsBoolean; edtPaymentsReceived.Text := '0'; edtPaymentsReceived.Visible := dblPaymentsReceivedName.Visible; dblTotalAdjustmentsName.Visible := qBatchType.FieldByName('TotalAdjustments').AsBoolean; edtTotalAdjustments.Value := 0; edtTotalAdjustments.Visible := dblTotalAdjustmentsName.Visible; dblGSTName.Visible := qBatchType.FieldByName('GST').AsBoolean; edtGST.Value := 0; edtGST.Visible := dblGSTName.Visible; dblGSTAdjustmentsName.Visible := qBatchType.FieldByName('GSTAdjustment').AsBoolean; edtGSTAdjustments.Value := 0; edtGSTAdjustments.Visible := dblGSTAdjustmentsName.Visible; lblToBePaidBy.Visible := qBatchType.FieldByName('PayByDate').AsBoolean; dtpPayByDate.Visible := lblToBePaidBy.Visible; dblPaymentDateName.Visible := qBatchType.FieldByName('PaymentDate').AsBoolean; dtpPaymentDate.Visible := dblPaymentDateName.Visible; dblIssueDate.Visible := qBatchType.FieldByName('IssueDate').AsBoolean; dtpIssueDate.Visible := dblIssueDate.Visible; dblRoundingName.Visible := qBatchType.FieldByName('Rounding').AsBoolean; edtRounding.Value := 0; edtRounding.Visible := dblRoundingName.Visible; lblRentStartDate.Visible := qBatchType.FieldByName('RentDates').AsBoolean; dtpRentStartDate.Visible := lblRentStartDate.Visible; lblRentEndDate.Visible := lblRentStartDate.Visible; dtpRentEndDate.Visible := lblRentStartDate.Visible; lblCallStartDate.Visible := qBatchType.FieldByName('CallDates').AsBoolean; dtpCallStartDate.Visible := lblCallStartDate.Visible; lblCallEndDate.Visible := lblCallStartDate.Visible; dtpCallEndDate.Visible := lblCallStartDate.Visible; if lNewBatch then begin // Always save Rent & Call dates based on the Billing Period (Year/Month) // even if not selected for display in tBatchType. dtpRentStartDate.Date := EncodeDate(StrToInt(Copy(cbBillingPeriod.Text,1,4)), StrToInt(Copy(cbBillingPeriod.Text,6,2)), 1); dtpRentEndDate.Date := LastDayInMonth(dtpRentStartDate.Date); dtpCallStartDate.Date := dtpRentStartDate.Date; dtpCallEndDate.Date := dtpRentEndDate.Date; end; // if lNewBatch then begin. edtOrderNumber.Text := ''; dblOrderNumberName.Visible := qBatchType.FieldByName('OrderNumber').AsBoolean; edtOrderNumber.Visible := dblOrderNumberName.Visible; edtRequisitionNumber.Text := ''; dblRequisitionNumberName.Visible := qBatchType.FieldByName('RequisitionNumber').AsBoolean; edtRequisitionNumber.Visible := dblRequisitionNumberName.Visible; TabInitiate.SetFocus; // Set focus to the first editable object on screen. if edtInvoiceNo.Visible then edtInvoiceNo.SetFocus else if edtOpeningBalance.Visible then edtOpeningBalance.SetFocus else if edtPaymentsReceived.Visible then edtPaymentsReceived.SetFocus else if edtTotalRevenue.Visible then edtTotalRevenue.SetFocus else if edtTotalCredits.Visible then edtTotalCredits.SetFocus else if edtGST.Visible then edtGST.SetFocus else if edtGSTAdjustments.Visible then edtGSTAdjustments.SetFocus else if edtTotalAdjustments.Visible then edtTotalAdjustments.SetFocus else if edtTotalPayable.Visible then edtTotalPayable.SetFocus; end; // DisplayInitiateEditBoxes. procedure TMainForm.DisplayInitiateGrid; begin // lManualEntry only with multiple regular transactions (eg. radio licences). RxDBGridBatch.Visible := False; btnNewBatch.Visible := False; btnAddTransaction.Visible := btnNewBatch.Visible; btnAddTransaction.Enabled := (not lNewBatch); btnOkay.Visible := aAccess[PROCESSIMPORT]; btnNewBatch.Visible := False; lblBatchID.Visible := True; edtBatchID.Visible := True; dblAccountNoName.Visible := False; edtAccountNo.Visible := dblAccountNoName.Visible; lblTenderNo.Visible := False; edtTenderNo.Visible := lblTenderNo.Visible; dblInvoiceNoName.Visible := False; edtInvoiceNo.Visible := dblInvoiceNoName.Visible; dblTotalRevenueName.Visible := False; edtTotalRevenue.Visible := dblTotalRevenueName.Visible; dblTotalPayableName.Visible := False; edtTotalPayable.Visible := dblTotalPayableName.Visible; dblTotalCreditsName.Visible := False; edtTotalCredits.Visible := dblTotalCreditsName.Visible; dblOpeningBalanceName.Visible := False; edtOpeningBalance.Visible := dblOpeningBalanceName.Visible; dblPaymentsReceivedName.Visible := False; edtPaymentsReceived.Visible := False; dblTotalAdjustmentsName.Visible := False; edtTotalAdjustments.Visible := False; dblGSTName.Visible := False; edtGST.Visible := False; dblGSTAdjustmentsName.Visible := False; edtGSTAdjustments.Visible := False; lblToBePaidBy.Visible := False; dtpPayByDate.Visible := False; dblPaymentDateName.Visible := False; dtpPaymentDate.Visible := False; dblIssueDate.Visible := False; dtpIssueDate.Visible := False; dblRoundingName.Visible := False; edtRounding.Visible := False; lblRentStartDate.Visible := False; dtpRentStartDate.Visible := False; lblRentEndDate.Visible := False; dtpRentEndDate.Visible := False; lblCallStartDate.Visible := False; dtpCallStartDate.Visible := False; lblCallEndDate.Visible := False; dtpCallEndDate.Visible := False; dblOrderNumberName.Visible := False; edtOrderNumber.Visible := False; dblRequisitionNumberName.Visible := False; edtRequisitionNumber.Visible := False; TabInitiate.SetFocus; navMultiManual.Visible := True; RxDBGridMultiManual.Visible := True; // Set focus to the Grid. RxDBGridMultiManual.SetFocus; end; // DisplayInitiateGrid. procedure TMainForm.InitiateScreen; var qryStringList : TStringList; sNodeName : string; sParentNodeName : string; begin // Note: Any changes to the button and edit fields made here must also be // reflected in the PopulateBatchScreen() and btnAddTransactionClick() procedures. sParentNodeName := UpperCase(InitiateNode.Parent.Text); sNodeName := UpperCase(InitiateNode.Text); lNewBatch := (Length(sInitiateBatchID) = 0); // New batch if BatchID is Null. sNewBatchTypeID := sInitiateBatchTypeID; lManualEntry := ((nProgramID >= PMIN_MANUAL_INVOICE) AND (nProgramID <= PMAX_MANUAL_INVOICE)); {// 110918 Above replaces below to remove dependence on parent name -RJC. lManualEntry := (Pos('KEYED',sParentNodeName)>0) OR (nProgramID = 3001); // 100221 Above replaces below to remove dependence on parent name -RJC. lManualEntry := (Pos('KEYED',sParentNodeName)>0); } lAutomaticEntry := ((nProgramID >= PMIN_AUTOMATIC_INVOICE) AND (nProgramID <= PMAX_AUTOMATIC_INVOICE)); {// 110918 Above replaces below to remove dependence on parent name -RJC. lAutomaticEntry := (Pos('AUTOMATIC',sParentNodeName)>0); } if lAutomaticEntry then begin if (Pos('VICTRACK ',sNodeName)=0) then begin lSACCEntry := False; lTelmaxEntry := False; lAnnualChargesEntry := ((nProgramID >= PMIN_AUTOMATIC_ANNUAL) AND (nProgramID <= PMAX_AUTOMATIC_ANNUAL)); {// 100221 Above replaces below to remove dependence on parent name -RJC. lAnnualChargesEntry := (Pos('ANNUAL',sNodeName)>0) OR ((nProgramID > 2000) AND (nProgramID < 3001)); } end else begin lSACCEntry := (Pos(' DIVERS',sNodeName)>0); // SACC = Call Diversion. lTelmaxEntry := (Pos(' RENT',sNodeName)>0); // PABX rent & calls (data). lAnnualChargesEntry := False; end; end // lAutomaticEntry. else begin lSACCEntry := False; lTelmaxEntry := False; lAnnualChargesEntry := False; end; // lAutomaticEntry. qryStringList := TStringList.Create; if lManualEntry then begin // Test if all manual entries are edited in RxDBGridMultiManual // or if single transaction entry only is to be used. qryStringList.Add('SELECT MultiManualEntry'); qryStringList.Add('FROM tBatchType'); qryStringList.Add('WHERE tBatchType.ID = ' + sNewBatchTypeID); qry.Close; qry.SQL := qryStringList; qry.Open; if VarIsNull(qry['MultiManualEntry']) then lManualMultipleEntry := False else lManualMultipleEntry := qry.FieldByName('MultiManualEntry').AsBoolean; qry.Close; qry.SQL.Clear; end else begin lManualMultipleEntry := False; end; // lManualEntry. qryStringList.Clear; qryStringList.Add('SELECT tValidAccountNumber.AccountNo AS "ValidAccountNo", '+ 'tValidAccountNumber.ID AS "ValidAccountID", '+ 'tValidAccountNumber.TenderNo as "ValidTenderNo", tBatchType.*'); qryStringList.Add('FROM tValidAccountNumber'); qryStringList.Add('RIGHT JOIN tBatchType '+ 'ON tValidAccountNumber.BatchTypeID = tBatchType.ID'); qryStringList.Add('WHERE (tBatchType.ID = ' + sNewBatchTypeID + ')'); qBatchType.Close; qBatchType.SQL := qryStringList; qBatchType.Open; qryStringList.Free; DisplayInitiateEditBoxes; end; // InitiateScreen. procedure TMainForm.PopulateBatchScreen; var qryStringList : TStringList; sBatch : string; nBatchStatusNumber : integer; begin // Note: Any changes to the button and edit fields made here must also be // reflected in the InitiateScreen() and btnAddTransactionClick() procedures. sBatch := sInitiateBatchID; qryStringList := TStringList.Create; qryStringList.Add('SELECT tBatch.*, tValidAccountNumber.AccountNo, '+ 'tValidAccountNumber.ID AS ValidAccountNoID'); qryStringList.Add('FROM tBatch'); qryStringList.Add('LEFT JOIN tBatchType '+ 'ON tBatch.BatchTypeID = tBatchType.ID'); qryStringList.Add('LEFT JOIN tValidAccountNumber '+ 'ON tBatchType.ID = tValidAccountNumber.BatchTypeID'); qryStringList.Add('WHERE tBatch.ID = ' + sBatch); qry.Close; qry.SQL := qryStringList; qry.Open; qryStringList.Free; edtBatchID.Text := sBatch; nBatchStatusNumber := qry.FieldByName('BatchStatusID').AsInteger; btnAddTransaction.Enabled := (not lAutomaticEntry or lSACCEntry or lTelmaxEntry or lAnnualChargesEntry) and ((nBatchStatusNumber >= BATCHSTAT_INIT) and (nBatchStatusNumber < BATCHSTAT_TRANSNS)); if edtAccountNo.Visible and not VarIsNull(qry['AccountNo']) then edtAccountNo.Text := qry.FieldByName('AccountNo').AsString; if edtTenderNo.Visible and not VarIsNull(qry['TenderNo']) then edtTenderNo.Text := qry.FieldByName('TenderNo').AsString; if edtInvoiceNo.Visible and (not VarIsNull(qry['InvoiceNo'])) then edtInvoiceNo.Text := qry['InvoiceNo']; if dblTotalRevenueName.Visible and (not VarIsNull(qry['TotalRevenue'])) then edtTotalRevenue.Value := qry['TotalRevenue']; if dblTotalPayableName.Visible and not VarIsNull(qry['TotalPayable']) then edtTotalPayable.Value := qry.FieldByName('TotalPayable').AsCurrency; if dblTotalCreditsName.Visible and (not VarIsNull(qry['TotalCredits'])) then edtTotalCredits.Value := qry['TotalCredits']; if dblOpeningBalanceName.Visible and (not VarIsNull(qry['OpeningBalance'])) then edtOpeningBalance.Value := qry['OpeningBalance']; if dblPaymentsReceivedName.Visible and (not VarIsNull(qry['PaymentsReceived'])) then // edtPaymentsReceived.Value := qry['PaymentsReceived']; edtPaymentsReceived.Text := qry['PaymentsReceived']; if dblTotalAdjustmentsName.Visible and (not VarIsNull(qry['TotalAdjustments'])) then edtTotalAdjustments.Value := qry['TotalAdjustments']; if dblGSTName.Visible and (not VarIsNull(qry['GSTAmount'])) then edtGST.Value := qry['GSTAmount']; if dblGSTAdjustmentsName.Visible and (not VarIsNull(qry['GSTAdjustment'])) then edtGSTAdjustments.Value := qry['GSTAdjustment']; if dtpPayByDate.Visible and (not VarIsNull(qry['PayByDate'])) then dtpPayByDate.Date := qry['PayByDate']; if dtpPaymentDate.Visible and (not VarIsNull(qry['PaymentDate'])) then dtpPaymentDate.Date := qry['PaymentDate']; if dtpIssueDate.Visible and (not VarIsNull(qry['IssueDate'])) then dtpIssueDate.Date := qry['IssueDate']; if edtRounding.Visible and (not VarIsNull(qry['Rounding'])) then edtRounding.Value := qry['Rounding']; if dtpRentStartDate.Visible then begin if (not VarIsNull(qry['RentStartDate'])) then dtpRentStartDate.Date := qry['RentStartDate']; if (not VarIsNull(qry['RentEndDate'])) then dtpRentEndDate.Date := qry['RentEndDate']; end; // dtpRentStartDate.Visible. if dtpCallEndDate.Visible then begin if (not VarIsNull(qry['CallsStartDate'])) then dtpCallStartDate.Date := qry['CallsStartDate']; if (not VarIsNull(qry['CallsEndDate'])) then dtpCallEndDate.Date := qry['CallsEndDate']; end; // dtpRentStartDate.Visible. if edtOrderNumber.Visible and (not VarIsNull(qry['OrderNumber'])) then edtOrderNumber.Text := qry['OrderNumber']; if edtRequisitionNumber.Visible and (not VarIsNull(qry['RequisitionNumber'])) then edtRequisitionNumber.Text := qry['RequisitionNumber']; // Set focus to the next editable object. if edtInvoiceNo.Visible then edtInvoiceNo.SetFocus else if edtOpeningBalance.Visible then edtOpeningBalance.SetFocus else if edtPaymentsReceived.Visible then edtPaymentsReceived.SetFocus else if edtTotalRevenue.Visible then edtTotalRevenue.SetFocus else if edtTotalCredits.Visible then edtTotalCredits.SetFocus else if edtGST.Visible then edtGST.SetFocus else if edtGSTAdjustments.Visible then edtGSTAdjustments.SetFocus else if edtTotalAdjustments.Visible then edtTotalAdjustments.SetFocus else if edtTotalPayable.Visible then edtTotalPayable.SetFocus; end; // PopulateBatchScreen. procedure TMainForm.ShowPreviousBatch; var qryStringList : TStringList; sCurrentBatchID : string; begin sCurrentBatchID := Trim(sInitiateBatchID); qryStringList := TStringList.Create; qryStringList.Add('SELECT TOP 2 tBatch.ID as "Batch", BillingPeriod AS "Period",'+ 'tValidAccountNumber.AccountNo, InvoiceNo as "Invoice", TotalPayable, '+ 'OpeningBalance, PaymentsReceived,'); qryStringList.Add('TotalAdjustments, GSTAmount, GSTAdjustment'); qryStringList.Add('FROM tBatch'); qryStringList.Add('LEFT JOIN tValidAccountNumber '+ 'ON tBatch.SupplierAccountID = tValidAccountNumber.ID'); qryStringList.Add('WHERE tBatch.BatchTypeID = ' + Trim(sInitiateBatchTypeID)); if (Length(sCurrentBatchID) > 0) and (StrToInt(sCurrentBatchID) > 0) then qryStringList.Add('AND tBatch.ID <> ' + sCurrentBatchID); qryStringList.Add('ORDER BY tBatch.id DESC'); qLastBatch.Close; qLastBatch.SQL := qryStringList; qLastBatch.Open; if not RxDBGridBatch.Visible then begin RxDBGridBatch.Visible := True; end; // qryStringList.Free; end; // ShowPreviousBatch. procedure TMainForm.CheckEnvironment(var sUserName : String; var lSuccessful: boolean); var pcEnvironmentName: PChar; pcValue: PChar; nLenValue: Integer; begin lSuccessful := False; nLenValue := 20; pcEnvironmentName := 'USERNAME'; pcValue := StrAlloc(nLenValue); nLenValue := GetEnvironmentVariable( pcEnvironmentName, pcValue, nLenValue); if nLenValue > 0 then begin sUserName := StrPas(pcValue); lSuccessful := True; end else begin sUserName := ''; end; // if nLenValue > 0 then begin. end; // CheckEnvironment. procedure TMainForm.OpenTab(OpenTabSheet : TTabSheet); var TabSheet : TTabSheet; slStringList : TStringList; begin slStringList := TStringList.Create; TabSheet := OpenTabSheet; if (TabSheet = TabInitiate) then begin TabInitiate.TabVisible := True; // TabInitiate. TabInitiate.Visible := TabInitiate.TabVisible; end else if (TabSheet = TabBatchStatusSummary) then begin TabBatchStatusSummary.TabVisible := True; TabBatchStatusSummary.Visible := True; GetSQL('BatchSummary',slStringList); qBatchSummary.Close; qBatchSummary.SQL := slStringList; qBatchSummary.Open; mnuToolsWebData.Enabled := True; end else if (TabSheet = TabCompleted) then begin TabCompleted.TabVisible := True; TabCompleted.Visible := True; GetSQL('Completed',slStringList); qCompletedPayable.Close; qCompletedPayable.SQL := slStringList; qCompletedPayable.Open; end // TabCompleted. else if (TabSheet = TabCompletedDetail) then begin TabCompletedDetail.TabVisible := True; TabCompletedDetail.Visible := True; GetSQL('CompletedDetail',slStringList); qCompletedDetail.Close; qCompletedDetail.SQL := slStringList; qCompletedDetail.Open; end // TabCompletedDetail. else if (TabSheet = TabCriticalSummary) then begin TabCriticalSummary.TabVisible := True; TabCriticalSummary.Visible := True; btnCEProcess.Visible := aAccess[PROCESSIMPORT]; btnCEReject.Visible := aAccess[PROCESSIMPORT]; GetSQL('CESummary',slStringList); qCESummary.Close; qCESummary.SQL := slStringList; qCESummary.Open; end else if (TabSheet = TabAdjustment) then begin TabAdjustment.TabVisible := True; TabAdjustment.Visible := True; GetSQL('Adjustment',slStringList); qAdjustment.Close; qAdjustment.SQL := slStringList; qAdjustment.Open; end else if (TabSheet = TabDubiousSummary) then begin TabDubiousSummary.TabVisible := True; TabDubiousSummary.Visible := True; lblDESResolution.Visible := aAccess[PROCESSIMPORT]; cbDESResolution.Visible := aAccess[PROCESSIMPORT]; btnDESApply.Visible := aAccess[PROCESSIMPORT]; btnDERApplyAll.Visible := aAccess[PROCESSIMPORT]; btnDERecalculate.Visible := aAccess[PROCESSIMPORT]; btnReleaseRevenue.Visible := aAccess[PROCESSIMPORT]; GetSQL('Dubious',slStringList); qDubSummary.Close; qDubSummary.SQL := slStringList; qDubSummary.Open; qDESResolution.Open; cbDESResolution.KeyValue := qDESResolution['DubiousResolution']; RefreshResolutionChoices; end else if (TabSheet = TabAdminSummary) then begin TabAdminSummary.TabVisible := True; TabAdminSummary.Visible := True; btnAdminAcceptAll.Visible := aAccess[PROCESSIMPORT]; btnAdminReCalculate.Visible := aAccess[PROCESSIMPORT]; GetSQL('AdminSummary',slStringList); qAdminSummary.Close; qAdminSummary.SQL := slStringList; qAdminSummary.Open; end else if (TabSheet = TabExpendRelease) then begin TabExpendRelease.TabVisible := True; TabExpendRelease.Visible := True; btnRelExpenditure.Visible := aAccess[PROCESSIMPORT]; GetSQL('ReleaseTransactns',slStringList); qRelExpService.Close; qRelExpTransactSummary.SQL := slStringList; qRelExpService.Open; qRelExpTransactSummary.Open; RxDBGridRelExpSummaryCellClick(RxDBGridRelExpSummary.Columns[0]); RxDBGridRelExpSummary.SetFocus; end else if (TabSheet = TabTransactionSummary) then begin TabTransactionSummary.TabVisible := True; TabTransactionSummary.Visible := True; if rbBatchTransactions.Checked then begin RxDBGridTransactions.Visible := True; RxDBGridTransactionsAuto.Visible := False; GetSQL('DCTransactions',slStringList); PanelDCExpense.Visible := TestForDCRevenue(StrToInt(cbBatchTransactions.Text)); if PanelDCExpense.Visible and rbDCRevenue.Checked then ViewDCRevenue(slStringList); qTransactions.Close; qTransactions.SQL := slStringList; qTransactions.Open; end else begin RxDBGridTransactions.Visible := False; RxDBGridTransactionsAuto.Visible := True; GetSQL('DCTransactionsAuto',slStringList); PanelDCExpense.Visible := TestForDCRevenue(StrToInt(cbBatchTransactions.Text)); if PanelDCExpense.Visible and rbDCRevenue.Checked then ViewDCRevenue(slStringList); qTransactionsAuto.Close; qTransactionsAuto.SQL := slStringList; qTransactionsAuto.Open; SelectAutoColumnHeaders(StrToInt((cbBatchTransactions.Text))); end; end else if (TabSheet = TabServices) then begin TabServices.TabVisible := True; TabServices.Visible := True; GetSQL('RDServiceID',slStringList); qServices.Close; qServices.SQL := slStringList; qServices.Open; end; PageControl1.Visible := True; PageControl1.ActivePage := TabSheet; Application.ProcessMessages; end; // OpenTab. procedure TMainForm.CloseTab(CloseTabSheet : TTabSheet); var TabSheet : TTabSheet; begin TabSheet := CloseTabSheet; if (TabSheet = TabInitiate) then begin TabInitiate.TabVisible := False; // TabInitiate. TabInitiate.Visible := TabInitiate.TabVisible; qLastBatch.Close; end else if (TabSheet = TabBatchStatusSummary) then begin mnuToolsWebData.Enabled := False; TabBatchStatusSummary.TabVisible := False; // TabBatchStatusSummary. TabBatchStatusSummary.Visible := TabBatchStatusSummary.TabVisible; qBatchSummary.Close; end else if (TabSheet = TabCompleted) then begin if not (FilterForm = nil) then FilterForm.Hide; TabCompleted.TabVisible := False; // TabCompleted. TabCompleted.Visible := TabCompleted.TabVisible; qCompletedPayable.Close; qCompletedInvoice.Close; end else if (TabSheet = TabCompletedDetail) then begin // TabCompletedDetail. TabCompletedDetail.TabVisible := False; TabCompletedDetail.Visible := TabCompletedDetail.TabVisible; qCompletedDetail.Close; end // TabCompletedDetail. else if (TabSheet = TabCriticalSummary) then begin TabCriticalSummary.TabVisible := False; // TabCriticalSummary. TabCriticalSummary.Visible := TabCriticalSummary.TabVisible; qCESummary.Close; TabCriticalResolution.TabVisible := False; // TabCriticalResolution. TabCriticalResolution.Visible := TabCriticalResolution.TabVisible; qCEResolution.Close; end else if (TabSheet = TabCriticalResolution) then begin TabCriticalResolution.TabVisible := False; // TabCriticalResolution. TabCriticalResolution.Visible := TabCriticalResolution.TabVisible; qCEResolution.Close; end else if (TabSheet = TabAdjustment) then begin TabAdjustment.TabVisible := False; // TabAdjustment. TabAdjustment.Visible := TabAdjustment.TabVisible; qAdjustment.Close; end else if (TabSheet = TabDubiousSummary) then begin TabDubiousSummary.TabVisible := False; // TabDubiousSummary. TabDubiousSummary.Visible := TabDubiousSummary.TabVisible; qDubSummary.Close; qDESResolution.Close; TabDubiousDetail.TabVisible := False; // TabDubiousDetail. TabDubiousDetail.Visible := TabDubiousDetail.TabVisible; qDubDetail.Close; end else if (TabSheet = TabDubiousDetail) then begin TabDubiousDetail.TabVisible := False; // TabDubiousDetail. TabDubiousDetail.Visible := TabDubiousDetail.TabVisible; qDubDetail.Close; end else if (TabSheet = TabAdminSummary) then begin TabAdminSummary.TabVisible := False; // TabAdminSummary. TabAdminSummary.Visible := TabAdminSummary.TabVisible; qAdminSummary.Close; TabAdminDetail.TabVisible := False; // TabAdminDetail. TabAdminDetail.Visible := TabAdminDetail.TabVisible; qAdminDetail.Close; end else if (TabSheet = TabAdminDetail) then begin TabAdminDetail.TabVisible := False; // TabAdminDetail. TabAdminDetail.Visible := TabAdminDetail.TabVisible; qAdminDetail.Close; end else if (TabSheet = TabExpendRelease) then begin TabExpendRelease.TabVisible := False; // TabExpendRelease. TabExpendRelease.Visible := TabExpendRelease.TabVisible; qRelExpService.Close; qRelExpTransactSummary.Close; end else if (TabSheet = TabTransactionSummary) then begin TabTransactionSummary.TabVisible := False; // TabTransactionSummary. TabTransactionSummary.Visible := TabTransactionSummary.TabVisible; qTransactions.Close; qTransactionsAuto.Close; TabDCdetail.TabVisible := False; TabDCdetail.Visible := TabDCdetail.TabVisible; qDCdetail.Close; end else if (TabSheet = TabDCdetail) then begin TabDCdetail.TabVisible := False; // TabDCdetail. TabDCdetail.Visible := TabDCdetail.TabVisible; qDCdetail.Close; end else if (TabSheet = TabServices) then begin TabServices.TabVisible := False; // TabServices. TabServices.Visible := TabServices.TabVisible; qServices.Close; end; Application.ProcessMessages; end; // CloseTab. procedure TMainForm.ClearTabSheets; var nTab : integer; begin for nTab := 0 to PageControl1.PageCount-1 do CloseTab(PageControl1.Pages[nTab]); SetBillingPeriodBatch; PageControl1.Visible := False; Application.ProcessMessages; end; // procedure ClearTabSheets(). procedure TMainForm.SetBillingPeriodBatch; var qrySetBatch : TQuery; sTransactionBatchIDWas : string; nTransactionBatchIDWas : integer; iCount : integer; begin qrySetBatch := TQuery.Create(MainForm); qrySetBatch.DatabaseName := 'dbPPdata'; qrySetBatch.SQL.Add('SELECT tBatch.id, tBatch.BatchStatusID'); qrySetBatch.SQL.Add('FROM (tBatch'); qrySetBatch.SQL.Add('LEFT JOIN tBatchType ON tBatch.BatchTypeID = tBatchType.ID)'); qrySetBatch.SQL.Add('LEFT JOIN tDataSourceType '+ 'ON tBatchType.DataSourceTypeID = tDataSourceType.ID'); qrySetBatch.SQL.Add('WHERE tBatch.BillingPeriod LIKE '''+cbBillingPeriod.Text+''''); qrySetBatch.SQL.Add('AND tBatch.BatchStatusID > '+IntToStr(BATCHSTAT_INIT)); qrySetBatch.SQL.Add('AND tBatch.BatchStatusID < '+IntToStr(BATCHSTAT_TRANSNS)); if rbBatchTransactionsAuto.Checked then qrySetBatch.SQL.Add('AND tDataSourceType.DataSource LIKE ''%Automatic%'''); qrySetBatch.SQL.Add('ORDER BY tBatch.ID DESC'); qrySetBatch.Open; sTransactionBatchIDWas := cbBatchTransactions.Text; cbBatchTransactions.Clear; while not qrySetBatch.Eof do begin cbBatchTransactions.Items.Add(Trim(qrySetBatch['id'])); qrySetBatch.Next; end; nTransactionBatchIDWas := 0; for iCount := 0 to cbBatchTransactions.Items.Count-1 do if (cbBatchTransactions.Items[iCount]=sTransactionBatchIDWas) then nTransactionBatchIDWas := iCount; cbBatchTransactions.ItemIndex := nTransactionBatchIDWas; { // The following 2 lines are for switching between All and Automatic results. RxDBGridTransactions.Visible := rbBatchTransactions.Checked; RxDBGridTransactionsAuto.Visible := rbBatchTransactionsAuto.Checked; } qrySetBatch.Close; qrySetBatch.Free; end; // SetBillingPeriodBatch. procedure TMainForm.SelectAutoColumnHeaders(const nBatch: integer); var qryHeadings : TQuery; iCount : integer; sField : string; nColumns : integer; begin // Determine the BatchType. qryHeadings := TQuery.Create(MainForm); qryHeadings.DatabaseName := 'dbPPdata'; qryHeadings.SQL.Add('SELECT tBatchType.*'); qryHeadings.SQL.Add('FROM tBatch'); qryHeadings.SQL.Add('INNER JOIN tBatchType ON tBatch.BatchTypeID = tBatchType.ID'); qryHeadings.SQL.Add('WHERE tBatch.ID = '+IntToStr(nBatch)); qryHeadings.Open; // If there is a description then make the column visible and rename it. if not (qryHeadings.Bof and qryHeadings.Eof) then begin with qTransactionsAuto do for iCount := 1 to 15 do begin sField := 'Var'+Right('0'+IntToStr(iCount),2); for nColumns := 0 to FieldCount-1 do if (Fields[nColumns].FieldName = sField) then break; if (nColumns <= FieldCount-1) then begin sField := sField + 'Description'; if VarIsNull(qryHeadings[sField]) or (Length(Trim(qryHeadings.FieldByName(sField).AsString))=0) then begin RxDBGridTransactionsAuto.Columns[nColumns].Visible := False; end else begin RxDBGridTransactionsAuto.Columns[nColumns].Visible := True; RxDBGridTransactionsAuto.Columns[nColumns].Title.Caption := qryHeadings[sField]; end; // VarIsNull(qryHeadings[sField]). end; // nColumns <= Columns.Count. end; // for iCount. end; // not eof. //Clean up. qryHeadings.Close; qryHeadings.Free; end; // SelectAutoColumnHeaders. procedure TMainForm.btnCEProcessClick(Sender: TObject); var lSuccessful : Boolean; nBatchID : integer; wSelected : word; begin if qCEResolution.Active then begin nBatchID := qCESummary['BatchID']; CriticalErrorTest(nBatchID, lSuccessful); if lSuccessful then begin wSelected := MessageDlg('All Critical errors resolved. '+#13#10+ 'Process these transactions?', mtConfirmation, [mbYes, mbNo], 0); if (wSelected = mrYes) then begin // // StartTime; // ProcessWinMessages; // // SaveTransaction( nBatchID, lSuccessful ); // // EndTime; // ProcessWinMessages; // // CloseTab(TabCriticalSummary); if qBatchSummary.Active then begin qBatchSummary.Close; qBatchSummary.Open; PageControl1.ActivePage := TabBatchStatusSummary; end; // if qBatchSummary.Active then begin. end else begin qCEResolution.Close; // Refresh the screen. qCEResolution.Open; end; // if (wSelected = mrYes) then begin. end else begin qCEResolution.Close; // Refresh the screen. qCEResolution.Open; end; // if lSuccessful then begin..else. if qTransactions.Active and (cbBatchTransactions.Text = IntToStr(nBatchID)) then begin qTransactions.Close; qTransactions.Open; if qTransactionsAuto.Active then begin qTransactionsAuto.Close; qTransactionsAuto.Open; end; end; // qTransactions.Active. end; // if qCEResolution.Active then begin. end; // btnCEProcessClick. procedure TMainForm.GetSQL(const SQLType: string; var slSQL: TStringList); var sType : string; begin sType := LowerCase(SQLType); slSQL.Clear; if (sType = 'batchsummary') then begin slSQL.Add('SELECT BH.*'); slSQL.Add('FROM (SELECT tBatch.ID AS BatchID, '+ 'tBatchStatus.Description AS BatchStatus, '+ 'tBatch.BatchStatusID,'); slSQL.Add(' tBatchType.Description AS BatchType, '+ '(tBatch.TotalPayable - tBatch.GSTAmount) AS AmountExGST,'); slSQL.Add(' tBatch.GSTAmount, tBatch.TotalPayable, '+ 'tBatch.TotalAdjustments'); slSQL.Add('FROM (tBatch'); slSQL.Add('LEFT JOIN tBatchType '+ 'ON tBatch.BatchTypeID = tBatchType.ID)'); slSQL.Add('LEFT JOIN tBatchStatus '+ 'ON tBatch.BatchStatusID = tBatchStatus.ID'); slSQL.Add('WHERE tBatch.BillingPeriod LIKE '''+cbBillingPeriod.Text+''') AS BH'); slSQL.Add('ORDER BY BH.BatchID DESC'); end // if (sType = 'batchsummary') then begin. else if (sType = 'cesummary') then begin slSQL.Add('SELECT tDataCollection.BatchID, '+ 'tBatchType.Description AS BatchType,'); slSQL.Add(' Sum(tDataCollection.AmountExGST) AS TotalExGST, '+ 'Sum(tDataCollection.GSTAmount) AS TotalGST,'); slSQL.Add(' Sum(tDataCollection.AmountIncGST) AS TotalIncGST,'); slSQL.Add(' Sum(CASE WHEN tDataCollection.StatusID IS NOT NULL THEN '+ 'tDataCollection.AmountExGST ELSE 0 END) AS CriticalValue'); slSQL.Add('FROM (tDataCollection'); slSQL.Add('LEFT JOIN tBatch ON tDataCollection.BatchID = tBatch.ID)'); slSQL.Add('LEFT JOIN tBatchType ON tBatch.BatchTypeID = tBatchType.ID'); slSQL.Add('WHERE tBatch.BillingPeriod LIKE '''+ cbBillingPeriod.Text+''''); slSQL.Add('GROUP BY tDataCollection.BatchID, tBatchType.Description'); slSQL.Add('ORDER BY tDataCollection.BatchID DESC'); end // else if (sType = 'cesummary') then begin. else if (sType = 'completed') then begin slSQL.Add('SELECT C.*'); slSQL.Add('FROM (SELECT P.BatchID, P.PayableDate, '+ 'LEFT(P.PlatinumFileName, 20) AS "PlatinumFileName",'); slSQL.Add(' (CASE P.ReleasedFlag WHEN 1 THEN ''Yes'' ELSE '''' END) AS "Released",'); slSQL.Add(' tSupplier.SupplierName, tCentre.Description AS Centre,'); slSQL.Add(' tActivity.Description AS Activity, tElement.ElementCode AS Element,'); slSQL.Add(' tSubledger.Description AS Subledger, PD.ID AS "PayableDetailID",'); slSQL.Add(' PD.PayableID, PD.Period, PD.AmountExGST, PD.GSTAmount,'); slSQL.Add(' (PD.AmountExGST + PD.GSTAmount) AS "AmountIncGST"'); slSQL.Add('FROM tPayable P'); slSQL.Add('INNER JOIN tPayableDetail PD ON P.ID = PD.PayableID'); slSQL.Add('INNER JOIN tSupplier ON P.SupplierID = tSupplier.ID'); slSQL.Add('INNER JOIN tCentre ON PD.CentreID = tCentre.ID'); slSQL.Add('INNER JOIN tActivity ON PD.ActivityID = tActivity.ID'); slSQL.Add('INNER JOIN tElement ON PD.ElementID = tElement.ID'); slSQL.Add('INNER JOIN tSubledger ON PD.SubledgerID = tSubledger.ID) AS C'); slSQL.Add('WHERE C.Period LIKE '''+cbBillingPeriod.Text+''''); slSQL.Add('ORDER BY C.BatchID DESC'); { slSQL.Add('SELECT *'); slSQL.Add('FROM (SELECT tFinalisedTransaction.RevenueFlag, '+ 'tFinalisedTransaction.BatchID, tBatch.BillingPeriod,'); slSQL.Add(' tFinalisedTransaction.CustomerID, tCustomer.ShipTo, '+ 'tServiceID.ServiceID, tFinalisedTransaction.ServiceTypeID,'); slSQL.Add(' tServiceType.Code, tServiceType.Description AS ServiceType, '+ 'tFinalisedTransaction.AmountExGST,'); slSQL.Add(' tTransactionGroup.Description AS TransactionGroup, '+ 'tTransactionType.Description AS TransactionType'); slSQL.Add('FROM tFinalisedTransaction'); slSQL.Add(' INNER JOIN tServiceID '+ 'ON tFinalisedTransaction.ServiceID = tServiceID.ID'); slSQL.Add(' LEFT JOIN (tBatch INNER JOIN tSupplier '+ 'ON tBatch.SupplierID = tSupplier.ID) '+ 'ON tFinalisedTransaction.BatchID = tBatch.ID'); slSQL.Add(' LEFT JOIN (tTransactionType INNER JOIN tTransactionGroup '+ 'ON tTransactionType.TransactionGroupID = tTransactionGroup.ID)'); slSQL.Add(' ON tFinalisedTransaction.TransactionTypeID = tTransactionType.ID'); slSQL.Add(' INNER JOIN tCustomer '+ 'ON tFinalisedTransaction.CustomerID = tCustomer.ID'); slSQL.Add(' LEFT OUTER JOIN tServiceType '+ 'ON tFinalisedTransaction.ServiceTypeID = tServiceType.ID'); slSQL.Add(' LEFT OUTER JOIN tElement '+ 'ON tServiceType.ExpenseElementID = tElement.ID'); slSQL.Add('WHERE tBatch.BillingPeriod LIKE '''+cbBillingPeriod.Text+''''); slSQL.Add('AND (tFinalisedTransaction.RevenueFlag = 0)'); slSQL.Add('AND (tTransactionGroup.Description NOT LIKE ''ADMIN'')) AS FT'); slSQL.Add('ORDER BY ServiceID, BillingPeriod'); } end else if (sType = 'dubious') then begin slSQL.Add('SELECT DR.*'); slSQL.Add('FROM (SELECT TN.*, tDubiousResult.DubiousTypeID, '+ 'tDubiousResult.ID AS DID, tServiceID.ServiceID AS Service,'); slSQL.Add(' tServiceID.PreviousRent, tServiceID.PreviousCalls,'); slSQL.Add(' tDubiousType.Description AS DubiousError, '+ 'tDubiousResolution.Description AS Resolution'); slSQL.Add('FROM ((((SELECT tTransaction.BatchID, tTransaction.ServiceID, '+ 'tBatch.BatchTypeID, COUNT(tTransaction.ServiceID) AS RecordCount,'); slSQL.Add(' Sum(CASE tTransactionGroup.Description WHEN ''RENT'' '+ 'THEN tTransaction.AmountExGST ELSE 0 END) AS "Rent",'); slSQL.Add(' Sum(CASE tTransactionGroup.Description WHEN ''CALL'' '+ 'THEN tTransaction.AmountExGST ELSE 0 END) AS "Calls",'); slSQL.Add(' Sum(CASE tTransactionGroup.Description WHEN ''OTHER'' '+ 'THEN tTransaction.AmountExGST ELSE 0 END) AS "Other",'); slSQL.Add(' Sum(CASE tTransactionGroup.Description WHEN ''ADMIN'' '+ 'THEN 0 ELSE tTransaction.AmountExGST END) AS "TotalExGST",'); slSQL.Add(' Sum(CASE tTransactionGroup.Description WHEN ''ADMIN'' '+ 'THEN 0 ELSE tTransaction.GSTAmount END) AS "TotalGST",'); slSQL.Add(' Sum(CASE tTransactionGroup.Description WHEN ''ADMIN'' '+ 'THEN 0 ELSE tTransaction.AmountIncGST END) AS "TotalIncGST",'); slSQL.Add(' Sum(CASE tTransactionGroup.Description WHEN ''ADMIN'' '+ 'THEN tTransaction.AmountExGST ELSE 0 END) AS "Admin"'); slSQL.Add('FROM ((tTransaction'); slSQL.Add('LEFT JOIN tBatch ON tTransaction.BatchID = tBatch.ID)'); slSQL.Add('LEFT JOIN tTransactionType '+ 'ON tTransaction.TransactionTypeID = tTransactionType.ID)'); slSQL.Add('LEFT JOIN tTransactionGroup '+ 'ON tTransactionType.TransactionGroupID = tTransactionGroup.ID'); slSQL.Add('WHERE tTransaction.BatchID = '+ IntToStr(qBatchSummary.FieldByName('BatchID').AsInteger)); slSQL.Add('AND (tBatch.BatchStatusID > '+IntToStr(BATCHSTAT_ADMINOK)+ ' AND tBatch.BatchStatusID < '+IntToStr(BATCHSTAT_RELEASED)+')'); // slSQL.Add('WHERE tTransaction.BatchID = 20622'); // slSQL.Add('AND (tBatch.BatchStatusID > 7 AND tBatch.BatchStatusID < 11)'); slSQL.Add('AND tTransactionType.Description NOT LIKE ''%Lost%'''); slSQL.Add('AND tTransaction.StatusID IS NULL'); slSQL.Add('GROUP BY tTransaction.BatchID, tBatch.BatchTypeID, '+ 'tTransaction.ServiceID) AS TN'); slSQL.Add('INNER JOIN tDubiousResult '+ 'ON ((TN.BatchID = tDubiousResult.BatchID) '+ 'AND (TN.ServiceID = tDubiousResult.ServiceID)))'); slSQL.Add('LEFT JOIN tDubiousType '+ 'ON tDubiousResult.DubiousTypeID = tDubiousType.ID)'); slSQL.Add('LEFT JOIN tDubiousResolution '+ 'ON tDubiousResult.ResolutionID = tDubiousResolution.ID)'); slSQL.Add('LEFT JOIN tServiceID ON TN.ServiceID = tServiceID.ID'); slSQL.Add('WHERE tDubiousResult.DubiousTypeID IS NOT NULL) AS DR'); slSQL.Add('ORDER BY ServiceID'); end // dubious. else if (sType = 'rdserviceid') then begin slSQL.Add('SELECT SID2.ServiceID, SID2.Code, SID2.ServiceType, '+ 'SID2.Description, SID2.ShipTo,'); slSQL.Add(' SID2.Customer, SID2.CustomerCentre, SID2.CustomerActivity, '+ 'SID2.CustomerElement,'); slSQL.Add(' SID2.CustomerSubledger, tCentre.Description AS Centre, '+ 'tActivity.Description AS Activity,'); slSQL.Add(' tElement.ElementCode AS Element, '+ 'tSubledger.Description AS Subledger, SID2.CurrentRent,'); slSQL.Add(' SID2.CurrentCalls, SID2.PreviousRent, SID2.PreviousCalls, '+ 'SID2.InstallationDate,'); slSQL.Add(' SID2.TerminationDate, SID2.UpEnd, SID2.UpMDF, SID2.DownEnd, '+ 'SID2.DownMDF, SID2.Length, SID2.Person'); slSQL.Add('FROM ((((SELECT SID.ServiceID, tServiceType.Code, '+ 'tServiceType.Description AS ServiceType, SID.Description, tCustomer.ShipTo,'); slSQL.Add(' tCustomer.FullName AS Customer, '+ 'tCentre.Description AS CustomerCentre, '+ 'tActivity.Description AS CustomerActivity,'); slSQL.Add(' tElement.ElementCode AS CustomerElement, '+ 'tSubledger.Description AS CustomerSubledger, '+ 'SID.CurrentRent, SID.CurrentCalls,'); slSQL.Add(' SID.PreviousRent, SID.PreviousCalls, SID.InstallationDate, '+ 'SID.TerminationDate, SID.UpEnd,'); slSQL.Add(' SID.UpMDF, SID.DownEnd, SID.DownMDF, SID.Length,'); slSQL.Add(' SID.CentreID, SID.ActivityID, SID.ElementID, SID.SubledgerID,'); slSQL.Add(' tPerson.Person'); slSQL.Add('FROM (((((((((tServiceID AS SID '+ 'LEFT JOIN tServiceType ON SID.ServiceTypeID = tServiceType.ID)'); slSQL.Add(' LEFT JOIN tCentre ON SID.CustomerCentreID = tCentre.ID)'); slSQL.Add(' LEFT JOIN tActivity ON SID.CustomerActivityID = tActivity.ID)'); slSQL.Add(' LEFT JOIN tElement ON SID.CustomerElementID = tElement.ID)'); slSQL.Add(' LEFT JOIN tDepartment ON tCentre.DepartmentID = tDepartment.ID)'); slSQL.Add(' LEFT JOIN tGroup ON tDepartment.GroupID = tGroup.ID)'); slSQL.Add(' LEFT JOIN tLocation ON SID.LocationID = tLocation.ID)'); slSQL.Add(' LEFT JOIN tSubledger ON SID.CustomerSubledgerID = tSubledger.ID)'); slSQL.Add(' LEFT JOIN tCustomer ON tGroup.CustomerID = tCustomer.ID)'); slSQL.Add(' LEFT JOIN tPerson ON SID.PersonID = tPerson.ID) AS SID2'); slSQL.Add(' LEFT JOIN tCentre ON SID2.CentreID = tCentre.ID)'); slSQL.Add(' LEFT JOIN tActivity ON SID2.ActivityID = tActivity.ID)'); slSQL.Add(' LEFT JOIN tElement ON SID2.ElementID = tElement.ID)'); slSQL.Add(' LEFT JOIN tSubledger ON SID2.SubledgerID = tSubledger.ID'); slSQL.Add('ORDER BY SID2.ServiceID'); end // else if (sType = 'rdserviceid') then begin. else if (sType = 'releasetransactns') then begin slSQL.Add('SELECT TN.BatchID, tServiceID.ServiceID, TN.DateFrom, '+ 'TN.DateTo, TN.Rent, TN.Calls, TN.Other, TN.Admin, TN.Total,'); slSQL.Add('(TN.Rent + TN.Calls + TN.Other) AS "SubTotal"'); slSQL.Add('FROM (SELECT tTransaction.BatchID, tTransaction.ServiceID,'); slSQL.Add(' Min(CASE tTransactionGroup.Description WHEN ''RENT'' '+ 'THEN tTransaction.FromDate END) AS DateFrom,'); slSQL.Add(' Max(CASE tTransactionGroup.Description WHEN ''RENT'' '+ 'THEN tTransaction.ToDate END) AS DateTo,'); slSQL.Add(' Sum(CASE tTransactionGroup.Description WHEN ''RENT'' '+ 'THEN tTransaction.AmountExGST ELSE 0 END) AS Rent,'); slSQL.Add(' Sum(CASE tTransactionGroup.Description WHEN ''CALL'' '+ 'THEN tTransaction.AmountExGST ELSE 0 END) AS Calls,'); slSQL.Add(' Sum(CASE tTransactionGroup.Description WHEN ''OTHER'' '+ 'THEN tTransaction.AmountExGST ELSE 0 END) AS Other,'); slSQL.Add(' Sum(CASE tTransactionGroup.Description WHEN ''ADMIN'' THEN '+ 'tTransaction.AmountExGST ELSE 0 END) AS Admin,'); slSQL.Add(' Sum(tTransaction.AmountExGST) AS Total'); slSQL.Add('FROM ((tTransaction'); slSQL.Add('LEFT JOIN tBatch ON tTransaction.Batchid = tBatch.ID)'); slSQL.Add('LEFT JOIN tTransactionType '+ 'ON tTransaction.TransactionTypeID = tTransactionType.ID)'); slSQL.Add('LEFT JOIN tTransactionGroup '+ 'ON tTransactionType.TransactionGroupID = tTransactionGroup.ID'); slSQL.Add('WHERE tTransaction.BatchID = '+IntToStr(qBatchSummary['BatchID'])); slSQL.Add('AND tBatch.BatchStatusID = '+IntToStr(BATCHSTAT_ADMINOK)); slSQL.Add('AND tTransaction.StatusID IS NULL'); slSQL.Add('AND tTransaction.RevenueFlag = 0'); slSQL.Add('GROUP BY tTransaction.BatchID, tTransaction.ServiceID) AS TN'); slSQL.Add('LEFT JOIN tServiceID ON TN.ServiceID = tServiceID.ID'); slSQL.Add('ORDER BY tServiceID.ServiceID'); end // else if (sType = 'releasetransactns') then begin. else if (sType = 'adjustment') then begin slSQL.Add('SELECT tAdjustment.BatchID, tBatchType.Description AS BatchType,'); slSQL.Add(' Sum(tAdjustment.AmountExGST) AS TotalExGST, '+ 'Sum(tAdjustment.GSTAmount) AS TotalGST,'); slSQL.Add(' Sum(tAdjustment.AmountIncGST) AS TotalIncGST,'); slSQL.Add(' Sum(CASE WHEN tAdjustment.StatusID IS NOT NULL THEN '+ 'tAdjustment.AmountExGST ELSE 0 END) AS CriticalValue'); slSQL.Add('FROM (tAdjustment'); slSQL.Add('LEFT JOIN tBatch ON tAdjustment.BatchID = tBatch.ID)'); slSQL.Add('LEFT JOIN tBatchType ON tBatch.BatchTypeID = tBatchType.ID'); slSQL.Add('WHERE tBatch.BillingPeriod LIKE '''+ cbBillingPeriod.Text+''''); slSQL.Add('GROUP BY tAdjustment.BatchID, tBatchType.Description'); slSQL.Add('ORDER BY tAdjustment.BatchID'); end // else if (sType = 'adjustment') then begin. else if (sType = 'dctransactions') then begin slSQL.Add('SELECT DC3.*'); slSQL.Add('FROM (SELECT DC2.*, tCriticalType.Description AS "Reason"'); slSQL.Add('FROM (SELECT DC1.*, (DC1.RENT + DC1.CALL + DC1.OTHER) '+ 'AS "TotalExGST", tServiceType.AdminFee,'); slSQL.Add(' tCustomer.ShipTo'); slSQL.Add('FROM ((((((SELECT DC0.ServiceID, DC0.StatusID, DC0.ServiceTypeID, '+ 'DC0.Code,'); slSQL.Add(' Sum(CASE tTransactionGroup.Description WHEN ''RENT'' THEN '+ 'DC0.AmountExGST ELSE 0 END) AS "Rent",'); slSQL.Add(' Sum(CASE tTransactionGroup.Description WHEN ''CALL'' THEN '+ 'DC0.AmountExGST ELSE 0 END) AS "Call",'); slSQL.Add(' Sum(CASE tTransactionGroup.Description WHEN ''OTHER'' THEN '+ 'DC0.AmountExGST ELSE 0 END) AS "Other",'); slSQL.Add(' Sum(CASE DC0.GSTAmount WHEN NULL THEN 0 ELSE DC0.GSTAmount '+ 'END) AS "GST",'); slSQL.Add(' Sum(CASE DC0.AmountIncGST WHEN NULL THEN 0 ELSE '+ 'DC0.AmountIncGST END) AS "TotalIncGST",'); slSQL.Add(' Min(DC0.FromDate) AS "FromDate",'); slSQL.Add(' Max(DC0.ToDate) AS "ToDate"'); // slSQL.Add(' Min(CASE tTransactionGroup.Description WHEN ''RENT'' THEN '+ // 'DC0.FromDate END) AS "RentFrom",'); // slSQL.Add(' Max(CASE tTransactionGroup.Description WHEN ''RENT'' THEN '+ // 'DC0.ToDate END) AS "RentTill",'); // slSQL.Add(' Max(CASE tTransactionGroup.Description WHEN ''CALL'' THEN '+ // 'DC0.ToDate END) AS "CallTill"'); slSQL.Add('FROM ((SELECT DC.*, tServiceType.ID AS ServiceTypeID'); slSQL.Add('FROM (SELECT tDataCollection.*, (CASE '+ 'WHEN tDataCollection.ServiceType IS NULL THEN tServiceType.Code'); slSQL.Add(' ELSE RTRIM(SUBSTRING(tDataCollection.ServiceType,1,5)) END) '+ 'AS "Code"'); slSQL.Add('FROM (tDataCollection'); slSQL.Add('LEFT JOIN tServiceID '+ 'ON tDataCollection.ServiceID = tServiceID.ServiceID)'); slSQL.Add('LEFT JOIN tServiceType '+ 'ON tServiceID.ServiceTypeID = tServiceType.ID'); slSQL.Add('WHERE (tDataCollection.RevenueFlag IS NULL '+ 'OR tDataCollection.RevenueFlag = 0)'); slSQL.Add('AND tDataCollection.BatchID = '+cbBatchTransactions.Text+') '+ 'AS DC'); slSQL.Add('LEFT JOIN tServiceType ON DC.Code = tServiceType.Code) AS DC0'); slSQL.Add('LEFT JOIN tTransactionType '+ 'ON DC0.TransactionTypeID = tTransactionType.ID)'); slSQL.Add('LEFT JOIN tTransactionGroup '+ 'ON tTransactionType.TransactionGroupID = tTransactionGroup.ID'); slSQL.Add('GROUP BY DC0.ServiceID, DC0.StatusID, DC0.ServiceTypeID, '+ 'DC0.Code) AS DC1'); slSQL.Add(' LEFT JOIN tServiceID ON DC1.ServiceID = tServiceID.ServiceID)'); slSQL.Add(' LEFT JOIN tCentre ON tServiceID.CustomerCentreID = tCentre.ID)'); slSQL.Add(' LEFT JOIN tDepartment ON tCentre.DepartmentID = tDepartment.ID)'); slSQL.Add(' LEFT JOIN tGroup ON tDepartment.GroupID = tGroup.ID)'); slSQL.Add(' LEFT JOIN tCustomer ON tGroup.CustomerID = tCustomer.ID)'); slSQL.Add(' LEFT JOIN tServiceType '+ 'ON DC1.ServiceTypeID = tServiceType.ID) AS DC2'); slSQL.Add(' LEFT JOIN tCriticalType '+ 'ON DC2.StatusID = tCriticalType.ID) AS DC3'); slSQL.Add('ORDER BY DC3.ServiceID'); end // dctransactions. else if (sType = 'dctransactionsauto') then begin slSQL.Add('SELECT *'); slSQL.Add('FROM (SELECT tDataCollection.BatchID, '+ 'tDataCollection.ServiceID, tServiceType.Code,'); slSQL.Add(' tDataCollection.AmountExGST, tDataCollection.Var01, '+ 'tDataCollection.Var02,'); slSQL.Add(' tDataCollection.Var03, tDataCollection.Var04, '+ 'tDataCollection.Var05,'); slSQL.Add(' tDataCollection.Var06, tDataCollection.Var07, '+ 'tDataCollection.Var08,'); slSQL.Add(' tDataCollection.Var09, tDataCollection.Var10, '+ 'tDataCollection.Var11,'); slSQL.Add(' tDataCollection.Var12, tDataCollection.Var13, '+ 'tDataCollection.Var14,'); slSQL.Add(' tDataCollection.Var15, tCriticalType.Description AS "Reason",'); slSQL.Add(' tDataCollection.StatusID'); slSQL.Add('FROM ((tDataCollection'); slSQL.Add('LEFT JOIN tCriticalType '+ 'ON tDataCollection.StatusID = tCriticalType.ID)'); slSQL.Add('LEFT JOIN tServiceID '+ 'ON tDataCollection.ServiceID = tServiceID.ServiceID)'); slSQL.Add('LEFT JOIN tServiceType '+ 'ON tServiceID.ServiceTypeID = tServiceType.ID'); slSQL.Add('WHERE (tDataCollection.RevenueFlag IS NULL '+ 'OR tDataCollection.RevenueFlag = 0)'); slSQL.Add('AND tDataCollection.BatchID = '+cbBatchTransactions.Text+ ') AS DCA'); slSQL.Add('ORDER BY ServiceID'); end // dctransactionsauto. else if (sType = 'adminsummary') then begin slSQL.Add('SELECT TN1.*'); slSQL.Add('FROM (SELECT TN.BatchID, tServiceID.ServiceID, '+ 'tServiceType.Code, TN.DateFrom, TN.DateTo, TN.Rent, TN.Calls, '+ 'TN.Other, TN.Admin,'); slSQL.Add('tDiscount.DiscountRate, tDiscount.NonStdRevAdminFee, '+ 'tDiscount.NonStdExpAdminFee, '+ '(TN.Rent + TN.Calls + TN.Other) AS "SubTotal",'); slSQL.Add('(TN.Rent + TN.Calls + TN.Other + TN.Admin) AS "Total", '+ 'tCustomer.ShipTo'); slSQL.Add('FROM ((((SELECT tBatch.BillingPeriod, tTransaction.BatchID, '+ 'tTransaction.ServiceID, tTransaction.ServiceTypeID,'); slSQL.Add(' Min(tTransaction.FromDate) AS DateFrom,'); slSQL.Add(' Max(tTransaction.ToDate) AS DateTo,'); // slSQL.Add(' Min(CASE tTransactionGroup.Description WHEN ''RENT'''+ // ' THEN tTransaction.FromDate END) AS DateFrom,'); // slSQL.Add(' Max(CASE tTransactionGroup.Description WHEN ''RENT'''+ // ' THEN tTransaction.ToDate END) AS DateTo,'); slSQL.Add(' Sum(CASE tTransactionGroup.Description WHEN ''RENT'''+ ' THEN tTransaction.AmountExGST ELSE 0 END) AS Rent,'); slSQL.Add(' Sum(CASE tTransactionGroup.Description WHEN ''CALL'''+ ' THEN tTransaction.AmountExGST ELSE 0 END) AS Calls,'); slSQL.Add(' Sum(CASE tTransactionGroup.Description WHEN ''OTHER'''+ ' THEN tTransaction.AmountExGST ELSE 0 END) AS Other,'); slSQL.Add(' Sum(CASE tTransactionGroup.Description WHEN ''ADMIN'''+ ' THEN tTransaction.AmountExGST ELSE 0 END) AS Admin,'); slSQL.Add(' tTransaction.CustomerID'); slSQL.Add('FROM ((tTransaction'); slSQL.Add('LEFT JOIN tBatch ON tTransaction.Batchid = tBatch.ID)'); slSQL.Add('LEFT JOIN tTransactionType '+ 'ON tTransaction.TransactionTypeID = tTransactionType.ID)'); slSQL.Add('LEFT JOIN tTransactionGroup '+ 'ON tTransactionType.TransactionGroupID = tTransactionGroup.ID'); if qBatchSummary.Active and not (qBatchSummary.Bof and qBatchSummary.Eof) then slSQL.Add('WHERE tBatch.ID = '+ IntToStr(qBatchSummary['BatchID'])) else slSQL.Add('WHERE tBatch.BillingPeriod = '''+ cbBillingPeriod.Text+''''); slSQL.Add('AND tBatch.BatchStatusID > '+IntToStr(BATCHSTAT_CPASS)); slSQL.Add('AND tBatch.BatchStatusID < '+IntToStr(BATCHSTAT_PAYEXP)); slSQL.Add('AND tTransaction.StatusID IS NULL'); slSQL.Add('AND tTransaction.RevenueFlag = 1'); slSQL.Add('GROUP BY tBatch.BillingPeriod, tTransaction.BatchID, '+ 'tTransaction.ServiceID, tTransaction.CustomerID, '+ 'tTransaction.ServiceTypeID) AS TN'); slSQL.Add('LEFT JOIN tServiceID ON TN.ServiceID = tServiceID.ID)'); slSQL.Add('LEFT JOIN tServiceType '+ 'ON TN.ServiceTypeID = tServiceType.ID)'); slSQL.Add('LEFT JOIN tCustomer ON TN.CustomerID = tCustomer.ID)'); //27-07-2005. slSQL.Add('LEFT JOIN tDiscount '+ 'ON ((TN.CustomerID = tDiscount.CustomerID) '+ 'AND (TN.ServiceTypeID = tDiscount.ServiceTypeID))) AS TN1'); slSQL.Add('ORDER BY TN1.ServiceID'); end; // adminsummary. end; // GetSQL. procedure TMainForm.RxDBGridServicesGetCellParams(Sender: TObject; Field: TField; AFont: TFont; var Background: TColor; Highlight: Boolean); begin if not Highlight then begin if VarIsNull(RxDBGridServices.DataSource.DataSet['InstallationDate']) then AFont.Color := clRed else if not VarIsNull(RxDBGridServices.DataSource.DataSet['TerminationDate']) then AFont.Color := clBlue; end; // if not Highlight then. end; // GridServicesGetCellParams. procedure TMainForm.btnCERejectClick(Sender: TObject); var lSuccessful : Boolean; nThisBatch : integer; SelectedCount : integer; nSelect : integer; slCopyList : TStringList; curExGST, curGST, curIncGST : Currency; qBatchSettings : TQuery; begin if qCEResolution.Active and not (qCEResolution.Bof and qCEResolution.Eof) then begin nThisBatch := qCEResolution['BatchID']; slCopyList := TStringList.Create; SelectedCount := RxDBGridCERes.SelCount; if (SelectedCount > 0) then begin for nSelect := 0 to SelectedCount - 1 do begin RxDBGridCERes.GotoSelection(nSelect); slCopyList.Add(qCEResolution.FieldByName('ServiceID').AsString+'||' +IntToStr(qCEResolution['StatusID'])); end; // for nSelect := 0 to SelectedCount - 1 do begin. XCopyToAdjustment(nThisBatch, slCopyList, curExGST, curGST, curIncGST, lSuccessful); if lSuccessful then if (MessageDlg('Update the details for this Invoice?', mtWarning, [mbYes, mbNo], 0) = mrYes) then begin // Adjust the values entered in tBatch. qBatchSettings := TQuery.Create(SELF); qBatchSettings.DatabaseName := 'dbPPdata'; qBatchSettings.SQL.Add('SELECT TotalAdjustments, GSTAdjustment, '+ 'GSTAmount, TotalPayable'); qBatchSettings.SQL.Add('FROM tBatch'); qBatchSettings.SQL.Add('WHERE ID = '+IntToStr(nThisBatch)); qBatchSettings.Open; // Read old values. qry.Close; qry.SQL.Clear; qry.SQL.Add('UPDATE tBatch'); qry.SQL.Add('SET TotalAdjustments = '+Curr2Str(curSum( qBatchSettings.FieldByName('TotalAdjustments').AsCurrency, curExGST*(-1.00)))+','); qry.SQL.Add('GSTAdjustment = '+Curr2Str(curSum( qBatchSettings.FieldByName('GSTAdjustment').AsCurrency, curGST*(-1.00)))+','); qry.SQL.Add('GSTAmount = '+Curr2Str(curSum( qBatchSettings.FieldByName('GSTAmount').AsCurrency, curGST*(-1.00)))+','); qry.SQL.Add('TotalPayable = '+Curr2Str(curSum( qBatchSettings.FieldByName('TotalPayable').AsCurrency, curIncGST*(-1.00)))); qry.SQL.Add('WHERE ID = '+IntToStr(nThisBatch)); qBatchSettings.Close; qBatchSettings.Free; qry.ExecSQL; // Save corrected values. qry.Close; qry.SQL.Clear; if qBatchSummary.Active then begin qBatchSummary.Close; // Refresh the screen. qBatchSummary.Open; end; // Remove adjustments from tDataCollection. DeleteFromDC(nThisBatch, slCopyList, lSuccessful); end; // if lSuccessful then begin. end; // if (SelectedCount > 0) then begin. qCEResolution.Close; // Refresh the screen. qCEResolution.Open; end; // if qCEResolution.Active then begin. end; // btnCERejectClick. procedure TMainForm.RxDBGridCEResTitleClick(Column: TColumn); // Only act on a data source that is already sorted. // (Assumes ORDER BY is in the last line.) var slQuery : TStrings; begin // Change sort order - eg:"ORDER BY BatchID, fieldname DESC". qCEResolution.Close; slQuery := qCEResolution.SQL; GridTitleSort(Column, slQuery); qCEResolution.SQL := slQuery; qCEResolution.Open; end; // RxDBGridCEResTitleClick. procedure TMainForm.RxDBGridCESummaryTitleClick(Column: TColumn); var slQuery : TStrings; begin // Change sort order - eg:"ORDER BY BatchID, fieldname DESC". qCESummary.Close; slQuery := qCESummary.SQL; GridTitleSort(Column, slQuery); qCESummary.SQL := slQuery; qCESummary.Open; end; // RxDBGridCESummaryTitleClick. procedure TMainForm.RxDBGridBatchStatusTitleClick(Column: TColumn); var slQuery : TStrings; begin // Change sort order - eg:"ORDER BY BatchID, fieldname DESC". qBatchSummary.Close; slQuery := qBatchSummary.SQL; GridTitleSort(Column, slQuery); qBatchSummary.SQL := slQuery; qBatchSummary.Open; end; // RxDBGridBatchStatusTitleClick. procedure TMainForm.RxDBGridServicesTitleClick(Column: TColumn); var slQuery : TStrings; begin // Change sort order - eg:"ORDER BY BatchID, fieldname DESC". qServices.Close; slQuery := qServices.SQL; GridTitleSort(Column, slQuery); qServices.SQL := slQuery; qServices.Open; end; // GridServicesTitleClick. procedure TMainForm.RxDBGridAdjustmentTitleClick(Column: TColumn); var slQuery : TStrings; begin // Change sort order - eg:"ORDER BY BatchID, fieldname DESC". qAdjustment.Close; slQuery := qAdjustment.SQL; GridTitleSort(Column, slQuery); qAdjustment.SQL := slQuery; qAdjustment.Open; end; // RxDBGridAdjustmentTitleClick. procedure TMainForm.RxDBGridDubiousSummaryTitleClick(Column: TColumn); var slQuery : TStrings; begin // Change sort order - eg:"ORDER BY BatchID, fieldname DESC". qDubSummary.Close; slQuery := qDubSummary.SQL; GridTitleSort(Column, slQuery); qDubSummary.SQL := slQuery; qDubSummary.Open; end; // RxDBGridDubiousSummaryTitleClick. procedure TMainForm.RxDBGridAFSummaryTitleClick(Column: TColumn); var slQuery : TStrings; begin // Change sort order - eg:"ORDER BY BatchID, fieldname DESC". qAdminSummary.Close; slQuery := qAdminSummary.SQL; GridTitleSort(Column, slQuery); qAdminSummary.SQL := slQuery; qAdminSummary.Open; end; // RxDBGridAFSummaryTitleClick. procedure TMainForm.RxDBGridAFDetailTitleClick(Column: TColumn); var slQuery : TStrings; begin // Change sort order - eg:"ORDER BY BatchID, fieldname DESC". qAdminDetail.Close; slQuery := qAdminDetail.SQL; GridTitleSort(Column, slQuery); qAdminDetail.SQL := slQuery; qAdminDetail.Open; end; // RxDBGridAFDetailTitleClick. procedure TMainForm.RxDBGridTransactionsTitleClick(Column: TColumn); var slQuery : TStrings; begin // Change sort order - eg:"ORDER BY BatchID, fieldname DESC". qTransactions.Close; slQuery := qTransactions.SQL; GridTitleSort(Column, slQuery); qTransactions.SQL := slQuery; qTransactions.Open; end; // RxDBGridTransactionsTitleClick. procedure TMainForm.RxDBGridTransactionsAutoTitleClick(Column: TColumn); var slQuery : TStrings; begin // Change sort order - eg:"ORDER BY BatchID, fieldname DESC". qTransactionsAuto.Close; slQuery := qTransactionsAuto.SQL; GridTitleSort(Column, slQuery); qTransactionsAuto.SQL := slQuery; qTransactionsAuto.Open; end; // RxDBGridTransactionsAutoTitleClick. procedure TMainForm.RxDBGridDCdetailTitleClick(Column: TColumn); var slQuery : TStrings; begin // Change sort order - eg:"ORDER BY BatchID, fieldname DESC". qDCdetail.Close; slQuery := qDCdetail.SQL; GridTitleSort(Column, slQuery); qDCdetail.SQL := slQuery; qDCdetail.Open; end; // RxDBGridDCdetailTitleClick. procedure TMainForm.RxDBGridRelExpSummaryTitleClick(Column: TColumn); var slQuery : TStrings; begin // Change sort order - eg:"ORDER BY BatchID, fieldname DESC". qRelExpTransactSummary.Close; slQuery := qRelExpTransactSummary.SQL; GridTitleSort(Column, slQuery); qRelExpTransactSummary.SQL := slQuery; qRelExpTransactSummary.Open; end; // RxDBGridRelExpSummaryTitleClick. procedure TMainForm.RxDBGridCompletedPayableTitleClick(Column: TColumn); var slQuery : TStrings; begin // Change sort order - eg:"ORDER BY BatchID, fieldname DESC". qCompletedPayable.Close; slQuery := qCompletedPayable.SQL; GridTitleSort(Column, slQuery); qCompletedPayable.SQL := slQuery; qCompletedPayable.Open; end; // RxDBGridCompletedTitleClick. procedure TMainForm.RxDBGridRelExpSummaryCellClick(Column: TColumn); var slStrings : TStringList; sServiceName : string; begin sServiceName := qRelExpTransactSummary.FieldByName('ServiceID').AsString; slStrings := TStringList.Create; slStrings.Add('SELECT SID2.ServiceID, SID2.Code, SID2.ServiceType, '+ 'SID2.Description, SID2.RevElement, tElement.ElementCode AS ExpElement, '+ 'SID2.Supplier,'); slStrings.Add(' SID2.ShipTo, SID2.CustomerCentre, SID2.CustomerActivity, '+ 'SID2.CustomerElement, SID2.CustomerSubledger, SID2.SplitService, '+ 'SID2.VRTService'); slStrings.Add('FROM (SELECT SID1.ServiceID, SID1.Code, SID1.ServiceType, '+ 'SID1.Description, tElement.ElementCode AS RevElement, ExpElementID, '+ 'SID1.Supplier,'); slStrings.Add(' SID1.ShipTo, SID1.CustomerCentre, SID1.CustomerActivity, '+ 'SID1.CustomerElement, SID1.CustomerSubledger, SID1.SplitService, '+ 'SID1.VRTService'); slStrings.Add('FROM (SELECT SID.ServiceID, tServiceType.Code, '+ 'tServiceType.Description AS ServiceType, SID.Description,'); slStrings.Add(' tServiceType.RevenueElementID AS RevElementID, '+ 'tServiceType.ExpenseElementID as ExpElementID,'); slStrings.Add(' tSupplier.SupplierName AS Supplier, tCustomer.ShipTo, '+ 'tCentre.Description AS CustomerCentre,'); slStrings.Add(' tActivity.Description AS CustomerActivity, '+ 'tElement.ElementCode AS CustomerElement,'); slStrings.Add(' tSubledger.Description AS CustomerSubledger, '+ 'tServiceType.SplitService, tServiceType.VRTServiceType as VRTService'); slStrings.Add('FROM ((((((((tServiceID AS SID '+ 'LEFT JOIN tServiceType ON SID.ServiceTypeID = tServiceType.ID)'); slStrings.Add(' LEFT JOIN tSupplier ON tServiceType.SupplierID = tSupplier.ID)'); slStrings.Add(' LEFT JOIN tCentre ON SID.CustomerCentreID = tCentre.ID)'); slStrings.Add(' LEFT JOIN tActivity ON SID.CustomerActivityID = tActivity.ID)'); slStrings.Add(' LEFT JOIN tElement ON SID.CustomerElementID = tElement.ID)'); slStrings.Add(' LEFT JOIN tDepartment ON tCentre.DepartmentID = tDepartment.ID)'); slStrings.Add(' LEFT JOIN tGroup ON tDepartment.GroupID = tGroup.ID)'); slStrings.Add(' LEFT JOIN tSubledger ON SID.CustomerSubledgerID = tSubledger.ID)'); slStrings.Add(' LEFT JOIN tCustomer ON tGroup.CustomerID = tCustomer.ID'); slStrings.Add('WHERE SID.ServiceID LIKE '''+sServiceName+''') AS SID1'); slStrings.Add(' LEFT JOIN tElement ON SID1.RevElementID = tElement.ID) AS SID2'); slStrings.Add(' LEFT JOIN tElement ON SID2.ExpElementID = tElement.ID'); slStrings.Add('ORDER BY SID2.ServiceID'); qRelExpService.Close; qRelExpService.SQL := slStrings; qRelExpService.Open; PageControl1.ActivePage := TabExpendRelease; slStrings.Free; end; // RxDBGridRelExpSummaryCellClick. procedure TMainForm.RxDBGridDubiousSummaryDblClick(Sender: TObject); var slStrings : TStringList; sBatchID : string; sServiceID : string; begin if not (qDubSummary.Bof and qDubSummary.Eof) then begin sBatchID := IntToStr(qDubSummary.FieldByName('BatchID').AsInteger); sServiceID := IntToStr(qDubSummary.FieldByName('ServiceID').AsInteger); slStrings := TStringList.Create; slStrings.Add('SELECT tTransaction.BatchID, tServiceID.ServiceID, '+ 'Min(tTransaction.FromDate) AS "DateFrom",'); slStrings.Add(' Max(tTransaction.ToDate) AS "DateTo", '+ 'tTransactionType.Description AS "TransactionType",'); slStrings.Add(' Sum(tTransaction.AmountExGST) AS "TotalExGST", '+ 'Sum(tTransaction.GSTAmount) AS "TotalGST",'); slStrings.Add(' Sum(tTransaction.AmountIncGST) AS "TotalIncGST"'); slStrings.Add('FROM ((tTransaction'); slStrings.Add('LEFT JOIN tDubiousResult '+ 'ON ((tTransaction.BatchID = tDubiousResult.BatchID)'); slStrings.Add(' AND (tTransaction.ServiceID = tDubiousResult.ServiceID)))'); slStrings.Add('LEFT JOIN tServiceID ON tTransaction.ServiceID = tServiceID.ID)'); slStrings.Add('LEFT JOIN tTransactionType '+ 'ON tTransaction.TransactionTypeID = tTransactionType.ID'); slStrings.Add('WHERE tTransaction.BatchID = '+sBatchID); slStrings.Add('AND tServiceID.ID = '+sServiceID); slStrings.Add('AND tDubiousResult.ID IS NOT NULL'); slStrings.Add('AND tTransaction.RevenueFlag <> 0'); slStrings.Add('AND tTransactionType.Description NOT LIKE ''%Lost%'''); slStrings.Add('GROUP BY tTransaction.BatchID, tServiceID.ServiceID, '+ 'tTransactionType.Description'); slStrings.Add('ORDER BY tServiceID.ServiceID'); TabDubiousDetail.TabVisible := True; TabDubiousDetail.Visible := True; qDubDetail.Close; qDubDetail.SQL := slStrings; qDubDetail.Open; PageControl1.ActivePage := TabDubiousDetail; slStrings.Free; end; // if not (qDubSummary.Bof and qDubSummary.Eof) then begin. end; // RxDBGridDubiousSummaryDblClick. procedure TMainForm.RxDBGridCESummaryDblClick(Sender: TObject); var slStrings : TStringList; sBatchID : string; begin sBatchID := IntToStr(qCESummary.FieldByName('BatchID').AsInteger); slStrings := TStringList.Create; slStrings.Add('SELECT DC.*, tCriticalType.Description AS "CriticalType"'); slStrings.Add('FROM (SELECT tDataCollection.BatchID, tDataCollection.ServiceID,'+ ' tDataCollection.StatusID,'); slStrings.Add(' tTransactionGroup.Description AS "TransactionType", '); slStrings.Add(' Sum(tDataCollection.AmountExGST) AS "TotalExGST", '+ 'Sum(tDataCollection.GSTAmount) AS "TotalGST",'); slStrings.Add(' Sum(tDataCollection.AmountIncGST) AS "TotalIncGST", '+ 'COUNT(tDataCollection.StatusID) AS "RecordCount"'); slStrings.Add('FROM (tDataCollection'); slStrings.Add('LEFT JOIN tTransactionType '+ 'ON tDataCollection.TransactionTypeID = tTransactionType.ID)'); slStrings.Add('LEFT JOIN tTransactionGroup '+ 'ON tTransactionType.TransactionGroupID = tTransactionGroup.ID'); slStrings.Add('WHERE tDataCollection.BatchID = '+sBatchID); slStrings.Add('AND tDataCollection.StatusID IS NOT NULL'); slStrings.Add('GROUP BY tDataCollection.BatchID, tDataCollection.ServiceID, '+ 'tTransactionGroup.Description, tDataCollection.StatusID) AS DC'); slStrings.Add('LEFT JOIN tCriticalType ON DC.StatusID = tCriticalType.ID'); slStrings.Add('ORDER BY DC.ServiceID'); TabCriticalResolution.TabVisible := True; TabCriticalResolution.Visible := True; qCEResolution.Close; qCEResolution.SQL := slStrings; qCEResolution.Open; PageControl1.ActivePage := TabCriticalResolution; slStrings.Free; end; // RxDBGridCESummaryDblClick. procedure TMainForm.SaveBatch( var nBatchNumber, nBatchStatusNumber: LongInt); var qryStringList : TStringList; FieldStringList : TStringList; ValueStringList : TStringList; nListItems : integer; sListFields : String; sListValues : String; MinimumDate : TDateTime; nBatchID : integer; lUpdateBatch : boolean; lFound : boolean; begin nBatchID := nBatchNumber; lNewBatch := (nBatchID = 0); lUpdateBatch := (not lNewBatch); MinimumDate := dDateToday - 1000; qryStringList := TStringList.Create; FieldStringList := TStringList.Create; ValueStringList := TStringList.Create; sListFields := '('; if lNewBatch then sListValues := 'VALUES (' else sListValues := 'SET '; sNewBatchStatusID := '0'; if lNewBatch then begin sListFields := sListFields +'BillingPeriod,'; sListValues := sListValues +''''+cbBillingPeriod.Text+''','; sListFields := sListFields + 'BatchTypeID,'; sListValues := sListValues + sNewBatchTypeID+','; if not VarIsNull(qBatchType['SupplierID']) then begin sListFields := sListFields + 'SupplierID,'; sListValues := sListValues +IntToStr(qBatchType['SupplierID'])+','; end; // SupplierID. if edtAccountNo.Visible and (Length(Trim(edtAccountNo.Text)) > 0) then begin if VarIsNull(qBatchType['ValidAccountID']) then begin if (Trim(edtAccountNo.Text) = Trim(qBatchType['ValidAccountID'])) then lFound := True else with qBatchType do lFound := Locate('ValidAccountNo', VarArrayOf([Trim(edtAccountNo.Text)]), [loCaseInsensitive]); end else lFound := True; if lFound then begin sListFields := sListFields + 'SupplierAccountID,'; sListValues := sListValues + IntToStr(qBatchType['ValidAccountID'])+','; end; end; // SupplierAccountID. end; // if lNewBatch then begin. if edtInvoiceNo.Visible then begin sListFields := sListFields + 'InvoiceNo,'; if lUpdateBatch then sListValues := sListValues +'InvoiceNo ='; if (Length(Trim(edtInvoiceNo.Text))>0) then sListValues := sListValues + ''''+ Trim(edtInvoiceNo.Text) +''',' else sListValues := sListValues + 'NULL,'; end; // edtInvoiceNo. if not lUpdateBatch or edtTenderNo.Visible then begin sListFields := sListFields + 'TenderNo,'; if lUpdateBatch then sListValues := sListValues +'TenderNo ='; if (Length(Trim(edtTenderNo.Text))>0) then sListValues := sListValues + ''''+ Trim(edtTenderNo.Text) +''',' else sListValues := sListValues + 'NULL,'; end; if edtTotalRevenue.Visible then begin sListFields := sListFields + 'TotalRevenue,'; if lUpdateBatch then sListValues := sListValues +'TotalRevenue ='; if (Length(Trim(edtTotalRevenue.Text))>0) then sListValues := sListValues + Trim(edtTotalRevenue.Text) +',' else sListValues := sListValues + 'NULL,'; end; // edtTotalRevenue. if edtTotalPayable.Visible then begin sListFields := sListFields + 'TotalPayable,'; if lUpdateBatch then sListValues := sListValues +'TotalPayable ='; if (Length(Trim(edtTotalPayable.Text))>0) then sListValues := sListValues + Trim(edtTotalPayable.Text)+',' else sListValues := sListValues + 'NULL,'; end; // edtTotalPayable. if edtTotalCredits.Visible then begin sListFields := sListFields + 'TotalCredits,'; if lUpdateBatch then sListValues := sListValues +'TotalCredits ='; if (Length(Trim(edtTotalCredits.Text))>0) then sListValues := sListValues + Trim(edtTotalCredits.Text) +',' else sListValues := sListValues + 'NULL,'; end; // edtTotalCredits. if edtOpeningBalance.Visible then begin sListFields := sListFields + 'OpeningBalance,'; if lUpdateBatch then sListValues := sListValues +'OpeningBalance ='; if (Length(Trim(edtOpeningBalance.Text))>0) then sListValues := sListValues + Trim(edtOpeningBalance.Text) +',' else sListValues := sListValues + 'NULL,'; end; // edtOpeningBalance. if edtPaymentsReceived.Visible then begin sListFields := sListFields + 'PaymentsReceived,'; if lUpdateBatch then sListValues := sListValues +'PaymentsReceived ='; if (Length(Trim(edtPaymentsReceived.Text))>0) then sListValues := sListValues + Trim(edtPaymentsReceived.Text) +',' else sListValues := sListValues + 'NULL,'; end; // edtPaymentsReceived. if edtTotalAdjustments.Visible then begin sListFields := sListFields + 'TotalAdjustments,'; if lUpdateBatch then sListValues := sListValues +'TotalAdjustments ='; if (Length(Trim(edtTotalAdjustments.Text))>0) then sListValues := sListValues + Trim(edtTotalAdjustments.Text) +',' else sListValues := sListValues + 'NULL,'; end; // edtTotalAdjustments. if edtGSTAdjustments.Visible then begin sListFields := sListFields + 'GSTAdjustment,'; if lUpdateBatch then sListValues := sListValues +'GSTAdjustment ='; if (Length(Trim(edtGSTAdjustments.Text))>0) then sListValues := sListValues + Trim(edtGSTAdjustments.Text) +',' else sListValues := sListValues + 'NULL,'; end; // edtGSTAdjustments. if edtGST.Visible then begin sListFields := sListFields + 'GSTAmount,'; if lUpdateBatch then sListValues := sListValues +'GSTAmount ='; if (Length(Trim(edtGST.Text))>0) then sListValues := sListValues + Trim(edtGST.Text) +',' else sListValues := sListValues + 'NULL,'; end; // edtGST. if lNewBatch then begin sListFields := sListFields + 'BatchStartDate,'; sListValues := sListValues + ''''+ StandardDateString(dDateToday) +''','; // sListFields := sListFields + 'BatchStartDate, BatchEndDate,'; // sListValues := sListValues + ''''+ Copy(cbBillingPeriod.Text,1,4) + // Copy(cbBillingPeriod.Text,6,2) +'01'', '''+ // StandardDateString(LastDayInMonth(StrDMYtoDate('01/'+ // Copy(cbBillingPeriod.Text,6,2)+'/'+Copy(cbBillingPeriod.Text,1,4)))) +''','; end; FieldStringList.Add(sListFields); ValueStringList.Add(sListValues); sListFields := ''; sListValues := ''; if dtpPaymentDate.Visible and (dtpPaymentDate.Date > MinimumDate) then begin sListFields := sListFields + 'PaymentDate,'; if lUpdateBatch then sListValues := sListValues +'PaymentDate ='; sListValues := sListValues +''''+ StandardDateString(dtpPaymentDate.Date) + ''','; end; // dtpPaymentDate. if dtpIssueDate.Visible and (dtpIssueDate.Date > MinimumDate) then begin sListFields := sListFields + 'IssueDate,'; if lUpdateBatch then sListValues := sListValues +'IssueDate ='; sListValues := sListValues +''''+ StandardDateString(dtpIssueDate.Date) + ''','; end; // dtpIssueDate. if edtRounding.Visible then begin sListFields := sListFields + 'Rounding,'; if lUpdateBatch then sListValues := sListValues +' Rounding ='; if (Length(Trim(edtRounding.Text))>0) then sListValues := sListValues + Trim(edtRounding.Text) +',' else sListValues := sListValues + 'NULL,'; end; // edtRounding. if (dtpRentStartDate.Date > MinimumDate) then begin sListFields := sListFields + 'RentStartDate,'; if lUpdateBatch then sListValues := sListValues +'RentStartDate ='; sListValues := sListValues +''''+ StandardDateString(dtpRentStartDate.Date) + ''','; end; // dtpRentStartDate. if (dtpRentEndDate.Date > MinimumDate) then begin sListFields := sListFields + 'RentEndDate,'; if lUpdateBatch then sListValues := sListValues +'RentEndDate ='; sListValues := sListValues +''''+ StandardDateString(dtpRentEndDate.Date) +''','; end; // dtpRentEndDate. if (dtpCallStartDate.Date > MinimumDate) then begin sListFields := sListFields + 'CallsStartDate,'; if lUpdateBatch then sListValues := sListValues +'CallsStartDate ='; sListValues := sListValues +''''+ StandardDateString(dtpCallStartDate.Date) + ''','; end; // dtpCallStartDate. if (dtpCallEndDate.Date > MinimumDate) then begin sListFields := sListFields + 'CallsEndDate,'; if lUpdateBatch then sListValues := sListValues +'CallsEndDate ='; sListValues := sListValues +''''+ StandardDateString(dtpCallEndDate.Date) + ''','; end; // dtpCallEndDate. if dtpPaymentDate.Visible and (dtpPaymentDate.Date > MinimumDate) then begin sListFields := sListFields + 'PayByDate,'; if lUpdateBatch then sListValues := sListValues +'PayByDate ='; sListValues := sListValues +''''+ StandardDateString(dtpPaymentDate.Date) + ''','; end; // dtpPaymentDate. if edtOrderNumber.Visible then begin sListFields := sListFields + 'OrderNumber,'; if lUpdateBatch then sListValues := sListValues +'OrderNumber ='; if (Length(Trim(edtOrderNumber.Text))>0) then sListValues := sListValues +''''+ Trim(edtOrderNumber.Text) + ''',' else sListValues := sListValues + 'NULL,'; end; // dtpPaymentDate. if edtRequisitionNumber.Visible then begin sListFields := sListFields + 'RequisitionNumber,'; if lUpdateBatch then sListValues := sListValues +'RequisitionNumber ='; if (Length(Trim(edtRequisitionNumber.Text))>0) then sListValues := sListValues +''''+ Trim(edtRequisitionNumber.Text) + ''',' else sListValues := sListValues + 'NULL,'; end; // edtRequisitionNumber. if lNewBatch then begin // Always save the BatchStatus - never set by the user, only by the program. // Assumes the batch status is stored in order (1st to last, Initiate=1st). with qry do begin Close; SQL.Clear; SQL.Add('SELECT id FROM tBatchStatus'); SQL.Add('ORDER BY id ASC'); Open; First; sNewBatchStatusID := Trim(IntToStr(FieldByName('id').AsInteger)); Close; end; // qry. sListFields := sListFields + ' BatchStatusID'; sListValues := sListValues + ' ' + sNewBatchStatusID; end; // if lNewBatch then begin. if (ValueStringList.Count > 1) or (Length(ValueStringList[0])>5) then begin // Set to save - check that there are no "commas" at the end of the string. if (Right(sListFields,1) = ',') then begin sListFields := Copy(sListFields,1,Length(sListFields)-1); sListValues := Copy(sListValues,1,Length(sListValues)-1); end; sListFields := sListFields + ')'; if lNewBatch then begin sListValues := sListValues + ')'; end; FieldStringList.Add(sListFields); ValueStringList.Add(sListValues); if lNewBatch then begin qryStringList.Add('INSERT INTO tBatch'); for nListItems := 0 to FieldStringList.Count-1 do qryStringList.Add(FieldStringList[nListItems]); end else begin qryStringList.Add('UPDATE tBatch'); end; for nListItems := 0 to ValueStringList.Count-1 do qryStringList.Add(ValueStringList[nListItems]); if lUpdateBatch then qryStringList.Add('WHERE tBatch.ID = '+IntToStr(nBatchID)); with qry do begin Close; SQL := qryStringList; ExecSQL; end; // qry. if lNewBatch then begin // Update the Batch number on the form. qryStringList.Clear; qryStringList.Add('SELECT ID FROM tBatch'); qryStringList.Add('WHERE BatchTypeID = '+sNewBatchTypeID); qryStringList.Add('AND (BatchStartDate = '''+ StandardDateString(dDateToday)+''')'); qryStringList.Add('ORDER BY ID DESC'); with qry do begin Close; SQL := qryStringList; Open; nBatchID := FieldByName('id').AsInteger; // Read the new BatchID created. Close; end; // qry. nBatchNumber := nBatchID; // Return to calling program. edtBatchID.Text := IntToStr(nBatchID); // Last entry in the table. qryStringList.Clear; qryStringList.Add('INSERT INTO tBatchStatusHistory'); qryStringList.Add('(BatchID, BatchStatusID, StatusChangeDate,UserID)'); qryStringList.Add('Values ('+Trim(IntToStr(nBatchID))+ ', '+sNewBatchStatusID +', ''' + StandardDateString(dDateToday)+ ''', '+ s_User_ID +')'); with qry do begin Close; SQL := qryStringList; ExecSQL; end; end; // if lNewBatch then begin. // qry.Close; end; // if (ValueStringList.Count > 1) then begin. // Return the current status of this batch. with qry do begin SQL.Clear; SQL.Add('SELECT BatchStatusID FROM tBatch'); SQL.Add('WHERE id = '+IntToStr(nBatchID)); Open; if Eof then nBatchStatusNumber := 0 else nBatchStatusNumber := FieldByName('BatchStatusID').AsInteger; Close; end; // qry. qryStringList.Free; FieldStringList.Free; ValueStringList.Free; end; // SaveBatch. procedure TMainForm.SaveMultiManualData; // Action when RxDBGridMultiManual.Visible and btnOkayClick called. var sBatchID : string; lContinue : boolean; qryStringList : TStringList; lEarlyRevenue : boolean; sGSTrate : string; begin Str(GST_RATE:6:4,sGSTrate); qMultiManual.ApplyUpdates; qMultiManual.Close; // Remove old data in tDataCollection and then replace it with new data. sBatchID := edtBatchID.Text; DeleteTransactions('tDataCollection', StrToInt(sBatchID), '', '', '=', '', lContinue); WriteMultiManualToDC(StrToInt(sBatchID)); // Test if Revenue entries are to be added to tDataCollection from tRate. qryStringList := TStringList.Create; qryStringList.Add('SELECT tBatchType.MultiManualEntry, tBatchType.EarlyRevenue'); qryStringList.Add('FROM tBatchType'); qryStringList.Add('INNER JOIN tBatch ON tBatchType.ID = tBatch.BatchTypeID'); qryStringList.Add('WHERE tBatch.ID = ' + sInitiateBatchID); qry.Close; qry.SQL := qryStringList; qry.Open; lEarlyRevenue := (qry.FieldByName('MultiManualEntry').AsBoolean AND qry.FieldByName('EarlyRevenue').AsBoolean); qry.Close; if lEarlyRevenue then begin // First, prevent creating additional revenue entries when exporting // from tDataCollection to tTransactions later by setting RevenueFlag=0. // Revenue transactions for OTHER transactiontypes will be added later // as usual when transaferring to the tTransaction table. qryStringList.Clear; qryStringList.Add('UPDATE tDataCollection'); qryStringList.Add('SET RevenueFlag = 0'); qryStringList.Add('WHERE BatchID = ' + sBatchID); qryStringList.Add('AND TransactionTypeID IN (SELECT TT.ID'); qryStringList.Add('FROM tTransactionType TT'); qryStringList.Add('LEFT JOIN tTransactionGroup TG '+ 'ON TT.TransactionGroupID = TG.ID'); qryStringList.Add('WHERE TG.Description NOT LIKE ''OTHER'')'); qry.SQL := qryStringList; qry.ExecSQL; // Now we add the Revenue items wherever services are found in tRate. qryStringList.Clear; qryStringList.Add('INSERT INTO tDataCollection'); qryStringList.Add('(SequenceNo, ServiceID, BatchID,'); qryStringList.Add(' RateDescription, FromDate, ToDate, TransactionTypeID,'); qryStringList.Add(' RevenueFlag, GSTFlag, AmountExGST, GSTAmount, AmountIncGST)'); qryStringList.Add('SELECT DC3.SequenceNo, DC3.ServiceID, DC3.BatchID,'); qryStringList.Add(' DC3.RateDescription, DC3.FromDate, DC3.ToDate,'); qryStringList.Add(' DC3.TransactionTypeID, DC3.RevenueFlag, DC3.GSTFlag,'); qryStringList.Add(' DC3.AmountExGST,'); qryStringList.Add(' DC3.GSTAmount,'); qryStringList.Add(' (DC3.AmountExGST + DC3.GSTAmount) AS "AmountIncGST"'); qryStringList.Add('FROM (SELECT DC2.SequenceNo, DC2.ServiceID, DC2.BatchID,'); qryStringList.Add(' DC2.RateDescription, DC2.FromDate, DC2.ToDate,'); qryStringList.Add(' DC2.TransactionTypeID, (1) AS "RevenueFlag", DC2.GSTFlag,'); qryStringList.Add(' DC1.AmountExGST,'); qryStringList.Add(' CAST((CASE WHEN DC2.GSTFlag = ''Y'' '+ 'THEN (DC1.AmountExGST * '+sGSTrate+') ELSE 0 END) AS money) AS "GSTAmount"'); { Following replaced to allow for Rent and Call entries in a single MultiManual line. qryStringList.Add('FROM (SELECT DC0.*, (Min(tRate.Cost) / 12 * '+ 'DC0.Frequency) AS "AmountExGST"'); qryStringList.Add('FROM (SELECT DC.ID AS "DataCollectionID", '+ 'tServiceID.ServiceTypeID,'); qryStringList.Add(' (CASE tServiceType.Frequency'); qryStringList.Add(' WHEN NULL THEN 1'); qryStringList.Add(' WHEN 0 THEN 1'); qryStringList.Add(' ELSE tServiceType.Frequency'); qryStringList.Add(' END) AS "Frequency"'); qryStringList.Add('FROM tDataCollection DC'); qryStringList.Add('LEFT JOIN tServiceID '+ 'ON DC.ServiceID = tServiceID.ServiceID'); qryStringList.Add('LEFT JOIN tServiceType '+ 'ON tServiceID.ServiceTypeID = tServiceType.ID'); qryStringList.Add('WHERE DC.BatchID = '+sBatchID); qryStringList.Add('AND DC.RevenueFlag = 0) AS DC0'); qryStringList.Add('LEFT JOIN tRate '+ 'ON ((DC0.ServiceTypeID = tRate.ServiceTypeID) AND (tRate.Active = 1))'); qryStringList.Add('GROUP BY DC0.DataCollectionID, '+ 'DC0.ServiceTypeID, DC0.Frequency) AS DC1'); // Above replaced by the following - RJC 070617. } qryStringList.Add('FROM (SELECT DC00.DataCollectionID, DC00.ServicetypeID,'); qryStringList.Add(' (CASE TG.Description'); // qryStringList.Add(' WHEN ''RENT'' THEN DC00.RentExGST'); qryStringList.Add(' WHEN ''RENT'' THEN'); qryStringList.Add(' (CASE WHEN DC00.RentExGST IS NULL THEN DCX.AmountExGST'); qryStringList.Add(' ELSE DC00.RentExGST END)'); // qryStringList.Add(' WHEN ''CALL'' THEN DC00.CallExGST'); qryStringList.Add(' WHEN ''CALL'' THEN ('+ '(CASE WHEN DC00.CallExGSTRate IS NULL THEN 1.0 ELSE DC00.CallExGSTRate END)'+ ' * DCX.AmountExGST)'); qryStringList.Add(' ELSE DCX.AmountExGST END) AS "AmountExGST"'); qryStringList.Add('FROM (SELECT DC0.*, (Min(tRate.Cost) / 12 * '+ 'DC0.Frequency) AS "RentExGST",'); qryStringList.Add(' (Min(tRate.PartCost) / 100) AS "CallExGSTRate"'); qryStringList.Add('FROM (SELECT DC.ID AS "DataCollectionID", '+ 'tServiceID.ServiceTypeID,'); qryStringList.Add(' (CASE tServiceType.Frequency'); qryStringList.Add(' WHEN NULL THEN 1'); qryStringList.Add(' WHEN 0 THEN 1'); qryStringList.Add(' ELSE tServiceType.Frequency'); qryStringList.Add(' END) AS "Frequency"'); qryStringList.Add('FROM tDataCollection DC'); qryStringList.Add('LEFT JOIN tServiceID '+ 'ON DC.ServiceID = tServiceID.ServiceID'); qryStringList.Add('LEFT JOIN tServiceType '+ 'ON tServiceID.ServiceTypeID = tServiceType.ID'); qryStringList.Add('WHERE DC.BatchID = '+sBatchID); qryStringList.Add('AND DC.RevenueFlag = 0) AS DC0'); qryStringList.Add('LEFT JOIN tRate '+ 'ON ((DC0.ServiceTypeID = tRate.ServiceTypeID) AND (tRate.Active = 1))'); qryStringList.Add('GROUP BY DC0.DataCollectionID, DC0.ServiceTypeID, '+ 'DC0.Frequency) AS DC00'); qryStringList.Add('LEFT JOIN tDataCollection DCX '+ 'ON DC00.DataCollectionID = DCX.ID'); qryStringList.Add('LEFT JOIN tTransactionType TT '+ 'ON DCX.TransactionTypeID = TT.ID'); qryStringList.Add('LEFT JOIN tTransactionGroup TG '+ 'ON TT.TransactionGroupID = TG.ID) AS DC1'); // ... and Remainder of the original SQL statement follows. qryStringList.Add('LEFT JOIN tDataCollection DC2 '+ 'ON DC1.DataCollectionID = DC2.ID) AS DC3'); qry.Close; qry.SQL := qryStringList; qry.ExecSQL; end; qry.Close; UpdateBatchTotals(StrToInt(sBatchID)); UpdateBatchStatus(StrToInt(sBatchID), BATCH_STATUS_IMPORT); CloseTab(TabInitiate); PaintPPTreeView(cbBillingPeriod.Text); end; // SaveMultiManualData. procedure TMainForm.btnOkayClick(Sender: TObject); var nNewBatchNumber: integer; // sSupplierName : string; sBatchTypeName : string; sImportFromDir : string; nBatchTypeID : integer; lBatchNew : boolean; lContinue : boolean; nBatchStatusNumber : integer; dStartDate : TDateTime; dEndDate : TDateTime; BillingPeriodDate : TDateTime; begin if (PageControl1.ActivePage = TabInitiate) then begin if RxDBGridMultiManual.Visible then begin // Save the data entered in the MultiManual entry screen.. SaveMultiManualData; end // RxDBGridMultiManual.Visible. else begin // Okay button is in regular Initiate screen - process input selected. lBatchNew := (Length(Trim(edtBatchID.Text)) = 0); lContinue := True; if lBatchNew then begin nNewBatchNumber := 0; lContinue := (MessageDlg('About to add to Period: '+ cbBillingPeriod.Text+#13#10'Continue?', mtConfirmation,[mbYes,mbNo],0) = mrYes); end else nNewBatchNumber := StrToInt(edtBatchID.Text); if lContinue then begin SaveBatch( nNewBatchNumber, nBatchStatusNumber ); sInitiateBatchID := IntToStr(nNewBatchNumber); if lBatchNew or (nBatchStatusNumber = BATCHSTAT_INIT) or ((nBatchStatusNumber < BATCHSTAT_TRANSNS) and (lAutomaticEntry or ((nProgramID = PIMPORTBILLINGFILE_TELSTRA_DATA_SMARTBUSINCLUDEDRATE)))) then begin // (lAutomaticEntry or ((nProgramID // = PIMPORTBILLINGFILE_TELSTRA_DATA_SMARTBUSINCLUDEDRATE) // option?:: or (nProgramID = PIMPORTBILLINGFILE_OPTUS_MOBILE)))) then begin GetBatchTypeName(nNewBatchNumber,sBatchTypeName,nBatchTypeID); sBatchTypeName := UpperCase(sBatchTypeName); if lAutomaticEntry then begin // Check that the date range is valid. dStartDate := DateOnly(dtpCallStartDate.Date); dEndDate := DateOnly(dtpCallEndDate.Date); BillingPeriodDate := StrDMYtoDate('01/'+ Copy(cbBillingPeriod.Text,6,2)+'/'+Copy(cbBillingPeriod.Text,1,4)); if (dStartDate < BillingPeriodDate) or (DateOnly(LastDayInMonth(BillingPeriodDate)) < dStartDate) or (dEndDate < BillingPeriodDate) or (DateOnly(LastDayInMonth(BillingPeriodDate)) < dEndDate) then begin MessageDlg('Dates must be within the Billing Period.', mtWarning, [mbOk], 0); end else if (dEndDate < dStartDate) then begin MessageDlg('Start and End Dates are transposed. Try again.', mtWarning, [mbOk], 0); end else begin if (nProgramID = PAUTOMATIC_QUETZAL_TAXI) then begin {// 110918 Above replaces below to remove dependence on parent name -RJC. if (Pos('TAXI', sBatchTypeName) > 0) then begin } TaxiInvoice( nNewBatchNumber, dStartDate, dEndDate, sBatchTypeName); end // Taxi. else if (nProgramID = PAUTOMATIC_QUETZAL_FAULTS_RADIO) then begin {// 110918 Above replaces below to remove dependence on parent name -RJC. else if (Pos('RADIO', sBatchTypeName) > 0) then begin } RadioInvoice( nNewBatchNumber, dStartDate, dEndDate, sBatchTypeName); end // Radio. else if (nProgramID = PAUTOMATIC_QUETZAL_FAULTS) then begin {// 110918 Above replaces below to remove dependence on parent name -RJC. else if (Pos('FAULT', sBatchTypeName) > 0) then begin } QuetzalInvoice( nNewBatchNumber, dStartDate, dEndDate, sBatchTypeName); end // Quetzal. else if (nProgramID = PAUTOMATIC_SECURITY) then begin {// 110918 Above replaces below to remove dependence on parent name -RJC. else if (Pos('SECURITY', sBatchTypeName) > 0) then begin } SecurityInvoice( nNewBatchNumber, dStartDate, dEndDate, sBatchTypeName); end // Security. else if lAnnualChargesEntry then begin {// 110918 Above replaces below to remove dependence on parent name -RJC. else if (Pos('ANNUAL', sBatchTypeName) > 0) OR ((nProgramID > 2000) AND (nProgramID < 3001)) then begin } if (nProgramID = PAUTOMATIC_ANNUAL_KAMCO) then begin {// 110918 Above replaces below to remove dependence on parent name -RJC. if (Pos('KAMCO', sBatchTypeName) > 0) then begin } ImportDataWithRefund( nNewBatchNumber, cbBillingPeriod.Text, dStartDate, dEndDate, sBatchTypeName, nBatchTypeID); end // KAMCO.. else begin AnnualChargesInvoice( nNewBatchNumber, dStartDate, dEndDate, sBatchTypeName, nBatchTypeID); end; // KAMCO and Everything else.. end // Annual. else if (nProgramID = PAUTOMATIC_SERVICE) then begin {// 110918 Above replaces below to remove dependence on parent name -RJC. else if (Pos('SERVICE', sBatchTypeName) > 0) then begin } ServiceInvoice( nNewBatchNumber, dStartDate, dEndDate, sBatchTypeName, nBatchTypeID); end // Service. else if (nProgramID = PAUTOMATIC_QUOTE_PURCHASE) then begin {// 110918 Above replaces below to remove dependence on parent name -RJC. else if (Pos(' PURCH', sBatchTypeName) > 0) then begin } QuoteSystemPurchasesInvoice( nNewBatchNumber, dStartDate, dEndDate, sBatchTypeName); end else if (nProgramID = PAUTOMATIC_QUOTE_INSTALL) then begin {// 110918 Above replaces below to remove dependence on parent name -RJC. else if (Pos(' INSTAL', sBatchTypeName) > 0) then begin } QuoteSystemJobsInvoice( nNewBatchNumber, dStartDate, dEndDate, sBatchTypeName); end else if (nProgramID = PAUTOMATIC_TELMAX_DIVERSIONS) then begin {// 110918 Above replaces below to remove dependence on parent name -RJC. else if (Pos(' DIVERS', sBatchTypeName) > 0) then begin } with qry do begin Close; SQL.Clear; SQL.Add('SELECT tParameter.ParmText'); SQL.Add('FROM tParameter'); SQL.Add('WHERE tParameter.Parm LIKE ''InDataRootDirectory'''); Open; if Eof then sImportFromDir := '' else sImportFromDir := FieldByName('ParmText').AsString; Close; end; // qry. TelmaxSACCInvoice(nNewBatchNumber, dStartDate, dEndDate, sBatchTypeName, sImportFromDir ); end else if (nProgramID = PAUTOMATIC_TELMAX_RENT_CALLS) then begin {// 110918 Above replaces below to remove dependence on parent name -RJC. else begin } with qry do begin Close; SQL.Clear; SQL.Add('SELECT tParameter.ParmText'); SQL.Add('FROM tParameter'); SQL.Add('WHERE tParameter.Parm LIKE ''InDataRootDirectory'''); Open; if Eof then sImportFromDir := '' else sImportFromDir := FieldByName('ParmText').AsString; Close; end; // qry. TelmaxInvoice(nNewBatchNumber, dStartDate, dEndDate, sBatchTypeName, sImportFromDir ); end; // Telmax. end; // valid date range. end // Automatic entry. else if not lManualEntry then begin qry.Close; qry.SQL.Clear; qry.SQL.Add('SELECT tParameter.ParmText'); qry.SQL.Add('FROM tParameter'); qry.SQL.Add('WHERE tParameter.Parm LIKE ''ImportDirectory'''); qry.Open; if qry.Eof then sImportFromDir := '' else sImportFromDir := qry.FieldByName('ParmText').AsString; qry.Close; if (nProgramID = PIMPORTBILLINGFILE_TELSTRA_FIXED_EBS) or (nProgramID = PIMPORTBILLINGFILE_TELSTRA_EBS_PABX) then begin {// 110918 Above replaces below to remove dependence on parent name -RJC. else if (Pos('TELSTRA',sBatchTypeName)>0) then begin } CloseDisabled := True; // Read in the Telstra bill. TelstraInvoiceFixedEBS( nNewBatchNumber, sImportFromDir, nProgramID); CloseDisabled := False; end // if TELSTRA FIXED EBS. else if (nProgramID = PIMPORTBILLINGFILE_TELSTRA_ORIG_EBS_FIXED) then begin CloseDisabled := True; // Read in the Telstra bill. TelstraInvoiceFixedOrigEBS( nNewBatchNumber, sImportFromDir); CloseDisabled := False; end // if TELSTRA FIXED EBS - Original version. else if (nProgramID = PIMPORTBILLINGFILE_TELSTRA_OBS_FIXED) or (nProgramID = PIMPORTBILLINGFILE_TELSTRA_OBS_PABX) then begin // The PABX version is special to make the totals right. CloseDisabled := True; // Read in the Telstra bill. TelstraInvoiceFixedOBS( nNewBatchNumber, sImportFromDir, nProgramID); CloseDisabled := False; end // if TELSTRA FIXED OBS. else if (nProgramID = PIMPORTBILLINGFILE_TELSTRA_MOBILE) then begin CloseDisabled := True; // Read in the Telstra bill. TelstraInvoiceMobileEBS( nNewBatchNumber, sImportFromDir ); CloseDisabled := False; end // if TELSTRA MOBILE EBS. else if (nProgramID = PIMPORTBILLINGFILE_TELSTRA_OBS_MOBILE) then begin CloseDisabled := True; // Read in the Telstra bill. TelstraInvoiceMobileOBS( nNewBatchNumber, sImportFromDir ); CloseDisabled := False; end // if TELSTRA MOBILE OBS. else if (nProgramID = PIMPORTBILLINGFILE_TELSTRA_DATA_SMARTBUSINCLUDEDRATE) then begin // Mobile data with fixed charge + fee for excess data usage. CloseDisabled := True; // Telstra Data. // Read in the Telstra data bill. TelstraInvoiceDataEBS( False, True, nNewBatchNumber, sImportFromDir ); CloseDisabled := False; end // if TELSTRA DATA EBS SMARTBUSINCLUDEDRATE. else if (nProgramID = PIMPORTBILLINGFILE_TELSTRA_OBS_DATA_SMARTBUSINCLUDEDRATE) then begin // Mobile data with fixed charge + fee for excess data usage. CloseDisabled := True; // Telstra Data. // Read in the Telstra data bill. TelstraInvoiceData( False, True, nNewBatchNumber, sImportFromDir ); CloseDisabled := False; end // TELSTRA DATA OBS SMARTBUSINCLUDEDRATE. else if (nProgramID = PIMPORTBILLINGFILE_TELSTRA_DATA_GPRSSMARTBUS) then begin {// 110918 Above replaces below to remove dependence on parent name -RJC. else if (Pos('SMARTBUS',sBatchTypeName)>0) then begin } CloseDisabled := True; // Telstra Data. // Read in the Telstra data bill. TelstraInvoiceDataEBS( False, False, nNewBatchNumber, sImportFromDir ); CloseDisabled := False; end // TELSTRA DATA EBS SMARTBUS GPRS. else if (nProgramID = PIMPORTBILLINGFILE_TELSTRA_OBS_DATA_GPRSSMARTBUS) then begin CloseDisabled := True; // TelstraOBS Data. // Read in the Telstra data bill. TelstraInvoiceData( False, False, nNewBatchNumber, sImportFromDir ); CloseDisabled := False; end // TELSTRA DATA OBS SMARTBUS GPRS. else if (nProgramID = PIMPORTBILLINGFILE_TELSTRA_DATA_GPRS) then begin {// 110918 Above replaces below to remove dependence on parent name -RJC. else if (Pos('CAMS',sBatchTypeName)>0) or (Pos('KAMCO',sBatchTypeName)>0) then begin } CloseDisabled := True; // Telstra Data. // Read in the Telstra data bill. TelstraInvoiceDataEBS( True, False, nNewBatchNumber, sImportFromDir ); CloseDisabled := False; end // TELSTRA DATA EBS - CAMS or KAMCO. else if (nProgramID = PIMPORTBILLINGFILE_TELSTRA_OBS_DATA_GPRS) then begin CloseDisabled := True; // Telstra Data. // Read in the Telstra data bill. TelstraInvoiceData( True, False, nNewBatchNumber, sImportFromDir ); CloseDisabled := False; end // TELSTRA DATA OBS CAMS or KAMCO. else if (nProgramID = IMPORTBILLINGFILE_VODAFONE) then begin {// 110918 Above replaces below to remove dependence on parent name -RJC. else if (Pos('VODAFONE',sBatchTypeName)>0) then begin } CloseDisabled := True; // Read in the Vodafone bill. VodafoneInvoice( nNewBatchNumber, sImportFromDir ); CloseDisabled := False; end // if VODAFONE. else if (nProgramID = PIMPORTBILLINGFILE_OPTUS_FIXED_ORIGINAL) or (nProgramID = PIMPORTBILLINGFILE_OPTUS_FIXED_LONG) then begin {// 110918 Above replaces below to remove dependence on parent name -RJC. else if (Pos('OPTUS ',sBatchTypeName)>0) then begin } CloseDisabled := True; // OPTUS Fixed. // Read in the Optus bill -Structure tested in OptusFixedInvoice(). OptusFixedInvoice( nProgramID, nNewBatchNumber, sImportFromDir ); CloseDisabled := False; end // if OPTUS FIXED. else if (nProgramID = PIMPORTBILLINGFILE_OPTUS_MOBILE) then begin {// 110918 Above replaces below to remove dependence on parent name -RJC. if (Pos(' MOB',sBatchTypeName)>0) then } // Read in the Optus bill. // OPTUS Mobile. OptusMobileInvoice( nNewBatchNumber, sImportFromDir ); CloseDisabled := False; end // if OPTUS MOBILE. else if (nProgramID = PIMPORTBILLINGFILE_OPTUS_MOBILEDATA) then begin // Read in the Optus bill. // OPTUS Mobile. OptusMobileDataInvoice( nNewBatchNumber, sImportFromDir ); CloseDisabled := False; end; // if OPTUS MOBILEDATA. end; // if not lManualEntry then begin. if lBatchNew then begin if lManualEntry then begin InitiateNode.Text := InitiateNode.Text+' '+IntToStr(nNewBatchNumber); end else begin CloseTab(TabInitiate); PaintPPTreeView(cbBillingPeriod.Text); end; // if lManualEntry then begin..else. end; // if lBatchNew then begin. end; // if lBatchNew then begin. // btnAddTransaction.Enabled := (not lAutomaticEntry and // (nBatchStatusNumber >= BATCHSTAT_INIT) and // (nBatchStatusNumber < BATCHSTAT_TRANSNS)); btnAddTransaction.Enabled := (not lAutomaticEntry or lSACCEntry or lTelmaxEntry or lAnnualChargesEntry) and ((nBatchStatusNumber >= BATCHSTAT_INIT) and (nBatchStatusNumber < BATCHSTAT_TRANSNS)); end; // if lContinue then begin. end; // RxDBGridMultiManual. end; // TabInitiate. end; // btnOkayClick. procedure TMainForm.GetSupplierName(const nBatchNumber: integer; var sName: string); var slQuery: TStringList; begin slQuery := TStringList.Create; slQuery.Add('SELECT tSupplier.SupplierName'); slQuery.Add('FROM tBatch'); slQuery.Add('LEFT JOIN tSupplier ON tBatch.SupplierID = tSupplier.ID'); slQuery.Add('WHERE tBatch.ID = '+IntToStr(nBatchNumber)); qry.Close; qry.SQL := slQuery; qry.Open; if qry.Eof then sName := '' else sName := Trim(qry.FieldByName('SupplierName').AsString); qry.Close; slQuery.Free; end; // GetSupplierName. procedure TMainForm.GetBatchTypeName(const nBatchNumber: integer; var sName: string; var nBatchTypeID: integer); var slQuery: TStringList; begin slQuery := TStringList.Create; slQuery.Add('SELECT tBatch.BatchTypeID, tBatchType.Description'); slQuery.Add('FROM tBatch'); slQuery.Add('LEFT JOIN tBatchType ON tBatch.BatchTypeID = tBatchType.ID'); slQuery.Add('WHERE tBatch.ID = '+IntToStr(nBatchNumber)); qry.Close; qry.SQL := slQuery; qry.Open; if qry.Eof then begin nBatchTypeID := 0; sName := '' end else begin nBatchTypeID := qry.FieldByName('BatchTypeID').AsInteger; sName := Trim(qry.FieldByName('Description').AsString); end; qry.Close; slQuery.Free; end; // GetBatchTypeName. procedure TMainForm.btnCancelClick(Sender: TObject); begin // Close the TabInitiate view too. if (PageControl1.ActivePage = TabInitiate) then begin if RxDBGridMultiManual.Visible then begin DisplayInitiateEditBoxes; qMultiManual.Close; end else begin InitiateNode.Parent.Selected := True; TabInitiate.Visible := False; TabInitiate.TabVisible := False; end; end; // TabInitiate. end; // btnCancelClick. procedure TMainForm.ResolveDubious(Sender: TObject); var // Called by btnDESApply and btnDESApplyAll buttons on nCount : integer; // the "Dubious Error Summary" tab only. sResolutionSet : string; slStrings : TStringList; qryValidRes : TQuery; nBatchID : integer; lFound : boolean; lChangeAll : boolean; begin if not (qDubSummary.Bof and qDubSummary.Eof) then begin nBatchID := RxDBGridDubiousSummary.DataSource.DataSet['BatchID']; lChangeAll := (Sender = btnDERApplyAll); if lChangeAll then RxDBGridDubiousSummary.SelectAll; if (RxDBGridDubiousSummary.SelCount > 0) then begin sResolutionSet := IntToStr(qDESResolution['DubiousResolutionID']); slStrings := TStringList.Create; slStrings.Add('SELECT tDubiousValidResolution.DubiousResolutionID, '+ 'tDubiousValidResolution.DubiousTypeID'); slStrings.Add('FROM tDubiousValidResolution'); slStrings.Add('WHERE tDubiousValidResolution.Active = 1'); slStrings.Add('AND tDubiousValidResolution.DubiousResolutionID = '+ sResolutionSet); slStrings.Add('AND tDubiousValidResolution.DubiousTypeID = '+ IntToStr(RxDBGridDubiousSummary.DataSource.DataSet.FieldByName( 'DubiousTypeID').AsInteger)); qryValidRes := TQuery.Create(SELF); qryValidRes.DatabaseName := 'dbPPdata'; qryValidRes.SQL := slStrings; qryValidRes.Open; if not (qryValidRes.Bof and qryValidRes.Eof) then begin for nCount := 0 to RxDBGridDubiousSummary.SelCount-1 do begin RxDBGridDubiousSummary.GotoSelection(nCount); if ((not lChangeAll) or VarIsNull( RxDBGridDubiousSummary.DataSource.DataSet['Resolution'])) and (RxDBGridDubiousSummary.DataSource.DataSet['BatchID']=nBatchID) then begin lFound := (qryValidRes.FieldByName('DubiousTypeID').AsInteger = RxDBGridDubiousSummary.DataSource.DataSet.FieldByName( 'DubiousTypeID').AsInteger); if lFound then begin slStrings.Clear; slStrings.Add('UPDATE tDubiousResult'); slStrings.Add('SET ResolutionID = '+ IntToStr(qryValidRes['DubiousResolutionID'])); slStrings.Add('WHERE tDubiousResult.id = '+ IntToStr(RxDBGridDubiousSummary.DataSource.DataSet['DID'])); qry.Close; qry.SQL := slStrings; qry.ExecSQL; qry.SQL.Clear; end; // if lFound then begin. end; // if not lChangeAll or VarIsNull() then begin. end; // for nCount := 0 to RxDBGridDubiousSummary.SelCount-1 do begin. end; // if not (qryValidRes.Bof and qryValidRes.Eof) then begin. slStrings.Free; qryValidRes.Close; qryValidRes.Free; end; // if qDubSummary.SelCount > 0 then begin. RxDBGridDubiousSummary.UnSelectAll; qDubSummary.Close; qDubSummary.Open; // Refresh the displayed data. end; // if not (qDubSummary.Bof and qDubSummary.Eof) then begin. end; // ResolveDubious. procedure TMainForm.RefreshResolutionChoices; var slStrings : TStringList; begin slStrings := TStringList.Create; slStrings.Add('SELECT tDubiousValidResolution.DubiousResolutionID, '+ 'tDubiousType.Description AS DubiousType, '+ 'tDubiousResolution.Description AS DubiousResolution'); slStrings.Add('FROM tDubiousValidResolution'); slStrings.Add('LEFT JOIN tDubiousType '+ 'ON tDubiousValidResolution.DubiousTypeID = tDubiousType.ID'); slStrings.Add('LEFT JOIN tDubiousResolution '+ 'ON tDubiousValidResolution.DubiousResolutionID = tDubiousResolution.ID'); slStrings.Add('WHERE tDubiousValidResolution.Active = 1'); if (qDubSummary.Active) and not qDubSummary.Eof then slStrings.Add('AND tDubiousValidResolution.DubiousTypeID = '+ IntToStr(qDubSummary.FieldByName('DubiousTypeID').AsInteger)); qDESResolution.Close; qDESResolution.SQL := slStrings; qDESResolution.Open; cbDESResolution.KeyValue := qDESResolution.FieldByName('DubiousResolution').AsString; slStrings.Free; end; // RefreshResolutionChoices. procedure TMainForm.RxDBGridDubiousSummaryCellClick(Column: TColumn); begin RefreshResolutionChoices; end; // RxDBGridDubiousSummaryCellClick. procedure TMainForm.btnReleaseRevenueClick(Sender: TObject); var slStrings : TStringList; nBatchID : integer; lRevenue : boolean; lContinue : boolean; lDubiousCheckOkay : boolean; wSelected : word; begin if (qDubSummary.Bof and qDubSummary.Eof) then begin if qBatchSummary.Active then nBatchID := qBatchSummary.FieldByName('BatchID').AsInteger else nBatchID := 0; end else begin nBatchID := qDubSummary.FieldByName('BatchID').AsInteger; end; if (nBatchID = 0) then begin MessageDlg('Unable to determine the Batch to process.', mtWarning, [mbOk],0); end else begin // // StartTime; // slStrings := TStringList.Create; slStrings.Add('SELECT BatchStatusID'); // Ensure Dubious Tests undertaken. slStrings.Add('FROM tBatch'); slStrings.Add('WHERE ID = '+IntToStr(nBatchID)); qry.Close; qry.SQL := slStrings; qry.Open; lDubiousCheckOkay := (qry.FieldByName('BatchStatusID').AsInteger = BATCHSTAT_DCHECKOK); lContinue := (qry.FieldByName('BatchStatusID').AsInteger = BATCHSTAT_DCHECK); qry.Close; if lContinue then begin slStrings.Clear; slStrings.Add('SELECT TOP 1 ID'); // Test that all errors resolved. slStrings.Add('FROM tDubiousResult'); slStrings.Add('WHERE BatchID = '+IntToStr(nBatchID)); slStrings.Add('AND ResolutionID IS NULL'); qry.Close; qry.SQL := slStrings; qry.Open; if qry.bof and qry.Eof then begin UpdateBatchStatus(nBatchID, MainForm.BATCH_STATUS_DCHECKOK); lDubiousCheckOkay := True; end; end; // if lContinue then begin. if lDubiousCheckOkay then begin wSelected := MessageDlg('All results resolved. '+ 'Release revenue for invoicing?', mtInformation, [mbYes,mbNo],0); if (wSelected = mrYes) then begin lRevenue := True; ReleaseTransaction(nBatchID, lRevenue); qDubSummary.Close; qDubSummary.Open; if qDubSummary.Bof and qDubSummary.Eof then begin CloseTab(TabDubiousSummary); end else if TabDubiousDetail.TabVisible then begin // Refresh the detail page. RxDBGridDubiousSummaryDblClick(Sender); PageControl1.ActivePage := TabDubiousSummary; end; // if TabDubiousDetail.Visible then begin. if qBatchSummary.Active then begin qBatchSummary.Close; qBatchSummary.Open; PageControl1.ActivePage := TabBatchStatusSummary; end; PaintPPTreeView(cbBillingPeriod.Text); end; // if (wSelected = mrYes) then begin. { end // if qry.eof and qry.Eof then begin. else begin MessageDlg('Revenue cannot be released until all errors are resolved.', mtWarning, [mbOK],0); end; // if qry.eof and qry.Eof then begin..else. } end else begin MessageDlg('Revenue cannot be released until you test for Dubious '+ 'Errors'+#13#10+'and all errors are resolved.', mtWarning, [mbOK],0); end; // if lContinue then begin // qry.Close; slStrings.Free; // qDubSummary.Close; // qDubSummary.Open; end; // if not (qDubSummary.Bof and qDubSummary.Eof) then begin. end; // btnReleaseRevenueClick. procedure TMainForm.ViewDCExpense(var slSQL: TStringList); var slStringList : TStringList; nCount : integer; begin // slStringList := TStringList.Create; slStringList := slSQL; with slStringList do begin for nCount := 0 to (Count - 1) do begin if (Pos('.RevenueFlag', Strings[nCount]) > 0) then begin // RevenueFlag entry found - replace it. Strings[nCount] := 'WHERE ((tDataCollection.RevenueFlag IS NULL) '+ 'OR (tDataCollection.RevenueFlag = 0))'; break; end; end; end; slSQL := slStringList; // slStringList.Free; end; // ViewDCExpense. procedure TMainForm.ViewDCRevenue(var slSQL: TStringList); var slStringList : TStringList; nCount : integer; begin // slStringList := TStringList.Create; slStringList := slSQL; with slStringList do begin for nCount := 0 to (Count - 1) do begin if (Pos('.RevenueFlag', Strings[nCount]) > 0) then begin // RevenueFlag entry found - replace it. Strings[nCount] := 'WHERE ((tDataCollection.RevenueFlag IS NOT NULL) '+ 'AND (tDataCollection.RevenueFlag = 1))'; break; end; end; end; slSQL := slStringList; // slStringList.Free; end; // ViewDCRevenue. procedure TMainForm.cbBatchTransactionsChange(Sender: TObject); var slStringList : TStringList; begin slStringList := TStringList.Create; PanelDCExpense.Visible := TestForDCRevenue(StrToInt(cbBatchTransactions.Text)); GetSQL('DCTransactions',slStringList); if PanelDCExpense.Visible and rbDCRevenue.Checked then ViewDCRevenue(slStringList); qTransactions.SQL := slStringList; qTransactions.Open; slStringList.Clear; if rbBatchTransactionsAuto.Checked then begin GetSQL('DCTransactionsAuto',slStringList); if PanelDCExpense.Visible and rbDCRevenue.Checked then ViewDCRevenue(slStringList); qTransactionsAuto.SQL := slStringList; qTransactionsAuto.Open; SelectAutoColumnHeaders(StrToInt((cbBatchTransactions.Text))); end; slStringList.Free; end; // cbBatchTransactionsChange. procedure TMainForm.SetCompletedBatchIDValues; // Adds items in cbBatchCompleted. begin with qry do begin Close; SQL.Add('SELECT DISTINCT CB.BatchID'); SQL.Add('FROM (SELECT DISTINCT BatchID'); SQL.Add('FROM tPayableDetail'); { SQL.Add('WHERE Period LIKE '''+cbBillingPeriod.Text+''''); SQL.Add('UNION'); SQL.Add('SELECT DISTINCT BatchID'); SQL.Add('FROM tInvoiceDetail'); } SQL.Add('WHERE Period LIKE '''+cbBillingPeriod.Text+''') AS CB'); SQL.Add('WHERE CB.BatchID IS NOT NULL'); SQL.Add('ORDER BY CB.BatchID'); Open; cbBatchCompleted.Items.Add('All'); while not Eof do begin cbBatchCompleted.Items.Add(FieldByName('BatchID').AsString); Next; end; Close; end; cbBatchCompleted.ItemIndex := 0; cbBatchCompleted.Text := cbBatchCompleted.Items[0]; end; // SetCompletedBatchIDValues. procedure TMainForm.SetBatchCompleted(var slSQL: TStringList); var lAddBatchID : boolean; slTemp : TStringList; nPosWhere : integer; nPosOrder : integer; nWhere : integer; begin slTemp := TStringList.Create; lAddBatchID := (LowerCase(Trim(cbBatchCompleted.Text)) <> 'all'); slTemp.Text := slSQL.Text; // Apply the filter to reduce the data displayed. for nPosWhere := (slTemp.Count-1) downto 0 do begin if (Pos('WHERE ',UpperCase(slTemp.Strings[nPosWhere])) > 0) then break; end; for nPosOrder := (slTemp.Count-1) downto 0 do begin if (Pos('ORDER BY ',UpperCase(slTemp.Strings[nPosOrder])) > 0) then break; end; // Assume ORDER BY is after WHERE. slTemp.Clear; for nWhere := 0 to (nPosWhere) do slTemp.Add(slSQL.Strings[nWhere]); if lAddBatchID then slTemp.Add('AND BatchID = '+cbBatchCompleted.Text); for nWhere := (nPosOrder) to (slSQL.Count-1) do slTemp.Add(slSQL.Strings[nWhere]); // Done, update the final SQL statement. slSQL.Text := slTemp.Text; slTemp.Free; end; // SetBatchCompleted. procedure TMainForm.cbBatchCompletedChange(Sender: TObject); var slStringList : TStringList; begin slStringList := TStringList.Create; // Update the Payables query. slStringList.Text := qCompletedPayable.SQL.Text; SetBatchCompleted(slStringList); qCompletedPayable.SQL := slStringList; qCompletedPayable.Open; // Update the Invoice query. { slStringList.Text := qCompletedInvoice.SQL.Text; SetBatchCompleted(slStringList); qCompletedInvoice.SQL := slStringList; qCompletedInvoice.Open; } slStringList.Free; end; // cbBatchCompletedChange. procedure TMainForm.RxDBGridTransactionsGetCellParams(Sender: TObject; Field: TField; AFont: TFont; var Background: TColor; Highlight: Boolean); begin if not Highlight then begin if (RxDBGridTransactions.DataSource.DataSet.FieldByName('StatusID').AsInteger>0) then begin AFont.Color := clRed; end; end; // if not Highlight then. end; // RxDBGridTransactionsGetCellParams. procedure TMainForm.RxDBGridTransactionsAutoGetCellParams(Sender: TObject; Field: TField; AFont: TFont; var Background: TColor; Highlight: Boolean); begin if not Highlight then begin if (RxDBGridTransactionsAuto.DataSource.DataSet.FieldByName('StatusID').AsInteger>0) then begin AFont.Color := clRed; end; end; // if not Highlight then. end; // RxDBGridTransactionsAutoGetCellParams. procedure TMainForm.RxDBGridDCdetailGetCellParams(Sender: TObject; Field: TField; AFont: TFont; var Background: TColor; Highlight: Boolean); begin if not Highlight then begin if (RxDBGridDCdetail.DataSource.DataSet.FieldByName('StatusID').AsInteger>0) then begin AFont.Color := clRed; end; end; // if not Highlight then. end; // RxDBGridDCdetailGetCellParams. procedure TMainForm.btnAdminAcceptAllClick(Sender: TObject); var nBatch : integer; // lSuccess : boolean; nBatchStatus : integer; begin if (qAdminSummary.Bof and qAdminSummary.Eof) then begin if qBatchSummary.Active then nBatch := qBatchSummary.FieldByName('BatchID').AsInteger else nBatch := 0; end else begin nBatch := RxDBGridAFSummary.DataSource.DataSet.FieldByName('BatchID').AsInteger; end; if (nBatch = 0) then begin MessageDlg('Unable to determine the Batch to process.', mtWarning, [mbOk],0); end else begin qry.Close; qry.SQL.Clear; qry.SQL.Add('SELECT tBatch.BatchStatusID'); qry.SQL.Add('FROM tBatch'); qry.SQL.Add('WHERE tBatch.ID = '+IntToStr(nBatch)); qry.Open; if qry.Eof then nBatchStatus := 0 else nBatchStatus := qry.FieldByName('BatchStatusID').AsInteger; qry.Close; if (nBatchStatus = BATCHSTAT_ADMINRVW) then begin UpdateBatchStatus(nBatch, BATCHSTAT_ADMINOK); if TabAdminDetail.TabVisible then CloseTab(TabAdminDetail); CloseTab(TabAdminSummary); if qBatchSummary.Active then begin qBatchSummary.Close; qBatchSummary.Open; PageControl1.ActivePage := TabBatchStatusSummary; end; // if qBatchSummary.Active then begin. end else if (nBatchStatus < BATCHSTAT_ADMINRVW) then begin MessageDlg('You must calculate the fees first.', mtWarning, [mbOk],0); end else begin MessageDlg('Improper access to fee approval. Aborted.', mtWarning, [mbOk],0); CloseTab(TabAdminSummary); end; end; end; // btnAdminAcceptAllClick. procedure TMainForm.btnAdminReCalculateClick(Sender: TObject); var lSuccess : boolean; nBatch : integer; begin if (qAdminSummary.Bof and qAdminSummary.Eof) then begin if qBatchSummary.Active then // Read from BatchSummary. nBatch := qBatchSummary.FieldByName('BatchID').AsInteger else nBatch := 0; end else begin nBatch := qAdminSummary.FieldByName('BatchID').AsInteger; end; if (nBatch = 0) then begin MessageDlg('Unable to determine the Batch to process.', mtWarning, [mbOk],0); end else begin SaveAllAdminFees(nBatch, lSuccess); if lSuccess then begin qAdminSummary.Close; qAdminSummary.Open; end; // lSuccess. end; // if not (qAdminSummary.Bof and qAdminSummary.Eof) then begin. end; // btnAdminReCalculateClick. procedure TMainForm.btnRelExpenditureClick(Sender: TObject); var lRevenue : boolean; nBatch : integer; nBatchStatus : integer; begin if (qRelExpTransactSummary.Bof and qRelExpTransactSummary.Eof) then begin if qBatchSummary.Active then // Read from BatchSummary. nBatch := qBatchSummary.FieldByName('BatchID').AsInteger else nBatch := 0; end else begin nBatch := RxDBGridRelExpSummary.DataSource.DataSet.FieldByName('BatchID').AsInteger; end; if (nBatch = 0) then begin MessageDlg('Unable to determine the Batch to process.', mtWarning, [mbOk],0); end else begin // // StartTime; // qry.Close; qry.SQL.Clear; qry.SQL.Add('SELECT tBatch.BatchStatusID'); qry.SQL.Add('FROM tBatch'); qry.SQL.Add('WHERE tBatch.ID = '+IntToStr(nBatch)); qry.Open; if qry.Eof then nBatchStatus := 0 else nBatchStatus := qry.FieldByName('BatchStatusID').AsInteger; qry.Close; if (nBatchStatus = BATCHSTAT_ADMINOK) then begin if (qRelExpTransactSummary.Bof and qRelExpTransactSummary.Eof) then begin UpdateBatchStatus(nBatch, BATCH_STATUS_PAYEXP); end else begin lRevenue := False; // not Revenue => Expenditure. ReleaseTransaction(qRelExpTransactSummary.FieldByName('BatchID').AsInteger, lRevenue); end; // if (qRelExpTransactSummary.Bof and qRelExpTransactSummary.Eof).. CloseTab(TabExpendRelease); if qBatchSummary.Active then begin qBatchSummary.Close; qBatchSummary.Open; PageControl1.ActivePage := TabBatchStatusSummary; end; // if qBatchSummary.Active then begin. end; // if (nBatchStatus = BATCHSTAT_ADMINOK) then begin. // // EndTime; // end; // if not (qRelExpTransactSummary.Bof and .Eof) then begin. end; // btnRelExpenditureClick. procedure TMainForm.RxDBGridBatchStatusDblClick(Sender: TObject); var slStringList : TStringList; lSuccess : boolean; wSelected : word; begin slStringList := TStringList.Create; // if (not (qBatchSummary.Bof and qBatchSummary.Eof)) and aAccess[BATCHREVIEW] and if (not (qBatchSummary.Bof and qBatchSummary.Eof)) and (aAccess[PROCESSIMPORT] or aAccess[VIEWDATA]) and (qBatchSummary.FieldByName('BatchStatusID').AsInteger < BATCHSTAT_RELEASED) then begin if (qBatchSummary.FieldByName('BatchID').AsInteger < BATCHSTAT_IMPORT) then begin // User must return to the Initiate tab to import/enter data. end else if (qBatchSummary['BatchStatusID'] < BATCHSTAT_CPASS) then begin if qCESummary.Active then begin qCESummary.Close; qCESummary.Open; end else begin OpenTab(TabCriticalSummary); end; // if qAdminSummary.Active then begin. PageControl1.ActivePage := TabCriticalSummary; end else if (qBatchSummary['BatchStatusID'] < BATCHSTAT_TRANSNS) then begin wSelected := MessageDlg('All errors resolved. Process these transactions'+ ' or Retry for Errors?', mtConfirmation, [mbYes,mbRetry,mbCancel], 0); if (wSelected = mrYes) then begin SaveTransaction( qBatchSummary['BatchID'], lSuccess ); if TabCriticalSummary.TabVisible then CloseTab(TabCriticalSummary); qBatchSummary.Close; qBatchSummary.Open; PageControl1.ActivePage := TabBatchStatusSummary; end else if (wSelected = mrRetry) then begin // The following is repeated EXACTLY from the previous test. if qCESummary.Active then begin qCESummary.Close; qCESummary.Open; end else begin OpenTab(TabCriticalSummary); end; // if qAdminSummary.Active then begin. PageControl1.ActivePage := TabCriticalSummary; end; // if (wSelected = mrYes) then begin. end else if (qBatchSummary['BatchStatusID'] < BATCHSTAT_ADMINOK) then begin if qAdminSummary.Active then begin qAdminSummary.Close; qAdminSummary.Open; end else begin OpenTab(TabAdminSummary); end; // if qAdminSummary.Active then begin. PageControl1.ActivePage := TabAdminSummary; end else if (qBatchSummary['BatchStatusID'] < BATCHSTAT_PAYEXP) then begin if qRelExpTransactSummary.Active then begin qRelExpService.Close; qRelExpService.Open; qRelExpTransactSummary.Close; qRelExpTransactSummary.Open; end else begin OpenTab(TabExpendRelease); end; // if qAdminSummary.Active then begin. PageControl1.ActivePage := TabExpendRelease; end else if (qBatchSummary['BatchStatusID'] < BATCHSTAT_DCHECK) then begin if qDubSummary.Active then begin qDESResolution.Close; qDubSummary.Close; qDubSummary.Open; qDESResolution.Open; cbDESResolution.KeyValue := qDESResolution['DubiousResolution']; end else begin lSuccess := False; DubiousErrorTest( qBatchSummary.FieldByName('BatchID').AsInteger, lSuccess ); OpenTab(TabDubiousSummary); end; // if qAdminSummary.Active then begin. PageControl1.ActivePage := TabDubiousSummary; end else if (qBatchSummary['BatchStatusID'] < BATCHSTAT_RELEASED) then begin if qDubSummary.Active then begin qDESResolution.Close; qDubSummary.Close; qDubSummary.Open; qDESResolution.Open; cbDESResolution.KeyValue := qDESResolution['DubiousResolution']; end else begin OpenTab(TabDubiousSummary); end; // if qAdminSummary.Active then begin. PageControl1.ActivePage := TabDubiousSummary; end; // qBatchSummary['BatchStatusID'] tests. end; // if not (qBatchSummary.Bof and qBatchSummary.Eof) and.. slStringList.Free; end; // RxDBGridBatchStatusDblClick. procedure TMainForm.RxDBGridAFSummaryDblClick(Sender: TObject); var slStrings : TStringList; sBatchID : string; sServiceName : string; begin if not (qAdminSummary.Bof and qAdminSummary.Eof) then begin sBatchID := IntToStr(qAdminSummary.FieldByName('BatchID').AsInteger); sServiceName := qAdminSummary.FieldByName('ServiceID').AsString; slStrings := TStringList.Create; slStrings.Add('SELECT *'); slStrings.Add('FROM (SELECT tTransaction.BatchID, tServiceID.ServiceID,'); slStrings.Add(' Min(tTransaction.FromDate) AS DateFrom,'); slStrings.Add(' Max(tTransaction.ToDate) AS DateTo, '+ 'tTransactionType.Description AS "TransactionType",'); slStrings.Add(' Sum(tTransaction.AmountExGST) AS TotalExGST,'); slStrings.Add(' Sum(tTransaction.GSTAmount) AS TotalGST,'); slStrings.Add(' Sum(tTransaction.AmountIncGST) AS TotalIncGST,'); slStrings.Add(' tTransactionGroup.Description AS TransactionGroup'); slStrings.Add('FROM ((tTransaction'); slStrings.Add('LEFT JOIN tServiceID '+ 'ON tTransaction.ServiceID = tServiceID.ID)'); slStrings.Add('LEFT JOIN tTransactionType '+ 'ON tTransaction.TransactionTypeID = tTransactionType.ID)'); slStrings.Add('LEFT JOIN tTransactionGroup '+ 'ON tTransactionType.TransactionGroupID = tTransactionGroup.ID'); slStrings.Add('WHERE tTransaction.BatchID = '+sBatchID); slStrings.Add('AND tServiceID.ServiceID LIKE '''+sServiceName+''''); slStrings.Add('AND (tTransaction.RevenueFlag = 1 '+ 'OR tTransactionGroup.Description LIKE ''ADMIN'')'); slStrings.Add('AND tTransactionType.Description NOT LIKE ''%Lost%'''); slStrings.Add('GROUP BY tTransaction.BatchID, tServiceID.ServiceID, '+ 'tTransactionType.Description,'); slStrings.Add(' tTransactionGroup.Description) AS ADetail'); slStrings.Add('ORDER BY ServiceID'); TabAdminDetail.TabVisible := True; TabAdminDetail.Visible := True; qAdminDetail.Close; qAdminDetail.SQL := slStrings; qAdminDetail.Open; lblAFDFeeRate.Left := lblAFDAcctFee.Left + lblAFDAcctFee.Width + 9; if VarIsNull(qAdminSummary['NonStdRevAdminFee']) then lblAFDFeeRate.Caption := sDefaultExpAdmRate + '%' else lblAFDFeeRate.Caption := FloatToStrF(100*qAdminSummary['NonStdRevAdminFee'], ffFixed, 5, 2)+'%'; lblAFDDiscountRate.Left := lblAFDAcctFee.Left+lblAFDAcctFee.Width+9; if VarIsNull(qAdminSummary['DiscountRate']) then lblAFDDiscountRate.Caption := '0.00%' else lblAFDDiscountRate.Caption := FloatToStrF(100*qAdminSummary['DiscountRate'], ffFixed,5,2)+'%'; lblAFDTotalRate.Left := lblAFDTotalFee.Left + lblAFDTotalFee.Width + 9; if VarIsNull(qAdminSummary['Admin']) then lblAFDTotalRate.Caption := '$0.00' else lblAFDTotalRate.Caption := '$'+CurrToStr(qAdminSummary['Admin']); PageControl1.ActivePage := TabAdminDetail; slStrings.Free; end; // if not (qAdminSummary.Bof and qAdminSummary.Eof) then begin. end; // RxDBGridAFSummaryDblClick. procedure TMainForm.RxDBGridRelExpSummaryKeyUp(Sender: TObject; var Key: Word; Shift: TShiftState); begin RxDBGridRelExpSummaryCellClick(RxDBGridRelExpSummary.Columns[0]); end; // RxDBGridRelExpSummaryKeyUp. procedure TMainForm.btnDERecalculateClick(Sender: TObject); var nBatchID : integer; lSuccess : boolean; begin // if not (qDubSummary.Bof and qDubSummary.Eof) then begin lSuccess := True; if qDubSummary.Bof and qDubSummary.Eof then begin if qBatchSummary.Active and not (qBatchSummary.Bof and qBatchSummary.Eof) then begin nBatchID := qBatchSummary['BatchID']; end else begin lSuccess := False; nBatchID := 0; MessageDlg('Test failed. Unable to identify a Batch to process.'+#13#10+ 'Select a Batch in the Batch Status Summary.', mtWarning, [mbOk], 0); CloseTab(TabDubiousSummary); end; end else begin nBatchID := RxDBGridDubiousSummary.DataSource.DataSet['BatchID']; end; if lSuccess then DubiousErrorTest( nBatchID, lSuccess ); end; // btnDERecalculateClick. procedure TMainForm.EditSystemTables; var Hndl : THandle; sParams : string; begin if TestDatabaseFlag then sParams := s_User_ID + ' 1' else sParams := s_User_ID + ' 0'; Hndl := ExecuteFile(sExeDirectory+'PPSystem.exe', sParams, '', SW_SHOWNORMAL); if Integer(Hndl) < 32 then ShowMessage('Error running PPSystem.EXE'+ #13#10+'It MUST be in the same directory as NewPP.exe'); end; // EditSystemTables. procedure TMainForm.ActionPrinterSetup(Sender: TObject); begin MainPrinterSetupDialog.Execute; end; // ActionPrinterSetup. procedure TMainForm.ActionPrintCurrentTab(Sender: TObject); var TabSheet : TTabSheet; sCaption : string; begin if PageControl1.Visible then begin TabSheet := PageControl1.ActivePage; if (TabSheet = TabBatchStatusSummary) then begin PrintQuery(qBatchSummary, 'Batch Status Summary - '+cbBillingPeriod.Text); end else if (TabSheet = TabCriticalSummary) then begin PrintQuery(qCESummary, 'Critical Error Summary - '+cbBillingPeriod.Text); end else if (TabSheet = TabCriticalResolution) then begin PrintQuery(qCEResolution, 'Critical Error Details - '+cbBillingPeriod.Text); end else if (TabSheet = TabCompleted) then begin PrintQuery(qCompletedPayable, 'Completed Payable Invoices'); end else if (TabSheet = TabAdjustment) then begin PrintQuery(qAdjustment, 'Batch Adjustments - '+cbBillingPeriod.Text); end else if (TabSheet = TabDubiousSummary) then begin PrintQuery(qDubSummary, 'Dubious Error Summary - '+cbBillingPeriod.Text); end else if (TabSheet = TabDubiousDetail) then begin PrintQuery(qDubDetail, 'Dubious Error Details - '+cbBillingPeriod.Text); end else if (TabSheet = TabAdminSummary) then begin // PrintQuery(qAdminSummary, 'Administration Fee Summary - '+ // cbBillingPeriod.Text); PrintDataSet(RxDBGridAFSummary.DataSource.DataSet, 'Administration Fee Summary - '+cbBillingPeriod.Text); end else if (TabSheet = TabAdminDetail) then begin // PrintQuery(qAdminDetail, 'Administration Fee Details - '+ // cbBillingPeriod.Text); PrintDataSet(RxDBGridAFDetail.DataSource.DataSet, 'Administration Fee Details - '+cbBillingPeriod.Text); end else if (TabSheet = TabExpendRelease) then begin PrintQuery(qRelExpTransactSummary, 'Expenditure Release - '+cbBillingPeriod.Text); end else if (TabSheet = TabTransactionSummary) then begin if PanelDCExpense.Visible then begin if rbDCRevenue.Checked then begin sCaption := 'Revenue '; end else begin sCaption := 'Expense '; end; end else begin sCaption := 'Expense '; end; if rbBatchTransactions.Checked then PrintQuery(qTransactions, sCaption + 'Batch Transactions ('+ cbBatchTransactions.Text+') - '+cbBillingPeriod.Text) else PrintQuery(qTransactionsAuto, sCaption + 'Batch Transactions Automatic ('+ cbBatchTransactions.Text+') - '+cbBillingPeriod.Text); end else if (TabSheet = TabDCdetail) then begin if PanelDCExpense.Visible then begin if rbDCRevenue.Checked then begin sCaption := 'Revenue '; end else begin sCaption := 'Expense '; end; end else begin sCaption := 'Expense '; end; PrintQuery(qDCdetail, sCaption + 'Batch Transaction Details ('+ cbBatchTransactions.Text +') - '+cbBillingPeriod.Text); end else if (TabSheet = TabServices) then begin PrintQuery(qServices, 'VicTrack Services - '+cbBillingPeriod.Text); end; // TabSheet requiring printing. end; // if PageControl1.Visible then begin..else.. end; // ActionPrintCurrentTab. procedure TMainForm.mnuUpdateSystemdataClick(Sender: TObject); begin EditSystemTables; end; // MenuUpdateSystemdataItemClick. procedure TMainForm.btnNewBatchClick(Sender: TObject); begin lNewManualInvoice := True; ProcessBilling; end; // btnNewBatchClick() procedure TMainForm.mnuViewAdjustmentsItemClick(Sender: TObject); begin OpenTab(TabAdjustment); end; // MenuViewAdjustmentsClick() procedure TMainForm.mnuViewBatchStatusItemClick(Sender: TObject); begin OpenTab(TabBatchStatusSummary); end; // MenuViewBatchStatusItemClick() procedure TMainForm.FormKeyDown(Sender: TObject; var Key: Word; Shift: TShiftState); begin if (shift = ([ssCtrl])) and (Key = VK_F4) then ActionCloseTab(Sender); end; // FormKeyDown. procedure TMainForm.ActionCloseTab(Sender: TObject); begin if PageControl1.Visible then begin CloseTab(PageControl1.ActivePage); if (PageControl1.PageCount = 0) then PageControl1.Visible := False; end; end; // ActionCloseTab. procedure TMainForm.btnAddTransactionClick(Sender: TObject); var Save_Cursor : TCursor; slManual : TStringList; lSuccess : boolean; dManual : TDateTime; lManualDate : boolean; sDateFrom : string; sDateTo : string; begin // Note: Any changes to the button and edit fields made here must also be // reflected in the InitiateScreen() and PopulateScreen() procedures. slManual := TStringList.Create; lManualDate := (Length(cbBillingPeriod.Text) = 7); if lManualDate then dManual := StrDMYtoDate('01/'+Copy(cbBillingPeriod.Text,6,2)+'/'+ Copy(cbBillingPeriod.Text,1,4)) else dManual := Date; if lManualMultipleEntry then begin // Define and open the Query. // First, update the date fields from tBatch. sDateFrom := FormattedDateString(FirstDayInMonth(dManual)); sDateTo := FormattedDateString(LastDayInMonth(dManual)); slManual.Add('UPDATE tMultiManualData'); slManual.Add('SET FromDate = '''+sDateFrom+''','); slManual.Add(' ToDate = '''+sDateTo+''''); slManual.Add('WHERE BatchTypeID = '+sInitiateBatchTypeID); qMultiManual.SQL := slManual; qMultiManual.ExecSQL; // Now open the query. slManual.Clear; slManual.Add('SELECT *'); slManual.Add('FROM tMultiManualData'); slManual.Add('WHERE BatchTypeID = '+sInitiateBatchTypeID); slManual.Add('ORDER BY ServiceID'); qMultiManual.SQL := slManual; qMultiManual.Open; DisplayInitiateGrid; uqMultiManual.DataSet := qMultiManual; end else begin Save_Cursor := Screen.Cursor; Screen.Cursor := crHourglass; // Show hour glass cursor. try // Set the range of valid ServiceTypes appropriate for this BatchType. slManual.Add('SELECT DISTINCT tServiceType.Code + '' - '' + '+ 'tServiceType.Description AS ServiceType, tServiceType.ID AS ServiceTypeID'); slManual.Add('FROM ((tBatchType'); slManual.Add(' LEFT JOIN tSupplier ON tBatchType.SupplierID = tSupplier.ID)'); slManual.Add(' LEFT JOIN tSystem ON tBatchType.ID = tSystem.BatchTypeID)'); slManual.Add(' LEFT JOIN tServiceType ON tSystem.ID = tServiceType.SystemID'); // slManual.Add('WHERE tBatchType.ID = '+IntToStr(qBatchType['id'])); slManual.Add('WHERE tBatchType.ID = '+sInitiateBatchTypeID); AddManualForm.Free; AddManualForm := TManualForm.Create(SELF); // Pass the SQL statement to the form to set the range of valid ServiceTypes. AddManualForm.InitiateSQL(slManual, lSuccess); if lSuccess then begin AddManualForm.BatchID := StrToInt(Trim(edtBatchID.Text)); if dtpCallStartDate.Visible then AddManualForm.FromDate := dtpCallStartDate.Date else if dtpRentStartDate.Visible then AddManualForm.FromDate := dtpRentStartDate.Date else if lManualDate then AddManualForm.FromDate := dManual; if dtpCallEndDate.Visible then AddManualForm.ToDate := dtpCallEndDate.Date else if dtpRentEndDate.Visible then AddManualForm.ToDate := dtpRentEndDate.Date else if lManualDate then AddManualForm.ToDate := LastDayInMonth(dManual); Screen.Cursor := Save_Cursor; if (AddManualForm.ShowModal = mrOk) then begin UpdateBatchTotals(StrToInt(sInitiateBatchID)); UpdateBatchStatus(StrToInt(edtBatchID.Text), BATCH_STATUS_IMPORT); end; end; // if lSuccess then begin. finally Screen.Cursor := Save_Cursor; // Always restore to normal. // slManual.Free; end; // try. end; // lManualMultipleEntry. slManual.Free; end; // btnAddTransactionClick. procedure TMainForm.RxDBGridTransactionsDblClick(Sender: TObject); var slStrings : TStringList; sBatchID : string; sServiceName : string; sCaption : string; begin if not (qTransactions.Bof and qTransactions.Eof) then begin sBatchID := cbBatchTransactions.Text; sServiceName := qTransactions.FieldByName('ServiceID').AsString; slStrings := TStringList.Create; slStrings.Add('SELECT DCDA.*'); slStrings.Add('FROM (SELECT DCD.BatchID, DCD.ServiceID, DCD.Code, '+ 'DCD.AmountExGST,'); slStrings.Add(' DCD.CallCode, DCD.DialledNumber, DCD.RateDescription, '+ 'DCD.TransactionType,'); slStrings.Add(' DCD.Var01, DCD.Var02, DCD.Var03, DCD.Var04, DCD.Var05,'); slStrings.Add(' DCD.Var06, DCD.Var07, DCD.Var08, DCD.Var09, DCD.Var10,'); slStrings.Add(' DCD.Var11, DCD.Var12, DCD.Var13, DCD.Var14, DCD.Var15'); slStrings.Add('FROM (SELECT tDataCollection.BatchID, '+ 'tDataCollection.ServiceID,'); slStrings.Add(' tDataCollection.AmountExGST, tDataCollection.CallCode,'); slStrings.Add(' tDataCollection.RateDescription, '+ 'tDataCollection.DialledNumber,'); slStrings.Add(' tDataCollection.Var01, tDataCollection.Var02,'); slStrings.Add(' tDataCollection.Var03, tDataCollection.Var04,'); slStrings.Add(' tDataCollection.Var05, tDataCollection.Var06,'); slStrings.Add(' tDataCollection.Var07, tDataCollection.Var08,'); slStrings.Add(' tDataCollection.Var09, tDataCollection.Var10,'); slStrings.Add(' tDataCollection.Var11, tDataCollection.Var12,'); slStrings.Add(' tDataCollection.Var13, tDataCollection.Var14,'); slStrings.Add(' tDataCollection.Var15, tTransactionType.Description '+ 'AS TransactionType,'); slStrings.Add(' (CASE WHEN tDataCollection.ServiceType IS NULL THEN '+ 'tServiceType.Code ELSE RTRIM(SUBSTRING(tDataCollection.ServiceType,1,5))'+ ' END) AS "Code"'); slStrings.Add('FROM ((tDataCollection'); slStrings.Add('LEFT JOIN tServiceID '+ 'ON tDataCollection.ServiceID = tServiceID.ServiceID)'); slStrings.Add('LEFT JOIN tServiceType '+ 'ON tServiceID.ServiceTypeID = tServiceType.ID)'); slStrings.Add('LEFT JOIN tTransactionType '+ 'ON tDataCollection.TransactionTypeID = tTransactionType.ID'); slStrings.Add('WHERE tDataCollection.BatchID = '+sBatchID); if PanelDCExpense.Visible then begin if rbDCRevenue.Checked then begin slStrings.Add('AND tDataCollection.RevenueFlag IS NOT NULL '+ 'AND tDataCollection.RevenueFlag = 1'); sCaption := 'Revenue: '; end else begin slStrings.Add('AND (tDataCollection.RevenueFlag IS NULL OR '+ 'tDataCollection.RevenueFlag = 0)'); sCaption := 'Expense: '; end; end; slStrings.Add('AND tDataCollection.ServiceID LIKE '''+sServiceName+''') '+ 'AS DCD) AS DCDA'); slStrings.Add('ORDER BY DCDA.ServiceID'); TabDCdetail.TabVisible := True; TabDCdetail.Visible := True; PanelDCdetail.Caption := sCaption + 'BatchID='+sBatchID+' ServiceID='+ sServiceName; qDCdetail.Close; qDCdetail.SQL := slStrings; qDCdetail.Open; PageControl1.ActivePage := TabDCdetail; slStrings.Free; end; // if not (qAdminSummary.Bof and qAdminSummary.Eof) then begin. end; // RxDBGridTransactionsDblClick. procedure TMainForm.sbtnEditClick(Sender: TObject); // Edit the selected transaction group or transaction in tDataCollection. var TabSheet : TTabSheet; sBatchID : string; vLocateValue : variant; qDCEdit : TQuery; lDCEdit : boolean; begin if PageControl1.Visible then begin qDCEdit := TQuery.Create(SELF); TabSheet := PageControl1.ActivePage; if (TabSheet = TabTransactionSummary) and not qTransactions.Eof {and (RxDBGridTransactions.DataSource.DataSet.FieldByName('StatusID').AsInteger>0)} then begin lDCEdit := True; if RxDBGridTransactions.Visible then qDCEdit := qTransactions else if RxDBGridTransactionsAuto.Visible then qDCEdit := qTransactionsAuto else lDCEdit := False; if lDCEdit then begin sBatchID := cbBatchTransactions.Text; DCEditForm.Free; DCEditForm := TDCEditForm.Create(SELF); DCEditForm.DCdetail := False; DCEditForm.BatchID := StrToInt(sBatchID); DCEditForm.ServiceID := qDCEdit.FieldByName('ServiceID').AsString; Application.ProcessMessages; if (DCEditForm.ShowModal = mrOk) then begin vLocateValue := Copy(qDCEdit['ServiceID'],1,7); qTransactions.Close; qTransactions.Open; if qTransactionsAuto.Active then begin qTransactionsAuto.Close; qTransactionsAuto.Open; end; // DCEditForm.ShowModal. with qDCEdit do Locate('ServiceID',vLocateValue,[loPartialKey]); end; end; // lDCEdit. Refresh; end; // TabTransactionSummary. end; // PageControl1.Visible. end; // sbtnEditClick. procedure TMainForm.rbPayableClick(Sender: TObject); // Called when either rbPayable or rbInvoice is clicked. var slStringList : TStringList; begin slStringList := TStringList.Create; if rbBatchTransactions.Checked then begin DBNavCompleted.DataSource := dsCompletedPayable; end else begin if not qTransactionsAuto.Active then OpenTab(PageControl1.ActivePage); DBNavCompleted.DataSource := dsCompletedInvoice; end; if rbPayable.Checked then begin slStringList.Text := qCompletedPayable.SQL.Text; qCompletedPayable.Close; qCompletedPayable.SQL := slStringList; qCompletedPayable.Open; DBNavCompleted.DataSource := dsCompletedPayable; end else begin slStringList.Text := qCompletedInvoice.SQL.Text; qCompletedInvoice.Close; qCompletedInvoice.SQL := slStringList; qCompletedInvoice.Open; DBNavCompleted.DataSource := dsCompletedInvoice; end; RxDBGridCompletedPayable.Visible := rbPayable.Checked; RxDBGridCompletedInvoice.Visible := rbInvoice.Checked; slStringList.Free; end; procedure TMainForm.sbtnSelectAllClick(Sender: TObject); begin if (PageControl1.Visible and (PageControl1.ActivePage = TabCriticalResolution) and not (qCEResolution.Bof and qCEResolution.Eof)) then begin RxDBGridCERes.SelectAll; end; end; // sbtnSelectAllClick. procedure TMainForm.rbBatchTransactionsClick(Sender: TObject); begin SetBillingPeriodBatch; if rbBatchTransactions.Checked then begin dbNavDC.DataSource := dsTransactions; end else begin if not qTransactionsAuto.Active and (cbBatchTransactions.Items.Count > 0) then OpenTab(PageControl1.ActivePage); dbNavDC.DataSource := dsTransactionsAuto; end; RxDBGridTransactions.Visible := rbBatchTransactions.Checked; RxDBGridTransactionsAuto.Visible := rbBatchTransactionsAuto.Checked; end; // rbBatchTransactionsClick. procedure TMainForm.rbDCRevenueClick(Sender: TObject); var slStringList : TStringList; begin slStringList := TStringList.Create; if rbBatchTransactions.Checked then begin slStringList.Text := qTransactions.SQL.Text; ViewDCRevenue(slStringList); qTransactions.Close; qTransactions.SQL := slStringList; qTransactions.Open; end else begin slStringList.Text := qTransactionsAuto.SQL.Text; ViewDCRevenue(slStringList); qTransactionsAuto.Close; qTransactionsAuto.SQL := slStringList; qTransactionsAuto.Open; end; end; // rbDCRevenueClick. procedure TMainForm.rbDCExpenseClick(Sender: TObject); var slStringList : TStringList; begin slStringList := TStringList.Create; if rbBatchTransactions.Checked then begin slStringList.Text := qTransactions.SQL.Text; ViewDCExpense(slStringList); qTransactions.Close; qTransactions.SQL := slStringList; qTransactions.Open; end else begin slStringList.Text := qTransactionsAuto.SQL.Text; ViewDCExpense(slStringList); qTransactionsAuto.Close; qTransactionsAuto.SQL := slStringList; qTransactionsAuto.Open; end; slStringList.Free; end; // rbDCExpenseClick. function TMainForm.TestForDCRevenue(const nBatchID: integer): boolean; begin qry.Close; qry.SQL.Clear; qry.SQL.Add('SELECT tBatchType.EarlyRevenue'); qry.SQL.Add('FROM tBatchType'); qry.SQL.Add('RIGHT JOIN tBatch ON tBatchType.ID = tBatch.BatchTypeID'); qry.SQL.Add('WHERE tBatch.ID = '+IntToStr(nBatchID)); qry.Open; if VarIsNull(qry['EarlyRevenue']) then Result := False else Result := qry['EarlyRevenue']; qry.Close; qry.SQL.Clear; end; // TestForDCRevenue. procedure TMainForm.qMultiManualUpdateRecord(DataSet: TDataSet; UpdateKind: TUpdateKind; var UpdateAction: TUpdateAction); // This routine is called by calling qMultiManual.ApplyUpdates // when it is a cached query. Used for Multiple Manual transaction entry. var slManual : TStringList; begin slManual := TStringList.Create; // Updates done by the update SQl object uqMultiManual (linked to qMultiManual). // Only the InsertSQL statement needs to be modified - to add BatchTypeID. // The default ModifySQL and DeleteSQL statements in uqMultiManual are okay. if (UpdateKind = ukInsert) then begin slManual.Add('INSERT INTO tMultiManualData'); slManual.Add(' (BatchTypeID, ServiceID, Rent, Call, Other, GSTAmt,'+ ' FromDate, ToDate, RateDescription)'); slManual.Add('VALUES'); slManual.Add(' ('+sInitiateBatchTypeID + ', :ServiceID, :Rent, :Call, :Other, :GSTAmt, '+ ' :FromDate, :ToDate, :RateDescription)'); uqMultiManual.InsertSQL.Text := slManual.Text; end; uqMultiManual.Apply(UpdateKind); UpdateAction := uaApplied; slManual.Free; end; // qMultiManualUpdateRecord. procedure TMainForm.mnuHelpAboutClick(Sender: TObject); var AboutForm : TAboutBox; begin AboutForm := TAboutBox.Create(self); AboutForm.ShowModal; AboutForm.Free; end; // mnuHelpAboutClick. procedure TMainForm.btnFilterClick(Sender: TObject); begin FilterForm.Show; end; // btnFilterClick. procedure TMainForm.mnuToolsSelectMainDatabaseItemClick(Sender: TObject); begin DM.SetDatabaseSet(False); TestDatabaseFlag := TestForTestDB; lblTestData.Visible := TestDatabaseFlag; end; // mnuToolsSelectMainDatabaseItemClick. procedure TMainForm.mnuToolsSelectTestDatabaseItemClick(Sender: TObject); begin DM.SetDatabaseSet(True); TestDatabaseFlag := TestForTestDB; lblTestData.Visible := TestDatabaseFlag; end; // mnuToolsSelectTestDatabaseItemClick. procedure TMainForm.mnuViewRefreshItemClick(Sender: TObject); begin PaintPPTreeView(cbBillingPeriod.Text); end; // mnuViewRefreshItemClick. procedure TMainForm.mnuToolsWebDataClick(Sender: TObject); var nBatch : integer; begin if qBatchSummary.Active and not qBatchSummary.Eof then begin nBatch := qBatchSummary.FieldByName('BatchID').AsInteger; if (MessageDlg('Previous Web data for Batch '+IntToStr(nBatch)+ ' will be overwritten. Continue?', mtwarning, [mbYes, mbNo], 0) = mrYes) then CodeToFile('tProcessedTransaction', nBatch); end; end; // mnuViewRefreshItemClick. { function TMainForm.CurrentTabPageID: integer; var TabSheet : TTabSheet; begin TabSheet := PageControl1.ActivePage; if (TabSheet = TabDubiousSummary) then Result := TabDubiousSummaryID else if (TabSheet = TabDubiousDetail) then Result := TabDubiousDetailID else if (TabSheet = TabBatchStatusSummary) then Result := TabAgreementID else if (TabSheet = TabCompleted) then Result := TabCompletedID else if (TabSheet = TabCompletedDetail) then Result := TabCompletedDetailID else if (TabSheet = TabCriticalSummary) then Result := TabCriticalSummaryID else if (TabSheet = TabCriticalResolution) then Result := TabCriticalResolutionID else if (TabSheet = TabDCdetail) then Result := TabDCdetailID else if (TabSheet = TabTransactionSummary) then Result := TabBatchID else if (TabSheet = TabAdminSummary) then Result := TabBatchHistoryID else if (TabSheet = TabAdminDetail) then Result := TabBatchStatusID else if (TabSheet = TabAdjustment) then Result := TabAdjustmentID else Result := 0; end; // CurrentTabPageID. function TMainForm.CurrentQuery(const TabPageID: integer): TQuery; var CurrentTabID : integer; begin CurrentTabID := TabPageID; case CurrentTabID of TabActivityID: Result := qSysActivity; TabAdjustmentID: Result := qSysAdjustment; TabAgreementID: Result := qSysAgreement; TabAgreementTariffsID: Result := qSysAgreementTariffs; TabAgreementTypeID: Result := qSysAgreementType; TabApplicationID: Result := qSysApplication; TabAuditID: Result := qSysAudit; TabAuditTrailID: Result := qSysAuditTrail; TabBatchID: Result := qSysBatch; TabBatchHistoryID: Result := qSysBatchHistory; TabBatchStatusID: Result := qSysBatchStatus; TabBatchTypeID: Result := qSysBatchType; TabCentreID: Result := qSysCentre; TabCostRangeID: Result := qSysCostRange; TabCriticalTypeID: Result := qSysCriticalType; TabCustomerID: Result := qSysCustomer; TabDataCollectionMapID: Result := qSysDataCollectionMap; TabDataSourceDetailID: Result := qSysDataSourcedetail; TabDataSourceTypeID: Result := qSysDataSourceType; TabDepartmentID: Result := qSysDepartment; TabDestinationID: Result := qSysDestination; TabDialledNumberID: Result := qSysDialledNumber; TabDiscountID: Result := qSysDiscount; TabDiscountedRateID: Result := qSysDiscountedRate; TabDubiousResolutionID: Result := qSysDubiousResolution; TabDubiousResultID: Result := qSysDubiousResult; TabDubiousTypeID: Result := qSysDubiousType; TabDubiousValidResolutionID: Result := qSysDubiousValidResolution; TabElementID: Result := qSysElement; TabFunctionID: Result := qSysFunction; TabGroupID: Result := qSysGroup; TabLocationID: Result := qSysLocation; TabOriginID: Result := qSysOrigin; TabParameterID: Result := qSysParameter; TabPersonID: Result := qSysPerson; TabProgramID: Result := qSysProgram; TabRateID: Result := qSysRate; TabRateDescriptionID: Result := qSysRateDescription; TabServiceIDID: Result := qSysServiceID; TabServiceSplitID: Result := qSysServiceSplit; TabServiceSystemID: Result := qSysServiceSystem; TabServiceTypeID: Result := qSysServiceType; TabStatusID: Result := qSysStatus; TabSubledgerID: Result := qSysSubledger; TabSupplierID: Result := qSysSupplier; TabSupplierServiceCodeID: Result := qSysSupplierServiceCode; TabSystemID: Result := qSysSystem; TabTransactionGroupID: Result := qSysTransactionGroup; TabTransactionTypeID: Result := qSysTransactionType; TabUserID: Result := qSysUser; TabUserFunctionID: Result := qSysUserFunction; TabValidAccountID: Result := qSysValidAccount; else Result := Nil; end; // case. end; // CurrentQuery. function TMainForm.CurrentTable(const TabPageID: integer): string; var CurrentTabID : integer; begin CurrentTabID := TabPageID; case CurrentTabID of TabActivityID: Result := 'tActivity'; TabAdjustmentID: Result := 'tAdjustment'; TabAgreementID: Result := 'tAgreement'; TabAgreementTariffsID: Result := 'tAgreementTariffs'; TabAgreementTypeID: Result := 'tAgreementType'; TabApplicationID: Result := 'tApplication'; TabAuditID: Result := 'tAudit'; TabAuditTrailID: Result := 'tAuditTrail'; TabBatchID: Result := 'tBatch'; TabBatchHistoryID: Result := 'tBatchHistory'; TabBatchStatusID: Result := 'tBatchStatus'; TabBatchTypeID: Result := 'tBatchType'; TabCentreID: Result := 'tCentre'; TabCostRangeID: Result := 'tCostRange'; TabCriticalTypeID: Result := 'tCriticalType'; TabCustomerID: Result := 'tCustomer'; TabDataCollectionMapID: Result := 'tDataCollectionMap'; TabDataSourceDetailID: Result := 'tDataSourceDetail'; TabDataSourceTypeID: Result := 'tDataSourceType'; TabDepartmentID: Result := 'tDepartment'; TabDestinationID: Result := 'tDestination'; TabDialledNumberID: Result := 'tDialledNumber'; TabDiscountID: Result := 'tDiscount'; TabDiscountedRateID: Result := 'tDiscountedRate'; TabDubiousResolutionID: Result := 'tDubiousResolution'; TabDubiousResultID: Result := 'tDubiousResult'; TabDubiousTypeID: Result := 'tDubiousType'; TabDubiousValidResolutionID: Result := 'tDubiousValidResolution'; TabElementID: Result := 'tElement'; TabFunctionID: Result := 'tFunction'; TabGroupID: Result := 'tGroup'; TabLocationID: Result := 'tLocation'; TabOriginID: Result := 'tOrigin'; TabParameterID: Result := 'tParameter'; TabPersonID: Result := 'tPerson'; TabProgramID: Result := 'tProgram'; TabRateID: Result := 'tRate'; TabRateDescriptionID: Result := 'tRateDescription'; TabServiceIDID: Result := 'tServiceID'; TabServiceSplitID: Result := 'tServiceSplit'; TabServiceSystemID: Result := 'ServiceSystem'; TabServiceTypeID: Result := 'tServiceType'; TabStatusID: Result := 'tStatus'; TabSubledgerID: Result := 'tSubledger'; TabSupplierID: Result := 'tSupplier'; TabSupplierServiceCodeID: Result := 'tSupplierServiceCode'; TabSystemID: Result := 'tSystem'; TabTransactionGroupID: Result := 'tTransactionGroup'; TabTransactionTypeID: Result := 'tTransactionType'; TabUserID: Result := 'tUser'; TabUserFunctionID: Result := 'tUserFunction'; TabValidAccountID: Result := 'tValidAccount'; else Result := 'empty'; end; // case. end; // CurrentTable. } procedure TMainForm.ActionFileCurrentTab(Sender: TObject); var TabSheet : TTabSheet; qryFile : TQuery; sFileName : string; TakeAction : boolean; // BM : TBookmark; begin if PageControl1.Visible then begin qryFile := TQuery.Create(SELF); // // Generic statements - see PPSystem(). // qryFile := CurrentQuery(CurrentTabPageID); // sFileName := CurrentTable(CurrentTabPageID); TakeAction := True; TabSheet := PageControl1.ActivePage; if (TabSheet = TabTransactionSummary) then begin if RxDBGridTransactions.Visible then begin qryFile := qTransactions; sFileName := 'tDataCollection Summary'; end else if RxDBGridTransactionsAuto.Visible then begin qryFile := qTransactionsAuto; sFileName := 'tDataCollection Auto Summary'; end else begin TakeAction := False; end; end else if (TabSheet = TabDCdetail) then begin qryFile := qDCdetail; sFileName := 'tDataCollection Detail'; end else begin TakeAction := False; end; if TakeAction then begin FileQuery(qryFile, sFileName); end; end; // if PageControl1.Visible then begin..else.. end; // ActionFileCurrentTab. // procedure TMainForm.StartTime; // var // temptime : string; // begin // temptime := Copy(MainForm.lblStart.Caption, 8, 100); // MainForm.lblStart.Caption := 'Start: '+temptime+' '+TimeToStr(Time); // end; // procedure TMainForm.EndTime; // var // temptime : string; // begin // temptime := Copy(MainForm.lblEnd.Caption, 6, 100); // MainForm.lblEnd.Caption := 'End: '+temptime+' '+TimeToStr(Time); // end; // StartTime. procedure TMainForm.TelstraInvoice1Click(Sender: TObject); // This program manages the importing of data from a Telstra file that // contains one or more separate Accounts. { var lContinue : Boolean; stFiles : TStringList; OpenDlg: TOpenDialog; } begin // Assumes that the file has each account clustered together - not spaced // in parts throughout the file. { // Now, we must establish which file is to be processed. // Select the source directory. OpenDlg := TOpenDialog.Create(MainForm); OpenDlg.Filter := 'Telstra Billing file (*.dat)|*.DAT|All files (*.*)|*.*'; OpenDlg.FilterIndex := 1; // Use the program directory as the default directory. if (Length(sDirectory) > 0) then OpenDlg.InitialDir := sDirectory else OpenDlg.InitialDir := ExtractFilePath(ParamStr(0)); OpenDlg.Title := 'Select The Telstra Account Data File'; OpenDlg.Options := [ofFileMustExist, ofNoChangeDir]; // Read and save the user's choices before continuing. lContinue := OpenDlg.Execute; stFiles.Text := OpenDlg.Files.Text; OpenDlg.Free; if lContinue then begin lContinue := stFiles.Count > 0; // Now call for processing of the input files. if lContinue then begin lReturnCode := False; CurrentFile := Fname; FileIteration := 0; // Original file ext'n, then 001,002, etc. // Set the earliest a valid date is likely to be. dMinDate := EncodeDate(1980,1,1); // Set a dummy date for initialisation. dBilling := dMinDate; if FileExists(CurrentFile) then begin // Read the file size to determine the approximate number of records. nFileSize := GetFileSize(CurrentFile); // Now we can calculate the factor for the progress bar to be displayed. // From prev. data: 30,930 lines in a file of 12,135,424 bytes. // Estimate the number of data lines in the file. if (nFileSize > 0) then begin nFactor := 100.0 / ((30930.0 / 12135424.0) * nFileSize); end else begin nFactor := 100.0; end; nCount := 0; sPartFileName := CurrentFile; while Pos('\',sPartFileName)>0 do begin nBackSlashAt := Pos('\',sPartFileName); sPartFileName := Copy(sPartFileName, nBackSlashAt+1, Length(sPartFileName)-nBackSlashAt); end; frmProgress.Caption := 'Telstra Fixed Services Batch: ' + sBatchID; frmProgress.Msg.Caption := 'Reading ' + sPartFileName + ' ...'; frmProgress.ProgressBar1.Position := 0; frmProgress.Msg.Refresh; // Open the input (text) billing file for reading. AssignFile( TelstraFile, CurrentFile ); Reset( TelstraFile ); try Readln( TelstraFile, T ); // Test for the original EBS file format for a Mobile or Data. with qryTelstra do begin Close; SQL.Clear; SQL.Add('SELECT tValidAccountNumber.AccountNo'); SQL.Add('FROM tValidAccountNumber'); SQL.Add('INNER JOIN tBatch ON tValidAccountNumber.BatchTypeID = tBatch.BatchTypeID'); SQL.Add('WHERE tBatch.ID = '+IntToStr(nBatch)); Open; if Eof then begin MessageDlg('BatchType not found in the tValidAccountNumber table. '+ 'Processing stopped.', mtWarning, [mbOk], 0); lContinue := False; end else begin sAccountNumber := Trim(FieldByName('AccountNo').AsString); nPos := Pos(' ',sAccountNumber); while (nPos > 0) do begin sAccountNumber := Copy(sAccountNumber,1,nPos-1) + Copy(sAccountNumber,nPos+1,Length(sAccountNumber)-nPos); nPos := Pos(' ',sAccountNumber); end; // nPos. lContinue := True; end; // Eof. Close; if LOGPROGRESS then begin LogToFile('Account Number='+sAccountNumber); end; end; // qryTelstra. end; if lContinue then begin // lThisIsAPABX := (nProgram = PIMPORTBILLINGFILE_TELSTRA_EBS_PABX);//Global. // BillingInputFixedEBS(stFiles, nBatch); end; // if lContinue then. end; // if lContinue then. } end; // TelstraInvoice1Click. end.