public List <Status> List(DbCriteria criteria)
{
DbSelectStatement clause = DbMgr.CreateSelectClause();
clause
.SelectAll()
.From("Status")
.Criteria.And(criteria);
List <Status> result = new List <Status>();
DbCommand _cmd = CreateDbCommand(clause);
DbDataReader _reader = _cmd.ExecuteReader();
while (_reader.Read())
{
Status _obj = CreateDbEntity();
LoadFromReader(_obj, _reader);
result.Add(_obj);
}
_reader.Close();
_cmd.Dispose();
return(result);
}
public List <Definitions.Status> ListPurchaseStatus()
{
DbSelectStatement clause = DbMgr.CreateSelectClause();
clause
.SelectDistinct()
.SelectColumn("RecurringPurchases", "PurchaseStatusID")
.From("RecurringPurchases");
DbCriteria criteria = DbMgr.CreateCriteria();
DbCommand _cmd = CreateDbCommand(clause);
DbDataReader _reader = _cmd.ExecuteReader();
while (_reader.Read())
{
criteria.IsEqual("Status", "StatusID", GetString(_reader, ("PurchaseStatusID")), DbCriteria.ConcatMode.OR);
}
_reader.Close();
_cmd.Dispose();
return(RepositoryMgr.StatusMgr.List(criteria));
}
public virtual DataTable Table(DbCriteria criteria)
{
DbSelectStatement clause = DbMgr.CreateSelectClause();
clause
.SelectColumn("Locations", "LocationID", "ID")
.SelectColumn("Locations", "LocationName", "Name")
.SelectColumn("Locations", "IsInactive", "Inactive")
.From("Locations")
.Criteria.And(criteria);
DataTable table = new DataTable();
table.Columns.Add("ID");
table.Columns.Add("Name");
table.Columns.Add("Active");
DbCommand _cmd = CreateDbCommand(clause);
DbDataReader _reader = _cmd.ExecuteReader();
while (_reader.Read())
{
DataRow row = table.NewRow();
row["ID"] = GetInt32(_reader, "ID");
row["Name"] = GetString(_reader, "Name");
row["Active"] = (GetString(_reader, "Inactive") == "N");
table.Rows.Add(row);
}
_reader.Close();
_cmd.Dispose();
return(table);
}
public virtual DbCriteria CreateCriteria()
{
DbCriteria criteria = new DbCriteria();
criteria.DbMgr = this;
return(criteria);
}
public List <Status> ListPurchaseStatus()
{
DbCriteria criteria = DbMgr.CreateCriteria();
criteria
.IsEqual("Status", "StatusID", "O", DbCriteria.ConcatMode.OR)
.IsEqual("Status", "StatusID", "C", DbCriteria.ConcatMode.OR)
.IsEqual("Status", "StatusID", "OR", DbCriteria.ConcatMode.OR)
.IsEqual("Status", "StatusID", "DR", DbCriteria.ConcatMode.OR)
.IsEqual("Status", "StatusID", "Q", DbCriteria.ConcatMode.OR);
return(List(criteria));
}
protected virtual IList <Status> _FindSaleStatusCollection()
{
DbCriteria criteria = DbMgr.CreateCriteria();
criteria
.IsEqual("Status", "StatusID", "O", DbCriteria.ConcatMode.OR)
.IsEqual("Status", "StatusID", "C", DbCriteria.ConcatMode.OR)
.IsEqual("Status", "StatusID", "OR", DbCriteria.ConcatMode.OR)
.IsEqual("Status", "StatusID", "CR", DbCriteria.ConcatMode.OR)
.IsEqual("Status", "StatusID", "Q", DbCriteria.ConcatMode.OR);
return(List(criteria));
}
public int GetPageCount(DbCriteria criteria)
{
DbSelectStatement clause = DbMgr.CreateSelectClause();
clause
.SelectCount()
.From("Sales")
.From("Customers")
.Join("Customers", "CustomerID", "Sales", "CardRecordID")
.Criteria.And(criteria);
return(ExecuteScalarInt(clause));
}
private IList <Sale> _FindFilteredCollection(DateTime?start, DateTime?end, Customer customer, Status invoiceStatus)
{
DbCriteria criteria = CreateCriteria();
criteria
.IsGreaterEqual("Sales", "InvoiceDate", start)
.IsLessEqual("Sales", "InvoiceDate", end);
if (customer != null)
{
criteria.IsEqual("Customers", "CardIdentification", customer.CardIdentification);
}
if (invoiceStatus != null)
{
criteria.IsEqual("Sales", "InvoiceStatusID", invoiceStatus.StatusID);
}
Currencies.CurrencyManager cm = RepositoryMgr.CurrencyMgr;
bool support_multi_currency = cm.SupportMultiCurrency;
DbSelectStatement clause = DbMgr.CreateSelectClause();
clause
.SelectAll()
.From("Sales")
.OrderBy("Sales", "InvoiceDate", "ASC")
.Criteria.And(criteria);
BindingList <Sale> sales = new BindingList <Sale>();
Dictionary <int, int> currencyIds = new Dictionary <int, int>();
DbCommand _cmd = CreateDbCommand(clause);
DbDataReader _reader = _cmd.ExecuteReader();
while (_reader.Read())
{
Sale _obj = CreateDbEntity();
LoadFromReader(_obj, _reader);
sales.Add(_obj);
}
_reader.Close();
_cmd.Dispose();
return(sales);
}
private IList <Item> _FindFilteredCollection(bool sold, bool bought, bool inventoried, Dictionary <string, string> keywords)
{
DbSelectStatement clause = DbMgr.CreateSelectClause();
clause
.SelectAll()
.From("Items");
DbCriteria criteria = clause.Criteria;
if (sold)
{
criteria.IsEqual("Items", "ItemIsSold", "Y");
}
if (bought)
{
criteria.IsEqual("Items", "ItemIsBought", "Y");
}
if (inventoried)
{
criteria.IsEqual("Items", "ItemIsInventoried", "Y");
}
foreach (string key in keywords.Keys)
{
criteria.Like("Items", key, keywords[key]);
}
BindingList <Item> _grp = new BindingList <Item>();
Currencies.CurrencyManager cm = RepositoryMgr.CurrencyMgr;
DbCommand _cmd = CreateDbCommand(clause);
DbDataReader _reader = _cmd.ExecuteReader();
while (_reader.Read())
{
Item _obj = CreateDbEntity();
LoadFromReader(_obj, _reader);
_grp.Add(_obj);
}
_reader.Close();
_cmd.Dispose();
return(_grp);
}
private IList <Sale> _FindFilteredItemSaleCollection(DateTime?start, DateTime?end, Item _item)
{
DbCriteria criteria = CreateCriteria();
criteria
.IsGreaterEqual("Sales", "InvoiceDate", start)
.IsLessEqual("Sales", "InvoiceDate", end)
.IsEqual("Sales", "InvoiceType", "I");
Currencies.CurrencyManager cm = RepositoryMgr.CurrencyMgr;
bool support_multi_currency = cm.SupportMultiCurrency;
DbSelectStatement clause = DbMgr.CreateSelectClause();
clause
.SelectAll()
.From("Sales")
.OrderBy("Sales", "InvoiceDate", "ASC")
.Criteria.And(criteria);
BindingList <Sale> sales = new BindingList <Sale>();
Dictionary <int, int> currencyIds = new Dictionary <int, int>();
DbCommand _cmd = CreateDbCommand(clause);
DbDataReader _reader = _cmd.ExecuteReader();
while (_reader.Read())
{
Sale _obj = CreateDbEntity();
LoadFromReader(_obj, _reader);
if (_obj.IsSellingItem(_item))
{
sales.Add(_obj);
}
}
_reader.Close();
_cmd.Dispose();
return(sales);
}
private IList <Purchase> _FindFilteredCollection(DateTime?start, DateTime?end, Supplier supplier, Status purchaseStatus)
{
DbCriteria criteria = CreateCriteria();
criteria
.IsGreaterEqual("Purchases", "PurchaseDate", start)
.IsLessEqual("Purchases", "PurchaseDate", end);
if (supplier != null)
{
criteria.IsEqual("Suppliers", "CardIdentification", supplier.CardIdentification);
}
if (purchaseStatus != null)
{
criteria.IsEqual("Purchases", "PurchaseStatusID", purchaseStatus.StatusID);
}
DbSelectStatement clause = DbMgr.CreateSelectClause();
clause
.SelectAll()
.From("Purchases")
.OrderBy("Purchases", "PurchaseDate", "ASC")
.Criteria.And(criteria);
BindingList <Purchase> purchases = new BindingList <Purchase>();
DbCommand _cmd = CreateDbCommand(clause);
DbDataReader _reader = _cmd.ExecuteReader();
while (_reader.Read())
{
Purchase _obj = CreateDbEntity();
LoadFromReader(_obj, _reader);
purchases.Add(_obj);
}
_reader.Close();
_cmd.Dispose();
return(purchases);
}
public List <SaleRow> ListLight(DateTime start, DateTime end, Customer customer, Status invoiceStatus, int?startIndex, int?pageSize)
{
DbCriteria criteria = mAccountant.SaleMgr.CreateCriteria();
criteria
.IsGreaterEqual("Sales", "InvoiceDate", start)
.IsLessEqual("Sales", "InvoiceDate", end);
if (customer != null)
{
criteria.IsEqual("Customers", "CardIdentification", customer.CardIdentification);
}
if (invoiceStatus != null)
{
criteria.IsEqual("Sales", "InvoiceStatusID", invoiceStatus.StatusID);
}
return(mAccountant.SaleMgr.ListTableRow(criteria, startIndex, pageSize));
}
public int GetSaleCount(DateTime start, DateTime end, Customer customer, Status invoiceStatus)
{
DbCriteria criteria = mAccountant.SaleMgr.CreateCriteria();
criteria
.IsGreaterEqual("Sales", "InvoiceDate", start)
.IsLessEqual("Sales", "InvoiceDate", end);
if (customer != null)
{
criteria.IsEqual("Customers", "CardIdentification", customer.CardIdentification);
}
if (invoiceStatus != null)
{
criteria.IsEqual("Sales", "InvoiceStatusID", invoiceStatus.StatusID);
}
return(mAccountant.SaleMgr.GetPageCount(criteria));
}
public DataTable Table(DbCriteria criteria)
{
DbSelectStatement clause = DbMgr.CreateSelectClause();
clause
.SelectColumn("RecurringPurchases", "RecurringPurchaseID", "ID")
.SelectColumn("Suppliers", "Name", "SupplierName")
.SelectColumn("RecurringPurchases", "PurchaseDate", "PurchaseDate")
.From("RecurringPurchases")
.From("Suppliers")
.Join("RecurringPurchases", "CardRecordID", "Suppliers", "SupplierID")
.Criteria.And(criteria);
DataTable table = new DataTable();
table.Columns.Add("ID");
table.Columns.Add("Supplier ID");
table.Columns.Add("Purchase Date");
DbCommand _cmd = CreateDbCommand(clause);
DbDataReader _reader = _cmd.ExecuteReader();
while (_reader.Read())
{
DataRow row = table.NewRow();
row["ID"] = GetInt32(_reader, "ID");
row["Supplier ID"] = GetString(_reader, "SupplierName");
DateTime?purchase_date = GetDateTime(_reader, "PurchaseDate");
if (purchase_date.HasValue)
{
row["Purchase Date"] = purchase_date.Value.ToString("yyyy-MM-dd");
}
table.Rows.Add(row);
}
_reader.Close();
_cmd.Dispose();
return(table);
}
public List <SaleRow> ListTableRow(DbCriteria criteria, int?startIndex, int?pageSize)
{
Currencies.CurrencyManager cm = RepositoryMgr.CurrencyMgr;
bool support_multi_currency = cm.SupportMultiCurrency;
DbSelectStatement clause = DbMgr.CreateSelectClause();
clause
.SelectDistinct()
.SelectColumn("Sales", "SaleID", "ID")
.SelectColumn("Sales", "CurrencyID", "CurrencyID")
.SelectColumn("Sales", "InvoiceNumber", "InvoiceNo")
.SelectColumn("Customers", "Name", "Customer")
.SelectColumn("Sales", "InvoiceDate", "InvoiceDate")
.SelectColumn("Sales", "TotalLines", "Amount")
.SelectColumn("Sales", "InvoiceStatusID", "InvoiceStatusID")
.SelectColumn("Sales", "OutstandingBalance", "AmtDue")
.From("Sales")
.From("Customers")
.Join("Customers", "CustomerID", "Sales", "CardRecordID")
.OrderBy("Sales", "InvoiceDate", "ASC")
.Criteria.And(criteria);
int indexer = 0;
List <SaleRow> _grp = new List <SaleRow>();
DbCommand _cmd = CreateDbCommand(clause);
DbDataReader _reader = _cmd.ExecuteReader();
while (_reader.Read())
{
if (startIndex != null)
{
indexer++;
if (indexer < startIndex)
{
continue;
}
else if (indexer > startIndex + pageSize)
{
break;
}
}
SaleRow _obj = new SaleRow();
_obj.SaleID = GetInt32(_reader, "ID");
_obj.InvoiceNumber = GetString(_reader, "InvoiceNo");
_obj.Customer = GetString(_reader, "Customer");
_obj.InvoiceDate = GetDateTime(_reader, "InvoiceDate");
_obj.Amount = GetDouble(_reader, "Amount");
_obj.AmountDue = GetDouble(_reader, "AmtDue");
_obj.CurrencyID = GetInt32(_reader, "CurrencyID");
_obj.InvoiceStatusID = GetString(_reader, "InvoiceStatusID");
_grp.Add(_obj);
}
_reader.Close();
_cmd.Dispose();
Definitions.StatusManager sm = RepositoryMgr.StatusMgr;
foreach (SaleRow _obj in _grp)
{
_obj.Currency = cm.FindById(_obj.CurrencyID);
_obj.InvoiceStatus = sm.FindById(_obj.InvoiceStatusID);
}
return(_grp);
}
public DataTable Table(DbCriteria criteria)
{
Currencies.CurrencyManager cm = RepositoryMgr.CurrencyMgr;
bool support_multi_currency = cm.SupportMultiCurrency;
DbSelectStatement clause = DbMgr.CreateSelectClause();
clause
.SelectDistinct()
.SelectColumn("RecurringSales", "RecurringSaleID", "ID")
.SelectColumn("RecurringSales", "CurrencyID", "CurrencyID")
.SelectColumn("RecurringSales", "InvoiceNumber", "InvoiceNo")
.SelectColumn("Customers", "Name", "Customer")
.SelectColumn("RecurringSales", "InvoiceDate", "InvoiceDate")
.SelectColumn("RecurringSales", "TotalLines", "Amount")
.SelectColumn("RecurringSales", "OutstandingBalance", "AmtDue")
.From("RecurringSales")
.From("Customers")
.Join("Customers", "CustomerID", "RecurringSales", "CardRecordID")
.OrderBy("RecurringSales", "InvoiceDate", "ASC")
.Criteria.And(criteria);
DataTable table = new DataTable();
table.Columns.Add("ID");
table.Columns.Add("Invoice #");
table.Columns.Add("Customer");
table.Columns.Add("Invoice Date");
table.Columns.Add("Amount");
table.Columns.Add("Amt Due");
Dictionary <int, int> currencyIds = new Dictionary <int, int>();
DbCommand _cmd = CreateDbCommand(clause);
DbDataReader _reader = _cmd.ExecuteReader();
while (_reader.Read())
{
DataRow row = table.NewRow();
int? id = GetInt32(_reader, "ID");
row["ID"] = id.Value;
row["Invoice #"] = GetString(_reader, "InvoiceNo");
row["Customer"] = GetString(_reader, "Customer");
DateTime?invoice_date = GetDateTime(_reader, "InvoiceDate");
if (invoice_date.HasValue)
{
row["Invoice Date"] = invoice_date.Value.ToString("yyyy-MM-dd");
}
row["Amount"] = GetDouble(_reader, "Amount");
row["Amt Due"] = GetDouble(_reader, "AmtDue");
int?currency_id = GetInt32(_reader, "CurrencyID");
if (currency_id != null)
{
currencyIds[id.Value] = currency_id.Value;
}
table.Rows.Add(row);
}
_reader.Close();
_cmd.Dispose();
foreach (DataRow row in table.Rows)
{
double Amount = Convert.ToDouble(row["Amount"]);
double AmtDue = Convert.ToDouble(row["Amt Due"]);
int id = Convert.ToInt32(row["ID"]);
if (support_multi_currency && currencyIds.ContainsKey(id))
{
Currencies.Currency currency = cm.FindById(currencyIds[id]);
row["Amount"] = currency.Format(Amount);
row["Amt Due"] = currency.Format(AmtDue);
}
else
{
row["Amount"] = cm.Format(Amount);
row["Amt Due"] = cm.Format(AmtDue);
}
}
return(table);
}
