/// <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);
}
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);
}
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);
}
/// <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);
}
/// <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);
}
/// <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);
}
public static string BuildNewCode()
{
string code = "";
bool exists = true;
while (exists)
{
code = "P-" + SBObject.generateCode();
exists = SBProduct.productCodeExists(code);
}
return(code);
}
/// <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);
}
/// <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);
}
/// <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);
}
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);
}
/// <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;
}
}