private static GiftBatchTDS CreateGiftBatches(SortedList <DateTime, List <XmlNode> > AGiftsPerDate, int APeriodNumber)
{
GiftBatchTDS MainDS = new GiftBatchTDS();
ALedgerTable LedgerTable = null;
TDBTransaction ReadTransaction = null;
DBAccess.GDBAccessObj.BeginAutoReadTransaction(IsolationLevel.ReadCommitted, ref ReadTransaction,
delegate
{
// get a list of potential donors (all class FAMILY)
string sqlGetFamilyPartnerKeys = "SELECT p_partner_key_n FROM PUB_p_family";
DataTable FamilyKeys = DBAccess.GDBAccessObj.SelectDT(sqlGetFamilyPartnerKeys,
"keys",
ReadTransaction);
// get a list of workers (all class FAMILY, with special type WORKER)
string sqlGetWorkerPartnerKeys =
"SELECT PUB_p_family.p_partner_key_n FROM PUB_p_family, PUB_p_partner_type WHERE PUB_p_partner_type.p_partner_key_n = PUB_p_family.p_partner_key_n AND p_type_code_c = 'WORKER'";
DataTable WorkerKeys = DBAccess.GDBAccessObj.SelectDT(sqlGetWorkerPartnerKeys, "keys", ReadTransaction);
// get a list of fields (all class UNIT, with unit type F)
string sqlGetFieldPartnerKeys =
String.Format(
"SELECT U.p_partner_key_n FROM PUB_p_unit U WHERE u_unit_type_code_c = 'F' AND EXISTS (SELECT * FROM PUB_a_valid_ledger_number V WHERE V.a_ledger_number_i = {0} AND V.p_partner_key_n = U.p_partner_key_n)",
FLedgerNumber);
DataTable FieldKeys = DBAccess.GDBAccessObj.SelectDT(sqlGetFieldPartnerKeys, "keys", ReadTransaction);
// get a list of key ministries (all class UNIT, with unit type KEY-MIN), and their field ledger number and cost centre code
string sqlGetKeyMinPartnerKeys =
"SELECT u.p_partner_key_n, us.um_parent_unit_key_n, vl.a_cost_centre_code_c " +
"FROM PUB_p_unit u, PUB_um_unit_structure us, PUB_a_valid_ledger_number vl " +
"WHERE u.u_unit_type_code_c = 'KEY-MIN' " +
"AND us.um_child_unit_key_n = u.p_partner_key_n " +
"AND vl.p_partner_key_n = us.um_parent_unit_key_n " +
"AND vl.a_ledger_number_i = " + FLedgerNumber.ToString();
DataTable KeyMinistries = DBAccess.GDBAccessObj.SelectDT(sqlGetKeyMinPartnerKeys, "keys", ReadTransaction);
LedgerTable = ALedgerAccess.LoadByPrimaryKey(FLedgerNumber, ReadTransaction);
AAccountingPeriodRow AccountingPeriodRow = AAccountingPeriodAccess.LoadByPrimaryKey(FLedgerNumber,
APeriodNumber,
ReadTransaction)[0];
// create a gift batch for each day.
// TODO: could create one batch per month, if there are not so many gifts (less than 100 per month)
foreach (DateTime GlEffectiveDate in AGiftsPerDate.Keys)
{
if ((GlEffectiveDate.CompareTo(AccountingPeriodRow.PeriodStartDate) < 0) ||
(GlEffectiveDate.CompareTo(AccountingPeriodRow.PeriodEndDate) > 0))
{
// only create gifts in that period
continue;
}
AGiftBatchRow giftBatch = TGiftBatchFunctions.CreateANewGiftBatchRow(ref MainDS,
ref ReadTransaction,
ref LedgerTable,
FLedgerNumber,
GlEffectiveDate);
TLogging.LogAtLevel(1, "create gift batch for " + GlEffectiveDate.ToShortDateString());
giftBatch.BatchDescription = "Benerator Batch for " + GlEffectiveDate.ToShortDateString();
giftBatch.BatchTotal = 0.0m;
foreach (XmlNode RecordNode in AGiftsPerDate[GlEffectiveDate])
{
AGiftRow gift = MainDS.AGift.NewRowTyped();
gift.LedgerNumber = giftBatch.LedgerNumber;
gift.BatchNumber = giftBatch.BatchNumber;
gift.GiftTransactionNumber = giftBatch.LastGiftNumber + 1;
gift.DateEntered = GlEffectiveDate;
// set donorKey
int donorID = Convert.ToInt32(TXMLParser.GetAttribute(RecordNode, "donor")) % FamilyKeys.Rows.Count;
gift.DonorKey = Convert.ToInt64(FamilyKeys.Rows[donorID].ItemArray[0]);
// calculate gift detail information
int countDetails = Convert.ToInt32(TXMLParser.GetAttribute(RecordNode, "splitgift"));
for (int counter = 1; counter <= countDetails; counter++)
{
AGiftDetailRow giftDetail = MainDS.AGiftDetail.NewRowTyped();
giftDetail.LedgerNumber = gift.LedgerNumber;
giftDetail.BatchNumber = gift.BatchNumber;
giftDetail.GiftTransactionNumber = gift.GiftTransactionNumber;
giftDetail.MotivationGroupCode = "GIFT";
giftDetail.GiftTransactionAmount = Convert.ToDecimal(TXMLParser.GetAttribute(RecordNode, "amount_" + counter.ToString()));
giftDetail.GiftAmount = giftDetail.GiftTransactionAmount;
giftBatch.BatchTotal += giftDetail.GiftAmount;
string motivation = TXMLParser.GetAttribute(RecordNode, "motivation_" + counter.ToString());
if (motivation == "SUPPORT")
{
if (WorkerKeys.Rows.Count == 0)
{
continue;
}
giftDetail.MotivationDetailCode = "SUPPORT";
int recipientID =
Convert.ToInt32(TXMLParser.GetAttribute(RecordNode, "recipient_support_" +
counter.ToString())) % WorkerKeys.Rows.Count;
giftDetail.RecipientKey = Convert.ToInt64(WorkerKeys.Rows[recipientID].ItemArray[0]);
giftDetail.RecipientLedgerNumber = TGiftTransactionWebConnector.GetRecipientFundNumber(giftDetail.RecipientKey,
giftBatch.GlEffectiveDate);
// ignore this gift detail, if there is no valid commitment period for the worker
if (giftDetail.RecipientLedgerNumber == 0)
{
continue;
}
}
else if (motivation == "FIELD")
{
if (FieldKeys.Rows.Count == 0)
{
continue;
}
giftDetail.MotivationDetailCode = "FIELD";
int recipientID =
Convert.ToInt32(TXMLParser.GetAttribute(RecordNode, "recipient_field_" +
counter.ToString())) % FieldKeys.Rows.Count;
giftDetail.RecipientKey = Convert.ToInt64(FieldKeys.Rows[recipientID].ItemArray[0]);
giftDetail.RecipientLedgerNumber = giftDetail.RecipientKey;
giftDetail.CostCentreCode = (giftDetail.RecipientKey / 10000).ToString("0000");
}
else if (motivation == "KEYMIN")
{
if (KeyMinistries.Rows.Count == 0)
{
continue;
}
giftDetail.MotivationDetailCode = "KEYMIN";
int recipientID =
Convert.ToInt32(TXMLParser.GetAttribute(RecordNode, "recipient_keymin_" +
counter.ToString())) % KeyMinistries.Rows.Count;
giftDetail.RecipientKey = Convert.ToInt64(KeyMinistries.Rows[recipientID].ItemArray[0]);
giftDetail.RecipientLedgerNumber = Convert.ToInt64(KeyMinistries.Rows[recipientID].ItemArray[1]);
// TTransactionWebConnector.GetRecipientFundNumber(giftDetail.RecipientKey);
giftDetail.CostCentreCode = KeyMinistries.Rows[recipientID].ItemArray[2].ToString();
// TTransactionWebConnector.IdentifyPartnerCostCentre(FLedgerNumber, giftDetail.RecipientLedgerNumber);
}
giftDetail.DetailNumber = gift.LastDetailNumber + 1;
MainDS.AGiftDetail.Rows.Add(giftDetail);
gift.LastDetailNumber = giftDetail.DetailNumber;
}
if (gift.LastDetailNumber > 0)
{
MainDS.AGift.Rows.Add(gift);
giftBatch.LastGiftNumber = gift.GiftTransactionNumber;
}
if (giftBatch.LastGiftNumber >= MaxGiftsPerBatch)
{
break;
}
}
if (TLogging.DebugLevel > 0)
{
TLogging.Log(
GlEffectiveDate.ToShortDateString() + " " + giftBatch.LastGiftNumber.ToString());
}
}
});
// need to save the last gift batch number in a_ledger
if (LedgerTable != null)
{
TDBTransaction WriteTransaction = null;
bool SubmissionOk = false;
DBAccess.GDBAccessObj.BeginAutoTransaction(IsolationLevel.Serializable, ref WriteTransaction, ref SubmissionOk,
delegate
{
ALedgerAccess.SubmitChanges(LedgerTable, WriteTransaction);
SubmissionOk = true;
});
if (!SubmissionOk)
{
TLogging.Log("An Exception occured during the creation of Gift Batches" + Environment.NewLine);
}
}
return(MainDS);
}
public static bool PeriodMonthEnd(
Int32 ALedgerNumber,
bool AInfoMode,
out List <Int32> AglBatchNumbers,
out Boolean AStewardshipBatch,
out TVerificationResultCollection AVerificationResults,
TDataBase ADataBase = null)
{
AglBatchNumbers = new List <int>();
AStewardshipBatch = false;
try
{
TLedgerInfo ledgerInfo = new TLedgerInfo(ALedgerNumber, ADataBase);
Int32 PeriodClosing = ledgerInfo.CurrentPeriod;
bool succeeded = new TMonthEnd(ADataBase, ledgerInfo).RunMonthEnd(AInfoMode,
out AglBatchNumbers,
out AStewardshipBatch,
out AVerificationResults);
if (succeeded && !AInfoMode)
{
TDBTransaction Transaction = new TDBTransaction();
TDataBase db = DBAccess.Connect("PeriodMonthEnd", ADataBase);
AAccountingPeriodTable PeriodTbl = null;
db.ReadTransaction(
ref Transaction,
delegate
{
PeriodTbl = AAccountingPeriodAccess.LoadByPrimaryKey(ledgerInfo.LedgerNumber, PeriodClosing, Transaction);
});
if (ADataBase == null)
{
db.CloseDBConnection();
}
if (succeeded && PeriodTbl.Rows.Count > 0)
{
AVerificationResults.Add(
new TVerificationResult(
Catalog.GetString("Month End"),
String.Format(Catalog.GetString("The period {0} - {1} has been closed."),
PeriodTbl[0].PeriodStartDate.ToShortDateString(), PeriodTbl[0].PeriodEndDate.ToShortDateString()),
TResultSeverity.Resv_Status));
}
}
return(succeeded);
}
catch (Exception e)
{
TLogging.Log("TPeriodIntervallConnector.TPeriodMonthEnd() throws " + e.ToString());
AVerificationResults = new TVerificationResultCollection();
AVerificationResults.Add(
new TVerificationResult(
Catalog.GetString("Month End"),
Catalog.GetString("Uncaught Exception: ") + e.Message,
TResultSeverity.Resv_Critical));
return(false);
}
}
/// <summary>
/// Main Entry point. The parameters are the same as in
/// Ict.Petra.Server.MFinance.GL.WebConnectors.TPeriodMonthEnd
/// </summary>
/// <param name="AInfoMode"></param>
/// <param name="AglBatchNumbers">The Client should print the generated Batches</param>
/// <param name="AStewardshipBatch">True if Stewardship Batch was generated</param>
/// <param name="AVRCollection"></param>
/// <returns>true if it went OK</returns>
public bool RunMonthEnd(
bool AInfoMode,
out List <Int32> AglBatchNumbers,
out Boolean AStewardshipBatch,
out TVerificationResultCollection AVRCollection)
{
FInfoMode = AInfoMode;
FverificationResults = new TVerificationResultCollection();
AVRCollection = FverificationResults;
AStewardshipBatch = false;
WasCancelled = false;
AglBatchNumbers = new List <int>();
if (FledgerInfo.ProvisionalYearEndFlag)
{
// we want to run a month end, but the provisional year end flag has been set
TVerificationResult tvt =
new TVerificationResult(Catalog.GetString("Year End is required!"),
Catalog.GetString("In this situation you cannot run a month end routine"), "",
TPeriodEndErrorAndStatusCodes.PEEC_03.ToString(),
TResultSeverity.Resv_Critical);
FverificationResults.Add(tvt);
FHasCriticalErrors = true;
return(false);
}
TDBTransaction Transaction = new TDBTransaction();
TDataBase db = DBAccess.Connect("RunMonthEnd", FDataBase);
if (AInfoMode)
{
AAccountingPeriodTable PeriodTbl = null;
db.ReadTransaction(
ref Transaction,
delegate
{
PeriodTbl = AAccountingPeriodAccess.LoadByPrimaryKey(FledgerInfo.LedgerNumber, FledgerInfo.CurrentPeriod, Transaction);
});
if (PeriodTbl.Rows.Count > 0)
{
FverificationResults.Add(
new TVerificationResult(
Catalog.GetString("Month End"),
String.Format(Catalog.GetString("Current period is {0} - {1}"),
PeriodTbl[0].PeriodStartDate.ToShortDateString(), PeriodTbl[0].PeriodEndDate.ToShortDateString()),
TResultSeverity.Resv_Status));
}
}
RunPeriodEndCheck(new RunMonthEndChecks(db, FledgerInfo), FverificationResults);
if (!AInfoMode)
{
TVerificationResultCollection IchVerificationResults;
if (!StewardshipCalculationDelegate(FledgerInfo.LedgerNumber, FledgerInfo.CurrentPeriod,
out AglBatchNumbers,
out IchVerificationResults, db))
{
FHasCriticalErrors = true;
}
// Merge VerificationResults:
FverificationResults.AddCollection(IchVerificationResults);
if (AglBatchNumbers.Count > 0)
{
AStewardshipBatch = true;
}
}
// RunPeriodEndSequence(new RunMonthlyAdminFees(), "Example");
if (TPeriodEndOperations.WasCancelled)
{
FverificationResults.Add(new TVerificationResult(Catalog.GetString("Month End"),
Catalog.GetString("Process was cancelled by user."), "",
TPeriodEndErrorAndStatusCodes.PEEC_12.ToString(),
TResultSeverity.Resv_Critical));
FHasCriticalErrors = true;
}
if (!FInfoMode)
{
if (!FHasCriticalErrors)
{
SetNextPeriod(Transaction);
NoteForexRevalRequired(FledgerInfo.LedgerNumber, FledgerInfo.CurrentFinancialYear, FledgerInfo.CurrentPeriod);
// refresh cached ledger table, so that the client will know the current period
TCacheableTablesManager.GCacheableTablesManager.MarkCachedTableNeedsRefreshing(
TCacheableFinanceTablesEnum.LedgerDetails.ToString());
}
}
if (FDataBase == null)
{
db.CloseDBConnection();
}
return(!FHasCriticalErrors);
}
public static Boolean GetCurrentPostingRangeDates(Int32 ALedgerNumber,
out DateTime AStartDateCurrentPeriod,
out DateTime AEndDateLastForwardingPeriod)
{
Boolean dataIsOk = false;
#region Validate Arguments
if (ALedgerNumber <= 0)
{
throw new EFinanceSystemInvalidLedgerNumberException(String.Format(Catalog.GetString(
"Function:{0} - The Ledger number must be greater than 0!"),
Utilities.GetMethodName(true)), ALedgerNumber);
}
#endregion Validate Arguments
DateTime StartDateCurrentPeriod = new DateTime();
DateTime EndDateLastForwardingPeriod = new DateTime();
TDBTransaction Transaction = new TDBTransaction();
TDataBase db = DBAccess.Connect("GetCurrentPostingRangeDates");
db.ReadTransaction(
ref Transaction,
delegate
{
ALedgerTable ledgerTable = ALedgerAccess.LoadByPrimaryKey(ALedgerNumber, Transaction);
#region Validate Data
if ((ledgerTable == null) || (ledgerTable.Count == 0))
{
throw new EFinanceSystemDataTableReturnedNoDataException(String.Format(Catalog.GetString(
"Function:{0} - Ledger data for Ledger number {1} does not exist or could not be accessed!"),
Utilities.GetMethodName(true),
ALedgerNumber));
}
#endregion Validate Data
int firstPostingPeriod = -1;
int lastPostingPeriod = -1;
// If final month end has been run but year end has not yet been run
// then we cannot post to the current period as it is actually closed.
if (ledgerTable[0].ProvisionalYearEndFlag)
{
firstPostingPeriod = ledgerTable[0].CurrentPeriod + 1;
}
else
{
firstPostingPeriod = ledgerTable[0].CurrentPeriod;
}
AAccountingPeriodTable accountingPeriodTable = AAccountingPeriodAccess.LoadByPrimaryKey(ALedgerNumber,
firstPostingPeriod,
Transaction);
#region Validate Data 2
if ((accountingPeriodTable == null) || (accountingPeriodTable.Count == 0))
{
throw new EFinanceSystemDataTableReturnedNoDataException(String.Format(Catalog.GetString(
"Function:{0} - Accounting Period data for Ledger number {1} and posting period {2} does not exist or could not be accessed!"),
Utilities.GetMethodName(true),
ALedgerNumber,
firstPostingPeriod));
}
#endregion Validate Data 2
StartDateCurrentPeriod = accountingPeriodTable[0].PeriodStartDate;
lastPostingPeriod = ledgerTable[0].CurrentPeriod + ledgerTable[0].NumberFwdPostingPeriods;
accountingPeriodTable = AAccountingPeriodAccess.LoadByPrimaryKey(ALedgerNumber,
lastPostingPeriod,
Transaction);
#region Validate Data 3
if ((accountingPeriodTable == null) || (accountingPeriodTable.Count == 0))
{
throw new EFinanceSystemDataTableReturnedNoDataException(String.Format(Catalog.GetString(
"Function:{0} - Accounting Period data for Ledger number {1} and posting period {2} does not exist or could not be accessed!"),
Utilities.GetMethodName(true),
ALedgerNumber,
lastPostingPeriod));
}
#endregion Validate Data 3
EndDateLastForwardingPeriod = accountingPeriodTable[0].PeriodEndDate;
dataIsOk = true;
});
AStartDateCurrentPeriod = StartDateCurrentPeriod;
AEndDateLastForwardingPeriod = EndDateLastForwardingPeriod;
db.CloseDBConnection();
return(dataIsOk);
}
/// <summary>
/// Main Entry point. The parameters are the same as in
/// Ict.Petra.Server.MFinance.GL.WebConnectors.TPeriodMonthEnd
/// </summary>
/// <param name="AInfoMode"></param>
/// <param name="AVRCollection"></param>
/// <returns>false if it went OK</returns>
public bool RunMonthEnd(
bool AInfoMode,
out TVerificationResultCollection AVRCollection)
{
FInfoMode = AInfoMode;
FverificationResults = new TVerificationResultCollection();
AVRCollection = FverificationResults;
TPeriodEndOperations.FwasCancelled = false;
if (FledgerInfo.ProvisionalYearEndFlag)
{
// we want to run a month end, but the provisional year end flag has been set
TVerificationResult tvt =
new TVerificationResult(Catalog.GetString("Year End is required!"),
Catalog.GetString("In this situation you cannot run a month end routine"), "",
TPeriodEndErrorAndStatusCodes.PEEC_03.ToString(),
TResultSeverity.Resv_Critical);
FverificationResults.Add(tvt);
FHasCriticalErrors = true;
return(true);
}
if (AInfoMode)
{
AAccountingPeriodTable PeriodTbl = null;
TDBTransaction Transaction = null;
DBAccess.GDBAccessObj.GetNewOrExistingAutoReadTransaction(IsolationLevel.ReadUncommitted,
TEnforceIsolationLevel.eilMinimum,
ref Transaction,
delegate
{
PeriodTbl = AAccountingPeriodAccess.LoadByPrimaryKey(FledgerInfo.LedgerNumber, FledgerInfo.CurrentPeriod, Transaction);
});
if (PeriodTbl.Rows.Count > 0)
{
FverificationResults.Add(
new TVerificationResult(
Catalog.GetString("Month End"),
String.Format(Catalog.GetString("Current period is {0} - {1}"),
PeriodTbl[0].PeriodStartDate.ToShortDateString(), PeriodTbl[0].PeriodEndDate.ToShortDateString()),
TResultSeverity.Resv_Status));
}
}
RunPeriodEndCheck(new RunMonthEndChecks(FledgerInfo), FverificationResults);
if (!AInfoMode)
{
TVerificationResultCollection IchVerificationReults;
if (!StewardshipCalculationDelegate(FledgerInfo.LedgerNumber, FledgerInfo.CurrentPeriod,
out IchVerificationReults))
{
FHasCriticalErrors = true;
}
// Merge VerificationResults:
FverificationResults.AddCollection(IchVerificationReults);
}
// RunPeriodEndSequence(new RunMonthlyAdminFees(), "Example");
if (TPeriodEndOperations.FwasCancelled)
{
FverificationResults.Add(new TVerificationResult(Catalog.GetString("Month End"),
Catalog.GetString("Process was cancelled by user."), "",
TPeriodEndErrorAndStatusCodes.PEEC_12.ToString(),
TResultSeverity.Resv_Critical));
FHasCriticalErrors = true;
}
if (!FInfoMode)
{
if (!FHasCriticalErrors)
{
SetNextPeriod();
NoteForexRevalRequired(FledgerInfo.LedgerNumber, FledgerInfo.CurrentFinancialYear, FledgerInfo.CurrentPeriod);
// refresh cached ledger table, so that the client will know the current period
TCacheableTablesManager.GCacheableTablesManager.MarkCachedTableNeedsRefreshing(
TCacheableFinanceTablesEnum.LedgerDetails.ToString());
}
}
//
// The 4GL code throws out these reports:
//
// Admin fee calculations report.
// ICH stewardship report.
// "Trial Balance" with account details.
// HOSA for each foreign cost centre (ledger/fund).
// Income Statement/Profit & Loss
// Current Accounts Payable if interfaced. M025
// AFO report.
// Executive Summary Report.
//
return(FHasCriticalErrors);
}
public static bool GenerateHOSAFiles(int ALedgerNumber,
int APeriodNumber,
int AIchNumber,
string ACostCentre,
String ACurrencySelect,
string AFileName,
out TVerificationResultCollection AVerificationResult
)
{
bool Successful = false;
AVerificationResult = new TVerificationResultCollection();
//Begin the transaction
bool NewTransaction = false;
TDBTransaction DBTransaction = DBAccess.GDBAccessObj.GetNewOrExistingTransaction(IsolationLevel.ReadCommitted, out NewTransaction);
try
{
GLBatchTDS MainDS = new GLBatchTDS();
//Load tables needed: AccountingPeriod, Ledger, Account, Cost Centre, Transaction, Gift Batch, ICHStewardship
ALedgerAccess.LoadByPrimaryKey(MainDS, ALedgerNumber, DBTransaction);
/* Retrieve info on the ledger. */
ALedgerRow LedgerRow = (ALedgerRow)MainDS.ALedger.Rows[0];
String Currency = (ACurrencySelect == MFinanceConstants.CURRENCY_BASE) ? LedgerRow.BaseCurrency : LedgerRow.IntlCurrency;
/* String StoreNumericFormat = "#" + CultureInfo.CurrentCulture.NumberFormat.NumberGroupSeparator + "##0";
*
* if (CultureInfo.CurrentCulture.NumberFormat.NumberDecimalDigits > 0)
* {
* string DecPls = new String('0', CultureInfo.CurrentCulture.NumberFormat.NumberDecimalDigits);
* StoreNumericFormat += CultureInfo.CurrentCulture.NumberFormat.NumberDecimalSeparator + DecPls;
* }
*/
AAccountingPeriodTable AccountingPeriodTable = AAccountingPeriodAccess.LoadByPrimaryKey(ALedgerNumber, APeriodNumber, DBTransaction);
AAccountingPeriodRow AccountingPeriodRow = (AAccountingPeriodRow)AccountingPeriodTable.Rows[0];
String MonthName = AccountingPeriodRow.AccountingPeriodDesc;
//Create table definitions
DataTable TableForExport = new DataTable();
TableForExport.Columns.Add("CostCentre", typeof(string));
TableForExport.Columns.Add("Account", typeof(string));
TableForExport.Columns.Add("LedgerMonth", typeof(string));
TableForExport.Columns.Add("ICHPeriod", typeof(string));
TableForExport.Columns.Add("Date", typeof(DateTime));
TableForExport.Columns.Add("IndividualDebitTotal", typeof(decimal));
TableForExport.Columns.Add("IndividualCreditTotal", typeof(decimal));
string TableForExportHeader = "/** Header **" + "," +
APeriodNumber.ToString() + "," +
TLedgerInfo.GetStandardCostCentre(ALedgerNumber) + "," +
ACostCentre + "," +
DateTime.Today.ToShortDateString() + "," +
Currency;
//See gi3200.p ln: 170
//Select any gift transactions to export
string strSql = TDataBase.ReadSqlFile("ICH.HOSAExportGifts.sql");
OdbcParameter parameter;
List <OdbcParameter> parameters = new List <OdbcParameter>();
parameter = new OdbcParameter("LedgerNumber", OdbcType.Int);
parameter.Value = ALedgerNumber;
parameters.Add(parameter);
parameter = new OdbcParameter("Year", OdbcType.Int);
parameter.Value = LedgerRow.CurrentFinancialYear;
parameters.Add(parameter);
parameter = new OdbcParameter("CostCentre", OdbcType.VarChar);
parameter.Value = ACostCentre;
parameters.Add(parameter);
DataTable TmpTable = DBAccess.GDBAccessObj.SelectDT(strSql, "table", DBTransaction, parameters.ToArray());
foreach (DataRow untypedTransRow in TmpTable.Rows)
{
string gLMAcctCode = untypedTransRow[3].ToString();
string gLMCostCCode = untypedTransRow[4].ToString();
string gLMAcctType = untypedTransRow[5].ToString();
if (gLMAcctType == MFinanceConstants.ACCOUNT_TYPE_INCOME) //a_account.a_account_type_c
{
DateTime PeriodStartDate = AccountingPeriodRow.PeriodStartDate;
DateTime PeriodEndDate = AccountingPeriodRow.PeriodEndDate;
/*RUN Export_gifts(INPUT pv_ledger_number_i...*/
//gi3200-1.i
ExportGifts(ALedgerNumber,
ACostCentre,
gLMAcctCode,
MonthName,
APeriodNumber,
PeriodStartDate,
PeriodEndDate,
ACurrencySelect,
AIchNumber,
ref TableForExport,
ref AVerificationResult);
}
/* Then see if there are any GL transactions to export */
//gi3200.i ln:33
/*
* This scheme with ODBC parameters consistently causes an "input string is the wrong type" eror:
*
* strSql = TDataBase.ReadSqlFile("ICH.HOSAExportGLTrans.sql");
* OdbcParameter[] SqlParams = new OdbcParameter[] {
* new OdbcParameter("LedgerNumber", (Int32)ALedgerNumber),
* new OdbcParameter("Account", (String)gLMAcctCode),
* new OdbcParameter("CostCentre", (String)gLMCostCCode),
* new OdbcParameter("Narrative", (String)MFinanceConstants.NARRATIVE_YEAR_END_REALLOCATION),
* new OdbcParameter("ICHNumber", (Int32)AIchNumber),
* new OdbcParameter("ICHNumber2", (Int32)AIchNumber),
* new OdbcParameter("PeriodNumber", (Int32)APeriodNumber)
* };
* DataTable TmpTransTable = DBAccess.GDBAccessObj.SelectDT(strSql, "Transactions", DBTransaction, SqlParams);
*/
strSql = "SELECT Trans.a_ledger_number_i, Trans.a_batch_number_i, Trans.a_journal_number_i, Trans.a_transaction_number_i, " +
"Trans.a_account_code_c, Trans.a_cost_centre_code_c, Trans.a_transaction_date_d, Trans.a_transaction_amount_n, " +
"Trans.a_amount_in_base_currency_n, Trans.a_amount_in_intl_currency_n, Trans.a_ich_number_i, Trans.a_system_generated_l, "
+
"Trans.a_narrative_c, Trans.a_debit_credit_indicator_l FROM public.a_transaction AS Trans, public.a_journal AS Journal "
+
"WHERE Trans.a_ledger_number_i = Journal.a_ledger_number_i AND Trans.a_batch_number_i = Journal.a_batch_number_i " +
"AND Trans.a_journal_number_i = Journal.a_journal_number_i " +
String.Format(
"AND Trans.a_ledger_number_i = {0} AND Trans.a_account_code_c = '{1}' AND Trans.a_cost_centre_code_c = '{2}' " +
"AND Trans.a_transaction_status_l = true AND NOT (Trans.a_narrative_c LIKE '{3}%' AND Trans.a_system_generated_l = true) " +
"AND ((Trans.a_ich_number_i + {4}) = Trans.a_ich_number_i OR Trans.a_ich_number_i = {4}) " +
"AND Journal.a_journal_period_i = {5};",
ALedgerNumber,
gLMAcctCode,
gLMCostCCode,
MFinanceConstants.NARRATIVE_YEAR_END_REALLOCATION,
AIchNumber,
APeriodNumber
);
DataTable TmpTransTable = DBAccess.GDBAccessObj.SelectDT(strSql, "Transactions", DBTransaction);
foreach (DataRow untypedTransactRow in TmpTransTable.Rows)
{
Decimal DebitTotal = 0;
Decimal CreditTotal = 0;
bool Debit = Convert.ToBoolean(untypedTransactRow[13]); //a_transaction.a_debit_credit_indicator_l
bool SystemGenerated = Convert.ToBoolean(untypedTransactRow[11]); //a_transaction.a_system_generated_l
string Narrative = untypedTransactRow[12].ToString(); //a_transaction.a_narrative_c
DateTime TransactionDate = Convert.ToDateTime(untypedTransactRow[6]); //a_transaction.a_transaction_date_d
if (ACurrencySelect == MFinanceConstants.CURRENCY_BASE)
{
decimal AmountInBaseCurrency = Convert.ToDecimal(untypedTransactRow[8]); //a_transaction.a_amount_in_base_currency_n
/* find transaction amount and store as debit or credit */
if (Debit)
{
DebitTotal += AmountInBaseCurrency;
}
else
{
CreditTotal += AmountInBaseCurrency;
}
}
else
{
decimal AmountInIntlCurrency = Convert.ToDecimal(untypedTransactRow[9]); //a_transaction.a_amount_in_intl_currency_n
if (Debit)
{
DebitTotal += AmountInIntlCurrency;
}
else
{
CreditTotal += AmountInIntlCurrency;
}
}
TLogging.LogAtLevel(4, "HOSA-Narrative: " + Narrative);
//Check for specific narrative strings
bool IsNarrativeGBGiftBatch = false;
int LenNarrativeGBGiftBatch = MFinanceConstants.NARRATIVE_GB_GIFT_BATCH.Length;
bool IsNarrativeGiftsReceivedGiftBatch = false;
int LenNarrativeGiftsReceivedGiftBatch = MFinanceConstants.NARRATIVE_GIFTS_RECEIVED_GIFT_BATCH.Length;
if (Narrative.Length >= LenNarrativeGiftsReceivedGiftBatch)
{
IsNarrativeGiftsReceivedGiftBatch =
(Narrative.Substring(0, LenNarrativeGiftsReceivedGiftBatch) == MFinanceConstants.NARRATIVE_GIFTS_RECEIVED_GIFT_BATCH);
}
if (Narrative.Length >= LenNarrativeGBGiftBatch)
{
IsNarrativeGBGiftBatch = (Narrative.Substring(0, LenNarrativeGBGiftBatch) == MFinanceConstants.NARRATIVE_GB_GIFT_BATCH);
}
if ((gLMAcctType.ToUpper() != MFinanceConstants.ACCOUNT_TYPE_INCOME.ToUpper()) ||
!(SystemGenerated && (IsNarrativeGBGiftBatch || IsNarrativeGiftsReceivedGiftBatch)))
{
// Put transaction information
DataRow DR = (DataRow)TableForExport.NewRow();
DR[0] = gLMCostCCode;
DR[1] = ConvertAccount(gLMAcctCode);
DR[2] = ALedgerNumber.ToString() + MonthName + ":" + Narrative;
DR[3] = "ICH-" + APeriodNumber.ToString("00");
DR[4] = TransactionDate;
DR[5] = DebitTotal;
DR[6] = CreditTotal;
TableForExport.Rows.Add(DR);
}
}
}
TableForExport.AcceptChanges();
TLogging.LogAtLevel(4, "HOSA-TableForExport: " + TableForExport.Rows.Count.ToString());
//DataTables to XML to CSV
XmlDocument doc = TDataBase.DataTableToXml(TableForExport);
TCsv2Xml.Xml2Csv(doc, AFileName);
//Replace the default CSV header row with OM specific
ReplaceHeaderInFile(AFileName, TableForExportHeader, ref AVerificationResult);
/* Change number format back */
//TODO
Successful = true;
}
catch (Exception e)
{
TLogging.Log(e.ToString());
}
// rollback the reading transaction
if (NewTransaction)
{
DBAccess.GDBAccessObj.RollbackTransaction();
}
return(Successful);
}
