Revision as of 03:40, 11 February 2019 by DanielY(talk | contribs)(Created page with "==References of AutoCount Accounting version 1.8 / 1.9== {{BaseReferenceAC18}} '''BCE.AutoCount.Invoicing.dll''' '''BCE.AutoCount.Invoicing.Sales.dll''' ==Get Single Sale I...")
LoadData method of InvoiceCommand requires only DocKey, this method returns DocKey from a DocNo
privatelongGetDocKeyByDocNo(stringdocNo,BCE.Data.DBSettingdbSetting){objectobj=dbSetting.ExecuteScalar("SELECT DocKey FROM IV WHERE DocNo=?",docNo);returnobj==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.
publicDataTableGetInvoiceByDateRange(DateTimefromDate,DateTimetoDate,BCE.Data.DBSettingdbSetting){BCE.AutoCount.Invoicing.Sales.Invoice.InvoiceCommandSQLcmdSql=BCE.AutoCount.Invoicing.Sales.Invoice.InvoiceCommand.Create(dbSetting)asBCE.AutoCount.Invoicing.Sales.Invoice.InvoiceCommandSQL;BCE.AutoCount.Invoicing.Sales.Invoice.InvoiceCriteriacriteria=newBCE.AutoCount.Invoicing.Sales.Invoice.InvoiceCriteria();DataTabletblInvoice=newDataTable("Invoice");//Filter Date RangeBCE.AutoCount.SearchFilter.FilterfilterDate=newBCE.AutoCount.SearchFilter.Filter("IV","LastModified");filterDate.Type=BCE.AutoCount.SearchFilter.FilterType.ByRange;filterDate.From=fromDate;filterDate.To=toDate;//Add filter to Criteriacriteria.AddFilter(filterDate);//In BasicSearch, defines the columns that are in the database tablecmdSql.BasicSearch(criteria,"DocKey, DocNo, DocDate, LastModified, DebtorCode, DebtorName, Description, CurrencyCode, CurrencyRate, FinalTotal, TotalExTax",tblInvoice,"");returntblInvoice;}
Get Sales Invoice by Debtor list and Last Modified Date Time
publicDataTableGetInvoiceByDebtorAndDateRange(List<string>debtorCodeList,DateTimefromDate,DateTimetoDate,BCE.Data.DBSettingdbSetting){BCE.AutoCount.Invoicing.Sales.Invoice.InvoiceCommandSQLcmdSql=BCE.AutoCount.Invoicing.Sales.Invoice.InvoiceCommand.Create(dbSetting)asBCE.AutoCount.Invoicing.Sales.Invoice.InvoiceCommandSQL;BCE.AutoCount.Invoicing.Sales.Invoice.InvoiceCriteriacriteria=newBCE.AutoCount.Invoicing.Sales.Invoice.InvoiceCriteria();DataTabletblInvoice=newDataTable("Invoice");//Filter Debtor Code with Multi-SelectionBCE.AutoCount.SearchFilter.FilterfilterDebtor=newBCE.AutoCount.SearchFilter.Filter("IV","DebtorCode");filterDebtor.Type=BCE.AutoCount.SearchFilter.FilterType.ByIndividual;debtorCodeList.ForEach(code=>filterDebtor.Add(code));//Filter Date RangeBCE.AutoCount.SearchFilter.FilterfilterDate=newBCE.AutoCount.SearchFilter.Filter("IV","LastModified");filterDate.Type=BCE.AutoCount.SearchFilter.FilterType.ByRange;filterDate.From=fromDate;filterDate.To=toDate;//Add filters to Criteriacriteria.AddFilter(filterDebtor);criteria.AddFilter(filterDate);//In BasicSearch, defines the columns that are in the database tablecmdSql.BasicSearch(criteria,"DocKey, DocNo, DocDate, LastModified, DebtorCode, DebtorName, Description, CurrencyCode, CurrencyRate, FinalTotal, TotalExTax",tblInvoice,"");returntblInvoice;}
Call above function
//Last Modified DateDateTimefromDate=newDateTime(2019,1,1);DateTimetoDate=DateTime.Today.Date;//Create a list of DebtorCodeList<string>debtorList=newList<string>(){"300-A001","300-C002"};DataTabletblInvoiceMaster=GetInvoiceByDebtorAndDateRange(debtorList,fromDate,toDate,dbSetting);