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