Programmer:Search Sales Invoice (19): Difference between revisions
Content added Content deleted
(Created page with "==References of AutoCount Accounting version 1.8 / 1.9== {{BaseReferenceAC18}} '''BCE.AutoCount.Invoicing.dll''' '''BCE.AutoCount.Invoicing.Sales.dll''' ==Get Single Sale I...") |
No edit summary |
||
Line 46: | Line 46: | ||
//In BasicSearch, defines the columns that are in the database table |
//In BasicSearch, defines the columns that are in the database table |
||
//There are 3 pre-loaded columns |
|||
cmdSql.BasicSearch(criteria, "DocKey, DocNo, DocDate, LastModified, DebtorCode, DebtorName, Description, CurrencyCode, CurrencyRate, FinalTotal, TotalExTax", tblInvoice, ""); |
|||
//At the time of this testing, the pre-loaded columns are "DocKey", "DocNo", "Cancelled"; |
|||
// |
|||
//If non pre-loaded column has not been defined, |
|||
//System.Exception: 'Unknown Sql Exception (Number=102, Message=Incorrect syntax near ','.)' |
|||
//is prompted at run-time. |
|||
cmdSql.BasicSearch(criteria, "DocDate, LastModified, DebtorCode, DebtorName, Description, CurrencyCode, CurrencyRate, FinalTotal, TotalExTax", tblInvoice, ""); |
|||
return tblInvoice; |
return tblInvoice; |
||
Line 85: | Line 91: | ||
//In BasicSearch, defines the columns that are in the database table |
//In BasicSearch, defines the columns that are in the database table |
||
//There are 3 pre-loaded columns |
|||
cmdSql.BasicSearch(criteria, "DocKey, DocNo, DocDate, LastModified, DebtorCode, DebtorName, Description, CurrencyCode, CurrencyRate, FinalTotal, TotalExTax", tblInvoice, ""); |
|||
//At the time of this testing, the pre-loaded columns are "DocKey", "DocNo", "Cancelled"; |
|||
cmdSql.BasicSearch(criteria, "DocDate, LastModified, DebtorCode, DebtorName, Description, CurrencyCode, CurrencyRate, FinalTotal, TotalExTax", tblInvoice, ""); |
|||
return tblInvoice; |
return tblInvoice; |
||
Line 102: | Line 110: | ||
</syntaxhighlight> |
</syntaxhighlight> |
||
<br/> |
|||
==Extract Master and Detail Tables of Sales Invoice with selected document list== |
|||
===Get Full Invoice Details by List of DocNo=== |
|||
<syntaxhighlight lang="csharp"> |
|||
public DataSet GetFullInvoiceListByDocNo(List<string> docNoList, BCE.Data.DBSetting dbSetting) |
|||
{ |
|||
BCE.AutoCount.Invoicing.Sales.Invoice.InvoiceListingReport ivReport = BCE.AutoCount.Invoicing.Sales.Invoice.InvoiceListingReport.Create(dbSetting); |
|||
BCE.AutoCount.Invoicing.Sales.Invoice.InvoiceReportingCriteria criteria = new BCE.AutoCount.Invoicing.Sales.Invoice.InvoiceReportingCriteria(); |
|||
return (DataSet) ivReport.GetReportDataSource(DocNoListToDocKeyArray(docNoList, dbSetting), criteria); |
|||
} |
|||
</syntaxhighlight> |
|||
<syntaxhighlight lang="csharp"> |
|||
private long[] DocNoListToDocKeyArray(List<string> docNoList, BCE.Data.DBSetting dbSetting) |
|||
{ |
|||
return GetDocKeysByDocNo(docNoList, dbSetting).AsEnumerable() |
|||
.Select(r => r.Field<long>("DocKey")) |
|||
.ToArray(); |
|||
} |
|||
private DataTable GetDocKeysByDocNo(List<string> docNoList, BCE.Data.DBSetting dbSetting) |
|||
{ |
|||
BCE.AutoCount.Invoicing.Sales.Invoice.InvoiceCommandSQL cmdSql = |
|||
BCE.AutoCount.Invoicing.Sales.Invoice.InvoiceCommand.Create(dbSetting) as BCE.AutoCount.Invoicing.Sales.Invoice.InvoiceCommandSQL; |
|||
BCE.AutoCount.Invoicing.Sales.Invoice.InvoiceCriteria criteria = new BCE.AutoCount.Invoicing.Sales.Invoice.InvoiceCriteria(); |
|||
DataTable tblInvoice = new DataTable("Invoice"); |
|||
//Filter DocNo |
|||
BCE.AutoCount.SearchFilter.Filter filterDocNo = new BCE.AutoCount.SearchFilter.Filter("IV", "DocNo"); |
|||
filterDocNo.Type = BCE.AutoCount.SearchFilter.FilterType.ByIndividual; |
|||
docNoList.ForEach(s => filterDocNo.Add(s)); |
|||
//Add Filter |
|||
criteria.AddFilter(filterDocNo); |
|||
//In BasicSearch, DocKey will be automatically loaded, as it is one of the pre-loaded column, |
|||
//but must define at least one column that is not in pre-loaded column. |
|||
// |
|||
//At the time of this testing, the pre-loaded columns are "DocKey", "DocNo", "Cancelled"; |
|||
//If non pre-loaded column has not been defined, |
|||
//System.Exception: 'Unknown Sql Exception (Number=102, Message=Incorrect syntax near ','.)' |
|||
//is prompted at run-time. |
|||
cmdSql.BasicSearch(criteria, "DocDate", tblInvoice, ""); |
|||
return tblInvoice; |
|||
} |
|||
</syntaxhighlight> |
|||
Call above function |
|||
<syntaxhighlight lang="csharp"> |
|||
List<string> docNoList = new List<string>() { "I-000005", "I-000011", "I-000026" }; |
|||
DataSet dsFullInvoiceList = GetFullInvoiceListByDocNo(docNoList, dbSetting); |
|||
</syntaxhighlight> |
|||
*The result of above '''DataSet''' contains many tables. These tables' names are: |
|||
*#Master |
|||
*#Detail |
|||
*#PackageDetail |
|||
*#ARPaymentMaster |
|||
*#ARPaymentDetail |
|||
*#TaxSummary |
|||
*#and others. |
|||
===Get Full Invoice Details by Date Range of Last Modified document=== |
|||
<syntaxhighlight lang="csharp"> |
|||
public DataSet GetFullInvoiceListByDateRange(DateTime fromDate, DateTime toDate, BCE.Data.DBSetting dbSetting) |
|||
{ |
|||
BCE.AutoCount.Invoicing.Sales.Invoice.InvoiceListingReport ivReport = BCE.AutoCount.Invoicing.Sales.Invoice.InvoiceListingReport.Create(dbSetting); |
|||
BCE.AutoCount.Invoicing.Sales.Invoice.InvoiceReportingCriteria criteria = new BCE.AutoCount.Invoicing.Sales.Invoice.InvoiceReportingCriteria(); |
|||
return (DataSet)ivReport.GetReportDataSource(GetDocKeyArrayByDateRange(fromDate, toDate, dbSetting), criteria); |
|||
} |
|||
</syntaxhighlight> |
|||
<syntaxhighlight lang="csharp"> |
|||
private long[] GetDocKeyArrayByDateRange(DateTime fromDate, DateTime toDate, BCE.Data.DBSetting dbSetting) |
|||
{ |
|||
BCE.AutoCount.Invoicing.Sales.Invoice.InvoiceCommandSQL cmdSql = |
|||
BCE.AutoCount.Invoicing.Sales.Invoice.InvoiceCommand.Create(dbSetting) as BCE.AutoCount.Invoicing.Sales.Invoice.InvoiceCommandSQL; |
|||
BCE.AutoCount.Invoicing.Sales.Invoice.InvoiceCriteria criteria = new BCE.AutoCount.Invoicing.Sales.Invoice.InvoiceCriteria(); |
|||
DataTable tblInvoice = new DataTable("Invoice"); |
|||
//Filter Last Modified by Date Range |
|||
BCE.AutoCount.SearchFilter.Filter filterDate = new BCE.AutoCount.SearchFilter.Filter("IV", "LastModified"); |
|||
filterDate.Type = BCE.AutoCount.SearchFilter.FilterType.ByRange; |
|||
filterDate.From = fromDate; |
|||
filterDate.To = toDate; |
|||
//Add Filter |
|||
criteria.AddFilter(filterDate); |
|||
//In BasicSearch, DocKey will be automatically loaded, as it is one of the pre-loaded column, |
|||
//but must define at least one column that is not in pre-loaded column. |
|||
// |
|||
//At the time of this testing, the pre-loaded columns are "DocKey", "DocNo", "Cancelled"; |
|||
//If non pre-loaded column has not been defined, |
|||
//System.Exception: 'Unknown Sql Exception (Number=102, Message=Incorrect syntax near ','.)' |
|||
//is prompted at run-time. |
|||
cmdSql.BasicSearch(criteria, "DocDate", tblInvoice, ""); |
|||
return tblInvoice.AsEnumerable() |
|||
.Select(r => r.Field<long>("DocKey")) |
|||
.ToArray(); |
|||
} |
|||
</syntaxhighlight> |
|||
Call above function |
|||
<syntaxhighlight lang="csharp"> |
|||
DateTime fromDate = new DateTime(2018, 8, 1); |
|||
DateTime toDate = DateTime.Today.Date; |
|||
DataSet dsFullInvoiceList = GetFullInvoiceListByDateRange(fromDate, toDate, dbSetting); |
|||
</syntaxhighlight> |
|||
*The result of above '''DataSet''' contains many tables. These tables' names are: |
|||
*#Master |
|||
*#Detail |
|||
*#PackageDetail |
|||
*#ARPaymentMaster |
|||
*#ARPaymentDetail |
|||
*#TaxSummary |
|||
*#and others. |
|||
{{SeeAlsoAPIReport}} |
{{SeeAlsoAPIReport}} |
Revision as of 05:03, 11 February 2019
References of AutoCount Accounting version 1.8 / 1.9
BCE.AutoCount.dll BCE.AutoCount.CommonAccounting.dll BCE.AutoCount.MainEntry.dll BCE.Utils.dll BCE.Utils.UI.dll BCE.AutoCount.Invoicing.dll BCE.AutoCount.Invoicing.Sales.dll
Get Single Sale Invoice DataSet
public DataSet GetInvoiceDocument(string docNo, BCE.Data.DBSetting dbSetting)
{
BCE.AutoCount.Invoicing.Sales.Invoice.InvoiceCommand cmd =
BCE.AutoCount.Invoicing.Sales.Invoice.InvoiceCommand.Create(dbSetting);
return cmd.LoadData(GetDocKeyByDocNo(docNo, dbSetting));
}
LoadData method of InvoiceCommand requires only DocKey, this method returns DocKey from a DocNo
private long GetDocKeyByDocNo(string docNo, BCE.Data.DBSetting dbSetting)
{
object obj = dbSetting.ExecuteScalar("SELECT DocKey FROM IV WHERE DocNo=?", docNo);
return obj == null ? 0 : BCE.Data.Convert.ToInt64(obj);
}
Extract Master Table of Sales Invoice with filtering
Get Sales Invoice by Last Modified Date Time
- LastModified is a DateTime type that records Date and Time.
- Unlike DocDate which is also DateTime type, but it DocDate does not record the time of the document.
- The string "IV" is a sql prefix which is fixed.
public DataTable GetInvoiceByDateRange(DateTime fromDate, DateTime toDate, BCE.Data.DBSetting dbSetting)
{
BCE.AutoCount.Invoicing.Sales.Invoice.InvoiceCommandSQL cmdSql =
BCE.AutoCount.Invoicing.Sales.Invoice.InvoiceCommand.Create(dbSetting) as BCE.AutoCount.Invoicing.Sales.Invoice.InvoiceCommandSQL;
BCE.AutoCount.Invoicing.Sales.Invoice.InvoiceCriteria criteria = new BCE.AutoCount.Invoicing.Sales.Invoice.InvoiceCriteria();
DataTable tblInvoice = new DataTable("Invoice");
//Filter Date Range
BCE.AutoCount.SearchFilter.Filter filterDate = new BCE.AutoCount.SearchFilter.Filter("IV", "LastModified");
filterDate.Type = BCE.AutoCount.SearchFilter.FilterType.ByRange;
filterDate.From = fromDate;
filterDate.To = toDate;
//Add filter to Criteria
criteria.AddFilter(filterDate);
//In BasicSearch, defines the columns that are in the database table
//There are 3 pre-loaded columns
//At the time of this testing, the pre-loaded columns are "DocKey", "DocNo", "Cancelled";
//
//If non pre-loaded column has not been defined,
//System.Exception: 'Unknown Sql Exception (Number=102, Message=Incorrect syntax near ','.)'
//is prompted at run-time.
cmdSql.BasicSearch(criteria, "DocDate, LastModified, DebtorCode, DebtorName, Description, CurrencyCode, CurrencyRate, FinalTotal, TotalExTax", tblInvoice, "");
return tblInvoice;
}
Call above function
//Last Modified Date
DateTime fromDate = new DateTime(2019, 1, 1);
DateTime toDate = DateTime.Today.Date;
DataTable tblInvoiceMaster = GetInvoiceByDateRange(fromDate, toDate, dbSetting);
Get Sales Invoice by Debtor list and Last Modified Date Time
public DataTable GetInvoiceByDebtorAndDateRange(List<string> debtorCodeList, DateTime fromDate, DateTime toDate, BCE.Data.DBSetting dbSetting)
{
BCE.AutoCount.Invoicing.Sales.Invoice.InvoiceCommandSQL cmdSql =
BCE.AutoCount.Invoicing.Sales.Invoice.InvoiceCommand.Create(dbSetting) as BCE.AutoCount.Invoicing.Sales.Invoice.InvoiceCommandSQL;
BCE.AutoCount.Invoicing.Sales.Invoice.InvoiceCriteria criteria = new BCE.AutoCount.Invoicing.Sales.Invoice.InvoiceCriteria();
DataTable tblInvoice = new DataTable("Invoice");
//Filter Debtor Code with Multi-Selection
BCE.AutoCount.SearchFilter.Filter filterDebtor = new BCE.AutoCount.SearchFilter.Filter("IV", "DebtorCode");
filterDebtor.Type = BCE.AutoCount.SearchFilter.FilterType.ByIndividual;
debtorCodeList.ForEach(code => filterDebtor.Add(code));
//Filter Date Range
BCE.AutoCount.SearchFilter.Filter filterDate = new BCE.AutoCount.SearchFilter.Filter("IV", "LastModified");
filterDate.Type = BCE.AutoCount.SearchFilter.FilterType.ByRange;
filterDate.From = fromDate;
filterDate.To = toDate;
//Add filters to Criteria
criteria.AddFilter(filterDebtor);
criteria.AddFilter(filterDate);
//In BasicSearch, defines the columns that are in the database table
//There are 3 pre-loaded columns
//At the time of this testing, the pre-loaded columns are "DocKey", "DocNo", "Cancelled";
cmdSql.BasicSearch(criteria, "DocDate, LastModified, DebtorCode, DebtorName, Description, CurrencyCode, CurrencyRate, FinalTotal, TotalExTax", tblInvoice, "");
return tblInvoice;
}
Call above function
//Last Modified Date
DateTime fromDate = new DateTime(2019, 1, 1);
DateTime toDate = DateTime.Today.Date;
//Create a list of DebtorCode
List<string> debtorList = new List<string>() { "300-A001", "300-C002" };
DataTable tblInvoiceMaster = GetInvoiceByDebtorAndDateRange(debtorList, fromDate, toDate, dbSetting);
Extract Master and Detail Tables of Sales Invoice with selected document list
Get Full Invoice Details by List of DocNo
public DataSet GetFullInvoiceListByDocNo(List<string> docNoList, BCE.Data.DBSetting dbSetting)
{
BCE.AutoCount.Invoicing.Sales.Invoice.InvoiceListingReport ivReport = BCE.AutoCount.Invoicing.Sales.Invoice.InvoiceListingReport.Create(dbSetting);
BCE.AutoCount.Invoicing.Sales.Invoice.InvoiceReportingCriteria criteria = new BCE.AutoCount.Invoicing.Sales.Invoice.InvoiceReportingCriteria();
return (DataSet) ivReport.GetReportDataSource(DocNoListToDocKeyArray(docNoList, dbSetting), criteria);
}
private long[] DocNoListToDocKeyArray(List<string> docNoList, BCE.Data.DBSetting dbSetting)
{
return GetDocKeysByDocNo(docNoList, dbSetting).AsEnumerable()
.Select(r => r.Field<long>("DocKey"))
.ToArray();
}
private DataTable GetDocKeysByDocNo(List<string> docNoList, BCE.Data.DBSetting dbSetting)
{
BCE.AutoCount.Invoicing.Sales.Invoice.InvoiceCommandSQL cmdSql =
BCE.AutoCount.Invoicing.Sales.Invoice.InvoiceCommand.Create(dbSetting) as BCE.AutoCount.Invoicing.Sales.Invoice.InvoiceCommandSQL;
BCE.AutoCount.Invoicing.Sales.Invoice.InvoiceCriteria criteria = new BCE.AutoCount.Invoicing.Sales.Invoice.InvoiceCriteria();
DataTable tblInvoice = new DataTable("Invoice");
//Filter DocNo
BCE.AutoCount.SearchFilter.Filter filterDocNo = new BCE.AutoCount.SearchFilter.Filter("IV", "DocNo");
filterDocNo.Type = BCE.AutoCount.SearchFilter.FilterType.ByIndividual;
docNoList.ForEach(s => filterDocNo.Add(s));
//Add Filter
criteria.AddFilter(filterDocNo);
//In BasicSearch, DocKey will be automatically loaded, as it is one of the pre-loaded column,
//but must define at least one column that is not in pre-loaded column.
//
//At the time of this testing, the pre-loaded columns are "DocKey", "DocNo", "Cancelled";
//If non pre-loaded column has not been defined,
//System.Exception: 'Unknown Sql Exception (Number=102, Message=Incorrect syntax near ','.)'
//is prompted at run-time.
cmdSql.BasicSearch(criteria, "DocDate", tblInvoice, "");
return tblInvoice;
}
Call above function
List<string> docNoList = new List<string>() { "I-000005", "I-000011", "I-000026" };
DataSet dsFullInvoiceList = GetFullInvoiceListByDocNo(docNoList, dbSetting);
- The result of above DataSet contains many tables. These tables' names are:
- Master
- Detail
- PackageDetail
- ARPaymentMaster
- ARPaymentDetail
- TaxSummary
- and others.
Get Full Invoice Details by Date Range of Last Modified document
public DataSet GetFullInvoiceListByDateRange(DateTime fromDate, DateTime toDate, BCE.Data.DBSetting dbSetting)
{
BCE.AutoCount.Invoicing.Sales.Invoice.InvoiceListingReport ivReport = BCE.AutoCount.Invoicing.Sales.Invoice.InvoiceListingReport.Create(dbSetting);
BCE.AutoCount.Invoicing.Sales.Invoice.InvoiceReportingCriteria criteria = new BCE.AutoCount.Invoicing.Sales.Invoice.InvoiceReportingCriteria();
return (DataSet)ivReport.GetReportDataSource(GetDocKeyArrayByDateRange(fromDate, toDate, dbSetting), criteria);
}
private long[] GetDocKeyArrayByDateRange(DateTime fromDate, DateTime toDate, BCE.Data.DBSetting dbSetting)
{
BCE.AutoCount.Invoicing.Sales.Invoice.InvoiceCommandSQL cmdSql =
BCE.AutoCount.Invoicing.Sales.Invoice.InvoiceCommand.Create(dbSetting) as BCE.AutoCount.Invoicing.Sales.Invoice.InvoiceCommandSQL;
BCE.AutoCount.Invoicing.Sales.Invoice.InvoiceCriteria criteria = new BCE.AutoCount.Invoicing.Sales.Invoice.InvoiceCriteria();
DataTable tblInvoice = new DataTable("Invoice");
//Filter Last Modified by Date Range
BCE.AutoCount.SearchFilter.Filter filterDate = new BCE.AutoCount.SearchFilter.Filter("IV", "LastModified");
filterDate.Type = BCE.AutoCount.SearchFilter.FilterType.ByRange;
filterDate.From = fromDate;
filterDate.To = toDate;
//Add Filter
criteria.AddFilter(filterDate);
//In BasicSearch, DocKey will be automatically loaded, as it is one of the pre-loaded column,
//but must define at least one column that is not in pre-loaded column.
//
//At the time of this testing, the pre-loaded columns are "DocKey", "DocNo", "Cancelled";
//If non pre-loaded column has not been defined,
//System.Exception: 'Unknown Sql Exception (Number=102, Message=Incorrect syntax near ','.)'
//is prompted at run-time.
cmdSql.BasicSearch(criteria, "DocDate", tblInvoice, "");
return tblInvoice.AsEnumerable()
.Select(r => r.Field<long>("DocKey"))
.ToArray();
}
Call above function
DateTime fromDate = new DateTime(2018, 8, 1);
DateTime toDate = DateTime.Today.Date;
DataSet dsFullInvoiceList = GetFullInvoiceListByDateRange(fromDate, toDate, dbSetting);
- The result of above DataSet contains many tables. These tables' names are:
- Master
- Detail
- PackageDetail
- ARPaymentMaster
- ARPaymentDetail
- TaxSummary
- and others.
See Also Report API
Category | Reporting | |||
---|---|---|---|---|
AR |
| |||
AP |
| |||
Stock |
| |||
Sales |
| |||
Purchase |
|
Go to top
|
Resources For AutoCount Software Developers
|