/// <summary>
/// Post a budget
/// </summary>
private static void PostBudget(int ALedgerNumber, ABudgetRow ABudgetRow, List <ABudgetPeriodRow> ABudgetPeriodRows)
{
//gb5300.p
string AccountCode = ABudgetRow.AccountCode;
string CostCentreList = ABudgetRow.CostCentreCode; /* posting CC and parents */
//Populate list of affected Cost Centres
CostCentreParentsList(ALedgerNumber, ref CostCentreList);
//Locate the row for the current account
AAccountRow AccountRow = (AAccountRow)GLPostingDS.AAccount.Rows.Find(new object[] { ALedgerNumber, AccountCode });
GLPostingDS.AGeneralLedgerMaster.DefaultView.Sort = String.Format("{0},{1},{2},{3}",
AGeneralLedgerMasterTable.GetLedgerNumberDBName(),
AGeneralLedgerMasterTable.GetYearDBName(),
AGeneralLedgerMasterTable.GetAccountCodeDBName(),
AGeneralLedgerMasterTable.GetCostCentreCodeDBName());
/* calculate values for budgets and store them in a temp table; uses lb_budget */
ProcessAccountParent(
ALedgerNumber,
AccountCode,
AccountRow.DebitCreditIndicator,
CostCentreList,
ABudgetRow,
ABudgetPeriodRows);
}
/// <summary>
/// Unpost a budget
/// </summary>
/// <param name="ABudgetRow"></param>
/// <param name="ALedgerNumber"></param>
/// <returns>true if it seemed to go OK</returns>
private static bool UnPostBudget(ABudgetRow ABudgetRow, int ALedgerNumber)
{
/* post the negative budget, which will result in an empty a_glm_period.budget */
// get the current budget value for each GLM Period, and unpost that budget
GLPostingDS.AGeneralLedgerMaster.DefaultView.Sort = String.Format("{0},{1},{2},{3}",
AGeneralLedgerMasterTable.GetLedgerNumberDBName(),
AGeneralLedgerMasterTable.GetYearDBName(),
AGeneralLedgerMasterTable.GetAccountCodeDBName(),
AGeneralLedgerMasterTable.GetCostCentreCodeDBName());
int glmIndex = GLPostingDS.AGeneralLedgerMaster.DefaultView.Find(
new object[] { ALedgerNumber, ABudgetRow.Year, ABudgetRow.AccountCode, ABudgetRow.CostCentreCode });
if (glmIndex != -1)
{
AGeneralLedgerMasterRow glmRow = (AGeneralLedgerMasterRow)GLPostingDS.AGeneralLedgerMaster.DefaultView[glmIndex].Row;
List <ABudgetPeriodRow> budgetPeriods = new List <ABudgetPeriodRow>();
for (int Period = 1; Period <= GLPostingDS.ALedger[0].NumberOfAccountingPeriods; Period++)
{
AGeneralLedgerMasterPeriodRow GeneralLedgerMasterPeriodRow =
(AGeneralLedgerMasterPeriodRow)GLPostingDS.AGeneralLedgerMasterPeriod.Rows.Find(
new object[] { glmRow.GlmSequence, Period });
ABudgetPeriodRow budgetPeriodRow = FBudgetTDS.ABudgetPeriod.NewRowTyped(true);
budgetPeriodRow.PeriodNumber = Period;
budgetPeriodRow.BudgetSequence = ABudgetRow.BudgetSequence;
// use negative amount for unposting
budgetPeriodRow.BudgetBase = -1 * GeneralLedgerMasterPeriodRow.BudgetBase;
// do not add to the budgetperiod table, but to our local list
budgetPeriods.Add(budgetPeriodRow);
}
PostBudget(ALedgerNumber, ABudgetRow, budgetPeriods);
}
ABudgetRow.BudgetStatus = false; //i.e. unposted
return(true);
}
/// <summary>
/// </summary>
public TGlmNewYearInit(TLedgerInfo ALedgerInfo, int AYear, TYearEnd AYearEndOperator)
{
FOldYearNum = AYear;
FNewYearNum = FOldYearNum + 1;
FLedgerInfo = ALedgerInfo;
FYearEndOperator = AYearEndOperator;
FLedgerAccountingPeriods = FLedgerInfo.NumberOfAccountingPeriods; // Don't call these properties in a loop,
FLedgerFwdPeriods = FLedgerInfo.NumberFwdPostingPeriods; // as they reload the row from the DB!
bool NewTransaction;
TDBTransaction Transaction = DBAccess.GDBAccessObj.GetNewOrExistingTransaction(IsolationLevel.ReadCommitted,
TEnforceIsolationLevel.eilMinimum,
out NewTransaction);
try
{
DataTable GlmTble = LoadTable(FLedgerInfo.LedgerNumber, FOldYearNum, Transaction);
FGlmPostingFrom.Merge(GlmTble);
GlmTble = LoadTable(FLedgerInfo.LedgerNumber, FNewYearNum, Transaction);
GlmTDS.AGeneralLedgerMaster.Merge(GlmTble);
GlmTDS.AGeneralLedgerMaster.DefaultView.Sort =
AGeneralLedgerMasterTable.GetAccountCodeDBName() + "," +
AGeneralLedgerMasterTable.GetCostCentreCodeDBName();
DataTable GlmpTbl = GetGlmpRows(FOldYearNum, Transaction, FLedgerAccountingPeriods);
FGlmpFrom.Merge(GlmpTbl);
FGlmpFrom.DefaultView.Sort = "a_glm_sequence_i,a_period_number_i";
GlmpTbl = GetGlmpRows(FNewYearNum, Transaction, 0);
GlmTDS.AGeneralLedgerMasterPeriod.Merge(GlmpTbl);
GlmTDS.AGeneralLedgerMasterPeriod.DefaultView.Sort = "a_glm_sequence_i,a_period_number_i";
}
finally
{
if (NewTransaction)
{
DBAccess.GDBAccessObj.RollbackTransaction();
}
}
}
/// <summary>
/// Post a budget
/// </summary>
private static void PostBudget(int ALedgerNumber, ABudgetRow ABudgetRow, List <ABudgetPeriodRow> ABudgetPeriodRows)
{
GLPostingDS.AGeneralLedgerMaster.DefaultView.Sort = String.Format("{0},{1},{2},{3}",
AGeneralLedgerMasterTable.GetLedgerNumberDBName(),
AGeneralLedgerMasterTable.GetYearDBName(),
AGeneralLedgerMasterTable.GetAccountCodeDBName(),
AGeneralLedgerMasterTable.GetCostCentreCodeDBName());
int glmRowIndex = GLPostingDS.AGeneralLedgerMaster.DefaultView.Find(new object[] {
ALedgerNumber,
ABudgetRow.Year,
ABudgetRow.AccountCode,
ABudgetRow.CostCentreCode
});
if (glmRowIndex == -1)
{
TGLPosting.CreateGLMYear(ref GLPostingDS,
ALedgerNumber,
ABudgetRow.Year,
ABudgetRow.AccountCode,
ABudgetRow.CostCentreCode);
glmRowIndex = GLPostingDS.AGeneralLedgerMaster.DefaultView.Find(new object[] {
ALedgerNumber,
ABudgetRow.Year,
ABudgetRow.AccountCode,
ABudgetRow.CostCentreCode
});
}
int GLMSequence = ((AGeneralLedgerMasterRow)GLPostingDS.AGeneralLedgerMaster.DefaultView[glmRowIndex].Row).GlmSequence;
/* Update totals for the General Ledger Master period record. */
foreach (ABudgetPeriodRow BPR in ABudgetPeriodRows)
{
AddBudgetValue(GLMSequence, BPR.PeriodNumber, BPR.BudgetBase);
}
}
/// <summary>
/// export all GL Balances in the given year, towards the specified cost centres
/// </summary>
public static void ExportGLBalances(string AOutputPath,
char ACSVSeparator,
string ANewLine,
Int32 ALedgerNumber,
Int32 AFinancialYear,
string ACostCentres,
string AIgnoreAccounts)
{
string filename = Path.GetFullPath(Path.Combine(AOutputPath, "balance.csv"));
Console.WriteLine("Writing file: " + filename);
string sql =
String.Format("SELECT GLM.{1} AS CostCentre, GLM.{0} AS Account, {2} AS StartBalance, GLMP.{3} AS EndBalance " +
"FROM PUB_{4} AS GLM, PUB_{10} AS A, PUB_{13} AS GLMP " +
"WHERE GLM.{5} = {6} AND {7} = {8} AND GLM.{1} IN ({9}) " +
"AND GLMP.{14} = GLM.{14} AND GLMP.{15} = 12 " +
"AND A.{5} = GLM.{5} AND A.{0} = GLM.{0} AND " +
"A.{11}=true AND NOT GLM.{0} IN ({12})" +
"ORDER BY GLM.{1}, GLM.{0}",
AGeneralLedgerMasterTable.GetAccountCodeDBName(),
AGeneralLedgerMasterTable.GetCostCentreCodeDBName(),
AGeneralLedgerMasterTable.GetStartBalanceBaseDBName(),
AGeneralLedgerMasterPeriodTable.GetActualBaseDBName(),
AGeneralLedgerMasterTable.GetTableDBName(),
AGeneralLedgerMasterTable.GetLedgerNumberDBName(),
ALedgerNumber,
AGeneralLedgerMasterTable.GetYearDBName(),
AFinancialYear,
"'" + ACostCentres.Replace(",", "','") + "'",
AAccountTable.GetTableDBName(),
AAccountTable.GetPostingStatusDBName(),
"'" + AIgnoreAccounts.Replace(",", "','") + "'",
AGeneralLedgerMasterPeriodTable.GetTableDBName(),
AGeneralLedgerMasterPeriodTable.GetGlmSequenceDBName(),
AGeneralLedgerMasterPeriodTable.GetPeriodNumberDBName());
TDBTransaction Transaction = new TDBTransaction();
DataTable balances = null;
DBAccess.ReadTransaction(ref Transaction,
delegate
{
balances = Transaction.DataBaseObj.SelectDT(sql, "balances", Transaction);
});
StringBuilder sb = new StringBuilder();
if (balances != null)
{
foreach (DataRow row in balances.Rows)
{
sb.Append(StringHelper.StrMerge(
new string[] {
row["CostCentre"].ToString(),
row["Account"].ToString(),
String.Format("{0:N}", Convert.ToDecimal(row["StartBalance"])),
String.Format("{0:N}", Convert.ToDecimal(row["EndBalance"]))
}, ACSVSeparator));
sb.Append(ANewLine);
}
}
StreamWriter sw = new StreamWriter(filename, false, Encoding.GetEncoding(1252));
sw.Write(sb.ToString());
sw.Close();
}