//*********************************************
//try using raw sql for efficiency
/// <summary>
/// Counts low stock level stocks
/// </summary>
/// <returns>json data for total low stock</returns>
public ActionResult StockLevel()
{
using (MyContext db = new MyContext())
{
var list = db.Stocks.SqlQuery("select * from dbo.Stock s, dbo.Item i where i.ID = s.ItemID and s.Qty<i.AlertQty and s.Qty>0").ToList();
return Json(list.Count(), JsonRequestBehavior.AllowGet);
}
}
/// <summary>
/// Counts the total stocks that has been ecpired
/// </summary>
/// <returns>json result for total expiry data</returns>
public ActionResult CheckExpiry()
{
using (MyContext db = new MyContext())
{
var list = db.Stocks.SqlQuery("select * from Stock s where s.ExpiryDate - GETDATE() < 75").ToList();
return Json(list.Count(), JsonRequestBehavior.AllowGet);
}
}
/// <summary>
/// Add Items to PurchaseItems
/// </summary>
/// <param name="pi"></param>
public void AddPurchaseAndPurchseItems(Purchase p)
{
using (MyContext db = new MyContext())
{
db.Purchases.Add(p);
db.SaveChanges();
}
}
/// <summary>
/// Insert or update stock based on purchase Items
/// </summary>
/// <param name="vm"></param>
public void InsertOrUpdateInventory(PurchaseItem vm)
{
using (MyContext db = new MyContext())
{
_stock = new Stock();
//Initialize new stock with vm inserted values
_stock.ItemID = vm.ItemID;
_stock.BatchNo = vm.Batch;
_stock.CostPrice = vm.CostPrice;
_stock.SellingPrice = vm.SellingPrice;
_stock.ExpiryDate = vm.Expiry;
_stock.PurchaseID = vm.PurchaseID;
//Get list of all the inserted item in Stock table
List<Stock> _checkItem = (from s in db.Stocks
where s.ItemID == vm.ItemID && s.BatchNo == vm.Batch
select s).ToList();
//count the number of exixting record on inserted item
int countStock = _checkItem.Count();
//Add new record if record is not found
if (countStock == 0)
{
//Add new item with new Initial qty
_stock.Qty = vm.Qty;
_stock.InitialQty = _stock.Qty;
db.Stocks.Add(_stock);
db.SaveChanges();
}
else
{
//to check how many times loop executes completely
int loopCount = 0;
//Check and Add or update
foreach (Stock stock in _checkItem)
{
if (stock.CostPrice == vm.CostPrice)
{
//Update qty and InitialQty
stock.Qty += vm.Qty;
stock.InitialQty += vm.Qty;
db.SaveChanges();
break;
}
loopCount++;
}
if (loopCount == _checkItem.Count())
{
//Add new record with Qty and intial Qty
_stock.InitialQty += vm.Qty;
db.Stocks.Add(_stock);
db.SaveChanges();
}
}
}
}
/// <summary>
/// Checks if invoice number already exists in database.
/// If no saves the entry
/// </summary>
/// <param name="_purchase"></param>
/// <returns>1 as success, 0 as failure</returns>
public int InsertIntoPurchase(Purchase _purchase)
{
using( MyContext db = new MyContext())
{
//Checking if invoice number already exists in database
List<Purchase> obj = (from p in db.Purchases
where p.ID == _purchase.ID
select p).ToList();
if(obj.Count()>0)
{
return 0;
}
else
{
db.Purchases.Add(_purchase);
db.SaveChanges();
return 1;
}
}
}
/*************************/
//try using raw sql for efficiency
public JsonResult CheckExpiry()
{
using(MyContext db = new MyContext())
{
/*
int count = (from s in db.Stocks
where DateTime.Now > s.ExpiryDate
select s).Count();
*/
/* var notif = (from n in db.Notifications
select n).SingleOrDefault();
notif.ToExpire = count;
db.SaveChanges();*/
var list = db.Stocks.SqlQuery("select * from dbo.Stock where GETDATE() > ExpiryDate").ToList();
return Json(list.Count(), JsonRequestBehavior.AllowGet);
}
}
public StocksFilterRepository()
{
db = new MyContext();
}
public PurchaseFilterRepository()
{
db = new MyContext();
}
public SalesEntryRepository()
{
db = new MyContext();
}
public FilterBusinessLogic()
{
db = new MyContext();
}
/// <summary>
/// Checks if invoice number already exists in database.
/// If no saves the entry
/// </summary>
/// <param name="_purchase"></param>
/// <returns>1 as success, 0 as failure</returns>
public void InsertIntoPurchase(Purchase _purchase)
{
using (MyContext db = new MyContext())
{
db.Purchases.Add(_purchase);
db.SaveChanges();
}
}