//By Klaus
public static bool InsertJob(JobO jobToAdd)
{
OpenConnection();
try
{
SqlCommand insertJob = new SqlCommand("INSERT INTO Job ([Name],[ResourceID],[CreatedByUserID],[Deadline],[Created],[Priority],[ExecutionTime],[DurationHours])VALUES(@name,@resource,@UserID,@deadline,@creation,@priority,@exeTime,@duration); ", myConnection);
insertJob.Parameters.Add("@name", SqlDbType.VarChar);
insertJob.Parameters["@name"].Value = jobToAdd.JobName;
insertJob.Parameters.Add("@resource", SqlDbType.Int);
insertJob.Parameters["@resource"].Value = jobToAdd.ResourceID;
insertJob.Parameters.Add("@userID", SqlDbType.Int);
insertJob.Parameters["@userID"].Value = jobToAdd.CreatedUserID;
insertJob.Parameters.Add("@deadline", SqlDbType.DateTime);
insertJob.Parameters["@deadline"].Value = jobToAdd.Deadline;
insertJob.Parameters.Add("@creation", SqlDbType.DateTime);
insertJob.Parameters["@creation"].Value = jobToAdd.Created;
insertJob.Parameters.Add("@priority", SqlDbType.TinyInt);
insertJob.Parameters["@priority"].Value = jobToAdd.Priority;
insertJob.Parameters.Add("@exeTime", SqlDbType.DateTime);
insertJob.Parameters["@exeTime"].Value = jobToAdd.ExeTime;
insertJob.Parameters.Add("@duration", SqlDbType.Int);
insertJob.Parameters["@duration"].Value = jobToAdd.DurationHours;
insertJob.ExecuteNonQuery();
CloseConnection();
return(true);
}
catch (Exception)
{
return(false);
}
}
//By BKP
public static bool FindPlaceInQueue(JobO JobToBeScheduled)
{
Queue queue = GetJobs(JobToBeScheduled.ResourceID);
JobToBeScheduled.ExeTime = CalculateBestSpot(queue, JobToBeScheduled);
JobToBeScheduled.JobName = GenerateJobName(JobToBeScheduled);
bool isSucces = DB.InsertJob(JobToBeScheduled);
return(isSucces);
}
private static DateTime?FindSpotInEsp(ESPO currentESP, JobO currentJob, DateTime now)
{
DateTime soonestStart = DateTime.Today + currentESP.StartTime;
DateTime ESPEnd, possibleStart, possibleEnd, ESPFinalEnd = currentESP.EndDate + currentESP.EndTime;
DateTime?startDateTime = soonestStart;
bool spotFound;
if (currentESP.EndTime < currentESP.StartTime)
{
ESPEnd = DateTime.Today.AddDays(1) + currentESP.EndTime;
}
else
{
ESPEnd = DateTime.Today + currentESP.EndTime;
}
if (soonestStart < now && ESPEnd < now)
{
soonestStart = soonestStart.AddDays(1);
ESPEnd = ESPEnd.AddDays(1);
}
while (soonestStart < currentESP.EndDate)
{
if (currentJob.Deadline > ESPEnd)
{
possibleStart = soonestStart;
int jobHours = currentJob.DurationHours;
possibleEnd = possibleStart.AddHours(jobHours);
while (possibleEnd <= ESPEnd)
{
spotFound = DB.IsResourceAvailable(possibleStart, possibleEnd, currentJob);
if (spotFound == true)
{
startDateTime = possibleStart;
return(startDateTime);
}
possibleStart = possibleStart.AddHours(1);
possibleEnd = possibleStart.AddHours(jobHours);
}
startDateTime = null;
}
else
{
startDateTime = null;
}
soonestStart = soonestStart.AddDays(1);
ESPEnd = ESPEnd.AddDays(1);
}
return(startDateTime);
}
private void listbox_Show_SelectionChanged(object sender, SelectionChangedEventArgs e)
{
if ((string)lbl_Title.Content == "Jobs")
{
JobO chosenJob = (JobO)listbox_Show.SelectedItem;
SelectedTemp.ChosenJob = chosenJob;
ShowDetails sdJ = new ShowDetails((string)lbl_Title.Content);
sdJ.Show();
this.Close();
}
else if ((string)lbl_Title.Content == "Users")
{
UserO chosenUser = (UserO)listbox_Show.SelectedItem;
SelectedTemp.ChosenUser = chosenUser;
ShowDetails sdU = new ShowDetails((string)lbl_Title.Content);
sdU.Show();
this.Close();
}
else if ((string)lbl_Title.Content == "Tarif")
{
TO chosenTarif = (TO)listbox_Show.SelectedItem;
SelectedTemp.ChosenTarif = chosenTarif;
ShowDetails sdT = new ShowDetails((string)lbl_Title.Content);
sdT.Show();
this.Close();
}
else if ((string)lbl_Title.Content == "ESPs")
{
ESPO chosenESP = (ESPO)listbox_Show.SelectedItem;
SelectedTemp.ChosenESP = chosenESP;
ShowDetails sdESP = new ShowDetails((string)lbl_Title.Content);
sdESP.Show();
this.Close();
}
else if ((string)lbl_Title.Content == "Resources")
{
RO chosenR = (RO)listbox_Show.SelectedItem;
SelectedTemp.ChosenR = chosenR;
ShowDetails sdR = new ShowDetails((string)lbl_Title.Content);
sdR.Show();
this.Close();
}
else if ((string)lbl_Title.Content == "ResourceTypes")
{
RTO chosenRT = (RTO)listbox_Show.SelectedItem;
SelectedTemp.ChosenRT = chosenRT;
ShowDetails sdRT = new ShowDetails((string)lbl_Title.Content);
sdRT.Show();
this.Close();
}
}
//By BKP
private static DateTime CalculateBestSpot(Queue queue, JobO currentJob)
{
DateTime now = DateTime.Now;
DateTime? ESPtempTime = null;
DateTime? TarifTempTime = null;
DateTime tempTime = new DateTime();
List <ESPO> avaibleESPs = DB.GetESPs(now, currentJob.Deadline);
List <TO> avaibleTarifs = DB.SelectAllTarifs(true);
for (int i = 0; i < avaibleESPs.Count; i++)
{
ESPO currentESP = avaibleESPs[i];
ESPtempTime = FindSpotInEsp(currentESP, currentJob, now);
if (ESPtempTime != null)
{
tempTime = (DateTime)ESPtempTime;
i = avaibleESPs.Count;
}
}
if (ESPtempTime == null)
{
for (int t = 0; t < avaibleTarifs.Count; t++)
{
TO currentTarif = avaibleTarifs[t];
TarifTempTime = FindSpotInTarif(currentTarif, currentJob, now);
if (TarifTempTime != null)
{
tempTime = (DateTime)TarifTempTime;
t = avaibleTarifs.Count;
}
}
}
return(tempTime);
/*int hour = 00;
* tempTime = Convert.ToDateTime($"{hour}:00:00");
* int index = queue.jobsInQueue.FindIndex(item => item.ExeTime == tempTime);
* if (index < 0)
* {
* return tempTime;
* }
* else
* {
* hour++;
* tempTime = Convert.ToDateTime($"{hour}:00:00");
* return tempTime;
* }*/
}
private static string GenerateJobName(JobO jobToBeScheduled)
{
UserO jobCreatedBy = DB.GetUser(jobToBeScheduled.CreatedUserID);
RO jobResource = DB.GetResource(jobToBeScheduled.ResourceID);
string resName;
if (jobResource.Name.Length < 8)
{
resName = jobResource.Name;
}
else
{
resName = jobResource.Name.Substring(0, 8);
}
string jobname = jobCreatedBy.FirstName + resName + jobToBeScheduled.ExeTime.ToShortDateString() + rnd.Next(1000, 9999).ToString();
return(jobname);
}
//By Klaus
public static List <JobO> FillQueue(int resource)
{
//Get jobs to add to the queue
List <JobO> queueJobs = new List <JobO>();
OpenConnection();
SqlCommand getJobs = new SqlCommand("SELECT * FROM Job WHERE ResourceID = @resource", myConnection);
getJobs.Parameters.Add("@resource", SqlDbType.Int);
getJobs.Parameters["@resource"].Value = resource;
SqlDataReader reader = getJobs.ExecuteReader();
while (reader.Read())
{
JobO j = new JobO();
j.ExeTime = reader.GetDateTime(7);
queueJobs.Add(j);
}
CloseConnection();
return(queueJobs);
}
public static void CreateJob(int Resource, int User, int Priority, DateTime Deadline, int duration)
{
JobO jobToBeScheduled = new JobO();
jobToBeScheduled.ResourceID = Resource;
jobToBeScheduled.CreatedUserID = User;
jobToBeScheduled.Priority = Priority;
jobToBeScheduled.Deadline = Deadline;
jobToBeScheduled.CreatedUserID = CurrentUser.ID;
jobToBeScheduled.Created = DateTime.Now;
jobToBeScheduled.DurationHours = duration;
bool isSucces = Scheduler.FindPlaceInQueue(jobToBeScheduled);
if (isSucces)
{
MessageBox.Show("Job succesfully added to Queue");
}
else
{
MessageBox.Show("Job could not be added to queue");
}
}
internal static List <JobO> SelectExecutedJobs()
{
List <JobO> jList = new List <JobO>();
OpenConnection();
SqlCommand getJ = new SqlCommand(" SELECT * FROM Job WHERE (DATEADD(hh,durationhours,ExecutionTime) < getdate())", myConnection);
SqlDataReader reader = getJ.ExecuteReader();
while (reader.Read())
{
JobO j = new JobO();
j.JobID = reader.GetInt32(0);
j.JobName = reader.GetString(6);
j.Priority = reader.GetByte(5);
j.ResourceID = reader.GetInt32(1);
j.Deadline = reader.GetDateTime(3);
j.Created = reader.GetDateTime(4);
j.CreatedUserID = reader.GetInt32(2);
jList.Add(j);
}
CloseConnection();
return(jList);
}
internal static bool IsResourceAvailable(DateTime possibleStart, DateTime soonestEnd, JobO currentJob)
{
bool available = true;
OpenConnection();
SqlCommand getJ = new SqlCommand("SELECT DISTINCT JobID, Priority FROM Job WHERE ResourceID=@ResourceID" +
"( (DATEADD(hour, DurationHours, ExecutionTime)>@PossibleStart AND DATEADD(hour, DurationHours, ExecutionTime)<@SoonestEnd) " +
"OR (ExecutionTime>=@PossibleStart AND ExecutionTime<@SoonestEnd) " +
"OR (ExecutionTime<@PossibleStart AND DATEADD(hour, DurationHours, ExecutionTime)>@PossibleStart) " +
"OR (ExecutionTime<@SoonestEnd AND DATEADD(hour, DurationHours, ExecutionTime)>@SoonestEnd) )", myConnection);
getJ.Parameters.Add("@ResourceID", SqlDbType.Int);
getJ.Parameters["@ResourceID"].Value = currentJob.ResourceID;
getJ.Parameters.Add("@PossibleStart", SqlDbType.DateTime);
getJ.Parameters["@PossibleStart"].Value = possibleStart;
getJ.Parameters.Add("@SoonestEnd", SqlDbType.DateTime);
getJ.Parameters["@SoonestEnd"].Value = soonestEnd;
SqlDataReader reader = getJ.ExecuteReader();
string query = getJ.CommandText;
// This is here in case of debugging the above statement
/*foreach (SqlParameter p in getJ.Parameters)
* {
* query = query.Replace(p.ParameterName, p.Value.ToString());
* }*/
if (reader.Read())
{
DateTime executionTime = reader.GetDateTime(0);
int existingJobPriority = reader.GetInt32(1);
available = false;
}
CloseConnection();
return(available);
}
public ShowDetails(string subject)
{
InitializeComponent();
if (CurrentUser.IsAdmin == false)
{
btn_Delete.IsEnabled = false;
btn_Edit.IsEnabled = false;
}
if (subject == "Jobs")
{
lbl_Title.Content = "Job";
JobO chosenJob = SelectedTemp.ChosenJob;
lbl_CreatedFill.Content = chosenJob.Created;
lbl_DeadlineFill.Content = chosenJob.Deadline;
UserO createdBy = DB.GetUser(chosenJob.CreatedUserID);
lbl_CreatedByFill.Content = (createdBy.FirstName + "" + createdBy.SurName);
lbl_NameFill.Content = chosenJob.JobName;
lbl_PriorityFill.Content = chosenJob.Priority;
}
else if (subject == "Users")
{
lbl_Title.Content = "User";
UserO chosenUser = SelectedTemp.ChosenUser;
lbl_NameTitle.Content = ("Username: "******"Fornavn: ");
lbl_CreatedTitle.Content = ("Efternavn: ");
lbl_DeadlineTitle.Content = ("Password: "******"Is User admin:");
lbl_NameFill.Content = chosenUser.UserName;
lbl_CreatedByFill.Content = chosenUser.FirstName;
lbl_CreatedFill.Content = chosenUser.SurName;
lbl_DeadlineFill.Content = chosenUser.Password;
lbl_PriorityFill.Content = chosenUser.IsAdmin;
}
else if (subject == "Tarif")
{
lbl_Title.Content = "Tarif";
TO chosenTarif = SelectedTemp.ChosenTarif;
lbl_NameTitle.Content = ("Price: ");
lbl_CreatedByTitle.Content = ("Start: ");
lbl_CreatedTitle.Content = ("End: ");
lbl_DeadlineTitle.Content = ("");
lbl_PriorityTitle.Content = ("");
lbl_NameFill.Content = chosenTarif.Cost;
lbl_CreatedByFill.Content = chosenTarif.StartTime;
lbl_CreatedFill.Content = chosenTarif.EndTime;
}
else if (subject == "ESPs")
{
lbl_Title.Content = "ESP";
ESPO chosenESP = SelectedTemp.ChosenESP;
lbl_NameTitle.Content = ("Energy Surplus: ");
lbl_CreatedByTitle.Content = ("Start date: ");
lbl_CreatedTitle.Content = ("End date: ");
lbl_DeadlineTitle.Content = ("start time: ");
lbl_PriorityTitle.Content = ("End time: ");
lbl_NameFill.Content = chosenESP.EnergySurplus;
lbl_CreatedByFill.Content = chosenESP.StartDate;
lbl_CreatedFill.Content = chosenESP.EndDate;
lbl_DeadlineFill.Content = chosenESP.StartTime;
lbl_PriorityFill.Content = chosenESP.EndTime;
}
else if (subject == "Resources")
{
RO chosenR = SelectedTemp.ChosenR;
lbl_Title.Content = "Resource";
lbl_NameTitle.Content = ("Name: ");
lbl_CreatedByTitle.Content = ("");
lbl_CreatedTitle.Content = ("");
lbl_DeadlineTitle.Content = ("");
lbl_PriorityTitle.Content = ("");
lbl_NameFill.Content = chosenR.Name;
}
else if (subject == "ResourceTypes")
{
RTO chosenRT = SelectedTemp.ChosenRT;
lbl_Title.Content = "Resource type";
lbl_NameTitle.Content = ("Name: ");
lbl_CreatedByTitle.Content = ("");
lbl_CreatedTitle.Content = ("");
lbl_DeadlineTitle.Content = ("");
lbl_PriorityTitle.Content = ("");
lbl_NameFill.Content = chosenRT.Name;
}
}
private static DateTime?FindSpotInTarif(TO currentTarif, JobO currentJob, DateTime now)
{
DateTime tarifStart = DateTime.Today + currentTarif.StartTime;
DateTime?startDateTime = null;
DateTime tarifEnd = DateTime.Today + currentTarif.EndTime, possibleStart = now, possibleEnd, soonestStart = now;
bool spotFound;
bool needToResetToTarifStart = true;
if (currentTarif.EndTime < currentTarif.StartTime)
{
tarifEnd = DateTime.Today.AddDays(1) + currentTarif.EndTime;
}
else
{
tarifEnd = DateTime.Today + currentTarif.EndTime;
}
if (tarifStart < now && tarifEnd < now)
{
tarifEnd = tarifEnd.AddDays(1);
tarifStart = tarifStart.AddDays(1);
}
if (tarifStart > possibleStart)
{
soonestStart = tarifStart;
needToResetToTarifStart = false;
}
DateTime testDeadline = currentJob.Deadline.AddDays(1);
while (testDeadline >= tarifEnd)
{
possibleStart = soonestStart;
int jobHours = currentJob.DurationHours;
possibleEnd = possibleStart.AddHours(jobHours);
while (possibleEnd <= tarifEnd && currentJob.Deadline >= possibleEnd)
{
spotFound = DB.IsResourceAvailable(possibleStart, possibleEnd, currentJob);
if (spotFound == true)
{
startDateTime = possibleStart;
return(startDateTime);
}
possibleStart = possibleStart.AddHours(1);
possibleEnd = possibleStart.AddHours(jobHours);
}
if (needToResetToTarifStart)
{
soonestStart = tarifStart.AddDays(1);
}
else
{
soonestStart = soonestStart.AddDays(1);
needToResetToTarifStart = false;
}
tarifEnd = tarifEnd.AddDays(1);
}
return(startDateTime);
}
