public List <UserSelectedIds> getEquipmentIdAndDateByCondition(int PageNo, int PageSize, UserSelectedIds SelectedIds, int userId) { var customerIds = new CustomerManagement().getListOfActiveCustomersForLoggedInUser(userId).Select(m => m.customerId); PageNo = PageNo <= 1 ? 0 : PageNo - 1; using (var _context = new DAL.UndercarriageContext()) { return(_context.EQUIPMENTs.Join(_context.CRSF, equipment => equipment.crsf_auto, jobsite => jobsite.crsf_auto, (equipment, jobsite) => new UserSelectedIds { CustomerId = (int)jobsite.customer_auto, EquipmentId = (int)equipment.equipmentid_auto, FamilyId = equipment.LU_MMTA.type_auto, JobSiteId = (int)jobsite.crsf_auto, MakeId = equipment.LU_MMTA.make_auto, ModelId = equipment.LU_MMTA.model_auto, LastReadingDate = equipment.last_reading_date ?? DateTime.MinValue }).Where(joined => customerIds.Any(cId => joined.CustomerId == cId) && (SelectedIds.CustomerId != 0 ? joined.CustomerId == SelectedIds.CustomerId : true) && (SelectedIds.JobSiteId != 0 ? joined.JobSiteId == SelectedIds.JobSiteId : true) && (SelectedIds.EquipmentId != 0 ? joined.EquipmentId == SelectedIds.EquipmentId : true) && (SelectedIds.MakeId != 0 ? joined.MakeId == SelectedIds.MakeId : true) && (SelectedIds.FamilyId != 0 ? joined.FamilyId == SelectedIds.FamilyId : true) && (SelectedIds.ModelId != 0 ? joined.ModelId == SelectedIds.ModelId : true)).OrderByDescending(m => m.LastReadingDate).Skip(PageNo * PageSize).Take(PageSize).ToList()); } }
public List <GETImplementInventoryVM> returnImplementInventory(long customer, long jobsite, int make, long type, string equipmentSerial, string equipmentUnit, string implementSerial, int status, string userId) { List <GETImplementInventoryVM> result = new List <GETImplementInventoryVM>(); // Parse the user id. long lUserId = long.TryParse(userId, out lUserId) ? lUserId : 0; // Set the initial condition and LTD for an implement. int INITIAL_CONDITION = 0; // 0% worn int INITIAL_LTD = 0; // Filter by customers that the user has permissions to view. List <CustomerIdAndNameDataSet> customers = new List <CustomerIdAndNameDataSet>(); CustomerManagement customerManagement = new CustomerManagement(); customers = customerManagement.getListOfCustomersForLoggedInUser(lUserId); // Filter by customer dropdown list. long[] customerIds = new long[customers.Count]; for (int i = 0; i < customers.Count; i++) { if ((customer == customers[i].customerId) || (customer == 0)) { customerIds[i] = customers[i].customerId; } } // Parse the status bit positions. int status_OnEquipment = (status & 0x01); int status_AwaitingRepairs = (status & 0x02) >> 1; int status_UndergoingRepairs = (status & 0x04) >> 2; int status_ReadyForUse = (status & 0x08) >> 3; int status_Scrapped = (status & 0x10) >> 4; // These statuses map to the values in GET_INVENTORY_STATUS table. // Needs to be made dynamic eventually. int STATUS_ON_EQUIPMENT = (int)GETInterfaces.Enum.InventoryStatus.On_Equipment; int STATUS_AWAITING_REPAIR = (int)GETInterfaces.Enum.InventoryStatus.Awaiting_Repair; int STATUS_UNDERGOING_REPAIR = (int)GETInterfaces.Enum.InventoryStatus.Undergoing_Repair; int STATUS_READY_FOR_USE = (int)GETInterfaces.Enum.InventoryStatus.Ready_for_Use; int STATUS_SCRAPPED = (int)GETInterfaces.Enum.InventoryStatus.Scrapped; using (var dataEntities = new DAL.GETContext()) { // Find the implements that are already installed on equipment. var statusDescription_OnEquipment = dataEntities.GET_INVENTORY_STATUS.Find(STATUS_ON_EQUIPMENT).status_desc; var inventoryOnEquipment = (from g in dataEntities.GET join l in dataEntities.LU_IMPLEMENT on g.implement_auto equals l.implement_auto join e in dataEntities.EQUIPMENTs on g.equipmentid_auto equals e.equipmentid_auto join j in dataEntities.CRSF on e.crsf_auto equals j.crsf_auto join c in dataEntities.CUSTOMERs on j.customer_auto equals c.customer_auto join m in dataEntities.MAKE on g.make_auto equals m.make_auto where customerIds.Contains(c.customer_auto) && ((j.crsf_auto == jobsite) || (jobsite == 0)) && ((m.make_auto == make) || (make == 0)) && ((l.implement_auto == type) || (type == 0)) && ((g.impserial.ToString().Contains(implementSerial)) || (implementSerial == null) || (implementSerial == "")) && ((e.serialno.ToString().Contains(equipmentSerial)) || (equipmentSerial == null) || (equipmentSerial == "")) && ((e.unitno.ToString().Contains(equipmentUnit)) || (equipmentUnit == null) || (equipmentUnit == "")) && ((g.on_equipment == true) && (status_OnEquipment == 1)) select new { g.get_auto, condition = INITIAL_CONDITION, c.cust_name, j.site_name, m.makedesc, l.implementdescription, g.impserial, ltd = INITIAL_LTD, status_desc = statusDescription_OnEquipment, e.serialno, e.unitno }); // Find implements that are in inventory (status != onEquipment). var inventoryList = (from gi in dataEntities.GET_INVENTORY join gis in dataEntities.GET_INVENTORY_STATUS on gi.status_auto equals gis.status_auto join gw in dataEntities.GET_WORKSHOP on gi.workshop_auto equals gw.workshop_auto into gw_gi from gw2 in gw_gi.DefaultIfEmpty() join gr in dataEntities.GET_REPAIRER on gw2.repairer_auto equals gr.repairer_auto into gr_gw2 from gr2 in gr_gw2.DefaultIfEmpty() join g in dataEntities.GET on gi.get_auto equals g.get_auto join l in dataEntities.LU_IMPLEMENT on g.implement_auto equals l.implement_auto join j in dataEntities.CRSF on gi.jobsite_auto equals j.crsf_auto join c in dataEntities.CUSTOMERs on j.customer_auto equals c.customer_auto join m in dataEntities.MAKE on g.make_auto equals m.make_auto where customerIds.Contains(c.customer_auto) && ((j.crsf_auto == jobsite) || (jobsite == 0)) && ((m.make_auto == make) || (make == 0)) && ((l.implement_auto == type) || (type == 0)) && ((equipmentSerial == null) || (equipmentSerial.Trim() == "")) && ((equipmentUnit == null) || (equipmentUnit.Trim() == "")) && ((g.impserial.ToString().Contains(implementSerial)) || (implementSerial == null) || (implementSerial == "")) && ( ((gi.status_auto == STATUS_AWAITING_REPAIR) && (g.on_equipment == false) && (status_AwaitingRepairs == 1)) || ((gi.status_auto == STATUS_UNDERGOING_REPAIR) && (g.on_equipment == false) && (status_UndergoingRepairs == 1)) || ((gi.status_auto == STATUS_READY_FOR_USE) && (g.on_equipment == false) && (status_ReadyForUse == 1)) || ((gi.status_auto == STATUS_SCRAPPED) && (g.on_equipment == false) && (status_Scrapped == 1)) ) select new { g.get_auto, condition = INITIAL_CONDITION, c.cust_name, j.site_name, m.makedesc, l.implementdescription, g.impserial, ltd = INITIAL_LTD, gis.status_desc, serialno = "", unitno = "", workshopName = (gw2 != null ? gw2.name : ""), repairerName = (gr2 != null ? gr2.name : "") }); result = inventoryOnEquipment.Select( i => new GETImplementInventoryVM { get_auto = i.get_auto, condition = i.condition, customer = i.cust_name, jobsite = i.site_name, make = i.makedesc, type = i.implementdescription, serial_no = i.impserial, ltd = i.ltd, status = i.status_desc, equipment_serialno = (i.serialno != null ? i.serialno : ""), equipment_unitno = (i.unitno != null ? i.unitno : ""), repairer = "", workshop = "" }).ToList(); result.AddRange( inventoryList.Select( i => new GETImplementInventoryVM { get_auto = i.get_auto, condition = i.condition, customer = i.cust_name, jobsite = i.site_name, make = i.makedesc, type = i.implementdescription, serial_no = i.impserial, ltd = i.ltd, status = i.status_desc, equipment_serialno = (i.serialno != null ? i.serialno : ""), equipment_unitno = (i.unitno != null ? i.unitno : ""), repairer = i.repairerName, workshop = i.workshopName }).ToList() ); foreach (var item in result) { // Find the LTD for each implement. var eventRecord = dataEntities.GET_EVENTS_IMPLEMENT .Where(s => s.get_auto == item.get_auto) .OrderByDescending(m => m.implement_events_auto) .FirstOrDefault(); if (eventRecord != null) { item.ltd = eventRecord.ltd; } // Find the remaining life for each implement. var inspectionRecord = dataEntities.GET_IMPLEMENT_INSPECTION .Where(g => g.get_auto == item.get_auto) .OrderByDescending(h => h.inspection_auto) .FirstOrDefault(); if (inspectionRecord != null) { item.condition = inspectionRecord.eval; } } } return(result); }