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	AR Debtor Aging-[1.8, 1.9] [2.0] AR Debtor Statement-[1.8, 1.9] [2.0]
AP
Stock	Stock Balance with Costing-[1.8, 1.9] [2.0] Stock Status Report - [1.9] [2.0]
Sales	Outstanding Sales Order - [1.8, 1.9] [2.0] Outstanding Delivery Order-[1.8, 1.9] [2.0] Search/Load Sales Invoice - [1.8, 1.9] [2.0]
Purchase	Outstanding Purchase Order - [1.8, 1.9] [2.0]

Go to menu

Go to top

Resources For AutoCount Software Developers