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