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 U.username 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,
|
U.username as lm_user,
|
parent.lm_date
|
from TOrganization as parent
|
left join TOrganization as child on parent.parent_id=child.id
|
left join TUser U on parent.lm_user=U.usercode
|
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.count = 0;
|
mes.Message = e.Message;
|
mes.data = null;
|
}
|
return mes;
|
}
|
#endregion
|
|
#region [组织架构查找上级单位]
|
public static ToMessage PrentOrganization(string orgcode)
|
{
|
var sql = "";
|
var dynamicParams = new DynamicParameters();
|
string search = "";
|
try
|
{
|
switch (orgcode)
|
{
|
case "F": //工厂
|
orgcode = "";
|
search += "and description=@orgcode ";
|
dynamicParams.Add("@orgcode", orgcode);
|
break;
|
case "D": //部门
|
orgcode = "F";
|
search += "and description=@orgcode ";
|
dynamicParams.Add("@orgcode", orgcode);
|
break;
|
case "W": //车间
|
orgcode = "D";
|
search += "and description=@orgcode ";
|
dynamicParams.Add("@orgcode", orgcode);
|
break;
|
case "K": //科室
|
orgcode = "W";
|
search += "and description=@orgcode ";
|
dynamicParams.Add("@orgcode", orgcode);
|
break;
|
case "L": //产线
|
orgcode = "K";
|
search += "and description=@orgcode ";
|
dynamicParams.Add("@orgcode", orgcode);
|
break;
|
default:
|
orgcode = "";
|
search += "and description=@orgcode ";
|
dynamicParams.Add("@orgcode", orgcode);
|
break;
|
}
|
sql = @"select id,org_code,org_name
|
from TOrganization
|
where is_delete<>'1' " + search;
|
|
var data = DapperHelper.selectdata(sql, dynamicParams);
|
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 AddUpdateOrganization(string organType, string organCode, string organName, string Operator, int supUnit,string RightCode,string numvalue, string operType)
|
{
|
string sql = "";
|
List<object> list = new List<object>();
|
var dynamicParams = new DynamicParameters();
|
try
|
{
|
if (operType == "Add")
|
{
|
sql = @"select * from TOrganization where org_code=@organCode";
|
dynamicParams.Add("@organCode", organCode);
|
var data = DapperHelper.selectdata(sql, dynamicParams);
|
if (data.Rows.Count > 0)
|
{
|
mes.code = "300";
|
mes.count = 0;
|
mes.Message = "新增操作失败,编码重复!";
|
mes.data = null;
|
return mes;
|
}
|
|
sql = @"insert into TOrganization(parent_id,org_code,org_name,lm_user,lm_date,description)
|
values(@supUnit,@organCode,@organName,@Operator,@CreateDate,@organType)";
|
list.Add(new { str = sql, parm = new { supUnit = supUnit, organCode= organCode, organName= organName, Operator= Operator, CreateDate= DateTime.Now.ToString(), organType= organType } });
|
//sql = @"update T_CodeRules set value=@cunm2 where rightcode=@RightCode";
|
//list.Add(new { str = sql, parm = new { cunm2 = numvalue, RightCode = RightCode } });
|
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;
|
}
|
}
|
if (operType == "Update")
|
{
|
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.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 DeleteOrganization(int orgid)
|
{
|
var sql = "";
|
var cont = 0;
|
var dynamicParams = new DynamicParameters();
|
try
|
{
|
sql = @"select * from TOrganization where parent_id=@orgid and is_delete<>'1'";
|
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.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 UserSearch(string UserCode, string UserName, string StuOrg,string wagetype, 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 S.username like '%'+@UserName+'%' ";
|
dynamicParams.Add("@UserName", UserName);
|
}
|
if (StuOrg != "" && StuOrg != null)
|
{
|
search += "and u.stu_torgcode=@StuOrg ";
|
dynamicParams.Add("@StuOrg", StuOrg);
|
}
|
if (wagetype != "" && wagetype != null)
|
{
|
search += "and u.wagetype=@wagetype ";
|
dynamicParams.Add("@wagetype", wagetype);
|
}
|
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.id,u.usercode,u.username,g.group_code,g.group_name,u.stu_torgcode,t.org_name,u.password,u.mobile,u.email,u.wagetype,u.enable,S.username as lm_user,u.lm_date,u.is_role
|
from TUser u
|
left join TOrganization t on u.stu_torgcode=t.org_code
|
left join TGroup g on u.usergroup_code=g.group_code
|
left join TUser S on u.lm_user=S.usercode
|
where u.is_delete='0' and u.userclassid='0' " + 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.count = 0;
|
mes.Message = e.Message;
|
mes.data = null;
|
}
|
return mes;
|
}
|
#endregion
|
|
#region [用户所属组织]
|
public static ToMessage UserOrganization()
|
{
|
var dynamicParams = new DynamicParameters();
|
string sql = "";
|
try
|
{
|
List<TreeDG> list = new List<TreeDG>();
|
sql = @"select id,parent_id,org_code,org_name,description
|
FROM TOrganization where is_delete<>'1'";
|
var dt = DapperHelper.selectdata(sql, dynamicParams);
|
for (int i = 0; i < dt.Rows.Count; i++) //这里是循环所有
|
{
|
if (string.IsNullOrEmpty(dt.Rows[i]["PARENT_ID"].ToString()) || dt.Rows[i]["PARENT_ID"].ToString() == "0") //判断是否最外层根节点
|
{
|
TreeDG tbj = new TreeDG();
|
tbj.id = dt.Rows[i]["ID"].ToString();
|
tbj.code = dt.Rows[i]["ORG_CODE"].ToString();
|
tbj.name = dt.Rows[i]["ORG_NAME"].ToString();
|
list.Add(tbj);
|
}
|
}
|
digui(dt, list);
|
mes.code = "200";
|
mes.Message = "加载完成";
|
mes.data = list;
|
}
|
catch (Exception e)
|
{
|
mes.code = "300";
|
mes.count = 0;
|
mes.data = e.Message;
|
mes.data = null;
|
}
|
return mes;
|
}
|
#endregion
|
|
#region[用户所属班组]
|
public static ToMessage UserGroup()
|
{
|
var dynamicParams = new DynamicParameters();
|
try
|
{
|
// --------------查询指定数据--------------
|
var total = 0; //总条数
|
var sql = @"select group_code,group_name
|
from TGroup
|
where is_delete<>'1' ";
|
var data = DapperHelper.selecttable(sql);
|
mes.code = "200";
|
mes.Message = "查询成功!";
|
mes.count = total;
|
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 void digui(DataTable dt, List<TreeDG> tree)
|
{
|
for (int m = 0; m < tree.Count; m++)
|
{
|
tree[m].children = new List<TreeDG>();
|
for (int i = 0; i < dt.Rows.Count; i++)//第一次循环，得到所有根节点的子集
|
{
|
if (tree[m].id == dt.Rows[i]["PARENT_ID"].ToString() && dt.Rows[i]["ID"].ToString() != dt.Rows[i]["PARENT_ID"].ToString())
|
{
|
TreeDG tbjson = new TreeDG();
|
tbjson.id = dt.Rows[i]["ID"].ToString();
|
tbjson.code = dt.Rows[i]["ORG_CODE"].ToString();
|
tbjson.name = dt.Rows[i]["ORG_NAME"].ToString();
|
tree[m].children.Add(tbjson);
|
}
|
}
|
for (int i = 0; i < tree[m].children.Count; i++)
|
{
|
digui(dt, tree[m].children);//再次用子集去循环，拿出子集的子集
|
}
|
}
|
}
|
|
#endregion
|
|
#region[用户清单新增编辑]
|
public static ToMessage AddUpdateUser(string UserId, string UserCode, string UserName,string Password, string Enable, string StuOrg,string groupcode, string wagetype, string Mobile, string Email, string Operator, string OperType)
|
{
|
var dynamicParams = new DynamicParameters();
|
try
|
{
|
if (OperType == "Add")
|
{
|
var sql0 = @"select * from TUser where usercode=@UserCode";
|
dynamicParams.Add("@UserCode", UserCode);
|
var data = DapperHelper.selectdata(sql0, dynamicParams);
|
if (data.Rows.Count > 0)
|
{
|
mes.code = "300";
|
mes.count = 0;
|
mes.Message = "新增操作失败,编码重复!";
|
mes.data = null;
|
return mes;
|
}
|
|
var sql = @"insert into TUser(usercode,username,password,enable,mobile,lm_date,email,lm_user,stu_torgcode,wagetype,usergroup_code)
|
values(@UserCode,@UserName,@password,@Enable,@Mobile,@CreateDate,@Email,@Operator,@StuOrg,@wagetype,@groupcode)";
|
dynamicParams.Add("@UserCode", UserCode);
|
dynamicParams.Add("@UserName", UserName);
|
dynamicParams.Add("@password", Password);
|
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);
|
dynamicParams.Add("@wagetype", wagetype);
|
dynamicParams.Add("@groupcode", groupcode);
|
int 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;
|
}
|
}
|
if (OperType == "Update")
|
{
|
var sql = @"update TUser set username=@UserName,password=@password,enable=@Enable,mobile=@Mobile,lm_user=@Operator,email=@Email,stu_torgcode=@StuOrg,wagetype=@wagetype,usergroup_code=@groupcode,lm_date=@CreateDate where id=@UserId";
|
dynamicParams.Add("@UserId", UserId);
|
dynamicParams.Add("@UserName", UserName);
|
dynamicParams.Add("@password", Password);
|
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);
|
dynamicParams.Add("@wagetype", wagetype);
|
dynamicParams.Add("@groupcode", groupcode);
|
int 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 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 = @"delete TUserRoleRelation where user_code=@usercode";
|
list.Add(new { str = sql, parm = new { usercode = data.Rows[0]["USERCODE"].ToString() } });
|
|
sql = @"update TUser set is_delete='1' where id=@userid";
|
list.Add(new { str = sql, parm = new { userid = userid } });
|
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;
|
}
|
}
|
catch (Exception e)
|
{
|
mes.code = "300";
|
mes.count = 0;
|
mes.Message = e.Message;
|
mes.data = null;
|
}
|
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(select role_code from TUserRoleRelation where user_code=@usercode) C on B.role_code=C.role_code
|
where B.roletype_code=@roletype_code and B.is_delete='0' and B.is_system='0'";
|
dynamicParams.Add("@roletype_code", data[i].code);
|
dynamicParams.Add("@usercode", usercode);
|
var data0 = DapperHelper.select<TreeObejctCn>(sql, dynamicParams);
|
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 SaveUserAssoctRole(string usercode, List<RoleUserSubmit> json)
|
{
|
var sql = "";
|
List<object> list = new List<object>();
|
try
|
{
|
list.Clear();
|
if (json == null || json.Count <= 0)
|
{
|
//清除用户关联角色表数据
|
sql = @"delete TUserRoleRelation where user_code=@usercode";
|
list.Add(new { str = sql, parm =new {usercode = usercode } });
|
//清除用户表关联角色标识
|
sql = @"update TUser set is_role='N' where usercode=@usercode";
|
list.Add(new { str = sql, parm = new { usercode = usercode } });
|
}
|
else
|
{
|
//清除用户关联角色表数据
|
sql = @"delete TUserRoleRelation where user_code=@usercode";
|
list.Add(new { str = sql, parm = new { usercode = usercode } });
|
|
//循环写入用户关联角色表
|
for (int i = 0; i < json.Count; i++)
|
{
|
for (int j = 0; j < json[i].Data.Count; j++)
|
{
|
sql = @"insert into TUserRoleRelation(user_code,role_code) values(@usercode,@role_code)";
|
list.Add(new { str = sql, parm = new { usercode = usercode, role_code= json[i].Data[j] } });
|
|
}
|
}
|
//标记用户表关联角色标识
|
sql = @"update TUser set is_role='Y' where usercode=@usercode";
|
list.Add(new { str = sql, parm = new { usercode = usercode} });
|
}
|
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;
|
}
|
}
|
catch (Exception e)
|
{
|
mes.code = "300";
|
mes.count = 0;
|
mes.Message = e.Message;
|
mes.data = null;
|
}
|
return mes;
|
}
|
#endregion
|
|
#region[用户清单导入]
|
public static ToMessage ImportUserExcel(string username, string filesName)
|
{
|
var sql = "";
|
List<object> list = new List<object>();
|
DataTable ds = new DataTable();
|
|
try
|
{
|
ds = NPOIHelper.GetExcelDatatable(filesName); //调用自定义方法
|
int rowsnum = ds.Rows.Count;
|
if (rowsnum == 0)
|
{
|
mes.code = "300";
|
mes.count = 0;
|
mes.Message = "Excel表格为空,无数据！";
|
mes.data = null;
|
}
|
else
|
{
|
list.Clear();
|
for (int i = 0; i < ds.Rows.Count; i++)
|
{
|
|
if (ds.Rows[i][1].ToString() != "" && ds.Rows[i][1].ToString() != null)
|
{
|
sql = @"insert into TUser(usercode,username,enable,password,mobile,email,lm_user,lm_date,wagetype,stu_torgcode)
|
values(@usercode,@username,@enable,@password,@mobile,@email,@lm_user,@lm_date,@wagetype,@stu_torgcode)";
|
list.Add(new { str = sql, parm = new {
|
usercode = ds.Rows[i][1].ToString(),
|
username = ds.Rows[i][2].ToString(),
|
enable = ds.Rows[i][3].ToString(),
|
password = ds.Rows[i][4].ToString(),
|
mobile = ds.Rows[i][5].ToString(),
|
email = ds.Rows[i][6].ToString(),
|
lm_user = username,
|
lm_date = DateTime.Now.ToString(),
|
wagetype = ds.Rows[i][7].ToString(),
|
stu_torgcode = ds.Rows[i][8].ToString()
|
}
|
});
|
}
|
}
|
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;
|
}
|
}
|
}
|
catch (Exception e)
|
{
|
mes.code = "300";
|
mes.count = 0;
|
mes.Message = e.Message;
|
mes.data = null;
|
}
|
return mes;
|
}
|
#endregion
|
|
|
|
#region[用户组列表查询]
|
public static ToMessage UserGroupSearch(int startNum, int endNum, string prop, string order)
|
{
|
var dynamicParams = new DynamicParameters();
|
try
|
{
|
// --------------查询指定数据--------------
|
var total = 0; //总条数
|
var sql = @"select group_code,group_name,description
|
from TGroup
|
where is_delete<>'1' ";
|
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.count = 0;
|
mes.Message = e.Message;
|
mes.data = null;
|
}
|
return mes;
|
}
|
#endregion
|
|
#region[用户组新增]
|
public static ToMessage UserGroupAdd(List<StepDefect> json,string username)
|
{
|
var sql = "";
|
List<object> list = new List<object>();
|
var dynamicParams = new DynamicParameters();
|
try
|
{
|
list.Clear();
|
//循环写入用户组表
|
for (int i = 0; i < json.Count; i++)
|
{
|
var sql0 = @"select * from TGroup where group_code=@group_code";
|
dynamicParams.Add("@group_code", json[i].code);
|
var data = DapperHelper.selectdata(sql0, dynamicParams);
|
if (data.Rows.Count > 0)
|
{
|
mes.code = "300";
|
mes.count = 0;
|
mes.Message = "操作失败,编码重复!";
|
mes.data = null;
|
return mes;
|
}
|
sql = @"insert into TGroup(group_code,group_name,description,lm_user,lm_date) values(@groupcode,@groupename,@description,@lm_user,@lm_date)";
|
list.Add(new
|
{
|
str = sql,
|
parm = new
|
{
|
groupcode = json[i].code,
|
groupename = json[i].name,
|
description = json[i].flag,
|
lm_user = username,
|
lm_date = DateTime.Now.ToString()
|
}
|
});
|
}
|
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;
|
}
|
}
|
catch (Exception e)
|
{
|
mes.code = "300";
|
mes.count = 0;
|
mes.Message = e.Message;
|
mes.data = null;
|
}
|
return mes;
|
}
|
#endregion
|
|
#region[用户组删除]
|
public static ToMessage UserGroupDelete(string UserGrupCode)
|
{
|
var sql = "";
|
List<object> list = new List<object>();
|
var dynamicParams = new DynamicParameters();
|
try
|
{
|
list.Clear();
|
sql = @"select * from TUser where is_delete<>'1' and usergroup_code=@UserGrupCode";
|
dynamicParams.Add("@UserGrupCode", UserGrupCode);
|
var data = DapperHelper.selectdata(sql, dynamicParams);
|
if (data.Rows.Count > 0)
|
{
|
mes.code = "300";
|
mes.count = 0;
|
mes.Message = "该用户组下有关联的用户,不允许删除!";
|
mes.data = null;
|
return mes;
|
}
|
sql = @"delete TGroup where group_code=@UserGrupCode";
|
list.Add(new { str = sql, parm = new { UserGrupCode = UserGrupCode } });
|
|
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;
|
}
|
}
|
catch (Exception e)
|
{
|
mes.code = "300";
|
mes.count = 0;
|
mes.Message = e.Message;
|
mes.data = null;
|
}
|
return mes;
|
}
|
#endregion
|
|
|
|
|
|
#region[角色类型查询]
|
public static ToMessage RoleTypeSearch(int startNum, int endNum, string prop, string order)
|
{
|
var dynamicParams = new DynamicParameters();
|
try
|
{
|
// --------------查询指定数据--------------
|
var total = 0; //总条数
|
var sql = @"select roletype_code,roletype_name
|
from TRoleType
|
where is_delete<>'1' ";
|
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.count = 0;
|
mes.Message = e.Message;
|
mes.data = null;
|
}
|
return mes;
|
}
|
#endregion
|
|
#region[角色类型新增]
|
public static ToMessage RoleTypeAdd(List<ObjectData> json)
|
{
|
var sql = "";
|
List<object> list = new List<object>();
|
var dynamicParams = new DynamicParameters();
|
try
|
{
|
list.Clear();
|
//循环写入角色类型表
|
for (int i = 0; i < json.Count; i++)
|
{
|
var sql0 = @"select * from TRoleType where roletype_code=@roletype_code";
|
dynamicParams.Add("@roletype_code", json[i].code);
|
var data = DapperHelper.selectdata(sql0, dynamicParams);
|
if (data.Rows.Count > 0)
|
{
|
mes.code = "300";
|
mes.count = 0;
|
mes.Message = "操作失败,编码重复!";
|
mes.data = null;
|
return mes;
|
}
|
sql = @"insert into TRoleType(roletype_code,roletype_name) values(@roletypecode,@roletypename)";
|
list.Add(new
|
{
|
str = sql,
|
parm = new
|
{
|
roletypecode = json[i].code,
|
roletypename = json[i].name
|
}
|
});
|
}
|
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;
|
}
|
}
|
catch (Exception e)
|
{
|
mes.code = "300";
|
mes.count = 0;
|
mes.Message = e.Message;
|
mes.data = null;
|
}
|
return mes;
|
}
|
#endregion
|
|
#region[角色类型删除]
|
public static ToMessage RoleTypeDelete(string roleTypeCode)
|
{
|
var sql = "";
|
List<object> list = new List<object>();
|
try
|
{
|
list.Clear();
|
sql = @"delete TRoleType where roletype_code=@roletypecode";
|
list.Add(new { str = sql, parm = new { roletypecode = roleTypeCode}});
|
sql = @"delete TRole where roletype_code=@roletypecode";
|
list.Add(new { str = sql, parm = new { roletypecode = roleTypeCode }});
|
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;
|
}
|
}
|
catch (Exception e)
|
{
|
mes.code = "300";
|
mes.count = 0;
|
mes.Message = e.Message;
|
mes.data = null;
|
}
|
return mes;
|
}
|
#endregion
|
|
#region[角色类型下拉接口]
|
public static ToMessage RoleTypeSelect()
|
{
|
string sql = "";
|
try
|
{
|
//获取设备类型数据
|
sql = @"select roletype_code,roletype_name from TRoleType where is_delete<>'1' ";
|
var data = DapperHelper.selecttable(sql);
|
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 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 T.username like '%'+@CreateUser+'%' ";
|
dynamicParams.Add("@CreateUser", CreateUser);
|
}
|
if (search == "")
|
{
|
search = "and 1=1 ";
|
}
|
//search = search.Substring(3);//截取索引2后面的字符
|
// --------------查询指定数据--------------
|
var total = 0; //总条数
|
var sql = @"select A.id,A.role_code,A.role_name,b.roletype_code,B.roletype_name,A.description,T.username as 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
|
left join TUser T on A.lm_user=T.usercode
|
where A.is_delete='0' and A.is_system='0' " + 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.count = 0;
|
mes.Message = e.Message;
|
mes.data = null;
|
}
|
return mes;
|
}
|
#endregion
|
|
#region[角色清单新增编辑]
|
public static ToMessage AddUpdateRole(string RoleId, string RoleCode, string RoleName, string RoleTypeCode, string description, string Usercode, string OperType)
|
{
|
var dynamicParams = new DynamicParameters();
|
try
|
{
|
if (OperType == "Add")
|
{
|
var sql0 = @"select * from TRole where role_code=@role_code";
|
dynamicParams.Add("@role_code", RoleCode);
|
var data = DapperHelper.selectdata(sql0, dynamicParams);
|
if (data.Rows.Count > 0)
|
{
|
mes.code = "300";
|
mes.count = 0;
|
mes.Message = "操作失败,编码重复!";
|
mes.data = null;
|
return mes;
|
}
|
var sql = @"insert into TRole(role_code,role_name,lm_user,lm_date,roletype_code,description)
|
values(@RoleCode,@RoleName,@Usercode,@CreateDate,@RoleTypeCode,@description)";
|
dynamicParams.Add("@RoleCode", RoleCode);
|
dynamicParams.Add("@RoleName", RoleName);
|
dynamicParams.Add("@Usercode", Usercode);
|
dynamicParams.Add("@CreateDate", DateTime.Now.ToString());
|
dynamicParams.Add("@RoleTypeCode", RoleTypeCode);
|
dynamicParams.Add("@description", description);
|
int 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;
|
}
|
}
|
if (OperType == "Update")
|
{
|
var sql = @"update TRole set role_name=@RoleName,lm_user=@Usercode,lm_date=@CreateDate,roletype_code=@RoleTypeCode,description=@description where id=@RoleId";
|
dynamicParams.Add("@RoleId", RoleId);
|
dynamicParams.Add("@RoleName", RoleName);
|
dynamicParams.Add("@Usercode", Usercode);
|
dynamicParams.Add("@CreateDate", DateTime.Now.ToString());
|
dynamicParams.Add("@RoleTypeCode", RoleTypeCode);
|
dynamicParams.Add("@description", description);
|
int 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 DeleteRole(string RoleCode)
|
{
|
var sql = "";
|
List<object> list = new List<object>();
|
try
|
{
|
//更新角色表、角色关联用户标识及删除状态
|
sql = @"update TRole set is_user='N',is_right='N',is_delete='1' where role_code=@RoleCode";
|
list.Add(new { str = sql, parm = new { RoleCode = RoleCode } });
|
//更新角色关联用户表、角色关联用户标识及删除状态
|
sql = @"delete TUserRoleRelation where role_code=@RoleCode";
|
list.Add(new { str = sql, parm = new { RoleCode = RoleCode } });
|
//删除(更新状态)角色功能表
|
sql = @"update TRoleRightRelation set is_delete='1' where role_code=@RoleCode";
|
list.Add(new { str = sql, parm = new { RoleCode = RoleCode } });
|
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;
|
}
|
}
|
catch (Exception e)
|
{
|
mes.code = "300";
|
mes.count = 0;
|
mes.Message = e.Message;
|
mes.data = null;
|
}
|
return mes;
|
}
|
#endregion
|
|
#region[角色清单关联用户查询]
|
public static ToMessage RoleAssociationUser(string rolecode, string usercode, string username, string orgcode, string isrole, int startNum, int endNum, string prop, string order)
|
{
|
var dynamicParams = new DynamicParameters();
|
string search = "";
|
try
|
{
|
if (usercode != "" && usercode != null)
|
{
|
search += "and A.usercode like '%'+@usercode+'%' ";
|
dynamicParams.Add("@usercode", usercode);
|
}
|
if (username != "" && username != null)
|
{
|
search += "and A.username like '%'+@username+'%' ";
|
dynamicParams.Add("@username", username);
|
}
|
if (orgcode != "" && orgcode != null)
|
{
|
search += "and B.org_code=@orgcode ";
|
dynamicParams.Add("@orgcode", orgcode);
|
}
|
if (isrole != "" && isrole != null)
|
{
|
if (isrole == "Y")
|
{
|
search += "and C.user_code is not null ";
|
}
|
else
|
{
|
search += "and C.user_code is null ";
|
}
|
}
|
if (search == "")
|
{
|
search = " and 1=1 ";
|
}
|
//search = search.Substring(3);//截取索引2后面的字符
|
// --------------查询指定数据--------------
|
var total = 0; //总条数
|
var sql = @"select A.usercode,A.username,B.org_code,B.org_name,(case when C.user_code is not null then 'Y' else 'N' end) as flag
|
from TUser A
|
left join TOrganization B on A.stu_torgcode=B.org_code
|
left join (
|
select distinct user_code from TUserRoleRelation where role_code=@rolecode
|
) C on A.usercode=C.user_code where A.is_delete='0' and A.userclassid='0' " + search;
|
dynamicParams.Add("@rolecode", rolecode);
|
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.count = 0;
|
mes.Message = e.Message;
|
mes.data = null;
|
}
|
return mes;
|
}
|
#endregion
|
|
#region[用户清单关联角色查询1]
|
public static ToMessage RoleAssociationUserTwo(string rolecode)
|
{
|
string sql = "";
|
var dynamicParams = new DynamicParameters();
|
List<TreeObejct> list = new List<TreeObejct>();
|
try
|
{
|
//获取角色类型集合(包含绑定标识)
|
dynamicParams.Add("@role_code", rolecode);
|
var data = DapperHelper.selectProcedure("h_p_T_RoleAssociationUserDisplay", dynamicParams);
|
|
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 SaveRoleAssoctUser(string rolecode, List<ObjectData> json)
|
{
|
var sql = "";
|
var sql2 = "";
|
List<object> list = new List<object>();
|
var dynamicParams = new DynamicParameters();
|
try
|
{
|
list.Clear();
|
if (json == null || json.Count <= 0)
|
{
|
//清除用户关联角色表数据
|
sql = @"delete TUserRoleRelation where role_code=@rolecode";
|
list.Add(new { str = sql, parm = new { rolecode = rolecode } });
|
//标记角色表关联用户标识
|
sql = @"update TRole set is_user='N' where role_code=@rolecode";
|
list.Add(new { str = sql, parm = new { rolecode = rolecode } });
|
|
}
|
else
|
{
|
//清除用户关联角色表数据
|
sql = @"delete TUserRoleRelation where role_code=@rolecode";
|
list.Add(new { str = sql, parm = new { rolecode = rolecode } });
|
//循环写入用户关联角色表
|
for (int i = 0; i < json.Count; i++)
|
{
|
sql = @"insert into TUserRoleRelation(user_code,role_code) values(@usercode,@role_code)";
|
list.Add(new { str = sql, parm = new { usercode = json[i].code, role_code = rolecode } });
|
}
|
//标记角色表关联用户标识
|
sql = @"update TRole set is_user='Y' where role_code=@rolecode";
|
list.Add(new { str = sql, parm = new { rolecode = rolecode } });
|
}
|
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;
|
}
|
}
|
catch (Exception e)
|
{
|
mes.code = "300";
|
mes.count = 0;
|
mes.Message = e.Message;
|
mes.data = null;
|
}
|
return mes;
|
}
|
#endregion
|
|
#region[角色清单关联功能查询]
|
public static ToMessage RoleAssociationRight(string rolecode,string type)
|
{
|
List<object> list=new List<object>();
|
try
|
{
|
if (type == "" || type == null)
|
{
|
list.Add(SeachEncode.RightLocation(rolecode, "PC"));
|
list.Add(SeachEncode.RightLocation(rolecode, "APP"));
|
}
|
else if (type == "APP")
|
{
|
list.Add(SeachEncode.RightLocation(rolecode, type));
|
}
|
else {
|
list.Add(SeachEncode.RightLocation(rolecode, type));
|
}
|
mes.code = "200";
|
mes.count = list.Count;
|
mes.Message = "查询成功!";
|
mes.data = list;
|
}
|
catch (Exception e)
|
{
|
mes.code = "300";
|
mes.count = 0;
|
mes.Message = e.Message;
|
mes.data = null;
|
}
|
return mes;
|
}
|
#endregion
|
|
#region [角色清单关联功能保存]
|
public static ToMessage SaveUserAssoctRight(string rolecode,string usercode, List<TreeDM> json)
|
{
|
var sql = "";
|
List<object> list = new List<object>();
|
try
|
{
|
list.Clear();
|
if (json == null || json.Count <= 0)
|
{
|
//清除用户关联功能表数据
|
sql = @"delete TRoleRightRelation where role_code=@rolecode";
|
list.Add(new { str = sql, parm = new { rolecode = rolecode } });
|
//标记角色表关联功能标识
|
sql = @"update TRole set is_right='N' where role_code=@rolecode";
|
list.Add(new { str = sql, parm = new { rolecode = rolecode } });
|
}
|
else
|
{
|
//清除角色关联功能表数据
|
sql = @"delete TRoleRightRelation where role_code=@rolecode";
|
list.Add(new { str = sql, parm = new { rolecode = rolecode } });
|
//循环写入用户关联角色表
|
for (int i = 0; i < json.Count; i++) //PC、APP层
|
{
|
for (int j = 0; j < json[i].children.Count; j++) //一级菜单
|
{
|
for (int k = 0; k < json[i].children[j].children.Count; k++) //二级菜单
|
{
|
sql = @"insert into TRoleRightRelation(role_code,right_code,lm_user,lm_date) values(@role_code,@right_code,@lm_user,@lm_date)";
|
list.Add(new { str = sql, parm = new {
|
role_code = rolecode,
|
right_code= json[i].children[j].children[k].code,
|
lm_user= usercode, lm_date= DateTime.Now.ToString()
|
}
|
});
|
}
|
}
|
}
|
//标记角色表关联用户标识
|
sql = @"update TRole set is_right='Y' where role_code=@rolecode";
|
list.Add(new { str = sql, parm = new { rolecode = rolecode } });
|
}
|
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;
|
}
|
}
|
catch (Exception e)
|
{
|
mes.code = "300";
|
mes.count = 0;
|
mes.Message = e.Message;
|
mes.data = null;
|
}
|
return mes;
|
}
|
#endregion
|
|
|
#region[往来单位查询]
|
public static ToMessage CurrentUnitSearch(string cuntUnitCode, string cuntUnitName, string unitAttr, string createUser, int startNum, int endNum, string prop, string order)
|
{
|
var dynamicParams = new DynamicParameters();
|
string search = "";
|
try
|
{
|
if (cuntUnitCode != "" && cuntUnitCode != null)
|
{
|
search += "and A.code like '%'+@cuntUnitCode+'%' ";
|
dynamicParams.Add("@cuntUnitCode", cuntUnitCode);
|
}
|
if (cuntUnitName != "" && cuntUnitName != null)
|
{
|
search += "and A.name like '%'+@cuntUnitName+'%' ";
|
dynamicParams.Add("@cuntUnitName", cuntUnitName);
|
}
|
if (unitAttr != "" && unitAttr != null)
|
{
|
switch (unitAttr)
|
{
|
case "WG": //外购
|
search += "and A.mtype=@unitAttr ";
|
dynamicParams.Add("@unitAttr", unitAttr);
|
break;
|
case "WX": //外协
|
search += "and A.btype=@unitAttr ";
|
dynamicParams.Add("@unitAttr", unitAttr);
|
break;
|
default: //客户
|
search += "and A.htype=@unitAttr ";
|
dynamicParams.Add("@unitAttr", unitAttr);
|
break;
|
}
|
}
|
if (createUser != "" && createUser != null)
|
{
|
search += "and T.username like '%'+@createUser+'%' ";
|
dynamicParams.Add("@createUser", createUser);
|
}
|
if (search == "")
|
{
|
search = "and 1=1 ";
|
}
|
// --------------查询指定数据--------------
|
var total = 0; //总条数
|
var sql = @"select A.id,A.code,A.name,A.mtype,A.btype,A.htype,A.conttacts,A.conttphone,A.addr,T.username as lm_user,A.lm_date from TCustomer A
|
left join TUser T on A.lm_user=T.usercode
|
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.count = 0;
|
mes.Message = e.Message;
|
mes.data = null;
|
}
|
return mes;
|
}
|
#endregion
|
|
#region[往来单位新增编辑]
|
public static ToMessage AddUpdateCurrentUnit(string unitid, string unitcode, string unitname, string mtypecode, string btypecode, string htypecode, string person, string contact, string description, string usercode,string OperType)
|
{
|
var dynamicParams = new DynamicParameters();
|
try
|
{
|
if (OperType == "Add")
|
{
|
var sql0 = @"select * from TCustomer where code=@unitcode";
|
dynamicParams.Add("@unitcode", unitcode);
|
var data = DapperHelper.selectdata(sql0, dynamicParams);
|
if (data.Rows.Count > 0)
|
{
|
mes.code = "300";
|
mes.count = 0;
|
mes.Message = "操作失败,编码重复!";
|
mes.data = null;
|
return mes;
|
}
|
var sql = @"insert into TCustomer(code,name,mtype,btype,htype,conttacts,conttphone,addr,lm_user,lm_date)
|
values(@unitcode,@unitname,@mtypecode,@btypecode,@htypecode,@person,@contact,@description,@usercode,@CreateDate)";
|
dynamicParams.Add("@unitcode", unitcode);
|
dynamicParams.Add("@unitname", unitname);
|
dynamicParams.Add("@mtypecode", mtypecode);
|
dynamicParams.Add("@btypecode", btypecode);
|
dynamicParams.Add("@htypecode", htypecode);
|
dynamicParams.Add("@person", person);
|
dynamicParams.Add("@contact", contact);
|
dynamicParams.Add("@description", description);
|
dynamicParams.Add("@usercode", usercode);
|
dynamicParams.Add("@CreateDate", DateTime.Now.ToString());
|
int 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;
|
}
|
}
|
if (OperType == "Update")
|
{
|
var sql = @"update TCustomer set name=@unitname,mtype=@mtypecode,btype=@btypecode,htype=@htypecode,conttacts=@person,conttphone=@contact,addr=@description,lm_user=@usercode,lm_date=@CreateDate where id=@unitid";
|
dynamicParams.Add("@unitid", unitid);
|
dynamicParams.Add("@unitname", unitname);
|
dynamicParams.Add("@mtypecode", mtypecode);
|
dynamicParams.Add("@btypecode", btypecode);
|
dynamicParams.Add("@htypecode", htypecode);
|
dynamicParams.Add("@person", person);
|
dynamicParams.Add("@contact", contact);
|
dynamicParams.Add("@description", description);
|
dynamicParams.Add("@usercode", usercode);
|
dynamicParams.Add("@CreateDate", DateTime.Now.ToString());
|
int 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 DeleteCurrentUnit(string unitcode)
|
{
|
var sql = "";
|
List<object> list = new List<object>();
|
var dynamicParams = new DynamicParameters();
|
try
|
{
|
list.Clear();
|
sql = @"select * from TFlw_Rteqp where eqp_code=@unitcode";
|
dynamicParams.Add("@unitcode", unitcode);
|
var data = DapperHelper.selectdata(sql, dynamicParams);
|
if (data.Rows.Count > 0)
|
{
|
mes.code = "300";
|
mes.count = 0;
|
mes.Message = "外协供方已关联外协工序,不允许删除!";
|
mes.data = null;
|
return mes;
|
}
|
//删除往来单位表
|
sql = @"delete TCustomer where code=@unitcode";
|
list.Add(new { str = sql, parm = new { unitcode = unitcode } });
|
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;
|
}
|
}
|
catch (Exception e)
|
{
|
mes.code = "300";
|
mes.count = 0;
|
mes.Message = e.Message;
|
mes.data = null;
|
}
|
return mes;
|
}
|
#endregion
|
}
|
}
|
