public inventory_position_report_import_export_model()
{
db_line_up = new inventory_position_report_import_export_db();
}
public void calculate(int month, int year, string id_warehouse)
{
var date_update = _context.inventory_position_report_import_exports
.Where(d => d.month == month && d.year == year && d.id_warehouse == id_warehouse)
.Max(d => d.date_update) ?? new DateTime(1999, 1, 1);
var date_update_current = DateTime.Now;
var first_date = new DateTime(year, month, 1);
var end_date = new DateTime(year, month, 1).AddMonths(1);
//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 < end_date)
.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, id_warehouse_position = d.id_warehouse_position }).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, id_warehouse_position = d.id_warehouse_position }).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 < end_date)
.Select(d => d.id).ToList();
for (int i = 0; i < list_phieu_nhap.Count; i += 1000)
{
list_item.AddRange(_context.inventory_item_line_ups
.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, id_warehouse_position = d.id_warehouse_position }).ToList());
}
list_item = list_item.Distinct().ToList();
var listadd = new List <inventory_position_report_import_export_db>();
for (int i = 0; i < list_item.Count; i++)
{
var ton_dau =
(_context.inventory_item_line_ups
.Where(d => d.id_item == list_item[i].id_item)
.Where(d => d.id_warehouse_position == list_item[i].id_warehouse_position)
.Where(d => _context.Fn_check_finish_approval(_context.inventory_receivings.Where(t => t.id == d.id_inventory_receiving)
.Select(t => t.id_approval).SingleOrDefault()) == true)
.Where(d => _context.inventory_receivings.Where(t => t.id == d.id_inventory_receiving)
.Select(t => t.id_warehouse).SingleOrDefault() == id_warehouse)
.Where(d => _context.inventory_receivings.Where(t => t.id == d.id_inventory_receiving)
.Select(t => t.import_date).SingleOrDefault() < first_date).Sum(d => d.quantity_unit_main) ?? 0)
-
(_context.inventory_delivery_items
.Where(d => _context.Fn_check_finish_approval(_context.inventory_deliverys.Where(t => t.id == d.id_inventory_delivery)
.Select(t => t.id_approval).SingleOrDefault()) == true)
.Where(d => _context.inventory_deliverys.Where(t => t.id == d.id_inventory_delivery)
.Select(t => t.id_warehouse).SingleOrDefault() == id_warehouse)
.Where(d => _context.inventory_deliverys.Where(t => t.id == d.id_inventory_delivery)
.Select(t => t.export_date).SingleOrDefault() < first_date).Sum(d => d.quantity_unit_main) ?? 0);
var nhap =
(_context.inventory_item_line_ups
.Where(d => d.id_item == list_item[i].id_item)
.Where(d => d.id_warehouse_position == list_item[i].id_warehouse_position)
.Where(d => _context.Fn_check_finish_approval(_context.inventory_receivings.Where(t => t.id == d.id_inventory_receiving)
.Select(t => t.id_approval).SingleOrDefault()) == true)
.Where(d => _context.inventory_receivings.Where(t => t.id == d.id_inventory_receiving)
.Select(t => t.id_warehouse).SingleOrDefault() == id_warehouse)
.Where(d => _context.inventory_receivings.Where(t => t.id == d.id_inventory_receiving)
.Select(t => t.import_date).SingleOrDefault() >= first_date &&
_context.inventory_receivings.Where(t => t.id == d.id_inventory_receiving)
.Select(t => t.import_date).SingleOrDefault() < end_date).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 => d.id_warehouse_position == list_item[i].id_warehouse_position)
.Where(d => _context.Fn_check_finish_approval(_context.inventory_deliverys.Where(t => t.id == d.id_inventory_delivery)
.Select(t => t.id_approval).SingleOrDefault()) == true)
.Where(d => _context.inventory_deliverys.Where(t => t.id == d.id_inventory_delivery)
.Select(t => t.id_warehouse).SingleOrDefault() == id_warehouse)
.Where(d => _context.inventory_deliverys.Where(t => t.id == d.id_inventory_delivery)
.Select(t => t.export_date).SingleOrDefault() >= first_date &&
_context.inventory_deliverys.Where(t => t.id == d.id_inventory_delivery)
.Select(t => t.export_date).SingleOrDefault() < end_date).Sum(d => d.quantity_unit_main) ?? 0);
var toncuoi = ton_dau + nhap - xuat;
var db = new inventory_position_report_import_export_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,
id_warehouse_position = list_item[i].id_warehouse_position,
month = month,
year = year,
quantity_beginning_stocks = ton_dau,
quantity_ending_stocks = toncuoi,
quantity_export = xuat,
quantity_import = nhap,
};
listadd.Add(db);
}
for (int i = 0; i < list_item.Count; i += 1000)
{
var delete = _context.inventory_position_report_import_exports
.Where(d => d.month == month && d.year == year &&
d.id_warehouse == id_warehouse &&
list_item.Skip(i).Take(1000)
.Select(d => d.id_item).ToList().Contains(d.id_item));
_context.inventory_position_report_import_exports.RemoveRange(delete);
_context.SaveChanges();
}
_context.inventory_position_report_import_exports.AddRange(listadd);
_context.SaveChanges();
}
