protected override Task ExecuteJob(IJobExecutionContext context)
{
var dataMap = context.MergedJobDataMap;
ScriptPath = dataMap.GetString("shell-script-path");
ShellName = dataMap.GetString("shell") ?? "/bin/bash";
DbType = dataMap.GetString("db-type") ?? "MySql";
DbConnString = dataMap.GetString("conn-string") ?? "";
NoticeApi = dataMap.GetString("notice-api") ?? "";
NoticeApi2 = dataMap.GetString("notice-api-2") ?? "";
AfterShellSql = dataMap.GetString("after-shell-sql") ?? "";
BeforeShellSql = dataMap.GetString("before-shell-sql") ?? "";
var logService = new LogService(DbType, DbConnString);
Stopwatch stopwatch = new Stopwatch();
//如果表还没初始化
if (TableInit.TableInit.IsInit == false)
{
TableInit.TableInit.Init(DbType, DbConnString);
TableInit.TableInit.IsInit = true;
}
if (IsActive)
{
Logger.Info("存在执行中任务,跳过该周期!");
}
else
{
IsActive = true;
string command = string.Empty;
string querySql;
long? task_detail_id = 0;
string ttime = string.Empty;
string thour = string.Empty;
string phone_number = string.Empty;
string type1 = string.Empty;
string type3 = string.Empty;
int step_no = 1;
// Logger.Info($"任务数据--{Environment.NewLine}ShellName={ShellName}{Environment.NewLine}ScriptPath={ScriptPath}{Environment.NewLine}DbType={DbType}{Environment.NewLine}DbConnString={DbConnString}{Environment.NewLine}BeforeShellSql={BeforeShellSql}{Environment.NewLine}AfterShellSql={AfterShellSql}{Environment.NewLine}NoticeApi={NoticeApi}{Environment.NewLine}NoticeApi2={NoticeApi2}");
try
{
DbConnection conn;
switch (DbType)
{
case "MsSql":
conn = new SqlConnection(DbConnString);
querySql = @"select top 1 task_detail_id,ttime,thour,def_cellname,type1,type3 from manager_task_detail where reply='1001' ";
break;
default:
conn = new MySqlConnection(DbConnString);
querySql = @"select task_detail_id,ttime,thour,def_cellname,type1,type3 from manager_task_detail where reply='1001' limit 1";
break;
}
using (conn)
{
try
{
conn.Open();
var cmd = conn.CreateCommand();
cmd.CommandType = System.Data.CommandType.Text;
cmd.CommandText = querySql;
cmd.CommandTimeout = 0;
var reader = cmd.ExecuteReader();
if (reader.HasRows)
{
List <string> arguments = new List <string>()
{
};
while (reader.Read())
{
//int i = 0;
task_detail_id = reader.GetInt64(0);
ttime = reader.GetString(1);
thour = reader.GetString(2);
phone_number = reader.GetString(3);
type1 = reader.GetString(4);
type3 = reader.GetString(5);
int type3Value = type3.Contains("未接通") ? 1 : type3.Contains("掉话") ? 2 : type3.ToUpper().Contains("ESRVCC切换失败") ? 4 : type3.Contains("切换失败") ? 3 : throw new ArgumentOutOfRangeException($"TYPE3不在范围内:{type3}");
Logger.Info($"查询到工单{{task_detail_id:{task_detail_id},ttime:{ttime},thour:{thour},phone_number:{phone_number},type1:{type1},type3:{type3}}}");
ShellName.Split(";").ToList().ForEach(shell =>
{
arguments.Add($"{shell} {ttime} {thour} {phone_number} {type1} {type3Value}");
});
break;
}
conn.Close();
if (!string.IsNullOrEmpty(BeforeShellSql))
{
conn.Open();
cmd.CommandText = BeforeShellSql.Replace("@select_date", ttime);//替换日期占位符
//记录日志到数据表
var step_1 = logService.Insert(new TableEntity.SingleUserAnalysisHistoryInfo()
{
col_create_time = DateTime.Now,
error_msg = "", //异常信息
run_status = RunStatus.执行中.ToString(),
spend_time = 0, //耗时
step_name = "（BeforeShellSql）执行shell脚本之前的sql脚本", //步骤名称
step_no = step_no, //步骤编号
step_remark = "", //备注
task_detail_id = task_detail_id ?? 0 //工单id
});
stopwatch.Restart(); //计时器开始
int asqResult = 0;
try
{
asqResult = cmd.ExecuteNonQuery();
Logger.Info($" {asqResult} 行受影响");
}
catch (Exception ex)
{
step_1.error_msg = ex.Message;
}
stopwatch.Stop();//计时器停止
step_1.run_status = RunStatus.已执行.ToString();
step_1.step_remark = $" {asqResult} 行受影响";
step_1.spend_time = (int)stopwatch.Elapsed.TotalSeconds;
logService.Update(step_1);
conn.Close();
}
Logger.Info($"--------------开始执行SHELL命令--------------");
arguments.ForEach(argument =>
{
step_no++;
//执行shell脚本
Logger.Info($"/bin/bash {argument}");
//记录日志到数据表
var step_2 = logService.Insert(new TableEntity.SingleUserAnalysisHistoryInfo()
{
col_create_time = DateTime.Now,
error_msg = "", //异常信息
run_status = RunStatus.执行中.ToString(),
spend_time = 0, //耗时
step_name = "执行shell脚本，脚本参数" + argument, //步骤名称
step_no = step_no, //步骤编号
step_remark = "", //备注
task_detail_id = task_detail_id ?? 0 //工单id
});
stopwatch.Restart(); //计时器开始
var result = ShellUtil.ExecuteCommand("/bin/bash", argument, null);
stopwatch.Stop();//计时器停止
step_2.run_status = RunStatus.已执行.ToString();
step_2.step_remark += "执行结果：" + (result.Item1 == true ? "无错误" : string.Concat("有错误,", result.Item2));
step_2.spend_time = (int)stopwatch.Elapsed.TotalSeconds;
logService.Update(step_2);
});
Logger.Info($"--------------执行SHELL命令完成--------------");
Logger.Info($"[AfterShellSql:]-- {AfterShellSql}");
if (!string.IsNullOrEmpty(AfterShellSql))
{
conn.Open();
cmd.CommandText = AfterShellSql.Replace("@select_date", ttime).Replace("@select_hour", thour);//替换日期占位符
//记录日志到数据表
var step_3 = logService.Insert(new TableEntity.SingleUserAnalysisHistoryInfo()
{
col_create_time = DateTime.Now,
error_msg = "", //异常信息
run_status = RunStatus.执行中.ToString(),
spend_time = 0, //耗时
step_name = "（AfterShellSql）执行shell脚本之后的sql脚本", //步骤名称
step_no = step_no, //步骤编号
step_remark = "", //备注
task_detail_id = task_detail_id ?? 0 //工单id
});
stopwatch.Restart(); //计时器开始
int asqResult = 0;
try
{
asqResult = cmd.ExecuteNonQuery();
Logger.Info($" {asqResult} 行受影响");
}
catch (Exception ex)
{
step_3.error_msg = ex.Message;
}
stopwatch.Stop();//计时器停止
step_3.run_status = RunStatus.已执行.ToString();
step_3.step_remark = $" {asqResult} 行受影响";
step_3.spend_time = (int)stopwatch.Elapsed.TotalSeconds;
logService.Update(step_3);
conn.Close();
}
step_no++;
//记录日志到数据表
var step_4 = logService.Insert(new TableEntity.SingleUserAnalysisHistoryInfo()
{
col_create_time = DateTime.Now,
error_msg = "", //异常信息
run_status = RunStatus.执行中.ToString(),
spend_time = 0, //耗时
step_name = "生成工单结果 并更新reply为1002", //步骤名称
step_no = step_no, //步骤编号
step_remark = "", //备注
task_detail_id = task_detail_id ?? 0 //工单id
});
stopwatch.Restart(); //计时器开始
//生成工单结果 并更新reply为1002
//NoticeApi2 : http://120.76.26.161:5000/api/WorkorderService/BuildCellQuestion
var httpResult = HttpUtil.HttpGet(NoticeApi2 + $"?task_detail_id={task_detail_id}", timeout: 120);
Logger.Info("生成工单结果:" + httpResult);
stopwatch.Stop();//计时器停止
step_4.run_status = RunStatus.已执行.ToString();
step_4.step_remark = httpResult;
step_4.spend_time = (int)stopwatch.Elapsed.TotalSeconds;
logService.Update(step_4);
//通知专家系统网站该工单已经处理完毕
//10秒/次*30次无结果则跳过，直接通知专家系统
int i = 0;
while (i++ < 30)
{
conn.Open();
cmd.CommandText = $"select reply from manager_task_detail where task_detail_id={task_detail_id}";
string analysisResult = (string)cmd.ExecuteScalar();
conn.Close();
if (analysisResult != "1001")
{
break;
}
Thread.Sleep(1000 * 10);
}
}
}
catch (Exception ex)
{
Logger.Error(ex);
}
finally
{
conn.Close();
//NoticeApi : http://120.76.26.161:5000/TaskManagement/OnTuAnalysisFinished
if (task_detail_id.GetValueOrDefault() != 0)
{
Logger.Info("通知专家系统:" + HttpUtil.HttpGet(NoticeApi + $"?id={task_detail_id}", timeout: 60));
}
}
//Thread.Sleep(10*1000);//测试存在执行中任务
}
}
catch (Exception ex)
{
Logger.Error(ex);
}
finally
{
IsActive = false;
}
}
return(TaskUtil.CompletedTask);
}
protected override Task ExecuteJob(IJobExecutionContext context)
{
var dataMap = context.MergedJobDataMap;
ScriptPath = dataMap.GetString("shell-script-path");
ShellName = dataMap.GetString("shell") ?? "/bin/bash";
DbType = dataMap.GetString("db-type") ?? "MySql";
DbConnString = dataMap.GetString("conn-string") ?? "";
AfterShellSql = dataMap.GetString("after-shell-sql") ?? "";
BeforeShellSql = dataMap.GetString("before-shell-sql") ?? "";
AutoDelete = dataMap.GetString("auto-delete") ?? "";
var logService = new LogService(DbType, DbConnString);
var topService = new TopService(DbType, DbConnString);
Stopwatch stopwatch = new Stopwatch();
if (IsActive)
{
Logger.Info("存在执行中任务,跳过该周期!");
}
else
{
IsActive = true;
string command = string.Empty;
string querySql = string.Empty;
long? task_id = 0;
string ttime = string.Empty;
string thour = string.Empty;
string phone_number = string.Empty;
string tableName = string.Empty;
string filePath = string.Empty;
string operate_type = "5";
int step_no = 1;
try
{
DbConnection conn;
switch (DbType)
{
case "MsSql":
conn = new SqlConnection(DbConnString);
//querySql = @"select task_id,list_order,UTCstr,logtime,logmillisecond,MSISDN from sig_ue_mr_top200_plan";
break;
default:
conn = new MySqlConnection(DbConnString);
//querySql = @"select task_id,list_order,UTCstr,logtime,logmillisecond,MSISDN from sig_ue_mr_top200_plan";
break;
}
using (conn)
{
try
{
conn.Open();
var cmd = conn.CreateCommand();
cmd.CommandType = System.Data.CommandType.Text;
TableEntity.sig_ue_mr_top200_job sig_ue_mr_top200_job = new TableEntity.sig_ue_mr_top200_job();
sig_ue_mr_top200_job = topService.Query("select * from sig_ue_mr_top200_job where completeTime is null or completeTime='' order by createTime limit 1");
if (sig_ue_mr_top200_job != null)
{
//更新任务状态
sig_ue_mr_top200_job.state = ExecStatus.运算中.ToString();
topService.Update(sig_ue_mr_top200_job);
cmd.CommandText = @"drop table if exists sig_ue_mr_top200_plan_tmp";
cmd.ExecuteNonQuery();
cmd.CommandText = @"create table sig_ue_mr_top200_plan_tmp(task_id int,list_order int,
UTCstr nvarchar(20),
logtime nvarchar(22),
logmillisecond nvarchar(10),
MSISDN nvarchar(15))";
cmd.ExecuteNonQuery();
//选取数据到临时表
cmd.CommandText = string.Format(@"insert into sig_ue_mr_top200_plan_tmp select task_id,list_order,UTCstr,logtime,logmillisecond,MSISDN from sig_ue_mr_top200_plan where task_id={0}", sig_ue_mr_top200_job.task_id);
cmd.ExecuteNonQuery();
querySql = string.Format(@"select task_id,list_order,UTCstr,logtime,logmillisecond,MSISDN from sig_ue_mr_top200_plan_tmp where task_id={0}", sig_ue_mr_top200_job.task_id);
cmd.CommandText = querySql;
cmd.CommandTimeout = 0;
var reader = cmd.ExecuteReader();
if (reader.HasRows)
{
List <string> arguments = new List <string>()
{
};
StringBuilder str = new StringBuilder();
while (reader.Read())
{
task_id = reader.GetInt64(0);
str.Append(reader.GetString(5));
str.Append(",");
str.Append(reader.GetString(2));
str.Append("|");
}
str.Remove(str.Length - 1, 1);
phone_number = str.ToString();
ShellName.Split(";").ToList().ForEach(shell =>
{
arguments.Add($"{shell} {ttime} {thour} {phone_number} {filePath} {operate_type}");
});
conn.Close();
Logger.Info($"[BeforeShellSql:]-- {BeforeShellSql}");
if (!string.IsNullOrEmpty(BeforeShellSql))
{
conn.Open();
cmd.CommandText = BeforeShellSql.Replace("@select_date", ttime);//替换日期占位符
//记录日志到数据表
var step_1 = logService.Insert(new TableEntity.SingleUserAnalysisHistoryInfo()
{
col_create_time = DateTime.Now,
error_msg = "", //异常信息
run_status = RunStatus.执行中.ToString(),
spend_time = 0, //耗时
step_name = "（BeforeShellSql）执行shell脚本之前的sql脚本", //步骤名称
step_no = step_no, //步骤编号
step_remark = "", //备注
task_detail_id = task_id ?? 0 //task_id
});
stopwatch.Restart(); //计时器开始
int asqResult = 0;
try
{
asqResult = cmd.ExecuteNonQuery();
Logger.Info($" {asqResult} 行受影响");
}
catch (Exception ex)
{
step_1.error_msg = ex.Message;
}
stopwatch.Stop();//计时器停止
step_1.run_status = RunStatus.已执行.ToString();
step_1.step_remark = $" {asqResult} 行受影响";
step_1.spend_time = (int)stopwatch.Elapsed.TotalSeconds;
logService.Update(step_1);
conn.Close();
}
Logger.Info($"--------------开始执行SHELL命令--------------");
arguments.ForEach(argument =>
{
step_no++;
//执行shell脚本
Logger.Info($"/bin/bash {argument}");
//记录日志到数据表
var step_2 = logService.Insert(new TableEntity.SingleUserAnalysisHistoryInfo()
{
col_create_time = DateTime.Now,
error_msg = "", //异常信息
run_status = RunStatus.执行中.ToString(),
spend_time = 0, //耗时
step_name = "执行shell脚本，脚本参数" + argument, //步骤名称
step_no = step_no, //步骤编号
step_remark = "", //备注
task_detail_id = task_id ?? 0 //task_id
});
stopwatch.Restart(); //计时器开始
var result = ShellUtil.ExecuteCommand("/bin/bash", argument, null);
stopwatch.Stop();//计时器停止
step_2.run_status = RunStatus.已执行.ToString();
step_2.step_remark += "执行结果：" + (result.Item1 == true ? "无错误" : string.Concat("有错误,", result.Item2));
step_2.spend_time = (int)stopwatch.Elapsed.TotalSeconds;
logService.Update(step_2);
});
Logger.Info($"--------------执行SHELL命令完成--------------");
Logger.Info($"[AfterShellSql:]-- {AfterShellSql}");
if (!string.IsNullOrEmpty(AfterShellSql))
{
conn.Open();
cmd.CommandText = AfterShellSql.Replace("@select_date", ttime).Replace("@select_hour", thour);//替换日期占位符
//记录日志到数据表
var step_3 = logService.Insert(new TableEntity.SingleUserAnalysisHistoryInfo()
{
col_create_time = DateTime.Now,
error_msg = "", //异常信息
run_status = RunStatus.执行中.ToString(),
spend_time = 0, //耗时
step_name = "（AfterShellSql）执行shell脚本之后的sql脚本", //步骤名称
step_no = step_no, //步骤编号
step_remark = "", //备注
task_detail_id = task_id ?? 0 //task_id
});
stopwatch.Restart(); //计时器开始
int asqResult = 0;
try
{
asqResult = cmd.ExecuteNonQuery();
Logger.Info($" {asqResult} 行受影响");
}
catch (Exception ex)
{
step_3.error_msg = ex.Message;
}
stopwatch.Stop();//计时器停止
step_3.run_status = RunStatus.已执行.ToString();
step_3.step_remark = $" {asqResult} 行受影响";
step_3.spend_time = (int)stopwatch.Elapsed.TotalSeconds;
logService.Update(step_3);
conn.Close();
}
step_no++;
//记录日志到数据表
var step_4 = logService.Insert(new TableEntity.SingleUserAnalysisHistoryInfo()
{
col_create_time = DateTime.Now,
error_msg = "", //异常信息
run_status = RunStatus.执行中.ToString(),
spend_time = 0, //耗时
step_name = "查询完成，并更新任务状态", //步骤名称
step_no = step_no, //步骤编号
step_remark = "", //备注
task_detail_id = task_id ?? 0 //task_id
});
stopwatch.Restart(); //计时器开始
sig_ue_mr_top200_job.completeTime = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss:ms");
sig_ue_mr_top200_job.state = ExecStatus.已完成.ToString();
topService.Update(sig_ue_mr_top200_job);
stopwatch.Stop();//计时器停止
step_4.run_status = RunStatus.已执行.ToString();
step_4.step_remark = "Top200用户批量UE_MR查询完成";
step_4.spend_time = (int)stopwatch.Elapsed.TotalSeconds;
logService.Update(step_4);
if (!string.IsNullOrEmpty(AutoDelete))
{
Logger.Info($"[DeleteOldData:task_id]-- {task_id}");
conn.Open();
cmd.CommandText = string.Format(@"delete from sig_ue_mr_top200_plan where task_id={0}", task_id);
cmd.ExecuteNonQuery();
Logger.Info($"[DeleteOldData:task_id]--completed");
}
}
}
}
catch (Exception ex)
{
Logger.Error(ex);
}
finally
{
conn.Close();
}
}
}
catch (Exception ex)
{
Logger.Error(ex);
}
finally
{
IsActive = false;
}
}
return(TaskUtil.CompletedTask);
}
