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 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,string torgtype_code)
|
{
|
var dynamicParams = new DynamicParameters();
|
string search = "";
|
try
|
{
|
if (torgtype_code != "" && torgtype_code != null)
|
{
|
search += "and T.torgtype_code=@torgtype_code ";
|
dynamicParams.Add("@torgtype_code", torgtype_code);
|
}
|
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.torgtype_code,Y.torgtype_name,T.status,T.leve,T.torg_seq,
|
T.lm_user as usercode,U.username,T.lm_date
|
from TOrganization T
|
left join TOrganType Y on T.torgtype_code=Y.torgtype_code
|
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 torgtype_code, 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,torgtype_code,status,leve,torg_seq,lm_user,lm_date)
|
values(@parent_id,@torg_code,@torg_name,@torgtype_code,@status,@leve,@torg_seq,@lm_user,@lm_date)";
|
list.Add(new
|
{
|
str = sql,
|
parm = new
|
{
|
parent_id = storgcode,
|
torg_code = OrganCode,
|
torg_name = OrganName,
|
torgtype_code= torgtype_code,
|
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,torgtype_code=@torgtype_code,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,
|
torgtype_code= torgtype_code,
|
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,menu_seq,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 StepSelect()
|
{
|
string sql = "";
|
try
|
{
|
//获取工序数据
|
sql = @"select stepcode,stepname from TStep where is_delete<>'1' and enable='Y'";
|
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 RouteSearch(string routecode, string routename, string description, string createuser, int startNum, int endNum, string prop, string order)
|
{
|
var dynamicParams = new DynamicParameters();
|
string search = "";
|
try
|
{
|
if (routecode != "" && routecode != null)
|
{
|
search += "and A.code like '%'+@routecode+'%' ";
|
dynamicParams.Add("@routecode", routecode);
|
}
|
if (routename != "" && routename != null)
|
{
|
search += "and A.name like '%'+@routename+'%' ";
|
dynamicParams.Add("@routename", routename);
|
}
|
if (description != "" && description != null)
|
{
|
search += "and A.description like '%'+@description+'%' ";
|
dynamicParams.Add("@description", description);
|
}
|
if (createuser != "" && createuser != null)
|
{
|
search += "and U.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.description,A.enable,U.username as lm_user,A.lm_date from TFlw_Rout 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 ViewRoute(string routecode)
|
{
|
string sql = "";
|
var dynamicParams = new DynamicParameters();
|
List<RoutEdit> list = new List<RoutEdit>();
|
try
|
{
|
//获取工艺路线信息
|
sql = @"select code,name,description,enable
|
from TFlw_Rout
|
where code=@routecode and is_delete<>'1'";
|
dynamicParams.Add("@routecode", routecode);
|
var data = DapperHelper.selectdata(sql, dynamicParams);
|
for (int i = 0; i < data.Rows.Count; i++)
|
{
|
RoutEdit rout = new RoutEdit();
|
rout.code = data.Rows[i]["CODE"].ToString();
|
rout.name = data.Rows[i]["NAME"].ToString();
|
rout.enable = data.Rows[i]["ENABLE"].ToString();
|
rout.description = data.Rows[i]["DESCRIPTION"].ToString();
|
|
//根据工艺路线编码获取关联的工序信息
|
sql = @"select A.seq,B.stepcode,B.stepname,B.enable from TFlw_Rtdt A
|
inner join TStep B on A.step_code=B.stepcode
|
where A.rout_code=@route_code and B.is_delete<>'1' order by A.seq asc";
|
dynamicParams.Add("@route_code", rout.code);
|
var data0 = DapperHelper.selectdata(sql, dynamicParams);
|
rout.Data = data0;
|
list.Add(rout);
|
|
}
|
mes.code = "200";
|
mes.message = "查询成功!";
|
mes.data = list;
|
}
|
catch (Exception e)
|
{
|
mes.code = "300";
|
mes.count = 0;
|
mes.message = e.Message;
|
mes.data = null;
|
}
|
return mes;
|
}
|
#endregion
|
|
#region[工艺路线新增]
|
public static ToMessage AddUpdateRoute(string id, string opertype, User us, RoutEdit json)
|
{
|
var sql = "";
|
var dynamicParams = new DynamicParameters();
|
List<object> list = new List<object>();
|
try
|
{
|
if (opertype == "Add")
|
{
|
var sql0 = @"select * from TFlw_Rout where code=@code";
|
dynamicParams.Add("@code", json.code);
|
var data = DapperHelper.selectdata(sql0, dynamicParams);
|
if (data.Rows.Count > 0)
|
{
|
mes.code = "300";
|
mes.count = 0;
|
mes.message = "操作失败,编码重复!";
|
mes.data = null;
|
return mes;
|
}
|
//新增工艺路线表
|
sql = @"insert into TFlw_Rout(code,name,description,enable,lm_user,lm_date) values(@code,@name,@description,@enable,@lm_user,@lm_date)";
|
list.Add(new { str = sql, parm = new { code = json.code, name = json.name, description = json.description, enable = json.enable, lm_user = us.usercode, lm_date = DateTime.Now.ToString() } });
|
for (int i = 0; i < json.Data.Rows.Count; i++)
|
{
|
string is_firststep = "N"; //是否首道工序
|
string is_laststep = "N"; //是否末道工序
|
if (json.Data.Rows[i]["SEQ"].ToString() == "1") //是否首道工序
|
{
|
is_firststep = "Y";
|
}
|
if (Convert.ToInt32(json.Data.Rows[i]["SEQ"].ToString()) == json.Data.Rows.Count) //是否末道工序
|
{
|
is_laststep = "Y";
|
}
|
//新增工艺路线关联工序表
|
sql = @"insert TFlw_Rtdt (rout_code,seq,step_code,first_choke,last_choke,lm_user,lm_date) values(@rout_code,@seq,@step_code,@first_choke,@last_choke,@lm_user,@lm_date)";
|
list.Add(new { str = sql, parm = new { rout_code = json.code, seq = Convert.ToInt32(json.Data.Rows[i]["SEQ"].ToString()), step_code = json.Data.Rows[i]["STEPCODE"].ToString(), first_choke = is_firststep, last_choke = is_laststep, lm_user = us.usercode, lm_date = DateTime.Now.ToString() } });
|
}
|
bool aa = DapperHelper.DoTransaction(list);
|
if (aa)
|
{
|
//写入操作记录表
|
LogHelper.DbOperateLog(us.usercode, "新增", "新增了工艺路线:" + json.code, 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 DeleteRoute(string routecode, User us)
|
{
|
var sql = "";
|
List<object> list = new List<object>();
|
var dynamicParams = new DynamicParameters();
|
try
|
{
|
//判断工艺路路线是否被存货绑定
|
sql = @"select * from TMateriel_Route where route_code=@routecode";
|
dynamicParams.Add("@routecode", routecode);
|
var data_0 = DapperHelper.selectdata(sql, dynamicParams);
|
if (data_0.Rows.Count > 0)
|
{
|
mes.code = "300";
|
mes.count = 0;
|
mes.message = "存货档案已关联工艺路线，不允许删除!";
|
mes.data = null;
|
return mes;
|
}
|
//判断工艺路线是否被工单引用(被引用则不能删除)
|
sql = @"select * from TK_Wrk_Man where route_code=@routecode";
|
dynamicParams.Add("@routecode", routecode);
|
var data = DapperHelper.selectdata(sql, dynamicParams);
|
if (data.Rows.Count > 0)
|
{
|
mes.code = "300";
|
mes.count = 0;
|
mes.message = "工艺路线已被工单引用，不允许删除!";
|
mes.data = null;
|
return mes;
|
}
|
else
|
{
|
//判断当前工艺路线是否有设置节拍工价(有设置，提示先删除节拍工价设置)
|
sql = @"select * from TPrteStep_Price where route_code=@routecode";
|
dynamicParams.Add("@routecode", routecode);
|
var data0 = DapperHelper.selectdata(sql, dynamicParams);
|
if (data0.Rows.Count > 0)
|
{
|
mes.code = "300";
|
mes.count = 0;
|
mes.message = "工艺路线已设置节拍工价，请先删除设置!";
|
mes.data = null;
|
return mes;
|
}
|
else
|
{
|
//删除工艺路线关联工序表
|
sql = @"delete TFlw_Rtdt where rout_code=@routecode";
|
list.Add(new { str = sql, parm = new { routecode = routecode } });
|
//删除工艺路线表
|
sql = @"delete TFlw_Rout where code=@routecode";
|
list.Add(new { str = sql, parm = new { routecode = routecode } });
|
//删除物料关联工艺路线表
|
sql = @"delete TMateriel_Route where route_code=@routecode";
|
list.Add(new { str = sql, parm = new { routecode = routecode } });
|
}
|
bool aa = DapperHelper.DoTransaction(list);
|
if (aa)
|
{
|
//写入操作记录表
|
LogHelper.DbOperateLog(us.usercode, "删除", "删除了工艺路线:" + routecode, us.usertype);
|
mes.code = "200";
|
mes.count = 0;
|
mes.message = "删除成功!";
|
mes.data = null;
|
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;
|
}
|
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.is_eqp,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 operType, User us, TFlw_Rteqp tflw_rteqp)
|
{
|
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", tflw_rteqp.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)";
|
list.Add(new
|
{
|
str = sql,
|
parm = new
|
{
|
stepcode = tflw_rteqp.stepcode,
|
stepname = tflw_rteqp.stepname,
|
steptypecode = tflw_rteqp.steptypecode,
|
enable = tflw_rteqp.enable,
|
description = tflw_rteqp.description,
|
username = us.usercode,
|
CreateDate = DateTime.Now.ToString()
|
}
|
});
|
//写入工序关联设备表
|
if (tflw_rteqp.children.Count > 0)
|
{
|
for (int i = 0; i < tflw_rteqp.children.Count; i++)
|
{
|
sql = @"insert into TFlw_Rteqp(eqp_code,step_code,style,lm_user,lm_date)
|
values(@eqp_code,@step_code,@style,@lm_user,@lm_date)";
|
list.Add(new
|
{
|
str = sql,
|
parm = new
|
{
|
eqp_code = tflw_rteqp.children[i].eqpcode,
|
step_code = tflw_rteqp.children[i].stepcode,
|
style = tflw_rteqp.children[i].style,
|
lm_user = us.usercode,
|
lm_date = DateTime.Now.ToString()
|
}
|
});
|
}
|
//标记物料表关联工艺路线标识
|
sql = @"update TStep set is_eqp='Y' where stepcode=@stepcode";
|
list.Add(new { str = sql, parm = new { stepcode = tflw_rteqp.stepcode } });
|
}
|
bool aa = DapperHelper.DoTransaction(list);
|
if (aa)
|
{
|
//写入操作记录表
|
LogHelper.DbOperateLog(us.usercode, "新增", "新增了工序:" + tflw_rteqp.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 (tflw_rteqp.enable == "N")
|
{
|
//需判断当前工序是否被工单引用
|
sql = @"select * from TK_Wrk_Step A where A.step_code=@stepcode";
|
dynamicParams.Add("@stepcode", tflw_rteqp.stepcode);
|
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 step_code as stepcode,eqp_code as eqpcode,style from TFlw_Rteqp where step_code=@stepcode";
|
dynamicParams.Add("@stepcode", tflw_rteqp.stepcode);
|
var data = DapperHelper.select<StepEqpData>(sql, dynamicParams);
|
// 历史中有而提交中没有的元素(执行删除)
|
var inBNotInA = data.Where(b => !tflw_rteqp.children.Any(a => a.eqpcode == b.eqpcode)).ToList();
|
for (int i = 0; i < inBNotInA.Count; i++)
|
{
|
//删除工序关联设备表
|
sql = @"delete TFlw_Rteqp where step_code=@step_code and eqp_code=@eqp_code";
|
list.Add(new { str = sql, parm = new { step_code = inBNotInA[i].stepcode, eqp_code= inBNotInA[i].eqpcode } });
|
//删除设备节拍工价表
|
sql = @"delete TPrteEqp_Stad where step_code=@step_code and eqp_code=@eqp_code";
|
list.Add(new { str = sql, parm = new { step_code = inBNotInA[i].stepcode, eqp_code = inBNotInA[i].eqpcode } });
|
}
|
// 提交中有而历史中没有的元素(执行写入)
|
var inANotInB = tflw_rteqp.children.Where(a => !data.Any(b => b.eqpcode == a.eqpcode)).ToList();
|
for (int i = 0; i < inANotInB.Count; i++)
|
{
|
//写入工序关联设备表
|
sql = @"insert into TFlw_Rteqp(eqp_code,step_code,style,lm_user,lm_date)
|
values(@eqp_code,@step_code,@style,@lm_user,@lm_date)";
|
list.Add(new
|
{
|
str = sql,
|
parm = new
|
{
|
eqp_code = inANotInB[i].eqpcode,
|
step_code = inANotInB[i].stepcode,
|
style = inANotInB[i].style,
|
lm_user = us.usercode,
|
lm_date = DateTime.Now.ToString()
|
}
|
});
|
}
|
if (tflw_rteqp.children.Count > 0)
|
{
|
//标记物料表关联工艺路线标识
|
sql = @"update TStep set is_eqp='Y' where stepcode=@stepcode";
|
list.Add(new { str = sql, parm = new { stepcode = tflw_rteqp.stepcode } });
|
}
|
if (tflw_rteqp.children.Count <= 0)
|
{
|
//标记物料表关联工艺路线标识
|
sql = @"update TStep set is_eqp='N' where stepcode=@stepcode";
|
list.Add(new { str = sql, parm = new { stepcode = tflw_rteqp.stepcode } });
|
}
|
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 = tflw_rteqp.id, stepname = tflw_rteqp.stepname, steptypecode = tflw_rteqp.steptypecode, enable = tflw_rteqp.enable, description = tflw_rteqp.description, username = us.usercode, CreateDate = DateTime.Now.ToString() } });
|
bool aa = DapperHelper.DoTransaction(list);
|
if (aa)
|
{
|
//写入操作记录表
|
LogHelper.DbOperateLog(us.usercode, "修改", "修改了工序:" + tflw_rteqp.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 TPrteStep_Price A where A.step_code=@stepcode";
|
dynamicParams.Add("@stepcode", stepcode);
|
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 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 TFlw_Rteqp where step_code=@stepcode";
|
list.Add(new { str = sql, parm = new { stepcode = stepcode } });
|
//删除工序
|
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
|
|
#region[工序定义关联工作站查询]
|
public static ToMessage StepAssociationEqp(string stepcode, User us)
|
{
|
string sql = "";
|
var dynamicParams = new DynamicParameters();
|
List<StepEqp> list = new List<StepEqp>();
|
try
|
{
|
//获取工作站集合(车间、外协供应商,包含已绑定工作站标识)
|
sql = @"select distinct T.torg_code as wksp_code,T.torg_name as wksp_name,'E' as type,(case when B.eqp_code is null then 'N' else 'Y' end) flag
|
from TOrganization T
|
left join(
|
select distinct A.eqp_code,B.torg_code as wksp_code from TFlw_Rteqp A
|
inner join TEqpInfo B on A.eqp_code=B.code
|
where A.step_code=@stepcode and B.enable='Y'
|
) B on T.torg_code=B.wksp_code where T.torgtype_code='W'
|
UNION ALL
|
select distinct T.code as wksp_code,T.name as wksp_name,'W' as type,(case when B.customertype is null then 'N' else 'Y' end) flag
|
from TCustomerType T
|
left join(
|
select distinct A.eqp_code,B.customertype from TFlw_Rteqp A
|
inner join TCustomer B on A.eqp_code=B.code
|
where A.step_code=@stepcode
|
) B on T.code=B.customertype";
|
dynamicParams.Add("@stepcode", stepcode);
|
var data = DapperHelper.selectdata(sql, dynamicParams);
|
for (int i = 0; i < data.Rows.Count; i++)
|
{
|
StepEqp rout = new StepEqp();
|
rout.code = data.Rows[i]["WKSP_CODE"].ToString();
|
rout.name = data.Rows[i]["WKSP_NAME"].ToString();
|
rout.type = data.Rows[i]["TYPE"].ToString();
|
rout.flag = data.Rows[i]["FLAG"].ToString();
|
rout.children = new List<StepEqpCn>();
|
if (rout.type == "W") //外协供方
|
{
|
//根据外协供方标识编码查找外协供方信息(包含已关联标识)
|
sql = @"select A.code,A.name,'W' as type,(case when B.eqp_code is null then 'N' else 'Y' end) flag
|
from TCustomer A
|
left join(
|
select distinct A.eqp_code from TFlw_Rteqp A
|
inner join TCustomer B on A.eqp_code=B.code
|
where A.step_code=@stepcode and B.customertype=@wxcode
|
) B on A.code=B.eqp_code where A.customertype=@wxcode ";
|
dynamicParams.Add("@stepcode", stepcode);
|
dynamicParams.Add("@wxcode", rout.code);
|
var data0 = DapperHelper.selectdata(sql, dynamicParams);
|
for (int k = 0; k < data0.Rows.Count; k++)
|
{
|
StepEqpCn cn = new StepEqpCn();
|
cn.code = data0.Rows[k]["CODE"].ToString();//外协供方编码
|
cn.name = data0.Rows[k]["NAME"].ToString();//外协供方名称
|
cn.type = data0.Rows[k]["TYPE"].ToString();//工作站类型(E:设备 W:外协供方)
|
cn.flag = data0.Rows[k]["FLAG"].ToString();//关联标识
|
rout.children.Add(cn);
|
}
|
list.Add(rout);
|
}
|
else
|
{
|
//根据车间编码查找设备(包含已关联标识)
|
sql = @"select A.code,A.name,'E' as type,(case when B.eqp_code is null then 'N' else 'Y' end) flag
|
from TEqpInfo A
|
left join(
|
select distinct A.eqp_code from TFlw_Rteqp A
|
inner join TEqpInfo B on A.eqp_code=B.code
|
where A.step_code=@stepcode and B.enable='Y'
|
) B on A.code=B.eqp_code where A.torg_code=@wkspcode ";
|
dynamicParams.Add("@stepcode", stepcode);
|
dynamicParams.Add("@wkspcode", data.Rows[i]["WKSP_CODE"].ToString());
|
var data0 = DapperHelper.selectdata(sql, dynamicParams);
|
for (int j = 0; j < data0.Rows.Count; j++)
|
{
|
StepEqpCn cn = new StepEqpCn();
|
cn.code = data0.Rows[j]["CODE"].ToString();//设备编码
|
cn.name = data0.Rows[j]["NAME"].ToString();//设备名称
|
cn.type = data0.Rows[j]["TYPE"].ToString();//工作站类型(E:设备 W:外协供方)
|
cn.flag = data0.Rows[j]["FLAG"].ToString();//关联标识
|
rout.children.Add(cn);
|
}
|
list.Add(rout);
|
}
|
}
|
mes.code = "200";
|
mes.message = "查询成功!";
|
mes.data = list;
|
}
|
catch (Exception e)
|
{
|
mes.code = "300";
|
mes.count = 0;
|
mes.message = e.Message;
|
mes.data = null;
|
}
|
return mes;
|
}
|
#endregion
|
|
#region [工序定义关联工作站提交]
|
public static ToMessage SaveStepAssociationEqp(string stepcode, User us, List<ObjectData> json)
|
{
|
var sql = "";
|
List<object> list = new List<object>();
|
var dynamicParams = new DynamicParameters();
|
try
|
{
|
list.Clear();
|
//根据工序查找历史设备
|
sql = @"select step_code as stepcode,eqp_code as eqpcode,style from TFlw_Rteqp where step_code=@stepcode";
|
dynamicParams.Add("@stepcode", stepcode);
|
var data = DapperHelper.select<StepEqpData>(sql, dynamicParams);
|
// 历史中有而提交中没有的元素(执行删除)
|
var inBNotInA = data.Where(b => !json.Any(a => a.code == b.eqpcode)).ToList();
|
for (int i = 0; i < inBNotInA.Count; i++)
|
{
|
//删除工序关联设备表
|
sql = @"delete TFlw_Rteqp where step_code=@step_code and eqp_code=@eqp_code";
|
list.Add(new { str = sql, parm = new { step_code = inBNotInA[i].stepcode, eqp_code = inBNotInA[i].eqpcode } });
|
//删除设备节拍工价表
|
sql = @"delete TPrteEqp_Stad where step_code=@step_code and eqp_code=@eqp_code";
|
list.Add(new { str = sql, parm = new { step_code = inBNotInA[i].stepcode, eqp_code = inBNotInA[i].eqpcode } });
|
}
|
// 提交中有而历史中没有的元素(执行写入)
|
var inANotInB = json.Where(a => !data.Any(b => b.eqpcode == a.code)).ToList();
|
for (int i = 0; i < inANotInB.Count; i++)
|
{
|
//写入工序关联设备表
|
sql = @"insert into TFlw_Rteqp(eqp_code,step_code,style,lm_user,lm_date)
|
values(@eqp_code,@step_code,@style,@lm_user,@lm_date)";
|
list.Add(new
|
{
|
str = sql,
|
parm = new
|
{
|
eqp_code = inANotInB[i].code,
|
step_code = stepcode,
|
style = inANotInB[i].name,
|
lm_user = us.usercode,
|
lm_date = DateTime.Now.ToString()
|
}
|
});
|
}
|
if (json.Count > 0)
|
{
|
//标记物料表关联工艺路线标识
|
sql = @"update TStep set is_eqp='Y' where stepcode=@stepcode";
|
list.Add(new { str = sql, parm = new { stepcode = stepcode } });
|
}
|
if (json.Count <= 0)
|
{
|
//标记物料表关联工艺路线标识
|
sql = @"update TStep set is_eqp='N' where stepcode=@stepcode";
|
list.Add(new { str = sql, parm = new { stepcode = stepcode } });
|
}
|
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 PartSelect()
|
{
|
string sql = "";
|
try
|
{
|
//获取物料数据
|
sql = @"select M.partcode,M.partname,M.partspec,T.code as uom_code,T.name as uom_name,
|
M.idunitgroup as stocktype_code,D.name as stocktype_name
|
from TMateriel_Info M
|
left join TUnit T on M.idunit=T.code
|
left join TUnitGroup D on M.idunitgroup=D.code";
|
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 PartSelectRpute(string partcode, User us)
|
{
|
string sql = "";
|
var dynamicParams = new DynamicParameters();
|
try
|
{
|
dynamic dynObj = JObject.Parse(us.mesSetting);
|
bool route = dynObj.route;
|
if (route) //工艺路线版
|
{
|
//通过产品编码查找关联的工艺路线信息
|
sql = @"select A.route_code,B.name as route_name
|
from TMateriel_Route A
|
inner join TFlw_Rout B on A.route_code=B.code
|
where A.materiel_code=@partcode and B.is_delete<>'1' and A.is_delete<>'1'";
|
dynamicParams.Add("@partcode", partcode);
|
var data = DapperHelper.selectdata(sql, dynamicParams);
|
mes.code = "200";
|
mes.message = "查询成功!";
|
mes.data = data;
|
}
|
else //工序版
|
{
|
//通过产品编码查找关联的工序信息
|
sql = @"select A.step_seq as seq,A.step_code as code,S.stepname as name,A.unprice,A.isbott,A.isend,S.flwtype
|
from (
|
select S.materiel_code,R.step_seq,S.step_code,S.unprice,R.isbott,R.isend from TPrteStep_Price S
|
inner join TMateriel_Step R on S.materiel_code=R.materiel_code and S.step_code=R.step_code
|
where S.route_code is null and S.materiel_code=@partcode
|
union all
|
select materiel_code,step_seq,step_code,'0' as unprice,isbott,isend from TMateriel_Step
|
where materiel_code=@partcode and materiel_code+step_code
|
not in(select materiel_code+step_code from TPrteStep_Price
|
where route_code is null and materiel_code=@partcode)
|
) as A
|
left join TStep S on A.step_code=S.stepcode
|
A.step_seq";
|
dynamicParams.Add("@partcode", partcode);
|
var parents = DapperHelper.select<SubData>(sql, dynamicParams);
|
for (int i = 0; i < parents.Count; i++)
|
{
|
if (parents[i].flwtype == "Z")
|
{
|
//通过产品编码+工序编码查找对应设备信息
|
sql = @"select A.eqp_code,E.name as eqp_name,A.eqp_value,A.stand_value,A.cavity_qty,A.unprice
|
from (
|
select S.eqp_code,S.eqp_value,S.stand_value,S.cavity_qty,S.unprice from TPrteEqp_Stad S
|
inner join TMateriel_Step R on S.materiel_code=R.materiel_code and S.step_code=R.step_code
|
where S.materiel_code=@partcode and S.step_code=@stepcode and S.route_code is null
|
union all
|
select R.eqp_code,'0' as eqp_value,'0' as stand_value,'0' as cavity_qty,'0' as unprice from TMateriel_Step S
|
inner join TFlw_Rteqp R on S.step_code=R.step_code
|
where S.materiel_code=@partcode and S.step_code=@stepcode and S.materiel_code+S.step_code+R.eqp_code
|
not in(select materiel_code+step_code+eqp_code from TPrteEqp_Stad
|
where materiel_code=@partcode and step_code=@stepcode and route_code is null)
|
) as A
|
left join TEqpInfo E on A.eqp_code=E.code";
|
dynamicParams.Add("@partcode", partcode);
|
dynamicParams.Add("@stepcode", parents[i].code);
|
var children = DapperHelper.select<EqpSubData>(sql, dynamicParams);
|
parents[i].children = children.ToList();
|
}
|
else
|
{
|
//通过产品编码+工序编码查找对应外协供应商信息
|
sql = @"select A.eqp_code,C.name as eqp_name,A.eqp_value,A.stand_value,A.cavity_qty,A.unprice
|
from (
|
select S.eqp_code,S.eqp_value,S.stand_value,S.cavity_qty,S.unprice from TPrteEqp_Stad S
|
inner join TMateriel_Step R on S.materiel_code=R.materiel_code and S.step_code=R.step_code
|
where S.materiel_code=@partcode and S.step_code=@stepcode and S.route_code is null
|
union all
|
select R.eqp_code,'0' as eqp_value,'0' as stand_value,'0' as cavity_qty,'0' as unprice from TMateriel_Step S
|
inner join TFlw_Rteqp R on S.step_code=R.step_code
|
where S.materiel_code=@partcode and S.step_code=@stepcode and S.materiel_code+S.step_code+R.eqp_code
|
not in(select materiel_code+step_code+eqp_code from TPrteEqp_Stad
|
where materiel_code=@partcode and step_code=@stepcode and route_code is null)
|
) as A
|
left join TCustomer C on A.eqp_code=C.code";
|
dynamicParams.Add("@partcode", partcode);
|
dynamicParams.Add("@stepcode", parents[i].code);
|
var children = DapperHelper.select<EqpSubData>(sql, dynamicParams);
|
parents[i].children = children.ToList();
|
}
|
|
}
|
mes.code = "200";
|
mes.message = "查询成功!";
|
mes.data = parents;
|
}
|
}
|
catch (Exception e)
|
{
|
mes.code = "300";
|
mes.count = 0;
|
mes.message = e.Message;
|
mes.data = null;
|
}
|
return mes;
|
}
|
#endregion
|
|
#region[根据工艺信息(工艺路线编码)查找关联工序集合]
|
public static ToMessage RouteSelectStep(string partcode, string routecode)
|
{
|
string sql = "";
|
var dynamicParams = new DynamicParameters();
|
try
|
{
|
//通过工艺路线编码查找关联的工序信息
|
sql = @"select A.step_seq as seq,A.step_code as code,S.stepname as name,A.unprice,A.isbott,A.isend,S.flwtype
|
from (
|
select S.materiel_code,R.seq as step_seq,S.step_code,S.unprice,R.first_choke as isbott,R.last_choke as isend from TPrteStep_Price S
|
inner join TFlw_Rtdt R on S.route_code=R.rout_code and S.step_code=R.step_code
|
where S.materiel_code=@partcode and S.route_code=@route_code
|
union all
|
select @partcode as materiel_code,B.seq as step_seq,B.step_code,'0' as unprice,B.first_choke as isbott,B.last_choke as isend from TFlw_Rout A
|
inner join TFlw_Rtdt B on A.code=B.rout_code
|
where A.code=@route_code and @partcode+A.code+B.step_code
|
not in(select materiel_code+rout_code+step_code from TPrteStep_Price
|
where materiel_code=@partcode and route_code=@route_code)
|
) as A
|
left join TStep S on A.step_code=S.stepcode
|
order by A.step_seq";
|
dynamicParams.Add("@partcode", partcode);
|
dynamicParams.Add("@route_code", routecode);
|
var parents = DapperHelper.select<SubData>(sql, dynamicParams);
|
for (int i = 0; i < parents.Count; i++)
|
{
|
if (parents[i].flwtype == "Z")
|
{
|
//通过产品编码+工艺路线+工序编码查找对应设备信息
|
sql = @"select A.eqp_code,E.name as eqp_name,A.enable,A.eqp_value,A.stand_value,A.cavity_qty,A.unprice
|
from (
|
select S.eqp_code,S.eqp_value,S.enable,S.stand_value,S.cavity_qty,S.unprice from TPrteEqp_Stad S
|
where S.materiel_code=@partcode and S.route_code=@routecode and S.step_code=@stepcode
|
union all
|
select R.eqp_code,'0' as eqp_value,'N' as enable,'0' as stand_value,'0' as cavity_qty,'0' as unprice from TMateriel_Route S
|
inner join TFlw_Rtdt T on S.route_code=T.rout_code
|
inner join TFlw_Rteqp R on T.step_code=R.step_code
|
where S.materiel_code=@partcode and S.route_code=@routecode and T.step_code=@stepcode and S.materiel_code+S.route_code+T.step_code+R.eqp_code
|
not in(select materiel_code+route_code+step_code+eqp_code from TPrteEqp_Stad
|
where materiel_code=@partcode and route_code=@routecode and step_code=@stepcode)
|
) as A
|
left join TEqpInfo E on A.eqp_code=E.code";
|
dynamicParams.Add("@partcode", partcode);
|
dynamicParams.Add("@routecode", routecode);
|
dynamicParams.Add("@stepcode", parents[i].code);
|
var children = DapperHelper.select<EqpSubData>(sql, dynamicParams);
|
parents[i].children = children.ToList();
|
}
|
else
|
{
|
//通过产品编码+工艺路线+工序编码查找对应外协供应商信息
|
sql = @"select A.eqp_code,C.name as eqp_name,A.enable,A.eqp_value,A.stand_value,A.cavity_qty,A.unprice
|
from (
|
select S.eqp_code,S.eqp_value,S.enable,S.stand_value,S.cavity_qty,S.unprice from TPrteEqp_Stad S
|
where S.materiel_code=@partcode and S.route_code=@routecode and S.step_code=@stepcode
|
union all
|
select R.eqp_code,'0' as eqp_value,'N' as enable,'0' as stand_value,'0' as cavity_qty,'0' as unprice from TMateriel_Route S
|
inner join TFlw_Rtdt T on S.route_code=T.rout_code
|
inner join TFlw_Rteqp R on T.step_code=R.step_code
|
where S.materiel_code=@partcode and S.route_code=@routecode and T.step_code=@stepcode and S.materiel_code+S.route_code+T.step_code+R.eqp_code
|
not in(select materiel_code+route_code+step_code+eqp_code from TPrteEqp_Stad
|
where materiel_code=@partcode and route_code=@routecode and step_code=@stepcode)
|
) as A
|
left join TCustomer C on A.eqp_code=C.code";
|
dynamicParams.Add("@partcode", partcode);
|
dynamicParams.Add("@routecode", routecode);
|
dynamicParams.Add("@stepcode", parents[i].code);
|
var children = DapperHelper.select<EqpSubData>(sql, dynamicParams);
|
parents[i].children = children.ToList();
|
}
|
}
|
mes.code = "200";
|
mes.message = "查询成功!";
|
mes.data = parents;
|
}
|
catch (Exception e)
|
{
|
mes.code = "300";
|
mes.count = 0;
|
mes.message = e.Message;
|
mes.data = null;
|
}
|
return mes;
|
}
|
#endregion
|
|
#region[节拍工价查询]
|
public static ToMessage BeatRateSearch(string partcode, string routecode, string stepcode, User us, int startNum, int endNum, string prop, string order)
|
{
|
string sql = "", search="";
|
int total = 0;
|
var dynamicParams = new DynamicParameters();
|
try
|
{
|
if (partcode != "" && partcode != null)
|
{
|
search += "and AA.partcode=@partcode ";
|
dynamicParams.Add("@partcode", partcode);
|
}
|
if (stepcode != "" && stepcode != null)
|
{
|
search += "and AA.stepcode=@stepcode ";
|
dynamicParams.Add("@stepcode", stepcode);
|
}
|
if (search == "")
|
{
|
search = "and 1=1 ";
|
}
|
|
dynamic dynObj = JObject.Parse(us.mesSetting);
|
bool route = dynObj.route;
|
if (route) //工艺路线版
|
{
|
if (routecode != "" && routecode != null)
|
{
|
search += "and AA.route_code=@routecode ";
|
dynamicParams.Add("@routecode", routecode);
|
}
|
search = search.Substring(3);//截取索引2后面的字符
|
total = 0; //总条数
|
sql = @"select * from (
|
select A.id,A.materiel_code as partcode,B.partname,B.partspec,A.route_code,C.name as route_name,
|
D.stepcode,D.stepname,D.flwtype,A.unprice,U.username as lm_user,A.lm_date
|
from TPrteStep_Price A
|
left join TMateriel_Info B on A.materiel_code=B.partcode
|
left join TFlw_Rout C on A.route_code=C.code
|
left join TStep D on A.step_code=D.stepcode
|
left join TUser U on A.lm_user=U.usercode
|
) as AA where" + search;
|
}
|
else //工序版
|
{
|
search = search.Substring(3);//截取索引2后面的字符
|
total = 0; //总条数
|
sql = @"select * from (
|
select A.id,A.materiel_code as partcode,B.partname,B.partspec,D.stepcode,D.stepname,D.flwtype,A.unprice,U.username as lm_user,A.lm_date
|
from TPrteStep_Price A
|
left join TMateriel_Info B on A.materiel_code=B.partcode
|
left join TStep D on A.step_code=D.stepcode
|
left join TUser U on A.lm_user=U.usercode
|
) as AA where" + 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 SaveBeatRate(List<MaterialCraftsSave> json, User us)
|
{
|
var sql = "";
|
List<object> list = new List<object>();
|
var dynamicParams = new DynamicParameters();
|
try
|
{
|
list.Clear();
|
dynamic dynObj = JObject.Parse(us.mesSetting);
|
bool route = dynObj.route;
|
if (route) //工艺路线版
|
{
|
//工序信息
|
for (int i = 0; i < json[0].children.Count; i++)
|
{
|
//当前产品工艺路线对应工序是否已设置工价
|
sql = @"select * from TPrteStep_Price where materiel_code=@partcode and route_code=@routecode and step_code=@stepcode";
|
dynamicParams.Add("@partcode", json[0].partcode);
|
dynamicParams.Add("@routecode", json[0].defaultroute_code);
|
dynamicParams.Add("@stepcode", json[0].children[i].code);
|
var data = DapperHelper.selectdata(sql, dynamicParams);
|
if (data.Rows.Count > 0)
|
{
|
sql = @"update TPrteStep_Price set unprice=@unprice,lm_user=@username,lm_date=@userdate
|
where materiel_code=@partcode and route_code=@routecode and step_code=@stepcode";
|
list.Add(new
|
{
|
str = sql,
|
parm = new
|
{
|
partcode = json[0].partcode,
|
routecode = json[0].defaultroute_code,
|
stepcode = json[0].children[i].code,
|
unprice = json[0].children[i].unprice,
|
username = us.usercode,
|
userdate = DateTime.Now.ToString()
|
}
|
});
|
}
|
else
|
{
|
sql = @"insert into TPrteStep_Price(materiel_code,route_code,step_code,unprice,lm_user,lm_date)
|
values(@partcode,@routecode,@stepcode,@unprice,@username,@userdate)";
|
list.Add(new
|
{
|
str = sql,
|
parm = new
|
{
|
partcode = json[0].partcode,
|
routecode = json[0].defaultroute_code,
|
stepcode = json[0].children[i].code,
|
unprice = json[0].children[i].unprice,
|
username = us.usercode,
|
userdate = DateTime.Now.ToString()
|
}
|
});
|
}
|
//设备信息
|
for (int j = 0; j < json[0].children[i].children.Count; j++)
|
{
|
//当前产品工艺路线对应工序设备是否已设置节拍工价
|
sql = @"select * from TPrteEqp_Stad where materiel_code=@partcode and route_code=@routecode and step_code=@stepcode and eqp_code=@eqpcode";
|
dynamicParams.Add("@partcode", json[0].partcode);
|
dynamicParams.Add("@routecode", json[0].defaultroute_code);
|
dynamicParams.Add("@stepcode", json[0].children[i].code);
|
dynamicParams.Add("@eqpcode", json[0].children[i].children[j].eqp_code);
|
var data0 = DapperHelper.selectdata(sql, dynamicParams);
|
if (data0.Rows.Count > 0)
|
{
|
sql = @"update TPrteEqp_Stad set enable=@enable, eqp_value=@eqp_value,stand_value=@stand_value,cavity_qty=@cavity_qty,unprice=@unprice,lm_user=@username,lm_date=@userdate
|
where materiel_code=@partcode and route_code=@routecode and step_code=@stepcode and eqp_code=@eqpcode";
|
list.Add(new
|
{
|
str = sql,
|
parm = new
|
{
|
partcode = json[0].partcode,
|
routecode = json[0].defaultroute_code,
|
stepcode = json[0].children[i].code,
|
eqpcode = json[0].children[i].children[j].eqp_code,
|
enable= json[0].children[i].children[j].enable,
|
eqp_value = json[0].children[i].children[j].eqp_value,
|
stand_value = json[0].children[i].children[j].stand_value,
|
cavity_qty = json[0].children[i].children[j].cavity_qty,
|
unprice = json[0].children[i].children[j].unprice,
|
username = us.usercode,
|
userdate = DateTime.Now.ToString()
|
}
|
});
|
}
|
else
|
{
|
sql = @"insert into TPrteEqp_Stad(materiel_code,route_code,step_code,eqp_code,enable,stand_value,eqp_value,cavity_qty,unprice,lm_user,lm_date)
|
values(@materiel_code,@route_code,@step_code,@eqp_code,@enable,@stand_value,@eqp_value,@cavity_qty,@unprice,@lm_user,@lm_date)";
|
list.Add(new
|
{
|
str = sql,
|
parm = new
|
{
|
materiel_code = json[0].partcode,
|
route_code = json[0].defaultroute_code,
|
step_code = json[0].children[i].code,
|
eqp_code = json[0].children[i].children[j].eqp_code,
|
enable = json[0].children[i].children[j].enable,
|
stand_value = json[0].children[i].children[j].eqp_value,
|
eqp_value = json[0].children[i].children[j].stand_value,
|
cavity_qty = json[0].children[i].children[j].cavity_qty,
|
unprice = json[0].children[i].children[j].unprice,
|
lm_user = us.usercode,
|
lm_date = DateTime.Now.ToString()
|
}
|
});
|
}
|
}
|
}
|
}
|
else //工序版
|
{
|
for (int i = 0; i < json[0].children.Count; i++)
|
{
|
//当前产品对应工序是否已设置节拍工价
|
sql = @"select * from TPrteStep_Price where materiel_code=@partcode and step_code=@stepcode";
|
dynamicParams.Add("@partcode", json[0].partcode);
|
dynamicParams.Add("@stepcode", json[0].children[i].code);
|
var data = DapperHelper.selectdata(sql, dynamicParams);
|
if (data.Rows.Count > 0)
|
{
|
sql = @"update TPrteStep_Price set unprice=@unprice,lm_user=@username,lm_date=@userdate
|
where materiel_code=@partcode and step_code=@stepcode";
|
list.Add(new
|
{
|
str = sql,
|
parm = new
|
{
|
partcode = json[0].partcode,
|
stepcode = json[0].children[i].code,
|
unprice = json[0].children[i].unprice,
|
username = us.usercode,
|
userdate = DateTime.Now.ToString()
|
}
|
});
|
}
|
else
|
{
|
sql = @"insert into TPrteStep_Price(materiel_code,step_code,unprice,lm_user,lm_date)
|
values(@partcode,@stepcode,@unprice,@username,@userdate)";
|
list.Add(new
|
{
|
str = sql,
|
parm = new
|
{
|
partcode = json[0].partcode,
|
stepcode = json[0].children[i].code,
|
unprice = json[0].children[i].unprice,
|
username = us.usercode,
|
userdate = DateTime.Now.ToString()
|
}
|
});
|
}
|
//设备信息
|
for (int j = 0; j < json[0].children[i].children.Count; j++)
|
{
|
//当前产品工艺路线对应工序设备是否已设置节拍工价
|
sql = @"select * from TPrteEqp_Stad where materiel_code=@partcode and route_code=@routecode and step_code=@stepcode and eqp_code=@eqpcode";
|
dynamicParams.Add("@partcode", json[0].partcode);
|
dynamicParams.Add("@routecode", json[0].defaultroute_code);
|
dynamicParams.Add("@stepcode", json[0].children[i].code);
|
dynamicParams.Add("@eqpcode", json[0].children[i].children[j].eqp_code);
|
var data0 = DapperHelper.selectdata(sql, dynamicParams);
|
if (data0.Rows.Count > 0)
|
{
|
sql = @"update TPrteEqp_Stad set enable=@enable,eqp_value=@eqp_value,stand_value=@stand_value,cavity_qty=@cavity_qty,unprice=@unprice,lm_user=@username,lm_date=@userdate
|
where materiel_code=@partcode and route_code=@routecode and step_code=@stepcode and eqp_code=@eqpcode";
|
list.Add(new
|
{
|
str = sql,
|
parm = new
|
{
|
partcode = json[0].partcode,
|
routecode = json[0].defaultroute_code,
|
stepcode = json[0].children[i].code,
|
eqpcode = json[0].children[i].children[j].eqp_code,
|
enable= json[0].children[i].children[j].enable,
|
eqp_value = json[0].children[i].children[j].eqp_value,
|
stand_value = json[0].children[i].children[j].stand_value,
|
cavity_qty = json[0].children[i].children[j].cavity_qty,
|
unprice = json[0].children[i].children[j].unprice,
|
username = us.usercode,
|
userdate = DateTime.Now.ToString()
|
}
|
});
|
}
|
else
|
{
|
sql = @"insert into TPrteEqp_Stad(materiel_code,route_code,step_code,eqp_code,enable,stand_value,eqp_value,cavity_qty,unprice,lm_user,lm_date)
|
values(@materiel_code,@route_code,@step_code,@eqp_code,@enable,@stand_value,@eqp_value,@cavity_qty,@unprice,@lm_user,@lm_date)";
|
list.Add(new
|
{
|
str = sql,
|
parm = new
|
{
|
materiel_code = json[0].partcode,
|
route_code = json[0].defaultroute_code,
|
step_code = json[0].children[i].code,
|
eqp_code = json[0].children[i].children[j].eqp_code,
|
enable= json[0].children[i].children[j].enable,
|
stand_value = json[0].children[i].children[j].eqp_value,
|
eqp_value = json[0].children[i].children[j].stand_value,
|
cavity_qty = json[0].children[i].children[j].cavity_qty,
|
unprice = json[0].children[i].children[j].unprice,
|
lm_user = us.usercode,
|
lm_date = DateTime.Now.ToString()
|
}
|
});
|
}
|
}
|
}
|
}
|
bool aa = DapperHelper.DoTransaction(list);
|
if (aa)
|
{
|
//写入操作记录表
|
LogHelper.DbOperateLog(us.usercode, "节拍工价", "产品："+ json[0].partcode + "设置了工价:" + string.Join(",", json[0].children.Select(s => s.unprice)), 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 DeleteBeatRate(string id, User us)
|
{
|
var sql = "";
|
List<object> list = new List<object>();
|
var dynamicParams = new DynamicParameters();
|
try
|
{
|
//查找工序工价表
|
sql = @"select * from TPrteStep_Price where id=@id";
|
dynamicParams.Add("@id", id);
|
var data = DapperHelper.selectdata(sql, dynamicParams);
|
if (data.Rows.Count > 0)
|
{
|
//删除节拍工价表
|
sql = @"delete TPrteEqp_Stad where materiel_code=@materiel_code and route_code=@route_code and step_code=@step_code";
|
list.Add(new
|
{
|
str = sql,
|
parm = new
|
{
|
materiel_code = data.Rows[0]["materiel_code"].ToString(),
|
route_code = data.Rows[0]["route_code"].ToString(),
|
step_code = data.Rows[0]["step_code"].ToString()
|
}
|
});
|
}
|
//删除工序工价表
|
sql = @"delete TPrteStep_Price where id=@id";
|
list.Add(new
|
{
|
str = sql,
|
parm = new
|
{
|
id = id
|
}
|
});
|
|
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 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, User us)
|
{
|
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 = us.usercode,
|
createdate = DateTime.Now.ToString()
|
}
|
});
|
}
|
bool aa = DapperHelper.DoTransaction(list);
|
if (aa)
|
{
|
//写入操作记录表
|
LogHelper.DbOperateLog(us.usercode, "新增", "新增了安灯呼叫类型:" + string.Join(",", json.Select(item => item.name)), 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 DeleteAnDengType(string andengtypecode, User us)
|
{
|
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)
|
{
|
//写入操作记录表
|
LogHelper.DbOperateLog(us.usercode, "删除", "删除了安灯呼叫类型:" + andengtypecode, 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 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";
|
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.is_system_admin='N'";
|
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, User us, 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 = us.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)
|
{
|
//写入操作记录表
|
LogHelper.DbOperateLog(us.usercode, "安灯响应人员绑定", "车间：" + wkshopcode + ",呼叫类型：" + calltypecode + ",绑定响应人员:" + string.Join(",", json.AsEnumerable().Select(row => row.Field<string>("code"))), 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 AnDengResponUserCloseSeave(string wkshopcode, string calltypecode, string enable, User us)
|
{
|
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 = us.usercode,
|
lm_date = DateTime.Now.ToString(),
|
enable = enable
|
}
|
});
|
bool aa = DapperHelper.DoTransaction(list);
|
if (aa)
|
{
|
//写入操作记录表
|
LogHelper.DbOperateLog(us.usercode, "安灯设置呼叫类型关闭", "车间：" + wkshopcode + ",呼叫类型：" + calltypecode, 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 AnDengResponUserDeleteSeave(string id, string ando_cogfigid, User us)
|
{
|
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)
|
{
|
//写入操作记录表
|
LogHelper.DbOperateLog(us.usercode, "删除", "安灯设置呼叫类型人员删除,操作呼叫配置表id为：" + ando_cogfigid, 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
|
}
|
}
|
