using Dapper;
|
using System;
|
using System.Collections.Generic;
|
using System.Data;
|
using System.Data.SqlClient;
|
using System.Linq;
|
using System.Web;
|
using VueWebApi.Models;
|
using VueWebApi.Tools;
|
|
namespace VueWebApi.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 StockTypeSelect()
|
{
|
string sql = "";
|
try
|
{
|
//获取设备类型数据
|
sql = @"select code,name from T_Dict where dict_group='CHLX' ";
|
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 MaterialTypeSearch(string materialtypecode, string materialtypename, string stocktypecode, int startNum, int endNum, string prop, string order)
|
{
|
var dynamicParams = new DynamicParameters();
|
string search = "";
|
try
|
{
|
if (materialtypecode != "" && materialtypecode != null)
|
{
|
search += "and A.code like '%'+@materialtypecode+'%' ";
|
dynamicParams.Add("@materialtypecode", materialtypecode);
|
}
|
if (materialtypename != "" && materialtypename != null)
|
{
|
search += "and A.name like '%'+@warehousename+'%' ";
|
dynamicParams.Add("@materialtypename", materialtypename);
|
}
|
if (stocktypecode != "" && stocktypecode != null)
|
{
|
search += "and A.dict_code=@stocktypecode ";
|
dynamicParams.Add("@stocktypecode", stocktypecode);
|
}
|
if (search == "")
|
{
|
search = "and 1=1 ";
|
}
|
// --------------查询指定数据--------------
|
var total = 0; //总条数
|
var sql = @"select A.id,A.code,A.name,A.dict_code as stockcode,B.name as stockname,A.lm_user,A.lm_date
|
from TMateriel_Type A
|
left join T_Dict B on A.dict_code=B.code and B.dict_group='CHLX'
|
where A.is_delete<>'1' " + search;
|
var data = DapperHelper.GetPageList<object>(sql, dynamicParams, prop, order, startNum, endNum, out total);
|
mes.code = "200";
|
mes.Message = "查询成功!";
|
mes.count = total;
|
mes.data = data.ToList();
|
}
|
catch (Exception e)
|
{
|
mes.code = "300";
|
mes.count = 0;
|
mes.Message = e.Message;
|
mes.data = null;
|
}
|
return mes;
|
}
|
#endregion
|
|
#region [物料类型新增编辑]
|
public static ToMessage AddUpdateMaterialType(string materialtypeid, string materialtypecode, string materialtypename, string stocktypecode, string username, string operType)
|
{
|
var dynamicParams = new DynamicParameters();
|
try
|
{
|
if (operType == "Add")
|
{
|
var sql = @"insert into TMateriel_Type(dict_code,code,name,lm_user,lm_date)
|
values(@stocktypecode,@materialtypecode,@materialtypename,@username,@CreateDate)";
|
dynamicParams.Add("@stocktypecode", stocktypecode);
|
dynamicParams.Add("@materialtypecode", materialtypecode);
|
dynamicParams.Add("@materialtypename", materialtypename);
|
dynamicParams.Add("@username", username);
|
dynamicParams.Add("@CreateDate", DateTime.Now.ToString());
|
int cont = DapperHelper.SQL(sql, dynamicParams);
|
if (cont > 0)
|
{
|
mes.code = "200";
|
mes.count = 0;
|
mes.Message = "新增操作成功!";
|
mes.data = null;
|
}
|
else
|
{
|
mes.code = "300";
|
mes.count = 0;
|
mes.Message = "新增操作失败!";
|
mes.data = null;
|
}
|
}
|
if (operType == "Update")
|
{
|
var sql = @"update TMateriel_Type set name=@materialtypename,dict_code=@stocktypecode,lm_user=@username,lm_date=@CreateDate where id=@materialtypeid";
|
dynamicParams.Add("@materialtypeid", materialtypeid);
|
dynamicParams.Add("@materialtypename", materialtypename);
|
dynamicParams.Add("@stocktypecode", stocktypecode);
|
dynamicParams.Add("@username", username);
|
dynamicParams.Add("@CreateDate", DateTime.Now.ToString());
|
int cont = DapperHelper.SQL(sql, dynamicParams);
|
if (cont > 0)
|
{
|
mes.code = "200";
|
mes.count = 0;
|
mes.Message = "修改操作成功!";
|
mes.data = null;
|
}
|
else
|
{
|
mes.code = "300";
|
mes.count = 0;
|
mes.Message = "修改操作失败!";
|
mes.data = null;
|
}
|
}
|
}
|
catch (Exception e)
|
{
|
mes.code = "300";
|
mes.count = 0;
|
mes.Message = e.Message;
|
mes.data = null;
|
}
|
return mes;
|
}
|
#endregion
|
|
#region[物料类型删除]
|
public static ToMessage DeleteMaterialType(string materialtypecode)
|
{
|
var sql = "";
|
List<object> list = new List<object>();
|
var dynamicParams = new DynamicParameters();
|
try
|
{
|
list.Clear();
|
//判断物料类型是否有关联物料
|
sql = @"select B.partcode,B.partname from TMateriel_Type A
|
inner join TMateriel_Info B on A.code=B.materieltype_code and B.is_delete<>'1'
|
where A.is_delete<>'1' and A.code=@materialtypecode";
|
dynamicParams.Add("@materialtypecode", materialtypecode);
|
var data = DapperHelper.selectdata(sql, dynamicParams);
|
if (data.Rows.Count > 0)
|
{
|
mes.code = "300";
|
mes.count = 0;
|
mes.Message = "物料类型有关联物料,不允许删除!";
|
mes.data = null;
|
}
|
else
|
{
|
//删除仓库
|
sql = @"delete TMateriel_Type where code=@materialtypecode";
|
list.Add(new { str = sql, parm = new { materialtypecode = materialtypecode } });
|
bool aa = DapperHelper.DoTransaction(list);
|
if (aa)
|
{
|
mes.code = "200";
|
mes.count = 0;
|
mes.Message = "删除成功!";
|
mes.data = null;
|
}
|
else
|
{
|
mes.code = "300";
|
mes.count = 0;
|
mes.Message = "删除失败!";
|
mes.data = null;
|
}
|
}
|
}
|
catch (Exception e)
|
{
|
mes.code = "300";
|
mes.count = 0;
|
mes.Message = e.Message;
|
mes.data = null;
|
}
|
return mes;
|
}
|
#endregion
|
|
|
|
#region[单位列表查询]
|
public static ToMessage UomSearch(int startNum, int endNum, string prop, string order)
|
{
|
var dynamicParams = new DynamicParameters();
|
try
|
{
|
// --------------查询指定数据--------------
|
var total = 0; //总条数
|
var sql = @"select code,name,digdect,r_ule
|
from TUom
|
where is_delete<>'1' ";
|
var data = DapperHelper.GetPageList<object>(sql, dynamicParams, prop, order, startNum, endNum, out total);
|
mes.code = "200";
|
mes.Message = "查询成功!";
|
mes.count = total;
|
mes.data = data.ToList();
|
}
|
catch (Exception e)
|
{
|
mes.code = "300";
|
mes.count = 0;
|
mes.Message = e.Message;
|
mes.data = null;
|
}
|
return mes;
|
}
|
#endregion
|
|
#region[单位新增]
|
public static ToMessage UomAdd(string username, List<Uom> json)
|
{
|
var sql = "";
|
List<object> list = new List<object>();
|
var dynamicParams = new DynamicParameters();
|
try
|
{
|
list.Clear();
|
//循环写入单位表
|
for (int i = 0; i < json.Count; i++)
|
{
|
sql = @"insert into TUom(code,name,digdect,r_ule,lm_user,lm_date) values(@code,@name,@digdect,@r_ule,@username,@CreateDate)";
|
list.Add(new
|
{
|
str = sql,
|
parm = new
|
{
|
code = json[i].code,
|
name = json[i].name,
|
digdect=json[i].digdect,
|
r_ule=json[i].r_ule,
|
username= username,
|
CreateDate= DateTime.Now.ToString()
|
}
|
});
|
}
|
bool aa = DapperHelper.DoTransaction(list);
|
if (aa)
|
{
|
mes.code = "200";
|
mes.count = 0;
|
mes.Message = "操作成功!";
|
mes.data = null;
|
}
|
else
|
{
|
mes.code = "300";
|
mes.count = 0;
|
mes.Message = "操作失败!";
|
mes.data = null;
|
}
|
}
|
catch (Exception e)
|
{
|
mes.code = "300";
|
mes.count = 0;
|
mes.Message = e.Message;
|
mes.data = null;
|
}
|
return mes;
|
}
|
#endregion
|
|
#region[单位删除]
|
public static ToMessage UomDelete(string uomcode)
|
{
|
var sql = "";
|
var cont = 0;
|
var dynamicParams = new DynamicParameters();
|
try
|
{
|
sql = @"select * from TMateriel_Info where uom_code=@uomcode";
|
dynamicParams.Add(@"uomcode", uomcode);
|
cont = DapperHelper.selectcount(sql, dynamicParams);
|
if (cont <= 0)
|
{
|
mes.code = "300";
|
mes.Message = "单位已关联物料,不能删除!";
|
}
|
else
|
{
|
sql = @"update TUom set is_delete='1' where code=@uomcode";
|
dynamicParams.Add(@"uomcode", uomcode);
|
cont = DapperHelper.SQL(sql, dynamicParams);
|
if (cont > 0)
|
{
|
mes.code = "200";
|
mes.count = 0;
|
mes.Message = "删除操作成功!";
|
mes.data = null;
|
}
|
else
|
{
|
mes.code = "300";
|
mes.count = 0;
|
mes.Message = "删除操作失败!";
|
mes.data = null;
|
}
|
}
|
}
|
catch (Exception e)
|
{
|
mes.code = "300";
|
mes.count = 0;
|
mes.Message = e.Message;
|
mes.data = null;
|
}
|
return mes;
|
}
|
#endregion
|
|
#region[存类型查找物料类型]
|
public static ToMessage StockTypeSelectMaterialType(string stocktypecode)
|
{
|
string sql = "";
|
var dynamicParams = new DynamicParameters();
|
try
|
{
|
//获取设备类型数据
|
sql = @"select code,name from TMateriel_Type where dict_code=@stocktypecode and is_delete<>'1'";
|
dynamicParams.Add("@stocktypecode", stocktypecode);
|
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 InventoryFileSelect(string partcode, string partname, string partspec, string stocktypecode, string materialtypecode, string storehousecode, int startNum, int endNum, string prop, string order)
|
{
|
var dynamicParams = new DynamicParameters();
|
string search = "";
|
try
|
{
|
if (partcode != "" && partcode != null)
|
{
|
search += "and A.partcode like '%'+@partcode+'%' ";
|
dynamicParams.Add("@partcode", partcode);
|
}
|
if (partname != "" && partname != null)
|
{
|
search += "and A.partname like '%'+@partname+'%' ";
|
dynamicParams.Add("@partname", partname);
|
}
|
if (partspec != "" && partspec != null)
|
{
|
search += "and A.partspec like '%'+@partspec+'%' ";
|
dynamicParams.Add("@partspec", partspec);
|
}
|
if (stocktypecode != "" && stocktypecode != null)
|
{
|
search += "and D.code=@stocktypecode ";
|
dynamicParams.Add("@stocktypecode", stocktypecode);
|
}
|
if (materialtypecode != "" && materialtypecode != null)
|
{
|
search += "and C.code=@materialtypecode ";
|
dynamicParams.Add("@materialtypecode", materialtypecode);
|
}
|
if (storehousecode != "" && storehousecode != null)
|
{
|
search += "and A.stck_code=@storehousecode ";
|
dynamicParams.Add("@storehousecode", storehousecode);
|
}
|
if (search == "")
|
{
|
search = "and 1=1 ";
|
}
|
// --------------查询指定数据--------------
|
var total = 0; //总条数
|
var sql = @"select A.partcode,A.partname,A.partspec,A.uom_code,B.name as uom_name,D.code as stocktypecode,D.name as stocktypename,
|
C.code as materialtypecode,C.name as materialtypename,A.stck_code,T.name as stck_name,A.maxqty,A.minqty,A.lm_user,
|
A.lm_date,A.proute_id
|
from TMateriel_Info A
|
left join TUom B on A.uom_code=B.code
|
left join TMateriel_Type C on A.materieltype_code=C.code
|
left join T_Dict D on C.dict_code=D.code and D.dict_type='CHLX'
|
left join T_Sec_Stck T on A.stck_code=T.code
|
where A.is_delete<>'1' " + search;
|
var data = DapperHelper.GetPageList<object>(sql, dynamicParams, prop, order, startNum, endNum, out total);
|
mes.code = "200";
|
mes.Message = "查询成功!";
|
mes.count = total;
|
mes.data = data.ToList();
|
}
|
catch (Exception e)
|
{
|
mes.code = "300";
|
mes.count = 0;
|
mes.Message = e.Message;
|
mes.data = null;
|
}
|
return mes;
|
}
|
#endregion
|
|
#region[存货档案新增编辑]
|
public static ToMessage AddUpdateInventoryFile(string materialid, string materialcode, string materialname, string materialspec, string uomcode, string warehousecode, string stocktypecode, string materialtypecode, string minstockqty, string maxstockqty, string username, string operType)
|
{
|
var dynamicParams = new DynamicParameters();
|
try
|
{
|
if (operType == "Add")
|
{
|
var sql = @"insert into TMateriel_Info(partcode,partname,partspec,uom_code,materialtypecode,stck_code,proute_id,maxqty,minqty,lm_user,lm_date)
|
values(@stocktypecode,@materialtypecode,@materialtypename,@username,@CreateDate)";
|
dynamicParams.Add("@materialcode", materialcode);
|
dynamicParams.Add("@materialname", materialname);
|
dynamicParams.Add("@materialspec", materialspec);
|
dynamicParams.Add("@uomcode", uomcode);
|
dynamicParams.Add("@warehousecode", warehousecode);
|
dynamicParams.Add("@materialtypecode", materialtypecode);
|
dynamicParams.Add("@minstockqty", minstockqty);
|
dynamicParams.Add("@maxstockqty", maxstockqty);
|
dynamicParams.Add("@username", username);
|
dynamicParams.Add("@CreateDate", DateTime.Now.ToString());
|
int cont = DapperHelper.SQL(sql, dynamicParams);
|
if (cont > 0)
|
{
|
mes.code = "200";
|
mes.count = 0;
|
mes.Message = "新增操作成功!";
|
mes.data = null;
|
}
|
else
|
{
|
mes.code = "300";
|
mes.count = 0;
|
mes.Message = "新增操作失败!";
|
mes.data = null;
|
}
|
}
|
if (operType == "Update")
|
{
|
var sql = @"update TMateriel_Type set partname=@materialname,partspec=@materialspec,uom_code=@uomcode,materialtypecode=@materialtypecode,stck_code=@warehousecode,
|
maxqty=@maxstockqty,minqty=@minstockqty lm_user=@username,lm_date=@CreateDate where id=@materialid";
|
dynamicParams.Add("@materialid", materialid);
|
dynamicParams.Add("@materialname", materialname);
|
dynamicParams.Add("@materialspec", materialspec);
|
dynamicParams.Add("@uomcode", uomcode);
|
dynamicParams.Add("@warehousecode", warehousecode);
|
dynamicParams.Add("@materialtypecode", materialtypecode);
|
dynamicParams.Add("@minstockqty", minstockqty);
|
dynamicParams.Add("@maxstockqty", maxstockqty);
|
dynamicParams.Add("@username", username);
|
dynamicParams.Add("@CreateDate", DateTime.Now.ToString());
|
int cont = DapperHelper.SQL(sql, dynamicParams);
|
if (cont > 0)
|
{
|
mes.code = "200";
|
mes.count = 0;
|
mes.Message = "修改操作成功!";
|
mes.data = null;
|
}
|
else
|
{
|
mes.code = "300";
|
mes.count = 0;
|
mes.Message = "修改操作失败!";
|
mes.data = null;
|
}
|
}
|
}
|
catch (Exception e)
|
{
|
mes.code = "300";
|
mes.count = 0;
|
mes.Message = e.Message;
|
mes.data = null;
|
}
|
return mes;
|
}
|
#endregion
|
|
#region[存货档案删除]
|
public static ToMessage DeleteInventoryFile(string materialcode)
|
{
|
var sql = "";
|
List<object> list = new List<object>();
|
var dynamicParams = new DynamicParameters();
|
try
|
{
|
list.Clear();
|
//当前物料是否关联工艺路线
|
sql = @"select B.route_code,C.name as route_name from TMateriel_Info A
|
inner join TMateriel_Route B on A.partcode=B.materiel_code
|
inner join TFlw_Rout C on B.route_code=C.code
|
where A.partcode=@materialcode";
|
dynamicParams.Add("@materialcode", materialcode);
|
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 TPrteEqp_Stad
|
where materiel_code=@materialcode";
|
dynamicParams.Add("@materialcode", materialcode);
|
var data0 = DapperHelper.selectdata(sql, dynamicParams);
|
if (data0.Rows.Count > 0)
|
{
|
mes.code = "300";
|
mes.count = 0;
|
mes.Message = "物料已关联节拍工价,不允许删除!";
|
mes.data = null;
|
return mes;
|
}
|
//当前物料是否设置Bom清单
|
sql = @"select * from TBom_Main
|
where materiel_code=@materialcode";
|
dynamicParams.Add("@materialcode", materialcode);
|
var data1 = DapperHelper.selectdata(sql, dynamicParams);
|
if (data1.Rows.Count > 0)
|
{
|
mes.code = "300";
|
mes.count = 0;
|
mes.Message = "物料已关联BOM清单【主料】,不允许删除!";
|
mes.data = null;
|
return mes;
|
}
|
sql = @"select * from TBom_Deta
|
where smateriel_code=@materialcode";
|
dynamicParams.Add("@materialcode", materialcode);
|
var data2 = DapperHelper.selectdata(sql, dynamicParams);
|
if (data2.Rows.Count > 0)
|
{
|
mes.code = "300";
|
mes.count = 0;
|
mes.Message = "物料已关联BOM清单【子料】,不允许删除!";
|
mes.data = null;
|
return mes;
|
}
|
//当前物料是否生成出入库记录
|
//有没有被工单引用
|
|
//删除物料
|
sql = @"update TMateriel_Info set is_delete='1' where partcode=@materialcode";
|
list.Add(new { str = sql, parm = new { materialcode = materialcode } });
|
bool aa = DapperHelper.DoTransaction(list);
|
if (aa)
|
{
|
mes.code = "200";
|
mes.count = 0;
|
mes.Message = "删除成功!";
|
mes.data = null;
|
}
|
else
|
{
|
mes.code = "300";
|
mes.count = 0;
|
mes.Message = "删除失败!";
|
mes.data = null;
|
}
|
}
|
catch (Exception e)
|
{
|
mes.code = "300";
|
mes.count = 0;
|
mes.Message = e.Message;
|
mes.data = null;
|
}
|
return mes;
|
}
|
#endregion
|
|
#region[存货档案关联工艺路线查询]
|
public static ToMessage InventoryFileAssociationRoute(string partcode)
|
{
|
string sql = "";
|
var dynamicParams = new DynamicParameters();
|
List<MaterialRout> list = new List<MaterialRout>();
|
try
|
{
|
//获取工艺路线集合(包含物料绑定工艺路线标识)
|
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'";
|
dynamicParams.Add("@partcode", partcode);
|
var data = DapperHelper.selectdata(sql, dynamicParams);
|
for (int i = 0; i < data.Rows.Count; i++)
|
{
|
MaterialRout rout = new MaterialRout();
|
rout.code = data.Rows[i]["CODE"].ToString();
|
rout.name = data.Rows[i]["NAME"].ToString();
|
rout.flag = data.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.code = "200";
|
mes.Message = "查询成功!";
|
mes.data = list;
|
}
|
catch (Exception e)
|
{
|
mes.code = "300";
|
mes.count = 0;
|
mes.Message = e.Message;
|
mes.data = null;
|
}
|
return mes;
|
}
|
#endregion
|
|
}
|
}
|
