public List<Inventory_Product_OrderItem> getinventory_Product_OrderItems(uint inventory_product_ordertransaction_id)
{
List<Inventory_Product_OrderItem> returnoc = new List<Inventory_Product_OrderItem>();
try
{
using (MySqlConnection conn = new MySqlConnection(ConfigurationManager.ConnectionStrings["connectionString"].ConnectionString))
using (MySqlCommand cmd = conn.CreateCommand())
{
conn.Open();
cmd.CommandText = @"SELECT p.`_id` AS product__id, p.`name` AS product__name, p.`product_code`, p.`imageurl`, ip.`_id` AS inventory_product__id, ip.`amount` AS inventory_product__amount,
ipoi.`_id` AS inventory_product_orderitem__id, ipoi.`amount` AS inventory_product_orderitem__amount, ipoi.`description` AS inventory_product_orderitem__description
FROM inventory_product_orderitem AS ipoi
INNER JOIN inventory_product AS ip ON ip._id = ipoi.`inventory_product_id`
INNER JOIN product AS p ON p.`_id` = ip.`product_id`
WHERE inventory_product_ordertransaction_id = @inventory_product_ordertransaction_id
ORDER BY ipoi.`_id`";
cmd.Parameters.AddWithValue("@inventory_product_ordertransaction_id", inventory_product_ordertransaction_id);
using (MySqlDataReader reader = cmd.ExecuteReader())
while (reader.Read())
{
Product mProduct = new Product();
mProduct.ID = reader.GetUInt32("product__id");
mProduct.Name = reader.GetString("product__name");
mProduct.ProductCode = reader.GetString("product_code");
Inventory_Product mInventory_Product = new Inventory_Product();
mInventory_Product.Product = mProduct;
mInventory_Product.ID = reader.GetUInt32("inventory_product__id");
mInventory_Product.Amount = reader.GetDouble("inventory_product__amount");
Inventory_Product_OrderItem mInventory_Product_OrderItem = new Inventory_Product_OrderItem();
mInventory_Product_OrderItem.ID = reader.GetUInt32("inventory_product_orderitem__id");
mInventory_Product_OrderItem.Amount = reader.GetDouble("inventory_product_orderitem__amount");
mInventory_Product_OrderItem.Description = reader.GetString("inventory_product_orderitem__description");
mInventory_Product_OrderItem.Inventory_Product = mInventory_Product;
returnoc.Add(mInventory_Product_OrderItem);
}
}
}
catch (MySqlException ex)
{
logger.Trace(ex.StackTrace);
}
return returnoc;
}
public ObservableCollection<Inventory_Product> GetInventory_Product()
{
ObservableCollection<Inventory_Product> mInventory_Products = new ObservableCollection<Inventory_Product>();
try
{
using (MySqlConnection conn = new MySqlConnection(ConfigurationManager.ConnectionStrings["connectionString"].ConnectionString))
using (MySqlCommand cmd = conn.CreateCommand())
{
conn.Open();
cmd.CommandText = @"SELECT p._id, p.name, p.`product_code`, p.`imageurl`, ipp.`price`, pu.`name` AS unit, ip.`amount`, i.`_id` AS inventory_id, i.`name` AS inventory_name FROM product p
INNER JOIN inventory_product_price AS ipp ON p._id = ipp.`product_id`
INNER JOIN product_unit AS pu ON p.`product_unit_id` = pu.`_id`
INNER JOIN inventory_product AS ip ON p._id=ip.`product_id`
INNER JOIN inventory AS i ON ip.`inventory_id` = i._id
WHERE i.`_id` = @inventory_id
AND ipp.`_id` = (SELECT ipp2._id
FROM inventory_product_price AS ipp2
WHERE ipp.`product_id`=ipp2.`product_id`
ORDER BY ipp2.`date` DESC
LIMIT 1)
ORDER BY p._id";
cmd.Parameters.AddWithValue("@inventory_id", GlobalVariables.inventory_id);
using (MySqlDataReader reader = cmd.ExecuteReader())
while (reader.Read())
{
Product mProduct = new Product();
mProduct.ID = reader.GetUInt32("_id");
mProduct.Name = reader.GetString("name");
mProduct.ProductCode = reader.GetString("product_code");
//mProduct.ImageURL = reader.GetString("imageurl");
mProduct.Price = reader.GetDouble("price");
mProduct.Unit = reader.GetString("unit");
Inventory_Product mInventory_Product = new Inventory_Product();
mInventory_Product.Product = mProduct;
mInventory_Product.Amount = reader.GetDouble("amount");
mInventory_Products.Add(mInventory_Product);
}
}
}
catch (MySqlException ex)
{
logger.Trace(ex.StackTrace);
}
return mInventory_Products;
}
private void AddItem()
{
_canExecuteAddItemButton = false;
Inventory_Product mInventory_Product = new Inventory_Product();
mInventory_Product.Inventory = this.Inventory;
mInventory_Product.Product = _product;
using (MySqlConnection conn = new MySqlConnection(ConfigurationManager.ConnectionStrings["connectionString"].ConnectionString))
{
using (MySqlCommand cmd = new MySqlCommand("sp_get_product_inventory_id", conn))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("in_inventory_id", SqlDbType.Int).Value = 1;
cmd.Parameters.Add("in_product_id", SqlDbType.Int).Value = _product.ID;
cmd.Parameters.Add("out_param", SqlDbType.Int).Direction = ParameterDirection.Output;
conn.Open();
cmd.ExecuteNonQuery();
// read output value from @NewId
uint _id = Convert.ToUInt32(cmd.Parameters["out_param"].Value);
logger.Trace("INventory_product_id {0}", _id);
mInventory_Product.ID = _id;
}
}
Inventory_Product_OrderItem mInventory_Product_OrderItem = new Inventory_Product_OrderItem();
mInventory_Product_OrderItem.Inventory_Product = mInventory_Product;
mInventory_Product_OrderItem.Amount = Convert.ToDouble(_orderItemAmount);
mInventory_Product_OrderItem.Description = _orderItemDescritpion;
Inventory_Product_OrderItems.Add(mInventory_Product_OrderItem);
_addItemCommand.RaiseCanExecuteChanged();
}