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)
|
{
|
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 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 + " order by T.lm_date desc";
|
var data = DapperHelper.selectdata(sql, dynamicParams);
|
mes.code = "200";
|
mes.message = "查询成功!";
|
mes.count = data.Rows.Count;
|
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 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, "新增", "新增了组织:" + OrganCode, 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, "修改", "修改了组织:" + OrganCode, 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, "新增", "删除了组织:" + orgcode, 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, "新增", "新增了岗位:" + postcode, 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, "修改", "修改了岗位:" + postcode, 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, "删除", "删除了岗位:" + postcode, 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
|
where 1=1 " + 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, "新增", "新增了用户:" + 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, "修改", "修改了用户:" + 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, "删除", "删除了用户:" + 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, "新增", "新增了班组:" + groupcode, 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, "修改", "修改了班组:" + groupcode, 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, "删除", "删除了班组:" + groupcode, 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, "新增", "新增了角色:" + ((List<string>)mes.data)[0].ToString(), 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, "修改", "修改了角色名称:" + rolecode, 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, "删除", "删除了角色:" + rolecode, 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, "取消", "取消了角色关联的用户:" + string.Join(",", user), 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, "添加", "添加了角色关联的用户:" + string.Join(",", user), 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 is_show='Y' and 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, "分配", "分配了角色权限:" + "-->角色:" + rolecode, 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 TCunstomerClassTree()
|
{
|
string sql = "";
|
try
|
{
|
//获取往来单位分类信息
|
sql = @"select code,name,idparent,data_sources from TCustomerType ";
|
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 TCunstomerClassTreeAddUpdate(string data_sources, string customerclasscode, string customerclassname, string parentcode, string OperType, User us)
|
{
|
string sql = "";
|
List<object> list = new List<object>();
|
var dynamicParams = new DynamicParameters();
|
try
|
{
|
if (parentcode != "" || parentcode != null) //往来单位分类上级编码为空
|
{
|
sql = @"select * from TCustomer where customertype=@parentcode";
|
dynamicParams.Add("@parentcode", parentcode);
|
var data0 = DapperHelper.selectdata(sql, dynamicParams);
|
if (data0.Rows.Count > 0)
|
{
|
mes.code = "300";
|
mes.count = 0;
|
mes.message = "往来单位分类上级不能是已有往来单位的分类，请重新输入！";
|
mes.data = null;
|
return mes;
|
}
|
}
|
if (OperType == "Add")
|
{
|
//判断往来单位分类编码是否重复
|
sql = @"select * from TCustomerType where code=@customerclasscode";
|
dynamicParams.Add("@customerclasscode", customerclasscode);
|
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 = @"select * from TCustomerType where name=@customerclassname";
|
dynamicParams.Add("@customerclassname", customerclassname);
|
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 = @"insert into TCustomerType(code,name,idparent,lm_user,lm_date,data_sources)
|
values(@code,@name,@idparent,@lm_user,@lm_date,@data_sources)";
|
list.Add(new
|
{
|
str = sql,
|
parm = new
|
{
|
code = customerclasscode,
|
name = customerclassname,
|
idparent = parentcode,
|
lm_user = us.usercode,
|
lm_date = DateTime.Now.ToString(),
|
data_sources = data_sources
|
}
|
});
|
bool aa = DapperHelper.DoTransaction(list);
|
if (aa)
|
{
|
//写入操作记录表
|
LogHelper.DbOperateLog(us.usercode, "新增", "新增了往来单位分类:" + customerclasscode, 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 = @"select * from TCustomerType where code<>@customerclasscode and name=@customerclassname";
|
dynamicParams.Add("@customerclasscode", customerclasscode);
|
dynamicParams.Add("@customerclassname", customerclassname);
|
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 TCustomerType set name=@name,idparent=@idparent,lm_user=@lm_user,lm_date=@lm_date where code=@code";
|
list.Add(new
|
{
|
str = sql,
|
parm = new
|
{
|
code = customerclasscode,
|
name = customerclassname,
|
idparent = parentcode,
|
lm_user = us.usercode,
|
lm_date = DateTime.Now.ToString(),
|
data_sources = data_sources
|
}
|
});
|
bool aa = DapperHelper.DoTransaction(list);
|
if (aa)
|
{
|
//写入操作记录表
|
LogHelper.DbOperateLog(us.usercode, "修改", "修改了往来单位分类:" + customerclasscode, 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 TCunstomerClassTreeDelete(string customerclasscode, User us)
|
{
|
string sql = "";
|
List<object> list = new List<object>();
|
var dynamicParams = new DynamicParameters();
|
try
|
{
|
string[] classcode = Array.ConvertAll<string, string>(customerclasscode.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries), s => s.ToString()); //string分割转string[]
|
//判断存货分类是否被存货信息引用
|
sql = @"select * from TCustomer where customertype in @customerclasscode";
|
dynamicParams.Add("@customerclasscode", classcode);
|
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 = @"delete TCustomerType where code in @code";
|
list.Add(new
|
{
|
str = sql,
|
parm = new
|
{
|
code = classcode
|
}
|
});
|
bool aa = DapperHelper.DoTransaction(list);
|
if (aa)
|
{
|
//写入操作记录表
|
LogHelper.DbOperateLog(us.usercode, "删除", "删除了往来单位分类:" + string.Join(",", classcode), 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 TCunstomerData(string customerclasscode, string customercode, string customername, int startNum, int endNum, string prop, string order)
|
{
|
var dynamicParams = new DynamicParameters();
|
string search = "", sql = "";
|
IEnumerable<object> data;
|
try
|
{
|
if (customerclasscode != "" && customerclasscode != null)
|
{
|
string[] classcode = Array.ConvertAll<string, string>(customerclasscode.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries), s => s.ToString()); //string分割转string[]
|
search += "and C.customertype in @classcode";
|
dynamicParams.Add("@classcode", classcode);
|
}
|
if (customercode != "" && customercode != null)
|
{
|
search += "and C.code like '%'+@customercode+'%' ";
|
dynamicParams.Add("@customercode", customercode);
|
}
|
if (customername != "" && customername != null)
|
{
|
search += "and C.name like '%'+@customername+'%' ";
|
dynamicParams.Add("@customername", customername);
|
}
|
sql = @"select C.code,C.name,C.status,C.conttacts,C.conttphone,C.addr,C.customertype as customertypecode,T.name as customertypename,
|
C.lm_user as usercode,U.username,C.lm_date,C.data_sources
|
from TCustomer C
|
left join TCustomerType T on C.customertype=T.code
|
left join TUser U on C.lm_user=U.usercode
|
where 1=1 " + search;
|
// --------------查询指定数据--------------
|
var total = 0; //总条数
|
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 TCunstomerAddUpdate(string data_sources, string customercode, string customername, string customerclasscode, string status, string conttacts, string conttphone, string addr, string OperType, User us)
|
{
|
string sql = "";
|
List<object> list = new List<object>();
|
var dynamicParams = new DynamicParameters();
|
try
|
{
|
if (OperType == "Add")
|
{
|
//判断往来单位编码是否重复
|
sql = @"select * from TCustomer where code=@customercode";
|
dynamicParams.Add("@customercode", customercode);
|
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 = @"select * from TCustomer where name=@customername";
|
dynamicParams.Add("@customername", customername);
|
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 = @"insert into TCustomer(code,name,status,conttacts,conttphone,addr,customertype,lm_user,lm_date,data_sources)
|
values(@code,@name,@status,@conttacts,@conttphone,@addr,@customertype,@lm_user,@lm_date,@data_sources)";
|
list.Add(new
|
{
|
str = sql,
|
parm = new
|
{
|
code = customercode,
|
name = customername,
|
status = status,
|
conttacts = conttacts,
|
conttphone = conttphone,
|
addr = addr,
|
customertype = customerclasscode,
|
lm_user = us.usercode,
|
lm_date = DateTime.Now.ToString(),
|
data_sources = data_sources
|
}
|
});
|
bool aa = DapperHelper.DoTransaction(list);
|
if (aa)
|
{
|
//写入操作记录表
|
LogHelper.DbOperateLog(us.usercode, "新增", "新增了往来单位:" + customercode, 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 = @"select * from TCustomer where code<>@code and name=@name";
|
dynamicParams.Add("@code", customercode);
|
dynamicParams.Add("@name", customername);
|
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 TCustomer set name=@name,status=@status,conttacts=@conttacts,conttphone=@conttphone,addr=@addr,customertype=@customertype,lm_user=@lm_user,lm_date=@lm_date
|
where code=@code";
|
list.Add(new
|
{
|
str = sql,
|
parm = new
|
{
|
code = customercode,
|
name = customername,
|
status = status,
|
conttacts = conttacts,
|
conttphone = conttphone,
|
addr = addr,
|
customertype = customerclasscode,
|
lm_user = us.usercode,
|
lm_date = DateTime.Now.ToString()
|
}
|
});
|
bool aa = DapperHelper.DoTransaction(list);
|
if (aa)
|
{
|
//写入操作记录表
|
LogHelper.DbOperateLog(us.usercode, "修改", "修改了往来单位:" + customercode, 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 TCunstomerDelete(string data_sources, string customercode, User us)
|
{
|
string sql = "";
|
List<object> list = new List<object>();
|
var dynamicParams = new DynamicParameters();
|
try
|
{
|
//判断往来单位是否被单据引用
|
|
//删除存货档案
|
sql = @"delete TCustomer where code=@code and data_sources=@data_sources";
|
list.Add(new
|
{
|
str = sql,
|
parm = new
|
{
|
code = customercode,
|
data_sources = data_sources
|
}
|
});
|
bool aa = DapperHelper.DoTransaction(list);
|
if (aa)
|
{
|
//写入操作记录表
|
LogHelper.DbOperateLog(us.usercode, "删除", "删除了往来单位:" + customercode, 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 TSecStckData(string code, string name, string status, string ishasPosition, int startNum, int endNum, string prop, string order)
|
{
|
var dynamicParams = new DynamicParameters();
|
string search = "", sql = "";
|
IEnumerable<object> data;
|
try
|
{
|
if (code != "" && code != null)
|
{
|
search += "and S.code like '%'+@code+'%' ";
|
dynamicParams.Add("@code", code);
|
}
|
if (name != "" && name != null)
|
{
|
search += "and S.name like '%'+@name+'%' ";
|
dynamicParams.Add("@name", name);
|
}
|
if (status != "" && status != null)
|
{
|
search += "and S.status=@status ";
|
dynamicParams.Add("@status", status);
|
}
|
if (ishasPosition != "" && ishasPosition != null)
|
{
|
search += "and S.ishasPosition=@ishasPosition ";
|
dynamicParams.Add("@ishasPosition", ishasPosition);
|
}
|
sql = @"select S.code,S.name,S.status,S.ishasPosition,S.description,S.lm_user as usercode,U.username,S.lm_date,S.data_sources
|
from TSecStck S
|
left join TUser U on S.lm_user=U.usercode
|
where 1=1 " + search;
|
// --------------查询指定数据--------------
|
var total = 0; //总条数
|
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 TSecStckAddUpdate(string data_sources, string stckcode, string stckname, string ishaspostion, string status, string description, string OperType, User us)
|
{
|
string sql = "";
|
List<object> list = new List<object>();
|
var dynamicParams = new DynamicParameters();
|
try
|
{
|
if (OperType == "Add")
|
{
|
//判断仓库编码是否重复
|
sql = @"select * from TSecStck where code=@stckcode";
|
dynamicParams.Add("@stckcode", stckcode);
|
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 = @"select * from TSecStck where name=@stckname";
|
dynamicParams.Add("@stckname", stckname);
|
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 = @"insert into TSecStck(code,name,status,ishasPosition,description,lm_user,lm_date,data_sources)
|
values(@code,@name,@status,@ishasPosition,@description,@lm_user,@lm_date,@data_sources)";
|
list.Add(new
|
{
|
str = sql,
|
parm = new
|
{
|
code = stckcode,
|
name = stckname,
|
status = status,
|
ishasPosition = ishaspostion,
|
description = description,
|
lm_user = us.usercode,
|
lm_date = DateTime.Now.ToString(),
|
data_sources = data_sources
|
}
|
});
|
bool aa = DapperHelper.DoTransaction(list);
|
if (aa)
|
{
|
//写入操作记录表
|
LogHelper.DbOperateLog(us.usercode, "新增", "新增了仓库:" + stckcode, 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 = @"select * from TSecStck where code<>@code and name=@name";
|
dynamicParams.Add("@code", stckcode);
|
dynamicParams.Add("@name", stckname);
|
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 TSecStck set name=@name,status=@status,ishasPosition=@ishasPosition,description=@description,lm_user=@lm_user,lm_date=@lm_date
|
where code=@code";
|
list.Add(new
|
{
|
str = sql,
|
parm = new
|
{
|
code = stckcode,
|
name = stckname,
|
status = status,
|
ishasPosition = ishaspostion,
|
description = description,
|
lm_user = us.usercode,
|
lm_date = DateTime.Now.ToString()
|
}
|
});
|
if (status == "1") //仓库停用时,下面所有的仓位停用
|
{
|
//更新库位状态为禁用
|
sql = @"update TSecLoca set status=@status where idwarehouse=@code";
|
list.Add(new
|
{
|
str = sql,
|
parm = new
|
{
|
status = status,
|
code = stckcode
|
}
|
});
|
}
|
|
bool aa = DapperHelper.DoTransaction(list);
|
if (aa)
|
{
|
//写入操作记录表
|
LogHelper.DbOperateLog(us.usercode, "修改", "修改了仓库:" + stckcode, 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 TSecStckDelete(string data_sources, string stckcode, User us)
|
{
|
string sql = "";
|
List<object> list = new List<object>();
|
var dynamicParams = new DynamicParameters();
|
try
|
{
|
//判断仓库是否有库位
|
sql = @"select * from TSecLoca where idwarehouse=@stckcode";
|
dynamicParams.Add("@stckcode", stckcode);
|
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 TSecStck where code=@code and data_sources=@data_sources";
|
list.Add(new
|
{
|
str = sql,
|
parm = new
|
{
|
code = stckcode,
|
data_sources = data_sources
|
}
|
});
|
bool aa = DapperHelper.DoTransaction(list);
|
if (aa)
|
{
|
//写入操作记录表
|
LogHelper.DbOperateLog(us.usercode, "删除", "删除了仓库:" + stckcode, 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 TSecLocaTree()
|
{
|
string sql = "";
|
try
|
{
|
//获取开启仓位管控的仓库信息
|
sql = @"select code,name,'-1' as idparent,'' as warhouse,'0' as depth from TSecStck where ishasPosition='1'
|
union all
|
select code,name,case when L.idparent is NULL or L.idparent='' then '-1' else L.idparent end as idparent,idwarehouse,depth
|
from TSecLoca L ";
|
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 TSecLocaData(string flag, string stckcode, string locacode, string locaname, int startNum, int endNum, string prop, string order)
|
{
|
var dynamicParams = new DynamicParameters();
|
string search = "", sql = "";
|
IEnumerable<object> data;
|
try
|
{
|
if (flag == "-1" && stckcode == null)
|
{
|
mes.code = "200";
|
mes.count = 0;
|
mes.message = "暂无数据!";
|
mes.data = null;
|
return mes;
|
}
|
string[] classcode = Array.ConvertAll<string, string>(stckcode.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries), s => s.ToString()); //string分割转string[]
|
switch (flag)
|
{
|
case "-1": //全部
|
search += " and L.idparent is null or L.idparent='' and L.idwarehouse in @idwarehouse ";
|
dynamicParams.Add("@idwarehouse", classcode);
|
break;
|
case "0": //仓库
|
search += " and L.depth='1' and L.idwarehouse in @idwarehouse ";
|
dynamicParams.Add("@idwarehouse", classcode);
|
break;
|
default: //仓位
|
search += " and L.idparent in @idparent";
|
dynamicParams.Add("@idparent", classcode);
|
break;
|
}
|
if (locacode != "" && locacode != null)
|
{
|
search += "and L.code like '%'+@locacode+'%' ";
|
dynamicParams.Add("@locacode", locacode);
|
}
|
if (locaname != "" && locaname != null)
|
{
|
search += "and L.name like '%'+@locaname+'%' ";
|
dynamicParams.Add("@locaname", locaname);
|
}
|
sql = @"select L.code,L.name,L.status,L.description,L.idwarehouse as stckcode,T.name as stckname,
|
L.idparent as parentcode,S.name as parentname,L.data_sources,L.lm_user as usercode,U.username,L.lm_date
|
from TSecLoca L
|
left join TSecStck T on L.idwarehouse=T.code
|
left join TSecLoca S on L.idparent=S.code
|
left join TUser U on L.lm_user=U.usercode
|
where 1=1 " + search;
|
// --------------查询指定数据--------------
|
var total = 0; //总条数
|
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 TSecLocaAddUpdate(string data_sources, string locacode, string locaname, string stckcode, string parentlocacode, string depth, string status, string description, string OperType, User us)
|
{
|
string sql = "";
|
List<object> list = new List<object>();
|
var dynamicParams = new DynamicParameters();
|
try
|
{
|
if (OperType == "Add")
|
{
|
//判断仓位编码是否重复
|
sql = @"select * from TSecLoca where code=@locacode";
|
dynamicParams.Add("@locacode", locacode);
|
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 = @"select * from TSecLoca where name=@locaname";
|
dynamicParams.Add("@locaname", locaname);
|
var data1 = DapperHelper.selectdata(sql, dynamicParams);
|
if (data1.Rows.Count > 0)
|
{
|
mes.code = "300";
|
mes.count = 0;
|
mes.message = "仓位已存在,请重新输入!";
|
mes.data = null;
|
return mes;
|
}
|
if (depth == "1") //层级为1时,置空上级编码
|
{
|
parentlocacode = "";
|
}
|
//写入仓位
|
sql = @"insert into TSecLoca(code,name,idwarehouse,idparent,status,description,lm_user,lm_date,data_sources,depth)
|
values(@code,@name,@idwarehouse,@idparent,@status,@description,@lm_user,@lm_date,@data_sources,@depth)";
|
list.Add(new
|
{
|
str = sql,
|
parm = new
|
{
|
code = locacode,
|
name = locaname,
|
idwarehouse = stckcode,
|
idparent = parentlocacode,
|
status = status,
|
description = description,
|
lm_user = us.usercode,
|
lm_date = DateTime.Now.ToString(),
|
data_sources = data_sources,
|
depth = depth
|
}
|
});
|
bool aa = DapperHelper.DoTransaction(list);
|
if (aa)
|
{
|
//写入操作记录表
|
LogHelper.DbOperateLog(us.usercode, "新增", "新增了仓位:" + locacode, 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 (locacode == parentlocacode)
|
{
|
mes.code = "300";
|
mes.count = 0;
|
mes.message = "上级货位不能是库位本身！";
|
mes.data = null;
|
return mes;
|
}
|
//判断往来单位名称是否重复
|
sql = @"select * from TSecLoca where code<>@code and name=@name";
|
dynamicParams.Add("@code", locacode);
|
dynamicParams.Add("@name", locaname);
|
var data1 = DapperHelper.selectdata(sql, dynamicParams);
|
if (data1.Rows.Count > 0)
|
{
|
mes.code = "300";
|
mes.count = 0;
|
mes.message = "仓位名称已存在,请重新输入!";
|
mes.data = null;
|
return mes;
|
}
|
if (depth == "1") //层级为1时,置空上级编码
|
{
|
parentlocacode = "";
|
}
|
//更新存货分类
|
sql = @"update TSecLoca set name=@name,idwarehouse=@idwarehouse,idparent=@idparent,depth=@depth,status=@status,description=@description,lm_user=@lm_user,lm_date=@lm_date
|
where code=@code";
|
list.Add(new
|
{
|
str = sql,
|
parm = new
|
{
|
code = locacode,
|
name = locaname,
|
idwarehouse = stckcode,
|
idparent = parentlocacode,
|
depth = depth,
|
status = status,
|
description = description,
|
lm_user = us.usercode,
|
lm_date = DateTime.Now.ToString()
|
}
|
});
|
if (status == "1") //仓位停用时,下面所有的仓位停用
|
{
|
//更新库位状态为禁用
|
sql = @"update TSecLoca set status=@status where idparent=@code";
|
list.Add(new
|
{
|
str = sql,
|
parm = new
|
{
|
status = status,
|
code = locacode
|
}
|
});
|
}
|
bool aa = DapperHelper.DoTransaction(list);
|
if (aa)
|
{
|
//写入操作记录表
|
LogHelper.DbOperateLog(us.usercode, "修改", "修改了仓位:" + locacode, 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 TSecLocaDelete(string data_sources, string locacode, User us)
|
{
|
string sql = "";
|
List<object> list = new List<object>();
|
var dynamicParams = new DynamicParameters();
|
try
|
{
|
//判断当前仓位下是否有子项
|
sql = @"select * from TSecLoca where idparent=@locacode";
|
dynamicParams.Add("@locacode", locacode);
|
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 TSecLoca where code=@code and data_sources=@data_sources";
|
list.Add(new
|
{
|
str = sql,
|
parm = new
|
{
|
code = locacode,
|
data_sources = data_sources
|
}
|
});
|
bool aa = DapperHelper.DoTransaction(list);
|
if (aa)
|
{
|
//写入操作记录表
|
LogHelper.DbOperateLog(us.usercode, "删除", "删除了仓位:" + locacode, 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 StepSearch(string stepcode, string stepname, string enable, string steptypecode, int startNum, int endNum, string prop, string order)
|
{
|
var dynamicParams = new DynamicParameters();
|
string search = "";
|
try
|
{
|
if (stepcode != "" && stepcode != null)
|
{
|
search += "and A.stepcode like '%'+@stepcode+'%' ";
|
dynamicParams.Add("@stepcode", stepcode);
|
}
|
if (stepname != "" && stepname != null)
|
{
|
search += "and A.stepname like '%'+@stepname+'%' ";
|
dynamicParams.Add("@stepname", stepname);
|
}
|
if (enable != "" && enable != null)
|
{
|
search += "and A.enable=@enable ";
|
dynamicParams.Add("@enable", enable);
|
}
|
if (steptypecode != "" && steptypecode != null)
|
{
|
search += "and A.flwtype=@steptypecode ";
|
dynamicParams.Add("@steptypecode", steptypecode);
|
}
|
if (search == "")
|
{
|
search = "and 1=1 ";
|
}
|
// --------------查询指定数据--------------
|
var total = 0; //总条数
|
var sql = @"select A.id,A.stepcode,A.stepname,A.flwtype,A.enable,A.descr,U.username as lm_user,A.lm_date
|
from TStep A
|
left join TUser U on A.lm_user=U.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 AddUpdateStep(string stepid, string stepcode, string stepname, string steptypecode, string enable, string description, User us, string operType)
|
{
|
var dynamicParams = new DynamicParameters();
|
List<object> list = new List<object>();
|
var sql = "";
|
try
|
{
|
if (operType == "Add")
|
{
|
sql = @"select * from TStep where stepcode=@stepcode";
|
dynamicParams.Add("@stepcode", stepcode);
|
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 TStep(stepcode,stepname,flwtype,enable,descr,lm_user,lm_date)
|
values(@stepcode,@stepname,@steptypecode,@enable,@description,@username,@CreateDate)";
|
dynamicParams.Add("@stepcode", stepcode);
|
dynamicParams.Add("@stepname", stepname);
|
dynamicParams.Add("@steptypecode", steptypecode);
|
dynamicParams.Add("@enable", enable);
|
dynamicParams.Add("@description", description);
|
dynamicParams.Add("@username", us.usercode);
|
dynamicParams.Add("@CreateDate", DateTime.Now.ToString());
|
int cont = DapperHelper.SQL(sql, dynamicParams);
|
if (cont > 0)
|
{
|
//写入操作记录表
|
LogHelper.DbOperateLog(us.usercode, "新增", "新增了工序:" + stepcode, 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")
|
{
|
list.Clear();
|
//如果当前工序使用状态位N(未启用),需判断当前工序是否被工单引用
|
if (enable == "N")
|
{
|
var sql0 = @"select * from TK_Wrk_Step A where A.step_code=@stepcode";
|
dynamicParams.Add("@stepcode", stepcode);
|
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 = @"update TStep set stepname=@stepname,flwtype=@steptypecode,enable=@enable,descr=@description,
|
lm_user=@username,lm_date=@CreateDate where id=@stepid";
|
list.Add(new { str = sql, parm = new { stepid = stepid, stepname = stepname, steptypecode = steptypecode, enable = enable, description = description, username = us.usercode, CreateDate = DateTime.Now.ToString() } });
|
bool aa = DapperHelper.DoTransaction(list);
|
if (aa)
|
{
|
//写入操作记录表
|
LogHelper.DbOperateLog(us.usercode, "修改", "修改了工序:" + stepcode, 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 DeleteStep(string stepcode, User us)
|
{
|
var sql = "";
|
List<object> list = new List<object>();
|
var dynamicParams = new DynamicParameters();
|
try
|
{
|
list.Clear();
|
//当前工序是否关联工单
|
sql = @"select * from TK_Wrk_Step A where A.step_code=@stepcode";
|
dynamicParams.Add("@stepcode", stepcode);
|
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 TStep where stepcode=@stepcode";
|
list.Add(new { str = sql, parm = new { stepcode = stepcode } });
|
bool aa = DapperHelper.DoTransaction(list);
|
if (aa)
|
{
|
//写入操作记录表
|
LogHelper.DbOperateLog(us.usercode, "删除", "删除了工序:" + stepcode, 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
|
|
}
|
}
|
