public static List <Jurisdiction> GetJurisdictionByName(string JurisdictionName)
{
using (var ctx = new EvolutionRepository.Models.EvolutionDBContext())
{
return(ctx.Jurisdictions.AsNoTracking().Where(x => x.JurisdictionName == JurisdictionName).ToList());
}
}
public static List <Jurisdiction> GetJurisdictionByType(string JurisdictionType)
{
using (var ctx = new EvolutionRepository.Models.EvolutionDBContext())
{
return(ctx.Jurisdictions.Where(x => x.JurisdictionTypesJurisdictionType.JurisdictionTypeName == JurisdictionType).ToList());
}
}
public static bool AddArea(EvolutionRepository.Models.Area newArea)
{
var Result = false;
try
{
using (var ctx = new EvolutionRepository.Models.EvolutionDBContext())
{
ctx.Database.ExecuteSqlCommand("Evo_Areas_Insert @OrganisationID, @AreaName, @Telephone, @EmergencyNumber, @Mobile, @EmployeeID, @Total_staff_Allocted, @Total_Engineers_Allocated, @Total_Vehicles_Allocated",
new SqlParameter("@OrganisationID ", newArea.OrganisationID),
new SqlParameter("@AreaName ", newArea.AreaName),
new SqlParameter("@Telephone ", newArea.Telephone),
new SqlParameter("@EmergencyNumber ", newArea.EmergencyNumber),
new SqlParameter("@Mobile ", newArea.Mobile),
new SqlParameter("@EmployeeID ", newArea.EmployeeID),
new SqlParameter("@Total_staff_Allocted ", null),
new SqlParameter("@Total_Engineers_Allocated", null),
new SqlParameter("@Total_Vehicles_Allocated ", null));
Result = true;
}
}
catch (Exception)
{
Result = false;
}
return(Result);
}
public static List <Jurisdiction> GetJurisdictions()
{
using (var ctx = new EvolutionRepository.Models.EvolutionDBContext())
{
return(ctx.Jurisdictions.AsNoTracking().ToList());
}
}
public static EvolutionRepository.ManualModels.GetOrganisationCommListResult GetAllOrganisationCommsByID(int organisationID, int skip, int take)
{
using (var ctx = new EvolutionRepository.Models.EvolutionDBContext())
{
int TotalInstancesWithoutSkipTake;
ctx.Configuration.ProxyCreationEnabled = false;
var orgComList = ctx.OrganisationComms.Where(p => p.OrganisationID == organisationID).ToList();
if (orgComList != null)
{
TotalInstancesWithoutSkipTake = orgComList.Count();
orgComList = orgComList.OrderBy(x => x.OrganisationCommID).Skip(skip).Take(take).ToList();
var orgComManualList = new List <EvolutionRepository.ManualModels.OrganisationCommSimple>();
foreach (var item in orgComList)
{
orgComManualList.Add(new EvolutionRepository.ManualModels.OrganisationCommSimple
{
Address = item.Address,
AddressPrefix = item.AddressPrefix,
OrganisationCommID = item.OrganisationCommID,
OrganisationCommTypeID = item.OrganisationCommTypeID,
OrganisationCommTypeName = ctx.OrganisationCommTypes.Where(p => p.OrganisationCommTypeID == item.OrganisationCommTypeID).FirstOrDefault().OrganisationCommTypeName,
OrganisationID = item.OrganisationID,
OrganisationName = ctx.Organisations.Where(p => p.OrganisationID == item.OrganisationID).FirstOrDefault().OrganisationName
});
}
return(new EvolutionRepository.ManualModels.GetOrganisationCommListResult(orgComManualList.ToArray(), TotalInstancesWithoutSkipTake));
}
return(null);
}
}
public static EvolutionRepository.Models.Area GetAreaByID(int areaID)
{
using (var ctx = new EvolutionRepository.Models.EvolutionDBContext())
{
return(ctx.Areas.AsNoTracking().Where(p => p.AreaID == areaID).FirstOrDefault());
}
}
public static bool InsertOrganisationComm(OrganisationComm newOrgCom)
{
var Result = false;
try
{
using (var ctx = new EvolutionRepository.Models.EvolutionDBContext())
{
ctx.Database.ExecuteSqlCommand("Evo_OrganisationComm_Insert @OrganisationID, @OrganisationCommTypeID, @Address, @AddressPrefix, @CommsHoneymoon",
new SqlParameter("@OrganisationID ", newOrgCom.OrganisationID),
new SqlParameter("@OrganisationCommTypeID ", newOrgCom.OrganisationCommTypeID),
new SqlParameter("@Address ", newOrgCom.Address),
new SqlParameter("@AddressPrefix ", newOrgCom.AddressPrefix),
new SqlParameter("@CommsHoneymoon ", newOrgCom.CommsHoneymoon));
Result = true;
}
}
catch (Exception)
{
Result = false;
}
return(Result);
}
public static bool CreateAudit(Audit audit)
{
bool result = false;
try
{
using (var ctx = new EvolutionRepository.Models.EvolutionDBContext())
{
ctx.Database.ExecuteSqlCommand("Evo_Audits_Insert @AuditType, @AuditDate, @OldValue, @NewValue, @IPAddress, @UserAgentString, @ChangeByUserID, @ChangeByFullName",
new SqlParameter("@AuditType", audit.AuditType),
new SqlParameter("@AuditDate", audit.AuditDate),
new SqlParameter("@OldValue", audit.OldValue),
new SqlParameter("@NewValue", audit.NewValue),
new SqlParameter("@IPAddress", audit.IPAddress),
new SqlParameter("@UserAgentString", audit.UserAgentString),
new SqlParameter("@ChangeByUserID", audit.ChangeByUserID),
new SqlParameter("@ChangeByFullName", audit.ChangeByFullName)
);
}
result = true;
}
catch (Exception ex) {
throw;
}
return(result);
}
public static List <Jurisdiction> GetJurisdictionByParent(int JurisdictionParentID)
{
using (var ctx = new EvolutionRepository.Models.EvolutionDBContext())
{
return(ctx.Jurisdictions.AsNoTracking().Where(x => x.JurisdictionParentID == JurisdictionParentID).ToList());
}
}
public static List <EvolutionRepository.Models.Employee> GetManagersForAnOrganisation(int organisationID)
{
using (var ctx = new EvolutionRepository.Models.EvolutionDBContext())
{
ctx.Configuration.ProxyCreationEnabled = false;
return(ctx.Employees.AsNoTracking().Where(p => p.OrganisationID == organisationID).ToList());
}
}
public static List <OrganisationCommType> GetAllOrganisationCommTypes()
{
using (var ctx = new EvolutionRepository.Models.EvolutionDBContext())
{
ctx.Configuration.ProxyCreationEnabled = false;
return(ctx.OrganisationCommTypes.AsNoTracking().ToList());
}
}
public static List <DeductionType> GetAllDeductionTypes()
{
using (var ctx = new EvolutionRepository.Models.EvolutionDBContext())
{
ctx.Configuration.ProxyCreationEnabled = false;
return(ctx.DeductionTypes.ToList());
}
}
public static EvolutionRepository.Models.OrganisationComm GetOrganisationCommByID(int organisationCommID)
{
using (var ctx = new EvolutionRepository.Models.EvolutionDBContext())
{
ctx.Configuration.ProxyCreationEnabled = false;
return(ctx.OrganisationComms.AsNoTracking().FirstOrDefault(x => x.OrganisationCommID == organisationCommID));
}
}
public static List <Audit> GetAuditsByType(List <ActionType> actionTypes, BusinessUnits businessUnit)
{
using (var ctx = new EvolutionRepository.Models.EvolutionDBContext())
{
ctx.Configuration.ProxyCreationEnabled = false;
return(ctx.Audits.ToList());
}
}
public static Audit GetAudit(int AuditID)
{
using (var ctx = new EvolutionRepository.Models.EvolutionDBContext())
{
ctx.Configuration.ProxyCreationEnabled = false;
return(ctx.Audits.FirstOrDefault(x => x.AuditID == AuditID));
}
}
public static User GetUser(string Username)
{
User user = new User();
try
{
using (var ctx = new EvolutionRepository.Models.EvolutionDBContext())
{
user = ctx.Users.FirstOrDefault(x => x.UserName == Username);
}
}
catch (Exception ex)
{
throw ex;
}
return(user);
}
public static List <EvolutionRepository.ManualModels.OrganisationGrid> GetOrganisations(EvolutionRepository.ManualModels.SearchOrganisationsParams searchParams, EvolutionRepository.Models.User LoggedInUser, out int TotalInstancesWithoutSkipTake)
{
List <EvolutionRepository.ManualModels.OrganisationGrid> organisations = new List <EvolutionRepository.ManualModels.OrganisationGrid>();
using (EvolutionRepository.Models.EvolutionDBContext ctx = new EvolutionRepository.Models.EvolutionDBContext())
{
try
{
//Get records from stored procedure with Skip ad Take applied
organisations = ctx.Database.SqlQuery <EvolutionRepository.ManualModels.OrganisationGrid>(
"exec uspGetOrganisation @parentorganisation, @organisation, @childto, @type, @subtype, @jurisdiction, @skip, @take",
new SqlParameter("@parentorganisation", Convert.ToInt32(searchParams.parentorganisation)),
new SqlParameter("@organisation", searchParams.organisation),
new SqlParameter("@childto", Convert.ToInt32(searchParams.childto)),
new SqlParameter("@type", Convert.ToInt32(searchParams.type)),
new SqlParameter("@subtype", Convert.ToInt32(searchParams.subtype)),
new SqlParameter("@jurisdiction", Convert.ToInt32(searchParams.jurisdiction)),
new SqlParameter("@skip", searchParams.skip),
new SqlParameter("@take", searchParams.take))
.ToList <EvolutionRepository.ManualModels.OrganisationGrid>();
//Get record count from stored procedure without Skip ad Take applied
string organisationsCount = ctx.Database.SqlQuery <string>(
"exec uspGetOrganisationCount @parentorganisation, @organisation, @childto, @type, @subtype, @jurisdiction",
new SqlParameter("@parentorganisation", Convert.ToInt32(searchParams.parentorganisation)),
new SqlParameter("@organisation", searchParams.organisation),
new SqlParameter("@childto", Convert.ToInt32(searchParams.childto)),
new SqlParameter("@type", Convert.ToInt32(searchParams.type)),
new SqlParameter("@subtype", Convert.ToInt32(searchParams.subtype)),
new SqlParameter("@jurisdiction", Convert.ToInt32(searchParams.jurisdiction))).ToString();
TotalInstancesWithoutSkipTake = 1; //Convert.ToInt32(organisationsCount);
}
catch (Exception ex)
{
throw ex;
}
}
//return organisations.OrderBy(x => x.DateAdded).Skip(searchParams.skip).Take(searchParams.take).ToList();
return(organisations);
}
public static bool DeleteArea(int areaID) //EvolutionRepository.Models.Area AddArea)
{
var Result = false;
try
{
using (var ctx = new EvolutionRepository.Models.EvolutionDBContext())
{
ctx.Database.ExecuteSqlCommand("Evo_Areas_Delete @AreaID",
new SqlParameter("@AreaID ", areaID));
Result = true;
}
}
catch (Exception)
{
Result = false;
}
return(Result);
}
public static bool DeleteOrganisationComm(int organisationCommID)
{
var Result = false;
try
{
using (var ctx = new EvolutionRepository.Models.EvolutionDBContext())
{
ctx.Database.ExecuteSqlCommand("Evo_OrganisationComm_Delete @OrganisationCommID",
new SqlParameter("@OrganisationID ", organisationCommID));
Result = true;
}
}
catch (Exception)
{
Result = false;
}
return(Result);
}
public static List <OrganisationTypeSimple> GetOrganisationSubTypeByTypeID(int OrganisationTypeID)
{
List <OrganisationTypeSimple> organisationTypesSimple = new List <OrganisationTypeSimple>();
using (var ctx = new EvolutionRepository.Models.EvolutionDBContext())
{
List <OrganisationSubType> organisationSubTypes = ctx.OrganisationSubTypes.AsNoTracking().Where(x => x.OrganisationTypeID == OrganisationTypeID).OrderBy(x => x.OrganisationSubTypeName).ToList();
foreach (var organisationSubType in organisationSubTypes)
{
OrganisationTypeSimple organisationTypeSimple = new OrganisationTypeSimple();
organisationTypeSimple.OrganisationTypeID = organisationSubType.OrganisationSubTypeID;
organisationTypeSimple.OrganisationTypeName = organisationSubType.OrganisationSubTypeName;
organisationTypesSimple.Add(organisationTypeSimple);
}
}
return(organisationTypesSimple);
}
public static List <EvolutionRepository.ManualModels.Resource> GetResources(string sessionID, Boolean isNew)
{
List <EvolutionRepository.ManualModels.Resource> employeeResources = new List <EvolutionRepository.ManualModels.Resource>();
using (EvolutionRepository.Models.EvolutionDBContext ctx = new EvolutionRepository.Models.EvolutionDBContext())
{
try
{
//Get records from stored procedure
employeeResources = ctx.Database.SqlQuery <EvolutionRepository.ManualModels.Resource>(
"EXEC Evo_ResourcesForSession @SessionID, @IsNew",
new SqlParameter("@SessionID", sessionID),
new SqlParameter("@IsNew", isNew))
.ToList <EvolutionRepository.ManualModels.Resource>();
}
catch (Exception ex)
{
throw ex;
}
}
return(employeeResources);
}
public static EvolutionRepository.ManualModels.GetAreaListResult GetAllAreasForOrganisation(int organisationID, int skip, int take)
{
using (var ctx = new EvolutionRepository.Models.EvolutionDBContext())
{
int TotalInstancesWithoutSkipTake;
ctx.Configuration.ProxyCreationEnabled = false;
var areas = ctx.Areas.Where(p => p.OrganisationID == organisationID).ToList();
if (areas != null)
{
TotalInstancesWithoutSkipTake = areas.Count;
areas = areas.OrderBy(x => x.AreaID).Skip(skip).Take(take).ToList();
var manualAreas = new List <EvolutionRepository.ManualModels.AreaSimple>();
foreach (var item in areas)
{
var manager = ctx.Employees.Where(p => p.EmployeeID == item.EmployeeID).FirstOrDefault();
manualAreas.Add(new EvolutionRepository.ManualModels.AreaSimple
{
AreaID = item.AreaID,
AreaName = item.AreaName,
EmergencyNumber = item.EmergencyNumber,
EmployeeFullname = manager.FirstName + " " + manager.LastName,
EmployeeID = item.EmployeeID.Value,
Mobile = item.Mobile,
OrganisationID = item.OrganisationID,
OrganisationName = ctx.Organisations.Where(p => p.OrganisationID == item.OrganisationID).FirstOrDefault().OrganisationName
});
}
return(new EvolutionRepository.ManualModels.GetAreaListResult(manualAreas.ToArray(), TotalInstancesWithoutSkipTake));
}
return(null);
}
}
