/// <summary>
/// Update the specified Journal's LastTransaction number. Assumes all necessary data is loaded for Journal
/// </summary>
/// <param name="AMainDS">ATransactions are filtered on current journal</param>
/// <param name="ACurrentJournal"></param>
/// <returns>false if no change to journal totals</returns>
public static bool UpdateJournalLastTransaction(ref GLBatchTDS AMainDS,
ref GLBatchTDSAJournalRow ACurrentJournal)
{
#region Validate Arguments
if (AMainDS == null)
{
throw new EFinanceSystemDataObjectNullOrEmptyException(String.Format(Catalog.GetString("Function:{0} - The GL Batch dataset is null!"),
Utilities.GetMethodName(true)));
}
else if (ACurrentJournal == null)
{
throw new EFinanceSystemDataObjectNullOrEmptyException(String.Format(Catalog.GetString(
"Function:{0} - The Journal row does not exist or is empty!"),
Utilities.GetMethodName(true)));
}
else if (ACurrentJournal.JournalStatus != MFinanceConstants.BATCH_UNPOSTED)
{
TLogging.Log(String.Format("Function:{0} - Tried to update totals for non-Unposted Batch:{1} and Journal:{2}",
Utilities.GetMethodName(true),
ACurrentJournal.BatchNumber,
ACurrentJournal.JournalNumber));
return(false);
}
#endregion Validate Arguments
bool RowUpdated = false;
int ActualLastTransNumber = 0;
DataView TransDV = new DataView(AMainDS.ATransaction);
TransDV.RowFilter = String.Format("{0}={1} And {2}={3}",
ATransactionTable.GetBatchNumberDBName(),
ACurrentJournal.BatchNumber,
ATransactionTable.GetJournalNumberDBName(),
ACurrentJournal.JournalNumber);
TransDV.Sort = String.Format("{0} DESC",
ATransactionTable.GetTransactionNumberDBName());
foreach (DataRowView drv in TransDV)
{
ATransactionRow transRow = (ATransactionRow)drv.Row;
//Run once only
ActualLastTransNumber = transRow.TransactionNumber;
break;
}
if (ACurrentJournal.LastTransactionNumber != ActualLastTransNumber)
{
ACurrentJournal.BeginEdit();
ACurrentJournal.LastTransactionNumber = ActualLastTransNumber;
ACurrentJournal.EndEdit();
RowUpdated = true;
}
return(RowUpdated);
}
private void LoadJournalsForCurrentBatch()
{
//Current Batch number
Int32 BatchNumber = FPreviouslySelectedDetailRow.BatchNumber;
if (FMainDS.AJournal != null)
{
FMainDS.AJournal.DefaultView.RowFilter = String.Format("{0}={1}",
ATransactionTable.GetBatchNumberDBName(),
BatchNumber);
if (FMainDS.AJournal.DefaultView.Count == 0)
{
FMainDS.Merge(TRemote.MFinance.GL.WebConnectors.LoadAJournal(FLedgerNumber, BatchNumber));
}
}
}
/// <summary>
/// Update the totals for the current journal (no exchange rate calculation at this point)
/// and set LastTransactionNumber
/// </summary>
/// <param name="AMainDS">ATransactions are filtered on current journal</param>
/// <param name="ACurrentJournal"></param>
public static void UpdateTotalsOfJournal(ref GLBatchTDS AMainDS,
ref GLBatchTDSAJournalRow ACurrentJournal)
{
if (ACurrentJournal == null)
{
return;
}
int LastTransactionNumber = 0;
decimal JournalDebitTotal = 0.0M;
decimal JournalDebitTotalBase = 0.0M;
decimal JournalCreditTotal = 0.0M;
decimal JournalCreditTotalBase = 0.0M;
if (DBNull.Value.Equals(ACurrentJournal[GLBatchTDSAJournalTable.ColumnJournalDebitTotalBaseId]))
{
ACurrentJournal.JournalDebitTotalBase = 0;
}
if (DBNull.Value.Equals(ACurrentJournal[GLBatchTDSAJournalTable.ColumnJournalCreditTotalBaseId]))
{
ACurrentJournal.JournalCreditTotalBase = 0;
}
DataView TransDataView = new DataView(AMainDS.ATransaction);
TransDataView.RowFilter = String.Format("{0}={1} And {2}={3}",
ATransactionTable.GetBatchNumberDBName(),
ACurrentJournal.BatchNumber,
ATransactionTable.GetJournalNumberDBName(),
ACurrentJournal.JournalNumber);
TransDataView.Sort = string.Format("{0} DESC",
ATransactionTable.GetTransactionNumberDBName());
// transactions are filtered for this journal; add up the total amounts
foreach (DataRowView drv in TransDataView)
{
ATransactionRow transRow = (ATransactionRow)drv.Row;
//on first recursion
if (transRow.TransactionNumber > LastTransactionNumber)
{
//Sort order will ensure this is the highest trans number on first pass
LastTransactionNumber = transRow.TransactionNumber;
}
if (transRow.DebitCreditIndicator)
{
JournalDebitTotal += transRow.TransactionAmount;
JournalDebitTotalBase += transRow.AmountInBaseCurrency;
}
else
{
JournalCreditTotal += transRow.TransactionAmount;
JournalCreditTotalBase += transRow.AmountInBaseCurrency;
}
}
if ((ACurrentJournal.JournalDebitTotal != JournalDebitTotal) ||
(ACurrentJournal.JournalDebitTotalBase != JournalDebitTotalBase) ||
(ACurrentJournal.JournalCreditTotal != JournalCreditTotal) ||
(ACurrentJournal.JournalCreditTotalBase != JournalCreditTotalBase))
{
ACurrentJournal.JournalDebitTotal = JournalDebitTotal;
ACurrentJournal.JournalDebitTotalBase = JournalDebitTotalBase;
ACurrentJournal.JournalCreditTotal = JournalCreditTotal;
ACurrentJournal.JournalCreditTotalBase = JournalCreditTotalBase;
}
if (ACurrentJournal.LastTransactionNumber != LastTransactionNumber)
{
ACurrentJournal.LastTransactionNumber = LastTransactionNumber;
}
}
/// <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,
ref List <string> ACostCentresInvolved,
ref List <string> AAccountsInvolved)
{
string filename = Path.GetFullPath(Path.Combine(AOutputPath, "transaction.csv"));
Console.WriteLine("Writing file: " + filename);
TDBTransaction Transaction = new TDBTransaction();
ATransactionTable transactions = new ATransactionTable();
ATransAnalAttribTable TransAnalAttrib = new ATransAnalAttribTable();
ATransactionTable allTransactionsInJournal = new ATransactionTable();
AGiftBatchTable giftbatches = new AGiftBatchTable();
AAccountTable accounts = new AAccountTable();
DBAccess.ReadTransaction(ref Transaction,
delegate
{
TDataBase db = Transaction.DataBaseObj;
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)db.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());
db.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)db.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);
db.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);
db.SelectDT(accounts, sql, Transaction, null, 0, 0);
accounts.DefaultView.Sort = AAccountTable.GetAccountCodeDBName();
});
StringBuilder sb = new StringBuilder();
int rowCounter = 0;
foreach (ATransactionRow row in transactions.Rows)
{
if (row.DebitCreditIndicator)
{
row.TransactionAmount *= -1.0m;
}
StringBuilder attributes = new StringBuilder();
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()
}, 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();
}
/// <summary>
/// Used internally to build a SQL WHERE criteria from the AFindCriteria HashTable.
///
/// </summary>
/// <param name="ACriteriaData">HashTable containing non-empty Partner Find parameters</param>
/// <param name="AParametersArray">An array holding 1..n instantiated OdbcParameters
/// (including parameter Value)</param>
/// <returns>SQL WHERE criteria
/// </returns>
private static String BuildCustomWhereCriteria(DataTable ACriteriaData, out OdbcParameter[] AParametersArray)
{
String CustomWhereCriteria = "";
DataTable CriteriaDataTable;
DataRow CriteriaRow;
ArrayList InternalParameters;
CriteriaDataTable = ACriteriaData;
CriteriaRow = CriteriaDataTable.Rows[0];
InternalParameters = new ArrayList();
if (CriteriaRow["Ledger"].ToString().Length > 0)
{
// Searched DB Field: 'a_ledger_number_i'
new TDynamicSearchHelper(ATransactionTable.TableId,
ATransactionTable.ColumnLedgerNumberId, CriteriaRow, "Ledger", "",
ref CustomWhereCriteria, ref InternalParameters);
}
// Searched DB Field: 'a_batch_number_i'
if ((CriteriaRow["BatchNumber"] != null) && (CriteriaRow["BatchNumber"] != System.DBNull.Value))
{
// do manually otherwise 0 gets changed to a string and we get a crash
CustomWhereCriteria = String.Format("{0} AND {1} = ?", CustomWhereCriteria,
"PUB_" + TTypedDataTable.GetTableNameSQL(ATransactionTable.TableId) + "." + ATransactionTable.GetBatchNumberDBName());
OdbcParameter miParam = new OdbcParameter("", OdbcType.Int, 10);
miParam.Value = (object)(CriteriaRow["BatchNumber"]);
InternalParameters.Add(miParam);
}
// Searched DB Field: 'a_transaction_status_l'
if (!string.IsNullOrEmpty(CriteriaRow["TransactionStatus"].ToString()))
{
CustomWhereCriteria = String.Format("{0} AND {1} = ?", CustomWhereCriteria,
"CAST (PUB_" + TTypedDataTable.GetTableNameSQL(
ATransactionTable.TableId) + "." + ATransactionTable.GetTransactionStatusDBName() + " as INT)");
OdbcParameter miParam = new OdbcParameter("", DbType.Boolean);
miParam.Value = (object)(CriteriaRow["TransactionStatus"]);
InternalParameters.Add(miParam);
}
// Searched DB Field: 'a_batch_description_c'
if (!string.IsNullOrEmpty(CriteriaRow["BatchDescription"].ToString()))
{
CriteriaRow.Table.Columns.Add(new DataColumn("BatchDescriptionMatch"));
CriteriaRow["BatchDescriptionMatch"] = "CONTAINS";
new TDynamicSearchHelper(ABatchTable.TableId,
ABatchTable.ColumnBatchDescriptionId, CriteriaRow, "BatchDescription", "BatchDescriptionMatch",
ref CustomWhereCriteria,
ref InternalParameters);
}
// Searched DB Field: 'a_journal_description_c'
if (CriteriaRow["JournalDescription"].ToString().Length > 0)
{
CriteriaRow.Table.Columns.Add(new DataColumn("JournalDescriptionMatch"));
CriteriaRow["JournalDescriptionMatch"] = "CONTAINS";
new TDynamicSearchHelper(AJournalTable.TableId,
AJournalTable.ColumnJournalDescriptionId, CriteriaRow, "JournalDescription", "JournalDescriptionMatch",
ref CustomWhereCriteria,
ref InternalParameters);
}
// Searched DB Field: 'a_narrative_c'
if (CriteriaRow["Narrative"].ToString().Length > 0)
{
CriteriaRow.Table.Columns.Add(new DataColumn("NarrativeMatch"));
CriteriaRow["NarrativeMatch"] = "CONTAINS";
new TDynamicSearchHelper(ATransactionTable.TableId,
ATransactionTable.ColumnNarrativeId, CriteriaRow, "Narrative", "NarrativeMatch",
ref CustomWhereCriteria,
ref InternalParameters);
}
// Searched DB Field: 'a_cost_centre_code_c'
if (CriteriaRow["CostCentreCode"].ToString().Length > 0)
{
new TDynamicSearchHelper(ATransactionTable.TableId,
ATransactionTable.ColumnCostCentreCodeId, CriteriaRow, "CostCentreCode", "",
ref CustomWhereCriteria,
ref InternalParameters);
}
// Searched DB Field: 'a_account_code_c'
if (CriteriaRow["AccountCode"].ToString().Length > 0)
{
new TDynamicSearchHelper(ATransactionTable.TableId,
ATransactionTable.ColumnAccountCodeId, CriteriaRow, "AccountCode", "",
ref CustomWhereCriteria,
ref InternalParameters);
}
// Searched DB Field: 'a_transaction_date_d'
if ((CriteriaRow["From"] != System.DBNull.Value) && (CriteriaRow["To"] != System.DBNull.Value) &&
(CriteriaRow["From"] == CriteriaRow["To"]))
{
CustomWhereCriteria = String.Format("{0} AND {1} = ?", CustomWhereCriteria,
ATransactionTable.GetTransactionDateDBName());
OdbcParameter miParam = new OdbcParameter("", OdbcType.DateTime, 10);
miParam.Value = (object)(CriteriaRow["From"]);
InternalParameters.Add(miParam);
}
else
{
if (CriteriaRow["From"] != System.DBNull.Value)
{
CustomWhereCriteria = String.Format("{0} AND {1} >= ?", CustomWhereCriteria,
ATransactionTable.GetTransactionDateDBName());
OdbcParameter miParam = new OdbcParameter("", OdbcType.DateTime, 10);
miParam.Value = (object)(CriteriaRow["From"]);
InternalParameters.Add(miParam);
}
if (CriteriaRow["To"] != System.DBNull.Value)
{
CustomWhereCriteria = String.Format("{0} AND {1} <= ?", CustomWhereCriteria,
ATransactionTable.GetTransactionDateDBName());
OdbcParameter miParam = new OdbcParameter("", OdbcType.DateTime, 10);
miParam.Value = (object)(CriteriaRow["To"]);
InternalParameters.Add(miParam);
}
}
// Searched DB Field: 'a_amount_in_base_currency_n'
if ((CriteriaRow["MinAmount"].ToString().Length > 0) && (CriteriaRow["MaxAmount"].ToString().Length > 0) &&
(CriteriaRow["MinAmount"] == CriteriaRow["MaxAmount"]))
{
CustomWhereCriteria = String.Format("{0} AND {1} = ?", CustomWhereCriteria,
ATransactionTable.GetAmountInBaseCurrencyDBName());
OdbcParameter miParam = new OdbcParameter("", OdbcType.Int, 10);
miParam.Value = (object)(CriteriaRow["MinAmount"]);
InternalParameters.Add(miParam);
}
else
{
if (CriteriaRow["MinAmount"].ToString().Length > 0)
{
CustomWhereCriteria = String.Format("{0} AND {1} >= ?", CustomWhereCriteria,
ATransactionTable.GetAmountInBaseCurrencyDBName());
OdbcParameter miParam = new OdbcParameter("", OdbcType.Int, 10);
miParam.Value = (object)(CriteriaRow["MinAmount"]);
InternalParameters.Add(miParam);
}
if (CriteriaRow["MaxAmount"].ToString().Length > 0)
{
CustomWhereCriteria = String.Format("{0} AND {1} <= ?", CustomWhereCriteria,
ATransactionTable.GetAmountInBaseCurrencyDBName());
OdbcParameter miParam = new OdbcParameter("", OdbcType.Int, 10);
miParam.Value = (object)(CriteriaRow["MaxAmount"]);
InternalParameters.Add(miParam);
}
}
// TLogging.LogAtLevel(7, "CustomWhereCriteria: " + CustomWhereCriteria);
/* Convert ArrayList to a array of ODBCParameters
* seem to need to declare a type first
*/
AParametersArray = ((OdbcParameter[])(InternalParameters.ToArray(typeof(OdbcParameter))));
InternalParameters = null; // ensure this is GC'd
return(CustomWhereCriteria);
}
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 static bool UpdateJournalTotals(ref GLBatchTDS AMainDS,
ref GLBatchTDSAJournalRow ACurrentJournal)
{
#region Validate Arguments
if (AMainDS == null)
{
throw new EFinanceSystemDataObjectNullOrEmptyException(String.Format(Catalog.GetString("Function:{0} - The GL Batch dataset is null!"),
Utilities.GetMethodName(true)));
}
else if (ACurrentJournal == null)
{
throw new EFinanceSystemDataObjectNullOrEmptyException(String.Format(Catalog.GetString(
"Function:{0} - The Journal row does not exist or is empty!"),
Utilities.GetMethodName(true)));
}
else if (ACurrentJournal.JournalStatus != MFinanceConstants.BATCH_UNPOSTED)
{
TLogging.Log(String.Format("Function:{0} - Tried to update totals for non-Unposted Batch:{1} and Journal:{2}",
Utilities.GetMethodName(true),
ACurrentJournal.BatchNumber,
ACurrentJournal.JournalNumber));
return(false);
}
#endregion Validate Arguments
bool AmountsUpdated = false;
decimal JournalDebitTotal = 0.0M;
decimal JournalDebitTotalBase = 0.0M;
decimal JournalCreditTotal = 0.0M;
decimal JournalCreditTotalBase = 0.0M;
if (ACurrentJournal.IsJournalDebitTotalBaseNull()) //DBNull.Value.Equals(ACurrentJournal[GLBatchTDSAJournalTable.ColumnJournalDebitTotalBaseId])
{
ACurrentJournal.JournalDebitTotalBase = 0;
AmountsUpdated = true;
}
if (ACurrentJournal.IsJournalCreditTotalBaseNull()) //DBNull.Value.Equals(ACurrentJournal[GLBatchTDSAJournalTable.ColumnJournalCreditTotalBaseId])
{
ACurrentJournal.JournalCreditTotalBase = 0;
AmountsUpdated = true;
}
DataView TransDV = new DataView(AMainDS.ATransaction);
TransDV.RowFilter = String.Format("{0}={1} And {2}={3}",
ATransactionTable.GetBatchNumberDBName(),
ACurrentJournal.BatchNumber,
ATransactionTable.GetJournalNumberDBName(),
ACurrentJournal.JournalNumber);
if ((TransDV.Count == 0) && (ACurrentJournal.LastTransactionNumber > 0))
{
//do not update totals as no transactions loaded as yet so no need to update journal total
return(AmountsUpdated);
}
// transactions are filtered for this journal; add up the total amounts
foreach (DataRowView drv in TransDV)
{
ATransactionRow transRow = (ATransactionRow)drv.Row;
if (transRow.DebitCreditIndicator)
{
JournalDebitTotal += transRow.TransactionAmount;
JournalDebitTotalBase += transRow.AmountInBaseCurrency;
}
else
{
JournalCreditTotal += transRow.TransactionAmount;
JournalCreditTotalBase += transRow.AmountInBaseCurrency;
}
}
if ((ACurrentJournal.JournalDebitTotal != JournalDebitTotal) ||
(ACurrentJournal.JournalDebitTotalBase != JournalDebitTotalBase) ||
(ACurrentJournal.JournalCreditTotal != JournalCreditTotal) ||
(ACurrentJournal.JournalCreditTotalBase != JournalCreditTotalBase))
{
ACurrentJournal.JournalDebitTotal = JournalDebitTotal;
ACurrentJournal.JournalDebitTotalBase = JournalDebitTotalBase;
ACurrentJournal.JournalCreditTotal = JournalCreditTotal;
ACurrentJournal.JournalCreditTotalBase = JournalCreditTotalBase;
AmountsUpdated = true;
}
return(AmountsUpdated);
}
/// <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);
StringBuilder sb = new StringBuilder();
TDBTransaction Transaction = DBAccess.GDBAccessObj.BeginTransaction(IsolationLevel.ReadCommitted);
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(",", "','") + "'");
ATransactionTable transactions = new ATransactionTable();
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());
ATransAnalAttribTable TransAnalAttrib = new ATransAnalAttribTable();
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");
ATransactionTable allTransactionsInJournal = new ATransactionTable();
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);
AGiftBatchTable giftbatches = new AGiftBatchTable();
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);
AAccountTable accounts = new AAccountTable();
DBAccess.GDBAccessObj.SelectDT(accounts, sql, Transaction, null, 0, 0);
accounts.DefaultView.Sort = AAccountTable.GetAccountCodeDBName();
DBAccess.GDBAccessObj.RollbackTransaction();
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();
}
/// <summary>
/// Export all the Data of the batches array list to a String
/// </summary>
/// <param name="Abatches"></param>
/// <param name="ArequestParams"></param>
/// <param name="AexportString"></param>
/// <returns>false if batch does not exist at all</returns>
public bool ExportAllGLBatchData(ArrayList Abatches, Hashtable ArequestParams, out String AexportString)
{
FStringWriter = new StringWriter();
FMainDS = new GLBatchTDS();
FDelimiter = (String)ArequestParams["Delimiter"];
FLedgerNumber = (Int32)ArequestParams["ALedgerNumber"];
FDateFormatString = (String)ArequestParams["DateFormatString"];
FSummary = (bool)ArequestParams["Summary"];
FUseBaseCurrency = (bool)ArequestParams["bUseBaseCurrency"];
FBaseCurrency = (String)ArequestParams["BaseCurrency"];
FDateForSummary = (DateTime)ArequestParams["DateForSummary"];
String NumberFormat = (String)ArequestParams["NumberFormat"];
FCultureInfo = new CultureInfo(NumberFormat.Equals("American") ? "en-US" : "de-DE");
FTransactionsOnly = (bool)ArequestParams["TransactionsOnly"];
FDontSummarize = (bool)ArequestParams["bDontSummarize"];
FDontSummarizeAccount = (String)ArequestParams["DontSummarizeAccount"];
SortedDictionary <String, AJournalSummaryRow> sdSummary = new SortedDictionary <String, AJournalSummaryRow>();
TDBTransaction Transaction = new TDBTransaction();
TDataBase db = DBAccess.Connect("ExportAllGLBatchData");
db.ReadTransaction(ref Transaction,
delegate
{
UInt32 progressCounter = 0;
UInt32 progressJournalCounter = 0;
TProgressTracker.InitProgressTracker(DomainManager.GClientID.ToString(),
Catalog.GetString("Exporting GL Batches"),
100);
TProgressTracker.SetCurrentState(DomainManager.GClientID.ToString(),
Catalog.GetString("Retrieving records"),
10);
while (Abatches.Count > 0)
{
Int32 ABatchNumber = (Int32)Abatches[0];
ABatchAccess.LoadByPrimaryKey(FMainDS, FLedgerNumber, ABatchNumber, Transaction);
AJournalAccess.LoadViaABatch(FMainDS, FLedgerNumber, ABatchNumber, Transaction);
foreach (AJournalRow journal in FMainDS.AJournal.Rows)
{
if (journal.BatchNumber.Equals(ABatchNumber) && journal.LedgerNumber.Equals(FLedgerNumber))
{
ATransactionAccess.LoadViaAJournal(FMainDS, journal.LedgerNumber,
journal.BatchNumber,
journal.JournalNumber,
Transaction);
}
}
foreach (ATransactionRow trans in FMainDS.ATransaction.Rows)
{
if (trans.BatchNumber.Equals(ABatchNumber) && trans.LedgerNumber.Equals(FLedgerNumber))
{
ATransAnalAttribAccess.LoadViaATransaction(FMainDS, trans.LedgerNumber,
trans.BatchNumber,
trans.JournalNumber,
trans.TransactionNumber,
Transaction);
}
}
Abatches.RemoveAt(0);
}
UInt32 counter = 0;
AJournalSummaryRow journalSummary = null;
foreach (ABatchRow batch in FMainDS.ABatch.Rows)
{
progressCounter = 0;
TProgressTracker.SetCurrentState(DomainManager.GClientID.ToString(),
String.Format(Catalog.GetString("Batch {0}"), batch.BatchNumber),
20);
if (!FTransactionsOnly & !FSummary)
{
WriteBatchLine(batch);
}
//foreach (AJournalRow journal in journalDS.AJournal.Rows)
foreach (AJournalRow journal in FMainDS.AJournal.Rows)
{
if (journal.BatchNumber.Equals(batch.BatchNumber) && journal.LedgerNumber.Equals(batch.LedgerNumber))
{
progressJournalCounter = 0;
TProgressTracker.SetCurrentState(DomainManager.GClientID.ToString(),
String.Format(Catalog.GetString("Batch {0}, Journal {1}"), batch.BatchNumber, journal.JournalNumber),
(progressCounter / 25 + 4) * 5 > 90 ? 90 : (progressCounter / 25 + 4) * 5);
if (FSummary)
{
String mapCurrency = FUseBaseCurrency ? FBaseCurrency : journal.TransactionCurrency;
decimal mapExchangeRateToBase = FUseBaseCurrency ? 1 : journal.ExchangeRateToBase;
if (!sdSummary.TryGetValue(mapCurrency, out journalSummary))
{
journalSummary = new AJournalSummaryRow();
sdSummary.Add(mapCurrency, journalSummary);
}
//overwrite always because we want to have the last
journalSummary.ExchangeRateToBase = mapExchangeRateToBase;
journalSummary.TransactionCurrency = mapCurrency;
}
else
{
if (!FTransactionsOnly)
{
WriteJournalLine(journal);
}
}
FMainDS.ATransaction.DefaultView.Sort = ATransactionTable.GetTransactionNumberDBName();
FMainDS.ATransaction.DefaultView.RowFilter =
String.Format("{0}={1} and {2}={3} and {4}={5}",
ATransactionTable.GetLedgerNumberDBName(),
journal.LedgerNumber,
ATransactionTable.GetBatchNumberDBName(),
journal.BatchNumber,
ATransactionTable.GetJournalNumberDBName(),
journal.JournalNumber);
foreach (DataRowView dv in FMainDS.ATransaction.DefaultView)
{
progressJournalCounter++;
if (++progressCounter % 25 == 0)
{
TProgressTracker.SetCurrentState(DomainManager.GClientID.ToString(),
String.Format(Catalog.GetString(
"Batch {0}, Journal {1} - {2}"), batch.BatchNumber, journal.JournalNumber,
progressJournalCounter),
(progressCounter / 25 + 4) * 5 > 90 ? 90 : (progressCounter / 25 + 4) * 5);
}
ATransactionRow transactionRow = (ATransactionRow)dv.Row;
if (FSummary)
{
ATransactionSummaryRow transactionSummary;
counter++;
String DictionaryKey = transactionRow.CostCentreCode + ";" + transactionRow.AccountCode;
int signum = transactionRow.DebitCreditIndicator ? 1 : -1;
bool bDontSummarizeAccount = FDontSummarize && FDontSummarizeAccount != null &&
FDontSummarizeAccount.Length > 0 &&
transactionRow.AccountCode.Equals(FDontSummarizeAccount);
if (bDontSummarizeAccount)
{
DictionaryKey += ";" + counter.ToString("X");
}
if (journalSummary.TransactionSummaries.TryGetValue(DictionaryKey, out transactionSummary))
{
transactionSummary.TransactionAmount += signum * transactionRow.TransactionAmount;
transactionSummary.AmountInBaseCurrency += signum * transactionRow.AmountInBaseCurrency;
}
else
{
transactionSummary = new ATransactionSummaryRow();
transactionSummary.CostCentreCode = transactionRow.CostCentreCode;
transactionSummary.AccountCode = transactionRow.AccountCode;
transactionSummary.TransactionAmount = signum * transactionRow.TransactionAmount;
transactionSummary.AmountInBaseCurrency = signum * transactionRow.AmountInBaseCurrency;
if (bDontSummarizeAccount)
{
transactionSummary.Narrative = transactionRow.Narrative;
transactionSummary.Reference = transactionRow.Reference;
}
else
{
transactionSummary.Narrative = summarizedData;
transactionSummary.Reference = "";
}
journalSummary.TransactionSummaries.Add(DictionaryKey, transactionSummary);
}
}
else
{
WriteTransactionLine(transactionRow);
}
}
}
}
}
if (FSummary)
{
TProgressTracker.SetCurrentState(DomainManager.GClientID.ToString(),
Catalog.GetString("Summarising"),
95);
//To simplify matters this is always written even if there are no batches
if (!FTransactionsOnly)
{
// no batch summary line if only transactions are to be exported
WriteBatchSummaryLine();
}
foreach (KeyValuePair <string, AJournalSummaryRow> kvp in sdSummary)
{
if (!FTransactionsOnly)
{
// no journal summary line if only transactions are to be exported
WriteJournalSummaryLine(kvp.Value);
}
foreach (KeyValuePair <string, ATransactionSummaryRow> kvpt in kvp.Value.TransactionSummaries)
{
WriteTransactionSummaryLine(kvpt.Value);
}
}
}
TProgressTracker.SetCurrentState(DomainManager.GClientID.ToString(),
Catalog.GetString("GL batch export successful"),
100);
TProgressTracker.FinishJob(DomainManager.GClientID.ToString());
});
AexportString = FStringWriter.ToString();
return(true);
} // Export All GLBatch Data
/// <summary>
/// Method to cancel a specified journal
/// </summary>
/// <param name="ACurrentJournalRow">The row to cancel</param>
/// <param name="AJournalDescriptionTextBox">Pass a reference to a TextBox that is used to display the journal description text. This is required
/// to ensure that validation passes when the cancelled journal is saved.</param>
/// <param name="AExchangeRateToBaseTextBox">Pass a reference to a TextBox that is used to display the exchange rate to base. This is required
/// to ensure that validation passes when the cancelled journal is saved.</param>
/// <returns>True if the journal is cancelled.</returns>
public bool CancelRow(GLBatchTDSAJournalRow ACurrentJournalRow,
TextBox AJournalDescriptionTextBox,
TTxtNumericTextBox AExchangeRateToBaseTextBox)
{
if ((ACurrentJournalRow == null) || !FMyForm.SaveChanges())
{
return(false);
}
int CurrentBatchNumber = ACurrentJournalRow.BatchNumber;
int CurrentJournalNumber = ACurrentJournalRow.JournalNumber;
if ((MessageBox.Show(String.Format(Catalog.GetString(
"You have chosen to cancel this journal ({0}).\n\nDo you really want to cancel it?"),
CurrentJournalNumber),
Catalog.GetString("Confirm Cancel"),
MessageBoxButtons.YesNo) == System.Windows.Forms.DialogResult.Yes))
{
try
{
//clear any transactions currently being editied in the Transaction Tab
FMyForm.GetTransactionsControl().ClearCurrentSelection();
//Load any new data
FMainDS.Merge(TRemote.MFinance.GL.WebConnectors.LoadATransactionATransAnalAttrib(FLedgerNumber, CurrentBatchNumber,
CurrentJournalNumber));
DataView dvAA = new DataView(FMainDS.ATransAnalAttrib);
dvAA.RowFilter = String.Format("{0}={1} AND {2}={3}",
ATransAnalAttribTable.GetBatchNumberDBName(),
CurrentBatchNumber,
ATransAnalAttribTable.GetJournalNumberDBName(),
CurrentJournalNumber);
//Delete Analysis Attribs
foreach (DataRowView dvr in dvAA)
{
dvr.Delete();
}
DataView dvTr = new DataView(FMainDS.ATransaction);
dvTr.RowFilter = String.Format("{0}={1} AND {2}={3}",
ATransactionTable.GetBatchNumberDBName(),
CurrentBatchNumber,
ATransactionTable.GetJournalNumberDBName(),
CurrentJournalNumber);
//Delete Transactions
foreach (DataRowView dvr in dvTr)
{
dvr.Delete();
}
ACurrentJournalRow.BeginEdit();
ACurrentJournalRow.JournalStatus = MFinanceConstants.BATCH_CANCELLED;
//Ensure validation passes
if (ACurrentJournalRow.JournalDescription.Length == 0)
{
AJournalDescriptionTextBox.Text = " ";
}
if (ACurrentJournalRow.ExchangeRateToBase == 0)
{
AExchangeRateToBaseTextBox.NumberValueDecimal = 1;
}
ABatchRow CurrentBatchRow = FMyForm.GetBatchControl().GetSelectedDetailRow();
CurrentBatchRow.BatchCreditTotal -= ACurrentJournalRow.JournalCreditTotal;
CurrentBatchRow.BatchDebitTotal -= ACurrentJournalRow.JournalDebitTotal;
if (CurrentBatchRow.BatchControlTotal != 0)
{
CurrentBatchRow.BatchControlTotal -= ACurrentJournalRow.JournalCreditTotal;
}
ACurrentJournalRow.JournalCreditTotal = 0;
ACurrentJournalRow.JournalDebitTotal = 0;
ACurrentJournalRow.EndEdit();
FPetraUtilsObject.SetChangedFlag();
//Need to call save
if (FMyForm.SaveChanges())
{
MessageBox.Show(Catalog.GetString("The journal has been cancelled successfully!"),
Catalog.GetString("Success"),
MessageBoxButtons.OK, MessageBoxIcon.Information);
FMyForm.DisableTransactions();
}
else
{
// saving failed, therefore do not try to post
MessageBox.Show(Catalog.GetString(
"The journal has been cancelled but there were problems during saving; ") + Environment.NewLine +
Catalog.GetString("Please try and save the cancellation immediately."),
Catalog.GetString("Failure"), MessageBoxButtons.OK, MessageBoxIcon.Error);
}
return(true);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
return(false);
}
/// <summary>
/// Checks the specified stewardship batch to see whether the summary transactions
/// match the amounts specified.
/// </summary>
/// <param name="AICHLedgerNumber">ICH Ledger number</param>
/// <param name="ABatchNumber">The batch number of the batch to check</param>
/// <param name="ACostCentre">Fund to which stewardship batch applies</param>
/// <param name="AIncomeAmount">Income total to check against</param>
/// <param name="AExpenseAmount">Expense total to check against</param>
/// <param name="ATransferAmount">Transfer total to check against</param>
/// <param name="ADBTransaction">Current database transaction</param>
/// <returns>True if transasctions match, false otherwise</returns>
public bool SummaryStewardshipTransactionsMatch(int AICHLedgerNumber,
int ABatchNumber,
string ACostCentre,
decimal AIncomeAmount,
decimal AExpenseAmount,
decimal ATransferAmount,
ref TDBTransaction ADBTransaction)
{
decimal ExistingIncExpTotal;
ABatchTable BatchTable = ABatchAccess.LoadByPrimaryKey(AICHLedgerNumber, ABatchNumber, ADBTransaction);
ABatchRow BatchRow = (ABatchRow)BatchTable.Rows[0];
if (BatchRow != null)
{
/* look for summary transaction for transfers */
ATransactionTable TransTable = new ATransactionTable();
ATransactionRow TemplateRow = (ATransactionRow)TransTable.NewRowTyped(false);
TemplateRow.LedgerNumber = AICHLedgerNumber;
TemplateRow.BatchNumber = ABatchNumber;
TemplateRow.CostCentreCode = ACostCentre;
TemplateRow.AccountCode = MFinanceConstants.ICH_ACCT_DEPOSITS_WITH_ICH; //i.e. 8540
StringCollection operators = StringHelper.InitStrArr(new string[] { "=", "=", "=", "=" });
ATransactionTable TransactionTable = ATransactionAccess.LoadUsingTemplate(TemplateRow, operators, null, ADBTransaction);
ATransactionRow TransactionRow = (ATransactionRow)TransactionTable.Rows[0];
if (TransactionRow != null)
{
if (TransactionRow.TransactionAmount != ATransferAmount)
{
DBAccess.GDBAccessObj.RollbackTransaction();
return false;
}
}
/* find the summary transactions for income and expense and sum them */
ExistingIncExpTotal = 0;
ATransactionTable TransTable2 = new ATransactionTable();
ATransactionRow TemplateRow2 = (ATransactionRow)TransTable2.NewRowTyped(false);
TemplateRow2.LedgerNumber = AICHLedgerNumber;
TemplateRow2.BatchNumber = ABatchNumber;
TemplateRow2.CostCentreCode = ACostCentre;
TemplateRow2.AccountCode = MFinanceConstants.ICH_ACCT_LOCAL_LEDGER; //i.e. 8520
StringCollection operators2 = StringHelper.InitStrArr(new string[] { "=", "=", "=", "=" });
ATransactionTable TransactionTable2 = ATransactionAccess.LoadUsingTemplate(TemplateRow2, operators2, null, ADBTransaction);
ATransactionRow TransactionRow2 = null;
for (int i = 0; i < TransactionTable2.Count; i++)
{
TransactionRow2 = (ATransactionRow)TransactionTable2.Rows[i];
ExistingIncExpTotal += TransactionRow2.TransactionAmount;
}
DBAccess.GDBAccessObj.RollbackTransaction();
return ExistingIncExpTotal == (AIncomeAmount + AExpenseAmount);
}
#if TODO
/* now check previous periods if batch wasn't in current period */
AThisYearOldBatchTable ThisYearOldBatchTable = AThisYearOldBatchAccess.LoadByPrimaryKey(AICHLedgerNumber, ABatchNumber, ADBTransaction);
AThisYearOldBatchRow ThisYearOldBatchRow = (AThisYearOldBatchRow)ThisYearOldBatchTable.Rows[0];
if (ThisYearOldBatchRow != null)
{
/* look for summary transaction for transfers */
AThisYearOldTransactionTable ThisYearOldTransTable = new AThisYearOldTransactionTable();
AThisYearOldTransactionRow TemplateRow3 = (AThisYearOldTransactionRow)ThisYearOldTransTable.NewRowTyped(false);
TemplateRow3.LedgerNumber = AICHLedgerNumber;
TemplateRow3.BatchNumber = ABatchNumber;
TemplateRow3.CostCentreCode = ACostCentre;
TemplateRow3.AccountCode = MFinanceConstants.ICH_ACCT_DEPOSITS_WITH_ICH; //i.e. 8540
StringCollection operators3 = StringHelper.InitStrArr(new string[] { "=", "=", "=", "=" });
AThisYearOldTransactionTable ThisYearOldTransactionTable = AThisYearOldTransactionAccess.LoadUsingTemplate(TemplateRow3,
operators3,
null,
ADBTransaction);
AThisYearOldTransactionRow ThisYearOldTransactionRow = (AThisYearOldTransactionRow)ThisYearOldTransactionTable.Rows[0];
if (ThisYearOldTransactionRow != null)
{
if (ThisYearOldTransactionRow.TransactionAmount != ATransferAmount)
{
DBAccess.GDBAccessObj.RollbackTransaction();
return false;
}
}
/* find the summary transactions for income and expense and sum them */
ExistingIncExpTotal = 0;
AThisYearOldTransactionTable ThisYearOldTransTable4 = new AThisYearOldTransactionTable();
AThisYearOldTransactionRow TemplateRow4 = (AThisYearOldTransactionRow)ThisYearOldTransTable4.NewRowTyped(false);
TemplateRow4.LedgerNumber = AICHLedgerNumber;
TemplateRow4.BatchNumber = ABatchNumber;
TemplateRow4.CostCentreCode = ACostCentre;
TemplateRow4.AccountCode = MFinanceConstants.ICH_ACCT_LOCAL_LEDGER; //i.e. 8520
StringCollection operators4 = StringHelper.InitStrArr(new string[] { "=", "=", "=", "=" });
AThisYearOldTransactionTable ThisYearOldTransactionTable2 = AThisYearOldTransactionAccess.LoadUsingTemplate(TemplateRow4,
operators4,
null,
ADBTransaction);
AThisYearOldTransactionRow ThisYearOldTransactionRow2 = null;
for (int i = 0; i < ThisYearOldTransactionTable2.Count; i++)
{
ThisYearOldTransactionRow2 = (AThisYearOldTransactionRow)ThisYearOldTransactionTable2.Rows[i];
ExistingIncExpTotal += ThisYearOldTransactionRow2.TransactionAmount;
}
DBAccess.GDBAccessObj.RollbackTransaction();
return ExistingIncExpTotal == (AIncomeAmount + AExpenseAmount);
}
/* now check previous years if batch wasn't in current year */
APreviousYearBatchTable PreviousYearBatchTable = APreviousYearBatchAccess.LoadByPrimaryKey(AICHLedgerNumber, ABatchNumber, ADBTransaction);
APreviousYearBatchRow PreviousYearBatchRow = (APreviousYearBatchRow)PreviousYearBatchTable.Rows[0];
if (PreviousYearBatchRow != null)
{
/* look for summary transaction for transfers */
APreviousYearTransactionTable PreviousYearTransTable = new APreviousYearTransactionTable();
APreviousYearTransactionRow TemplateRow5 = (APreviousYearTransactionRow)PreviousYearTransTable.NewRowTyped(false);
TemplateRow5.LedgerNumber = AICHLedgerNumber;
TemplateRow5.BatchNumber = ABatchNumber;
TemplateRow5.CostCentreCode = ACostCentre;
TemplateRow5.AccountCode = MFinanceConstants.ICH_ACCT_DEPOSITS_WITH_ICH; //i.e. 8540
StringCollection operators5 = StringHelper.InitStrArr(new string[] { "=", "=", "=", "=" });
APreviousYearTransactionTable PreviousYearTransactionTable = APreviousYearTransactionAccess.LoadUsingTemplate(TemplateRow5,
operators5,
null,
ADBTransaction);
APreviousYearTransactionRow PreviousYearTransactionRow = (APreviousYearTransactionRow)PreviousYearTransactionTable.Rows[0];
if (PreviousYearTransactionRow != null)
{
if (PreviousYearTransactionRow.TransactionAmount != ATransferAmount)
{
DBAccess.GDBAccessObj.RollbackTransaction();
return false;
}
}
/* find the summary transactions for income and expense and sum them */
ExistingIncExpTotal = 0;
APreviousYearTransactionTable PreviousYearTransTable4 = new APreviousYearTransactionTable();
APreviousYearTransactionRow TemplateRow6 = (APreviousYearTransactionRow)PreviousYearTransTable4.NewRowTyped(false);
TemplateRow6.LedgerNumber = AICHLedgerNumber;
TemplateRow6.BatchNumber = ABatchNumber;
TemplateRow6.CostCentreCode = ACostCentre;
TemplateRow6.AccountCode = MFinanceConstants.ICH_ACCT_LOCAL_LEDGER; //i.e. 8520
StringCollection operators6 = StringHelper.InitStrArr(new string[] { "=", "=", "=", "=" });
APreviousYearTransactionTable PreviousYearTransactionTable2 = APreviousYearTransactionAccess.LoadUsingTemplate(TemplateRow6,
operators6,
null,
ADBTransaction);
APreviousYearTransactionRow PreviousYearTransactionRow2 = null;
for (int i = 0; i < PreviousYearTransactionTable2.Count; i++)
{
PreviousYearTransactionRow2 = (APreviousYearTransactionRow)PreviousYearTransactionTable2.Rows[i];
ExistingIncExpTotal += PreviousYearTransactionRow2.TransactionAmount;
}
DBAccess.GDBAccessObj.RollbackTransaction();
return ExistingIncExpTotal == (AIncomeAmount + AExpenseAmount);
}
#endif
DBAccess.GDBAccessObj.RollbackTransaction();
return false;
}
/// <summary>
/// Searches all transaction tables to see if there is a stewardship
/// batch that already exists which appears to match the one
/// currently being processed.
/// </summary>
/// <param name="AICHLedgerNumber">ICH Ledger number</param>
/// <param name="ABatchRef">Reference of current stewardship batch</param>
/// <param name="AYear">Year to which current stewardship batch applies</param>
/// <param name="AMonth">Month to which current stewardship batch applies</param>
/// <param name="ACurrentPeriod">Current period of ICH ledger</param>
/// <param name="ACurrentYear">Current year of ICH ledger</param>
/// <param name="ADBTransaction">Current database transaction</param>
/// <returns>The batch number of the matching batch or 0 if there is no match.</returns>
public int FindMatchingStewardshipBatch(int AICHLedgerNumber,
string ABatchRef,
int AYear,
int AMonth,
int ACurrentPeriod,
int ACurrentYear,
ref TDBTransaction ADBTransaction
)
{
int BatchNumber = 0;
bool MatchFound = false;
/*cOldStyleDescription = REPLACE(REPLACE(pcBatchDescription, STRING(piYear) + " ", ""), " (Run " + STRING(piRunNumber) + ")", "").*/
/* Old reference format (prior to this program being available) was xxyy where xx was the fund number
* and yy was the period number. Therefore we need to strip off the final 3 characters to convert
* from the new style reference to the old style reference (those 3 characters are the run number).
* We need to do this because some of the batches we will search through will be in old format. */
string OldStyleReference = ABatchRef.Substring(0, ABatchRef.Length - 3);
int OldStyleReferenceLen = OldStyleReference.Length;
/* Note: In the queries below we need to check the length of the reference as well because of the
* 3 digit fund numbers (eg. Central America Period 12 would have reference 2012 while NAA Period 2 would be
* 20112) */
/* look in current period */
ATransactionTable TransTable = new ATransactionTable();
ATransactionRow TemplateRow = (ATransactionRow)TransTable.NewRowTyped(false);
TemplateRow.LedgerNumber = AICHLedgerNumber;
TemplateRow.TransactionStatus = true;
StringCollection operators = StringHelper.InitStrArr(new string[] { "=", "=" });
ATransactionTable TransactionTable = ATransactionAccess.LoadUsingTemplate(TemplateRow, operators, null, ADBTransaction);
ATransactionRow TransactionRow = null;
string TransRef;
int TransRefLen;
for (int i = 0; i < TransactionTable.Count; i++)
{
TransactionRow = (ATransactionRow)TransactionTable.Rows[i];
TransRef = TransactionRow.Reference;
TransRefLen = TransRef.Length;
if ((TransRef.Substring(0, OldStyleReferenceLen) == OldStyleReference)
&& ((TransRefLen == OldStyleReferenceLen) || (TransRefLen == (OldStyleReferenceLen + 3))))
{
BatchNumber = TransactionRow.BatchNumber;
MatchFound = true;
break;
}
}
if (!MatchFound)
{
#if TODO
/* look in previous periods (need to compare date of transactions to the month of the stewardship
* because there may be a batch present which was for last years stewardship - eg. Dec 2007
* Stewardship for US may have been processed in Jan 2008) */
AThisYearOldTransactionTable YearOldTransTable = new AThisYearOldTransactionTable();
AThisYearOldTransactionRow TemplateRow2 = (AThisYearOldTransactionRow)YearOldTransTable.NewRowTyped(false);
TemplateRow2.LedgerNumber = AICHLedgerNumber;
TemplateRow2.TransactionStatus = true;
StringCollection operators2 = StringHelper.InitStrArr(new string[] { "=", "=" });
AThisYearOldTransactionTable ThisYearOldTransactionTable = AThisYearOldTransactionAccess.LoadUsingTemplate(TemplateRow2,
operators2,
null,
ADBTransaction);
AThisYearOldTransactionRow ThisYearOldTransactionRow = null;
string OldTransRef;
int OldTransRefLen;
for (int i = 0; i < ThisYearOldTransactionTable.Count; i++)
{
ThisYearOldTransactionRow = (AThisYearOldTransactionRow)ThisYearOldTransactionTable.Rows[i];
OldTransRef = ThisYearOldTransactionRow.Reference;
OldTransRefLen = OldTransRef.Length;
if ((OldTransRef.Substring(0, OldStyleReferenceLen) == OldStyleReference)
&& ((OldTransRefLen == OldStyleReferenceLen) || (OldTransRefLen == (OldStyleReferenceLen + 3)))
&& ((AMonth <= ThisYearOldTransactionRow.TransactionDate.Month) || (AMonth > ACurrentPeriod)))
{
BatchNumber = ThisYearOldTransactionRow.BatchNumber;
MatchFound = true;
break;
}
}
/* look in previous years (need to make sure that you only match batches that are stewardships
* for the same year as the one being processed). */
if (!MatchFound && (AYear < ACurrentYear))
{
APreviousYearTransactionTable YearPreviousTransTable = new APreviousYearTransactionTable();
APreviousYearTransactionRow TemplateRow3 = (APreviousYearTransactionRow)YearPreviousTransTable.NewRowTyped(false);
TemplateRow3.LedgerNumber = AICHLedgerNumber;
TemplateRow3.TransactionStatus = true;
StringCollection operators3 = StringHelper.InitStrArr(new string[] { "=", "=" });
APreviousYearTransactionTable PreviousYearTransactionTable = APreviousYearTransactionAccess.LoadUsingTemplate(TemplateRow3,
operators3,
null,
ADBTransaction);
APreviousYearTransactionRow PreviousYearTransactionRow = null;
string PreviousTransRef;
int PreviousTransRefLen;
for (int i = 0; i < PreviousYearTransactionTable.Count; i++)
{
PreviousYearTransactionRow = (APreviousYearTransactionRow)PreviousYearTransactionTable.Rows[i];
PreviousTransRef = PreviousYearTransactionRow.Reference;
PreviousTransRefLen = PreviousTransRef.Length;
if ((PreviousTransRef.Substring(0, OldStyleReferenceLen) == OldStyleReference)
&& ((PreviousTransRefLen == OldStyleReferenceLen) || (PreviousTransRefLen == (OldStyleReferenceLen + 3)))
&& (PreviousYearTransactionRow.TransactionDate.Month >= AMonth)
&& (AMonth > ACurrentPeriod)
&& (PreviousYearTransactionRow.TransactionDate.Year == AYear))
{
BatchNumber = PreviousYearTransactionRow.BatchNumber;
break;
}
}
}
#endif
}
return BatchNumber;
}
} // Generate StewardshipBatch From ReportFile
/// <summary>
/// Looks at the specified batch and works out whether it is a duplicate
/// of the stewardship currently being processed (defined by the other
/// parameters). There are a number of possibilities from definite duplicate,
/// through possible duplicate to definitely not a duplicate, depending on
/// comparison of run numbers and comparison of amount totals. The
/// procedure works out which it is and writes to the log file appropriately.
/// </summary>
/// <param name="ALedgerNumber">ICH Ledger number</param>
/// <param name="ABatchNumber">The number of the batch to check</param>
/// <param name="ARunNumber">The run number of the stewardship being processed</param>
/// <param name="AYear">The year of the stewardship being processed</param>
/// <param name="AFileName">The name of the stewardship file being processed</param>
/// <param name="AFromCostCentre">Fund number to which the stewardship applies</param>
/// <param name="AFromCostCentreName">Name of the fund</param>
/// <param name="APeriodName">Name of the period to which the stewardship applies (ie. Month)</param>
/// <param name="ATotalIncome">Total amount of income transactions in stewardship being processed</param>
/// <param name="ATotalExpense">Total amount of expense transactions in stewardship being processed</param>
/// <param name="ATotalTransfer">Total amount of transfer transactions in stewardship being processed</param>
/// <param name="ALogWriter">TextWriter for log file</param>
/// <param name="ADBTransaction">Current database transaction</param>
/// <param name="AProcessFile">Set to True if processing of the stewardship should go ahead or False if it should be rejected (ie. if a duplicate or possible duplicate</param>
private void DealWithMatchingStewardshipBatch(int ALedgerNumber, int ABatchNumber, int ARunNumber,
int AYear, string AFileName, string AFromCostCentre,
string AFromCostCentreName, string APeriodName, decimal ATotalIncome,
decimal ATotalExpense, decimal ATotalTransfer, ref TextWriter ALogWriter, ref TDBTransaction ADBTransaction, out bool AProcessFile)
{
string LogMessage = string.Empty;
AProcessFile = false;
int BatchRunNumber = 0;
/*
* Possible scenarios:
*
* matching batch exists, both are run 0 - full duplicate
* matching batch exists, original is run 0 new one is run xxx - possible duplicate if totals identical (return flag to indicate that totals
* need to be compared)
* matching batch exists, original is run xxx new one is run 0 - not valid
* matching batch exists, original is run xxx new one is run xxx - full duplicate
* matching batch exists, original is run xxx new one is run yyy - not duplicate
* matching batch exists, original is run ? new one is run 0 - full duplicate
* matching batch exists, original is run ? new one is run xxx - possible duplicate if totals identical (return flag to indicate that totals
*/
/* check if batch is in current period and do the appropriate comparisons if it is (based around
* the scenarios above) */
ABatchTable BatchTable = ABatchAccess.LoadByPrimaryKey(ALedgerNumber, ABatchNumber, ADBTransaction);
ABatchRow BatchRow = (ABatchRow)BatchTable.Rows[0];
if (BatchRow != null)
{
if (ARunNumber == 0)
{
if (SummaryStewardshipTransactionsMatch(ALedgerNumber, ABatchNumber, AFromCostCentre, ATotalIncome, ATotalExpense, ATotalTransfer,
ref ADBTransaction))
{
LogMessage =
String.Format(Catalog.GetString(
"Stewardship Batch for {0} {1} {2} already exists (Batch {3}) and data is identical. {4} will be skipped."),
AFromCostCentreName, APeriodName, AYear, ABatchNumber, AFileName);
ALogWriter.WriteLine(LogMessage);
AProcessFile = false;
}
else
{
LogMessage =
String.Format(Catalog.GetString(
"Stewardship Batch for {0} {1} {2} already exists (Batch {3}) and data has changed. {4} will be skipped."),
AFromCostCentreName, APeriodName, AYear, ABatchNumber, AFileName);
ALogWriter.WriteLine(LogMessage);
AProcessFile = false;
}
}
else
{
ATransactionTable TransTable = new ATransactionTable();
ATransactionRow TemplateRow = (ATransactionRow)TransTable.NewRowTyped(false);
TemplateRow.LedgerNumber = ALedgerNumber;
TemplateRow.BatchNumber = ABatchNumber;
StringCollection operators = StringHelper.InitStrArr(new string[] { "=", "=" });
ATransactionTable TransactionTable = ATransactionAccess.LoadUsingTemplate(TemplateRow, operators, null, ADBTransaction);
ATransactionRow TransactionRow = (ATransactionRow)TransactionTable.Rows[0];
if (TransactionTable.Count > 0)
{
string TransRowRef = TransactionRow.Reference;
BatchRunNumber = Convert.ToInt32(TransRowRef.Substring(TransRowRef.Length - 3, 3));
if (ARunNumber == BatchRunNumber)
{
if (SummaryStewardshipTransactionsMatch(ALedgerNumber, ABatchNumber, AFromCostCentre, ATotalIncome, ATotalExpense,
ATotalTransfer, ref ADBTransaction))
{
LogMessage =
String.Format(Catalog.GetString(
"Stewardship Batch for {0} {1} {2} already exists (Batch {3}) and data is identical. {4} will be skipped."),
AFromCostCentreName, APeriodName, AYear, ABatchNumber, AFileName);
ALogWriter.WriteLine(LogMessage);
AProcessFile = false;
}
else
{
LogMessage =
String.Format(Catalog.GetString(
"Stewardship Batch for {0} {1} {2} already exists (Batch {3}) and data has changed. {4} will be skipped."),
AFromCostCentreName, APeriodName, AYear, ABatchNumber, AFileName);
ALogWriter.WriteLine(LogMessage);
AProcessFile = false;
}
return;
}
else if (BatchRunNumber == 0)
{
if (SummaryStewardshipTransactionsMatch(ALedgerNumber, ABatchNumber, AFromCostCentre, ATotalIncome, ATotalExpense,
ATotalTransfer, ref ADBTransaction))
{
LogMessage =
String.Format(Catalog.GetString(
"Stewardship Batch for {0} {1} {2} already exists (Batch {3}, date {4}) and is run number 0. File {5} is for run number {6} but the data is identical to the existing batch so it will not be processed."),
AFromCostCentreName, APeriodName, AYear, ABatchNumber,
TransactionRow.TransactionDate.ToShortDateString(), AFileName, ARunNumber);
ALogWriter.WriteLine(LogMessage);
AProcessFile = false;
}
else
{
AProcessFile = true;
}
}
else
{
AProcessFile = true;
}
}
}
return;
}
#if TODO
/* if the batch wasn't in the current period try previous periods. In this case we can't assume
* that the transaction reference will be in the "new style" (ie. LLLPPRRR). It might be in the
* old style (ie. LLLPP). We are able to do more checks where it is in the new style as we can
* get hold of the run number */
AThisYearOldTransactionTable OldTransTable = new AThisYearOldTransactionTable();
AThisYearOldTransactionRow TemplateRow2 = (AThisYearOldTransactionRow)OldTransTable.NewRowTyped(false);
TemplateRow2.LedgerNumber = ALedgerNumber;
TemplateRow2.BatchNumber = ABatchNumber;
StringCollection operators2 = StringHelper.InitStrArr(new string[] { "=", "=" });
AThisYearOldTransactionTable OldTransactionTable = AThisYearOldTransactionAccess.LoadUsingTemplate(TemplateRow2,
operators2,
null,
ADBTransaction);
AThisYearOldTransactionRow OldTransactionRow = (AThisYearOldTransactionRow)OldTransactionTable.Rows[0];
if (OldTransactionTable.Count > 0)
{
string OldTransRowRef = OldTransactionRow.Reference;
//BatchRunNumber = Convert.ToInt32(TransRowRef.Substring(TransRowRef.Length - 3, 3));
if (OldTransRowRef.Length < 7)
{
if (ARunNumber == 0)
{
if (SummaryStewardshipTransactionsMatch(ALedgerNumber, ABatchNumber, AFromCostCentre, ATotalIncome, ATotalExpense,
ATotalTransfer, ref ADBTransaction))
{
LogMessage =
String.Format(Catalog.GetString(
"Stewardship Batch for {0} {1} {2} already exists (Batch {3}, date {4}) and data is identical. {5} will be skipped."),
AFromCostCentreName, APeriodName, AYear, ABatchNumber,
OldTransactionRow.TransactionDate.ToShortDateString(), AFileName);
ALogWriter.WriteLine(LogMessage);
AProcessFile = false;
}
else
{
LogMessage =
String.Format(Catalog.GetString(
"Stewardship Batch for {0} {1} {2} already exists (Batch {3}, date {4}) and data has changed. {5} will be skipped."),
AFromCostCentreName, APeriodName, AYear, ABatchNumber,
OldTransactionRow.TransactionDate.ToShortDateString(), AFileName);
ALogWriter.WriteLine(LogMessage);
AProcessFile = false;
}
}
else
{
if (SummaryStewardshipTransactionsMatch(ALedgerNumber, ABatchNumber, AFromCostCentre, ATotalIncome, ATotalExpense,
ATotalTransfer, ref ADBTransaction))
{
LogMessage =
String.Format(Catalog.GetString(
"Stewardship Batch for {0} {1} {2} already exists (Batch {3}, date {4}) but the run number is unknown. File {5} is for run number {6} but the data is identical to the existing batch so it will not be processed."),
AFromCostCentreName, APeriodName, AYear, ABatchNumber,
OldTransactionRow.TransactionDate.ToShortDateString(), AFileName, ARunNumber);
ALogWriter.WriteLine(LogMessage);
AProcessFile = false;
}
else
{
AProcessFile = true;
}
}
}
else
{
BatchRunNumber = Convert.ToInt32(OldTransRowRef.Substring(OldTransRowRef.Length - 3, 3));
if (ARunNumber == BatchRunNumber)
{
if (SummaryStewardshipTransactionsMatch(ALedgerNumber, ABatchNumber, AFromCostCentre, ATotalIncome, ATotalExpense,
ATotalTransfer, ref ADBTransaction))
{
LogMessage =
String.Format(Catalog.GetString(
"Stewardship Batch for {0} {1} {2} already exists (Batch {3}, date {4}) and data is identical. {5} will be skipped."),
AFromCostCentreName, APeriodName, AYear, ABatchNumber,
OldTransactionRow.TransactionDate.ToShortDateString(), AFileName);
ALogWriter.WriteLine(LogMessage);
AProcessFile = false;
}
else
{
LogMessage =
String.Format(Catalog.GetString(
"Stewardship Batch for {0} {1} {2} already exists (Batch {3}, date {4}) and data has changed. {5} will be skipped."),
AFromCostCentreName, APeriodName, AYear, ABatchNumber,
OldTransactionRow.TransactionDate.ToShortDateString(), AFileName);
ALogWriter.WriteLine(LogMessage);
AProcessFile = false;
}
}
else if (ARunNumber < BatchRunNumber)
{
LogMessage =
String.Format(Catalog.GetString(
"Stewardship Batch for {0} {1} {2} already exists (Batch {3}, date {4}). The run number on that batch ({5}) is higher than the run number on the file being processed and therefore {6} will be skipped."),
AFromCostCentreName, APeriodName, AYear, ABatchNumber,
OldTransactionRow.TransactionDate.ToShortDateString(), BatchRunNumber, AFileName);
ALogWriter.WriteLine(LogMessage);
AProcessFile = false;
}
else if (BatchRunNumber == 0)
{
if (SummaryStewardshipTransactionsMatch(ALedgerNumber, ABatchNumber, AFromCostCentre, ATotalIncome, ATotalExpense,
ATotalTransfer, ref ADBTransaction))
{
LogMessage =
String.Format(Catalog.GetString(
"Stewardship Batch for {0} {1} {2} already exists (Batch {3}, date {4}) and is run number 0. File {5} is for run number {6} but the data is identical to the existing batch so it will not be processed."),
AFromCostCentreName, APeriodName, AYear, ABatchNumber,
OldTransactionRow.TransactionDate.ToShortDateString(), AFileName, ARunNumber);
ALogWriter.WriteLine(LogMessage);
AProcessFile = false;
}
else
{
AProcessFile = true;
}
}
else
{
AProcessFile = true;
}
}
return;
}
/* if we still haven't found the batch try previous years */
APreviousYearTransactionTable PreviousTransTable = new APreviousYearTransactionTable();
APreviousYearTransactionRow TemplateRow3 = (APreviousYearTransactionRow)PreviousTransTable.NewRowTyped(false);
TemplateRow3.LedgerNumber = ALedgerNumber;
TemplateRow3.BatchNumber = ABatchNumber;
StringCollection operators3 = StringHelper.InitStrArr(new string[] { "=", "=" });
APreviousYearTransactionTable PreviousTransactionTable = APreviousYearTransactionAccess.LoadUsingTemplate(TemplateRow3,
operators3,
null,
ADBTransaction);
APreviousYearTransactionRow PreviousTransactionRow = (APreviousYearTransactionRow)PreviousTransactionTable.Rows[0];
if (PreviousTransactionTable.Count > 0)
{
string PreviousTransRowRef = PreviousTransactionRow.Reference;
//BatchRunNumber = Convert.ToInt32(TransRowRef.Substring(TransRowRef.Length - 3, 3));
if (PreviousTransRowRef.Length < 7)
{
if (ARunNumber == 0)
{
if (SummaryStewardshipTransactionsMatch(ALedgerNumber, ABatchNumber, AFromCostCentre, ATotalIncome, ATotalExpense,
ATotalTransfer, ref ADBTransaction))
{
LogMessage =
String.Format(Catalog.GetString(
"Stewardship Batch for {0} {1} {2} already exists (Batch {3}, date {4}) and data is identical. {5} will be skipped."),
AFromCostCentreName, APeriodName, AYear, ABatchNumber,
PreviousTransactionRow.TransactionDate.ToShortDateString(), AFileName);
ALogWriter.WriteLine(LogMessage);
AProcessFile = false;
}
else
{
LogMessage =
String.Format(Catalog.GetString(
"Stewardship Batch for {0} {1} {2} already exists (Batch {3}, date {4}) and data has changed. {5} will be skipped."),
AFromCostCentreName, APeriodName, AYear, ABatchNumber,
PreviousTransactionRow.TransactionDate.ToShortDateString(), AFileName);
ALogWriter.WriteLine(LogMessage);
AProcessFile = false;
}
}
else
{
if (SummaryStewardshipTransactionsMatch(ALedgerNumber, ABatchNumber, AFromCostCentre, ATotalIncome, ATotalExpense,
ATotalTransfer, ref ADBTransaction))
{
LogMessage =
String.Format(Catalog.GetString(
"Stewardship Batch for {0} {1} {2} already exists (Batch {3}, date {4}) but the run number is unknown. File {5} is for run number {6} but the data is identical to the existing batch so it will not be processed."),
AFromCostCentreName, APeriodName, AYear, ABatchNumber,
PreviousTransactionRow.TransactionDate.ToShortDateString(), AFileName, ARunNumber);
ALogWriter.WriteLine(LogMessage);
AProcessFile = false;
}
else
{
AProcessFile = true;
}
}
}
else
{
BatchRunNumber = Convert.ToInt32(PreviousTransRowRef.Substring(PreviousTransRowRef.Length - 3, 3));
if (ARunNumber == BatchRunNumber)
{
if (SummaryStewardshipTransactionsMatch(ALedgerNumber, ABatchNumber, AFromCostCentre, ATotalIncome, ATotalExpense,
ATotalTransfer, ref ADBTransaction))
{
LogMessage =
String.Format(Catalog.GetString(
"Stewardship Batch for {0} {1} {2} already exists (Batch {3}, date {4}) and data is identical. {5} will be skipped."),
AFromCostCentreName, APeriodName, AYear, ABatchNumber,
PreviousTransactionRow.TransactionDate.ToShortDateString(), AFileName);
ALogWriter.WriteLine(LogMessage);
AProcessFile = false;
}
else
{
LogMessage =
String.Format(Catalog.GetString(
"Stewardship Batch for {0} {1} {2} already exists (Batch {3}, date {4}) and data has changed. {5} will be skipped."),
AFromCostCentreName, APeriodName, AYear, ABatchNumber,
PreviousTransactionRow.TransactionDate.ToShortDateString(), AFileName);
ALogWriter.WriteLine(LogMessage);
AProcessFile = false;
}
}
else if (ARunNumber < BatchRunNumber)
{
LogMessage =
String.Format(Catalog.GetString(
"Stewardship Batch for {0} {1} {2} already exists (Batch {3}, date {4}). The run number on that batch ({5}) is higher than the run number on the file being processed and therefore {6} will be skipped."),
AFromCostCentreName, APeriodName, AYear, ABatchNumber,
PreviousTransactionRow.TransactionDate.ToShortDateString(), BatchRunNumber, AFileName);
ALogWriter.WriteLine(LogMessage);
AProcessFile = false;
}
else if (BatchRunNumber == 0)
{
if (SummaryStewardshipTransactionsMatch(ALedgerNumber, ABatchNumber, AFromCostCentre, ATotalIncome, ATotalExpense,
ATotalTransfer, ref ADBTransaction))
{
LogMessage =
String.Format(Catalog.GetString(
"Stewardship Batch for {0} {1} {2} already exists (Batch {3}, date {4}) and is run number 0. File {5} is for run number {6} but the data is identical to the existing batch so it will not be processed."),
AFromCostCentreName, APeriodName, AYear, ABatchNumber,
PreviousTransactionRow.TransactionDate.ToShortDateString(), AFileName, ARunNumber);
ALogWriter.WriteLine(LogMessage);
AProcessFile = false;
}
else
{
AProcessFile = true;
}
}
else
{
AProcessFile = true;
}
}
}
#endif
}
public static void GenerateHOSAReports(int ALedgerNumber,
int APeriodNumber,
int AIchNumber,
string ACurrencySelect,
out TVerificationResultCollection AVerificationResult
)
{
AVerificationResult = new TVerificationResultCollection();
//Begin the transaction
bool NewTransaction = false;
TDBTransaction DBTransaction = DBAccess.GDBAccessObj.GetNewOrExistingTransaction(IsolationLevel.ReadCommitted, out NewTransaction);
ALedgerTable ALedger = ALedgerAccess.LoadByPrimaryKey(ALedgerNumber, DBTransaction);
AJournalTable AJournal = new AJournalTable();
ATransactionTable ATransaction = new ATransactionTable();
ACostCentreTable ACostCentre = new ACostCentreTable();
try
{
#if TODO
ALedgerRow LedgerRow = PostingDS.ALedger[0];
//Find the Ledger Name = Partner Short Name
PPartnerTable PartnerTable = PPartnerAccess.LoadByPrimaryKey(LedgerRow.PartnerKey, DBTransaction);
PPartnerRow PartnerRow = (PPartnerRow)PartnerTable.Rows[0];
string LedgerName = PartnerRow.PartnerShortName;
#endif
//
// Load the Journals, and Transactions for this period:
String JournalQuery = "SELECT PUB_a_journal.* FROM PUB_a_batch, PUB_a_journal WHERE " +
"PUB_a_batch.a_ledger_number_i = " + ALedgerNumber +
" AND PUB_a_batch.a_batch_year_i = " + ALedger[0].CurrentFinancialYear +
" AND PUB_a_batch.a_batch_period_i = " + APeriodNumber +
" AND PUB_a_batch.a_batch_status_c = 'Posted'" +
" AND PUB_a_batch.a_ledger_number_i = PUB_a_journal.a_ledger_number_i" +
" AND PUB_a_batch.a_batch_number_i = PUB_a_journal.a_batch_number_i";
DBAccess.GDBAccessObj.SelectDT(AJournal, JournalQuery, DBTransaction);
String TransactionQuery = "SELECT PUB_a_transaction.* FROM PUB_a_batch, PUB_a_transaction WHERE " +
"PUB_a_batch.a_ledger_number_i = " + ALedgerNumber +
" AND PUB_a_batch.a_batch_year_i = " + ALedger[0].CurrentFinancialYear +
" AND PUB_a_batch.a_batch_period_i = " + APeriodNumber +
" AND PUB_a_batch.a_batch_status_c = 'Posted'" +
" AND PUB_a_batch.a_ledger_number_i = PUB_a_transaction.a_ledger_number_i" +
" AND PUB_a_batch.a_batch_number_i = PUB_a_transaction.a_batch_number_i";
DBAccess.GDBAccessObj.SelectDT(ATransaction, TransactionQuery, DBTransaction);
String CostCentreQuery = "SELECT * FROM a_cost_centre WHERE " +
ACostCentreTable.GetLedgerNumberDBName() + " = " + ALedgerNumber +
" AND " + ACostCentreTable.GetPostingCostCentreFlagDBName() + " = True" +
" AND " + ACostCentreTable.GetCostCentreTypeDBName() + " LIKE '" + MFinanceConstants.FOREIGN_CC_TYPE + "'" +
" ORDER BY " + ACostCentreTable.GetCostCentreCodeDBName();
DBAccess.GDBAccessObj.SelectDT(ACostCentre, CostCentreQuery, DBTransaction);
//Iterate through the cost centres
foreach (ACostCentreRow CostCentreRow in ACostCentre.Rows)
{
bool TransactionExists = false;
//Iterate through the journals
foreach (AJournalRow JournalRow in AJournal.Rows)
{
int BatchNumber = JournalRow.BatchNumber;
int JournalNumber = JournalRow.JournalNumber;
#if TODO
String TransFilter =
ATransactionTable.GetBatchNumberDBName() + " = " + BatchNumber.ToString() +
" AND " + ATransactionTable.GetJournalNumberDBName() + " = " + JournalNumber.ToString() +
" AND " + ATransactionTable.GetCostCentreCodeDBName() + " = '" + CostCentreRow.CostCentreCode + "'" +
" AND (" + ATransactionTable.GetIchNumberDBName() + " = 0" +
" OR " + ATransactionTable.GetIchNumberDBName() + " = " + AIchNumber.ToString() +
")";
DataRow[] FoundTransRows = BatchDS.ATransaction.Select(TransFilter);
foreach (DataRow untypedTransRow in FoundTransRows)
{
ATransactionRow TransactionRow = (ATransactionRow)untypedTransRow;
TransactionExists = true;
string DefaultData = ALedgerNumber.ToString() + "," +
LedgerName + "," +
APeriodNumber.ToString() + "," +
APeriodNumber.ToString() + "," +
CostCentreRow.CostCentreCode + "," +
"" + "," +
"" + "," +
"" + "," +
"A" + "," +
LedgerRow.CurrentFinancialYear.ToString() + "," +
LedgerRow.CurrentPeriod.ToString() + "," +
MFinanceConstants.MAX_PERIODS.ToString() + "," +
"h" + "," +
ACurrency + "," +
AIchNumber.ToString();
string ReportTitle = "Home Office Stmt of Acct: " + CostCentreRow.CostCentreName;
//call code for gl2120p.p Produces Account Detail, Analysis Attribute and HOSA Reprint reports.
/* RUN sm9000.w ("gl2120p.p",
* lv_report_title_c,
* lv_default_data_c).*/
//TODO: call code to produce reports
break;
}
#endif
if (TransactionExists)
{
//only need to run above code once for 1 transaction per cost centre code
break; //goto next cost centre else try next journal
}
}
}
if (NewTransaction)
{
DBAccess.GDBAccessObj.RollbackTransaction();
}
}
catch (Exception Exp)
{
if (NewTransaction)
{
DBAccess.GDBAccessObj.RollbackTransaction();
}
TLogging.Log(Exp.Message);
TLogging.Log(Exp.StackTrace);
throw;
}
}
public static void GenerateHOSAReports(int ALedgerNumber,
int APeriodNumber,
int AIchNumber,
string ACurrencySelect,
out TVerificationResultCollection AVerificationResult
)
{
AVerificationResult = new TVerificationResultCollection();
//Begin the transaction
bool NewTransaction = false;
TDBTransaction DBTransaction = DBAccess.GDBAccessObj.GetNewOrExistingTransaction(IsolationLevel.ReadCommitted, out NewTransaction);
ALedgerTable ALedger = ALedgerAccess.LoadByPrimaryKey(ALedgerNumber, DBTransaction);
AJournalTable AJournal = new AJournalTable();
ATransactionTable ATransaction = new ATransactionTable();
ACostCentreTable ACostCentre = new ACostCentreTable();
try
{
#if TODO
ALedgerRow LedgerRow = PostingDS.ALedger[0];
//Find the Ledger Name = Partner Short Name
PPartnerTable PartnerTable = PPartnerAccess.LoadByPrimaryKey(LedgerRow.PartnerKey, DBTransaction);
PPartnerRow PartnerRow = (PPartnerRow)PartnerTable.Rows[0];
string LedgerName = PartnerRow.PartnerShortName;
#endif
//
// Load the Journals, and Transactions for this period:
String JournalQuery = "SELECT PUB_a_journal.* FROM PUB_a_batch, PUB_a_journal WHERE " +
"PUB_a_batch.a_ledger_number_i = " + ALedgerNumber +
" AND PUB_a_batch.a_batch_year_i = " + ALedger[0].CurrentFinancialYear +
" AND PUB_a_batch.a_batch_period_i = " + APeriodNumber +
" AND PUB_a_batch.a_batch_status_c = 'Posted'" +
" AND PUB_a_batch.a_ledger_number_i = PUB_a_journal.a_ledger_number_i" +
" AND PUB_a_batch.a_batch_number_i = PUB_a_journal.a_batch_number_i";
DBAccess.GDBAccessObj.SelectDT(AJournal, JournalQuery, DBTransaction);
String TransactionQuery = "SELECT PUB_a_transaction.* FROM PUB_a_batch, PUB_a_transaction WHERE " +
"PUB_a_batch.a_ledger_number_i = " + ALedgerNumber +
" AND PUB_a_batch.a_batch_year_i = " + ALedger[0].CurrentFinancialYear +
" AND PUB_a_batch.a_batch_period_i = " + APeriodNumber +
" AND PUB_a_batch.a_batch_status_c = 'Posted'" +
" AND PUB_a_batch.a_ledger_number_i = PUB_a_transaction.a_ledger_number_i" +
" AND PUB_a_batch.a_batch_number_i = PUB_a_transaction.a_batch_number_i";
DBAccess.GDBAccessObj.SelectDT(ATransaction, TransactionQuery, DBTransaction);
String CostCentreQuery = "SELECT * FROM a_cost_centre WHERE " +
ACostCentreTable.GetLedgerNumberDBName() + " = " + ALedgerNumber +
" AND " + ACostCentreTable.GetPostingCostCentreFlagDBName() + " = True" +
" AND " + ACostCentreTable.GetCostCentreTypeDBName() + " LIKE '" + MFinanceConstants.FOREIGN_CC_TYPE + "'" +
" ORDER BY " + ACostCentreTable.GetCostCentreCodeDBName();
DBAccess.GDBAccessObj.SelectDT(ACostCentre, CostCentreQuery, DBTransaction);
//Iterate through the cost centres
foreach (ACostCentreRow CostCentreRow in ACostCentre.Rows)
{
bool TransactionExists = false;
//Iterate through the journals
foreach (AJournalRow JournalRow in AJournal.Rows)
{
int BatchNumber = JournalRow.BatchNumber;
int JournalNumber = JournalRow.JournalNumber;
#if TODO
String TransFilter =
ATransactionTable.GetBatchNumberDBName() + " = " + BatchNumber.ToString() +
" AND " + ATransactionTable.GetJournalNumberDBName() + " = " + JournalNumber.ToString() +
" AND " + ATransactionTable.GetCostCentreCodeDBName() + " = '" + CostCentreRow.CostCentreCode + "'" +
" AND (" + ATransactionTable.GetIchNumberDBName() + " = 0" +
" OR " + ATransactionTable.GetIchNumberDBName() + " = " + AIchNumber.ToString() +
")";
DataRow[] FoundTransRows = BatchDS.ATransaction.Select(TransFilter);
foreach (DataRow untypedTransRow in FoundTransRows)
{
ATransactionRow TransactionRow = (ATransactionRow)untypedTransRow;
TransactionExists = true;
string DefaultData = ALedgerNumber.ToString() + "," +
LedgerName + "," +
APeriodNumber.ToString() + "," +
APeriodNumber.ToString() + "," +
CostCentreRow.CostCentreCode + "," +
"" + "," +
"" + "," +
"" + "," +
"A" + "," +
LedgerRow.CurrentFinancialYear.ToString() + "," +
LedgerRow.CurrentPeriod.ToString() + "," +
MFinanceConstants.MAX_PERIODS.ToString() + "," +
"h" + "," +
ACurrency + "," +
AIchNumber.ToString();
string ReportTitle = "Home Office Stmt of Acct: " + CostCentreRow.CostCentreName;
//call code for gl2120p.p Produces Account Detail, Analysis Attribute and HOSA Reprint reports.
/* RUN sm9000.w ("gl2120p.p",
* lv_report_title_c,
* lv_default_data_c).*/
//TODO: call code to produce reports
break;
}
#endif
if (TransactionExists)
{
//only need to run above code once for 1 transaction per cost centre code
break; //goto next cost centre else try next journal
}
}
}
if (NewTransaction)
{
DBAccess.GDBAccessObj.RollbackTransaction();
}
}
catch (Exception Exp)
{
if (NewTransaction)
{
DBAccess.GDBAccessObj.RollbackTransaction();
}
TLogging.Log(Exp.Message);
TLogging.Log(Exp.StackTrace);
throw;
}
}
