public void UpdateUnitCost()
{
using (db db = new db())
{
List <item> items = db.items.ToList();
foreach (item item in items)
{
if (item.item_product.FirstOrDefault() != null)
{
item_product item_product = item.item_product.FirstOrDefault();
/// Check for movement that have credit and no parents (Purchase or Inventory). Also that has value in Item Movement Value.
item_movement item_movement = db.item_movement
.Where(x =>
x.id_item_product == item_product.id_item_product &&
x.credit > 0 &&
x._parent == null &&
x.item_movement_value.Sum(y => y.unit_value) > 0).OrderByDescending(x => x.trans_date).FirstOrDefault();
if (item_movement != null)
{
item.unit_cost = item_movement.item_movement_value.Sum(x => x.unit_value);
}
}
}
db.SaveChanges();
}
}
public void calc_SingleCost(item_product item_product)
{
using (db db = new db())
{
int itemproductId = item_product.id_item_product;
item_movement ItemMovement = db.item_movement.Where(x => x.credit > 0 && x.id_item_product == itemproductId).OrderByDescending(x => x.trans_date).Take(1).FirstOrDefault();
if (ItemMovement != null)
{
item_product.item.unit_cost = ItemMovement.item_movement_value.Sum(x => x.unit_value);
}
}
}
private void StockList(object sender, EventArgs e)
{
if (item_movementViewSource.View != null)
{
if (item_movementViewSource.View.CurrentItem != null)
{
item_product _item_product = (item_movementViewSource.View.CurrentItem as item_movement).item_product;
if (_item_product != null && inventoryViewSource != null)
{
using (StockDB StockDB = new StockDB())
{
var movement =
(from items in StockDB.items
join item_product in StockDB.item_product on items.id_item equals item_product.id_item
into its
from p in its
join item_movement in StockDB.item_movement on p.id_item_product equals item_movement.id_item_product
into IMS
from a in IMS
join AM in StockDB.app_branch on a.app_location.id_branch equals AM.id_branch
where a.status == Status.Stock.InStock &&
a.id_item_product == _item_product.id_item_product &&
a.trans_date <= DateTime.Now &&
a.app_location.id_branch == CurrentSession.Id_Branch
group a by new { a.item_product, a.app_location }
into last
select new
{
code = last.Key.item_product.item.code,
name = last.Key.item_product.item.name,
location = last.Key.app_location.name,
itemid = last.Key.item_product.item.id_item,
quantity = last.Sum(x => x.credit) - last.Sum(x => x.debit),
id_item_product = last.Key.item_product.id_item_product,
measurement = last.Key.item_product.item.app_measurement.code_iso,
id_location = last.Key.app_location.id_location
}).ToList().OrderBy(y => y.name);
inventoryViewSource.Source = movement;
}
}
}
}
}
public int get_Location(item_product item_product, app_branch app_branch)
{
try
{
return(get_ProductLocation(item_product, app_branch));
}
catch
{
app_location app_location = new app_location();
app_location.id_branch = app_branch.id_branch;
app_location.name = "Default of " + app_branch.name;
app_location.is_default = true;
using (db db = new db())
{
db.app_location.Add(app_location);
db.SaveChangesAsync();
return(app_location.id_location);
}
}
}
public int get_ProductLocation(item_product item_product, app_branch app_branch)
{
int id_location = 0;
if (item_product != null)
{
//calculate location.
using (db db = new db())
{
if (db.item_movement.Where(x => x.item_product.id_item_product == item_product.id_item_product && x.app_location.id_branch == app_branch.id_branch).Any())
{
id_location = Convert.ToInt16(db.item_movement.Where(x => x.item_product.id_item_product == item_product.id_item_product &&
x.app_location.id_branch == app_branch.id_branch)
.FirstOrDefault().id_location);
}
else
{
id_location = get_DefaultLocation(app_branch);
}
}
}
return(id_location);
}
public void add_item(item_inventory_detail item_inventory_detail)
{
item_inventory_detail.id_inventory = item_inventoryList.FirstOrDefault().id_inventory;
// item_inventory_detail.item_inventory = item_inventoryList.FirstOrDefault().item_inventory;
item_inventory_detail.value_system = item_inventoryList.FirstOrDefault().value_system;
item_inventory_detail.id_item_product = item_inventoryList.FirstOrDefault().id_item_product;
item_inventory_detail.item_product = item_inventoryList.FirstOrDefault().item_product;
item_inventory_detail.id_location = item_inventoryList.FirstOrDefault().id_location;
item_inventory_detail.IsSelected = true;
item_inventory_detail.State = EntityState.Added;
item_inventory_detail.timestamp = item_inventoryList.FirstOrDefault().item_inventory.trans_date;
if (InventoryDB.app_currencyfx.Where(x => x.app_currency.is_priority && x.is_active).FirstOrDefault() != null)
{
item_inventory_detail.id_currencyfx = InventoryDB.app_currencyfx.Where(x => x.app_currency.is_priority && x.is_active).FirstOrDefault().id_currencyfx;
}
item_inventoryList.FirstOrDefault().item_inventory.item_inventory_detail.Add(item_inventory_detail);
if (item_inventory_detail.id_item_product > 0)
{
if (InventoryDB.item_product.Where(x => x.id_item_product == item_inventory_detail.id_item_product).FirstOrDefault() != null)
{
item_product item_product = InventoryDB.item_product.Where(x => x.id_item_product == item_inventory_detail.id_item_product).FirstOrDefault();
if (InventoryDB.item_dimension.Where(x => x.id_item == item_product.id_item).ToList() != null)
{
List <item_dimension> item_dimensionList = InventoryDB.item_dimension.Where(x => x.id_item == item_product.id_item).ToList();
foreach (item_dimension item_dimension in item_dimensionList)
{
item_inventory_dimension item_inventory_dimension = new item_inventory_dimension();
item_inventory_dimension.id_dimension = item_dimension.id_app_dimension;
item_inventory_dimension.value = item_dimension.value;
// item_inventory_dimension.id_measurement = item_dimension.id_measurement;
item_inventory_detail.item_inventory_dimension.Add(item_inventory_dimension);
}
}
}
}
}
public List <item_movement> CalculateStock(production_execution production_execution, List <item_movement> item_movementList)
{
foreach (production_execution_detail detail in production_execution.production_execution_detail
.Where(x => x.item.id_item_type == item.item_type.Product ||
x.item.id_item_type == item.item_type.RawMaterial))
{
item_product item_product = Stock.FindNFix_ItemProduct(detail.item);
List <item_movement> _item_movementList;
_item_movementList = db.item_movement.Where(x => x.id_location == production_execution.production_line.id_location &&
x.id_item_product == item_product.id_item_product &&
x.status == entity.Status.Stock.InStock &&
(x.credit - (x._child.Count() > 0 ? x._child.Sum(y => y.debit) : 0)) > 0).ToList();
if (item_product.cogs_type == item_product.COGS_Types.LIFO && _item_movementList != null)
{
_item_movementList = _item_movementList.OrderBy(x => x.trans_date).ToList();
}
else if (_item_movementList != null)
{
_item_movementList = _item_movementList.OrderByDescending(x => x.trans_date).ToList();
}
else
{
//Adding into List if _item_movementList is empty.
item_movementList.Add(Stock.debit_Movement(entity.Status.Stock.InStock,
App.Names.ProductionExecustion,
detail.id_production_execution,
item_product.id_item_product,
(int)production_execution.production_line.id_location,
detail.quantity,
production_execution.trans_date,
Stock.comment_Generator(App.Names.ProductionExecustion, production_execution.id_production_execution.ToString(), "")
));
}
foreach (item_movement object_Movement in _item_movementList)
{
decimal qty_ExexustionDetail = detail.quantity;
if (qty_ExexustionDetail > 0)
{
item_movement item_movement = new item_movement();
if (detail.is_input)
{
decimal movement_debit_quantity = qty_ExexustionDetail;
if (object_Movement.credit <= qty_ExexustionDetail)
{
movement_debit_quantity = object_Movement.credit;
}
else
{
movement_debit_quantity = qty_ExexustionDetail;
}
//If input is true, then we should DEBIT Stock.
item_movement = Stock.debit_Movement(entity.Status.Stock.InStock,
App.Names.ProductionExecustion,
(int)detail.id_production_execution,
item_product.id_item_product,
(int)production_execution.production_line.id_location,
movement_debit_quantity,
production_execution.trans_date,
Stock.comment_Generator(App.Names.ProductionExecustion,
production_execution.id_production_execution.ToString(), ""));
}
else
{
//If input is false, then we should CREDIT Stock.
item_movement = Stock.credit_Movement(entity.Status.Stock.InStock,
App.Names.ProductionExecustion,
(int)detail.id_production_execution,
item_product.id_item_product,
(int)production_execution.production_line.id_location,
qty_ExexustionDetail,
production_execution.trans_date,
Stock.comment_Generator(App.Names.ProductionExecustion,
production_execution.id_production_execution.ToString(), ""));
}
item_movement._parent = object_Movement;
//Logic for Value
item_movement_value item_movement_value = new item_movement_value();
item_movement_value.unit_value = object_Movement.item_movement_value.Sum(i => i.unit_value);
item_movement_value.id_currencyfx = object_Movement.item_movement_value.FirstOrDefault().id_currencyfx;
item_movement_value.comment = item_movement.comment;
item_movement.item_movement_value.Add(item_movement_value);
//Adding into List
item_movementList.Add(item_movement);
qty_ExexustionDetail = qty_ExexustionDetail - object_Movement.credit;
}
}
}
return(item_movementList);
}
public void product()
{
string sql = " SELECT dbo.FAMILIA.DESFAMILIA, dbo.LINEA.DESLINEA, dbo.RUBRO.DESRUBRO, dbo.IVA.DESIVA, dbo.CODIGOS.DESCODIGO1, dbo.CODIGOS.CODIGO, dbo.CODIGOS.PESABLE, dbo.CODIGOS.VENCIMIENTO, dbo.CODIGOS.BALANZA, dbo.PRODUCTOS.DESPRODUCTO, dbo.PRODUCTOS.STOCKMINIMO, dbo.PRODUCTOS.STOCKMAXIMO, "
+ " dbo.PRODUCTOS.SERVICIO, dbo.PRODUCTOS.ESTADO, dbo.PRODUCTOS.ESPECIFICACIONES, dbo.PRODUCTOS.PRODUCTO, dbo.UNIDADMEDIDA.DESMEDIDA, dbo.CODIGOS.CODCODIGO"
+ " FROM dbo.CODIGOS LEFT OUTER JOIN"
+ " dbo.PRODUCTOS ON dbo.CODIGOS.CODPRODUCTO = dbo.PRODUCTOS.CODPRODUCTO LEFT OUTER JOIN"
+ " dbo.UNIDADMEDIDA ON dbo.PRODUCTOS.CODMEDIDA = dbo.UNIDADMEDIDA.CODMEDIDA LEFT OUTER JOIN"
+ " dbo.RUBRO ON dbo.PRODUCTOS.CODRUBRO = dbo.RUBRO.CODRUBRO LEFT OUTER JOIN"
+ " dbo.FAMILIA ON dbo.PRODUCTOS.CODFAMILIA = dbo.FAMILIA.CODFAMILIA LEFT OUTER JOIN"
+ " dbo.LINEA ON dbo.PRODUCTOS.CODLINEA = dbo.LINEA.CODLINEA LEFT OUTER JOIN"
+ " dbo.IVA ON dbo.PRODUCTOS.CODIVA = dbo.IVA.CODIVA";
SqlConnection conn = new SqlConnection(_connString);
//Counts Total number of Rows we have to process
SqlCommand cmd = new SqlCommand();
conn.Open();
cmd.Connection = conn;
cmd.CommandText = "SELECT COUNT(*) FROM CODIGOS";
cmd.CommandType = CommandType.Text;
int count = (int)cmd.ExecuteScalar();
//cmd.Dispose();
conn.Close();
int value = 0;
Dispatcher.BeginInvoke((Action)(() => progItem.Maximum = count));
Dispatcher.BeginInvoke((Action)(() => progItem.Value += value));
Dispatcher.BeginInvoke((Action)(() => itemMaximum.Text = count.ToString()));
Dispatcher.BeginInvoke((Action)(() => itemValue.Text = value.ToString()));
string sql_price = " SELECT dbo.TIPOCLIENTE.DESTIPOCLIENTE, dbo.PRECIO.CANTIDAD, dbo.PRECIO.PRECIOVENTA, dbo.MONEDA.DESMONEDA, dbo.PRODUCTOS.DESPRODUCTO"
+ " FROM dbo.PRECIO LEFT OUTER JOIN"
+ " dbo.MONEDA ON dbo.PRECIO.CODMONEDA = dbo.MONEDA.CODMONEDA LEFT OUTER JOIN"
+ " dbo.PRODUCTOS ON dbo.PRECIO.CODPRODUCTO = dbo.PRODUCTOS.CODPRODUCTO LEFT OUTER JOIN"
+ " dbo.TIPOCLIENTE ON dbo.PRECIO.CODTIPOCLIENTE = dbo.TIPOCLIENTE.CODTIPOCLIENTE";
DataTable dt_Price = exeDT(sql_price);
conn.Open();
cmd.Connection = conn;
cmd.CommandText = sql;
cmd.CommandType = CommandType.Text;
DataTable dt_product = exeDT(sql);
foreach (DataRow reader in dt_product.Rows)
{
using (db db = new db())
{
db.Configuration.AutoDetectChangesEnabled = false;
item item = new item();
item.id_company = id_company;
if (!(reader[9] is DBNull))
{
item.name = reader[9].ToString();
}
else
{
continue;
}
item.code = (reader[5] is DBNull) ? string.Empty : reader[5].ToString();
item.variation = (reader[9] is DBNull) ? string.Empty : reader[9].ToString();
item.is_active = (reader[13] is DBNull) ? false : true;
item.description = (reader[14] is DBNull) ? string.Empty : reader[14].ToString();
string name = (reader[16] is DBNull) ? string.Empty : reader[16].ToString();
if (db.app_measurement.Where(x => x.name == name && x.id_company == id_company).FirstOrDefault() != null)
{
item.id_measurement = db.app_measurement.Where(x => x.name == name && x.id_company == id_company).FirstOrDefault().id_measurement;
}
//string FAMILIA;
if (!(reader[0] is DBNull))
{
string tagname = reader[0].ToString();
item_tag item_tagFam = db.item_tag.Where(x => x.name == tagname && x.id_company == id_company).FirstOrDefault();
item_tag_detail tag_detailFam = new item_tag_detail();
tag_detailFam.id_tag = item_tagFam.id_tag;
item.item_tag_detail.Add(tag_detailFam);
}
//string LINEA;
if (!(reader[1] is DBNull))
{
string tagLinname = reader[1].ToString();
item_tag item_tagLin = db.item_tag.Where(x => x.name == tagLinname && x.id_company == id_company).FirstOrDefault();
item_tag_detail tag_detailLin = new item_tag_detail();
tag_detailLin.id_tag = item_tagLin.id_tag;
item.item_tag_detail.Add(tag_detailLin);
}
//string RUBRO;
if (!(reader[2] is DBNull))
{
string tagrubro = reader[2].ToString();
item_tag item_tagRub = db.item_tag.Where(x => x.name == tagrubro && x.id_company == id_company).FirstOrDefault();
item_tag_detail tag_detailRub = new item_tag_detail();
tag_detailRub.id_tag = item_tagRub.id_tag;
item.item_tag_detail.Add(tag_detailRub);
}
if (!(reader[15] is DBNull))
{
if (Convert.ToInt32(reader[15]) == 1)
{
//Product
item.id_item_type = item.item_type.Product;
item_product product = new item_product();
product.id_company = id_company;
product.can_expire = (reader[7] is DBNull || Convert.ToInt32(reader[7]) == 0) ? false : true;
product.is_weigted = (reader[6] is DBNull || Convert.ToInt32(reader[6]) == 0) ? false : true;
product.stock_max = (reader[11] is DBNull) ? 0M : (decimal)reader[11];
product.stock_min = (reader[10] is DBNull) ? 0M : (decimal)reader[10];
item.item_product.Add(product);
}
else
{
item.id_item_type = item.item_type.Task; //Generic Service
//item_service service
}
}
else
{
item.id_item_type = item.item_type.Task; //Generic Service
//item_service service
}
decimal _vat_coeficient = 0;
if (!(reader[3] is DBNull))
{
string vatname = reader[3].ToString();
app_vat_group app_vat_group = db.app_vat_group.Where(x => x.name == vatname && x.id_company == id_company).FirstOrDefault();
item.id_vat_group = app_vat_group.id_vat_group;
}
decimal coefficient = 0;
List <app_vat_group_details> app_vat_group_details = db.app_vat_group_details.Where(x => x.id_vat_group == item.id_vat_group).ToList();
foreach (app_vat_group_details app_vat_group in app_vat_group_details)
{
coefficient = coefficient + app_vat_group.app_vat.coefficient;
}
string _DESPRODUCTO = reader["DESPRODUCTO"].ToString();
_DESPRODUCTO = _DESPRODUCTO.Replace("'", "");
try
{
foreach (DataRow price_row in dt_Price.Select("DESPRODUCTO = '" + _DESPRODUCTO + "'"))
{
string _desTipoCliente = (price_row.IsNull("DESTIPOCLIENTE")) ? string.Empty : price_row["DESTIPOCLIENTE"].ToString();
string _desMoneda = (price_row.IsNull("DESMONEDA")) ? string.Empty : price_row["DESMONEDA"].ToString();
if (_desTipoCliente != string.Empty && _desMoneda != string.Empty)
{
item_price_list item_price_list = db.item_price_list.Where(x => x.name == _desTipoCliente && x.id_company == id_company).FirstOrDefault();
app_currency app_currency = db.app_currency.Where(x => x.name == _desMoneda && x.id_company == id_company).FirstOrDefault();
if (item_price_list != null && app_currency != null && _vat_coeficient != -1)
{
item_price item_price = new item_price();
item_price.item = item;
if (price_row["PRECIOVENTA"] is DBNull)
{
item_price.value = 0;
}
else
{
if (coefficient != -1)
{
item_price.value = ((decimal)price_row["PRECIOVENTA"] / (1 + coefficient));
}
}
item_price.min_quantity = (price_row.IsNull("CANTIDAD")) ? 0 : Convert.ToDecimal(price_row["CANTIDAD"]);
item_price.id_currency = app_currency.id_currency;
item_price.id_price_list = item_price_list.id_price_list;
item.item_price.Add(item_price);
}
}
}
}
catch (Exception ex)
{
throw ex;
}
try
{
if (item.Error == null)
{
db.items.Add(item);
db.SaveChanges();
value += 1;
Dispatcher.BeginInvoke((Action)(() => progItem.Value = value));
Dispatcher.BeginInvoke((Action)(() => itemValue.Text = value.ToString()));
}
}
catch { }
}
}
//cmd.Dispose();
conn.Close();
_product_Current = _product_Max;
}
private void cmbitem_SelectionChanged(object sender, SelectionChangedEventArgs e)
{
item item = itemViewSource.View.CurrentItem as item;
if (item != null)
{
//Product
if (item.id_item_type == item.item_type.Product ||
item.id_item_type == item.item_type.RawMaterial ||
item.id_item_type == item.item_type.Supplies)
{
if (item.item_product.Count == 0 || item.item_product == null)
{
if (itemitem_productViewSource.View != null)
{
item_product _product = new item_product();
item.item_product.Add(_product);
itemitem_productViewSource.View.Refresh();
itemitem_productViewSource.View.MoveCurrentTo(_product);
}
}
if (item.item_asset.Count > 0)
{
List <item_asset> records = item.item_asset.ToList();
foreach (var record in records)
{
ItemDB.item_asset.Remove(record);
}
}
}
//Service
else if (item.id_item_type == item.item_type.Service || item.id_item_type == entity.item.item_type.ServiceContract)
{
if (item.item_asset.Count > 0)
{
List <item_asset> records = item.item_asset.ToList();
foreach (var record in records)
{
ItemDB.item_asset.Remove(record);
}
}
if (item.item_product.Count > 0)
{
List <item_product> records = item.item_product.ToList();
foreach (var record in records)
{
ItemDB.item_product.Remove(record);
}
}
}
//Capital Resource
//else if (item.id_item_type == item.item_type.FixedAssets)
//{
// if (item.item_asset.Count == 0)
// {
// item_asset _capital = new item_asset();
// item.item_asset.Add(_capital);
// if (itemitem_capitalViewSource.View != null)
// {
// itemitem_capitalViewSource.View.Refresh();
// itemitem_capitalViewSource.View.MoveCurrentTo(_capital);
// }
// }
// if (item.item_product.Count > 0)
// {
// List<item_product> records = item.item_product.ToList();
// foreach (var record in records)
// {
// ItemDB.item_product.Remove(record);
// }
// }
//}
}
//Product
}
public List <StockList> List(app_branch app_branch, app_location app_location, item_product item_product)
{
string query = @"select
parent.id_movement as MovementID,
parent.trans_date as TransDate,
parent.credit - if( sum(child.debit) > 0, sum(child.debit), 0 ) as QtyBalance,
(select sum(unit_value) from item_movement_value as parent_val where id_movement = parent.id_movement) as Cost
from item_movement as parent
inner join app_location as loc on parent.id_location = loc.id_location
left join item_movement as child on child._parent_id_movement = parent.id_movement
where {0} and parent.id_item_product = {1} and parent.status = 2 and parent.debit = 0
group by parent.id_movement
order by parent.trans_date";
string WhereQuery = "";
//This determins if we should bring cost of entire block of
if (app_location != null)
{
WhereQuery = String.Format("parent.id_location = {0}", app_location.id_location);
}
else
{
WhereQuery = String.Format("loc.id_branch = {0}", app_branch.id_branch);
}
query = String.Format(query, WhereQuery, item_product.id_item_product);
DataTable dt = exeDT(query);
return(GenerateList(dt));
}
private void cmbitem_SelectionChanged(object sender, SelectionChangedEventArgs e)
{
entity.item item = itemViewSource.View.CurrentItem as entity.item;
if (item != null)
{
//Product
if (item.id_item_type == global::entity.item.item_type.Product)
{
if (item.item_product.Count == 0)
{
item_product _product = new item_product();
item.item_product.Add(_product);
}
if (item.item_asset.Count > 0)
{
List <item_asset> records = item.item_asset.ToList();
foreach (var record in records)
{
db.item_asset.Remove(record);
}
}
}
//Searvice
else if (item.id_item_type == global::entity.item.item_type.Service)
{
if (item.item_asset.Count > 0)
{
List <item_asset> records = item.item_asset.ToList();
foreach (var record in records)
{
db.item_asset.Remove(record);
}
}
if (item.item_product.Count > 0)
{
List <item_product> records = item.item_product.ToList();
foreach (var record in records)
{
db.item_product.Remove(record);
}
}
}
//Capital Resource
else if (item.id_item_type == global::entity.item.item_type.FixedAssets)
{
if (item.item_asset.Count == 0)
{
item_asset _capital = new item_asset();
item.item_asset.Add(_capital);
}
if (item.item_product.Count > 0)
{
List <item_product> records = item.item_product.ToList();
foreach (var record in records)
{
db.item_product.Remove(record);
}
}
}
}
//Product
}
