public DataTable GetDataTable(int limit, int page, string strWhere, List <object> value)
{
string strSql = "select sfl.name flowName,sno.name nodeName,stfnl.log_time,stfnl.log_level,stfnl.message,stfnl.sql from BF_ST_TASK_FLOW_NODE_LOG stfnl left join BF_ST_NODE sno on STFNL.NODE_ID=SNO.ID left join BF_ST_flow sfl on STFNL.FLOW_ID=SFL.ID " + strWhere + " order by id";
using (BDBHelper dbHelper = new BDBHelper())
{
if (limit == 0 && page == 0)
{
return(dbHelper.ExecuteDataTable(strSql));//不分页查询所有
}
return(dbHelper.ExecuteDataTablePageParams(strSql, limit, page, value));
}
}
/// <summary>
/// 查询报表并返回结果集
/// </summary>
/// <param name="entity"></param>
/// <param name="generatedSQL">解析后的SQL语句</param>
/// <param name="paramList">参数列表</param>
/// <param name="pageSize"></param>
/// <param name="pageIndex"></param>
/// <returns></returns>
public static DataTable QueryTable(Entity entity, string generatedSQL, List <object> paramList, int pageSize, int pageIndex)
{
if (entity == null || string.IsNullOrWhiteSpace(generatedSQL))
{
return(new DataTable());
}
string sql = generatedSQL;
if (entity.DB_ID <= 0)
{
using (BDBHelper dbHelper = new BDBHelper())
{
if (paramList == null || paramList.Count < 1)
{
if (pageSize < 1)
{
return(dbHelper.ExecuteDataTable(sql));
}
return(dbHelper.ExecuteDataTablePage(sql, pageSize, pageIndex));
}
if (pageSize < 1)
{
return(dbHelper.ExecuteDataTableParams(sql, paramList));
}
return(dbHelper.ExecuteDataTablePageParams(sql, pageSize, pageIndex, paramList));
}
}
else
{
BF_DATABASE.Entity db = BF_DATABASE.Instance.GetEntityByKey <BF_DATABASE.Entity>(entity.DB_ID);
if (db == null)
{
throw new Exception("数据库不存在");
}
string dbType = Enums.DBType.Oracle.ToString();
try
{
dbType = ((Enums.DBType)db.DB_TYPE).ToString();
}
catch
{
throw new Exception("未知的数据库类型");
}
using (BDBHelper dbHelper = new BDBHelper(dbType, db.IP, db.PORT, db.USER_NAME, db.PASSWORD, db.DB_NAME, db.DB_NAME))
{
if (paramList == null || paramList.Count < 1)
{
if (pageSize < 1)
{
return(dbHelper.ExecuteDataTable(sql));
}
return(dbHelper.ExecuteDataTablePage(sql, pageSize, pageIndex));
}
if (pageSize < 1)
{
return(dbHelper.ExecuteDataTableParams(sql, paramList));
}
return(dbHelper.ExecuteDataTablePageParams(sql, pageSize, pageIndex, paramList));
}
}
}
//作废
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>
/// 往待拷贝列表中加入文件编号
/// </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);
}
}
/// <summary>
/// 在指定数据库执行查询语句
/// </summary>
/// <param name="dbID">数据库ID</param>
/// <param name="sql">SQL语句</param>
/// <param name="paramList">参数列表</param>
/// <param name="rowsCount">记录数（如果传入值等于0，则会重新计算此值，反之不计算）</param>
/// <param name="pageSize">分页大小（默认为10，0表示不分页）</param>
/// <param name="pageIndex">页号（从1开始）</param>
/// <returns>DataTable</returns>
public DataTable ExecuteSelectSQL(int dbID, string sql, List <object> paramList, ref int rowsCount, int pageSize = 10, int pageIndex = 1)
{
if (dbID < 0)
{
throw new Exception("错误的数据库ID");
}
if (string.IsNullOrWhiteSpace(sql))
{
throw new Exception("SQL语句不可为空");
}
string s = sql.Trim();
if (s.ToUpper().StartsWith("SELECT ") == false)
{
throw new Exception("只能执行SLECT语句！");
}
DataTable dt = null;
using (BDBHelper dbHelper = GetBDBHelper(dbID))
{
try
{
if (pageSize <= 0)
{
if (paramList == null || paramList.Count < 1)
{
dt = dbHelper.ExecuteDataTable(sql);
}
else
{
dt = dbHelper.ExecuteDataTableParams(sql, paramList);
}
if (dt != null)
{
rowsCount = dt.Rows.Count;
}
}
else
{
if (paramList == null || paramList.Count < 1)
{
dt = dbHelper.ExecuteDataTablePage(sql, pageSize, pageIndex);
}
else
{
dt = dbHelper.ExecuteDataTablePageParams(sql, pageSize, pageIndex, paramList);
}
if (dt != null && dt.Rows.Count < pageSize && pageIndex <= 1)
{
rowsCount = dt.Rows.Count;
}
else if (rowsCount == 0)
{
if (paramList == null || paramList.Count < 1)
{
rowsCount = dbHelper.ExecuteScalarInt("SELECT COUNT(*) C FROM (" + sql + ")");
}
else
{
rowsCount = dbHelper.ExecuteScalarIntParams("SELECT COUNT(*) C FROM (" + sql + ")", paramList);
}
}
}
}
catch (Exception ex)
{
throw new Exception("在数据库" + dbID + "执行SQL查询出错：" + ex.Message);
}
}
if (pageIndex <= 1 && (dt == null || dt.Rows.Count == 0))
{
rowsCount = 0;
}
return(dt);
}
