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.Models;
|
using VueWebCoreApi.Models.UnitMaterial;
|
using VueWebCoreApi.Tools;
|
|
namespace VueWebCoreApi.DLL.DAL
|
{
|
public class ProductModelDAL
|
{
|
public static DataTable dt; //定义全局变量dt
|
public static bool res; //定义全局变量dt
|
public static ToMessage mes = new ToMessage(); //定义全局返回信息对象
|
public static string strProcName = ""; //定义全局sql变量
|
public static List<SqlParameter> listStr = new List<SqlParameter>(); //定义全局参数集合
|
public static SqlParameter[] parameters; //定义全局SqlParameter参数数组
|
|
|
#region[计量单位组信息]
|
public static ToMessage TUnitGroup()
|
{
|
string sql = "";
|
try
|
{
|
//获取计量单位组信息
|
sql = @"select code,name,'1' as isGroup,data_sources from TUnitGroup ";
|
var data = DapperHelper.selecttable(sql);
|
mes.code = "200";
|
mes.message = "查询成功!";
|
mes.data = data;
|
LogHelper.OperateLog("999", "查询", "计量单位组查询数据为：" + JsonConvert.SerializeObject(data), "PC");
|
}
|
catch (Exception e)
|
{
|
mes.code = "300";
|
mes.count = 0;
|
mes.message = e.Message;
|
mes.data = null;
|
}
|
return mes;
|
}
|
#endregion
|
|
#region[计量单位信息列表]
|
public static ToMessage TUnitData(string tunittype, string tunitgroupcode, int startNum, int endNum, string prop, string order)
|
{
|
var dynamicParams = new DynamicParameters();
|
string sql = "";
|
IEnumerable<object> data;
|
try
|
{
|
if (tunittype == "" || tunittype == null)
|
{
|
sql = @"select AA.code,AA.name,U.username as lm_user,AA.lm_date,AA.isGroup,AA.idunitgroup,AA.data_sources
|
from(
|
select code,name,lm_user,lm_date,isGroup,idunitgroup,data_sources
|
from TUnit T
|
where T.isGroup='0'
|
union all
|
select code,name,lm_user,lm_date,'1' as isGroup,code as idunitgroup,data_sources
|
from TUnitGroup
|
) as AA
|
left join TUser U on AA.lm_user=U.usercode ";
|
}
|
if (tunittype == "S") //单计量
|
{
|
sql = @"select T.code,T.name,U.username as lm_user,T.lm_date,T.isGroup,T.idunitgroup,T.data_sources
|
from TUnit T
|
left join TUser U on T.lm_user=U.usercode
|
where T.isSingleUnit='1' and T.isGroup='0'";
|
}
|
if (tunittype == "M" && (tunitgroupcode == "" || tunitgroupcode == null)) //多计量,且多计量组编码为空
|
{
|
sql = @"select T.code,T.name,U.usercode as lm_user,T.lm_date,'1' as isGroup,T.code as idunitgroup,T.data_sources
|
from TUnitGroup T
|
left join TUser U on T.lm_user=U.usercode";
|
}
|
if (tunittype == "M" && (tunitgroupcode != "" && tunitgroupcode != null)) //多计量,且多计量组编码不为空
|
{
|
sql = @"select T.code,T.name,T.isMainUnit,T.changeRate,T.rateDescription,U.username as lm_user,T.lm_date,T.isGroup,T.idunitgroup,T.data_sources
|
from TUnit T
|
left join TUser U on T.lm_user=U.usercode
|
where T.isSingleUnit='0' and T.isGroup='1' and T.idunitgroup=@tunitgroupcode";
|
dynamicParams.Add("@tunitgroupcode", tunitgroupcode);
|
}
|
// --------------查询指定数据--------------
|
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();
|
LogHelper.OperateLog("999", "查询", "查询了计量单位", "PC");
|
}
|
catch (Exception e)
|
{
|
mes.code = "300";
|
mes.count = 0;
|
mes.message = e.Message;
|
mes.data = null;
|
}
|
return mes;
|
}
|
#endregion
|
|
#region[计量单位编辑获取组明细]
|
public static ToMessage TUnitGroupProject(string unitcode, string isgroup, User us)
|
{
|
var dynamicParams = new DynamicParameters();
|
string sql = "";
|
try
|
{
|
//获取计量单位组对应的计量单位信息
|
sql = @"select code,name,isMainUnit,changeRate,rateDescription,lm_user,lm_date,data_sources
|
from TUnit where isSingleUnit='0' and isGroup=@isgroup and idunitgroup=@tunitgroupcode ";
|
dynamicParams.Add("@isGroup", isgroup);
|
dynamicParams.Add("@tunitgroupcode", unitcode);
|
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 AddUpdateTUnit(string menucode, string data_sources, string tunittype, string tunitgroupcode, string OperType, List<Unit> listjson, User us)
|
{
|
string sql = "";
|
List<object> list = new List<object>();
|
var dynamicParams = new DynamicParameters();
|
try
|
{
|
if (OperType == "Add")
|
{
|
//获取计量单位编码
|
mes = SeachEncodeJob.EncodingSeach(menucode);
|
if (mes.code == "300")
|
{
|
return mes;
|
}
|
if (tunittype == "S") //单计量
|
{
|
sql = @"select * from TUnit where isGroup='0' and name=@name";
|
dynamicParams.Add("@name", listjson[0].unitname);
|
var data = DapperHelper.selectdata(sql, dynamicParams);
|
if (data.Rows.Count > 0)
|
{
|
mes.code = "300";
|
mes.count = 0;
|
mes.message = "计量单位的名称不能重复,请重新输入!";
|
mes.data = null;
|
return mes;
|
}
|
}
|
if (tunittype == "M")//多计量
|
{
|
sql = @"select * from TUnitGroup where name=@name";
|
dynamicParams.Add("@name", listjson[0].unitname);
|
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 TUnitGroup(code,name,lm_user,lm_date,data_sources)
|
values(@code,@name,@lm_user,@lm_date,@data_sources)";
|
list.Add(new
|
{
|
str = sql,
|
parm = new
|
{
|
code = ((List<string>)mes.data)[0].ToString(),
|
name = listjson[0].unitname,
|
data_sources = data_sources,
|
lm_user = us.usercode,
|
lm_date = DateTime.Now.ToString()
|
}
|
});
|
}
|
for (int i = 0; i < listjson[0].children.Count; i++)
|
{
|
//写入计量单位
|
sql = @"insert into TUnit(code,name,isMainUnit,changeRate,rateDescription,isSingleUnit,disabled,idunitgroup,isGroup,data_sources,lm_user,lm_date)
|
values(@code,@name,@isMainUnit,@changeRate,@rateDescription,@isSingleUnit,@disabled,@idunitgroup,@isGroup,@data_sources,@lm_user,@lm_date)";
|
list.Add(new
|
{
|
str = sql,
|
parm = new
|
{
|
code = tunittype == "S" ? ((List<string>)mes.data)[0].ToString() : listjson[0].children[i].unitcode,
|
name = listjson[0].children[i].unitname,
|
isMainUnit = listjson[0].children[i].isMainUnit,
|
changeRate = listjson[0].children[i].changeRate,
|
rateDescription = listjson[0].children[i].rateDescription,
|
isSingleUnit = listjson[0].children[i].isSingleUnit,
|
disabled = listjson[0].children[i].disabled,
|
idunitgroup = ((List<string>)mes.data)[0].ToString(),
|
isGroup = listjson[0].children[i].isGroup,
|
data_sources = data_sources,
|
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, "新增", "新增了计量单位："+ tunittype == "S" ? ((List<string>)mes.data)[0].ToString() : string.Join(",", listjson[0].children.Select(m => m.unitcode)), 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 (tunittype == "S") //单计量
|
{
|
sql = @"select * from TUnit where isGroup='0' and code<>@code and name=@name";
|
dynamicParams.Add("@code", listjson[0].unitcode);
|
dynamicParams.Add("@name", listjson[0].unitname);
|
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 TUnit set name=@name,lm_user=@lm_user,lm_date=@lm_date
|
where code=@code and isSingleUnit=@isSingleUnit and isGroup=@isGroup";
|
list.Add(new
|
{
|
str = sql,
|
parm = new
|
{
|
code = listjson[0].children[0].unitcode,
|
name = listjson[0].children[0].unitname,
|
isSingleUnit = listjson[0].children[0].isSingleUnit,
|
isGroup = listjson[0].children[0].isGroup,
|
lm_user = us.usercode,
|
lm_date = DateTime.Now.ToString()
|
}
|
});
|
|
}
|
if (tunittype == "M")//多计量
|
{
|
sql = @"select * from TUnitGroup where code<>@code and name=@name";
|
dynamicParams.Add("@code", listjson[0].unitcode);
|
dynamicParams.Add("@name", listjson[0].unitname);
|
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 TUnitGroup set name=@name,lm_user=@lm_user,lm_date=@lm_date where code=@code";
|
list.Add(new
|
{
|
str = sql,
|
parm = new
|
{
|
code = listjson[0].unitcode,
|
name = listjson[0].unitname,
|
lm_user = us.usercode,
|
lm_date = DateTime.Now.ToString()
|
}
|
});
|
//删除计量单位组明细(从计量单位表删除)
|
sql = @"delete TUnit where idunitgroup=@idunitgroup";
|
list.Add(new
|
{
|
str = sql,
|
parm = new
|
{
|
idunitgroup = tunitgroupcode
|
}
|
});
|
for (int i = 0; i < listjson[0].children.Count; i++)
|
{
|
//写入计量单位
|
sql = @"insert into TUnit(code,name,isMainUnit,changeRate,rateDescription,isSingleUnit,disabled,idunitgroup,isGroup,data_sources,lm_user,lm_date)
|
values(@code,@name,@isMainUnit,@changeRate,@rateDescription,@isSingleUnit,@disabled,@idunitgroup,@isGroup,@data_sources,@lm_user,@lm_date)";
|
list.Add(new
|
{
|
str = sql,
|
parm = new
|
{
|
code = listjson[0].children[i].unitcode,
|
name = listjson[0].children[i].unitname,
|
isMainUnit = listjson[0].children[i].isMainUnit,
|
changeRate = listjson[0].children[i].changeRate,
|
rateDescription = listjson[0].children[i].rateDescription,
|
isSingleUnit = listjson[0].children[i].isSingleUnit,
|
disabled = listjson[0].children[i].disabled,
|
idunitgroup = listjson[0].children[i].idunitgroup,
|
isGroup = listjson[0].children[i].isGroup,
|
data_sources = listjson[0].children[i].data_sources,
|
lm_user = us.usercode,
|
lm_date = DateTime.Now.ToString()
|
}
|
});
|
}
|
}
|
|
bool aa = DapperHelper.DoTransaction(list);
|
if (aa)
|
{
|
//写入操作记录表
|
LogHelper.DbOperateLog(us.usercode, "修改", "修改了计量单位：" + tunittype == "S" ? ((List<string>)mes.data)[0].ToString() : string.Join(",", listjson[0].children.Select(m => m.unitcode)), 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 DeleteTUnit(string data_sources, string tunittype, string tunitgroupcode, string tunitcode, User us)
|
{
|
string sql = "";
|
List<object> list = new List<object>();
|
var dynamicParams = new DynamicParameters();
|
try
|
{
|
if (tunittype == "S") //单计量
|
{
|
sql = @"select * from TMateriel_Info where isSingleUnit='1' and idunit=@idunit";
|
dynamicParams.Add("@idunit", tunitcode);
|
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 TUnit where isSingleUnit='1' and isGroup='0' and code=@code and data_sources=@data_sources";
|
list.Add(new
|
{
|
str = sql,
|
parm = new
|
{
|
code = tunitcode,
|
data_sources = data_sources
|
}
|
});
|
}
|
if (tunittype == "M" && (tunitgroupcode != "" || tunitgroupcode != null) && (tunitcode == "" || tunitcode == null)) //多计量,且多计量组编码不为空,明细编码为空
|
{
|
sql = @"select * from TUnit T
|
inner join TMateriel_Info M on T.idunitgroup=M.idunitgroup and T.code=M.idunit
|
where T.isSingleUnit='0' and T.idunitgroup=@idunitgroup";
|
dynamicParams.Add("@idunitgroup", tunitgroupcode);
|
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 TUnitGroup where code=@code and data_sources=@data_sources";
|
list.Add(new
|
{
|
str = sql,
|
parm = new
|
{
|
code = tunitgroupcode,
|
data_sources = data_sources
|
}
|
});
|
//删除多计量单位组下所有计量单位
|
sql = @"delete TUnit where isSingleUnit='0' and isGroup='1' and idunitgroup=@idunitgroup and data_sources=@data_sources";
|
list.Add(new
|
{
|
str = sql,
|
parm = new
|
{
|
idunitgroup = tunitgroupcode,
|
data_sources = data_sources
|
}
|
});
|
}
|
if (tunittype == "M" && (tunitgroupcode != "" && tunitgroupcode != null) && (tunitcode != "" || tunitcode != null)) //多计量,且多计量组编码不为空,明细编码不为空
|
{
|
sql = @"select * from TUnit T
|
inner join TMateriel_Info M on T.idunitgroup=M.idunitgroup and T.code=M.idunit
|
where T.isSingleUnit='0' and M.isSingleUnit='0' and T.idunitgroup=@idunitgroup and T.code=@idunit";
|
dynamicParams.Add("@idunitgroup", tunitgroupcode);
|
dynamicParams.Add("@idunit", tunitcode);
|
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 TUnit where isSingleUnit='0' and isGroup='1' and idunitgroup=@idunitgroup and code=@code and data_sources=@data_sources";
|
list.Add(new
|
{
|
str = sql,
|
parm = new
|
{
|
idunitgroup = tunitgroupcode,
|
code = tunitcode,
|
data_sources = data_sources
|
}
|
});
|
}
|
bool aa = DapperHelper.DoTransaction(list);
|
if (aa)
|
{
|
//写入操作记录表
|
LogHelper.DbOperateLog(us.usercode, "新增", "新增了计量单位：" + tunittype == "S" ? tunitcode : tunittype == "M"&& (tunitgroupcode != "" || tunitgroupcode != null) && (tunitcode == "" || tunitcode == null)? tunitgroupcode: tunitgroupcode+"-->"+tunitcode, 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 TMaterielClassTree()
|
{
|
string sql = "";
|
try
|
{
|
//获取存货分类信息
|
sql = @"select code,name,idparent,data_sources from TMateriel_Class ";
|
var data = DapperHelper.selecttable(sql);
|
mes.code = "200";
|
mes.message = "查询成功!";
|
mes.data = data;
|
//LogHelper.OperateLog("999", "查询", "存货分类查询数据为：" + JsonConvert.SerializeObject(data), "PC");
|
}
|
catch (Exception e)
|
{
|
mes.code = "300";
|
mes.count = 0;
|
mes.message = e.Message;
|
mes.data = null;
|
}
|
return mes;
|
}
|
#endregion
|
|
#region[存货分类新增/编辑提交]
|
public static ToMessage TMaterielClassTreeAddUpdate(string data_sources, string inventoryclasscode, string inventoryclassname, 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 TMateriel_Info where idinventoryclass=@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 TMateriel_Class where code=@inventoryclasscode";
|
dynamicParams.Add("@inventoryclasscode", inventoryclasscode);
|
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 TMateriel_Class where name=@inventoryclassname";
|
dynamicParams.Add("@inventoryclassname", inventoryclassname);
|
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 TMateriel_Class(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 = inventoryclasscode,
|
name = inventoryclassname,
|
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, "新增", "新增了存货分类:"+ inventoryclasscode, 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 TMateriel_Class where code<>@inventoryclasscode and name=@inventoryclassname";
|
dynamicParams.Add("@inventoryclasscode", inventoryclasscode);
|
dynamicParams.Add("@inventoryclassname", inventoryclassname);
|
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 TMateriel_Class set name=@name,idparent=@idparent,lm_user=@lm_user,lm_date=@lm_date where code=@code";
|
list.Add(new
|
{
|
str = sql,
|
parm = new
|
{
|
code = inventoryclasscode,
|
name = inventoryclassname,
|
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, "修改", "修改了存货分类:"+ inventoryclasscode, 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 TMaterielClassTreeDelete(string inventoryclasscode, User us)
|
{
|
string sql = "";
|
List<object> list = new List<object>();
|
var dynamicParams = new DynamicParameters();
|
try
|
{
|
string[] classcode = Array.ConvertAll<string, string>(inventoryclasscode.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries), s => s.ToString()); //string分割转string[]
|
//判断存货分类是否被存货信息引用
|
sql = @"select * from TMateriel_Info where idinventoryclass in @inventoryclasscode";
|
dynamicParams.Add("@inventoryclasscode", 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 TMateriel_Class where code in @code";
|
list.Add(new
|
{
|
str = sql,
|
parm = new
|
{
|
code = classcode
|
}
|
});
|
bool aa = DapperHelper.DoTransaction(list);
|
if (aa)
|
{
|
//写入操作记录表
|
LogHelper.DbOperateLog(us.usercode, "删除", "删除了存货分类"+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 TMaterielData(string inventoryclasscode, string partcode, string partname, string partspec, int startNum, int endNum, string prop, string order)
|
{
|
var dynamicParams = new DynamicParameters();
|
string search = "", sql = "";
|
IEnumerable<object> data;
|
try
|
{
|
if (inventoryclasscode != "" && inventoryclasscode != null)
|
{
|
string[] classcode = Array.ConvertAll<string, string>(inventoryclasscode.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries), s => s.ToString()); //string分割转string[]
|
search += "and M.idinventoryclass in @classcode ";
|
dynamicParams.Add("@classcode", classcode);
|
}
|
if (partcode != "" && partcode != null)
|
{
|
search += "and M.partcode like '%'+@partcode+'%' ";
|
dynamicParams.Add("@partcode", partcode);
|
}
|
if (partname != "" && partname != null)
|
{
|
search += "and M.partname like '%'+@partname+'%' ";
|
dynamicParams.Add("@partname", partname);
|
}
|
if (partspec != "" && partspec != null)
|
{
|
search += "and M.partspec like '%'+@partspec+'%' ";
|
dynamicParams.Add("@partspec", partspec);
|
}
|
sql = @"select M.partcode,M.partname,M.partspec,M.idunit as idunitcode,T.name as idunitname,M.idunitgroup as idunitgroupcode,G.name as idunitgroupname,
|
M.isSingleUnit,M.idSubUnitByReport as idSubUnitByReportcode,R.name as idSubUnitByReportname,M.idUnitByStock as idUnitByStockcode,K.name as idUnitByStockname,
|
M.idUnitByPurchase as idUnitByPurchasecode,P.name as idUnitByPurchasename,M.idUnitBySale as idUnitBySalecode,S.name as idUnitBySalename,
|
M.idunitbymanufacture as idunitbymanufacturecode,F.name as idunitbymanufacturename,M.idinventoryclass as idinventoryclasscode,C.name as idinventoryclassname,
|
M.isPurchase,M.isSale,M.isMadeSelf,M.isMaterial,M.isMadeRequest,M.idwarehouse as idwarehousecode,H.name as idwarehousename,M.lowQuantity,M.topQuantity,M.safeQuantity,
|
M.status,U.username as lm_user,M.lm_date,M.data_sources,M.is_retdproc,M.default_route,M.priuserdefnvc1,M.priuserdefnvc2,M.priuserdefnvc3,M.priuserdefnvc4,M.priuserdefnvc5,M.priuserdefnvc6
|
from TMateriel_Info M
|
left join TUnit T on M.idunit=T.code
|
left join TUnit R on M.idSubUnitByReport=R.code
|
left join TUnit K on M.idUnitByStock=K.code
|
left join TUnit P on M.idUnitByPurchase=P.code
|
left join TUnit S on M.idUnitBySale=S.code
|
left join TUnit F on M.idunitbymanufacture=F.code
|
left join TMateriel_Class C on M.idinventoryclass=C.code
|
left join TUnitGroup G on M.idunitgroup=G.code
|
left join TSecStck H on M.idwarehouse=H.code
|
left join TUser U on M.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 TMaterielAddUpdate(string data_sources, string partcode, string partname, string partspec, string inventoryclasscode, string unittypcode, string unitcode, string unitsubcode, string idSubUnitByReport, string idUnitByStock, string idUnitByPurchase, string idUnitBySale, string idunitbymanufacture, string isPurchase, string isSale, string isMadeSelf, string isMaterial, string isMadeRequest, string idwarehouse, 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 TMateriel_Info where partcode=@partcode";
|
dynamicParams.Add("@partcode", partcode);
|
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 TMateriel_Info where partname=@partname";
|
//dynamicParams.Add("@partname", partname);
|
//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 = @"select * from TMateriel_Info where partspec=@partspec";
|
//dynamicParams.Add("@partspec", partspec);
|
//var data2 = DapperHelper.selectdata(sql, dynamicParams);
|
//if (data2.Rows.Count > 0)
|
//{
|
// mes.code = "300";
|
// mes.count = 0;
|
// mes.message = "存货档案规格已存在,请重新输入!";
|
// mes.data = null;
|
// return mes;
|
//}
|
//写入存货分类
|
sql = @"insert into TMateriel_Info(partcode,partname,partspec,idunit,idunitgroup,isSingleUnit,idSubUnitByReport,idUnitByStock,idUnitByPurchase,idUnitBySale,idunitbymanufacture,
|
idinventoryclass,isPurchase,isSale,isMadeSelf,isMaterial,isMadeRequest,idwarehouse,status,lm_user,lm_date,data_sources)
|
values(@partcode,@partname,@partspec,@idunit,@idunitgroup,@isSingleUnit,@idSubUnitByReport,@idUnitByStock,@idUnitByPurchase,@idUnitBySale,@idunitbymanufacture,
|
@idinventoryclass,@isPurchase,@isSale,@isMadeSelf,@isMaterial,@isMadeRequest,@idwarehouse,@status,@lm_user,@lm_date,@data_sources)";
|
list.Add(new
|
{
|
str = sql,
|
parm = new
|
{
|
partcode=partcode,
|
partname=partname,
|
partspec=partspec,
|
idunit=unittypcode=="1"?unitcode:unitsubcode,
|
idunitgroup = unittypcode == "0"?"1":"0",
|
isSingleUnit= unittypcode,
|
idSubUnitByReport=idSubUnitByReport,
|
idUnitByStock= idUnitByStock,
|
idUnitByPurchase= idUnitByPurchase,
|
idUnitBySale= idUnitBySale,
|
idunitbymanufacture= idunitbymanufacture,
|
idinventoryclass= inventoryclasscode,
|
isPurchase= isPurchase,
|
isSale= isSale,
|
isMadeSelf= isMadeSelf,
|
isMaterial= isMaterial,
|
isMadeRequest= isMadeRequest,
|
idwarehouse= idwarehouse,
|
status= status,
|
lm_user = us.usercode,
|
lm_date = DateTime.Now.ToString(),
|
data_sources = data_sources
|
}
|
});
|
bool aa = DapperHelper.DoTransaction(list);
|
if (aa)
|
{
|
//写入操作记录表
|
LogHelper.DbOperateLog(us.usercode, "新增", "新增了存货档案:"+ partcode, 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 TMateriel_Info where partcode<>@partcode and partname=@partname";
|
dynamicParams.Add("@partcode", partcode);
|
dynamicParams.Add("@partname", partname);
|
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 = @"select * from TMateriel_Info where partcode<>@partcode and partspec=@partspec";
|
//dynamicParams.Add("@partcode", partcode);
|
//dynamicParams.Add("@partspec", partspec);
|
//var data2 = DapperHelper.selectdata(sql, dynamicParams);
|
//if (data2.Rows.Count > 0)
|
//{
|
// mes.code = "300";
|
// mes.count = 0;
|
// mes.message = "存货档案规格型号已存在,请重新输入!";
|
// mes.data = null;
|
// return mes;
|
//}
|
//更新存货分类
|
sql = @"update TMateriel_Info set partname=@partname,partspec=@partspec,idunit=@idunit,idunitgroup=@idunitgroup,isSingleUnit=@isSingleUnit,idSubUnitByReport=@idSubUnitByReport,idUnitByStock=@idUnitByStock,
|
idUnitByPurchase=@idUnitByPurchase,idUnitBySale=@idUnitBySale,idunitbymanufacture=@idunitbymanufacture,idinventoryclass=@idinventoryclass,isPurchase=@isPurchase,isSale=@isSale,isMadeSelf=@isMadeSelf,
|
isMaterial=@isMaterial,isMadeRequest=@isMadeRequest,idwarehouse=@idwarehouse,status=@status,lm_user=@lm_user,lm_date=@lm_date where partcode=@partcode";
|
list.Add(new
|
{
|
str = sql,
|
parm = new
|
{
|
partcode = partcode,
|
partname = partname,
|
partspec = partspec,
|
idunit = unittypcode == "1" ? unitcode : unitsubcode,
|
idunitgroup = unittypcode == "0" ? "1" : "0",
|
isSingleUnit = unittypcode,
|
idSubUnitByReport = idSubUnitByReport,
|
idUnitByStock = idUnitByStock,
|
idUnitByPurchase = idUnitByPurchase,
|
idUnitBySale = idUnitBySale,
|
idunitbymanufacture = idunitbymanufacture,
|
idinventoryclass = inventoryclasscode,
|
isPurchase = isPurchase,
|
isSale = isSale,
|
isMadeSelf = isMadeSelf,
|
isMaterial = isMaterial,
|
isMadeRequest = isMadeRequest,
|
idwarehouse = idwarehouse,
|
status = status,
|
lm_user = us.usercode,
|
lm_date = DateTime.Now.ToString()
|
}
|
});
|
bool aa = DapperHelper.DoTransaction(list);
|
if (aa)
|
{
|
//写入操作记录表
|
LogHelper.DbOperateLog(us.usercode, "修改", "修改了存货档案:"+ partcode, 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 TMaterielDelete(string data_sources, string partcode, User us)
|
{
|
string sql = "";
|
List<object> list = new List<object>();
|
var dynamicParams = new DynamicParameters();
|
try
|
{
|
//判断存货档案是否被订单引用
|
sql = @"select * from TKimp_Ewo where materiel_code=@partcode";
|
dynamicParams.Add("@partcode", partcode);
|
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_Man where materiel_code=@partcode";
|
dynamicParams.Add("@partcode", partcode);
|
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 TMateriel_Info where partcode=@partcode and data_sources=@data_sources";
|
list.Add(new
|
{
|
str = sql,
|
parm = new
|
{
|
partcode = partcode,
|
data_sources= data_sources
|
}
|
});
|
bool aa = DapperHelper.DoTransaction(list);
|
if (aa)
|
{
|
//写入操作记录表
|
LogHelper.DbOperateLog(us.usercode, "删除", "删除了存货档案:"+partcode, 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 TMaterielFileAssociationRoute(string partcode,User us)
|
{
|
string sql = "";
|
var dynamicParams = new DynamicParameters();
|
List<MaterialRout> list = new List<MaterialRout>();
|
try
|
{
|
dynamic dynObj = JObject.Parse(us.mesSetting);
|
bool route = dynObj.route;
|
if (route) //工艺路线版
|
{
|
//获取工艺路线集合(包含物料绑定工艺路线标识)
|
sql = @"select distinct A.code as routecode,A.name as routename,(case when B.route_code is null then 'N' else 'Y' end) flag from TFlw_Rout A
|
left join (
|
select distinct route_code from TMateriel_Route where materiel_code=@partcode and is_delete<>'1'
|
) B
|
on A.code=B.route_code
|
where A.is_delete='0' and A.enable='Y'";
|
dynamicParams.Add("@partcode", partcode);
|
var dt = DapperHelper.selectdata(sql, dynamicParams);
|
for (int i = 0; i < dt.Rows.Count; i++)
|
{
|
MaterialRout rout = new MaterialRout();
|
rout.code = dt.Rows[i]["ROUTECODE"].ToString();
|
rout.name = dt.Rows[i]["ROUTENAME"].ToString();
|
rout.flag = dt.Rows[i]["FLAG"].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.data = list;
|
}
|
else //工序版
|
{
|
//通过产品编码查找关联的工序信息
|
sql = @"select A.step_seq,A.step_code,S.stepname as step_name,A.isbott,A.isend
|
from (
|
select materiel_code,step_seq,step_code,isbott,isend from TMateriel_Step
|
where materiel_code=@partcode
|
) as A
|
left join TStep S on A.step_code=S.stepcode";
|
dynamicParams.Add("@partcode", partcode);
|
var data = DapperHelper.selectdata(sql, dynamicParams);
|
mes.data = data;
|
}
|
mes.code = "200";
|
mes.message = "查询成功!";
|
}
|
catch (Exception e)
|
{
|
mes.code = "300";
|
mes.count = 0;
|
mes.message = e.Message;
|
mes.data = null;
|
}
|
return mes;
|
}
|
#endregion
|
|
#region[存货档案关联工艺(工艺路线或工序)查询多存货]
|
public static ToMessage TMaterielFileAssociationRouteList(List<string> partcode, User us)
|
{
|
string sql = "";
|
var dynamicParams = new DynamicParameters();
|
List<MaterialRoutList> list = new List<MaterialRoutList>();
|
try
|
{
|
dynamic dynObj = JObject.Parse(us.mesSetting);
|
bool route = dynObj.route;
|
if (route) //工艺路线版
|
{
|
// 1. 批量获取所有物料的基础信息
|
var baseInfoSql = @"SELECT M.partcode, M.partname, M.default_route as default_routecode, R.name as default_routename
|
FROM TMateriel_Info M
|
LEFT JOIN TFlw_Rout R ON M.default_route = R.code
|
WHERE M.partcode IN @PartCodes";
|
var baseInfos = DapperHelper.select<dynamic>(baseInfoSql, new { PartCodes = partcode }).ToDictionary(x => (string)x.partcode, x => x);
|
if (!baseInfos.Any())
|
{
|
mes.code = "200";
|
mes.message = "未找到对应的物料信息";
|
mes.data = list;
|
return mes;
|
}
|
// 2. 批量获取所有物料的工艺路线信息
|
var routeSql = @"SELECT mi.partcode AS partcode,fr.code AS routecode,fr.name AS routename,
|
CASE
|
WHEN mr.materiel_code IS NOT NULL THEN 'Y'
|
ELSE 'N'
|
END AS flag
|
FROM TMateriel_Info mi
|
CROSS JOIN TFlw_Rout fr
|
LEFT JOIN TMateriel_Route mr ON mi.partcode = mr.materiel_code AND fr.code = mr.route_code
|
WHERE mi.partcode IN @PartCodes AND fr.enable = 'Y'
|
ORDER BY mi.partcode, fr.code";
|
var allRoutes = DapperHelper.select<dynamic>(routeSql, new { PartCodes = partcode }).GroupBy(x => (string)x.partcode).ToDictionary(g => g.Key, g => g.ToList());
|
|
// 3. 批量获取所有工序信息（一次性查询）
|
var allRouteCodes = allRoutes.Values
|
.SelectMany(routes => routes.Select(r => (string)r.routecode))
|
.Distinct()
|
.ToList();
|
|
Dictionary<string, DataTable> routeSteps = new Dictionary<string, DataTable>();
|
|
if (allRouteCodes.Any())
|
{
|
var stepSql = @"SELECT A.rout_code as route_code, 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 IN @RouteCodes AND B.is_delete <> '1'
|
ORDER BY A.rout_code, A.seq ASC";
|
var allSteps = DapperHelper.select<dynamic>(stepSql, new { RouteCodes = allRouteCodes });
|
// 将工序数据按工艺路线编码分组
|
routeSteps = allSteps
|
.GroupBy(x => (string)x.route_code)
|
.ToDictionary(
|
g => g.Key,
|
g =>
|
{
|
// 将动态类型转换为DataTable（根据您原有代码的返回类型调整）
|
var dt = new DataTable();
|
dt.Columns.Add("seq");
|
dt.Columns.Add("stepcode");
|
dt.Columns.Add("stepname");
|
dt.Columns.Add("enable");
|
|
foreach (var step in g.OrderBy(s => s.seq))
|
{
|
dt.Rows.Add(step.seq, step.stepcode, step.stepname, step.enable);
|
}
|
return dt;
|
});
|
}
|
// 4. 在内存中组合数据
|
foreach (var part in partcode)
|
{
|
if (!baseInfos.ContainsKey(part)) continue;
|
|
var baseInfo = baseInfos[part];
|
var routlist = new MaterialRoutList
|
{
|
partcode = baseInfo.partcode,
|
partname = baseInfo.partname,
|
defalutroutecode = baseInfo.default_routecode,
|
defalutroutename = baseInfo.default_routename,
|
children = new List<RouteList>()
|
};
|
|
// 添加该物料的工艺路线
|
if (allRoutes.ContainsKey(part))
|
{
|
foreach (var routeInfo in allRoutes[part])
|
{
|
var rout = new RouteList
|
{
|
routecode = routeInfo.routecode,
|
routename = routeInfo.routename,
|
flag = routeInfo.flag
|
};
|
|
// 添加工序信息
|
if (routeSteps.ContainsKey(rout.routecode))
|
{
|
rout.Data = routeSteps[rout.routecode];
|
}
|
|
routlist.children.Add(rout);
|
}
|
}
|
|
list.Add(routlist);
|
}
|
|
mes.data = list;
|
mes.code = "200";
|
mes.message = $"查询成功，共处理{list.Count}个物料";
|
}
|
else //工序版
|
{
|
// 1. 批量获取所有物料基础信息
|
var baseInfoSql = @"SELECT partcode, partname FROM TMateriel_Info
|
WHERE partcode IN @PartCodes";
|
var baseInfos = DapperHelper.select<dynamic>(baseInfoSql, new { PartCodes = partcode })
|
.ToDictionary(x => (string)x.partcode, x => x);
|
|
if (!baseInfos.Any())
|
{
|
mes.code = "200";
|
mes.message = "未找到对应的物料信息";
|
mes.data = new List<MaterialRoutList>();
|
return mes;
|
}
|
|
// 2. 批量获取所有物料的工序信息
|
var stepSql = @"SELECT A.materiel_code as partcode, A.step_seq, A.step_code,
|
S.stepname, A.isbott, A.isend
|
FROM TMateriel_Step A
|
INNER JOIN TStep S ON A.step_code = S.stepcode
|
WHERE A.materiel_code IN @PartCodes AND S.is_delete <> '1'
|
ORDER BY A.materiel_code, A.step_seq";
|
|
var allSteps = DapperHelper.select<dynamic>(stepSql, new { PartCodes = partcode })
|
.GroupBy(x => (string)x.partcode)
|
.ToDictionary(g => g.Key, g => g.ToList());
|
|
// 3. 内存中组合数据
|
foreach (var part in partcode)
|
{
|
if (!baseInfos.ContainsKey(part)) continue;
|
|
var baseInfo = baseInfos[part];
|
var routlist = new MaterialRoutList
|
{
|
partcode = baseInfo.partcode,
|
partname = baseInfo.partname,
|
children = new List<RouteList>()
|
};
|
|
// 添加工序信息
|
if (allSteps.ContainsKey(part))
|
{
|
// 转换为DataTable（如需保持原有结构）
|
var stepData = allSteps[part];
|
var dataTable = new DataTable();
|
dataTable.Columns.Add("step_seq");
|
dataTable.Columns.Add("step_code");
|
dataTable.Columns.Add("step_name");
|
dataTable.Columns.Add("isbott");
|
dataTable.Columns.Add("isend");
|
|
foreach (var step in stepData.OrderBy(s => s.step_seq))
|
{
|
dataTable.Rows.Add(step.step_seq, step.step_code,
|
step.stepname, step.isbott, step.isend);
|
}
|
|
routlist.children.Add(new RouteList { Data = dataTable });
|
}
|
|
list.Add(routlist);
|
}
|
|
mes.data = list;
|
mes.code = "200";
|
mes.message = $"查询成功，共处理{list.Count}个物料";
|
}
|
}
|
catch (Exception e)
|
{
|
mes.code = "300";
|
mes.count = 0;
|
mes.message = e.Message;
|
mes.data = null;
|
}
|
return mes;
|
}
|
#endregion
|
|
#region[存货档案关联工艺(工艺路线或工序)提交]
|
public static ToMessage SaveTMaterielFile(User us, List<MaterialCraftsSave> json)
|
{
|
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) //工艺路线版
|
{
|
if (json == null || json.Count <= 0)
|
{
|
//清除物料关联工艺路线表数据
|
sql = @"delete TMateriel_Route where materiel_code=@partcode";
|
list.Add(new { str = sql, parm = new { partcode = json[0].partcode } });
|
//标记物料表关联工艺路线标识
|
sql = @"update TMateriel_Info set is_retdproc='N',default_route='' where partcode=@partcode";
|
list.Add(new { str = sql, parm = new { partcode = json[0].partcode } });
|
//清除节拍工价表对应数据
|
sql = @"delete TPrteEqp_Stad where materiel_code=@partcode";
|
list.Add(new { str = sql, parm = new { partcode = json[0].partcode } });
|
}
|
else
|
{
|
//清除存货关联工艺路线表数据
|
sql = @"delete TMateriel_Route where materiel_code=@partcode";
|
list.Add(new { str = sql, parm = new { partcode = json[0].partcode } });
|
//循环写入存货关联工艺路线表
|
for (int i = 0; i < json[0].children.Count; i++)
|
{
|
sql = @"insert into TMateriel_Route(materiel_code,route_code,lm_user,lm_date) values(@materiel_code,@route_code,@lm_user,@lm_date)";
|
list.Add(new
|
{
|
str = sql,
|
parm = new
|
{
|
materiel_code = json[0].partcode,
|
route_code = json[0].children[i].code,
|
lm_user = us.usercode,
|
lm_date = DateTime.Now.ToString()
|
}
|
});
|
}
|
//标记物料表关联工艺路线标识
|
sql = @"update TMateriel_Info set is_retdproc='Y',default_route=@defaultroute_code where partcode=@partcode";
|
list.Add(new { str = sql, parm = new { partcode = json[0].partcode, defaultroute_code = json[0].defaultroute_code } });
|
|
//清除节拍工价表当前产品对应工艺路线不包含此次设置的其它数据
|
//string分割转string[]
|
string[] routcode = Array.ConvertAll<string, string>(string.Join(",", json[0].children.Select(s => s.code)).Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries), s => s.ToString());
|
sql = @"delete TPrteEqp_Stad where materiel_code=@partcode and route_code not in @routecode";
|
list.Add(new { str = sql, parm = new { partcode = json[0].partcode, routecode= routcode } });
|
}
|
}
|
else //工序版
|
{
|
if (json == null || json.Count <= 0)
|
{
|
//清除物料关联工艺路线表数据
|
sql = @"delete TMateriel_Step where materiel_code=@partcode";
|
list.Add(new { str = sql, parm = new { partcode = json[0].partcode } });
|
//标记物料表关联工艺路线标识
|
sql = @"update TMateriel_Info set is_retdproc='N',default_route='' where partcode=@partcode";
|
list.Add(new { str = sql, parm = new { partcode = json[0].partcode } });
|
//清除节拍工价表对应数据
|
sql = @"delete TPrteEqp_Stad where materiel_code=@partcode";
|
list.Add(new { str = sql, parm = new { partcode = json[0].partcode } });
|
}
|
else
|
{
|
//清除存货关联工艺路线表数据
|
sql = @"delete TMateriel_Step where materiel_code=@partcode";
|
list.Add(new { str = sql, parm = new { partcode = json[0].partcode } });
|
//循环写入存货关联工艺路线表
|
for (int i = 0; i < json[0].children.Count; i++)
|
{
|
sql = @"insert into TMateriel_Step(materiel_code,step_seq,step_code,isbott,isend,lm_user,lm_date)
|
values(@materiel_code,@step_seq,@step_code,@isbott,@isend,@lm_user,@lm_date)";
|
list.Add(new
|
{
|
str = sql,
|
parm = new
|
{
|
materiel_code = json[0].partcode,
|
step_seq = json[0].children[i].seq,
|
step_code = json[0].children[i].code,
|
isbott = json[0].children[i].isbott,
|
isend = json[0].children[i].isend,
|
lm_user = us.usercode,
|
lm_date = DateTime.Now.ToString()
|
}
|
});
|
}
|
//标记物料表关联工艺路线标识
|
sql = @"update TMateriel_Info set is_retdproc='Y',default_route=@defaultroute_code where partcode=@partcode";
|
list.Add(new { str = sql, parm = new { partcode = json[0].partcode, defaultroute_code = json[0].defaultroute_code } });
|
|
//清除节拍工价表当前产品对应工序不包含此次设置的其它数据
|
//string分割转string[]
|
string[] stepcode = Array.ConvertAll<string, string>(string.Join(",", json[0].children.Select(s => s.code)).Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries), s => s.ToString());
|
sql = @"delete TPrteEqp_Stad where materiel_code=@partcode and step_code not in @stepcode";
|
list.Add(new { str = sql, parm = new { partcode = json[0].partcode, stepcode = stepcode } });
|
}
|
}
|
bool aa = DapperHelper.DoTransaction(list);
|
if (aa)
|
{
|
//写入操作记录表
|
LogHelper.DbOperateLog(us.usercode, "存货关联工艺", "产品："+json[0].partcode+"关联了工艺:" + string.Join(",", json[0].children.Select(s => s.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 SaveTMaterielFileList(User us, List<MaterialCraftsSave> json)
|
{
|
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.Count; i++)
|
{
|
if (json[i].children == null || json[i].children.Count <= 0)
|
{
|
//清除物料关联工艺路线表数据
|
sql = @"delete TMateriel_Route where materiel_code=@partcode";
|
list.Add(new { str = sql, parm = new { partcode = json[i].partcode } });
|
//标记物料表关联工艺路线标识
|
sql = @"update TMateriel_Info set is_retdproc='N',default_route='' where partcode=@partcode";
|
list.Add(new { str = sql, parm = new { partcode = json[i].partcode } });
|
//清除节拍工价表对应数据
|
sql = @"delete TPrteEqp_Stad where materiel_code=@partcode";
|
list.Add(new { str = sql, parm = new { partcode = json[i].partcode } });
|
}
|
else
|
{
|
//清除存货关联工艺路线表数据
|
sql = @"delete TMateriel_Route where materiel_code=@partcode";
|
list.Add(new { str = sql, parm = new { partcode = json[i].partcode } });
|
//循环写入存货关联工艺路线表
|
for (int j = 0; j < json[i].children.Count; j++)
|
{
|
sql = @"insert into TMateriel_Route(materiel_code,route_code,lm_user,lm_date) values(@materiel_code,@route_code,@lm_user,@lm_date)";
|
list.Add(new
|
{
|
str = sql,
|
parm = new
|
{
|
materiel_code = json[i].partcode,
|
route_code = json[i].children[j].code,
|
lm_user = us.usercode,
|
lm_date = DateTime.Now.ToString()
|
}
|
});
|
}
|
//标记物料表关联工艺路线标识
|
sql = @"update TMateriel_Info set is_retdproc='Y',default_route=@defaultroute_code where partcode=@partcode";
|
list.Add(new { str = sql, parm = new { partcode = json[i].partcode, defaultroute_code = json[i].defaultroute_code } });
|
|
//清除节拍工价表当前产品对应工艺路线不包含此次设置的其它数据
|
//string分割转string[]
|
string[] routcode = Array.ConvertAll<string, string>(string.Join(",", json[i].children.Select(s => s.code)).Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries), s => s.ToString());
|
sql = @"delete TPrteEqp_Stad where materiel_code=@partcode and route_code not in @routecode";
|
list.Add(new { str = sql, parm = new { partcode = json[i].partcode, routecode = routcode } });
|
}
|
}
|
}
|
else //工序版
|
{
|
for (int i = 0; i < json.Count; i++)
|
{
|
if (json[i].children == null || json[i].children.Count <= 0)
|
{
|
//清除物料关联工艺路线表数据
|
sql = @"delete TMateriel_Step where materiel_code=@partcode";
|
list.Add(new { str = sql, parm = new { partcode = json[i].partcode } });
|
//标记物料表关联工艺路线标识
|
sql = @"update TMateriel_Info set is_retdproc='N',default_route='' where partcode=@partcode";
|
list.Add(new { str = sql, parm = new { partcode = json[i].partcode } });
|
//清除节拍工价表对应数据
|
sql = @"delete TPrteEqp_Stad where materiel_code=@partcode";
|
list.Add(new { str = sql, parm = new { partcode = json[i].partcode } });
|
}
|
else
|
{
|
//清除存货关联工艺路线表数据
|
sql = @"delete TMateriel_Step where materiel_code=@partcode";
|
list.Add(new { str = sql, parm = new { partcode = json[i].partcode } });
|
//循环写入存货关联工艺路线表
|
for (int j = 0; j < json[i].children.Count; j++)
|
{
|
sql = @"insert into TMateriel_Step(materiel_code,step_seq,step_code,isbott,isend,lm_user,lm_date)
|
values(@materiel_code,@step_seq,@step_code,@isbott,@isend,@lm_user,@lm_date)";
|
list.Add(new
|
{
|
str = sql,
|
parm = new
|
{
|
materiel_code = json[i].partcode,
|
step_seq = json[i].children[j].seq,
|
step_code = json[i].children[j].code,
|
isbott = json[i].children[j].isbott,
|
isend = json[i].children[j].isend,
|
lm_user = us.usercode,
|
lm_date = DateTime.Now.ToString()
|
}
|
});
|
}
|
//标记物料表关联工艺路线标识
|
sql = @"update TMateriel_Info set is_retdproc='Y',default_route=@defaultroute_code where partcode=@partcode";
|
list.Add(new { str = sql, parm = new { partcode = json[i].partcode, defaultroute_code = json[i].defaultroute_code } });
|
|
//清除节拍工价表当前产品对应工序不包含此次设置的其它数据
|
//string分割转string[]
|
string[] stepcode = Array.ConvertAll<string, string>(string.Join(",", json[i].children.Select(s => s.code)).Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries), s => s.ToString());
|
sql = @"delete TPrteEqp_Stad where materiel_code=@partcode and step_code not in @stepcode";
|
list.Add(new { str = sql, parm = new { partcode = json[i].partcode, stepcode = stepcode } });
|
}
|
}
|
}
|
bool aa = DapperHelper.DoTransaction(list);
|
if (aa)
|
{
|
//写入操作记录表
|
LogHelper.DbOperateLog(us.usercode, "存货关联工艺", "产品：" + string.Join(",", json.Select(x => x.partcode)) + "关联了工艺:" + string.Join(",", json.SelectMany(x => x.children?.Select(c => c.code) ?? Enumerable.Empty<string>())), 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 PartSelect()
|
{
|
string sql = "";
|
try
|
{
|
//获取物料数据
|
sql = @"select M.partcode,M.partname,M.partspec,M.idunit as uom_code,T.name as uom_name,
|
M.idunitgroup as stocktype_code,D.name as stocktype_name,
|
M.idwarehouse as stck_code,S.name as stck_name,M.default_route
|
from TMateriel_Info M
|
left join TUnit T on M.idunit=T.code
|
left join TUnitGroup D on M.idunitgroup=D.code
|
left join TSecStck S on M.idwarehouse=S.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
|
}
|
}
|
