/// <summary>
/// 在指定数据库执行查询语句
/// </summary>
/// <param name="dbID">数据库ID</param>
/// <param name="psd">二次密码验证（执行DDL语句时传入）</param>
/// <param name="sql">SQL语句</param>
/// <param name="paramList">参数列表</param>
/// <returns>DataTable</returns>
public int ExecuteSQL(int dbID, string psd, string sql, List <object> paramList)
{
int i = 0;
if (dbID < 0)
{
throw new Exception("错误的数据库ID");
}
if (string.IsNullOrWhiteSpace(sql))
{
throw new Exception("SQL语句不可为空");
}
string s = sql.Trim();
if (s.ToUpper().StartsWith("SELECT ") == true)
{
throw new Exception("只能执行非SELECT语句！");
}
if (psd != "lb@em")
{
throw new Exception("二次验证密码不正确，非SELECT语句需要二次验证密码！");
}
using (BDBHelper dbHelper = GetBDBHelper(dbID))
{
try
{
if (paramList == null || paramList.Count < 1)
{
i = dbHelper.ExecuteNonQuery(sql);
}
else
{
i = dbHelper.ExecuteNonQueryParams(sql, paramList);
}
}
catch (Exception ex)
{
throw new Exception("在数据库" + dbID + "执行SQL查询出错：" + ex.Message);
}
}
return(i);
}
/// <summary>
/// 在指定数据库上执行SQL语句
/// </summary>
/// <param name="dbID"></param>
/// <param name="sql"></param>
/// <param name="paramList"></param>
/// <returns></returns>
public int ExecuteNonQuery(int dbID, string sql, List <object> paramList)
{
using (BDBHelper dbHelper = GetBDBHelper(dbID))
{
try
{
if (paramList == null || paramList.Count < 1)
{
return(dbHelper.ExecuteNonQuery(sql));
}
else
{
return(dbHelper.ExecuteNonQueryParams(sql, paramList));
}
}
catch (Exception ex)
{
throw new Exception("在数据库" + dbID + "执行SQL语句出错：" + ex.Message);
}
}
}
/// <summary>
/// 限定和处理待
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private static void DoWork3(object sender, DoWorkEventArgs e)
{
while (Main.IsRun)
{
try
{
BLog.Write(BLog.LogLevel.INFO, "开始处理限定的监控文件夹任务");
#region 查询当前非并行执行中的任务实例的数量，如果数量小于MonitFolderCount，则补齐执行中的数量。
//修改等待中的任务为执行中(补齐差量)
string sql = string.Format(@"SELECT COUNT (1)
FROM EM_SCRIPT_CASE
WHERE IS_SUPERVENE <> 1 AND RUN_STATUS = 2");
object obj = null;
using (BDBHelper dbop = new BDBHelper())
{
obj = dbop.ExecuteScalar(sql);//获得执行中的非并行任务数
BLog.Write(BLog.LogLevel.INFO, "获取到执行中任务数：" + obj);
if (obj != null && Convert.ToInt32(obj) < Main.MaxMonitCount) //当执行中的数量小于MaxMonitCount
{
int difCount = Main.MaxMonitCount - Convert.ToInt32(obj); //差量
sql = string.Format(@"SELECT COUNT(1)
FROM (SELECT A.ID,
ROW_NUMBER () OVER (ORDER BY ID) RN
FROM EM_SCRIPT_CASE A WHERE RUN_STATUS = 1)
WHERE RN <= {0}", difCount);
object o2 = o2 = dbop.ExecuteScalar(sql);
BLog.Write(BLog.LogLevel.INFO, "按差量获取等待中任务数：" + o2);
if (o2 != null && Convert.ToInt32(o2) > 0)
{
sql = string.Format(@"MERGE INTO EM_SCRIPT_CASE A
USING (SELECT ID
FROM (SELECT ID,
ROW_NUMBER ()
OVER ( ORDER BY ID)
RN
FROM EM_SCRIPT_CASE
WHERE RUN_STATUS = 1)
WHERE RN <= {0}) B
ON (A.ID = B.ID)
WHEN MATCHED
THEN
UPDATE SET RUN_STATUS = 2", difCount);
dbop.ExecuteNonQuery(sql);//修改等待的任务为执行中
BLog.Write(BLog.LogLevel.INFO, "执行把等待中任务改为执行中");
}
}
dbop.Close();//关闭连接
}
#endregion
}
catch (Exception ex)
{
BLog.Write(BLog.LogLevel.ERROR, "限定监控的文件夹任务出现异常：" + ex.ToString());
}
Thread.Sleep(100000);//100秒执行一次
}
}
/// <summary>
/// 往待拷贝列表中加入文件编号
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private static void DoWork2(object sender, DoWorkEventArgs e)
{
while (Main.IsRun)
{
try
{
#region 再次验证和清理未在线终端
//var ipArr = global.ipList.ToArray();
//for (int i = 0; i < ipArr.Count(); i++)
//{
// if (Request.PingIP(ipArr[i].Value) && global.ipList.ContainsKey(ipArr[i].Key))
// {
// global.ipList.Remove(ipArr[i].Key);//移除已在线的终端
// }
//}
var ipNotLists = global.OpIpNotList("getall");
if (ipNotLists != null && ipNotLists.Count > 0)
{
int cnt = ipNotLists.Count;
for (int i = cnt - 1; i >= 0; i--)
{
var item = ipNotLists[i];
if (Librarys.ApiRequest.Request.OldPingIP(item.V))
{
global.OpIpNotList("remove", item);
}
}
ipNotLists = global.OpIpNotList("getall");
BLog.Write(BLog.LogLevel.INFO, "输出未在线的ip：" + string.Join(",", ipNotLists.Select(p => p.V)));
}
#endregion
BLog.Write(BLog.LogLevel.INFO, "已在列表中的数量：" + global.GetMonitKVCount());
if (global.GetEffectMonitKVCount() < 200)
//if (global.GetMonitKVCount() < 200)
{
//var ipNotLists = global.OpIpNotList("getall");
#region 获取MaxUploadCount条待拷贝记录(排除未在线终端)
//采集待插入的文件列表
//采集未在线的终端列表
//lcz, 这个地方的sql可以只返回同一客户机ip的，便于下面的一个连接多个文件拷贝
//获取不返回一个ip的文件，在从monitKVList中获取5个一样ip的终端去处理
//string sql = string.Format(@"SELECT A.ID, B.IP, A.COMPUTER_ID
// FROM (SELECT ID, COMPUTER_ID
// FROM (SELECT A.ID,
// A.COMPUTER_ID,
// ROW_NUMBER () OVER (ORDER BY A.ID) RN
// FROM FM_MONIT_FILE A
// LEFT JOIN ( SELECT DISTINCT REGEXP_SUBSTR ('{0}',
// '[^,]+',
// 1,
// LEVEL)
// AS COMPUTER_ID
// FROM DUAL
// CONNECT BY REGEXP_SUBSTR ('{0}',
// '[^,]+',
// 1,
// LEVEL)
// IS NOT NULL) C
// ON (A.COMPUTER_ID = C.COMPUTER_ID)
// LEFT JOIN FM_FILE_FORMAT F ON (F.ID=A.FILE_FORMAT_ID)
// WHERE NVL (C.COMPUTER_ID, 0) = 0 AND F.NAME<>'Folder'
// AND (A.COPY_STATUS = 0 OR A.COPY_STATUS = 3))
// WHERE RN <={1}) A
// LEFT JOIN FM_COMPUTER B ON (A.COMPUTER_ID = B.ID)", string.Join(",", ipNotLists.Select(p => p.K).Distinct()), Main.EachSearchUploadCount);
string sql = string.Format(@"SELECT A.ID, B.IP, A.COMPUTER_ID
FROM (SELECT A.ID, A.COMPUTER_ID
FROM FM_MONIT_FILE A
LEFT JOIN ( SELECT DISTINCT REGEXP_SUBSTR ('{0}',
'[^,]+',
1,
LEVEL)
AS COMPUTER_ID
FROM DUAL
CONNECT BY REGEXP_SUBSTR ('{0}',
'[^,]+',
1,
LEVEL)
IS NOT NULL) C
ON (A.COMPUTER_ID = C.COMPUTER_ID)
LEFT JOIN FM_FILE_FORMAT F ON (F.ID = A.FILE_FORMAT_ID)
WHERE NVL (C.COMPUTER_ID, 0) = 0
AND F.NAME <> 'Folder'
AND (A.COPY_STATUS = 0 OR A.COPY_STATUS = 3)
AND ROWNUM <= {1}) A
LEFT JOIN FM_COMPUTER B ON (A.COMPUTER_ID = B.ID)", string.Join(",", ipNotLists.Select(p => p.K).Distinct()), Main.EachSearchUploadCount);
StringBuilder sb = new StringBuilder(); //待处理
//StringBuilder sbNotAlive = new StringBuilder();//未在线
List <string> notAliveList = new List <string>(); //当前查询的未在线
DataTable dt = null;
using (BDBHelper dbop = new BDBHelper())
{
dt = dbop.ExecuteDataTable(sql);
if (dt != null && dt.Rows.Count > 0)
{
string updateSql = string.Format(@"update FM_MONIT_FILE set COPY_STATUS=5 where id in({0})", string.Join(",", dt.AsEnumerable().Select(r => r["ID"]).Distinct().ToArray()).TrimEnd(','));
dbop.ExecuteNonQuery(updateSql);
}
dbop.Close();
}
//log("查询出的数量为：【" + dt.Rows.Count + "】");
BLog.Write(BLog.LogLevel.INFO, "查询出的数量为：【" + dt.Rows.Count + "】");
if (dt != null && dt.Rows.Count > 0)
{
List <string> hasAliveIps = new List <string>();//当前批次的在线ip
for (int i = 0; i < dt.Rows.Count; i++)
{
sb.Append(dt.Rows[i][0] + ",");
//校验ip
string curIp = dt.Rows[i][1].ToString().Trim();
//log("当前ip【" + curIp + "】");
var curKv = new KV {
K = Convert.ToInt64(dt.Rows[i][2].ToString()), V = dt.Rows[i][1].ToString()
}; //不在线的ip
if (string.IsNullOrEmpty(curIp))
{
BLog.Write(BLog.LogLevel.INFO, "文件编号:" + dt.Rows[i][0] + "为空");
//log("ip[" + curIp + "]为空");//20180701注释
//BLog.Write(BLog.LogLevel.INFO, "ip[" + curIp + "]为空");
}
else if (hasAliveIps.Contains(curIp))
{
BLog.Write(BLog.LogLevel.INFO, "文件编号:" + dt.Rows[i][0] + "IP在线");
global.OpMonitKVList("add", new KV {
K = Convert.ToInt64(dt.Rows[i][0].ToString()), V = dt.Rows[i][1].ToString()
}); //20180701注释
//log("ip[" + curIp + "]在已在线列表中");
}
else
{
if (ipNotLists.Exists(p => p.K == curKv.K))
{
BLog.Write(BLog.LogLevel.INFO, "文件编号:" + dt.Rows[i][0] + "IP不在线");
//log("ip[" + curIp + "]未在线2");
using (BDBHelper dbop = new BDBHelper())
{
string updateSql = string.Format(@"update FM_MONIT_FILE set COPY_STATUS=0 where id ={0}", dt.Rows[i][0].ToString());
dbop.ExecuteNonQuery(updateSql);
dbop.Close();
}
if (!notAliveList.Contains(curKv.V))
{
notAliveList.Add(curKv.V);
}
}
else if (!Request.PingIP(curIp))
{
BLog.Write(BLog.LogLevel.INFO, "文件编号2:" + dt.Rows[i][0] + "IP不在线");
//log("ip[" + curIp + "]未在线");
using (BDBHelper dbop = new BDBHelper())
{
string updateSql = string.Format(@"update FM_MONIT_FILE set COPY_STATUS=0 where id ={0}", dt.Rows[i][0].ToString());
dbop.ExecuteNonQuery(updateSql);
dbop.Close();
}
global.OpIpNotList("add", curKv);
notAliveList.Add(dt.Rows[i][1].ToString());
if (!ipNotLists.Exists(p => p.K == curKv.K))
{
ipNotLists.Add(curKv);
}
}
else
{
global.OpMonitKVList("add", new KV {
K = Convert.ToInt64(dt.Rows[i][0].ToString()), V = dt.Rows[i][1].ToString()
});
hasAliveIps.Add(curIp);
BLog.Write(BLog.LogLevel.INFO, "文件编号:" + dt.Rows[i][0] + "添加文件");
//log("ip[" + curIp + "]在线");
//BLog.Write(BLog.LogLevel.INFO, "ip[" + curIp + "]在线");//20180701注释
}
}
}
//log("再次输出未在线ip：" + string.Join(",", global.OpIpNotList("getall").Select(p => p.V)));
#endregion
//log("内存中无监控的文件列表，从数据库中去获取", 4, string.Format(@"执行查询的sql:\r\n{0}。\r\n查询的结果为：{1}", sql, sb));
BLog.Write(BLog.LogLevel.INFO, "内存中无监控的文件列表，从数据库中去获取." + string.Format(@"执行查询的sql:\r\n{0}。\r\n查询的结果为：{1}", sql, sb));
BLog.Write(BLog.LogLevel.INFO, "获取到未在线的ip【" + (notAliveList.Count > 0 ? string.Join(",", notAliveList.Distinct()) : "") + "】,当前未在线的ip列表为【" + string.Join(" , ", global.ipNotList.Select(p => p.V)) + "】");
//log("获取到未在线的ip【" + (notAliveList.Count > 0 ? string.Join(",", notAliveList.Distinct()) : "") + "】,当前未在线的ip列表为【" + string.Join(" , ", global.ipNotList.Select(p => p.V)) + "】");
}
else
{
//string msg = "未在库中查询到需要拷贝的文件，当前不存在需拷贝文件";
//log(msg);
//log(msg, 3, string.Format(@"执行查询的sql:\r\n{0}。", sql));
BLog.Write(BLog.LogLevel.INFO, string.Format(@"执行查询的sql:\r\n{0}。", sql));
//return null;
}
}
}
catch (Exception ex)
{
BLog.Write(BLog.LogLevel.ERROR, "查询添加待拷贝文件出错：" + ex.ToString());
}
Thread.Sleep(2000);
}
}
public JsonResult Edit(SR_TOPIC_FUNDS.Entity ent, FormCollection collection)
{
JsonResultData result = new JsonResultData();
try
{
//throw new Exception("抛出错误");
#region 00.数据校验(暂未实现)
//校验经费总金额与清单经费金额之和是否相等
#endregion
int fundsId = ent.ID;
#region 01.保存经费总表
ent.UPDATE_TIME = DateTime.Now;
ent.UPDATE_UID = SystemSession.UserID;
if (ent.ID == 0)
{
fundsId = SR_TOPIC_FUNDS.Instance.GetNextValueFromSeqDef();
ent.CREATE_TIME = DateTime.Now;
ent.CREATE_UID = SystemSession.UserID;
ent.ID = fundsId;
SR_TOPIC_FUNDS.Instance.Add(ent);
}
else
{
SR_TOPIC_FUNDS.Instance.UpdateByKey(ent, ent.ID);
}
#endregion
#region 01-2.附件银行卡信息自动存储
short isDefault = Convert.ToInt16(collection["IS_DEFAULT_BANK"]);
var bankList = SR_BANK.Instance.GetList <SR_BANK.Entity>("CREATE_UID=?", SystemSession.UserID);
if (bankList != null && bankList.Count > 0)
{
var bank = bankList.FirstOrDefault(p => p.BANK_NO == ent.BANK_NO);
if (bank != null && bank.ID > 0)
{
bank.BANK_NAME = ent.BANK_NAME;
bank.BANK_ADDRESS = ent.BANK_ADDRESS;
bank.USER_NAME = ent.USER_NAME;
bank.USER_PHONE = ent.USER_PHONE;
bank.IS_DEFAULT = isDefault;
if (isDefault == 1)
{
foreach (var item in bankList)
{
item.IS_DEFAULT = 0;
SR_BANK.Instance.UpdateByKey(item, item.ID);
}
}
SR_BANK.Instance.UpdateByKey(bank, bank.ID);
}
else
{
if (isDefault == 1)
{
foreach (var item in bankList)
{
item.IS_DEFAULT = 0;
SR_BANK.Instance.UpdateByKey(item, item.ID);
}
}
SaveBank(ent, isDefault);
}
}
else
{
SaveBank(ent, isDefault);
}
#endregion
int addCount = 0, updateCount = 0, delCount = 0;
#region 02.保存经费清单列表
var fundsDetails = collection["FundsDetails"];
if (!string.IsNullOrWhiteSpace(fundsDetails) && fundsDetails.Length > 0)
{
SR_TOPIC_FUNDS_DETAIL.Instance.SaveFundsDetail(fundsId, ent.TOPIC_ID, fundsDetails, out addCount, out updateCount, out delCount);
#region 修改经费清单表字段：TOPIC_ID、TOPIC_FUNDS_ID
using (BDBHelper db = new BDBHelper())
{
db.ExecuteNonQuery(string.Format(@"UPDATE SR_TOPIC_FUNDS_DETAIL SET TOPIC_ID={0} WHERE TOPIC_FUNDS_ID={1}", ent.TOPIC_ID, fundsId));
}
#endregion
}
#endregion
result.IsSuccess = true;
result.Message = string.Format(@"填报经费报销信息成功：新增【{0}】,修改【{1}】,删除【{2}】", addCount, updateCount, delCount);
}
catch (Exception ex)
{
string err = string.Format(@"填报经费报销信息失败：{0}", ex.Message);
result.IsSuccess = false;
result.Message = err;
BLog.Write(BLog.LogLevel.ERROR, err);
WriteOperationLog(BLog.LogLevel.ERROR, false, Modular, "经费报销填报", "", err);
}
return(Json(result, JsonRequestBehavior.AllowGet));
}
/// <summary>
/// 启动
/// </summary>
public static void Start()
{
IsRun = true;
try
{
BLog.Write(BLog.LogLevel.INFO, "程序即将启动。");
MaxExecuteNodeCount = BConfig.GetConfigToInt("MaxExecuteNodeCount");
if (MaxExecuteNodeCount < 1)
{
MaxExecuteNodeCount = 10;
}
MaxUploadCount = BConfig.GetConfigToInt("MaxUploadCount");
EachUploadCount = BConfig.GetConfigToInt("EachUploadCount");
EachSearchUploadCount = BConfig.GetConfigToInt("EachSearchUploadCount");
MaxMonitCount = BConfig.GetConfigToInt("MaxMonitCount");
NeedClearCount = BConfig.GetConfigToInt("NeedClearCount");
//if (MaxExecuteNodeCount < 1)
//{
// MaxExecuteNodeCount = 10;
//}
if (bool.TryParse(BConfig.GetConfigToString("KeyFieldIsAutoIncrement"), out KeyFieldIsAutoIncrement) == false)
{
KeyFieldIsAutoIncrement = true;
}
if (bool.TryParse(BConfig.GetConfigToString("KeyFieldIsUseSequence"), out KeyFieldIsUseSequence) == false)
{
BLog.Write(BLog.LogLevel.FATAL, "KeyFieldIsUseSequence配置不正确，请在.config中配置为true或false。");
IsRun = false;
return;
}
if (KeyFieldIsAutoIncrement && KeyFieldIsUseSequence)
{
BLog.Write(BLog.LogLevel.FATAL, "KeyFieldIsAutoIncrement和KeyFieldIsUseSequence不可以同时配置为true，即：数据库已经可以自己实现自增长了，就不再需要另外配置序列，请在.config中修改配置。");
IsRun = false;
return;
}
if (KeyFieldIsAutoIncrement == false && KeyFieldIsUseSequence == false)
{
BLog.Write(BLog.LogLevel.FATAL, "KeyFieldIsAutoIncrement和KeyFieldIsUseSequence不可以同时配置为false，即：数据库不能实现自增长，对于oracle和DB2来说，就需要使用序列，请在.config中修改配置。");
IsRun = false;
return;
}
//节点任务记录器
_dicRunningNodeCaseID = new Dictionary <long, DateTime>();
lock (_dicRunningNodeCaseID)
{
_dicRunningNodeCaseID = new Dictionary <long, DateTime>();
}
_bw = new BackgroundWorker();
_bw.WorkerSupportsCancellation = true;
_bw.DoWork += bw_DoWork;
_bw.RunWorkerAsync();
#region 并行+非并行：停止遗留的(等待+执行中)任务组 （2018/12/13，与非并行的处理合并）
var supCaseList = BLL.EM_SCRIPT_CASE.Instance.GetNotStopCaseList();
if (supCaseList != null && supCaseList.Count > 0)
{
foreach (var sc in supCaseList)
{
BLL.EM_SCRIPT_CASE.Instance.SetStop(sc.ID, Enums.ReturnCode.Warn);//标记停止的为警告状态
}
}
#endregion
#region 作废： 非并行：停止等待中的任务组 （2018/12/13注释，与以上并行的处理合并）
//var noSupCaseList = BLL.EM_SCRIPT_CASE.Instance.GetRunningNoSuperveneCaseList();
//if (noSupCaseList != null && noSupCaseList.Count > 0)
//{
// foreach (var sc in noSupCaseList)
// {
// BLL.EM_SCRIPT_CASE.Instance.SetStop(sc.ID, Enums.ReturnCode.Success);
// }
//}
#endregion
#region 5回复为0
using (BDBHelper dbop = new BDBHelper())
{
dbop.ExecuteNonQuery(string.Format(@"update FM_MONIT_FILE set COPY_STATUS=0 where COPY_STATUS= 5"));
dbop.Close();//主动关闭
}
#endregion
#region 重启时删除临时表FM_MONIT_FILE_TEMP_PRO FM_MONIT_FILE_TEMP
using (BDBHelper dbop = new BDBHelper())
{
dbop.ExecuteNonQuery(string.Format(@"truncate table FM_MONIT_FILE_TEMP"));
dbop.ExecuteNonQuery(string.Format(@"truncate table FM_MONIT_FILE_TEMP_PRO"));
dbop.Close();//主动关闭
}
#endregion
//启动手动任务线程
Task.Hand.Start();
//启动节点扫描线程（含监控任务数量的限制处理，含添加的待拷贝文件列表）
Task.Scanner.Start();
BLog.Write(BLog.LogLevel.INFO, "程序已经启动。");
}
catch (Exception ex)
{
BLog.Write(BLog.LogLevel.FATAL, "程序启动失败。" + ex.ToString());
}
}
//作废
private void AddMonitKv()
{
var ipNotLists = global.OpIpNotList("getall");
log("输出未在线的ip：" + string.Join(",", ipNotLists.Select(p => p.V)));
#region 获取MaxUploadCount条待拷贝记录(排除未在线终端)
//采集待插入的文件列表
//采集未在线的终端列表
//lcz, 这个地方的sql可以只返回同一客户机ip的，便于下面的一个连接多个文件拷贝
//获取不返回一个ip的文件，在从monitKVList中获取5个一样ip的终端去处理
string sql = string.Format(@"SELECT A.ID, B.IP, A.COMPUTER_ID
FROM (SELECT ID, COMPUTER_ID
FROM (SELECT A.ID,
A.COMPUTER_ID,
ROW_NUMBER () OVER (ORDER BY A.ID) RN
FROM FM_MONIT_FILE A
LEFT JOIN ( SELECT DISTINCT REGEXP_SUBSTR ('{0}',
'[^,]+',
1,
LEVEL)
AS COMPUTER_ID
FROM DUAL
CONNECT BY REGEXP_SUBSTR ('{0}',
'[^,]+',
1,
LEVEL)
IS NOT NULL) C
ON (A.COMPUTER_ID = C.COMPUTER_ID)
LEFT JOIN FM_FILE_FORMAT F ON (F.ID=A.FILE_FORMAT_ID)
WHERE NVL (C.COMPUTER_ID, 0) = 0 AND F.NAME<>'Folder'
AND (A.COPY_STATUS = 0 OR A.COPY_STATUS = 3))
WHERE RN <={1}) A
LEFT JOIN FM_COMPUTER B ON (A.COMPUTER_ID = B.ID)", string.Join(",", ipNotLists.Select(p => p.K).Distinct()), Main.EachSearchUploadCount);
StringBuilder sb = new StringBuilder(); //待处理
//StringBuilder sbNotAlive = new StringBuilder();//未在线
List <string> notAliveList = new List <string>(); //当前查询的未在线
DataTable dt = null;
using (BDBHelper dbop = new BDBHelper())
{
dt = dbop.ExecuteDataTable(sql);
if (dt != null && dt.Rows.Count > 0)
{
string updateSql = string.Format(@"update FM_MONIT_FILE set COPY_STATUS=5 where id in({0})", string.Join(",", dt.AsEnumerable().Select(r => r["ID"]).Distinct().ToArray()).TrimEnd(','));
dbop.ExecuteNonQuery(updateSql);
}
dbop.Close();
}
log("查询出的数量为：【" + dt.Rows.Count + "】");
if (dt != null && dt.Rows.Count > 0)
{
List <string> hasAliveIps = new List <string>();//当前批次的在线ip
for (int i = 0; i < dt.Rows.Count; i++)
{
sb.Append(dt.Rows[i][0] + ",");
//校验ip
string curIp = dt.Rows[i][1].ToString().Trim();
//log("当前ip【" + curIp + "】");
var curKv = new KV {
K = Convert.ToInt64(dt.Rows[i][2].ToString()), V = dt.Rows[i][1].ToString()
}; //不在线的ip
if (string.IsNullOrEmpty(curIp))
{
log("ip[" + curIp + "]为空");
}
else if (hasAliveIps.Contains(curIp))
{
global.OpMonitKVList("add", new KV {
K = Convert.ToInt64(dt.Rows[i][0].ToString()), V = dt.Rows[i][1].ToString()
});
//log("ip[" + curIp + "]在已在线列表中");
}
else
{
if (ipNotLists.Exists(p => p.K == curKv.K))
{
//log("ip[" + curIp + "]未在线2");
using (BDBHelper dbop = new BDBHelper())
{
string updateSql = string.Format(@"update FM_MONIT_FILE set COPY_STATUS=0 where id ={0}", dt.Rows[i][0].ToString());
dbop.ExecuteNonQuery(updateSql);
}
if (!notAliveList.Contains(curKv.V))
{
notAliveList.Add(curKv.V);
}
}
else if (!Request.OldPingIP(curIp))
{
//log("ip[" + curIp + "]未在线");
using (BDBHelper dbop = new BDBHelper())
{
string updateSql = string.Format(@"update FM_MONIT_FILE set COPY_STATUS=0 where id ={0}", dt.Rows[i][0].ToString());
dbop.ExecuteNonQuery(updateSql);
}
global.OpIpNotList("add", curKv);
notAliveList.Add(dt.Rows[i][1].ToString());
if (!ipNotLists.Exists(p => p.K == curKv.K))
{
ipNotLists.Add(curKv);
}
}
else
{
global.OpMonitKVList("add", new KV {
K = Convert.ToInt64(dt.Rows[i][0].ToString()), V = dt.Rows[i][1].ToString()
});
hasAliveIps.Add(curIp);
log("ip[" + curIp + "]在线");
}
}
}
log("再次输出未在线ip：" + string.Join(",", global.OpIpNotList("getall").Select(p => p.V)));
#endregion
log("内存中无监控的文件列表，从数据库中去获取", 4, string.Format(@"执行查询的sql:\r\n{0}。\r\n查询的结果为：{1}", sql, sb));
log("获取到未在线的ip【" + (notAliveList.Count > 0 ? string.Join(",", notAliveList.Distinct()) : "") + "】,当前未在线的ip列表为【" + string.Join(" , ", global.ipNotList.Select(p => p.V)) + "】");
}
else
{
string msg = "未在库中查询到需要拷贝的文件，当前不存在需拷贝文件";
//log(msg);
log(msg, 3, string.Format(@"执行查询的sql:\r\n{0}。", sql));
//return null;
}
}
/// <summary>
/// 文件导入ORACLE测试
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void button4_Click(object sender, EventArgs e)
{
string tableName = "Z_LOAD_TEST";
string basePath = BConfig.BaseDirectory;
string fileName = "loadtest";
string ip = "10.95.240.9";
int port = 1521;
string dbName = "ORCL";
string user = "******";
string password = "******";
string createSql = string.Format("CREATE TABLE {0} (ID NUMBER(10), NAME NVARCHAR2(50))", tableName);
string txtFile = basePath + fileName + ".txt";
string ctlFile = basePath + fileName + ".ctl";
string batFile = basePath + fileName + ".bat";
StringBuilder sbMessage = new StringBuilder();
//先删除当前目录相应文件
try
{
DirectoryInfo di = new DirectoryInfo(basePath);
FileInfo[] fis = di.GetFiles(fileName + ".*");
foreach (FileInfo fi in fis)
{
fi.Delete();
}
sbMessage.AppendLine(string.Format("成功删除了【{0}】个旧文件", fis.Length));
}
catch (Exception ex)
{
sbMessage.AppendLine("删除旧文件失败");
sbMessage.AppendLine(ex.ToString());
}
//写数据文件
StringBuilder txtContent = new StringBuilder();
for (int i = 0; i < 10; i++)
{
txtContent.AppendLine(i + "\tname is:" + i);
}
//txtContent.AppendLine(11 + "aaa\tname is:" + 11);
File.WriteAllText(txtFile, txtContent.ToString());
//写格式文件
string ctlContent = string.Format(@"LOAD DATA
INFILE '{0}'
APPEND
INTO TABLE {1}
FIELDS TERMINATED BY '\t'
TRAILING NULLCOLS
(
ID,
NAME
)", txtFile, tableName);
File.WriteAllText(ctlFile, ctlContent);
//写批处理文件 mh/[email protected]:1521/ora10 control=fund_inf.ctl
string batContent = string.Format("sqlldr {0}/{1}@{2}:{3}/{4} control={5}", user, password, ip, port, dbName, ctlFile);
File.WriteAllText(batFile, batContent);
//创建表
using (BDBHelper dbHelper = new BDBHelper("ORACLE", ip, port, user, password, dbName, dbName))
{
try
{
dbHelper.ExecuteNonQuery(createSql);
sbMessage.AppendLine("创建表成功");
}
catch (Exception ex)
{
sbMessage.AppendLine("创建表出错");
sbMessage.AppendLine(ex.ToString());
}
}
//调用批处理，导入数据
string output = string.Empty;
try
{
System.Diagnostics.Process p = new System.Diagnostics.Process();
p.StartInfo.UseShellExecute = false;
p.StartInfo.CreateNoWindow = true;
p.StartInfo.WindowStyle = ProcessWindowStyle.Normal;
p.StartInfo.RedirectStandardOutput = true;
p.StartInfo.FileName = batFile;
p.StartInfo.WorkingDirectory = basePath;
p.Start();
output = p.StandardOutput.ReadToEnd();
p.WaitForExit();
p.Close();
sbMessage.AppendLine("导入表成功");
sbMessage.AppendLine(output);
}
catch (Exception ex)
{
sbMessage.AppendLine("导入表失败");
sbMessage.AppendLine(ex.ToString());
}
MessageBox.Show(sbMessage.ToString());
}
/// <summary>
/// 文件导DB2
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void button3_Click(object sender, EventArgs e)
{
string tableName = "ZZZ_TEST_3";
string basePath = BConfig.BaseDirectory;
string fileName = "loadtest";
string ip = "10.95.240.9";
int port = 443;
string dbName = "DMDY";
string user = "******";
string password = "******";
string createSql = string.Format("CREATE TABLE {0} (ID INT, NAME NVARCHAR2(50))", tableName);
string clearSql = string.Format("DELETE FROM {0}", tableName);
string txtFile = basePath + fileName + ".txt";
string batFile = basePath + fileName + ".bat";
StringBuilder sbMessage = new StringBuilder();
//先删除当前目录相应文件
try
{
DirectoryInfo di = new DirectoryInfo(basePath);
FileInfo[] fis = di.GetFiles(fileName + ".*");
foreach (FileInfo fi in fis)
{
fi.Delete();
}
sbMessage.AppendLine(string.Format("成功删除了【{0}】个旧文件", fis.Length));
}
catch (Exception ex)
{
sbMessage.AppendLine("删除旧文件失败");
sbMessage.AppendLine(ex.ToString());
}
//写数据文件
StringBuilder txtContent = new StringBuilder();
for (int i = 0; i < 10; i++)
{
txtContent.AppendLine(i + "\tname is:" + i);
}
//txtContent.AppendLine(11 + "aaa\tname is:" + 11);
File.WriteAllText(txtFile, txtContent.ToString());
//写批处理文件
StringBuilder batContent = new StringBuilder();
batContent.AppendLine(string.Format("db2 catalog tcpip node N240 remote {0} server {1}", ip, port));
batContent.AppendLine(string.Format("db2 catalog database {0} as DMDY240 at node N240 authentication server", dbName));
batContent.AppendLine(string.Format("db2 connect to DMDY240 user {0} using {1}", user, password));
batContent.AppendLine(string.Format("db2 load client from '{0}' of del modified by codepage = 1208 COLDEL0x09 insert into {1}", txtFile, tableName));
File.WriteAllText(batFile, batContent.ToString());
//创建表
using (BDBHelper dbHelper = new BDBHelper("DB2", ip, port, user, password, dbName, dbName))
{
//try
//{
// dbHelper.ExecuteNonQuery(createSql);
// sbMessage.AppendLine("创建表成功");
//}
//catch (Exception ex)
//{
// sbMessage.AppendLine("创建表出错");
// sbMessage.AppendLine(ex.ToString());
//}
try
{
dbHelper.ExecuteNonQuery(clearSql);
sbMessage.AppendLine("清空表成功");
}
catch (Exception ex)
{
sbMessage.AppendLine("清空表出错");
sbMessage.AppendLine(ex.ToString());
}
}
//调用批处理，导入数据
string output = string.Empty;
try
{
ProcessStartInfo info = new ProcessStartInfo();
info.FileName = @"C:\Program Files\IBM\SQLLIB_01\BIN\db2cwadmin.bat";
info.Arguments = batFile;
Process p = System.Diagnostics.Process.Start(info);
sbMessage.AppendLine("导入表成功");
sbMessage.AppendLine(output);
}
catch (Exception ex)
{
sbMessage.AppendLine("导入表失败");
sbMessage.AppendLine(ex.ToString());
}
MessageBox.Show(sbMessage.ToString());
return;
try
{
batFile = @"C:\Program Files\IBM\SQLLIB_01\BIN\db2cwadmin.bat";
System.Diagnostics.Process p = new System.Diagnostics.Process();
p.StartInfo.Arguments = batContent.ToString();
p.StartInfo.UseShellExecute = false;
p.StartInfo.CreateNoWindow = true;
p.StartInfo.WindowStyle = ProcessWindowStyle.Normal;
p.StartInfo.RedirectStandardOutput = true;
p.StartInfo.FileName = batContent.ToString();
p.StartInfo.WorkingDirectory = @"C:\Program Files\IBM\SQLLIB_01\BIN";
p.Start();
output = p.StandardOutput.ReadToEnd();
p.WaitForExit();
p.Close();
sbMessage.AppendLine("导入表成功");
sbMessage.AppendLine(output);
}
catch (Exception ex)
{
sbMessage.AppendLine("导入表失败");
sbMessage.AppendLine(ex.ToString());
}
MessageBox.Show(sbMessage.ToString());
}