/// <summary>
/// Load CostCentre hierarchy from the dataset into the tree view
/// </summary>
public void PopulateTreeView(GLSetupTDS MainDS)
{
FDuringInitialisation = true;
trvCostCentres.BeginUpdate();
trvCostCentres.Nodes.Clear();
// find the root cost centre
MainDS.ACostCentre.DefaultView.RowFilter =
ACostCentreTable.GetCostCentreToReportToDBName() + " IS NULL";
DataView view = new DataView(MainDS.ACostCentre);
view.Sort = ACostCentreTable.GetCostCentreCodeDBName();
InsertNodeIntoTreeView(null,
view,
(ACostCentreRow)MainDS.ACostCentre.DefaultView[0].Row);
MainDS.ACostCentre.DefaultView.RowFilter = "";
trvCostCentres.EndUpdate();
this.trvCostCentres.BeforeSelect += TreeViewBeforeSelect;
this.trvCostCentres.AfterSelect += TreeViewAfterSelect;
trvCostCentres.EndUpdate();
FDuringInitialisation = false;
if (trvCostCentres.Nodes.Count > 0)
{
SelectNodeByName(trvCostCentres.Nodes[0].Name); // Select the first item
}
}
/// <summary>
/// Helper method that we can call to initialise each of the filter/find comboBoxes
/// </summary>
private void InitFilterFindComboBox(TCmbAutoPopulated AClonedFromComboBox,
TCmbAutoComplete AFFInstance)
{
AFFInstance.DisplayMember = AClonedFromComboBox.DisplayMember;
AFFInstance.ValueMember = AClonedFromComboBox.ValueMember;
if (AClonedFromComboBox.Name.Contains("Account"))
{
// This is quicker than getting the cached table again
DataView dv = new DataView(FAccountTable.Copy());
dv.RowFilter = TFinanceControls.PrepareAccountFilter(true, false, false, false, "");
dv.Sort = String.Format("{0}", AAccountTable.GetAccountCodeDBName());
AFFInstance.DataSource = dv;
}
else if (AClonedFromComboBox.Name.Contains("CostCentre"))
{
// This is quicker than getting the cached table again
DataView dv = new DataView(FCostCentreTable.Copy());
dv.RowFilter = TFinanceControls.PrepareCostCentreFilter(true, false, false, false);
dv.Sort = String.Format("{0}", ACostCentreTable.GetCostCentreCodeDBName());
AFFInstance.DataSource = dv;
}
else
{
throw new Exception("Unexpected ComboBox name");
}
AFFInstance.DrawMode = DrawMode.OwnerDrawFixed;
AFFInstance.DrawItem += new DrawItemEventHandler(DrawComboBoxItem);
}
/// <summary>
/// Export the cost centres
/// </summary>
public static void ExportCostCentres(string AOutputPath,
char ACSVSeparator,
string ANewLine,
Int32 ALedgerNumber,
List <string> ACostCentres)
{
string filename = Path.GetFullPath(Path.Combine(AOutputPath, "costcentre.csv"));
Console.WriteLine("Writing file: " + filename);
StringBuilder sb = new StringBuilder();
ACostCentreTable costcentres = ACostCentreAccess.LoadViaALedger(ALedgerNumber, null);
costcentres.DefaultView.Sort = ACostCentreTable.GetCostCentreCodeDBName();
foreach (ACostCentreRow row in costcentres.Rows)
{
if (ACostCentres.Contains(row.CostCentreCode))
{
ACostCentreRow departmentRow = GetDepartmentCostCentre(costcentres,
row,
StringHelper.StrSplit(TAppSettingsManager.GetValue("SummaryCostCentres", "4300S"), ","));
sb.Append(StringHelper.StrMerge(new string[] { row.CostCentreCode, row.CostCentreName,
departmentRow.CostCentreName }, ACSVSeparator));
sb.Append(ANewLine);
}
}
StreamWriter sw = new StreamWriter(filename, false, Encoding.GetEncoding(1252));
sw.Write(sb.ToString());
sw.Close();
}
private DataTable GetCostCentreListTable(TDBTransaction AReadTransaction, System.Int32 ALedgerNumber, string ATableName)
{
StringCollection FieldList = new StringCollection();
FieldList.Add(ACostCentreTable.GetLedgerNumberDBName());
FieldList.Add(ACostCentreTable.GetCostCentreCodeDBName());
FieldList.Add(ACostCentreTable.GetCostCentreNameDBName());
FieldList.Add(ACostCentreTable.GetCostCentreToReportToDBName());
FieldList.Add(ACostCentreTable.GetPostingCostCentreFlagDBName());
FieldList.Add(ACostCentreTable.GetCostCentreActiveFlagDBName());
FieldList.Add(ACostCentreTable.GetCostCentreTypeDBName());
return(ACostCentreAccess.LoadViaALedger(ALedgerNumber, FieldList, AReadTransaction));
}
/// <summary>
/// Populates the Cost Centre, Account Code and DR Account Code ComboBoxes.
/// </summary>
/// <param name="ACostCentreComboBox">Cost Centre ComboBox Control</param>
/// <param name="AAccountCodeComboBox">Account Code ComboBox Control</param>
/// <param name="ADrAccountCodeComboBox">DR Account Code ComboBox Control</param>
/// <param name="ALedgerNumber">Ledger Number</param>
/// <param name="ACalledByReceivableScreen">Set this to true if the 'Admin Grants Receivable' screen is calling this Method.</param>
public static void PopulateComboBoxes(TCmbAutoPopulated ACostCentreComboBox,
TCmbAutoPopulated AAccountCodeComboBox,
TCmbAutoPopulated ADrAccountCodeComboBox,
Int32 ALedgerNumber,
bool ACalledByReceivableScreen)
{
string filter = String.Empty;
DataTable CostCentreListTable = TDataCache.TMFinance.GetCacheableFinanceTable(TCacheableFinanceTablesEnum.CostCentreList, ALedgerNumber);
CostCentreListTable.DefaultView.Sort = ACostCentreTable.GetCostCentreNameDBName() + " ASC";
if (ACalledByReceivableScreen)
{
filter = ACostCentreTable.GetPostingCostCentreFlagDBName() + " = true AND " +
ACostCentreTable.GetCostCentreTypeDBName() + " = 'Local'";
}
else
{
filter = ACostCentreTable.GetPostingCostCentreFlagDBName() + " = true";
}
ACostCentreComboBox.InitialiseUserControl(CostCentreListTable,
ACostCentreTable.GetCostCentreCodeDBName(), ACostCentreTable.GetCostCentreNameDBName(), null);
ACostCentreComboBox.AppearanceSetup(new int[] { -1, 300 }, 20);
ACostCentreComboBox.Filter = filter;
DataTable AccountListTable = TDataCache.TMFinance.GetCacheableFinanceTable(TCacheableFinanceTablesEnum.AccountList, ALedgerNumber);
AccountListTable.DefaultView.Sort = AAccountTable.GetAccountCodeDBName() + " ASC";
filter = AAccountTable.GetPostingStatusDBName() + " = true AND " +
AAccountTable.GetAccountTypeDBName().ToUpper() + " = 'INCOME'";
AAccountCodeComboBox.InitialiseUserControl(AccountListTable,
AAccountTable.GetAccountCodeDBName(), AAccountTable.GetAccountCodeShortDescDBName(), null);
AAccountCodeComboBox.AppearanceSetup(new int[] { -1, 300 }, 20);
AAccountCodeComboBox.Filter = filter;
DataTable DrAccountListTable = TDataCache.TMFinance.GetCacheableFinanceTable(TCacheableFinanceTablesEnum.AccountList, ALedgerNumber);
DrAccountListTable.DefaultView.Sort = AAccountTable.GetAccountCodeDBName() + " ASC";
filter = AAccountTable.GetPostingStatusDBName() + " = true AND " +
AAccountTable.GetAccountTypeDBName().ToUpper() + " = 'EXPENSE'";
ADrAccountCodeComboBox.InitialiseUserControl(DrAccountListTable,
AAccountTable.GetAccountCodeDBName(), AAccountTable.GetAccountCodeShortDescDBName(), null);
ADrAccountCodeComboBox.AppearanceSetup(new int[] { -1, 300 }, 20);
ADrAccountCodeComboBox.Filter = filter;
}
/// <summary>
/// return a list of costcentres that does not contail any costcentre linked to a person
/// </summary>
public static string WithoutPersonCostCentres(int ALedgerNumber, String ACostCentreList)
{
// remove all costcentres that report to a costcentre which name ends with Personalkosten
ACostCentreTable costcentres = ACostCentreAccess.LoadViaALedger(ALedgerNumber, null);
costcentres.DefaultView.Sort = ACostCentreTable.GetCostCentreCodeDBName();
string[] costcentresList = ACostCentreList.Split(new char[] { ',' });
List <string> newList = new List <string>();
foreach (string cc in costcentresList)
{
ACostCentreRow costcentre = (ACostCentreRow)costcentres.DefaultView.FindRows(cc)[0].Row;
ACostCentreRow parentCC = (ACostCentreRow)costcentres.DefaultView.FindRows(costcentre.CostCentreToReportTo)[0].Row;
if (!parentCC.CostCentreName.EndsWith("Personalkosten"))
{
newList.Add(cc);
}
}
return(String.Join(",", newList.ToArray()));
}
public static void GenerateHOSAReports(int ALedgerNumber,
int APeriodNumber,
int AIchNumber,
string ACurrencySelect,
out TVerificationResultCollection AVerificationResult
)
{
AVerificationResult = new TVerificationResultCollection();
//Begin the transaction
bool NewTransaction = false;
TDBTransaction DBTransaction = DBAccess.GDBAccessObj.GetNewOrExistingTransaction(IsolationLevel.ReadCommitted, out NewTransaction);
ALedgerTable ALedger = ALedgerAccess.LoadByPrimaryKey(ALedgerNumber, DBTransaction);
AJournalTable AJournal = new AJournalTable();
ATransactionTable ATransaction = new ATransactionTable();
ACostCentreTable ACostCentre = new ACostCentreTable();
try
{
#if TODO
ALedgerRow LedgerRow = PostingDS.ALedger[0];
//Find the Ledger Name = Partner Short Name
PPartnerTable PartnerTable = PPartnerAccess.LoadByPrimaryKey(LedgerRow.PartnerKey, DBTransaction);
PPartnerRow PartnerRow = (PPartnerRow)PartnerTable.Rows[0];
string LedgerName = PartnerRow.PartnerShortName;
#endif
//
// Load the Journals, and Transactions for this period:
String JournalQuery = "SELECT PUB_a_journal.* FROM PUB_a_batch, PUB_a_journal WHERE " +
"PUB_a_batch.a_ledger_number_i = " + ALedgerNumber +
" AND PUB_a_batch.a_batch_year_i = " + ALedger[0].CurrentFinancialYear +
" AND PUB_a_batch.a_batch_period_i = " + APeriodNumber +
" AND PUB_a_batch.a_batch_status_c = 'Posted'" +
" AND PUB_a_batch.a_ledger_number_i = PUB_a_journal.a_ledger_number_i" +
" AND PUB_a_batch.a_batch_number_i = PUB_a_journal.a_batch_number_i";
DBAccess.GDBAccessObj.SelectDT(AJournal, JournalQuery, DBTransaction);
String TransactionQuery = "SELECT PUB_a_transaction.* FROM PUB_a_batch, PUB_a_transaction WHERE " +
"PUB_a_batch.a_ledger_number_i = " + ALedgerNumber +
" AND PUB_a_batch.a_batch_year_i = " + ALedger[0].CurrentFinancialYear +
" AND PUB_a_batch.a_batch_period_i = " + APeriodNumber +
" AND PUB_a_batch.a_batch_status_c = 'Posted'" +
" AND PUB_a_batch.a_ledger_number_i = PUB_a_transaction.a_ledger_number_i" +
" AND PUB_a_batch.a_batch_number_i = PUB_a_transaction.a_batch_number_i";
DBAccess.GDBAccessObj.SelectDT(ATransaction, TransactionQuery, DBTransaction);
String CostCentreQuery = "SELECT * FROM a_cost_centre WHERE " +
ACostCentreTable.GetLedgerNumberDBName() + " = " + ALedgerNumber +
" AND " + ACostCentreTable.GetPostingCostCentreFlagDBName() + " = True" +
" AND " + ACostCentreTable.GetCostCentreTypeDBName() + " LIKE '" + MFinanceConstants.FOREIGN_CC_TYPE + "'" +
" ORDER BY " + ACostCentreTable.GetCostCentreCodeDBName();
DBAccess.GDBAccessObj.SelectDT(ACostCentre, CostCentreQuery, DBTransaction);
//Iterate through the cost centres
foreach (ACostCentreRow CostCentreRow in ACostCentre.Rows)
{
bool TransactionExists = false;
//Iterate through the journals
foreach (AJournalRow JournalRow in AJournal.Rows)
{
int BatchNumber = JournalRow.BatchNumber;
int JournalNumber = JournalRow.JournalNumber;
#if TODO
String TransFilter =
ATransactionTable.GetBatchNumberDBName() + " = " + BatchNumber.ToString() +
" AND " + ATransactionTable.GetJournalNumberDBName() + " = " + JournalNumber.ToString() +
" AND " + ATransactionTable.GetCostCentreCodeDBName() + " = '" + CostCentreRow.CostCentreCode + "'" +
" AND (" + ATransactionTable.GetIchNumberDBName() + " = 0" +
" OR " + ATransactionTable.GetIchNumberDBName() + " = " + AIchNumber.ToString() +
")";
DataRow[] FoundTransRows = BatchDS.ATransaction.Select(TransFilter);
foreach (DataRow untypedTransRow in FoundTransRows)
{
ATransactionRow TransactionRow = (ATransactionRow)untypedTransRow;
TransactionExists = true;
string DefaultData = ALedgerNumber.ToString() + "," +
LedgerName + "," +
APeriodNumber.ToString() + "," +
APeriodNumber.ToString() + "," +
CostCentreRow.CostCentreCode + "," +
"" + "," +
"" + "," +
"" + "," +
"A" + "," +
LedgerRow.CurrentFinancialYear.ToString() + "," +
LedgerRow.CurrentPeriod.ToString() + "," +
MFinanceConstants.MAX_PERIODS.ToString() + "," +
"h" + "," +
ACurrency + "," +
AIchNumber.ToString();
string ReportTitle = "Home Office Stmt of Acct: " + CostCentreRow.CostCentreName;
//call code for gl2120p.p Produces Account Detail, Analysis Attribute and HOSA Reprint reports.
/* RUN sm9000.w ("gl2120p.p",
* lv_report_title_c,
* lv_default_data_c).*/
//TODO: call code to produce reports
break;
}
#endif
if (TransactionExists)
{
//only need to run above code once for 1 transaction per cost centre code
break; //goto next cost centre else try next journal
}
}
}
if (NewTransaction)
{
DBAccess.GDBAccessObj.RollbackTransaction();
}
}
catch (Exception Exp)
{
if (NewTransaction)
{
DBAccess.GDBAccessObj.RollbackTransaction();
}
TLogging.Log(Exp.Message);
TLogging.Log(Exp.StackTrace);
throw;
}
}
private void CalculateAccountInfo()
{
FaccountInfo = new TAccountInfo(FledgerInfo);
bool blnIncomeFound = false;
bool blnExpenseFound = false;
String strIncomeAccount = TAccountTypeEnum.Income.ToString();
String strExpenseAccount = TAccountTypeEnum.Expense.ToString();
FaccountInfo.Reset();
FAccountList = new List <String>();
while (FaccountInfo.MoveNext())
{
if (FaccountInfo.PostingStatus)
{
if (FaccountInfo.AccountType == strIncomeAccount)
{
FAccountList.Add(FaccountInfo.AccountCode);
blnIncomeFound = true;
}
if (FaccountInfo.AccountType == strExpenseAccount)
{
FAccountList.Add(FaccountInfo.AccountCode);
blnExpenseFound = true;
}
}
}
if (!blnIncomeFound)
{
TVerificationResult tvt =
new TVerificationResult(Catalog.GetString("No Income Account found"),
Catalog.GetString("At least one income account is required."), "",
TPeriodEndErrorAndStatusCodes.PEEC_09.ToString(),
TResultSeverity.Resv_Critical);
FverificationResults.Add(tvt);
FHasCriticalErrors = true;
}
if (!blnExpenseFound)
{
TVerificationResult tvt =
new TVerificationResult(Catalog.GetString("No Expense Account found"),
Catalog.GetString("At least one expense account is required."), "",
TPeriodEndErrorAndStatusCodes.PEEC_10.ToString(),
TResultSeverity.Resv_Critical);
FverificationResults.Add(tvt);
FHasCriticalErrors = true;
}
FaccountInfo.SetSpecialAccountCode(TAccountPropertyEnum.ICH_ACCT);
if (FaccountInfo.IsValid)
{
FAccountList.Add(FaccountInfo.AccountCode);
}
else
{
TVerificationResult tvt =
new TVerificationResult(Catalog.GetString("No ICH_ACCT Account defined"),
Catalog.GetString("An ICH Account must be defined."), "",
TPeriodEndErrorAndStatusCodes.PEEC_11.ToString(),
TResultSeverity.Resv_Critical);
FverificationResults.Add(tvt);
FHasCriticalErrors = true;
}
TDBTransaction Transaction = null;
DBAccess.GDBAccessObj.GetNewOrExistingAutoReadTransaction(IsolationLevel.ReadCommitted,
TEnforceIsolationLevel.eilMinimum,
ref Transaction,
delegate
{
FCostCentreTbl = ACostCentreAccess.LoadViaALedger(FledgerInfo.LedgerNumber, Transaction);
});
FCostCentreTbl.DefaultView.Sort = ACostCentreTable.GetCostCentreCodeDBName();
}
