예제 #1
0
//*********************************************
        //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);
            }
        }
예제 #2
0
 /// <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;
                }
             }
        }
예제 #6
0
        /*************************/


        //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();
 }
예제 #9
0
 public SalesEntryRepository()
 {
     db = new MyContext();
 } 
예제 #10
0
 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();
            }
        }