using Dapper;
|
using Newtonsoft.Json;
|
using System;
|
using System.Collections.Generic;
|
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 BasicSettingDAL
|
{
|
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参数数组
|
|
#region [组织架构数据查询]
|
public static ToMessage OrganizationSearch(string orgCode, string orgName, string orgType, string userName,int startNum,int endNum,string prop,string order)
|
{
|
var dynamicParams = new DynamicParameters();
|
string search = "";
|
try
|
{
|
if (orgCode != "" && orgCode != null)
|
{
|
search += "and parent.org_code like '%'+@orgCode+'%' ";
|
dynamicParams.Add("@orgCode", orgCode);
|
}
|
if (orgName != "" && orgName != null)
|
{
|
search += "and parent.org_name like '%'+@orgName+'%' ";
|
dynamicParams.Add("@orgName", orgName);
|
}
|
if (orgType != "" && orgType != null)
|
{
|
search += "and parent.description=@orgType ";
|
dynamicParams.Add("@orgType", orgType);
|
}
|
if (userName != "" && userName != null)
|
{
|
search += "and parent.lm_user like '%'+@userName+'%' ";
|
dynamicParams.Add("@userName", userName);
|
}
|
if (search == "")
|
{
|
search = "and 1=1 ";
|
}
|
//search = search.Substring(3);//截取索引2后面的字符
|
// --------------查询指定数据--------------
|
var total = 0; //总条数
|
var sql = @"select
|
parent.id,
|
parent.org_code,
|
parent.org_name,
|
parent.description as description,
|
parent.parent_id,
|
child.org_code as parentorg_code,
|
child.org_name as parentorg_name,
|
parent.lm_user,
|
parent.lm_date
|
from TOrganization as parent
|
left join TOrganization as child on parent.parent_id=child.id where parent.is_delete<>1 " + search;
|
var data = DapperHelper.GetPageList<object>(sql, dynamicParams,prop,order, startNum, endNum, out total);
|
mes.code = "200";
|
mes.Message = "查询成功!";
|
mes.count = total;
|
mes.data = data.ToList();
|
}
|
catch (Exception e)
|
{
|
mes.code = "300";
|
mes.Message = e.Message;
|
}
|
return mes;
|
}
|
#endregion
|
|
#region[新增编辑组织架构]
|
public static ToMessage AddUpdateOrganization(string organType, string organCode, string organName,string Operator, int supUnit, string operType)
|
{
|
var dynamicParams = new DynamicParameters();
|
try
|
{
|
if (operType == "Add")
|
{
|
var sql = @"insert into TOrganization(parent_id,org_code,org_name,lm_user,lm_date,description)
|
values(@supUnit,@organCode,@organName,@Operator,@CreateDate,@organType)";
|
dynamicParams.Add("@supUnit", supUnit);
|
dynamicParams.Add("@organCode", organCode);
|
dynamicParams.Add("@organName", organName);
|
dynamicParams.Add("@Operator", Operator);
|
dynamicParams.Add("@CreateDate", DateTime.Now.ToString());
|
dynamicParams.Add("@organType", organType);
|
int cont = DapperHelper.SQL(sql, dynamicParams);
|
if (cont > 0)
|
{
|
mes.code = "200";
|
mes.Message = "新增操作成功!";
|
}
|
else
|
{
|
mes.code = "300";
|
mes.Message = "新增操作失败!";
|
}
|
}
|
if (operType == "Update")
|
{
|
var sql = @"update TOrganization set description=@organType,org_name=@organName,parent_id=@supUnit,lm_user=@Operator,lm_date=@CreateDate where org_code=@organCode";
|
dynamicParams.Add(@"organCode", organCode);
|
dynamicParams.Add("@supUnit", supUnit);
|
dynamicParams.Add("@organName", organName);
|
dynamicParams.Add("@Operator", Operator);
|
dynamicParams.Add("@CreateDate", DateTime.Now.ToString());
|
dynamicParams.Add("@organType", organType);
|
int cont = DapperHelper.SQL(sql, dynamicParams);
|
if (cont > 0)
|
{
|
mes.code = "200";
|
mes.Message = "修改操作成功!";
|
}
|
else
|
{
|
mes.code = "300";
|
mes.Message = "修改操作失败!";
|
}
|
}
|
}
|
catch (Exception e)
|
{
|
mes.code = "300";
|
mes.Message = e.Message;
|
}
|
return mes;
|
}
|
#endregion
|
|
#region [组织架构删除]
|
public static ToMessage DeleteOrganization(int orgid)
|
{
|
var sql = "";
|
var cont = 0;
|
var dynamicParams = new DynamicParameters();
|
try
|
{
|
sql = @"select * from TOrganization where parent_id=@orgid";
|
dynamicParams.Add(@"orgid", orgid);
|
cont = DapperHelper.selectcount(sql, dynamicParams);
|
if (cont <= 0)
|
{
|
mes.code = "300";
|
mes.Message = "当前组织有下级关联,请先删除下级组织!";
|
}
|
else
|
{
|
sql = @"update TOrganization set is_delete='1' where id=@orgid";
|
dynamicParams.Add(@"orgid", orgid);
|
cont = DapperHelper.SQL(sql, dynamicParams);
|
if (cont > 0)
|
{
|
mes.code = "200";
|
mes.Message = "删除操作成功!";
|
}
|
else
|
{
|
mes.code = "300";
|
mes.Message = "删除操作失败!";
|
}
|
}
|
}
|
catch (Exception e)
|
{
|
mes.code = "300";
|
mes.Message = e.Message;
|
}
|
return mes;
|
}
|
#endregion
|
|
|
|
#region[用户清单数据查询]
|
public static ToMessage UserSearch(string UserCode, string UserName, string StuOrg, string Enable, int startNum, int endNum, string prop, string order)
|
{
|
var dynamicParams = new DynamicParameters();
|
string search = "";
|
try
|
{
|
if (UserCode != "" && UserCode != null)
|
{
|
search += "and u.usercode like '%'+@UserCode+'%' ";
|
dynamicParams.Add("@UserCode", UserCode);
|
}
|
if (UserName != "" && UserName != null)
|
{
|
search += "and u.username like '%'+@UserName+'%' ";
|
dynamicParams.Add("@UserName", UserName);
|
}
|
if (StuOrg != "" && StuOrg != null)
|
{
|
search += "and u.stu_torgcode=@StuOrg ";
|
dynamicParams.Add("@StuOrg", StuOrg);
|
}
|
if (Enable != "" && Enable != null)
|
{
|
search += "and u.enable=@Enable ";
|
dynamicParams.Add("@Enable", Enable);
|
}
|
if (search == "")
|
{
|
search = "and 1=1 ";
|
}
|
//search = search.Substring(3);//截取索引2后面的字符
|
// --------------查询指定数据--------------
|
var total = 0; //总条数
|
var sql = @"select u.usercode,u.username,u.stu_torgcode,t.org_name,u.enable,u.lm_user,u.lm_date,u.is_role
|
from TUser u
|
left join TOrganization t on u.stu_torgcode=t.org_code where u.is_delete<>1 " + search;
|
var data = DapperHelper.GetPageList<object>(sql, dynamicParams, prop, order, startNum, endNum, out total);
|
mes.code = "200";
|
mes.Message = "查询成功!";
|
mes.count = total;
|
mes.data = data.ToList();
|
}
|
catch (Exception e)
|
{
|
mes.code = "300";
|
mes.Message = e.Message;
|
}
|
return mes;
|
}
|
#endregion
|
|
#region[用户清单新增编辑]
|
public static ToMessage AddUpdateUser(string UserId, string UserCode, string UserName, string Enable, string StuOrg,string Mobile,string Email, string Operator, string OperType)
|
{
|
var dynamicParams = new DynamicParameters();
|
try
|
{
|
if (OperType == "Add")
|
{
|
var sql = @"insert into TUser(usercode,username,enable,mobile,lm_date,email,lm_user,stu_torgcode)
|
values(@UserCode,@UserName,@Enable,@CreateDate,@Email,@Operator,@StuOrg)";
|
dynamicParams.Add("@UserCode", UserCode);
|
dynamicParams.Add("@UserName", UserName);
|
dynamicParams.Add("@Enable", Enable);
|
dynamicParams.Add("@Mobile", Mobile);
|
dynamicParams.Add("@CreateDate", DateTime.Now.ToString());
|
dynamicParams.Add("@Email", Email);
|
dynamicParams.Add("@Operator", Operator);
|
dynamicParams.Add("@StuOrg", StuOrg);
|
int cont = DapperHelper.SQL(sql, dynamicParams);
|
if (cont > 0)
|
{
|
mes.code = "200";
|
mes.Message = "新增操作成功!";
|
}
|
else
|
{
|
mes.code = "300";
|
mes.Message = "新增操作失败!";
|
}
|
}
|
if (OperType == "Update")
|
{
|
var sql = @"update TUser set username=@UserName,enable=@Enable,mobile=@Mobile,lm_user=@Operator,email=@Email,stu_torgcode=@StuOrg,lm_date=@CreateDate where id=@UserId";
|
dynamicParams.Add("@UserId", UserId);
|
dynamicParams.Add("@UserName", UserName);
|
dynamicParams.Add("@Enable", Enable);
|
dynamicParams.Add("@Mobile", Mobile);
|
dynamicParams.Add("@CreateDate", DateTime.Now.ToString());
|
dynamicParams.Add("@Email", Email);
|
dynamicParams.Add("@Operator", Operator);
|
dynamicParams.Add("@StuOrg", StuOrg);
|
int cont = DapperHelper.SQL(sql, dynamicParams);
|
if (cont > 0)
|
{
|
mes.code = "200";
|
mes.Message = "修改操作成功!";
|
}
|
else
|
{
|
mes.code = "300";
|
mes.Message = "修改操作失败!";
|
}
|
}
|
}
|
catch (Exception e)
|
{
|
mes.code = "300";
|
mes.Message = e.Message;
|
}
|
return mes;
|
}
|
#endregion
|
|
#region[用户清单删除]
|
public static ToMessage DeleteUser(int userid)
|
{
|
var sql = "";
|
//List<Dictionary<object, string>> list = new List<Dictionary<string, string>>();
|
List<object> list = new List<object>();
|
var dynamicParams = new DynamicParameters();
|
try
|
{
|
sql = @"select usercode from TUser where id=@userid";
|
dynamicParams.Add("@userid", userid);
|
var data = DapperHelper.selectdata(sql, dynamicParams);
|
|
sql = @"update TUserRoleRelation set is_delete='1' where user_code=@usercode";
|
dynamicParams.Add("@usercode",data.Rows[0]["USERCODE"].ToString());
|
list.Add(new { str = sql, parm = dynamicParams });
|
|
sql = @"update TUser set is_delete='1' where id=@userid";
|
dynamicParams.Add("@userid", userid);
|
list.Add(new { str = sql, parm = dynamicParams });
|
bool aa = DapperHelper.DoTransaction(list);
|
if (aa)
|
{
|
mes.code = "200";
|
mes.Message = "删除用户成功!";
|
}
|
else
|
{
|
mes.code = "300";
|
mes.Message = "删除用户失败!";
|
}
|
}
|
catch (Exception e)
|
{
|
mes.code = "300";
|
mes.Message = e.Message;
|
}
|
return mes;
|
}
|
#endregion
|
|
#region[用户清单关联角色查询]
|
public static ToMessage UserAssociationRole(string usercode)
|
{
|
string sql = "";
|
var dynamicParams = new DynamicParameters();
|
List<TreeObejct> list = new List<TreeObejct>();
|
try
|
{
|
//获取角色类型集合(包含绑定标识)
|
sql = @"select A.roletype_code as code,A.roletype_name as name,(case when B.roletype_code is null then 'N' else 'Y' end) flag from TRoleType A
|
left join (
|
select distinct B.roletype_code from TRole B
|
inner join TUserRoleRelation C on B.role_code=C.role_code
|
where C.user_code=@usercode
|
) B
|
on A.roletype_code=B.roletype_code
|
where A.is_delete='0'";
|
dynamicParams.Add("@usercode", usercode);
|
var data = DapperHelper.select<TreeObejct>(sql, dynamicParams);
|
for (int i = 0; i < data.Count; i++)
|
{
|
sql = @"select B.role_code as code,B.role_name as name,(case when C.role_code is null then 'N' else 'Y' end) flag from TRole B
|
left join TUserRoleRelation C on B.role_code=C.role_code
|
where B.roletype_code=@roletype_code and C.user_code=@usercode and B.is_delete='0'";
|
dynamicParams.Add("@roletype_code", data[i].code);
|
dynamicParams.Add("@usercode", usercode);
|
var data0 = DapperHelper.select<TreeObejctCont>(sql, dynamicParams);
|
data[i].children = data0;
|
|
}
|
mes.code = "200";
|
mes.Message = "查询成功!";
|
mes.data = data;
|
}
|
catch (Exception e)
|
{
|
mes.code = "300";
|
mes.Message = e.Message;
|
}
|
return mes;
|
}
|
#endregion
|
|
|
|
#region[角色清单查询]
|
public static ToMessage RoleSearch(string RoleCode, string RoleName, string RoleTypeCode, string CreateUser, int startNum, int endNum, string prop, string order)
|
{
|
var dynamicParams = new DynamicParameters();
|
string search = "";
|
try
|
{
|
if (RoleCode != "" && RoleCode != null)
|
{
|
search += "and A.role_code like '%'+@RoleCode+'%' ";
|
dynamicParams.Add("@RoleCode", RoleCode);
|
}
|
if (RoleName != "" && RoleName != null)
|
{
|
search += "and A.role_name like '%'+@RoleName+'%' ";
|
dynamicParams.Add("@RoleName", RoleName);
|
}
|
if (RoleTypeCode != "" && RoleTypeCode != null)
|
{
|
search += "and A.roletype_code=@RoleTypeCode ";
|
dynamicParams.Add("@RoleTypeCode", RoleTypeCode);
|
}
|
if (CreateUser != "" && CreateUser != null)
|
{
|
search += "and A.lm_user like '%'+@CreateUser+'%' ";
|
dynamicParams.Add("@CreateUser", CreateUser);
|
}
|
if (search == "")
|
{
|
search = "and 1=1 ";
|
}
|
//search = search.Substring(3);//截取索引2后面的字符
|
// --------------查询指定数据--------------
|
var total = 0; //总条数
|
var sql = @"select A.role_code,A.role_name,b.roletype_code,B.roletype_name,A.lm_user,A.lm_date,A.is_user,A.is_right
|
from TRole A
|
left join TRoleType B on A.roletype_code=B.roletype_code
|
where A.is_delete<>'1' " + search;
|
var data = DapperHelper.GetPageList<object>(sql, dynamicParams, prop, order, startNum, endNum, out total);
|
mes.code = "200";
|
mes.Message = "查询成功!";
|
mes.count = total;
|
mes.data = data.ToList();
|
}
|
catch (Exception e)
|
{
|
mes.code = "300";
|
mes.Message = e.Message;
|
}
|
return mes;
|
}
|
#endregion
|
}
|
}
|
