using Dapper;
|
using System;
|
using System.Collections.Generic;
|
using System.Configuration;
|
using System.Data;
|
using System.Data.SqlClient;
|
using System.Linq;
|
using System.Web;
|
using VueWebApi.Models;
|
using VueWebApi.Tools;
|
|
namespace VueWebApi.DLL.DAL
|
{
|
public class LoginDAL
|
{
|
public static DataTable dt; //定义全局变量dt
|
public static bool res; //定义全局变量dt
|
public static ToMessage mes = new ToMessage(); //定义全局返回信息对象
|
public static string strProcName = ""; //定义全局sql变量
|
public static List<SqlParameter> listStr = new List<SqlParameter>(); //定义全局参数集合
|
public static SqlParameter[] parameters; //定义全局SqlParameter参数数组
|
public static string fileip = System.Configuration.ConfigurationManager.AppSettings["FileIP"];
|
//获取企业配置Enterprise
|
private static readonly string Enterprise = ConfigurationManager.AppSettings["Enterprise"];
|
|
#region [登录查询]
|
public static DataTable LoginSearch(string username, string password)
|
{
|
|
//执行sql
|
strProcName = @"select * from TUser U
|
left join TOrganization T on U.stu_torgcode = T.org_code
|
where U.usercode =@username and U.password =@password and U.is_delete = '0'";
|
//创建参数
|
listStr.Add(new SqlParameter("@username", username));
|
listStr.Add(new SqlParameter("@password", password));
|
parameters = listStr.ToArray();
|
listStr.Clear();
|
DataTable dt = DBHelper.GetTable(strProcName, parameters);
|
return dt;
|
}
|
#endregion
|
|
#region [写入登录记录表]
|
public static ToMessage LoginBas(string usercode, string usertype,string stu_torgcode)
|
{
|
var sql = "";
|
var cont = 0;
|
var dynamicParams = new DynamicParameters();
|
try
|
{
|
sql = @"insert into T_Bas_Login(usercode,status,indate,origin,torg_code)
|
values(@usercode,'Y',@indate,@usertype,@stu_torgcode)";
|
dynamicParams.Add("@usercode", usercode);
|
dynamicParams.Add("@usertype", usertype);
|
dynamicParams.Add("@indate", DateTime.Now.ToString());
|
dynamicParams.Add("@stu_torgcode", stu_torgcode);
|
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)
|
{
|
string sql = "";
|
var dynamicParams = new DynamicParameters();
|
List<TreeObejct> list = new List<TreeObejct>();
|
try
|
{
|
//获取菜单集合(根据登录用户编码查询权限反推菜单)
|
sql = @"select distinct rt.right_code as code,rt.right_name as name,rt.right_seq,rt.is_delete as flag,imgurl from TRight rt where rt.right_code in(
|
select A.parent_id from TRight A
|
left join TRoleRightRelation B on B.right_code=A.right_code
|
left join TRole C on C.role_code=B.role_code
|
left join TUserRoleRelation D on D.role_code=C.role_code
|
left join TUser E on E.usercode=D.user_code
|
where E.usercode=@usercode) and rt.Type=@usertype order by rt.right_seq";
|
dynamicParams.Add("@usercode", usercode);
|
dynamicParams.Add("@usertype", usertype);
|
var data = DapperHelper.select<TreeObejct>(sql, dynamicParams);
|
data = data.Select(c =>
|
{
|
c.imgurl = fileip + c.imgurl;
|
return c;
|
}).ToList();
|
for (int i = 0; i < data.Count; i++)
|
{
|
sql = @"select * from(
|
select distinct mu.right_code as code,mu.right_name as name,mu.right_seq,mu.is_delete as flag,imgurl
|
from TRight mu
|
inner join TRoleRightRelation rl on mu.right_code=rl.right_code
|
inner join TUserRoleRelation ro on rl.role_code=ro.role_code
|
where mu.type=@usertype and ro.user_code=@usercode and mu.parent_id=@paent_id
|
) as AA order by CAST(AA.right_seq AS DECIMAL) ASC,AA.code";
|
dynamicParams.Add("@usercode", usercode);
|
dynamicParams.Add("@usertype", usertype);
|
dynamicParams.Add("@paent_id", data[i].code);
|
var data0 = DapperHelper.select<TreeObejctCn>(sql, dynamicParams);
|
data0 = data0.Select(c =>
|
{
|
c.imgurl = fileip + c.imgurl;
|
return c;
|
}).ToList();
|
data[i].children = data0;
|
}
|
mes.code = "200";
|
mes.Message = "查询成功!";
|
mes.data = data;
|
}
|
catch (Exception e)
|
{
|
mes.code = "300";
|
mes.count = 0;
|
mes.Message = e.Message;
|
mes.data = null;
|
}
|
return mes;
|
}
|
#endregion
|
|
|
|
#region[修改密码]
|
public static ToMessage UpdateUserPassword(string usercode, string username, 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 username=@username and password=@password";
|
dynamicParams.Add("@usercode", usercode);
|
dynamicParams.Add("@username", username);
|
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 username=@username and password=@password";
|
list.Add(new { str = sql, parm = new { usercode = usercode, username = username, password = password, newpassword = newpassword } });
|
//写入操作记录表
|
sql = @"insert into T_Bas_User_Operlog(usercode,oper_date,oper_type,description,origin) values(@usercode,@oper_date,@oper_type,@description,@origin)";
|
list.Add(new
|
{
|
str = sql,
|
parm = new
|
{
|
usercode = usercode,
|
oper_date = DateTime.Now.ToString(),
|
oper_type = "PC",
|
description = username + "修改了密码",
|
origin = "PC"
|
}
|
});
|
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 AppUpdateUserPassword(string usercode, string username, 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 username=@username and password=@password";
|
dynamicParams.Add("@usercode", usercode);
|
dynamicParams.Add("@username", username);
|
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 username=@username and password=@password";
|
list.Add(new { str = sql, parm = new { usercode = usercode, username = username, password = password, newpassword = newpassword } });
|
//写入操作记录表
|
sql = @"insert into T_Bas_User_Operlog(usercode,oper_date,oper_type,description,origin) values(@usercode,@oper_date,@oper_type,@description,@origin)";
|
list.Add(new
|
{
|
str = sql,
|
parm = new
|
{
|
usercode = usercode,
|
oper_date = DateTime.Now.ToString(),
|
oper_type = "APP",
|
description = username + "修改了密码",
|
origin = "APP"
|
}
|
});
|
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 ResettUserPassword(string usercode, string username)
|
{
|
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" } });
|
//写入操作记录表
|
sql = @"insert into T_Bas_User_Operlog(usercode,oper_date,oper_type,description,origin) values(@usercode,@oper_date,@oper_type,@description,@origin)";
|
list.Add(new
|
{
|
str = sql,
|
parm = new
|
{
|
usercode = usercode,
|
oper_date = DateTime.Now.ToString(),
|
oper_type = "APP",
|
description = username + "重置了密码",
|
origin = "APP"
|
}
|
});
|
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[PC登出]
|
public static ToMessage LoginOut(int userid, string usercode, string username, string usertype,string stu_torgcode)
|
{
|
var sql = "";
|
var cont = 0;
|
var dynamicParams = new DynamicParameters();
|
RedisHelper redis = new RedisHelper();
|
try
|
{
|
sql = @"insert into T_Bas_Login(usercode,status,outdate,origin,torg_code)
|
values(@usercode,'N',@outdate,@usertype,@stu_torgcode)";
|
dynamicParams.Add("@usercode", usercode);
|
dynamicParams.Add("@usertype", usertype);
|
dynamicParams.Add("@outdate", DateTime.Now.ToString());
|
dynamicParams.Add("@stu_torgcode", stu_torgcode);
|
cont = DapperHelper.SQL(sql, dynamicParams);
|
if (cont > 0)
|
{
|
redis.Remove(Enterprise + stu_torgcode + "LoginUserID" + usertype + userid, 0); //删除redis
|
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[App登出]
|
public static ToMessage LoginAppOut(int userid, string usercode, string username, string usertype,string stu_torgcode)
|
{
|
var sql = "";
|
var cont = 0;
|
var dynamicParams = new DynamicParameters();
|
RedisHelper redis = new RedisHelper();
|
try
|
{
|
sql = @"insert into T_Bas_Login(usercode,status,outdate,origin,torg_code)
|
values(@usercode,'N',@outdate,@usertype,@stu_torgcode)";
|
dynamicParams.Add("@usercode", usercode);
|
dynamicParams.Add("@usertype", usertype);
|
dynamicParams.Add("@outdate", DateTime.Now.ToString());
|
dynamicParams.Add("@stu_torgcode", stu_torgcode);
|
cont = DapperHelper.SQL(sql, dynamicParams);
|
if (cont > 0)
|
{
|
redis.Remove(Enterprise + stu_torgcode + "LoginUserID" + usertype + userid, 1); //删除redis
|
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[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
|
|
}
|
}
|
