/// Add all selected invoices to the payment list and show that list so that the user can make the payment private void AddTaggedToPayment(object sender, EventArgs e) { List <Int32> TaggedDocuments = new List <Int32>(); AccountsPayableTDS TempDS = new AccountsPayableTDS(); foreach (DataRowView rv in FPagedDataTable.DefaultView) { if ( (rv.Row["Tagged"].Equals(true)) && (rv.Row["Currency"].ToString() == txtSupplierCurrency.Text) && ("|POSTED|PARTPAID|".IndexOf("|" + rv.Row["Status"].ToString()) >= 0) ) { Int32 DocumentId = Convert.ToInt32(rv.Row["ApDocumentId"]); TempDS.Merge(TRemote.MFinance.AP.WebConnectors.LoadAApDocument(FLedgerNumber, DocumentId)); // I've loaded this record in my DS, but I was not given a handle to it, so I need to find it! TempDS.AApDocument.DefaultView.Sort = AApDocumentTable.GetApDocumentIdDBName(); Int32 Idx = TempDS.AApDocument.DefaultView.Find(DocumentId); AApDocumentRow DocumentRow = TempDS.AApDocument[Idx]; if ("|POSTED|PARTPAID|".IndexOf("|" + DocumentRow["a_document_status_c"].ToString()) >= 0) { TaggedDocuments.Add(DocumentId); } } } if (TaggedDocuments.Count == 0) { return; } TFrmAPPayment frm = new TFrmAPPayment(this); if (frm.AddDocumentsToPayment(TempDS, FLedgerNumber, TaggedDocuments)) { frm.Show(); } }
public static void GetData(string ATablename, TSearchCriteria[] ASearchCriteria, out TTypedDataTable AResultTable, TDBTransaction AReadTransaction) { AResultTable = null; string context = string.Format("GetData {0}", SharedConstants.MODULE_ACCESS_MANAGER); // check access permissions for the current user TModuleAccessManager.CheckUserPermissionsForTable(ATablename, TTablePermissionEnum.eCanRead); // TODO: auto generate if (ATablename == AApSupplierTable.GetTableDBName()) { AResultTable = AApSupplierAccess.LoadUsingTemplate(ASearchCriteria, AReadTransaction); } else if (ATablename == AApDocumentTable.GetTableDBName()) { AResultTable = AApDocumentAccess.LoadUsingTemplate(ASearchCriteria, AReadTransaction); } else if (ATablename == ATransactionTypeTable.GetTableDBName()) { AResultTable = ATransactionTypeAccess.LoadUsingTemplate(ASearchCriteria, AReadTransaction); } else if (ATablename == ACurrencyTable.GetTableDBName()) { AResultTable = ACurrencyAccess.LoadAll(AReadTransaction); } else if (ATablename == ADailyExchangeRateTable.GetTableDBName()) { AResultTable = ADailyExchangeRateAccess.LoadAll(AReadTransaction); } else if (ATablename == ACorporateExchangeRateTable.GetTableDBName()) { AResultTable = ACorporateExchangeRateAccess.LoadAll(AReadTransaction); } else if (ATablename == ACurrencyLanguageTable.GetTableDBName()) { AResultTable = ACurrencyLanguageAccess.LoadAll(AReadTransaction); } else if (ATablename == AFeesPayableTable.GetTableDBName()) { AResultTable = AFeesPayableAccess.LoadAll(AReadTransaction); } else if (ATablename == AFeesReceivableTable.GetTableDBName()) { AResultTable = AFeesReceivableAccess.LoadAll(AReadTransaction); } else if (ATablename == AAnalysisTypeTable.GetTableDBName()) { AResultTable = AAnalysisTypeAccess.LoadUsingTemplate(ASearchCriteria, AReadTransaction); } else if (ATablename == AGiftBatchTable.GetTableDBName()) { AResultTable = AGiftBatchAccess.LoadAll(AReadTransaction); } else if (ATablename == AJournalTable.GetTableDBName()) { AResultTable = AJournalAccess.LoadAll(AReadTransaction); } else if (ATablename == ALedgerTable.GetTableDBName()) { AResultTable = ALedgerAccess.LoadAll(AReadTransaction); } else if (ATablename == MExtractMasterTable.GetTableDBName()) { if (ASearchCriteria == null) { AResultTable = MExtractMasterAccess.LoadAll(AReadTransaction); } else { AResultTable = MExtractMasterAccess.LoadUsingTemplate(ASearchCriteria, AReadTransaction); } } else if (ATablename == MExtractTable.GetTableDBName()) { // it does not make sense to load ALL extract rows for all extract masters so search criteria needs to be set if (ASearchCriteria != null) { AResultTable = MExtractAccess.LoadUsingTemplate(ASearchCriteria, AReadTransaction); } } else if (ATablename == PcAttendeeTable.GetTableDBName()) { AResultTable = PcAttendeeAccess.LoadUsingTemplate(ASearchCriteria, AReadTransaction); } else if (ATablename == PcConferenceCostTable.GetTableDBName()) { AResultTable = PcConferenceCostAccess.LoadUsingTemplate(ASearchCriteria, AReadTransaction); } else if (ATablename == PcEarlyLateTable.GetTableDBName()) { AResultTable = PcEarlyLateAccess.LoadUsingTemplate(ASearchCriteria, AReadTransaction); } else if (ATablename == PcSupplementTable.GetTableDBName()) { AResultTable = PcSupplementAccess.LoadUsingTemplate(ASearchCriteria, AReadTransaction); } else if (ATablename == PcDiscountTable.GetTableDBName()) { AResultTable = PcDiscountAccess.LoadUsingTemplate(ASearchCriteria, AReadTransaction); } else if (ATablename == PCountryTable.GetTableDBName()) { AResultTable = PCountryAccess.LoadAll(AReadTransaction); } else if (ATablename == PFormTable.GetTableDBName()) { string[] columns = TTypedDataTable.GetColumnStringList(PFormTable.TableId); StringCollection fieldList = new StringCollection(); for (int i = 0; i < columns.Length; i++) { // Do not load the template document - we don't display it and it is big! if (columns[i] != PFormTable.GetTemplateDocumentDBName()) { fieldList.Add(columns[i]); } } AResultTable = PFormAccess.LoadAll(fieldList, AReadTransaction); } else if (ATablename == PInternationalPostalTypeTable.GetTableDBName()) { AResultTable = PInternationalPostalTypeAccess.LoadAll(AReadTransaction); } else if (ATablename == PtApplicationTypeTable.GetTableDBName()) { AResultTable = PtApplicationTypeAccess.LoadAll(AReadTransaction); } else if (ATablename == PFormalityTable.GetTableDBName()) { AResultTable = PFormalityAccess.LoadAll(AReadTransaction); } else if (ATablename == PMailingTable.GetTableDBName()) { AResultTable = PMailingAccess.LoadAll(AReadTransaction); } else if (ATablename == PPartnerGiftDestinationTable.GetTableDBName()) { AResultTable = PPartnerGiftDestinationAccess.LoadUsingTemplate(ASearchCriteria, AReadTransaction); } else if (ATablename == PmDocumentTypeTable.GetTableDBName()) { AResultTable = PmDocumentTypeAccess.LoadAll(AReadTransaction); } else if (ATablename == SGroupTable.GetTableDBName()) { TSecurityChecks.CheckUserModulePermissions(SharedConstants.PETRAMODULE_SYSADMIN, context); AResultTable = SGroupAccess.LoadAll(AReadTransaction); } else if (ATablename == SSystemDefaultsTable.GetTableDBName()) { TSecurityChecks.CheckUserModulePermissions(SharedConstants.PETRAMODULE_SYSADMIN, context); AResultTable = SSystemDefaultsAccess.LoadAll(AReadTransaction); } else if (ATablename == SSystemDefaultsGuiTable.GetTableDBName()) { AResultTable = SSystemDefaultsGuiAccess.LoadAll(AReadTransaction); } else { throw new Exception("TCommonDataReader.GetData: unknown table " + ATablename); } // Accept row changes here so that the Client gets 'unmodified' rows AResultTable.AcceptChanges(); }
/// <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(); }
/// <summary> /// A payment made to a supplier needs to be reversed. /// It's done by creating and posting a set of matching "negatives" - /// In the simplest case this is a single credit note matching an invoice /// but it could be more complex. These negative documents are payed using /// a standard call to PostAPPayments. /// /// After the reversal, I'll also create and post new copies of all /// the invoices / credit notes that made up the original payment. /// </summary> /// <param name="ALedgerNumber"></param> /// <param name="APaymentNumber"></param> public void ReversePayment(Int32 ALedgerNumber, Int32 APaymentNumber) { AccountsPayableTDS TempDS = TRemote.MFinance.AP.WebConnectors.LoadAPPayment(ALedgerNumber, APaymentNumber); if (TempDS.AApPayment.Rows.Count == 0) // Invalid Payment number? { MessageBox.Show(Catalog.GetString("The referenced payment Connot be loaded."), Catalog.GetString("Error")); return; } TempDS.AApDocument.DefaultView.Sort = AApDocumentTable.GetApDocumentIdDBName(); // // First I'll check that the amounts add up: // Decimal PaidDocumentsTotal = 0.0m; foreach (AApDocumentPaymentRow PaymentRow in TempDS.AApDocumentPayment.Rows) { Int32 DocIdx = TempDS.AApDocument.DefaultView.Find(PaymentRow.ApDocumentId); AApDocumentRow DocumentRow = TempDS.AApDocument[DocIdx]; if (DocumentRow.CreditNoteFlag) { PaidDocumentsTotal -= DocumentRow.TotalAmount; } else { PaidDocumentsTotal += DocumentRow.TotalAmount; } } // // If this is a partial payment, I can't deal with that here... // if (PaidDocumentsTotal != TempDS.AApPayment[0].Amount) { String ErrorMsg = String.Format(Catalog.GetString( "This Payment cannot be reversed automatically because the total amount of the referenced documents ({0:n2} {1}) differs from the amount in the payment ({2:n2} {3})."), PaidDocumentsTotal, TempDS.AApSupplier[0].CurrencyCode, TempDS.AApPayment[0].Amount, TempDS.AApSupplier[0].CurrencyCode); MessageBox.Show(ErrorMsg, Catalog.GetString("Reverse Payment"), MessageBoxButtons.OK, MessageBoxIcon.Error); return; } // Find out if this payment was already reversed, // because if it was, perhaps the user doesn't really want to // reverse it again? this.Cursor = Cursors.WaitCursor; if (TRemote.MFinance.AP.WebConnectors.WasThisPaymentReversed(ALedgerNumber, APaymentNumber)) { this.Cursor = Cursors.Default; MessageBox.Show(Catalog.GetString("Cannot reverse Payment - there is already a matching reverse transaction."), Catalog.GetString("Reverse Payment"), MessageBoxButtons.OK, MessageBoxIcon.Error); return; } this.Cursor = Cursors.Default; // // Ask the user to confirm reversal of this payment // String PaymentMsg = Catalog.GetString("Do you want to reverse this payment?"); PaymentMsg += ("\r\n" + String.Format("Payment made {0} to {1}\r\n\r\nRelated invoices:", TDate.DateTimeToLongDateString2(TempDS.AApPayment[0].PaymentDate.Value), TempDS.PPartner[0].PartnerShortName)); foreach (AApDocumentPaymentRow PaymentRow in TempDS.AApDocumentPayment.Rows) { Int32 DocIdx = TempDS.AApDocument.DefaultView.Find(PaymentRow.ApDocumentId); AApDocumentRow DocumentRow = TempDS.AApDocument[DocIdx]; PaymentMsg += ("\r\n" + String.Format(" {2} ({3}) {0:n2} {1}", DocumentRow.TotalAmount, TempDS.AApSupplier[0].CurrencyCode, DocumentRow.DocumentCode, DocumentRow.Reference)); } PaymentMsg += ("\r\n\r\n" + String.Format("Total payment {0:n2} {1}", TempDS.AApPayment[0].Amount, TempDS.AApSupplier[0].CurrencyCode)); DialogResult YesNo = MessageBox.Show(PaymentMsg, Catalog.GetString("Reverse Payment"), MessageBoxButtons.YesNo); if (YesNo == DialogResult.No) { return; } TDlgGLEnterDateEffective dateEffectiveDialog = new TDlgGLEnterDateEffective( ALedgerNumber, Catalog.GetString("Select posting date"), Catalog.GetString("The date effective for the reversal") + ":"); if (dateEffectiveDialog.ShowDialog() != DialogResult.OK) { MessageBox.Show(Catalog.GetString("Reversal was cancelled."), Catalog.GetString("Reverse Payment"), MessageBoxButtons.OK, MessageBoxIcon.Error); return; } DateTime PostingDate = dateEffectiveDialog.SelectedDate; TVerificationResultCollection Verifications; this.Cursor = Cursors.WaitCursor; if (TRemote.MFinance.AP.WebConnectors.ReversePayment(ALedgerNumber, APaymentNumber, PostingDate, out Verifications)) { this.Cursor = Cursors.Default; // TODO: print reports on successfully posted batch MessageBox.Show(Catalog.GetString("The AP payment has been reversed."), Catalog.GetString("Reverse Payment")); TFormsMessage broadcastMessage = new TFormsMessage(TFormsMessageClassEnum.mcAPTransactionChanged); broadcastMessage.SetMessageDataAPTransaction(String.Empty); TFormsList.GFormsList.BroadcastFormMessage(broadcastMessage); } else { this.Cursor = Cursors.Default; string ErrorMessages = String.Empty; foreach (TVerificationResult verif in Verifications) { ErrorMessages += "[" + verif.ResultContext + "] " + verif.ResultTextCaption + ": " + verif.ResultText + Environment.NewLine; } System.Windows.Forms.MessageBox.Show(ErrorMessages, Catalog.GetString("Reverse Payment Failed")); } }
public static bool GetData(string ATablename, TSearchCriteria[] ASearchCriteria, out TTypedDataTable AResultTable) { // TODO: check access permissions for the current user TDBTransaction ReadTransaction = null; TTypedDataTable tempTable = null; DBAccess.GDBAccessObj.GetNewOrExistingAutoReadTransaction(IsolationLevel.RepeatableRead, TEnforceIsolationLevel.eilMinimum, ref ReadTransaction, delegate { // TODO: auto generate if (ATablename == AApSupplierTable.GetTableDBName()) { tempTable = AApSupplierAccess.LoadUsingTemplate(ASearchCriteria, ReadTransaction); } else if (ATablename == AApDocumentTable.GetTableDBName()) { tempTable = AApDocumentAccess.LoadUsingTemplate(ASearchCriteria, ReadTransaction); } else if (ATablename == ATransactionTypeTable.GetTableDBName()) { tempTable = ATransactionTypeAccess.LoadUsingTemplate(ASearchCriteria, ReadTransaction); } else if (ATablename == ACurrencyTable.GetTableDBName()) { tempTable = ACurrencyAccess.LoadAll(ReadTransaction); } else if (ATablename == ADailyExchangeRateTable.GetTableDBName()) { tempTable = ADailyExchangeRateAccess.LoadAll(ReadTransaction); } else if (ATablename == ACorporateExchangeRateTable.GetTableDBName()) { tempTable = ACorporateExchangeRateAccess.LoadAll(ReadTransaction); } else if (ATablename == ACurrencyLanguageTable.GetTableDBName()) { tempTable = ACurrencyLanguageAccess.LoadAll(ReadTransaction); } else if (ATablename == AFeesPayableTable.GetTableDBName()) { tempTable = AFeesPayableAccess.LoadAll(ReadTransaction); } else if (ATablename == AFeesReceivableTable.GetTableDBName()) { tempTable = AFeesReceivableAccess.LoadAll(ReadTransaction); } else if (ATablename == AAnalysisTypeTable.GetTableDBName()) { tempTable = AAnalysisTypeAccess.LoadUsingTemplate(ASearchCriteria, ReadTransaction); } else if (ATablename == AGiftBatchTable.GetTableDBName()) { tempTable = AGiftBatchAccess.LoadAll(ReadTransaction); } else if (ATablename == AJournalTable.GetTableDBName()) { tempTable = AJournalAccess.LoadAll(ReadTransaction); } else if (ATablename == ALedgerTable.GetTableDBName()) { tempTable = ALedgerAccess.LoadAll(ReadTransaction); } else if (ATablename == MExtractMasterTable.GetTableDBName()) { if (ASearchCriteria == null) { tempTable = MExtractMasterAccess.LoadAll(ReadTransaction); } else { tempTable = MExtractMasterAccess.LoadUsingTemplate(ASearchCriteria, ReadTransaction); } } else if (ATablename == MExtractTable.GetTableDBName()) { // it does not make sense to load ALL extract rows for all extract masters so search criteria needs to be set if (ASearchCriteria != null) { tempTable = MExtractAccess.LoadUsingTemplate(ASearchCriteria, ReadTransaction); } } else if (ATablename == PcAttendeeTable.GetTableDBName()) { tempTable = PcAttendeeAccess.LoadUsingTemplate(ASearchCriteria, ReadTransaction); } else if (ATablename == PcConferenceCostTable.GetTableDBName()) { tempTable = PcConferenceCostAccess.LoadUsingTemplate(ASearchCriteria, ReadTransaction); } else if (ATablename == PcEarlyLateTable.GetTableDBName()) { tempTable = PcEarlyLateAccess.LoadUsingTemplate(ASearchCriteria, ReadTransaction); } else if (ATablename == PcSupplementTable.GetTableDBName()) { tempTable = PcSupplementAccess.LoadUsingTemplate(ASearchCriteria, ReadTransaction); } else if (ATablename == PcDiscountTable.GetTableDBName()) { tempTable = PcDiscountAccess.LoadUsingTemplate(ASearchCriteria, ReadTransaction); } else if (ATablename == PFormTable.GetTableDBName()) { string[] columns = TTypedDataTable.GetColumnStringList(PFormTable.TableId); StringCollection fieldList = new StringCollection(); for (int i = 0; i < columns.Length; i++) { // Do not load the template document - we don't display it and it is big! if (columns[i] != PFormTable.GetTemplateDocumentDBName()) { fieldList.Add(columns[i]); } } tempTable = PFormAccess.LoadAll(fieldList, ReadTransaction); } else if (ATablename == PInternationalPostalTypeTable.GetTableDBName()) { tempTable = PInternationalPostalTypeAccess.LoadAll(ReadTransaction); } else if (ATablename == PtApplicationTypeTable.GetTableDBName()) { tempTable = PtApplicationTypeAccess.LoadAll(ReadTransaction); } else if (ATablename == PFormalityTable.GetTableDBName()) { tempTable = PFormalityAccess.LoadAll(ReadTransaction); } else if (ATablename == PMailingTable.GetTableDBName()) { tempTable = PMailingAccess.LoadAll(ReadTransaction); } else if (ATablename == PPartnerGiftDestinationTable.GetTableDBName()) { tempTable = PPartnerGiftDestinationAccess.LoadUsingTemplate(ASearchCriteria, ReadTransaction); } else if (ATablename == PmDocumentTypeTable.GetTableDBName()) { tempTable = PmDocumentTypeAccess.LoadAll(ReadTransaction); } else if (ATablename == SGroupTable.GetTableDBName()) { tempTable = SGroupAccess.LoadAll(ReadTransaction); } else { throw new Exception("TCommonDataReader.GetData: unknown table " + ATablename); } }); // Accept row changes here so that the Client gets 'unmodified' rows tempTable.AcceptChanges(); // return the table AResultTable = tempTable; return(true); }
public static bool GetData(string ATablename, TSearchCriteria[] ASearchCriteria, out TTypedDataTable AResultTable) { // TODO: check access permissions for the current user bool NewTransaction = false; TDBTransaction ReadTransaction; TTypedDataTable tempTable = null; try { ReadTransaction = DBAccess.GDBAccessObj.GetNewOrExistingTransaction(IsolationLevel.RepeatableRead, TEnforceIsolationLevel.eilMinimum, out NewTransaction); // TODO: auto generate if (ATablename == AApSupplierTable.GetTableDBName()) { tempTable = AApSupplierAccess.LoadUsingTemplate(ASearchCriteria, ReadTransaction); } else if (ATablename == AApDocumentTable.GetTableDBName()) { tempTable = AApDocumentAccess.LoadUsingTemplate(ASearchCriteria, ReadTransaction); } else if (ATablename == ATransactionTypeTable.GetTableDBName()) { tempTable = ATransactionTypeAccess.LoadUsingTemplate(ASearchCriteria, ReadTransaction); } else if (ATablename == ACurrencyTable.GetTableDBName()) { tempTable = ACurrencyAccess.LoadAll(ReadTransaction); } else if (ATablename == ADailyExchangeRateTable.GetTableDBName()) { tempTable = ADailyExchangeRateAccess.LoadAll(ReadTransaction); } else if (ATablename == ACorporateExchangeRateTable.GetTableDBName()) { tempTable = ACorporateExchangeRateAccess.LoadAll(ReadTransaction); } else if (ATablename == ACurrencyLanguageTable.GetTableDBName()) { tempTable = ACurrencyLanguageAccess.LoadAll(ReadTransaction); } else if (ATablename == AFeesPayableTable.GetTableDBName()) { tempTable = AFeesPayableAccess.LoadAll(ReadTransaction); } else if (ATablename == AFeesReceivableTable.GetTableDBName()) { tempTable = AFeesReceivableAccess.LoadAll(ReadTransaction); } else if (ATablename == AAnalysisTypeTable.GetTableDBName()) { tempTable = AAnalysisTypeAccess.LoadAll(ReadTransaction); } else if (ATablename == AGiftBatchTable.GetTableDBName()) { tempTable = AGiftBatchAccess.LoadAll(ReadTransaction); } else if (ATablename == AJournalTable.GetTableDBName()) { tempTable = AJournalAccess.LoadAll(ReadTransaction); } else if (ATablename == ALedgerTable.GetTableDBName()) { tempTable = ALedgerAccess.LoadAll(ReadTransaction); } else if (ATablename == MExtractMasterTable.GetTableDBName()) { if (ASearchCriteria == null) { tempTable = MExtractMasterAccess.LoadAll(ReadTransaction); } else { tempTable = MExtractMasterAccess.LoadUsingTemplate(ASearchCriteria, ReadTransaction); } } else if (ATablename == MExtractTable.GetTableDBName()) { // it does not make sense to load ALL extract rows for all extract masters so search criteria needs to be set if (ASearchCriteria != null) { tempTable = MExtractAccess.LoadUsingTemplate(ASearchCriteria, ReadTransaction); } } else if (ATablename == PcAttendeeTable.GetTableDBName()) { tempTable = PcAttendeeAccess.LoadUsingTemplate(ASearchCriteria, ReadTransaction); } else if (ATablename == PcConferenceCostTable.GetTableDBName()) { tempTable = PcConferenceCostAccess.LoadUsingTemplate(ASearchCriteria, ReadTransaction); } else if (ATablename == PcEarlyLateTable.GetTableDBName()) { tempTable = PcEarlyLateAccess.LoadUsingTemplate(ASearchCriteria, ReadTransaction); } else if (ATablename == PcSupplementTable.GetTableDBName()) { tempTable = PcSupplementAccess.LoadUsingTemplate(ASearchCriteria, ReadTransaction); } else if (ATablename == PcDiscountTable.GetTableDBName()) { tempTable = PcDiscountAccess.LoadUsingTemplate(ASearchCriteria, ReadTransaction); } else if (ATablename == PInternationalPostalTypeTable.GetTableDBName()) { tempTable = PInternationalPostalTypeAccess.LoadAll(ReadTransaction); } else if (ATablename == PtApplicationTypeTable.GetTableDBName()) { tempTable = PtApplicationTypeAccess.LoadAll(ReadTransaction); } else if (ATablename == PMailingTable.GetTableDBName()) { tempTable = PMailingAccess.LoadAll(ReadTransaction); } else if (ATablename == PPartnerGiftDestinationTable.GetTableDBName()) { tempTable = PPartnerGiftDestinationAccess.LoadUsingTemplate(ASearchCriteria, ReadTransaction); } else if (ATablename == PmDocumentTypeTable.GetTableDBName()) { tempTable = PmDocumentTypeAccess.LoadAll(ReadTransaction); } else if (ATablename == SGroupTable.GetTableDBName()) { tempTable = SGroupAccess.LoadAll(ReadTransaction); } else { throw new Exception("TCommonDataReader.GetData: unknown table " + ATablename); } } catch (Exception Exp) { DBAccess.GDBAccessObj.RollbackTransaction(); TLogging.Log("TCommonDataReader.GetData exception: " + Exp.ToString(), TLoggingType.ToLogfile); TLogging.Log(Exp.StackTrace, TLoggingType.ToLogfile); throw; } finally { if (NewTransaction) { DBAccess.GDBAccessObj.CommitTransaction(); TLogging.LogAtLevel(7, "TCommonDataReader.GetData: committed own transaction."); } } // Accept row changes here so that the Client gets 'unmodified' rows tempTable.AcceptChanges(); // return the table AResultTable = tempTable; 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(); }