public static bool GetFirstDayOfAccountingPeriod(Int32 ALedgerNumber, DateTime ADateInAPeriod, out DateTime AFirstDayOfPeriod, TDataBase ADataBase = null)
{
TDBTransaction Transaction = new TDBTransaction();
TDataBase db = DBAccess.Connect("GetFirstDayOfAccountingPeriod", ADataBase);
DateTime Result = DateTime.MinValue;
db.ReadTransaction(ref Transaction,
delegate
{
// Get the accounting periods for this ledger. The table will contain more than 12 rows.
// The start dates will be the correct day and month but may have been inserted for an arbitrary year when the table was first created.
// We are really only interested in the Day anyway
AAccountingPeriodTable periods = AAccountingPeriodAccess.LoadViaALedger(ALedgerNumber, Transaction);
DataView periodsView = new DataView(periods, "",
String.Format("{0} ASC", AAccountingPeriodTable.GetPeriodStartDateDBName()), DataViewRowState.CurrentRows);
AAccountingPeriodRow row = (AAccountingPeriodRow)periodsView[0].Row;
Result = new DateTime(ADateInAPeriod.Year, ADateInAPeriod.Month, row.PeriodStartDate.Day);
if (ADateInAPeriod.Day < row.PeriodStartDate.Day)
{
Result = Result.AddMonths(-1);
}
});
if (ADataBase == null)
{
db.CloseDBConnection();
}
AFirstDayOfPeriod = Result;
return(Result != DateTime.MinValue);
}
/// <summary>
/// Get the start and end date of a given period in a given ledger in the given year
/// </summary>
/// <returns>void</returns>
public void GetPeriodDetails(int ledgernr, int period, out DateTime startOfPeriod, out DateTime endOfPeriod, int whichyear, int column)
{
int currentFinancialYear = parameters.Get("param_current_financial_year_i", column).ToInt();
TFinancialPeriod financialPeriod = new TFinancialPeriod(situation.GetDatabaseConnection(), period, whichyear,
situation.GetParameters(), situation.GetColumn());
AAccountingPeriodTable tab = AAccountingPeriodAccess.LoadByPrimaryKey(ledgernr, period, situation.GetDatabaseConnection().Transaction);
if (tab.Rows.Count == 1)
{
AAccountingPeriodRow row = tab[0];
try
{
endOfPeriod = new DateTime(row.PeriodEndDate.Year - (currentFinancialYear - financialPeriod.realYear),
row.PeriodEndDate.Month,
row.PeriodEndDate.Day);
}
catch (Exception)
{
endOfPeriod = new DateTime(row.PeriodEndDate.Year - (currentFinancialYear - financialPeriod.realYear),
row.PeriodEndDate.Month,
row.PeriodEndDate.Day - 1);
}
startOfPeriod = new DateTime(row.PeriodStartDate.Year - (currentFinancialYear - financialPeriod.realYear),
row.PeriodStartDate.Month,
row.PeriodStartDate.Day);
}
else
{
endOfPeriod = DateTime.MinValue;
startOfPeriod = DateTime.MinValue;
}
financialPeriod = null;
}
public static bool GetAccountingYearPeriodByDate(Int32 ALedgerNumber,
DateTime ADate,
out Int32 AYearNumber,
out Int32 APeriodNumber)
{
bool newTransaction;
Int32 CurrentFinancialYear;
//Set the year to return
AYearNumber = FindFinancialYearByDate(ALedgerNumber, ADate);
if (AYearNumber == 99)
{
AYearNumber = 0;
APeriodNumber = 0;
return(false);
}
TDBTransaction Transaction = DBAccess.GDBAccessObj.GetNewOrExistingTransaction(IsolationLevel.Serializable, out newTransaction);
ALedgerTable LedgerTable = ALedgerAccess.LoadByPrimaryKey(ALedgerNumber, Transaction);
CurrentFinancialYear = ((ALedgerRow)LedgerTable.Rows[0]).CurrentFinancialYear;
AAccountingPeriodTable AccPeriodTableTmp = new AAccountingPeriodTable();
AAccountingPeriodRow TemplateRow = AccPeriodTableTmp.NewRowTyped(false);
TemplateRow.LedgerNumber = ALedgerNumber;
TemplateRow.PeriodStartDate = ADate.AddYears(CurrentFinancialYear - AYearNumber);
TemplateRow.PeriodEndDate = ADate.AddYears(CurrentFinancialYear - AYearNumber);
StringCollection operators = StringHelper.InitStrArr(new string[] { "=", "<=", ">=" });
AAccountingPeriodTable AccountingPeriodTable = AAccountingPeriodAccess.LoadUsingTemplate(TemplateRow,
operators,
null,
Transaction);
if ((AccountingPeriodTable == null) || (AccountingPeriodTable.Count == 0))
{
if (newTransaction)
{
DBAccess.GDBAccessObj.RollbackTransaction();
}
APeriodNumber = 0;
return(false);
}
AAccountingPeriodRow AccountingPeriodRow = (AAccountingPeriodRow)AccountingPeriodTable.Rows[0];
APeriodNumber = AccountingPeriodRow.AccountingPeriodNumber;
if (newTransaction)
{
DBAccess.GDBAccessObj.RollbackTransaction();
}
return(true);
}
private static Int32 FindFinancialYearByDate(Int32 ALedgerNumber, DateTime ADate)
{
Int32 yearDateBelongsTo = 99;
DateTime yearStartDate = DateTime.Today;
TDBTransaction transaction = null;
DBAccess.GDBAccessObj.GetNewOrExistingAutoReadTransaction(IsolationLevel.Serializable, ref transaction,
delegate
{
ALedgerTable LedgerTable = ALedgerAccess.LoadByPrimaryKey(ALedgerNumber, transaction);
if (LedgerTable.Count == 0)
{
return;
}
ALedgerRow LedgerRow = (ALedgerRow)LedgerTable.Rows[0];
yearDateBelongsTo = LedgerRow.CurrentFinancialYear;
AAccountingPeriodTable AccPeriodTable = AAccountingPeriodAccess.LoadViaALedger(ALedgerNumber, transaction);
if (AccPeriodTable.Count == 0)
{
return;
}
//Find earliest start date (don't assume PK order)
AAccountingPeriodRow AccPeriodRow = null;
for (int i = 0; i < AccPeriodTable.Count; i++)
{
DateTime currentStartDate;
AccPeriodRow = (AAccountingPeriodRow)AccPeriodTable.Rows[i];
currentStartDate = AccPeriodRow.PeriodStartDate;
if (i > 0)
{
if (yearStartDate > currentStartDate)
{
yearStartDate = currentStartDate;
}
}
else
{
yearStartDate = currentStartDate;
}
}
//Find the correct year
while (ADate < yearStartDate)
{
ADate = ADate.AddYears(1);
yearDateBelongsTo--;
}
}); // Get NewOrExisting AutoReadTransaction
//Set the year to return
return(yearDateBelongsTo);
} // Find FinancialYear ByDate
public static bool GetFirstDayOfAccountingPeriod(Int32 ALedgerNumber, DateTime ADateInAPeriod, out DateTime AFirstDayOfPeriod)
{
TDBTransaction Transaction = null;
DateTime Result = DateTime.MinValue;
// Used by importing so the isolation level is serializable
DBAccess.GDBAccessObj.GetNewOrExistingAutoReadTransaction(IsolationLevel.Serializable, ref Transaction,
delegate
{
// Get the accounting periods for this ledger. The table will contain more than 12 rows.
// The start dates will be the correct day and month but may have been inserted for an arbitrary year when the table was first created.
// We are really only interested in the Day anyway
AAccountingPeriodTable periods = AAccountingPeriodAccess.LoadViaALedger(ALedgerNumber, Transaction);
DataView periodsView = new DataView(periods, "",
String.Format("{0} ASC", AAccountingPeriodTable.GetPeriodStartDateDBName()), DataViewRowState.CurrentRows);
AAccountingPeriodRow row = (AAccountingPeriodRow)periodsView[0].Row;
Result = new DateTime(ADateInAPeriod.Year, ADateInAPeriod.Month, row.PeriodStartDate.Day);
if (ADateInAPeriod.Day < row.PeriodStartDate.Day)
{
Result = Result.AddMonths(-1);
}
});
AFirstDayOfPeriod = Result;
return(Result != DateTime.MinValue);
}
/// <summary>
/// Get the start and end date of the given period in the given year
/// </summary>
public static bool GetStartAndEndDateOfPeriod(Int32 ALedgerNumber,
Int32 AYear,
Int32 APeriodNumber,
out DateTime APeriodStartDate,
out DateTime APeriodEndDate,
TDBTransaction ATransaction)
{
#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);
}
// ATransaction can be null
//else if (ATransaction == null)
//{
// throw new EFinanceSystemDBTransactionNullException(String.Format(Catalog.GetString(
// "Function:{0} - Database Transaction must not be NULL!"),
// Utilities.GetMethodName(true)));
//}
#endregion Validate Arguments
// invalid period
if (APeriodNumber == -1)
{
APeriodStartDate = DateTime.MinValue;
APeriodEndDate = DateTime.MaxValue;
return(false);
}
AAccountingPeriodTable AccPeriodTable = AAccountingPeriodAccess.LoadByPrimaryKey(ALedgerNumber, APeriodNumber, ATransaction);
#region Validate Data
if ((AccPeriodTable == null) || (AccPeriodTable.Count == 0))
{
throw new EFinanceSystemDataTableReturnedNoDataException(String.Format(Catalog.GetString(
"Function:{0} - Accounting Period data for period {1} in Ledger number {2} does not exist or could not be accessed!"),
Utilities.GetMethodName(true),
APeriodNumber,
ALedgerNumber));
}
#endregion Validate Data
AAccountingPeriodRow AccPeriodRow = (AAccountingPeriodRow)AccPeriodTable.Rows[0];
ALedgerTable LedgerTable = ALedgerAccess.LoadByPrimaryKey(ALedgerNumber, ATransaction);
Int32 currentYear = LedgerTable[0].CurrentFinancialYear;
Int32 yearsAgo = currentYear - AYear;
APeriodStartDate = AccPeriodRow.PeriodStartDate.AddYears(0 - yearsAgo);
APeriodEndDate = AccPeriodRow.PeriodEndDate.AddYears(0 - yearsAgo);
return(true);
}
public static bool GetAccountingYearPeriodByDate(Int32 ALedgerNumber,
DateTime ADate,
out Int32 AYearNumber,
out Int32 APeriodNumber)
{
Int32 CurrentFinancialYear;
//Set the year to return
Int32 YearNumber = FindFinancialYearByDate(ALedgerNumber, ADate);
AYearNumber = YearNumber;
if (AYearNumber == 99)
{
AYearNumber = 0;
APeriodNumber = 0;
return(false);
}
Int32 PeriodNumber = 0;
TDBTransaction transaction = null;
DBAccess.GDBAccessObj.GetNewOrExistingAutoReadTransaction(IsolationLevel.Serializable, ref transaction,
delegate
{
ALedgerTable LedgerTable = ALedgerAccess.LoadByPrimaryKey(ALedgerNumber, transaction);
CurrentFinancialYear = ((ALedgerRow)LedgerTable.Rows[0]).CurrentFinancialYear;
AAccountingPeriodTable AccPeriodTableTmp = new AAccountingPeriodTable();
AAccountingPeriodRow TemplateRow = AccPeriodTableTmp.NewRowTyped(false);
TemplateRow.LedgerNumber = ALedgerNumber;
TemplateRow.PeriodStartDate = ADate.AddYears(CurrentFinancialYear - YearNumber);
TemplateRow.PeriodEndDate = ADate.AddYears(CurrentFinancialYear - YearNumber);
StringCollection operators = StringHelper.InitStrArr(new string[] { "=", "<=", ">=" });
AAccountingPeriodTable AccountingPeriodTable = AAccountingPeriodAccess.LoadUsingTemplate(TemplateRow,
operators,
null,
transaction);
if (AccountingPeriodTable.Count == 0)
{
return;
}
AAccountingPeriodRow AccountingPeriodRow = (AAccountingPeriodRow)AccountingPeriodTable.Rows[0];
PeriodNumber = AccountingPeriodRow.AccountingPeriodNumber;
});
APeriodNumber = PeriodNumber;
return(true);
} // Get AccountingYear Period ByDate
public static bool CanDeleteCorporateExchangeRate(DateTime ADateEffectiveFrom, string AIntlCurrency, string ATransactionCurrency)
{
bool ReturnValue = true;
TDBTransaction ReadTransaction = new TDBTransaction();
TDataBase db = DBAccess.Connect("CanDeleteCorporateExchangeRate");
db.ReadTransaction(
ref ReadTransaction,
delegate
{
// get accounting period for when the exchange rate is effective (if it exists)
string Query = "SELECT * FROM a_accounting_period" +
" WHERE a_accounting_period.a_period_end_date_d >= '" + DataUtilities.DateToSQLString(ADateEffectiveFrom) + "'" +
" AND a_accounting_period.a_period_start_date_d <= '" + DataUtilities.DateToSQLString(ADateEffectiveFrom) + "'";
AAccountingPeriodTable AccountingPeriodTable = new AAccountingPeriodTable();
db.SelectDT(AccountingPeriodTable, Query, ReadTransaction);
// no accounting period if effective in a year other that the current year
if ((AccountingPeriodTable == null) || (AccountingPeriodTable.Rows.Count == 0))
{
return;
}
AAccountingPeriodRow AccountingPeriodRow = AccountingPeriodTable[0];
// search for batches for the found accounting period
string Query2 = "SELECT CASE WHEN EXISTS (" +
"SELECT * FROM a_batch, a_journal, a_ledger" +
" WHERE a_batch.a_date_effective_d <= '" + DataUtilities.DateToSQLString(
AccountingPeriodRow.PeriodEndDate) + "'" +
" AND a_batch.a_date_effective_d >= '" + DataUtilities.DateToSQLString(
AccountingPeriodRow.PeriodStartDate) + "'" +
" AND a_journal.a_ledger_number_i = a_batch.a_ledger_number_i" +
" AND a_journal.a_batch_number_i = a_batch.a_batch_number_i" +
" AND a_ledger.a_ledger_number_i = a_batch.a_ledger_number_i" +
" AND ((a_journal.a_transaction_currency_c = '" + ATransactionCurrency + "'" +
" AND a_ledger.a_intl_currency_c = '" + AIntlCurrency + "')" +
" OR (a_journal.a_transaction_currency_c = '" + AIntlCurrency + "'" +
" AND a_ledger.a_intl_currency_c = '" + ATransactionCurrency + "'))" +
") THEN 'TRUE'" +
" ELSE 'FALSE' END";
DataTable DT = db.SelectDT(Query2, "temp", ReadTransaction);
// a batch has been found
if ((DT != null) && (DT.Rows.Count > 0) && (DT.Rows[0][0].ToString() == "TRUE"))
{
ReturnValue = false;
return;
}
});
return(ReturnValue);
}
/// <summary>
///
/// </summary>
/// <param name="AContext"></param>
/// <param name="ARow"></param>
/// <param name="AVerificationResultCollection"></param>
public static void ValidateAccountingPeriod(object AContext, AAccountingPeriodRow ARow,
ref TVerificationResultCollection AVerificationResultCollection)
{
DataColumn ValidationColumn;
TVerificationResult VerificationResult;
// 'Period End Date' must be later than 'Period Start Date'
ValidationColumn = ARow.Table.Columns[AAccountingPeriodTable.ColumnPeriodEndDateId];
if (true)
{
VerificationResult = TDateChecks.FirstGreaterOrEqualThanSecondDate(ARow.PeriodEndDate, ARow.PeriodStartDate,
String.Empty, String.Empty,
AContext, ValidationColumn);
// Handle addition to/removal from TVerificationResultCollection
AVerificationResultCollection.Auto_Add_Or_AddOrRemove(AContext, VerificationResult);
}
}
/// <summary>
///
/// </summary>
/// <param name="AContext"></param>
/// <param name="ARow"></param>
/// <param name="AVerificationResultCollection"></param>
/// <param name="AValidationControlsDict"></param>
public static void ValidateAccountingPeriod(object AContext, AAccountingPeriodRow ARow,
ref TVerificationResultCollection AVerificationResultCollection, TValidationControlsDict AValidationControlsDict)
{
DataColumn ValidationColumn;
TValidationControlsData ValidationControlsData;
TVerificationResult VerificationResult;
// 'Period End Date' must be later than 'Period Start Date'
ValidationColumn = ARow.Table.Columns[AAccountingPeriodTable.ColumnPeriodEndDateId];
if (AValidationControlsDict.TryGetValue(ValidationColumn, out ValidationControlsData))
{
VerificationResult = TDateChecks.FirstGreaterOrEqualThanSecondDate(ARow.PeriodEndDate, ARow.PeriodStartDate,
ValidationControlsData.ValidationControlLabel, ValidationControlsData.SecondValidationControlLabel,
AContext, ValidationColumn, ValidationControlsData.ValidationControl);
// Handle addition to/removal from TVerificationResultCollection
AVerificationResultCollection.Auto_Add_Or_AddOrRemove(AContext, VerificationResult, ValidationColumn);
}
}
/// <summary>
/// get the start and end date of the given period in the current year
/// </summary>
public static bool GetStartAndEndDateOfPeriod(Int32 ALedgerNumber,
Int32 APeriodNumber,
out DateTime APeriodStartDate,
out DateTime APeriodEndDate,
TDBTransaction ATransaction)
{
// invalid period
if (APeriodNumber == -1)
{
APeriodStartDate = DateTime.MinValue;
APeriodEndDate = DateTime.MaxValue;
return(false);
}
AAccountingPeriodTable AccPeriodTable = AAccountingPeriodAccess.LoadByPrimaryKey(ALedgerNumber, APeriodNumber, ATransaction);
AAccountingPeriodRow AccPeriodRow = (AAccountingPeriodRow)AccPeriodTable.Rows[0];
APeriodStartDate = AccPeriodRow.PeriodStartDate;
APeriodEndDate = AccPeriodRow.PeriodEndDate;
return(true);
}
private void ValidateDataDetailsManual(AAccountingPeriodRow ARow)
{
if (FDuringSave)
{
AAccountingPeriodRow PeriodRow;
foreach (DataRow row in FMainDS.AAccountingPeriod.Rows)
{
PeriodRow = (AAccountingPeriodRow)row;
if (PeriodRow.AccountingPeriodNumber <= FNumberOfAccountingPeriods)
{
// only check gap to next record as otherwise messages would appear twice
ValidateRecord(PeriodRow, false, true);
}
}
}
else
{
// if not within save process then
ValidateRecord(ARow, true, true);
}
}
private static string BudgetRevisionYearName(int ALedgerNumber, int ABudgetRevisionYear)
{
int budgetYear = 0;
ALedgerTable LedgerTable = null;
AAccountingPeriodTable accPeriodTable = null;
TDBTransaction Transaction = null;
DBAccess.GDBAccessObj.GetNewOrExistingAutoReadTransaction(IsolationLevel.ReadCommitted,
TEnforceIsolationLevel.eilMinimum,
ref Transaction,
delegate
{
LedgerTable = ALedgerAccess.LoadByPrimaryKey(ALedgerNumber, Transaction);
accPeriodTable = AAccountingPeriodAccess.LoadByPrimaryKey(ALedgerNumber, 1, Transaction);
});
ALedgerRow ledgerRow = (ALedgerRow)LedgerTable.Rows[0];
AAccountingPeriodRow accPeriodRow = (AAccountingPeriodRow)accPeriodTable.Rows[0];
DateTime CurrentYearEnd = TAccountingPeriodsWebConnector.GetPeriodEndDate(ALedgerNumber,
ledgerRow.CurrentFinancialYear,
0,
ledgerRow.NumberOfAccountingPeriods);
budgetYear = ABudgetRevisionYear + CurrentYearEnd.Year - ledgerRow.CurrentFinancialYear;
if (budgetYear == accPeriodRow.PeriodStartDate.Year)
{
return("This");
}
else
{
return("Next");
}
}
public static DataTable GetAvailableGLYearEnds(Int32 ALedgerNumber,
System.Int32 ADiffPeriod,
bool AIncludeNextYear,
out String ADisplayMember, out String AValueMember)
{
//Create the table to populate the combobox
DataTable ReturnTable = null;
ADisplayMember = "YearEndDate";
AValueMember = "YearNumber";
string YearEnd = "YearEndDateLong";
DateTime YearEndDate;
int YearNumber;
bool NewTransaction = false;
TDBTransaction Transaction = DBAccess.GDBAccessObj.GetNewOrExistingTransaction(IsolationLevel.ReadCommitted, out NewTransaction);
ALedgerTable LedgerTable = ALedgerAccess.LoadByPrimaryKey(ALedgerNumber, Transaction);
AAccountingPeriodTable AccountingPeriods = AAccountingPeriodAccess.LoadViaALedger(ALedgerNumber, Transaction);
if (LedgerTable.Count < 1)
{
throw new Exception("Ledger " + ALedgerNumber + " not found");
}
ALedgerRow LedgerRow = (ALedgerRow)LedgerTable[0];
AccountingPeriods.DefaultView.RowFilter = String.Format("{0}={1}",
AAccountingPeriodTable.GetAccountingPeriodNumberDBName(),
LedgerRow.NumberOfAccountingPeriods);
//Get last period row
AAccountingPeriodRow periodRow = (AAccountingPeriodRow)AccountingPeriods.DefaultView[0].Row;
//Create the table to populate the combobox
ReturnTable = new DataTable();
ReturnTable.Columns.Add(AValueMember, typeof(System.Int32));
ReturnTable.Columns.Add(ADisplayMember, typeof(String));
ReturnTable.Columns.Add(YearEnd, typeof(String));
ReturnTable.PrimaryKey = new DataColumn[] {
ReturnTable.Columns[0]
};
//Add the current year to the table
YearNumber = LedgerRow.CurrentFinancialYear;
YearEndDate = periodRow.PeriodEndDate;
DataRow ResultRow = ReturnTable.NewRow();
ResultRow[0] = YearNumber;
ResultRow[1] = YearEndDate.ToShortDateString();
ResultRow[2] = YearEndDate.ToLongDateString();
ReturnTable.Rows.Add(ResultRow);
//Retrieve all previous years
string sql =
String.Format("SELECT DISTINCT {0} AS batchYear" +
" FROM PUB_{1}" +
" WHERE {2} = {3} And {0} < {4}" +
" ORDER BY 1 DESC",
ABatchTable.GetBatchYearDBName(),
ABatchTable.GetTableDBName(),
ABatchTable.GetLedgerNumberDBName(),
ALedgerNumber,
YearNumber);
DataTable BatchYearTable = DBAccess.GDBAccessObj.SelectDT(sql, "BatchYearTable", Transaction);
BatchYearTable.DefaultView.Sort = String.Format("batchYear DESC");
try
{
foreach (DataRowView row in BatchYearTable.DefaultView)
{
DataRow currentBatchYearRow = row.Row;
Int32 currentBatchYear = Convert.ToInt32(currentBatchYearRow[0]);
if (YearNumber != currentBatchYear)
{
YearNumber -= 1;
YearEndDate = DecrementYear(YearEndDate);
if (YearNumber != currentBatchYear)
{
//Gap in year numbers
throw new Exception(String.Format(Catalog.GetString("Year {0} not found for Ledger {1}"),
YearNumber,
ALedgerNumber));
}
}
DataRow ResultRow2 = ReturnTable.NewRow();
ResultRow2[0] = YearNumber;
ResultRow2[1] = YearEndDate.ToShortDateString();
ReturnTable.Rows.Add(ResultRow2);
}
}
catch (Exception ex)
{
TLogging.Log(ex.ToString());
//Do nothing
}
if (NewTransaction)
{
DBAccess.GDBAccessObj.RollbackTransaction();
}
return(ReturnTable);
}
/// <summary>
/// using the calendar of the ledger to determine the financial period at the given date.
/// </summary>
/// <param name="ALedgerNumber"></param>
/// <param name="ADateToTest"></param>
/// <param name="AFinancialYear"></param>
/// <param name="AFinancialPeriod"></param>
/// <param name="ATransaction"></param>
/// <param name="ADoFixDate">do you want to fix the date if it is outside the open periods;
/// if the date is outside of the open periods, the date will be changed to the most appropriate date:
/// if the original date is before the posting period, the first available date in the posting period will be returned,
/// otherwise the last possible date</param>
/// <returns>false if date needed to change</returns>
public static bool GetLedgerDatePostingPeriod(Int32 ALedgerNumber,
ref DateTime ADateToTest,
out Int32 AFinancialYear,
out Int32 AFinancialPeriod,
TDBTransaction ATransaction,
bool ADoFixDate)
{
bool RetVal = false;
AFinancialPeriod = -1;
AFinancialYear = -1;
AAccountingPeriodRow currentPeriodRow = null;
AAccountingPeriodRow lastAllowedPeriodRow = null;
AAccountingPeriodTable table = AAccountingPeriodAccess.LoadViaALedger(ALedgerNumber, ATransaction);
ALedgerTable LedgerTable = ALedgerAccess.LoadByPrimaryKey(ALedgerNumber, ATransaction);
try
{
if (LedgerTable.Count < 1)
{
throw new Exception("Ledger " + ALedgerNumber + " not found");
}
int ACurrentPeriod = LedgerTable[0].CurrentPeriod;
int AAllowedForwardPeriod = ACurrentPeriod + LedgerTable[0].NumberFwdPostingPeriods;
foreach (AAccountingPeriodRow row in table.Rows)
{
if (row.AccountingPeriodNumber == ACurrentPeriod)
{
currentPeriodRow = row;
}
if (row.AccountingPeriodNumber == AAllowedForwardPeriod)
{
lastAllowedPeriodRow = row;
}
if ((row.PeriodStartDate <= ADateToTest) && (ADateToTest <= row.PeriodEndDate))
{
// check if this period is either the current period or one of the forward posting periods
if (LedgerTable.Count == 1)
{
AFinancialPeriod = row.AccountingPeriodNumber;
//This is the number of the period to which the "DateToTest" belongs
//This can be
// 1.) before the current period or in the last financial year
// => FIX Date to be the first day of the current period
// 2.) greater oder eqal currentperiod but within AllowedForwardperiod = no FIX required
// 3.) after the allowed Forward period or even in a future financial year: = FIX Date to be the last day of the last allowed forward period
if ((AFinancialPeriod >= ACurrentPeriod) && (AFinancialPeriod <= AAllowedForwardPeriod))
{
AFinancialYear = LedgerTable[0].CurrentFinancialYear;
RetVal = true;
break;
}
}
}
}
if (ADoFixDate && !RetVal)
{
if (ADateToTest < currentPeriodRow.PeriodStartDate)
{
ADateToTest = currentPeriodRow.PeriodStartDate;
AFinancialYear = LedgerTable[0].CurrentFinancialYear;
AFinancialPeriod = currentPeriodRow.AccountingPeriodNumber;
}
else
{
if (lastAllowedPeriodRow == null)
{
lastAllowedPeriodRow = table[table.Rows.Count - 1];
}
if (ADateToTest > lastAllowedPeriodRow.PeriodEndDate)
{
ADateToTest = lastAllowedPeriodRow.PeriodEndDate;
AFinancialYear = LedgerTable[0].CurrentFinancialYear;
AFinancialPeriod = lastAllowedPeriodRow.AccountingPeriodNumber;
}
}
}
}
catch (Exception Ex)
{
TLogging.Log("Error in GetLedgerDatePostingPeriod: " + Ex.Message);
throw;
}
return(RetVal);
}
private static Int32 FindFinancialYearByDate(Int32 ALedgerNumber,
DateTime ADate)
{
Int32 yearDateBelongsTo;
DateTime yearStartDate = DateTime.Today;
bool newTransaction = false;
TDBTransaction Transaction = DBAccess.GDBAccessObj.GetNewOrExistingTransaction(IsolationLevel.Serializable, out newTransaction);
ALedgerTable LedgerTable = ALedgerAccess.LoadByPrimaryKey(ALedgerNumber, Transaction);
if ((LedgerTable == null) || (LedgerTable.Count == 0))
{
if (newTransaction)
{
DBAccess.GDBAccessObj.RollbackTransaction();
}
return(99);
}
ALedgerRow LedgerRow = (ALedgerRow)LedgerTable.Rows[0];
yearDateBelongsTo = LedgerRow.CurrentFinancialYear;
AAccountingPeriodTable AccPeriodTable = AAccountingPeriodAccess.LoadViaALedger(ALedgerNumber, Transaction);
if ((AccPeriodTable == null) || (AccPeriodTable.Count == 0))
{
if (newTransaction)
{
DBAccess.GDBAccessObj.RollbackTransaction();
}
return(99);
}
//Find earliest start date (don't assume PK order)
AAccountingPeriodRow AccPeriodRow = null;
for (int i = 0; i < AccPeriodTable.Count; i++)
{
DateTime currentStartDate;
AccPeriodRow = (AAccountingPeriodRow)AccPeriodTable.Rows[i];
currentStartDate = AccPeriodRow.PeriodStartDate;
if (i > 0)
{
if (yearStartDate > currentStartDate)
{
yearStartDate = currentStartDate;
}
}
else
{
yearStartDate = currentStartDate;
}
}
//Find the correct year
while (ADate < yearStartDate)
{
ADate = ADate.AddYears(1);
yearDateBelongsTo--;
}
if (newTransaction)
{
DBAccess.GDBAccessObj.RollbackTransaction();
}
//Set the year to return
return(yearDateBelongsTo);
}
public static bool GenerateHOSAFiles(int ALedgerNumber,
int APeriodNumber,
int AIchNumber,
string ACostCentre,
String ACurrencySelect,
string AFileName,
out TVerificationResultCollection AVerificationResult
)
{
bool Successful = false;
GLBatchTDS MainDS = new GLBatchTDS();
TVerificationResultCollection VerificationResult = new TVerificationResultCollection();
AVerificationResult = VerificationResult;
TDBTransaction DBTransaction = null;
DBAccess.GDBAccessObj.GetNewOrExistingAutoReadTransaction(IsolationLevel.ReadCommitted,
TEnforceIsolationLevel.eilMinimum,
ref DBTransaction,
delegate
{
//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,
TableForExport,
VerificationResult);
}
/* 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
//TODO: Calendar vs Financial Date Handling - Check if number of ledger periods needs to be used here and not 12 assumed
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 VerificationResult);
Successful = true;
}); // Get NewOrExisting AutoReadTransaction
return(Successful);
} // Generate HOSA Files
private void ValidateRecord(AAccountingPeriodRow ARow, Boolean ACheckGapToPrevious, Boolean ACheckGapToNext)
{
TVerificationResultCollection VerificationResultCollection = FPetraUtilsObject.VerificationResultCollection;
DataColumn ValidationColumn;
TValidationControlsData ValidationControlsData;
TVerificationResult VerificationResult = null;
AAccountingPeriodRow OtherRow;
DataRow OtherDataRow;
string CurrentDateRangeErrorCode;
if (FDuringSave)
{
CurrentDateRangeErrorCode = PetraErrorCodes.ERR_PERIOD_DATE_RANGE;
}
else
{
CurrentDateRangeErrorCode = PetraErrorCodes.ERR_PERIOD_DATE_RANGE_WARNING;
}
// first run through general checks related to the current AccountingPeriod row
TSharedFinanceValidation_GLSetup.ValidateAccountingPeriod(this, ARow, ref VerificationResultCollection,
FPetraUtilsObject.ValidationControlsDict);
// the following checks need to be done in this ManualCode file as they involve other rows on the screen:
// check that there is no gap to previous accounting period
if (ACheckGapToPrevious)
{
ValidationColumn = ARow.Table.Columns[AAccountingPeriodTable.ColumnPeriodStartDateId];
if (FPetraUtilsObject.ValidationControlsDict.TryGetValue(ValidationColumn, out ValidationControlsData))
{
if (!ARow.IsPeriodStartDateNull()
&& (ARow.PeriodStartDate != DateTime.MinValue))
{
OtherDataRow = FMainDS.AAccountingPeriod.Rows.Find(
new string[] { FLedgerNumber.ToString(), (ARow.AccountingPeriodNumber - 1).ToString() });
if (OtherDataRow != null)
{
OtherRow = (AAccountingPeriodRow)OtherDataRow;
if (OtherRow.PeriodEndDate != ARow.PeriodStartDate.Date.AddDays(-1))
{
VerificationResult = new TScreenVerificationResult(new TVerificationResult(this,
ErrorCodes.GetErrorInfo(CurrentDateRangeErrorCode,
new string[] { (ARow.AccountingPeriodNumber - 1).ToString() })),
ValidationColumn, ValidationControlsData.ValidationControl);
}
else
{
VerificationResult = null;
}
// Handle addition/removal to/from TVerificationResultCollection
VerificationResultCollection.Auto_Add_Or_AddOrRemove(this, VerificationResult, ValidationColumn);
}
}
}
}
// check that there is no gap to next accounting period
if (ACheckGapToNext)
{
ValidationColumn = ARow.Table.Columns[AAccountingPeriodTable.ColumnPeriodEndDateId];
if (FPetraUtilsObject.ValidationControlsDict.TryGetValue(ValidationColumn, out ValidationControlsData))
{
if (!ARow.IsPeriodEndDateNull()
&& (ARow.PeriodEndDate != DateTime.MinValue))
{
OtherDataRow = FMainDS.AAccountingPeriod.Rows.Find(
new string[] { FLedgerNumber.ToString(), (ARow.AccountingPeriodNumber + 1).ToString() });
if (OtherDataRow != null)
{
OtherRow = (AAccountingPeriodRow)OtherDataRow;
if (OtherRow.PeriodStartDate != ARow.PeriodEndDate.Date.AddDays(1))
{
VerificationResult = new TScreenVerificationResult(new TVerificationResult(this,
ErrorCodes.GetErrorInfo(CurrentDateRangeErrorCode,
new string[] { (ARow.AccountingPeriodNumber).ToString() })),
ValidationColumn, ValidationControlsData.ValidationControl);
}
else
{
VerificationResult = null;
}
// Handle addition/removal to/from TVerificationResultCollection
VerificationResultCollection.Auto_Add_Or_AddOrRemove(this, VerificationResult, ValidationColumn);
}
}
}
}
}
private void ValidateRecord(AAccountingPeriodRow ARow, Boolean ACheckGapToPrevious, Boolean ACheckGapToNext)
{
TVerificationResultCollection VerificationResultCollection = FPetraUtilsObject.VerificationResultCollection;
DataColumn ValidationColumn;
TValidationControlsData ValidationControlsData;
TVerificationResult VerificationResult = null;
AAccountingPeriodRow OtherRow;
DataRow OtherDataRow;
string CurrentDateRangeErrorCode;
if (FDuringSave)
{
CurrentDateRangeErrorCode = PetraErrorCodes.ERR_PERIOD_DATE_RANGE;
}
else
{
CurrentDateRangeErrorCode = PetraErrorCodes.ERR_PERIOD_DATE_RANGE_WARNING;
}
// first run through general checks related to the current AccountingPeriod row
TSharedFinanceValidation_GLSetup.ValidateAccountingPeriod(this, ARow, ref VerificationResultCollection,
FPetraUtilsObject.ValidationControlsDict);
// the following checks need to be done in this ManualCode file as they involve other rows on the screen:
// check that there is no gap to previous accounting period
if (ACheckGapToPrevious)
{
ValidationColumn = ARow.Table.Columns[AAccountingPeriodTable.ColumnPeriodStartDateId];
if (FPetraUtilsObject.ValidationControlsDict.TryGetValue(ValidationColumn, out ValidationControlsData))
{
if (!ARow.IsPeriodStartDateNull() &&
(ARow.PeriodStartDate != DateTime.MinValue))
{
OtherDataRow = FMainDS.AAccountingPeriod.Rows.Find(
new string[] { FLedgerNumber.ToString(), (ARow.AccountingPeriodNumber - 1).ToString() });
if (OtherDataRow != null)
{
OtherRow = (AAccountingPeriodRow)OtherDataRow;
if (OtherRow.PeriodEndDate != ARow.PeriodStartDate.Date.AddDays(-1))
{
VerificationResult = new TScreenVerificationResult(new TVerificationResult(this,
ErrorCodes.GetErrorInfo(CurrentDateRangeErrorCode,
new string[] { (ARow.AccountingPeriodNumber - 1).ToString() })),
ValidationColumn, ValidationControlsData.ValidationControl);
}
else
{
VerificationResult = null;
}
// Handle addition/removal to/from TVerificationResultCollection
VerificationResultCollection.Auto_Add_Or_AddOrRemove(this, VerificationResult, ValidationColumn);
}
}
}
}
// check that there is no gap to next accounting period
if (ACheckGapToNext)
{
ValidationColumn = ARow.Table.Columns[AAccountingPeriodTable.ColumnPeriodEndDateId];
if (FPetraUtilsObject.ValidationControlsDict.TryGetValue(ValidationColumn, out ValidationControlsData))
{
if (!ARow.IsPeriodEndDateNull() &&
(ARow.PeriodEndDate != DateTime.MinValue))
{
OtherDataRow = FMainDS.AAccountingPeriod.Rows.Find(
new string[] { FLedgerNumber.ToString(), (ARow.AccountingPeriodNumber + 1).ToString() });
if (OtherDataRow != null)
{
OtherRow = (AAccountingPeriodRow)OtherDataRow;
if (OtherRow.PeriodStartDate != ARow.PeriodEndDate.Date.AddDays(1))
{
VerificationResult = new TScreenVerificationResult(new TVerificationResult(this,
ErrorCodes.GetErrorInfo(CurrentDateRangeErrorCode,
new string[] { (ARow.AccountingPeriodNumber).ToString() })),
ValidationColumn, ValidationControlsData.ValidationControl);
}
else
{
VerificationResult = null;
}
// Handle addition/removal to/from TVerificationResultCollection
VerificationResultCollection.Auto_Add_Or_AddOrRemove(this, VerificationResult, ValidationColumn);
}
}
}
}
}
public static DataTable GetAvailableGLYearsHOSA(Int32 ALedgerNumber,
out String ADisplayMember,
out String AValueMember,
out String ADescriptionMember)
{
DateTime YearEndDate;
int YearNumber;
ADisplayMember = "YearEndDate";
AValueMember = "YearNumber";
ADescriptionMember = "YearEndDateLong";
DataTable BatchTable = null;
TDBTransaction transaction = null;
DBAccess.GDBAccessObj.GetNewOrExistingAutoReadTransaction(IsolationLevel.ReadCommitted,
TEnforceIsolationLevel.eilMinimum,
ref transaction,
delegate
{
ALedgerTable LedgerTable = ALedgerAccess.LoadByPrimaryKey(ALedgerNumber, transaction);
AAccountingPeriodTable AccountingPeriods = AAccountingPeriodAccess.LoadViaALedger(ALedgerNumber, transaction);
if (LedgerTable.Rows.Count < 1)
{
return;
}
ALedgerRow LedgerRow = (ALedgerRow)LedgerTable[0];
AccountingPeriods.DefaultView.RowFilter = String.Format("{0}={1}",
AAccountingPeriodTable.GetAccountingPeriodNumberDBName(),
LedgerRow.NumberOfAccountingPeriods);
//Get last period row
AAccountingPeriodRow periodRow = (AAccountingPeriodRow)AccountingPeriods.DefaultView[0].Row;
//Create the table to populate the combobox
BatchTable = new DataTable();
BatchTable.Columns.Add("YearNumber", typeof(System.Int32));
BatchTable.Columns.Add("YearEndDate", typeof(String));
BatchTable.Columns.Add("YearEndDateLong", typeof(String));
BatchTable.PrimaryKey = new DataColumn[] { BatchTable.Columns[0] };
//Add the current year to the table
YearNumber = LedgerRow.CurrentFinancialYear;
YearEndDate = periodRow.PeriodEndDate;
DataRow ResultRow = BatchTable.NewRow();
ResultRow[0] = YearNumber;
ResultRow[1] = YearEndDate.ToShortDateString();
ResultRow[2] = YearEndDate.ToLongDateString();
BatchTable.Rows.Add(ResultRow);
//Retrieve all previous years
string sql =
String.Format("SELECT DISTINCT {0} AS batchYear" +
" FROM PUB_{1}" +
" WHERE {2} = {3} And {0} < {4}" +
" ORDER BY 1 DESC",
ABatchTable.GetBatchYearDBName(),
ABatchTable.GetTableDBName(),
ABatchTable.GetLedgerNumberDBName(),
ALedgerNumber,
YearNumber);
DataTable BatchYearTable = DBAccess.GDBAccessObj.SelectDT(sql, "BatchYearTable", transaction);
BatchYearTable.DefaultView.Sort = String.Format("batchYear DESC");
foreach (DataRowView row in BatchYearTable.DefaultView)
{
DataRow currentBatchYearRow = row.Row;
Int32 currentBatchYear = (Int32)currentBatchYearRow[0];
if (YearNumber != currentBatchYear)
{
YearNumber -= 1;
YearEndDate = DecrementYear(YearEndDate);
if (YearNumber != currentBatchYear)
{
//Gap in year numbers
throw new Exception(String.Format(Catalog.GetString("Year {0} not found for Ledger {1}"),
YearNumber,
ALedgerNumber));
}
}
DataRow ResultRow2 = BatchTable.NewRow();
ResultRow2[0] = YearNumber;
ResultRow2[1] = YearEndDate.ToShortDateString();
ResultRow2[2] = YearEndDate.ToLongDateString();
BatchTable.Rows.Add(ResultRow2);
} // foreach
}); // Get NewOrExisting AutoReadTransaction
return(BatchTable);
} // Get Available GLYears HOSA
/// <summary>
/// using the calendar of the ledger to determine the financial period at the given date.
/// </summary>
/// <param name="ALedgerNumber"></param>
/// <param name="ADateToTest"></param>
/// <param name="AFinancialYear"></param>
/// <param name="AFinancialPeriod"></param>
/// <param name="ATransaction"></param>
/// <param name="ADoFixDate">do you want to fix the date if it is outside the open periods;
/// if the date is outside of the open periods, the date will be changed to the most appropriate date:
/// if the original date is before the posting period, the first available date in the posting period will be returned,
/// otherwise the last possible date</param>
/// <returns>false if date needed to change</returns>
public static bool GetLedgerDatePostingPeriod(Int32 ALedgerNumber,
ref DateTime ADateToTest,
out Int32 AFinancialYear,
out Int32 AFinancialPeriod,
TDBTransaction ATransaction,
bool ADoFixDate)
{
#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);
}
else if (ATransaction == null)
{
throw new EFinanceSystemDBTransactionNullException(String.Format(Catalog.GetString(
"Function:{0} - Database Transaction must not be NULL!"),
Utilities.GetMethodName(true)));
}
#endregion Validate Arguments
bool RetVal = false;
AFinancialPeriod = -1;
AFinancialYear = -1;
AAccountingPeriodRow currentPeriodRow = null;
AAccountingPeriodRow lastAllowedPeriodRow = null;
ALedgerTable LedgerTable = ALedgerAccess.LoadByPrimaryKey(ALedgerNumber, ATransaction);
AAccountingPeriodTable AccountingPeriodTable = AAccountingPeriodAccess.LoadViaALedger(ALedgerNumber, ATransaction);
#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));
}
else if ((AccountingPeriodTable == null) || (AccountingPeriodTable.Count == 0))
{
throw new EFinanceSystemDataTableReturnedNoDataException(String.Format(Catalog.GetString(
"Function:{0} - Accounting Period data for Ledger number {1} does not exist or could not be accessed!"),
Utilities.GetMethodName(true),
ALedgerNumber));
}
#endregion Validate Data
try
{
int aCurrentPeriod = LedgerTable[0].CurrentPeriod;
int anAllowedForwardPeriod = aCurrentPeriod + LedgerTable[0].NumberFwdPostingPeriods;
foreach (AAccountingPeriodRow row in AccountingPeriodTable.Rows)
{
if (row.AccountingPeriodNumber == aCurrentPeriod)
{
currentPeriodRow = row;
}
if (row.AccountingPeriodNumber == anAllowedForwardPeriod)
{
lastAllowedPeriodRow = row;
}
if ((row.PeriodStartDate <= ADateToTest) && (ADateToTest <= row.PeriodEndDate))
{
// check if this period is either the current period or one of the forward posting periods
if (LedgerTable.Count == 1)
{
AFinancialPeriod = row.AccountingPeriodNumber;
//This is the number of the period to which the "DateToTest" belongs
//This can be
// 1.) before the current period or in the last financial year
// => FIX Date to be the first day of the current period
// 2.) greater oder eqal currentperiod but within AllowedForwardperiod = no FIX required
// 3.) after the allowed Forward period or even in a future financial year: = FIX Date to be the last day of the last allowed forward period
if ((AFinancialPeriod >= aCurrentPeriod) && (AFinancialPeriod <= anAllowedForwardPeriod))
{
AFinancialYear = LedgerTable[0].CurrentFinancialYear;
RetVal = true;
break;
}
}
}
}
if (ADoFixDate && !RetVal)
{
if (ADateToTest < currentPeriodRow.PeriodStartDate)
{
ADateToTest = currentPeriodRow.PeriodStartDate;
AFinancialYear = LedgerTable[0].CurrentFinancialYear;
AFinancialPeriod = currentPeriodRow.AccountingPeriodNumber;
}
else
{
if (lastAllowedPeriodRow == null)
{
lastAllowedPeriodRow = AccountingPeriodTable[AccountingPeriodTable.Rows.Count - 1];
}
if (ADateToTest > lastAllowedPeriodRow.PeriodEndDate)
{
ADateToTest = lastAllowedPeriodRow.PeriodEndDate;
AFinancialYear = LedgerTable[0].CurrentFinancialYear;
AFinancialPeriod = lastAllowedPeriodRow.AccountingPeriodNumber;
}
}
}
}
catch (Exception ex)
{
TLogging.Log(String.Format("Method:{0} - Unexpected error!{1}{1}{2}",
Utilities.GetMethodSignature(),
Environment.NewLine,
ex.Message));
throw ex;
}
return(RetVal);
}
/// <summary>
///
/// </summary>
/// <param name="AContext"></param>
/// <param name="ARow"></param>
/// <param name="AVerificationResultCollection"></param>
/// <param name="AValidationControlsDict"></param>
public static void ValidateAccountingPeriod(object AContext, AAccountingPeriodRow ARow,
ref TVerificationResultCollection AVerificationResultCollection, TValidationControlsDict AValidationControlsDict)
{
DataColumn ValidationColumn;
TValidationControlsData ValidationControlsData;
TVerificationResult VerificationResult;
// 'Period End Date' must be later than 'Period Start Date'
ValidationColumn = ARow.Table.Columns[AAccountingPeriodTable.ColumnPeriodEndDateId];
if (AValidationControlsDict.TryGetValue(ValidationColumn, out ValidationControlsData))
{
VerificationResult = TDateChecks.FirstGreaterOrEqualThanSecondDate(ARow.PeriodEndDate, ARow.PeriodStartDate,
ValidationControlsData.ValidationControlLabel, ValidationControlsData.SecondValidationControlLabel,
AContext, ValidationColumn, ValidationControlsData.ValidationControl);
// Handle addition to/removal from TVerificationResultCollection
AVerificationResultCollection.Auto_Add_Or_AddOrRemove(AContext, VerificationResult, ValidationColumn);
}
}
/// <summary>
/// Imports currency exchange rates, daily and corporate,
/// from a one-of-two-styles formatted CSV file
/// </summary>
/// <param name="AExchangeRDT">Daily or Corporate exchange rate table</param>
/// <param name="ADataFilename">The .CSV file to process</param>
/// <param name="ACSVSeparator"></param>
/// <param name="ANumberFormat"></param>
/// <param name="ADateFormat"></param>
/// <param name="AImportMode">Daily or Corporate</param>
/// <param name="AResultCollection">A validation collection to which errors will be added</param>
/// <returns>The number of rows that were actually imported. Rows that duplicate existing rows do not count.
/// This is usually because this is an attempt to import again after a failed previous attempt.</returns>
private static int ImportCurrencyExRatesFromCSV(TTypedDataTable AExchangeRDT,
string ADataFilename,
string ACSVSeparator,
string ANumberFormat,
string ADateFormat,
string AImportMode,
TVerificationResultCollection AResultCollection)
{
// Keep a list of errors/warnings and severity
List <Tuple <string, TResultSeverity> > InvalidRows = new List <Tuple <string, TResultSeverity> >();
// keep a variable that becomes true if any row has an invalid column count, so we can show a helpful message
bool InvalidColumnCount = false;
// Check our method parameters
if ((AImportMode != "Corporate") && (AImportMode != "Daily"))
{
throw new ArgumentException("Invalid value '" + AImportMode + "' for mode argument: Valid values are Corporate and Daily");
}
else if ((AImportMode == "Corporate") && !(AExchangeRDT is ACorporateExchangeRateTable))
{
throw new ArgumentException("Invalid type of exchangeRateDT argument for mode: 'Corporate'. Needs to be: ACorporateExchangeRateTable");
}
else if ((AImportMode == "Daily") && !(AExchangeRDT is ADailyExchangeRateTable))
{
throw new ArgumentException("Invalid type of exchangeRateDT argument for mode: 'Daily'. Needs to be: ADailyExchangeRateTable");
}
bool IsShortFileFormat;
int x, y;
// To store the From and To currencies
// Use an array to store these to make for easy
// inverting of the two currencies when calculating
// the inverse value.
string[] Currencies = new string[2];
Type DataTableType;
int RowsImported = 0;
// This table will tell us the base currencies used by the current set of ledgers
ALedgerTable ledgers = TRemote.MFinance.Setup.WebConnectors.GetAvailableLedgers();
List <string> allBaseCurrencies = new List <string>();
DateTime maxRecommendedEffectiveDate = DateTime.MaxValue;
DateTime minRecommendedEffectiveDate = DateTime.MinValue;
int preferredPeriodStartDay = 0;
// Use the ledger table rows to get a list of base currencies and current period end dates
for (int i = 0; i < ledgers.Rows.Count; i++)
{
ALedgerRow ledger = (ALedgerRow)ledgers.Rows[i];
string code = ledger.BaseCurrency;
if (ledger.LedgerStatus == true)
{
if (allBaseCurrencies.Contains(code) == false)
{
allBaseCurrencies.Add(code);
}
DataTable AccountingPeriods = TDataCache.TMFinance.GetCacheableFinanceTable(TCacheableFinanceTablesEnum.AccountingPeriodList,
ledger.LedgerNumber);
AAccountingPeriodRow currentPeriodRow = (AAccountingPeriodRow)AccountingPeriods.Rows.Find(new object[] { ledger.LedgerNumber,
ledger.CurrentPeriod });
AAccountingPeriodRow forwardPeriodRow = (AAccountingPeriodRow)AccountingPeriods.Rows.Find(
new object[] { ledger.LedgerNumber,
ledger.CurrentPeriod +
ledger.NumberFwdPostingPeriods });
if ((forwardPeriodRow != null) &&
((maxRecommendedEffectiveDate == DateTime.MaxValue) || (forwardPeriodRow.PeriodEndDate > maxRecommendedEffectiveDate)))
{
maxRecommendedEffectiveDate = forwardPeriodRow.PeriodEndDate;
}
if ((currentPeriodRow != null) &&
((minRecommendedEffectiveDate == DateTime.MinValue) || (currentPeriodRow.PeriodStartDate > minRecommendedEffectiveDate)))
{
minRecommendedEffectiveDate = currentPeriodRow.PeriodStartDate;
}
if ((currentPeriodRow != null) && (preferredPeriodStartDay == 0))
{
preferredPeriodStartDay = currentPeriodRow.PeriodStartDate.Day;
}
else if ((currentPeriodRow != null) && (currentPeriodRow.PeriodStartDate.Day != preferredPeriodStartDay))
{
preferredPeriodStartDay = -1;
}
}
}
// This will tell us the complete list of all available currencies
ACurrencyTable allCurrencies = new ACurrencyTable();
DataTable CacheDT = TDataCache.GetCacheableDataTableFromCache("CurrencyCodeList", String.Empty, null, out DataTableType);
allCurrencies.Merge(CacheDT);
allCurrencies.CaseSensitive = true;
// Start reading the file
using (StreamReader DataFile = new StreamReader(ADataFilename, System.Text.Encoding.Default))
{
string ThousandsSeparator = (ANumberFormat == TDlgSelectCSVSeparator.NUMBERFORMAT_AMERICAN ? "," : ".");
string DecimalSeparator = (ANumberFormat == TDlgSelectCSVSeparator.NUMBERFORMAT_AMERICAN ? "." : ",");
CultureInfo MyCultureInfoDate = new CultureInfo("en-GB");
MyCultureInfoDate.DateTimeFormat.ShortDatePattern = ADateFormat;
// TODO: disconnect the grid from the datasource to avoid flickering?
string FileNameWithoutExtension = Path.GetFileNameWithoutExtension(ADataFilename).ToUpper();
if ((FileNameWithoutExtension.IndexOf("_") == 3) &&
(FileNameWithoutExtension.LastIndexOf("_") == 3) &&
(FileNameWithoutExtension.Length == 7))
{
// File name format assumed to be like this: USD_HKD.csv
IsShortFileFormat = true;
Currencies = FileNameWithoutExtension.Split(new char[] { '_' });
}
else
{
IsShortFileFormat = false;
}
int LineNumber = 0;
while (!DataFile.EndOfStream)
{
string Line = DataFile.ReadLine();
LineNumber++;
// See if the first line is a special case??
if (LineNumber == 1)
{
// see if the first line is a text header - look for digits
// A valid header would look like: From,To,Date,Rate
bool bFoundDigit = false;
for (int i = 0; i < Line.Length; i++)
{
char c = Line[i];
if ((c >= '0') && (c <= '9'))
{
bFoundDigit = true;
break;
}
}
if (!bFoundDigit)
{
// No digits so we will assume the line is a header
continue;
}
}
//Convert separator to a char
char Sep = ACSVSeparator[0];
//Turn current line into string array of column values
string[] CsvColumns = Line.Split(Sep);
int NumCols = CsvColumns.Length;
// Do we have the correct number of columns?
int minColCount = IsShortFileFormat ? 2 : 4;
int maxColCount = (AImportMode == "Daily") ? minColCount + 1 : minColCount;
if ((NumCols < minColCount) || (NumCols > maxColCount))
{
// raise an error
string resultText = String.Format(Catalog.GetPluralString(
"Line {0}: contains 1 column", "Line {0}: contains {1} columns", NumCols, true),
LineNumber, NumCols.ToString());
InvalidRows.Add(new Tuple <string, TResultSeverity>(resultText, TResultSeverity.Resv_Critical));
InvalidColumnCount = true;
continue;
}
if (!IsShortFileFormat)
{
//Read the values for the current line
//From currency
Currencies[0] = StringHelper.GetNextCSV(ref Line, ACSVSeparator, false, true).ToString().ToUpper();
//To currency
Currencies[1] = StringHelper.GetNextCSV(ref Line, ACSVSeparator, false, true).ToString().ToUpper();
}
// Perform validation on the From and To currencies at this point!!
if ((allCurrencies.Rows.Find(Currencies[0]) == null) && (allCurrencies.Rows.Find(Currencies[1]) == null))
{
// raise an error
string resultText = String.Format(Catalog.GetString(
"Line {0}: invalid currency codes ({1} and {2})"), LineNumber.ToString(), Currencies[0], Currencies[1]);
InvalidRows.Add(new Tuple <string, TResultSeverity>(resultText, TResultSeverity.Resv_Critical));
continue;
}
else if (allCurrencies.Rows.Find(Currencies[0]) == null)
{
// raise an error
string resultText = String.Format(Catalog.GetString(
"Line {0}: invalid currency code ({1})"), LineNumber.ToString(), Currencies[0]);
InvalidRows.Add(new Tuple <string, TResultSeverity>(resultText, TResultSeverity.Resv_Critical));
continue;
}
else if (allCurrencies.Rows.Find(Currencies[1]) == null)
{
// raise an error
string resultText = String.Format(Catalog.GetString(
"Line {0}: invalid currency code ({1})"), LineNumber.ToString(), Currencies[1]);
InvalidRows.Add(new Tuple <string, TResultSeverity>(resultText, TResultSeverity.Resv_Critical));
continue;
}
if ((allBaseCurrencies.Contains(Currencies[0]) == false) && (allBaseCurrencies.Contains(Currencies[1]) == false))
{
//raise a non-critical error
string resultText = String.Format(Catalog.GetString(
"Line {0}: Warning: One of '{1}' and '{2}' should be a base currency in one of the active ledgers."),
LineNumber.ToString(), Currencies[0], Currencies[1]);
InvalidRows.Add(new Tuple <string, TResultSeverity>(resultText, TResultSeverity.Resv_Noncritical));
}
// Date parsing as in Petra 2.x instead of using XML date format!!!
string DateEffectiveStr = StringHelper.GetNextCSV(ref Line, ACSVSeparator, false, true).Replace("\"", String.Empty);
DateTime DateEffective;
if (!DateTime.TryParse(DateEffectiveStr, MyCultureInfoDate, DateTimeStyles.None, out DateEffective))
{
// raise an error
string resultText = String.Format(Catalog.GetString(
"Line {0}: invalid date ({1})"), LineNumber.ToString(), DateEffectiveStr);
InvalidRows.Add(new Tuple <string, TResultSeverity>(resultText, TResultSeverity.Resv_Critical));
continue;
}
if (DateEffective > maxRecommendedEffectiveDate)
{
// raise a warning
string resultText = String.Format(Catalog.GetString(
"Line {0}: Warning: The date '{1}' is after the latest forwarding period of any active ledger"),
LineNumber.ToString(), DateEffectiveStr);
InvalidRows.Add(new Tuple <string, TResultSeverity>(resultText, TResultSeverity.Resv_Noncritical));
}
else if (DateEffective < minRecommendedEffectiveDate)
{
// raise a warning
string resultText = String.Format(Catalog.GetString(
"Line {0}: Warning: The date '{1}' is before the current accounting period of any active ledger"),
LineNumber.ToString(), DateEffectiveStr);
InvalidRows.Add(new Tuple <string, TResultSeverity>(resultText, TResultSeverity.Resv_Noncritical));
}
if (AImportMode == "Corporate")
{
if ((preferredPeriodStartDay >= 1) && (DateEffective.Day != preferredPeriodStartDay))
{
// raise a warning
string resultText = String.Format(Catalog.GetString(
"Line {0}: Warning: The date '{1}' should be the first day of an accounting period used by all the active ledgers."),
LineNumber.ToString(), DateEffectiveStr);
InvalidRows.Add(new Tuple <string, TResultSeverity>(resultText, TResultSeverity.Resv_Noncritical));
}
}
decimal ExchangeRate = 0.0m;
try
{
string ExchangeRateString =
StringHelper.GetNextCSV(ref Line, ACSVSeparator, false, true).Replace(ThousandsSeparator, "").Replace(
DecimalSeparator, ".").Replace("\"", String.Empty);
ExchangeRate = Convert.ToDecimal(ExchangeRateString, System.Globalization.CultureInfo.InvariantCulture);
if (ExchangeRate == 0)
{
throw new Exception();
}
}
catch (Exception)
{
// raise an error
string resultText = String.Format(Catalog.GetString(
"Line {0}: invalid rate of exchange ({1})"), LineNumber.ToString(), ExchangeRate);
InvalidRows.Add(new Tuple <string, TResultSeverity>(resultText, TResultSeverity.Resv_Critical));
continue;
}
int TimeEffective = 7200;
if (AImportMode == "Daily")
{
// Daily rate imports can have an optional final column which is the time
// Otherwise we assume the time is a default of 7200 (02:00am)
if ((IsShortFileFormat && (NumCols == 3)) ||
(!IsShortFileFormat && (NumCols == 5)))
{
string timeEffectiveStr = StringHelper.GetNextCSV(ref Line, ACSVSeparator, false, true);
int t = (int)new Ict.Common.TypeConverter.TShortTimeConverter().ConvertTo(timeEffectiveStr, typeof(int));
if (t < 0)
{
// it wasn't in the format 02:00
if (!Int32.TryParse(timeEffectiveStr, out t))
{
// Not a regular Int32 either
t = -1;
}
}
if ((t >= 0) && (t < 86400))
{
TimeEffective = t;
}
else
{
// raise an error
string resultText = String.Format(Catalog.GetString(
"Line {0}: invalid effective time ({1})"), LineNumber.ToString(), t);
InvalidRows.Add(new Tuple <string, TResultSeverity>(resultText, TResultSeverity.Resv_Critical));
continue;
}
}
}
if ((AImportMode == "Corporate") && AExchangeRDT is ACorporateExchangeRateTable)
{
ACorporateExchangeRateTable ExchangeRateDT = (ACorporateExchangeRateTable)AExchangeRDT;
// run this code in the loop twice to get ExchangeRate value and its inverse
for (int i = 0; i <= 1; i++)
{
//this will cause x and y to go from 0 to 1 and 1 to 0 respectively
x = i;
y = Math.Abs(i - 1);
ACorporateExchangeRateRow ExchangeRow = (ACorporateExchangeRateRow)ExchangeRateDT.Rows.
Find(new object[] { Currencies[x], Currencies[y], DateEffective });
if (ExchangeRow == null) // remove 0 for Corporate
{
ExchangeRow = (ACorporateExchangeRateRow)ExchangeRateDT.NewRowTyped();
ExchangeRow.FromCurrencyCode = Currencies[x];
ExchangeRow.ToCurrencyCode = Currencies[y];
ExchangeRow.DateEffectiveFrom = DateEffective;
ExchangeRateDT.Rows.Add(ExchangeRow);
RowsImported++;
}
if (i == 0)
{
ExchangeRow.RateOfExchange = ExchangeRate;
}
else
{
ExchangeRow.RateOfExchange = Math.Round(1 / ExchangeRate, 10);
}
}
}
else if ((AImportMode == "Daily") && AExchangeRDT is ADailyExchangeRateTable)
{
ADailyExchangeRateTable ExchangeRateDT = (ADailyExchangeRateTable)AExchangeRDT;
// run this code in the loop twice to get ExchangeRate value and its inverse
for (int i = 0; i <= 1; i++)
{
//this will cause x and y to go from 0 to 1 and 1 to 0 respectively
x = i;
y = Math.Abs(i - 1);
ADailyExchangeRateRow ExchangeRow = (ADailyExchangeRateRow)ExchangeRateDT.Rows.
Find(new object[] { Currencies[x], Currencies[y], DateEffective, TimeEffective });
if (ExchangeRow == null) // remove 0 for Corporate
{
ExchangeRow = (ADailyExchangeRateRow)ExchangeRateDT.NewRowTyped();
ExchangeRow.FromCurrencyCode = Currencies[x];
ExchangeRow.ToCurrencyCode = Currencies[y];
ExchangeRow.DateEffectiveFrom = DateEffective;
ExchangeRow.TimeEffectiveFrom = TimeEffective;
ExchangeRateDT.Rows.Add(ExchangeRow);
RowsImported++;
}
if (i == 0)
{
ExchangeRow.RateOfExchange = ExchangeRate;
}
else
{
ExchangeRow.RateOfExchange = Math.Round(1 / ExchangeRate, 10);
}
}
}
}
// if there are rows that could not be imported
if ((InvalidRows != null) && (InvalidRows.Count > 0))
{
int errorCount = 0;
int warningCount = 0;
// Go through once just to count the errors and warnings
foreach (Tuple <string, TResultSeverity> Row in InvalidRows)
{
if (Row.Item2 == TResultSeverity.Resv_Noncritical)
{
warningCount++;
}
else
{
errorCount++;
}
}
string resultText = String.Empty;
bool messageListIsFull = false;
int counter = 0;
if (errorCount > 0)
{
resultText = string.Format(Catalog.GetPluralString("1 row was not imported due to invalid data:",
"{0} rows were not imported due to invalid data:", errorCount, true), errorCount) +
Environment.NewLine;
}
if (warningCount > 0)
{
resultText = string.Format(Catalog.GetPluralString("There was 1 warning associated with the imported rows:",
"There were {0} warnings associated with the imported rows:", warningCount, true), warningCount) +
Environment.NewLine;
}
// Now go through again itemising each one
foreach (Tuple <string, TResultSeverity> Row in InvalidRows)
{
counter++;
if (counter <= MAX_MESSAGE_COUNT)
{
resultText += Environment.NewLine + Row.Item1;
}
else if (!messageListIsFull)
{
resultText += String.Format(Catalog.GetString(
"{0}{0}{1} errors/warnings were reported in total. This message contains the first {2}."),
Environment.NewLine, InvalidRows.Count, MAX_MESSAGE_COUNT);
messageListIsFull = true;
}
}
// additional message if one or more rows has an invalid number of columns
if (InvalidColumnCount && IsShortFileFormat)
{
resultText += String.Format("{0}{0}" + Catalog.GetString("Each row should contain 2 or 3 columns as follows:") + "{0}" +
Catalog.GetString(
" 1. Effective Date{0} 2. Exchange Rate{0} 3. Effective time in seconds (Optional for Daily Rate only)"),
Environment.NewLine);
}
else if (InvalidColumnCount && !IsShortFileFormat)
{
resultText += String.Format("{0}{0}" + Catalog.GetString("Each row should contain 4 or 5 columns as follows:") + "{0}" +
Catalog.GetString(
" 1. From Currency{0} 2. To Currency{0} 3. Effective Date{0} 4. Exchange Rate{0} 5. Effective time in seconds (Optional for Daily Rate only)"),
Environment.NewLine);
}
TVerificationResult result = new TVerificationResult(AImportMode,
resultText,
CommonErrorCodes.ERR_INCONGRUOUSSTRINGS,
(errorCount > 0) ? TResultSeverity.Resv_Critical : TResultSeverity.Resv_Noncritical);
AResultCollection.Add(result);
}
DataFile.Close();
return(RowsImported);
}
}
public static DataTable GetAvailableGLYearEnds(Int32 ALedgerNumber,
Int32 ADiffPeriod,
bool AIncludeNextYear,
out String ADisplayMember,
out String AValueMember)
{
const string INTL_DATE_FORMAT = "yyyy-MM-dd";
//Create the table to populate the combobox
DataTable ReturnTable = null;
AValueMember = "YearNumber";
ADisplayMember = "YearEndDate";
TDBTransaction Transaction = null;
try
{
DBAccess.GDBAccessObj.GetNewOrExistingAutoReadTransaction(IsolationLevel.ReadCommitted,
TEnforceIsolationLevel.eilMinimum,
ref Transaction,
delegate
{
DateTime yearEndDate;
DateTime currentYearEndDate;
int yearNumber;
int currentFinancialYear;
ALedgerTable LedgerTable = null;
AAccountingPeriodTable AccountingPeriods = null;
LedgerTable = ALedgerAccess.LoadByPrimaryKey(ALedgerNumber, Transaction);
AccountingPeriods = AAccountingPeriodAccess.LoadViaALedger(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));
}
else if ((AccountingPeriods == null) || (AccountingPeriods.Count == 0))
{
throw new EFinanceSystemDataTableReturnedNoDataException(String.Format(Catalog.GetString(
"Function:{0} - AAccount Period data for Ledger number {1} does not exist or could not be accessed!"),
Utilities.GetMethodName(true),
ALedgerNumber));
}
#endregion Validate Data
ALedgerRow ledgerRow = (ALedgerRow)LedgerTable[0];
currentYearEndDate = GetPeriodEndDate(ALedgerNumber,
ledgerRow.CurrentFinancialYear,
ADiffPeriod,
ledgerRow.NumberOfAccountingPeriods);
currentFinancialYear = ledgerRow.CurrentFinancialYear;
//Filter to highest period number
AccountingPeriods.DefaultView.RowFilter = String.Format("{0}={1}",
AAccountingPeriodTable.GetAccountingPeriodNumberDBName(),
ledgerRow.NumberOfAccountingPeriods);
//Get last period row
AAccountingPeriodRow periodRow = (AAccountingPeriodRow)AccountingPeriods.DefaultView[0].Row;
//Create the table to populate the combobox
ReturnTable = new DataTable();
ReturnTable.Columns.Add("YearNumber", typeof(System.Int32));
ReturnTable.Columns.Add("YearEndDate", typeof(String));
ReturnTable.Columns.Add("YearEndDateLong", typeof(String));
ReturnTable.PrimaryKey = new DataColumn[] { ReturnTable.Columns[0] };
//Add the current year to the table
yearNumber = currentFinancialYear;
yearEndDate = periodRow.PeriodEndDate;
DataRow ResultRow = ReturnTable.NewRow();
ResultRow[0] = yearNumber;
ResultRow[1] = yearEndDate.ToString(INTL_DATE_FORMAT);
ResultRow[2] = yearEndDate.ToLongDateString();
ReturnTable.Rows.Add(ResultRow);
//Retrieve all previous years
string sql =
String.Format("SELECT DISTINCT {0} AS batchYear" +
" FROM PUB_{1}" +
" WHERE {2} = {3} And {0} < {4}" +
" ORDER BY 1 DESC",
ABatchTable.GetBatchYearDBName(),
ABatchTable.GetTableDBName(),
ABatchTable.GetLedgerNumberDBName(),
ALedgerNumber,
yearNumber);
DataTable BatchYearTable = DBAccess.GDBAccessObj.SelectDT(sql, "BatchYearTable", Transaction);
BatchYearTable.DefaultView.Sort = String.Format("batchYear DESC");
foreach (DataRowView row in BatchYearTable.DefaultView)
{
DataRow currentBatchYearRow = row.Row;
Int32 currentBatchYear = Convert.ToInt32(currentBatchYearRow[0]);
if (yearNumber != currentBatchYear)
{
yearNumber -= 1;
yearEndDate = DecrementYear(yearEndDate);
if (yearNumber != currentBatchYear)
{
//Gap in year numbers
throw new Exception(String.Format(Catalog.GetString("Year {0} not found for Ledger {1}"),
yearNumber,
ALedgerNumber));
}
}
DataRow ResultRow2 = ReturnTable.NewRow();
ResultRow2[0] = yearNumber;
ResultRow2[1] = yearEndDate.ToString(INTL_DATE_FORMAT);
ReturnTable.Rows.Add(ResultRow2);
}
if (AIncludeNextYear && (ReturnTable.Rows.Find(currentFinancialYear + 1) == null))
{
DataRow resultRow = ReturnTable.NewRow();
resultRow[0] = currentFinancialYear + 1;
resultRow[1] = currentYearEndDate.AddYears(1).ToString(INTL_DATE_FORMAT);
//resultRow[2] = currentYearEndDate.ToString("dd-MMM-yyyy");
ReturnTable.Rows.InsertAt(resultRow, 0);
}
}); // Get NewOrExisting AutoReadTransaction
}
catch (Exception ex)
{
TLogging.LogException(ex, Utilities.GetMethodSignature());
throw;
}
return(ReturnTable);
}
private static GiftBatchTDS CreateGiftBatches(SortedList <DateTime, List <XmlNode> > AGiftsPerDate, int APeriodNumber)
{
GiftBatchTDS MainDS = new GiftBatchTDS();
ALedgerTable LedgerTable = null;
TDBTransaction ReadTransaction = new TDBTransaction();
DBAccess.ReadTransaction(ref ReadTransaction,
delegate
{
TDataBase db = ReadTransaction.DataBaseObj;
// get a list of potential donors (all class FAMILY)
string sqlGetFamilyPartnerKeys = "SELECT p_partner_key_n FROM PUB_p_family";
DataTable FamilyKeys = db.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 = db.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 = db.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 = db.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 = new TDBTransaction();
bool SubmissionOk = false;
DBAccess.WriteTransaction(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);
}
private void ValidateDataDetailsManual(AAccountingPeriodRow ARow)
{
if (FDuringSave)
{
AAccountingPeriodRow PeriodRow;
foreach (DataRow row in FMainDS.AAccountingPeriod.Rows)
{
PeriodRow = (AAccountingPeriodRow)row;
if (PeriodRow.AccountingPeriodNumber <= FNumberOfAccountingPeriods)
{
// only check gap to next record as otherwise messages would appear twice
ValidateRecord(PeriodRow, false, true);
}
}
}
else
{
// if not within save process then
ValidateRecord(ARow, true, true);
}
}
public static DataTable GetAvailablePeriods(Int32 ALedgerNumber, Int32 AFinancialYear)
{
#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
DataTable ReturnTable = new DataTable();
ReturnTable.Columns.Add("PeriodNumber", typeof(System.Int32));
ReturnTable.Columns.Add("PeriodName", typeof(String));
ReturnTable.PrimaryKey = new DataColumn[] {
ReturnTable.Columns[0]
};
System.Type TypeofTable = null;
TCacheable CachePopulator = new TCacheable();
ALedgerTable LedgerTable = (ALedgerTable)CachePopulator.GetCacheableTable(TCacheableFinanceTablesEnum.LedgerDetails,
"",
false,
ALedgerNumber,
out TypeofTable);
AAccountingPeriodTable AccountingPeriods = (AAccountingPeriodTable)CachePopulator.GetCacheableTable(
TCacheableFinanceTablesEnum.AccountingPeriodList,
"",
false,
ALedgerNumber,
out TypeofTable);
#region Validate Data
if ((LedgerTable == null) || (LedgerTable.Count == 0))
{
throw new EFinanceSystemCacheableTableReturnedNoDataException(String.Format(Catalog.GetString(
"Function:{0} - Ledger data for Ledger number {1} does not exist or could not be accessed!"),
Utilities.GetMethodName(true),
ALedgerNumber));
}
else if ((AccountingPeriods == null) || (AccountingPeriods.Count == 0))
{
throw new EFinanceSystemCacheableTableReturnedNoDataException(String.Format(Catalog.GetString(
"Function:{0} - Accounting Periods data for Ledger number {1} does not exist or could not be accessed!"),
Utilities.GetMethodName(true),
ALedgerNumber));
}
else if (AFinancialYear > LedgerTable[0].CurrentFinancialYear)
{
throw new EFinanceSystemCacheableTableReturnedNoDataException(String.Format(Catalog.GetString(
"Function:{0} - Financial Year {2} for Ledger number {1} does not exist!"),
Utilities.GetMethodName(true),
ALedgerNumber,
AFinancialYear));
}
#endregion Validate Data
if (AFinancialYear == LedgerTable[0].CurrentFinancialYear)
{
DataRow resultRow = ReturnTable.NewRow();
resultRow[0] = "-1";
resultRow[1] = "all_periods";
ReturnTable.Rows.Add(resultRow);
resultRow = ReturnTable.NewRow();
resultRow[0] = "0";
resultRow[1] = "all_open_periods";
ReturnTable.Rows.Add(resultRow);
for (int i = 1; i <= LedgerTable[0].CurrentPeriod + LedgerTable[0].NumberFwdPostingPeriods; i++)
{
AAccountingPeriodRow period =
(AAccountingPeriodRow)AccountingPeriods.Rows.Find(new object[] { ALedgerNumber, i });
resultRow = ReturnTable.NewRow();
resultRow[0] = i;
resultRow[1] = period.AccountingPeriodDesc + " " + period.PeriodEndDate.ToString("yyyy");
ReturnTable.Rows.Add(resultRow);
}
}
else
{
DataRow resultRow = ReturnTable.NewRow();
resultRow[0] = "-1";
resultRow[1] = "all_periods";
ReturnTable.Rows.Add(resultRow);
for (int i = 1; i <= LedgerTable[0].NumberOfAccountingPeriods; i++)
{
AAccountingPeriodRow period =
(AAccountingPeriodRow)AccountingPeriods.Rows.Find(new object[] { ALedgerNumber, i });
resultRow = ReturnTable.NewRow();
resultRow[0] = i;
resultRow[1] = period.AccountingPeriodDesc;
ReturnTable.Rows.Add(resultRow);
}
}
return(ReturnTable);
}
