private SumIV GetLotIV00300(string product, string warehouse)
{
SumIV sumiv = new SumIV();
decimal tot = 0;
CultureInfo culture;
culture = CultureInfo.CreateSpecificCulture("es-PA");
Thread.CurrentThread.CurrentCulture = culture;
try
{
using (TSGVLEntities db = new TSGVLEntities())
{
db.Database.CommandTimeout = 0;
var qry = "SELECT sum(QTYRECVD - QTYSOLD - ATYALLOC) as qtyrecvd, lotnumbr, DATERECD";
qry += " FROM IV00300";
qry += " WHERE itemnmbr='" + product + "' and locncode='" + warehouse + "' and qtytype='1' and ((QTYRECVD - QTYSOLD - ATYALLOC) > 0)";
qry += " GROUP BY itemnmbr, lotnumbr, DATERECD";
var result = db.Database.SqlQuery <SumIV>(qry).ToList();
sumiv = result.Count > 1 ? result.LastOrDefault() : result.FirstOrDefault();
}
}
catch (Exception ex)
{
sumiv = null;
Debug.Write(@"Error " + ex.Message);
}
return(sumiv);
}
public List <OrderReqDetailExtend> GetAllDetail()
{
List <OrderReqDetailExtend> list = new List <OrderReqDetailExtend>();
try
{
using (TSGVLEntities db = new TSGVLEntities())
{
//var detail = (from or in context.OrderReqDetails
// where or.ProductCodeChanged != null
// select or).ToList();
var detail = (from or in context.OrderReqDetails
join orp in context.OrderReqDetailProducts on or.OrderReqCode equals orp.OrderReqCode
select new OrderReqDetailExtend
{
Id = or.Id,
OrderReqCode = or.OrderReqCode.Trim(),
Observation = or.Observation,
ProductCode = or.ProductCode,
ProductCodeChanged = orp.ProductCodeChanged,
Quantity = or.Quantity,
DateProductChanged = orp.DateProductChanged,
UserUpdated = orp.UserUpdated,
//ProductName = db.IV00101.FirstOrDefault(a => a.ITEMNMBR.Trim() == item.ProductCode).ITEMDESC.Trim(),
//ProductNameChanged = (item.ProductCodeChanged != null) ? db.IV00101.FirstOrDefault(a => a.ITEMNMBR.Trim() == item.ProductCodeChanged.ToString()).ITEMDESC.Trim() : string.Empty,
QuantityChanged = orp.Quantity == null ? 0 : orp.Quantity
}).ToList();
list.AddRange(detail.Select(item =>
{
var productName = db.IV00101.FirstOrDefault(a => a.ITEMNMBR.Trim() == item.ProductCode);
var productNameChanged = db.IV00101.FirstOrDefault(a => a.ITEMNMBR.Trim() == item.ProductCodeChanged);
return(new OrderReqDetailExtend
{
Id = item.Id,
OrderReqCode = item.OrderReqCode.Trim(),
Observation = item.Observation,
ProductCode = item.ProductCode,
ProductCodeChanged = item.ProductCodeChanged,
Quantity = item.Quantity,
DateProductChanged = item.DateProductChanged,
UserUpdated = item.UserUpdated,
ProductName = (productName != null)? productName.ITEMDESC.Trim(): string.Empty,
ProductNameChanged = (productNameChanged != null)? productNameChanged.ITEMDESC.Trim() : string.Empty
});
}));
}
}
catch (Exception ex)
{
list = null;
Debug.Write("Error " + ex.InnerException.Message);
}
return(list);
}
public List <Product> Search(string code)
{
//return context.Products.Where(a => a.Code.ToLower().Contains(code.ToLower()) || a.Description.ToLower().Contains(code.ToLower())).ToList();
using (TSGVLEntities db = new TSGVLEntities())
{
return(db.IV00101.Where(a => a.ITEMNMBR.Trim().ToLower().Contains(code.ToLower()) || a.ITEMDESC.Trim().ToLower().Contains(code.ToLower())).Select(a => new Product()
{
Id = 0,
Code = a.ITEMNMBR.Trim(),
Description = a.ITEMDESC.Trim(),
}).ToList());
}
}
public Product GetByCode(string code)
{
//return context.Products.FirstOrDefault(a => a.Code == code);
Product prod = new Product();
using (TSGVLEntities db = new TSGVLEntities())
{
var p = db.IV00101.FirstOrDefault(a => a.ITEMNMBR.Trim() == code);
if (p != null)
{
prod.Id = 0;
prod.Code = p.ITEMNMBR.Trim();
prod.Description = p.ITEMDESC.Trim();
}
}
return(prod);
}
public List <OrderReqDetailExtend> GetOrderRedDetailExtendProduct(string code)
{
List <OrderReqDetailExtend> list = new List <OrderReqDetailExtend>();
try
{
var details = context.OrderReqDetailProducts.Where(a => a.OrderReqCode == code).ToList();
using (TSGVLEntities db = new TSGVLEntities())
{
foreach (var rows in details)
{
list.Add(new OrderReqDetailExtend
{
Id = rows.Id,
OrderReqCode = rows.OrderReqCode,
Observation = string.Empty,
ProductCode = rows.ProductCode,
ProductCodeChanged = rows.ProductCodeChanged,
Quantity = rows.Quantity,
DateProductChanged = rows.DateProductChanged,
UserUpdated = rows.UserUpdated,
ProductName = db.IV00101.FirstOrDefault(a => a.ITEMNMBR.Trim() == rows.ProductCode)
?.ITEMDESC
.Trim(),
ProductNameChanged = db.IV00101
.FirstOrDefault(a => a.ITEMNMBR.Trim() == rows.ProductCodeChanged)
?.ITEMDESC.Trim(),
QuantityChanged = rows.Quantity,
Warehouse = rows.Warehouse
});
}
}
return(list);
}
catch (Exception)
{
return(null);
throw;
}
}
private bool syncIV10300()
{
CultureInfo culture;
culture = CultureInfo.CreateSpecificCulture("es-PA");
Thread.CurrentThread.CurrentCulture = culture;
string query = string.Empty;
List <string> arrayNotDelete = new List <string>();
try
{
using (TSGVLEntities db = new TSGVLEntities())
{
db.Database.CommandTimeout = 0;
using (var conn = new SQLiteConnection(
@"Data Source=" + Path.Combine(AppDomain.CurrentDomain.BaseDirectory,
ConfigurationManager.AppSettings["filePath"])))
{
conn.Open();
DatabaseHelper context = new DatabaseHelper();
using (var cmd = new SQLiteCommand(conn))
{
//IV10300: Conteo de Stock "Master"
var _idStt = new[] { 1, 2 };
//List<IV10300> _IV10300 = db.IV10300.Where(a => a.STCKCNTSTTS == 1 && a.STCKCNTSTTS == 2).ToList();
List <IV10300> _IV10300 = db.IV10300.Where(a => _idStt.Contains(a.STCKCNTSTTS)).ToList();
List <Models.CountPlan> _list = (from cp in context.CountPlans
select cp).ToList();
if (_IV10300 != null)
{
List <IV10300> iv10300 = new List <IV10300>();
#region "Elimina conteo que estan inactivos en GP (estatus 1) y se encuentran en la bd intermedia"
foreach (var item in _list)
{
if (_IV10300.Where(r => r.STCKCNTSTTS == 1).Any(a => a.STCKCNTID.Trim() == item.Name.Trim()))
{
query = "DELETE FROM countplandetailitem ";
query += " WHERE countPlanId = '" + item.Id + "'";
removeData(conn, cmd, query);
query = "";
//remove status 0 CountPlandetail
query = "DELETE FROM countplandetail ";
query += " WHERE countPlanId = '" + item.Id + "'";
removeData(conn, cmd, query);
query = "";
//remove status 0 CountPlan
query = "DELETE FROM countplan WHERE Id = '" + item.Id + "'";
removeData(conn, cmd, query);
}
}
#endregion
#region "Eliminia de la bd los conteos que fueron procesados"
//remove status 0 CountPlandetailItem
query = "DELETE FROM countplandetailitem ";
query += " WHERE countPlanId IN (SELECT ID FROM countplan WHERE status = '0')";
removeData(conn, cmd, query);
query = "";
//remove status 0 CountPlandetail
query = "DELETE FROM countplandetail ";
query += " WHERE countPlanId IN (SELECT ID FROM countplan WHERE status = '0')";
removeData(conn, cmd, query);
query = "";
//remove status 0 CountPlan
query = "DELETE FROM countplan WHERE status = '0'";
removeData(conn, cmd, query);
#endregion
arrayNotDelete = getLisNotDeleteCountPlan(); //Get countPlan no deleted
var myListInsert = string.Empty;
if (arrayNotDelete.Count > 0)
{
iv10300 = (from cp in _IV10300
where cp.STCKCNTSTTS == 2 && !arrayNotDelete.Contains(cp.STCKCNTID.Trim())
select cp).ToList();
}
else
{
iv10300 = _IV10300.Where(a => a.STCKCNTSTTS == 2).ToList();
}
//Get CountPlan from GP and filter to no delete ID in arrayNotDelete
using (var transaction = conn.BeginTransaction())
{
arrayNotDelete.Clear(); //limpia los conteos
foreach (var item in iv10300)
{
cmd.CommandText =
"INSERT INTO CountPlan (Name, Description, Status, DateCreated, Warehouse) VALUES ('" +
item.STCKCNTID.Trim() + "','" +
item.STCKCNTDSCRPTN.Trim().Replace("'", "''") +
"','" + item.STCKCNTSTTS + "','" + item.LSTCNTDT.ToShortDateString().Trim() +
"','" + item.LOCNCODE + "')";
cmd.ExecuteNonQuery();
arrayNotDelete.Add(item.STCKCNTID.Trim()); //agrega el nombre del conteo para luego agregar los detalles
}
transaction.Commit();
}
}
else
{
return(false);
Debug.Write("Fail load IV10300");
}
if (_list != null)
{
_list = (from cp in context.CountPlans
where arrayNotDelete.Contains(cp.Name)
select cp).ToList();
foreach (Models.CountPlan countPlan in _list)
{
var _IV10301 = (from _iv10300 in db.IV10301
join _iv00102 in db.IV00102
on new { _iv10300.ITEMNMBR, _iv10300.LOCNCODE } equals new
{ _iv00102.ITEMNMBR, _iv00102.LOCNCODE }
where _iv10300.STCKCNTID.Trim().Equals(countPlan.Name)
select new
{
_ITEMNMBR = _iv10300.ITEMNMBR,
_CAPTUREDQTY = (_iv10300.CAPTUREDQTY - _iv00102.ATYALLOC),
_COUNTDATE = _iv10300.COUNTDATE,
_LOCNCODE = _iv10300.LOCNCODE
}).ToList();
//List <IV10301> _IV10301 = db.IV10301
// .Where(stl => stl.STCKCNTID.Trim().Equals(countPlan.Name))
// .ToList();
if (_IV10301 != null)
{
using (var transaction = conn.BeginTransaction())
{
foreach (var item in _IV10301)
{
cmd.CommandText =
"INSERT INTO CountPlanDetail (CountPlanId, ProductCode, Quantity, DateCreated, Warehouse) VALUES (" +
countPlan.Id + ",'" + item._ITEMNMBR.Trim() + "'," + item._CAPTUREDQTY +
",'" + item._COUNTDATE.ToShortDateString().Trim() + "','" +
item._LOCNCODE.Trim() + "')";
cmd.ExecuteNonQuery();
}
transaction.Commit();
}
}
else
{
Debug.Write(string.Format("Fail load IV10301 with STCKCNTID: {0}",
countPlan.Name));
}
}
}
else
{
return(false);
Debug.Write("Fail load CountPlan");
}
} //Cmd Sqlite
conn.Close();
} //Conn Sqlite
}
}
catch (Exception ex)
{
return(false);
Debug.Write(@"Error " + ex.Message);
}
return(true);
}
private bool syncPOP10100()
{
CultureInfo culture;
culture = CultureInfo.CreateSpecificCulture("es-PA");
Thread.CurrentThread.CurrentCulture = culture;
string query = string.Empty;
List <string> arrayNotDelete = new List <string>();
try
{
using (TSGVLEntities db = new TSGVLEntities())
{
db.Database.CommandTimeout = 0;
using (var conn = new SQLiteConnection(
@"Data Source=" + Path.Combine(AppDomain.CurrentDomain.BaseDirectory,
ConfigurationManager.AppSettings["filePath"])))
{
conn.Open();
DatabaseHelper context = new DatabaseHelper();
using (var cmd = new SQLiteCommand(conn))
{
List <POP10100> _POP10100 = db.POP10100.Where(p => p.POSTATUS == 2 && p.STATGRP == 1).ToList();
if (_POP10100 != null)
{
query = "DELETE FROM OrderTShirt";
removeData(conn, cmd, query);
using (var transaction = conn.BeginTransaction())
{
foreach (var item in _POP10100)
{
cmd.CommandText =
"INSERT INTO OrderTShirt (Code, ProviderCode, DateCreated) VALUES ('" +
item.PONUMBER.Trim() + "','" + item.VENDORID.Trim() + "','" +
item.DOCDATE.ToShortDateString().Trim() + "')";
cmd.ExecuteNonQuery();
}
transaction.Commit();
}
}
else
{
return(false);
Debug.Write("Fail load POP10100");
}
List <Models.OrderTShirt> _OrderTShirt = context.Orders.ToList();
if (_OrderTShirt != null)
{
query = "DELETE FROM OrderDetail";
removeData(conn, cmd, query);
foreach (Models.OrderTShirt orderTShirt in _OrderTShirt)
{
List <POP10110> _POP10110 = db.POP10110.Where(p => p.PONUMBER.Trim().Equals(orderTShirt.Code)).ToList();
if (_POP10110 != null)
{
using (var transaction = conn.BeginTransaction())
{
foreach (var item in _POP10110)
{
cmd.CommandText =
"INSERT INTO OrderDetail (OrderCode, ProductCode, Quantity, Value2, Value3, DateCreated, OrderTShirt_id, InitQuantity) VALUES ('" +
item.PONUMBER.Trim() + "','" +
item.ITEMNMBR.Trim().Replace("'", "''") + "'," +
item.QTYORDER.ToString().Replace(",", ".") + ",'" +
item.LOCNCODE.Trim() + "','" +
item.VNDITNUM.Trim() + "','" +
item.REQDATE.ToShortDateString().Trim() + "'," +
orderTShirt.Id + "," +
item.QTYORDER.ToString().Replace(",", ".") + ")";
cmd.ExecuteNonQuery();
}
transaction.Commit();
}
}
else
{
Debug.Write(string.Format("Fail load POP10110 with PONUMBER: {0}", orderTShirt.Code));
}
}
//var _data = (from ord in context.Orders
// join orddet in context.OrderDetails on ord.Code equals orddet.OrderCode
// where ord.Value1 == "0"
// select orddet.ProductCode).Distinct().ToArray();
//var _data = context.Products.ToList().Distinct().Select(a=> a.Code).ToArray();
////List<IV00101> _IV00101 = db.IV00101.Where(a => _data.Contains(a.ITEMNMBR.Trim())).ToList();
//var _IV00101 = (from c in db.IV00101
// where !_data.Contains(c.ITEMNMBR.Trim())
// select c).Distinct().ToList();
//if (_IV00101 != null)
//{
// //removeData(conn, cmd, "DELETE FROM Product");
// using (var transaction = conn.BeginTransaction())
// {
// foreach (var item in _IV00101)
// {
// cmd.CommandText =
// "INSERT INTO Product (Code, BarCode, Description) VALUES ('" +
// item.ITEMNMBR.Trim() + "','" + item.ITEMNMBR.Trim() + "','" +
// item.ITEMDESC.Trim().Replace("'", "''") + "')";
// cmd.ExecuteNonQuery();
// }
// transaction.Commit();
// }
//}
}
} //Cmd Sqlite
conn.Close();
} //Conn Sqlite
}
}
catch (Exception ex)
{
// conn.Close();
return(false);
Debug.Write(@"Error " + ex.Message);
}
return(true);
}
public OrderReqExtend GetOrderByCode(string code)
{
var order = new OrderReqExtend();
try
{
order = (from or in context.OrderReqs
where or.Code == code
select new OrderReqExtend()
{
Id = or.Id,
Code = or.Code,
Description = or.Description,
Status = or.Status,
ClientCode = or.ClientCode,
DateCreated = or.DateCreated,
Value1 = or.Value1,
Value2 = or.Value2,
Value3 = or.Value3,
Value4 = or.Value4,
Value5 = or.Value5,
Observation = or.Observation,
ClientName = or.ClientCode
}).FirstOrDefault();
if (order == null) //si no se encuentra en la bd intermedia busca en GP
{
using (TSGVLEntities db = new TSGVLEntities())
{
var _sop10100 = db.SOP10100.FirstOrDefault(a => a.SOPNUMBE.Trim().Equals(code));
if (_sop10100 == null)
{
var _sop30200 = db.SOP30200.FirstOrDefault(a => a.SOPNUMBE.Trim() == code);
order = new OrderReqExtend
{
Id = 1,
ClientCode = _sop30200.CUSTNAME.Trim().Replace("'", "''"),
Code = _sop30200.SOPNUMBE.Trim(),
ClientName = _sop30200.CUSTNAME.Trim().Replace("'", "''"),
DateCreated = _sop30200.DOCDATE.ToString("dd/MM/yyyy"),
Status = _sop30200.SOPSTATUS.ToString()
};
}
else
{
order = new OrderReqExtend
{
Id = 1,
ClientCode = _sop10100.CUSTNAME.Trim().Replace("'", "''"),
Code = _sop10100.SOPNUMBE.Trim(),
ClientName = _sop10100.CUSTNAME.Trim().Replace("'", "''"),
DateCreated = _sop10100.DOCDATE.ToString("dd/MM/yyyy"),
Status = _sop10100.SOPSTATUS.ToString()
};
}
}
}
}
catch (Exception ex)
{
Debug.Write("Error " + ex.InnerException.Message);
return(null);
}
return(order);
}
public OrderReqExtend GetDetailByCode(string code)
{
try
{
var order = new OrderReqExtend();
List <OrderReqDetailExtend> list = new List <OrderReqDetailExtend>();
List <OrderReqDetail> detailInsert = new List <OrderReqDetail>();
using (TSGVLEntities db = new TSGVLEntities())
{
var anyOrder = context.OrderReqs.Any(a => a.Code == code);
if (!anyOrder)
{
var master = db.SOP10100.FirstOrDefault(p => p.SOPNUMBE.Trim().Equals(code));
if (master != null)
{
var _master = new OrderReq()
{
Code = master.SOPNUMBE.Trim(),
ClientCode = master.CUSTNAME.Trim().Replace("'", "''"),
DateCreated = master.DOCDATE.ToString("dd/MM/yyyy"),
Status = master.SOPSTATUS.ToString(),
};
context.OrderReqs.Add(_master);
context.SaveChanges();
order.Id = _master.Id;
order.Code = _master.Code;
order.DateCreated = _master.DateCreated;
order.Status = _master.Status;
order.ClientCode = _master.ClientCode;
order.ClientName = _master.ClientCode;
var detail = db.SOP10200.Where(p => p.SOPNUMBE.Trim().Equals(code)).ToList();
if (detail.Any())
{
foreach (var row in detail)
{
var _detail = new OrderReqDetail()
{
OrderReqCode = row.SOPNUMBE.Trim(),
Observation = row.ITEMDESC.Trim(),
ProductCode = row.ITEMNMBR.Trim().Replace("'", "''"),
Quantity = Convert.ToInt32(row.QUANTITY),
Warehouse = row.LOCNCODE.Trim()
};
detailInsert.Add(_detail);
context.OrderReqDetails.Add(_detail);
context.SaveChanges();
}
list.AddRange(detailInsert.Select(items => new OrderReqDetailExtend()
{
OrderReqCode = items.OrderReqCode,
Observation = string.Empty,
ProductCode = items.ProductCode,
ProductCodeChanged = string.Empty,
Quantity = items.Quantity,
DateProductChanged = string.Empty,
UserUpdated = string.Empty,
ProductName = items.Observation,
ProductNameChanged = string.Empty,
QuantityChanged = 0,
Warehouse = items.Warehouse
}));
order.Detail = list;
}
}
else
{
var master30200 = db.SOP30200.FirstOrDefault(p => p.SOPNUMBE.Trim().Equals(code));
var _master2 = new OrderReq()
{
Code = master30200.SOPNUMBE.Trim(),
ClientCode = master30200.CUSTNAME.Trim().Replace("'", "''"),
DateCreated = master30200.DOCDATE.ToString("dd/MM/yyyy"),
Status = master30200.SOPSTATUS.ToString(),
};
context.OrderReqs.Add(_master2);
context.SaveChanges();
order.Id = _master2.Id;
order.Code = _master2.Code;
order.DateCreated = _master2.DateCreated;
order.Status = _master2.Status;
order.ClientCode = _master2.ClientCode;
order.ClientName = _master2.ClientCode;
var detail = db.SOP30300.Where(p => p.SOPNUMBE.Trim().Equals(code)).ToList();
if (detail.Any())
{
foreach (var row in detail)
{
var _detail = new OrderReqDetail()
{
OrderReqCode = row.SOPNUMBE.Trim(),
Observation = row.ITEMDESC.Trim(),
ProductCode = row.ITEMNMBR.Trim().Replace("'", "''"),
Quantity = Convert.ToInt32(row.QUANTITY),
Warehouse = row.LOCNCODE.Trim()
};
detailInsert.Add(_detail);
context.OrderReqDetails.Add(_detail);
context.SaveChanges();
}
list.AddRange(detailInsert.Select(items => new OrderReqDetailExtend()
{
OrderReqCode = items.OrderReqCode,
Observation = string.Empty,
ProductCode = items.ProductCode,
ProductCodeChanged = string.Empty,
Quantity = items.Quantity,
DateProductChanged = string.Empty,
UserUpdated = string.Empty,
ProductName = items.Observation,
ProductNameChanged = string.Empty,
QuantityChanged = 0,
Warehouse = items.Warehouse
}));
order.Detail = list;
}
}
}
else
{
order = GetOrderByCode(code);
var qyDetail = (from or in context.OrderReqDetails
join orp in context.OrderReqDetailProducts on new { or.OrderReqCode, or.ProductCode } equals new { orp.OrderReqCode, orp.ProductCode }
into rel
from relleft in rel.DefaultIfEmpty()
where or.OrderReqCode.Trim() == code
select new OrderReqDetailExtend
{
Id = or.Id,
OrderReqCode = or.OrderReqCode.Trim(),
Observation = or.Observation,
ProductCode = or.ProductCode,
ProductName = or.Observation,
ProductCodeChanged = relleft.ProductCodeChanged,
Quantity = or.Quantity,
DateProductChanged = relleft.DateProductChanged,
UserUpdated = relleft.UserUpdated,
QuantityChanged = relleft.Quantity == null ? 0 : relleft.Quantity,
Warehouse = or.Warehouse
}).ToList();
list.AddRange(qyDetail.Select(item => new OrderReqDetailExtend
{
Id = item.Id,
OrderReqCode = item.OrderReqCode.Trim(),
Observation = item.Observation,
ProductCode = item.ProductCode,
ProductCodeChanged = item.ProductCodeChanged,
Quantity = item.Quantity,
DateProductChanged = item.DateProductChanged,
UserUpdated = item.UserUpdated,
ProductName = item.ProductName, //db.IV00101.FirstOrDefault(a => a.ITEMNMBR.Trim() == item.ProductCode).ITEMDESC.Trim(),
ProductNameChanged = (item.ProductCodeChanged != null) ? db.IV00101.FirstOrDefault(a => a.ITEMNMBR.Trim() == item.ProductCodeChanged.ToString()).ITEMDESC.Trim() : string.Empty,
QuantityChanged = item.QuantityChanged,
Warehouse = item.Warehouse
}));
order.Detail = list;
}
}
return(order);
}
catch (Exception ex)
{
return(null);
Debug.Write("Error " + ex.InnerException.Message);
}
}
public IEnumerable <WarehouseProduct> GetListByString(string warehouseCode, string str)
{
List <WarehouseProduct> result = new List <WarehouseProduct>();
try
{
str = str.ToUpper();
using (TSGVLEntities db = new TSGVLEntities())
{
//context.Database.CommandTimeout = 0;
//var _result = context.WarehouseProduct.Where(wp => wp.WarehouseCode.Equals(warehouseCode) && wp.Quantity > 0)
// .ToList()
// .Join(context.Products, wp => wp.ProductCode, p => p.Code, (wp, p) => new { wp, p })
// .ToList()
// .Where(f => f.p.Code.ToUpper().Contains(str) || f.p.Description.ToUpper().Contains(str))
// .ToList();
//if (_result != null)
//{
// result = _result.Select(r => new WarehouseProduct()
// {
// Id = r.wp.Id,
// WarehouseCode = r.wp.WarehouseCode,
// ProductCode = r.wp.ProductCode,
// Quantity = r.wp.Quantity,
// Product = r.p
// })
// .ToList();
//}
result = (from wp in db.IV00102
join w in db.IV40700 on wp.LOCNCODE.Trim() equals w.LOCNCODE.Trim()
join p in db.IV00101 on wp.ITEMNMBR.Trim() equals p.ITEMNMBR.Trim()
where
(p.ITEMNMBR.Trim().ToUpper().Contains(str) || p.ITEMDESC.Trim().ToUpper().Contains(str)) &&
wp.LOCNCODE.Trim() == warehouseCode
select new WarehouseProduct
{
Id = 0,
WarehouseCode = wp.LOCNCODE.Trim(),
ProductCode = wp.ITEMNMBR.Trim(),
Quantity = wp.QTYONHND - wp.ATYALLOC,
Warehouse = new Warehouse
{
Id = 0,
Code = w.LOCNCODE.Trim(),
Name = w.LOCNDSCR.Trim()
},
Product = new Product
{
Id = 0,
Code = p.ITEMNMBR.Trim(),
BarCode = p.ITEMNMBR.Trim(),
Description = p.ITEMDESC.Trim()
}
}).ToList();
}
}
catch (Exception e)
{
System.Diagnostics.Debug.WriteLine("e " + e.Message);
return(null);
}
return(result);
}
public WarehouseProduct GetByCodes(string warehouseCode, string productCode)
{
try
{
WarehouseProduct _warehouseProduct = null;
using (TSGVLEntities db = new TSGVLEntities())
{
//var _result = context.WarehouseProduct.Join(context.Warehouses, wp => wp.WarehouseCode, w => w.Code,
// (wp, w) => new {wp, w})
// .Join(context.Products, x => x.wp.ProductCode, p => p.Code, (x, p) => new {x, p})
// .Where(cond => cond.x.wp.Quantity > 0)
// .FirstOrDefault(
// result => result.x.wp.WarehouseCode == warehouseCode && result.p.Code.Equals(productCode));
//if (_result != null)
//{
// _warehouseProduct = new WarehouseProduct()
// {
// Id = _result.x.wp.Id,
// WarehouseCode = _result.x.wp.WarehouseCode,
// ProductCode = _result.x.wp.ProductCode,
// Quantity = _result.x.wp.Quantity,
// Warehouse = _result.x.w,
// Product = _result.p
// };
//}
_warehouseProduct = (from wp in db.IV00102
join w in db.IV40700 on wp.LOCNCODE.Trim() equals w.LOCNCODE.Trim()
join p in db.IV00101 on wp.ITEMNMBR.Trim() equals p.ITEMNMBR.Trim()
where
wp.ITEMNMBR.Trim() == productCode && wp.LOCNCODE.Trim() == warehouseCode &&
(wp.QTYONHND - wp.ATYALLOC) > 0
select new WarehouseProduct
{
Id = 0,
WarehouseCode = wp.LOCNCODE.Trim(),
ProductCode = wp.ITEMNMBR.Trim(),
Quantity = wp.QTYONHND - wp.ATYALLOC,
Warehouse = new Warehouse
{
Id = 0,
Code = w.LOCNCODE.Trim(),
Name = w.LOCNDSCR.Trim()
},
Product = new Product
{
Id = 0,
Code = p.ITEMNMBR.Trim(),
BarCode = p.ITEMNMBR.Trim(),
Description = p.ITEMDESC.Trim()
}
}).FirstOrDefault();
}
return(_warehouseProduct);
}
catch (Exception e)
{
System.Diagnostics.Debug.WriteLine("e " + e.Message);
return(null);
}
}
private List <SumIV> GetLotIV00300(string product, string warehouse, decimal qty)
{
List <SumIV> sumiv = new List <SumIV>();
decimal tot = 0;
CultureInfo culture;
culture = CultureInfo.CreateSpecificCulture("es-PA");
Thread.CurrentThread.CurrentCulture = culture;
try
{
using (TSGVLEntities db = new TSGVLEntities())
{
db.Database.CommandTimeout = 0;
var qry = "SELECT sum(QTYRECVD - QTYSOLD - ATYALLOC) as qtyrecvd, lotnumbr, DATERECD";
qry += " FROM IV00300";
qry += " WHERE itemnmbr='" + product + "' and locncode='" + warehouse + "' and qtytype='1' and ((QTYRECVD - QTYSOLD - ATYALLOC) > 0)";
qry += " GROUP BY itemnmbr, lotnumbr, DATERECD";
var result = db.Database.SqlQuery <SumIV>(qry).ToList();
decimal sumProduct = result.Sum(x => x.qtyrecvd); // determina si hay cantidad suficiente en los lotes de los productos
if (result != null)
{
//Si la cantidad es igual a la del inventario no se genera linea e documento
if (sumProduct == qty)
{
sumiv.Add(new SumIV()
{
lotnumbr = null,
qtyrecvd = 0,
DATERECD = DateTime.Now,
IsPositive = false,
EqualQuantiy = true,
});
}
else if (qty > sumProduct) //Si la cantidad es mayor a la existente en inventario genera un ajusta positivo con la diferencia
{
sumiv.Add(new SumIV()
{
lotnumbr = null,
qtyrecvd = qty - sumProduct,
DATERECD = DateTime.Now,
IsPositive = true,
EqualQuantiy = false,
});
}
else
{
foreach (var items in result)
{
sumiv.Add(new SumIV()
{
lotnumbr = items.lotnumbr.Trim(),
qtyrecvd = (items.qtyrecvd <= qty) ? items.qtyrecvd : qty,
DATERECD = items.DATERECD,
IsPositive = false,
EqualQuantiy = false
});
if (items.qtyrecvd < qty)
{
qty = qty - items.qtyrecvd;
}
else
{
break;
}
}
var firstOrDefault = sumiv.FirstOrDefault();
if (firstOrDefault != null && string.IsNullOrEmpty(firstOrDefault.lotnumbr.Trim()))
{
sumiv = null;
}
}
}
}
}
catch (Exception ex)
{
sumiv = null;
Debug.Write(@"Error " + ex.Message);
}
return(sumiv);
}
