Exemplo n.º 1
0
        /// <summary>
        /// Get warehouse product
        /// </summary>
        /// <param name="product_code">
        /// A <see cref="System.String"/>
        /// </param>
        /// <returns>
        /// A <see cref="SMProduct"/>
        /// </returns>
        public static SBProduct getProduct(string product_code)
        {
            SBProduct prod = new SBProduct();

            prod.getData(product_code);
            return(prod);
        }
Exemplo n.º 2
0
        public static ArrayList GetStoreCategoryProducts(int store_id, int category_id)
        {
            string query = @"SELECT p.* FROM products p, product2category p2c
                                WHERE p.product_id = p2c.product_id
                                AND p.store_id = {0}
                                AND p2c.category_id = {1}";

            query = string.Format(query, store_id, category_id);
            ArrayList _prods = SBFactory.getDbh().Query(query);

            if (_prods == null)
            {
                return(null);
            }
            ArrayList prods = new ArrayList();

            foreach (Hashtable prod in _prods)
            {
                SBProduct product = new SBProduct();
                product.SetDbData(prod);
                product.GetDbMeta();
                prods.Add(product);
            }

            return(prods);
        }
Exemplo n.º 3
0
        public ArrayList GetProducts(int store_id = 0)
        {
            ArrayList prods = new ArrayList();
            string    query = "";

            if (store_id == 0)
            {
                query = @"SELECT * FROM products p, product2category p2c 
                            WHERE p2c.category_id = {0}
                            AND p.product_id = p2c.product_id";
                query = string.Format(query, this.CategoryID);
            }
            else
            {
                query = @"SELECT * FROM products p, product2category p2c 
                                    WHERE p.store_id = {0}
                                    AND p2c.category_id = {1}
                                    AND p.product_id = p2c.product_id";
                query = string.Format(query, store_id, this.CategoryID);
            }
            ArrayList _prods = SBFactory.getDbh().Query(query);

            if (_prods != null)
            {
                foreach (Hashtable _p in _prods)
                {
                    SBProduct p = new SBProduct();
                    p.SetDbData(_p);
                    prods.Add(p);
                }
            }

            return(prods);
        }
Exemplo n.º 4
0
        /// <summary>
        /// Increase the product stock in warehouse
        /// </summary>
        /// <param name="product_code">
        /// A <see cref="System.String"/>
        /// </param>
        /// <param name="qty">
        /// A <see cref="System.Int32"/>
        /// </param>
        /// <returns>
        /// A <see cref="SMProduct"/>
        /// </returns>
        public static SBProduct increaseProductStock(string product_code, int qty)
        {
            SBProduct p = SBWarehouse.getProduct(product_code);

            p.Quantity = p.Quantity + qty;
            p.Update();
            return(p);
        }
Exemplo n.º 5
0
        /// <summary>
        /// Descrease the product stock in warehouse
        /// </summary>
        /// <param name="product_code">
        /// A <see cref="System.String"/>
        /// </param>
        /// <param name="qty">
        /// A <see cref="System.Int32"/>
        /// </param>
        /// <returns>
        /// A <see cref="SMProduct"/>
        /// </returns>
        public static SBProduct restStock(string product_code, int qty)
        {
            SBProduct p = SBWarehouse.getProduct(product_code);

            p.Quantity = p.Quantity - qty;
            p.Quantity = (p.Quantity < 0) ? 0 : p.Quantity;
            p.Update();
            return(p);
        }
Exemplo n.º 6
0
        /// <summary>
        /// Check if  product code already exists into database
        /// </summary>
        /// <param name="code">
        /// A <see cref="System.String"/>
        /// </param>
        /// <returns>
        /// A <see cref="System.Boolean"/>
        /// </returns>
        public static bool productCodeExists(string code)
        {
            SBProduct p = new SBProduct();

            if (p.db_table.getRow("product_code = '" + code + "'") == null)
            {
                return(false);
            }
            return(true);
        }
Exemplo n.º 7
0
        public static string BuildNewCode()
        {
            string code   = "";
            bool   exists = true;

            while (exists)
            {
                code   = "P-" + SBObject.generateCode();
                exists = SBProduct.productCodeExists(code);
            }
            return(code);
        }
Exemplo n.º 8
0
        /// <summary>
        /// Gets the store product.
        /// </summary>
        /// <returns>
        /// The store product.
        /// A <see cref="SBProduct"/>
        /// </returns>
        /// <param name='store_id'>
        /// Store_id.
        /// </param>
        /// <param name='product_code'>
        /// Product_code.
        /// </param>
        public static SBProduct GetStoreProduct(int store_id, string product_code)
        {
            SBTableProduct tp   = new SBTableProduct();
            Hashtable      prod = tp.getRow(string.Format("store_id = {0} AND product_code = '{1}'", store_id, product_code.Trim()));

            if (prod == null)
            {
                return(null);
            }
            SBProduct product = new SBProduct();

            product.SetDbData(prod);
            product.GetDbMeta();
            return(product);
        }
Exemplo n.º 9
0
        /// <summary>
        /// Gets the category products.
        /// The method returns an SBProduct object array
        /// </summary>
        /// <returns>The category products.</returns>
        /// <param name="category_id">Category identifier.</param>
        public static ArrayList GetCategoryProducts(int category_id)
        {
            string query = "SELECT p.* " +
                           "FROM products p, product2category p2c " +
                           "WHERE p.product_id = p2c.product_id " +
                           "AND p2c.category_id = {0}";

            query = string.Format(query, category_id);
            ArrayList prods     = SBFactory.getDbh().Query(query);
            ArrayList obj_prods = new ArrayList();

            foreach (Hashtable p in prods)
            {
                SBProduct product = new SBProduct();
                product.SetDbData(p);
                obj_prods.Add(product);
            }

            return(obj_prods);
        }
Exemplo n.º 10
0
        /// <summary>
        /// Get all Warehouse products and return an ArrayList that contains SMProduct instances
        /// </summary>
        /// <returns>
        /// A <see cref="ArrayList"/>
        /// </returns>
        public static ArrayList getProducts()
        {
            SBTableProduct tp    = new SBTableProduct();
            ArrayList      prods = tp.getRows("(status = 'publish' OR status = 'initial') ORDER BY creation_date ASC");

            if (prods == null)
            {
                return(null);
            }
            ArrayList obj_prods = new ArrayList();

            foreach (Hashtable p in prods)
            {
                SBProduct product = new SBProduct();
                product.SetDbData(p);
                obj_prods.Add(product);
            }

            return(obj_prods);
        }
Exemplo n.º 11
0
        public static ArrayList GetStoreProducts(int store_id)
        {
            SBTableProduct tp     = new SBTableProduct();
            ArrayList      _prods = tp.getRows(string.Format("(status = 'publish' OR status = 'initial') AND store_id = {0}", store_id));

            if (_prods == null)
            {
                return(null);
            }
            ArrayList prods = new ArrayList();

            foreach (Hashtable prod in _prods)
            {
                SBProduct product = new SBProduct();
                product.SetDbData(prod);
                product.GetDbMeta();
                prods.Add(product);
            }

            return(prods);
        }
        /// <summary>
        /// Reverts the transaction.
        /// </summary>
        /// <returns><c>true</c>, if transaction was reverted, <c>false</c> otherwise.</returns>
        public bool RevertTransaction()
        {
            this.Status = "reverted";
            //Hashtable w = new Hashtable();
            //w.Add("transaction_code", this.TransactionCode);

            SBFactory.getDbh().BeginTransaction();

            //SBFactory.getDbh().update("transactions", this._dbData, w);
            if (this.TransactionType.InputOutput.ToUpper() == "IN" || this.TransactionType.InputOutput.ToUpper() == "INPUT")
            {
                //reduce products stock
                foreach (Hashtable item in this._items)
                {
                    int       product_id = Convert.ToInt32(item["object_id"]);
                    SBProduct product    = new SBProduct(product_id);
                    product.Quantity -= Convert.ToInt32(item["object_quantity"]);
                    product.Update();
                    //delete transaction product kardex records
                    SBFactory.getDbh().delete("product_kardex", new{ product_code = product.Id.ToString(), transaction_code = this.TransactionCode });
                }
            }
            else if (this.TransactionType.InputOutput.ToUpper() == "OUT" || this.TransactionType.InputOutput.ToUpper() == "OUTPUT")
            {
                //increase products stock
                foreach (Hashtable item in this._items)
                {
                    string    product_code = item["object_code"].ToString();
                    SBProduct product      = new SBProduct();
                    product.GetDbData(product_code);
                    product.Quantity += Convert.ToInt32(item["object_quantity"]);
                    product.Update();
                    //delete transaction product kardex records
                    SBFactory.getDbh().delete("product_kardex", new{ product_code = product.Id.ToString(), transaction_code = this.TransactionCode });
                }
            }
            this.db_table.updateRow(this._dbData);
            SBFactory.getDbh().EndTransaction();
            return(true);
        }
Exemplo n.º 13
0
        /// <summary>
        /// Gets the product by codebar.
        /// </summary>
        /// <returns>The product by codebar.</returns>
        public static SBProduct GetProductByCodebar(string barcode, int store_id = 0)
        {
            SBFactory.getDbh().Select("*")
            .From("products")
            .Where(string.Format("product_barcode = '{0}'", barcode));
            if (store_id > 0)
            {
                SBFactory.getDbh().And(string.Format("store_id = {0}", store_id));
            }

            Hashtable prod_row = SBFactory.getDbh().QueryRow();

            if (prod_row == null)
            {
                return(null);
            }
            SBProduct product = new SBProduct();

            product.SetDbData(prod_row);

            return(product);
        }
        /// <summary>
        /// Complete an INPUT/OUTPUT transaction
        /// </summary>
        /// <returns><c>true</c>, if transaction was completed, <c>false</c> otherwise.</returns>
        /// <param name="transaction_code">Transaction code.</param>
        /// <param name="apply_price_rules">If set to <c>true</c> apply price rules.</param>
        public static bool CompleteTransaction(string transaction_code, bool apply_price_rules = false)
        {
            try
            {
                SBTransaction transaction = new SBTransaction(transaction_code);
                Hashtable     tt          = SBWarehouse.GetTransactionType(transaction.TransactionTypeId);
                //check if it's  an input
                if (tt["in_out"].ToString().ToUpper() == "IN" || tt["in_out"].ToString().ToUpper() == "INPUT")
                {
                    //update stocks
                    foreach (Hashtable item in transaction.Items)
                    {
                        SBProduct prod = new SBProduct();
                        prod.GetDbData(item["object_code"]);
                        prod.Quantity = prod.Quantity + Convert.ToInt32(item["object_quantity"]);
                        prod.Cost     = Convert.ToDouble(item["object_price"]);

                        /*
                         * //check to apply price rules
                         * if( apply_price_rules )
                         * {
                         *  //update product prices
                         *  double _price = SBWarehouse.GetPriceFromPriceRule((float)prod.Cost, "price_1");
                         *  if( _price != prod.Cost )
                         *      prod.Price = _price;
                         *  _price = SBWarehouse.GetPriceFromPriceRule((float)prod.Cost, "price_2");
                         *  if( _price != prod.Cost )
                         *      prod.Price2 = _price;
                         *  //we need to round decimals to next integer for prices
                         *  prod.Price = Math.Ceiling(prod.Price);
                         *  prod.Price2 = Math.Ceiling(prod.Price2);
                         * }
                         * else
                         * {
                         * }
                         */
                        //string p_code = prod.Code;
                        prod.Update();

                        string real_cost_str = SBMeta.GetMeta("transaction_item_meta",
                                                              "transaction_item_id", Convert.ToInt32(item["transaction_item_id"]),
                                                              "_real_cost"
                                                              ).ToString();
                        float real_cost = 0;
                        float.TryParse(real_cost_str, out real_cost);
                        SBMeta.UpdateMeta("product_meta",
                                          "product_code", prod.Code,
                                          "_real_cost", real_cost.ToString("0.00")
                                          );
                        //##get latest two cost of the product for weighted average inventory
                        float  weighted_cost = 0;
                        string query         = "SELECT unit_price FROM product_kardex " +
                                               "WHERE transaction_type_id = {0} " +
                                               "AND product_code = '{1}' " +
                                               "ORDER BY creation_date DESC " +
                                               "LIMIT 2";
                        query = string.Format(query, transaction.TransactionTypeId, item["object_id"].ToString());
                        //Console.WriteLine(query);
                        var costs = SBFactory.getDbh().Query(query);
                        if (costs != null)
                        {
                            float total_cost = 0;
                            foreach (Hashtable row in costs)
                            {
                                total_cost += Convert.ToSingle(row["unit_price"]);
                            }
                            weighted_cost = total_cost / 2;
                        }
                        //##get fifo cost
                        float cost_fifo = 0;
                        //##build data to create product kardex
                        Hashtable k = new Hashtable();
                        k.Add("product_code", prod.Id);
                        k.Add("quantity", item["object_quantity"]);
                        k.Add("quantity_balance", prod.Quantity);
                        k.Add("unit_price", item["object_price"]);
                        k.Add("cost", item["object_price"]);
                        k.Add("cost_fifo", cost_fifo);
                        k.Add("cost_weighted_average", weighted_cost);
                        k.Add("total_amount", item["total"]);
                        k.Add("monetary_balance", prod.Quantity * Convert.ToSingle(item["object_price"]));
                        k.Add("author_id", SBUser.getLoggedInUser().UserId);
                        k.Add("creation_date", DateTime.Now);
                        k.Add("transaction_code", transaction.TransactionCode);
                        if (prod.Status.ToLower() == "initial")
                        {
                            //create initial kardex
                            k.Add("in_out", "initial");
                            k.Add("transaction_type_id", -1);
                            prod.Status = "publish";
                            prod.Update();
                        }
                        else
                        {
                            //update product kardex
                            k.Add("in_out", "input");
                            k.Add("transaction_type_id", tt["transaction_type_id"]);
                        }
                        SBFactory.getDbh().insert("product_kardex", k);
                    }
                    //update transaction status to completed|received
                    string tquery = string.Format("UPDATE transactions SET status = 'received' WHERE transaction_code = '{0}'",
                                                  transaction.TransactionCode);
                    SBFactory.getDbh().Execute(tquery);
                }
                //##complete OUTPUT transaction
                else
                {
                    foreach (Hashtable item in transaction.Items)
                    {
                        //##update product stock
                        SBProduct prod = new SBProduct(Convert.ToInt32(item["object_id"]));
                        prod.Quantity = prod.Quantity - Convert.ToInt32(item["object_quantity"]);
                        string p_code = prod.Code;
                        prod.Update();
                        //##update product kardex
                        Hashtable k = new Hashtable();
                        k.Add("product_code", prod.Id);
                        k.Add("in_out", "output");
                        k.Add("quantity", item["object_quantity"]);
                        k.Add("quantity_balance", prod.Quantity);
                        k.Add("cost", prod.Cost);
                        k.Add("cost_fifo", 0);                         //TODO: calculated fifo cost
                        k.Add("cost_weighted_average", 0);             //TODO: calculate average cost
                        k.Add("unit_price", Convert.ToSingle(item["object_price"]));
                        k.Add("total_amount", item["sub_total"]);
                        k.Add("monetary_balance", prod.Quantity * prod.Cost);
                        k.Add("transaction_type_id", tt["transaction_type_id"]);
                        k.Add("author_id", SBUser.getLoggedInUser().UserId);
                        k.Add("creation_date", DateTime.Now);
                        k.Add("transaction_code", transaction.TransactionCode);
                        SBFactory.getDbh().insert("product_kardex", k);
                    }
                }
                return(true);
            }
            catch (Exception e)
            {
                (SBGlobals.getVar("app") as SBApplication).logString(e.Message);
                (SBGlobals.getVar("app") as SBApplication).logString(e.StackTrace);
                throw e;
            }
        }