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);
}
