Programmer:Search Sales Invoice (19)

From AutoCount Resource Center

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:
    1. Master
    2. Detail
    3. PackageDetail
    4. ARPaymentMaster
    5. ARPaymentDetail
    6. TaxSummary
    7. 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:
    1. Master
    2. Detail
    3. PackageDetail
    4. ARPaymentMaster
    5. ARPaymentDetail
    6. TaxSummary
    7. and others.

See Also Report API

Category Reporting
AR
AP
Stock
Sales
Purchase

Go to menu

Go to top
Resources For AutoCount Software Developers