Programmer:Search Sales Invoice (20)

From AutoCount Resource Center

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 menu

Go to top
Resources For AutoCount Software Developers