コード例 #1
0
        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);
        }
コード例 #2
0
        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);
        }