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();
}
