public virtual DataTable Table()
{
DbSelectStatement clause = DbMgr.CreateSelectClause();
clause
.SelectColumn("Gender", "GenderID")
.SelectColumn("Gender", "Description")
.From("Gender");
DataTable table = new DataTable();
table.Columns.Add("GenderID");
table.Columns.Add("Description");
Currencies.CurrencyManager cm = RepositoryMgr.CurrencyMgr;
DbCommand _cmd = CreateDbCommand(clause);
DbDataReader _reader = _cmd.ExecuteReader();
while (_reader.Read())
{
DataRow row = table.NewRow();
row["GenderID"] = GetString(_reader, "GenderID");
row["Description"] = GetString(_reader, "Description");
table.Rows.Add(row);
}
_reader.Close();
_cmd.Dispose();
return(table);
}
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);
}
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);
}
