private void ValidateDataDetailsManual(AApDocumentDetailRow ARow)
{
TVerificationResultCollection VerificationResultCollection = FPetraUtilsObject.VerificationResultCollection;
TSharedFinanceValidation_AP.ValidateApDocumentDetailManual(this, ARow, ref VerificationResultCollection,
FPetraUtilsObject.ValidationControlsDict);
}
/// <summary>
/// Detail 'Amount' must be positive or 0
/// </summary>
/// <param name="AContext">Context that describes where the data validation failed.</param>
/// <param name="ARow">The <see cref="DataRow" /> which holds the the data against which the validation is run.</param>
/// <param name="AVerificationResultCollection">Will be filled with any <see cref="TVerificationResult" /> items if
/// data validation errors occur.</param>
public static void ValidateApDocumentDetailManual(object AContext, AApDocumentDetailRow ARow,
ref TVerificationResultCollection AVerificationResultCollection)
{
DataColumn ValidationColumn;
TVerificationResult VerificationResult;
// Don't validate deleted DataRows
if (ARow.RowState == DataRowState.Deleted)
{
return;
}
// 'Detail Amount' must be positive or 0
ValidationColumn = ARow.Table.Columns[AApDocumentDetailTable.ColumnAmountId];
if (true)
{
VerificationResult = TNumericalChecks.IsPositiveOrZeroDecimal(ARow.IsAmountNull() ? 0 : ARow.Amount,
String.Empty,
AContext, ValidationColumn);
// Handle addition/removal to/from TVerificationResultCollection
AVerificationResultCollection.Auto_Add_Or_AddOrRemove(AContext, VerificationResult);
}
}
/// <summary>
/// Detail 'Amount' must be positive or 0
/// </summary>
/// <param name="AContext">Context that describes where the data validation failed.</param>
/// <param name="ARow">The <see cref="DataRow" /> which holds the the data against which the validation is run.</param>
/// <param name="AVerificationResultCollection">Will be filled with any <see cref="TVerificationResult" /> items if
/// data validation errors occur.</param>
/// <param name="AValidationControlsDict">A <see cref="TValidationControlsDict" /> containing the Controls that
/// display data that is about to be validated.</param>
public static void ValidateApDocumentDetailManual(object AContext, AApDocumentDetailRow ARow,
ref TVerificationResultCollection AVerificationResultCollection, TValidationControlsDict AValidationControlsDict)
{
DataColumn ValidationColumn;
TValidationControlsData ValidationControlsData;
TVerificationResult VerificationResult;
// Don't validate deleted DataRows
if (ARow.RowState == DataRowState.Deleted)
{
return;
}
// 'Detail Amount' must be positive or 0
ValidationColumn = ARow.Table.Columns[AApDocumentDetailTable.ColumnAmountId];
if (AValidationControlsDict.TryGetValue(ValidationColumn, out ValidationControlsData))
{
VerificationResult = TNumericalChecks.IsPositiveOrZeroDecimal(ARow.Amount,
ValidationControlsData.ValidationControlLabel,
AContext, ValidationColumn, ValidationControlsData.ValidationControl);
// Handle addition/removal to/from TVerificationResultCollection
AVerificationResultCollection.Auto_Add_Or_AddOrRemove(AContext, VerificationResult, ValidationColumn);
}
}
/// <summary>
/// Detail 'Amount' must be positive or 0
/// </summary>
/// <param name="AContext">Context that describes where the data validation failed.</param>
/// <param name="ARow">The <see cref="DataRow" /> which holds the the data against which the validation is run.</param>
/// <param name="AVerificationResultCollection">Will be filled with any <see cref="TVerificationResult" /> items if
/// data validation errors occur.</param>
/// <param name="AValidationControlsDict">A <see cref="TValidationControlsDict" /> containing the Controls that
/// display data that is about to be validated.</param>
public static void ValidateApDocumentDetailManual(object AContext, AApDocumentDetailRow ARow,
ref TVerificationResultCollection AVerificationResultCollection, TValidationControlsDict AValidationControlsDict)
{
DataColumn ValidationColumn;
TValidationControlsData ValidationControlsData;
TVerificationResult VerificationResult;
// Don't validate deleted DataRows
if (ARow.RowState == DataRowState.Deleted)
{
return;
}
// 'Detail Amount' must be positive or 0
ValidationColumn = ARow.Table.Columns[AApDocumentDetailTable.ColumnAmountId];
if (AValidationControlsDict.TryGetValue(ValidationColumn, out ValidationControlsData))
{
VerificationResult = TNumericalChecks.IsPositiveOrZeroDecimal(ARow.Amount,
ValidationControlsData.ValidationControlLabel,
AContext, ValidationColumn, ValidationControlsData.ValidationControl);
// Handle addition/removal to/from TVerificationResultCollection
AVerificationResultCollection.Auto_Add_Or_AddOrRemove(AContext, VerificationResult, ValidationColumn);
}
}
private void PostDeleteManual(AApDocumentDetailRow ARowToDelete,
bool AAllowDeletion,
bool ADeletionPerformed,
string ACompletionMessage)
{
if (ADeletionPerformed)
{
EnableControls();
}
}
/// initialise some comboboxes
private void BeforeShowDetailsManual(AApDocumentDetailRow ARow)
{
if (ARow == null)
{
return;
}
grdDetails.Columns[1].Width = pnlDetailGrid.Width - 380; // It doesn't really work having these here -
grdDetails.Columns[0].Width = 90; // there's something else that overrides these settings.
grdDetails.Columns[2].Width = 200;
grdDetails.Columns[3].Width = 90;
// if this document was already posted, then we need all account and cost centre codes, because old codes might have been used
bool ActiveOnly = ("|POSTED|PARTPAID|PAID|".IndexOf("|" + FMainDS.AApDocument[0].DocumentStatus) < 0);
FPetraUtilsObject.SuppressChangeDetection = true;
TFinanceControls.InitialiseAccountList(ref cmbDetailAccountCode, ARow.LedgerNumber, true, false, ActiveOnly, false);
TFinanceControls.InitialiseCostCentreList(ref cmbDetailCostCentreCode, ARow.LedgerNumber, true, false, ActiveOnly, false);
FPetraUtilsObject.SuppressChangeDetection = false;
EnableControls();
Decimal ExchangeRateToBase = 0;
if (txtExchangeRateToBase.NumberValueDecimal.HasValue)
{
ExchangeRateToBase = txtExchangeRateToBase.NumberValueDecimal.Value;
}
if (ARow.IsAmountNull() || (ExchangeRateToBase == 0))
{
txtDetailBaseAmount.NumberValueDecimal = null;
}
else
{
decimal DetailAmount = Convert.ToDecimal(ARow.Amount);
DetailAmount /= ExchangeRateToBase;
txtDetailBaseAmount.NumberValueDecimal = DetailAmount;
}
}
/// <summary>
/// Performs checks to determine whether a deletion of the current
/// row is permissable
/// </summary>
/// <param name="ARowToDelete">the currently selected row to be deleted</param>
/// <param name="ADeletionQuestion">can be changed to a context-sensitive deletion confirmation question</param>
/// <returns>true if user is permitted and able to delete the current row</returns>
private bool PreDeleteManual(AApDocumentDetailRow ARowToDelete, ref string ADeletionQuestion)
{
GetDataFromControls(FMainDS.AApDocument[0]);
return(true);
}
private static bool DetailLineAttributesRequired(ref bool AllPresent, AccountsPayableTDS Atds, AApDocumentDetailRow DetailRow)
{
Atds.AAnalysisAttribute.DefaultView.RowFilter =
String.Format("{0}='{1}'", AAnalysisAttributeTable.GetAccountCodeDBName(), DetailRow.AccountCode);
if (Atds.AAnalysisAttribute.DefaultView.Count > 0)
{
bool IhaveAllMyAttributes = true;
//
// It's possible that my TDS doesn't even have an AnalAttrib table...
if (Atds.AApAnalAttrib == null)
{
Atds.Merge(new AApAnalAttribTable());
}
foreach (DataRowView rv in Atds.AAnalysisAttribute.DefaultView)
{
AAnalysisAttributeRow AttrRow = (AAnalysisAttributeRow)rv.Row;
Atds.AApAnalAttrib.DefaultView.RowFilter =
String.Format("{0}={1} AND {2}='{3}'",
AApAnalAttribTable.GetDetailNumberDBName(), DetailRow.DetailNumber,
AApAnalAttribTable.GetAccountCodeDBName(), AttrRow.AccountCode);
if (Atds.AApAnalAttrib.DefaultView.Count == 0)
{
IhaveAllMyAttributes = false;
break;
}
foreach (DataRowView rv2 in Atds.AApAnalAttrib.DefaultView)
{
AApAnalAttribRow AttribValueRow = (AApAnalAttribRow)rv2.Row;
if (AttribValueRow.AnalysisAttributeValue == "")
{
IhaveAllMyAttributes = false;
break;
}
// Is the referenced AttribValue active?
AFreeformAnalysisRow referencedRow = (AFreeformAnalysisRow)Atds.AFreeformAnalysis.Rows.Find(
new Object[] { AttribValueRow.LedgerNumber, AttribValueRow.AnalysisTypeCode, AttribValueRow.AnalysisAttributeValue }
);
if ((referencedRow == null) || !referencedRow.Active)
{
IhaveAllMyAttributes = false;
break;
}
}
if (IhaveAllMyAttributes == false) // because of the test above..
{
break;
}
}
AllPresent = IhaveAllMyAttributes;
return(true);
}
else
{
AllPresent = true; // This detail line is fully specified
return(false); // No attributes are required
}
}
/// <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();
}
/// initialise some comboboxes
private void BeforeShowDetailsManual(AApDocumentDetailRow ARow)
{
if (ARow == null)
{
return;
}
grdDetails.Columns[1].Width = pnlDetailGrid.Width - 380; // It doesn't really work having these here -
grdDetails.Columns[0].Width = 90; // there's something else that overrides these settings.
grdDetails.Columns[2].Width = 200;
grdDetails.Columns[3].Width = 90;
// if this document was already posted, then we need all account and cost centre codes, because old codes might have been used
bool ActiveOnly = ("|POSTED|PARTPAID|PAID|".IndexOf("|" + FMainDS.AApDocument[0].DocumentStatus) < 0);
FPetraUtilsObject.SuppressChangeDetection = true;
TFinanceControls.InitialiseAccountList(ref cmbDetailAccountCode, ARow.LedgerNumber, true, false, ActiveOnly, false);
TFinanceControls.InitialiseCostCentreList(ref cmbDetailCostCentreCode, ARow.LedgerNumber, true, false, ActiveOnly, false);
FPetraUtilsObject.SuppressChangeDetection = false;
EnableControls();
Decimal ExchangeRateToBase = 0;
if (txtExchangeRateToBase.NumberValueDecimal.HasValue)
{
ExchangeRateToBase = txtExchangeRateToBase.NumberValueDecimal.Value;
}
if (ARow.IsAmountNull() || (ExchangeRateToBase == 0))
{
txtDetailBaseAmount.NumberValueDecimal = null;
}
else
{
decimal DetailAmount = Convert.ToDecimal(ARow.Amount);
DetailAmount /= ExchangeRateToBase;
txtDetailBaseAmount.NumberValueDecimal = DetailAmount;
}
}
private void ValidateDataDetailsManual(AApDocumentDetailRow ARow)
{
TVerificationResultCollection VerificationResultCollection = FPetraUtilsObject.VerificationResultCollection;
TSharedFinanceValidation_AP.ValidateApDocumentDetailManual(this, ARow, ref VerificationResultCollection,
FPetraUtilsObject.ValidationControlsDict);
}
private void PostDeleteManual(AApDocumentDetailRow ARowToDelete,
bool AAllowDeletion,
bool ADeletionPerformed,
string ACompletionMessage)
{
if (ADeletionPerformed)
{
EnableControls();
}
}
/// <summary>
/// Performs checks to determine whether a deletion of the current
/// row is permissable
/// </summary>
/// <param name="ARowToDelete">the currently selected row to be deleted</param>
/// <param name="ADeletionQuestion">can be changed to a context-sensitive deletion confirmation question</param>
/// <returns>true if user is permitted and able to delete the current row</returns>
private bool PreDeleteManual(AApDocumentDetailRow ARowToDelete, ref string ADeletionQuestion)
{
GetDataFromControls(FMainDS.AApDocument[0]);
return true;
}
private static bool DetailLineAttributesRequired(ref bool AllPresent, AccountsPayableTDS Atds, AApDocumentDetailRow DetailRow)
{
Atds.AAnalysisAttribute.DefaultView.RowFilter =
String.Format("{0}='{1}'", AAnalysisAttributeTable.GetAccountCodeDBName(), DetailRow.AccountCode);
if (Atds.AAnalysisAttribute.DefaultView.Count > 0)
{
bool IhaveAllMyAttributes = true;
//
// It's possible that my TDS doesn't even have an AnalAttrib table...
if (Atds.AApAnalAttrib == null)
{
Atds.Merge(new AApAnalAttribTable());
}
foreach (DataRowView rv in Atds.AAnalysisAttribute.DefaultView)
{
AAnalysisAttributeRow AttrRow = (AAnalysisAttributeRow)rv.Row;
Atds.AApAnalAttrib.DefaultView.RowFilter =
String.Format("{0}={1} AND {2}='{3}'",
AApAnalAttribTable.GetDetailNumberDBName(), DetailRow.DetailNumber,
AApAnalAttribTable.GetAccountCodeDBName(), AttrRow.AccountCode);
if (Atds.AApAnalAttrib.DefaultView.Count == 0)
{
IhaveAllMyAttributes = false;
break;
}
foreach (DataRowView rv2 in Atds.AApAnalAttrib.DefaultView)
{
AApAnalAttribRow AttribValueRow = (AApAnalAttribRow)rv2.Row;
if (AttribValueRow.AnalysisAttributeValue == "")
{
IhaveAllMyAttributes = false;
break;
}
// Is the referenced AttribValue active?
AFreeformAnalysisRow referencedRow = (AFreeformAnalysisRow)Atds.AFreeformAnalysis.Rows.Find(
new Object[] { AttribValueRow.LedgerNumber, AttribValueRow.AnalysisTypeCode, AttribValueRow.AnalysisAttributeValue }
);
if ((referencedRow == null) || !referencedRow.Active)
{
IhaveAllMyAttributes = false;
break;
}
}
if (IhaveAllMyAttributes == false) // because of the test above..
{
break;
}
}
AllPresent = IhaveAllMyAttributes;
return true;
}
else
{
AllPresent = true; // This detail line is fully specified
return false; // No attributes are required
}
}
/// <summary>
/// generate the invoices from a text file that was generated with Benerator
/// </summary>
/// <param name="AInputBeneratorFile"></param>
/// <param name="AYear">eg. 2013</param>
/// <param name="ASmallNumber">boolean to keep the size of the demo database down</param>
public static void GenerateInvoices(string AInputBeneratorFile, int AYear, bool ASmallNumber)
{
MaxInvoicesPerYear = (ASmallNumber ? 25 : 200);
XmlDocument doc = TCsv2Xml.ParseCSVFile2Xml(AInputBeneratorFile, ",");
XmlNode RecordNode = doc.FirstChild.NextSibling.FirstChild;
AccountsPayableTDS MainDS = new AccountsPayableTDS();
// get a list of potential suppliers
string sqlGetSupplierPartnerKeys =
"SELECT PUB_a_ap_supplier.p_partner_key_n, PUB_a_ap_supplier.a_currency_code_c " +
"FROM PUB_p_organisation, PUB_a_ap_supplier WHERE PUB_a_ap_supplier.p_partner_key_n = PUB_p_organisation.p_partner_key_n";
DataTable SupplierKeys = DBAccess.GDBAccessObj.SelectDT(sqlGetSupplierPartnerKeys, "keys", null);
// get a list of potential expense account codes
string sqlGetExpenseAccountCodes = "SELECT a_account_code_c FROM PUB_a_account WHERE a_ledger_number_i = " +
FLedgerNumber.ToString() +
" AND a_account_type_c = 'Expense' AND a_account_active_flag_l = true AND a_posting_status_l = true";
DataTable AccountCodes = DBAccess.GDBAccessObj.SelectDT(sqlGetExpenseAccountCodes, "codes", null);
while (RecordNode != null)
{
int supplierID = Convert.ToInt32(TXMLParser.GetAttribute(RecordNode, "Supplier")) % SupplierKeys.Rows.Count;
Int64 SupplierKey = Convert.ToInt64(SupplierKeys.Rows[supplierID].ItemArray[0]);
String CurrencyCode = Convert.ToString(SupplierKeys.Rows[supplierID].ItemArray[1]);
AApDocumentRow invoiceRow = MainDS.AApDocument.NewRowTyped(true);
invoiceRow.LedgerNumber = FLedgerNumber;
invoiceRow.ApDocumentId = (Int32)TSequenceWebConnector.GetNextSequence(TSequenceNames.seq_ap_document);
invoiceRow.ApNumber = invoiceRow.ApDocumentId;
invoiceRow.DocumentCode = invoiceRow.ApDocumentId.ToString();
invoiceRow.PartnerKey = SupplierKey;
invoiceRow.Reference = "something";
invoiceRow.DateIssued = Convert.ToDateTime(TXMLParser.GetAttribute(RecordNode, "DateIssued"));
invoiceRow.DateIssued = new DateTime(AYear, invoiceRow.DateIssued.Month, invoiceRow.DateIssued.Day);
invoiceRow.DateEntered = invoiceRow.DateIssued;
invoiceRow.TotalAmount = Convert.ToDecimal(TXMLParser.GetAttribute(RecordNode, "Amount")) / 100.0m;
invoiceRow.CurrencyCode = CurrencyCode;
invoiceRow.ApAccount = "9100";
invoiceRow.DocumentStatus = MFinanceConstants.AP_DOCUMENT_APPROVED;
invoiceRow.LastDetailNumber = 1;
// TODO reasonable exchange rate for non base currency. need to check currency of supplier
invoiceRow.ExchangeRateToBase = 1.0m;
MainDS.AApDocument.Rows.Add(invoiceRow);
AApDocumentDetailRow detailRow = MainDS.AApDocumentDetail.NewRowTyped(true);
detailRow.ApDocumentId = invoiceRow.ApDocumentId;
detailRow.DetailNumber = 1;
detailRow.LedgerNumber = invoiceRow.LedgerNumber;
detailRow.CostCentreCode = (FLedgerNumber * 100).ToString("0000");
int accountID = Convert.ToInt32(TXMLParser.GetAttribute(RecordNode, "ExpenseAccount")) % AccountCodes.Rows.Count;
detailRow.AccountCode = AccountCodes.Rows[accountID].ItemArray[0].ToString();
detailRow.Amount = invoiceRow.TotalAmount;
MainDS.AApDocumentDetail.Rows.Add(detailRow);
if (MainDS.AApDocument.Rows.Count > MaxInvoicesPerYear)
{
break;
}
RecordNode = RecordNode.NextSibling;
}
AccountsPayableTDSAccess.SubmitChanges(MainDS);
}
