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));
}
private DbSelectStatement GetQuery_SelectAll()
{
DbSelectStatement clause = DbMgr.CreateSelectClause();
clause
.SelectDistinct()
.SelectColumn("CardTypes", "CardTypeID")
.From("CardTypes");
return(clause);
}
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);
}