using Dapper;
|
using Newtonsoft.Json;
|
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
|
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
|
}
|
}
|
