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