Esempio n. 1
0
 private ItemCache()
 {
     lock (dummy)
     {
         //upgrade db with stock column if not done already
         StockCache.GetInstance();
         items.Clear();
         Item   item;
         double doubleVar;
         int    intVar;
         DBFunctions.GetInstance().OpenConnection();
         OleDbDataReader reader = DBFunctions.GetInstance().GetReader("Select ID, ItemName, RetailRate, ItemUnit, TaxRate, Stock from Items");
         while (reader.Read())
         {
             item      = new Item();
             item.ID   = (int)reader[0];
             item.Name = reader[1].ToString();
             item.Rate = double.Parse(reader[2].ToString());
             double.TryParse(reader[4].ToString(), out doubleVar);
             item.TaxRate = doubleVar;
             int.TryParse(reader[3].ToString(), out intVar);
             item.Unit    = new Unit();
             item.Unit.ID = intVar;
             //adding stock 24/9/2010
             double.TryParse(reader[5].ToString(), out doubleVar);
             item.Stock = doubleVar;
             items.Add(item);
         }
         foreach (Item x in items)
         {
             x.Unit = UnitCache.GetInstance().GetUnitById(x.Unit.ID);
         }
     }
 }
Esempio n. 2
0
        public void DeleteSale(int saleId)
        {
            // Update stock if the sale has to be deleted. Stock management. 24.9.2010

            OleDbCommand command = DBFunctions.GetInstance().GetCommand(
                "select Item, SaleQuantity from Sales Where ID=?");

            command.Parameters.AddWithValue("ID", saleId);
            OleDbDataReader reader = DBFunctions.GetInstance().GetReader(command);

            if (reader.Read())
            {
                Item item = ItemCache.GetInstance().GetItemById(int.Parse(reader[0].ToString()));
                //qtty of deleted sale will be added to the stock
                item.Stock = item.Stock + double.Parse(reader[1].ToString());
                StockCache.GetInstance().UpdateStock(item);
            }
            //then delete sale from the db
            command = DBFunctions.GetInstance().GetCommand(
                "Delete From Sales Where ID = ?");
            command.Parameters.AddWithValue("ID", saleId);


            if (command.ExecuteNonQuery() > 1)
            {//to confirm that only one sale got deleted for that single ID.. this error will roll back everything
                throw new Exception("Error while deleting sales. Sale ID:" + saleId);
            }
        }
Esempio n. 3
0
 public static StockCache GetInstance()
 {
     if (instance == null)
     {
         instance = new StockCache();
     }
     return(instance);
 }
Esempio n. 4
0
        public void DeleteSale(int saleID, bool updateStock)
        {
            Transaction     tx;
            OleDbCommand    command;
            OleDbDataReader reader;
            double          saleQuantity = 0.0;

            if (updateStock)
            {
                // Update stock if the sale has to be deleted. Stock management. 24.9.2010

                command = DBFunctions.GetInstance().GetCommand(
                    "select Item, SaleQuantity, TransactionId from Sales Where ID=?");
                command.Parameters.AddWithValue("ID", saleID);
                reader = DBFunctions.GetInstance().GetReader(command);
                if (reader.Read())
                {
                    Item item = ItemCache.GetInstance().GetItemById(int.Parse(reader[0].ToString()));
                    saleQuantity = double.Parse(reader[1].ToString());
                    tx           = TransactionCache.GetInstance().GetTransactionById(int.Parse(reader[2].ToString()));
                    if (tx.IsPurchase)
                    {
                        //qtty of deleted purchase will be subtracted from the stock
                        item.Stock -= saleQuantity;
                        //Update moving avg cost of items before deleting purchase item. 25.8.2014.
                        MovingAvgCache.GetInstance().GetSuppressedMovingAvgRate(GetSaleBySaleIDFromDB(saleID));
                    }
                    else
                    {
                        //qtty of deleted sale will be added to the stock
                        item.Stock += saleQuantity;
                    }
                    StockCache.GetInstance().UpdateStock(item);
                }
            }
            //then delete sale from the db
            command = DBFunctions.GetInstance().GetCommand(
                "Delete From Sales Where ID = ?");
            command.Parameters.AddWithValue("ID", saleID);


            if (command.ExecuteNonQuery() > 1)
            {//to confirm that only one sale got deleted for that single ID.. this error will roll back everything
                throw new ApplicationException("Error while deleting sales. Sale ID:" + saleID);
            }
        }
Esempio n. 5
0
        //modified to incorporate stock management on sale insert/update 24.9.2010
        public Sale SyncSale(Sale sale)
        {
            OleDbCommand command = DBFunctions.GetInstance().GetCommand(
                "Select ID, SaleQuantity From Sales Where ID=?");

            command.Parameters.AddWithValue("ID", sale.ID);
            OleDbDataReader reader = DBFunctions.GetInstance().GetReader(command);

            if (reader.Read())
            {
                //sale present. So update it
                //stock management incorporated. 24.9.2010
                //add the previous salequantity to item's stock, then subtract the new salequantity
                sale.Item.Stock = sale.Item.Stock + double.Parse(reader[1].ToString()) - sale.SaleQuantity;
                StockCache.GetInstance().UpdateStock(sale.Item);

                command = DBFunctions.GetInstance().GetCommand(
                    "Update Sales Set TransactionId=?, Item=?, SaleRate=?, SaleQuantity=?, Tax=?, SaleNumber=?,SaleUnit=?, LastUpdatedTime=Now() Where ID=?");
                command.Parameters.AddWithValue("Transaction", sale.Transaction.ID);
                command.Parameters.AddWithValue("Item", sale.Item.ID);
                command.Parameters.AddWithValue("SaleRate", sale.SaleRate);
                command.Parameters.AddWithValue("SaleQuantity", sale.SaleQuantity);
                command.Parameters.AddWithValue("Tax", sale.SaleTax);
                command.Parameters.AddWithValue("SaleNumber", sale.Number);
                command.Parameters.AddWithValue("SaleUnit", sale.SaleUnit.ID);
                command.Parameters.AddWithValue("ID", sale.ID);
                command.ExecuteNonQuery();
            }
            else
            {
                //sale not present. so insert it
                command = DBFunctions.GetInstance().GetCommand(
                    "INSERT INTO SALES(TransactionId,Item,SaleRate,SaleQuantity,Tax,SaleNumber,SaleUnit,LastUpdatedTime) "
                    + "VALUES(?,?,?,?,?,?,?,?)");
                command.Parameters.AddWithValue("Transaction", sale.Transaction.ID);
                command.Parameters.AddWithValue("Item", sale.Item.ID);
                command.Parameters.AddWithValue("SaleRate", sale.SaleRate);
                command.Parameters.AddWithValue("SaleQuantity", sale.SaleQuantity);
                command.Parameters.AddWithValue("Tax", sale.SaleTax);
                command.Parameters.AddWithValue("SaleNumber", sale.Number);
                command.Parameters.AddWithValue("SaleUnit", sale.SaleUnit.ID);
                command.Parameters.Add("LastUpdatedTime", OleDbType.Date).Value = DateTime.Now;
                command.ExecuteNonQuery();
                //stock management incorporated. 24.9.2010
                //subtract new sale's sale qtty from the item stock.
                sale.Item.Stock = sale.Item.Stock - sale.SaleQuantity;
                StockCache.GetInstance().UpdateStock(sale.Item);

                //get back the id
                command = DBFunctions.GetInstance().GetCommand(
                    "Select ID From Sales Where SaleNumber=? And TransactionId=?");
                command.Parameters.AddWithValue("SaleNumber", sale.Number);
                command.Parameters.AddWithValue("Transaction", sale.Transaction.ID);
                reader = DBFunctions.GetInstance().GetReader(command);
                if (reader.Read())
                {
                    sale.ID = int.Parse(reader[0].ToString());
                }
            }
            return(sale);
        }
Esempio n. 6
0
        //modified to incorporate stock management on sale insert/update 24.9.2010
        public SalePurchase SyncSale(SalePurchase sale)
        {
            OleDbCommand command = DBFunctions.GetInstance().GetCommand(
                "Select ID, SaleQuantity From Sales Where ID=?");

            command.Parameters.AddWithValue("ID", sale.ID);
            OleDbDataReader reader = DBFunctions.GetInstance().GetReader(command);

            if (reader.Read())
            {
                //sale present. So update it
                //moving average rate of items incorporated. 25.8.2014

                //stock management incorporated. 24.9.2010
                //sale purchase modes incorporated. 1/4/2011
                if (sale.Transaction.IsPurchase)
                {
                    //subtract the previous purchase qtty to stock, then add new purchace qtty.
                    sale.Item.Stock = sale.Item.Stock - double.Parse(reader[1].ToString()) + sale.Quantity;
                    //in case of updating a purchase, revert the average changed due to
                    //previous item rate, apply change in average due to new rate.
                    //this has to be done before updating DB with new purchase rate.
                    //Get the previous purchase object from db and pass it on to the calculator.
                    SalePurchase prevPurchase = GetSaleBySaleIDFromDB(sale.ID);
                    sale.Item.MovingAvgRate = MovingAvgCache.GetInstance().GetMovingAvgRate(sale, prevPurchase);
                    MovingAvgCache.GetInstance().UpdateMovingAvgRate(sale.Item);
                }
                else
                {
                    //add the previous salequantity to item's stock, then subtract the new salequantity
                    sale.Item.Stock = sale.Item.Stock + double.Parse(reader[1].ToString()) - sale.Quantity;
                    //if not a purchase, do not bother updating moving avg
                }
                StockCache.GetInstance().UpdateStock(sale.Item);

                command = DBFunctions.GetInstance().GetCommand(
                    "Update Sales Set TransactionId=?, Item=?, SaleRate=?, SaleQuantity=?, Tax=?, SaleNumber=?,SaleUnit=?, LastUpdatedTime=Now() Where ID=?");
                command.Parameters.AddWithValue("Transaction", sale.Transaction.ID);
                command.Parameters.AddWithValue("Item", sale.Item.ID);
                command.Parameters.AddWithValue("SaleRate", sale.SaleRate);
                command.Parameters.AddWithValue("SaleQuantity", sale.Quantity);
                command.Parameters.AddWithValue("Tax", sale.SaleTax);
                command.Parameters.AddWithValue("SaleNumber", sale.Number);
                command.Parameters.AddWithValue("SaleUnit", sale.SaleUnit.ID);
                command.Parameters.AddWithValue("ID", sale.ID);
                command.ExecuteNonQuery();
            }
            else
            {
                //sale not present. so insert it
                command = DBFunctions.GetInstance().GetCommand(
                    "INSERT INTO SALES(TransactionId,Item,SaleRate,SaleQuantity,Tax,SaleNumber,SaleUnit,LastUpdatedTime) "
                    + "VALUES(?,?,?,?,?,?,?,?)");
                command.Parameters.AddWithValue("Transaction", sale.Transaction.ID);
                command.Parameters.AddWithValue("Item", sale.Item.ID);
                command.Parameters.AddWithValue("SaleRate", sale.SaleRate);
                command.Parameters.AddWithValue("SaleQuantity", sale.Quantity);
                command.Parameters.AddWithValue("Tax", sale.SaleTax);
                command.Parameters.AddWithValue("SaleNumber", sale.Number);
                command.Parameters.AddWithValue("SaleUnit", sale.SaleUnit.ID);
                command.Parameters.Add("LastUpdatedTime", OleDbType.Date).Value = DateTime.Now;
                command.ExecuteNonQuery();
                //stock management incorporated. 24.9.2010
                //sale/purchace incorporated 1.4.2011
                if (sale.Transaction.IsPurchase)
                {
                    //add new sale's sale qtty to the item stock.
                    sale.Item.Stock = sale.Item.Stock + sale.Quantity;
                    //incorporating moving avg rate of item 25.8.2014
                    //while inserting a new purchase, update moving averate rate.
                    sale.Item.MovingAvgRate = MovingAvgCache.GetInstance().GetMovingAvgRate(sale);
                    MovingAvgCache.GetInstance().UpdateMovingAvgRate(sale.Item);
                }
                else
                {
                    //subtract new sale's sale qtty from the item stock.
                    sale.Item.Stock = sale.Item.Stock - sale.Quantity;
                }
                StockCache.GetInstance().UpdateStock(sale.Item);

                //get back the id
                command = DBFunctions.GetInstance().GetCommand(
                    "Select ID From Sales Where SaleNumber=? And TransactionId=?");
                command.Parameters.AddWithValue("SaleNumber", sale.Number);
                command.Parameters.AddWithValue("Transaction", sale.Transaction.ID);
                reader = DBFunctions.GetInstance().GetReader(command);
                if (reader.Read())
                {
                    sale.ID = int.Parse(reader[0].ToString());
                }
            }
            return(sale);
        }