private DateTime?FindMonthlySchedulePlanNextTriggerDate(SchedulePlan monthlyPlan, DateTime nowDateTime, DateTime startDate)
{
DateTime startingDate = startDate;
try
{
while (true)
{
//check for expired interval
if (monthlyPlan.EndDate.HasValue)
{
if (monthlyPlan.EndDate.Value < startingDate)
{
return(null);
}
}
DateTime movedTime = startingDate.AddSeconds(10);
if (movedTime >= nowDateTime)
{
return(startingDate);
}
else //step
{
startingDate = startingDate.AddMonths(1);
}
}
}
catch
{
return(null);
}
}
public bool CreateSchedulePlan(SchedulePlan schedulePlan)
{
if (schedulePlan.Id == Guid.Empty)
{
schedulePlan.Id = Guid.NewGuid();
}
schedulePlan.NextTriggerTime = FindSchedulePlanNextTriggerDate(schedulePlan);
return(JobService.CreateSchedule(schedulePlan));
}
//private DateTime? FindIntervalSchedulePlanNextTriggerDate(SchedulePlan intervalPlan, DateTime nowDateTime, DateTime startDate)
//{
// if (intervalPlan.ScheduledDays == null)
// intervalPlan.ScheduledDays = new SchedulePlanDaysOfWeek();
// var daysOfWeek = intervalPlan.ScheduledDays;
// //if interval is <=0 then can't find match
// if (intervalPlan.IntervalInMinutes <= 0)
// {
// return null;
// }
// DateTime startingDate = startDate;
// try
// {
// while (true)
// {
// //check for expired interval
// if (intervalPlan.EndDate.HasValue)
// {
// if (intervalPlan.EndDate.Value < startingDate)
// {
// return null;
// }
// }
// int timeAsInt = startingDate.Hour * 60 + startingDate.Minute;
// bool isIntervalConnectedToFirstDay = false;
// if (intervalPlan.StartTimespan.HasValue)
// {
// isIntervalConnectedToFirstDay = ((intervalPlan.StartTimespan.Value > intervalPlan.EndTimespan.Value) &&
// ((0 < timeAsInt) && (timeAsInt <= intervalPlan.EndTimespan.Value)));
// }
// DateTime movedTime = startingDate.AddSeconds(10);
// if (movedTime >= nowDateTime && IsTimeInTimespanInterval(startingDate, intervalPlan.StartTimespan, intervalPlan.EndTimespan) &&
// IsDayUsedInSchedulePlan(startingDate, daysOfWeek, isIntervalConnectedToFirstDay))
// {
// return startingDate;
// }
// else //step
// {
// startingDate = startingDate.AddMinutes(intervalPlan.IntervalInMinutes.Value);
// }
// }
// }
// catch
// {
// return null;
// }
//}
private DateTime?FindDailySchedulePlanNextTriggerDate(SchedulePlan dailyPlan, DateTime nowDateTime, DateTime startDate)
{
if (dailyPlan.ScheduledDays == null)
{
dailyPlan.ScheduledDays = new SchedulePlanDaysOfWeek();
}
var daysOfWeek = dailyPlan.ScheduledDays;
DateTime startingDate = startDate;
try
{
while (true)
{
//check for expired interval
if (dailyPlan.EndDate.HasValue)
{
if (dailyPlan.EndDate.Value < startingDate)
{
return(null);
}
}
DateTime movedTime = startingDate.AddSeconds(10);
if (movedTime >= nowDateTime && IsDayUsedInSchedulePlan(startingDate, daysOfWeek, false))
{
return(startingDate);
}
else //step
{
startingDate = startingDate.AddDays(1);
}
}
}
catch
{
return(null);
}
}
public SchedulePlan GetSchedulePlan(Guid id)
{
List <NpgsqlParameter> parameters = new List <NpgsqlParameter>();
parameters.Add(new NpgsqlParameter("id", id)
{
NpgsqlDbType = NpgsqlDbType.Uuid
});
string sql = "SELECT * FROM schedule_plan WHERE id = @id";
DataTable dtSchedulePlans = ExecuteQuerySqlCommand(sql, parameters);
SchedulePlan schedulePlan = null;
if (dtSchedulePlans.Rows != null && dtSchedulePlans.Rows.Count > 0)
{
schedulePlan = dtSchedulePlans.Rows[0].MapTo <SchedulePlan>();
}
return(schedulePlan);
}
public DateTime?FindSchedulePlanNextTriggerDate(SchedulePlan schedulePlan)
{
SchedulePlanType planType = schedulePlan.Type;
DateTime nowDateTime = DateTime.UtcNow; //.AddMinutes(1);
DateTime startingDate;
if (schedulePlan.StartDate.HasValue) //if day is selected then
{
startingDate = schedulePlan.StartDate.Value;
}
else
{
startingDate = nowDateTime;
}
switch (planType)
{
case SchedulePlanType.Interval:
{
return(FindIntervalSchedulePlanNextTriggerDate(schedulePlan, nowDateTime, schedulePlan.LastTriggerTime));
}
case SchedulePlanType.Daily:
{
return(FindDailySchedulePlanNextTriggerDate(schedulePlan, nowDateTime, startingDate));
}
case SchedulePlanType.Weekly:
{
return(FindWeeklySchedulePlanNextTriggerDate(schedulePlan, nowDateTime, startingDate));
}
case SchedulePlanType.Monthly:
{
return(FindMonthlySchedulePlanNextTriggerDate(schedulePlan, nowDateTime, startingDate));
}
}
return(null);
}
public bool UpdateSchedule(SchedulePlan schedulePlan)
{
JsonSerializerSettings settings = new JsonSerializerSettings {
TypeNameHandling = TypeNameHandling.All
};
List <NpgsqlParameter> parameters = new List <NpgsqlParameter>();
parameters.Add(new NpgsqlParameter("id", schedulePlan.Id)
{
NpgsqlDbType = NpgsqlDbType.Uuid
});
parameters.Add(new NpgsqlParameter("name", schedulePlan.Name)
{
NpgsqlDbType = NpgsqlDbType.Text
});
parameters.Add(new NpgsqlParameter("type", (int)schedulePlan.Type)
{
NpgsqlDbType = NpgsqlDbType.Integer
});
if (schedulePlan.StartDate.HasValue)
{
parameters.Add(new NpgsqlParameter("start_date", schedulePlan.StartDate)
{
NpgsqlDbType = NpgsqlDbType.Timestamp
});
}
if (schedulePlan.EndDate.HasValue)
{
parameters.Add(new NpgsqlParameter("end_date", schedulePlan.EndDate)
{
NpgsqlDbType = NpgsqlDbType.Timestamp
});
}
if (schedulePlan.ScheduledDays != null)
{
parameters.Add(new NpgsqlParameter("schedule_days", JsonConvert.SerializeObject(schedulePlan.ScheduledDays, settings).ToString())
{
NpgsqlDbType = NpgsqlDbType.Json
});
}
if (schedulePlan.IntervalInMinutes.HasValue)
{
parameters.Add(new NpgsqlParameter("interval_in_minutes", schedulePlan.IntervalInMinutes)
{
NpgsqlDbType = NpgsqlDbType.Integer
});
}
if (schedulePlan.StartTimespan.HasValue)
{
parameters.Add(new NpgsqlParameter("start_timespan", schedulePlan.StartTimespan)
{
NpgsqlDbType = NpgsqlDbType.Integer
});
}
if (schedulePlan.EndTimespan.HasValue)
{
parameters.Add(new NpgsqlParameter("end_timespan", schedulePlan.EndTimespan)
{
NpgsqlDbType = NpgsqlDbType.Integer
});
}
if (schedulePlan.LastTriggerTime.HasValue)
{
parameters.Add(new NpgsqlParameter("last_trigger_time", schedulePlan.LastTriggerTime)
{
NpgsqlDbType = NpgsqlDbType.Timestamp
});
}
if (schedulePlan.NextTriggerTime.HasValue)
{
parameters.Add(new NpgsqlParameter("next_trigger_time", schedulePlan.NextTriggerTime)
{
NpgsqlDbType = NpgsqlDbType.Timestamp
});
}
else
{
parameters.Add(new NpgsqlParameter("next_trigger_time", DBNull.Value)
{
NpgsqlDbType = NpgsqlDbType.Timestamp
});
}
parameters.Add(new NpgsqlParameter("enabled", schedulePlan.Enabled)
{
NpgsqlDbType = NpgsqlDbType.Boolean
});
if (schedulePlan.LastStartedJobId.HasValue)
{
parameters.Add(new NpgsqlParameter("last_started_job_id", schedulePlan.LastStartedJobId)
{
NpgsqlDbType = NpgsqlDbType.Uuid
});
}
parameters.Add(new NpgsqlParameter("last_modified_on", DateTime.UtcNow)
{
NpgsqlDbType = NpgsqlDbType.Timestamp
});
if (schedulePlan.LastModifiedBy.HasValue)
{
parameters.Add(new NpgsqlParameter("last_modified_by", schedulePlan.LastModifiedBy)
{
NpgsqlDbType = NpgsqlDbType.Uuid
});
}
string setClause = "";
foreach (NpgsqlParameter param in parameters)
{
if (param.ParameterName != "id")
{
setClause += $"{param.ParameterName} = @{param.ParameterName}, ";
}
}
setClause = setClause.Remove(setClause.Length - 2, 2);
string sql = $"UPDATE schedule_plan SET {setClause} WHERE id = @id";
return(ExecuteNonQuerySqlCommand(sql, parameters));
}
public bool CreateSchedule(SchedulePlan schedulePlan)
{
JsonSerializerSettings settings = new JsonSerializerSettings {
TypeNameHandling = TypeNameHandling.All
};
List <NpgsqlParameter> parameters = new List <NpgsqlParameter>();
parameters.Add(new NpgsqlParameter("id", schedulePlan.Id)
{
NpgsqlDbType = NpgsqlDbType.Uuid
});
parameters.Add(new NpgsqlParameter("name", schedulePlan.Name)
{
NpgsqlDbType = NpgsqlDbType.Text
});
parameters.Add(new NpgsqlParameter("type", (int)schedulePlan.Type)
{
NpgsqlDbType = NpgsqlDbType.Integer
});
parameters.Add(new NpgsqlParameter("job_type_id", schedulePlan.JobTypeId)
{
NpgsqlDbType = NpgsqlDbType.Uuid
});
if (schedulePlan.StartDate.HasValue)
{
parameters.Add(new NpgsqlParameter("start_date", schedulePlan.StartDate)
{
NpgsqlDbType = NpgsqlDbType.Timestamp
});
}
if (schedulePlan.EndDate.HasValue)
{
parameters.Add(new NpgsqlParameter("end_date", schedulePlan.EndDate)
{
NpgsqlDbType = NpgsqlDbType.Timestamp
});
}
parameters.Add(new NpgsqlParameter("schedule_days", JsonConvert.SerializeObject(schedulePlan.ScheduledDays, settings).ToString())
{
NpgsqlDbType = NpgsqlDbType.Json
});
if (schedulePlan.IntervalInMinutes.HasValue)
{
parameters.Add(new NpgsqlParameter("interval_in_minutes", schedulePlan.IntervalInMinutes)
{
NpgsqlDbType = NpgsqlDbType.Integer
});
}
if (schedulePlan.StartTimespan.HasValue)
{
parameters.Add(new NpgsqlParameter("start_timespan", schedulePlan.StartTimespan)
{
NpgsqlDbType = NpgsqlDbType.Integer
});
}
if (schedulePlan.EndTimespan.HasValue)
{
parameters.Add(new NpgsqlParameter("end_timespan", schedulePlan.EndTimespan)
{
NpgsqlDbType = NpgsqlDbType.Integer
});
}
if (schedulePlan.LastTriggerTime.HasValue)
{
parameters.Add(new NpgsqlParameter("last_trigger_time", schedulePlan.LastTriggerTime)
{
NpgsqlDbType = NpgsqlDbType.Timestamp
});
}
if (schedulePlan.NextTriggerTime.HasValue)
{
parameters.Add(new NpgsqlParameter("next_trigger_time", schedulePlan.NextTriggerTime)
{
NpgsqlDbType = NpgsqlDbType.Timestamp
});
}
parameters.Add(new NpgsqlParameter("job_attributes", JsonConvert.SerializeObject(schedulePlan.JobAttributes, settings).ToString())
{
NpgsqlDbType = NpgsqlDbType.Text
});
parameters.Add(new NpgsqlParameter("enabled", schedulePlan.Enabled)
{
NpgsqlDbType = NpgsqlDbType.Boolean
});
if (schedulePlan.LastStartedJobId.HasValue)
{
parameters.Add(new NpgsqlParameter("last_started_job_id", schedulePlan.LastStartedJobId)
{
NpgsqlDbType = NpgsqlDbType.Uuid
});
}
parameters.Add(new NpgsqlParameter("created_on", DateTime.UtcNow)
{
NpgsqlDbType = NpgsqlDbType.Timestamp
});
parameters.Add(new NpgsqlParameter("last_modified_on", DateTime.UtcNow)
{
NpgsqlDbType = NpgsqlDbType.Timestamp
});
if (schedulePlan.LastModifiedBy.HasValue)
{
parameters.Add(new NpgsqlParameter("last_modified_by", schedulePlan.LastModifiedBy)
{
NpgsqlDbType = NpgsqlDbType.Uuid
});
}
string columns = "";
string values = "";
foreach (NpgsqlParameter param in parameters)
{
columns += $"{param.ParameterName}, ";
values += $"@{param.ParameterName}, ";
}
columns = columns.Remove(columns.Length - 2, 2);
values = values.Remove(values.Length - 2, 2);
string sql = $"INSERT INTO schedule_plan ({columns}) VALUES ({values})";
return(ExecuteNonQuerySqlCommand(sql, parameters));
}
public void TriggerNowSchedulePlan(SchedulePlan schedulePlan)
{
schedulePlan.NextTriggerTime = DateTime.UtcNow.AddMinutes(1);
UpdateSchedulePlanShort(schedulePlan);
}
private bool UpdateSchedulePlanShort(SchedulePlan schedulePlan)
{
return(JobService.UpdateSchedule(schedulePlan.Id, schedulePlan.LastTriggerTime, schedulePlan.NextTriggerTime,
schedulePlan.LastModifiedBy, schedulePlan.LastStartedJobId));
}
public bool UpdateSchedulePlan(SchedulePlan schedulePlan)
{
return(JobService.UpdateSchedule(schedulePlan));
}
private DateTime?FindIntervalSchedulePlanNextTriggerDate(SchedulePlan intervalPlan, DateTime nowDateTime, DateTime?lastExecution)
{
if (intervalPlan.ScheduledDays == null)
{
intervalPlan.ScheduledDays = new SchedulePlanDaysOfWeek();
}
var daysOfWeek = intervalPlan.ScheduledDays;
//if interval is <=0 then can't find match
if (intervalPlan.IntervalInMinutes <= 0)
{
return(null);
}
DateTime startingDate = lastExecution.HasValue ? lastExecution.Value.AddMinutes(intervalPlan.IntervalInMinutes.Value) : DateTime.UtcNow;
try
{
while (true)
{
//check for expired interval
if (intervalPlan.EndDate.HasValue)
{
if (intervalPlan.EndDate.Value < startingDate)
{
return(null);
}
}
int timeAsInt = startingDate.Hour * 60 + startingDate.Minute;
bool isIntervalConnectedToFirstDay = false;
if (intervalPlan.StartTimespan.HasValue)
{
isIntervalConnectedToFirstDay = ((intervalPlan.StartTimespan.Value > intervalPlan.EndTimespan.Value) &&
((0 < timeAsInt) && (timeAsInt <= intervalPlan.EndTimespan.Value)));
}
if (IsTimeInTimespanInterval(startingDate, intervalPlan.StartTimespan, intervalPlan.EndTimespan))
{
if (IsDayUsedInSchedulePlan(startingDate, daysOfWeek, isIntervalConnectedToFirstDay))
{
return(startingDate);
}
else
{
startingDate = startingDate.AddDays(1);
}
continue;
}
else //step
{
DateTime startTimespan = new DateTime(startingDate.Year, startingDate.Month, startingDate.Day, 0, 0, 0, DateTimeKind.Utc);
startTimespan = startTimespan.AddMinutes(intervalPlan.StartTimespan.Value);
if (nowDateTime <= startTimespan && startingDate <= startTimespan)
{
startingDate = startTimespan;
}
else
{
startingDate = startTimespan.AddDays(1);
}
}
}
}
catch
{
return(null);
}
}