Programmer:Search Sales Invoice (19)
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.InvoiceCommand cmd = BCE.AutoCount.Invoicing.Sales.Invoice.InvoiceCommand.Create(dbSetting);
BCE.AutoCount.Invoicing.Sales.Invoice.InvoiceCriteria criteria = new BCE.AutoCount.Invoicing.Sales.Invoice.InvoiceCriteria();
DataTable tblInvoice = new DataTable("Invoice");
//Create 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.
cmd.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.InvoiceCommand cmd = BCE.AutoCount.Invoicing.Sales.Invoice.InvoiceCommand.Create(dbSetting);
BCE.AutoCount.Invoicing.Sales.Invoice.InvoiceCriteria criteria = new BCE.AutoCount.Invoicing.Sales.Invoice.InvoiceCriteria();
DataTable tblInvoice = new DataTable("Invoice");
//Create 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));
//Create 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";
cmd.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 filtering
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.InvoiceCommand cmd = BCE.AutoCount.Invoicing.Sales.Invoice.InvoiceCommand.Create(dbSetting);
BCE.AutoCount.Invoicing.Sales.Invoice.InvoiceCriteria criteria = new BCE.AutoCount.Invoicing.Sales.Invoice.InvoiceCriteria();
DataTable tblInvoice = new DataTable("Invoice");
//Create 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.
cmd.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 in a 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(GetDocKeyArrayOfDateRange(fromDate, toDate, dbSetting), criteria);
}
private long[] GetDocKeyArrayOfDateRange(DateTime fromDate, DateTime toDate, BCE.Data.DBSetting dbSetting)
{
BCE.AutoCount.Invoicing.Sales.Invoice.InvoiceCommand cmd = BCE.AutoCount.Invoicing.Sales.Invoice.InvoiceCommand.Create(dbSetting);
BCE.AutoCount.Invoicing.Sales.Invoice.InvoiceCriteria criteria = new BCE.AutoCount.Invoicing.Sales.Invoice.InvoiceCriteria();
DataTable tblInvoice = new DataTable("Invoice");
//Create 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.
cmd.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
|