/// <summary>
        /// Post all tagged documents
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        public void PostAllTagged(object sender, EventArgs e)
        {
            // This will throw an exception if insufficient permissions
            TSecurityChecks.CheckUserModulePermissions("FINANCE-2", "PostAllTagged [raised by Client Proxy for ModuleAccessManager]");

            string MsgTitle = Catalog.GetString("Document Posting");

            AccountsPayableTDS TempDS = LoadTaggedDocuments();

            List <int> PostTheseDocs = new List <int>();

            TempDS.AApDocument.DefaultView.Sort = AApDocumentDetailTable.GetApDocumentIdDBName();

            string testString = "|CANCELLED|POSTED|PARTPAID|PAID|";

            if (FRequireApprovalBeforePosting)
            {
                testString += "OPEN|";
            }

            foreach (DataRowView rv in grdInvoices.PagedDataTable.DefaultView)
            {
                if ((rv.Row["Selected"].Equals(true) && (testString.IndexOf("|" + rv.Row["DocumentStatus"].ToString()) < 0)))
                {
                    int DocId = Convert.ToInt32(rv.Row["ApDocumentId"]);

                    int RowIdx = TempDS.AApDocument.DefaultView.Find(DocId);

                    if (RowIdx >= 0)
                    {
                        AApDocumentRow DocumentRow = (AApDocumentRow)TempDS.AApDocument.DefaultView[RowIdx].Row;

                        if (TFrmAPEditDocument.ApDocumentCanPost(TempDS, DocumentRow)) // This will produce an message box if there's a problem.
                        {
                            PostTheseDocs.Add(DocId);
                        }
                    }
                }
            }

            if (PostTheseDocs.Count > 0)
            {
                string msg = String.Format(Catalog.GetString("Are you sure that you want to post the {0} tagged document(s)?"), PostTheseDocs.Count);

                if (MessageBox.Show(msg, MsgTitle, MessageBoxButtons.YesNo, MessageBoxIcon.Question,
                                    MessageBoxDefaultButton.Button2) == DialogResult.No)
                {
                    return;
                }

                if (TFrmAPEditDocument.PostApDocumentList(TempDS, FMainForm.LedgerNumber, PostTheseDocs, FMainForm))
                {
                    // TODO: print reports on successfully posted batch
                    MessageBox.Show(Catalog.GetString("The tagged documents have been posted successfully!"), MsgTitle);
                    FMainForm.IsInvoiceDataChanged = true;

                    LoadInvoices();

                    // TODO: show posting register of GL Batch?
                }
            }
            else
            {
                MessageBox.Show(Catalog.GetString("There are no tagged documents to be posted."), MsgTitle);
            }
        }
예제 #2
0
        /// <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();
        }