void WriteAnalysisAttributesSuffix(ATransactionRow transaction) { FMainDS.ATransAnalAttrib.DefaultView.Sort = ATransAnalAttribTable.GetAnalysisTypeCodeDBName(); FMainDS.ATransAnalAttrib.DefaultView.RowFilter = String.Format("{0}={1} and {2}={3} and {4}={5} and {6}={7}", ATransAnalAttribTable.GetLedgerNumberDBName(), transaction.LedgerNumber, ATransAnalAttribTable.GetBatchNumberDBName(), transaction.BatchNumber, ATransAnalAttribTable.GetJournalNumberDBName(), transaction.JournalNumber, ATransAnalAttribTable.GetTransactionNumberDBName(), transaction.TransactionNumber); DataView anaView = FMainDS.ATransAnalAttrib.DefaultView; for (int i = 1; i <= maxNumValuesExport; i++) { if (i <= anaView.Count) { ATransAnalAttribRow ar = (ATransAnalAttribRow)anaView[i - 1].Row; WriteStringQuoted(ar.AnalysisTypeCode, false); WriteStringQuoted(ar.AnalysisAttributeValue, (i == maxNumValuesExport)); } else { WriteStringQuoted("", false); WriteStringQuoted("", (i == maxNumValuesExport)); } } }
/// <summary> /// Need to ensure that the Analysis Attributes grid has all the entries /// that are required for the selected account. /// There may or may not already be attribute assignments for this transaction. /// </summary> /// <param name="AGLBatchDS"></param> /// <param name="AAccountCode"></param> /// <param name="ATransactionNumber"></param> public void ReconcileTransAnalysisAttributes(ref GLBatchTDS AGLBatchDS, string AAccountCode, int ATransactionNumber) { if (string.IsNullOrEmpty(AAccountCode)) { return; } StringCollection RequiredAnalAttrCodes = TRemote.MFinance.Setup.WebConnectors.RequiredAnalysisAttributesForAccount(FLedgerNumber, AAccountCode, true); SetTransAnalAttributeDefaultView(AGLBatchDS, true, ATransactionNumber, TAnalysisAttributes.ConvertStringCollectionToCSV(RequiredAnalAttrCodes, "'")); // If the AnalysisType list I'm currently using is the same as the list of required types, I can keep it (with any existing values). bool existingListIsOk = (RequiredAnalAttrCodes.Count == AGLBatchDS.ATransAnalAttrib.DefaultView.Count); if (existingListIsOk) { foreach (DataRowView rv in AGLBatchDS.ATransAnalAttrib.DefaultView) { ATransAnalAttribRow row = (ATransAnalAttribRow)rv.Row; if (!RequiredAnalAttrCodes.Contains(row.AnalysisTypeCode)) { existingListIsOk = false; break; } } } if (existingListIsOk) { return; } // Delete any existing Analysis Type records and re-create the list (Removing any prior selections by the user). foreach (DataRowView rv in AGLBatchDS.ATransAnalAttrib.DefaultView) { ATransAnalAttribRow attrRowCurrent = (ATransAnalAttribRow)rv.Row; attrRowCurrent.Delete(); } foreach (String analysisTypeCode in RequiredAnalAttrCodes) { ATransAnalAttribRow newRow = AGLBatchDS.ATransAnalAttrib.NewRowTyped(true); newRow.LedgerNumber = FLedgerNumber; newRow.BatchNumber = FBatchNumber; newRow.JournalNumber = FJournalNumber; newRow.TransactionNumber = ATransactionNumber; newRow.AnalysisTypeCode = analysisTypeCode; newRow.AccountCode = AAccountCode; AGLBatchDS.ATransAnalAttrib.Rows.Add(newRow); } }
/// <summary> /// make sure the correct transaction number is assigned and the journal.lastTransactionNumber is updated /// </summary> /// <param name="ANewRow">returns the modified new transaction row</param> /// <param name="ARefTransactionRow">this can be null; otherwise this is the transaction that the Attribute should belong to</param> public void NewRowManual(ref ATransAnalAttribRow ANewRow, ATransactionRow ARefTransactionRow) { if (ARefTransactionRow == null) { ARefTransactionRow = GetTransactionRow(); } ANewRow.LedgerNumber = ARefTransactionRow.LedgerNumber; ANewRow.BatchNumber = ARefTransactionRow.BatchNumber; ANewRow.JournalNumber = ARefTransactionRow.JournalNumber; ANewRow.TransactionNumber = ARefTransactionRow.TransactionNumber; }
/// <summary> /// Used for the validation of Analysis Attributes /// </summary> /// <param name="ATransactionNumber"></param> /// <param name="AAccountCode"></param> /// <param name="AGLBatchDS"></param> /// <param name="ValueRequiredForType"></param> /// <param name="AIsUnposted"></param> /// <returns></returns> public bool AccountAnalysisAttributesValuesExist(int ATransactionNumber, string AAccountCode, GLBatchTDS AGLBatchDS, out String ValueRequiredForType, bool AIsUnposted = true) { ValueRequiredForType = ""; if (!AIsUnposted || string.IsNullOrEmpty(AAccountCode) || (AGLBatchDS.ATransAnalAttrib.DefaultView.Count == 0)) { return(true); } StringCollection RequiredAnalAttrCodes = TRemote.MFinance.Setup.WebConnectors.RequiredAnalysisAttributesForAccount(FLedgerNumber, AAccountCode, true); string AnalysisCodeFilterValues = TAnalysisAttributes.ConvertStringCollectionToCSV(RequiredAnalAttrCodes, "'"); DataView analAttrib = new DataView(AGLBatchDS.ATransAnalAttrib); analAttrib.RowFilter = String.Format("{0}={1} AND {2}={3} AND {4}={5} AND {6} IN ({7})", ATransAnalAttribTable.GetBatchNumberDBName(), FBatchNumber, ATransAnalAttribTable.GetJournalNumberDBName(), FJournalNumber, ATransAnalAttribTable.GetTransactionNumberDBName(), ATransactionNumber, ATransAnalAttribTable.GetAnalysisTypeCodeDBName(), AnalysisCodeFilterValues); foreach (DataRowView drv in analAttrib) { ATransAnalAttribRow rw = (ATransAnalAttribRow)drv.Row; string analysisCode = rw.AnalysisTypeCode; if (TRemote.MFinance.Setup.WebConnectors.AccountAnalysisAttributeRequiresValues(FLedgerNumber, analysisCode, true)) { if (rw.IsAnalysisAttributeValueNull() || (rw.AnalysisAttributeValue == string.Empty)) { ValueRequiredForType = rw.AnalysisTypeCode; return(false); } } } return(true); }
private void RefreshAnalysisAttributesGrid(TCmbAutoPopulated ACombo, GLBatchTDS ADS) { TSgrdDataGridPaged Grid = null; int TransactionNumber = 0; if (ACombo.Name == cmbFromAccountCode.Name) { Grid = grdFromAnalAttributes; TransactionNumber = FJournal.LastTransactionNumber + 1; } else { Grid = grdToAnalAttributes; TransactionNumber = FCurrentTransactionNumber; } //Empty the grid ADS.ATransAnalAttrib.DefaultView.RowFilter = "1=2"; FPSAttributesRow = null; if (!TRemote.MFinance.Setup.WebConnectors.AccountHasAnalysisAttributes(FLedgerNumber, ACombo.GetSelectedString(), true)) { if (Grid.Enabled) { Grid.Enabled = false; } return; } else { if (!Grid.Enabled) { Grid.Enabled = true; } } FAnalysisAttributesLogic.SetTransAnalAttributeDefaultView(ADS, TransactionNumber); Grid.DataSource = new DevAge.ComponentModel.BoundDataView(ADS.ATransAnalAttrib.DefaultView); if (Grid.Rows.Count > 1) { Grid.SelectRowWithoutFocus(1); AnalysisAttributesGrid_RowSelected(Grid, null); } }
private void ShowDetailsManual(ATransAnalAttribRow ARow) { cmbDetailAnalysisAttributeValue.Items.Clear(); if (ARow == null) { return; } // The content of the combobox derives from the typecode, the ledgernumber and whether the value is active. foreach (AFreeformAnalysisRow AFRow in FCacheDS.AFreeformAnalysis.Rows) { if (ARow.AnalysisTypeCode.Equals(AFRow.AnalysisTypeCode) && ARow.LedgerNumber.Equals(AFRow.LedgerNumber)) { // add value if it is active but also if not active and already set if (AFRow.Active || (ARow.AnalysisAttributeValue == AFRow.AnalysisValue)) { cmbDetailAnalysisAttributeValue.Items.Add(AFRow.AnalysisValue); } } } txtReadonlyAnalysisTypeCode.Text = ARow.AnalysisTypeCode; AAnalysisTypeRow analysisTypeRow = (AAnalysisTypeRow)FCacheDS.AAnalysisType.Rows.Find(new Object[] { ARow.AnalysisTypeCode }); txtReadonlyDescription.Text = analysisTypeRow.AnalysisTypeDescription; if ((ARow.AnalysisAttributeValue != null) && (ARow.AnalysisAttributeValue.Length > 0)) { cmbDetailAnalysisAttributeValue.SetSelectedString(ARow.AnalysisAttributeValue); } else { cmbDetailAnalysisAttributeValue.SetSelectedString("", -1); } // If the batch has been posted, the Combobox can't be changed. Boolean changeable = GetBatchRow() != null && (GetBatchRow().BatchStatus == MFinanceConstants.BATCH_UNPOSTED); cmbDetailAnalysisAttributeValue.Enabled = changeable; }
private void RefreshAnalysisAttributesGrid(object sender, GLBatchTDS ADS) { TSgrdDataGridPaged Grid = null; int TransactionNumber = 0; string AccountCode = ""; if ((sender as SourceGrid.Selection.RowSelection) != null) { Grid = grdFromAnalAttributes; TransactionNumber = FJournal.LastTransactionNumber + 1; AccountCode = FPreviouslySelectedAccountsRow[AGeneralLedgerMasterTable.GetAccountCodeDBName()].ToString(); } else { Grid = grdToAnalAttributes; TransactionNumber = FCurrentTransactionNumber; AccountCode = cmbDetailAccountCode.GetSelectedString(); } //Empty the grid ADS.ATransAnalAttrib.DefaultView.RowFilter = "1=2"; FPSAttributesRow = null; if (!TRemote.MFinance.Setup.WebConnectors.AccountHasAnalysisAttributes(FLedgerNumber, AccountCode, true)) { if (Grid.Enabled) { Grid.Enabled = false; } return; } else { if (!Grid.Enabled) { Grid.Enabled = true; } } FAnalysisAttributesLogic.SetTransAnalAttributeDefaultView(ADS, true, TransactionNumber); Grid.DataSource = new DevAge.ComponentModel.BoundDataView(ADS.ATransAnalAttrib.DefaultView); if (Grid.Rows.Count > 1) { Grid.SelectRowWithoutFocus(1); AnalysisAttributesGrid_RowSelected(Grid, null); } }
private void AnalysisAttributesGrid_RowSelected(System.Object sender, RangeRegionChangedEventArgs e) { TSgrdDataGridPaged Grid = sender as TSgrdDataGridPaged; if (Grid == null) { Grid = (TSgrdDataGridPaged)((SourceGrid.Selection.RowSelection)sender).Grid; } if (Grid.Selection.ActivePosition.IsEmpty() || (Grid.Selection.ActivePosition.Column == 0)) { return; } if ((TAnalysisAttributes.GetSelectedAttributeRow(Grid) == null) || (FPSAttributesRow == TAnalysisAttributes.GetSelectedAttributeRow(Grid))) { return; } FPSAttributesRow = TAnalysisAttributes.GetSelectedAttributeRow(Grid); string currentAnalTypeCode = FPSAttributesRow.AnalysisTypeCode; FCacheDS.AFreeformAnalysis.DefaultView.RowFilter = String.Format("{0}='{1}' AND {2}=true", AFreeformAnalysisTable.GetAnalysisTypeCodeDBName(), currentAnalTypeCode, AFreeformAnalysisTable.GetActiveDBName()); int analTypeCodeValuesCount = FCacheDS.AFreeformAnalysis.DefaultView.Count; if (analTypeCodeValuesCount == 0) { MessageBox.Show(Catalog.GetString( "No attribute values are defined!"), currentAnalTypeCode, MessageBoxButtons.OK, MessageBoxIcon.Error); } string[] analTypeValues = new string[analTypeCodeValuesCount]; FCacheDS.AFreeformAnalysis.DefaultView.Sort = AFreeformAnalysisTable.GetAnalysisValueDBName(); int counter = 0; foreach (DataRowView dvr in FCacheDS.AFreeformAnalysis.DefaultView) { AFreeformAnalysisRow faRow = (AFreeformAnalysisRow)dvr.Row; analTypeValues[counter] = faRow.AnalysisValue; counter++; } //Refresh the combo values if (Grid.Name == grdFromAnalAttributes.Name) { FcmbFromAnalAttribValues.StandardValuesExclusive = true; FcmbFromAnalAttribValues.StandardValues = analTypeValues; } else { FcmbToAnalAttribValues.StandardValuesExclusive = true; FcmbToAnalAttribValues.StandardValues = analTypeValues; } }
private void RefreshAnalysisAttributesGrid() { //Empty the grid FMainDS.ATransAnalAttrib.DefaultView.RowFilter = "1=2"; FPSAttributesRow = null; if ((FPreviouslySelectedDetailRow == null) || (!pnlTransAnalysisAttributes.Enabled && FIsUnposted) || !TRemote.MFinance.Setup.WebConnectors.AccountHasAnalysisAttributes(FLedgerNumber, cmbDetailAccountCode.GetSelectedString(), FActiveOnly)) { if (grdAnalAttributes.Enabled) { grdAnalAttributes.Enabled = false; } return; } else { if (!grdAnalAttributes.Enabled) { grdAnalAttributes.Enabled = true; } } FAnalysisAttributesLogic.SetTransAnalAttributeDefaultView(FMainDS, FTransactionNumber); grdAnalAttributes.DataSource = new DevAge.ComponentModel.BoundDataView(FMainDS.ATransAnalAttrib.DefaultView); if (grdAnalAttributes.Rows.Count > 1) { grdAnalAttributes.SelectRowWithoutFocus(1); AnalysisAttributesGrid_RowSelected(null, null); } }
private bool AllowInactiveFieldValues(int ABatchNumber, out int ANumInactiveValues) { bool RetVal = false; TVerificationResultCollection VerificationResult = new TVerificationResultCollection(); string VerificationMessage = string.Empty; DataView TransDV = new DataView(FMainDS.ATransaction); DataView AttribDV = new DataView(FMainDS.ATransAnalAttrib); int NumInactiveAccounts = 0; int NumInactiveCostCentres = 0; int NumInactiveAccountTypes = 0; int NumInactiveAccountValues = 0; try { //Check for inactive account or cost centre codes TransDV.RowFilter = String.Format("{0}={1}", ATransactionTable.GetBatchNumberDBName(), ABatchNumber); TransDV.Sort = String.Format("{0} ASC, {1} ASC", ATransactionTable.GetJournalNumberDBName(), ATransactionTable.GetTransactionNumberDBName()); foreach (DataRowView drv in TransDV) { ATransactionRow transRow = (ATransactionRow)drv.Row; if (!AccountIsActive(transRow.AccountCode)) { VerificationMessage += String.Format(" Account '{0}' in Journal:{1} Transaction:{2}.{3}", transRow.AccountCode, transRow.JournalNumber, transRow.TransactionNumber, Environment.NewLine); NumInactiveAccounts++; } if (!CostCentreIsActive(transRow.CostCentreCode)) { VerificationMessage += String.Format(" Cost Centre '{0}' in Journal:{1} Transaction:{2}.{3}", transRow.CostCentreCode, transRow.JournalNumber, transRow.TransactionNumber, Environment.NewLine); NumInactiveCostCentres++; } } //Check anlysis attributes AttribDV.RowFilter = String.Format("{0}={1}", ATransAnalAttribTable.GetBatchNumberDBName(), ABatchNumber); AttribDV.Sort = String.Format("{0} ASC, {1} ASC, {2} ASC", ATransAnalAttribTable.GetJournalNumberDBName(), ATransAnalAttribTable.GetTransactionNumberDBName(), ATransAnalAttribTable.GetAnalysisTypeCodeDBName()); foreach (DataRowView drv2 in AttribDV) { ATransAnalAttribRow analAttribRow = (ATransAnalAttribRow)drv2.Row; if (!AnalysisCodeIsActive(analAttribRow.AccountCode, analAttribRow.AnalysisTypeCode)) { VerificationMessage += String.Format(" Analysis Code '{0}' in Journal:{1} Transaction:{2}.{3}", analAttribRow.AnalysisTypeCode, analAttribRow.JournalNumber, analAttribRow.TransactionNumber, Environment.NewLine); NumInactiveAccountTypes++; } if (!AnalysisAttributeValueIsActive(analAttribRow.AnalysisTypeCode, analAttribRow.AnalysisAttributeValue)) { VerificationMessage += String.Format(" Analysis Value '{0}' in Journal:{1} Transaction:{2}.{3}", analAttribRow.AnalysisAttributeValue, analAttribRow.JournalNumber, analAttribRow.TransactionNumber, Environment.NewLine); NumInactiveAccountValues++; } } } catch (Exception ex) { TLogging.LogException(ex, Utilities.GetMethodSignature()); throw; } ANumInactiveValues = (NumInactiveAccounts + NumInactiveCostCentres + NumInactiveAccountTypes + NumInactiveAccountValues); if (ANumInactiveValues > 0) { VerificationResult.Add(new TVerificationResult(string.Format(Catalog.GetString("Inactive Values:{0}"), Environment.NewLine), VerificationMessage, TResultSeverity.Resv_Noncritical)); StringBuilder errorMessages = new StringBuilder(); errorMessages.AppendFormat(Catalog.GetString("{0} inactive value(s) found in GL Batch {1}. Do you still want to post?{2}"), ANumInactiveValues, ABatchNumber, Environment.NewLine); foreach (TVerificationResult message in VerificationResult) { errorMessages.AppendFormat("{0}{1}", Environment.NewLine, message.ResultText); } TFrmExtendedMessageBox extendedMessageBox = new TFrmExtendedMessageBox(FMyForm); RetVal = (extendedMessageBox.ShowDialog(errorMessages.ToString(), Catalog.GetString("Post Batch"), string.Empty, TFrmExtendedMessageBox.TButtons.embbYesNo, TFrmExtendedMessageBox.TIcon.embiQuestion) == TFrmExtendedMessageBox.TResult.embrYes); } else { RetVal = true; } return(RetVal); }
/// <summary> /// Re-show the specified row /// </summary> /// <param name="ACurrentBatch"></param> /// <param name="AJournalToCancel"></param> /// <param name="ARedisplay"></param> public void PrepareJournalDataForCancelling(Int32 ACurrentBatch, Int32 AJournalToCancel, Boolean ARedisplay) { //This code will only be called when the Journal tab is active. DataView JournalDV = new DataView(FMainDS.AJournal); DataView TransDV = new DataView(FMainDS.ATransaction); DataView TransAnalDV = new DataView(FMainDS.ATransAnalAttrib); JournalDV.RowFilter = String.Format("{0}={1} And {2}={3}", AJournalTable.GetBatchNumberDBName(), ACurrentBatch, AJournalTable.GetJournalNumberDBName(), AJournalToCancel); TransDV.RowFilter = String.Format("{0}={1} And {2}={3}", ATransactionTable.GetBatchNumberDBName(), ACurrentBatch, ATransactionTable.GetJournalNumberDBName(), AJournalToCancel); TransAnalDV.RowFilter = String.Format("{0}={1} And {2}={3}", ATransAnalAttribTable.GetBatchNumberDBName(), ACurrentBatch, ATransAnalAttribTable.GetJournalNumberDBName(), AJournalToCancel); //Work from lowest level up if (TransAnalDV.Count > 0) { TransAnalDV.Sort = String.Format("{0}, {1}", ATransAnalAttribTable.GetTransactionNumberDBName(), ATransAnalAttribTable.GetAnalysisTypeCodeDBName()); foreach (DataRowView drv in TransAnalDV) { ATransAnalAttribRow transAnalRow = (ATransAnalAttribRow)drv.Row; if (transAnalRow.RowState == DataRowState.Added) { //Do nothing } else if (transAnalRow.RowState != DataRowState.Unchanged) { transAnalRow.RejectChanges(); } } } if (TransDV.Count > 0) { TransDV.Sort = String.Format("{0}", ATransactionTable.GetTransactionNumberDBName()); foreach (DataRowView drv in TransDV) { ATransactionRow transRow = (ATransactionRow)drv.Row; if (transRow.RowState == DataRowState.Added) { //Do nothing } else if (transRow.RowState != DataRowState.Unchanged) { transRow.RejectChanges(); } } } if (JournalDV.Count > 0) { GLBatchTDSAJournalRow journalRow = (GLBatchTDSAJournalRow)JournalDV[0].Row; //No need to check for Added state as new journals are always saved // on creation if (journalRow.RowState != DataRowState.Unchanged) { journalRow.RejectChanges(); } if (ARedisplay) { ShowDetails(journalRow); } } if (TransDV.Count == 0) { //Load all related data for journal ready to delete/cancel FMainDS.Merge(TRemote.MFinance.GL.WebConnectors.LoadATransactionAndRelatedTablesForJournal(FLedgerNumber, ACurrentBatch, AJournalToCancel)); } }
private void GetDetailDataFromControlsManual(ATransAnalAttribRow ARow) { //needed? }
/// <summary> /// make sure the correct transaction number is assigned and the journal.lastTransactionNumber is updated; /// will use the currently selected journal /// </summary> public void NewRowManual(ref ATransAnalAttribRow ANewRow) { NewRowManual(ref ANewRow, null); }
private void RefreshAnalysisAttributesGrid(TCmbAutoPopulated ACombo, GLBatchTDS ADS) { TSgrdDataGridPaged Grid = null; int TransactionNumber = 0; if (ACombo.Name == cmbFromAccountCode.Name) { Grid = grdFromAnalAttributes; TransactionNumber = FJournal.LastTransactionNumber + 1; } else { Grid = grdToAnalAttributes; TransactionNumber = FCurrentTransactionNumber; } //Empty the grid ADS.ATransAnalAttrib.DefaultView.RowFilter = "1=2"; FPSAttributesRow = null; if (!TRemote.MFinance.Setup.WebConnectors.AccountHasAnalysisAttributes(FLedgerNumber, ACombo.GetSelectedString(), true)) { if (Grid.Enabled) { Grid.Enabled = false; } return; } else { if (!Grid.Enabled) { Grid.Enabled = true; } } FAnalysisAttributesLogic.SetTransAnalAttributeDefaultView(ADS, true, TransactionNumber); Grid.DataSource = new DevAge.ComponentModel.BoundDataView(ADS.ATransAnalAttrib.DefaultView); if (Grid.Rows.Count > 1) { Grid.SelectRowWithoutFocus(1); AnalysisAttributesGrid_RowSelected(Grid, null); } }
/// <summary> /// export all GL Transactions in the given year, towards the specified cost centres /// </summary> public static void ExportGLTransactions(string AOutputPath, char ACSVSeparator, string ANewLine, Int32 ALedgerNumber, Int32 AFinancialYear, string ACostCentres, string AIgnoreAccounts, string AIgnoreReferences, SortedList <string, string> ATaxAnalysisAttributes, ref List <string> ACostCentresInvolved, ref List <string> AAccountsInvolved) { string filename = Path.GetFullPath(Path.Combine(AOutputPath, "transaction.csv")); Console.WriteLine("Writing file: " + filename); TDBTransaction Transaction = null; ATransactionTable transactions = new ATransactionTable(); ATransAnalAttribTable TransAnalAttrib = new ATransAnalAttribTable(); ATransactionTable allTransactionsInJournal = new ATransactionTable(); AGiftBatchTable giftbatches = new AGiftBatchTable(); AAccountTable accounts = new AAccountTable(); DBAccess.GDBAccessObj.BeginAutoReadTransaction(IsolationLevel.ReadCommitted, ref Transaction, delegate { string sql = String.Format("SELECT T.*, B.{4} AS a_transaction_date_d " + "FROM PUB_{8} AS B, PUB_{7} AS T " + "WHERE B.{9} = {10} AND B.{15} = {16} AND B.{11}='{12}' " + "AND T.{9} = B.{9} AND T.{0} = B.{0} " + "AND T.{13} IN ({14}) " + "AND NOT T.{17} IN ({19}) " + "AND NOT T.{20} IN ({21}) " + "ORDER BY {0}, {1}, {2}", ATransactionTable.GetBatchNumberDBName(), ATransactionTable.GetJournalNumberDBName(), ATransactionTable.GetTransactionNumberDBName(), ATransactionTable.GetTransactionAmountDBName(), ABatchTable.GetDateEffectiveDBName(), ATransactionTable.GetNarrativeDBName(), ATransactionTable.GetReferenceDBName(), ATransactionTable.GetTableDBName(), ABatchTable.GetTableDBName(), ATransactionTable.GetLedgerNumberDBName(), ALedgerNumber, ABatchTable.GetBatchStatusDBName(), MFinanceConstants.BATCH_POSTED, ATransactionTable.GetCostCentreCodeDBName(), "'" + ACostCentres.Replace(",", "','") + "'", ABatchTable.GetBatchYearDBName(), AFinancialYear, ATransactionTable.GetAccountCodeDBName(), ATransactionTable.GetDebitCreditIndicatorDBName(), "'" + AIgnoreAccounts.Replace(",", "','") + "'", ATransactionTable.GetReferenceDBName(), "'" + AIgnoreReferences.Replace(",", "','") + "'"); transactions = (ATransactionTable)DBAccess.GDBAccessObj.SelectDT(transactions, sql, Transaction, null, 0, 0); // get the analysis attributes sql = String.Format("SELECT A.* from PUB_{1} AS B, PUB_{13} AS T, PUB_{0} AS A " + "WHERE B.{2} = {3} AND B.{4} = {5} AND B.{6}='{7}' " + "AND T.{2} = B.{2} AND T.{8} = B.{8} " + "AND T.{9} IN ({10}) " + "AND A.{2} = T.{2} AND A.{8} = T.{8} AND A.{11} = T.{11} AND A.{12} = T.{12}", ATransAnalAttribTable.GetTableDBName(), ABatchTable.GetTableDBName(), ABatchTable.GetLedgerNumberDBName(), ALedgerNumber, ABatchTable.GetBatchYearDBName(), AFinancialYear, ABatchTable.GetBatchStatusDBName(), MFinanceConstants.BATCH_POSTED, ATransactionTable.GetBatchNumberDBName(), ATransactionTable.GetCostCentreCodeDBName(), "'" + ACostCentres.Replace(",", "','") + "'", ATransactionTable.GetJournalNumberDBName(), ATransactionTable.GetTransactionNumberDBName(), ATransactionTable.GetTableDBName(), ABatchTable.GetBatchYearDBName()); DBAccess.GDBAccessObj.SelectDT(TransAnalAttrib, sql, Transaction, null, 0, 0); TransAnalAttrib.DefaultView.Sort = ATransAnalAttribTable.GetBatchNumberDBName() + "," + ATransAnalAttribTable.GetJournalNumberDBName() + "," + ATransAnalAttribTable.GetTransactionNumberDBName(); // get a list of all batches involved List <Int64> batches = new List <Int64>(); StringBuilder batchnumbers = new StringBuilder(); foreach (ATransactionRow r in transactions.Rows) { if (!batches.Contains(r.BatchNumber)) { batches.Add(r.BatchNumber); batchnumbers.Append(r.BatchNumber.ToString() + ","); } } // get the other transactions in the same journal for finding the opposite cc/acc involved // for performance reasons, get all transactions of the whole batch sql = String.Format("SELECT DISTINCT TJ.* " + "FROM PUB_{0} AS TJ " + "WHERE TJ.{1} = {2} AND TJ.{3} IN ({4})", ATransactionTable.GetTableDBName(), ATransactionTable.GetLedgerNumberDBName(), ALedgerNumber, ATransactionTable.GetBatchNumberDBName(), batchnumbers.ToString() + "-1"); allTransactionsInJournal = (ATransactionTable)DBAccess.GDBAccessObj.SelectDT(allTransactionsInJournal, sql, Transaction, null, 0, 0); allTransactionsInJournal.DefaultView.Sort = ATransactionTable.GetBatchNumberDBName() + "," + ATransactionTable.GetJournalNumberDBName(); // get all names of gift batches sql = String.Format("SELECT * FROM PUB_{0} " + "WHERE {1} = {2} " + "AND {3} = {4}", AGiftBatchTable.GetTableDBName(), AGiftBatchTable.GetLedgerNumberDBName(), ALedgerNumber, AGiftBatchTable.GetBatchYearDBName(), AFinancialYear); DBAccess.GDBAccessObj.SelectDT(giftbatches, sql, Transaction, null, 0, 0); giftbatches.DefaultView.Sort = AGiftBatchTable.GetBatchNumberDBName(); sql = String.Format("SELECT * FROM PUB_{0} " + "WHERE {1} = {2}", AAccountTable.GetTableDBName(), AAccountTable.GetLedgerNumberDBName(), ALedgerNumber); DBAccess.GDBAccessObj.SelectDT(accounts, sql, Transaction, null, 0, 0); accounts.DefaultView.Sort = AAccountTable.GetAccountCodeDBName(); }); StringBuilder sb = new StringBuilder(); int rowCounter = 0; StringCollection costcentreCollection = StringHelper.StrSplit(ACostCentres, ","); foreach (ATransactionRow row in transactions.Rows) { if (row.DebitCreditIndicator) { row.TransactionAmount *= -1.0m; } StringBuilder attributes = new StringBuilder(); DataRowView[] attribs = TransAnalAttrib.DefaultView.FindRows(new object[] { row.BatchNumber, row.JournalNumber, row.TransactionNumber }); decimal TaxOnIncome = 0.0m; decimal TaxOnExpense = 0.0m; // only mention tax codes, if this transaction was against a costcentre that has to pay taxes if (costcentreCollection.Contains(row.CostCentreCode)) { foreach (DataRowView rv in attribs) { ATransAnalAttribRow attribRow = (ATransAnalAttribRow)rv.Row; // also export attribRow.AnalysisTypeCode? attributes.Append(attribRow.AnalysisAttributeValue); if (attribRow.AnalysisAttributeValue == "v19") { TaxOnExpense = row.TransactionAmount * 0.19m; } else if (attribRow.AnalysisAttributeValue == "v7") { TaxOnExpense = row.TransactionAmount * 0.07m; } else if (attribRow.AnalysisAttributeValue == "70v7") { TaxOnExpense = row.TransactionAmount * 0.7m * 0.07m; } else if (attribRow.AnalysisAttributeValue == "70v19") { TaxOnExpense = row.TransactionAmount * 0.7m * 0.19m; } else if (attribRow.AnalysisAttributeValue == "m19") { TaxOnIncome = row.TransactionAmount * 0.19m; } else if (attribRow.AnalysisAttributeValue == "m7") { TaxOnIncome = row.TransactionAmount * 0.07m; } } } DataRowView[] RelatedTransactions = allTransactionsInJournal.DefaultView.FindRows(new object[] { row.BatchNumber, row.JournalNumber }); ATransactionRow[] OtherTransactions = GetOtherTransactions(row, RelatedTransactions); string OtherCostCentres = string.Empty; string OtherAccountCodes = string.Empty; if (OtherTransactions.Length < 30) { foreach (ATransactionRow r in OtherTransactions) { OtherCostCentres = StringHelper.AddCSV(OtherCostCentres, r.CostCentreCode); OtherAccountCodes = StringHelper.AddCSV(OtherAccountCodes, r.AccountCode); } } if (!ACostCentresInvolved.Contains(row.CostCentreCode)) { ACostCentresInvolved.Add(row.CostCentreCode); } if (!AAccountsInvolved.Contains(row.AccountCode)) { AAccountsInvolved.Add(row.AccountCode); } // we are using gift batch for receiving payments string Narrative = row.Narrative; if (Narrative.StartsWith("GB - Gift Batch ") && row.Reference.StartsWith("GB")) { // find the account and set the account description into the narrative try { DataRowView[] acc = accounts.DefaultView.FindRows(row.AccountCode); Narrative = ((AAccountRow)acc[0].Row).AccountCodeLongDesc; } catch (Exception) { } try { DataRowView[] gb = giftbatches.DefaultView.FindRows(Convert.ToInt32(row.Reference.Substring(2))); Narrative += " " + ((AGiftBatchRow)gb[0].Row).BatchDescription; } catch (Exception) { } } sb.Append(StringHelper.StrMerge( new string[] { "B" + row.BatchNumber.ToString() + "_J" + row.JournalNumber.ToString() + "_T" + row.TransactionNumber.ToString(), row.CostCentreCode, row.AccountCode, row.TransactionDate.ToString("yyyyMMdd"), OtherCostCentres, OtherAccountCodes, Narrative, row.Reference, String.Format("{0:N}", row.TransactionAmount), attributes.ToString(), TaxOnIncome.ToString(), TaxOnExpense.ToString() }, ACSVSeparator)); sb.Append(ANewLine); rowCounter++; if (rowCounter % 500 == 0) { TLogging.Log("Processing transactions " + rowCounter.ToString()); } } TLogging.Log("Processing transactions " + rowCounter.ToString()); StreamWriter sw = new StreamWriter(filename, false, Encoding.GetEncoding(1252)); sw.Write(sb.ToString()); sw.Close(); }