/// <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);
}
}
