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.Bom;
|
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参数数组
|
public static string sqlServerConnectString = AppSetting.GetAppSetting("DBServer");
|
|
|
#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 AA.idinventoryclass in @classcode ";
|
dynamicParams.Add("@classcode", classcode);
|
}
|
if (partcode != "" && partcode != null)
|
{
|
search += "and AA.partcode like '%'+@partcode+'%' ";
|
dynamicParams.Add("@partcode", partcode);
|
}
|
if (partname != "" && partname != null)
|
{
|
search += "and AA.partname like '%'+@partname+'%' ";
|
dynamicParams.Add("@partname", partname);
|
}
|
if (partspec != "" && partspec != null)
|
{
|
search += "and AA.partspec like '%'+@partspec+'%' ";
|
dynamicParams.Add("@partspec", partspec);
|
}
|
sql = @"select * from (
|
select
|
M.partcode,M.partname,M.partspec,M.idinventoryclass,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
|
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 (M.isSingleUnit = '1' and (T.isSingleUnit = '1' OR T.isSingleUnit IS NULL))
|
or (M.isSingleUnit = '0' AND(
|
(T.isSingleUnit = '0' AND M.idunitgroup = T.idunitgroup)
|
OR
|
(R.isSingleUnit = '0' AND M.idunitgroup = R.idunitgroup)
|
OR
|
(K.isSingleUnit = '0' AND M.idunitgroup = K.idunitgroup)
|
OR
|
(P.isSingleUnit = '0' AND M.idunitgroup = P.idunitgroup)
|
OR
|
(S.isSingleUnit = '0' AND M.idunitgroup = S.idunitgroup)
|
OR
|
(F.isSingleUnit = '0' AND M.idunitgroup = F.idunitgroup)
|
))
|
) as AA 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 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 TPrteStep_Price 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 TPrteStep_Price 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 TPrteStep_Price 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 TPrteStep_Price 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 BomMainData(string parentpartcode, string parentpartname, string parentpartspec, string version, string createuser, string operopendate, string operclosedate, string status, int startNum, int endNum, string prop, string order)
|
{
|
var dynamicParams = new DynamicParameters();
|
List<BomDate> parents = new List<BomDate>();
|
string search = "", sql = "";
|
try
|
{
|
if (parentpartcode != "" && parentpartcode != null)
|
{
|
search += "and AA.materiel_code like '%'+@parentpartcode+'%' ";
|
dynamicParams.Add("@parentpartcode", parentpartcode);
|
}
|
if (parentpartname != "" && parentpartname != null)
|
{
|
search += "and AA.materiel_name like '%'+@parentpartname+'%' ";
|
dynamicParams.Add("@parentpartname", parentpartname);
|
}
|
if (parentpartspec != "" && parentpartspec != null)
|
{
|
search += "and AA.materiel_spec like '%'+@parentpartspec+'%' ";
|
dynamicParams.Add("@parentpartspec", parentpartspec);
|
}
|
if (version != "" && version != null)
|
{
|
search += "and AA.version like '%'+@version+'%' ";
|
dynamicParams.Add("@version", version);
|
}
|
if (createuser != "" && createuser != null)
|
{
|
search += "and AA.lm_user like '%'+@createuser+'%' ";
|
dynamicParams.Add("@createuser", createuser);
|
}
|
if (operopendate != "" && operopendate != null)
|
{
|
search += "and AA.lm_date between @operopendate and @operclosedate ";
|
dynamicParams.Add("@operopendate", operopendate + " 00:00:00");
|
dynamicParams.Add("@operclosedate", operclosedate + " 23:59:59");
|
}
|
var total = 0; //总条数
|
//查询Bom主表信息
|
sql = @"select * from(
|
select B.id,B.materiel_code,M.partname as materiel_name,M.partspec as materiel_spec,
|
M.idinventoryclass as materiel_typecode,C.name as materiel_typename,M.idunit as unitcode,T.name as unitname,
|
B.quantity,B.status,B.startdate,B.version,B.isdefaultbom,B.bomdepth,U.username as lm_user,B.lm_date
|
from TBom_Main B
|
left join TMateriel_Info M on B.materiel_code=M.partcode
|
left join TUnit T on M.idunit=T.code
|
left join TMateriel_Class C on M.idinventoryclass=C.code
|
left join TUser U on B.lm_user=U.usercode
|
where (M.isSingleUnit = '1' and (T.isSingleUnit = '1' OR T.isSingleUnit IS NULL))
|
or (M.isSingleUnit = '0' AND(
|
(T.isSingleUnit = '0' AND M.idunitgroup = T.idunitgroup)
|
))
|
) as AA " + search;
|
parents = DapperHelper.GetPageListData<BomDate>(sql, dynamicParams, prop, order, startNum, endNum, out total);
|
for (int i = 0; i < parents.Count; i++)
|
{
|
//通过Bom主表id查找子件信息
|
sql = @"select * from(
|
select S.id,S.m_id,S.seq,S.materiel_code,M.partname as materiel_name,M.partspec as materiel_spec,
|
M.idunit as unitcode,T.name as unitname,S.base_quantity,S.loss_quantity,S.total_quantity,S.idchildbom
|
from TBom_Deta S
|
left join TMateriel_Info M on S.materiel_code=M.partcode
|
left join TUnit T on M.idunit=T.code
|
where (M.isSingleUnit = '1' and (T.isSingleUnit = '1' OR T.isSingleUnit IS NULL))
|
or (M.isSingleUnit = '0' AND(
|
(T.isSingleUnit = '0' AND M.idunitgroup = T.idunitgroup)
|
))
|
) as AA where AA.m_id=@m_id";
|
dynamicParams.Add("@m_id", parents[i].id);
|
var children = DapperHelper.select<BomDateSub>(sql, dynamicParams);
|
parents[i].children = children.ToList();
|
}
|
mes.code = "200";
|
mes.count = total;
|
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[物料清单新增修改获取父件信息-T+畅捷通模式]
|
public static ToMessage AddUpdateTBomParentData(User us)
|
{
|
string sql = "";
|
var dynamicParams = new DynamicParameters();
|
try
|
{
|
//根据存货信息,查找自制属性或委外属性物料
|
sql = @"select * from (
|
select M.partcode,M.partname,M.partspec,M.idunit as unitcode,U.name as unitname,M.isMadeSelf,M.isMadeRequest,
|
(case when M.idunitgroup='0' then NUll else M.idunitgroup end) as idunitgroup
|
from TMateriel_Info M
|
left join TUnit U on M.idunitgroup=U.idunitgroup and M.idunit=U.code and M.IsSingleUnit=U.IsSingleUnit
|
where M.isSingleUnit='0' and U.IsMainUnit='1' and M.Status='0'
|
) as AA where AA.isMadeSelf='1' or AA.isMadeRequest='1'
|
union all
|
select * from (
|
select M.partcode,M.partname,M.partspec,M.idunit as unitcode,U.name as unitname,M.isMadeSelf,M.isMadeRequest,
|
(case when M.idunitgroup='0' then NUll else M.idunitgroup end) as idunitgroup
|
from TMateriel_Info M
|
inner join TUnit U on M.idunit=U.code and M.IsSingleUnit=U.IsSingleUnit
|
where M.IsSingleUnit='1' and U.IsMainUnit='0' and M.Status='0'
|
) as AA where AA.isMadeSelf='1' or AA.isMadeRequest='1'";
|
var data = DapperHelper.selecttable(sql);
|
mes.code = "200";
|
mes.message = "查询成功!";
|
mes.data = data;
|
}
|
catch (Exception e)
|
{
|
mes.code = "300";
|
mes.count = 0;
|
mes.message = e.Message;
|
mes.data = null;
|
}
|
return mes;
|
}
|
#endregion
|
|
#region[物料清单新增修改根据父件信息的计量单位组编码查找计量单位组信息-T+畅捷通模式]
|
public static ToMessage AddUpdateTBomUnitGroup(string idunitgroup)
|
{
|
var dynamicParams = new DynamicParameters();
|
string sql = "";
|
try
|
{
|
//获取计量单位组信息
|
sql = @"select code as unitcode,name as unitname
|
from TUnit where idunitgroup=@idunitgroup";
|
dynamicParams.Add("@idunitgroup", idunitgroup);
|
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 AddUpdateTBomMaxVsion(string parentpartcode)
|
{
|
string sql = "";
|
var dynamicParams = new DynamicParameters();
|
try
|
{
|
sql = @"select isnull(max(substring(version,charindex('V',version)+1,len(version)-charindex('V',version))),0)+1 as version
|
from TBom_Main where materiel_code=@parentpartcode";
|
dynamicParams.Add("@parentpartcode", parentpartcode);
|
var data = DapperHelper.selectdata(sql, dynamicParams);
|
if (data.Rows.Count > 0)
|
{
|
mes.code = "200";
|
mes.message = "获取版本号成功!";
|
mes.data = "V" + data.Rows[0]["version"].ToString();
|
}
|
else
|
{
|
mes.code = "300";
|
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[物料清单新增修改获取子件信息-T+畅捷通模式]
|
public static ToMessage AddUpdateTBomDetailsData(User us)
|
{
|
string sql = "";
|
var dynamicParams = new DynamicParameters();
|
try
|
{
|
//根据存货信息,查找生产耗用的物料
|
sql = @"select * from (
|
select M.partcode,M.partname,M.partspec,M.idunit as unitcode,U.name as unitname,M.IsMaterial,
|
(case when M.idunitgroup='0' then NUll else M.idunitgroup end) as idunitgroup
|
from TMateriel_Info M
|
left join TUnit U on M.idunitgroup=U.idunitgroup and M.idunit=U.code and M.IsSingleUnit=U.IsSingleUnit
|
where M.isSingleUnit='0' and U.IsMainUnit='1' and M.Status='0'
|
) as AA where AA.IsMaterial='1'
|
union all
|
select * from (
|
select M.partcode,M.partname,M.partspec,M.idunit as unitcode,U.name as unitname,M.IsMaterial,
|
(case when M.idunitgroup='0' then NUll else M.idunitgroup end) as idunitgroup
|
from TMateriel_Info M
|
inner join TUnit U on M.idunit=U.code and M.IsSingleUnit=U.IsSingleUnit
|
where M.IsSingleUnit='1' and U.IsMainUnit='0' and M.Status='0'
|
) as AA where AA.IsMaterial='1' ";
|
var data = DapperHelper.selecttable(sql);
|
mes.code = "200";
|
mes.message = "查询成功!";
|
mes.data = data;
|
}
|
catch (Exception e)
|
{
|
mes.code = "300";
|
mes.count = 0;
|
mes.message = e.Message;
|
mes.data = null;
|
}
|
return mes;
|
}
|
#endregion
|
|
#region[物料清单新增修改获取子件Bom-T+畅捷通模式]
|
public static ToMessage AddUpdateTBomDetailsBomData(string materiel_code, User us)
|
{
|
string sql = "";
|
var dynamicParams = new DynamicParameters();
|
try
|
{
|
//根据子件物料编码查询子件对应的Bom信息
|
sql = @"select B.id,B.materiel_code,M.partname as materiel_name,M.partspec as materiel_spec,B.version
|
from TBom_Main B
|
left join TMateriel_Info M on B.materiel_code=M.partcode
|
where B.materiel_code=@materiel_code";
|
dynamicParams.Add("@materiel_code", materiel_code);
|
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[物料清单新增修改提交-T+畅捷通模式]
|
public static ToMessage TBomAddUpdate(string operType, BomDate bom, User us)
|
{
|
string sql = "";
|
List<object> list = new List<object>();
|
var dynamicParams = new DynamicParameters();
|
using (var conn = new SqlConnection(sqlServerConnectString))
|
{
|
conn.Open();
|
using (var transaction = conn.BeginTransaction())
|
{
|
try
|
{
|
if (operType == "Add")
|
{
|
// 假设你已经有了插入主表和子表的SQL语句
|
string masterUpdateversionSql = @"update TBom_Main set isdefaultbom='0' where materiel_code=@materiel_code";
|
string masterInsertSql = @"insert into TBom_Main(materiel_code,unitcode,bomdepth,isdefaultbom,quantity,status,startdate,version,lm_user,lm_date)
|
values(@materiel_code,@unitcode,@bomdepth,@isdefaultbom,@quantity,@status,@startdate,@version,@lm_user,@lm_date); SELECT CAST(SCOPE_IDENTITY() AS INT)";
|
string detailInsertSql = @"insert into TBom_Deta(m_id,seq,materiel_code,unitcode,base_quantity,loss_quantity,total_quantity,idchildbom)
|
values(@m_id,@seq,@materiel_code,@unitcode,@base_quantity,@loss_quantity,@total_quantity,@idchildbom)";
|
string detailUpdateSql = @"update TBom_Deta set idchildbom=@idchildbom where materiel_code=@materiel_code";
|
|
|
//查询当前父件物料+版本号是否存在
|
sql = @"select * from TBom_Main where materiel_code=@materiel_code and version=@version";
|
dynamicParams.Add("@materiel_code", bom.materiel_code);
|
dynamicParams.Add("@Version", bom.version);
|
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 TBom_Main where materiel_code=@materiel_code";
|
dynamicParams.Add("@materiel_code", bom.materiel_code);
|
var data1 = DapperHelper.selectdata(sql, dynamicParams);
|
if (data1.Rows.Count > 0) //存在当前物料的其他版本BOM
|
{
|
if (bom.isdefaultbom == "1") //默认Bom为:是
|
{
|
//更新物料清单对应其他非此版本的是否默认BOM
|
var updateversionParam = new DynamicParameters();
|
updateversionParam.Add("@materiel_code", bom.materiel_code);
|
conn.Execute(masterUpdateversionSql, updateversionParam, transaction);
|
}
|
}
|
else
|
{
|
if (bom.isdefaultbom == "0") //默认Bom为:否
|
{
|
mes.code = "300";
|
mes.count = 0;
|
mes.message = "一个产品只能有一个且必须有一个默认BOM!";
|
mes.data = null;
|
return mes;
|
}
|
}
|
|
// 插入主表并获取新插入的ID
|
var masterParam = new DynamicParameters();
|
masterParam.Add("@materiel_code", bom.materiel_code);
|
masterParam.Add("@unitcode", bom.unitcode);
|
masterParam.Add("@bomdepth", bom.bomdepth);
|
masterParam.Add("@isdefaultbom", bom.isdefaultbom);
|
masterParam.Add("@quantity", bom.quantity);
|
masterParam.Add("@status", bom.status);
|
masterParam.Add("@startdate", bom.startdate);
|
masterParam.Add("@version", bom.version);
|
masterParam.Add("@lm_user", us.usercode);
|
masterParam.Add("@lm_date", DateTime.Now);
|
|
int masterId = Convert.ToInt32(conn.ExecuteScalar(masterInsertSql, masterParam, transaction));
|
|
// 插入子表
|
foreach (var detail in bom.children)
|
{
|
var detailParam = new DynamicParameters();
|
detailParam.Add("@m_id", masterId);
|
detailParam.Add("@seq", detail.seq);
|
detailParam.Add("@materiel_code", detail.materiel_code);
|
detailParam.Add("@unitcode", detail.unitcode);
|
detailParam.Add("@base_quantity", detail.base_quantity);
|
detailParam.Add("@loss_quantity", detail.loss_quantity);
|
detailParam.Add("@total_quantity", detail.total_quantity);
|
detailParam.Add("@idchildbom", string.IsNullOrEmpty(detail.idchildbom) ? null : detail.idchildbom);
|
conn.Execute(detailInsertSql, detailParam, transaction);
|
}
|
//更新子表idchildbom
|
var detailUpdateParam = new DynamicParameters();
|
detailUpdateParam.Add("@idchildbom", masterId);
|
detailUpdateParam.Add("@materiel_code", bom.materiel_code);
|
conn.Execute(detailUpdateSql, detailUpdateParam, transaction);
|
|
// 所有插入成功，提交事务
|
transaction.Commit();
|
//更新Bom层级
|
var bomdepthParam = new DynamicParameters();
|
bomdepthParam.Add("@bomId", masterId);
|
bomdepthParam.Add("@isAllFlag", 1);
|
var dt = DapperHelper.selectProcedure("AA_SP_UpdateBomDepth", bomdepthParam);
|
mes.code = "200";
|
mes.count = 0;
|
mes.message = "新增操作成功!";
|
mes.data = null;
|
//写入操作记录表
|
LogHelper.DbOperateLog(us.usercode, "新增", "新增了物料清单：" + bom.materiel_code + "，版本号为:" + bom.version, us.usertype);
|
}
|
if (operType == "Update")
|
{
|
// 假设你已经有了插入的SQL语句
|
string masterUpdateversionSql = @"update TBom_Main set isdefaultbom='0' where materiel_code=@materiel_code and id<>@id";
|
string detailInsertSql = @"insert into TBom_Deta(m_id,seq,materiel_code,unitcode,base_quantity,loss_quantity,total_quantity,idchildbom)
|
values(@m_id,@seq,@materiel_code,@unitcode,@base_quantity,@loss_quantity,@total_quantity,@idchildbom)";
|
string detailUpdateSql = @"update TBom_Deta set seq=@seq,materiel_code=@materiel_code,base_quantity=@base_quantity,loss_quantity=@loss_quantity,
|
total_quantity=@total_quantity,unitcode=@unitcode,idchildbom=@idchildbom where materiel_code=@materiel_code and m_id=@m_id";
|
string detailDeleteSql = @"delete TBom_Deta where materiel_code=@materiel_code and m_id=@m_id";
|
string masterUpdateSql = @"update TBom_Main set materiel_code=@materiel_code,unitcode=@unitcode,quantity=@quantity,status=@status,startdate=@startdate,
|
version=@version,isdefaultbom=@isdefaultbom,lm_user=@lm_user,lm_date=@lm_date where materiel_code=@materiel_code and id=@ID";
|
|
//查询当前父件物料+版本号是否存在
|
sql = @"select * from TBom_Main where materiel_code=@materiel_code and version=@version and id=@ID";
|
dynamicParams.Add("@ID", bom.id);
|
dynamicParams.Add("@materiel_code", bom.materiel_code);
|
dynamicParams.Add("@version", bom.version);
|
var data0 = DapperHelper.selectdata(sql, dynamicParams);
|
if (data0.Rows.Count > 0 && data0.Rows.Count > 1)
|
{
|
mes.code = "300";
|
mes.count = 0;
|
mes.message = "同一物料版本号有重复!";
|
mes.data = null;
|
return mes;
|
}
|
//根据Bomid查询是否被工单引用
|
sql = @"select * from TK_Wrk_Man where bom_id=@bom_id";
|
dynamicParams.Add("@bom_id", bom.id);
|
var data_1 = DapperHelper.selectdata(sql, dynamicParams);
|
if (data_1.Rows.Count > 0)
|
{
|
mes.code = "300";
|
mes.count = 0;
|
mes.message = "修改前BOM已经被工单引用,不可修改!";
|
mes.data = null;
|
return mes;
|
}
|
//查询当前父件物料非此版本是否存在
|
sql = @"select * from TBom_Main where materiel_code=@materiel_code";
|
dynamicParams.Add("@materiel_code", bom.materiel_code);
|
var data1 = DapperHelper.selectdata(sql, dynamicParams);
|
if (data1.Rows.Count > 0) //存在当前物料的其他版本BOM
|
{
|
// 使用LINQ查询DataTable
|
var BomDefault = from row in data1.AsEnumerable()
|
where row.Field<int>("isdefaultbom") == 1
|
select row;
|
|
// 检查是否有结果
|
if (BomDefault.Any())
|
{
|
if (bom.isdefaultbom == "1") //默认Bom为:是
|
{
|
//更新物料清单对应其他非此版本的是否默认BOM
|
var updateversionParam = new DynamicParameters();
|
updateversionParam.Add("@materiel_code", bom.materiel_code);
|
updateversionParam.Add("@id", bom.id);
|
conn.Execute(masterUpdateversionSql, updateversionParam, transaction);
|
}
|
else
|
{
|
if (bom.isdefaultbom == "0") //默认Bom为:是
|
{
|
mes.code = "300";
|
mes.count = 0;
|
mes.message = "一个产品只能有一个且必须有一个默认BOM!";
|
mes.data = null;
|
return mes;
|
}
|
}
|
}
|
else
|
{
|
if (bom.isdefaultbom == "0") //默认Bom为:是
|
{
|
mes.code = "300";
|
mes.count = 0;
|
mes.message = "一个产品只能有一个且必须有一个默认BOM!";
|
mes.data = null;
|
return mes;
|
}
|
}
|
|
}
|
else
|
{
|
if (bom.isdefaultbom == "0") //默认Bom为:否
|
{
|
mes.code = "300";
|
mes.count = 0;
|
mes.message = "一个产品只能有一个且必须有一个默认BOM!";
|
mes.data = null;
|
return mes;
|
}
|
}
|
//通过父件id查找子件信息
|
sql = @"select * from TBom_Deta where m_id=@m_id";
|
dynamicParams.Add("@m_id", bom.id);
|
var list2 = DapperHelper.select<BomDateSub>(sql, dynamicParams);
|
// 使用LINQ的GroupJoin来找出差异
|
var query = from item1 in bom.children
|
join item2 in list2 on new { item1.m_id, item1.materiel_code }
|
equals new { item2.m_id, item2.materiel_code } into gj
|
from subitem in gj.DefaultIfEmpty()
|
select new { Item1 = item1, Item2 = subitem };
|
var toAdd = query.Where(q => q.Item2 == null).Select(q => q.Item1).ToList();
|
var toUpdate = query.Where(q => q.Item2 != null && q.Item1.m_id == q.Item2.m_id && q.Item1.materiel_code == q.Item2.materiel_code).Select(q => q.Item1).ToList();
|
var toDelete = list2
|
.Where(item2 => !bom.children.Any(item1 =>
|
item1.m_id == item2.m_id &&
|
item1.materiel_code == item2.materiel_code
|
))
|
.ToList();
|
//新增
|
for (int i = 0; i < toAdd.Count; i++)
|
{
|
//写入物料清单子表
|
var detailParam = new DynamicParameters();
|
detailParam.Add("@m_id", toAdd[i].m_id);
|
detailParam.Add("@seq", toAdd[i].seq);
|
detailParam.Add("@materiel_code", toAdd[i].materiel_code);
|
detailParam.Add("@unitcode", toAdd[i].unitcode);
|
detailParam.Add("@base_quantity", toAdd[i].base_quantity);
|
detailParam.Add("@loss_quantity", toAdd[i].loss_quantity);
|
detailParam.Add("@total_quantity", toAdd[i].total_quantity);
|
detailParam.Add("@idchildbom", string.IsNullOrEmpty(toAdd[i].idchildbom) ? null : toAdd[i].idchildbom);
|
conn.Execute(detailInsertSql, detailParam, transaction);
|
}
|
//更新
|
for (int i = 0; i < toUpdate.Count; i++)
|
{
|
//更新物料清单子表
|
var detailUpdateParam = new DynamicParameters();
|
detailUpdateParam.Add("@m_id", toUpdate[i].m_id);
|
detailUpdateParam.Add("@seq", toUpdate[i].seq);
|
detailUpdateParam.Add("@materiel_code", toUpdate[i].materiel_code);
|
detailUpdateParam.Add("@unitcode", toUpdate[i].unitcode);
|
detailUpdateParam.Add("@base_quantity", toUpdate[i].base_quantity);
|
detailUpdateParam.Add("@loss_quantity", toUpdate[i].loss_quantity);
|
detailUpdateParam.Add("@total_quantity", toUpdate[i].total_quantity);
|
detailUpdateParam.Add("@idchildbom", string.IsNullOrEmpty(toUpdate[i].idchildbom) ? null : toUpdate[i].idchildbom);
|
conn.Execute(detailUpdateSql, detailUpdateParam, transaction);
|
}
|
//删除
|
for (int i = 0; i < toDelete.Count; i++)
|
{
|
//删除物料清单子表
|
var detailDeleteParam = new DynamicParameters();
|
detailDeleteParam.Add("@materiel_code", toDelete[i].materiel_code);
|
detailDeleteParam.Add("@m_id", toDelete[i].m_id);
|
conn.Execute(detailDeleteSql, detailDeleteParam, transaction);
|
}
|
//更新物料清单主表
|
var masterUpdateParam = new DynamicParameters();
|
masterUpdateParam.Add("@ID", bom.id);
|
masterUpdateParam.Add("@materiel_code", bom.materiel_code);
|
masterUpdateParam.Add("@unitcode", bom.unitcode);
|
masterUpdateParam.Add("@quantity", bom.quantity);
|
masterUpdateParam.Add("@status", bom.status);
|
masterUpdateParam.Add("@startdate", bom.startdate);
|
masterUpdateParam.Add("@version", bom.version);
|
masterUpdateParam.Add("@isdefaultbom", bom.isdefaultbom);
|
masterUpdateParam.Add("@lm_user", us.usercode);
|
masterUpdateParam.Add("@lm_date", DateTime.Now);
|
conn.Execute(masterUpdateSql, masterUpdateParam, transaction);
|
// 所有插入成功，提交事务
|
transaction.Commit();
|
//更新Bom层级
|
var bomdepthParam = new DynamicParameters();
|
bomdepthParam.Add("@bomId", bom.id);
|
bomdepthParam.Add("@isAllFlag", 1);
|
var dt = DapperHelper.selectProcedure("AA_SP_UpdateBomDepth", bomdepthParam);
|
mes.code = "200";
|
mes.count = 0;
|
mes.message = "修改操作成功!";
|
mes.data = null;
|
//写入操作记录表
|
LogHelper.DbOperateLog(us.usercode, "修改", "修改了物料清单：" + bom.materiel_code + "，版本号为:" + bom.version, us.usertype);
|
}
|
}
|
catch (Exception e)
|
{
|
// 捕获异常，回滚事务
|
transaction.Rollback();
|
mes.code = "300";
|
mes.count = 0;
|
mes.message = e.Message;
|
mes.data = null;
|
}
|
}
|
}
|
return mes;
|
}
|
#endregion
|
|
#region[物料清单删除提交-T+畅捷通模式]
|
public static ToMessage TBomDelete(BomDate bom, User us)
|
{
|
string sql = "";
|
List<object> list = new List<object>();
|
var dynamicParams = new DynamicParameters();
|
try
|
{
|
//查询当前删除物料清单父件编码非本次是否存在多个版本,存在多个版本时,如果当前是默认BOM则不允许删除
|
sql = @"select * from TBom_Main S where S.materiel_code=@materiel_code and version<>@version";
|
dynamicParams.Add("@materiel_code", bom.materiel_code);
|
dynamicParams.Add("@version", bom.version);
|
var data1 = DapperHelper.selectdata(sql, dynamicParams);
|
if (data1.Rows.Count > 0)
|
{
|
if (bom.isdefaultbom == "1")
|
{
|
mes.code = "300";
|
mes.count = 0;
|
mes.message = "[" + bom.materiel_name + "-" + bom.version + "]默认BOM不允许删除!";
|
mes.data = null;
|
return mes;
|
}
|
}
|
|
//查询当前删除物料清单父件编码(ID)在物料清单子表中是否存在
|
sql = @"select * from TBom_Deta S
|
left join TMateriel_Info M on S.materiel_code=M.partcode
|
where S.materiel_code=@materiel_code";
|
dynamicParams.Add("@materiel_code", bom.materiel_code);
|
var data0 = DapperHelper.selectdata(sql, dynamicParams);
|
if (data0.Rows.Count > 0)
|
{
|
mes.code = "300";
|
mes.count = 0;
|
mes.message = "[" + data0.Rows[0]["partname"].ToString() + "-"+bom.version+ "]已使用的记录不允许删除！[物料清单子件]";
|
mes.data = null;
|
return mes;
|
}
|
//查询当前删除的物料清单父件编码(ID)是否被工单引用
|
sql = @"select * from TK_Wrk_Man where bom_id=@bom_id";
|
dynamicParams.Add("@bom_id", bom.id);
|
var data_1 = DapperHelper.selectdata(sql, dynamicParams);
|
if (data_1.Rows.Count > 0)
|
{
|
mes.code = "300";
|
mes.count = 0;
|
mes.message = "当前前BOM已经被工单引用,不可删除!";
|
mes.data = null;
|
return mes;
|
}
|
//删除物料清单子表
|
sql = @"delete TBom_Deta where m_id=@m_id";
|
list.Add(new
|
{
|
str = sql,
|
parm = new
|
{
|
m_id = bom.id
|
}
|
});
|
//删除物料清单主表
|
sql = @"delete TBom_Main where id=@id";
|
list.Add(new
|
{
|
str = sql,
|
parm = new
|
{
|
id = bom.id
|
}
|
});
|
bool aa = DapperHelper.DoTransaction(list);
|
if (aa)
|
{
|
//写入操作记录表
|
LogHelper.DbOperateLog(us.usercode, "删除", "删除了物料清单：" + bom.materiel_code + "，版本号为:" + bom.version, 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
|
}
|
}
|
