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