public DataTable GetDataTable(int limit, int page, ref int count, string name, string orderByField = "CR.ID", string orderByType = "DESC")
{
string strWhere = "1=1";
List <object> param = new List <object>();
#region 添加参数
if (string.IsNullOrWhiteSpace(name) == false)
{
strWhere += " AND NAME LIKE '%" + name.Replace('\'', ' ') + "%'";
}
#endregion
string strSql = "SELECT CR.ID,CR.NAME,DB.NAME DBNAME,CHART_TYPE,(CASE IS_SHOW_EXPORT WHEN 1 THEN '是' ELSE '否' END )SHOWEXPORT,(CASE IS_SHOW_DEBUG WHEN 1 THEN '是' ELSE '否' END )SHOWDEBUG,SQL_CODE,(CASE IS_ENABLE WHEN 1 THEN '是' ELSE '否' END )IS_ENABLE,CR.CREATE_TIME,CR.update_time FROM BF_CHART_REPORT CR LEFT JOIN BF_DATABASE DB on CR.DB_ID=DB.ID WHERE " + strWhere;
//添加排序
if (string.IsNullOrWhiteSpace(orderByField) == false)
{
strSql += " ORDER BY " + orderByField + " " + (string.IsNullOrWhiteSpace(orderByType) == false ? orderByType : "");
}
using (BDBHelper dbHelper = new BDBHelper())
{
if (limit == 0 && page == 0)
{
return(dbHelper.ExecuteDataTableParams(strSql));//不分页查询所有
}
//算总记录
if (count == 0)
{
string sqlCount = string.Format("SELECT COUNT(*) FROM ({0})", strSql);
count = dbHelper.ExecuteScalarIntParams(sqlCount, param);
}
return(dbHelper.ExecuteDataTablePageParams(strSql, limit, page, param));
}
}
public DataTable GetLogDataTable(int limit, int page, int stakId, int nodeId, ref int count, string strMessage = "", string strSql = "", string orderByField = "STFNL.ID", string orderByType = "DESC")
{
string sql = @"SELECT STFNL.ID,SFL.NAME FLOWNAME,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
WHERE STFNL.TASK_ID =? AND STFNL.NODE_ID=? ";
if (string.IsNullOrWhiteSpace(strMessage) == false)
{
sql += " and stfnl.message LIKE '%" + strMessage.Replace('\'', ' ') + "%'";
}
if (string.IsNullOrWhiteSpace(strSql) == false)
{
sql += " and stfnl.SQL LIKE '%" + strSql.Replace('\'', ' ') + "%'";
}
//添加排序
if (string.IsNullOrWhiteSpace(orderByField) == false)
{
sql += " ORDER BY " + orderByField + " " + (string.IsNullOrWhiteSpace(orderByType) == false ? orderByType : "");
}
using (BDBHelper dbHelper = new BDBHelper())
{
if (limit == 0)
{
return(dbHelper.ExecuteDataTableParams(sql, stakId, nodeId));//不分页查询所有
}
//算总记录
if (count == 0)
{
string sqlCount = string.Format("SELECT COUNT(*) FROM ({0})", sql);
count = dbHelper.ExecuteScalarIntParams(sqlCount, stakId, nodeId);
}
return(dbHelper.ExecuteDataTablePageParams(sql, limit, page, stakId, nodeId));
}
}
public DataTable GetDataTable(int limit, int page, ref int count, string name, string typeId, string dbId, int self = 0, string orderByField = "sn.ID", string orderByType = "ASC")
{
string strWhere = "1=1";
List <object> param = new List <object>();
#region 添加参数
if (string.IsNullOrWhiteSpace(name) == false)
{
strWhere += " AND SN.NAME LIKE '%" + name.Replace('\'', ' ') + "%'";
}
if (string.IsNullOrWhiteSpace(typeId) == false)
{
int type = Convert.ToInt32(typeId);
List <int> types = BF_ST_TYPE.Instance.GetAllChildren(type);
if (types.Count > 0)
{
strWhere += string.Format(" AND SN.TYPE_ID IN ({0})", string.Join(",", types));
}
//param.Add(typeId);
}
if (string.IsNullOrWhiteSpace(dbId) == false)
{
strWhere += " AND SN.DB_ID = ?";
param.Add(dbId);
}
if (self > 0)
{
strWhere += " AND SN.CREATE_UID = ?";
param.Add(SystemSession.UserID);
}
#endregion
string strSql = "select sn.id, sn.NAME, stp.name typeName,sn.LAST_TASK_ID,SFL.name TaskName,db.name dbName,sn.RUN_STATUS,sn.LAST_TASK_IS,sn.LAST_TASK_ST,sn.LAST_TASK_FT,sn.CREATE_TIME ";
strSql += ",(select FULL_NAME from BF_USER WHERE ID=SN.CREATE_UID )CREATE_NAME,sn.UPDATE_TIME,(select FULL_NAME from BF_USER WHERE ID=SN.UPDATE_UID )UPDATE_NAME ";
strSql += " from BF_ST_NODE sn left join BF_ST_flow sfl on sn.LAST_TASK_ID = SFL.ID left join BF_DATABASE db on SN.DB_ID = DB.ID left join BF_ST_TYPE stp on sn.type_id = stp.id where " + strWhere;
//添加排序
if (string.IsNullOrWhiteSpace(orderByField) == false)
{
strSql += " ORDER BY " + orderByField + " " + (string.IsNullOrWhiteSpace(orderByType) == false ? orderByType : "");
}
using (BDBHelper dbHelper = new BDBHelper())
{
if (limit == 0 && page == 0)
{
return(dbHelper.ExecuteDataTableParams(strSql, param));//不分页查询所有
}
//算总记录
if (count == 0)
{
string sqlCount = string.Format("SELECT COUNT(*) FROM ({0})", strSql);
count = dbHelper.ExecuteScalarIntParams(sqlCount, param);
}
return(dbHelper.ExecuteDataTablePageParams(strSql, limit, page, param));
}
}
public DataTable GetDataTable(int limit, int page, string strWhere, List <object> value)
{
string strSql = "select f.id,f.name,(case f.db_id when 0 then '本地默认数据库' else to_char(DB.NAME) end ) DBNAME,TABLE_NAME,CREATE_TABLE_MODE,(case IS_ALLOW_DELETE when 1 then '是' else '否' end )IS_ALLOW_DELETE,(case IS_ENABLE when 1 then '是' else '否' end )IS_ENABLE,f.UPDATE_TIME,f.REMARK from BF_FORM f left join BF_DATABASE db on f.db_id=db.id where " + strWhere + " order by f.id desc";
using (BDBHelper dbHelper = new BDBHelper())
{
if (limit == 0 && page == 0)
{
return(dbHelper.ExecuteDataTableParams(strSql, value));//不分页查询所有
}
return(dbHelper.ExecuteDataTablePageParams(strSql, limit, page, value));
}
}
public DataTable GetDataTable(int limit, int page, ref int count, int pid, string name, string orderByField, string orderByType)
{
string strWhere = "1=1";
List <object> param = new List <object>();
if (pid > 0)
{
strWhere += " AND st.PID=?";
param.Add(pid);
}
if (string.IsNullOrWhiteSpace(name) == false)
{
strWhere += " AND st.NAME LIKE '%" + name.Replace('\'', ' ') + "%'";
}
string strSql = "select st.id, st.NAME,(select stp.name from BF_ST_TYPE stp where stp.id=st.PID)PNAME,st.REMARK,(select full_name from BF_USER where st.CREATE_UID=ID)createName,st.CREATE_TIME,(select full_name from BF_USER where st.UPDATE_UID=ID)updateName,st.UPDATE_TIME from BF_ST_TYPE st where " + strWhere;
//添加排序
if (string.IsNullOrWhiteSpace(orderByField) == false)
{
strSql += " ORDER BY " + orderByField + " " + (string.IsNullOrWhiteSpace(orderByType) == false ? orderByType : "");
}
using (BDBHelper dbHelper = new BDBHelper())
{
if (limit == 0 && page == 0)
{
return(dbHelper.ExecuteDataTableParams(strSql, param));//不分页查询所有
}
//算总记录
if (count == 0)
{
string sqlCount = string.Format("SELECT COUNT(*) FROM ({0})", strSql);
count = dbHelper.ExecuteScalarIntParams(sqlCount, param);
}
return(dbHelper.ExecuteDataTablePageParams(strSql, limit, page, param));
}
}
/// <summary>
/// 递归更新子节点的层级
/// </summary>
/// <param name="pCode"></param>
/// <param name="level"></param>
/// <param name="dbHelper"></param>
/// <param name="updateCount"></param>
public void RefreshSubNodesLevel(int pCode, int level, BDBHelper dbHelper, ref int updateCount)
{
level++;
string sqlSelect = "SELECT CODE FROM BF_DEPARTMENT WHERE P_CODE=?";
DataTable dt = dbHelper.ExecuteDataTableParams(sqlSelect, pCode);
if (dt == null || dt.Rows.Count < 1)
{
return;
}
string sqlUpdate = "UPDATE BF_DEPARTMENT SET DEPT_LEVEL=" + level + " WHERE P_CODE=?";
int i = dbHelper.ExecuteNonQueryParams(sqlUpdate, pCode);
if (i > 0)
{
foreach (DataRow dr in dt.Rows)
{
int code = Convert.ToInt32(dr["CODE"]);
RefreshSubNodesLevel(code, level, dbHelper, ref updateCount);
}
}
updateCount += i;
}
/// <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));
}
}
}
public DataTable GetDataTable(int limit, int page, ref int count, string daterange = "", string flowName = "", string nodeName = "", string param = "", string statusId = "", string successId = "", string orderByField = "st.ID", string orderByType = "ASC")
{
string strWhere = "1=1";
List <object> paramList = new List <object>();
#region 添加参数
DateTime endDate = DateTime.Today;
DateTime beginDate = endDate.AddDays(-endDate.Day);
beginDate = beginDate.AddDays(1 - beginDate.Day);
if (string.IsNullOrWhiteSpace(daterange) == false)
{
Functions.GetIntervalDate(daterange, ref beginDate, ref endDate);
}
strWhere += " AND ST.REFERENCE_DATE>=? AND ST.REFERENCE_DATE<?";
paramList.Add(beginDate.Date);
paramList.Add(endDate.AddDays(1).Date);
if (string.IsNullOrWhiteSpace(flowName) == false)
{
strWhere += " and SF.NAME like '%" + flowName.Replace('\'', ' ') + "%'";
}
if (string.IsNullOrWhiteSpace(nodeName) == false)
{
strWhere += " AND SN.NAME like '%" + nodeName.Replace('\'', ' ') + "%'";
}
if (string.IsNullOrWhiteSpace(param) == false)
{
strWhere += " AND ST.PARAMETER like '%" + param.Replace('\'', ' ') + "%'";
}
if (string.IsNullOrWhiteSpace(statusId) == false)
{
strWhere += " AND st.RUN_STATUS =?";
paramList.Add(statusId);
}
if (string.IsNullOrWhiteSpace(successId) == false)
{
strWhere += " AND st.IS_SUCCESS =?";
paramList.Add(successId);
}
#endregion
string strSql = "select st.id,sf.name FLOWNAME,SN.NAME NODENAME,st.IS_MANUAL,st.REFERENCE_DATE,st.RETRY_TIMES,st.RUN_STATUS,st.IS_SUCCESS ,st.START_TIME,st.FINISH_TIME,st.CREATE_TIME,st.REMARK, st.BEGIN_DATE, st.END_DATE, st.PARAMETER ";
strSql += "from BF_ST_TASK st left join BF_ST_FLOW sf on ST.FLOW_ID = SF.ID left join BF_ST_NODE sn on ST.NODE_ID = SN.ID where " + strWhere;
//排序
if (string.IsNullOrWhiteSpace(orderByField) == false)
{
strSql += " ORDER BY " + orderByField + " " + (string.IsNullOrWhiteSpace(orderByType) == false ? orderByType : "");
}
using (BDBHelper dbHelper = new BDBHelper())
{
if (limit == 0 && page == 0)
{
return(dbHelper.ExecuteDataTableParams(strSql, paramList));//不分页查询所有
}
//算总记录
if (count == 0)
{
string sqlCount = string.Format("SELECT COUNT(*) FROM ({0})", strSql);
count = dbHelper.ExecuteScalarIntParams(sqlCount, paramList);
}
return(dbHelper.ExecuteDataTablePageParams(strSql, limit, page, paramList));
}
}
/// <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);
}