using Dapper;
|
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;
|
}
|
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 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 ";
|
}
|
if (tunittype == "S") //单计量
|
{
|
sql = @"select code,name,lm_user,lm_date,isGroup,idunitgroup,data_sources from TUnit where isSingleUnit='1' and isGroup='0'";
|
}
|
if (tunittype == "M" && (tunitgroupcode == "" || tunitgroupcode == null)) //多计量,且多计量组编码为空
|
{
|
sql = @"select code,name,lm_user,lm_date,'1' as isGroup,code as idunitgroup,data_sources from TUnitGroup";
|
}
|
if (tunittype == "M" && (tunitgroupcode != "" && tunitgroupcode != null)) //多计量,且多计量组编码不为空
|
{
|
sql = @"select code,name,isMainUnit,changeRate,rateDescription,lm_user,lm_date,isGroup,idunitgroup,data_sources
|
from TUnit where isSingleUnit='0' and isGroup='1' and 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();
|
}
|
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, "新增", "新增了计量单位", 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 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 = @"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 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 = @"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 = data_sources,
|
lm_user = us.usercode,
|
lm_date = DateTime.Now.ToString()
|
}
|
});
|
}
|
}
|
|
bool aa = DapperHelper.DoTransaction(list);
|
if (aa)
|
{
|
//写入操作记录表
|
LogHelper.DbOperateLog(us.usercode, "修改", "修改了计量单位", us.usertype);
|
mes.code = "200";
|
mes.count = 0;
|
mes.Message = "修改操作成功!";
|
mes.data = null;
|
}
|
else
|
{
|
mes.code = "300";
|
mes.count = 0;
|
mes.Message = "修改操作失败!";
|
mes.data = null;
|
}
|
}
|
}
|
catch (Exception e)
|
{
|
mes.code = "300";
|
mes.count = 0;
|
mes.Message = e.Message;
|
mes.data = null;
|
}
|
return mes;
|
}
|
#endregion
|
|
#region[计量单位删除]
|
public static ToMessage 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", 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 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)) //多计量,且多计量组编码为空
|
{
|
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' where 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)) //多计量,且多计量组编码不为空
|
{
|
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' where 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, "删除", "删除了计量单位", 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
|
}
|
}
|
