using Dapper;
|
using Newtonsoft.Json;
|
using Newtonsoft.Json.Linq;
|
using System;
|
using System.Collections.Generic;
|
using System.Data;
|
using System.Data.SqlClient;
|
using System.Linq;
|
using System.Threading.Tasks;
|
using VueWebCoreApi.DLL.BLL;
|
using VueWebCoreApi.Models;
|
using VueWebCoreApi.Models.RolePermission;
|
using VueWebCoreApi.Tools;
|
|
namespace VueWebCoreApi.DLL.DAL
|
{
|
public class LoginDAL
|
{
|
public static DataTable dt; //定义全局变量dt
|
public static bool res; //定义全局变量dt
|
public static ToMessage mes = new ToMessage(); //定义全局返回信息对象
|
public static SqlParameter[] parameters; //定义全局SqlParameter参数数组
|
public static string fileip = AppSetting.GetAppSetting("FileIP");
|
//获取企业配置Enterprise
|
private static readonly string Enterprise = AppSetting.GetAppSetting("Enterprise");
|
|
#region [登录查询]
|
public static ToMessage LoginSearch(string usercode, string password)
|
{
|
string strProcName = ""; //定义局部sql变量
|
List<SqlParameter> listStr = new List<SqlParameter>(); //定义参数集合
|
try
|
{
|
//查询账号是否存在
|
strProcName = @"select U.id,U.usercode,U.username,U.password,U.storg_code,T.torg_name as storg_name,U.role_code,U.role_datapermissions,U.post_code,U.is_system_admin,U.status
|
from TUser U
|
left join TOrganization T on U.storg_code=T.torg_code
|
where usercode =@usercode and password=@password";
|
//创建参数
|
listStr.Add(new SqlParameter("@usercode", usercode));
|
listStr.Add(new SqlParameter("@password", password));
|
parameters = listStr.ToArray();
|
listStr.Clear();
|
DataTable dt_ = DBHelper.GetTable(strProcName, parameters);
|
if (dt_.Rows.Count <= 0)
|
{
|
mes.code = "300";
|
mes.Message = "账号密码不正确!";
|
mes.data = null;
|
return mes;
|
}
|
if (dt_.Rows[0]["status"].ToString() == "N")
|
{
|
mes.code = "300";
|
mes.Message = "当前账号已停用!";
|
mes.data = null;
|
return mes;
|
}
|
//判断账号是否系统管理员账号
|
if (dt_.Rows[0]["is_system_admin"].ToString() == "Y")
|
{
|
mes.code = "200";
|
mes.data = dt_;
|
return mes;
|
}
|
//执行sql查询用户是否指定组织
|
if (dt_.Rows[0]["storg_code"].ToString() == "" || dt_.Rows[0]["storg_code"].ToString() == null)
|
{
|
mes.code = "300";
|
mes.Message = "当前用户未指定组织!";
|
mes.data = null;
|
return mes;
|
}
|
//执行sql查询用户是否指定岗位
|
if (dt_.Rows[0]["post_code"].ToString() == "" || dt_.Rows[0]["post_code"].ToString() == null)
|
{
|
mes.code = "300";
|
mes.Message = "当前用户未指定岗位!";
|
mes.data = null;
|
return mes;
|
}
|
//执行sql查询用户是否指定角色
|
if (dt_.Rows[0]["role_code"].ToString() == "" || dt_.Rows[0]["role_code"].ToString() == null)
|
{
|
mes.code = "300";
|
mes.Message = "当前用户未指定角色!";
|
mes.data = null;
|
return mes;
|
}
|
mes.code = "200";
|
mes.data = dt_;
|
return mes;
|
}
|
catch (Exception e)
|
{
|
mes.code = "300";
|
mes.Message = e.Message;
|
mes.data = null;
|
return mes;
|
}
|
|
}
|
#endregion
|
|
#region [写入登录记录表]
|
public static ToMessage LoginBas(string usercode, string oper_type, string description, string type)
|
{
|
var sql = "";
|
var cont = 0;
|
var dynamicParams = new DynamicParameters();
|
try
|
{
|
sql = @"insert into T_Bas_User_Operlog(usercode,oper_date,oper_type,description,origin)
|
values(@usercode,@oper_date,@oper_type,@description,@origin)";
|
dynamicParams.Add("@usercode", usercode);
|
dynamicParams.Add("@oper_date", DateTime.Now.ToString());
|
dynamicParams.Add("@oper_type", oper_type);
|
dynamicParams.Add("@description", description);
|
dynamicParams.Add("@origin", type);
|
cont = DapperHelper.SQL(sql, dynamicParams);
|
if (cont > 0)
|
{
|
mes.code = "200";
|
mes.count = 0;
|
mes.Message = "记录成功!";
|
mes.data = null;
|
}
|
else
|
{
|
mes.code = "300";
|
mes.count = 0;
|
mes.Message = "记录失败!";
|
mes.data = null;
|
}
|
}
|
catch (Exception e)
|
{
|
mes.code = "300";
|
mes.count = 0;
|
mes.Message = e.Message;
|
mes.data = null;
|
}
|
return mes;
|
}
|
#endregion
|
|
#region[查询菜单功能]
|
public static ToMessage LoginMenu(string usercode, string usertype, User us)
|
{
|
string sql = ""; string strProcName = ""; string result = ""; //定义局部sql变量
|
var dynamicParams = new DynamicParameters();
|
List<SqlParameter> listStr = new List<SqlParameter>(); //定义参数集合
|
try
|
{
|
listStr.Clear();
|
//查询所有的组织数据
|
sql = @"select * from TOrganization where status='Y' and parent_id<>'0'";
|
var dattorg = DapperHelper.selecttable(sql);
|
if (us.is_system_admin == "Y") //管理员账号
|
{
|
//获取权限关联表数据(管理员账号)
|
sql = @"select jsondate from TRole TR
|
inner join TAuthority TA on TR.rolecode=TA.rolecode
|
where TR.identifying='0' and TA.type=@usertype";
|
dynamicParams.Add("@usertype", usertype);
|
var data = DapperHelper.selectdata(sql, dynamicParams);
|
if (data.Rows.Count > 0)
|
{
|
List<MenuItem> items = JsonConvert.DeserializeObject<List<MenuItem>>(data.Rows[0]["jsondate"].ToString());
|
// 获取所有的menucode
|
List<string> menuCodes = items.Select(item => item.MenuCode).ToList();
|
string codeString = string.Join(",", menuCodes);
|
//查询菜单数据
|
sql = @"select menucode,menuname,menu_seq,imgurl,'0' as flag,parent_id,is_show
|
from TMenu where Type=@usertype order by menu_seq";
|
dynamicParams.Add("@usertype", usertype);
|
var data0 = DapperHelper.selectdata(sql, dynamicParams);
|
if (data0.Rows.Count > 0)
|
{
|
string[] codes = codeString.Split(',');
|
// 使用lambda表达式查询对应父级编码的父级菜单数据添加到List
|
List<TreeOne> parentMenus = data0.AsEnumerable()
|
.Where(row => codes.Contains(row.Field<string>("menucode")))
|
.Select(row => row.Field<string>("parent_id"))
|
.Distinct()
|
.Select(parentMenuCode => data0.AsEnumerable()
|
.FirstOrDefault(row => row.Field<string>("menucode") == parentMenuCode))
|
.Where(row => row != null).Select(row => new TreeOne
|
{
|
code = row.Field<string>("menucode"),
|
name = row.Field<string>("menuname"),
|
flag = "0",
|
imgurl = fileip + row.Field<string>("imgurl"),
|
is_show= row.Field<string>("is_show")
|
}).ToList(); //.CopyToDataTable();
|
//添加子菜单数据
|
for (int i = 0; i < parentMenus.Count; i++)
|
{
|
parentMenus[i].children = data0.AsEnumerable().Where(row => codes.Contains(row.Field<string>("menucode")) && row.Field<string>("parent_id") == parentMenus[i].code)
|
.Select(row => new TreeTwo
|
{
|
code = row.Field<string>("menucode"),
|
name = row.Field<string>("menuname"),
|
flag = "0",
|
imgurl = fileip + row.Field<string>("imgurl"),
|
buttoncode = items.Where(m => m.MenuCode == row.Field<string>("menucode")).Select(m => m.ButtonCode).FirstOrDefault(),
|
datacode = items.Where(m => m.MenuCode == row.Field<string>("menucode")).Select(m => m.DataCode).FirstOrDefault(),
|
is_show = row.Field<string>("is_show")
|
}).ToList();
|
|
}
|
mes.code = "200";
|
mes.Message = "查询成功!";
|
mes.data = parentMenus;
|
return mes;
|
}
|
else
|
{
|
mes.code = "300";
|
mes.Message = "系统未设置菜单资料,请联系管理员!";
|
mes.data = null;
|
return mes;
|
}
|
}
|
else
|
{
|
mes.code = "300";
|
mes.Message = "当前账号对应角色未设置功能权限!";
|
mes.data = null;
|
return mes;
|
}
|
}
|
else
|
{
|
string[] rolecode = Array.ConvertAll<string, string>(us.role_code.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries), s => s.ToString()); //string分割转string[]
|
//获取当前用户所属岗位对应的角色关联表数据
|
sql = @"select TD.jsondate from TRole R
|
inner join TAuthority TD on R.rolecode=TD.rolecode
|
where R.rolecode in @rolecode
|
and TD.type=@usertype";
|
dynamicParams.Add("@rolecode", rolecode);
|
dynamicParams.Add("@usertype", usertype);
|
var data = DapperHelper.selectdata(sql, dynamicParams);
|
if (data.Rows.Count > 0)
|
{
|
List<MenuItem> item = new List<MenuItem>();
|
for (int i = 0; i < data.Rows.Count; i++)
|
{
|
var aa = JsonConvert.DeserializeObject<List<MenuItem>>(data.Rows[i]["jsondate"].ToString());
|
for (int j = 0; j < aa.Count; j++)
|
{
|
item.Add(aa[j]);
|
}
|
}
|
// 取并集
|
var items = item.GroupBy(m => m.MenuCode).Select(g => new MenuItem
|
{
|
MenuCode = g.Key,
|
ButtonCode = string.Join(",", string.Join(",", g.Select(m => m.ButtonCode)).Split(',').Distinct()),
|
DataCode = string.Join(",", string.Join(",", g.Select(m => m.DataCode=="LEVEL"?us.storg_code:m.DataCode == "PERSON"?us.usercode:m.DataCode)).Split(',')
|
.Distinct().Contains("ALL") ? "ALL": string.Join(",", string.Join(",", g.Select(m => m.DataCode == "LEVEL" ? us.storg_code : m.DataCode == "PERSON" ? us.usercode : m.DataCode)).Split(',').Distinct()))
|
//DataCode = string.Join(",", string.Join(",", g.Select(m => m.DataCode)).Split(',').Distinct().Contains("ALL") ? "ALL" : (string.Join(",", string.Join(",", g.Select(m => m.DataCode)).Split(',').Distinct().Contains("DEPT") ? "DEPT" : (string.Join(",", string.Join(",", g.Select(m => m.DataCode)).Split(',').Distinct().Contains("PERSON") ? "PERSON" : "")))))
|
}).ToList();
|
|
// 获取所有的menucode
|
List<string> menuCodes = items.Select(item => item.MenuCode).ToList();
|
string codeString = string.Join(",", menuCodes);
|
//查询菜单数据
|
sql = @"select menucode,menuname,menu_seq,imgurl,'0' as flag,parent_id,is_show
|
from TMenu where Type=@usertype order by menu_seq";
|
dynamicParams.Add("@usertype", usertype);
|
var data0 = DapperHelper.selectdata(sql, dynamicParams);
|
if (data0.Rows.Count > 0)
|
{
|
string[] codes = codeString.Split(',');
|
// 使用lambda表达式查询对应父级编码的父级菜单数据添加到List
|
List<TreeOne> parentMenus = data0.AsEnumerable()
|
.Where(row => codes.Contains(row.Field<string>("menucode")))
|
.Select(row => row.Field<string>("parent_id"))
|
.Distinct()
|
.Select(parentMenuCode => data0.AsEnumerable()
|
.FirstOrDefault(row => row.Field<string>("menucode") == parentMenuCode))
|
.Where(row => row != null).Select(row => new TreeOne
|
{
|
code = row.Field<string>("menucode"),
|
name = row.Field<string>("menuname"),
|
flag = "0",
|
imgurl = fileip + row.Field<string>("imgurl"),
|
is_show = row.Field<string>("is_show")
|
}).ToList(); //.CopyToDataTable();
|
//添加子菜单数据
|
for (int i = 0; i < parentMenus.Count; i++)
|
{
|
parentMenus[i].children = data0.AsEnumerable().Where(row => codes.Contains(row.Field<string>("menucode")) && row.Field<string>("parent_id") == parentMenus[i].code)
|
.Select(row => new TreeTwo
|
{
|
code = row.Field<string>("menucode"),
|
name = row.Field<string>("menuname"),
|
flag = "0",
|
imgurl = fileip + row.Field<string>("imgurl"),
|
buttoncode = items.Where(m => m.MenuCode == row.Field<string>("menucode")).Select(m => m.ButtonCode).FirstOrDefault(),
|
datacode = items.Where(m => m.MenuCode == row.Field<string>("menucode")).Select(m => m.DataCode).FirstOrDefault(),
|
is_show = row.Field<string>("is_show")
|
}).ToList();
|
}
|
mes.code = "200";
|
mes.Message = "查询成功!";
|
mes.data = parentMenus;
|
return mes;
|
}
|
else
|
{
|
mes.code = "300";
|
mes.Message = "系统未设置菜单资料,请联系管理员!";
|
mes.data = null;
|
return mes;
|
}
|
}
|
else
|
{
|
mes.code = "300";
|
mes.Message = "当前账号对应角色未设置功能权限!";
|
mes.data = null;
|
return mes;
|
}
|
}
|
}
|
catch (Exception e)
|
{
|
mes.code = "300";
|
mes.count = 0;
|
mes.Message = e.Message;
|
mes.data = null;
|
}
|
return mes;
|
}
|
#endregion
|
|
#region[修改密码]
|
public static ToMessage UpdateUserPassword(User us, string password, string newpassword)
|
{
|
var sql = "";
|
List<object> list = new List<object>();
|
var dynamicParams = new DynamicParameters();
|
|
sql = @"select * from [dbo].[TUser] where usercode=@usercode and password=@password";
|
dynamicParams.Add("@usercode", us.usercode);
|
dynamicParams.Add("@password", password);
|
var data = DapperHelper.selectdata(sql, dynamicParams);
|
if (data.Rows.Count <= 0)
|
{
|
mes.code = "300";
|
mes.count = 0;
|
mes.Message = "输入的密码不正确,请重新输入！";
|
mes.data = null;
|
}
|
else
|
{
|
//修改密码
|
sql = @"update [dbo].[TUser] set password=@newpassword where usercode=@usercode and password=@password";
|
list.Add(new { str = sql, parm = new { usercode = us.usercode, password = password, newpassword = newpassword } });
|
bool aa = DapperHelper.DoTransaction(list);
|
if (aa)
|
{
|
//写入日志
|
LogHelper.DbOperateLog(us.usercode, "修改", "修改了密码", us.usertype);
|
mes.code = "200";
|
mes.count = 0;
|
mes.Message = "修改密码成功!";
|
mes.data = null;
|
}
|
else
|
{
|
mes.code = "300";
|
mes.count = 0;
|
mes.Message = "修改密码失败!";
|
mes.data = null;
|
}
|
}
|
return mes;
|
}
|
#endregion
|
|
#region[登出]
|
public static ToMessage LoginOut(User us)
|
{
|
RedisHelper redis = new RedisHelper();
|
try
|
{
|
//删除redis
|
redis.Remove(Enterprise + "LoginUserID" + us.usertype + us.usercode, 0);
|
//写入日志
|
LogHelper.DbOperateLog(us.usercode, "退出", "退出了系统", us.usertype);
|
mes.code = "200";
|
mes.count = 0;
|
mes.Message = "登出成功!";
|
mes.data = null;
|
}
|
catch (Exception e)
|
{
|
mes.code = "300";
|
mes.count = 0;
|
mes.Message = e.Message;
|
mes.data = null;
|
}
|
return mes;
|
}
|
#endregion
|
|
#region[密码重置]
|
public static ToMessage ResettUserPassword(string usercode, string username, string usertype)
|
{
|
var sql = "";
|
List<object> list = new List<object>();
|
var dynamicParams = new DynamicParameters();
|
|
//重置密码
|
sql = @"update [dbo].[TUser] set password=@newpassword where usercode=@usercode and username=@username";
|
list.Add(new { str = sql, parm = new { usercode = usercode, username = username, newpassword = "123456" } });
|
//写入操作记录表
|
LogHelper.DbOperateLog(usercode, "重置", "重置了密码", usertype);
|
bool aa = DapperHelper.DoTransaction(list);
|
if (aa)
|
{
|
mes.code = "200";
|
mes.count = 0;
|
mes.Message = "重置密码成功!";
|
mes.data = null;
|
}
|
else
|
{
|
mes.code = "300";
|
mes.count = 0;
|
mes.Message = "重置密码失败!";
|
mes.data = null;
|
}
|
|
return mes;
|
}
|
#endregion
|
|
#region[App登录查询安灯呼叫信息]
|
public static ToMessage LoginAppAnDonMessage(string rid, string usercode, string username, string usertype, string stu_torgcode, string stu_torgtypecode)
|
{
|
var sql = "";
|
string search = "";
|
var dynamicParams = new DynamicParameters();
|
try
|
{
|
switch (stu_torgtypecode)
|
{
|
case "":
|
search += "and T.description=@description ";
|
dynamicParams.Add("@description", "W");
|
break;
|
case "D":
|
search += "and child.org_code=@stu_torgcode ";
|
dynamicParams.Add("@stu_torgcode", stu_torgcode);
|
break;
|
case "W":
|
search += "and T.org_code=@stu_torgcode ";
|
dynamicParams.Add("@stu_torgcode", stu_torgcode);
|
break;
|
default:
|
break;
|
}
|
sql = @"select B.eqp_code,E.name as eqp_name,B.wkshp_code,T.org_name as wkshp_name,
|
A.type as typecode,D.name as typename,B.start_user,B.start_date
|
from TAnDnMessagePush A
|
inner join TAnDon_Task_Info B on A.m_id=B.id
|
left join TEqpInfo E on B.eqp_code=E.code
|
left join TOrganization T on B.wkshp_code=T.org_code
|
left join TAnDonType D on A.type=d.code
|
left join TUser U on A.resp_user=U.usercode
|
left join TOrganization as child on T.parent_id=child.id
|
where A.resp_user=@usercode and B.status='START' and U.rid=@rid " + search;
|
dynamicParams.Add("@usercode", usercode);
|
dynamicParams.Add("@rid", rid);
|
var data = DapperHelper.selectdata(sql, dynamicParams);
|
|
mes.code = "200";
|
mes.count = 0;
|
mes.Message = "查询成功!";
|
mes.data = data;
|
|
}
|
catch (Exception e)
|
{
|
mes.code = "300";
|
mes.count = 0;
|
mes.Message = e.Message;
|
mes.data = null;
|
}
|
return mes;
|
}
|
#endregion
|
}
|
}
|
