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