Programmer:Search Sales Invoice (20)
References of AutoCount Accounting version 2.0
AutoCount.Accounting.dll AutoCount.Accounting.UI.dll AutoCount.dll AutoCount.MainEntry.dll AutoCount.UI.dll AutoCount.Invoicing.dll AutoCount.Invoicing.Sales.dll
Get Single Sale Invoice DataSet
public DataSet GetInvoiceDocument(string docNo, AutoCount.Authentication.UserSession userSession)
{
AutoCount.Invoicing.Sales.Invoice.InvoiceCommand cmd =
AutoCount.Invoicing.Sales.Invoice.InvoiceCommand.Create(userSession, userSession.DBSetting);
return cmd.LoadData(GetDocKeyByDocNo(docNo, userSession.DBSetting));
}
LoadData method of InvoiceCommand requires only DocKey, this method returns DocKey from a DocNo
private long GetDocKeyByDocNo(string docNo, AutoCount.Data.DBSetting dbSetting)
{
object obj = dbSetting.ExecuteScalar("SELECT DocKey FROM IV WHERE DocNo=?", docNo);
return obj == null ? 0 : AutoCount.Converter.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, AutoCount.Authentication.UserSession userSession)
{
AutoCount.Invoicing.Sales.Invoice.InvoiceCommand cmd = AutoCount.Invoicing.Sales.Invoice.InvoiceCommand.Create(userSession, userSession.DBSetting);
AutoCount.Invoicing.Sales.Invoice.InvoiceCriteria criteria = new AutoCount.Invoicing.Sales.Invoice.InvoiceCriteria();
DataTable tblInvoice = new DataTable("Invoice");
//Create Filter Date Range
AutoCount.SearchFilter.Filter filterDate = new AutoCount.SearchFilter.Filter("IV", "LastModified");
filterDate.Type = 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, userSession);
Get Sales Invoice by Debtor list and Last Modified Date Time
public DataTable GetInvoiceByDebtorAndDateRange(List<string> debtorCodeList, DateTime fromDate, DateTime toDate, AutoCount.Authentication.UserSession userSession)
{
AutoCount.Invoicing.Sales.Invoice.InvoiceCommand cmd = AutoCount.Invoicing.Sales.Invoice.InvoiceCommand.Create(userSesssion, userSession.DBSetting);
AutoCount.Invoicing.Sales.Invoice.InvoiceCriteria criteria = new AutoCount.Invoicing.Sales.Invoice.InvoiceCriteria();
DataTable tblInvoice = new DataTable("Invoice");
//Create Filter Debtor Code with Multi-Selection
AutoCount.SearchFilter.Filter filterDebtor = new AutoCount.SearchFilter.Filter("IV", "DebtorCode");
filterDebtor.Type = AutoCount.SearchFilter.FilterType.ByIndividual;
debtorCodeList.ForEach(code => filterDebtor.Add(code));
//Create Filter Date Range
AutoCount.SearchFilter.Filter filterDate = new AutoCount.SearchFilter.Filter("IV", "LastModified");
filterDate.Type = 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, "DocKey, DocNo, 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, userSession);
Extract Master and Detail Tables of Sales Invoice with filtering
Get Full Invoice Details by List of DocNo
public DataSet GetFullInvoiceListByDocNo(List<string> docNoList, AutoCount.Authentication.UserSession userSession)
{
AutoCount.Invoicing.Sales.Invoice.InvoiceListingReport ivReport = AutoCount.Invoicing.Sales.Invoice.InvoiceListingReport.Create(userSession);
AutoCount.Invoicing.Sales.Invoice.InvoiceReportingCriteria criteria = new AutoCount.Invoicing.Sales.Invoice.InvoiceReportingCriteria();
return (DataSet)ivReport.GetReportDataSource(DocNoListToDocKeyArray(docNoList, userSession), criteria);
}
private long[] DocNoListToDocKeyArray(List<string> docNoList, AutoCount.Authentication.UserSession userSession)
{
return GetDocKeysByDocNo(docNoList, userSession).AsEnumerable()
.Select(r => r.Field<long>("DocKey"))
.ToArray();
}
private DataTable GetDocKeysByDocNo(List<string> docNoList, AutoCount.Authentication.UserSession userSession)
{
AutoCount.Invoicing.Sales.Invoice.InvoiceCommand cmd = AutoCount.Invoicing.Sales.Invoice.InvoiceCommand.Create(userSession, userSession.DBSetting);
AutoCount.Invoicing.Sales.Invoice.InvoiceCriteria criteria = new AutoCount.Invoicing.Sales.Invoice.InvoiceCriteria();
DataTable tblInvoice = new DataTable("Invoice");
//Create Filter DocNo
AutoCount.SearchFilter.Filter filterDocNo = new AutoCount.SearchFilter.Filter("IV", "DocNo");
filterDocNo.Type = 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, userSession);
Get Full Invoice Details in a Date Range of Last Modified document
public DataSet GetFullInvoiceListByDateRange(DateTime fromDate, DateTime toDate, AutoCount.Authentication.UserSession userSession)
{
AutoCount.Invoicing.Sales.Invoice.InvoiceListingReport ivReport = AutoCount.Invoicing.Sales.Invoice.InvoiceListingReport.Create(userSession);
AutoCount.Invoicing.Sales.Invoice.InvoiceReportingCriteria criteria = new AutoCount.Invoicing.Sales.Invoice.InvoiceReportingCriteria();
return (DataSet)ivReport.GetReportDataSource(GetDocKeyArrayOfDateRange(fromDate, toDate, userSession), criteria);
}
private long[] GetDocKeyArrayOfDateRange(DateTime fromDate, DateTime toDate, AutoCount.Authentication.UserSession userSession)
{
AutoCount.Invoicing.Sales.Invoice.InvoiceCommand cmd = AutoCount.Invoicing.Sales.Invoice.InvoiceCommand.Create(userSession, userSession.DBSetting);
AutoCount.Invoicing.Sales.Invoice.InvoiceCriteria criteria = new AutoCount.Invoicing.Sales.Invoice.InvoiceCriteria();
DataTable tblInvoice = new DataTable("Invoice");
//Create Filter Last Modified by Date Range
AutoCount.SearchFilter.Filter filterDate = new AutoCount.SearchFilter.Filter("IV", "LastModified");
filterDate.Type = 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, userSession);
See Also Report API
Category | Reporting | |||
---|---|---|---|---|
AR |
| |||
AP |
| |||
Stock |
| |||
Sales |
| |||
Purchase |
|
Go to top
|
Resources For AutoCount Software Developers
|