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);
}