public void calculate(int month, int year, string id_warehouse) { var date_update = _context.inventory_position_report_import_export_specifications .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 => d.type_add == 2) .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, id_specification = d.id_specification, 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 => d.type_add == 2) .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, id_specification = d.id_specification, 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 => d.type_add == 2) .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, id_specification = d.id_specification, id_warehouse_position = d.id_warehouse_position }).ToList()); } list_item = list_item.Distinct().ToList(); var listadd = new List <inventory_position_report_import_export_specification_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_specification == list_item[i].id_specification) .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 => d.id_item == list_item[i].id_item) .Where(d => d.id_specification == list_item[i].id_specification) .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).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_specification == list_item[i].id_specification) .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_specification == list_item[i].id_specification) .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_specification_db() { date_update = date_update_current, id = 0, id_item = list_item[i].id_item, id_unit = list_item[i].id_unit, id_specification = list_item[i].id_specification, id_warehouse_position = list_item[i].id_warehouse_position, id_warehouse = id_warehouse, 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_export_specifications .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_export_specifications.RemoveRange(delete); _context.SaveChanges(); } _context.inventory_position_report_import_export_specifications.AddRange(listadd); _context.SaveChanges(); }
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(); }