public static BudgetTDS LoadBudget(Int32 ALedgerNumber) { BudgetTDS MainDS = new BudgetTDS(); TDBTransaction Transaction = null; DBAccess.GDBAccessObj.GetNewOrExistingAutoReadTransaction(IsolationLevel.ReadCommitted, TEnforceIsolationLevel.eilMinimum, ref Transaction, delegate { //TODO: need to filter on Year ABudgetAccess.LoadViaALedger(MainDS, ALedgerNumber, Transaction); ABudgetRevisionAccess.LoadViaALedger(MainDS, ALedgerNumber, Transaction); //TODO: need to filter on ABudgetPeriod using LoadViaBudget or LoadViaUniqueKey ABudgetPeriodAccess.LoadAll(MainDS, Transaction); ALedgerAccess.LoadByPrimaryKey(MainDS, ALedgerNumber, Transaction); // ABudgetPeriodTable BudgetPeriodTable = new ABudgetPeriodTable(); // ABudgetPeriodRow TemplateRow = (ABudgetPeriodRow)BudgetPeriodTable.NewRow(false); // // TemplateRow.BudgetSequence; // ABudgetPeriodAccess.LoadViaABudgetTemplate(MainDS, TemplateRow, Transaction); }); // Accept row changes here so that the Client gets 'unmodified' rows MainDS.AcceptChanges(); // Remove all Tables that were not filled with data before remoting them. MainDS.RemoveEmptyTables(); return MainDS; }
public void T4_AutoGenerationSetBudgetAmount() { BudgetTDS MainDS = LoadData(); if (MainDS.ABudget.Count == 0) { return; } int budgetSequence = MainDS.ABudget[0].BudgetSequence; ABudgetPeriodRow bPRow = (ABudgetPeriodRow)MainDS.ABudgetPeriod.Rows.Find(new object[] { budgetSequence, 1 }); if (bPRow == null) { Assert.IsNotNull(bPRow, String.Format("Cannot find budget period 1 value for budget sequence {0}", budgetSequence)); return; } //Add 10 to Budget base value and check if it is written decimal budgetBase = bPRow.BudgetBase; TBudgetAutoGenerateWebConnector.SetBudgetPeriodBaseAmount(budgetSequence, 1, (budgetBase + 10)); decimal budgetBaseNew = TBudgetAutoGenerateWebConnector.GetBudgetPeriodAmount(budgetSequence, 1); Assert.IsTrue(budgetBaseNew == (budgetBase + 10), String.Format("SetBudgetPeriod Failed. BudgetBase ({0}) has not been updated to: {1}", budgetBase, budgetBaseNew)); }
public static BudgetTDS LoadBudget(Int32 ALedgerNumber) { BudgetTDS MainDS = new BudgetTDS(); TDBTransaction Transaction = null; DBAccess.GDBAccessObj.GetNewOrExistingAutoReadTransaction(IsolationLevel.ReadCommitted, TEnforceIsolationLevel.eilMinimum, ref Transaction, delegate { //TODO: need to filter on Year ABudgetAccess.LoadViaALedger(MainDS, ALedgerNumber, Transaction); ABudgetRevisionAccess.LoadViaALedger(MainDS, ALedgerNumber, Transaction); //TODO: need to filter on ABudgetPeriod using LoadViaBudget or LoadViaUniqueKey ABudgetPeriodAccess.LoadAll(MainDS, Transaction); ALedgerAccess.LoadByPrimaryKey(MainDS, ALedgerNumber, Transaction); // ABudgetPeriodTable BudgetPeriodTable = new ABudgetPeriodTable(); // ABudgetPeriodRow TemplateRow = (ABudgetPeriodRow)BudgetPeriodTable.NewRow(false); // // TemplateRow.BudgetSequence; // ABudgetPeriodAccess.LoadViaABudgetTemplate(MainDS, TemplateRow, Transaction); }); // Accept row changes here so that the Client gets 'unmodified' rows MainDS.AcceptChanges(); // Remove all Tables that were not filled with data before remoting them. MainDS.RemoveEmptyTables(); return(MainDS); }
public static BudgetTDS LoadBudgetForAutoGenerate(Int32 ALedgerNumber) { BudgetTDS FMainDS = new BudgetTDS(); TDBTransaction Transaction = new TDBTransaction(); TDataBase db = DBAccess.Connect("LoadBudgetForAutoGenerate"); db.ReadTransaction( ref Transaction, delegate { ALedgerAccess.LoadByPrimaryKey(FMainDS, ALedgerNumber, Transaction); #region Validate Data if ((FMainDS.ALedger == null) || (FMainDS.ALedger.Count == 0)) { throw new EFinanceSystemDataTableReturnedNoDataException(String.Format(Catalog.GetString( "Function:{0} - Ledger data for Ledger number {1} does not exist or could not be accessed!"), Utilities.GetMethodName(true), ALedgerNumber)); } #endregion Validate Data int CurrentFinancialYear = FMainDS.ALedger[0].CurrentFinancialYear; //Load all by Ledger/Year but none may exist ABudgetTable BudgetTable = new ABudgetTable(); ABudgetRow TemplateRow = (ABudgetRow)BudgetTable.NewRow(); TemplateRow.LedgerNumber = ALedgerNumber; TemplateRow.Year = CurrentFinancialYear; StringCollection Operators = StringHelper.InitStrArr(new string[] { "=", "=" }); StringCollection OrderList = new StringCollection(); OrderList.Add("ORDER BY"); OrderList.Add(ABudgetTable.GetCostCentreCodeDBName() + " ASC"); OrderList.Add(ABudgetTable.GetAccountCodeDBName() + " ASC"); ABudgetAccess.LoadUsingTemplate(FMainDS, TemplateRow, Operators, null, Transaction, OrderList, 0, 0); ABudgetPeriodAccess.LoadViaABudgetTemplate(FMainDS, TemplateRow, Operators, null, Transaction, OrderList, 0, 0); ABudgetRevisionAccess.LoadByPrimaryKey(FMainDS, ALedgerNumber, CurrentFinancialYear, 0, Transaction); ABudgetTypeAccess.LoadAll(FMainDS, Transaction); }); // Accept row changes here so that the Client gets 'unmodified' rows FMainDS.AcceptChanges(); // Remove all Tables that were not filled with data before remoting them. FMainDS.RemoveEmptyTables(); return(FMainDS); }
/// <summary> /// test the budget autogeneration /// </summary> private BudgetTDS LoadData() { BudgetTDS MainDS = new BudgetTDS(); MainDS.Merge(TBudgetAutoGenerateWebConnector.LoadBudgetForAutoGenerate(FLedgerNumber)); //Not needed MainDS.RemoveTable("AGeneralLedgerMasterPeriod"); return(MainDS); }
public static TSubmitChangesResult SaveBudget(ref BudgetTDS AInspectDS) { if (AInspectDS != null) { BudgetTDSAccess.SubmitChanges(AInspectDS); return(TSubmitChangesResult.scrOK); } return(TSubmitChangesResult.scrError); }
/// <summary> /// Update Budget Period Amounts for each record that was created or modified during an import /// </summary> private void UpdateABudgetPeriodAmounts(BudgetTDS AMainDS, int ACurrentBudgetYear) { DataView BudgetDV = new DataView(AMainDS.ABudget); BudgetDV.RowFilter = String.Format("Isnull({0},'') <> '' And ({0}='Added' Or {0}='Updated')", ABudgetTable.GetCommentDBName()); BudgetDV.Sort = String.Format("{0} ASC", ABudgetTable.GetYearDBName()); foreach (DataRowView drv in BudgetDV) { BudgetTDSABudgetRow budgetRow = (BudgetTDSABudgetRow)drv.Row; int budgetSeq = budgetRow.BudgetSequence; DataView budgetPeriodsDV = new DataView(AMainDS.ABudgetPeriod); budgetPeriodsDV.RowFilter = String.Format("{0}={1}", ABudgetPeriodTable.GetBudgetSequenceDBName(), budgetRow.BudgetSequence); budgetPeriodsDV.Sort = String.Format("{0} ASC", ABudgetPeriodTable.GetPeriodNumberDBName()); foreach (DataRowView drv2 in budgetPeriodsDV) { ABudgetPeriodRow budgetPeriodRow = (ABudgetPeriodRow)drv2.Row; int period = budgetPeriodRow.PeriodNumber; string periodAmountColumn = string.Empty; if (period <= FNumberOfPeriods) { periodAmountColumn = "Period" + period.ToString("00") + "Amount"; budgetRow[periodAmountColumn] = budgetPeriodRow.BudgetBase; } else { //TODO After data migration, this should not happen so add an error message. // In old Petra, budget periods always go up to 20, but are only populated // up to number of financial periods } } } //Remove import related comment // - done separately to loop above to avoid confusion as DataView filtering is on Comment field foreach (DataRowView drv in BudgetDV) { BudgetTDSABudgetRow budgetRow = (BudgetTDSABudgetRow)drv.Row; budgetRow.Comment = string.Empty; } }
public static decimal GetBudgetPeriodAmount(BudgetTDS FMainDS, int ABudgetSequence, int APeriodNumber) { decimal retVal = 0m; ABudgetPeriodTable BudgetPeriodTable = FMainDS.ABudgetPeriod; ABudgetPeriodRow BudgetPeriodRow = (ABudgetPeriodRow)BudgetPeriodTable.Rows.Find(new object[] { ABudgetSequence, APeriodNumber }); if (BudgetPeriodRow != null) { retVal = (decimal)BudgetPeriodRow.BudgetBase; } return(retVal); }
public void T1_AutoGenerationLoadData() { BudgetTDS MainDS = LoadData(); string emptyTables = string.Empty; foreach (DataTable tb in MainDS.Tables) { if (MainDS.Tables[tb.TableName].Rows.Count == 0) { emptyTables += tb.TableName + "; "; } } Assert.IsEmpty(emptyTables, "Empty Budget Autogeneration Tables: " + emptyTables); }
public void T2_AutoGenerationGenBudget() { BudgetTDS MainDS = LoadData(); int budgetSequence = MainDS.ABudget.Count > 0 ? MainDS.ABudget[0].BudgetSequence : 0; string forecastType = MFinanceConstants.FORECAST_TYPE_BUDGET; if (MainDS.ABudget.Count == 0) { return; } Assert.IsTrue(TBudgetAutoGenerateWebConnector.GenBudgetForNextYear(FLedgerNumber, budgetSequence, forecastType), "Budget Autogenerate failed!"); }
public static decimal SetBudgetPeriodBaseAmount(BudgetTDS FMainDS, int ABudgetSequence, int APeriodNumber, decimal ABudgetAmount) { decimal retVal = 0; ABudgetPeriodTable BudgetPeriodTable = FMainDS.ABudgetPeriod; ABudgetPeriodRow BudgetPeriodRow = (ABudgetPeriodRow)BudgetPeriodTable.Rows.Find(new object[] { ABudgetSequence, APeriodNumber }); if (BudgetPeriodRow != null) { BudgetPeriodRow.BeginEdit(); BudgetPeriodRow.BudgetBase = ABudgetAmount; BudgetPeriodRow.EndEdit(); retVal = ABudgetAmount; } return(retVal); }
public void T1_AutoGenerationLoadData() { FMainDS = new BudgetTDS(); FMainDS.Merge(TBudgetAutoGenerateWebConnector.LoadBudgetForAutoGenerate(FLedgerNumber)); //Not needed FMainDS.RemoveTable("AGeneralLedgerMasterPeriod"); string emptyTables = string.Empty; foreach (DataTable tb in FMainDS.Tables) { if (FMainDS.Tables[tb.TableName].Rows.Count == 0) { emptyTables += tb.TableName + "; "; } } Assert.IsEmpty(emptyTables, "Empty Budget Autogeneration Tables: " + emptyTables); }
public static int ExportBudgets(Int32 ALedgerNumber, string ACSVFileName, string[] AFdlgSeparator, ref string AFileContents, ref BudgetTDS AExportDS, out TVerificationResultCollection AVerificationResult) { AVerificationResult = null; if (AExportDS != null) { int retVal = ExportBudgetToCSV(ALedgerNumber, ACSVFileName, AFdlgSeparator, ref AFileContents, ref AExportDS, ref AVerificationResult); return(retVal); } return(0); }
public static decimal ImportBudgets(Int32 ALedgerNumber, Int32 ACurrentBudgetYear, string ACSVFileName, string[] AFdlgSeparator, ref BudgetTDS AImportDS, out TVerificationResultCollection AVerificationResult) { AVerificationResult = null; if (AImportDS != null) { decimal retVal = ImportBudgetFromCSV(ALedgerNumber, ACurrentBudgetYear, ACSVFileName, AFdlgSeparator, ref AImportDS, ref AVerificationResult); return(retVal); } return(0); }
/// <summary> /// Import the budget from a CSV file /// </summary> /// <param name="ALedgerNumber"></param> /// <param name="ACurrentBudgetYear"></param> /// <param name="AImportString"></param> /// <param name="ACSVFileName"></param> /// <param name="AFdlgSeparator"></param> /// <param name="AImportDS"></param> /// <param name="ARecordsUpdated"></param> /// <param name="AFailedRows"></param> /// <param name="AVerificationResult"></param> /// <returns>Total number of records imported and number of which updated as the fractional part</returns> private static Int32 ImportBudgetFromCSV(Int32 ALedgerNumber, Int32 ACurrentBudgetYear, string AImportString, string ACSVFileName, string[] AFdlgSeparator, ref BudgetTDS AImportDS, ref Int32 ARecordsUpdated, ref Int32 AFailedRows, ref TVerificationResultCollection AVerificationResult) { StringReader sr = new StringReader(AImportString); string Separator = AFdlgSeparator[0]; string DateFormat = AFdlgSeparator[1]; string NumberFormat = AFdlgSeparator[2]; FCultureInfoNumberFormat = new CultureInfo(NumberFormat.Equals("American") ? "en-US" : "de-DE"); CultureInfo MyCultureInfoDate = new CultureInfo("en-GB"); MyCultureInfoDate.DateTimeFormat.ShortDatePattern = DateFormat; // To store the From and To currencies // Use an array to store these to make for easy // inverting of the two currencies when calculating // the inverse value. //string currentBudgetVal = string.Empty; //string mess = string.Empty; ACostCentreTable CostCentreTable = null; AAccountTable AccountTable = null; string CostCentre = string.Empty; string Account = string.Empty; string BudgetTypeUpper = string.Empty; string BudgetYearStringUpper = string.Empty; int BudgetYearNumber = 0; int BdgRevision = 0; //not currently implementing versioning so always zero int NumPeriods = TAccountingPeriodsWebConnector.GetNumberOfPeriods(ALedgerNumber); decimal[] BudgetPeriods = new decimal[NumPeriods]; int RowNumber = 0; AFailedRows = 0; ABudgetTable BudgetTableExistingAndImported = new ABudgetTable(); // Go round a loop reading the file line by line string ImportLine = sr.ReadLine(); while (ImportLine != null) { decimal totalBudgetRowAmount = 0; try { CostCentre = StringHelper.GetNextCSV(ref ImportLine, Separator, false).ToString(); //Check if header row exists if ((CostCentre == "Cost Centre") || string.IsNullOrEmpty(ImportLine)) { continue; } //Increment row number RowNumber++; //Read the values for the current line //Account Account = StringHelper.GetNextCSV(ref ImportLine, Separator, false).ToString(); //BudgetType BudgetTypeUpper = StringHelper.GetNextCSV(ref ImportLine, Separator, false).ToString().ToUpper(); BudgetTypeUpper = BudgetTypeUpper.Replace(" ", ""); //Ad hoc will become ADHOC //Allow for variations on Inf.Base and Inf.N if (BudgetTypeUpper.Contains("INF")) { if (BudgetTypeUpper.Contains("BASE")) { if (BudgetTypeUpper != MFinanceConstants.BUDGET_INFLATE_BASE) { BudgetTypeUpper = MFinanceConstants.BUDGET_INFLATE_BASE; } } else if (BudgetTypeUpper != MFinanceConstants.BUDGET_INFLATE_N) { BudgetTypeUpper = MFinanceConstants.BUDGET_INFLATE_N; } } //BudgetYear BudgetYearStringUpper = (StringHelper.GetNextCSV(ref ImportLine, Separator, false)).ToUpper(); //Check validity of CSV file line values if (!ValidateKeyBudgetFields(ALedgerNumber, RowNumber, ref CostCentreTable, ref AccountTable, CostCentre, Account, BudgetTypeUpper, BudgetYearStringUpper, ref AVerificationResult)) { AFailedRows++; continue; } //Read the budgetperiod values to check if valid according to type Array.Clear(BudgetPeriods, 0, NumPeriods); if (!ProcessBudgetTypeImportDetails(RowNumber, ref ImportLine, Separator, BudgetTypeUpper, ref BudgetPeriods, BudgetYearStringUpper, CostCentre, Account, ref AVerificationResult)) { continue; } //Calculate the budget Year BudgetYearNumber = GetBudgetYearNumber(ALedgerNumber, BudgetYearStringUpper); //Add budget revision record if there's not one already. if (AImportDS.ABudgetRevision.Rows.Find(new object[] { ALedgerNumber, BudgetYearNumber, BdgRevision }) == null) { ABudgetRevisionRow BudgetRevisionRow = (ABudgetRevisionRow)AImportDS.ABudgetRevision.NewRowTyped(); BudgetRevisionRow.LedgerNumber = ALedgerNumber; BudgetRevisionRow.Year = BudgetYearNumber; BudgetRevisionRow.Revision = BdgRevision; BudgetRevisionRow.Description = "Budget Import from: " + ACSVFileName; AImportDS.ABudgetRevision.Rows.Add(BudgetRevisionRow); } for (int i = 0; i < NumPeriods; i++) { totalBudgetRowAmount += BudgetPeriods[i]; } BudgetTDS mainDS = new BudgetTDS(); TDBTransaction transaction = null; DBAccess.GDBAccessObj.GetNewOrExistingAutoReadTransaction(IsolationLevel.ReadCommitted, TEnforceIsolationLevel.eilMinimum, ref transaction, delegate { //TODO: need to filter on ABudgetPeriod using LoadViaBudget or LoadViaUniqueKey ABudgetAccess.LoadByUniqueKey(mainDS, ALedgerNumber, BudgetYearNumber, BdgRevision, CostCentre, Account, transaction); #region Validate Data if ((mainDS.ABudget != null) && (mainDS.ABudget.Count > 1)) { //TODO: update when budget revisioning is added throw new EFinanceSystemDataTableReturnedNoDataException(String.Format(Catalog.GetString( "Function:{0} - Duplicates unique keys exist in the Budget table for Ledger: {1} Year: '{2}' ({3}), Cost Centre: '{4}' & Account: '{5}'!"), Utilities.GetMethodName(true), ALedgerNumber, BudgetYearStringUpper, BudgetYearNumber, CostCentre, Account)); } #endregion Validate Data }); //Check to see if the budget combination already exists: if (mainDS.ABudget.Count > 0) { //Will only be one row ABudgetRow br2 = (ABudgetRow)mainDS.ABudget.Rows[0]; //Check if exists in AImportDS int bdgSeq = br2.BudgetSequence; //Add to duplicates-checking table //If not in saved budget table, check if already been imported earlier in the file DataRow duplicateBudgetRow = BudgetTableExistingAndImported.Rows.Find(new object[] { bdgSeq }); if (duplicateBudgetRow != null) { //TODO: update when budget revisioning is added AVerificationResult.Add(new TVerificationResult(Catalog.GetString("Row: " + RowNumber.ToString("0000")), String.Format(Catalog.GetString( " This budget import row (Year: '{0}', Cost Centre: '{1}', Account: '{2}') is repeated in the import file!"), BudgetYearStringUpper, CostCentre, Account), TResultSeverity.Resv_Noncritical)); AFailedRows++; continue; } BudgetTableExistingAndImported.ImportRow(br2); ABudgetRow bdgRow = (ABudgetRow)AImportDS.ABudget.Rows.Find(new object[] { bdgSeq }); if (bdgRow != null) { bool rowUpdated = false; if (bdgRow.BudgetTypeCode != BudgetTypeUpper) { rowUpdated = true; bdgRow.BudgetTypeCode = BudgetTypeUpper; } ABudgetPeriodRow BPRow = null; for (int i = 0; i < NumPeriods; i++) { BPRow = (ABudgetPeriodRow)AImportDS.ABudgetPeriod.Rows.Find(new object[] { bdgSeq, i + 1 }); if ((BPRow != null) && (BPRow.BudgetBase != BudgetPeriods[i])) { rowUpdated = true; BPRow.BudgetBase = BudgetPeriods[i]; } BPRow = null; } if (rowUpdated) { ARecordsUpdated++; } } } else { //If not in saved budget table, check if already been imported earlier in the file DataRow[] duplicateBudgetRows = BudgetTableExistingAndImported.Select(String.Format("{0}={1} And {2}={3} And {4}={5} And {6}='{7}' And {8}='{9}'", ABudgetTable.GetLedgerNumberDBName(), ALedgerNumber, ABudgetTable.GetYearDBName(), BudgetYearNumber, ABudgetTable.GetRevisionDBName(), BdgRevision, ABudgetTable.GetCostCentreCodeDBName(), CostCentre, ABudgetTable.GetAccountCodeDBName(), Account)); if ((duplicateBudgetRows != null) && (duplicateBudgetRows.Length > 0)) { //TODO: update when budget revisioning is added AVerificationResult.Add(new TVerificationResult(Catalog.GetString("Row: " + RowNumber.ToString("0000")), String.Format(Catalog.GetString( "This budget import row (Year: '{0}', Cost Centre: '{1}', Account: '{2}') is repeated in the import file!"), BudgetYearStringUpper, CostCentre, Account), TResultSeverity.Resv_Noncritical)); AFailedRows++; continue; } //Add the new budget row ABudgetRow BudgetRow = (ABudgetRow)AImportDS.ABudget.NewRowTyped(); int newSequence = Convert.ToInt32(TSequenceWebConnector.GetNextSequence(TSequenceNames.seq_budget)); // -1 * (AImportDS.ABudget.Rows.Count + 1); BudgetRow.BudgetSequence = newSequence; BudgetRow.LedgerNumber = ALedgerNumber; BudgetRow.Year = BudgetYearNumber; BudgetRow.Revision = BdgRevision; BudgetRow.CostCentreCode = CostCentre; BudgetRow.AccountCode = Account; BudgetRow.BudgetTypeCode = BudgetTypeUpper; AImportDS.ABudget.Rows.Add(BudgetRow); //Add to import table to check for later duplicates BudgetTableExistingAndImported.ImportRow(BudgetRow); //Add the budget periods for (int i = 0; i < NumPeriods; i++) { ABudgetPeriodRow BudgetPeriodRow = (ABudgetPeriodRow)AImportDS.ABudgetPeriod.NewRowTyped(); BudgetPeriodRow.BudgetSequence = newSequence; BudgetPeriodRow.PeriodNumber = i + 1; BudgetPeriodRow.BudgetBase = BudgetPeriods[i]; AImportDS.ABudgetPeriod.Rows.Add(BudgetPeriodRow); } } } catch (Exception ex) { TLogging.Log(String.Format("Method:{0} - Unexpected error!{1}{1}{2}", Utilities.GetMethodSignature(), Environment.NewLine, ex.Message)); throw ex; } finally { // Read the next line ImportLine = sr.ReadLine(); } } //DataFile.Close(); return RowNumber; }
/// <summary> /// Import the budget from a CSV file /// </summary> /// <param name="ALedgerNumber"></param> /// <param name="ACurrentBudgetYear"></param> /// <param name="ACSVFileName"></param> /// <param name="AFdlgSeparator"></param> /// <param name="AImportDS"></param> /// <param name="AVerificationResult"></param> /// <returns>Total number of records imported and number of which updated as the fractional part</returns> private static decimal ImportBudgetFromCSV(Int32 ALedgerNumber, Int32 ACurrentBudgetYear, string ACSVFileName, string[] AFdlgSeparator, ref BudgetTDS AImportDS, ref TVerificationResultCollection AVerificationResult) { StreamReader DataFile = new StreamReader(ACSVFileName, System.Text.Encoding.Default); string Separator = AFdlgSeparator[0]; string DateFormat = AFdlgSeparator[1]; string NumberFormat = AFdlgSeparator[2]; FCultureInfoNumberFormat = new CultureInfo(NumberFormat.Equals("American") ? "en-US" : "de-DE"); CultureInfo MyCultureInfoDate = new CultureInfo("en-GB"); MyCultureInfoDate.DateTimeFormat.ShortDatePattern = DateFormat; // To store the From and To currencies // Use an array to store these to make for easy // inverting of the two currencies when calculating // the inverse value. //string currentBudgetVal = string.Empty; //string mess = string.Empty; string CostCentre = string.Empty; string Account = string.Empty; string budgetType = string.Empty; string budgetYearString = string.Empty; int budgetYear = 0; Int32 numPeriods = TAccountingPeriodsWebConnector.GetNumberOfPeriods(ALedgerNumber); decimal[] BudgetPeriods = new decimal[numPeriods]; int YearForBudgetRevision = 0; int BdgRevision = 0; //not currently implementing versioning so always zero decimal rowNumber = 0; decimal duplicateRowNumber = 0; while (!DataFile.EndOfStream) { decimal totalBudgetRowAmount = 0; try { string Line = DataFile.ReadLine(); CostCentre = StringHelper.GetNextCSV(ref Line, Separator, false).ToString(); if (CostCentre == "Cost Centre") { //Read the next line Line = DataFile.ReadLine(); CostCentre = StringHelper.GetNextCSV(ref Line, Separator, false).ToString(); } //Increment row number rowNumber++; //Convert separator to a char // char Sep = Separator[0]; //Turn current line into string array of column values // string[] CsvColumns = Line.Split(Sep); //int NumCols = CsvColumns.Length; //If number of columns is not 4 then import csv file is wrongly formed. // if (NumCols != 24) // { // AVerificationResult. MessageBox.Show(Catalog.GetString("Failed to import the CSV budget file:\r\n\r\n" + // " " + ADataFilename + "\r\n\r\n" + // "It contains " + NumCols.ToString() + " columns. " + // ), AImportMode + " Exchange Rates Import Error"); // return; // } //Read the values for the current line Account = StringHelper.GetNextCSV(ref Line, Separator, false).ToString(); budgetType = StringHelper.GetNextCSV(ref Line, Separator, false).ToString().ToUpper(); budgetType = budgetType.Replace(" ", ""); //Ad hoc will become ADHOC //Allow for variations on Inf.Base and Inf.N if (budgetType.Contains("INF")) { if (budgetType.Contains("BASE")) { if (budgetType != MFinanceConstants.BUDGET_INFLATE_BASE) { budgetType = MFinanceConstants.BUDGET_INFLATE_BASE; } } else if (budgetType != MFinanceConstants.BUDGET_INFLATE_N) { budgetType = MFinanceConstants.BUDGET_INFLATE_N; } } if ((budgetType != MFinanceConstants.BUDGET_ADHOC) && (budgetType != MFinanceConstants.BUDGET_SAME) && (budgetType != MFinanceConstants.BUDGET_INFLATE_N) && (budgetType != MFinanceConstants.BUDGET_SPLIT) && (budgetType != MFinanceConstants.BUDGET_INFLATE_BASE) ) { throw new InvalidOperationException("Budget Type: " + budgetType + " in row: " + rowNumber.ToString() + " does not exist."); } //Calculate the budget Year budgetYearString = StringHelper.GetNextCSV(ref Line, Separator, false); YearForBudgetRevision = BudgetRevisionYearNumber(ALedgerNumber, budgetYearString); //Add budget revision record if there's not one already. if (AImportDS.ABudgetRevision.Rows.Find(new object[] { ALedgerNumber, YearForBudgetRevision, BdgRevision }) == null) { ABudgetRevisionRow BudgetRevisionRow = (ABudgetRevisionRow)AImportDS.ABudgetRevision.NewRowTyped(); BudgetRevisionRow.LedgerNumber = ALedgerNumber; BudgetRevisionRow.Year = YearForBudgetRevision; BudgetRevisionRow.Revision = BdgRevision; BudgetRevisionRow.Description = "Budget Import from: " + ACSVFileName; AImportDS.ABudgetRevision.Rows.Add(BudgetRevisionRow); } //Read the budgetperiod values to check if valid according to type Array.Clear(BudgetPeriods, 0, numPeriods); bool successfulBudgetRowProcessing = ProcessBudgetTypeImportDetails(ref Line, Separator, budgetType, ref BudgetPeriods); for (int i = 0; i < numPeriods; i++) { totalBudgetRowAmount += BudgetPeriods[i]; } if (!successfulBudgetRowProcessing) { throw new InvalidOperationException(String.Format( "The budget in row {0} for Ledger: {1}, Year: {2}, Cost Centre: {3} and Account: {4}, does not have values consistent with Budget Type: {5}.", rowNumber, ALedgerNumber, budgetYear, CostCentre, Account, budgetType)); } BudgetTDS MainDS = new BudgetTDS(); TDBTransaction transaction = null; DBAccess.GDBAccessObj.GetNewOrExistingAutoReadTransaction(IsolationLevel.ReadCommitted, TEnforceIsolationLevel.eilMinimum, ref transaction, delegate { ABudgetAccess.LoadByUniqueKey(MainDS, ALedgerNumber, YearForBudgetRevision, BdgRevision, CostCentre, Account, transaction); //TODO: need to filter on ABudgetPeriod using LoadViaBudget or LoadViaUniqueKey }); //Check to see if the budget combination already exists: if (MainDS.ABudget.Count > 0) { ABudgetRow BR2 = (ABudgetRow)MainDS.ABudget.Rows[0]; int BTSeq = BR2.BudgetSequence; ABudgetRow BdgTRow = (ABudgetRow)AImportDS.ABudget.Rows.Find(new object[] { BTSeq }); if (BdgTRow != null) { duplicateRowNumber++; BdgTRow.BeginEdit(); //Edit the new budget row BdgTRow.BudgetTypeCode = budgetType; BdgTRow.EndEdit(); ABudgetPeriodRow BPRow = null; for (int i = 0; i < numPeriods; i++) { BPRow = (ABudgetPeriodRow)AImportDS.ABudgetPeriod.Rows.Find(new object[] { BTSeq, i + 1 }); if (BPRow != null) { BPRow.BeginEdit(); BPRow.BudgetBase = BudgetPeriods[i]; BPRow.EndEdit(); } BPRow = null; } } } else { //Add the new budget row ABudgetRow BudgetRow = (ABudgetRow)AImportDS.ABudget.NewRowTyped(); int newSequence = -1 * (AImportDS.ABudget.Rows.Count + 1); BudgetRow.BudgetSequence = newSequence; BudgetRow.LedgerNumber = ALedgerNumber; BudgetRow.Year = YearForBudgetRevision; BudgetRow.Revision = BdgRevision; BudgetRow.CostCentreCode = CostCentre; BudgetRow.AccountCode = Account; BudgetRow.BudgetTypeCode = budgetType; AImportDS.ABudget.Rows.Add(BudgetRow); //Add the budget periods for (int i = 0; i < numPeriods; i++) { ABudgetPeriodRow BudgetPeriodRow = (ABudgetPeriodRow)AImportDS.ABudgetPeriod.NewRowTyped(); BudgetPeriodRow.BudgetSequence = newSequence; BudgetPeriodRow.PeriodNumber = i + 1; BudgetPeriodRow.BudgetBase = BudgetPeriods[i]; AImportDS.ABudgetPeriod.Rows.Add(BudgetPeriodRow); } } } catch (Exception) { throw; } } DataFile.Close(); if (duplicateRowNumber > 0) { //fractional part is the number of updates divided by 10000 if (duplicateRowNumber < 10000) { rowNumber += (duplicateRowNumber / 10000); } } return(rowNumber); }
/// <summary> /// Imports budgets from a file /// </summary> /// <param name="ASelectedBudgetYear"></param> /// <param name="AMainDS"></param> public void ImportBudget(int ASelectedBudgetYear, ref BudgetTDS AMainDS) { TVerificationResultCollection Messages = new TVerificationResultCollection(); int NumBudgetsToImport = 0; int NumRecsUpdated = 0; int NumRowsFailed = 0; if (FPetraUtilsObject.HasChanges) { // saving failed, therefore do not try to post MessageBox.Show(Catalog.GetString("Please save before trying to import!"), Catalog.GetString( "Failure"), MessageBoxButtons.OK, MessageBoxIcon.Exclamation); return; } String DateFormatString = TUserDefaults.GetStringDefault("Imp Date", "MDY"); OpenFileDialog OFDialog = new OpenFileDialog(); string ExportPath = TClientSettings.GetExportPath(); string FullPath = TUserDefaults.GetStringDefault("Imp Filename", ExportPath + Path.DirectorySeparatorChar + "import.csv"); TImportExportDialogs.SetOpenFileDialogFilePathAndName(OFDialog, FullPath, ExportPath); OFDialog.Title = Catalog.GetString("Import budget(s) from csv file"); OFDialog.Filter = Catalog.GetString("Budget files (*.csv)|*.csv"); String ImportOptions = TUserDefaults.GetStringDefault("Imp Options", ";" + TDlgSelectCSVSeparator.NUMBERFORMAT_AMERICAN); // This call fixes Windows7 Open File Dialogs. It must be the line before ShowDialog() TWin7FileOpenSaveDialog.PrepareDialog(Path.GetFileName(FullPath)); if (OFDialog.ShowDialog() == DialogResult.OK) { FdlgSeparator = new TDlgSelectCSVSeparator(false); try { FParentForm.UseWaitCursor = true; Boolean fileCanOpen = FdlgSeparator.OpenCsvFile(OFDialog.FileName); if (!fileCanOpen) { throw new Exception(String.Format(Catalog.GetString("File {0} cannot be opened."), OFDialog.FileName)); } FdlgSeparator.DateFormat = DateFormatString; if (ImportOptions.Length > 1) { FdlgSeparator.NumberFormat = ImportOptions.Substring(1); } FdlgSeparator.SelectedSeparator = ImportOptions.Substring(0, 1); if (FdlgSeparator.ShowDialog() == DialogResult.OK) { string[] FdlgSeparatorVal = new string[] { FdlgSeparator.SelectedSeparator, FdlgSeparator.DateFormat, FdlgSeparator.NumberFormat }; // read contents of file string ImportString = File.ReadAllText(OFDialog.FileName); //TODO return the budget from the year, and -99 for fail NumBudgetsToImport = TRemote.MFinance.Budget.WebConnectors.ImportBudgets(FLedgerNumber, ASelectedBudgetYear, ImportString, OFDialog.FileName, FdlgSeparatorVal, ref AMainDS, out NumRecsUpdated, out NumRowsFailed, out Messages); ShowMessages(Messages, NumBudgetsToImport, NumRecsUpdated, NumRowsFailed); } } catch (Exception ex) { NumBudgetsToImport = -1; MessageBox.Show(ex.Message, Catalog.GetString("Budget Import"), MessageBoxButtons.OK, MessageBoxIcon.Error); } finally { // We save the defaults even if ok is false - because the client will probably want to try and import // the same file again after correcting any errors SaveUserDefaults(OFDialog, ImportOptions); FParentForm.UseWaitCursor = false; } // update grid if (NumBudgetsToImport > 0) { FParentForm.UseWaitCursor = true; UpdateABudgetPeriodAmounts(AMainDS, ASelectedBudgetYear); FUserControl.SetBudgetDefaultView(); FParentForm.UseWaitCursor = false; FUserControl.SelectRowInGrid(1); FPetraUtilsObject.SetChangedFlag(); } else if (NumBudgetsToImport <= 0) { FUserControl.SelectRowInGrid(1); } } }
public static bool GenBudgetForNextYear(int ALedgerNumber, int ABudgetSeq, string AForecastType) { BudgetTDS FMainDS = LoadBudgetForAutoGenerate(ALedgerNumber); bool retVal = false; decimal budgetSum; decimal priorAmount = 0; decimal afterAmount = 0; int periodOfChange = 0; int gLMSequenceThisYear = 0; int gLMSequenceLastYear = 0; ALedgerTable ledgerTable = FMainDS.ALedger; ALedgerRow ledgerRow = (ALedgerRow)ledgerTable.Rows[0]; int currentFinancialYear = ledgerRow.CurrentFinancialYear; int currentPeriod = ledgerRow.CurrentPeriod; int numAccPeriods = ledgerRow.NumberOfAccountingPeriods; ABudgetTable budgetTable = FMainDS.ABudget; ABudgetRow budgetRow = (ABudgetRow)budgetTable.Rows.Find(new object[] { ABudgetSeq }); #region Validate Method Parameters if ((budgetRow == null) || (budgetRow.LedgerNumber != ledgerRow.LedgerNumber) || (budgetRow.Year != currentFinancialYear)) { TLogging.Log("GenBudgetForNextYear: either could not find budget sequence " + ABudgetSeq.ToString() + "or the sequence does not apply to ledger " + ledgerRow.LedgerNumber.ToString() + "or to ledger current year " + currentFinancialYear.ToString()); return(retVal); } if (ALedgerNumber != ledgerRow.LedgerNumber) { TLogging.Log("GenBudgetForNextYear: method parameter ALedgerNumber is " + ALedgerNumber.ToString() + " but the data set expected the ledger to be " + ledgerRow.LedgerNumber.ToString()); } #endregion string accountCode = budgetRow.AccountCode; string costCentreCode = budgetRow.CostCentreCode; gLMSequenceThisYear = TCommonBudgetMaintain.GetGLMSequenceForBudget(ALedgerNumber, accountCode, costCentreCode, currentFinancialYear); gLMSequenceLastYear = TCommonBudgetMaintain.GetGLMSequenceForBudget(ALedgerNumber, accountCode, costCentreCode, (currentFinancialYear - 1)); try { //Update the budget status budgetRow.BeginEdit(); budgetRow.BudgetStatus = false; budgetRow.EndEdit(); string budgetType = budgetRow.BudgetTypeCode; decimal budgetAmount = 0; decimal actualAmount = 0; bool validBudgetType = true; switch (budgetType) { case MFinanceConstants.BUDGET_ADHOC: case MFinanceConstants.BUDGET_INFLATE_BASE: for (int i = 1; i < currentPeriod; i++) { //Set budget period actualAmount = TCommonBudgetMaintain.GetActual(ALedgerNumber, gLMSequenceLastYear, gLMSequenceThisYear, i, numAccPeriods, currentFinancialYear, (currentFinancialYear - 1), false, MFinanceConstants.CURRENCY_BASE); SetBudgetPeriodBaseAmount(FMainDS, ABudgetSeq, i, actualAmount); } for (int j = currentPeriod; j <= MFinanceConstants.MAX_PERIODS; j++) { if (AForecastType == MFinanceConstants.FORECAST_TYPE_BUDGET) { budgetAmount = Math.Round(TCommonBudgetMaintain.GetBudget(gLMSequenceThisYear, -1, j, numAccPeriods, false, MFinanceConstants.CURRENCY_BASE)); SetBudgetPeriodBaseAmount(FMainDS, ABudgetSeq, j, budgetAmount); } else { actualAmount = TCommonBudgetMaintain.GetActual(ALedgerNumber, gLMSequenceLastYear, gLMSequenceThisYear, j, numAccPeriods, currentFinancialYear, (currentFinancialYear - 1), false, MFinanceConstants.CURRENCY_BASE); SetBudgetPeriodBaseAmount(FMainDS, ABudgetSeq, j, actualAmount); } } break; case MFinanceConstants.BUDGET_SAME: //because this case has no code it will fall through to the next case until it finds code. case MFinanceConstants.BUDGET_SPLIT: if ((currentPeriod - 1) != 0) { budgetSum = TCommonBudgetMaintain.GetActual(ALedgerNumber, gLMSequenceThisYear, -1, (currentPeriod - 1), numAccPeriods, currentFinancialYear, currentFinancialYear, true, MFinanceConstants.CURRENCY_BASE); } else { budgetSum = 0; } if (AForecastType == MFinanceConstants.FORECAST_TYPE_BUDGET) { for (int i = currentPeriod; i <= numAccPeriods; i++) { budgetSum += TCommonBudgetMaintain.GetBudget(gLMSequenceThisYear, -1, i, numAccPeriods, false, MFinanceConstants.CURRENCY_BASE); } } else { if (currentPeriod > 1) { budgetSum += TCommonBudgetMaintain.GetActual(ALedgerNumber, gLMSequenceLastYear, gLMSequenceThisYear, numAccPeriods, numAccPeriods, currentFinancialYear, (currentFinancialYear - 1), true, MFinanceConstants.CURRENCY_BASE) - TCommonBudgetMaintain.GetActual(ALedgerNumber, gLMSequenceLastYear, gLMSequenceThisYear, (currentPeriod - 1), numAccPeriods, currentFinancialYear, (currentFinancialYear - 1), true, MFinanceConstants.CURRENCY_BASE); } else { budgetSum += TCommonBudgetMaintain.GetActual(ALedgerNumber, gLMSequenceLastYear, gLMSequenceThisYear, numAccPeriods, numAccPeriods, currentFinancialYear, (currentFinancialYear - 1), true, MFinanceConstants.CURRENCY_BASE); } } budgetSum = budgetSum / numAccPeriods; for (int i = 1; i <= numAccPeriods; i++) { SetBudgetPeriodBaseAmount(FMainDS, ABudgetSeq, i, Math.Round(budgetSum)); } break; case MFinanceConstants.BUDGET_INFLATE_N: for (int i = 1; i <= numAccPeriods; i++) { if (GetBudgetPeriodAmount(FMainDS, ABudgetSeq, i) != GetBudgetPeriodAmount(FMainDS, ABudgetSeq, 1)) { periodOfChange = i - 1; break; } } /* Calculate average prior to change and after change. */ if (periodOfChange < (currentPeriod - 1)) { priorAmount = TCommonBudgetMaintain.GetActual(ALedgerNumber, gLMSequenceThisYear, -1, periodOfChange, numAccPeriods, currentFinancialYear, currentFinancialYear, true, MFinanceConstants.CURRENCY_BASE); afterAmount = TCommonBudgetMaintain.GetActual(ALedgerNumber, gLMSequenceThisYear, -1, (currentPeriod - 1), numAccPeriods, currentFinancialYear, currentFinancialYear, true, MFinanceConstants.CURRENCY_BASE) - TCommonBudgetMaintain.GetActual(ALedgerNumber, gLMSequenceThisYear, -1, periodOfChange + 1, numAccPeriods, currentFinancialYear, currentFinancialYear, true, MFinanceConstants.CURRENCY_BASE); if (AForecastType == MFinanceConstants.FORECAST_TYPE_BUDGET) { for (int i = currentPeriod; i <= numAccPeriods; i++) { afterAmount += TCommonBudgetMaintain.GetBudget(gLMSequenceThisYear, -1, i, numAccPeriods, false, MFinanceConstants.CURRENCY_BASE); } } else { afterAmount += TCommonBudgetMaintain.GetActual(ALedgerNumber, gLMSequenceLastYear, gLMSequenceThisYear, numAccPeriods, numAccPeriods, currentFinancialYear, (currentFinancialYear - 1), true, MFinanceConstants.CURRENCY_BASE) - TCommonBudgetMaintain.GetActual(ALedgerNumber, gLMSequenceLastYear, gLMSequenceThisYear, currentPeriod, numAccPeriods, currentFinancialYear, (currentFinancialYear - 1), true, MFinanceConstants.CURRENCY_BASE); } } else /* Period of change HAS NOT taken place. */ { if ((currentPeriod - 1) != 0) { priorAmount = TCommonBudgetMaintain.GetActual(ALedgerNumber, gLMSequenceThisYear, -1, (currentPeriod - 1), numAccPeriods, currentFinancialYear, currentFinancialYear, true, MFinanceConstants.CURRENCY_BASE); } else { priorAmount = 0; } if (AForecastType == MFinanceConstants.FORECAST_TYPE_BUDGET) { for (int i = currentPeriod; i <= periodOfChange; i++) { priorAmount += TCommonBudgetMaintain.GetBudget(gLMSequenceThisYear, -1, i, numAccPeriods, false, MFinanceConstants.CURRENCY_BASE); } } else { priorAmount = TCommonBudgetMaintain.GetActual(ALedgerNumber, gLMSequenceLastYear, gLMSequenceThisYear, periodOfChange, numAccPeriods, currentFinancialYear, (currentFinancialYear - 1), true, MFinanceConstants.CURRENCY_BASE) - TCommonBudgetMaintain.GetActual(ALedgerNumber, gLMSequenceLastYear, gLMSequenceThisYear, currentPeriod, numAccPeriods, currentFinancialYear, (currentFinancialYear - 1), true, MFinanceConstants.CURRENCY_BASE); } if (AForecastType == MFinanceConstants.FORECAST_TYPE_BUDGET) { for (int i = (periodOfChange + 1); i <= numAccPeriods; i++) { afterAmount += TCommonBudgetMaintain.GetBudget(gLMSequenceThisYear, -1, i, numAccPeriods, false, MFinanceConstants.CURRENCY_BASE); } } else { afterAmount = TCommonBudgetMaintain.GetActual(ALedgerNumber, gLMSequenceLastYear, gLMSequenceThisYear, numAccPeriods, numAccPeriods, currentFinancialYear, (currentFinancialYear - 1), true, MFinanceConstants.CURRENCY_BASE) - TCommonBudgetMaintain.GetActual(ALedgerNumber, gLMSequenceLastYear, gLMSequenceThisYear, (periodOfChange + 1), numAccPeriods, currentFinancialYear, (currentFinancialYear - 1), true, MFinanceConstants.CURRENCY_BASE); } /* Dividing after sum by prior sum gives rate of inflation. */ priorAmount = priorAmount / periodOfChange; afterAmount = afterAmount / (numAccPeriods - periodOfChange); for (int i = 1; i <= periodOfChange; i++) { SetBudgetPeriodBaseAmount(FMainDS, ABudgetSeq, i, Math.Round(priorAmount, 0)); } for (int i = (periodOfChange + 1); i <= numAccPeriods; i++) { SetBudgetPeriodBaseAmount(FMainDS, ABudgetSeq, i, Math.Round(afterAmount, 0)); } } break; default: validBudgetType = false; break; } if (!validBudgetType) { throw new InvalidOperationException(String.Format("Invalid budget type of: {0} for Budget Seq.: {1}", budgetType, budgetRow.BudgetSequence)); } retVal = true; } catch (Exception) { throw; } return(retVal); }
/// <summary> /// Update Budget Period Amounts for each record that was created or modified during an import /// </summary> private void UpdateABudgetPeriodAmounts(BudgetTDS AMainDS, int ASelectedBudgetYear) { foreach (BudgetTDSABudgetRow budgetRow in AMainDS.ABudget.Rows) { if (budgetRow.RowState == DataRowState.Unchanged) { continue; } int budgetSeq = budgetRow.BudgetSequence; DataView budgetPeriodsDV = new DataView(AMainDS.ABudgetPeriod); budgetPeriodsDV.RowFilter = String.Format("{0}={1}", ABudgetPeriodTable.GetBudgetSequenceDBName(), budgetRow.BudgetSequence); budgetPeriodsDV.Sort = String.Format("{0} ASC", ABudgetPeriodTable.GetPeriodNumberDBName()); foreach (DataRowView drv2 in budgetPeriodsDV) { ABudgetPeriodRow budgetPeriodRow = (ABudgetPeriodRow)drv2.Row; int period = budgetPeriodRow.PeriodNumber; string periodAmountColumn = string.Empty; if (period <= FNumberOfPeriods) { periodAmountColumn = "Period" + period.ToString("00") + "Amount"; budgetRow[periodAmountColumn] = budgetPeriodRow.BudgetBase; } else { //TODO After data migration, this should not happen so add an error message. // In old Petra, budget periods always go up to 20, but are only populated // up to number of financial periods } } } //Attempts using LINQ //DataTable BudgetPeriodAmounts = new DataTable(); //BudgetPeriodAmounts.Columns.Add("BudgetSequence", typeof(int)); //BudgetPeriodAmounts.Columns.Add("PeriodNumber", typeof(int)); //BudgetPeriodAmounts.Columns.Add("Amount", typeof(decimal)); //BudgetPeriodAmounts.PrimaryKey = new DataColumn[] {BudgetPeriodAmounts.Columns["BudgetSequence"], // BudgetPeriodAmounts.Columns["PeriodNumber"]}; //var varBudgetPeriodAmounts = // from BudgetTDSABudgetRow budgetRow in FMainDS.ABudget.Rows // where budgetRow.Year == ASpecificYear // join ABudgetPeriodRow budgetPeriodRow in FMainDS.ABudgetPeriod.Rows on budgetRow.BudgetSequence equals budgetPeriodRow.BudgetSequence // select new // { // BudgetSequence = budgetRow.BudgetSequence, // PeriodNumber = budgetPeriodRow.PeriodNumber, // Amount = budgetPeriodRow.BudgetBase // }; //produces flat sequence //foreach (var rowObj in varBudgetPeriodAmounts) //{ // DataRow row = BudgetPeriodAmounts.NewRow(); // BudgetPeriodAmounts.Rows.Add(rowObj.BudgetSequence, rowObj.PeriodNumber, rowObj.Amount); //} //DataView BudgetsPeriodAmountsForYearDV = new DataView(BudgetPeriodAmounts); //BudgetsPeriodAmountsForYearDV.Sort = "BudgetSequence ASC, PeriodNumber ASC"; //for (int i = 0; i < BudgetsForYear.Count; i++) //{ // BudgetTDSABudgetRow budgetRow = (BudgetTDSABudgetRow)BudgetsForYear[i].Row; // for (int j = 1; j <= FNumberOfPeriods; j++) // { // DataRow budgetsPeriodAmounts = BudgetsPeriodAmountsForYearDV[(FNumberOfPeriods * i) + j - 1].Row; // string columnName = "Period" + j.ToString("00") + "Amount"; // if (budgetRow.BudgetSequence == (int)budgetsPeriodAmounts["BudgetSequence"]) // { // budgetRow[columnName] = (decimal)budgetsPeriodAmounts["Amount"]; // } // } //} }
public static int ExportBudgets(Int32 ALedgerNumber, string ACSVFileName, string[] AFdlgSeparator, ref string AFileContents, ref BudgetTDS AExportDS, out TVerificationResultCollection AVerificationResult) { #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 (ACSVFileName.Length == 0) { throw new ArgumentException(String.Format(Catalog.GetString("Function:{0} - The CSV File Name is missing!"), Utilities.GetMethodName(true))); } else if (AFdlgSeparator.Length == 0) { throw new ArgumentException(String.Format(Catalog.GetString("Function:{0} - Information on how to parse the CSV file is missing!"), Utilities.GetMethodName(true))); } #endregion Validate Arguments AVerificationResult = null; if (AExportDS != null) { int retVal = ExportBudgetToCSV(ALedgerNumber, ACSVFileName, AFdlgSeparator, ref AFileContents, ref AExportDS, ref AVerificationResult); return retVal; } return 0; }
private static Int32 ExportBudgetToCSV(Int32 ALedgerNumber, string ACSVFileName, string[] AFdlgSeparator, ref string AFileContents, ref BudgetTDS AExportDS, ref TVerificationResultCollection AVerificationResult) { Int32 numBudgetsExported = 0; ALedgerRow lr = (ALedgerRow)AExportDS.ALedger.Rows[0]; ABudgetPeriodTable budgetPeriod = (ABudgetPeriodTable)AExportDS.ABudgetPeriod; Int32 numPeriods = lr.NumberOfAccountingPeriods; char separator = AFdlgSeparator[0].Substring(0, 1).ToCharArray()[0]; TLogging.Log("Writing file: " + ACSVFileName); StringBuilder sb = new StringBuilder(); string budgetAmounts = string.Empty; foreach (ABudgetRow row in AExportDS.ABudget.Rows) { switch (row.BudgetTypeCode) { case MFinanceConstants.BUDGET_SAME: StringBudgetTypeSameAmounts(row.BudgetSequence, ref budgetPeriod, out budgetAmounts); break; case MFinanceConstants.BUDGET_SPLIT: StringBudgetTypeSplitAmounts(row.BudgetSequence, numPeriods, ref budgetPeriod, separator, out budgetAmounts); break; case MFinanceConstants.BUDGET_INFLATE_BASE: StringBudgetTypeInflateBaseAmounts(row.BudgetSequence, numPeriods, ref budgetPeriod, separator, out budgetAmounts); break; case MFinanceConstants.BUDGET_INFLATE_N: StringBudgetTypeInflateNAmounts(row.BudgetSequence, numPeriods, ref budgetPeriod, separator, out budgetAmounts); break; default: //MFinanceConstants.BUDGET_ADHOC: StringBudgetTypeAdhocAmounts(row.BudgetSequence, numPeriods, ref budgetPeriod, separator, out budgetAmounts); break; } sb.Append(StringHelper.StrMerge( new string[] { Encase(row.CostCentreCode), Encase(row.AccountCode), Encase(row.BudgetTypeCode), Encase(BudgetRevisionYearName(ALedgerNumber, row.Year)) }, separator)); sb.Append(separator.ToString()); sb.Append(budgetAmounts); sb.Append(Environment.NewLine); numBudgetsExported++; } AFileContents = sb.ToString(); return(numBudgetsExported); }
public static TSubmitChangesResult SaveBudget(ref BudgetTDS AInspectDS) { if (AInspectDS != null) { BudgetTDSAccess.SubmitChanges(AInspectDS); return TSubmitChangesResult.scrOK; } return TSubmitChangesResult.scrError; }
public static bool LoadBudgetForConsolidate(Int32 ALedgerNumber) { FBudgetTDS = new BudgetTDS(); TDBTransaction Transaction = null; DBAccess.GDBAccessObj.GetNewOrExistingAutoReadTransaction(IsolationLevel.ReadCommitted, TEnforceIsolationLevel.eilMinimum, ref Transaction, delegate { ALedgerAccess.LoadByPrimaryKey(FBudgetTDS, ALedgerNumber, Transaction); string sqlLoadBudgetForThisAndNextYear = string.Format("SELECT * FROM PUB_{0} WHERE {1}=? AND ({2} = ? OR {2} = ?)", ABudgetTable.GetTableDBName(), ABudgetTable.GetLedgerNumberDBName(), ABudgetTable.GetYearDBName()); List <OdbcParameter> parameters = new List <OdbcParameter>(); OdbcParameter param = new OdbcParameter("ledgernumber", OdbcType.Int); param.Value = ALedgerNumber; parameters.Add(param); param = new OdbcParameter("thisyear", OdbcType.Int); param.Value = FBudgetTDS.ALedger[0].CurrentFinancialYear; parameters.Add(param); param = new OdbcParameter("nextyear", OdbcType.Int); param.Value = FBudgetTDS.ALedger[0].CurrentFinancialYear + 1; parameters.Add(param); DBAccess.GDBAccessObj.Select(FBudgetTDS, sqlLoadBudgetForThisAndNextYear, FBudgetTDS.ABudget.TableName, Transaction, parameters.ToArray()); string sqlLoadBudgetPeriodForThisAndNextYear = string.Format("SELECT {0}.* FROM PUB_{0}, PUB_{1} WHERE {0}.a_budget_sequence_i = {1}.a_budget_sequence_i AND " + "{2}=? AND ({3} = ? OR {3} = ?)", ABudgetPeriodTable.GetTableDBName(), ABudgetTable.GetTableDBName(), ABudgetTable.GetLedgerNumberDBName(), ABudgetTable.GetYearDBName()); DBAccess.GDBAccessObj.Select(FBudgetTDS, sqlLoadBudgetPeriodForThisAndNextYear, FBudgetTDS.ABudgetPeriod.TableName, Transaction, parameters.ToArray()); // Accept row changes here so that the Client gets 'unmodified' rows FBudgetTDS.AcceptChanges(); GLPostingDS = new GLPostingTDS(); AAccountAccess.LoadViaALedger(GLPostingDS, ALedgerNumber, Transaction); AAccountHierarchyDetailAccess.LoadViaALedger(GLPostingDS, ALedgerNumber, Transaction); ACostCentreAccess.LoadViaALedger(GLPostingDS, ALedgerNumber, Transaction); ALedgerAccess.LoadByPrimaryKey(GLPostingDS, ALedgerNumber, Transaction); // get the glm sequences for this year and next year for (int i = 0; i <= 1; i++) { int Year = GLPostingDS.ALedger[0].CurrentFinancialYear + i; AGeneralLedgerMasterRow TemplateRow = (AGeneralLedgerMasterRow)GLPostingDS.AGeneralLedgerMaster.NewRowTyped(false); TemplateRow.LedgerNumber = ALedgerNumber; TemplateRow.Year = Year; GLPostingDS.AGeneralLedgerMaster.Merge(AGeneralLedgerMasterAccess.LoadUsingTemplate(TemplateRow, Transaction)); } string sqlLoadGlmperiodForThisAndNextYear = string.Format("SELECT {0}.* FROM PUB_{0}, PUB_{1} WHERE {0}.a_glm_sequence_i = {1}.a_glm_sequence_i AND " + "{2}=? AND ({3} = ? OR {3} = ?)", AGeneralLedgerMasterPeriodTable.GetTableDBName(), AGeneralLedgerMasterTable.GetTableDBName(), AGeneralLedgerMasterTable.GetLedgerNumberDBName(), AGeneralLedgerMasterTable.GetYearDBName()); DBAccess.GDBAccessObj.Select(GLPostingDS, sqlLoadGlmperiodForThisAndNextYear, GLPostingDS.AGeneralLedgerMasterPeriod.TableName, Transaction, parameters.ToArray()); }); GLPostingDS.AcceptChanges(); return(true); }
/// <summary> /// Imports budgets from a file /// </summary> /// <param name="ACurrentBudgetYear"></param> /// <param name="AMainDS"></param> public void ImportBudget(int ACurrentBudgetYear, ref BudgetTDS AMainDS) { TVerificationResultCollection Messages = new TVerificationResultCollection(); int BudgetsImported = 0; int BudgetsAdded = 0; int BudgetsUpdated = 0; int BudgetsFailed = 0; if (FPetraUtilsObject.HasChanges) { // saving failed, therefore do not try to post MessageBox.Show(Catalog.GetString("Please save before trying to import!"), Catalog.GetString( "Failure"), MessageBoxButtons.OK, MessageBoxIcon.Exclamation); return; } String DateFormatString = TUserDefaults.GetStringDefault("Imp Date", "MDY"); OpenFileDialog OFDialog = new OpenFileDialog(); string ExportPath = TClientSettings.GetExportPath(); string FullPath = TUserDefaults.GetStringDefault("Imp Filename", ExportPath + Path.DirectorySeparatorChar + "import.csv"); TImportExportDialogs.SetOpenFileDialogFilePathAndName(OFDialog, FullPath, ExportPath); OFDialog.Title = Catalog.GetString("Import budget(s) from CSV file"); OFDialog.Filter = Catalog.GetString("Text Files(*.txt) | *.txt | Delimited Files(*.csv) | *.csv"); String ImportOptions = TUserDefaults.GetStringDefault("Imp Options", ";" + TDlgSelectCSVSeparator.NUMBERFORMAT_AMERICAN); // This call fixes Windows7 Open File Dialogs. It must be the line before ShowDialog() TWin7FileOpenSaveDialog.PrepareDialog(Path.GetFileName(FullPath)); if (OFDialog.ShowDialog() == DialogResult.OK) { TFrmStatusDialog dlgStatus = new TFrmStatusDialog(FPetraUtilsObject.GetForm()); FdlgSeparator = new TDlgSelectCSVSeparator(false); try { string fileTitle = OFDialog.SafeFileName; Boolean fileCanOpen = FdlgSeparator.OpenCsvFile(OFDialog.FileName); if (!fileCanOpen) { MessageBox.Show(Catalog.GetString("Unable to open file."), Catalog.GetString("Budget Import"), MessageBoxButtons.OK, MessageBoxIcon.Stop); return; } FdlgSeparator.DateFormat = DateFormatString; if (ImportOptions.Length > 1) { FdlgSeparator.NumberFormat = ImportOptions.Substring(1); } FdlgSeparator.SelectedSeparator = ImportOptions.Substring(0, 1); if (FdlgSeparator.ShowDialog() == DialogResult.OK) { string[] FdlgSeparatorVal = new string[] { FdlgSeparator.SelectedSeparator, FdlgSeparator.DateFormat, FdlgSeparator.NumberFormat }; Application.UseWaitCursor = true; //New set of budgets to be loaded dlgStatus.Show(); dlgStatus.Heading = Catalog.GetString("Budget Import"); dlgStatus.CurrentStatus = Catalog.GetString("Importing budgets from '" + fileTitle + "' ..."); // read contents of file string ImportString = File.ReadAllText(OFDialog.FileName); //TODO return the budget from the year, and -99 for fail BudgetsImported = TRemote.MFinance.Budget.WebConnectors.ImportBudgets(FLedgerNumber, ImportString, OFDialog.FileName, FdlgSeparatorVal, ref AMainDS, out BudgetsAdded, out BudgetsUpdated, out BudgetsFailed, out Messages); dlgStatus.Visible = false; Application.UseWaitCursor = false; ShowMessages(Messages, BudgetsImported, BudgetsAdded, BudgetsUpdated, BudgetsFailed); } // We save the defaults even if ok is false - because the client will probably want to try and import // the same file again after correcting any errors SaveUserDefaults(OFDialog, ImportOptions); } catch (Exception ex) { TLogging.LogException(ex, Utilities.GetMethodSignature()); throw; } finally { Application.UseWaitCursor = false; } // update grid if ((BudgetsAdded + BudgetsUpdated) > 0) { try { dlgStatus.CurrentStatus = Catalog.GetString("Updating budget period data..."); dlgStatus.Visible = true; Application.UseWaitCursor = true; UpdateABudgetPeriodAmounts(AMainDS, ACurrentBudgetYear); FUserControl.SetBudgetDefaultView(AMainDS); } finally { Application.UseWaitCursor = false; dlgStatus.Close(); } FPetraUtilsObject.SetChangedFlag(); } else { dlgStatus.Close(); } } }
private static void LoadABudgetByYearWithCustomColumns(ref BudgetTDS AMainDS, Int32 ALedgerNumber, Int32 AYear, Int32 ANumberOfPeriods, TDBTransaction ATransaction) { //Parameters for SQL as strings string prmLedgerNumber = ALedgerNumber.ToString(); string prmYear = AYear.ToString(); //Tables with alias string BudgetTableAlias = "b"; string bBudgetTable = ABudgetTable.GetTableDBName() + " " + BudgetTableAlias; string BudgetPeriodTableAlias = "bp"; string bpBudgetPeriodTable = ABudgetPeriodTable.GetTableDBName() + " " + BudgetPeriodTableAlias; //Table: ABudgetTable and fields string bLedgerNumber = BudgetTableAlias + "." + ABudgetTable.GetLedgerNumberDBName(); string bBudgetSequence = BudgetTableAlias + "." + ABudgetTable.GetBudgetSequenceDBName(); string bYear = BudgetTableAlias + "." + ABudgetTable.GetYearDBName(); string bRevision = BudgetTableAlias + "." + ABudgetTable.GetRevisionDBName(); string bCostCentreCode = BudgetTableAlias + "." + ABudgetTable.GetCostCentreCodeDBName(); string bAccountCode = BudgetTableAlias + "." + ABudgetTable.GetAccountCodeDBName(); string bBudgetTypeCode = BudgetTableAlias + "." + ABudgetTable.GetBudgetTypeCodeDBName(); string bBudgetStatus = BudgetTableAlias + "." + ABudgetTable.GetBudgetStatusDBName(); string bComment = BudgetTableAlias + "." + ABudgetTable.GetCommentDBName(); string bDateCreated = BudgetTableAlias + "." + ABudgetTable.GetDateCreatedDBName(); string bCreatedBy = BudgetTableAlias + "." + ABudgetTable.GetCreatedByDBName(); string bDateModified = BudgetTableAlias + "." + ABudgetTable.GetDateModifiedDBName(); string bModifiedBy = BudgetTableAlias + "." + ABudgetTable.GetModifiedByDBName(); string bModificationId = BudgetTableAlias + "." + ABudgetTable.GetModificationIdDBName(); //Table: ABudgetPeriodTable and fields string bpPeriodNumber = BudgetPeriodTableAlias + "." + ABudgetPeriodTable.GetPeriodNumberDBName(); string bpBudgetBase = BudgetPeriodTableAlias + "." + ABudgetPeriodTable.GetBudgetBaseDBName(); string bpBudgetSequence = BudgetPeriodTableAlias + "." + ABudgetPeriodTable.GetBudgetSequenceDBName(); string SQLStatement = "select " + bBudgetSequence + ", " + bLedgerNumber + ", " + bYear + ", " + bRevision + ", " + bCostCentreCode + ", " + bAccountCode + ", " + bBudgetTypeCode + ", " + bBudgetStatus + ", " + bComment + ", " + bDateCreated + ", " + bCreatedBy + ", " + bDateModified + ", " + bModifiedBy + ", " + bModificationId + ", " + "min(case when " + bpPeriodNumber + " = 1 then " + bpBudgetBase + " end) Period01Amount, " + "min(case when " + bpPeriodNumber + " = 2 then " + bpBudgetBase + " end) Period02Amount, " + "min(case when " + bpPeriodNumber + " = 3 then " + bpBudgetBase + " end) Period03Amount, " + "min(case when " + bpPeriodNumber + " = 4 then " + bpBudgetBase + " end) Period04Amount, " + "min(case when " + bpPeriodNumber + " = 5 then " + bpBudgetBase + " end) Period05Amount, " + "min(case when " + bpPeriodNumber + " = 6 then " + bpBudgetBase + " end) Period06Amount, " + "min(case when " + bpPeriodNumber + " = 7 then " + bpBudgetBase + " end) Period07Amount, " + "min(case when " + bpPeriodNumber + " = 8 then " + bpBudgetBase + " end) Period08Amount, " + "min(case when " + bpPeriodNumber + " = 9 then " + bpBudgetBase + " end) Period09Amount, " + "min(case when " + bpPeriodNumber + " = 10 then " + bpBudgetBase + " end) Period10Amount, " + "min(case when " + bpPeriodNumber + " = 11 then " + bpBudgetBase + " end) Period11Amount, " + "min(case when " + bpPeriodNumber + " = 12 then " + bpBudgetBase + " end) Period12Amount"; if (ANumberOfPeriods > 12) { SQLStatement += ", min(case when " + bpPeriodNumber + " = 13 then " + bpBudgetBase + " end) Period13Amount"; } else { SQLStatement += ", 0 Period13Amount"; } if (ANumberOfPeriods > 13) { SQLStatement += ", min(case when " + bpPeriodNumber + " = 14 then " + bpBudgetBase + " end) Period14Amount"; } else { SQLStatement += ", 0 Period14Amount"; } SQLStatement += " from " + bBudgetTable + " join " + bpBudgetPeriodTable + " on " + bpBudgetSequence + " = " + bBudgetSequence + " where " + bLedgerNumber + " = " + prmLedgerNumber + " and " + bYear + " = " + prmYear + " group by " + bBudgetSequence + ", " + bLedgerNumber + ", " + bYear + ", " + bRevision + ", " + bCostCentreCode + ", " + bAccountCode + ", " + bBudgetTypeCode + ", " + bBudgetStatus + ", " + bComment + ", " + bDateCreated + ", " + bCreatedBy + ", " + bDateModified + ", " + bModifiedBy + ", " + bModificationId + ";"; DBAccess.GDBAccessObj.Select(AMainDS, SQLStatement, AMainDS.ABudget.TableName, ATransaction); }
public static BudgetTDS LoadAllBudgets(Int32 ALedgerNumber) { #region Validate Arguments if (ALedgerNumber <= 0) { throw new EFinanceSystemInvalidLedgerNumberException(String.Format(Catalog.GetString( "Function:{0} - The Ledger number must be greater than 0!"), Utilities.GetMethodName(true)), ALedgerNumber); } #endregion Validate Arguments BudgetTDS MainDS = new BudgetTDS(); TDBTransaction Transaction = null; try { DBAccess.GDBAccessObj.GetNewOrExistingAutoReadTransaction(IsolationLevel.ReadCommitted, TEnforceIsolationLevel.eilMinimum, ref Transaction, delegate { ALedgerAccess.LoadByPrimaryKey(MainDS, ALedgerNumber, Transaction); #region Validate Data if ((MainDS.ALedger == null) || (MainDS.ALedger.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 //Load all by Ledger but none may exist ABudgetAccess.LoadViaALedger(MainDS, ALedgerNumber, Transaction); ABudgetRevisionAccess.LoadViaALedger(MainDS, ALedgerNumber, Transaction); ABudgetTable BudgetTable = new ABudgetTable(); ABudgetRow TemplateRow = (ABudgetRow)BudgetTable.NewRow(); TemplateRow.LedgerNumber = ALedgerNumber; ABudgetPeriodAccess.LoadViaABudgetTemplate(MainDS, TemplateRow, Transaction); }); // Accept row changes here so that the Client gets 'unmodified' rows MainDS.AcceptChanges(); // Remove all Tables that were not filled with data before remoting them. MainDS.RemoveEmptyTables(); } catch (Exception ex) { TLogging.Log(String.Format("Method:{0} - Unexpected error!{1}{1}{2}", Utilities.GetMethodSignature(), Environment.NewLine, ex.Message)); throw ex; } return MainDS; }
public void T0_Consolidation() { // reset the database, so that there is no consolidated budget CommonNUnitFunctions.ResetDatabase(); string budgetTestFile = TAppSettingsManager.GetValue("GiftBatch.file", CommonNUnitFunctions.rootPath + "/csharp/ICT/Testing/lib/MFinance/SampleData/BudgetImport-All.csv"); TVerificationResultCollection VerificationResult; BudgetTDS ImportDS = new BudgetTDS(); // import budget from CSV decimal RowsImported = TBudgetMaintainWebConnector.ImportBudgets( FLedgerNumber, 0, budgetTestFile, new string[] { ",", "dmy", "American" }, ref ImportDS, out VerificationResult); Assert.AreNotEqual(0, RowsImported, "expect to import several rows"); CommonNUnitFunctions.EnsureNullOrOnlyNonCriticalVerificationResults(VerificationResult, "ImportBudgets has critical errors:"); BudgetTDSAccess.SubmitChanges(ImportDS); // check for value in budget table string sqlQueryBudget = String.Format( "SELECT {0} FROM PUB_{1}, PUB_{2} WHERE {1}.a_budget_sequence_i = {2}.a_budget_sequence_i AND a_period_number_i = 1 AND " + "a_ledger_number_i = {3} AND a_revision_i = 0 AND a_year_i = 0 AND a_account_code_c = '0300' AND a_cost_centre_code_c = '4300'", ABudgetPeriodTable.GetBudgetBaseDBName(), ABudgetTable.GetTableDBName(), ABudgetPeriodTable.GetTableDBName(), FLedgerNumber); decimal budgetValue = Convert.ToDecimal(DBAccess.GDBAccessObj.ExecuteScalar(sqlQueryBudget, IsolationLevel.ReadCommitted)); Assert.AreEqual(250m, budgetValue, "problem with importing budget from CSV"); // check for zero in glmperiod budget: that row does not even exist yet, so check that it does not exist string sqlQueryCheckEmptyConsolidatedBudget = String.Format( "SELECT COUNT(*) FROM PUB_{0}, PUB_{1} WHERE {0}.a_glm_sequence_i = {1}.a_glm_sequence_i AND a_period_number_i = 1 AND " + "a_ledger_number_i = {2} AND a_year_i = 0 AND a_account_code_c = '0300' AND a_cost_centre_code_c = '4300'", AGeneralLedgerMasterPeriodTable.GetTableDBName(), AGeneralLedgerMasterTable.GetTableDBName(), FLedgerNumber); Assert.AreEqual(0, DBAccess.GDBAccessObj.ExecuteScalar(sqlQueryCheckEmptyConsolidatedBudget, IsolationLevel.ReadCommitted), "budget should not be consolidated yet"); // consolidate the budget TBudgetConsolidateWebConnector.LoadBudgetForConsolidate(FLedgerNumber); TBudgetConsolidateWebConnector.ConsolidateBudgets(FLedgerNumber, true); // check for correct value in glmperiod budget string sqlQueryConsolidatedBudget = String.Format( "SELECT {0} FROM PUB_{1}, PUB_{2} WHERE {1}.a_glm_sequence_i = {2}.a_glm_sequence_i AND a_period_number_i = 1 AND " + "a_ledger_number_i = {3} AND a_year_i = 0 AND a_account_code_c = '0300' AND a_cost_centre_code_c = '4300'", AGeneralLedgerMasterPeriodTable.GetBudgetBaseDBName(), AGeneralLedgerMasterPeriodTable.GetTableDBName(), AGeneralLedgerMasterTable.GetTableDBName(), FLedgerNumber); decimal consolidatedBudgetValue = Convert.ToDecimal(DBAccess.GDBAccessObj.ExecuteScalar(sqlQueryConsolidatedBudget, IsolationLevel.ReadCommitted)); Assert.AreEqual(250m, consolidatedBudgetValue, "budget should now be consolidated"); // TODO: also check some summary account and cost centre for summed up budget values // check how reposting a budget works string sqlChangeBudget = String.Format("UPDATE PUB_{0} SET {1} = 44 WHERE a_period_number_i = 1 AND " + "EXISTS (SELECT * FROM PUB_{2} WHERE {0}.a_budget_sequence_i = {2}.a_budget_sequence_i AND a_ledger_number_i = {3} " + "AND a_year_i = 0 AND a_revision_i = 0 AND a_account_code_c = '0300' AND a_cost_centre_code_c = '4300')", ABudgetPeriodTable.GetTableDBName(), ABudgetPeriodTable.GetBudgetBaseDBName(), ABudgetTable.GetTableDBName(), FLedgerNumber); TDBTransaction Transaction = DBAccess.GDBAccessObj.BeginTransaction(IsolationLevel.Serializable); DBAccess.GDBAccessObj.ExecuteNonQuery(sqlChangeBudget, Transaction); DBAccess.GDBAccessObj.CommitTransaction(); // post all budgets again TBudgetConsolidateWebConnector.LoadBudgetForConsolidate(FLedgerNumber); TBudgetConsolidateWebConnector.ConsolidateBudgets(FLedgerNumber, true); consolidatedBudgetValue = Convert.ToDecimal(DBAccess.GDBAccessObj.ExecuteScalar(sqlQueryConsolidatedBudget, IsolationLevel.ReadCommitted)); Assert.AreEqual(44.0m, consolidatedBudgetValue, "budget should be consolidated with the new value"); // post only a modified budget (testing UnPostBudget) sqlChangeBudget = String.Format("UPDATE PUB_{0} SET {1} = 65 WHERE a_period_number_i = 1 AND " + "EXISTS (SELECT * FROM PUB_{2} WHERE {0}.a_budget_sequence_i = {2}.a_budget_sequence_i AND a_ledger_number_i = {3} " + "AND a_year_i = 0 AND a_revision_i = 0 AND a_account_code_c = '0300' AND a_cost_centre_code_c = '4300')", ABudgetPeriodTable.GetTableDBName(), ABudgetPeriodTable.GetBudgetBaseDBName(), ABudgetTable.GetTableDBName(), FLedgerNumber); string sqlMarkBudgetForConsolidation = String.Format("UPDATE PUB_{0} SET {1} = false WHERE " + "a_ledger_number_i = {2} " + "AND a_year_i = 0 AND a_revision_i = 0 AND a_account_code_c = '0300' AND a_cost_centre_code_c = '4300'", ABudgetTable.GetTableDBName(), ABudgetTable.GetBudgetStatusDBName(), FLedgerNumber); Transaction = DBAccess.GDBAccessObj.BeginTransaction(IsolationLevel.Serializable); DBAccess.GDBAccessObj.ExecuteNonQuery(sqlChangeBudget, Transaction); DBAccess.GDBAccessObj.ExecuteNonQuery(sqlMarkBudgetForConsolidation, Transaction); DBAccess.GDBAccessObj.CommitTransaction(); // post only modified budget again TBudgetConsolidateWebConnector.LoadBudgetForConsolidate(FLedgerNumber); TBudgetConsolidateWebConnector.ConsolidateBudgets(FLedgerNumber, false); consolidatedBudgetValue = Convert.ToDecimal(DBAccess.GDBAccessObj.ExecuteScalar(sqlQueryConsolidatedBudget, IsolationLevel.ReadCommitted)); Assert.AreEqual(65.0m, consolidatedBudgetValue, "budget should be consolidated with the new value, after UnPostBudget"); // TODO: test forwarding periods. what happens to next year values, when there is no next year glm record yet? }
private void LoadBudgets() { FBudgetDS = TRemote.MFinance.Budget.WebConnectors.LoadBudget(FLedgerNumber); }
public static int ExportBudgets(Int32 ALedgerNumber, string ACSVFileName, string[] AFdlgSeparator, ref string AFileContents, ref BudgetTDS AExportDS, out TVerificationResultCollection AVerificationResult) { AVerificationResult = null; if (AExportDS != null) { int retVal = ExportBudgetToCSV(ALedgerNumber, ACSVFileName, AFdlgSeparator, ref AFileContents, ref AExportDS, ref AVerificationResult); return retVal; } return 0; }
private static Int32 ExportBudgetToCSV(Int32 ALedgerNumber, string ACSVFileName, string[] AFdlgSeparator, ref string AFileContents, ref BudgetTDS AExportDS, ref TVerificationResultCollection AVerificationResult) { Int32 numBudgetsExported = 0; ALedgerRow lr = (ALedgerRow)AExportDS.ALedger.Rows[0]; ABudgetPeriodTable budgetPeriod = (ABudgetPeriodTable)AExportDS.ABudgetPeriod; Int32 numPeriods = lr.NumberOfAccountingPeriods; char separator = AFdlgSeparator[0].Substring(0, 1).ToCharArray()[0]; TLogging.Log("Writing file: " + ACSVFileName); StringBuilder sb = new StringBuilder(); string budgetAmounts = string.Empty; foreach (ABudgetRow row in AExportDS.ABudget.Rows) { switch (row.BudgetTypeCode) { case MFinanceConstants.BUDGET_SAME: StringBudgetTypeSameAmounts(row.BudgetSequence, ref budgetPeriod, out budgetAmounts); break; case MFinanceConstants.BUDGET_SPLIT: StringBudgetTypeSplitAmounts(row.BudgetSequence, numPeriods, ref budgetPeriod, separator, out budgetAmounts); break; case MFinanceConstants.BUDGET_INFLATE_BASE: StringBudgetTypeInflateBaseAmounts(row.BudgetSequence, numPeriods, ref budgetPeriod, separator, out budgetAmounts); break; case MFinanceConstants.BUDGET_INFLATE_N: StringBudgetTypeInflateNAmounts(row.BudgetSequence, numPeriods, ref budgetPeriod, separator, out budgetAmounts); break; default: //MFinanceConstants.BUDGET_ADHOC: StringBudgetTypeAdhocAmounts(row.BudgetSequence, numPeriods, ref budgetPeriod, separator, out budgetAmounts); break; } sb.Append(StringHelper.StrMerge( new string[] { Encase(row.CostCentreCode), Encase(row.AccountCode), Encase(row.BudgetTypeCode), Encase(BudgetRevisionYearName(ALedgerNumber, row.Year)) }, separator)); sb.Append(separator.ToString()); sb.Append(budgetAmounts); sb.Append(Environment.NewLine); numBudgetsExported++; } AFileContents = sb.ToString(); return numBudgetsExported; }
public void T0_Consolidation() { // reset the database, so that there is no consolidated budget CommonNUnitFunctions.ResetDatabase(); string budgetTestFile = TAppSettingsManager.GetValue("GiftBatch.file", CommonNUnitFunctions.rootPath + "/csharp/ICT/Testing/lib/MFinance/SampleData/BudgetImport-All.csv"); int NumBudgetsUpdated; int NumFailedRows; TVerificationResultCollection VerificationResult; BudgetTDS ImportDS = new BudgetTDS(); string ImportString = File.ReadAllText(budgetTestFile); // import budget from CSV decimal RowsImported = TBudgetMaintainWebConnector.ImportBudgets( FLedgerNumber, 0, ImportString, budgetTestFile, new string[] { ",", "dmy", "American" }, ref ImportDS, out NumBudgetsUpdated, out NumFailedRows, out VerificationResult); Assert.AreNotEqual(0, RowsImported, "expect to import several rows"); CommonNUnitFunctions.EnsureNullOrOnlyNonCriticalVerificationResults(VerificationResult, "ImportBudgets has critical errors:"); BudgetTDSAccess.SubmitChanges(ImportDS); // check for value in budget table string sqlQueryBudget = String.Format( "SELECT {0} FROM PUB_{1}, PUB_{2} WHERE {1}.a_budget_sequence_i = {2}.a_budget_sequence_i AND a_period_number_i = 1 AND " + "a_ledger_number_i = {3} AND a_revision_i = 0 AND a_year_i = 0 AND a_account_code_c = '0300' AND a_cost_centre_code_c = '4300'", ABudgetPeriodTable.GetBudgetBaseDBName(), ABudgetTable.GetTableDBName(), ABudgetPeriodTable.GetTableDBName(), FLedgerNumber); decimal budgetValue = Convert.ToDecimal(DBAccess.GDBAccessObj.ExecuteScalar(sqlQueryBudget, IsolationLevel.ReadCommitted)); Assert.AreEqual(250m, budgetValue, "problem with importing budget from CSV"); // check for zero in glmperiod budget: that row does not even exist yet, so check that it does not exist string sqlQueryCheckEmptyConsolidatedBudget = String.Format( "SELECT COUNT(*) FROM PUB_{0}, PUB_{1} WHERE {0}.a_glm_sequence_i = {1}.a_glm_sequence_i AND a_period_number_i = 1 AND " + "a_ledger_number_i = {2} AND a_year_i = 0 AND a_account_code_c = '0300' AND a_cost_centre_code_c = '4300'", AGeneralLedgerMasterPeriodTable.GetTableDBName(), AGeneralLedgerMasterTable.GetTableDBName(), FLedgerNumber); Assert.AreEqual(0, DBAccess.GDBAccessObj.ExecuteScalar(sqlQueryCheckEmptyConsolidatedBudget, IsolationLevel.ReadCommitted), "budget should not be consolidated yet"); // consolidate the budget TBudgetConsolidateWebConnector.LoadBudgetForConsolidate(FLedgerNumber); TBudgetConsolidateWebConnector.ConsolidateBudgets(FLedgerNumber, true); // check for correct value in glmperiod budget string sqlQueryConsolidatedBudget = String.Format( "SELECT {0} FROM PUB_{1}, PUB_{2} WHERE {1}.a_glm_sequence_i = {2}.a_glm_sequence_i AND a_period_number_i = 1 AND " + "a_ledger_number_i = {3} AND a_year_i = 0 AND a_account_code_c = '0300' AND a_cost_centre_code_c = '4300'", AGeneralLedgerMasterPeriodTable.GetBudgetBaseDBName(), AGeneralLedgerMasterPeriodTable.GetTableDBName(), AGeneralLedgerMasterTable.GetTableDBName(), FLedgerNumber); decimal consolidatedBudgetValue = Convert.ToDecimal(DBAccess.GDBAccessObj.ExecuteScalar(sqlQueryConsolidatedBudget, IsolationLevel.ReadCommitted)); Assert.AreEqual(250m, consolidatedBudgetValue, "budget should now be consolidated"); // TODO: also check some summary account and cost centre for summed up budget values // check how reposting a budget works string sqlChangeBudget = String.Format("UPDATE PUB_{0} SET {1} = 44 WHERE a_period_number_i = 1 AND " + "EXISTS (SELECT * FROM PUB_{2} WHERE {0}.a_budget_sequence_i = {2}.a_budget_sequence_i AND a_ledger_number_i = {3} " + "AND a_year_i = 0 AND a_revision_i = 0 AND a_account_code_c = '0300' AND a_cost_centre_code_c = '4300')", ABudgetPeriodTable.GetTableDBName(), ABudgetPeriodTable.GetBudgetBaseDBName(), ABudgetTable.GetTableDBName(), FLedgerNumber); bool SubmissionOK = true; TDBTransaction Transaction = null; DBAccess.GDBAccessObj.BeginAutoTransaction(IsolationLevel.Serializable, ref Transaction, ref SubmissionOK, delegate { DBAccess.GDBAccessObj.ExecuteNonQuery(sqlChangeBudget, Transaction); }); // post all budgets again TBudgetConsolidateWebConnector.LoadBudgetForConsolidate(FLedgerNumber); TBudgetConsolidateWebConnector.ConsolidateBudgets(FLedgerNumber, true); consolidatedBudgetValue = Convert.ToDecimal(DBAccess.GDBAccessObj.ExecuteScalar(sqlQueryConsolidatedBudget, IsolationLevel.ReadCommitted)); Assert.AreEqual(44.0m, consolidatedBudgetValue, "budget should be consolidated with the new value"); // post only a modified budget (testing UnPostBudget) sqlChangeBudget = String.Format("UPDATE PUB_{0} SET {1} = 65 WHERE a_period_number_i = 1 AND " + "EXISTS (SELECT * FROM PUB_{2} WHERE {0}.a_budget_sequence_i = {2}.a_budget_sequence_i AND a_ledger_number_i = {3} " + "AND a_year_i = 0 AND a_revision_i = 0 AND a_account_code_c = '0300' AND a_cost_centre_code_c = '4300')", ABudgetPeriodTable.GetTableDBName(), ABudgetPeriodTable.GetBudgetBaseDBName(), ABudgetTable.GetTableDBName(), FLedgerNumber); string sqlMarkBudgetForConsolidation = String.Format("UPDATE PUB_{0} SET {1} = false WHERE " + "a_ledger_number_i = {2} " + "AND a_year_i = 0 AND a_revision_i = 0 AND a_account_code_c = '0300' AND a_cost_centre_code_c = '4300'", ABudgetTable.GetTableDBName(), ABudgetTable.GetBudgetStatusDBName(), FLedgerNumber); SubmissionOK = true; Transaction = null; DBAccess.GDBAccessObj.BeginAutoTransaction(IsolationLevel.Serializable, ref Transaction, ref SubmissionOK, delegate { DBAccess.GDBAccessObj.ExecuteNonQuery(sqlChangeBudget, Transaction); DBAccess.GDBAccessObj.ExecuteNonQuery(sqlMarkBudgetForConsolidation, Transaction); }); // post only modified budget again TBudgetConsolidateWebConnector.LoadBudgetForConsolidate(FLedgerNumber); TBudgetConsolidateWebConnector.ConsolidateBudgets(FLedgerNumber, false); consolidatedBudgetValue = Convert.ToDecimal(DBAccess.GDBAccessObj.ExecuteScalar(sqlQueryConsolidatedBudget, IsolationLevel.ReadCommitted)); Assert.AreEqual(65.0m, consolidatedBudgetValue, "budget should be consolidated with the new value, after UnPostBudget"); // TODO: test forwarding periods. what happens to next year values, when there is no next year glm record yet? }
public static BudgetTDS LoadBudgetsForYear(Int32 ALedgerNumber, Int32 ABudgetYear) { #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 (ABudgetYear < 0) { throw new ArgumentException(String.Format(Catalog.GetString("Function:{0} - The Budget Year number cannot be negative!"), Utilities.GetMethodName(true))); } #endregion Validate Arguments BudgetTDS MainDS = new BudgetTDS(); TDBTransaction Transaction = null; try { DBAccess.GDBAccessObj.GetNewOrExistingAutoReadTransaction(IsolationLevel.ReadCommitted, TEnforceIsolationLevel.eilMinimum, ref Transaction, delegate { ALedgerAccess.LoadByPrimaryKey(MainDS, ALedgerNumber, Transaction); #region Validate Data if ((MainDS.ALedger == null) || (MainDS.ALedger.Count == 0)) { throw new EFinanceSystemDataTableReturnedNoDataException(String.Format(Catalog.GetString( "Function:{0} - Ledger data for Ledger number {1} does not exist or could not be accessed!"), Utilities.GetMethodName(true), ALedgerNumber)); } #endregion Validate Data int numPeriods = MainDS.ALedger[0].NumberOfAccountingPeriods; //Load all by Year LoadABudgetByYearWithCustomColumns(ref MainDS, ALedgerNumber, ABudgetYear, numPeriods, Transaction); //Load budget period data ABudgetTable BudgetTable = new ABudgetTable(); ABudgetRow TemplateRow = (ABudgetRow)BudgetTable.NewRow(); TemplateRow.Year = ABudgetYear; ABudgetPeriodAccess.LoadViaABudgetTemplate(MainDS, TemplateRow, Transaction); //TODO: add Budget Revision capability when decision made to add it to OP. // Assume Revision=0 for now until implemented ABudgetRevisionAccess.LoadByPrimaryKey(MainDS, ALedgerNumber, ABudgetYear, 0, Transaction); }); // Accept row changes here so that the Client gets 'unmodified' rows MainDS.AcceptChanges(); // Remove all Tables that were not filled with data before remoting them. MainDS.RemoveEmptyTables(); } catch (Exception ex) { TLogging.Log(String.Format("Method:{0} - Unexpected error!{1}{1}{2}", Utilities.GetMethodSignature(), Environment.NewLine, ex.Message)); throw ex; } return MainDS; }
/// <summary> /// test the budget autogeneration /// </summary> private BudgetTDS LoadData() { BudgetTDS MainDS = new BudgetTDS(); MainDS.Merge(TBudgetAutoGenerateWebConnector.LoadBudgetForAutoGenerate(FLedgerNumber)); //Not needed MainDS.RemoveTable("AGeneralLedgerMasterPeriod"); return MainDS; }
private TSubmitChangesResult StoreManualCode(ref BudgetTDS ASubmitChanges, out TVerificationResultCollection AVerificationResult) { AVerificationResult = null; TSubmitChangesResult TSCR = TRemote.MFinance.Budget.WebConnectors.SaveBudget(ref ASubmitChanges); //Reset this flag if the save was successful FRejectYearChange = !(TSCR == TSubmitChangesResult.scrOK); return TSCR; }
public static decimal ImportBudgets(Int32 ALedgerNumber, Int32 ACurrentBudgetYear, string ACSVFileName, string[] AFdlgSeparator, ref BudgetTDS AImportDS, out TVerificationResultCollection AVerificationResult) { AVerificationResult = null; if (AImportDS != null) { decimal retVal = ImportBudgetFromCSV(ALedgerNumber, ACurrentBudgetYear, ACSVFileName, AFdlgSeparator, ref AImportDS, ref AVerificationResult); return retVal; } return 0; }
public static Int32 ImportBudgets(Int32 ALedgerNumber, Int32 ACurrentBudgetYear, string AImportString, string ACSVFileName, string[] AFdlgSeparator, ref BudgetTDS AImportDS, out Int32 ARecordsUpdated, out Int32 AFailedRows, out TVerificationResultCollection AVerificationResult) { #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 (ACurrentBudgetYear < 0) { throw new ArgumentException(String.Format(Catalog.GetString("Function:{0} - The Budget Year number cannot be negative!"), Utilities.GetMethodName(true))); } else if (ACSVFileName.Length == 0) { throw new ArgumentException(String.Format(Catalog.GetString("Function:{0} - The CSV File Name is missing!"), Utilities.GetMethodName(true))); } else if (AFdlgSeparator.Length < 3) { throw new ArgumentException(String.Format(Catalog.GetString("Function:{0} - Information on how to parse the CSV file is missing!"), Utilities.GetMethodName(true))); } #endregion Validate Arguments ARecordsUpdated = 0; AFailedRows = 0; AVerificationResult = new TVerificationResultCollection(); if (AImportDS != null) { int retVal = ImportBudgetFromCSV(ALedgerNumber, ACurrentBudgetYear, AImportString, ACSVFileName, AFdlgSeparator, ref AImportDS, ref ARecordsUpdated, ref AFailedRows, ref AVerificationResult); return retVal; } return 0; }
/// <summary> /// Import the budget from a CSV file /// </summary> /// <param name="ALedgerNumber"></param> /// <param name="ACurrentBudgetYear"></param> /// <param name="ACSVFileName"></param> /// <param name="AFdlgSeparator"></param> /// <param name="AImportDS"></param> /// <param name="AVerificationResult"></param> /// <returns>Total number of records imported and number of which updated as the fractional part</returns> private static decimal ImportBudgetFromCSV(Int32 ALedgerNumber, Int32 ACurrentBudgetYear, string ACSVFileName, string[] AFdlgSeparator, ref BudgetTDS AImportDS, ref TVerificationResultCollection AVerificationResult) { StreamReader DataFile = new StreamReader(ACSVFileName, System.Text.Encoding.Default); string Separator = AFdlgSeparator[0]; string DateFormat = AFdlgSeparator[1]; string NumberFormat = AFdlgSeparator[2]; FCultureInfoNumberFormat = new CultureInfo(NumberFormat.Equals("American") ? "en-US" : "de-DE"); CultureInfo MyCultureInfoDate = new CultureInfo("en-GB"); MyCultureInfoDate.DateTimeFormat.ShortDatePattern = DateFormat; // To store the From and To currencies // Use an array to store these to make for easy // inverting of the two currencies when calculating // the inverse value. //string currentBudgetVal = string.Empty; //string mess = string.Empty; string CostCentre = string.Empty; string Account = string.Empty; string budgetType = string.Empty; string budgetYearString = string.Empty; int budgetYear = 0; Int32 numPeriods = TAccountingPeriodsWebConnector.GetNumberOfPeriods(ALedgerNumber); decimal[] BudgetPeriods = new decimal[numPeriods]; int YearForBudgetRevision = 0; int BdgRevision = 0; //not currently implementing versioning so always zero decimal rowNumber = 0; decimal duplicateRowNumber = 0; while (!DataFile.EndOfStream) { decimal totalBudgetRowAmount = 0; try { string Line = DataFile.ReadLine(); CostCentre = StringHelper.GetNextCSV(ref Line, Separator, false).ToString(); if (CostCentre == "Cost Centre") { //Read the next line Line = DataFile.ReadLine(); CostCentre = StringHelper.GetNextCSV(ref Line, Separator, false).ToString(); } //Increment row number rowNumber++; //Convert separator to a char // char Sep = Separator[0]; //Turn current line into string array of column values // string[] CsvColumns = Line.Split(Sep); //int NumCols = CsvColumns.Length; //If number of columns is not 4 then import csv file is wrongly formed. // if (NumCols != 24) // { // AVerificationResult. MessageBox.Show(Catalog.GetString("Failed to import the CSV budget file:\r\n\r\n" + // " " + ADataFilename + "\r\n\r\n" + // "It contains " + NumCols.ToString() + " columns. " + // ), AImportMode + " Exchange Rates Import Error"); // return; // } //Read the values for the current line Account = StringHelper.GetNextCSV(ref Line, Separator, false).ToString(); budgetType = StringHelper.GetNextCSV(ref Line, Separator, false).ToString().ToUpper(); budgetType = budgetType.Replace(" ", ""); //Ad hoc will become ADHOC //Allow for variations on Inf.Base and Inf.N if (budgetType.Contains("INF")) { if (budgetType.Contains("BASE")) { if (budgetType != MFinanceConstants.BUDGET_INFLATE_BASE) { budgetType = MFinanceConstants.BUDGET_INFLATE_BASE; } } else if (budgetType != MFinanceConstants.BUDGET_INFLATE_N) { budgetType = MFinanceConstants.BUDGET_INFLATE_N; } } if ((budgetType != MFinanceConstants.BUDGET_ADHOC) && (budgetType != MFinanceConstants.BUDGET_SAME) && (budgetType != MFinanceConstants.BUDGET_INFLATE_N) && (budgetType != MFinanceConstants.BUDGET_SPLIT) && (budgetType != MFinanceConstants.BUDGET_INFLATE_BASE) ) { throw new InvalidOperationException("Budget Type: " + budgetType + " in row: " + rowNumber.ToString() + " does not exist."); } //Calculate the budget Year budgetYearString = StringHelper.GetNextCSV(ref Line, Separator, false); YearForBudgetRevision = BudgetRevisionYearNumber(ALedgerNumber, budgetYearString); //Add budget revision record if there's not one already. if (AImportDS.ABudgetRevision.Rows.Find(new object[] { ALedgerNumber, YearForBudgetRevision, BdgRevision }) == null) { ABudgetRevisionRow BudgetRevisionRow = (ABudgetRevisionRow)AImportDS.ABudgetRevision.NewRowTyped(); BudgetRevisionRow.LedgerNumber = ALedgerNumber; BudgetRevisionRow.Year = YearForBudgetRevision; BudgetRevisionRow.Revision = BdgRevision; BudgetRevisionRow.Description = "Budget Import from: " + ACSVFileName; AImportDS.ABudgetRevision.Rows.Add(BudgetRevisionRow); } //Read the budgetperiod values to check if valid according to type Array.Clear(BudgetPeriods, 0, numPeriods); bool successfulBudgetRowProcessing = ProcessBudgetTypeImportDetails(ref Line, Separator, budgetType, ref BudgetPeriods); for (int i = 0; i < numPeriods; i++) { totalBudgetRowAmount += BudgetPeriods[i]; } if (!successfulBudgetRowProcessing) { throw new InvalidOperationException(String.Format( "The budget in row {0} for Ledger: {1}, Year: {2}, Cost Centre: {3} and Account: {4}, does not have values consistent with Budget Type: {5}.", rowNumber, ALedgerNumber, budgetYear, CostCentre, Account, budgetType)); } BudgetTDS MainDS = new BudgetTDS(); TDBTransaction transaction = null; DBAccess.GDBAccessObj.GetNewOrExistingAutoReadTransaction(IsolationLevel.ReadCommitted, TEnforceIsolationLevel.eilMinimum, ref transaction, delegate { ABudgetAccess.LoadByUniqueKey(MainDS, ALedgerNumber, YearForBudgetRevision, BdgRevision, CostCentre, Account, transaction); //TODO: need to filter on ABudgetPeriod using LoadViaBudget or LoadViaUniqueKey }); //Check to see if the budget combination already exists: if (MainDS.ABudget.Count > 0) { ABudgetRow BR2 = (ABudgetRow)MainDS.ABudget.Rows[0]; int BTSeq = BR2.BudgetSequence; ABudgetRow BdgTRow = (ABudgetRow)AImportDS.ABudget.Rows.Find(new object[] { BTSeq }); if (BdgTRow != null) { duplicateRowNumber++; BdgTRow.BeginEdit(); //Edit the new budget row BdgTRow.BudgetTypeCode = budgetType; BdgTRow.EndEdit(); ABudgetPeriodRow BPRow = null; for (int i = 0; i < numPeriods; i++) { BPRow = (ABudgetPeriodRow)AImportDS.ABudgetPeriod.Rows.Find(new object[] { BTSeq, i + 1 }); if (BPRow != null) { BPRow.BeginEdit(); BPRow.BudgetBase = BudgetPeriods[i]; BPRow.EndEdit(); } BPRow = null; } } } else { //Add the new budget row ABudgetRow BudgetRow = (ABudgetRow)AImportDS.ABudget.NewRowTyped(); int newSequence = -1 * (AImportDS.ABudget.Rows.Count + 1); BudgetRow.BudgetSequence = newSequence; BudgetRow.LedgerNumber = ALedgerNumber; BudgetRow.Year = YearForBudgetRevision; BudgetRow.Revision = BdgRevision; BudgetRow.CostCentreCode = CostCentre; BudgetRow.AccountCode = Account; BudgetRow.BudgetTypeCode = budgetType; AImportDS.ABudget.Rows.Add(BudgetRow); //Add the budget periods for (int i = 0; i < numPeriods; i++) { ABudgetPeriodRow BudgetPeriodRow = (ABudgetPeriodRow)AImportDS.ABudgetPeriod.NewRowTyped(); BudgetPeriodRow.BudgetSequence = newSequence; BudgetPeriodRow.PeriodNumber = i + 1; BudgetPeriodRow.BudgetBase = BudgetPeriods[i]; AImportDS.ABudgetPeriod.Rows.Add(BudgetPeriodRow); } } } catch (Exception) { throw; } } DataFile.Close(); if (duplicateRowNumber > 0) { //fractional part is the number of updates divided by 10000 if (duplicateRowNumber < 10000) { rowNumber += (duplicateRowNumber / 10000); } } return rowNumber; }
public static bool LoadBudgetForConsolidate(Int32 ALedgerNumber) { FBudgetTDS = new BudgetTDS(); TDBTransaction Transaction = null; DBAccess.GDBAccessObj.GetNewOrExistingAutoReadTransaction(IsolationLevel.ReadCommitted, TEnforceIsolationLevel.eilMinimum, ref Transaction, delegate { ALedgerAccess.LoadByPrimaryKey(FBudgetTDS, ALedgerNumber, Transaction); string sqlLoadBudgetForThisAndNextYear = string.Format("SELECT * FROM PUB_{0} WHERE {1}=? AND ({2} = ? OR {2} = ?)", ABudgetTable.GetTableDBName(), ABudgetTable.GetLedgerNumberDBName(), ABudgetTable.GetYearDBName()); List <OdbcParameter>parameters = new List <OdbcParameter>(); OdbcParameter param = new OdbcParameter("ledgernumber", OdbcType.Int); param.Value = ALedgerNumber; parameters.Add(param); param = new OdbcParameter("thisyear", OdbcType.Int); param.Value = FBudgetTDS.ALedger[0].CurrentFinancialYear; parameters.Add(param); param = new OdbcParameter("nextyear", OdbcType.Int); param.Value = FBudgetTDS.ALedger[0].CurrentFinancialYear + 1; parameters.Add(param); DBAccess.GDBAccessObj.Select(FBudgetTDS, sqlLoadBudgetForThisAndNextYear, FBudgetTDS.ABudget.TableName, Transaction, parameters.ToArray()); string sqlLoadBudgetPeriodForThisAndNextYear = string.Format("SELECT {0}.* FROM PUB_{0}, PUB_{1} WHERE {0}.a_budget_sequence_i = {1}.a_budget_sequence_i AND " + "{2}=? AND ({3} = ? OR {3} = ?)", ABudgetPeriodTable.GetTableDBName(), ABudgetTable.GetTableDBName(), ABudgetTable.GetLedgerNumberDBName(), ABudgetTable.GetYearDBName()); DBAccess.GDBAccessObj.Select(FBudgetTDS, sqlLoadBudgetPeriodForThisAndNextYear, FBudgetTDS.ABudgetPeriod.TableName, Transaction, parameters.ToArray()); // Accept row changes here so that the Client gets 'unmodified' rows FBudgetTDS.AcceptChanges(); GLPostingDS = new GLPostingTDS(); AAccountAccess.LoadViaALedger(GLPostingDS, ALedgerNumber, Transaction); AAccountHierarchyDetailAccess.LoadViaALedger(GLPostingDS, ALedgerNumber, Transaction); ACostCentreAccess.LoadViaALedger(GLPostingDS, ALedgerNumber, Transaction); ALedgerAccess.LoadByPrimaryKey(GLPostingDS, ALedgerNumber, Transaction); // get the glm sequences for this year and next year for (int i = 0; i <= 1; i++) { int Year = GLPostingDS.ALedger[0].CurrentFinancialYear + i; AGeneralLedgerMasterRow TemplateRow = (AGeneralLedgerMasterRow)GLPostingDS.AGeneralLedgerMaster.NewRowTyped(false); TemplateRow.LedgerNumber = ALedgerNumber; TemplateRow.Year = Year; GLPostingDS.AGeneralLedgerMaster.Merge(AGeneralLedgerMasterAccess.LoadUsingTemplate(TemplateRow, Transaction)); } string sqlLoadGlmperiodForThisAndNextYear = string.Format("SELECT {0}.* FROM PUB_{0}, PUB_{1} WHERE {0}.a_glm_sequence_i = {1}.a_glm_sequence_i AND " + "{2}=? AND ({3} = ? OR {3} = ?)", AGeneralLedgerMasterPeriodTable.GetTableDBName(), AGeneralLedgerMasterTable.GetTableDBName(), AGeneralLedgerMasterTable.GetLedgerNumberDBName(), AGeneralLedgerMasterTable.GetYearDBName()); DBAccess.GDBAccessObj.Select(GLPostingDS, sqlLoadGlmperiodForThisAndNextYear, GLPostingDS.AGeneralLedgerMasterPeriod.TableName, Transaction, parameters.ToArray()); }); GLPostingDS.AcceptChanges(); return true; }