public void triggerbusiness(List <long?> list_id_details)
{
var inventory_receivingdetail = _context.inventory_receiving_items
.Where(d => _context.Fn_check_valid_approval(_context.inventory_receivings.Where(t => t.id == d.id_inventory_receiving)
.Select(t => t.id_approval).SingleOrDefault(), _context.inventory_receivings.Where(t => t.id == d.id_inventory_receiving)
.Select(t => t.status_del).SingleOrDefault()) == true);
for (int i = 0; i < list_id_details.Count; i++)
{
var db_business_item = _context.business_purchase_order_import_dates
.Where(d => d.id == list_id_details[i]).SingleOrDefault();
var quantity_import = inventory_receivingdetail
.Where(d => d.id_business_purchase_order_import_date == list_id_details[i])
.Sum(d => d.quantity) ?? 0;
db_business_item.quantity_import = quantity_import;
if (quantity_import < db_business_item.quantity)
{
db_business_item.status_import = 1;
}
else
{
db_business_item.status_import = 2;
}
_context.SaveChanges();
}
}
public List <production_report_productivity_model> FindAll(int month, int year)
{
var result = _context.sys_factory_lines.Where(d => d.status_del == 1)
.Select(t => new production_report_productivity_model()
{
id_factory = t.id_factory,
factory_name = _context.sys_factorys.Where(d => d.id == t.id_factory).Select(d => d.name).SingleOrDefault(),
id_factory_line = t.id,
factory_line_name = t.name,
month = month,
year = year,
numb_of_product_error = 0,
numb_of_product_finish = 0,
}).ToList();
var numDays = 0;
for (int i = 1; i <= DateTime.DaysInMonth(year, month); i++)
{
DateTime thisDay = new DateTime(year, month, i);
if (thisDay.DayOfWeek != DayOfWeek.Sunday && thisDay.DayOfWeek != DayOfWeek.Saturday)
{
numDays += 1;
}
}
result.ForEach(item =>
{
var allproduct = _context.inventory_receiving_items
.Where(d => _context.Fn_check_valid_approval(_context.inventory_receivings.Where(t => t.id == d.id_inventory_receiving).Select(t => t.id_approval).SingleOrDefault(), _context.inventory_receivings.Where(t => t.id == d.id_inventory_receiving).Select(t => t.status_del).SingleOrDefault()) == true)
.Where(d => _context.production_order_item_schedules.Where(dt => dt.id == d.id_production_order_item_schedule).Select(t => t.id_sys_factory_line).SingleOrDefault() == item.id_factory_line)
.Where(d => _context.production_order_item_schedules.Where(dt => dt.id == d.id_production_order_item_schedule).Select(t => t.working_date.Value.Month).SingleOrDefault() == month)
.Where(d => _context.production_order_item_schedules.Where(dt => dt.id == d.id_production_order_item_schedule).Select(t => t.working_date.Value.Year).SingleOrDefault() == year)
.Select(t => new
{
id_production_order_item = _context.production_order_item_schedules.Where(dt => dt.id == t.id_production_order_item_schedule).Select(t => t.id_production_order_item).SingleOrDefault(),
id_production_order_item_schedule = t.id_production_order_item_schedule,
id_item = t.id_item,
quantity = t.quantity,
enegy = (_context.sys_factory_line_item_capacitys.Where(ca => ca.id_item == t.id_item && ca.id_specification == ca.id_specification && ca.id_sys_factory_line == item.id_factory_line).Select(ca => ca.energy).FirstOrDefault() ?? 0)
})
.ToList();
item.numb_of_product_finish = allproduct.Sum(t => t.quantity);
List <production_process_db> listProductionProcess = new List <production_process_db>();
allproduct.ForEach(product =>
{
listProductionProcess.AddRange(_context.production_processes.Where(p => p.id_production_order_item == product.id_production_order_item).Where(p => p.id_production_order_item_schedule == product.id_production_order_item_schedule).Where(p => p.id_item == product.id_item).ToList());
});
item.numb_of_product_error = listProductionProcess.Sum(d => d.quantity_error);
item.total_energy_used = allproduct.Sum(t => t.enegy * t.quantity);
item.numb_of_product_plan = _context.production_order_item_schedules
.Where(t => t.status_del == 1)
.Where(t => t.id_sys_factory_line == item.id_factory_line)
.Where(t => t.working_date.Value.Month == month && t.working_date.Value.Year == year)
.Sum(t => t.quantity);
item.numDays = numDays;
});
return(result);
}
public decimal?getcurretstockofitem(string id_item)
{
var xuat = _context.inventory_delivery_items
.Where(d => d.id_item == id_item)
.Where(d => _context.Fn_check_valid_approval(_context.inventory_deliverys.Where(t => t.id == d.id_inventory_delivery)
.Select(t => t.id_approval).SingleOrDefault(), _context.inventory_deliverys.Where(t => t.id == d.id_inventory_delivery)
.Select(t => t.status_del).SingleOrDefault()) == true)
.Sum(d => d.quantity_unit_main) ?? 0;
var nhap = _context.inventory_receiving_items
.Where(d => d.id_item == id_item)
.Where(d => _context.Fn_check_valid_approval(_context.inventory_receivings.Where(t => t.id == d.id_inventory_receiving)
.Select(t => t.id_approval).SingleOrDefault(), _context.inventory_receivings.Where(t => t.id == d.id_inventory_receiving)
.Select(t => t.status_del).SingleOrDefault()) == true)
.Sum(d => d.quantity_unit_main) ?? 0;
var result = nhap - xuat;
return(result);
}
public IQueryable <inventory_production_item_schedule_model> FindAllItemIndex()
{
var inventory_receivingdetail = _context.inventory_receiving_items.Where(d => _context.Fn_check_valid_approval(_context.inventory_receivings.Where(t => t.id == d.id_inventory_receiving)
.Select(t => t.id_approval).SingleOrDefault(), _context.inventory_receivings.Where(t => t.id == d.id_inventory_receiving)
.Select(t => t.status_del).SingleOrDefault()) == true);
var result = _context.production_order_item_schedules
//.Where(d => d.status_del == 1)
.Select(d => new inventory_production_item_schedule_model()
{
sys_item_specification_name = _context.sys_item_specifications.Where(t => t.id == d.id_specification).Select(d => d.name).SingleOrDefault(),
sys_unit_name = _context.sys_units.Where(t => t.id == d.id_unit).Select(d => d.name).SingleOrDefault(),
sys_unit_main_name = _context.sys_units.Where(t => t.id == d.id_unit_main).Select(d => d.name).SingleOrDefault(),
sys_item_name = _context.sys_items.Where(t => t.id == d.id_item).Select(d => d.name).SingleOrDefault(),
production_order_name = _context.production_orders.Where(t => t.id == d.id_production_order).Select(d => d.name).SingleOrDefault(),
id_customer = _context.production_orders.Where(s => s.id.Equals(d.id_production_order)).Select(s => s.id_customer).SingleOrDefault(),
sys_customer_name = _context.sys_customers.Where(a => a.id.Equals(_context.production_orders.Where(s => s.id.Equals(d.id_production_order)).Select(s => s.id_customer).SingleOrDefault())).Select(a => a.name).SingleOrDefault(),
db = d,
list_inventory_receiving = inventory_receivingdetail
.Where(t => t.id_production_order_item_schedule == d.id)
.Select(t => new inventory_production_item_import_inventory_receiving()
{
id = t.id_inventory_receiving,
name = _context.inventory_receivings.Where(dt => dt.id == t.id_inventory_receiving)
.Select(dt => dt.name).SingleOrDefault(),
}).Distinct().ToList()
});
return(result);
}
public void calculate(string id_warehouse)
{
var date_update = _context.inventory_report_min_max_stocks
.Where(d => d.id_warehouse == id_warehouse)
.Max(d => d.date_update) ?? new DateTime(1999, 1, 1);
var date_update_current = DateTime.Now;
//export
var list_phieu_xuat = _context.inventory_deliverys
.Where(d => d.id_warehouse == id_warehouse)
.Where(d => (d.update_date ?? d.create_date) > date_update || _context.Fn_approval_last_date_action(d.id_approval) > date_update)
.Where(d => d.export_date < date_update_current)
.Select(d => d.id).ToList();
var list_item = _context.inventory_delivery_items
.Where(d => list_phieu_xuat.Take(1000).Contains(d.id_inventory_delivery))
.Select(d => new { id_item = d.id_item, id_unit = d.id_unit_main }).ToList();
for (int i = 1000; i < list_phieu_xuat.Count; i += 1000)
{
list_item.AddRange(_context.inventory_delivery_items
.Where(d => list_phieu_xuat.Skip(i).Take(1000).Contains(d.id_inventory_delivery))
.Select(d => new { id_item = d.id_item, id_unit = d.id_unit_main }).ToList());
}
//import
var list_phieu_nhap = _context.inventory_receivings
.Where(d => d.id_warehouse == id_warehouse)
.Where(d => (d.update_date ?? d.create_date) > date_update || _context.Fn_approval_last_date_action(d.id_approval) > date_update)
.Where(d => d.import_date < date_update_current)
.Select(d => d.id).ToList();
for (int i = 0; i < list_phieu_nhap.Count; i += 1000)
{
list_item.AddRange(_context.inventory_receiving_items
.Where(d => list_phieu_nhap.Skip(i).Take(1000).Contains(d.id_inventory_receiving))
.Select(d => new { id_item = d.id_item, id_unit = d.id_unit_main }).ToList());
}
list_item = list_item.Distinct().ToList();
var listadd = new List <inventory_report_min_max_stock_db>();
for (int i = 0; i < list_item.Count; i++)
{
var nhap =
(_context.inventory_receiving_items
.Where(d => d.id_item == list_item[i].id_item)
.Where(d => _context.Fn_check_valid_approval(_context.inventory_receivings.Where(t => t.id == d.id_inventory_receiving)
.Select(t => t.id_approval).SingleOrDefault(), _context.inventory_receivings.Where(t => t.id == d.id_inventory_receiving)
.Select(t => t.status_del).SingleOrDefault()) == true)
.Where(d => _context.inventory_receivings.Where(t => t.id == d.id_inventory_receiving)
.Select(t => t.id_warehouse).SingleOrDefault() == id_warehouse).Sum(d => d.quantity_unit_main) ?? 0);
var xuat = (_context.inventory_delivery_items
.Where(d => d.id_item == list_item[i].id_item)
.Where(d => _context.Fn_check_valid_approval(_context.inventory_deliverys.Where(t => t.id == d.id_inventory_delivery)
.Select(t => t.id_approval).SingleOrDefault(), _context.inventory_deliverys.Where(t => t.id == d.id_inventory_delivery)
.Select(t => t.status_del).SingleOrDefault()) == true)
.Where(d => _context.inventory_deliverys.Where(t => t.id == d.id_inventory_delivery)
.Select(t => t.id_warehouse).SingleOrDefault() == id_warehouse)
.Sum(d => d.quantity_unit_main) ?? 0);
var db = new inventory_report_min_max_stock_db()
{
date_update = date_update_current,
id = 0,
id_item = list_item[i].id_item,
id_unit = list_item[i].id_unit,
id_warehouse = id_warehouse,
quantity_current = nhap - xuat,
quantity_max = _context.sys_item_min_max_stocks.Where(t => t.id_warehouse == id_warehouse && t.id_item == list_item[i].id_item)
.Select(t => t.max_stock).SingleOrDefault() ?? 0,
quantity_min = _context.sys_item_min_max_stocks.Where(t => t.id_warehouse == id_warehouse && t.id_item == list_item[i].id_item)
.Select(t => t.min_stock).SingleOrDefault() ?? 0,
quantity_purchase_not_import = _context.business_purchase_order_import_dates
.Where(t => _context.business_purchase_orders.Where(d => d.id == t.id_business_purchase_order)
.Select(d => d.status_del).SingleOrDefault() == 1)
.Where(t => t.id_item == list_item[i].id_item).Sum(d => (decimal?)((d.quantity ?? 0) - (d.quantity_import ?? 0))) ?? 0,
};
if (db.quantity_current < db.quantity_min)
{
db.status = 1;
}
if (db.quantity_current >= db.quantity_min && db.quantity_current <= db.quantity_max)
{
db.status = 2;
}
if (db.quantity_current > db.quantity_max)
{
db.status = 3;
}
listadd.Add(db);
}
for (int i = 0; i < list_item.Count; i += 1000)
{
var delete = _context.inventory_report_min_max_stocks
.Where(d => d.id_warehouse == id_warehouse &&
list_item.Skip(i).Take(1000)
.Select(d => d.id_item).ToList().Contains(d.id_item));
_context.inventory_report_min_max_stocks.RemoveRange(delete);
_context.SaveChanges();
}
_context.inventory_report_min_max_stocks.AddRange(listadd);
_context.SaveChanges();
}