private List<Plan> GetPlans(OracleConnection connection, RecordInfo info) { OracleCommand cmd = connection.CreateCommand(); cmd.CommandText = "select count(*) from tis_plan p, tis_vyrobek v" + " where p.vyrobek_id = v.vyrobek_id order by datum_vytvoreni desc"; OracleDataReader reader = cmd.ExecuteReader(); if (reader.Read()) info.Total = reader.GetInt32(0); reader.Close(); cmd.Dispose(); List<Plan> plans = new List<Plan>(); cmd = connection.CreateCommand(); cmd.CommandText = "select * from " + "(select rownum rn, p.*, v.nazev, v.cena, v.narocnost, v.aktivni" + " from tis_plan p, tis_vyrobek v" + " where p.vyrobek_id = v.vyrobek_id order by datum_vytvoreni desc)" + " where rn >= :1 and rn < :2"; cmd.Parameters.Add(GetNumber(":1", info.Start)); cmd.Parameters.Add(GetNumber(":2", info.Start + info.Count)); reader = cmd.ExecuteReader(); while (reader.Read()) { Nullable<int> orderId = null; if (!reader.IsDBNull(3)) orderId = reader.GetInt32(3); Nullable<int> productionLineId = null; if (!reader.IsDBNull(4)) productionLineId = reader.GetInt32(4); Nullable<DateTime> dateStart = null; if (!reader.IsDBNull(6)) dateStart = reader.GetDateTime(6); Nullable<DateTime> dateEnd = null; if (!reader.IsDBNull(7)) dateEnd = reader.GetDateTime(7); Plan plan = GetPlan(reader.GetInt32(1), reader.GetInt32(2), orderId, productionLineId, reader.GetDateTime(5), dateStart, dateEnd, reader.GetInt32(8), reader.GetString(9)); plan.Product = GetProduct(reader.GetInt32(2), reader.GetString(10), reader.GetDouble(11), reader.GetDouble(12), reader.GetInt32(13)); plans.Add(plan); } reader.Close(); cmd.Dispose(); return plans; }
private List<Customer> GetCustomers(OracleConnection connection, RecordInfo info) { OracleCommand cmd = connection.CreateCommand(); cmd.CommandText = "select count(*) from tis_zakaznik"; OracleDataReader reader = cmd.ExecuteReader(); if (reader.Read()) info.Total = reader.GetInt32(0); reader.Close(); cmd.Dispose(); List<Customer> customers = new List<Customer>(); // select record from table cmd = connection.CreateCommand(); cmd.CommandText = "select * from " + "(select rownum rn, z.* from tis_zakaznik z)" + " where rn >= :1 and rn < :2"; cmd.Parameters.Add(GetNumber(":1", info.Start)); cmd.Parameters.Add(GetNumber(":2", info.Start + info.Count)); reader = cmd.ExecuteReader(); while (reader.Read()) customers.Add(GetCustomer(reader.GetInt32(1), reader.GetString(2), reader.GetString(3), reader.GetInt32(4), reader.GetString(5), reader.GetInt32(6), reader.GetString(7), reader.GetInt32(8))); reader.Close(); cmd.Dispose(); return customers; }
private List<Order> GetOrders(OracleConnection connection, Customer customer, RecordInfo info) { OracleCommand cmd = connection.CreateCommand(); cmd.CommandText = "select count(*) from tis_zakazka z where z.zakaznik_id = :1 order by z.datum_objednavky"; cmd.Parameters.Add(GetNumber(":1", customer.Id)); OracleDataReader reader = cmd.ExecuteReader(); if (reader.Read()) info.Total = reader.GetInt32(0); reader.Close(); cmd.Dispose(); List<Order> orders = new List<Order>(); // select record from table cmd = connection.CreateCommand(); cmd.CommandText = "select * from " + "(select rownum rn, z.* from tis_zakazka z where z.zakaznik_id = :1 order by z.datum_objednavky)" + " where rn >= :2 and rn < :3"; cmd.Parameters.Add(GetNumber(":1", customer.Id)); cmd.Parameters.Add(GetNumber(":2", info.Start)); cmd.Parameters.Add(GetNumber(":3", info.Start + info.Count)); reader = cmd.ExecuteReader(); while (reader.Read()) { Nullable<DateTime> dateShipment = null; if (!reader.IsDBNull(4)) dateShipment = reader.GetDateTime(4); Order order = GetOrder(reader.GetInt32(1), reader.GetInt32(2), reader.GetDateTime(3), dateShipment, reader.GetString(5)); order.Customer = customer; orders.Add(order); } reader.Close(); cmd.Dispose(); foreach (Order order in orders) order.TotalPrice = GetOrderTotalPrice(connection, order.Id); return orders; }
public List<Item> GetStore(RecordInfo info) { List<Item> items = null; using (OracleConnection connection = new OracleConnection(connectionString)) { if (connection.State != ConnectionState.Open) connection.Open(); // select records from table items = GetStoreItems(connection, info); } return items; }
public List<User> GetUsers(RecordInfo info) { List<User> users; using (OracleConnection connection = new OracleConnection(connectionString)) { if (connection.State != ConnectionState.Open) connection.Open(); // select records from table users = GetUsers(connection, info); } return users; }
public List<Product> GetProducts(RecordInfo info) { List<Product> products; using (OracleConnection connection = new OracleConnection(connectionString)) { if (connection.State != ConnectionState.Open) connection.Open(); // select records from table products = GetProducts(connection, info); } return products; }
public List<Order> GetOrders(Customer customer, RecordInfo info) { List<Order> orderList = new List<Order>(); using (OracleConnection connection = new OracleConnection(connectionString)) { if (connection.State != ConnectionState.Open) connection.Open(); // select records from table orderList = GetOrders(connection, customer, info); } return orderList; }
private List<User> GetUsers(OracleConnection connection, RecordInfo info) { OracleCommand cmd = connection.CreateCommand(); cmd.CommandText = "select count(*) from tis_uzivatel"; OracleDataReader reader = cmd.ExecuteReader(); if (reader.Read()) info.Total = reader.GetInt32(0); reader.Close(); cmd.Dispose(); List<User> users = new List<User>(); // select record from table cmd = connection.CreateCommand(); cmd.CommandText = "select * from (select rownum rn, u.* from tis_uzivatel u) where rn >= :1 and rn < :2"; cmd.Parameters.Add(GetNumber(":1", info.Start)); cmd.Parameters.Add(GetNumber(":2", info.Start + info.Count)); reader = cmd.ExecuteReader(); while (reader.Read()) { Nullable<int> customerId = null; if (!reader.IsDBNull(4)) customerId = reader.GetInt32(4); users.Add(GetUser(reader.GetString(1), reader.GetString(3), customerId)); } reader.Close(); cmd.Dispose(); foreach (User user in users) if (user.Role == Role.Zakaznik) user.Customer = GetCustomer(connection, user.CustomerId); return users; }
private List<Item> GetStoreItems(OracleConnection connection, RecordInfo info) { OracleCommand cmd = connection.CreateCommand(); cmd.CommandText = "select count(*) from " + "(select v.vyrobek_id from tis_sklad s, tis_vyrobek v" + " where s.vyrobek_id = v.vyrobek_id group by v.vyrobek_id, v.nazev)"; OracleDataReader reader = cmd.ExecuteReader(); if (reader.Read()) info.Total = reader.GetInt32(0); reader.Close(); cmd.Dispose(); List<Item> items = new List<Item>(); cmd = connection.CreateCommand(); cmd.CommandText = "select * from " + "(select rownum rn, s.* from " + "(select v.vyrobek_id, v.nazev, sum(s.pocet)" + " from tis_sklad s, tis_vyrobek v" + " where s.vyrobek_id = v.vyrobek_id group by v.vyrobek_id, v.nazev)" + "s) where rn >= :1 and rn < :2"; cmd.Parameters.Add(GetNumber(":1", info.Start)); cmd.Parameters.Add(GetNumber(":2", info.Start + info.Count)); reader = cmd.ExecuteReader(); while (reader.Read()) { items.Add(new Item { ProductId = reader.GetInt32(1), Product = GetProduct(reader.GetInt32(1), reader.GetString(2), 0, 0, 1), Count = reader.GetInt32(3) }); } reader.Close(); cmd.Dispose(); return items; }
private List<Product> GetProducts(OracleConnection connection, RecordInfo info) { OracleCommand cmd = connection.CreateCommand(); cmd.CommandText = "select count(*) from tis_vyrobek"; OracleDataReader reader = cmd.ExecuteReader(); if (reader.Read()) info.Total = reader.GetInt32(0); reader.Close(); cmd.Dispose(); List<Product> products = new List<Product>(); // select record from table cmd = connection.CreateCommand(); cmd.CommandText = "select * from " + "(select rownum rn, v.* from tis_vyrobek v)" + " where rn >= :1 and rn < :2"; cmd.Parameters.Add(GetNumber(":1", info.Start)); cmd.Parameters.Add(GetNumber(":2", info.Start + info.Count)); reader = cmd.ExecuteReader(); while (reader.Read()) products.Add(GetProduct(reader.GetInt32(1), reader.GetString(2), reader.GetDouble(3), reader.GetDouble(4), reader.GetInt32(5))); reader.Close(); cmd.Dispose(); return products; }