public void changeStatusMaintenance() { var listDB = _context.maintenance_schedual_system_devices.Where(d => _context.Fn_check_finish_approval(_context.maintenance_processes.Where(p => p.id_schedual_system_device == d.id).Select(t => t.id_approval).SingleOrDefault()) == true) .Select(d => d).ToList(); listDB.ForEach(item => item.status_maintenance = 2); _context.SaveChanges(); }
public void changeStatusRepair() { var ListDB = _context.maintenance_repair_orders.Where(d => _context.Fn_check_finish_approval(_context.maintenance_repair_processes.Where(p => p.id_mainteance_repair_order == d.id).Select(t => t.id_approval).SingleOrDefault()) == true) .Select(d => d).ToList(); for (int i = 0; i < ListDB.Count(); i++) { ListDB[i].repair_status = 2; } _context.SaveChanges(); }
public List <maintenance_report_status_system_model> FindAll(DateTime startDate, DateTime endDate) { List <maintenance_report_status_system_model> result = new List <maintenance_report_status_system_model>(); List <sys_factory_db> LIST_DB_FACTORY = _context.sys_factorys.Where(d => d.status_del == 1).Select(d => d).ToList(); for (int FACTORY = 0; FACTORY < LIST_DB_FACTORY.Count; FACTORY++) { List <sys_factory_line_db> LIST_DB_FACTORY_LINE = _context.sys_factory_lines.Where(d => d.id_factory == LIST_DB_FACTORY[FACTORY].id).Where(d => d.status_del == 1).Select(d => d).ToList(); for (int FACTORY_LINE = 0; FACTORY_LINE < LIST_DB_FACTORY_LINE.Count; FACTORY_LINE++) { List <string> LIST_ID_SYSTEM = _context.sys_factory_line_list_maintenance_systems.Where(d => d.id_factory_line == LIST_DB_FACTORY_LINE[FACTORY_LINE].id).Select(d => d.id_system).ToList(); List <maintenance_system_db> LIST_DB_MAINTENANCE_SYSTEM = _context.maintenance_systems.Where(d => LIST_ID_SYSTEM.Contains(d.id)).Where(d => d.status_del == 1).Select(d => d).ToList(); for (int MAINTENANCE_SYSTEM = 0; MAINTENANCE_SYSTEM < LIST_DB_MAINTENANCE_SYSTEM.Count; MAINTENANCE_SYSTEM++) { string FACTORY_NAME = _context.sys_factorys.Where(s => s.id == LIST_DB_FACTORY[FACTORY].id).Where(d => d.status_del == 1).Select(d => d.name).SingleOrDefault(); string FACTORY_LINE_NAME = _context.sys_factory_lines.Where(s => s.id == LIST_DB_FACTORY_LINE[FACTORY_LINE].id).Where(d => d.status_del == 1).Select(d => d.name).SingleOrDefault(); string MAINTENANCE_SYSTEM_NAME = _context.maintenance_systems.Where(s => s.id == LIST_DB_MAINTENANCE_SYSTEM[MAINTENANCE_SYSTEM].id).Where(d => d.status_del == 1).Select(d => d.name).SingleOrDefault(); List <maintenance_repair_process_db> LIST_DB_REPAIR_PROCESS = _context.maintenance_repair_processes.Where(s => s.id_factory == LIST_DB_FACTORY[FACTORY].id && s.id_factory_line == LIST_DB_FACTORY_LINE[FACTORY_LINE].id && s.id_maintenance_system == LIST_DB_MAINTENANCE_SYSTEM[MAINTENANCE_SYSTEM].id && s.status_del == 1).Where(d => d.create_date >= startDate && d.create_date <= endDate).Where(d => _context.Fn_check_finish_approval(_context.maintenance_repair_processes.Where(t => t.id == d.id).Select(t => t.id_approval).SingleOrDefault()) == true).Select(s => s).ToList(); List <long> LIST_ID_REPAIR_PROCESS = LIST_DB_REPAIR_PROCESS.Select(s => s.id).ToList(); List <error_solution> LIST_DB_REPAIR_ERROR_SOLUTION = _context.maintenance_repair_error_solutions.Where(d => LIST_ID_REPAIR_PROCESS.Contains(d.id_maintenance_repair_process)).Where(d => d.status_del == 1).Select(d => new error_solution { db = d, error_name = _context.maintenance_error_lists.Where(l => l.id == d.id_error && l.status_del == 1).Select(l => l.name).SingleOrDefault(), reason_name = _context.maintenance_error_reasons.Where(l => l.id == d.id_reason && l.status_del == 1).Select(l => l.reason_name).SingleOrDefault(), solution_name = _context.maintenance_error_reason_list_solutions.Where(l => l.id == d.id_solution && l.status_del == 1).Select(l => l.solution_name).SingleOrDefault(), }).ToList(); List <repair_process_people> LIST_DB_REPAIR_PEOPLE = _context.maintenance_repair_process_peoples.Where(d => LIST_ID_REPAIR_PROCESS.Contains(d.id_maintenance_repair_process)).Where(d => d.status_del == 1).Select(m => new repair_process_people { db = m, people_name = _context.users.Where(t => t.Id == m.id_people).Select(t => t.FirstName + " " + t.LastName).SingleOrDefault(), }).ToList(); List <repair_device> LIST_DB_REPAIR_REPLACED_DEVICE = _context.maintenance_repair_replaced_devices.Where(d => LIST_ID_REPAIR_PROCESS.Contains(d.id_maintenance_repair_process)).Where(d => d.status_del == 1).Select(f => new repair_device { sys_unit_name = _context.sys_units.Where(t => t.id == f.id_unit).Select(d => d.name).SingleOrDefault(), sys_item_name = _context.sys_items.Where(t => t.id == f.id_item).Select(d => d.name).SingleOrDefault(), sys_item_specification_name = _context.sys_item_specifications.Where(t => t.id == f.id_specification).Select(d => d.name).SingleOrDefault(), sys_unit_main_name = _context.sys_units.Where(t => t.id == f.id_unit_main).Select(d => d.name).SingleOrDefault(), warehouse_position_name = _context.sys_warehouse_positions.Where(techpro => techpro.id == f.id_warehouse_position).Select(d => d.name).SingleOrDefault(), db = f, }).ToList(); for (int i = 0; i < LIST_DB_REPAIR_PROCESS.Count(); i++) { double totalMinutes = (LIST_DB_REPAIR_PROCESS[i].end_time - LIST_DB_REPAIR_PROCESS[i].start_time).TotalMinutes; result.Add(new maintenance_report_status_system_model { factory_line_name = FACTORY_LINE_NAME, factory_name = FACTORY_NAME, maintenance_repair_process_db = LIST_DB_REPAIR_PROCESS[i], maintenance_system_name = MAINTENANCE_SYSTEM_NAME, list_maintenance_repair_process_people_db = LIST_DB_REPAIR_PEOPLE.Where(d => d.db.id_maintenance_repair_process == LIST_DB_REPAIR_PROCESS[i].id).ToList(), list_maintenance_repair_replaced_device_db = LIST_DB_REPAIR_REPLACED_DEVICE.Where(d => d.db.id_maintenance_repair_process == LIST_DB_REPAIR_PROCESS[i].id).ToList(), list_maintenance_repair_error_solution_db = LIST_DB_REPAIR_ERROR_SOLUTION.Where(d => d.db.id_maintenance_repair_process == LIST_DB_REPAIR_PROCESS[i].id).ToList(), totalMinutes = totalMinutes, approval = _context.Fn_get_sys_approval(LIST_DB_REPAIR_PROCESS[i].id_approval).FirstOrDefault() }); } } } } return(result); }
public dynamic FindAllDetail(int month, int year, string id_warehouse, string id_item, long id_specification, string id_warehouse_position) { var first_date = new DateTime(year, month, 1); var end_date = new DateTime(year, month, 1).AddMonths(1); var list_receiving = _context.inventory_item_line_ups .Where(d => d.id_item == id_item) .Where(d => d.id_specification == id_specification) .Where(d => d.id_warehouse_position == 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).Select(d => new inventory_report_import_export_detail_receiving() { db_line_up = d, sys_unit_name = _context.sys_units.Where(t1 => t1.id == d.id_unit_main) .Select(t => t.name).SingleOrDefault(), sys_unit_main_name = _context.sys_units.Where(t1 => t1.id == d.id_unit_main) .Select(t => t.name).SingleOrDefault(), inventory_receiving_name = _context.inventory_receivings.Where(t => t.id == d.id_inventory_receiving) .Select(t => t.name).SingleOrDefault(), import_date = _context.inventory_receivings.Where(t => t.id == d.id_inventory_receiving) .Select(t => t.import_date).SingleOrDefault() }).ToList(); list_receiving.ForEach(d => { d.business_purchase_order_name = _context.business_purchase_orders.Where(t1 => t1.id == _context.inventory_receivings.Where(t => t.id == d.db_line_up.id_inventory_receiving) .Select(t => t.id_business_purchase_order).SingleOrDefault()) .Select(t => t.name).SingleOrDefault(); d.production_order_name = _context.production_orders.Where(t1 => t1.id == _context.inventory_receivings.Where(t => t.id == d.db_line_up.id_inventory_receiving) .Select(t => t.id_production_order).SingleOrDefault()) .Select(t => t.name).SingleOrDefault(); d.sys_receiving_type_name = _context.sys_receiving_types.Where(t1 => t1.id == _context.inventory_receivings.Where(t => t.id == d.db_line_up.id_inventory_receiving) .Select(t => t.id_receiving_type).SingleOrDefault()) .Select(t => t.name).SingleOrDefault(); d.createby_name = _context.users.Where(t1 => t1.Id == _context.inventory_receivings.Where(t => t.id == d.db_line_up.id_inventory_receiving) .Select(t => t.create_by).SingleOrDefault()) .Select(t => t.FirstName + " " + t.LastName).SingleOrDefault(); }); var list_delivery = _context.inventory_delivery_items .Where(d => d.id_item == id_item) .Where(d => d.id_warehouse_position == id_warehouse_position) .Where(d => d.id_specification == id_specification) .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) .Select(d => new inventory_report_import_export_detail_delivery() { db = d, sys_unit_name = _context.sys_units.Where(t1 => t1.id == d.id_unit_main) .Select(t => t.name).SingleOrDefault(), sys_unit_main_name = _context.sys_units.Where(t1 => t1.id == d.id_unit_main) .Select(t => t.name).SingleOrDefault(), inventory_delivery_name = _context.inventory_deliverys.Where(t => t.id == d.id_inventory_delivery) .Select(t => t.name).SingleOrDefault(), export_date = _context.inventory_deliverys.Where(t => t.id == d.id_inventory_delivery) .Select(t => t.export_date).SingleOrDefault(), }).ToList(); list_delivery.ForEach(d => { d.business_sale_order_name = _context.business_sale_orders.Where(t1 => t1.id == _context.inventory_deliverys.Where(t => t.id == d.db.id_inventory_delivery) .Select(t => t.id_business_sale_order).SingleOrDefault()) .Select(t => t.name).SingleOrDefault(); d.production_order_name = _context.production_orders.Where(t1 => t1.id == _context.inventory_deliverys.Where(t => t.id == d.db.id_inventory_delivery) .Select(t => t.id_production_order).SingleOrDefault()) .Select(t => t.name).SingleOrDefault(); d.sys_delivery_type_name = _context.sys_delivery_types.Where(t1 => t1.id == _context.inventory_deliverys.Where(t => t.id == d.db.id_inventory_delivery) .Select(t => t.id_delivery_type).SingleOrDefault()) .Select(t => t.name).SingleOrDefault(); d.createby_name = _context.users.Where(t1 => t1.Id == _context.inventory_deliverys.Where(t => t.id == d.db.id_inventory_delivery) .Select(t => t.create_by).SingleOrDefault()) .Select(t => t.FirstName + " " + t.LastName).SingleOrDefault(); }); return(new { list_receiving = list_receiving, list_delivery = list_delivery }); }
public void calculate(string id_warehouse) { var date_update = _context.production_report_suppliers .Max(d => d.date_update) ?? new DateTime(1999, 1, 1); var date_update_current = DateTime.Now; //export var supply_query = _context.production_order_need_supplier_schedules .Where(d => d.status_export != 2) .Where(d => d.create_date > date_update) .Where(d => _context.Fn_check_finish_approval(_context.production_orders.Where(t => t.id == d.id_production_order) .Select(t => t.id_approval).SingleOrDefault()) == true); var purchase_query = _context.business_purchase_order_import_dates .Where(d => d.status_import != 2) .Where(d => _context.Fn_check_finish_approval(_context.business_purchase_orders.Where(t => t.id == d.id_business_purchase_order) .Select(t => t.id_approval).SingleOrDefault()) == true); var list_item = supply_query .Select(d => new { id_item = d.id_item, id_unit = d.id_unit_main }).Distinct().ToList(); var listadd = new List <production_report_supplier_db>(); for (int i = 0; i < list_item.Count; i++) { var delete = _context.production_report_suppliers .Where(d => d.id_item == list_item[i].id_item) .Where(t => t.date_production > date_update_current); _context.production_report_suppliers.RemoveRange(delete); _context.SaveChanges(); var date_supplys = supply_query.Where(d => d.id_item == list_item[i].id_item) .Select(d => d.need_supplier_date).Distinct().ToList(); if (date_supplys.Count == 0) { continue; } var current_Stock = getcurretstockofitem(list_item[i].id_item); for (int d = 0; d < date_supplys.Count; d++) { var db = new production_report_supplier_db() { date_update = date_update_current, id = 0, id_item = list_item[i].id_item, id_unit = list_item[i].id_unit, date_production = date_supplys[d] }; db.quantity_current_stock_expected = current_Stock; db.quantity_need_suply = supply_query.Where(d => d.id_item == list_item[i].id_item) .Where(t => t.need_supplier_date == date_supplys[d]).Sum(d => (decimal?)((d.quantity ?? 0) - (d.quantity_export ?? 0))) ?? 0; var totalpurchase = purchase_query.Where(t => t.estimated_import_date < date_supplys[d]) .Sum(d => (decimal?)((d.quantity ?? 0) - (d.quantity_import ?? 0))) ?? 0; var totalsuplybefore = supply_query.Where(d => d.id_item == list_item[i].id_item) .Where(t => t.need_supplier_date < date_supplys[d]).Sum(d => (decimal?)((d.quantity ?? 0) - (d.quantity_export ?? 0))) ?? 0; db.quantity_purchase_expected = totalpurchase - totalsuplybefore; if (db.quantity_purchase_expected < 0) { db.quantity_purchase_expected = 0; } if (db.quantity_purchase_expected + db.quantity_current_stock_expected - db.quantity_need_suply < 0) { db.status = 2; } else { db.status = 1; } current_Stock = current_Stock - db.quantity_need_suply; listadd.Add(db); } ; } _context.production_report_suppliers.AddRange(listadd); _context.SaveChanges(); }
public List <maintenance_report_error_solution_model> FindAll(DateTime startDate, DateTime endDate) { List <sys_factory_db> LIST_DB_FACTORY = _context.sys_factorys.Where(d => d.status_del == 1).Select(d => d).ToList(); List <maintenance_report_error_solution_model> result = new List <maintenance_report_error_solution_model>(); for (int FACTORY = 0; FACTORY < LIST_DB_FACTORY.Count; FACTORY++) { List <sys_factory_line_db> LIST_DB_FACTORY_LINE = _context.sys_factory_lines.Where(d => d.id_factory == LIST_DB_FACTORY[FACTORY].id).Where(d => d.status_del == 1).Select(d => d).ToList(); for (int FACTORY_LINE = 0; FACTORY_LINE < LIST_DB_FACTORY_LINE.Count; FACTORY_LINE++) { List <string> LIST_ID_SYSTEM = _context.sys_factory_line_list_maintenance_systems.Where(d => d.id_factory_line == LIST_DB_FACTORY_LINE[FACTORY_LINE].id).Select(d => d.id_system).ToList(); List <maintenance_system_db> LIST_DB_MAINTENANCE_SYSTEM = _context.maintenance_systems.Where(d => LIST_ID_SYSTEM.Contains(d.id)).Where(d => d.status_del == 1).Select(d => d).ToList(); for (int MAINTENANCE_SYSTEM = 0; MAINTENANCE_SYSTEM < LIST_DB_MAINTENANCE_SYSTEM.Count; MAINTENANCE_SYSTEM++) { string FACTORY_NAME = _context.sys_factorys.Where(s => s.id == LIST_DB_FACTORY[FACTORY].id).Where(d => d.status_del == 1).Select(d => d.name).SingleOrDefault(); string FACTORY_LINE_NAME = _context.sys_factory_lines.Where(s => s.id == LIST_DB_FACTORY_LINE[FACTORY_LINE].id).Where(d => d.status_del == 1).Select(d => d.name).SingleOrDefault(); string MAINTENANCE_SYSTEM_NAME = _context.maintenance_systems.Where(s => s.id == LIST_DB_MAINTENANCE_SYSTEM[MAINTENANCE_SYSTEM].id).Where(d => d.status_del == 1).Select(d => d.name).SingleOrDefault(); List <maintenance_repair_process_db> LIST_DB_REPAIR_PROCESS = _context.maintenance_repair_processes.Where(s => s.id_factory == LIST_DB_FACTORY[FACTORY].id && s.id_factory_line == LIST_DB_FACTORY_LINE[FACTORY_LINE].id && s.id_maintenance_system == LIST_DB_MAINTENANCE_SYSTEM[MAINTENANCE_SYSTEM].id && s.status_del == 1).Where(d => d.create_date >= startDate && d.create_date <= endDate).Where(d => _context.Fn_check_finish_approval(_context.maintenance_repair_processes .Where(t => t.id == d.id).Select(t => t.id_approval).SingleOrDefault()) == true).Select(s => s).ToList(); List <long> LIST_ID_REPAIR_PROCESS = LIST_DB_REPAIR_PROCESS.Select(s => s.id).ToList(); List <string> LIST_ID_MAINTENANCE_DEVICE = LIST_DB_REPAIR_PROCESS.Select(d => d.id_maintenance_system_device).Distinct().ToList(); List <maintenance_system_device_db> LIST_MAINTENANCE_DEVICE = _context.maintenance_system_devices.Where(d => LIST_ID_MAINTENANCE_DEVICE.Contains(d.id)).ToList(); for (int i = 0; i < LIST_MAINTENANCE_DEVICE.Count(); i++) { List <maintenance_repair_process_db> LIST_DB_REPAIR_PROCESS_MATCHED_DEVICE = _context.maintenance_repair_processes.Where(s => s.id_maintenance_system_device == LIST_MAINTENANCE_DEVICE[i].id).Where(s => s.id_factory == LIST_DB_FACTORY[FACTORY].id && s.id_factory_line == LIST_DB_FACTORY_LINE[FACTORY_LINE].id && s.id_maintenance_system == LIST_DB_MAINTENANCE_SYSTEM[MAINTENANCE_SYSTEM].id && s.status_del == 1).Where(d => _context.Fn_check_finish_approval(_context.maintenance_repair_processes .Where(t => t.id == d.id).Select(t => t.id_approval).SingleOrDefault()) == true).Select(s => s).ToList(); string MAINTENANCE_SYSTEM_DEVICE_NAME = _context.maintenance_system_devices.Where(s => s.id == LIST_MAINTENANCE_DEVICE[i].id).Where(d => d.status_del == 1).Select(d => d.name).SingleOrDefault(); List <error_solution> ALL_ERROR_AND_SOLUTION = new List <error_solution>(); for (int j = 0; j < LIST_DB_REPAIR_PROCESS_MATCHED_DEVICE.Count(); j++) { ALL_ERROR_AND_SOLUTION.AddRange(_context.maintenance_repair_error_solutions.Where(d => LIST_DB_REPAIR_PROCESS_MATCHED_DEVICE[j].id == d.id_maintenance_repair_process).Where(d => d.status_del == 1).Select(d => new error_solution { db = d, error_name = _context.maintenance_error_lists.Where(l => l.id == d.id_error && l.status_del == 1).Select(l => l.name).SingleOrDefault(), solution_name = _context.maintenance_error_reason_list_solutions.Where(l => l.id == d.id_solution && l.status_del == 1).Select(l => l.solution_name).SingleOrDefault(), }).ToList()); } var LIST_DB_REPAIR_ERROR_SOLUTION = ALL_ERROR_AND_SOLUTION.GroupBy(d => new { d.error_name, d.solution_name }).Select(group => new maintenance_report_error_solution_model { error_name = group.Key.error_name, solution_name = group.Key.solution_name, factory_line_name = FACTORY_LINE_NAME, factory_name = FACTORY_NAME, maintenance_system_device_name = MAINTENANCE_SYSTEM_DEVICE_NAME, maintenance_system_name = MAINTENANCE_SYSTEM_NAME, repair_date = group.OrderByDescending(t => t.db.create_date).Select(t => t.db.create_date).FirstOrDefault(), repair_date_before = (DateTime?)group.OrderByDescending(t => t.db.create_date).Skip(1).Select(t => t.db.create_date).FirstOrDefault() ?? null, repaired_times = group.Count() }).ToList(); result.AddRange(LIST_DB_REPAIR_ERROR_SOLUTION); } } } } return(result); }
public List <maintenance_report_repair_item_model> FindAll(DateTime startDate, DateTime endDate) { List <sys_factory_db> LIST_DB_FACTORY = _context.sys_factorys.Where(d => d.status_del == 1).Select(d => d).ToList(); List <maintenance_report_repair_item_model> result = new List <maintenance_report_repair_item_model>(); for (int FACTORY = 0; FACTORY < LIST_DB_FACTORY.Count; FACTORY++) { List <sys_factory_line_db> LIST_DB_FACTORY_LINE = _context.sys_factory_lines.Where(d => d.id_factory == LIST_DB_FACTORY[FACTORY].id).Where(d => d.status_del == 1).Select(d => d).ToList(); for (int FACTORY_LINE = 0; FACTORY_LINE < LIST_DB_FACTORY_LINE.Count; FACTORY_LINE++) { List <string> LIST_ID_SYSTEM = _context.sys_factory_line_list_maintenance_systems.Where(d => d.id_factory_line == LIST_DB_FACTORY_LINE[FACTORY_LINE].id).Select(d => d.id_system).ToList(); List <maintenance_system_db> LIST_DB_MAINTENANCE_SYSTEM = _context.maintenance_systems.Where(d => LIST_ID_SYSTEM.Contains(d.id)).Where(d => d.status_del == 1).Select(d => d).ToList(); for (int MAINTENANCE_SYSTEM = 0; MAINTENANCE_SYSTEM < LIST_DB_MAINTENANCE_SYSTEM.Count; MAINTENANCE_SYSTEM++) { string FACTORY_NAME = _context.sys_factorys.Where(s => s.id == LIST_DB_FACTORY[FACTORY].id).Where(d => d.status_del == 1).Select(d => d.name).SingleOrDefault(); string FACTORY_LINE_NAME = _context.sys_factory_lines.Where(s => s.id == LIST_DB_FACTORY_LINE[FACTORY_LINE].id).Where(d => d.status_del == 1).Select(d => d.name).SingleOrDefault(); string MAINTENANCE_SYSTEM_NAME = _context.maintenance_systems.Where(s => s.id == LIST_DB_MAINTENANCE_SYSTEM[MAINTENANCE_SYSTEM].id).Where(d => d.status_del == 1).Select(d => d.name).SingleOrDefault(); List <maintenance_repair_process_db> LIST_DB_REPAIR_PROCESS = _context.maintenance_repair_processes.Where(s => s.id_factory == LIST_DB_FACTORY[FACTORY].id && s.id_factory_line == LIST_DB_FACTORY_LINE[FACTORY_LINE].id && s.id_maintenance_system == LIST_DB_MAINTENANCE_SYSTEM[MAINTENANCE_SYSTEM].id && s.status_del == 1).Where(d => d.create_date >= startDate && d.create_date <= endDate).Where(d => _context.Fn_check_finish_approval(_context.maintenance_repair_processes .Where(t => t.id == d.id).Select(t => t.id_approval).SingleOrDefault()) == true).Select(s => s).ToList(); List <long> LIST_ID_REPAIR_PROCESS = LIST_DB_REPAIR_PROCESS.Select(s => s.id).ToList(); List <repair_device> LIST_DB_REPAIR_REPLACED_DEVICE = _context.maintenance_repair_replaced_devices.Where(d => LIST_ID_REPAIR_PROCESS.Contains(d.id_maintenance_repair_process)).Where(d => d.status_del == 1).Select(f => new repair_device { sys_unit_name = _context.sys_units.Where(t => t.id == f.id_unit).Select(d => d.name).SingleOrDefault(), sys_item_name = _context.sys_items.Where(t => t.id == f.id_item).Select(d => d.name).SingleOrDefault(), sys_item_specification_name = _context.sys_item_specifications.Where(t => t.id == f.id_specification).Select(d => d.name).SingleOrDefault(), sys_unit_main_name = _context.sys_units.Where(t => t.id == f.id_unit_main).Select(d => d.name).SingleOrDefault(), warehouse_position_name = _context.sys_warehouse_positions.Where(techpro => techpro.id == f.id_warehouse_position).Select(d => d.name).SingleOrDefault(), db = f, }).ToList(); for (int i = 0; i < LIST_DB_REPAIR_PROCESS.Count(); i++) { string MAINTENANCE_SYSTEM_DEVICE_NAME = _context.maintenance_system_devices.Where(s => s.id == LIST_DB_REPAIR_PROCESS[i].id_maintenance_system_device).Where(d => d.status_del == 1).Select(d => d.name).SingleOrDefault(); maintenance_repair_order_db DB_REPAIR_ORDER = _context.maintenance_repair_orders.Where(d => d.id == LIST_DB_REPAIR_PROCESS[i].id_mainteance_repair_order && d.status_del == 1).Select(s => s).SingleOrDefault(); List <repair_device> MATCHED_RECORD = LIST_DB_REPAIR_REPLACED_DEVICE.Where(d => d.db.id_maintenance_repair_process == LIST_DB_REPAIR_PROCESS[i].id).Select(s => s).ToList(); for (int j = 0; j < MATCHED_RECORD.Count(); j++) { result.Add(new maintenance_report_repair_item_model { maintenance_system_device_name = MAINTENANCE_SYSTEM_DEVICE_NAME, type_add_repair = DB_REPAIR_ORDER.type_add_repair, factory_line_name = FACTORY_LINE_NAME, factory_name = FACTORY_NAME, maintenance_repair_process_db = LIST_DB_REPAIR_PROCESS[i], maintenance_system_name = MAINTENANCE_SYSTEM_NAME, maintenance_repair_replaced_device_db = MATCHED_RECORD[j], }); } } } } } return(result); }
public void calculate(int month, int year, string id_warehouse) { var date_update = _context.inventory_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 }).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 < end_date) .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_import_export_db>(); for (int i = 0; i < list_item.Count; i++) { var ton_dau = (_context.inventory_receiving_items .Where(d => d.id_item == list_item[i].id_item) .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_receiving_items .Where(d => d.id_item == list_item[i].id_item) .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 => _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_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, 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_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_report_import_exports.RemoveRange(delete); _context.SaveChanges(); } _context.inventory_report_import_exports.AddRange(listadd); _context.SaveChanges(); }
public List <maintenance_report_machine_stopped_time_model> FindAll(DateTime startDate, DateTime endDate) { List <sys_factory_db> LIST_DB_FACTORY = _context.sys_factorys.Where(d => d.status_del == 1).Select(d => d).ToList(); List <maintenance_report_machine_stopped_time_model> result = new List <maintenance_report_machine_stopped_time_model>(); for (int FACTORY = 0; FACTORY < LIST_DB_FACTORY.Count; FACTORY++) { List <sys_factory_line_db> LIST_DB_FACTORY_LINE = _context.sys_factory_lines.Where(d => d.id_factory == LIST_DB_FACTORY[FACTORY].id).Where(d => d.status_del == 1).Select(d => d).ToList(); for (int FACTORY_LINE = 0; FACTORY_LINE < LIST_DB_FACTORY_LINE.Count; FACTORY_LINE++) { List <string> LIST_ID_SYSTEM = _context.sys_factory_line_list_maintenance_systems.Where(d => d.id_factory_line == LIST_DB_FACTORY_LINE[FACTORY_LINE].id).Select(d => d.id_system).ToList(); List <maintenance_system_db> LIST_DB_MAINTENANCE_SYSTEM = _context.maintenance_systems.Where(d => LIST_ID_SYSTEM.Contains(d.id)).Where(d => d.status_del == 1).Select(d => d).ToList(); for (int MAINTENANCE_SYSTEM = 0; MAINTENANCE_SYSTEM < LIST_DB_MAINTENANCE_SYSTEM.Count; MAINTENANCE_SYSTEM++) { string FACTORY_NAME = _context.sys_factorys.Where(s => s.id == LIST_DB_FACTORY[FACTORY].id).Where(d => d.status_del == 1).Select(d => d.name).SingleOrDefault(); string FACTORY_LINE_NAME = _context.sys_factory_lines.Where(s => s.id == LIST_DB_FACTORY_LINE[FACTORY_LINE].id).Where(d => d.status_del == 1).Select(d => d.name).SingleOrDefault(); string MAINTENANCE_SYSTEM_NAME = _context.maintenance_systems.Where(s => s.id == LIST_DB_MAINTENANCE_SYSTEM[MAINTENANCE_SYSTEM].id).Where(d => d.status_del == 1).Select(d => d.name).SingleOrDefault(); List <maintenance_system_device_db> LIST_DB_MAINTENANCE_DEVICE = _context.maintenance_system_devices.Where(d => d.id_maintenance_system == LIST_DB_MAINTENANCE_SYSTEM[MAINTENANCE_SYSTEM].id && d.status_del == 1).Select(d => d).ToList(); List <maintenance_repair_process_db> LIST_DB_REPAIR_PROCESS = _context.maintenance_repair_processes.Where(s => s.id_factory == LIST_DB_FACTORY[FACTORY].id && s.id_factory_line == LIST_DB_FACTORY_LINE[FACTORY_LINE].id && s.id_maintenance_system == LIST_DB_MAINTENANCE_SYSTEM[MAINTENANCE_SYSTEM].id && s.status_del == 1).Where(d => d.create_date >= startDate && d.create_date <= endDate).Where(d => _context.Fn_check_finish_approval(_context.maintenance_repair_processes .Where(t => t.id == d.id).Select(t => t.id_approval).SingleOrDefault()) == true).Select(s => s).ToList(); List <long> LIST_ID_REPAIR_PROCESS = LIST_DB_REPAIR_PROCESS.Select(s => s.id).ToList(); List <maintenance_process_db> LIST_DB_MAINTENANCE_PROCESS = _context.maintenance_processes.Where(s => s.id_factory == LIST_DB_FACTORY[FACTORY].id && s.id_factory_line == LIST_DB_FACTORY_LINE[FACTORY_LINE].id && s.id_maintenance_system == LIST_DB_MAINTENANCE_SYSTEM[MAINTENANCE_SYSTEM].id && s.status_del == 1).Where(d => d.create_date >= startDate && d.create_date <= endDate).Where(d => _context.Fn_check_finish_approval(_context.maintenance_repair_processes .Where(t => t.id == d.id).Select(t => t.id_approval).SingleOrDefault()) == true).Select(s => s).ToList(); List <long> LIST_ID_MAINTENANCE_PROCESS = LIST_DB_REPAIR_PROCESS.Select(s => s.id).ToList(); for (int i = 0; i < LIST_DB_MAINTENANCE_DEVICE.Count(); i++) { double totalMinuteMaintenance = LIST_DB_MAINTENANCE_PROCESS.Where(d => d.id_maintenance_system_device == LIST_DB_MAINTENANCE_DEVICE[i].id).Select(d => (d.end_time - d.start_time).TotalSeconds).Sum(d => d) / 3600; double totalMinuteRepair = LIST_DB_REPAIR_PROCESS.Where(d => d.id_maintenance_system_device == LIST_DB_MAINTENANCE_DEVICE[i].id).Select(d => (d.end_time - d.start_time).TotalSeconds).Sum(d => d) / 3600; string MAINTENANCE_SYSTEM_DEVICE_NAME = _context.maintenance_system_devices.Where(s => s.id == LIST_DB_MAINTENANCE_DEVICE[i].id).Where(d => d.status_del == 1).Select(d => d.name).SingleOrDefault(); result.Add(new maintenance_report_machine_stopped_time_model { factory_line_name = FACTORY_LINE_NAME, factory_name = FACTORY_NAME, maintenance_system_device_name = MAINTENANCE_SYSTEM_DEVICE_NAME, maintenance_system_name = MAINTENANCE_SYSTEM_NAME, totalMinute = totalMinuteMaintenance, type = "Bảo trì" }); result.Add(new maintenance_report_machine_stopped_time_model { factory_line_name = FACTORY_LINE_NAME, factory_name = FACTORY_NAME, maintenance_system_device_name = MAINTENANCE_SYSTEM_DEVICE_NAME, maintenance_system_name = MAINTENANCE_SYSTEM_NAME, totalMinute = totalMinuteRepair, type = "Sửa chữa" }); } } } } return(result); }