public DataTable getSelectItem(string tableName
, string fieldKey
, string fieldValue
, int checkDataRole = 0)
{
DataTable dt = new DataTable();
//这里执行
try
{
sqlHelper sh = new sqlHelper();
ISqlSugarClient db = sh.dbClient();
string sql = "select * from " + tableName + " where flag=1";
if (!string.IsNullOrEmpty(fieldKey) &&
!string.IsNullOrEmpty(fieldValue))
{
sql += " and " + fieldKey + "='" + fieldValue + "'";
}
if (checkDataRole == 1)
{
#region
#endregion
}
dt = db.SqlQueryable <object>(sql).ToDataTable();
}
catch (Exception ex)
{
}
return(dt);
}
public DataTable get_times_user_softTop5(JObject passJson)
{
sqlHelper sh = new sqlHelper();
string timeQujian = passJson["timeQujian"].ToString();
string[] TimerArray = new string[2];
if (timeQujian != "")
{
TimerArray = timeQujian.Split('~');
}
string userIdList = passJson["userIdList"].ToString();
string sql = "select * from (select *,ROW_NUMBER() over(partition by userId,day order by usedSeconds desc ) num from (";
sql += " select userId,userName,(userName+'-'+appName) appName,postName,convert(varchar(10),createDate,120) day,sum(usedSeconds) usedSeconds";
sql += " from vw_tb_mouse_user where createDate between '" + TimerArray[0] + " ' and dateadd(day,1,'" + TimerArray[1] + "') ";
if (!string.IsNullOrEmpty(userIdList))
{
sql += "and userId in(" + userIdList + ")";
}
sql += " group by userId,userName,appName,postName,convert(varchar(10),createDate,120) ) t1 ) t0 where num<=" + System.Configuration.ConfigurationManager.AppSettings["softComparmCount"];
DataTable dt = new DataTable();
try
{
dt = sh.dbClient().SqlQueryable <object>(sql).OrderBy("userId desc").ToDataTable();
}
catch (Exception ex)
{
}
return(dt);
}
public string updateListenCount(string count)
{
if (string.IsNullOrEmpty(count))
{
return("错误，输入的count为空");
}
ISqlSugarClient db = null;
try
{
sqlHelper sh = new sqlHelper();
db = sh.dbClient();
sys_listen listen = new sys_listen();
listen.listen_count = DES_En_De.DesEncrypt(count);
var countList = db.SqlQueryable <sys_listen>("select listen_count from sys_listen").ToList();
if (countList.Count > 0)
{
db.Updateable(listen).ExecuteCommand();
}
else
{
db.Insertable(listen).ExecuteCommand();
}
}
catch (Exception ex) {
return(ex.Message);
}
return("操作成功");
}
public DataTable getSelectItem(string tableName, int belongsId, int checkDataRole = 0)
{
sysLoginInCls si = public_method.getLoginInObject();
DataTable dt = new DataTable();
//这里执行
try
{
sqlHelper sh = new sqlHelper();
ISqlSugarClient db = sh.dbClient();
string whereSql = "";
string sql = "select * from " + tableName + " where flag=1 " + whereSql;
if (checkDataRole == 1)
{
#region
sql += " and belongsId = " + belongsId;
#endregion
}
dt = db.SqlQueryable <object>(sql).ToDataTable();
}
catch (Exception ex)
{
}
return(dt);
}
public DataTable getUserTree()
{
sqlHelper sh = new sqlHelper();
sysLoginInCls si = public_method.getLoginInObject();
string sql = "select * from vw_tree_all "
+ " where userId in (select userId from sys_dataRole_user where roleId = " + si.dataRoleId + ")"
+ " union all select* from vw_tree_all"
+ " where id in (select distinct parentId from vw_tree_all"
+ " where userId in (select userId from sys_dataRole_user where roleId = " + si.dataRoleId + "))"
+ " union all select* from vw_tree_all"
+ " where id in (select distinct parentId from vw_tree_all"
+ " where id in (select distinct parentId from vw_tree_all"
+ " where userId in (select userId from sys_dataRole_user where roleId = " + si.dataRoleId + ")))"
+ " union all select* from vw_tree_all"
+ " where id in (select distinct parentId from vw_tree_all"
+ " where id in (select distinct parentId from vw_tree_all"
+ " where id in (select distinct parentId from vw_tree_all"
+ " where userId in (select userId from sys_dataRole_user where roleId = " + si.dataRoleId + "))))";
DataTable dt = new DataTable();
try
{
dt = sh.dbClient().SqlQueryable <object>(sql).ToDataTable();
}
catch
{
}
return(dt);
}
public List <object> getMachineUserDate(JObject passJson, String mac)
{
try
{
sqlHelper sh = new sqlHelper();
ISqlSugarClient db = sh.dbClient();
string sql = "";
sql += " select u.*,t.teamName from tb_Machine_user u" +
" inner join public_team t on t.teamId = u.belongsId and t.flag = 1 " +
" where u.flag = 1 and cpuid = '" + mac + "'";
//这里把查询的语句记录到内存中
sysSearchSql sss = new sysSearchSql();
sss.loginInIp = public_method.GetIPAddress();
sss.gridkey = "getMachineUserDate";//这里记录一下
sss.sql = sql;
MvcApplication.setsysSearchSql(sss);
var list = db.SqlQueryable <object>(sql).ToList();
return(list);
}
catch (Exception ex)
{
return(new List <object>());
}
}
public returnR machineUserDel(JObject passObj)
{
returnR rr = new returnR();
string idList = passObj["idList"].ToString();
string sql = "delete tb_Machine_user where userid in(" + idList + ")";
sqlHelper sh = new sqlHelper();
int iLen = 0;
try
{
iLen = sh.dbClient().Ado.ExecuteCommand(sql);
if (iLen > 0)
{
rr.code = 100;
rr.msg = "删除成功！";
}
else
{
rr.code = 0;
rr.msg = "删除失败！";
}
}
catch (Exception ex) {
rr.code = -5;
rr.msg = ex.Message;
}
return(rr);
}
public object search()
{
sqlHelper sh = new sqlHelper();
var list = sh.dbClient().Queryable <sys_user>().ToList();//查询所有
return(list);
}
public DataTable get_mouse_keyboard_union(JObject passJson)
{
sqlHelper sh = new sqlHelper();
string timeQujian = passJson["timeQujian"].ToString();
string[] TimerArray = new string[2];
if (timeQujian != "")
{
TimerArray = timeQujian.Split('~');
}
string userIdList = passJson["userIdList"].ToString();
string sql = "select userId,userName,convert(varchar(10),createDate,120) day,sum(Count) count from vw_mouse_keyboard_union_user";
sql += " where createDate between '" + TimerArray[0] + "' and dateadd(day,1,'" + TimerArray[1] + "')";
if (!string.IsNullOrEmpty(userIdList))
{
sql += "and userId in(" + userIdList + ")";
}
sql += " group by userId,userName,convert(varchar(10),createDate,120) ";
DataTable dt = new DataTable();
try
{
dt = sh.dbClient().SqlQueryable <object>(sql).OrderBy("day,userId asc").ToDataTable();
}
catch (Exception ex)
{
}
return(dt);
}
public DataTable getSelectItem2(string tableName, string fieldValue)
{
sysLoginInCls si = public_method.getLoginInObject();
DataTable dt = new DataTable();
//这里执行
try
{
sqlHelper sh = new sqlHelper();
ISqlSugarClient db = sh.dbClient();
string sql = "select * from " + tableName + " where (flag=1";
if (!string.IsNullOrEmpty(fieldValue) && fieldValue != "null")
{
sql += " and projectId= " + si.projectId + " and carId is null ) or (flag=1 and projectId= " + si.projectId + " and guanYouId=" + fieldValue + ")";
}
else
{
sql += " and projectId= " + si.projectId + " and carId is null )";
}
dt = db.SqlQueryable <object>(sql).ToDataTable();
}
catch (Exception ex)
{
}
return(dt);
}
public DataTable get_common(string tableName, string userIdList, string groupByModel)
{
sqlHelper sh = new sqlHelper();
string sql = "";
string sqlUserList = "";
if (!string.IsNullOrEmpty(userIdList))
{
sqlUserList = " where userId in (" + userIdList + ") ";
}
if (groupByModel == "person")
{
sql = "select top 5 * from " + tableName;
sql += sqlUserList;
}
else if (groupByModel == "team")
{
sql = " select teamName as userName, sum(Count) as Count from " + tableName;
sql += sqlUserList;
sql += "group by teamName";
}
else if (groupByModel == "department")
{
sql = " select departmentName as userName, sum(Count) as Count from " + tableName;
sql += sqlUserList;
sql += "group by departmentName";
}
else if (groupByModel == "fgs")
{
sql = " select fgsName as userName, sum(Count) as Count from " + tableName;
sql += sqlUserList;
sql += "group by fgsName";
}
else if (groupByModel == "zgs")
{
sql = " select zgsName as userName, sum(Count) as Count from " + tableName;
sql += sqlUserList;
sql += "group by zgsName";
}
DataTable dt = new DataTable();
try
{
dt = sh.dbClient().SqlQueryable <object>(sql).ToDataTable();
}
catch
{
}
return(dt);
}
public DataTable get_user_soft_list(string tableName, string userIdList, string groupByModel)
{
sqlHelper sh = new sqlHelper();
string sql = "";
string sqlUserList = "";
if (!string.IsNullOrEmpty(userIdList))
{
sqlUserList = " where userId in (" + userIdList + ") ";
}
if (groupByModel == "person")
{
sql = "select top 10 * from " + tableName;
sql += sqlUserList;
}
else if (groupByModel == "team")
{
sql = " select top 10 teamName as userName,partDate,appName,left(windowTitle,50)as windowTitle from " + tableName;
sql += sqlUserList;
}
else if (groupByModel == "department")
{
sql = " select top 10 departmentName as userName,partDate,appName,left(windowTitle,50)as windowTitle from " + tableName;
sql += sqlUserList;
}
else if (groupByModel == "fgs")
{
sql = " select top 10 fgsName as userName,partDate,appName,left(windowTitle,50)as windowTitle from " + tableName;
sql += sqlUserList;
}
else if (groupByModel == "zgs")
{
sql = " select top 10 zgsName as userName,partDate,appName,left(windowTitle,50)as windowTitle from " + tableName;
sql += sqlUserList;
}
DataTable dt = new DataTable();
try
{
dt = sh.dbClient().SqlQueryable <object>(sql).ToDataTable();
}
catch
{
}
return(dt);
}
public DataTable get_user_hours_keyword(string userIdList, string groupByModel)
{
sqlHelper sh = new sqlHelper();
string sql = "";
string sqlUserList = "";
if (!string.IsNullOrEmpty(userIdList))
{
sqlUserList += " where userId in (" + userIdList + ") ";
}
if (groupByModel == "person")
{
sql = "select * from vw_当日_人员时刻繁忙度对比 ";
sql += sqlUserList;
}
else if (groupByModel == "team")
{
sql = "select teamName as userName,sum(Count)as Count,hour from vw_当日_人员时刻繁忙度对比 " + sqlUserList;
sql += " group by teamName,hour";
}
else if (groupByModel == "department")
{
sql = "select departmentName as userName,sum(Count)as Count,hour from vw_当日_人员时刻繁忙度对比 " + sqlUserList;
sql += " group by departmentName,hour";
}
else if (groupByModel == "fgs")
{
sql = "select fgsName as userName,sum(Count)as Count,hour from vw_当日_人员时刻繁忙度对比 " + sqlUserList;
sql += " group by fgsName,hour";
}
else if (groupByModel == "zgs")
{
sql = "select zgsName as userName,sum(Count)as Count,hour from vw_当日_人员时刻繁忙度对比 " + sqlUserList;
sql += " group by zgsName,hour";
}
DataTable dt = new DataTable();
try
{
dt = sh.dbClient().SqlQueryable <object>(sql).OrderBy("hour asc,userName asc").ToDataTable();
}
catch (Exception ex)
{
}
return(dt);
}
public DataTable commonSql(string tableName)
{
DataTable dt = new DataTable();
string sql = "select * from " + tableName;
try
{
sqlHelper sh = new sqlHelper();
dt = sh.dbClient().Ado.GetDataTable(sql);
}
catch {
}
return(dt);
}
public DataTable getDataRoleUserIdList()
{
sqlHelper sh = new sqlHelper();
sysLoginInCls si = public_method.getLoginInObject();
string sql = "select userId from sys_dataRole_user where roleId = " + si.dataRoleId;
DataTable dt = new DataTable();
try
{
dt = sh.dbClient().SqlQueryable <object>(sql).ToDataTable();
}
catch
{
}
return(dt);
}
public returnR machineUserModify(JObject passObj)
{
returnR rr = new returnR();
string userId = passObj["userId"].ToString();
string belongsId = passObj["belongsId"].ToString();
string cpuId = passObj["cpuId"].ToString();
string userName = passObj["userName"].ToString();
string sex = passObj["sex"].ToString();
string psw = passObj["psw"].ToString();
string machineName = passObj["machineName"].ToString();
string account = passObj["account"].ToString();
string remarks = "";
if (passObj["remarks"] != null)
{
remarks = passObj["remarks"].ToString();
}
psw = DES_En_De.UserMd5(psw);
string sql = "update tb_Machine_user set account='" + account
+ "',cpuId='" + cpuId + "',machineName='" + machineName + "',userName='******',sex='" + sex + "',belongsId='" + belongsId
+ "',remarks='" + remarks + "',updateDate=GETDATE() where userId='" + userId + "'";
sqlHelper sh = new sqlHelper();
int iLen = 0;
try
{
iLen = sh.dbClient().Ado.ExecuteCommand(sql);
if (iLen > 0)
{
rr.code = 100;
rr.msg = "修改成功！";
}
else
{
rr.code = 0;
rr.msg = "修改失败！";
}
}
catch (Exception ex)
{
rr.code = -5;
rr.msg = ex.Message;
}
return(rr);
}
public DataTable commonMethod(string tableName)
{
sqlHelper sh = new sqlHelper();
string sql = "select * from " + tableName;
DataTable dt = new DataTable();
try
{
dt = sh.dbClient().SqlQueryable <object>(sql).ToDataTable();
}
catch
{
}
return(dt);
}
public List <sys_data_role> get_sys_data_role()
{
List <sys_data_role> l_role = new List <sys_data_role>();
//这里执行
try
{
sqlHelper sh = new sqlHelper();
ISqlSugarClient db = sh.dbClient();
l_role = db.Queryable <sys_data_role>().Where(it => it.flag == 1).OrderBy("orderNo").ToList();
}
catch (Exception ex)
{
}
return(l_role);
}
public DataTable getSysLog(JObject passJson)
{
DataTable dt = new DataTable();
try
{
sqlHelper sh = new sqlHelper();
ISqlSugarClient db = sh.dbClient();
string sql = "";
sql = " select s.*,u.userName createUserName from sys_log s left join sys_user u on s.createUserId=u.userId where 1=1 ";
if (passJson != null)
{
JArray passSearchJarry = JArray.Parse(passJson["centerSearchArray"].ToString());
for (var i = 0; i < passSearchJarry.Count; i++)
{
string key = passSearchJarry[i]["fieldName"].ToString();
string op = passSearchJarry[i]["op"].ToString();
string value = passSearchJarry[i]["fieldValue"].ToString();;
if (!string.IsNullOrEmpty(key) &&
!string.IsNullOrEmpty(op) &&
!string.IsNullOrEmpty(value))
{
sql += " and " + key + public_method.get_opTimeReset_sql(op, value);
}
}
}
// sql += " order by s.createDate desc ";
//这里把查询的语句记录到内存中
sysSearchSql sss = new sysSearchSql();
sss.loginInIp = public_method.GetIPAddress();
sss.gridkey = "getSysLog";//这里记录一下
sss.sql = sql;
MvcApplication.setsysSearchSql(sss);
dt = db.SqlQueryable <object>(sql).OrderBy("createDate desc").ToDataTable();
return(dt);
}
catch (Exception ex)
{
return(dt);
}
}
private DataTable getMySubordinateList(string departmentId)
{
ISqlSugarClient db = null;
try
{
sqlHelper sh = new sqlHelper();
db = sh.dbClient();
var depId = new SugarParameter("@departmentId", departmentId);
//var manager = new SugarParameter("@managerFlag", managerFlag);
SugarParameter[] para = new SugarParameter[1];
para[0] = depId;
//para[1] = manager;
DataTable MySubordinateList = db.Ado.UseStoredProcedure().GetDataTable("sp_getMySubordinateList", para);
//string sql = "";
//sql += " ;WITH rec AS( " +
// " SELECT departmentId,departmentName,belongsId FROM public_department where departmentId = " + departmentId +
// " UNION ALL " +
// " SELECT a.departmentId,a.departmentName,a.belongsId FROM public_department as a ,rec AS b WHERE a.belongsId = b.departmentId " +
// " ) " +
// " select userId from tb_Machine_user " +
// " where belongsId in " +
// " ( " +
// " SELECT departmentId FROM rec " +
// " ) " ;
return(MySubordinateList);
}
catch (Exception ex)
{
//return new List<object>();
return(new DataTable());
}
finally
{
db.Close();
}
}
public List <object> getUserManger(JObject passJson)
{
try
{
sqlHelper sh = new sqlHelper();
ISqlSugarClient db = sh.dbClient();
string sql = "";
sql += " select * from vw_userManger where 1=1";
if (passJson != null)
{
JArray passSearchJarry = JArray.Parse(passJson["centerSearchArray"].ToString());
for (var i = 0; i < passSearchJarry.Count; i++)
{
string key = passSearchJarry[i]["fieldName"].ToString();
string op = passSearchJarry[i]["op"].ToString();
string value = passSearchJarry[i]["fieldValue"].ToString();;
if (!string.IsNullOrEmpty(key) &&
!string.IsNullOrEmpty(op) &&
!string.IsNullOrEmpty(value))
{
sql += " and " + key + public_method.get_opTimeReset_sql(op, value);
}
}
}
//这里把查询的语句记录到内存中
sysSearchSql sss = new sysSearchSql();
sss.loginInIp = public_method.GetIPAddress();
sss.gridkey = "getUserManger";//这里记录一下
sss.sql = sql;
MvcApplication.setsysSearchSql(sss);
var list = db.SqlQueryable <object>(sql).OrderBy("orderNum").ToList();
return(list);
}
catch (Exception ex)
{
return(new List <object>());
}
}
public DataTable get_ruanjianFenbu(string userIdList, string groupByModel)
{
sqlHelper sh = new sqlHelper();
string sql = "select * from vw_当日_软件类别时间分布图 where mins>10 ";
if (!string.IsNullOrEmpty(userIdList))
{
sql += " and userId in (" + userIdList + ")";
}
DataTable dt = new DataTable();
try
{
dt = sh.dbClient().SqlQueryable <object>(sql).OrderBy("mins desc").ToDataTable();
}
catch {
}
return(dt);
}
public returnR submitUserInfo(JObject passObj)
{
returnR rr = new returnR();
rr.code = 0;
string cpuId = passObj["cpuId"].ToString();
string userName = passObj["userName"].ToString();
string belongsId = passObj["belongsId"].ToString();
sqlHelper sh = new sqlHelper();
string sql = "select * from tb_Machine_user with(nolock) where flag=1 and cpuId='" + cpuId + "'";
try
{
SqlSugarClient db = sh.dbClient();
DataTable dt = db.Ado.GetDataTable(sql);
if (dt.Rows.Count > 0)
{
sql = "update tb_Machine_user set userName='******' ,belongsId='" + belongsId + "',updateDate=GETDATE() where flag=1 and cpuId='" + cpuId + "'";
}
else
{
sql = "insert into tb_Machine_user(cpuId,userName,belongsId,createDate) values('" + cpuId + "','" + userName + "','" + belongsId
+ "',getdate())";
}
int iReturn = db.Ado.ExecuteCommand(sql);
if (iReturn > 0)
{
rr.code = 100;
}
else
{
rr.code = 0;
}
}
catch {
}
return(rr);
}
public List <dtTree_List> getUserTree()
{
List <dtTree_List> l_dtTree = new List <dtTree_List>();
sqlHelper sh = new sqlHelper();
sysLoginInCls si = public_method.getLoginInObject();
string sql = "select * from vw_Bs_mySelectUserTree where 1=1";
DataTable dt_all = new DataTable();
try
{
dt_all = sh.dbClient().SqlQueryable <object>(sql).ToDataTable();
}
catch
{
}
DataTable dt = new DataTable();
if (si.loginIsAdmin == false)
{
if (si.managerFlag == "1")
{
dt = dt_all.Clone();
var myDepartId = si.dataRoleId.ToString();//这里获取一下这个先所有的信息
for (var i = 0; i < dt_all.Rows.Count; i++)
{
if (myDepartId == dt_all.Rows[i]["id"].ToString().ToLower() &&
dt_all.Rows[i]["type"].ToString() == "depart")
{
dt_all.Rows[i]["belongsId"] = "0";
dt.Rows.Add(dt_all.Rows[i].ItemArray);
break;
}
}
//递归找到所有属于这个部门的部门
for (var i = 0; i < dt_all.Rows.Count; i++)
{
if (dt_all.Rows[i]["type"].ToString() == "depart")
{
if (myDepartId == dt_all.Rows[i]["belongsId"].ToString())
{
dt.Rows.Add(dt_all.Rows[i].ItemArray);
getMyDepartSon(ref dt, dt_all.Rows[i]["id"].ToString(), dt_all);
}
}
}
//然后找到所有属于这个部门的人员
for (var i = 0; i < dt_all.Rows.Count; i++)
{
if (dt_all.Rows[i]["type"].ToString() == "user")
{
string belongsId = dt_all.Rows[i]["belongsId"].ToString();
for (var j = 0; j < dt.Rows.Count; j++)
{
if (belongsId == dt.Rows[j]["id"].ToString())
{
dt.Rows.Add(dt_all.Rows[i].ItemArray);
break;
}
}
}
}
dt.DefaultView.Sort = "type desc ";
dt = dt.DefaultView.ToTable();
}
else
{
dt = dt_all.Clone();
//只能看到他自己
var myUserId = "u" + si.loginUserId.ToString();
var myDepartId = si.dataRoleId.ToString();
for (var i = 0; i < dt_all.Rows.Count; i++)
{
if (myUserId.ToLower() == dt_all.Rows[i]["id"].ToString().ToLower() &&
dt_all.Rows[i]["type"].ToString() == "user")
{
dt.Rows.Add(dt_all.Rows[i].ItemArray);
break;
}
}
for (var i = 0; i < dt_all.Rows.Count; i++)
{
if (myDepartId == dt_all.Rows[i]["id"].ToString().ToLower() &&
dt_all.Rows[i]["type"].ToString() == "depart")
{
dt_all.Rows[i]["belongsId"] = "0";
dt.Rows.Add(dt_all.Rows[i].ItemArray);
break;
}
}
}
}
else
{
dt = dt_all.Clone();
//这是是管理员登录的
string data_role_id = si.dataRoleId.ToString();//这里获取的 登录人员的 数据权限id
sql = "select teamId from sys_dataRole_user where roleId='" + data_role_id + "'and flag=1";
DataTable dt_department = new DataTable();
try
{
dt_department = sh.dbClient().Ado.GetDataTable(sql);//这是我所有的部门权限
}
catch {
}
if (dt_department != null && dt_department.Rows.Count > 0)
{
for (var i = 0; i < dt_department.Rows.Count; i++)
{
string departId = dt_department.Rows[i]["teamId"].ToString();
getMyDepartFather(ref dt, departId, dt_all);
}
}
//dt = dt_all;
//然后找到所有属于这个部门的人员
for (var i = 0; i < dt_all.Rows.Count; i++)
{
if (dt_all.Rows[i]["type"].ToString() == "user")
{
string belongsId = dt_all.Rows[i]["belongsId"].ToString();
for (var j = 0; j < dt_department.Rows.Count; j++)
{
if (belongsId == dt_department.Rows[j]["teamId"].ToString())
{
dt.Rows.Add(dt_all.Rows[i].ItemArray);
break;
}
}
}
}
dt.DefaultView.Sort = "type desc ";
dt = dt.DefaultView.ToTable();
}
if (dt != null && dt.Rows.Count > 0)
{
for (var i = 0; i < dt.Rows.Count; i++)
{
string id = dt.Rows[i]["id"].ToString();
dtTree_List dtTree = new dtTree_List();
dtTree.id = id;
dtTree.type = dt.Rows[i]["type"].ToString();
dtTree.mylevel = dt.Rows[i]["mylevel"].ToString();
if (id.Length > 1)
{
if (id.Substring(0, 1).ToLower() == "u")
{
dtTree.iconClass = "dtree-icon-yonghu";
}
else
{
dtTree.iconClass = "dtree-icon-fuxuankuang-banxuan";
}
}
dtTree.title = dt.Rows[i]["name"].ToString();
dtTree.checkArr = "0";
dtTree.parentId = dt.Rows[i]["belongsId"].ToString();
l_dtTree.Add(dtTree);
}
}
return(l_dtTree);
}
/// <summary>
/// 得到execl流
/// </summary>
/// <returns></returns>
private returnR ExcelStreamObject(JObject jObject)
{
returnR r = new returnR();
r.code = (int)sysEnum.还没有执行;
try
{
string fileName = "导出文件";
HSSFWorkbook hssfworkbook = new HSSFWorkbook();
ISheet sheet1 = hssfworkbook.CreateSheet(fileName);
IRow rowHeader = sheet1.CreateRow(0);//生成标题
JArray jColumnArray = JArray.Parse(jObject["columnArray"].ToString());
for (var i = 0; i < jColumnArray.Count; i++)
{
//写标题
string fileTitle = jColumnArray[i]["title"].ToString();
rowHeader.CreateCell(i).SetCellValue(fileTitle);
}
//开始写内容
string keyName = jObject["keyName"].ToString();
string ip = public_method.GetIPAddress();
string sql = "";//从内存中过去对应的信息
for (var i = 0; i < MvcApplication.l_sysSearchSql.Count; i++)
{
if (MvcApplication.l_sysSearchSql[i].loginInIp == ip &&
MvcApplication.l_sysSearchSql[i].gridkey == keyName)
{
sql = MvcApplication.l_sysSearchSql[i].sql;
break;
}
}
if (string.IsNullOrEmpty(sql))
{
r.code = (int)sysEnum.参数必填;
r.msg = "sql没有获取到";
return(r);
}
sqlHelper sh = new sqlHelper();
ISqlSugarClient db = sh.dbClient();
DataTable dt = db.SqlQueryable <object>(sql).ToDataTable();
if (dt == null)
{
r.code = (int)sysEnum.执行数据库失败;
r.msg = "dt数据库失败";
return(r);
}
#region 写入内容
for (int i = 0; i < dt.Rows.Count; i++)
{
IRow row = sheet1.CreateRow(i + 1);
for (int j = 0; j < jColumnArray.Count; j++)
{
string columnName = jColumnArray[j]["field"].ToString().Trim();//获取列名
if (checkColumnIndt(columnName, dt))
{
string str = dt.Rows[i][columnName].ToString();//获取到值
if (string.IsNullOrEmpty(str))
{
str = " ";
}
row.CreateCell(j).SetCellValue(str);//把值写进去
}
}
}
#endregion
#region 写入内存中
MemoryStream file = new MemoryStream();
hssfworkbook.Write(file);
file.Seek(0, SeekOrigin.Begin);
r.code = (int)sysEnum.操作成功;
r.data = file;
//return file;
#endregion
}
catch (Exception ex)
{
r.code = (int)sysEnum.发生异常;
r.msg = ex.Message;
}
return(r);
}
public returnR login(JObject passJson)
{
returnR r = new returnR();
r.code = (int)sysEnum.还没有执行;
try {
string userName = passJson["userName"].ToString();
if (string.IsNullOrEmpty(userName))
{
r.code = (int)sysEnum.参数必填;
return(r);
}
string psw = passJson["psw"].ToString();
if (string.IsNullOrEmpty(psw))
{
r.code = (int)sysEnum.参数必填;
return(r);
}
psw = DES_En_De.UserMd5(psw);
string isAdmin = passJson["isAdmin"].ToString();
if (string.IsNullOrEmpty(isAdmin))
{
isAdmin = "false";
}
sqlHelper sh = new sqlHelper();
if (isAdmin.ToLower() == "true")
{
#region
DataTable loginTable = sh.dbClient().Queryable <sys_user>()
.Where(it => it.userName == userName && it.pwd == psw).ToDataTable();
if (loginTable == null || loginTable.Rows.Count <= 0)
{
r.code = (int)sysEnum.数据不存在;
r.msg = "账号或密码错误";
return(r);
}
//这里是成成功的
string ip = public_method.GetIPAddress();
sysLoginInCls si = new sysLoginInCls();
si.loginInIp = ip;
si.loginUserId = int.Parse(loginTable.Rows[0]["userId"].ToString());
si.userName = loginTable.Rows[0]["userName"].ToString();
si.roleId = loginTable.Rows[0]["roleId"].ToString();
si.dataRoleId = loginTable.Rows[0]["dataRoleId"].ToString();
si.loginIsAdmin = true;
si.mySubordinateList = "";
MvcApplication.setLoginInCls(si);
r.code = (int)sysEnum.操作成功;
r.data = si;
r.msg = "登录成功！";
#endregion
}
else
{
DataTable loginTable = sh.dbClient().Queryable <tb_Machine_user>()
.Where(it => it.account == userName && it.psw == psw).ToDataTable();
if (loginTable == null || loginTable.Rows.Count <= 0)
{
r.code = (int)sysEnum.数据不存在;
r.msg = "账号或密码错误";
return(r);
}
//这里是成成功的
string ip = public_method.GetIPAddress();
sysLoginInCls si = new sysLoginInCls();
si.loginInIp = ip;
si.loginIsAdmin = false;
si.loginUserId = int.Parse(loginTable.Rows[0]["userId"].ToString());
si.userName = loginTable.Rows[0]["userName"].ToString();
si.roleId = "15";
si.dataRoleId = loginTable.Rows[0]["belongsId"].ToString();;
si.managerFlag = loginTable.Rows[0]["managerFlag"].ToString();
si.mySubordinateList = "";
MvcApplication.setLoginInCls(si);
r.code = (int)sysEnum.操作成功;
r.data = si;
r.msg = "登录成功！";
}
}
catch (Exception ex)
{
r.code = (int)sysEnum.发生异常;
r.msg = "发生异常" + ex.Message;
return(r);
}
return(r);
}
public returnR resetPsw(JObject userObj)
{
returnR r = new returnR();
r.code = (int)sysEnum.还没有执行;
if (userObj == null)
{
r.code = (int)sysEnum.参数必填;
return(r);
}
//这里执行
try
{
#region 判断用户
string userId = userObj["userId"].ToString();
if (string.IsNullOrEmpty(userId))
{
r.code = (int)sysEnum.参数必填;
r.msg = "userId必填";
return(r);
}
string pwd = userObj["psw"].ToString();
if (string.IsNullOrEmpty(pwd))
{
r.code = (int)sysEnum.参数必填;
r.msg = "用户密码必填";
return(r);
}
sqlHelper sh = new sqlHelper();
ISqlSugarClient db = sh.dbClient();
#endregion
sysLoginInCls si = public_method.getLoginInObject();
pwd = DES_En_De.UserMd5(pwd);//这里进行加密
int iReturn = db.Updateable <sys_user>()
.SetColumns(it => new sys_user()
{
pwd = pwd
,
updateUserId = si.loginUserId
,
updateDate = DateTime.Now
})
.Where(it => it.userId.ToString() == userId).ExecuteCommand();
if (iReturn > 0)
{
r.code = (int)sysEnum.操作成功;
r.msg = "重置密码成功";
#region //写日志
sys_log sys_log = new sys_log();
sys_log.logType = "重置";
sys_log.logText = "重置用户id[" + userId + "]密码成功";
sys_log.createUserId = si.loginUserId;
public_method.saveLog(sys_log);
#endregion
return(r);
}
else
{
r.code = (int)sysEnum.执行数据库失败;
r.msg = "重置密码失败";
return(r);
}
}
catch (Exception ex)
{
r.code = (int)sysEnum.发生异常;
r.msg = ex.Message;
}
return(r);
}
public returnR machineUserAdd(JObject passObj)
{
returnR rr = new returnR();
string belongsId = passObj["belongsId"].ToString();
string cpuId = passObj["cpuId"].ToString();
string userName = passObj["userName"].ToString();
string sex = passObj["sex"].ToString();
string psw = passObj["psw"].ToString();
psw = DES_En_De.UserMd5(psw);
string machineName = passObj["machineName"].ToString();
string account = passObj["account"].ToString();
string remarks = "";
if (passObj["remarks"] != null)
{
remarks = passObj["remarks"].ToString();
}
sqlHelper sh = new sqlHelper();
string sql = "select * from tb_Machine_user where flag=1 and cpuId='" + cpuId + "'";
DataTable dt = new DataTable();
dt = sh.dbClient().Ado.GetDataTable(sql);
if (dt != null && dt.Rows.Count > 0)
{
rr.code = 10;
rr.msg = "该系统key【" + cpuId + "】已存在！";
}
else
{
sql = "insert into tb_Machine_user(account,psw,cpuId,userName,sex,machineName,belongsId,remarks,managerFlag,flag,createDate)";
sql += " values('" + account + "','" + psw + "','" + cpuId + "','" + userName
+ "','" + sex + "','" + machineName + "','" + belongsId + "','" + remarks + "',0,1,getdate())";
int iLen = 0;
try
{
iLen = sh.dbClient().Ado.ExecuteCommand(sql);
if (iLen > 0)
{
rr.code = 100;
rr.msg = "添加成功！";
}
else
{
rr.code = 0;
rr.msg = "添加失败！";
}
}
catch (Exception ex)
{
rr.code = -5;
rr.msg = ex.Message;
}
}
return(rr);
}
private excelInReturnInfo importExcels()
{
string pl_no = public_method.getRadNum("dr");
int isheet = 0;
string msg = "";
string sql = "";
List <string> l_distinct_hrz = new List <string>();
string loginPersonId = public_method.getLoginInObject().personId.ToString(); //获取获取到当前登录人的personId
excelInReturnInfo ri = new excelInReturnInfo();
DataTable dt_public_moduelDt = new DataTable();
sqlHelper sh = new sqlHelper();
SqlSugarClient db = sh.dbClient();
int iAllCount = 0;
int iExportSuccessCount = 0;//成功导入的条数
string defaultPsw = System.Configuration.ConfigurationManager.AppSettings["defaultPsw"];
if (string.IsNullOrEmpty(defaultPsw))
{
defaultPsw = "123456";
}
defaultPsw = DES_En_De.UserMd5(defaultPsw);
int count = 0; //授权的cout
int departmentCount = 0; //当前公司的人员数量
string tempSql = "select (select listen_count from sys_listen) as listen_count,count(1) as deoartment_count from public_department";
DataTable table = db.SqlQueryable <object>(tempSql).ToDataTable();
count = int.Parse(DES_En_De.DesDecrypt(table.Rows[0]["listen_count"].ToString()));
departmentCount = int.Parse(table.Rows[0]["deoartment_count"].ToString());
foreach (ISheet sheet in hssfworkbook)
{
if (isheet > 0)
{
msg = "请上传正确模板，必须是7列";
break;
}
isheet++;
System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
while (rows.MoveNext())
{
IRow row = (HSSFRow)rows.Current;
if (row.RowNum == 0)//行数大于1行
{
if (row.Cells.Count != 7)
{
msg = "请上传正确模板，必须是7列!";
break;
}
}
else
{
//这里做循环 //第一列和第二列都为空的时候不起作用 row.GetCell(0).ToString().Trim()
//这里进行导入
try
{
importExcelTempPojo tempPojo = new importExcelTempPojo(row);
if (string.IsNullOrEmpty(tempPojo.department))
{
msg = "部门名称必填！"; continue;
}
if (string.IsNullOrEmpty(tempPojo.leadingCadre))
{
msg = "负责状态必填！"; continue;
}
if ((!"1".Equals(tempPojo.leadingCadre)) && (!"0".Equals(tempPojo.leadingCadre)))
{
msg = "负责状态不合法：请检查：1是负责人，0不是负责人！"; continue;
}
if (string.IsNullOrEmpty(tempPojo.loginAccount))
{
msg = "登录帐号必填！"; continue;
}
if (string.IsNullOrEmpty(tempPojo.identifying))
{
msg = "机器标识必填！"; continue;
}
if ((!"1".Equals(tempPojo.sex)) && (!"0".Equals(tempPojo.sex)))
{
msg = "性别不合法：请检查：1是男，0是女！"; continue;
}
string departId = ""; //当前的部门id
string parentId = "0"; //父级的部门id默认为0 如果为空的情况下
sql = "select departmentId,isnull((select departmentId from public_department where departmentName='" +
tempPojo.superiorDepartment + "' and flag =1),'0') as parentId from public_department where departmentName='" + tempPojo.department + "' and flag=1";
DataTable dt = db.Ado.GetDataTable(sql);
if (dt != null && dt.Rows.Count > 0)
{
departId = dt.Rows[0]["departmentId"].ToString();
parentId = dt.Rows[0]["parentId"].ToString();
}
//如果部门id不存在与对应的部门当中
public_department pdtt = new public_department();
pdtt.belongsId = int.Parse(parentId);
pdtt.departmentName = tempPojo.department;
pdtt.createDate = DateTime.Now;
pdtt.updateDate = DateTime.Now;
pdtt.updateUserId = int.Parse(loginPersonId);
pdtt.flag = 1;
pdtt.createUserId = int.Parse(loginPersonId);
if (string.IsNullOrEmpty(departId))
{
departId = db.Insertable(pdtt).IgnoreColumns(it => new { it.updateUserId, it.updateDate }).ExecuteReturnIdentity().ToString(); //执行插入并且返回对应的当前部门id
}
else
{
pdtt.belongsId = int.Parse(parentId);
db.Updateable(pdtt).IgnoreColumns(it => new { it.createDate, it.createUserId, it.remarks }).Where(it => it.departmentId == int.Parse(departId)).ExecuteCommand();
}
string findLeadingCadreSql = " select count(1) rownumber from tb_machine_user where account = '" + tempPojo.loginAccount + "'";
DataTable machineUserList = db.SqlQueryable <object>(findLeadingCadreSql).ToDataTable(); //如果当前人员存在的情况下
tb_Machine_user tempMachineUser = new tb_Machine_user();
tempMachineUser.updateDate = DateTime.Now;
tempMachineUser.updateUserId = int.Parse(loginPersonId);
tempMachineUser.createUserId = int.Parse(loginPersonId);
tempMachineUser.createDate = DateTime.Now;
tempMachineUser.userName = tempPojo.name;
tempMachineUser.flag = 1;
tempMachineUser.managerFlag = int.Parse(tempPojo.leadingCadre);
tempMachineUser.belongsId = int.Parse(departId);
tempMachineUser.cpuId = tempPojo.identifying;
tempMachineUser.psw = defaultPsw;
tempMachineUser.sex = int.Parse(tempPojo.sex);
tempMachineUser.account = tempPojo.loginAccount;
if (int.Parse(machineUserList.Rows[0]["rownumber"].ToString()) > 0)
{
db.Updateable(tempMachineUser).UpdateColumns(it => new { it.updateUserId, it.updateDate, it.userName, it.managerFlag, it.cpuId, it.belongsId }).Where(it => tempPojo.loginAccount.Equals(it.account)).ExecuteCommand();
iExportSuccessCount++;
}
else
{
db.Insertable(tempMachineUser).ExecuteCommand();
departmentCount++;
iExportSuccessCount++;
}
}
catch (Exception ex)
{
msg = "发生异常:" + ex.Message;
}
iAllCount++;
}
}
}
/**
* 将所有的部门登记全部进行筛选然后将部门等级全部更改掉
* */
var departmentLeveDataTable = db.Ado.UseStoredProcedure().GetDataTable("eve_department_leve");
List <public_department> departmentLeveList = new List <public_department>();
for (int i = 0; i < departmentLeveDataTable.Rows.Count; i++)
{
public_department pd = new public_department();
pd.departmentId = int.Parse(departmentLeveDataTable.Rows[i]["departmentId"].ToString());
pd.level = int.Parse(departmentLeveDataTable.Rows[i]["departmentLevel"].ToString());
departmentLeveList.Add(pd);
}
db.Updateable(departmentLeveList).UpdateColumns(it => new { it.level }).ExecuteCommand();
if (iAllCount > 0)
{
if (iExportSuccessCount > 0)
{
ri.code = 100;
ri.allCount = iAllCount;
ri.msg = "共有[" + iAllCount + "]条数据，共影响[" + iExportSuccessCount + "]条数据！";
}
else
{
ri.code = 0;
ri.allCount = iAllCount;
ri.msg = "共有[" + iAllCount + "]条,没有影响到数据！";
}
}
else
{
ri.code = -10;
ri.allCount = iAllCount;
ri.msg = "没有数据可以导入！";
}
return(ri);
}
private excelInReturnInfo ImportToDataBase()
{
string pl_no = public_method.getRadNum("dr");
int isheet = 0;
string msg = "";
string sql = "";
List <string> l_distinct_hrz = new List <string>();
excelInReturnInfo ri = new excelInReturnInfo();
DataTable dt_public_moduelDt = new DataTable();
sqlHelper sh = new sqlHelper();
SqlSugarClient db = sh.dbClient();
int iAllCount = 0;
int iExportSuccessCount = 0;//成功导入的条数
string defaultPsw = System.Configuration.ConfigurationManager.AppSettings["defaultPsw"];
if (string.IsNullOrEmpty(defaultPsw))
{
defaultPsw = "123456";
}
defaultPsw = DES_En_De.UserMd5(defaultPsw);
foreach (ISheet sheet in hssfworkbook)
{
if (isheet > 0)
{
msg = "请上传正确模板，必须是12列";
break;
}
isheet++;
System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
while (rows.MoveNext())
{
IRow row = (HSSFRow)rows.Current;
if (row.RowNum == 0)//行数大于1行
{
if (row.Cells.Count != 7)
{
msg = "请上传正确模板，必须是7列!";
break;
}
}
else
{
//这里做循环 //第一列和第二列都为空的时候不起作用 row.GetCell(0).ToString().Trim()
//这里进行导入
iAllCount++;
try
{
string level1 = row.GetCell(0).ToString().Trim();
string level2 = row.GetCell(1).ToString().Trim();
string level3 = row.GetCell(2).ToString().Trim();
string departName = "";
if (!string.IsNullOrEmpty(level1))
{
departName = level1;
}
if (!string.IsNullOrEmpty(level2))
{
departName = level2;
}
if (!string.IsNullOrEmpty(level3))
{
departName = level3;
}
string departId = "";
//通过这个查询de
sql = "select departmentId from public_department where departmentName='" + departName + "' and flag=1";
DataTable dt = db.Ado.GetDataTable(sql);
if (dt != null && dt.Rows.Count > 0)
{
departId = dt.Rows[0]["departmentId"].ToString();
}
if (string.IsNullOrEmpty(departId))
{
msg = "所属部门必填!"; continue;
}
string managerFlag = row.GetCell(3).ToString().Trim();
if (string.IsNullOrEmpty(managerFlag))
{
msg = "负责人必填!"; continue;
}
if (managerFlag == "是")
{
managerFlag = "1";
}
else
{
managerFlag = "0";
}
string userName = row.GetCell(4).ToString().Trim();
if (string.IsNullOrEmpty(userName))
{
msg = "姓名必填!"; continue;
}
string account = row.GetCell(5).ToString().Trim();
if (string.IsNullOrEmpty(account))
{
msg = "登录账号必填!"; continue;
}
string biaoshi = row.GetCell(6).ToString().Trim();
if (string.IsNullOrEmpty(biaoshi))
{
msg = "机器标识必填!"; continue;
}
//这里导入到数据库中
sql = " if(select COUNT(*) from tb_Machine_user where flag=1 and account='" + account + "')>0 begin";
sql += " update tb_Machine_user set userName='******',cpuId='" + biaoshi + "',managerFlag='" + managerFlag + "',belongsId='" + departId + "',updateDate=GETDATE() where account='" + account + "' end";
sql += " else begin";
sql += " insert into tb_Machine_user(account,psw,cpuId,userName,managerFlag,belongsId,flag,createDate)values('" + account
+ "','" + defaultPsw + "','" + biaoshi + "','" + userName + "','" + managerFlag + "','" + departId + "',1,GETDATE()) end";
int iReturn = db.Ado.ExecuteCommand(sql);
if (iReturn > 0)
{
iExportSuccessCount++;
}
}
catch (Exception ex)
{
msg = "发生异常:" + ex.Message;
}
}
}
}
if (iAllCount > 0)
{
if (iExportSuccessCount > 0)
{
ri.code = 100;
ri.allCount = iAllCount;
ri.msg = "共有[" + iAllCount + "]条数据，共影响[" + iExportSuccessCount + "]条数据！";
}
else
{
ri.code = 0;
ri.allCount = iAllCount;
ri.msg = "共有[" + iAllCount + "]条,没有影响到数据！";
}
}
else
{
ri.code = -10;
ri.allCount = iAllCount;
ri.msg = "没有数据可以导入！";
}
return(ri);
}