private static string BudgetRevisionYearName(int ALedgerNumber, int ABudgetRevisionYear) { int budgetYear = 0; ALedgerTable LedgerTable = null; AAccountingPeriodTable accPeriodTable = null; TDBTransaction Transaction = null; DBAccess.GDBAccessObj.GetNewOrExistingAutoReadTransaction(IsolationLevel.ReadCommitted, TEnforceIsolationLevel.eilMinimum, ref Transaction, delegate { LedgerTable = ALedgerAccess.LoadByPrimaryKey(ALedgerNumber, Transaction); accPeriodTable = AAccountingPeriodAccess.LoadByPrimaryKey(ALedgerNumber, 1, Transaction); }); ALedgerRow ledgerRow = (ALedgerRow)LedgerTable.Rows[0]; AAccountingPeriodRow accPeriodRow = (AAccountingPeriodRow)accPeriodTable.Rows[0]; DateTime CurrentYearEnd = TAccountingPeriodsWebConnector.GetPeriodEndDate(ALedgerNumber, ledgerRow.CurrentFinancialYear, 0, ledgerRow.NumberOfAccountingPeriods); budgetYear = ABudgetRevisionYear + CurrentYearEnd.Year - ledgerRow.CurrentFinancialYear; if (budgetYear == accPeriodRow.PeriodStartDate.Year) { return("This"); } else { return("Next"); } }
/// <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> /// export all posted invoices of other suppliers in this year /// </summary> public static void Export(string AOutputPath, char ACSVSeparator, string ANewLine, Int32 ALedgerNumber, Int32 AFinancialYear, string ACostCentres) { string filename = Path.GetFullPath(Path.Combine(AOutputPath, "accountspayable.csv")); Console.WriteLine("Writing file: " + filename); StringBuilder sb = new StringBuilder(); TDBTransaction Transaction = DBAccess.GDBAccessObj.BeginTransaction(IsolationLevel.ReadCommitted); // get all posted or paid ap_documents by their date issued string sql = String.Format( "SELECT * FROM PUB_{0} " + "WHERE {1} = {2} AND " + "({3} = '{4}' OR {3} = '{5}' OR {3} = '{6}') AND " + "{7} >= ? AND {7} <= ?", AApDocumentTable.GetTableDBName(), AApDocumentTable.GetLedgerNumberDBName(), ALedgerNumber, AApDocumentTable.GetDocumentStatusDBName(), MFinanceConstants.AP_DOCUMENT_POSTED, MFinanceConstants.AP_DOCUMENT_PARTIALLY_PAID, MFinanceConstants.AP_DOCUMENT_PAID, AApDocumentTable.GetDateIssuedDBName()); List <OdbcParameter> Parameters = new List <OdbcParameter>(); OdbcParameter param = new OdbcParameter("startdate", OdbcType.DateTime); param.Value = TAccountingPeriodsWebConnector.GetPeriodStartDate(ALedgerNumber, AFinancialYear, 0, 1); Parameters.Add(param); param = new OdbcParameter("enddate", OdbcType.DateTime); param.Value = TAccountingPeriodsWebConnector.GetPeriodEndDate(ALedgerNumber, AFinancialYear, 0, 12); Parameters.Add(param); AApDocumentTable apDocuments = new AApDocumentTable(); DBAccess.GDBAccessObj.SelectDT(apDocuments, sql, Transaction, Parameters.ToArray(), 0, 0); // get all ap details sql = String.Format( "SELECT Detail.* FROM PUB_{0} AS Doc, PUB_{8} AS Detail " + "WHERE Doc.{1} = {2} AND " + "({3} = '{4}' OR {3} = '{5}' OR {3} = '{6}') AND " + "{7} >= ? AND {7} <= ? AND " + "Detail.{1} = Doc.{1} AND Detail.{9} = Doc.{9} AND " + "Detail.{10} IN ({11})", AApDocumentTable.GetTableDBName(), AApDocumentTable.GetLedgerNumberDBName(), ALedgerNumber, AApDocumentTable.GetDocumentStatusDBName(), MFinanceConstants.AP_DOCUMENT_POSTED, MFinanceConstants.AP_DOCUMENT_PARTIALLY_PAID, MFinanceConstants.AP_DOCUMENT_PAID, AApDocumentTable.GetDateIssuedDBName(), AApDocumentDetailTable.GetTableDBName(), AApDocumentTable.GetApDocumentIdDBName(), AApDocumentDetailTable.GetCostCentreCodeDBName(), "'" + ACostCentres.Replace(",", "','") + "'"); AApDocumentDetailTable apDetails = new AApDocumentDetailTable(); DBAccess.GDBAccessObj.SelectDT(apDetails, sql, Transaction, Parameters.ToArray(), 0, 0); apDetails.DefaultView.Sort = AApDocumentDetailTable.GetApDocumentIdDBName(); // get all ap payments sql = String.Format( "SELECT DP.{0}, DP.{1}, P.{2} AS {3}, DP.{7}, DP.{15} FROM PUB_{4} AS Doc, PUB_{5} AS DP, PUB_{6} AS P " + "WHERE Doc.{7} = {8} AND " + "({9} = '{10}' OR {9} = '{11}' OR {9} = '{12}') AND " + "{13} >= ? AND {13} <= ? AND " + "DP.{7} = Doc.{7} AND DP.{14} = Doc.{14} AND " + "P.{7} = Doc.{7} AND P.{15} = DP.{15}", AApDocumentPaymentTable.GetApDocumentIdDBName(), AApDocumentPaymentTable.GetAmountDBName(), AApPaymentTable.GetPaymentDateDBName(), AApDocumentPaymentTable.GetDateCreatedDBName(), AApDocumentTable.GetTableDBName(), AApDocumentPaymentTable.GetTableDBName(), AApPaymentTable.GetTableDBName(), AApDocumentTable.GetLedgerNumberDBName(), ALedgerNumber, AApDocumentTable.GetDocumentStatusDBName(), MFinanceConstants.AP_DOCUMENT_POSTED, MFinanceConstants.AP_DOCUMENT_PARTIALLY_PAID, MFinanceConstants.AP_DOCUMENT_PAID, AApDocumentTable.GetDateIssuedDBName(), AApDocumentTable.GetApDocumentIdDBName(), AApPaymentTable.GetPaymentNumberDBName()); AApDocumentPaymentTable apPayments = new AApDocumentPaymentTable(); DBAccess.GDBAccessObj.SelectDT(apPayments, sql, Transaction, Parameters.ToArray(), 0, 0); apPayments.DefaultView.Sort = AApDocumentPaymentTable.GetApDocumentIdDBName(); // get the analysis attributes for the taxes sql = String.Format( "SELECT Attrib.* FROM PUB_{0} AS Doc, PUB_{8} AS Attrib " + "WHERE Doc.{1} = {2} AND " + "({3} = '{4}' OR {3} = '{5}' OR {3} = '{6}') AND " + "{7} >= ? AND {7} <= ? AND " + "Attrib.{1} = Doc.{1} AND Attrib.{9} = Doc.{9}", AApDocumentTable.GetTableDBName(), AApDocumentTable.GetLedgerNumberDBName(), ALedgerNumber, AApDocumentTable.GetDocumentStatusDBName(), MFinanceConstants.AP_DOCUMENT_POSTED, MFinanceConstants.AP_DOCUMENT_PARTIALLY_PAID, MFinanceConstants.AP_DOCUMENT_PAID, AApDocumentTable.GetDateIssuedDBName(), AApAnalAttribTable.GetTableDBName(), AApDocumentTable.GetApDocumentIdDBName()); AApAnalAttribTable apAttrib = new AApAnalAttribTable(); DBAccess.GDBAccessObj.SelectDT(apAttrib, sql, Transaction, Parameters.ToArray(), 0, 0); apAttrib.DefaultView.Sort = AApAnalAttribTable.GetApDocumentIdDBName() + "," + AApAnalAttribTable.GetDetailNumberDBName(); // get the supplier name sql = String.Format( "SELECT DISTINCT p.* FROM PUB_{0} AS Doc, PUB_{8} AS p " + "WHERE Doc.{1} = {2} AND " + "({3} = '{4}' OR {3} = '{5}' OR {3} = '{6}') AND " + "{7} >= ? AND {7} <= ? AND " + "p.{9} = Doc.{10}", AApDocumentTable.GetTableDBName(), AApDocumentTable.GetLedgerNumberDBName(), ALedgerNumber, AApDocumentTable.GetDocumentStatusDBName(), MFinanceConstants.AP_DOCUMENT_POSTED, MFinanceConstants.AP_DOCUMENT_PARTIALLY_PAID, MFinanceConstants.AP_DOCUMENT_PAID, AApDocumentTable.GetDateIssuedDBName(), PPartnerTable.GetTableDBName(), PPartnerTable.GetPartnerKeyDBName(), AApDocumentTable.GetPartnerKeyDBName()); PPartnerTable suppliers = new PPartnerTable(); DBAccess.GDBAccessObj.SelectDT(suppliers, sql, Transaction, Parameters.ToArray(), 0, 0); foreach (AApDocumentRow doc in apDocuments.Rows) { PPartnerRow supplier = (PPartnerRow)suppliers.Rows.Find(doc.PartnerKey); DataRowView[] detailsRV = apDetails.DefaultView.FindRows(doc.ApDocumentId); foreach (DataRowView rv in detailsRV) { AApDocumentDetailRow detail = (AApDocumentDetailRow)rv.Row; if (doc.CreditNoteFlag) { detail.Amount *= -1.0m; } DataRowView[] payments = apPayments.DefaultView.FindRows(doc.ApDocumentId); string DatePaid = string.Empty; if (payments.Length > 1) { DatePaid = "Several Payments: "; foreach (DataRowView rvPayment in payments) { AApDocumentPaymentRow payment = ((AApDocumentPaymentRow)rvPayment.Row); DatePaid += payment.DateCreated.Value.ToString("yyyyMMdd") + " "; DatePaid += payment.PaymentNumber.ToString() + "; "; } // for gdpdu, only write dates to this column DatePaid = string.Empty; } else if (payments.Length == 1) { AApDocumentPaymentRow payment = ((AApDocumentPaymentRow)payments[0].Row); DatePaid = payment.DateCreated.Value.ToString("yyyyMMdd"); } sb.Append(StringHelper.StrMerge( new string[] { doc.ApNumber.ToString(), detail.DetailNumber.ToString(), doc.DateIssued.ToString("yyyyMMdd"), DatePaid, doc.PartnerKey.ToString(), supplier.PartnerShortName, detail.CostCentreCode, detail.AccountCode, String.Format("{0:N}", detail.Amount), detail.Narrative }, ACSVSeparator)); sb.Append(ANewLine); } } DBAccess.GDBAccessObj.RollbackTransaction(); StreamWriter sw = new StreamWriter(filename, false, Encoding.GetEncoding(1252)); sw.Write(sb.ToString()); sw.Close(); }