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;
|
using static VueWebCoreApi.Models.RolePermission.RolePermission;
|
using static VueWebCoreApi.Models.RolePermission.RolePermissionSava;
|
|
namespace VueWebCoreApi.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参数数组
|
public static dynamic CompanyCode = AppSetting.GetAppSetting("CompanyCode");
|
public static dynamic CompanyName = AppSetting.GetAppSetting("CompanyName");
|
|
|
|
#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)
|
{
|
search += "and A.type=@unitAttr ";
|
dynamicParams.Add("@unitAttr", unitAttr);
|
}
|
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.type,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 typecode, 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,type,conttacts,conttphone,addr,lm_user,lm_date)
|
values(@unitcode,@unitname,@typecode,@person,@contact,@description,@usercode,@CreateDate)";
|
dynamicParams.Add("@unitcode", unitcode);
|
dynamicParams.Add("@unitname", unitname);
|
dynamicParams.Add("@typecode", typecode);
|
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,type=@typecode,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("@typecode", typecode);
|
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
|
|
|
#region[安灯系统/车间查询]
|
public static ToMessage AnDengShopSearch(string stu_torgcode, string description)
|
{
|
string sql = "";
|
string search = "";
|
var dynamicParams = new DynamicParameters();
|
try
|
{
|
//获取车间信息
|
switch (description)
|
{
|
case "":
|
break;
|
case "D":
|
search += "and child.org_code=@stu_torgcode ";
|
dynamicParams.Add("@stu_torgcode", stu_torgcode);
|
break;
|
case "W":
|
search += "and parent.org_code=@stu_torgcode ";
|
dynamicParams.Add("@stu_torgcode", stu_torgcode);
|
break;
|
default:
|
break;
|
}
|
sql = @"select parent.org_code,parent.org_name
|
from TOrganization parent
|
left join TOrganization as child on parent.parent_id=child.id
|
where parent.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 AnDengTypeSearch()
|
{
|
try
|
{
|
// --------------查询指定数据--------------
|
var total = 0; //总条数
|
var sql = @"select id, code,name
|
from TAnDonType
|
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 ToMessage AddUpdateAnDengType(List<ObjectData> 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++)
|
{
|
sql = @"insert into TAnDonType(code,name,lm_user,lm_date) values(@code,@name,@username,@createdate)";
|
list.Add(new
|
{
|
str = sql,
|
parm = new
|
{
|
code = json[i].code,
|
name = json[i].name,
|
username = username,
|
createdate = 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 DeleteAnDengType(string andengtypecode)
|
{
|
var sql = "";
|
List<object> list = new List<object>();
|
var dynamicParams = new DynamicParameters();
|
try
|
{
|
list.Clear();
|
//判断呼叫类型下是否关联
|
sql = @"select * from TAnDon_Roul_ConFig where andotype_code=@andengtypecode and is_delete<>'1'";
|
dynamicParams.Add("@andengtypecode", andengtypecode);
|
var data = DapperHelper.selectdata(sql, dynamicParams);
|
if (data.Rows.Count > 0)
|
{
|
mes.code = "300";
|
mes.count = 0;
|
mes.Message = "当前呼叫类型已关联呼叫配置,不允许删除!";
|
mes.data = null;
|
}
|
else
|
{
|
sql = @"delete TAnDonType where code=@andengtypecode";
|
list.Add(new { str = sql, parm = new { andengtypecode = andengtypecode } });
|
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 AnDengResponUserSearch(string wkshopcode, string calltypecode)
|
{
|
var sql = "";
|
var dynamicParams = new DynamicParameters();
|
try
|
{
|
sql = @"select B.id,B.ando_cogfigid,U.usercode,U.username,A.enable from TAnDon_Roul_ConFig A
|
inner join TAnDon_Roul_ConFigUser B on A.id=B.ando_cogfigid
|
left join TUser U on B.usercode=U.usercode
|
where A.wkshp_code=@wkshopcode and A.andotype_code=@calltypecode and U.is_delete=0";
|
dynamicParams.Add("@wkshopcode", wkshopcode);
|
dynamicParams.Add("@calltypecode", calltypecode);
|
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
|
|
#region[安灯系统/新增响应人员查询已绑定的响应人员]
|
public static ToMessage AnDengDialogResponUserSearch(string wkshopcode, string calltypecode)
|
{
|
var sql = "";
|
var dynamicParams = new DynamicParameters();
|
try
|
{
|
sql = @"select A.usercode,A.username,(case when B.usercode<>'' then 'Y' else 'N' end) as flag
|
from TUser A
|
left join (
|
select B.usercode from TAnDon_Roul_ConFig A
|
inner join TAnDon_Roul_ConFigUser B on A.id=B.ando_cogfigid
|
where A.wkshp_code=@wkshopcode and A.andotype_code=@calltypecode
|
) B on A.usercode=B.usercode
|
where A.userclassid='0'";
|
dynamicParams.Add("@wkshopcode", wkshopcode);
|
dynamicParams.Add("@calltypecode", calltypecode);
|
var data = DapperHelper.selectdata(sql, dynamicParams);
|
if (data.Rows.Count > 0)
|
{
|
mes.code = "200";
|
mes.count = 0;
|
mes.Message = "查询成功!";
|
mes.data = data;
|
}
|
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 AnDengDigoResponUserSeave(string wkshopcode, string calltypecode, string enable, string usercode, DataTable json)
|
{
|
var sql = "";
|
int mid = 0;
|
List<object> list = new List<object>();
|
var dynamicParams = new DynamicParameters();
|
try
|
{
|
list.Clear();
|
//判断呼叫类型下是否关联
|
sql = @"select isnull(id,0) id from TAnDon_Roul_ConFig where wkshp_code=@wkshopcode and andotype_code=@calltypecode and is_delete<>'1'";
|
dynamicParams.Add("@wkshopcode", wkshopcode);
|
dynamicParams.Add("@calltypecode", calltypecode);
|
var data = DapperHelper.selectdata(sql, dynamicParams);
|
if (data.Rows.Count > 0)
|
{
|
mid = Convert.ToInt32(data.Rows[0]["ID"].ToString());
|
//清除安灯、设备任务配置关联人员表数据
|
sql = @"delete TAnDon_Roul_ConFigUser where ando_cogfigid=@mid";
|
list.Add(new { str = sql, parm = new { mid = mid } });
|
}
|
else
|
{
|
//获取主表最大ID
|
sql = @"select ISNULL(IDENT_CURRENT('TAnDon_Roul_ConFig')+1,1) as id";
|
var dt = DapperHelper.selecttable(sql);
|
mid = Convert.ToInt32(dt.Rows[0]["ID"].ToString());
|
//写入安灯、设备任务配置表数据
|
sql = @"insert into TAnDon_Roul_ConFig(wkshp_code,andotype_code,lm_user,lm_date,enable) values(@wkshp_code,@andotype_code,@lm_user,@lm_date,@enable)";
|
list.Add(new
|
{
|
str = sql,
|
parm = new
|
{
|
wkshp_code = wkshopcode,
|
andotype_code = calltypecode,
|
lm_user = usercode,
|
lm_date = DateTime.Now.ToString(),
|
enable = enable
|
}
|
});
|
}
|
//循环写入安灯、设备任务配置关联人员表
|
for (int i = 0; i < json.Rows.Count; i++)
|
{
|
sql = @"insert into TAnDon_Roul_ConFigUser(ando_cogfigid,usercode) values(@ando_cogfigid,@role_code)";
|
list.Add(new { str = sql, parm = new { ando_cogfigid = mid, role_code = json.Rows[i]["CODE"].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 AnDengResponUserCloseSeave(string wkshopcode, string calltypecode, string enable, string usercode)
|
{
|
var sql = "";
|
List<object> list = new List<object>();
|
try
|
{
|
list.Clear();
|
//更新安灯、设备任务配置表状态
|
sql = @"update TAnDon_Roul_ConFig set enable=@enable,lm_user=@lm_user,lm_date=@lm_date where wkshp_code=@wkshp_code and andotype_code=@andengtypecode";
|
list.Add(new
|
{
|
str = sql,
|
parm = new
|
{
|
wkshp_code = wkshopcode,
|
andengtypecode = calltypecode,
|
lm_user = usercode,
|
lm_date = DateTime.Now.ToString(),
|
enable = enable
|
}
|
});
|
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 AnDengResponUserDeleteSeave(string id, string ando_cogfigid)
|
{
|
var sql = "";
|
List<object> list = new List<object>();
|
var dynamicParams = new DynamicParameters();
|
try
|
{
|
list.Clear();
|
//查询安灯、设备任务配置表
|
sql = @"select id from TAnDon_Roul_ConFigUser where ando_cogfigid=@ando_cogfigid";
|
dynamicParams.Add("@ando_cogfigid", ando_cogfigid);
|
var data = DapperHelper.selectdata(sql, dynamicParams);
|
if (data.Rows.Count > 1)
|
{
|
//根据子表id删除对应子表人员
|
sql = @"delete TAnDon_Roul_ConFigUser where id=@id";
|
list.Add(new { str = sql, parm = new { id = id } });
|
}
|
else
|
{
|
//根据子表id删除对应子表人员
|
sql = @"delete TAnDon_Roul_ConFigUser where id=@id";
|
list.Add(new { str = sql, parm = new { id = id } });
|
//根据主表id删除主表数据
|
sql = @"delete TAnDon_Roul_ConFig where id=@id";
|
list.Add(new { str = sql, parm = new { id = ando_cogfigid } });
|
}
|
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 WhkspIsEqpSearch(string wkshpcode)
|
{
|
string sql = "";
|
var dynamicParams = new DynamicParameters();
|
try
|
{
|
//获取设备下拉框数据
|
sql = @"select code as eqp_code,name as eqp_name
|
from TEqpInfo where wksp_code=@wkshpcode and enable='Y' ";
|
dynamicParams.Add("@wkshpcode", wkshpcode);
|
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 STorgData(string storg_code, string storg_name, int startNum, int endNum, string prop, string order)
|
{
|
var dynamicParams = new DynamicParameters();
|
string search = "";
|
try
|
{
|
if (storg_code != "" && storg_code != null)
|
{
|
search += "and T.torg_code like '%'+@storg_code+'%' ";
|
dynamicParams.Add("@storg_code", storg_code);
|
}
|
if (storg_name != "" && storg_name != null)
|
{
|
search += "and T.torg_name like '%'+@storg_name+'%' ";
|
dynamicParams.Add("@storg_name", storg_name);
|
}
|
// --------------查询指定数据--------------
|
var total = 0; //总条数
|
var sql = @"select T.id,T.parent_id,T.torg_code,T.torg_name,T.status,T.leve,T.torg_seq,
|
T.lm_user as usercode,U.username,T.lm_date
|
from TOrganization T
|
left join TUser U on T.lm_user=U.usercode
|
where 1=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 AddUpdateOrganization(string OrganCode, string OrganName, string leve, string parent_id, string storgcode, string torg_seq, string status, string OperType, User us)
|
{
|
string sql = "";
|
List<object> list = new List<object>();
|
var dynamicParams = new DynamicParameters();
|
try
|
{
|
if (OperType == "Add")
|
{
|
sql = @"select * from TOrganization where torg_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,torg_code,torg_name,status,leve,torg_seq,lm_user,lm_date)
|
values(@parent_id,@torg_code,@torg_name,@status,@leve,@torg_seq,@lm_user,@lm_date)";
|
list.Add(new
|
{
|
str = sql,
|
parm = new
|
{
|
parent_id = storgcode,
|
torg_code = OrganCode,
|
torg_name = OrganName,
|
status = status,
|
leve = leve,
|
torg_seq = torg_seq,
|
lm_user = us.usercode,
|
lm_date = DateTime.Now.ToString()
|
}
|
});
|
|
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;
|
}
|
}
|
if (OperType == "Update")
|
{
|
//判断所属组织是否有变更
|
if (parent_id != storgcode)
|
{
|
//判断新选择的组织是否被引用
|
sql = @"select * from TUser where storg_code=@storgcode and status='Y'";
|
dynamicParams.Add("@storgcode", storgcode);
|
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 = @"with CTE as (
|
select torg_code
|
from TOrganization
|
where torg_code =@OrganCode
|
union all
|
select t.torg_code
|
from TOrganization AS t
|
inner join CTE AS c ON t.parent_id = c.torg_code
|
)
|
select * from TUser
|
where storg_code in (select torg_code from CTE)";
|
dynamicParams.Add("@OrganCode", OrganCode);
|
var data1 = DapperHelper.selectdata(sql, dynamicParams);
|
if (data1.Rows.Count > 0)
|
{
|
mes.code = "300";
|
mes.count = 0;
|
mes.Message = "当前原有组织或子级被使用不允许修改!";
|
mes.data = null;
|
return mes;
|
}
|
|
//修改当前组织数据
|
sql = @"update TOrganization set parent_id=@parent_id,torg_name=@torg_name,leve=@leve,torg_seq=@torg_seq,lm_user=@lm_user,lm_date=@lm_date
|
where torg_code=@torg_code";
|
list.Add(new
|
{
|
str = sql,
|
parm = new
|
{
|
parent_id = storgcode,
|
torg_code = OrganCode,
|
torg_name = OrganName,
|
leve = leve,
|
torg_seq = torg_seq,
|
lm_user = us.usercode,
|
lm_date = DateTime.Now.ToString()
|
}
|
});
|
//递归更新状态,当前选中数据及子级
|
sql = @"with CTE as (
|
select torg_code
|
from TOrganization
|
where torg_code =@OrganCode
|
union all
|
select t.torg_code
|
from TOrganization AS t
|
inner join CTE AS c ON t.parent_id = c.torg_code
|
)
|
update TOrganization set status=@status
|
where torg_code in (select torg_code from CTE)";
|
list.Add(new
|
{
|
str = sql,
|
parm = new
|
{
|
OrganCode = OrganCode,
|
status = status
|
}
|
});
|
|
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;
|
}
|
}
|
}
|
catch (Exception e)
|
{
|
mes.code = "300";
|
mes.count = 0;
|
mes.Message = e.Message;
|
mes.data = null;
|
}
|
return mes;
|
}
|
#endregion
|
|
#region [组织架构删除]
|
public static ToMessage DeleteOrganization(string orgcode, User us)
|
{
|
var sql = "";
|
var cont = 0;
|
var dynamicParams = new DynamicParameters();
|
try
|
{
|
sql = @"select * from TOrganization where parent_id=@orgcode";
|
dynamicParams.Add(@"orgcode", orgcode);
|
var data0 = DapperHelper.selectdata(sql, dynamicParams);
|
if (data0.Rows.Count > 0)
|
{
|
mes.code = "300";
|
mes.Message = "当前组织有下级关联,请先删除下级组织!";
|
return mes;
|
}
|
sql = @"select * from TUser where storg_code=@orgcode";
|
dynamicParams.Add("@orgcode", orgcode);
|
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 TOrganization where torg_code=@orgcode";
|
dynamicParams.Add(@"orgcode", orgcode);
|
cont = DapperHelper.SQL(sql, dynamicParams);
|
if (cont > 0)
|
{
|
//写入操作记录表
|
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;
|
}
|
|
}
|
catch (Exception e)
|
{
|
mes.code = "300";
|
mes.count = 0;
|
mes.Message = e.Message;
|
mes.data = null;
|
}
|
return mes;
|
}
|
#endregion
|
|
|
#region[岗位信息列表查询]
|
public static ToMessage PostData(string postcode, string postname, int startNum, int endNum, string prop, string order)
|
{
|
var dynamicParams = new DynamicParameters();
|
string search = "";
|
try
|
{
|
if (postcode != "" && postcode != null)
|
{
|
search += "and P.postcode like '%'+@postcode+'%' ";
|
dynamicParams.Add("@postcode", postcode);
|
}
|
if (postname != "" && postname != null)
|
{
|
search += "and P.postname like '%'+@postname+'%' ";
|
dynamicParams.Add("@postname", postname);
|
}
|
// --------------查询指定数据--------------
|
var total = 0; //总条数
|
var sql = @"select P.id,P.postcode,P.postname,P.status,P.description,
|
P.lm_user as usercode,U.username,P.lm_date
|
from TPost P
|
left join TUser U on P.lm_user=U.usercode
|
where 1=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 AddUpdatePost(string postcode, string postname, string description, string status, string OperType, User us)
|
{
|
string sql = "";
|
List<object> list = new List<object>();
|
var dynamicParams = new DynamicParameters();
|
try
|
{
|
if (OperType == "Add")
|
{
|
sql = @"select * from TPost where postcode=@postcode";
|
dynamicParams.Add("@postcode", postcode);
|
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 TPost(postcode,postname,status,description,lm_user,lm_date)
|
values(@postcode,@postname,@status,@description,@lm_user,@lm_date)";
|
list.Add(new
|
{
|
str = sql,
|
parm = new
|
{
|
postcode = postcode,
|
postname = postname,
|
status = status,
|
description = description,
|
lm_user = us.usercode,
|
lm_date = DateTime.Now.ToString()
|
}
|
});
|
|
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;
|
}
|
}
|
if (OperType == "Update")
|
{
|
if (status == "N")
|
{
|
//当岗位状态为停用时,判断当前岗位是否被用户引用
|
sql = @"select * from TUser where ',' + post_code + ',' like '%,'+@postcode+',%'";
|
dynamicParams.Add("@postcode", postcode);
|
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 = @"update TPost set postname=@postname,status=@status,description=@description,lm_user=@lm_user,lm_date=@lm_date
|
where postcode=@postcode";
|
list.Add(new
|
{
|
str = sql,
|
parm = new
|
{
|
postcode = postcode,
|
postname = postname,
|
status = status,
|
description = description,
|
lm_user = us.usercode,
|
lm_date = DateTime.Now.ToString()
|
}
|
});
|
|
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;
|
}
|
}
|
}
|
catch (Exception e)
|
{
|
mes.code = "300";
|
mes.count = 0;
|
mes.Message = e.Message;
|
mes.data = null;
|
}
|
return mes;
|
}
|
#endregion
|
|
#region[岗位删除删除]
|
public static ToMessage DeletePost(string postcode, User us)
|
{
|
var sql = "";
|
var cont = 0;
|
var dynamicParams = new DynamicParameters();
|
try
|
{
|
sql = @"select * from TUser where ',' + post_code + ',' like '%,'+@postcode+',%'";
|
dynamicParams.Add("@postcode", postcode);
|
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 TPost where postcode=@postcode";
|
dynamicParams.Add(@"postcode", postcode);
|
cont = DapperHelper.SQL(sql, dynamicParams);
|
if (cont > 0)
|
{
|
//写入操作记录表
|
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;
|
}
|
|
}
|
catch (Exception e)
|
{
|
mes.code = "300";
|
mes.count = 0;
|
mes.Message = e.Message;
|
mes.data = null;
|
}
|
return mes;
|
}
|
#endregion
|
|
|
#region[用户信息列表查询]
|
public static ToMessage UserData(string storgcode, string usercode, string username, int startNum, int endNum, string prop, string order)
|
{
|
var dynamicParams = new DynamicParameters();
|
string search0 = "";
|
string search = "";
|
try
|
{
|
if (storgcode != "" && storgcode != null)
|
{
|
search0 += "and torg_code=@storgcode ";
|
dynamicParams.Add("@storgcode", storgcode);
|
}
|
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);
|
}
|
// --------------查询指定数据--------------
|
var total = 0; //总条数
|
var withsql = @"with cte as (
|
select torg_code, torg_name, parent_id
|
from TOrganization
|
where status='Y' " + search0 + " union all select T.torg_code, T.torg_name, T.parent_id from TOrganization T inner join CTE ON T.parent_id = CTE.torg_code)";
|
var sql = @"SELECT distinct U.usercode,U.username,U.password,U.mobile,U.email,U.status,U.storg_code as storg_code,T.torg_name as storg_name,T.parent_id,
|
U.role_code,
|
STUFF((SELECT ',' + R.rolename
|
FROM TRole R
|
WHERE CHARINDEX(',' + R.rolecode + ',', ',' + U.role_code + ',') > 0
|
FOR XML PATH('')), 1, 1, '') AS rolename,
|
U.post_code,
|
STUFF((SELECT ',' + P.postname
|
FROM TPost P
|
WHERE CHARINDEX(',' + P.postcode + ',', ',' + U.post_code + ',') > 0
|
FOR XML PATH('')), 1, 1, '') AS postname,
|
U.group_code,
|
STUFF((SELECT ',' + P.usergroupname
|
FROM TGroup P
|
WHERE CHARINDEX(',' + P.usergroupcode + ',', ',' + U.group_code + ',') > 0
|
FOR XML PATH('')), 1, 1, '') AS group_name,
|
U.lm_user as createusercode,S.username as createusername,U.lm_date
|
FROM TUser U
|
inner join cte on U.storg_code=cte.torg_code
|
left join TOrganization T on U.storg_code=T.torg_code
|
left join TUser S on U.lm_user=S.usercode " + search + "";
|
var data = DapperHelper.GetPagedDataWith<object>(withsql, 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 AddUpdateUserSave(string usercode, string username, string password, string mobile, string email, string storg_code, string status, string post_code, string role_code, string group_code, string opertype, User us)
|
{
|
string sql = "";
|
List<object> list = new List<object>();
|
//string result = "";
|
var dynamicParams = new DynamicParameters();
|
try
|
{
|
//查询所有的组织数据
|
//sql = @"select * from TOrganization where status='Y'";
|
//var dattorg = DapperHelper.selecttable(sql);
|
|
//根据新增用户关联的角色查找角色表对应的数据范围
|
//string[] rolecode = Array.ConvertAll<string, string>(role_code.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries), s => s.ToString()); //string分割转string[]
|
//sql = @"select R.datarange,T.dataname,R.datapermissions from TRole R
|
// inner join TData T on R.datarange=T.datacode
|
// where R.rolecode in @rolecode and R.status='Y'";
|
//dynamicParams.Add("@rolecode", rolecode);
|
//var data0 = DapperHelper.selectlist(sql, dynamicParams);
|
//使用LINQ去重,合并数据范围
|
//DataTable dtResult = data0.AsEnumerable().Distinct(DataRowComparer.Default).CopyToDataTable();
|
|
//for (int i = 0; i < dtResult.Rows.Count; i++)
|
//{
|
// switch (dtResult.Rows[i]["dataname"].ToString())
|
// {
|
// case "全部":
|
// //查询所有组织编码
|
// // 使用lambda表达式查询Name字段，并通过逗号隔开
|
// string all = string.Join(",", dattorg.AsEnumerable().Select(row => row.Field<string>("torg_code")));
|
// result += all + ",";
|
// break;
|
// //查询本部门组织编码
|
// case "本级":
|
// // 使用递归和 lambda 表达式查询指定 id 的父级编码,leve=1 代表部门
|
// string leve = storg_code;
|
// result += leve + ",";
|
// break;
|
// //本人编码
|
// case "本人":
|
// result += usercode + ",";
|
// break;
|
// //查询自定义的组织编码
|
// case "自定义":
|
// result += dtResult.Rows[i]["datapermissions"].ToString() + ",";
|
// break;
|
// default:
|
// break;
|
// }
|
//}
|
//// 移除最后一个逗号
|
//result = result.TrimEnd(',');
|
////去除重复值
|
//result = string.Join(",", result.Split(',').Distinct().ToArray());
|
|
if (opertype == "Add")
|
{
|
//查询新增用户编码是否重复
|
sql = @"select * from TUser where usercode=@usercode";
|
dynamicParams.Add("@usercode", usercode);
|
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 TUser(usercode,username,password,mobile,email,status,storg_code,role_code,role_datapermissions,post_code,group_code,is_system_admin,lm_user,lm_date)
|
values(@usercode,@username,@password,@mobile,@email,@status,@storg_code,@role_code,@role_datapermissions,@post_code,@group_code,@is_system_admin,@lm_user,@lm_date)";
|
list.Add(new
|
{
|
str = sql,
|
parm = new
|
{
|
usercode = usercode,
|
username = username,
|
password = password,
|
mobile = mobile,
|
email = email,
|
status = status,
|
storg_code = storg_code,
|
role_code = role_code,
|
role_datapermissions = "",
|
post_code = post_code,
|
group_code = group_code,
|
is_system_admin = "N",
|
lm_user = us.usercode,
|
lm_date = DateTime.Now.ToString()
|
}
|
});
|
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;
|
}
|
}
|
if (opertype == "Update")
|
{
|
|
sql = @"update TUser set username=@username,password=@password,mobile=@mobile,email=@email,status=@status,storg_code=@storg_code,
|
role_code=@role_code,role_datapermissions=@role_datapermissions,post_code=@post_code,group_code=@group_code,lm_user=@lm_user,lm_date=@lm_date
|
where usercode=@usercode";
|
list.Add(new
|
{
|
str = sql,
|
parm = new
|
{
|
usercode = usercode,
|
username = username,
|
password = password,
|
mobile = mobile,
|
email = email,
|
status = status,
|
storg_code = storg_code,
|
role_code = role_code,
|
role_datapermissions = "",
|
post_code = post_code,
|
group_code = group_code,
|
lm_user = us.usercode,
|
lm_date = DateTime.Now.ToString()
|
}
|
});
|
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;
|
}
|
}
|
}
|
catch (Exception e)
|
{
|
mes.code = "300";
|
mes.count = 0;
|
mes.Message = e.Message;
|
mes.data = null;
|
}
|
return mes;
|
}
|
#endregion
|
|
#region[用户信息删除]
|
public static ToMessage DeleteUserSave(string usercode, User us)
|
{
|
var sql = "";
|
List<object> list = new List<object>();
|
var dynamicParams = new DynamicParameters();
|
try
|
{
|
//判断当前用户是否有报工记录
|
|
sql = @"delete TUser where usercode=@usercode";
|
list.Add(new { str = sql, parm = new { usercode = usercode } });
|
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;
|
}
|
}
|
catch (Exception e)
|
{
|
mes.code = "300";
|
mes.count = 0;
|
mes.Message = e.Message;
|
mes.data = null;
|
}
|
return mes;
|
}
|
#endregion
|
|
|
#region[班组信息列表查询]
|
public static ToMessage GroupData(string groupcode, string groupname, int startNum, int endNum, string prop, string order)
|
{
|
var dynamicParams = new DynamicParameters();
|
string search = "";
|
try
|
{
|
if (groupcode != "" && groupcode != null)
|
{
|
search += "and G.usergroupcode like '%'+@groupcode+'%' ";
|
dynamicParams.Add("@groupcode", groupcode);
|
}
|
if (groupname != "" && groupname != null)
|
{
|
search += "and G.usergroupname like '%'+@groupname+'%' ";
|
dynamicParams.Add("@groupname", groupname);
|
}
|
// --------------查询指定数据--------------
|
var total = 0; //总条数
|
var sql = @"select G.usergroupcode,G.usergroupname,G.status,G.description,
|
G.lm_user as usercode,U.username,G.lm_date
|
from TGroup G
|
left join TUser U on G.lm_user=U.usercode
|
where 1=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 AddUpdateGroup(string groupcode, string groupname, string description, string status, string OperType, User us)
|
{
|
string sql = "";
|
List<object> list = new List<object>();
|
var dynamicParams = new DynamicParameters();
|
try
|
{
|
if (OperType == "Add")
|
{
|
sql = @"select * from TGroup where usergroupcode=@groupcode";
|
dynamicParams.Add("@groupcode", groupcode);
|
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 TGroup(usergroupcode,usergroupname,status,description,lm_user,lm_date)
|
values(@usergroupcode,@usergroupname,@status,@description,@lm_user,@lm_date)";
|
list.Add(new
|
{
|
str = sql,
|
parm = new
|
{
|
usergroupcode = groupcode,
|
usergroupname = groupname,
|
status = status,
|
description = description,
|
lm_user = us.usercode,
|
lm_date = DateTime.Now.ToString()
|
}
|
});
|
|
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;
|
}
|
}
|
if (OperType == "Update")
|
{
|
if (status == "N")
|
{
|
//当班组状态为停用时,判断当前班组是否被用户引用
|
sql = @"select * from TUser where ',' + group_code + ',' like '%,'+@groupcode+',%'";
|
dynamicParams.Add("@groupcode", groupcode);
|
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 = @"update TGroup set usergroupname=@usergroupname,status=@status,description=@description,lm_user=@lm_user,lm_date=@lm_date
|
where usergroupcode=@usergroupcode";
|
list.Add(new
|
{
|
str = sql,
|
parm = new
|
{
|
usergroupcode = groupcode,
|
usergroupname = groupname,
|
status = status,
|
description = description,
|
lm_user = us.usercode,
|
lm_date = DateTime.Now.ToString()
|
}
|
});
|
|
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;
|
}
|
}
|
}
|
catch (Exception e)
|
{
|
mes.code = "300";
|
mes.count = 0;
|
mes.Message = e.Message;
|
mes.data = null;
|
}
|
return mes;
|
}
|
#endregion
|
|
#region[班组删除]
|
public static ToMessage DeleteGroup(string groupcode, User us)
|
{
|
var sql = "";
|
var cont = 0;
|
var dynamicParams = new DynamicParameters();
|
try
|
{
|
sql = @"select * from TUser where ',' + group_code + ',' like '%,'+@groupcode+',%'";
|
dynamicParams.Add("@groupcode", groupcode);
|
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 usergroupcode=@groupcode";
|
dynamicParams.Add(@"groupcode", groupcode);
|
cont = DapperHelper.SQL(sql, dynamicParams);
|
if (cont > 0)
|
{
|
//写入操作记录表
|
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;
|
}
|
|
}
|
catch (Exception e)
|
{
|
mes.code = "300";
|
mes.count = 0;
|
mes.Message = e.Message;
|
mes.data = null;
|
}
|
return mes;
|
}
|
#endregion
|
|
|
#region[角色管理列表查询]
|
public static ToMessage RoleData(string role_code, string role_name, int startNum, int endNum, string prop, string order)
|
{
|
var dynamicParams = new DynamicParameters();
|
string search = "";
|
try
|
{
|
if (role_code != "" && role_code != null)
|
{
|
search += "and R.rolecode like '%'+@role_code+'%' ";
|
dynamicParams.Add("@role_code", role_code);
|
}
|
if (role_name != "" && role_name != null)
|
{
|
search += "and R.rolename like '%'+@role_name+'%' ";
|
dynamicParams.Add("@role_name", role_name);
|
}
|
// --------------查询指定数据--------------
|
var total = 0; //总条数
|
var sql = @"select R.rolecode,R.rolename,R.status,R.datarange as datacode,TB.dataname,
|
R.datapermissions as datapermissionscode,R.description,
|
STUFF((SELECT ',' + P.torg_name
|
FROM TOrganization P
|
WHERE CHARINDEX(',' + P.torg_code+ ',', ',' + R.datapermissions + ',') > 0
|
FOR XML PATH('')), 1, 1, '') AS datapermissionsname,
|
R.lm_user as usercode,U.username,R.lm_date
|
from TRole R
|
left join TData TB on R.datarange=TB.datacode
|
left join TUser U on R.lm_user=U.usercode
|
where 1=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 RoleAddUpdateSava(string menucode, string rolecode, string rolename, string status, string datacode, string datapermissions, string description, string OperType, User us)
|
{
|
var dynamicParams = new DynamicParameters();
|
List<object> list = new List<object>();
|
string sql = "";
|
string updatedJson = "";
|
try
|
{
|
list.Clear();
|
if (OperType == "Add")
|
{
|
//获取角色编码
|
mes = SeachEncodeJob.EncodingSeach(menucode);
|
if (mes.code == "300")
|
{
|
return mes;
|
}
|
//查询角色名称是否存在
|
sql = @"select * from TRole where rolename=@rolename";
|
dynamicParams.Add("@rolename", rolename);
|
var data = DapperHelper.selectdata(sql, dynamicParams);
|
if (data.Rows.Count > 0)
|
{
|
mes.code = "300";
|
mes.Message = "同名角色已存在!";
|
mes.data = null;
|
return mes;
|
}
|
//写入角色表
|
sql = @"insert into TRole(rolecode,rolename,status,datarange,datapermissions,identifying,description,lm_user,lm_date)
|
values(@rolecode,@rolename,@status,@datarange,@datapermissions,@identifying,@description,@lm_user,@lm_date)";
|
list.Add(new
|
{
|
str = sql,
|
parm = new
|
{
|
rolecode = ((List<string>)mes.data)[0].ToString(),
|
rolename = rolename,
|
status = status,
|
datarange = datacode,
|
datapermissions = datapermissions,
|
identifying = "2", //0(管理员) 1(所有人) 2(其它)
|
description = description,
|
lm_user = us.usercode,
|
lm_date = DateTime.Now.ToString()
|
}
|
});
|
//回写编码规则表
|
list.AddRange(SeachEncodeJob.StrEncodingUpdate(menucode, ((List<string>)mes.data)[1].ToString()));
|
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;
|
}
|
}
|
if (OperType == "Update")
|
{
|
if (status == "N")
|
{
|
//当角色状态为停用时,判断当前角色是否被用户引用
|
sql = @"select * from TUser where ',' + role_code + ',' like '%,'+@rolecode+',%'";
|
dynamicParams.Add("@rolecode", rolecode);
|
var data0 = DapperHelper.selectdata(sql, dynamicParams);
|
if (data0.Rows.Count > 0)
|
{
|
mes.code = "300";
|
mes.count = 0;
|
mes.Message = "该角色有关联的用户,不允许停用!";
|
mes.data = null;
|
return mes;
|
}
|
}
|
//通过角色编码查询角色数据关联表
|
sql = @"select * from TAuthority where rolecode=@rolecode";
|
dynamicParams.Add("@rolecode", rolecode);
|
var data = DapperHelper.selectdata(sql, dynamicParams);
|
if (data.Rows.Count > 0)
|
{
|
JArray jsonArray = JArray.Parse(data.Rows[0]["jsondate"].ToString());
|
jsonArray.Select(j => {
|
JObject jsonObject = (JObject)j;
|
if (datacode == "CUSTOM")
|
{
|
jsonObject["datacode"] = datapermissions;
|
}
|
else
|
{
|
jsonObject["datacode"] = datacode;
|
}
|
return j;
|
}).ToList();
|
updatedJson = JsonConvert.SerializeObject(jsonArray);
|
//更新角色数据表
|
sql = @"update TAuthority set jsondate=@jsondate,type=@type
|
where rolecode=@rolecode";
|
list.Add(new
|
{
|
str = sql,
|
parm = new
|
{
|
rolecode = rolecode,
|
jsondate = updatedJson,
|
type = us.usertype
|
}
|
});
|
}
|
//修改角色表
|
sql = @"update TRole set rolename=@rolename,status=@status,datarange=@datarange,
|
datapermissions=@datapermissions,description=@description,lm_user=@lm_user,lm_date=@lm_date
|
where rolecode=@rolecode";
|
list.Add(new
|
{
|
str = sql,
|
parm = new
|
{
|
rolecode = rolecode,
|
rolename = rolename,
|
status = status,
|
datarange = datacode,
|
datapermissions = datapermissions,
|
description = description,
|
lm_user = us.usercode,
|
lm_date = DateTime.Now.ToString()
|
}
|
});
|
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;
|
}
|
}
|
}
|
catch (Exception e)
|
{
|
mes.code = "300";
|
mes.count = 0;
|
mes.Message = e.Message;
|
mes.data = null;
|
}
|
return mes;
|
}
|
#endregion
|
|
#region[角色管理删除]
|
public static ToMessage RoleDeleteSava(string rolecode, User us)
|
{
|
var dynamicParams = new DynamicParameters();
|
List<object> list = new List<object>();
|
string sql = "";
|
try
|
{
|
list.Clear();
|
//判断当前角色是否关联用户
|
sql = @"select * from TUser where ',' + role_code + ',' like '%,'+@rolecode+',%'";
|
dynamicParams.Add("@rolecode", rolecode);
|
var data = DapperHelper.selectdata(sql, dynamicParams);
|
if (data.Rows.Count > 0)
|
{
|
mes.code = "300";
|
mes.count = 0;
|
mes.Message = "该角色有关联的用户,不允许删除!";
|
mes.data = null;
|
return mes;
|
}
|
//删除角色数据关联表 TAuthority
|
sql = @"delete TAuthority where rolecode=@rolecode";
|
list.Add(new { str = sql, parm = new { rolecode = rolecode } });
|
//删除角色表
|
sql = @"delete TRole where rolecode=@rolecode and identifying='2'";
|
list.Add(new { str = sql, parm = new { rolecode = rolecode } });
|
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;
|
}
|
}
|
catch (Exception e)
|
{
|
mes.code = "300";
|
mes.count = 0;
|
mes.Message = e.Message;
|
mes.data = null;
|
}
|
return mes;
|
}
|
#endregion
|
|
|
#region[角色管理分配用户,获取已分配用户列表]
|
public static ToMessage RoleAssignedUserData(string flag, string role_code, string usercode, string username, string storg_code, string post_code, string group_code, int startNum, int endNum, string prop, string order)
|
{
|
var dynamicParams = new DynamicParameters();
|
string search = "";
|
try
|
{
|
switch (flag)
|
{
|
case "TRUE":
|
if (role_code != "" && role_code != null)
|
{
|
search += "and ',' + U.role_code + ',' like '%,'+@role_code+',%' ";
|
dynamicParams.Add("@role_code", role_code);
|
}
|
break;
|
case "FALSE":
|
if (role_code != "" && role_code != null)
|
{
|
search += "and ',' + U.role_code + ',' not like '%,'+@role_code+',%' ";
|
dynamicParams.Add("@role_code", role_code);
|
}
|
break;
|
}
|
|
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 (storg_code != "" && storg_code != null)
|
{
|
search += "and U.storg_code=@storg_code ";
|
dynamicParams.Add("@storg_code", storg_code);
|
}
|
if (post_code != "" && post_code != null)
|
{
|
search += "and ',' + U.post_code + ',' like '%,'+@post_code+',%' ";
|
dynamicParams.Add("@post_code", post_code);
|
}
|
if (group_code != "" && group_code != null)
|
{
|
search += "and ',' + U.group_code + ',' like '%,'+@group_code+',%' ";
|
dynamicParams.Add("@group_code", group_code);
|
}
|
// --------------查询指定数据--------------
|
var total = 0; //总条数
|
var sql = @"SELECT distinct U.usercode,U.username,U.password,U.mobile,U.email,U.status,U.storg_code as storg_code,T.torg_name as storg_name,T.parent_id,
|
U.role_code,
|
STUFF((SELECT ',' + R.rolename
|
FROM TRole R
|
WHERE CHARINDEX(',' + R.rolecode + ',', ',' + U.role_code + ',') > 0
|
FOR XML PATH('')), 1, 1, '') AS rolename,
|
U.post_code,
|
STUFF((SELECT ',' + P.postname
|
FROM TPost P
|
WHERE CHARINDEX(',' + P.postcode + ',', ',' + U.post_code + ',') > 0
|
FOR XML PATH('')), 1, 1, '') AS postname,
|
U.group_code,
|
STUFF((SELECT ',' + P.usergroupname
|
FROM TGroup P
|
WHERE CHARINDEX(',' + P.usergroupcode + ',', ',' + U.group_code + ',') > 0
|
FOR XML PATH('')), 1, 1, '') AS group_name,
|
U.lm_user as createusercode,S.username as createusername,U.lm_date
|
FROM TUser U
|
left join TOrganization T on U.storg_code=T.torg_code
|
left join TUser S on U.lm_user=S.usercode
|
where 1=1 and U.status='Y' and U.role_code<>'' " + 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 RoleAssignedUserBatchCancel(string role_code, string[] user, User us)
|
{
|
List<object> list = new List<object>();
|
string sql = "";
|
try
|
{
|
list.Clear();
|
//批量更新用户表中关联的角色(自定义函数TSplitString来拆分字符串)
|
sql = @"update TUser
|
set role_code = stuff(
|
(
|
select ',' + value
|
from dbo.TSplitString(role_code, ',')
|
where value <> @role_code
|
for XML PATH('')
|
), 1, 1, ''
|
)
|
where usercode in @usercode";
|
list.Add(new
|
{
|
str = sql,
|
parm = new
|
{
|
role_code = role_code,
|
usercode = user
|
}
|
});
|
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;
|
}
|
|
|
}
|
catch (Exception e)
|
{
|
mes.code = "300";
|
mes.count = 0;
|
mes.Message = e.Message;
|
mes.data = null;
|
}
|
return mes;
|
}
|
#endregion
|
|
#region[角色管理分配用户,批量添加未分配的用户]
|
public static ToMessage RoleAssignedUserBatchAdd(string role_code, string[] user, User us)
|
{
|
List<object> list = new List<object>();
|
string sql = "";
|
try
|
{
|
list.Clear();
|
//批量更新用户表中关联的角色
|
sql = @"update TUser
|
set role_code = case
|
when role_code IS NULL OR role_code = '' then @role_code
|
else role_code + ',' + @role_code
|
end
|
where usercode in @usercode";
|
list.Add(new
|
{
|
str = sql,
|
parm = new
|
{
|
role_code = role_code,
|
usercode = user
|
}
|
});
|
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;
|
}
|
|
|
}
|
catch (Exception e)
|
{
|
mes.code = "300";
|
mes.count = 0;
|
mes.Message = e.Message;
|
mes.data = null;
|
}
|
return mes;
|
}
|
#endregion
|
|
|
#region[角色权限查询角色]
|
public static ToMessage RolePermissionSearchRole()
|
{
|
List<TRole> list = new List<TRole>();
|
var dynamicParams = new DynamicParameters();
|
string sql = "";
|
try
|
{
|
//查询所有角色
|
sql = @"select R.rolecode as code,R.rolename as name,R.datarange,T.dataname,R.datapermissions,R.identifying
|
from TRole R
|
left join TData T on R.datarange=T.datacode
|
where status='Y'";
|
list = DapperHelper.select<TRole>(sql, null);
|
if (list.Count > 0)
|
{
|
mes.code = "200";
|
mes.count = list.Count;
|
mes.Message = "查询角色成功！";
|
mes.data = list;
|
}
|
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 RolePermissionSearchRoleMenu(string type)
|
{
|
var dynamicParams = new DynamicParameters();
|
string sql = "";
|
try
|
{
|
//查询所有默认菜单及按钮、数据
|
sql = @"select menucode,menuname,parent_id,buttoncodelist
|
from TMenu
|
where Type=@type";
|
dynamicParams.Add("@type", type);
|
var data = DapperHelper.selectdata(sql, dynamicParams);
|
if (data.Rows.Count > 0)
|
{
|
mes.code = "200";
|
mes.count = 0;
|
mes.Message = "查询成功！";
|
mes.data = data;
|
return mes;
|
}
|
else
|
{
|
mes.code = "300";
|
mes.count = 0;
|
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 RolePermissionSearchRoleMenuButton(string rolecode, string type)
|
{
|
var dynamicParams = new DynamicParameters();
|
string sql = "";
|
try
|
{
|
//查询所有默认菜单及按钮、数据
|
sql = @"select jsondate from TAuthority where rolecode=@rolecode and type=@type";
|
dynamicParams.Add("@rolecode", rolecode);
|
dynamicParams.Add("@type", type);
|
var data = DapperHelper.selectdata(sql, dynamicParams);
|
mes.code = "200";
|
mes.count = 0;
|
mes.Message = "查询成功！";
|
mes.data = data;
|
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 RolePermissionSava(string json, string rolecode, string type, User us)
|
{
|
var dynamicParams = new DynamicParameters();
|
List<object> list = new List<object>();
|
string sql = "";
|
try
|
{
|
list.Clear();
|
//删除权限关系表
|
sql = @"delete from TAuthority where rolecode=@rolecode and type=@type";
|
list.Add(new { str = sql, parm = new { rolecode = rolecode.ToString(), type = type } });
|
//重新写入权限关系表
|
sql = @"insert into TAuthority(rolecode,jsondate,type) values(@rolecode,@jsondate,@type)";
|
list.Add(new { str = sql, parm = new { rolecode = rolecode, jsondate = json.ToString(), type = type } });
|
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;
|
}
|
}
|
catch (Exception e)
|
{
|
mes.code = "300";
|
mes.count = 0;
|
mes.Message = e.Message;
|
mes.data = null;
|
}
|
return mes;
|
}
|
#endregion
|
|
}
|
}
|
