/// <summary> /// 获取所有等待脚本任务 /// </summary> /// <returns></returns> public IList <ProInterface.Models.SCRIPT_TASK> AllScriptTask() { IList <ProInterface.Models.SCRIPT_TASK> reList = new List <ProInterface.Models.SCRIPT_TASK>(); reList = FunSqlToClass.SqlToList <ProInterface.Models.SCRIPT_TASK>("select * from YL_SCRIPT_TASK where RUN_STATE='等待'", ConfigurationManager.AppSettings["dbType"], ConfigurationManager.AppSettings["dbConnSt"]); return(reList); }
/// <summary> /// 获取所有任务 /// </summary> /// <returns></returns> public IList <ProInterface.Models.DATA_DOWN> AllDownData() { IList <ProInterface.Models.DATA_DOWN> reList = new List <ProInterface.Models.DATA_DOWN>(); reList = FunSqlToClass.SqlToList <ProInterface.Models.DATA_DOWN>("select * from YL_DATA_DOWN where STATUS='正常' and CRON_EXPRESSION is not null", ConfigurationManager.AppSettings["dbType"], ConfigurationManager.AppSettings["dbConnSt"]); return(reList); }
/// <summary> /// 运行出错日志 /// </summary> /// <param name="err"></param> public void ErrorScriptTask(Exception err) { var message = err.Message; if (message.Length > 40) { message = message.Substring(0, 40); } _Error = err.Message; log(err.Message, err.ToString(), 1); Dictionary <string, object> dic = new Dictionary <string, object>(); dic.Add("RUN_STATE", "停止"); dic.Add("RETURN_CODE", "失败"); dic.Add("END_TIME", DateTime.Now); dic.Add("DISABLE_DATE", DateTime.Now); dic.Add("DISABLE_REASON", message); FunSqlToClass.UpData <SCRIPT_TASK>( ConfigurationManager.AppSettings["dbType"], ConfigurationManager.AppSettings["dbConnSt"], dic, string.Format("where ID={0} ", _taskId), ConfigurationManager.AppSettings["dbPrefix"]); _isRun = false; }
public void Execute(IJobExecutionContext context) { var scriptId = Convert.ToInt32(context.JobDetail.Key.Name.Replace("ScriptJob_", "")); var taskEnt = FunSqlToClass.ClassSingle <SCRIPT_TASK>( ConfigurationManager.AppSettings["dbType"], ConfigurationManager.AppSettings["dbConnSt"], "where RUN_STATE='运行' AND SCRIPT_ID = " + scriptId, ConfigurationManager.AppSettings["dbPrefix"] ); //如果有任务没有结束,则不添加任务 if (taskEnt != null && taskEnt.ID != 0) { if (taskEnt.START_TIME != null && taskEnt.START_TIME.Value.AddHours(2) < DateTime.Now) { Dictionary <string, object> dicStart = new Dictionary <string, object>(); dicStart.Add("RUN_STATE", "失败"); dicStart.Add("DISABLE_REASON", "运行超时"); FunSqlToClass.UpData <SCRIPT_TASK>( ConfigurationManager.AppSettings["dbType"], ConfigurationManager.AppSettings["dbConnSt"], dicStart, string.Format("where ID={0} ", scriptId), ConfigurationManager.AppSettings["dbPrefix"]); } else { return; } } var scriptEnt = FunSqlToClass.ClassSingle <SCRIPT>( ConfigurationManager.AppSettings["dbType"], ConfigurationManager.AppSettings["dbConnSt"], "where ID = " + scriptId, ConfigurationManager.AppSettings["dbPrefix"] ); ScriptExt ext = new ScriptExt(); if (scriptEnt.IS_GROUP == (short)1)//表示是任务组 { IList <ProInterface.Models.SCRIPT_GROUP_LIST> reList = new List <ProInterface.Models.SCRIPT_GROUP_LIST>(); reList = FunSqlToClass.SqlToList <ProInterface.Models.SCRIPT_GROUP_LIST>(string.Format("SELECT * FROM YL_SCRIPT_GROUP_LIST WHERE GROUP_ID={0} ORDER BY ORDER_INDEX", scriptId), ConfigurationManager.AppSettings["dbType"], ConfigurationManager.AppSettings["dbConnSt"]); if (reList.Count() > 0) { long taskId = 0; ext.AddScriptTask(reList[0].SCRIPT_ID, scriptId, ref taskId); logger.InfoFormat("执行脚本 添加脚本【{0}】任务ID【{1}】", scriptId, taskId); } } else//普通脚本 { long taskId = 0; ext.AddScriptTask(scriptId, null, ref taskId); logger.InfoFormat("执行脚本 添加脚本【{0}】任务ID【{1}】", scriptId, taskId); } }
/// <summary> /// 获取所有脚本 /// </summary> /// <returns></returns> public IList <ProInterface.Models.SCRIPT> AllScript() { IList <ProInterface.Models.SCRIPT> reList = new List <ProInterface.Models.SCRIPT>(); reList = FunSqlToClass.SqlToList <ProInterface.Models.SCRIPT>("select * from YL_SCRIPT where STATUS='正常' and RUN_WHEN is not null", ConfigurationManager.AppSettings["dbType"], ConfigurationManager.AppSettings["dbConnSt"]); reList = reList.OrderBy(x => x.ID).ToList(); return(reList); }
public void log(string message, int taskId) { if (taskId == 0) { return; } ProInterface.Models.SCRIPT_TASK_LOG ent = new SCRIPT_TASK_LOG(); ent.SCRIPT_TASK_ID = taskId; ent.LOG_TIME = DateTime.Now; ent.MESSAGE = message; FunSqlToClass.Save <ProInterface.Models.SCRIPT_TASK_LOG>(ConfigurationManager.AppSettings["dbType"], ConfigurationManager.AppSettings["dbConnSt"], ent, ConfigurationManager.AppSettings["dbPrefix"]); }
public bool InsertInto(string tableName, string dbNickName, IList <TableFiled> allFiled, IList <IList <object> > allData, int rowsNum, int nowPage) { log(string.Format("开始导第【{0}】页数据", nowPage)); var ent = FunSqlToClass.ClassSingle <DB_SERVER>( ConfigurationManager.AppSettings["dbType"], ConfigurationManager.AppSettings["dbConnSt"], string.Format("where NICKNAME='{0}' ", dbNickName), ConfigurationManager.AppSettings["dbPrefix"] ); string connStr = GetDbConnStr(ent); try { object oldNum = 0, newNum = 0; switch (ent.TYPE.ToUpper()) { case "ORACLE": IList <OracleParameter> OracleP = new List <OracleParameter>(); for (int i = 0; i < allFiled.Count; i++) { var t = allFiled[i]; OracleParameter deptNoParam = new OracleParameter(t.Code, DbHelper.OracleHelper.GetDbType(t.CSharpType)); deptNoParam.Direction = ParameterDirection.Input; deptNoParam.Value = allData[i]; OracleP.Add(deptNoParam); } var insertSql = string.Format("insert into {0} ({1}) values({2})", tableName, string.Join(",", allFiled.Select(x => x.Code)), ":" + string.Join(",:", allFiled.Select(x => x.Code))); oldNum = DbHelper.OracleHelper.ExecuteScalar(connStr, CommandType.Text, string.Format("select count(1) T from {0}", tableName)); DbHelper.OracleHelper.Import(rowsNum, connStr, insertSql, OracleP); newNum = DbHelper.OracleHelper.ExecuteScalar(connStr, CommandType.Text, string.Format("select count(1) T from {0}", tableName)); break; } if (Convert.ToDecimal(newNum) != Convert.ToDecimal(oldNum) + rowsNum) { throw new Exception(string.Format("导入数据失败,已导入{0}条;导入第{1}页{2}条数据失败", oldNum, nowPage, rowsNum)); } log(string.Format("完成导入第【{0}】页,数据【{1}】行", nowPage, rowsNum)); allData.Clear(); return(true); } catch (Exception e) { ErrorScriptTask(e); log(string.Format("导入第【{0}】页数据失败", nowPage)); log(string.Format("失败原因:{0}", e.Message)); return(false); } }
/// <summary> /// 取消任务 /// </summary> public void ScriptTaskCancel() { log(string.Format("取消任务【{0}】", _taskId)); Dictionary <string, object> dic = new Dictionary <string, object>(); dic.Add("RUN_STATE", "停止"); dic.Add("RETURN_CODE", "取消"); FunSqlToClass.UpData <SCRIPT_TASK>( ConfigurationManager.AppSettings["dbType"], ConfigurationManager.AppSettings["dbConnSt"], dic, string.Format("where ID={0} ", _taskId), ConfigurationManager.AppSettings["dbPrefix"]); }
/// <summary> /// 添加等待发送的内容 /// </summary> /// <param name="phoneNo"></param> /// <param name="content"></param> /// <returns>主键</returns> public string SmsAdd(string phoneNo, string content) { ProInterface.Models.SMS_SEND ent = new SMS_SEND(); ent.KEY = Guid.NewGuid().ToString().Replace("-", ""); ent.PHONE_NO = phoneNo; ent.ADD_TIME = DateTime.Now; ent.CONTENT = content; ent.STAUTS = "等待"; FunSqlToClass.Save <ProInterface.Models.SMS_SEND>( ConfigurationManager.AppSettings["dbType"], ConfigurationManager.AppSettings["dbConnSt"], ent, ConfigurationManager.AppSettings["dbPrefix"]); return(ent.KEY); }
/// <summary> /// 检测任务是否已经在运行 /// </summary> /// <returns></returns> public bool ScriptTaskIsRun() { var ent = FunSqlToClass.ClassSingle <SCRIPT_TASK>( ConfigurationManager.AppSettings["dbType"], ConfigurationManager.AppSettings["dbConnSt"], string.Format("where ID={0} ", _taskId), ConfigurationManager.AppSettings["dbPrefix"] ); if (ent == null || ent.RUN_STATE.IndexOf("停止") > -1) { //log(string.Format("状态异常【{0}】", ent.RUN_STATE)); _isRun = false; return(false); } _isRun = true; return(true); }
/// <summary> /// 结束任务 /// </summary> public void CompleteScriptTask() { Dictionary <string, object> dic = new Dictionary <string, object>(); dic.Add("RUN_STATE", "停止"); dic.Add("END_TIME", DateTime.Now); if (IsRun()) { dic.Add("RETURN_CODE", "成功"); } else { dic.Add("RETURN_CODE", "失败"); } FunSqlToClass.UpData <SCRIPT_TASK>( ConfigurationManager.AppSettings["dbType"], ConfigurationManager.AppSettings["dbConnSt"], dic, string.Format("where ID={0} ", _taskId), ConfigurationManager.AppSettings["dbPrefix"]); }
public void ErrorScriptTask(string message, int taskId) { if (message.Length > 40) { message = message.Substring(0, 40); } log(message, taskId); Dictionary <string, object> dic = new Dictionary <string, object>(); dic.Add("RUN_STATE", "停止"); dic.Add("RETURN_CODE", "失败"); dic.Add("END_TIME", DateTime.Now); dic.Add("DISABLE_DATE", DateTime.Now); dic.Add("DISABLE_REASON", message); FunSqlToClass.UpData <SCRIPT_TASK>( ConfigurationManager.AppSettings["dbType"], ConfigurationManager.AppSettings["dbConnSt"], dic, string.Format("where ID={0} ", taskId), ConfigurationManager.AppSettings["dbPrefix"]); }
/// <summary> /// 写日志 /// </summary> /// <param name="message"></param> /// <param name="sql_text"></param> /// <param name="logType"></param> public void log(string message, string sql_text = "", Int16 logType = 0) { try { if (!IsRun()) { return; } if (_taskId == 0) { return; } ProInterface.Models.SCRIPT_TASK_LOG ent = new SCRIPT_TASK_LOG(); ent.SCRIPT_TASK_ID = _taskId; ent.LOG_TIME = DateTime.Now; ent.LOG_TYPE = logType; ent.MESSAGE = message; ent.SQL_TEXT = sql_text; FunSqlToClass.Save <ProInterface.Models.SCRIPT_TASK_LOG>(ConfigurationManager.AppSettings["dbType"], ConfigurationManager.AppSettings["dbConnSt"], ent, ConfigurationManager.AppSettings["dbPrefix"]); } catch { } }
/// <summary> /// 设置当前数据库 /// </summary> public void setnowdb(string dbNickName = null) { if (string.IsNullOrEmpty(dbNickName)) { dbNickName = "中心库"; } var ent = FunSqlToClass.ClassSingle <DB_SERVER>( ConfigurationManager.AppSettings["dbType"], ConfigurationManager.AppSettings["dbConnSt"], string.Format("where NICKNAME='{0}' ", dbNickName), ConfigurationManager.AppSettings["dbPrefix"] ); if (ent != null) { _connStr = GetDbConnStr(ent); if (string.IsNullOrEmpty(ent.TYPE)) { ErrorScriptTask(new Exception("数据类型不能为空")); return; } switch (ent.TYPE.ToUpper()) { case "ORACLE": _conn = new OracleConnection(_connStr); break; } _schema = ent.UID; _dbType = ent.TYPE.ToUpper(); _dbNickName = dbNickName; log(string.Format("【{0}】数据库切换成功", dbNickName)); } else { log(string.Format("【{0}】数据库不存在", dbNickName)); return; } }
/// <summary> /// 运行脚本任务 /// </summary> /// <param name="scriptId"></param> public void ScriptTaskRun(Int64 scriptTaskId) { var entTask = FunSqlToClass.ClassSingle <SCRIPT_TASK>( ConfigurationManager.AppSettings["dbType"], ConfigurationManager.AppSettings["dbConnSt"], "where ID = " + scriptTaskId, ConfigurationManager.AppSettings["dbPrefix"] ); Dictionary <string, object> dic = new Dictionary <string, object>(); dic.Add("RUN_STATE", "运行"); dic.Add("START_TIME", DateTime.Now); FunSqlToClass.UpData <SCRIPT_TASK>( ConfigurationManager.AppSettings["dbType"], ConfigurationManager.AppSettings["dbConnSt"], dic, string.Format("where ID={0} ", _taskId), ConfigurationManager.AppSettings["dbPrefix"]); _taskId = entTask.ID; _scriptId = entTask.SCRIPT_ID; runData = new Service().AnalysisRunDate(entTask.RUN_DATA); }
/// <summary> /// 添加一个任务脚本 /// </summary> /// <param name="scriptId"></param> public void AddScriptTask(int scriptId, int?groupId, ref long taskId) { var scriptEnt = FunSqlToClass.ClassSingle <SCRIPT>( ConfigurationManager.AppSettings["dbType"], ConfigurationManager.AppSettings["dbConnSt"], "where ID = " + scriptId, ConfigurationManager.AppSettings["dbPrefix"] ); SCRIPT_TASK ent = new SCRIPT_TASK(); ent.ID = FunSqlToClass.GetSeqID <SCRIPT_TASK>( ConfigurationManager.AppSettings["dbType"], ConfigurationManager.AppSettings["dbConnSt"], ConfigurationManager.AppSettings["dbPrefix"] ); ent.SCRIPT_ID = scriptId; ent.GROUP_ID = groupId; ent.BODY_TEXT = scriptEnt.BODY_TEXT; ent.BODY_HASH = scriptEnt.BODY_HASH; ent.RUN_WHEN = scriptEnt.RUN_WHEN; ent.RUN_ARGS = scriptEnt.RUN_ARGS; ent.RUN_DATA = scriptEnt.RUN_DATA; ent.RUN_STATE = "等待"; ent.DSL_TYPE = "自动添加"; ent.START_TIME = DateTime.Now; ent.SERVICE_FLAG = scriptEnt.SERVICE_FLAG; FunSqlToClass.Save <SCRIPT_TASK>( ConfigurationManager.AppSettings["dbType"], ConfigurationManager.AppSettings["dbConnSt"], ent, ConfigurationManager.AppSettings["dbPrefix"] ); taskId = ent.ID; }
public void Execute(IJobExecutionContext context) { #region 检测运算实例是否存在,并把结束了的线程终止 QuartzRunStatus nowQrs = new QuartzRunStatus(); IList <QuartzRunStatus> qrs = new List <QuartzRunStatus>(); try { qrs = ProInterface.JSON.EncodeToEntity <IList <QuartzRunStatus> >(System.IO.File.ReadAllText(statusLogPath)); } catch { } foreach (var t in qrs.Where(x => x.StatusTime.AddHours(2) < DateTime.Now).ToList()) { qrs.Remove(t); } if (qrs == null) { qrs = new List <QuartzRunStatus>(); } nowQrs = qrs.SingleOrDefault(x => x.JobName == context.JobDetail.Key.Name); if (nowQrs == null) { nowQrs = new QuartzRunStatus(); } if (nowQrs.IsRun) { return; } nowQrs.IsRun = true; nowQrs.JobName = context.JobDetail.Key.Name; nowQrs.StatusTime = DateTime.Now; if (qrs.SingleOrDefault(x => x.JobName == context.JobDetail.Key.Name) == null) { qrs.Add(nowQrs); } Fun.WriteAllText(statusLogPath, ProInterface.JSON.DecodeToStr(qrs)); #endregion try { #region 添加发送的短信 //添加数据 string sql = @" INSERT INTO YL_SMS_SEND( KEY, MESSAGE_ID, PHONE_NO, ADD_TIME, CONTENT, STAUTS) SELECT SYS_GUID() KEY, A.MESSAGE_ID, A.PHONE_NO, sysdate ADD_TIME, B.CONTENT, '等待' STAUTS FROM YL_USER_MESSAGE A,YL_MESSAGE B WHERE A.MESSAGE_ID=B.ID AND A.STATUS='等待' AND PHONE_NO IS NOT NULL AND ((CEIL(((SYSDATE -CAST(A.STATUS_TIME AS DATE) )) * 24 * 60)>30 AND B.PUSH_TYPE='智能推送') OR B.PUSH_TYPE='短信推送') "; //更新状态 string sqlUpdate = "UPDATE YL_USER_MESSAGE SET STATUS='已推送',PUSH_TYPE='短信推送' WHERE STATUS='等待' AND PHONE_NO IS NOT NULL AND ((CEIL(((SYSDATE -CAST(STATUS_TIME AS DATE) )) * 24 * 60)>30 AND PUSH_TYPE='智能推送') OR PUSH_TYPE='短信推送')"; FunSqlToClass.NonQuery(sql, ConfigurationManager.AppSettings["dbType"], ConfigurationManager.AppSettings["dbConnSt"], ConfigurationManager.AppSettings["dbPrefix"]); FunSqlToClass.NonQuery(sqlUpdate, ConfigurationManager.AppSettings["dbType"], ConfigurationManager.AppSettings["dbConnSt"], ConfigurationManager.AppSettings["dbPrefix"]); #endregion IList <ProInterface.Models.SMS_SEND> reList = new List <ProInterface.Models.SMS_SEND>(); reList = FunSqlToClass.SqlToList <ProInterface.Models.SMS_SEND>("SELECT * FROM YL_SMS_SEND WHERE STAUTS='等待' OR (STAUTS='失败' AND TRY_NUM<5)", ConfigurationManager.AppSettings["dbType"], ConfigurationManager.AppSettings["dbConnSt"]); if (reList != null && reList.Count() > 0) { reList = reList.OrderBy(x => x.ADD_TIME).ToList(); ProInterface.ErrorInfo error = new ErrorInfo(); int succNum = 0; FunSqlToClass.NonQuery("UPDATE YL_SMS_SEND SET TRY_NUM=TRY_NUM+1 WHERE STAUTS='等待' OR (STAUTS='失败' AND TRY_NUM<5)", ConfigurationManager.AppSettings["dbType"], ConfigurationManager.AppSettings["dbConnSt"], ConfigurationManager.AppSettings["dbPrefix"]); foreach (var t in reList.GroupBy(x => x.CONTENT).ToList()) { var conten = t.Key; var phoneList = t.Select(x => x.PHONE_NO).ToArray(); Dictionary <string, object> dic = new Dictionary <string, object>(); dic.Add("SEND_TIME", DateTime.Now); dic.Add("STAUTS", "成功"); var allPage = t.Count() / 1000; if (t.Count() % 1000 != 0) { allPage++; } for (var i = 0; i < allPage; i++) { FunSqlToClass.UpData <SMS_SEND>( ConfigurationManager.AppSettings["dbType"], ConfigurationManager.AppSettings["dbConnSt"], dic, string.Format(" where KEY IN ('{0}') ", string.Join("','", t.Skip(i * 1000).Take(1000).Select(x => x.KEY))), ConfigurationManager.AppSettings["dbPrefix"]); } foreach (var phone in phoneList) { DbHelper.MasHelper.Send(phone, conten); } succNum += phoneList.Count(); } //sms.SmsConnection("", ref error); //foreach (var t in reList) //{ // Dictionary<string, object> dic = new Dictionary<string, object>(); // dic.Add("SEND_TIME", DateTime.Now); // string[] listPhoneNo = new string[] { t.PHONE_NO }; // if (sms.SmsSend("", ref error, t.PHONE_NO, t.CONTENT)) // //if (SMSHandle.sendSms(listPhoneNo, t.CONTENT) == "0") // { // succNum++; // dic.Add("STAUTS", "成功"); // } // else // { // dic.Add("STAUTS", "失败"); // } // FunSqlToClass.UpData<SMS_SEND>( // ConfigurationManager.AppSettings["dbType"], // ConfigurationManager.AppSettings["dbConnSt"], // dic, // string.Format(" where KEY='{0}' ", t.KEY), // ConfigurationManager.AppSettings["dbPrefix"]); //} //sms.SmsDisconnection("", ref error); //if (error.IsError) //{ // logger.Info(string.Format("断开短信猫失败【{0}】", error.Message)); //} logger.Info(string.Format("成功发送【{0}】条短信", succNum)); } qrs.Remove(nowQrs); Fun.WriteAllText(statusLogPath, ProInterface.JSON.DecodeToStr(qrs)); } catch (Exception ex) { qrs.Remove(nowQrs); Fun.WriteAllText(statusLogPath, ProInterface.JSON.DecodeToStr(qrs)); logger.Error("发送短信 运行异常", ex); } }
/// <summary> /// 启动 /// </summary> /// <param name="csharpCode"></param> /// <param name="scriptTaskID"></param> /// <param name="defaultDb"></param> /// <returns>程序集的实例</returns> public bool ScriptTaskStart(ref ErrorInfo error, int scriptTaskID) { var scriptEnt = FunSqlToClass.ClassSingle <SCRIPT_TASK>( ConfigurationManager.AppSettings["dbType"], ConfigurationManager.AppSettings["dbConnSt"], "where ID = " + scriptTaskID, ConfigurationManager.AppSettings["dbPrefix"] ); if (scriptEnt == null) { error.IsError = true; error.Message = "脚本不存在"; return(false); } string pathInterface = AppDomain.CurrentDomain.BaseDirectory + "ProInterface.dll"; string pathServer = AppDomain.CurrentDomain.BaseDirectory + "ProServer.dll"; #region 判断类库是否存在 if (!System.IO.File.Exists(pathInterface)) { pathInterface = AppDomain.CurrentDomain.BaseDirectory + "Bin\\ProInterface.dll"; } if (!System.IO.File.Exists(pathServer)) { pathServer = AppDomain.CurrentDomain.BaseDirectory + "Bin\\ProServer.dll"; } if (!System.IO.File.Exists(pathInterface)) { error.Message = string.Format("类库【{0}】不存在", pathInterface); return(false); } if (!System.IO.File.Exists(pathServer)) { error.Message = string.Format("类库【{0}】不存在", pathInterface); return(false); } #endregion CSharpCodeProvider objCSharpCodePrivoder = new CSharpCodeProvider(); ICodeCompiler objICodeCompiler = objCSharpCodePrivoder.CreateCompiler(); CompilerParameters objCompilerParameters = new CompilerParameters(); objCompilerParameters.ReferencedAssemblies.Add("System.dll"); objCompilerParameters.ReferencedAssemblies.Add(pathInterface); objCompilerParameters.ReferencedAssemblies.Add(pathServer); objCompilerParameters.GenerateExecutable = false; objCompilerParameters.GenerateInMemory = true; string allCode = ""; try { allCode = GenerateCode(scriptEnt.BODY_TEXT, AnalysisRunDate(scriptEnt.RUN_DATA)); } catch { ErrorScriptTask(error.Message, Convert.ToInt32(scriptEnt.ID)); return(false); } CompilerResults cr = objICodeCompiler.CompileAssemblyFromSource(objCompilerParameters, allCode); if (cr.Errors.HasErrors) { StringBuilder sb = new StringBuilder("编译错误:"); foreach (CompilerError err in cr.Errors) { sb.AppendLine(string.Format("行{0}列{1}:{2} <br />", err.Line - 12, err.Column, err.ErrorText)); } error.IsError = true; error.Message = sb.ToString(); ErrorScriptTask(error.Message, Convert.ToInt32(scriptEnt.ID)); return(false); } else { // 通过反射,调用HelloWorld的实例 objAssembly = cr.CompiledAssembly; object objScripRun = objAssembly.CreateInstance("ProServer.ScripRun"); //创建任务 var bodyText = objScripRun.GetType().GetMethod("ScriptTaskRun").Invoke(objScripRun, new object[] { scriptEnt.ID }); var setnowdb = objScripRun.GetType().GetMethod("setnowdb").Invoke(objScripRun, new object[] { scriptEnt.SERVICE_FLAG }); //运行脚本 var run = objScripRun.GetType().GetMethod("Run").Invoke(objScripRun, null); var errorMsg = objScripRun.GetType().GetMethod("GetError").Invoke(objScripRun, null); IList <ProInterface.Models.SCRIPT_GROUP_LIST> reList = FunSqlToClass.SqlToList <ProInterface.Models.SCRIPT_GROUP_LIST>( string.Format("SELECT * FROM YL_SCRIPT_GROUP_LIST WHERE GROUP_ID={0} ORDER BY ORDER_INDEX", (scriptEnt.GROUP_ID == null) ? 0 : scriptEnt.GROUP_ID.Value), ConfigurationManager.AppSettings["dbType"], ConfigurationManager.AppSettings["dbConnSt"]); if (reList.Count() > 0 && (bool)run && string.IsNullOrEmpty(errorMsg.ToString())) { var nowPlace = reList.SingleOrDefault(x => x.SCRIPT_ID == scriptEnt.SCRIPT_ID); var last = reList.Where(x => x.ORDER_INDEX > nowPlace.ORDER_INDEX).ToList(); if (last.Count() > 0) { long taskId = 0; ScriptExt ext = new ScriptExt(); ext.AddScriptTask(last[0].SCRIPT_ID, last[0].GROUP_ID, ref taskId); } } return(string.IsNullOrEmpty(errorMsg.ToString())); } }
public void Execute(IJobExecutionContext context) { #region 添加脚本任务 ISchedulerFactory schedulerFactory = new StdSchedulerFactory(); IScheduler scheduler = schedulerFactory.GetScheduler(); string jobGroupName = "ScriptJobGroup"; string triGroupName = "ScriptTriGroup"; string jobNamePex = "ScriptJob_"; string triNamePex = "ScriptTri_"; //所有需要运行的脚本 var allScript = AllScript(); var triKeyArr = scheduler.GetTriggerKeys(GroupMatcher <TriggerKey> .GroupEquals("ScriptTriGroup")); //删除触发器,删除这个触发器没有在运行的脚本里 foreach (var t in triKeyArr) { var trigger = scheduler.GetTrigger(t); IJobDetail job = scheduler.GetJobDetail(trigger.JobKey); var tmp = allScript.SingleOrDefault(x => t.Name == triNamePex + x.ID.ToString()); if (tmp == null) { StopTask(Convert.ToInt32(t.Name.Replace(triNamePex, ""))); scheduler.DeleteJob(trigger.JobKey); logger.InfoFormat("脚本服务 移除触发器ID{0}", t.Name); } } foreach (var t in allScript) { try { //新任务 if (triKeyArr.SingleOrDefault(x => x.Name == triNamePex + t.ID.ToString()) == null) { IJobDetail job = JobBuilder.Create <ScriptTaskAddQuartz>() .WithIdentity(new JobKey(jobNamePex + t.ID.ToString(), jobGroupName)) .StoreDurably() .Build(); ICronTrigger trigger = (ICronTrigger)TriggerBuilder.Create() .WithIdentity(new TriggerKey(triNamePex + t.ID.ToString(), triGroupName)) .ForJob(job) .StartNow().WithCronSchedule(t.RUN_WHEN) .Build(); logger.InfoFormat("脚本服务 添加脚本触发器ID{0}", trigger.Key.Name); scheduler.ScheduleJob(job, trigger); } else { ICronTrigger trigger = (ICronTrigger)scheduler.GetTrigger(new TriggerKey(triNamePex + t.ID.ToString(), triGroupName)); IJobDetail job = scheduler.GetJobDetail(trigger.JobKey); if (trigger.CronExpressionString != t.RUN_WHEN) { logger.InfoFormat("脚本服务 修改触发器【{0}】的时间表达式【{1}】为【{2}】", trigger.Key.Name, trigger.CronExpressionString, t.RUN_WHEN); trigger.CronExpressionString = t.RUN_WHEN; scheduler.DeleteJob(trigger.JobKey); scheduler.ScheduleJob(job, trigger); } } } catch (Exception e) { Dictionary <string, object> dicStart = new Dictionary <string, object>(); dicStart.Add("STATUS", "禁用"); FunSqlToClass.UpData <SCRIPT>( ConfigurationManager.AppSettings["dbType"], ConfigurationManager.AppSettings["dbConnSt"], dicStart, string.Format("where ID={0} ", t.ID), ConfigurationManager.AppSettings["dbPrefix"]); logger.InfoFormat("脚本服务 添加脚本触发器任务【{0}】,失败【{1}】", t.CODE, e.Message); } } #endregion #region 运行脚本任务 //获取正在等待的任务 foreach (var task in AllScriptTask()) { try { #region 检测运算实例是否存在,并把结束了的线程终止 QuartzRunStatus nowQrs = new QuartzRunStatus(); IList <QuartzRunStatus> qrs = new List <QuartzRunStatus>(); try { qrs = ProInterface.JSON.EncodeToEntity <IList <QuartzRunStatus> >(System.IO.File.ReadAllText(statusLogPath)); } catch { } //清理2小时还没有远行完的口径 foreach (var t in qrs.Where(x => x.StatusTime.AddHours(2) < DateTime.Now).ToList()) { qrs.Remove(t); } if (qrs == null) { qrs = new List <QuartzRunStatus>(); } nowQrs = qrs.SingleOrDefault(x => x.JobName == "ScriptID_" + task.SCRIPT_ID); if (nowQrs == null) { nowQrs = new QuartzRunStatus(); } //表示该脚本正在运行,则退出 if (nowQrs.IsRun) { continue; } if (qrs.Count > ProInterface.AppSet.ScriptRunMaxNum) { logger.InfoFormat("执行脚本数【{0}】已经超过最大任务数【{1}】了", qrs.Count, ProInterface.AppSet.ScriptRunMaxNum); return; } nowQrs.IsRun = true; nowQrs.JobName = "ScriptID_" + task.SCRIPT_ID; nowQrs.StatusTime = DateTime.Now; if (qrs.SingleOrDefault(x => x.JobName == "ScriptID_" + task.SCRIPT_ID) == null) { qrs.Add(nowQrs); } Fun.WriteAllText(statusLogPath, ProInterface.JSON.DecodeToStr(qrs)); #endregion logger.InfoFormat("执行脚本 开始脚本【{0}】,任务ID【{1}】", task.SCRIPT_ID, task.ID); Dictionary <string, object> dicStart = new Dictionary <string, object>(); dicStart.Add("RUN_STATE", "运行"); dicStart.Add("START_TIME", DateTime.Now); dicStart.Add("RETURN_CODE", null); dicStart.Add("END_TIME", null); dicStart.Add("DISABLE_DATE", null); dicStart.Add("DISABLE_REASON", null); FunSqlToClass.UpData <SCRIPT_TASK>( ConfigurationManager.AppSettings["dbType"], ConfigurationManager.AppSettings["dbConnSt"], dicStart, string.Format("where ID={0} ", task.ID), ConfigurationManager.AppSettings["dbPrefix"]); FunSqlToClass.NonQuery("DELETE FROM YL_SCRIPT_TASK_LOG where SCRIPT_TASK_ID=" + task.ID, ConfigurationManager.AppSettings["dbType"], ConfigurationManager.AppSettings["dbConnSt"], ConfigurationManager.AppSettings["dbPrefix"]); ProServer.Service db = new Service(); ProInterface.ErrorInfo error = new ProInterface.ErrorInfo(); object obj = db.ScriptTaskStart(ref error, Convert.ToInt32(task.ID)); if (error.IsError) { Dictionary <string, object> dic = new Dictionary <string, object>(); dic.Add("RUN_STATE", "停止"); dic.Add("RETURN_CODE", "失败"); dic.Add("END_TIME", DateTime.Now); dic.Add("DISABLE_DATE", DateTime.Now); if (error.Message != null) { if (error.Message.Length > 25) { dic.Add("DISABLE_REASON", error.Message.Substring(0, 25)); } else { dic.Add("DISABLE_REASON", error.Message); } } FunSqlToClass.UpData <SCRIPT_TASK>( ConfigurationManager.AppSettings["dbType"], ConfigurationManager.AppSettings["dbConnSt"], dic, string.Format("where ID={0} ", task.ID), ConfigurationManager.AppSettings["dbPrefix"]); logger.InfoFormat("执行脚本 脚本【{0}】,任务ID【{1}】 出错:{2}", task.SCRIPT_ID, task.ID, error.Message); } else { logger.InfoFormat("执行脚本 结束脚本【{0}】,任务ID【{1}】", task.SCRIPT_ID, task.ID); } } catch (Exception e) { logger.InfoFormat("分析 结束脚本【{0}】,任务ID【{1}】,出错:{2}", task.SCRIPT_ID, task.ID, e.ToString()); } var qrsEnd = ProInterface.JSON.EncodeToEntity <IList <QuartzRunStatus> >(System.IO.File.ReadAllText(statusLogPath)); var nowQrsEnd = qrsEnd.SingleOrDefault(x => x.JobName == "ScriptID_" + task.SCRIPT_ID); if (nowQrsEnd != null) { qrsEnd.Remove(nowQrsEnd); Fun.WriteAllText(statusLogPath, ProInterface.JSON.DecodeToStr(qrsEnd)); } } #endregion }
/// <summary> /// 下载表到数据库 /// </summary> /// <param name="sql">查询语句</param> /// <param name="tableName">生成的表名</param> /// <param name="dbNickName">导入的数据库</param> /// <param name="isCreat">0(默认值)表示不创建表;1:表示自动创建表(在导数据之前,要删除已经存在表);</param> /// <param name="pageSize">设置每页导出的大小。值越大速度越快(对服务器压力大,内容要足够大) 默认值:1000000</param> public void down_db_to_db(string sql, string tableName, string dbNickName, int isCreatTable = 1, int pageSize = 1000000) { if (!IsRun()) { return; } DbDataReader dr = null; object conn = new object(); #region 读取数据 try { switch (_dbType) { case "ORACLE": conn = new OracleConnection(_connStr); dr = DbHelper.OracleHelper.ExecuteReader((OracleConnection)conn, CommandType.Text, sql); break; } log(string.Format("数据库【{1}】执行查询语句成功【{0}】", sql, _dbNickName)); } catch (Exception e) { ErrorScriptTask(e); log(string.Format("数据库【{1}】执行查询语句失败【{0}】", sql, _dbNickName)); log(string.Format("错误原因【{0}】", e.Message)); return; } #endregion var allFiled = new List <TableFiled>(); for (int i = 0; i < dr.FieldCount; i++) { allFiled.Add(new TableFiled { Code = dr.GetName(i), DataType = dr.GetDataTypeName(i), CSharpType = dr.GetFieldType(i).Name }); } var ent = FunSqlToClass.ClassSingle <DB_SERVER>( ConfigurationManager.AppSettings["dbType"], ConfigurationManager.AppSettings["dbConnSt"], string.Format(" where NICKNAME='{0}' ", dbNickName), ConfigurationManager.AppSettings["dbPrefix"] ); if (ent == null) { log(string.Format("【{0}】数据库不存在", dbNickName)); return; } IList <IList <object> > allRows = new List <IList <object> >(); int rowNum = 0; int pageNum = 1; log(string.Format("开始下载数据")); log(string.Format("开始下载第【{0}】页数据", pageNum)); var isRight = true; while (dr.Read()) { if (!_isRun) { break; } rowNum++; //行数据 IList <object> rowData = new List <object>(); #region 读取行数据 for (int i = 0; i < dr.FieldCount; i++) { if (dr[i] == null || dr[i] == DBNull.Value) { rowData.Add(null); } else { rowData.Add(dr[i]); } } allRows.Add(rowData); #endregion //如果行数据等于页数,则开始导入 if (rowNum == pageSize) { int parNum = allFiled.Count; //转成可插入的格式 IList <IList <object> > allData = new List <IList <object> >(); #region 行转列 foreach (var x in allFiled) { allData.Add(new object[allRows.Count]); } object[] allPar = new object[parNum]; for (int a = 0; a < allRows.Count(); a++) { for (int i = 0; i < parNum; i++) { allData[i][a] = allRows[a][i]; } } #endregion #region 如果是第一页则创建表 if (pageNum == 1) { for (var i = 0; i < allFiled.Count; i++) { if (allFiled[i].CSharpType == "String") { string maxSqlStr = string.Format("SELECT MAX(LENGTH({0})) FROM ({1})", allFiled[i].Code, sql); object maxLength = 0; switch (_dbType) { case "DB2": maxLength = DbHelper.DB2Helper.ExecuteScalar(_connStr, CommandType.Text, maxSqlStr); break; case "ORACLE": maxLength = DbHelper.OracleHelper.ExecuteScalar(_connStr, CommandType.Text, maxSqlStr); break; } if (maxLength == null || maxLength == DBNull.Value) { maxLength = 50; } allFiled[i].Length = Convert.ToInt32(maxLength); //var allUseCol = allData[i].Where(x => x != null && x != DBNull.Value); //if (allUseCol.Count() > 0) //{ // allFiled[i].Length = allUseCol.Max(x => x.ToString().Length); //} //else //{ // allFiled[i].Length = 500; //} } } if (isCreatTable == 1) { var createScript = MakeCreateTableSql(allFiled, tableName, ent.TYPE.ToUpper()); drop_table(tableName, ent); log(string.Format("在数据【{1}】上创建表【{0}】", tableName, dbNickName)); execute(createScript, ent); } } #endregion log(string.Format("完成下载第【{0}】页数据", pageNum)); if (!IsRun()) { break; } isRight = InsertInto(tableName, dbNickName, allFiled, allData, allRows.Count, pageNum); allData.Clear(); allRows.Clear(); rowNum = 0; if (!isRight || !ScriptTaskIsRun()) { break; } pageNum++; log(string.Format("开始下载第【{0}】页数据", pageNum)); } } if (!IsRun()) { return; } if (rowNum != 0) { int parNum = allFiled.Count; IList <IList <object> > allData = new List <IList <object> >(); foreach (var x in allFiled) { allData.Add(new object[allRows.Count]); } object[] allPar = new object[parNum]; for (int a = 0; a < allRows.Count(); a++) { for (int i = 0; i < parNum; i++) { allData[i][a] = allRows[a][i]; } } if (pageNum == 1) { for (var i = 0; i < allFiled.Count; i++) { if (allFiled[i].CSharpType == "String") { var allUseCol = allData[i].Where(x => x != null && x != DBNull.Value); if (allUseCol.Count() > 0) { allFiled[i].Length = allUseCol.Max(x => x.ToString().Length); } else { allFiled[i].Length = 500; } } } if (isCreatTable == 1) { var createScript = MakeCreateTableSql(allFiled, tableName, ent.TYPE.ToUpper()); drop_table(tableName, ent); log(string.Format("在数据【{1}】上创建表【{0}】", tableName, dbNickName)); execute(createScript, ent); } } isRight = InsertInto(tableName, dbNickName, allFiled, allData, allRows.Count, pageNum); allData.Clear(); } if (isRight) { log(string.Format("结束下载,共【{0}】页,【{1}】条数据", pageNum, (pageNum - 1) * pageSize + rowNum)); } else { log(string.Format("下载失败,共【{0}】页,【{1}】条数据", pageNum, (pageNum - 1) * pageSize + rowNum)); } dr.Close(); dr.Dispose(); switch (_dbType) { case "ORACLE": var oralceConn = (OracleConnection)conn; oralceConn.Close(); oralceConn.Dispose(); break; } }
public void Execute(IJobExecutionContext context) { try { logger.Info("启动自动下载数据 开始"); var downID = Convert.ToInt32(context.JobDetail.Key.Name.Replace("DownDataJob_", "")); logger.Info(string.Format("启动自动下载数据 数据ID{0}", downID)); var down = FunSqlToClass.ClassSingle <DATA_DOWN>( ConfigurationManager.AppSettings["dbType"], ConfigurationManager.AppSettings["dbConnSt"], "where ID = " + downID, ConfigurationManager.AppSettings["dbPrefix"] ); string tableName = ProServer.Fun.ReplaceDataTime(down.CREATE_TABLE_NAME, DateTime.Now); var eventEnt = new DATA_DOWN_EVENT(); eventEnt.ID = FunSqlToClass.GetSeqID <DATA_DOWN_EVENT>( ConfigurationManager.AppSettings["dbType"], ConfigurationManager.AppSettings["dbConnSt"], ConfigurationManager.AppSettings["dbPrefix"] ); eventEnt.TARGET_NAME = tableName; eventEnt.ALL_NUM = 0; eventEnt.LAST_MONTH_NUM = 0; eventEnt.PATH = down.TO_PATH; eventEnt.DATA_DOWN_ID = downID; eventEnt.START_TIME = DateTime.Now; FunSqlToClass.Save <DATA_DOWN_EVENT>( ConfigurationManager.AppSettings["dbType"], ConfigurationManager.AppSettings["dbConnSt"], eventEnt, ConfigurationManager.AppSettings["dbPrefix"] ); string createScript = ""; if (!string.IsNullOrEmpty(down.CREATE_SCRIPT)) { createScript = down.CREATE_SCRIPT.Replace("{@TABLE_NAME}", eventEnt.TARGET_NAME); } var allDataDownTo = FunSqlToClass.SqlToList <ProInterface.Models.DATA_DOWN_TO>(string.Format("select * from YL_DATA_DOWN_TO where DATA_DOWN_ID='{0}'", downID), ConfigurationManager.AppSettings["dbType"], ConfigurationManager.AppSettings["dbConnSt"]); foreach (var to in allDataDownTo) { var toServer = FunSqlToClass.ClassSingle <DB_SERVER>( ConfigurationManager.AppSettings["dbType"], ConfigurationManager.AppSettings["dbConnSt"], "where ID = " + to.DB_SERVER_ID, ConfigurationManager.AppSettings["dbPrefix"] ); #region 在目标服务器上创建表 var db = new ProServer.ScriptExt(); try { try { db.execute("drop table " + eventEnt.TARGET_NAME, toServer); } catch (Exception e) { } db.execute(createScript, toServer); } catch (Exception e) { logger.Info(string.Format("在服务器【{0}】上建表失败:\r\nSQL:{1}\r\n{2}", toServer.NICKNAME, createScript, e.Message)); } #endregion var allDataDownForm = FunSqlToClass.SqlToList <ProInterface.Models.DB_SERVER>(string.Format("select a.* from YL_DB_SERVER a,YL_DATA_DOWN_FORM b where a.ID=b.DB_SERVER_ID and b.DATA_DOWN_ID='{0}'", downID), ConfigurationManager.AppSettings["dbType"], ConfigurationManager.AppSettings["dbConnSt"]); foreach (var from in allDataDownForm) { DATA_DOWN_TASK task = new DATA_DOWN_TASK(); task.ID = FunSqlToClass.GetSeqID <DATA_DOWN_EVENT>( ConfigurationManager.AppSettings["dbType"], ConfigurationManager.AppSettings["dbConnSt"], ConfigurationManager.AppSettings["dbPrefix"] ); task.NAME = eventEnt.TARGET_NAME; task.SELECT_SCRIPT = down.SELECT_SCRIPT; task.EVENT_ID = eventEnt.ID; #region 替换SELECT_SCRIPT foreach (var replace in JSON.EncodeToEntity <IList <KV> >(to.REPLACE_STR)) { task.SELECT_SCRIPT = task.SELECT_SCRIPT.Replace(replace.K, replace.V); } task.SELECT_SCRIPT = ProServer.Fun.ReplaceDataTime(task.SELECT_SCRIPT, DateTime.Now); #endregion #region 生成@[00-99] if (task.SELECT_SCRIPT.IndexOf("@") != -1) { task.SELECT_SCRIPT = Fun.GetSelectScript(task.SELECT_SCRIPT); } #endregion //设置存放路径 task.TO_PATH = ProServer.Fun.ReplaceDataTime(down.TO_PATH, DateTime.Now); int thisAllNum = 0; task.ALL_NUM = thisAllNum; int upMonthNum = 0; task.LAST_MONTH_NUM = upMonthNum; task.SELECT_DB_SERVER = from.ID; task.SELECT_SERVER = string.Format("(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST={0})(PORT={1})))(CONNECT_DATA=(SERVER=DEDICATED)(SID={2})))", from.IP, from.PORT, from.DBNAME); task.SELECT_UID = from.UID; task.SELECT_PWD = from.PASSWORD; task.EVENT_TYPE = 1; if (down.SUCC_SCRIPT != null) { task.SUCC_SCRIPT = down.SUCC_SCRIPT.Replace("{@TABLE_NAME}", eventEnt.TARGET_NAME); } task.TO_DB_SERVER = toServer.ID; task.TO_SERVER = toServer.DBNAME; task.TO_UID = toServer.UID; task.TO_PWD = toServer.PASSWORD; task.CREATE_SCRIPT = createScript; task.ERROR_NUM = 0; task.STATUS = "等待"; task.PAGE_SIZE = down.PAGE_SIZE; task.SPLIT_STR = down.SPLIT_STR; task.IS_CANCEL = 0; task.ORDER_NUM = task.ID; FunSqlToClass.Save <DATA_DOWN_TASK>( ConfigurationManager.AppSettings["dbType"], ConfigurationManager.AppSettings["dbConnSt"], task, ConfigurationManager.AppSettings["dbPrefix"] ); } } logger.Info("启动自动下载数据 结束"); } catch (Exception ex) { logger.Error("启动自动下载数据 运行异常", ex); } }