protected void aspxCallback_Callback(object sender, DevExpress.Web.ASPxCallback.CallbackEventArgs e)
{
var sqlJob = jobSchedulerService.GetJobById(Convert.ToInt32(hdnJobId.Value));
try
{
// TODO: Validar
switch (e.Parameter)
{
case "SCHEDULEMANUAL":
// TODO: Analizar como se hace el agendamiento manual
e.Result = "SCHEDULEDMANUALOK";
break;
case "SCHEDULEAUTOMATIC":
var sqlJobTrigger = new SqlJobTrigger();
jobSchedulerService.AddTrigger(sqlJobTrigger);
e.Result = "SCHEDULEDAUTOMATICOK";
break;
default:
break;
}
}
catch (Exception ex)
{
throw new Exception("No se ha podido agendar el proceso debido al siguiente error: " + ex.Message);
}
}
public void Setup()
{
_schedulerService = new DefaultJobSchedulerService();
_schedulerService.UnitOfWork = new UnitOfWork();
ISchedulerFactory sf = new StdSchedulerFactory();
((DefaultJobSchedulerService)_schedulerService).Scheduler = sf.GetScheduler();
_schedulerService.Start();
_sqlJobTrigger = new SqlJobTrigger
{
CreatedBy = "Pepe",
CreatedDate = DateTime.Now,
Enabled = true,
JobLogs = new List<JobLog>(),
JobTriggerStatus = JobTriggerStatus.NoAgendado,
XmlFormInputValues = "<prueba>Prueba</prueba>",
};
_sqlJob = new SqlJob
{
Comments = "blabla",
CreatedBy = "sdsd",
DatabaseName = "CGControlPanel3",
CreatedDate = DateTime.Now,
Deleted = false,
DeletedBy = string.Empty,
Description = "Descripcion",
IsFavorite = true,
ExecutionDays = 1,
Group = "Replicación",
Name = "Job Replicación 1",
JobType = JobType.Automatico,
InputSchemaProcedure = "wdwdsadsa",
JOB_GROUP = "wwww",
JOB_NAME = "adasas",
JobStatus = JobStatus.Scheduled,
JobStatusEnum = 1,
JobTypeEnum = 1,
Password = "******",
Logs = new List<JobLog>(),
Triggers = new List<JobTrigger>
{
_sqlJobTrigger
},
SCHED_NAME = "asdsdsa",
LastExecutionStatus = LastExecutionStatus.Success,
LastExecutionStatusEnum = 1,
ServerName = @".\SQLEXPRESS",
UserName = "******",
ExecProcedure = "OP_PROCESO_EJEMPLO_EXEC",
};
_sqlJobTrigger.Job = _sqlJob;
}
public IList<JobLog> GetJobLog(int jobId)
{
var jobsList = new List<JobLog>();
var jobTrigger = new SqlJobTrigger();
if (jobId == 12)
{
jobsList.Add(new JobLog { JobTrigger = jobTrigger, ExecutionDate = DateTime.Now, JobLogId = 1, JobLogStatus = JobLogStatus.Success });
jobsList.Add(new JobLog { JobTrigger = jobTrigger, ExecutionDate = DateTime.Now, JobLogId = 2, JobLogStatus = JobLogStatus.Error });
}
if (jobId == 23)
{
jobsList.Add(new JobLog
{
JobTrigger = jobTrigger,
ExecutionDate = DateTime.Now,
JobLogId = 3,
JobLogStatus = JobLogStatus.Error
});
}
if (jobId == 34)
{
jobsList.Add(new JobLog
{
JobTrigger = jobTrigger,
ExecutionDate = DateTime.Now,
JobLogId = 4,
JobLogStatus = JobLogStatus.Error
});
}
if (jobId == 45)
{
jobsList.Add(new JobLog
{
JobTrigger = jobTrigger,
ExecutionDate = DateTime.Now,
JobLogId = 5,
JobLogStatus = JobLogStatus.Error
});
jobsList.Add(new JobLog
{
JobTrigger = jobTrigger,
ExecutionDate = DateTime.Now,
JobLogId = 6,
JobLogStatus = JobLogStatus.Success
});
}
return jobsList;
}
public Job GetJobById(int JobId)
{
SqlJobTrigger sqlJobTrigger = new SqlJobTrigger
{
JobTriggerId = 1,
CreatedBy = "Carlos",
XmlFormInputValues = "Input Values",
XmlTableInput = "XML Input Values",
XmlTableOutput = "XML Output",
XmlResult = "XML Result",
RecordsProcessed = 100,
RecordsAffected = 10,
JobTriggerStatus = JobTriggerStatus.Agendado
};
var triggers = new List<JobTrigger>();
triggers.Add(sqlJobTrigger);
return new SqlJob
{
JobId = 1,
Triggers = triggers,
IsFavorite = true,
ExecutionDays = 1,
Group = "Replicación",
Name = "Job Replicación 1",
JobType = JobType.Automatico,
ServerName = "SQL1",
DatabaseName = "CGPlanos",
Description = "Esto es una descripción de las tareas que realiza el proceso",
UserName = "******",
CreatedBy = "Carlos.Daniel.Vazquez"
};
}
public void Setup()
{
stream = new FileStream(@"C:\test.xlsx", FileMode.Open, FileAccess.Read, FileShare.ReadWrite);
IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);
excelReader.IsFirstRowAsColumnNames = true;
DataSet result = excelReader.AsDataSet();
ISchedulerFactory sf = new StdSchedulerFactory();
if (_schedulerService == null)
{
_schedulerService = new DefaultJobSchedulerService();
((DefaultJobSchedulerService)_schedulerService).Scheduler = sf.GetScheduler();
}
_unitOfWork = new UnitOfWork();
_schedulerService.UnitOfWork = _unitOfWork;
//_schedulerService.Resume();
_sqlJobTrigger = new SqlJobTrigger
{
CreatedBy = "Pepe",
CreatedDate = DateTime.Now,
Enabled = true,
JobTriggerStatus = JobTriggerStatus.NoAgendado,
InputFormXmlValues = "<prueba>Prueba</prueba>",
ScheduledStartExecutionDate = DateTime.Now
};
_sqlJob = new SqlJob
{
Comments = "blabla",
CreatedBy = "sdsd",
DatabaseName = "cgQuartz",
CreatedDate = DateTime.Now,
Deleted = false,
DeletedBy = string.Empty,
Description = "Descripcion",
IsFavorite = true,
Group = "Replicación",
Name = "Job Replicación 1",
JobType = JobType.Automático,
InputSchemaProcedure = "wdwdsadsa",
JobTypeEnum = 1,
Password = "******",
ServerName = @".\SQLEXPRESS",
UserName = "******",
ExecProcedure = "OP_PROCESO_QUARTZ_EXEC",
};
_sqlJobTrigger2 = new SqlJobTrigger
{
CreatedBy = "Pepe",
CreatedDate = DateTime.Now,
Enabled = true,
JobTriggerStatus = JobTriggerStatus.NoAgendado,
InputFormXmlValues = "<prueba>Prueba2</prueba>",
ScheduledStartExecutionDate = DateTime.Now
};
_sqlJobTrigger3 = new SqlJobTrigger
{
CreatedBy = "Pepe",
CreatedDate = DateTime.Now,
Enabled = true,
JobTriggerStatus = JobTriggerStatus.NoAgendado,
InputFormXmlValues = "<prueba>Prueba2</prueba>",
ScheduledStartExecutionDate = DateTime.Now
};
_sqlJobTrigger4 = new SqlJobTrigger
{
CreatedBy = "Pepe",
CreatedDate = DateTime.Now,
Enabled = true,
JobTriggerStatus = JobTriggerStatus.NoAgendado,
InputFormXmlValues = "<prueba>Prueba2</prueba>",
ScheduledStartExecutionDate = DateTime.Now
};
_sqlJobTrigger5 = new SqlJobTrigger
{
CreatedBy = "Pepe",
CreatedDate = DateTime.Now,
Enabled = true,
JobTriggerStatus = JobTriggerStatus.NoAgendado,
InputFormXmlValues = "<prueba>Prueba2</prueba>",
ScheduledStartExecutionDate = DateTime.Now
};
_sqlJobTrigger6 = new SqlJobTrigger
{
CreatedBy = "Pepe",
CreatedDate = DateTime.Now,
Enabled = true,
JobTriggerStatus = JobTriggerStatus.NoAgendado,
InputFormXmlValues = "<prueba>Prueba2</prueba>",
ScheduledStartExecutionDate = DateTime.Now
};
_sqlJob2 = new SqlJob
{
Comments = "blabla2",
CreatedBy = "sdsd2",
DatabaseName = "cgQuartz",
CreatedDate = DateTime.Now,
Deleted = false,
DeletedBy = string.Empty,
Description = "Descripcion",
IsFavorite = true,
Group = "Replicación",
Name = "Job Replicación 2",
JobType = JobType.Automático,
InputSchemaProcedure = "wdwdsadsa2",
JobTypeEnum = 1,
Password = "******",
ServerName = @".\SQLEXPRESS",
UserName = "******",
ExecProcedure = "OP_PROCESO_QUARTZ_EXEC2",
};
_sqlJob3 = new SqlJob
{
Comments = "blabla3",
CreatedBy = "sdsd3",
DatabaseName = "cgQuartz",
CreatedDate = DateTime.Now,
Deleted = false,
DeletedBy = string.Empty,
Description = "Descripcion",
IsFavorite = true,
Group = "Replicación",
Name = "Job Replicación 3",
JobType = JobType.Automático,
InputSchemaProcedure = "wdwdsadsa3",
JobTypeEnum = 1,
Password = "******",
ServerName = @".\SQLEXPRESS",
UserName = "******",
ExecProcedure = "OP_PROCESO_QUARTZ_EXEC3",
};
_sqlJob4 = new SqlJob
{
Comments = "blabla4",
CreatedBy = "sdsd4",
DatabaseName = "cgQuartz",
CreatedDate = DateTime.Now,
Deleted = false,
DeletedBy = string.Empty,
Description = "Descripcion",
IsFavorite = true,
Group = "Replicación",
Name = "Job Replicación 4",
JobType = JobType.Automático,
InputSchemaProcedure = "wdwdsadsa4",
JobTypeEnum = 1,
Password = "******",
ServerName = @".\SQLEXPRESS",
UserName = "******",
ExecProcedure = "OP_PROCESO_QUARTZ_EXEC4",
};
_sqlJob4 = new SqlJob
{
Comments = "blabla5",
CreatedBy = "sdsd5",
DatabaseName = "cgQuartz",
CreatedDate = DateTime.Now,
Deleted = false,
DeletedBy = string.Empty,
Description = "Descripcion",
IsFavorite = true,
Group = "Replicación",
Name = "Job Replicación 5",
JobType = JobType.Automático,
InputSchemaProcedure = "wdwdsadsa5",
JobTypeEnum = 1,
Password = "******",
ServerName = @".\SQLEXPRESS",
UserName = "******",
ExecProcedure = "OP_PROCESO_QUARTZ_EXEC5",
};
_sqlJob5 = new SqlJob
{
Comments = "blabla6",
CreatedBy = "sdsd6",
DatabaseName = "cgQuartz",
CreatedDate = DateTime.Now,
Deleted = false,
DeletedBy = string.Empty,
Description = "Descripcion",
IsFavorite = true,
Group = "Replicación",
Name = "Job Replicación 6",
JobType = JobType.Automático,
InputSchemaProcedure = "wdwdsadsa6",
JobTypeEnum = 1,
Password = "******",
ServerName = @".\SQLEXPRESS",
UserName = "******",
ExecProcedure = "OP_PROCESO_QUARTZ_EXEC6",
};
}
protected void aspxCallback_Callback(object sender, DevExpress.Web.ASPxCallback.CallbackEventArgs e)
{
var sqlJob = jobSchedulerService.GetJobById(Convert.ToInt32(hdnJobId.Value));
try
{
IFormatProvider culture = new CultureInfo("es-AR", true);
var date = DateTime.Parse(dtDate.Value,
culture,
DateTimeStyles.NoCurrentDateDefault);
var selectedDateTime = date; //new DateTime(date.Year, date.Month, date.Day);
switch (e.Parameter)
{
case "SCHEDULEMANUAL":
// Se agrega el trigger pero deshabilitado, para que pueda ser ejecutado desde la grilla de ejecución cuando se corra el comando Ejecutar desde el menú contextual.
// No se agrega en Quartz, solo en CGControlPanel.
e.Result = "SCHEDULEDMANUALOK";
var sqlManualJobTrigger = new SqlJobTrigger
{
CreatedBy = User.Identity.Name,
CreatedDate = DateTime.Now,
Enabled = false, // DESHABILITADO
ScheduledStartExecutionDate = selectedDateTime.AddMinutes(59).AddHours(11),
JobTriggerStatus = JobTriggerStatus.Agendado
};
jobSchedulerService.AddTrigger(sqlJob, sqlManualJobTrigger);
break;
case "SCHEDULEAUTOMATIC":
var time = DateTime.Parse(dtTime.Value,
culture,
DateTimeStyles.NoCurrentDateDefault);
selectedDateTime = new DateTime(date.Year, date.Month, date.Day, time.Hour, time.Minute, time.Second);
// Al crear el trigger se lo debe inicializar obligatoriamente con los siguientes datos.
// De todas maneras hay un método Validate que valida el objeto dentro de AddTrigger.
// JobTriggerStatus = JobTriggerStatus.Agendado;
// CreatedBy = User.Identity.Name;
// ScheduledStartExecutionDate = DateTime.Now;
// CreatedDate = DateTime.Now;
// Opcional Enabled = true;
var sqlJobTrigger = new SqlJobTrigger
{
CreatedBy = User.Identity.Name,
CreatedDate = DateTime.Now,
Enabled = true,
ScheduledStartExecutionDate = selectedDateTime,
JobTriggerStatus = JobTriggerStatus.Agendado
};
jobSchedulerService.AddTrigger(sqlJob, sqlJobTrigger);
e.Result = "SCHEDULEDAUTOMATICOK";
break;
}
}
catch (Exception ex)
{
if (log.IsErrorEnabled)
log.Error(Utils.UI.Helper.BuildRecursiveErrorMessage(ex));
e.Result = "Error:\r\n" + Utils.UI.Helper.BuildRecursiveErrorMessage(ex);
}
}
protected void aspxCallback_Callback(object sender, DevExpress.Web.ASPxCallback.CallbackEventArgs e)
{
try
{
var job = jobSchedulerService.GetJobById(Convert.ToInt32(hdnJobId.Value));
string xmlForm;
string formValues;
SqlJobTrigger sqlJobTrigger;
switch (e.Parameter)
{
// TODO: VERIFICAR SI NO ES UN JOB YA AGENDADO POR LO QUE HAY QUE UTILIZAR EL METODO CON EL SQLJOBTRIGGER COMO ARGUMENTO.
case "EXECUTEMANUALJOBTRIGGER":
var jobTrigger = (SqlJobTrigger)jobSchedulerService.GetJobTriggerById(Convert.ToInt32(hdnJobTriggerId.Value));
xmlForm = jobSchedulerService.GetInputFormSchema(job);
formValues = Utils.UI.Helper.GetFormValues(xmlForm, Request.Form);
// Al crear el trigger se lo debe inicializar obligatoriamente con los siguientes datos
// JobTriggerStatus = JobTriggerStatus.Agendado;
// CreatedBy = User.Identity.Name;
// ScheduledStartExecutionDate = DateTime.Now;
// CreatedDate = DateTime.Now;
// Opcional Enabled = true;
jobTrigger.InputFormXmlValues = formValues;
jobTrigger.CreatedDate = DateTime.Now;
jobTrigger.ScheduledStartExecutionDate = DateTime.Now;
jobTrigger.CreatedBy = User.Identity.Name;
jobTrigger.Enabled = true;
jobTrigger.JobTriggerStatus = JobTriggerStatus.Agendado;
// Actualizar el JobTrigger con los nuevos datos.
jobSchedulerService.ExecuteManualJob(jobTrigger);
e.Result = "MANUALJOBEXECUTED";
break;
case "EXECUTEMANUALJOB":
xmlForm = jobSchedulerService.GetInputFormSchema(job);
formValues = Utils.UI.Helper.GetFormValues(xmlForm, Request.Form);
// Al crear el trigger se lo debe inicializar obligatoriamente con los siguientes datos
// JobTriggerStatus = JobTriggerStatus.Agendado;
// CreatedBy = User.Identity.Name;
// ScheduledStartExecutionDate = DateTime.Now;
// CreatedDate = DateTime.Now;
// Opcional Enabled = true;
sqlJobTrigger = new SqlJobTrigger
{
InputFormXmlValues = formValues,
CreatedDate = DateTime.Now,
ScheduledStartExecutionDate = DateTime.Now,
CreatedBy = User.Identity.Name,
Enabled = true,
JobTriggerStatus = JobTriggerStatus.Agendado
};
jobSchedulerService.ExecuteManualJob(job, sqlJobTrigger);
e.Result = "MANUALJOBEXECUTED";
break;
case "EXECUTECHECK":
xmlForm = jobSchedulerService.GetInputFormSchema(job);
formValues = Utils.UI.Helper.GetFormValues(xmlForm, Request.Form);
e.Result = Utils.UI.Helper.FormatXml(formValues);
break;
}
}
catch (Exception ex)
{
e.Result = "ERROR: " + ex.Message;
}
}
protected void uplImage_FileUploadComplete(object sender, FileUploadCompleteEventArgs e)
{
var file = UpFile.UploadedFiles.First();
if (file.FileName.Contains(".xls") && file.IsValid)
{
file.SaveAs((@"C:\Excel\Entrada\" + file.FileName));
var job = jobSchedulerService.GetJobById(Convert.ToInt32(Request["jobId"]));
var xmlForm = jobSchedulerService.GetInputFormSchema(job);
var xml = new XmlDocument();
xml.LoadXml(xmlForm);
var formValues = Utils.UI.Helper.GetValuesFromExcelFile(@"C:\Excel\Entrada\" + file.FileName, xml);
if (!string.IsNullOrEmpty(formValues))
{
var sqlJobTrigger = new SqlJobTrigger
{
InputFormXmlValues = formValues,
CreatedDate = DateTime.Now,
ScheduledStartExecutionDate = DateTime.Now,
CreatedBy = User.Identity.Name,
Enabled = true,
JobTriggerStatus = JobTriggerStatus.Agendado
};
jobSchedulerService.ExecuteManualJob(job, sqlJobTrigger);
return;
}
e.IsValid = false;
e.ErrorText = "Los datos del archivo no son correctos";
}
e.IsValid = false;
e.ErrorText = "El formato de archivo no es correcto";
}
public JobTrigger GetJobTriggerById(int JobTriggerId)
{
var sqlJob = new SqlJob
{
JobId = 1,
IsFavorite = true,
Group = "Replicación",
Name = "Job Replicación 1",
Comments = "Comentarios",
JobType = JobType.Manual,
AutomaticProcessTime = DateTime.Now,
ServerName = "SQL1",
DatabaseName = "CGPlanos",
Description = "Esto es una descripción de las tareas que realiza el proceso",
UserName = "******",
CreatedBy = "Carlos.Daniel.Vazquez",
Weekdays = 21
};
var sqlJobTrigger = new SqlJobTrigger
{
JobTriggerId = JobTriggerId,
CreatedBy = "Carlos",
InputFormXmlValues = "Input Values",
InputXmlTable = "XML Input Values",
OutputXmlTable = "XML Output",
RecordsProcessed = 100,
RecordsAffected = 10,
Job =sqlJob,
JobTriggerStatus = JobTriggerStatus.Ejecutado,
ScheduledStartExecutionDate = DateTime.Now.AddDays(-1),
StartExecutionDate = DateTime.Now.AddDays(-1),
EndExecutionDate = DateTime.Now,
OutputExecutionStatus = "Ejecutado SIN Errores",
OutputExecutionLog = "<table width='100%'><tr><td>test</td></tr></table>",
OutputExecutionResult= @"<p>resultado 1</p><p>resultado 2</p><p>resultado 3</p>"
};
// Si NO se ejecutó
if (JobTriggerId != 3)
{
sqlJobTrigger.StartExecutionDate = null;
sqlJobTrigger.JobTriggerStatus = JobTriggerStatus.Agendado;
sqlJobTrigger.OutputExecutionLog = "-";
sqlJobTrigger.OutputExecutionResult = "-";
sqlJobTrigger.OutputExecutionStatus = "-";
sqlJobTrigger.InputFormXmlValues = "-";
sqlJobTrigger.InputXmlTable = "-";
sqlJobTrigger.OutputXmlTable = "-";
sqlJobTrigger.RecordsProcessed = 0;
sqlJobTrigger.RecordsAffected = 0;
sqlJobTrigger.EndExecutionDate = null;
sqlJobTrigger.Job.Weekdays = 0;
}
// En Ejecución
if (JobTriggerId == 2)
{
sqlJobTrigger.StartExecutionDate = DateTime.Now;
sqlJobTrigger.Job.Weekdays = 6;
sqlJobTrigger.JobTriggerStatus = JobTriggerStatus.Ejecutando;
sqlJobTrigger.Job.JobType = JobType.Automático;
}
return sqlJobTrigger;
}
protected void scheduleJob_Callback(object sender, DevExpress.Web.ASPxCallback.CallbackEventArgs e)
{
int jobTriggerId = 0;
//TODO: Agregar el job como un posible job para ejecución en el día actual. SIN AGENDA, o agendar si es Automático? VERIFICAR
var currentGridSelected = e.Parameter.Split('|')[2];
var currentGrid = GetServerSideGridView(currentGridSelected);
var jobId = Convert.ToInt32(currentGrid.GetRowValues(Convert.ToInt32(e.Parameter.Split('|')[1]), "JobId"));
try
{
var sqlJob = jobSchedulerService.GetJobById(jobId);
switch (e.Parameter.Split('|')[0])
{
// PROCESO AGENDADO PREVIAMENTE. SE EJECUTA DESDE LA GRILLA DE EJECUCIÓN
case "EXECUTESCHEDULEDMANUALPROCESS":
jobTriggerId = Convert.ToInt32(currentGrid.GetRowValues(Convert.ToInt32(e.Parameter.Split('|')[1]), "JobTriggerId"));
var sqlJobTrigger = jobSchedulerService.GetJobTriggerById(jobTriggerId);
if (sqlJobTrigger.JobTriggerStatus != JobTriggerStatus.Agendado)
{
e.Result = "ERRORPROCESSEXECUTED";
break;
}
jobSchedulerService.ExecuteManualJob(sqlJobTrigger);
e.Result = "PROCESSOK";
break;
// PROCESO MANUAL SIN AGENDAMIENTO PREVIO
case "EXECUTEMANUALPROCESS":
var sqlManualJobTrigger = new SqlJobTrigger
{
CreatedBy = User.Identity.Name,
CreatedDate = DateTime.Now,
Enabled = true,
StartExecutionDate = DateTime.Now,
JobTriggerStatus = JobTriggerStatus.Ejecutando
};
jobSchedulerService.ExecuteManualJob(sqlJob, sqlManualJobTrigger);
e.Result = "PROCESSOK";
break;
// PROCESO AUTOMATICO SIN AGENDAMIENTO PREVIO
case "EXECUTEAUTOMATICPROCESS":
var sqlNewJobTrigger = new SqlJobTrigger
{
CreatedBy = User.Identity.Name,
CreatedDate = DateTime.Now,
Enabled = true,
ScheduledStartExecutionDate = DateTime.Now,
JobTriggerStatus = JobTriggerStatus.Ejecutando
};
jobSchedulerService.AddTrigger(sqlJob, sqlNewJobTrigger);
e.Result = "PROCESSOK";
break;
default:
break;
}
}
catch (Exception ex)
{
if (log.IsErrorEnabled)
log.Error(Utils.UI.Helper.BuildRecursiveErrorMessage(ex));
e.Result = "Error:\r\n" + Utils.UI.Helper.BuildRecursiveErrorMessage(ex);
}
}
public void ProcessDailyJobs()
{
var jobs = UnitOfWork.JobsRepository.GetJobsByDaily(DateTime.Now).Where(x => x.JobType == JobType.Automático);
foreach (var job in jobs)
{
if (job.AutomaticProcessTime.HasValue)
{
var automaticProcessTime = DateTime.Now.Date.AddHours(job.AutomaticProcessTime.Value.Hour).AddMinutes(job.AutomaticProcessTime.Value.Minute);
var trigger = new SqlJobTrigger
{
CreatedBy = job.CreatedBy,
CreatedDate = DateTime.Now,
Enabled = true,
ScheduledStartExecutionDate = automaticProcessTime ,
JobTriggerStatus = JobTriggerStatus.Agendado
};
AddTrigger(job, trigger);
}
}
}