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