public ActionResult EditSale(int id, int inventoryId)
{
CreatePOS pos = new CreatePOS();
string queryObject = "select Medicine.Name,Medicine.Category,Sells.Quantity from Sells join Inventory on Inventory.Id=Sells.InventoryId join Medicine on Medicine.Id=Inventory.MedicineId and Sells.Id='" + id + "' and Sells.InventoryId='" + inventoryId + "'";
var editObject = DataBaseConnection.getInstance().readData(queryObject);
editObject.Read();
pos.sale.Name = editObject.GetValue(0).ToString();
pos.sale.Category = editObject.GetValue(1).ToString();
pos.sale.Quantity = (int)editObject.GetValue(2);
PreviousSale.quantity = (int)editObject.GetValue(2);
string query = "select Sells.InventoryId,Medicine.Name,Medicine.Category,Medicine.Price,Sells.Quantity,Sells.Discount,Sells.Subtotal from Sells join Inventory on Inventory.Id=Sells.InventoryId join Medicine on Medicine.Id=Inventory.MedicineId and Sells.Id='" + id + "'";
var data = DataBaseConnection.getInstance().readData(query);
while (data.Read())
{
Sales sale = new Sales();
sale.SaleId = id;
sale.InventoryId = (int)data.GetValue(0);
sale.Name = data.GetValue(1).ToString();
sale.Category = data.GetValue(2).ToString();
sale.Price = (int)data.GetValue(3);
sale.Quantity = (int)data.GetValue(4);
sale.Discount = float.Parse(data.GetValue(5).ToString());
sale.Subtotal = float.Parse(data.GetValue(6).ToString());
pos.listSale.Add(sale);
}
ViewBag.EditMode = "EditMode";
ViewBag.msg = "Quantity has been added already";
ViewBag.vl = prevSellQuantity;
// ViewBag.CategoryList = new SelectList(MedicineCategories, "Category", "Category");
return(View("AddSales", pos));
//return View();
}
public ActionResult EditSale(int id, int inventoryId, CreatePOS POS)
{
int quantity = PreviousSale.quantity - POS.sale.Quantity;
string query = string.Format("update Inventory set Quantity=(select Quantity from Inventory where Id='{1}')+'{0}' where Id='{1}'", quantity, inventoryId);
DataBaseConnection.getInstance().executeQuery(query);
string querySell = string.Format("update Sells set Quantity='{0}',SubTotal=(select top 1 Medicine.Price from Medicine join Inventory on Inventory.MedicineId=Medicine.Id join Sells on Sells.InventoryId=Inventory.Id and Sells.InventoryId='{1}')*'{0}' where InventoryId='{1}' and Id='{2}'", POS.sale.Quantity, inventoryId, id);
DataBaseConnection.getInstance().executeQuery(querySell);
return(RedirectToAction("AddSales"));
}
public int customerBillId()
{
CreatePOS pos = new CreatePOS();
int maxBillId = 1;
try
{
string queryId = string.Format("select max(OrderId) from Bill");
maxBillId = DataBaseConnection.getInstance().executeScalar(queryId) + 1;
}
catch
{
}
return(maxBillId);
}
public ActionResult AddSales(CreatePOS orderItem)
{
ViewBag.existOrder = "";
bool isInvalid = false;
////////////////////////////get inventory id////////////////////////////////////////////
string queryinventory = string.Format("select top 1 Inventory.Id,Medicine.Price from Inventory join Medicine on Medicine.Id=Inventory.MedicineId and Medicine.Name='{0}' and Medicine.Category='{1}'", orderItem.sale.Name, orderItem.sale.Category);
var inventory = DataBaseConnection.getInstance().readData(queryinventory);
inventory.Read();
int inventoryId = (int)inventory.GetValue(0);
///////////////////////////check medicine already in sale/////////////////////////////
string existCheckQuery = string.Format("select count(Inventory.Id) from Inventory join Medicine on Medicine.Id=Inventory.MedicineId and Name='{0}' and Category='{1}' and Inventory.Id=any(select InventoryId from Sells where Id='{2}')", orderItem.sale.Name, orderItem.sale.Category, this.customerBillId());
int countOrderAlready = DataBaseConnection.getInstance().executeScalar(existCheckQuery);
//////////////////////////////check quantity exist///////////////////////
string quantityexistQuery = string.Format("select Inventory.Quantity from Inventory where Inventory.Id='{0}'", inventoryId);
int countQuantityTotal = DataBaseConnection.getInstance().executeScalar(quantityexistQuery);
if (countOrderAlready > 0)
{
ViewBag.existOrder = "Items already added in order";
isInvalid = true;
}
else if (orderItem.sale.Quantity > countQuantityTotal)
{
ViewBag.existOrder = "Quantity not exist";
isInvalid = true;
}
if (isInvalid)
{
string query = "select Sells.InventoryId,Medicine.Name,Medicine.Category,Medicine.Price,Sells.Quantity,Sells.Discount,Sells.Subtotal from Sells join Inventory on Inventory.Id=Sells.InventoryId join Medicine on Medicine.Id=Inventory.MedicineId and Sells.Id='" + this.customerBillId() + "'";
var data = DataBaseConnection.getInstance().readData(query);
while (data.Read())
{
Sales sale = new Sales();
sale.SaleId = this.customerBillId();
sale.InventoryId = (int)data.GetValue(0);
sale.Name = data.GetValue(1).ToString();
sale.Category = data.GetValue(2).ToString();
sale.Price = (int)data.GetValue(3);
sale.Quantity = (int)data.GetValue(4);
sale.Discount = float.Parse(data.GetValue(5).ToString());
sale.Subtotal = float.Parse(data.GetValue(6).ToString());
pos.listSale.Add(sale);
}
pos.sale = orderItem.sale;
return(View(pos));
}
else if (!isInvalid)
{
int price = (int)inventory.GetValue(1);
float subtotal = float.Parse((orderItem.sale.Quantity * price).ToString());
string queryInsert = string.Format("insert into Sells values('{0}','{1}','{2}','{3}','{4}')", this.customerBillId(), inventoryId, orderItem.sale.Quantity, orderItem.sale.Discount, subtotal);
DataBaseConnection.getInstance().executeQuery(queryInsert);
/////update inventory quantity////////////////
string queryUpdateInventory = string.Format("update Inventory set Quantity=(select Quantity from Inventory where Id='{1}')-'{0}' where Id='{1}'", orderItem.sale.Quantity, inventoryId);
DataBaseConnection.getInstance().executeQuery(queryUpdateInventory);
////////upadet pack ///////////
string queryUpdatePack = string.Format("update Inventory set NumberofPacks=(Quantity/(select MedicinePerPack from MedicineInventory join Inventory on Inventory.MedicineId=MedicineInventory.MedicineId and Inventory.Id='{0}')) where Id='{0}'", inventoryId);
DataBaseConnection.getInstance().executeQuery(queryUpdatePack);
// string updateInventory=string.Format("update Inventory set Quantity='{0}'",)
// string query=string.Format("insert into ")
return(RedirectToAction("AddSales"));
}
return(View());
}
