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);
}
/// <summary>
/// Gets the product kardex from database
/// </summary>
/// <returns >A <see cref="System.Collections.ArrayList"/></returns>
public ArrayList GetDbKardex(string type = "all")
{
string query = string.Format(@"SELECT * FROM product_kardex
WHERE product_code = '{0}'
{1}
ORDER BY creation_date ASC
LIMIT 1000",
this.Id,
(type == "all") ? "" : "AND in_out = '" + type + "'");
ArrayList kardex = SBFactory.getDbh().Query(query);
if (kardex == null)
{
return(null);
}
if (this.Data.ContainsKey("kardex"))
{
this.Data["kardex"] = kardex;
}
else
{
this.Data.Add("kardex", kardex);
}
return(kardex);
}
public bool HasSerialNumber(string sn)
{
string query = string.Format("SELECT serial_number_id from product_serialnumbers WHERE product_id = {0} AND serial_number = '{1}'",
this.Id, sn);
return((SBFactory.getDbh().QueryRow(query) == null) ? false : true);
}
public static string BuildNewEAN13Barcode(string country_code, string manufacturer_code)
{
int digits = 12 - (country_code.Length + manufacturer_code.Length);
string product_code = "";
string aux = "";
for (int i = 0; i < digits; i++)
{
aux += "9";
}
int max_code = Convert.ToInt32(aux);
for (int i = 0; i < max_code; i++)
{
string aux_code = SBUtils.FillLeftCeros(i + 1, digits);
Ean13 bc = new Ean13(country_code, manufacturer_code, aux_code);
bc.CalculateChecksumDigit();
string check_barcode = country_code + manufacturer_code + aux_code + bc.ChecksumDigit;
string query = string.Format("SELECT product_id FROM products WHERE product_barcode = '{0}'", check_barcode);
if (SBFactory.getDbh().QueryRow(query) == null)
{
product_code = check_barcode;
break;
}
}
return(product_code);
}
public Hashtable getRow(string wheres)
{
if (SBFactory.getDbh().db_type == "mysql" || SBFactory.getDbh().db_type == "sqlite" || SBFactory.getDbh().db_type == "sqlite3")
{
ArrayList skip = new ArrayList();
skip.Add("table");
skip.Add("primary_key");
skip.Add("Data");
skip.Add("last_query");
string query = "SELECT {0} FROM {1} {2} LIMIT 1";
string w = (String.IsNullOrEmpty(wheres)) ? "" : String.Format("WHERE {0}", wheres);
query = String.Format(query, this.getPropertiesWithGlue(",", skip), this.table, w);
return(SBFactory.getDbh().QueryRow(query));
}
else
{
ArrayList rows = this.getRows(wheres);
if (rows != null && rows.Count > 0)
{
return((Hashtable)rows[0]);
}
}
return(null);
}
public ArrayList GetDbKardex(string in_out, DateTime f, DateTime to)
{
in_out = in_out.ToLower();
string query = string.Format(@"SELECT * FROM product_kardex
WHERE product_code = '{0}'
{1}
AND DATE(creation_date) >= date('{2}')
AND DATE(creation_date) <= date('{3}')
ORDER BY creation_date",
this.Id,
(in_out == "all") ? "" : "AND in_out = '" + in_out + "'",
f.ToString("yyyy-MM-dd"),
to.ToString("yyyy-MM-dd")
);
ArrayList kardex = SBFactory.getDbh().Query(query);
if (kardex == null)
{
return(null);
}
if (this.Data.ContainsKey("kardex"))
{
this.Data["kardex"] = kardex;
}
else
{
this.Data.Add("kardex", kardex);
}
return(kardex);
}
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);
}
public bool DeleteRow(string column, object the_value)
{
Hashtable w = new Hashtable();
w.Add(column, the_value);
SBFactory.getDbh().delete(this.table, w);
return(true);
}
public void GetDbItems(string transaction_code)
{
string query = string.Format("SELECT * FROM transaction_items WHERE transaction_code = '{0}'", transaction_code);
Console.WriteLine(query);
ArrayList items = SBFactory.getDbh().Query(query);
if (items == null)
{
return;
}
this._items = items;
}
public ArrayList getRows(string wheres)
{
ArrayList skip = new ArrayList();
skip.Add("table");
skip.Add("primary_key");
skip.Add("Data");
skip.Add("last_query");
string query = "SELECT {0} FROM {1} {2}";
string w = (String.IsNullOrEmpty(wheres)) ? "" : String.Format("WHERE {0}", wheres);
query = String.Format(query, this.getPropertiesWithGlue(",", skip), this.table, w);
return(SBFactory.getDbh().Query(query));
}
public static long GetNextSequenceTransaction(long transaction_type_id, long store_id)
{
string query = string.Format("SELECT COUNT(sequence) AS sequence FROM transactions WHERE transaction_type_id = {0} AND store_id = {1}",
transaction_type_id, store_id);
Hashtable row = SBFactory.getDbh().QueryRow(query);
if (row == null)
{
return(1);
}
long sequence = Convert.ToInt64(row["sequence"]);
return(sequence + 1);
}
public object obtenerParametro(string nombre, object default_val)
{
string query = "SELECT id_parametro, nombre_parametro, valor_parametro FROM parametros WHERE nombre_parametro = '" + nombre + "'";
this.logString(query);
Hashtable p = SBFactory.getDbh().QueryRow(query);
if (p == null)
{
//crear parametros
return(default_val);
}
return(p["valor_parametro"]);
}
public void GetDbMeta(string transaction_code)
{
string query = string.Format("SELECT * FROM transaction_meta WHERE transaction_code = '{0}'", transaction_code);
ArrayList meta = SBFactory.getDbh().Query(query);
if (meta == null)
{
return;
}
foreach (Hashtable row in meta)
{
this._meta.Add(row["meta_key"], row["meta_value"]);
}
}
public void GetDbMeta()
{
this._meta = new Hashtable();
string query = string.Format("SELECT * FROM product_meta WHERE product_code = '{0}'", this.Code);
ArrayList meta = SBFactory.getDbh().Query(query);
if (meta == null)
{
return;
}
foreach (Hashtable r in meta)
{
this._meta.Add(r["meta_key"], r["meta_value"]);
}
}
public void updateRow(Hashtable row_data)
{
if (row_data[this.primary_key] == null)
{
throw new Exception("SMTable: No primary key defined, (" + this.primary_key + ")");
}
object aux = row_data[this.primary_key];
Hashtable w = new Hashtable();
w.Add(this.primary_key, row_data[this.primary_key]);
row_data.Remove(this.primary_key);
SBFactory.getDbh().update(this.table, row_data, w);
//restore primary key data
row_data.Add(this.primary_key, aux);
}
/// <summary>
///
/// </summary>
/// <param name="opening_balance">
/// A <see cref="System.Single"/>
/// </param>
/// <param name="currency_id">
/// A <see cref="System.Int32"/>
/// </param>
/// <param name="user_id">
/// A <see cref="System.Int32"/>
/// </param>
/// <returns>
/// A <see cref="System.Int32"/>
/// </returns>
public static int open(decimal opening_balance, int currency_id, int user_id, int terminal_id, int branch_id)
{
Console.WriteLine("opening cash");
Hashtable row = new Hashtable();
row.Add("opening_balance", opening_balance);
row.Add("ending_balance", 0);
row.Add("currency_id", currency_id);
row.Add("user_id", user_id);
row.Add("terminal_id", terminal_id);
row.Add("branch_id", branch_id);
row.Add("status", "open");
row.Add("creation_date", DateTime.Now);
SBFactory.getDbh().insert("cash_register", row);
return((int)(SBFactory.getDbh() as SBDatabase).LastInsertID);
}
public override void GetDbData(object code_id)
{
Hashtable data = null;
if (code_id is int || code_id is long)
{
this.db_table.setPrimaryKey("product_id");
data = this.db_table.getRow("product_id = " + code_id.ToString());
}
else
{
data = this.db_table.getRow("product_code = '" + code_id.ToString() + "'");
}
if (data == null)
{
return;
}
this._dbData = data;
//get product meta
this.GetDbMeta();
//get serial numbers
string q = string.Format("SELECT serial_number_id, product_id, serial_number, status FROM product_serialnumbers WHERE product_id = {0}", this.Id);
ArrayList serials = SBFactory.getDbh().Query(q);
if (serials != null)
{
this._serialNumbers = serials;
}
//get category
string query = "SELECT c.category_id, c.name " +
"FROM categories c, product2category p2c " +
"WHERE c.category_id = p2c.category_id " +
"AND p2c.product_id = " + this.Id.ToString() + " ";
Hashtable row = SBFactory.getDbh().QueryRow(query);
if (row == null)
{
return;
}
this.Data.Add("category", row);
//get store
//query = string.Format("SELECT * FROM stores WHERE store_id = {0}", this);
}
public static void close(float ending_balance, int terminal_id, int branch_id, DateTime date)
{
if (!SBCashRegister.isOpen(terminal_id, branch_id, date))
{
throw new Exception("Cash Register is not open");
}
string query = "UPDATE cash_register " +
"SET AND status = 'closed', " +
"ending_balance = {0} " +
"WHERE terminal_id = {1} " +
"AND branch_id = {2} " +
"AND DATE(creation_date) = '{3}'" +
"AND status = 'open'";
query = String.Format(query, ending_balance, terminal_id, branch_id, date.ToString("yyyy-MM-dd"));
SBFactory.getDbh().Query(query);
}
public static string BuildNewTransactionCode(int transaction_type_id, string prefix)
{
bool valid_code = false;
string fcode = "{0}{1}";
string code = "";
while (!valid_code)
{
code = string.Format(fcode, prefix, SBObject.generateCode());
string query = "SELECT transaction_id FROM transactions WHERE transaction_type_id = " + transaction_type_id.ToString() + "" +
" AND transaction_code = '" + code + "'";
Hashtable row = SBFactory.getDbh().QueryRow(query);
if (row == null)
{
valid_code = true;
}
}
return(code);
}
public static bool isOpen(int terminal_id, int branch_id, DateTime date)
{
string datetime_format = CultureInfo.InvariantCulture.DateTimeFormat.UniversalSortableDateTimePattern;
string query = "SELECT * FROM cash_register " +
"WHERE terminal_id = {0} " +
"AND branch_id = {1} " +
"AND status = 'open' " +
"AND DATE(creation_date) = '{2}'";
query = String.Format(query, terminal_id, branch_id, date.ToString("yyyy-MM-dd"));
//Console.WriteLine();
Hashtable row = SBFactory.getDbh().QueryRow(query);
if (row == null)
{
return(false);
}
return(true);
}
public ArrayList GetDbKardex(DateTime f, DateTime to)
{
string query = string.Format(@"SELECT * FROM product_kardex
WHERE product_code = '{0}'
AND creation_date >= date('{1}')
AND creation_date <= date('{2}')
ORDER BY creation_date DESC LIMIT 1000",
this.Id,
f.ToString("yyyy-MM-dd"),
to.ToString("yyyy-MM-dd")
);
ArrayList kardex = SBFactory.getDbh().Query(query);
if (kardex == null)
{
return(null);
}
this.Data.Add("kardex", kardex);
return(kardex);
}
/// <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>
/// 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);
}
public static ArrayList GetPriceRules(int category_id, string price_number)
{
string query = "";
/*
* if (SBFactory.getDbh().db_type == "sqlite" || SBFactory.getDbh().db_type == "sqlite3")
* {
* query = string.Format("SELECT [rule_id], [price], [from], [to], [percentage], [creation_date] FROM price_rules WHERE category_id = {0} AND price = '{1}'",
* category_id, price_number);
* }
* else
* {
* query = string.Format("SELECT rule_id, price, from, to, percentage, creation_date FROM price_rules WHERE category_id = {0} AND price = '{1}'",
* category_id, price_number);
* }
*/
query = string.Format("SELECT * FROM price_rules WHERE category_id = {0} AND price = '{1}'",
category_id, price_number);
ArrayList rules = SBFactory.getDbh().Query(query);
return(rules);
}
/// <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);
}
public void actualizarParametro(string nombre, string valor)
{
Hashtable data = new Hashtable();
string query = "SELECT id_parametro FROM parametros WHERE nombre_parametro = '" + nombre + "'";
if (SBFactory.getDbh().QueryRow(query) == null)
{
//crear parametros
data.Add("nombre_parametro", nombre);
data.Add("valor_parametro", valor);
data.Add("fecha_creacion", DateTime.Now.ToString("yyyyMMdd HH:mm:ss"));
SBFactory.getDbh().insert("parametros", data);
}
else
{
//actualizar parametro
data.Add("valor_parametro", valor);
Hashtable w = new Hashtable();
w.Add("nombre_parametro", nombre);
SBFactory.getDbh().update("parametros", data, w);
}
}
/// <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;
}
}
public int insertRow(Hashtable new_row)
{
return(SBFactory.getDbh().insert(this.table, new_row));
}