private void UpdateAttributeLabel(AccountsPayableTDSAApDocumentDetailRow DetailRow)
{
string strAnalAttr = "";
FMainDS.AApAnalAttrib.DefaultView.RowFilter =
String.Format("{0}={1}", AApAnalAttribTable.GetDetailNumberDBName(), DetailRow.DetailNumber);
foreach (DataRowView rv in FMainDS.AApAnalAttrib.DefaultView)
{
AApAnalAttribRow Row = (AApAnalAttribRow)rv.Row;
if (strAnalAttr.Length > 0)
{
strAnalAttr += ", ";
}
strAnalAttr += (Row.AnalysisTypeCode + "=" + Row.AnalysisAttributeValue);
}
DetailRow.AnalAttr = strAnalAttr;
}
//
// Called from cmbDetailAccountCode.SelectedValueChanged,
// I need to load the Analysis Types Pane with the required attributes for this account,
// and show any assignments already made.
void ShowAnalysisAttributesForAccount(object sender, EventArgs e)
{
//
// It's possible that my TDS doesn't even have an AnalAttrib table...
if (FMainDS.AApAnalAttrib == null)
{
FMainDS.Merge(new AApAnalAttribTable());
}
if (FPetraUtilsObject.SuppressChangeDetection || (FPreviouslySelectedDetailRow == null))
{
return;
}
//Empty the grid
FMainDS.AApAnalAttrib.DefaultView.RowFilter = "1=2";
FPSAttributesRow = null;
if (grdAnalAttributes.Columns.Count < 2) // This is initialisation but I moved it here because sometimes this is called before Show().
{
grdAnalAttributes.SpecialKeys = GridSpecialKeys.Default | GridSpecialKeys.Tab;
cmbAnalAttribValues = new SourceGrid.Cells.Editors.ComboBox(typeof(string));
cmbAnalAttribValues.Control.DropDownStyle = ComboBoxStyle.DropDownList;
cmbAnalAttribValues.EnableEdit = true;
cmbAnalAttribValues.EditableMode = EditableMode.Focus;
cmbAnalAttribValues.Control.SelectedValueChanged += new EventHandler(AnalysisAttributeValueChanged);
grdAnalAttributes.AddTextColumn("Value",
FMainDS.AApAnalAttrib.Columns[AApAnalAttribTable.GetAnalysisAttributeValueDBName()], 120,
cmbAnalAttribValues);
grdAnalAttributes.Selection.SelectionChanged += new RangeRegionChangedEventHandler(AnalysisAttributesGrid_RowSelected);
}
grdAnalAttributes.Columns[0].Width = 90; // for some unknown reason, this doesn't work.
grdAnalAttributes.Columns[1].Width = 120;
AccountsPayableTDSAApDocumentDetailRow DetailRow = GetSelectedDetailRow();
DetailRow.AccountCode = cmbDetailAccountCode.GetSelectedString();
//
// So I want to remove any attributes attached to this row that don't have the new account code...
FMainDS.AApAnalAttrib.DefaultView.RowFilter = String.Format("{0}={1} AND {2}={3} AND {4}={5} AND {6}<>'{7}'",
AApAnalAttribTable.GetLedgerNumberDBName(), DetailRow.LedgerNumber,
AApAnalAttribTable.GetApDocumentIdDBName(), DetailRow.ApDocumentId,
AApAnalAttribTable.GetDetailNumberDBName(), DetailRow.DetailNumber,
AApAnalAttribTable.GetAccountCodeDBName(), DetailRow.AccountCode);
for (Int32 RowIdx = FMainDS.AApAnalAttrib.DefaultView.Count; RowIdx > 0; RowIdx--)
{
FMainDS.AApAnalAttrib.DefaultView[RowIdx - 1].Row.Delete();
}
FMainDS.AAnalysisAttribute.DefaultView.RowFilter =
String.Format("{0}='{1}'", AAnalysisAttributeTable.GetAccountCodeDBName(), DetailRow.AccountCode);
String AccountCodeRowFilter = String.Format("{0}={1} AND {2}={3} AND {4}={5} AND {6}='{7}'",
AApAnalAttribTable.GetLedgerNumberDBName(), DetailRow.LedgerNumber,
AApAnalAttribTable.GetApDocumentIdDBName(), DetailRow.ApDocumentId,
AApAnalAttribTable.GetDetailNumberDBName(), DetailRow.DetailNumber,
AApAnalAttribTable.GetAccountCodeDBName(), DetailRow.AccountCode);
foreach (DataRowView rv in FMainDS.AAnalysisAttribute.DefaultView) // List of attributes required for this account
{
AAnalysisAttributeRow AttrRow = (AAnalysisAttributeRow)rv.Row;
FMainDS.AApAnalAttrib.DefaultView.RowFilter = AccountCodeRowFilter +
String.Format(" AND {0}='{1}'",
AApAnalAttribTable.GetAnalysisTypeCodeDBName(), AttrRow.AnalysisTypeCode);
if (FMainDS.AApAnalAttrib.DefaultView.Count == 0) // No Entry yet for this attribute. This is likely, given I just deleted everything...
{
AApAnalAttribRow AARow = FMainDS.AApAnalAttrib.NewRowTyped();
AARow.LedgerNumber = DetailRow.LedgerNumber;
AARow.ApDocumentId = DetailRow.ApDocumentId;
AARow.DetailNumber = DetailRow.DetailNumber;
AARow.AnalysisTypeCode = AttrRow.AnalysisTypeCode;
AARow.AccountCode = DetailRow.AccountCode;
FMainDS.AApAnalAttrib.Rows.Add(AARow);
}
}
FMainDS.AApAnalAttrib.DefaultView.RowFilter = AccountCodeRowFilter;
FMainDS.AApAnalAttrib.DefaultView.Sort = AApAnalAttribTable.GetAnalysisTypeCodeDBName();
grdAnalAttributes.DataSource = null;
grdAnalAttributes.DataSource = new DevAge.ComponentModel.BoundDataView(FMainDS.AApAnalAttrib.DefaultView);
UpdateAttributeLabel(DetailRow);
if (grdAnalAttributes.Rows.Count > 2)
{
grdAnalAttributes.Enabled = true;
grdAnalAttributes.SelectRowWithoutFocus(1);
AnalysisAttributesGrid_RowSelected(null, null);
}
else
{
grdAnalAttributes.Enabled = false;
}
}
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();
}
public static Boolean CanDetachTypeCodeFromAccount(Int32 ALedgerNumber, String AAccountCode, String ATypeCode, out String Message)
{
TDBTransaction ReadTrans = DBAccess.GDBAccessObj.BeginTransaction(IsolationLevel.ReadCommitted);
try
{
{
AApAnalAttribTable tbl = new AApAnalAttribTable();
AApAnalAttribRow Template = tbl.NewRowTyped(false);
Template.LedgerNumber = ALedgerNumber;
Template.AccountCode = AAccountCode;
Template.AnalysisTypeCode = ATypeCode;
tbl = AApAnalAttribAccess.LoadUsingTemplate(Template, ReadTrans);
if (tbl.Rows.Count > 0)
{
Message = String.Format(Catalog.GetString("Cannot remove {0} from {1}: "), ATypeCode, AAccountCode) +
String.Format(Catalog.GetString("Analysis Type is used in AP documents ({0} entries)."), tbl.Rows.Count);
return false;
}
}
{
ATransAnalAttribTable tbl = new ATransAnalAttribTable();
ATransAnalAttribRow Template = tbl.NewRowTyped(false);
Template.LedgerNumber = ALedgerNumber;
Template.AccountCode = AAccountCode;
Template.AnalysisTypeCode = ATypeCode;
tbl = ATransAnalAttribAccess.LoadUsingTemplate(Template, ReadTrans);
if (tbl.Rows.Count > 0)
{
Message = String.Format(Catalog.GetString("Cannot remove {0} from {1}: "), ATypeCode, AAccountCode) +
String.Format(Catalog.GetString("Analysis Type is used in Transactions ({0} entries)."), tbl.Rows.Count);
return false;
}
}
{
ARecurringTransAnalAttribTable tbl = new ARecurringTransAnalAttribTable();
ARecurringTransAnalAttribRow Template = tbl.NewRowTyped(false);
Template.LedgerNumber = ALedgerNumber;
Template.AccountCode = AAccountCode;
Template.AnalysisTypeCode = ATypeCode;
tbl = ARecurringTransAnalAttribAccess.LoadUsingTemplate(Template, ReadTrans);
if (tbl.Rows.Count > 0)
{
Message = String.Format(Catalog.GetString("Cannot remove {0} from {1}: "), ATypeCode, AAccountCode) +
String.Format(Catalog.GetString("Analysis Type is used in recurring Transactions ({0} entries)."), tbl.Rows.Count);
return false;
}
}
}
finally
{
DBAccess.GDBAccessObj.RollbackTransaction();
}
Message = "";
return true;
}
/// <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 = null;
DBAccess.GDBAccessObj.BeginAutoReadTransaction(IsolationLevel.ReadCommitted, ref Transaction,
delegate
{
// 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);
//TODO: Calendar vs Financial Date Handling - Check if this should use financial year start/end and not assume calendar
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);
}
}
});
StreamWriter sw = new StreamWriter(filename, false, Encoding.GetEncoding(1252));
sw.Write(sb.ToString());
sw.Close();
}
