public ListDataResponse <AssetGroup> GetAssetGroups()
{
ListDataResponse <AssetGroup> response = new ListDataResponse <AssetGroup>();
try
{
var result = _appContext.AssetGroups.ToList();
if (result != null)
{
response.ListResult = result;
response.IsSuccess = true;
response.AffectedRecords = 1;
response.EndUserMessage = "Get All AssetGroup Details Successfull";
}
else
{
response.IsSuccess = true;
response.AffectedRecords = 0;
response.EndUserMessage = "No AssetGroup Details Found";
}
}
catch (Exception ex)
{
response.IsSuccess = false;
response.AffectedRecords = 0;
response.EndUserMessage = ex.InnerException == null ? ex.Message : ex.InnerException.Message;
response.Exception = ex;
}
return(response);
}
public ListDataResponse <AssetLocation> GetAssetsBySearch(SearchAsset search)
{
ListDataResponse <AssetLocation> response = new ListDataResponse <AssetLocation>();
try
{
var result = _appContext.AssetLocations.Where(x => x.LocationId == search.LocationId &&
(x.Name1.ToLower().Contains(search.searchValue.ToLower()) || x.AssetRef.Contains(search.searchValue))).ToList();
if (result.Count > 0)
{
response.ListResult = result;
response.IsSuccess = true;
response.AffectedRecords = result.Count;
response.EndUserMessage = "Get Asset Details Successfull";
}
else
{
response.IsSuccess = true;
response.AffectedRecords = 0;
response.EndUserMessage = "No Asset Details Found";
}
}
catch (Exception ex)
{
response.IsSuccess = false;
response.AffectedRecords = 0;
response.EndUserMessage = ex.InnerException == null ? ex.Message : ex.InnerException.Message;
response.Exception = ex;
}
return(response);
}
public ListDataResponse <TypeCdDmt> GetAllTypeCddmtdetails(int ClassTypeId)
{
ListDataResponse <TypeCdDmt> response = new ListDataResponse <TypeCdDmt>();
try
{
var result = _appContext.TypeCdDmts.Where(x => x.ClassTypeId == ClassTypeId).ToList();
if (result != null)
{
response.ListResult = result;
response.IsSuccess = true;
response.AffectedRecords = 1;
response.EndUserMessage = "Get All Typecddmt Details Successfull";
}
else
{
response.IsSuccess = true;
response.AffectedRecords = 0;
response.EndUserMessage = "No Typecddmt Details Found";
}
}
catch (Exception ex)
{
response.IsSuccess = false;
response.AffectedRecords = 0;
response.EndUserMessage = ex.InnerException == null ? ex.Message : ex.InnerException.Message;
response.Exception = ex;
}
return(response);
}
public ListDataResponse <SiteInfo> GetAllSite()
{
ListDataResponse <SiteInfo> response = new ListDataResponse <SiteInfo>();
try
{
var result = _appContext.SiteInfos.ToList();
var FileRepoBaseUrl = _config.Value.FileRepositoryUrl;
result.ForEach(f => f.FileLocation = string.Format("{0}/{1}/{2}{3}", FileRepoBaseUrl, f.FileLocation, f.FileName, f.FileExtention));
if (result != null)
{
response.ListResult = result;
response.IsSuccess = true;
response.AffectedRecords = 1;
response.EndUserMessage = "Get All Site Details Successfull";
}
else
{
response.IsSuccess = true;
response.AffectedRecords = 0;
response.EndUserMessage = "No Site Details Found";
}
}
catch (Exception ex)
{
response.IsSuccess = false;
response.AffectedRecords = 0;
response.EndUserMessage = ex.InnerException == null ? ex.Message : ex.InnerException.Message;
response.Exception = ex;
}
return(response);
}
public ListDataResponse <Project> GetProjectsBySiteId(int SiteId)
{
ListDataResponse <Project> response = new ListDataResponse <Project>();
try
{
var result = _appContext.Projects.Where(x => x.SiteId == SiteId).ToList();
if (result != null)
{
response.ListResult = result;
response.IsSuccess = true;
response.AffectedRecords = result.Count();
response.EndUserMessage = "Get Project Details Successfull";
}
else
{
response.IsSuccess = true;
response.AffectedRecords = 0;
response.EndUserMessage = "No Project Details Found";
}
}
catch (Exception ex)
{
response.IsSuccess = false;
response.AffectedRecords = 0;
response.EndUserMessage = ex.InnerException == null ? ex.Message : ex.InnerException.Message;
response.Exception = ex;
}
return(response);
}
public ListDataResponse <AssetLocation> GetAssetsByLocationId(int LocationId)
{
ListDataResponse <AssetLocation> response = new ListDataResponse <AssetLocation>();
try
{
var result = _appContext.AssetLocations.Where(x => x.LocationId == LocationId).ToList();
if (result != null)
{
response.ListResult = result;
response.IsSuccess = true;
response.AffectedRecords = result.Count();
response.EndUserMessage = "Get Asstes Details Successfull";
}
else
{
response.IsSuccess = true;
response.AffectedRecords = 0;
response.EndUserMessage = "No Asstes Details Found";
}
}
catch (Exception ex)
{
response.IsSuccess = false;
response.AffectedRecords = 0;
response.EndUserMessage = ex.InnerException == null ? ex.Message : ex.InnerException.Message;
response.Exception = ex;
}
return(response);
}
public ListDataResponse <GetPreventiveMaintenanceResponse> GetAllPreventiveMaintenances()
{
ListDataResponse <GetPreventiveMaintenanceResponse> response = new ListDataResponse <GetPreventiveMaintenanceResponse> ();
try
{
var result = (from pm in _appContext.PreventiveMaintenances
join wt in _appContext.LookUps on pm.WorkTechnicianId equals wt.Id
join sta in _appContext.TypeCdDmts on pm.StatusTypeId equals sta.TypeCdDmtId
join ty in _appContext.TypeCdDmts on pm.TypeOfMaintenance equals ty.TypeCdDmtId
select new GetPreventiveMaintenanceResponse
{
Id = pm.Id,
PreventiveRefId = pm.PreventiveRefId,
StartDate = pm.StartDate,
DurationInHours = pm.DurationinHours,
Details = pm.Details,
JobPlanId = pm.JobPlanId,
Priority = pm.Priority,
AssetId = _appContext.PMAssetXrefs.Where(S => S.PreventiveMaintenanceId == pm.Id).ToList(),
TechnicianId = pm.WorkTechnicianId,
TechnicianName = wt.Name1,
TypeOfMaintainanceId = pm.TypeOfMaintenance,
TypeOfMaintainanceName = ty.Description,
StatusTypeId = pm.StatusTypeId,
StatusTypeName = sta.Description,
IsActive = pm.IsActive,
CreatedBy = pm.CreatedBy,
CreatedDate = pm.CreatedDate,
UpdatedBy = pm.UpdatedBy,
UpdatedDate = pm.UpdatedDate
}).ToList();
if (result != null)
{
response.ListResult = result;
response.IsSuccess = true;
response.AffectedRecords = 1;
response.EndUserMessage = "Get All Preventive Maintenances Details Successfull";
}
else
{
response.IsSuccess = true;
response.AffectedRecords = 0;
response.EndUserMessage = "No Preventive Maintenances Found";
}
}
catch (Exception ex)
{
response.IsSuccess = false;
response.AffectedRecords = 0;
response.EndUserMessage = ex.InnerException == null ? ex.Message : ex.InnerException.Message;
response.Exception = ex;
}
return(response);
}
public ListDataResponse <GetItemResponse> GetAllItems()
{
ListDataResponse <GetItemResponse> response = new ListDataResponse <GetItemResponse>();
try
{
//var result = _appContext.Items.ToList();
var result = (from It in _appContext.Items
join l in _appContext.LookUps on It.ItemCategory equals l.Id
join p in _appContext.LookUps on It.UOMId equals p.Id
join t in _appContext.TypeCdDmts on It.ItemType equals t.TypeCdDmtId
select new GetItemResponse
{
Id = It.Id,
Name1 = It.Name1,
Name2 = It.Name2,
Description = It.Description,
ItemReference = It.ItemReference,
ItemCategory = l.Id,
CategoryName = l.Name1,
ItemTypeId = It.ItemType,
ItemTypeName = t.Description,
UOMId = p.Id,
UOMName = p.Name1,
AverageCost = It.AverageCost,
IsActive = It.IsActive,
CreatedBy = It.CreatedBy,
CreatedDate = It.CreatedDate,
UpdatedBy = It.UpdatedBy,
UpdatedDate = It.UpdatedDate,
}).ToList();
if (result != null)
{
response.ListResult = result;
response.IsSuccess = true;
response.AffectedRecords = 1;
response.EndUserMessage = "Get All Site Details Successfull";
}
else
{
response.IsSuccess = true;
response.AffectedRecords = 0;
response.EndUserMessage = "No Site Details Found";
}
}
catch (Exception ex)
{
response.IsSuccess = false;
response.AffectedRecords = 0;
response.EndUserMessage = ex.InnerException == null ? ex.Message : ex.InnerException.Message;
response.Exception = ex;
}
return(response);
}
public ListDataResponse <dynamic> GetWorkOrderStatusCount(int projectId, DateTime fromDate, DateTime toDate, int WorkType)
{
ListDataResponse <dynamic> response = new ListDataResponse <dynamic>();
try
{
var orders = (from l in _appContext.LookUps.Where(tcd => tcd.LookUpTypeId == (int)LookUP.WorkStatus)
join w in (from wo in _appContext.WorkOrders
join a in _appContext.AssetLocations on wo.AssetId equals a.Id
join lo in _appContext.Locations on a.LocationId equals lo.Id
join p in _appContext.Projects on lo.ProjectId equals p.Id
where lo.ProjectId == projectId && wo.WorkTypeId == WorkType &&
wo.CreatedDate.Date >= Convert.ToDateTime(fromDate).Date&& wo.CreatedDate.Date <= Convert.ToDateTime(toDate).Date
group wo by new
{
wo.WorkStatusId
} into grps
select new
{
WorkStatusId = grps.Key.WorkStatusId,
OrdersCount = grps.Count()
}
) on l.Id equals w.WorkStatusId into alrt
from a in alrt.DefaultIfEmpty()
group a by new
{
l.Id,
l.Name1,
} into grp
select new
{
Id = grp.Key.Id,
Description = grp.Key.Name1,
OrdersCount = grp.Max(w => w.OrdersCount)
}
).ToList();
response.ListResult = orders;
response.IsSuccess = true;
response.AffectedRecords = 0;
response.EndUserMessage = "Get All Work Orders Count Successfull";
}
catch (Exception ex)
{
response.IsSuccess = false;
response.AffectedRecords = 0;
response.EndUserMessage = ex.InnerException == null ? ex.Message : ex.InnerException.Message;
response.Exception = ex;
}
return(response);
}
public ListDataResponse <LookupDataResponse> GetAllLookUpDetails()
{
ListDataResponse <LookupDataResponse> response = new ListDataResponse <LookupDataResponse>();
try
{
//var result = _appContext.LookUps.ToList();
var result = (from e in _appContext.LookUps
join t in _appContext.TypeCdDmts
on e.LookUpTypeId equals t.TypeCdDmtId
join u in _appContext.Users
on e.CreatedBy equals u.Id
select new LookupDataResponse
{
Id = e.Id,
LookUpTypeId = e.LookUpTypeId,
Name1 = e.Name1,
Name2 = e.Name2,
Remarks = e.Remarks,
Description = t.Description,
CreatedBy = e.CreatedBy,
CreatedDate = e.CreatedDate,
UpdatedBy = e.UpdatedBy,
UpdatedDate = e.UpdatedDate,
IsActive = e.IsActive,
CreatedByUser = u.UserName,
UpdatedByUser = u.UserName
}).ToList();
if (result != null)
{
response.ListResult = result;
response.IsSuccess = true;
response.AffectedRecords = 1;
response.EndUserMessage = "Get All LookUp Details Successfull";
}
else
{
response.IsSuccess = true;
response.AffectedRecords = 0;
response.EndUserMessage = "No LookUp Details Found";
}
}
catch (Exception ex)
{
response.IsSuccess = false;
response.AffectedRecords = 0;
response.EndUserMessage = ex.InnerException == null ? ex.Message : ex.InnerException.Message;
response.Exception = ex;
}
return(response);
}
public ListDataResponse <LocationDataResponse> GetLocationDetails()
{
ListDataResponse <LocationDataResponse> response = new ListDataResponse <LocationDataResponse>();
try
{
//var result = _appContext.Locations.ToList();
var result = (from l in _appContext.Locations
join p in _appContext.Projects on l.ProjectId equals p.Id
join s in _appContext.SiteInfos on p.SiteId equals s.Id
select new LocationDataResponse
{
Id = l.Id,
SiteId = p.SiteId,
ProjectId = l.ProjectId,
Name1 = l.Name1,
Name2 = l.Name2,
LocationReference = l.LocationReference,
CreatedBy = l.CreatedBy,
CreatedDate = l.CreatedDate,
UpdatedBy = l.UpdatedBy,
UpdatedDate = l.UpdatedDate,
IsActive = l.IsActive,
SiteName1 = s.Name1,
SiteName2 = s.Name2,
ProjectName1 = p.Name1,
ProjectName2 = p.Name2
}).ToList();
if (result != null)
{
response.ListResult = result;
response.IsSuccess = true;
response.AffectedRecords = 1;
response.EndUserMessage = "Get All Location Details Successfull";
}
else
{
response.IsSuccess = true;
response.AffectedRecords = 0;
response.EndUserMessage = "No Location Details Found";
}
}
catch (Exception ex)
{
response.IsSuccess = false;
response.AffectedRecords = 0;
response.EndUserMessage = ex.InnerException == null ? ex.Message : ex.InnerException.Message;
response.Exception = ex;
}
return(response);
}
public ListDataResponse <GetPMAssetResponse> GetPMAssetsbyPMId(int Id)
{
ListDataResponse <GetPMAssetResponse> response = new ListDataResponse <GetPMAssetResponse>();
try
{
var result = (from pma in _appContext.PMAssetXrefs.Where(x => x.PreventiveMaintenanceId == Id).ToList()
join pm in _appContext.PreventiveMaintenances on pma.PreventiveMaintenanceId equals pm.Id
join a in _appContext.AssetLocations on pma.AssetId equals a.Id
join l in _appContext.Locations on a.LocationId equals l.Id
join p in _appContext.Projects on l.ProjectId equals p.Id
join s in _appContext.SiteInfos on p.SiteId equals s.Id
select new GetPMAssetResponse
{
AssetId = pma.AssetId,
AssetReference = a.AssetRef,
AssetFixedDate = a.AstFixedDate,
AssetName = a.Name1,
LocationId = a.LocationId,
LocationName = l.Name1,
ProjectId = l.ProjectId,
ProjectName = p.Name1,
SiteId = p.SiteId,
SiteName = s.Name1,
DaysApplicable = pma.DaysApplicable,
}).ToList();
if (result != null)
{
response.ListResult = result;
response.IsSuccess = true;
response.AffectedRecords = 1;
response.EndUserMessage = "Get All Assets Details Successfull";
}
else
{
response.IsSuccess = true;
response.AffectedRecords = 0;
response.EndUserMessage = "No Assets Found";
}
}
catch (Exception ex)
{
response.IsSuccess = false;
response.AffectedRecords = 0;
response.EndUserMessage = ex.InnerException == null ? ex.Message : ex.InnerException.Message;
response.Exception = ex;
}
return(response);
}
public ListDataResponse <GetJobPlanResponse> GetJobPlansByProject(int ProjectId)
{
ListDataResponse <GetJobPlanResponse> response = new ListDataResponse <GetJobPlanResponse>();
try
{
var result = (from jp in _appContext.JobPlans.Where(x => x.ProjectId == ProjectId || x.ProjectId == null).ToList()
select new GetJobPlanResponse
{
Id = jp.Id,
SiteId = jp.SiteId,
ProjectId = jp.ProjectId,
Name = jp.Name,
JobReference = jp.JobReference,
JobDescription = jp.JobDescription,
TechnicianId = jp.TechnicianId,
Tasks = _appContext.JobTasks.Where(x => x.JobPlanId == jp.Id).ToList(),
Duration = jp.Duration,
AssetGroupId = jp.AssetGroupId,
StatusTypeId = jp.StatusTypeId,
CreatedBy = jp.CreatedBy,
CreatedDate = jp.CreatedDate,
UpdatedBy = jp.UpdatedBy,
UpdatedDate = jp.UpdatedDate,
IsActive = jp.IsActive,
}).ToList();
if (result != null)
{
response.ListResult = result;
response.IsSuccess = true;
response.AffectedRecords = 1;
response.EndUserMessage = "Get All JobPlan Details Successfull";
}
else
{
response.IsSuccess = true;
response.AffectedRecords = 0;
response.EndUserMessage = "No Job Plan Details Found";
}
}
catch (Exception ex)
{
response.IsSuccess = false;
response.AffectedRecords = 0;
response.EndUserMessage = ex.InnerException == null ? ex.Message : ex.InnerException.Message;
response.Exception = ex;
}
return(response);
}
public ListDataResponse <ProjectRepositoryResposnse> GetRepositoryByProject(int ProjectId)
{
ListDataResponse <ProjectRepositoryResposnse> response = new ListDataResponse <ProjectRepositoryResposnse>();
try
{
var result = (from p in _appContext.ProjectRepositories
join t in _appContext.TypeCdDmts
on p.DocumentType equals t.TypeCdDmtId
select new ProjectRepositoryResposnse
{
RepositoryId = p.ProjectRepositoryId,
ProjectId = p.ProjectId,
FileName = p.FileName,
FileLocation = p.FileLocation,
FileExtention = p.FileExtention,
DocumentType = p.DocumentType,
FileTypeName = t.Description
}).Where(x => x.ProjectId == ProjectId).ToList();
var FileRepoBaseUrl = _config.Value.FileRepositoryUrl;
result.ForEach(f => f.FileLocation = string.Format("{0}/{1}/{2}{3}", FileRepoBaseUrl, f.FileLocation, f.FileName, f.FileExtention));
if (result != null)
{
response.ListResult = result;
response.IsSuccess = true;
response.AffectedRecords = 1;
response.EndUserMessage = "Get All Repositories Details Successfull";
}
else
{
response.IsSuccess = true;
response.AffectedRecords = 0;
response.EndUserMessage = "No Repositories Details Found";
}
}
catch (Exception ex)
{
response.IsSuccess = false;
response.AffectedRecords = 0;
response.EndUserMessage = ex.InnerException == null ? ex.Message : ex.InnerException.Message;
response.Exception = ex;
}
return(response);
}
public ListDataResponse <AssetLocation> GetAssetsByProject(int projectId, int AstGroupId)
{
ListDataResponse <AssetLocation> response = new ListDataResponse <AssetLocation>();
try
{
var result = (from a in _appContext.AssetLocations.Include(x => x.Location_Id)
join x in _appContext.Locations on a.LocationId equals x.Id
where x.ProjectId == projectId && a.AstGroupId == AstGroupId
select new AssetLocation
{
Id = a.Id,
Name1 = a.Name1,
Name2 = a.Name2,
AssetRef = a.AssetRef,
AstCounter = a.AstCounter,
AstFixedDate = a.AstFixedDate,
CreatedBy = a.CreatedBy,
CreatedDate = a.CreatedDate,
UpdatedBy = a.UpdatedBy,
UpdatedDate = a.UpdatedDate,
DaysApplicable = a.DaysApplicable
}).Distinct().ToList();
if (result != null)
{
response.ListResult = result;
response.IsSuccess = true;
response.AffectedRecords = 1;
response.EndUserMessage = "Get All Site Details Successfull";
}
else
{
response.IsSuccess = true;
response.AffectedRecords = 0;
response.EndUserMessage = "No Site Details Found";
}
}
catch (Exception ex)
{
response.IsSuccess = false;
response.AffectedRecords = 0;
response.EndUserMessage = ex.InnerException == null ? ex.Message : ex.InnerException.Message;
response.Exception = ex;
}
return(response);
}
public ListDataResponse <GetLoopUpResponse> GetStoresByProjectId(int ProjectId)
{
ListDataResponse <GetLoopUpResponse> response = new ListDataResponse <GetLoopUpResponse>();
try
{
var result = (from lp in _appContext.LookUpProjectXrefs.Where(x => x.ProjectId == ProjectId).ToList()
join l in _appContext.LookUps on lp.StoreId equals l.Id
select new GetLoopUpResponse
{
Id = l.Id,
Name1 = l.Name1,
Name2 = l.Name2,
LookUpTypeId = l.LookUpTypeId,
Remarks = l.Remarks,
IsActive = l.IsActive,
CreatedBy = l.CreatedBy,
CreatedDate = l.CreatedDate,
UpdatedBy = l.UpdatedBy,
UpdatedDate = l.UpdatedDate,
}).ToList();
if (result != null)
{
response.ListResult = result;
response.IsSuccess = true;
response.AffectedRecords = 1;
response.EndUserMessage = "Get All Store Details Successfull";
}
else
{
response.IsSuccess = true;
response.AffectedRecords = 0;
response.EndUserMessage = "No Store Details Found";
}
}
catch (Exception ex)
{
response.IsSuccess = false;
response.AffectedRecords = 0;
response.EndUserMessage = ex.InnerException == null ? ex.Message : ex.InnerException.Message;
response.Exception = ex;
}
return(response);
}
public ListDataResponse <SiteInfo> GetUserSites(string UserId)
{
ListDataResponse <SiteInfo> response = new ListDataResponse <SiteInfo>();
try
{
var result = (from p in _appContext.Projects.Include(s => s.SiteInfo_Id)
join s in _appContext.UserProjectXrefs on p.Id equals s.ProjectId
where s.UserId == UserId
select new SiteInfo
{
Id = p.SiteInfo_Id.Id,
Name1 = p.SiteInfo_Id.Name1,
Name2 = p.SiteInfo_Id.Name2,
SiteReference = p.SiteInfo_Id.SiteReference,
CreatedBy = p.SiteInfo_Id.CreatedBy,
CreatedDate = p.SiteInfo_Id.CreatedDate,
UpdatedBy = p.SiteInfo_Id.UpdatedBy,
UpdatedDate = p.SiteInfo_Id.UpdatedDate,
}).Distinct().ToList();
if (result != null)
{
response.ListResult = result;
response.IsSuccess = true;
response.AffectedRecords = 1;
response.EndUserMessage = "Get All Site Details Successfull";
}
else
{
response.IsSuccess = true;
response.AffectedRecords = 0;
response.EndUserMessage = "No Site Details Found";
}
}
catch (Exception ex)
{
response.IsSuccess = false;
response.AffectedRecords = 0;
response.EndUserMessage = ex.InnerException == null ? ex.Message : ex.InnerException.Message;
response.Exception = ex;
}
return(response);
}
public ListDataResponse <GetJobTaskResponse> GetJobTaskByJobPlanId(int JobPlanId)
{
ListDataResponse <GetJobTaskResponse> response = new ListDataResponse <GetJobTaskResponse>();
try
{
var result = (from jp in _appContext.JobTasks
join l in _appContext.LookUps on jp.AstTradeId equals l.Id
select new GetJobTaskResponse
{
Id = jp.Id,
JobPlanId = jp.JobPlanId,
Name = jp.Name,
AstTradeName = l.Name1,
AstTradeId = jp.AstTradeId,
Duration = jp.Duration,
}).Where(x => x.JobPlanId == JobPlanId).ToList();
if (result != null)
{
response.ListResult = result;
response.IsSuccess = true;
response.AffectedRecords = 1;
response.EndUserMessage = "Get All JobTask Details Successfull";
}
else
{
response.IsSuccess = true;
response.AffectedRecords = 0;
response.EndUserMessage = "No JobTask Details Found";
}
}
catch (Exception ex)
{
response.IsSuccess = false;
response.AffectedRecords = 0;
response.EndUserMessage = ex.InnerException == null ? ex.Message : ex.InnerException.Message;
response.Exception = ex;
}
return(response);
}
public ListDataResponse <GetWorkItemsResponse> GetItemsByWorkOrderId(int workOrderId)
{
ListDataResponse <GetWorkItemsResponse> response = new ListDataResponse <GetWorkItemsResponse>();
try
{
var result = (from wi in _appContext.WorkOrderItemXrefs
join i in _appContext.Items on wi.ItemId equals i.Id
join w in _appContext.WorkOrders on wi.WorkOrderId equals w.Id
select new GetWorkItemsResponse
{
Id = wi.Id,
WorkOrderId = wi.WorkOrderId,
ItemReference = i.ItemReference,
ItemId = wi.ItemId,
ItemName = i.Name1,
Quantity = wi.Quantity,
}).Where(x => x.WorkOrderId == workOrderId).ToList();
if (result != null)
{
response.ListResult = result;
response.IsSuccess = true;
response.AffectedRecords = 1;
response.EndUserMessage = "Get All Item Details Successfull";
}
else
{
response.IsSuccess = true;
response.AffectedRecords = 0;
response.EndUserMessage = "No Item Details Found";
}
}
catch (Exception ex)
{
response.IsSuccess = false;
response.AffectedRecords = 0;
response.EndUserMessage = ex.InnerException == null ? ex.Message : ex.InnerException.Message;
response.Exception = ex;
}
return(response);
}
public ListDataResponse <GetUserProjects> GetProjectsByUserIdandSiteId(string UserId, int SiteId)
{
ListDataResponse <GetUserProjects> response = new ListDataResponse <GetUserProjects>();
try
{
var result = (from up in _appContext.UserProjectXrefs
join p in _appContext.Projects on up.ProjectId equals p.Id
where up.UserId == UserId && p.SiteId == SiteId
select new GetUserProjects
{
Id = up.ProjectId,
Name1 = p.Name1,
Name2 = p.Name2,
ProjectReference = p.ProjectReference
}).ToList();
if (result != null)
{
response.ListResult = result;
response.IsSuccess = true;
response.AffectedRecords = 1;
response.EndUserMessage = "Get All Project Details Successfull";
}
else
{
response.IsSuccess = true;
response.AffectedRecords = 0;
response.EndUserMessage = "No Project Details Found";
}
}
catch (Exception ex)
{
response.IsSuccess = false;
response.AffectedRecords = 0;
response.EndUserMessage = ex.InnerException == null ? ex.Message : ex.InnerException.Message;
response.Exception = ex;
}
return(response);
}
// Asset Location Services
public ListDataResponse <GetAssetLocationResponse> GetAssetLocations()
{
ListDataResponse <GetAssetLocationResponse> response = new ListDataResponse <GetAssetLocationResponse>();
try
{
var FileRepoBaseUrl = _config.Value.FileRepositoryUrl;
var result = (from al in _appContext.AssetLocations
join l in _appContext.Locations on al.LocationId equals l.Id
join p in _appContext.Projects on l.ProjectId equals p.Id
join s in _appContext.SiteInfos on p.SiteId equals s.Id
join ag in _appContext.LookUps on al.AstGroupId equals ag.Id
join at in _appContext.LookUps on al.AstTradeId equals at.Id
select new GetAssetLocationResponse
{
Id = al.Id,
Name1 = al.Name1,
Name2 = al.Name2,
AssetLocationRef = al.AssetRef,
SiteId = p.SiteId,
SiteName1 = s.Name1,
SiteName2 = s.Name2,
ProjectId = l.ProjectId,
ProjectName1 = p.Name1,
ProjectName2 = p.Name2,
LocationId = al.LocationId,
LocationName1 = l.Name1,
LocationName2 = l.Name2,
AstTradeId = al.AstTradeId,
AstTradeName1 = at.Name1,
AstTradeName2 = at.Name2,
AstGroupId = al.AstGroupId,
AstGroupName1 = ag.Name1,
AstGroupName2 = ag.Name2,
AssetMake = al.AssetMake,
AssetCapacity = al.AssetCapacity,
AssetModel = al.AssetModel,
AssetSize = al.AssetSize,
AssetType = al.AssetType,
AstCounter = al.AstCounter,
DaysApplicable = al.DaysApplicable,
AstFixedDate = al.AstFixedDate,
IsActive = al.IsActive,
CreatedBy = al.CreatedBy,
CreatedDate = al.CreatedDate,
UpdatedBy = al.UpdatedBy,
UpdatedDate = al.UpdatedDate
}).ToList();
if (result != null)
{
response.ListResult = result;
response.IsSuccess = true;
response.AffectedRecords = 1;
response.EndUserMessage = "Get All Asset Location Details Successfull";
}
else
{
response.IsSuccess = true;
response.AffectedRecords = 0;
response.EndUserMessage = "No Asset Location Details Found";
}
}
catch (Exception ex)
{
response.IsSuccess = false;
response.AffectedRecords = 0;
response.EndUserMessage = ex.InnerException == null ? ex.Message : ex.InnerException.Message;
response.Exception = ex;
}
return(response);
}
//public ValueDataResponse<OrdersCount> GetWorkOrdersCount(int projectId, DateTime fromDate, DateTime toDate)
//{
// ValueDataResponse<OrdersCount> response = new ValueDataResponse<OrdersCount>();
// try
// {
// var orders = (from w in _appContext.WorkOrders.Include(s => s.Asset_Id).ThenInclude(b => b.Location_Id).ThenInclude(x => x.Project)
// .ThenInclude(s => s.SiteInfo_Id)
// where w.Asset_Id.Location_Id.ProjectId == projectId && w.CreatedDate.Date >= Convert.ToDateTime(fromDate).Date && w.CreatedDate.Date <= Convert.ToDateTime(toDate).Date
// select new
// {
// orders = w
// }).ToList();
// var dashboardData = new OrdersCount
// {
// NormalrdersCount = orders.Where(s => s.orders.OrderTypeId == (int)PMOrderTypes.WorkType).Count(),
// PMOrdersCount = orders.Where(s => s.orders.OrderTypeId == (int)PMOrderTypes.OrderType).Count(),
// };
// response.Result = dashboardData;
// response.IsSuccess = true;
// response.AffectedRecords = 0;
// response.EndUserMessage = "Get All Work Orders Count Successfull";
// }
// catch (Exception ex)
// {
// response.IsSuccess = false;
// response.AffectedRecords = 0;
// response.EndUserMessage = ex.InnerException == null ? ex.Message : ex.InnerException.Message;
// response.Exception = ex;
// }
// return response;
//}
public ListDataResponse <dynamic> GetWorkOrderDashboardCount(int projectId, DateTime fromDate, DateTime toDate, int WorkType)
{
ListDataResponse <dynamic> response = new ListDataResponse <dynamic>();
try
{
// var orders = (from w in _appContext.WorkOrders.Include(l => l.OrderType_Id).Include(s => s.Asset_Id).ThenInclude(b => b.Location_Id).ThenInclude(x => x.Project)
// // join l in _appContext.LookUps on w.StatusTypeId equals l.Id
// where w.Asset_Id.Location_Id.ProjectId == projectId
// && w.CreatedDate.Date >= Convert.ToDateTime(fromDate).Date && w.CreatedDate.Date <= Convert.ToDateTime(toDate).Date
// group w by new
// {
// w.OrderTypeId,
// w.OrderType_Id.Description
// } into grp
// select new
// {
// Id = grp.Key.OrderTypeId,
// OrderType = grp.Key.Description,
// OrdersCount = grp.Count()
// }
// ).ToList();
var orders = (from l in _appContext.LookUps.Where(tcd => tcd.LookUpTypeId == (int)LookUP.WorkType)
join ws in (from wo in _appContext.WorkOrders
join a in _appContext.AssetLocations on wo.AssetId equals a.Id
join lo in _appContext.Locations on a.LocationId equals lo.Id
where lo.ProjectId == projectId &&
wo.CreatedDate.Date >= Convert.ToDateTime(fromDate).Date&& wo.CreatedDate.Date <= Convert.ToDateTime(toDate).Date
group wo by new
{
wo.WorkTypeId
} into grps
select new
{
WorkTypeId = grps.Key.WorkTypeId,
OrdersCount = grps.Count()
}
) on l.Id equals ws.WorkTypeId into wl
from wo in wl.DefaultIfEmpty()
group wo by new
{
l.Id,
l.Name1,
} into grp
select new
{
Id = grp.Key.Id,
Description = grp.Key.Name1,
OrdersCount = grp.Max(s => s.OrdersCount)
}
).ToList();
//var orders = (from w in _appContext.WorkOrders.Include(s => s.Asset_Id).ThenInclude(b => b.Location_Id).ThenInclude(x => x.Project)
// .ThenInclude(s => s.SiteInfo_Id)
// where w.Asset_Id.Location_Id.ProjectId == projectId && w.OrderTypeId == WorkType
// && w.CreatedDate.Date >= Convert.ToDateTime(fromDate).Date && w.CreatedDate.Date <= Convert.ToDateTime(toDate).Date
// select new {
// orders = w
// }).ToList();
//var dashboardData = new OrdersTypeCount
//{
// OrderPlannedCount = orders.Where(s => s.orders.WorkStatusId == (int)WorkStatus.Planned).Count(),
// OrderCompletedCount = orders.Where(s => s.orders.WorkStatusId == (int)WorkStatus.Completed).Count(),
// OrderInProgressCount= orders.Where(s=>s.orders.WorkStatusId == (int)WorkStatus.InProgrss).Count(),
//};
response.ListResult = orders;
response.IsSuccess = true;
response.AffectedRecords = 0;
response.EndUserMessage = "Get All Work Orders Count Successfull";
}
catch (Exception ex)
{
response.IsSuccess = false;
response.AffectedRecords = 0;
response.EndUserMessage = ex.InnerException == null ? ex.Message : ex.InnerException.Message;
response.Exception = ex;
}
return(response);
}
public ListDataResponse <GetWorkOrderReponse> GetPMOrders()
{
ListDataResponse <GetWorkOrderReponse> response = new ListDataResponse <GetWorkOrderReponse>();
try
{
var result = (from wo in _appContext.WorkOrders.Where(x => x.OrderTypeId == 24)
join a in _appContext.AssetLocations on wo.AssetId equals a.Id
join l in _appContext.Locations on a.LocationId equals l.Id
join p in _appContext.Projects on l.ProjectId equals p.Id
join s in _appContext.SiteInfos on p.SiteId equals s.Id
join st in _appContext.LookUps on wo.StoreId equals st.Id into si
from so in si.DefaultIfEmpty()
join wty in _appContext.LookUps on wo.WorkTypeId equals wty.Id
join ws in _appContext.LookUps on wo.WorkStatusId equals ws.Id
join wf in _appContext.LookUps on wo.WorkFaultId equals wf.Id
join wt in _appContext.LookUps on wo.WorkTechnicianId equals wt.Id
join sta in _appContext.TypeCdDmts on wo.StatusTypeId equals sta.TypeCdDmtId
join pm in _appContext.PreventiveMaintenances on wo.PMProcedureId equals pm.Id
select new GetWorkOrderReponse
{
Id = wo.Id,
StartDate = wo.StartDate,
EndDate = wo.EndDate,
Reference1 = wo.Reference1,
ExtraDetails = wo.ExtraDetails,
OrderTypeId = wo.OrderTypeId,
Issue = wo.Issue,
Resolution = wo.Resolution,
PMProcedureId = wo.PMProcedureId,
PMProcedureName = pm.PreventiveRefId,
StatusTypeId = wo.StatusTypeId,
StatusTypeName = sta.Description,
AssetId = wo.AssetId,
AssetName = a.Name1,
SiteId = s.Id,
SiteName = s.Name1,
ProjectId = p.Id,
ProjectName = p.Name1,
LocationId = l.Id,
LocationName = l.Name1,
WorkStatusId = wo.WorkStatusId,
WorkStatusName = ws.Name1,
WorkTypeId = wo.WorkTypeId,
WorkTypeName = wty.Name1,
WorkFaultId = wo.WorkFaultId,
WorkFaultName = wf.Name1,
WorkTechnicianId = wo.WorkTechnicianId,
WorkTechnicianName = wt.Name1,
IsActive = wo.IsActive,
CreatedBy = wo.CreatedBy,
CreatedDate = wo.CreatedDate,
UpdatedBy = wo.UpdatedBy,
UpdatedDate = wo.UpdatedDate,
}).ToList();
if (result != null)
{
response.ListResult = result;
response.IsSuccess = true;
response.AffectedRecords = 1;
response.EndUserMessage = "Get All Work Order Details Successfull";
}
else
{
response.IsSuccess = true;
response.AffectedRecords = 0;
response.EndUserMessage = "No Work Order Details Found";
}
}
catch (Exception ex)
{
response.IsSuccess = false;
response.AffectedRecords = 0;
response.EndUserMessage = ex.InnerException == null ? ex.Message : ex.InnerException.Message;
response.Exception = ex;
}
return(response);
}
public ListDataResponse <GetPurchageResponse> GetAllPurchases()
{
ListDataResponse <GetPurchageResponse> response = new ListDataResponse <GetPurchageResponse>();
try
{
var result = (from po in _appContext.PurchageOrders
join s in _appContext.Suppliers on po.SupplierId equals s.Id
join p in _appContext.Projects on po.ProjectId equals p.Id
join t in _appContext.TypeCdDmts on po.StatusTypeId equals t.TypeCdDmtId
join lo in _appContext.LookUps on po.StoreId equals lo.Id
select new GetPurchageResponse
{
Id = po.Id,
SupplierId = po.SupplierId,
PurchaseReference = po.PurchaseReference,
SupplierName = s.Name1,
SupplierAddress = s.Address,
StatusTypeId = po.StatusTypeId,
StatusName = t.Description,
ProjectId = po.ProjectId,
ProjectName = p.Name1,
StoreId = po.StoreId,
StoreName = lo.Name1,
ArrivingDate = po.ArrivingDate,
FileExtention = po.FileExtention,
FileName = po.FileName,
FileLocation = po.FileLocation,
Remarks = po.Remarks,
BillingAddress = po.BillingAddress,
ShippingAddress = po.ShippingAddress,
IsActive = po.IsActive,
CreatedBy = po.CreatedBy,
CreatedDate = po.CreatedDate,
UpdatedBy = po.UpdatedBy,
UpdatedDate = po.UpdatedDate,
}).ToList();
var FileRepoBaseUrl = _config.Value.FileRepositoryUrl;
result.ForEach(f => f.PdfUrl = string.Format("{0}/{1}/{2}{3}", FileRepoBaseUrl, f.FileLocation, f.FileName, f.FileExtention));
if (result != null)
{
response.ListResult = result;
response.IsSuccess = true;
response.AffectedRecords = 1;
response.EndUserMessage = "Get All Purchase Details Successfull";
}
else
{
response.IsSuccess = true;
response.AffectedRecords = 0;
response.EndUserMessage = "No Purchase Order Details Found";
}
}
catch (Exception ex)
{
response.IsSuccess = false;
response.AffectedRecords = 0;
response.EndUserMessage = ex.InnerException == null ? ex.Message : ex.InnerException.Message;
response.Exception = ex;
}
return(response);
}
public ListDataResponse <GetItemsResponse> GetItemsByPurchaseId(int purchaseId)
{
ListDataResponse <GetItemsResponse> response = new ListDataResponse <GetItemsResponse>();
try
{
var res = _appContext.Inventories.GroupBy(n => new { n.PurchaseOrderId, n.ItemId })
.Select(g => new
{
PurchaseOrderId = g.Key.PurchaseOrderId,
ItemId = g.Key.ItemId,
ReceivedCost = 0,
Quantity = g.Sum(x => x.Quantity)
}).Where(x => x.PurchaseOrderId == purchaseId).ToList();
//var res = _appContext.Inventories.GroupBy(n => new { n.PurchaseOrderId, n.ItemId, n.ReceivedCost, n.Quantity })
//.Select(g => new
//{
// PurchaseOrderId = g.Key.PurchaseOrderId,
// ItemId = g.Key.ItemId,
// ReceivedCost = g.Key.ReceivedCost,
// Quantity = g.Key.Quantity
//}).Where(x => x.PurchaseOrderId == purchaseId).ToList();
var result = (from pi in _appContext.PurchageItemXrefs.Include(x => x.Item_Id).Include(x => x.Purchage_Id).Where(x => x.PurchageId == purchaseId).ToList()
join iv in res on pi.ItemId equals iv.ItemId into Details
from m in Details.DefaultIfEmpty()
select new GetItemsResponse
{
PurchaseId = pi.PurchageId,
PurchaseReference = pi.Purchage_Id.PurchaseReference,
ItemReference = pi.Item_Id.ItemReference,
ItemId = pi.ItemId,
ItemName = pi.Item_Id.Name1,
Quantity = pi.Quantity,
ExpectedCost = pi.ExpectdCost,
Comments = pi.Comments,
RemainingQuantity = m != null ? pi.Quantity - m.Quantity : 0,
ReceivedQuantity = m != null ? m.Quantity : 0,
ReceivedCost = m != null ? 0.0 : 0.0
}).Where(x => x.PurchaseId == purchaseId).Distinct().ToList();
if (result != null)
{
response.ListResult = result;
response.IsSuccess = true;
response.AffectedRecords = 1;
response.EndUserMessage = "Get All Item Details Successfull";
}
else
{
response.IsSuccess = true;
response.AffectedRecords = 0;
response.EndUserMessage = "No Item Details Found";
}
}
catch (Exception ex)
{
response.IsSuccess = false;
response.AffectedRecords = 0;
response.EndUserMessage = ex.InnerException == null ? ex.Message : ex.InnerException.Message;
response.Exception = ex;
}
return(response);
}
public ListDataResponse <GetInventoryItemsResponse> GetAllInventories()
{
ListDataResponse <GetInventoryItemsResponse> response = new ListDataResponse <GetInventoryItemsResponse>();
try
{
var result = (from In in _appContext.Inventories
join po in _appContext.PurchageOrders on In.PurchaseOrderId equals po.Id
join p in _appContext.Projects on po.ProjectId equals p.Id
join l in _appContext.LookUps on po.StoreId equals l.Id
join i in _appContext.Items on In.ItemId equals i.Id
select new GetInventoryItemsResponse
{
ItemId = In.ItemId,
ItemName = i.Name1,
Description = i.Description,
ItemReference = i.ItemReference,
PurchaseReference = po.PurchaseReference,
ProjectName = p.Name1,
StoreName = l.Name1,
Quantity = In.Quantity
}).ToList();
response.ListResult = (from r in result
group r by new
{
r.ItemId,
r.ItemName,
r.Description,
r.ItemReference,
r.ProjectName,
r.StoreName
} into grp
select new GetInventoryItemsResponse
{
ItemId = grp.Key.ItemId,
ItemName = grp.Key.ItemName,
Description = grp.Key.Description,
ItemReference = grp.Key.ItemReference,
ProjectName = grp.Key.ProjectName,
StoreName = grp.Key.StoreName,
Quantity = grp.Sum(u => u.Quantity)
}).ToList();
if (response.ListResult != null)
{
// response.ListResult = result;
response.IsSuccess = true;
response.AffectedRecords = response.ListResult.Count();
response.EndUserMessage = "Get All Inventory Details Successfull";
}
else
{
response.IsSuccess = true;
response.AffectedRecords = 0;
response.EndUserMessage = "No Inventory Details Found";
}
}
catch (Exception ex)
{
response.IsSuccess = false;
response.AffectedRecords = 0;
response.EndUserMessage = ex.InnerException == null ? ex.Message : ex.InnerException.Message;
response.Exception = ex;
}
return(response);
}
public ListDataResponse <GetProjectResponse> GetAllProject()
{
ListDataResponse <GetProjectResponse> response = new ListDataResponse <GetProjectResponse>();
try
{
var FileRepoBaseUrl = _config.Value.FileRepositoryUrl;
List <GetProjectResponse> finalResilt = new List <GetProjectResponse>();
var result = (from p in _appContext.Projects
join s in _appContext.SiteInfos on p.SiteId equals s.Id
join lp in _appContext.LookUpProjectXrefs on p.Id equals lp.ProjectId into pl
from pdata in pl.DefaultIfEmpty()
join l in _appContext.LookUps on pdata.StoreId equals l.Id
select new GetProjectResponse
{
Id = p.Id,
Name1 = p.Name1,
Name2 = p.Name2,
ProjectReference = p.ProjectReference,
SiteId = s.Id,
SiteName1 = s.Name1,
SiteName2 = s.Name2,
StoreId = _appContext.LookUpProjectXrefs.Where(S => S.ProjectId == p.Id).ToList(),
//StoreName1 = l.Name1,
//StoreName2 = l.Name2,
ProjectDetails = p.ProjectDetails,
IsActive = p.IsActive,
CreatedBy = p.CreatedBy,
CreatedDate = p.CreatedDate,
UpdatedBy = p.UpdatedBy,
UpdatedDate = p.UpdatedDate,
}).ToList();
foreach (var item in result)
{
var responce = finalResilt.Where(s => s.Id == item.Id).FirstOrDefault();
if (responce == null)
{
finalResilt.Add(item);
}
}
if (finalResilt != null)
{
response.ListResult = finalResilt;
response.IsSuccess = true;
response.AffectedRecords = finalResilt.Count();
response.EndUserMessage = "Get All Project Details Successfull";
}
else
{
response.IsSuccess = true;
response.AffectedRecords = 0;
response.EndUserMessage = "No Project Details Found";
}
}
catch (Exception ex)
{
response.IsSuccess = false;
response.AffectedRecords = 0;
response.EndUserMessage = ex.InnerException == null ? ex.Message : ex.InnerException.Message;
response.Exception = ex;
}
return(response);
}
public ListDataResponse <GetJobPlanResponse> GetJobPlans()
{
ListDataResponse <GetJobPlanResponse> response = new ListDataResponse <GetJobPlanResponse>();
try
{
var result = (from jp in _appContext.JobPlans
join p in _appContext.Projects on jp.ProjectId equals p.Id into pr
from ps in pr.DefaultIfEmpty()
join s in _appContext.SiteInfos on ps.SiteId equals s.Id into si
from so in si.DefaultIfEmpty()
join t in _appContext.LookUps on jp.TechnicianId equals t.Id
join a in _appContext.LookUps on jp.AssetGroupId equals a.Id
join st in _appContext.TypeCdDmts on jp.StatusTypeId equals st.TypeCdDmtId
select new GetJobPlanResponse
{
Id = jp.Id,
SiteId = jp.SiteId,
SiteName = so.Name1,
ProjectId = jp.ProjectId,
ProjectName = ps.Name1,
Name = jp.Name,
JobReference = jp.JobReference,
JobDescription = jp.JobDescription,
TechnicianId = jp.TechnicianId,
TechinicianName = t.Name1,
Tasks = _appContext.JobTasks.Where(x => x.JobPlanId == jp.Id).ToList(),
Duration = jp.Duration,
AssetGroupId = jp.AssetGroupId,
AssetGroupName = a.Name1,
StatusTypeId = jp.StatusTypeId,
StatusName = st.Description,
CreatedBy = jp.CreatedBy,
CreatedDate = jp.CreatedDate,
UpdatedBy = jp.UpdatedBy,
UpdatedDate = jp.UpdatedDate,
IsActive = jp.IsActive,
}).ToList();
if (result != null)
{
response.ListResult = result;
response.IsSuccess = true;
response.AffectedRecords = 1;
response.EndUserMessage = "Get All JobPlan Details Successfull";
}
else
{
response.IsSuccess = true;
response.AffectedRecords = 0;
response.EndUserMessage = "No Job Plan Details Found";
}
}
catch (Exception ex)
{
response.IsSuccess = false;
response.AffectedRecords = 0;
response.EndUserMessage = ex.InnerException == null ? ex.Message : ex.InnerException.Message;
response.Exception = ex;
}
return(response);
}