private void buttonPage_Click(object sender, EventArgs e)
{
try
{
labelInfo.Text = "";
TimeSpan ts = new TimeSpan();
string dbType = comboBoxDbType.Text;
string sql = textBoxSql.Text;
int pageSize = (int)numericUpDownPageSize.Value;
int pageIndex = (int)numericUpDownPageIndex.Value;
int start = (pageIndex - 1) * pageSize + 1;
DataTable dt = new DataTable();
using (BDBHelper dbHelper = new BDBHelper(dbType, textBoxConn.Text))
{
DateTime begin = DateTime.Now;
dt = dbHelper.ExecuteDataTablePage(sql, pageSize, pageIndex);
ts = DateTime.Now - begin;
}
dataGridView1.DataSource = dt;
labelTime.Text = ts.TotalMilliseconds + "毫秒";
labelInfo.Text = "返回时间：" + DateTime.Now.ToString();
}
catch (Exception ex)
{
labelInfo.Text = ex.Message;
}
}
/// <summary>
/// 执行一条SQL语句，返回第一行第一列object
/// </summary>
/// <param name="dbID"></param>
/// <param name="sql"></param>
/// <returns></returns>
public object ExecuteScalar(int dbID, string sql)
{
object obj = 0;
if (dbID < 0)
{
throw new Exception("错误的数据库ID");
}
if (string.IsNullOrWhiteSpace(sql))
{
throw new Exception("SQL语句不可为空");
}
using (BDBHelper dbHelper = GetBDBHelper(dbID))
{
try
{
obj = dbHelper.ExecuteScalar(sql);
}
catch (Exception ex)
{
throw new Exception("在数据库" + dbID + "执行SQL查询出错：" + ex.Message);
}
}
return(obj);
}
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 DBServer()
{
if (!Directory.Exists("LocalSQLDB"))
{
Directory.CreateDirectory("LocalSQLDB");
}
if (!Directory.Exists("DBCfg"))
{
Directory.CreateDirectory("DBCfg");
}
LocalSQLFile = Path.Combine("LocalSQLDB", "localdb.db");
KVDir = "LocalKVDB";
SQLCfgDir = "DBCfg";
RedisCfg = Path.Combine("DBCfg", "Redis.Cfg");
memHelper = new SqliteHelper();
bDBHelper = new BDBHelper <object, object>();
kvdir = bDBHelper.EnvHome;
bDBHelper.EnvHome = KVDir;
bDBHelper.Reset();
sqliteHelper = new SqliteHelper();
sqliteHelper.ConnectString = LocalSQLFile;
sqliteHelper.CreateEmptyDB(LocalSQLFile);
DBAcessPool.SetConfigDir(SQLCfgDir);
LoadRedisCfg();
}
private void buttonFlow_Click(object sender, EventArgs e)
{
try
{
labelInfo.Text = "";
TimeSpan ts = new TimeSpan();
string dbType = comboBoxDbType.Text;
string sql = textBoxSql.Text;
int pageSize = (int)numericUpDownPageSize.Value;
int pageIndex = (int)numericUpDownPageIndex.Value;
int start = (pageIndex - 1) * pageSize + 1;
int rowsCount = 0;
DataTable dt = new DataTable();
using (BDBHelper dbHelper = new BDBHelper(dbType, textBoxConn.Text))
{
DateTime begin = DateTime.Now;
using (IDataReader reader = dbHelper.ExecuteReader(sql))
{
for (int c = 0; c < reader.FieldCount; c++)
{
dt.Columns.Add(reader.GetName(c), reader.GetFieldType(c));
}
int i = 0;
while (reader.Read())
{
i++;
if (i < start)
{
continue;
}
DataRow dr = dt.NewRow();
for (int c = 0; c < reader.FieldCount; c++)
{
dr[c] = reader.GetValue(c);
}
dt.Rows.Add(dr);
rowsCount++;
if (rowsCount >= pageSize)
{
break;
}
}
ts = DateTime.Now - begin;
}
}
dataGridView1.DataSource = dt;
labelTime.Text = ts.TotalMilliseconds + "毫秒";
labelInfo.Text = "返回时间：" + DateTime.Now.ToString();
}
catch (Exception ex)
{
labelInfo.Text = ex.Message;
}
}
private void button13_Click(object sender, EventArgs e)
{
int rowsCount = 0;
int pageSize = 50;
int pageIndex = 0;
string sql = "select * from EM_MODULE_EVENT";
string dbType = "oracle";
string ip = "139.196.212.68";
int port = 1521;
string user = "******";
string password = "******";
string serviceName = "ORCL";
using (BDBHelper dbHelper = new BDBHelper(dbType, ip, port, user, password, serviceName, serviceName))
{
using (IDataReader reader = dbHelper.ExecuteReader(sql))
{
int i = 0;
DataTable dt = new DataTable();
for (int c = 0; c < reader.FieldCount; c++)
{
dt.Columns.Add(reader.GetName(c), reader.GetFieldType(c));
}
bool isCanRead = reader.Read();
while (true && isCanRead)
{
DataRow dr = dt.NewRow();
for (int c = 0; c < reader.FieldCount; c++)
{
dr[c] = reader.GetValue(c);
}
dt.Rows.Add(dr);
i++;
rowsCount++;
isCanRead = reader.Read();
if (i >= pageSize || isCanRead == false)
{
pageIndex++;
string fileName = "G:/dt_" + pageIndex + ".txt";
WriteDataTableIntoFile(dt, fileName);
dt.Rows.Clear();
i = 0;
}
if (isCanRead == false)
{
break;
}
}
}
}
MessageBox.Show(string.Format("共有{0}页，{1}条记录，已经写入文件。", pageIndex, rowsCount));
}
/// <summary>
/// 获取数据库表列表
/// </summary>
/// <param name="dbID">数据库ID</param>
/// <param name="isUsedInImport">是否用于外导表</param>
/// <returns>表名列表</returns>
public List <string> GetTableList(int dbID, bool isUsedInImport)
{
List <string> list = new List <string>();
string username = "";
if (dbID < 0)
{
return(new List <string>());
}
else if (dbID == 0)
{
using (BDBHelper dbHelper = new BDBHelper())
{
username = dbHelper.UserName;
list = dbHelper.GetTablesList();
}
}
else
{
BF_DATABASE.Entity entity = BF_DATABASE.Instance.GetEntityByKey <BF_DATABASE.Entity>(dbID);
if (entity == null)
{
throw new Exception("数据库" + dbID + "不存在");
}
string dbType = GetDbTypeName(entity.DB_TYPE);
using (BDBHelper dbHelper = new BDBHelper(dbType, entity.IP, entity.PORT, entity.USER_NAME, entity.PASSWORD, entity.DB_NAME, entity.DB_NAME))
{
username = dbHelper.UserName;
list = dbHelper.GetTablesList();
}
}
if (list == null || list.Count < 1)
{
return(new List <string>());
}
List <string> l = new List <string>();
int i = 0;
foreach (string t in list)
{
string name = t.Replace(username.ToUpper() + ".", "");
//外导时，排除框架所用的表
if (isUsedInImport == true && name.StartsWith("BF_"))
{
//continue;
}
l.Add(name);
i++;
//最多返回200张表
if (i >= 200 && isUsedInImport == false)
{
break;
}
}
return(l);
}
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 DataStorage()
{
//更详细的使用方式，可以访问
//https://www.oracle.com/database/technologies/related/berkeleydb-downloads.html
//下载源码包，里面有C#使用示例
//这里只是介绍基本使用
InitializeComponent();
bdb = new BDBHelper(DBPath);
}
/// <summary>
/// 更新所有节点的层级
/// </summary>
/// <param name="id"></param>
/// <param name="level"></param>
/// <returns></returns>
public int RefreshNodesLevel(int id, int level)
{
int updateCount = 0;
using (BDBHelper dbHelper = new BDBHelper())
{
RefreshSubNodesLevel(id, level, dbHelper, ref updateCount);//进入递归
}
return(updateCount);
}
/// <summary>
/// 添加脚本流节点配置的实例（后期执行脚本流时，按此配置顺序执行相应节点）
/// </summary>
/// <param name="scriptID">脚本流ID</param>
/// <param name="scriptCaseID">脚本流实例ID</param>
/// <returns></returns>
public List <long> AddReturnNodeIDList(long scriptID, long scriptCaseID)
{
IList <EM_SCRIPT_REF_NODE.Entity> refList = EM_SCRIPT_REF_NODE.Instance.GetNodeListByScriptID(scriptID);
//用于去重
Dictionary <long, byte> dic = new Dictionary <long, byte>();
if (refList != null && refList.Count > 0)
{
using (BDBHelper dbHelper = new BDBHelper())
{
//开始事务
dbHelper.BeginTrans();
foreach (EM_SCRIPT_REF_NODE.Entity refEntity in refList)
{
try
{
Entity entity = new Entity();
if (Main.KeyFieldIsUseSequence)
{
entity.ID = GetNextValueFromSeq();
}
entity.SCRIPT_ID = scriptID;
entity.SCRIPT_CASE_ID = scriptCaseID;
entity.PARENT_NODE_ID = refEntity.PARENT_NODE_ID;
entity.CURR_NODE_ID = refEntity.CURR_NODE_ID;
entity.REMARK = refEntity.REMARK;
int i = Add(entity);
if (i < 0)
{
dbHelper.RollbackTrans();
dic.Clear();
break;
}
if (dic.ContainsKey(entity.CURR_NODE_ID) == false)
{
dic.Add(entity.CURR_NODE_ID, 1);
}
}
catch (Exception ex)
{
BLog.Write(BLog.LogLevel.ERROR, "添加脚本流节点配置的实例出错\t" + ex.ToString());
//出错回滚
dbHelper.RollbackTrans();
return(new List <long>());
}
}
//提交事务
dbHelper.CommitTrans();
dbHelper.Close();
}
}
return(dic.Keys.ToList <long>());
}
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));
}
}
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));
}
}
/// <summary>
/// 记录重试次数
/// </summary>
/// <param name="scriptNodeCaseID">脚本节点实例ID</param>
/// <returns>更新后的重试次数</returns>
public int RecordTryTimes(long scriptNodeCaseID)
{
string sqlUpdate = "UPDATE " + TableName + " SET RETRY_TIME=RETRY_TIME+1 WHERE ID =?";
string sqlGet = "SELECT RETRY_TIME FROM " + TableName + " WHERE ID =?";
int i = 0;
using (BDBHelper dbHelper = new BDBHelper())
{
i = dbHelper.ExecuteNonQueryParams(sqlUpdate, scriptNodeCaseID);
if (i > 0)
{
i = dbHelper.ExecuteScalarIntParams(sqlGet, scriptNodeCaseID);
}
}
return(i);
}
/// <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>
/// 导入数据测试
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void button2_Click(object sender, EventArgs e)
{
string sql = "select * from em_script_node";
DataTable dt = new DataTable();
dt.Columns.Add("ID", typeof(int));
dt.Columns.Add("NAME", typeof(string));
dt.Columns.Add("CREATETIME", typeof(DateTime));
for (int i = 0; i < 10; i++)
{
DataRow dr = dt.NewRow();
dr[0] = i;
dr[1] = "name is: " + i;
dr[2] = DateTime.Now.AddMinutes(i);
dt.Rows.Add(dr);
}
string tableName = "zz_0628";
int n = 0;
TimeSpan ts = new TimeSpan();
using (BDBHelper dbHelper = new BDBHelper())
{
//从另外一张表查询出结果再导入
//dt = dbHelper.ExecuteDataTable(sql);
try
{
dbHelper.Drop(tableName);
}
catch
{
}
if (dbHelper.TableIsExists(tableName))
{
dbHelper.Drop(tableName, false);
}
dbHelper.CreateTableFromDataTable(tableName, dt);
DateTime begin = DateTime.Now;
n = dbHelper.LoadDataInDataTable(tableName, dt);
ts = DateTime.Now - begin;
}
MessageBox.Show(string.Format("共有【{0}】条记录导入表【{1}】，用时【{2}】毫秒。", n, tableName, ts.TotalMilliseconds));
}
/// <summary>
/// 获取指定库的全量表集合
/// </summary>
/// <param name="dbID"></param>
/// <returns></returns>
public List <string> GetAllTableList(int dbID)
{
List <string> list = new List <string>();
string username = "";
if (dbID < 0)
{
return(new List <string>());
}
else if (dbID == 0)
{
using (BDBHelper dbHelper = new BDBHelper())
{
username = dbHelper.UserName;
list = dbHelper.GetTablesList();
}
}
else
{
BF_DATABASE.Entity entity = BF_DATABASE.Instance.GetEntityByKey <BF_DATABASE.Entity>(dbID);
if (entity == null)
{
throw new Exception("数据库" + dbID + "不存在");
}
string dbType = GetDbTypeName(entity.DB_TYPE);
using (BDBHelper dbHelper = new BDBHelper(dbType, entity.IP, entity.PORT, entity.USER_NAME, entity.PASSWORD, entity.DB_NAME, entity.DB_NAME))
{
username = dbHelper.UserName;
list = dbHelper.GetTablesList();
}
}
if (list == null || list.Count < 1)
{
return(new List <string>());
}
else
{
return(list);
}
}
public string GetUserRole(int userId)
{
string strResult = "";
if (userId <= 0)
{
return(strResult);
}
using (BDBHelper dbHelper = new BDBHelper())
{
string strSql = "select menu_ids from bf_user us left join BF_ROLE role on US.ROLE_IDS=ROLE.ID where us.id=" + userId;
object objResult = dbHelper.ExecuteReader(strSql);
if (objResult != null)
{
return(objResult.ToString());
}
}
return(strResult);
}
/// <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>
/// 根据数据库ID获取数据库实例
/// </summary>
/// <param name="id">数据库ID（如果为0，则为本地默认数据库）</param>
/// <returns></returns>
public Entity GetDbByID(int id)
{
if (id == 0)
{
Entity entity = new Entity();
entity.ID = 0;
entity.NAME = DEFAULT_DB_NAME;
using (BDBHelper dbHelper = new BDBHelper())
{
switch (dbHelper.DbType.ToLower())
{
case "oracle":
entity.DB_TYPE = Enums.DBType.Oracle.GetHashCode();
break;
case "db2":
entity.DB_TYPE = Enums.DBType.DB2.GetHashCode();
break;
case "vertica":
entity.DB_TYPE = Enums.DBType.Vertica.GetHashCode();
break;
case "gbase":
entity.DB_TYPE = Enums.DBType.GBase.GetHashCode();
break;
}
entity.IP = dbHelper.IP;
entity.PORT = dbHelper.Port;
entity.USER_NAME = dbHelper.UserName;
entity.PASSWORD = dbHelper.Password;
entity.DB_NAME = dbHelper.DataBase;
Base.Log.BLog.Write(Base.Log.BLog.LogLevel.DEBUG, string.Format("选择本地数据库：{0}:{1},{2},{3},{4}", entity.IP, entity.PORT, entity.USER_NAME, entity.PASSWORD, entity.DB_NAME));
}
return(entity);
}
return(GetEntityByKey <Entity>(id));
}
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;
}
private void button7_Click(object sender, EventArgs e)
{
string conn = string.Empty;
string file = string.Empty;
string tableName = "";
int i = 0;
//方法一，写文件再导
using (BDBHelper dbHelper = new BDBHelper("DB2", conn))
{
i = dbHelper.LoadDataInLocalFile(tableName, file);
}
//方法二，转DataTable，再导入
DataTable dt = new DataTable();
using (BDBHelper dbHelper = new BDBHelper("DB2", conn))
{
//使用（适合数据量小，纯内存方式）
i = dbHelper.LoadDataInDataTable(tableName, dt);
//或者（适合数据量很大，超过10万的级别，内部会先写文件再导入）
i = dbHelper.LoadDataInDataTableWithFile(tableName, dt);
}
}
/// <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());
}
}
/// <summary>
/// 检测是否可上传，并将异常抛出
/// </summary>
/// <param name="id">配置ID</param>
/// <param name="rowkey">主键值</param>
/// <param name="yyyymmdd">如果是按日期为后缀的动态表，则需要传入该表日期</param>
/// <param name="tableName">表名</param>
public Entity CheckUpload(int id, string rowkey, string yyyymmdd, out string tableName)
{
if (id < 1)
{
throw new Exception("请通过参数id传入正确的配置ID");
}
if (string.IsNullOrWhiteSpace(rowkey))
{
throw new Exception("请通过参数rowkey传入附件所属记录的主键值");
}
Entity entity = GetEntityByKey <Entity>(id);
if (entity == null)
{
throw new Exception("未找到配置，请通过参数id传入正确的配置ID");
}
tableName = entity.TABLE_NAME;
int ymd = 0;
switch (entity.CREATE_TABLE_MODE)
{
case (short)Enums.CreateTableMode.年份后缀:
if (string.IsNullOrWhiteSpace(yyyymmdd) || yyyymmdd.Length < 4 || int.TryParse(yyyymmdd, out ymd) == false)
{
throw new Exception("由于目标表以年份为后缀，请通过yyyymmdd传入相应日期");
}
tableName += "_" + yyyymmdd.Substring(0, 4);
break;
case (short)Enums.CreateTableMode.年月后缀:
if (string.IsNullOrWhiteSpace(yyyymmdd) || yyyymmdd.Length < 6 || int.TryParse(yyyymmdd, out ymd) == false)
{
throw new Exception("由于目标表以年月为后缀，请通过yyyymmdd传入相应日期");
}
tableName += "_" + yyyymmdd.Substring(0, 6);
break;
case (short)Enums.CreateTableMode.年月日后缀:
if (string.IsNullOrWhiteSpace(yyyymmdd) || yyyymmdd.Length != 8 || int.TryParse(yyyymmdd, out ymd) == false)
{
throw new Exception("由于目标表以日期为后缀，请通过yyyymmdd传入相应日期");
}
tableName += "_" + yyyymmdd;
break;
case (short)Enums.CreateTableMode.用户ID后缀:
tableName += "_" + SystemSession.UserID;
break;
}
BDBHelper dbHelper = null;
try
{
if (entity.DB_ID == 0)
{
dbHelper = new BDBHelper();
}
else
{
BF_DATABASE.Entity db = BF_DATABASE.Instance.GetEntityByKey <BF_DATABASE.Entity>(entity.DB_ID);
if (db == null)
{
throw new Exception("数据库配置错误");
}
string dbType = BF_DATABASE.GetDbTypeName(db.DB_TYPE);
dbHelper = new BDBHelper(dbType, db.IP, db.PORT, db.USER_NAME, db.PASSWORD, db.DB_NAME, db.DB_NAME);
}
if (dbHelper.TableIsExists(tableName) == false)
{
throw new Exception("目标表" + tableName + "不存在");
}
string sql = string.Format("SELECT * FROM {0} WHERE {1}=?", tableName, entity.KEY_FIELD);
DataRow dr = dbHelper.ExecuteDataRowParams(sql, rowkey);
if (dr == null)
{
throw new Exception(string.Format("在目标表{0}中，没有找到主键{1}值为{2}的记录", tableName, entity.KEY_FIELD, rowkey));
}
}
catch (Exception ex)
{
throw new Exception("未知错误：" + ex.Message);
}
finally
{
if (dbHelper != null)
{
dbHelper.Dispose();
}
}
return(entity);
}
/// <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));
}
}
}
/// <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));
}
