/// <summary>
/// initialize the calender of the ledger for a specific year, so that we can have the specified number of open periods,
/// and the current year is open.
/// </summary>
public static void InitCalendar()
{
int YearDifference = (FNumberOfClosedPeriods / 12);
if (FNumberOfClosedPeriods % 12 >= DateTime.Today.Month)
{
YearDifference++;
}
AAccountingPeriodTable periodTable = AAccountingPeriodAccess.LoadViaALedger(FLedgerNumber, null);
foreach (AAccountingPeriodRow row in periodTable.Rows)
{
row.PeriodStartDate = new DateTime(row.PeriodStartDate.Year - YearDifference, row.PeriodStartDate.Month, row.PeriodStartDate.Day);
int LastDay = row.PeriodEndDate.Day;
if (row.PeriodEndDate.Month == 2)
{
LastDay = DateTime.IsLeapYear(row.PeriodEndDate.Year - YearDifference) ? 29 : 28;
}
row.PeriodEndDate = new DateTime(row.PeriodEndDate.Year - YearDifference, row.PeriodEndDate.Month, LastDay);
}
AAccountingPeriodAccess.SubmitChanges(periodTable, null);
}
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);
}
private void LoadData()
{
TDBTransaction Transaction = null;
try
{
DBAccess.GDBAccessObj.GetNewOrExistingAutoReadTransaction(IsolationLevel.ReadCommitted,
TEnforceIsolationLevel.eilMinimum,
ref Transaction,
delegate
{
FPeriodTable = AAccountingPeriodAccess.LoadViaALedger(FLedgerNumber, Transaction);
#region Validate Data
if ((FPeriodTable == null) || (FPeriodTable.Count == 0))
{
throw new EFinanceSystemDataTableReturnedNoDataException(String.Format(Catalog.GetString(
"Function:{0} - Accounting Period data for Ledger {1} does not exist or could not be accessed!"),
Utilities.GetMethodName(true),
FLedgerNumber));
}
#endregion Validate Data
});
}
catch (Exception ex)
{
TLogging.LogException(ex, Utilities.GetMethodSignature());
throw;
}
}
public static Boolean GetCurrentPostingRangeDates(Int32 ALedgerNumber,
out DateTime AStartDateCurrentPeriod,
out DateTime AEndDateLastForwardingPeriod)
{
DateTime StartDateCurrentPeriod = new DateTime();
DateTime EndDateLastForwardingPeriod = new DateTime();
TDBTransaction Transaction = null;
DBAccess.GDBAccessObj.GetNewOrExistingAutoReadTransaction(IsolationLevel.ReadCommitted,
TEnforceIsolationLevel.eilMinimum,
ref Transaction,
delegate
{
ALedgerTable LedgerTable = ALedgerAccess.LoadByPrimaryKey(ALedgerNumber, Transaction);
AAccountingPeriodTable AccountingPeriodTable = AAccountingPeriodAccess.LoadByPrimaryKey(ALedgerNumber,
LedgerTable[0].CurrentPeriod,
Transaction);
StartDateCurrentPeriod = AccountingPeriodTable[0].PeriodStartDate;
AccountingPeriodTable = AAccountingPeriodAccess.LoadByPrimaryKey(ALedgerNumber,
LedgerTable[0].CurrentPeriod + LedgerTable[0].NumberFwdPostingPeriods,
Transaction);
EndDateLastForwardingPeriod = AccountingPeriodTable[0].PeriodEndDate;
});
AStartDateCurrentPeriod = StartDateCurrentPeriod;
AEndDateLastForwardingPeriod = EndDateLastForwardingPeriod;
return(true);
}
public static Boolean GetCurrentPeriodDates(Int32 ALedgerNumber,
out DateTime AStartDateCurrentPeriod,
out DateTime AEndDateCurrentPeriod)
{
DateTime startDate = new DateTime();
DateTime endDate = new DateTime();
TDBTransaction Transaction = null;
DBAccess.GDBAccessObj.GetNewOrExistingAutoReadTransaction(IsolationLevel.ReadCommitted,
TEnforceIsolationLevel.eilMinimum,
ref Transaction,
delegate
{
ALedgerTable ledgerTable = ALedgerAccess.LoadByPrimaryKey(ALedgerNumber, Transaction);
AAccountingPeriodTable accountingPeriodTable = AAccountingPeriodAccess.LoadByPrimaryKey(ALedgerNumber,
ledgerTable[0].CurrentPeriod,
Transaction);
startDate = accountingPeriodTable[0].PeriodStartDate;
endDate = accountingPeriodTable[0].PeriodEndDate;
});
AStartDateCurrentPeriod = startDate;
AEndDateCurrentPeriod = endDate;
return(true);
}
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);
}
/// <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;
}
/// <summary>
/// Direct access to the unposted gifts
/// </summary>
/// <param name="ALedgerNumber"></param>
/// <param name="ADateEndOfPeriod"></param>
public GetUnpostedGiftInfo(int ALedgerNumber, DateTime ADateEndOfPeriod)
{
//IF CAN-FIND (FIRST a_gift_batch WHERE
// a_gift_batch.a_ledger_number_i EQ pv_ledger_number_i AND
// a_gift_batch.a_gl_effective_date_d LE
// a_accounting_period.a_period_end_date_d AND
// a_gift_batch.a_batch_status_c EQ "Unposted":U) THEN DO:
OdbcParameter[] ParametersArray;
ParametersArray = new OdbcParameter[3];
ParametersArray[0] = new OdbcParameter("", OdbcType.Int);
ParametersArray[0].Value = ALedgerNumber;
ParametersArray[1] = new OdbcParameter("", OdbcType.Date);
ParametersArray[1].Value = ADateEndOfPeriod;
ParametersArray[2] = new OdbcParameter("", OdbcType.VarChar);
ParametersArray[2].Value = MFinanceConstants.BATCH_UNPOSTED;
bool NewTransaction;
TDBTransaction transaction = DBAccess.GDBAccessObj.GetNewOrExistingTransaction(IsolationLevel.ReadCommitted,
TEnforceIsolationLevel.eilMinimum,
out NewTransaction);
string strSQL = "SELECT * FROM PUB_" + AGiftBatchTable.GetTableDBName() + " ";
strSQL += "WHERE " + AGiftBatchTable.GetLedgerNumberDBName() + " = ? ";
strSQL += "AND " + AGiftBatchTable.GetGlEffectiveDateDBName() + " <= ? ";
strSQL += "AND " + AGiftBatchTable.GetBatchStatusDBName() + " = ? ";
FDataTable = DBAccess.GDBAccessObj.SelectDT(
strSQL, AAccountingPeriodTable.GetTableDBName(), transaction, ParametersArray);
if (NewTransaction)
{
DBAccess.GDBAccessObj.CommitTransaction();
}
}
public static Boolean GetCurrentPeriodDates(Int32 ALedgerNumber,
out DateTime AStartDateCurrentPeriod,
out DateTime AEndDateCurrentPeriod)
{
DateTime startDate = new DateTime();
DateTime endDate = new DateTime();
TDBTransaction Transaction = new TDBTransaction();
TDataBase db = DBAccess.Connect("GetCurrentPeriodDates");
db.ReadTransaction(
ref Transaction,
delegate
{
ALedgerTable ledgerTable = ALedgerAccess.LoadByPrimaryKey(ALedgerNumber, Transaction);
AAccountingPeriodTable accountingPeriodTable = AAccountingPeriodAccess.LoadByPrimaryKey(ALedgerNumber,
ledgerTable[0].CurrentPeriod,
Transaction);
startDate = accountingPeriodTable[0].PeriodStartDate;
endDate = accountingPeriodTable[0].PeriodEndDate;
});
db.CloseDBConnection();
AStartDateCurrentPeriod = startDate;
AEndDateCurrentPeriod = endDate;
return(true);
}
public static bool GetCurrentPostingRangeDates(Int32 ALedgerNumber,
out DateTime AStartDateCurrentPeriod,
out DateTime AEndDateLastForwardingPeriod)
{
bool newTransaction = false;
TDBTransaction Transaction = DBAccess.GDBAccessObj.GetNewOrExistingTransaction(IsolationLevel.Serializable, out newTransaction);
ALedgerTable LedgerTable = ALedgerAccess.LoadByPrimaryKey(ALedgerNumber, Transaction);
AAccountingPeriodTable AccountingPeriodTable = AAccountingPeriodAccess.LoadByPrimaryKey(ALedgerNumber,
LedgerTable[0].CurrentPeriod,
Transaction);
AStartDateCurrentPeriod = AccountingPeriodTable[0].PeriodStartDate;
AccountingPeriodTable = AAccountingPeriodAccess.LoadByPrimaryKey(ALedgerNumber,
LedgerTable[0].CurrentPeriod + LedgerTable[0].NumberFwdPostingPeriods,
Transaction);
AEndDateLastForwardingPeriod = AccountingPeriodTable[0].PeriodEndDate;
if (newTransaction)
{
DBAccess.GDBAccessObj.RollbackTransaction();
}
return(true);
}
/// <summary>
/// Direct access to the unposted gifts
/// </summary>
/// <param name="ALedgerNumber"></param>
/// <param name="ADateEndOfPeriod"></param>
public GetUnpostedGiftInfo(int ALedgerNumber, DateTime ADateEndOfPeriod)
{
OdbcParameter[] ParametersArray;
ParametersArray = new OdbcParameter[3];
ParametersArray[0] = new OdbcParameter("", OdbcType.Int);
ParametersArray[0].Value = ALedgerNumber;
ParametersArray[1] = new OdbcParameter("", OdbcType.Date);
ParametersArray[1].Value = ADateEndOfPeriod;
ParametersArray[2] = new OdbcParameter("", OdbcType.VarChar);
ParametersArray[2].Value = MFinanceConstants.BATCH_UNPOSTED;
TDBTransaction transaction = new TDBTransaction();
TDataBase db = DBAccess.Connect("GetUnpostedGiftInfo");
db.ReadTransaction(
ref transaction,
delegate
{
string strSQL = "SELECT * FROM PUB_" + AGiftBatchTable.GetTableDBName() +
" WHERE " + AGiftBatchTable.GetLedgerNumberDBName() + " = ?" +
" AND " + AGiftBatchTable.GetGlEffectiveDateDBName() + " <= ?" +
" AND " + AGiftBatchTable.GetBatchStatusDBName() + " = ? " +
" ORDER BY " + AGiftBatchTable.GetBatchNumberDBName();
FDataTable = db.SelectDT(
strSQL, AAccountingPeriodTable.GetTableDBName(), transaction, ParametersArray);
});
db.CloseDBConnection();
}
public static System.DateTime GetPeriodEndDate(Int32 ALedgerNumber, System.Int32 AYear, System.Int32 ADiffPeriod,
System.Int32 APeriod, TDataBase ADataBase)
{
System.Int32 RealYear = 0;
System.Int32 RealPeriod = 0;
System.Type typeofTable = null;
TCacheable CachePopulator = new TCacheable();
DateTime ReturnValue = DateTime.Now;
GetRealPeriod(ALedgerNumber, ADiffPeriod, AYear, APeriod, out RealPeriod, out RealYear);
DataTable UntypedTable = CachePopulator.GetCacheableTable(TCacheableFinanceTablesEnum.AccountingPeriodList,
"",
false,
ALedgerNumber,
out typeofTable, ADataBase);
AAccountingPeriodTable CachedDataTable = (AAccountingPeriodTable)UntypedTable;
CachedDataTable.DefaultView.RowFilter = AAccountingPeriodTable.GetAccountingPeriodNumberDBName() + " = " + RealPeriod;
if (CachedDataTable.DefaultView.Count > 0)
{
ReturnValue = ((AAccountingPeriodRow)CachedDataTable.DefaultView[0].Row).PeriodEndDate;
ALedgerTable Ledger = (ALedgerTable)CachePopulator.GetCacheableTable(TCacheableFinanceTablesEnum.LedgerDetails,
"",
false,
ALedgerNumber,
out typeofTable, ADataBase);
ReturnValue = ReturnValue.AddYears(RealYear - Ledger[0].CurrentFinancialYear);
}
return(ReturnValue);
}
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);
}
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 System.DateTime GetPeriodEndDate(Int32 ALedgerNumber, System.Int32 AYear, System.Int32 ADiffPeriod, System.Int32 APeriod)
{
System.Int32 RealYear = 0;
System.Int32 RealPeriod = 0;
System.Type typeofTable = null;
TCacheable CachePopulator = new TCacheable();
DateTime ReturnValue = DateTime.Now;
GetRealPeriod(ALedgerNumber, ADiffPeriod, AYear, APeriod, out RealPeriod, out RealYear);
DataTable CachedDataTable = CachePopulator.GetCacheableTable(TCacheableFinanceTablesEnum.AccountingPeriodList,
"",
false,
ALedgerNumber,
out typeofTable);
string whereClause = AAccountingPeriodTable.GetLedgerNumberDBName() + " = " + ALedgerNumber.ToString() + " and " +
AAccountingPeriodTable.GetAccountingPeriodNumberDBName() + " = " + RealPeriod.ToString();
DataRow[] filteredRows = CachedDataTable.Select(whereClause);
if (filteredRows.Length > 0)
{
ReturnValue = ((AAccountingPeriodRow)filteredRows[0]).PeriodEndDate;
ALedgerTable Ledger = (ALedgerTable)CachePopulator.GetCacheableTable(TCacheableFinanceTablesEnum.LedgerDetails,
"",
false,
ALedgerNumber,
out typeofTable);
ReturnValue = ReturnValue.AddYears(RealYear - Ledger[0].CurrentFinancialYear);
}
return(ReturnValue);
}
/// <summary>
/// Direct access to the unposted gifts
/// </summary>
/// <param name="ALedgerNumber"></param>
/// <param name="ADateEndOfPeriod"></param>
public GetUnpostedGiftInfo(int ALedgerNumber, DateTime ADateEndOfPeriod)
{
OdbcParameter[] ParametersArray;
ParametersArray = new OdbcParameter[3];
ParametersArray[0] = new OdbcParameter("", OdbcType.Int);
ParametersArray[0].Value = ALedgerNumber;
ParametersArray[1] = new OdbcParameter("", OdbcType.Date);
ParametersArray[1].Value = ADateEndOfPeriod;
ParametersArray[2] = new OdbcParameter("", OdbcType.VarChar);
ParametersArray[2].Value = MFinanceConstants.BATCH_UNPOSTED;
TDBTransaction transaction = null;
DBAccess.GDBAccessObj.GetNewOrExistingAutoReadTransaction(IsolationLevel.ReadCommitted,
TEnforceIsolationLevel.eilMinimum,
ref transaction,
delegate
{
string strSQL = "SELECT * FROM PUB_" + AGiftBatchTable.GetTableDBName() +
" WHERE " + AGiftBatchTable.GetLedgerNumberDBName() + " = ?" +
" AND " + AGiftBatchTable.GetGlEffectiveDateDBName() + " <= ?" +
" AND " + AGiftBatchTable.GetBatchStatusDBName() + " = ? " +
" ORDER BY " + AGiftBatchTable.GetBatchNumberDBName();
FDataTable = DBAccess.GDBAccessObj.SelectDT(
strSQL, AAccountingPeriodTable.GetTableDBName(), transaction, ParametersArray);
});
}
/// <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 PeriodMonthEnd(
Int32 ALedgerNumber,
bool AInfoMode,
out List <Int32> AglBatchNumbers,
out Boolean AStewardshipBatch,
out TVerificationResultCollection AVerificationResults)
{
AglBatchNumbers = new List <int>();
AStewardshipBatch = false;
try
{
TLedgerInfo ledgerInfo = new TLedgerInfo(ALedgerNumber);
Int32 PeriodClosing = ledgerInfo.CurrentPeriod;
bool res = new TMonthEnd(ledgerInfo).RunMonthEnd(AInfoMode,
out AglBatchNumbers,
out AStewardshipBatch,
out AVerificationResults);
if (!res && !AInfoMode)
{
TDBTransaction Transaction = null;
AAccountingPeriodTable PeriodTbl = null;
DBAccess.GDBAccessObj.GetNewOrExistingAutoReadTransaction(IsolationLevel.ReadUncommitted,
TEnforceIsolationLevel.eilMinimum,
ref Transaction,
delegate
{
PeriodTbl = AAccountingPeriodAccess.LoadByPrimaryKey(ledgerInfo.LedgerNumber, PeriodClosing, Transaction);
});
if (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(res);
}
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(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);
}
private DataTable GetAccountingPeriodListTable(TDBTransaction AReadTransaction, System.Int32 ALedgerNumber, string ATableName)
{
StringCollection FieldList = new StringCollection();
FieldList.Add(AAccountingPeriodTable.GetLedgerNumberDBName());
FieldList.Add(AAccountingPeriodTable.GetAccountingPeriodNumberDBName());
FieldList.Add(AAccountingPeriodTable.GetAccountingPeriodDescDBName());
FieldList.Add(AAccountingPeriodTable.GetPeriodStartDateDBName());
FieldList.Add(AAccountingPeriodTable.GetPeriodEndDateDBName());
return(AAccountingPeriodAccess.LoadViaALedger(ALedgerNumber, FieldList, AReadTransaction));
}
/// <summary>
/// The AccountingPeriod Rows are updated ...
/// </summary>
override public Int32 RunOperation()
{
bool NewTransaction;
Int32 JobSize = 0;
Boolean ShouldCommit = false;
TDBTransaction Transaction = DBAccess.GDBAccessObj.GetNewOrExistingTransaction(IsolationLevel.Serializable, out NewTransaction);
AAccountingPeriodTable AccountingPeriodTbl = null;
try
{
AccountingPeriodTbl = AAccountingPeriodAccess.LoadViaALedger(FLedgerNumber, Transaction);
foreach (AAccountingPeriodRow accountingPeriodRow in AccountingPeriodTbl.Rows)
{
accountingPeriodRow.PeriodStartDate =
accountingPeriodRow.PeriodStartDate.AddDays(1).AddYears(1).AddDays(-1);
accountingPeriodRow.PeriodEndDate =
accountingPeriodRow.PeriodEndDate.AddDays(1).AddYears(1).AddDays(-1);
JobSize++;
}
if (DoExecuteableCode)
{
AAccountingPeriodAccess.SubmitChanges(AccountingPeriodTbl, Transaction);
ShouldCommit = true;
}
}
catch (Exception Exc)
{
TLogging.Log("Exception during running the AccountPeriod To New Year operation:" + Environment.NewLine + Exc.ToString());
throw;
}
finally
{
if (NewTransaction)
{
if (ShouldCommit)
{
DBAccess.GDBAccessObj.CommitTransaction();
}
else
{
DBAccess.GDBAccessObj.RollbackTransaction();
}
}
}
return JobSize;
} // RunOperation
public static bool GetCurrentPeriodDates(Int32 ALedgerNumber, out DateTime AStartDate, out DateTime AEndDate)
{
TDBTransaction Transaction = DBAccess.GDBAccessObj.BeginTransaction(IsolationLevel.Serializable);
ALedgerTable LedgerTable = ALedgerAccess.LoadByPrimaryKey(ALedgerNumber, Transaction);
AAccountingPeriodTable AccountingPeriodTable = AAccountingPeriodAccess.LoadByPrimaryKey(ALedgerNumber,
LedgerTable[0].CurrentPeriod,
Transaction);
AStartDate = AccountingPeriodTable[0].PeriodStartDate;
AEndDate = AccountingPeriodTable[0].PeriodEndDate;
DBAccess.GDBAccessObj.RollbackTransaction();
return(true);
}
private decimal GetCorporateExchangeRateFromDB(TDataBase databaseConnection,
int pv_ledger_number_i,
int pv_year_i,
int pv_period_i,
int currentFinancialYear)
{
ALedgerTable ledgerTable = ALedgerAccess.LoadByPrimaryKey(pv_ledger_number_i, databaseConnection.Transaction);
AAccountingPeriodTable AccountingPeriodTable = AAccountingPeriodAccess.LoadByPrimaryKey(pv_ledger_number_i,
pv_period_i,
databaseConnection.Transaction);
if (AccountingPeriodTable.Rows.Count < 1)
{
return(-1); // This is poor (because the caller can blindly use it!)
// I wonder whether an exception would be better. (Tim Ingham, Oct 2013)
}
if (currentFinancialYear < 0)
{
currentFinancialYear = ledgerTable[0].CurrentFinancialYear;
}
DateTime startOfPeriod = AccountingPeriodTable[0].PeriodStartDate;
DateTime endOfPeriod = AccountingPeriodTable[0].PeriodEndDate;
startOfPeriod = new DateTime(startOfPeriod.Year - (currentFinancialYear - pv_year_i), startOfPeriod.Month, startOfPeriod.Day);
if ((endOfPeriod.Month == 2) && (endOfPeriod.Day == 29) &&
(((currentFinancialYear - pv_year_i)) % 4 != 0))
{
endOfPeriod = endOfPeriod.AddDays(-1);
}
endOfPeriod = new DateTime(endOfPeriod.Year - (currentFinancialYear - pv_year_i), endOfPeriod.Month, endOfPeriod.Day);
// get the corporate exchange rate between base and intl currency for the period
decimal IntlToBaseExchRate;
TExchangeRateTools.GetCorporateExchangeRate(ledgerTable[0].IntlCurrency,
ledgerTable[0].BaseCurrency,
startOfPeriod,
endOfPeriod,
out IntlToBaseExchRate,
databaseConnection);
return(IntlToBaseExchRate);
}
public static bool GetCurrentPeriodDates(Int32 ALedgerNumber, out DateTime AStartDate, out DateTime AEndDate)
{
TDBTransaction transaction = null;
AAccountingPeriodTable AccountingPeriodTable = null;
DBAccess.GDBAccessObj.GetNewOrExistingAutoReadTransaction(IsolationLevel.Serializable, TEnforceIsolationLevel.eilMinimum, ref transaction,
delegate
{
ALedgerTable LedgerTable = ALedgerAccess.LoadByPrimaryKey(ALedgerNumber, transaction);
AccountingPeriodTable = AAccountingPeriodAccess.LoadByPrimaryKey(ALedgerNumber,
LedgerTable[0].CurrentPeriod,
transaction);
});
AStartDate = AccountingPeriodTable[0].PeriodStartDate;
AEndDate = AccountingPeriodTable[0].PeriodEndDate;
return(true);
}
public static bool GetPeriodDates(Int32 ALedgerNumber,
Int32 AYearNumber,
Int32 ADiffPeriod,
Int32 APeriodNumber,
out DateTime AStartDatePeriod,
out DateTime AEndDatePeriod)
{
if ((AYearNumber < 0) || (APeriodNumber < 0))
{
AStartDatePeriod = DateTime.MinValue;
AEndDatePeriod = DateTime.MinValue;
return(false);
}
DateTime StartDatePeriod = new DateTime();
DateTime EndDatePeriod = new DateTime();
TDBTransaction Transaction = new TDBTransaction();
TDataBase db = DBAccess.Connect("GetPeriodDates");
db.ReadTransaction(
ref Transaction,
delegate
{
AAccountingPeriodTable AccountingPeriodTable = AAccountingPeriodAccess.LoadByPrimaryKey(ALedgerNumber, APeriodNumber, Transaction);
// TODO: ADiffPeriod for support of different financial years
StartDatePeriod = AccountingPeriodTable[0].PeriodStartDate;
EndDatePeriod = AccountingPeriodTable[0].PeriodEndDate;
ALedgerTable LedgerTable = ALedgerAccess.LoadByPrimaryKey(ALedgerNumber, Transaction);
//TODO: Calendar vs Financial Date Handling - Should this be number of accounting periods rather than 12
StartDatePeriod = StartDatePeriod.AddMonths(-12 * (LedgerTable[0].CurrentFinancialYear - AYearNumber));
EndDatePeriod = EndDatePeriod.AddMonths(-12 * (LedgerTable[0].CurrentFinancialYear - AYearNumber));
});
db.CloseDBConnection();
AStartDatePeriod = StartDatePeriod;
AEndDatePeriod = EndDatePeriod;
return(true);
}
public static bool GetCurrentPeriodDates(Int32 ALedgerNumber, out DateTime AStartDate, out DateTime AEndDate)
{
TDBTransaction transaction = new TDBTransaction();
TDataBase db = DBAccess.Connect("GetCurrentPeriodDates");
AAccountingPeriodTable AccountingPeriodTable = null;
db.ReadTransaction(ref transaction,
delegate
{
ALedgerTable LedgerTable = ALedgerAccess.LoadByPrimaryKey(ALedgerNumber, transaction);
AccountingPeriodTable = AAccountingPeriodAccess.LoadByPrimaryKey(ALedgerNumber,
LedgerTable[0].CurrentPeriod,
transaction);
});
db.CloseDBConnection();
AStartDate = AccountingPeriodTable[0].PeriodStartDate;
AEndDate = AccountingPeriodTable[0].PeriodEndDate;
return(true);
}
public static bool GetPeriodDates(Int32 ALedgerNumber,
Int32 AYearNumber,
Int32 ADiffPeriod,
Int32 APeriodNumber,
out DateTime AStartDatePeriod,
out DateTime AEndDatePeriod)
{
if ((AYearNumber < 0) || (APeriodNumber < 0))
{
AStartDatePeriod = DateTime.MinValue;
AEndDatePeriod = DateTime.MinValue;
return(false);
}
DateTime StartDatePeriod = new DateTime();
DateTime EndDatePeriod = new DateTime();
TDBTransaction Transaction = null;
DBAccess.GDBAccessObj.GetNewOrExistingAutoReadTransaction(IsolationLevel.ReadCommitted,
TEnforceIsolationLevel.eilMinimum,
ref Transaction,
delegate
{
AAccountingPeriodTable AccountingPeriodTable = AAccountingPeriodAccess.LoadByPrimaryKey(ALedgerNumber, APeriodNumber, Transaction);
// TODO: ADiffPeriod for support of different financial years
StartDatePeriod = AccountingPeriodTable[0].PeriodStartDate;
EndDatePeriod = AccountingPeriodTable[0].PeriodEndDate;
ALedgerTable LedgerTable = ALedgerAccess.LoadByPrimaryKey(ALedgerNumber, Transaction);
//TODO: Calendar vs Financial Date Handling - Should this be number of accounting periods rather than 12
StartDatePeriod = StartDatePeriod.AddMonths(-12 * (LedgerTable[0].CurrentFinancialYear - AYearNumber));
EndDatePeriod = EndDatePeriod.AddMonths(-12 * (LedgerTable[0].CurrentFinancialYear - AYearNumber));
});
AStartDatePeriod = StartDatePeriod;
AEndDatePeriod = EndDatePeriod;
return(true);
}
/// <summary>
/// initialize the calender of the ledger for a specific year, so that we can have the specified number of open periods,
/// and the current year is open.
/// </summary>
public static void InitCalendar()
{
TDataBase db = DBAccess.Connect("InitCalendar");
TDBTransaction Transaction = new TDBTransaction();
bool SubmitOK = false;
db.WriteTransaction(ref Transaction,
ref SubmitOK,
delegate
{
int YearDifference = (FNumberOfClosedPeriods / 12);
if (FNumberOfClosedPeriods % 12 >= DateTime.Today.Month)
{
YearDifference++;
}
AAccountingPeriodTable periodTable = AAccountingPeriodAccess.LoadViaALedger(FLedgerNumber, Transaction);
foreach (AAccountingPeriodRow row in periodTable.Rows)
{
row.PeriodStartDate = new DateTime(row.PeriodStartDate.Year - YearDifference, row.PeriodStartDate.Month, row.PeriodStartDate.Day);
int LastDay = row.PeriodEndDate.Day;
if (row.PeriodEndDate.Month == 2)
{
LastDay = DateTime.IsLeapYear(row.PeriodEndDate.Year - YearDifference) ? 29 : 28;
}
row.PeriodEndDate = new DateTime(row.PeriodEndDate.Year - YearDifference, row.PeriodEndDate.Month, LastDay);
}
AAccountingPeriodAccess.SubmitChanges(periodTable, Transaction);
SubmitOK = true;
});
db.CloseDBConnection();
}
private void LoadData()
{
bool NewTransaction;
TDBTransaction transaction = DBAccess.GDBAccessObj.GetNewOrExistingTransaction(IsolationLevel.ReadCommitted,
TEnforceIsolationLevel.eilMinimum,
out NewTransaction);
try
{
periodTable = AAccountingPeriodAccess.LoadViaALedger(intLedgerNumber, transaction);
if (NewTransaction)
{
DBAccess.GDBAccessObj.CommitTransaction();
}
}
catch (Exception)
{
DBAccess.GDBAccessObj.RollbackTransaction();
throw;
}
}
public static TSubmitChangesResult SaveLedgerSettings(
Int32 ALedgerNumber,
DateTime ACalendarStartDate,
ref GLSetupTDS AInspectDS)
{
#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 (AInspectDS == null)
{
return TSubmitChangesResult.scrNothingToBeSaved;
}
#endregion Validate Arguments
ALedgerTable LedgerTable;
ALedgerRow LedgerRow;
AAccountingPeriodTable AccountingPeriodTable;
AAccountingPeriodRow AccountingPeriodRow;
AAccountingPeriodTable NewAccountingPeriodTable;
AAccountingPeriodRow NewAccountingPeriodRow;
AGeneralLedgerMasterTable GLMTable;
AGeneralLedgerMasterRow GLMRow;
AGeneralLedgerMasterPeriodTable GLMPeriodTable;
AGeneralLedgerMasterPeriodTable TempGLMPeriodTable;
AGeneralLedgerMasterPeriodTable NewGLMPeriodTable;
AGeneralLedgerMasterPeriodRow GLMPeriodRow;
AGeneralLedgerMasterPeriodRow TempGLMPeriodRow;
AGeneralLedgerMasterPeriodRow NewGLMPeriodRow;
int CurrentNumberPeriods;
int NewNumberPeriods;
int CurrentNumberFwdPostingPeriods;
int NewNumberFwdPostingPeriods;
int CurrentLastFwdPeriod;
int NewLastFwdPeriod;
int Period;
Boolean ExtendFwdPeriods = false;
DateTime PeriodStartDate;
DateTime CurrentCalendarStartDate;
Boolean CreateCalendar = false;
TDBTransaction Transaction = null;
bool SubmissionOK = false;
GLSetupTDS InspectDS = AInspectDS;
try
{
DBAccess.GDBAccessObj.GetNewOrExistingAutoTransaction(IsolationLevel.Serializable,
TEnforceIsolationLevel.eilMinimum,
ref Transaction,
ref SubmissionOK,
delegate
{
// load ledger row currently saved in database so it can be used for comparison with modified data
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
LedgerRow = (ALedgerRow)LedgerTable.Rows[0];
if (InspectDS.ALedger != null)
{
// initialize variables for accounting periods and forward periods
CurrentNumberPeriods = LedgerRow.NumberOfAccountingPeriods;
NewNumberPeriods = ((ALedgerRow)(InspectDS.ALedger.Rows[0])).NumberOfAccountingPeriods;
CurrentNumberFwdPostingPeriods = LedgerRow.NumberFwdPostingPeriods;
NewNumberFwdPostingPeriods = ((ALedgerRow)(InspectDS.ALedger.Rows[0])).NumberFwdPostingPeriods;
// retrieve currently saved calendar start date (start date of financial year)
AAccountingPeriodTable CalendarTable = AAccountingPeriodAccess.LoadByPrimaryKey(ALedgerNumber, 1, Transaction);
CurrentCalendarStartDate = DateTime.MinValue;
if (CalendarTable.Count > 0)
{
CurrentCalendarStartDate = ((AAccountingPeriodRow)CalendarTable.Rows[0]).PeriodStartDate;
}
// update accounting periods (calendar):
// this only needs to be done if the calendar mode is changed
// or if calendar mode is monthly and the start date has changed
// or if not monthly and number of periods has changed
if (((ALedgerRow)(InspectDS.ALedger.Rows[0])).CalendarMode != LedgerRow.CalendarMode)
{
CreateCalendar = true;
}
else if (((ALedgerRow)(InspectDS.ALedger.Rows[0])).CalendarMode
&& (ACalendarStartDate != CurrentCalendarStartDate))
{
CreateCalendar = true;
}
else if (!((ALedgerRow)(InspectDS.ALedger.Rows[0])).CalendarMode
&& (NewNumberPeriods != CurrentNumberPeriods))
{
CreateCalendar = true;
}
if (!CreateCalendar
&& (NewNumberFwdPostingPeriods < CurrentNumberFwdPostingPeriods))
{
CreateCalendar = true;
}
if (!CreateCalendar
&& (NewNumberFwdPostingPeriods > CurrentNumberFwdPostingPeriods))
{
// in this case only extend the periods (as there may already be existing transactions)
ExtendFwdPeriods = true;
}
// now perform the actual update of accounting periods (calendar)
if (CreateCalendar)
{
// first make sure all accounting period records are deleted
if (AAccountingPeriodAccess.CountViaALedger(ALedgerNumber, Transaction) > 0)
{
AAccountingPeriodTable TemplateTable = new AAccountingPeriodTable();
AAccountingPeriodRow TemplateRow = TemplateTable.NewRowTyped(false);
TemplateRow.LedgerNumber = ALedgerNumber;
AAccountingPeriodAccess.DeleteUsingTemplate(TemplateRow, null, Transaction);
}
// now create all accounting period records according to monthly calendar mode
// (at the same time create forwarding periods. If number of forwarding periods also
// changes with this saving method then this will be dealt with further down in the code)
NewAccountingPeriodTable = new AAccountingPeriodTable();
PeriodStartDate = ACalendarStartDate;
for (Period = 1; Period <= NewNumberPeriods; Period++)
{
NewAccountingPeriodRow = NewAccountingPeriodTable.NewRowTyped();
NewAccountingPeriodRow.LedgerNumber = ALedgerNumber;
NewAccountingPeriodRow.AccountingPeriodNumber = Period;
NewAccountingPeriodRow.PeriodStartDate = PeriodStartDate;
//TODO: Calendar vs Financial Date Handling - Check for current ledger number of periods
if ((((ALedgerRow)(InspectDS.ALedger.Rows[0])).NumberOfAccountingPeriods == 13)
&& (Period == 12))
{
// in case of 12 periods the second last period represents the last month except for the very last day
NewAccountingPeriodRow.PeriodEndDate = PeriodStartDate.AddMonths(1).AddDays(-2);
}
else if ((((ALedgerRow)(InspectDS.ALedger.Rows[0])).NumberOfAccountingPeriods == 13)
&& (Period == 13))
{
// in case of 13 periods the last period just represents the very last day of the financial year
NewAccountingPeriodRow.PeriodEndDate = PeriodStartDate;
}
else
{
NewAccountingPeriodRow.PeriodEndDate = PeriodStartDate.AddMonths(1).AddDays(-1);
}
NewAccountingPeriodRow.AccountingPeriodDesc = PeriodStartDate.ToString("MMMM");
NewAccountingPeriodTable.Rows.Add(NewAccountingPeriodRow);
PeriodStartDate = NewAccountingPeriodRow.PeriodEndDate.AddDays(1);
}
AAccountingPeriodAccess.SubmitChanges(NewAccountingPeriodTable, Transaction);
TCacheableTablesManager.GCacheableTablesManager.MarkCachedTableNeedsRefreshing(
TCacheableFinanceTablesEnum.AccountingPeriodList.ToString());
CurrentNumberPeriods = NewNumberPeriods;
}
// check if any new forwarding periods need to be created
if (CreateCalendar || ExtendFwdPeriods)
{
// now create new forwarding posting periods (if at all needed)
NewAccountingPeriodTable = new AAccountingPeriodTable();
// if calendar was created then there are no forward periods yet
if (CreateCalendar)
{
Period = CurrentNumberPeriods + 1;
}
else
{
Period = CurrentNumberPeriods + CurrentNumberFwdPostingPeriods + 1;
}
while (Period <= NewNumberPeriods + NewNumberFwdPostingPeriods)
{
AccountingPeriodTable = AAccountingPeriodAccess.LoadByPrimaryKey(ALedgerNumber,
Period - CurrentNumberPeriods,
Transaction);
AccountingPeriodRow = (AAccountingPeriodRow)AccountingPeriodTable.Rows[0];
NewAccountingPeriodRow = NewAccountingPeriodTable.NewRowTyped();
NewAccountingPeriodRow.LedgerNumber = ALedgerNumber;
NewAccountingPeriodRow.AccountingPeriodNumber = Period;
NewAccountingPeriodRow.AccountingPeriodDesc = AccountingPeriodRow.AccountingPeriodDesc;
NewAccountingPeriodRow.PeriodStartDate = AccountingPeriodRow.PeriodStartDate.AddYears(1);
NewAccountingPeriodRow.PeriodEndDate = AccountingPeriodRow.PeriodEndDate.AddYears(1);
NewAccountingPeriodTable.Rows.Add(NewAccountingPeriodRow);
Period++;
}
AAccountingPeriodAccess.SubmitChanges(NewAccountingPeriodTable, Transaction);
TCacheableTablesManager.GCacheableTablesManager.MarkCachedTableNeedsRefreshing(
TCacheableFinanceTablesEnum.AccountingPeriodList.ToString());
// also create new general ledger master periods with balances
CurrentLastFwdPeriod = LedgerRow.NumberOfAccountingPeriods + CurrentNumberFwdPostingPeriods;
NewLastFwdPeriod = LedgerRow.NumberOfAccountingPeriods + NewNumberFwdPostingPeriods;
// TODO: the following 2 lines would need to replace the 2 lines above if not all possible forward periods are created initially
//CurrentLastFwdPeriod = LedgerRow.CurrentPeriod + CurrentNumberFwdPostingPeriods;
//NewLastFwdPeriod = LedgerRow.CurrentPeriod + NewNumberFwdPostingPeriods;
GLMTable = new AGeneralLedgerMasterTable();
AGeneralLedgerMasterRow template = GLMTable.NewRowTyped(false);
template.LedgerNumber = ALedgerNumber;
template.Year = LedgerRow.CurrentFinancialYear;
// find all general ledger master records of the current financial year for given ledger
GLMTable = AGeneralLedgerMasterAccess.LoadUsingTemplate(template, Transaction);
NewGLMPeriodTable = new AGeneralLedgerMasterPeriodTable();
foreach (DataRow Row in GLMTable.Rows)
{
// for each of the general ledger master records of the current financial year set the
// new, extended forwarding glm period records (most likely they will not exist yet
// but if they do then update values)
GLMRow = (AGeneralLedgerMasterRow)Row;
GLMPeriodTable =
AGeneralLedgerMasterPeriodAccess.LoadByPrimaryKey(GLMRow.GlmSequence, CurrentLastFwdPeriod, Transaction);
if (GLMPeriodTable.Count > 0)
{
GLMPeriodRow = (AGeneralLedgerMasterPeriodRow)GLMPeriodTable.Rows[0];
for (Period = CurrentLastFwdPeriod + 1; Period <= NewLastFwdPeriod; Period++)
{
if (AGeneralLedgerMasterPeriodAccess.Exists(GLMPeriodRow.GlmSequence, Period, Transaction))
{
// if the record already exists then just change values
TempGLMPeriodTable = AGeneralLedgerMasterPeriodAccess.LoadByPrimaryKey(GLMPeriodRow.GlmSequence,
Period,
Transaction);
TempGLMPeriodRow = (AGeneralLedgerMasterPeriodRow)TempGLMPeriodTable.Rows[0];
TempGLMPeriodRow.ActualBase = GLMPeriodRow.ActualBase;
TempGLMPeriodRow.ActualIntl = GLMPeriodRow.ActualIntl;
if (!GLMPeriodRow.IsActualForeignNull())
{
TempGLMPeriodRow.ActualForeign = GLMPeriodRow.ActualForeign;
}
else
{
TempGLMPeriodRow.SetActualForeignNull();
}
NewGLMPeriodTable.Merge(TempGLMPeriodTable, true);
}
else
{
// add new row since it does not exist yet
NewGLMPeriodRow = NewGLMPeriodTable.NewRowTyped();
NewGLMPeriodRow.GlmSequence = GLMPeriodRow.GlmSequence;
NewGLMPeriodRow.PeriodNumber = Period;
NewGLMPeriodRow.ActualBase = GLMPeriodRow.ActualBase;
NewGLMPeriodRow.ActualIntl = GLMPeriodRow.ActualIntl;
if (!GLMPeriodRow.IsActualForeignNull())
{
NewGLMPeriodRow.ActualForeign = GLMPeriodRow.ActualForeign;
}
else
{
NewGLMPeriodRow.SetActualForeignNull();
}
NewGLMPeriodTable.Rows.Add(NewGLMPeriodRow);
}
}
// remove periods if the number of periods + forwarding periods has been reduced
int NumberOfExistingPeriods = LedgerRow.NumberOfAccountingPeriods + LedgerRow.NumberFwdPostingPeriods;
while ((NewNumberPeriods + NewNumberFwdPostingPeriods) < NumberOfExistingPeriods)
{
AGeneralLedgerMasterPeriodAccess.DeleteByPrimaryKey(GLMPeriodRow.GlmSequence,
NumberOfExistingPeriods,
Transaction);
NumberOfExistingPeriods--;
}
}
}
// just one SubmitChanges for all records needed
AGeneralLedgerMasterPeriodAccess.SubmitChanges(NewGLMPeriodTable, Transaction);
}
}
// update a_ledger_init_flag records for:
// suspense account flag: "SUSP-ACCT"
// budget flag: "BUDGET"
// base currency: "CURRENCY"
// international currency: "INTL-CURRENCY" (this is a new flag for OpenPetra)
// current period (start of ledger date): CURRENT-PERIOD
// calendar settings: CAL
AddOrRemoveLedgerInitFlag(ALedgerNumber, MFinanceConstants.LEDGER_INIT_FLAG_SUSP_ACC, LedgerRow.SuspenseAccountFlag,
Transaction);
AddOrRemoveLedgerInitFlag(ALedgerNumber, MFinanceConstants.LEDGER_INIT_FLAG_BUDGET, LedgerRow.BudgetControlFlag, Transaction);
AddOrRemoveLedgerInitFlag(ALedgerNumber, MFinanceConstants.LEDGER_INIT_FLAG_CURRENCY, !LedgerRow.IsBaseCurrencyNull(),
Transaction);
AddOrRemoveLedgerInitFlag(ALedgerNumber, MFinanceConstants.LEDGER_INIT_FLAG_INTL_CURRENCY, !LedgerRow.IsIntlCurrencyNull(),
Transaction);
AddOrRemoveLedgerInitFlag(ALedgerNumber, MFinanceConstants.LEDGER_INIT_FLAG_CURRENT_PERIOD, !LedgerRow.IsCurrentPeriodNull(),
Transaction);
AddOrRemoveLedgerInitFlag(ALedgerNumber, MFinanceConstants.LEDGER_INIT_FLAG_CAL, !LedgerRow.IsNumberOfAccountingPeriodsNull(),
Transaction);
GLSetupTDSAccess.SubmitChanges(InspectDS);
SubmissionOK = true;
});
}
catch (Exception ex)
{
TLogging.Log(String.Format("Method:{0} - Unexpected error!{1}{1}{2}",
Utilities.GetMethodSignature(),
Environment.NewLine,
ex.Message));
throw ex;
}
return TSubmitChangesResult.scrOK;
}
private void LoadData()
{
bool NewTransaction;
TDBTransaction transaction = DBAccess.GDBAccessObj.GetNewOrExistingTransaction(IsolationLevel.ReadCommitted,
TEnforceIsolationLevel.eilMinimum,
out NewTransaction);
try
{
periodTable = AAccountingPeriodAccess.LoadViaALedger(intLedgerNumber, transaction);
if (NewTransaction)
{
DBAccess.GDBAccessObj.CommitTransaction();
}
}
catch (Exception)
{
DBAccess.GDBAccessObj.RollbackTransaction();
throw;
}
}
private void ParseBatchLine(ref AGiftBatchRow AGiftBatch,
ref TDBTransaction ATransaction,
ref ALedgerTable ALedgerTable,
ref string AImportMessage,
int ARowNumber,
TVerificationResultCollection AMessages,
TValidationControlsDict AValidationControlsDictBatch,
AAccountTable AValidationAccountTable,
AAccountPropertyTable AValidationAccountPropertyTable,
AAccountingPeriodTable AValidationAccountingPeriodTable,
ACostCentreTable AValidationCostCentreTable,
ACorporateExchangeRateTable AValidationCorporateExchTable,
ACurrencyTable AValidationCurrencyTable)
{
// There are 8 elements to import (the last of which can be blank)
string BatchDescription = ImportString(Catalog.GetString("Batch description"),
FMainDS.AGiftBatch.ColumnBatchDescription, AValidationControlsDictBatch);
string BankAccountCode = ImportString(Catalog.GetString("Bank account code"),
FMainDS.AGiftBatch.ColumnBankAccountCode, AValidationControlsDictBatch).ToUpper();
decimal HashTotal = ImportDecimal(Catalog.GetString("Hash total"),
FMainDS.AGiftBatch.ColumnHashTotal, ARowNumber, AMessages, AValidationControlsDictBatch);
DateTime GlEffectiveDate = ImportDate(Catalog.GetString("Effective Date"),
FMainDS.AGiftBatch.ColumnGlEffectiveDate, ARowNumber, AMessages, AValidationControlsDictBatch);
AImportMessage = "Creating new batch";
// This call sets: BatchNumber, BatchYear, BatchPeriod, GlEffectiveDate, ExchangeRateToBase, BatchDescription, BankAccountCode
// BankCostCentre and CurrencyCode. The effective date will NOT be modified.
// The first three are not validated because they should be ok by default
AGiftBatch = TGiftBatchFunctions.CreateANewGiftBatchRow(ref FMainDS,
ref ATransaction,
ref ALedgerTable,
FLedgerNumber,
GlEffectiveDate,
false);
// Now we modify some of these in the light of the imported data
AGiftBatch.BatchDescription = BatchDescription;
AGiftBatch.BankAccountCode = BankAccountCode;
AGiftBatch.HashTotal = HashTotal;
AGiftBatch.CurrencyCode = ImportString(Catalog.GetString("Currency code"),
FMainDS.AGiftBatch.ColumnCurrencyCode, AValidationControlsDictBatch);
AGiftBatch.ExchangeRateToBase = ImportDecimal(Catalog.GetString("Exchange rate to base"),
FMainDS.AGiftBatch.ColumnExchangeRateToBase, ARowNumber, AMessages, AValidationControlsDictBatch);
AGiftBatch.BankCostCentre = ImportString(Catalog.GetString("Bank cost centre"),
FMainDS.AGiftBatch.ColumnBankCostCentre, AValidationControlsDictBatch).ToUpper();
AGiftBatch.GiftType = ImportString(Catalog.GetString("Gift type"),
FMainDS.AGiftBatch.ColumnGiftType, AValidationControlsDictBatch);
// If GiftType was empty, will default to GIFT
// In all cases we ensure that the case entered by the user is converted to the case of our constants
if ((AGiftBatch.GiftType == String.Empty) || (String.Compare(AGiftBatch.GiftType, MFinanceConstants.GIFT_TYPE_GIFT, true) == 0))
{
AGiftBatch.GiftType = MFinanceConstants.GIFT_TYPE_GIFT;
}
else if (String.Compare(AGiftBatch.GiftType, MFinanceConstants.GIFT_TYPE_GIFT_IN_KIND, true) == 0)
{
AGiftBatch.GiftType = MFinanceConstants.GIFT_TYPE_GIFT_IN_KIND;
}
else if (String.Compare(AGiftBatch.GiftType, MFinanceConstants.GIFT_TYPE_OTHER, true) == 0)
{
AGiftBatch.GiftType = MFinanceConstants.GIFT_TYPE_OTHER;
}
int messageCountBeforeValidate = AMessages.Count;
// Do our standard gift batch validation checks on this row
AImportMessage = Catalog.GetString("Validating the gift batch data");
AGiftBatchValidation.Validate(this, AGiftBatch, ref AMessages, AValidationControlsDictBatch);
// And do the additional manual ones
AImportMessage = Catalog.GetString("Additional validation of the gift batch data");
TSharedFinanceValidation_Gift.ValidateGiftBatchManual(this, AGiftBatch, ref AMessages, AValidationControlsDictBatch,
AValidationAccountTable, AValidationCostCentreTable, AValidationAccountPropertyTable, AValidationAccountingPeriodTable,
AValidationCorporateExchTable, AValidationCurrencyTable,
FLedgerBaseCurrency, FLedgerIntlCurrency);
for (int i = messageCountBeforeValidate; i < AMessages.Count; i++)
{
((TVerificationResult)AMessages[i]).OverrideResultContext(String.Format(MCommonConstants.StrValidationErrorInLine, ARowNumber));
if (AMessages[i] is TScreenVerificationResult)
{
TVerificationResult downgrade = new TVerificationResult((TScreenVerificationResult)AMessages[i]);
AMessages.RemoveAt(i);
AMessages.Insert(i, downgrade);
}
}
if (AGiftBatch.ExchangeRateToBase > 10000000) // Huge numbers here indicate that the decimal comma/point is incorrect.
{
AMessages.Add(new TVerificationResult(String.Format(MCommonConstants.StrImportValidationErrorInLine, ARowNumber),
String.Format(Catalog.GetString("A huge exchange rate of {0} suggests a decimal point format problem."),
AGiftBatch.ExchangeRateToBase),
TResultSeverity.Resv_Noncritical));
}
}
/// <summary>
/// Validates the Gift Batch data.
/// </summary>
/// <param name="AContext">Context that describes where the data validation failed.</param>
/// <param name="ARow">The <see cref="DataRow" /> which holds the the data against which the validation is run.</param>
/// <param name="AVerificationResultCollection">Will be filled with any <see cref="TVerificationResult" /> items if
/// data validation errors occur.</param>
/// <param name="AValidationControlsDict">A <see cref="TValidationControlsDict" /> containing the Controls that
/// display data that is about to be validated.</param>
/// <param name="AAccountTableRef">Account Table. A reference to this table is REQUIRED when importing - optional otherwise</param>
/// <param name="ACostCentreTableRef">Cost centre table. A reference to this table is REQUIRED when importing - optional otherwise</param>
/// <param name="AAccountPropertyTableRef">Account Property Table. A reference to this table is REQUIRED when importing - optional otherwise</param>
/// <param name="AAccountingPeriodTableRef">Accounting Period Table. A reference to this table is REQUIRED when importing - optional otherwise</param>
/// <param name="ACorporateExchangeTableRef">Corporate exchange rate table. A reference to this table is REQUIRED when importing - optional otherwise</param>
/// <param name="ACurrencyTableRef">Currency table. A reference to this table is REQUIRED when importing - optional otherwise</param>
/// <param name="ABaseCurrency">Ledger base currency. Required when importing</param>
/// <param name="AInternationalCurrency">Ledger international currency. Required when importing</param>
/// <returns>True if the validation found no data validation errors, otherwise false.</returns>
public static bool ValidateGiftBatchManual(object AContext,
AGiftBatchRow ARow,
ref TVerificationResultCollection AVerificationResultCollection,
TValidationControlsDict AValidationControlsDict,
AAccountTable AAccountTableRef = null,
ACostCentreTable ACostCentreTableRef = null,
AAccountPropertyTable AAccountPropertyTableRef = null,
AAccountingPeriodTable AAccountingPeriodTableRef = null,
ACorporateExchangeRateTable ACorporateExchangeTableRef = null,
ACurrencyTable ACurrencyTableRef = null,
string ABaseCurrency = null,
string AInternationalCurrency = null)
{
DataColumn ValidationColumn;
TValidationControlsData ValidationControlsData;
TScreenVerificationResult VerificationResult;
object ValidationContext;
int VerifResultCollAddedCount = 0;
// Don't validate deleted or posted DataRows
if ((ARow.RowState == DataRowState.Deleted) || (ARow.BatchStatus == MFinanceConstants.BATCH_POSTED))
{
return true;
}
bool IsImporting = AContext.ToString().Contains("Importing");
// Bank Account Code must be active
ValidationColumn = ARow.Table.Columns[AGiftBatchTable.ColumnBankAccountCodeId];
ValidationContext = ARow.BankAccountCode;
if (AValidationControlsDict.TryGetValue(ValidationColumn, out ValidationControlsData))
{
if (!ARow.IsBankAccountCodeNull() && (AAccountTableRef != null))
{
// We even need to check that the code exists!
AAccountRow foundRow = (AAccountRow)AAccountTableRef.Rows.Find(new object[] { ARow.LedgerNumber, ARow.BankAccountCode });
if ((foundRow == null)
&& AVerificationResultCollection.Auto_Add_Or_AddOrRemove(
AContext,
new TVerificationResult(ValidationContext,
String.Format(Catalog.GetString("Unknown bank account code '{0}'."), ARow.BankAccountCode),
TResultSeverity.Resv_Critical),
ValidationColumn))
{
VerifResultCollAddedCount++;
}
// If it does exist and the account is a foreign currency account then the batch currency must match
if ((foundRow != null) && foundRow.ForeignCurrencyFlag)
{
if ((foundRow.ForeignCurrencyCode != ARow.CurrencyCode) && AVerificationResultCollection.Auto_Add_Or_AddOrRemove(
AContext,
new TVerificationResult(ValidationContext,
String.Format(Catalog.GetString(
"The bank account code '{0}' is a foreign currency account so the currency code for the batch must be '{1}'."),
ARow.BankAccountCode, foundRow.ForeignCurrencyCode),
TResultSeverity.Resv_Critical),
ValidationColumn))
{
VerifResultCollAddedCount++;
}
}
// If it does exist it must be a posting account
if (foundRow != null)
{
if (!foundRow.PostingStatus && AVerificationResultCollection.Auto_Add_Or_AddOrRemove(
AContext,
new TVerificationResult(ValidationContext,
String.Format(Catalog.GetString(
"The bank account code '{0}' is not a posting account."),
ARow.BankAccountCode),
TResultSeverity.Resv_Critical),
ValidationColumn))
{
VerifResultCollAddedCount++;
}
}
if ((foundRow != null) && (ARow.GiftType == MFinanceConstants.GIFT_TYPE_GIFT))
{
// The account must be a bank account as defined in the AccountProperty table
if (AAccountPropertyTableRef != null)
{
AAccountPropertyRow foundRow2 = (AAccountPropertyRow)AAccountPropertyTableRef.Rows.Find(
new object[] { ARow.LedgerNumber, ARow.BankAccountCode, "BANK ACCOUNT", "true" });
if ((foundRow2 == null) && AVerificationResultCollection.Auto_Add_Or_AddOrRemove(
AContext,
new TVerificationResult(ValidationContext,
String.Format(Catalog.GetString(
"The bank account code '{0}' must be associated with a real 'Bank Account' when the gift type is a 'Gift'."),
ARow.BankAccountCode),
TResultSeverity.Resv_Critical),
ValidationColumn))
{
VerifResultCollAddedCount++;
}
}
}
}
VerificationResult = (TScreenVerificationResult)TStringChecks.ValidateValueIsActive(ARow.LedgerNumber,
AAccountTableRef,
ValidationContext.ToString(),
AAccountTable.GetAccountActiveFlagDBName(),
AContext,
ValidationColumn,
ValidationControlsData.ValidationControl);
// Handle addition/removal to/from TVerificationResultCollection
if ((VerificationResult != null)
&& AVerificationResultCollection.Auto_Add_Or_AddOrRemove(AContext, VerificationResult, ValidationColumn, true))
{
VerifResultCollAddedCount++;
}
}
// Bank Cost Centre Code validation
ValidationColumn = ARow.Table.Columns[AGiftBatchTable.ColumnBankCostCentreId];
ValidationContext = ARow.BankCostCentre;
if (AValidationControlsDict.TryGetValue(ValidationColumn, out ValidationControlsData))
{
if (!ARow.IsBankCostCentreNull() && (ACostCentreTableRef != null))
{
// We even need to check that the code exists!
ACostCentreRow foundRow = (ACostCentreRow)ACostCentreTableRef.Rows.Find(new object[] { ARow.LedgerNumber, ARow.BankCostCentre });
if ((foundRow == null)
&& AVerificationResultCollection.Auto_Add_Or_AddOrRemove(
AContext,
new TScreenVerificationResult(ValidationContext,
ValidationColumn,
String.Format(Catalog.GetString("Unknown Bank Cost Centre: '{0}'."), ARow.BankCostCentre),
ValidationControlsData.ValidationControl,
TResultSeverity.Resv_Critical),
ValidationColumn))
{
VerifResultCollAddedCount++;
}
// Even if the cost centre exists it must be a 'posting' cost centre
if (foundRow != null)
{
if (!foundRow.PostingCostCentreFlag && AVerificationResultCollection.Auto_Add_Or_AddOrRemove(
AContext,
new TScreenVerificationResult(ValidationContext,
ValidationColumn,
String.Format(Catalog.GetString("The cost centre '{0}' is not a Posting Cost Centre."), ARow.BankCostCentre),
ValidationControlsData.ValidationControl,
TResultSeverity.Resv_Critical),
ValidationColumn))
{
VerifResultCollAddedCount++;
}
}
}
// Bank Cost Centre Code must be active
VerificationResult = (TScreenVerificationResult)TStringChecks.ValidateValueIsActive(ARow.LedgerNumber,
ACostCentreTableRef,
ValidationContext.ToString(),
ACostCentreTable.GetCostCentreActiveFlagDBName(),
AContext,
ValidationColumn,
ValidationControlsData.ValidationControl);
// Handle addition/removal to/from TVerificationResultCollection
if ((VerificationResult != null)
&& AVerificationResultCollection.Auto_Add_Or_AddOrRemove(AContext, VerificationResult, ValidationColumn, true))
{
VerifResultCollAddedCount++;
}
}
// Currency Code validation
ValidationColumn = ARow.Table.Columns[AGiftBatchTable.ColumnCurrencyCodeId];
ValidationContext = ARow.BatchNumber;
if (AValidationControlsDict.TryGetValue(ValidationColumn, out ValidationControlsData))
{
if (!ARow.IsCurrencyCodeNull() && (ACurrencyTableRef != null))
{
// Currency code must exist in the currency table
ACurrencyRow foundRow = (ACurrencyRow)ACurrencyTableRef.Rows.Find(ARow.CurrencyCode);
if ((foundRow == null)
&& AVerificationResultCollection.Auto_Add_Or_AddOrRemove(
AContext,
new TScreenVerificationResult(ValidationContext,
ValidationColumn,
String.Format(Catalog.GetString("Unknown currency code '{0}'."), ARow.CurrencyCode),
ValidationControlsData.ValidationControl,
TResultSeverity.Resv_Critical),
ValidationColumn))
{
VerifResultCollAddedCount++;
}
}
}
// 'Exchange Rate' must be greater than 0
ValidationColumn = ARow.Table.Columns[AGiftBatchTable.ColumnExchangeRateToBaseId];
ValidationContext = ARow.BatchNumber;
if (AValidationControlsDict.TryGetValue(ValidationColumn, out ValidationControlsData))
{
if (!ARow.IsExchangeRateToBaseNull())
{
VerificationResult = (TScreenVerificationResult)TNumericalChecks.IsPositiveDecimal(ARow.ExchangeRateToBase,
ValidationControlsData.ValidationControlLabel +
(IsImporting ? String.Empty : " of Batch Number " + ValidationContext.ToString()),
AContext, ValidationColumn, ValidationControlsData.ValidationControl);
// Handle addition/removal to/from TVerificationResultCollection
if (AVerificationResultCollection.Auto_Add_Or_AddOrRemove(AContext, VerificationResult, ValidationColumn, true))
{
VerifResultCollAddedCount++;
}
// Exchange rate must be 1.00 if the currency is the the base ledger currency
if ((ABaseCurrency != null)
&& (!ARow.IsCurrencyCodeNull())
&& (ARow.CurrencyCode == ABaseCurrency)
&& (ARow.ExchangeRateToBase != 1.00m))
{
if (AVerificationResultCollection.Auto_Add_Or_AddOrRemove(AContext,
new TScreenVerificationResult(ValidationContext,
ValidationColumn,
Catalog.GetString("A batch in the ledger base currency must have exchange rate of 1.00."),
ValidationControlsData.ValidationControl,
TResultSeverity.Resv_Critical),
ValidationColumn))
{
VerifResultCollAddedCount++;
}
}
}
}
// 'Effective From Date' must be valid
ValidationColumn = ARow.Table.Columns[AGiftBatchTable.ColumnGlEffectiveDateId];
ValidationContext = ARow.BatchNumber;
DateTime StartDateCurrentPeriod;
DateTime EndDateLastForwardingPeriod;
TSharedFinanceValidationHelper.GetValidPostingDateRange(ARow.LedgerNumber,
out StartDateCurrentPeriod,
out EndDateLastForwardingPeriod);
if (AValidationControlsDict.TryGetValue(ValidationColumn, out ValidationControlsData))
{
VerificationResult = (TScreenVerificationResult)TDateChecks.IsDateBetweenDates(ARow.GlEffectiveDate,
StartDateCurrentPeriod,
EndDateLastForwardingPeriod,
ValidationControlsData.ValidationControlLabel + (IsImporting ? String.Empty : " of Batch Number " + ValidationContext.ToString()),
TDateBetweenDatesCheckType.dbdctUnspecific,
TDateBetweenDatesCheckType.dbdctUnspecific,
AContext,
ValidationColumn,
ValidationControlsData.ValidationControl);
// Handle addition/removal to/from TVerificationResultCollection
if (AVerificationResultCollection.Auto_Add_Or_AddOrRemove(AContext, VerificationResult, ValidationColumn, true))
{
VerifResultCollAddedCount++;
}
// If the GL date was good we need to have a corporate exchange rate for base currency to Intl for the first day of the period
if ((VerificationResult == null) && (ACorporateExchangeTableRef != null) && !ARow.IsGlEffectiveDateNull()
&& (ABaseCurrency != null) && (AInternationalCurrency != null) && (ABaseCurrency != AInternationalCurrency))
{
DateTime firstOfMonth;
if (TSharedFinanceValidationHelper.GetFirstDayOfAccountingPeriod(ARow.LedgerNumber, ARow.GlEffectiveDate, out firstOfMonth))
{
ACorporateExchangeRateRow foundRow = (ACorporateExchangeRateRow)ACorporateExchangeTableRef.Rows.Find(
new object[] { ABaseCurrency, AInternationalCurrency, firstOfMonth });
if ((foundRow == null)
&& AVerificationResultCollection.Auto_Add_Or_AddOrRemove(
AContext,
new TVerificationResult(ValidationContext,
String.Format(Catalog.GetString(
"International currency: there is no Corporate Exchange Rate defined for '{0}' to '{1}' for the month starting on '{2}'."),
ABaseCurrency, AInternationalCurrency,
StringHelper.DateToLocalizedString(firstOfMonth)),
TResultSeverity.Resv_Critical),
ValidationColumn))
{
VerifResultCollAddedCount++;
}
}
}
}
// Gift Type must be one of our predefined constants
ValidationColumn = ARow.Table.Columns[AGiftBatchTable.ColumnGiftTypeId];
ValidationContext = ARow.BatchNumber;
if (AValidationControlsDict.TryGetValue(ValidationColumn, out ValidationControlsData))
{
if (!ARow.IsGiftTypeNull())
{
// Ensure the gift type is correct and that it matches one of the allowable options (applies when importing)
if ((ARow.GiftType != MFinanceConstants.GIFT_TYPE_GIFT)
&& (ARow.GiftType != MFinanceConstants.GIFT_TYPE_GIFT_IN_KIND)
&& (ARow.GiftType != MFinanceConstants.GIFT_TYPE_OTHER))
{
if (AVerificationResultCollection.Auto_Add_Or_AddOrRemove(
AContext,
new TScreenVerificationResult(ValidationContext,
ValidationColumn,
String.Format(Catalog.GetString("Unknown gift type '{0}'. Expected one of '{1}', '{2}' or '{3}'"),
ARow.GiftType,
MFinanceConstants.GIFT_TYPE_GIFT, MFinanceConstants.GIFT_TYPE_GIFT_IN_KIND, MFinanceConstants.GIFT_TYPE_OTHER),
ValidationControlsData.ValidationControl,
TResultSeverity.Resv_Critical),
ValidationColumn))
{
VerifResultCollAddedCount++;
}
}
}
}
return VerifResultCollAddedCount == 0;
}
public static bool CanDeleteCorporateExchangeRate(DateTime ADateEffectiveFrom, string AIntlCurrency, string ATransactionCurrency)
{
bool ReturnValue = true;
TDBTransaction ReadTransaction = null;
DBAccess.GDBAccessObj.GetNewOrExistingAutoReadTransaction(IsolationLevel.ReadCommitted,
TEnforceIsolationLevel.eilMinimum,
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 >= '" + ADateEffectiveFrom + "'" +
" AND a_accounting_period.a_period_start_date_d <= '" + ADateEffectiveFrom + "'";
AAccountingPeriodTable AccountingPeriodTable = new AAccountingPeriodTable();
DBAccess.GDBAccessObj.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 <= '" + AccountingPeriodRow.PeriodEndDate + "'" +
" AND a_batch.a_date_effective_d >= '" + 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 = DBAccess.GDBAccessObj.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;
}
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
