/*
* Save Customer with Plan Info
*
* */
public List <CustomerPlanGridItem> GetAllCustomersWithPlanInfo()
{
try
{
using (var ctx = new DB_121539_alkeshpatelfEntities())
{
var customers = (from c in ctx.Customers
join p in ctx.EnergyPlans on c.EnergyPlanId equals p.EnergyPlanId
orderby c.CustomerName
select new CustomerPlanGridItem()
{
CustomerId = c.CustomerId,
CustomerName = c.CustomerName,
CustomerPlanId = c.EnergyPlanId,
Email = c.CustomerEmail,
EnergyPlanName = p.EnergyPlanName,
FixedCost = p.EnergyPlanFixedCost,
varCost = p.EnergyPlanVarCost
});
if (customers.Any())
{
return(customers.ToList());
}
else
{
return(new List <CustomerPlanGridItem>());
}
}
}
catch (Exception ex)
{
throw ex;
}
}
public List <DocumentGridItem> GetAllFiles()
{
try
{
using (var ctx = new DB_121539_alkeshpatelfEntities())
{
var customers = (from c in ctx.EnergySavedFiles
orderby c.CreateDate descending
select new DocumentGridItem()
{
FileId = c.FileId,
FileName = c.FileName,
CreateDate = c.CreateDate
});
if (customers.Any())
{
return(customers.ToList());
}
else
{
return(new List <DocumentGridItem>());
}
}
}
catch (Exception ex)
{
throw ex;
}
}
/*
* Get All Plans
*
* */
public List <EnergyPlanGridItem> GetAllPlans()
{
try
{
using (var ctx = new DB_121539_alkeshpatelfEntities())
{
var plans = (from p in ctx.EnergyPlans
orderby p.EnergyPlanId
select new EnergyPlanGridItem()
{
EnergyPlanId = p.EnergyPlanId,
EnergyPlanName = p.EnergyPlanName,
FixedCost = p.EnergyPlanFixedCost,
varCost = p.EnergyPlanVarCost
});
if (plans.Any())
{
return(plans.ToList());
}
else
{
return(new List <EnergyPlanGridItem>());
}
}
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// Find if engineer assigned scheduled for yesterday or tomorrow
///
/// </summary>
/// <param name="selectedDay">Day in which selection is going to made</param>
/// <param name="engineerId">Engineer id</param>
/// <returns>boolean value</returns>
public bool IsEngineerAssignedScheduleForConsecativeDays(DateTime selectedDay, int engineerId)
{
bool result = false;
try
{
using (var ctx = new DB_121539_alkeshpatelfEntities())
{
DateTime pastDay = selectedDay.AddDays(-1);
DateTime futureDay = selectedDay.AddDays(1);
if (selectedDay.DayOfWeek == DayOfWeek.Monday)
{
pastDay = selectedDay.AddDays(-3);
}
if (selectedDay.DayOfWeek == DayOfWeek.Friday)
{
futureDay = selectedDay.AddDays(3);
}
var scheduleItem = ctx.Schedules.Where(s => s.ScheduleEngineer != null && s.ScheduleEngineer.Value == engineerId && s.ScheduleDate >= pastDay && s.ScheduleDate <= futureDay).FirstOrDefault();
if (scheduleItem != null)
{
return(true);
}
}
}
catch (Exception ex)
{
}
return(result);
}
/*
* Get All Customers
*
* */
public List <CustomerGridItem> GetAllCustomers()
{
try
{
using (var ctx = new DB_121539_alkeshpatelfEntities())
{
var customers = (from c in ctx.Customers
orderby c.CustomerName
select new CustomerGridItem()
{
CustomerId = c.CustomerId,
CustomerName = c.CustomerName,
CustomerPlanId = c.EnergyPlanId,
Email = c.CustomerEmail
});
if (customers.Any())
{
return(customers.ToList());
}
else
{
return(new List <CustomerGridItem>());
}
}
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// Get all schedules for selected dates
///
/// </summary>
/// <param name="startDate">start date from where selection begins</param>
/// <param name="endDate">end date from where selection ends</param>
/// <returns>List<CalendarModel></returns>
public List <CalendarModel> GetAssignedSchedules(DateTime startDate, DateTime endDate)
{
try
{
using (var ctx = new DB_121539_alkeshpatelfEntities())
{
var query = (from s in ctx.Schedules
join e in ctx.Engineers on s.ScheduleEngineer equals e.EngineerId
where s.ScheduleDate >= startDate && s.ScheduleDate <= endDate
select new CalendarModel()
{
scheduleDate = s.ScheduleDate,
engineerId = s.ScheduleEngineer,
title = e.EngineerName,
period = s.SchedulePeriod,
createDate = s.CreateDate
});
if (query.Any())
{
List <CalendarModel> result = query.OrderBy(s => s.createDate).ToList();
return(result);
}
}
}
catch (Exception ex)
{
}
return(null);
}
/*
* Get Customer By Id
*
* */
public Customer GetCustomerById(int customerId)
{
try
{
using (var ctx = new DB_121539_alkeshpatelfEntities())
{
return(ctx.Customers.Where(c => c.CustomerId == customerId).FirstOrDefault());
}
}
catch (Exception ex)
{
throw ex;
}
}
public EnergyFile GetFile()
{
try
{
using (var ctx = new DB_121539_alkeshpatelfEntities())
{
return(ctx.EnergyFiles.FirstOrDefault());
}
}
catch (Exception ex)
{
throw ex;
}
}
/*
* Get Customer By Id
*
* */
public EnergyPlan GetEnergyPlanById(int energyPlanId)
{
try
{
using (var ctx = new DB_121539_alkeshpatelfEntities())
{
return(ctx.EnergyPlans.Where(c => c.EnergyPlanId == energyPlanId).FirstOrDefault());
}
}
catch (Exception ex)
{
throw ex;
}
}
public EnergySavedFile GetSavedFile(int fileId)
{
try
{
using (var ctx = new DB_121539_alkeshpatelfEntities())
{
return(ctx.EnergySavedFiles.Where(c => c.FileId == fileId).FirstOrDefault());
}
}
catch (Exception ex)
{
throw ex;
}
}
public EnergyFile SaveFile(EnergyFile file)
{
try
{
using (var ctx = new DB_121539_alkeshpatelfEntities())
{
ctx.EnergyFiles.Add(file);
ctx.SaveChanges();
return(file);
}
}
catch (Exception ex)
{
throw ex;
}
}
/*
* Save EnergyPlan
*
* */
public EnergyPlan SaveEnergyPlan(EnergyPlan energyPlan)
{
try
{
using (var ctx = new DB_121539_alkeshpatelfEntities())
{
energyPlan.CreateDate = DateTime.Now;
ctx.EnergyPlans.Add(energyPlan);
ctx.SaveChanges();
return(energyPlan);
}
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// Delete all schedules
///
/// </summary>
/// <returns>int value</returns>
public int DeleteAllSchedules()
{
try
{
using (var ctx = new DB_121539_alkeshpatelfEntities())
{
var scheduleItems = ctx.Schedules.Where(s => s.ScheduleId > 0);
ctx.Schedules.RemoveRange(scheduleItems);
ctx.SaveChanges();
return(1);
}
}
catch (Exception ex)
{
}
return(0);
}
/*
* Save Customer
*
* */
public Customer SaveCustomer(Customer customer)
{
try
{
using (var ctx = new DB_121539_alkeshpatelfEntities())
{
customer.CreateDate = DateTime.Now;
ctx.Customers.Add(customer);
ctx.SaveChanges();
return(customer);
}
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// Find number of schedule for selected date
///
/// </summary>
/// <param name="selectedDay">Day in which selection is going to made</param>
/// <returns>int value</returns>
public int GetNumberofScheduleForDate(DateTime selectedDay)
{
int result = 0;
try
{
using (var ctx = new DB_121539_alkeshpatelfEntities())
{
var scheduleItems = ctx.Schedules.Where(s => s.ScheduleDate == selectedDay);
if (scheduleItems.Any())
{
return(scheduleItems.Count());
}
}
}
catch (Exception ex)
{
}
return(result);
}
/// <summary>
/// Assign Engineer
///
/// </summary>
/// <param name="selectedDate">Date on which engineer selected</param>
/// <returns>int value</returns>
public int AssignEngineer(DateTime selectedDate)
{
try
{
EngineerService engineerService = new EngineerService();
EngineerModel engineerModel = engineerService.GetQualifiedEngineer(selectedDate);
if (engineerModel != null)
{
using (var ctx = new DB_121539_alkeshpatelfEntities())
{
var scheduleItems = ctx.Schedules.Where(s => s.ScheduleDate == selectedDate);
if (scheduleItems.Count() < 2)
{
var period = scheduleItems.Select(s => s.SchedulePeriod).FirstOrDefault();
int engineerPeriod = 1;
if (period == 1)
{
engineerPeriod = 2;
}
Schedule schedule = new Schedule();
schedule.SchedulePeriod = engineerPeriod;
schedule.ScheduleEngineer = engineerModel.EngineerId;
schedule.CreateDate = DateTime.Now;
schedule.UpdateDate = DateTime.Now;
schedule.ScheduleDate = selectedDate;
ctx.Schedules.Add(schedule);
ctx.SaveChanges();
return(schedule.ScheduleId);
}
}
}
}
catch (Exception ex)
{
}
return(0);
}
/// <summary>
/// Find if engineer assigned number of scheduled for two weeks span
///
/// </summary>
/// <param name="selectedDay">Day in which selection is going to made</param>
/// <param name="engineerId">Engineer id</param>
/// <returns>int value</returns>
public int GetEngineerAssignedScheduleForTwoWeeks(DateTime selectedDay, int engineerId)
{
int result = 0;
try
{
using (var ctx = new DB_121539_alkeshpatelfEntities())
{
DateTime pastDay = selectedDay.AddDays(-7);
DateTime futureDay = selectedDay.AddDays(7);
var scheduleItems = ctx.Schedules.Where(s => s.ScheduleEngineer != null && s.ScheduleEngineer.Value == engineerId && s.ScheduleDate >= pastDay && s.ScheduleDate <= futureDay);
if (scheduleItems.Any())
{
return(scheduleItems.Count());
}
}
}
catch (Exception ex)
{
}
return(result);
}
/// <summary>
/// Get all Engineers
///
/// </summary>
/// <returns>List<EngineerModel></returns>
public List <EngineerModel> GetAllEngineers()
{
try
{
using (var ctx = new DB_121539_alkeshpatelfEntities())
{
return((from c in ctx.Engineers
select new EngineerModel()
{
EngineerId = c.EngineerId,
EngineerName = c.EngineerName,
CreateDate = c.CreateDate,
CreateUserId = c.CreateUserId,
UpdateDate = c.UpdateDate,
UpdateUserId = c.UpdateUserId
}).ToList());
}
}
catch (Exception ex)
{
}
return(new List <EngineerModel>());
}
/// <summary>
/// Get single qualified engineer which meets all requirement
///
/// </summary>
/// <param name="selectedDate">selected date on which engineer will be added</param>
/// <returns>EngineerModel</returns>
public EngineerModel GetQualifiedEngineer(DateTime selectedDate)
{
try
{
using (var ctx = new DB_121539_alkeshpatelfEntities())
{
ScheduleService scheduleService = new ScheduleService();
List <EngineerModel> items = GetAllEngineers();
var engineerIds = items.Select(s => s.EngineerId).ToList();
//Check for engineer which has not been assigned to schedule twice in last 13 days or future 13 days
DateTime pastMinTwo = FindWorkDay(selectedDate, -9, true);
DateTime futureMinTwo = FindWorkDay(selectedDate, 9, false);
int pastMinTwoTotalRows = ctx.Schedules.Where(c => c.ScheduleDate >= pastMinTwo).Count();
if (pastMinTwoTotalRows >= 14)
{
var lastThirteenDaysCheck = ctx.Schedules.Where(c => c.ScheduleDate >= pastMinTwo).GroupBy(i => i.ScheduleEngineer)
.Where(x => x.Count() > 1)
.Select(val => val.Key);
List <int> notHitIds = new List <int>();
for (var i = 0; i < engineerIds.Count(); i++)
{
bool itemHit = false;
foreach (var item in lastThirteenDaysCheck)
{
if (item.Value == engineerIds[i])
{
itemHit = true;
break;
}
}
if (!itemHit)
{
if (!scheduleService.IsEngineerAssignedScheduleForConsecativeDays(selectedDate, engineerIds[i]))
{
notHitIds.Add(engineerIds[i]);
}
}
}
if (notHitIds.Count > 0)
{
Random rand = new Random();
int r = rand.Next(0, notHitIds.Count - 1);
EngineerModel missingEngineerModel = new EngineerModel();
missingEngineerModel.EngineerId = notHitIds[r];
return(missingEngineerModel);
}
}
int futureMinTwoTotalRows = ctx.Schedules.Where(c => c.ScheduleDate <= futureMinTwo).Count();
if (futureMinTwoTotalRows >= 14)
{
var futureThirteenDaysCheck = ctx.Schedules.Where(c => c.ScheduleDate <= futureMinTwo).GroupBy(i => i.ScheduleEngineer)
.Where(x => x.Count() > 1)
.Select(val => val.Key);
List <int> notHitIds = new List <int>();
for (var i = 0; i < engineerIds.Count(); i++)
{
bool itemHit = false;
foreach (var item in futureThirteenDaysCheck)
{
if (item.Value == engineerIds[i])
{
itemHit = true;
break;
}
}
if (!itemHit)
{
if (!scheduleService.IsEngineerAssignedScheduleForConsecativeDays(selectedDate, engineerIds[i]))
{
notHitIds.Add(engineerIds[i]);
}
}
}
if (notHitIds.Count > 0)
{
Random rand = new Random();
int r = rand.Next(0, notHitIds.Count - 1);
EngineerModel missingEngineerModel = new EngineerModel();
missingEngineerModel.EngineerId = notHitIds[r];
return(missingEngineerModel);
}
}
//Check for engineer which has not been assigned to schedule atleast once in last 11 days or future 11 days
DateTime pastMinOne = FindWorkDay(selectedDate, -7, true);
DateTime futureMinOne = FindWorkDay(selectedDate, 7, false);
int pastMinOneTotalRows = ctx.Schedules.Where(c => c.ScheduleDate >= pastMinOne).Count();
if (pastMinOneTotalRows > 8 && pastMinOneTotalRows < 14)
{
var lastElevenDaysCheck = ctx.Schedules.Where(c => c.ScheduleDate >= pastMinOne).GroupBy(i => i.ScheduleEngineer)
.Where(x => x.Count() > 0)
.Select(val => val.Key);
List <int> notHitIds = new List <int>();
for (var i = 0; i < engineerIds.Count(); i++)
{
bool itemHit = false;
foreach (var item in lastElevenDaysCheck)
{
if (item.Value == engineerIds[i])
{
itemHit = true;
break;
}
}
if (!itemHit)
{
if (!scheduleService.IsEngineerAssignedScheduleForConsecativeDays(selectedDate, engineerIds[i]))
{
notHitIds.Add(engineerIds[i]);
}
}
}
if (notHitIds.Count > 0)
{
Random rand = new Random();
int r = rand.Next(0, notHitIds.Count - 1);
EngineerModel missingEngineerModel = new EngineerModel();
missingEngineerModel.EngineerId = notHitIds[r];
return(missingEngineerModel);
}
}
int futureMinOneTotalRows = ctx.Schedules.Where(c => c.ScheduleDate <= futureMinOne).Count();
if (futureMinOneTotalRows > 8 && futureMinOneTotalRows < 14)
{
var futureElevenDaysCheck = ctx.Schedules.Where(c => c.ScheduleDate <= futureMinOne).GroupBy(i => i.ScheduleEngineer)
.Where(x => x.Count() > 0)
.Select(val => val.Key);
List <int> notHitIds = new List <int>();
for (var i = 0; i < engineerIds.Count(); i++)
{
bool itemHit = false;
foreach (var item in futureElevenDaysCheck)
{
if (item.Value == engineerIds[i])
{
itemHit = true;
break;
}
}
if (!itemHit)
{
if (!scheduleService.IsEngineerAssignedScheduleForConsecativeDays(selectedDate, engineerIds[i]))
{
notHitIds.Add(engineerIds[i]);
}
}
}
if (notHitIds.Count > 0)
{
Random rand = new Random();
int r = rand.Next(0, notHitIds.Count - 1);
EngineerModel missingEngineerModel = new EngineerModel();
missingEngineerModel.EngineerId = notHitIds[r];
return(missingEngineerModel);
}
}
EngineerModel selectedEngineer = null;
Random rnd = new Random();
while (selectedEngineer == null)
{
int r = rnd.Next(0, items.Count - 1);
if (!scheduleService.IsEngineerAssignedScheduleForConsecativeDays(selectedDate, items[r].EngineerId))
{
selectedEngineer = items[r];
}
}
return(selectedEngineer);
}
}
catch (Exception ex)
{
}
return(null);
}