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", 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
                });
            }
            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", 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) &&
                        IsDayUsedInSchedulePlan(startingDate, daysOfWeek, isIntervalConnectedToFirstDay))
                    {
                        return(startingDate);
                    }
                    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);
            }
        }