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_type='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,U.username as lm_user,A.lm_date
|
from TMateriel_Type A
|
left join T_Dict B on A.dict_code=B.code and B.dict_type='CHLX'
|
left join TUser U on A.lm_user=U.usercode
|
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 sql0 = @"select * from TMateriel_Type where code=@materialtypecode";
|
dynamicParams.Add("@materialtypecode", materialtypecode);
|
var data = DapperHelper.selectdata(sql0, dynamicParams);
|
if (data.Rows.Count > 0)
|
{
|
mes.code = "300";
|
mes.count = 0;
|
mes.Message = "操作失败,编码重复!";
|
mes.data = null;
|
return mes;
|
}
|
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++)
|
{
|
var sql0 = @"select * from TUom where code=@code";
|
dynamicParams.Add("@code", json[i].code);
|
var data = DapperHelper.selectdata(sql0, dynamicParams);
|
if (data.Rows.Count > 0)
|
{
|
mes.code = "300";
|
mes.count = 0;
|
mes.Message = "操作失败,编码重复!";
|
mes.data = null;
|
return mes;
|
}
|
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 UomSelect()
|
{
|
string sql = "";
|
try
|
{
|
//获取工艺路线数据
|
sql = @"select code,name from TUom where is_delete<>'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[存货档案查询]
|
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.id,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,U.username as lm_user,A.default_route,
|
A.lm_date,A.proute_id,A.is_batchno,A.is_fifo,A.is_incheck,A.is_outcheck
|
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 A.stocktype_code=D.code and D.dict_type='CHLX'
|
left join T_Sec_Stck T on A.stck_code=T.code
|
left join TUser U on A.lm_user=U.usercode
|
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 is_batchno,string is_fifo,string is_incheck,string is_outcheck, string username, string operType)
|
{
|
var dynamicParams = new DynamicParameters();
|
try
|
{
|
if (operType == "Add")
|
{
|
var sql0 = @"select * from TMateriel_Info where partcode=@materialcode";
|
dynamicParams.Add("@materialcode", materialcode);
|
var data = DapperHelper.selectdata(sql0, dynamicParams);
|
if (data.Rows.Count > 0)
|
{
|
mes.code = "300";
|
mes.count = 0;
|
mes.Message = "操作失败,编码重复!";
|
mes.data = null;
|
return mes;
|
}
|
var sql = @"insert into TMateriel_Info(partcode,partname,partspec,uom_code,stocktype_code,materieltype_code,stck_code,maxqty,minqty,lm_user,lm_date,is_batchno,is_fifo,is_incheck,is_outcheck)
|
values(@materialcode,@materialname,@materialspec,@uomcode,@stocktypecode,@materialtypecode,@warehousecode,@maxstockqty,@minstockqty,@username,@CreateDate,@is_batchno,@is_fifo,@is_incheck,@is_outcheck)";
|
dynamicParams.Add("@materialcode", materialcode);
|
dynamicParams.Add("@materialname", materialname);
|
dynamicParams.Add("@materialspec", materialspec);
|
dynamicParams.Add("@uomcode", uomcode);
|
dynamicParams.Add("@warehousecode", warehousecode);
|
dynamicParams.Add("@stocktypecode", stocktypecode);
|
dynamicParams.Add("@materialtypecode", materialtypecode);
|
dynamicParams.Add("@minstockqty", minstockqty);
|
dynamicParams.Add("@maxstockqty", maxstockqty);
|
dynamicParams.Add("@username", username);
|
dynamicParams.Add("@CreateDate", DateTime.Now.ToString());
|
dynamicParams.Add("@is_batchno", is_batchno);
|
dynamicParams.Add("@is_fifo", is_fifo);
|
dynamicParams.Add("@is_incheck", is_incheck);
|
dynamicParams.Add("@is_outcheck", is_outcheck);
|
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_Info set partname=@materialname,partspec=@materialspec,uom_code=@uomcode,stocktype_code=@stocktypecode,materieltype_code=@materialtypecode,stck_code=@warehousecode,
|
maxqty=@maxstockqty,minqty=@minstockqty,lm_user=@username,lm_date=@CreateDate,is_batchno=@is_batchno,is_fifo=@is_fifo,
|
is_incheck=@is_incheck,is_outcheck=@is_outcheck 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("@stocktypecode", stocktypecode);
|
dynamicParams.Add("@materialtypecode", materialtypecode);
|
dynamicParams.Add("@minstockqty", minstockqty);
|
dynamicParams.Add("@maxstockqty", maxstockqty);
|
dynamicParams.Add("@username", username);
|
dynamicParams.Add("@CreateDate", DateTime.Now.ToString());
|
dynamicParams.Add("@is_batchno", is_batchno);
|
dynamicParams.Add("@is_fifo", is_fifo);
|
dynamicParams.Add("@is_incheck", is_incheck);
|
dynamicParams.Add("@is_outcheck", is_outcheck);
|
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 = @"delete TMateriel_Info 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' and A.enable='Y'";
|
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]["ROUTECODE"].ToString();
|
rout.name = data.Rows[i]["ROUTENAME"].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
|
|
#region[存货档案关联工艺路线提交]
|
public static ToMessage SaveInventoryFile(string partcode, string defaultroute_code, string username, List<ObjectData> json)
|
{
|
var sql = "";
|
List<object> list = new List<object>();
|
try
|
{
|
list.Clear();
|
if (json == null || json.Count <= 0)
|
{
|
//清除物料关联工艺路线表数据
|
sql = @"delete TMateriel_Route where materiel_code=@partcode";
|
list.Add(new { str = sql, parm = new { partcode = partcode } });
|
//标记物料表关联工艺路线标识
|
sql = @"update TMateriel_Info set proute_id='N',default_route='' where partcode=@partcode";
|
list.Add(new { str = sql, parm = new { partcode = partcode } });
|
}
|
else
|
{
|
//清除用户关联角色表数据
|
sql = @"delete TMateriel_Route where materiel_code=@partcode";
|
list.Add(new { str = sql, parm = new { partcode = partcode } });
|
//循环写入用户关联角色表
|
for (int i = 0; i < json.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 = partcode,
|
route_code = json[i].code,
|
lm_user = username,
|
lm_date = DateTime.Now.ToString()
|
}
|
});
|
}
|
//标记物料表关联工艺路线标识
|
sql = @"update TMateriel_Info set proute_id='Y',default_route=@defaultroute_code where partcode=@partcode";
|
list.Add(new { str = sql, parm = new { partcode = partcode, defaultroute_code = defaultroute_code } });
|
}
|
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 BoIventorySelect(string parentpartcode, string parentpartname, string parentpartspec, string parttype, string status, string version, string createusername, string createopendate, string createclosedate, int startNum, int endNum, string prop, string order)
|
{
|
var dynamicParams = new DynamicParameters();
|
string search = "";
|
try
|
{
|
if (parentpartcode != "" && parentpartcode != null)
|
{
|
search += "and M.partcode like '%'+@parentpartcode+'%' ";
|
dynamicParams.Add("@parentpartcode", parentpartcode);
|
}
|
if (parentpartname != "" && parentpartname != null)
|
{
|
search += "and M.partname like '%'+@parentpartname+'%' ";
|
dynamicParams.Add("@parentpartname", parentpartname);
|
}
|
if (parentpartspec != "" && parentpartspec != null)
|
{
|
search += "and M.partspec like '%'+@parentpartspec+'%' ";
|
dynamicParams.Add("@parentpartspec", parentpartspec);
|
}
|
if (parttype != "" && parttype != null)
|
{
|
search += "and M.stocktype_code=@parttype ";
|
dynamicParams.Add("@parttype", parttype);
|
}
|
if (status != "" && status != null)
|
{
|
search += "and A.status=@status ";
|
dynamicParams.Add("@status", status);
|
}
|
if (version != "" && version != null)
|
{
|
search += "and A.version like '%'+@version+'%' ";
|
dynamicParams.Add("@version", version);
|
}
|
if (createusername != "" && createusername != null)
|
{
|
search += "and U.username like '%'+@createusername+'%' ";
|
dynamicParams.Add("@createusername", createusername);
|
}
|
if (createopendate != "" && createopendate != null)
|
{
|
search += "and A.lm_date between @createopendate and @createclosedate ";
|
dynamicParams.Add("@createopendate", createopendate + " 00:00:00");
|
dynamicParams.Add("@createclosedate", createclosedate + " 23:59:59");
|
}
|
if (search == "")
|
{
|
search = "and 1=1 ";
|
}
|
// --------------查询指定数据--------------
|
var total = 0; //总条数
|
var sql = @"select A.id, M.partcode,M.partname,M.partspec,M.stocktype_code,D.name as stocktype_name,M.uom_code,T.name as uom_name,
|
A.quantity,A.status,A.startdate,A.version,U.username,A.lm_date
|
from TBom_Main A
|
left join TMateriel_Info M on A.materiel_code=M.partcode
|
left join TUom T on M.uom_code=T.code
|
left join TUser U on A.lm_user=U.usercode
|
left join T_Dict D on M.stocktype_code=D.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 MaterielDetailedVsion(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[物料清单新增/编辑提交]
|
public static ToMessage AddUpdateBoIventory(string bomid, string parentpartcode, string parentpartname, string parentpartspec, string status, string uomcode, string quantity, string startdate, string version, string username, string opertype, List<BomSub> objs)
|
{
|
string sql = "";
|
List<object> list = new List<object>();
|
var dynamicParams = new DynamicParameters();
|
try
|
{
|
if (opertype == "Add")
|
{
|
//获取主表最大ID
|
string sql0 = @"select ISNULL(IDENT_CURRENT('TBom_Main')+1,1) as id";
|
var dt = DapperHelper.selecttable(sql0);
|
//写入BOM主表
|
sql = @"insert into TBom_Main(materiel_code,quantity,status,version,lm_user,lm_date,startdate)
|
values(@materiel_code,@quantity,@status,@version,@username,@CreateDate,@startdate)";
|
list.Add(new
|
{
|
str = sql,
|
parm = new
|
{
|
materiel_code = parentpartcode,
|
quantity = quantity,
|
status = status,
|
version = version,
|
username = username,
|
CreateDate = DateTime.Now.ToString(),
|
startdate= startdate
|
}
|
});
|
//写入BOM子表
|
for (int i = 0; i < objs.Count; i++)
|
{
|
sql = @"insert into TBom_Deta(m_id,seq,smateriel_code,base_quantity,loss_quantity,total_quantity,pn_type)
|
values(@m_id,@seq,@smateriel_code,@base_quantity,@loss_quantity,@total_quantity,@pn_type)";
|
list.Add(new
|
{
|
str = sql,
|
parm = new
|
{
|
m_id = dt.Rows[0]["id"].ToString(),
|
seq = objs[i].seq.ToString(),
|
smateriel_code = objs[i].smaterirl_code.ToString(),
|
base_quantity = objs[i].base_quantity.ToString(),
|
loss_quantity = objs[i].loss_quantity.ToString(),
|
total_quantity = objs[i].total_quantity.ToString(),
|
pn_type = objs[i].pn_type.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;
|
}
|
}
|
if (opertype == "Update")
|
{
|
//修改BOM主表基本用量、启用状态
|
sql = @"update TBom_Main set quantity=@quantity,status=@status,lm_user=@lm_user,lm_date=@lm_date where materiel_code=@materiel_code and id=@bomid";
|
list.Add(new
|
{
|
str = sql,
|
parm = new
|
{
|
bomid = bomid,
|
materiel_code = parentpartcode,
|
quantity = quantity,
|
status = status,
|
lm_user = username,
|
lm_date = DateTime.Now.ToString()
|
}
|
});
|
//删除BOM子表
|
sql = @"delete TBom_Deta where m_id=@bomid";
|
list.Add(new
|
{
|
str = sql,
|
parm = new
|
{
|
bomid = bomid
|
}
|
});
|
//写入BOM子表
|
for (int i = 0; i < objs.Count; i++)
|
{
|
sql = @"insert into TBom_Deta(m_id,seq,smateriel_code,base_quantity,loss_quantity,total_quantity,pn_type)
|
values(@m_id,@seq,@smateriel_code,@base_quantity,@loss_quantity,@total_quantity,@pn_type)";
|
list.Add(new
|
{
|
str = sql,
|
parm = new
|
{
|
m_id = bomid,
|
seq = objs[i].seq.ToString(),
|
smateriel_code = objs[i].smaterirl_code.ToString(),
|
base_quantity = objs[i].base_quantity.ToString(),
|
loss_quantity = objs[i].loss_quantity.ToString(),
|
total_quantity = objs[i].total_quantity.ToString(),
|
pn_type = objs[i].pn_type.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 BoIventorySelectView(string bomid)
|
{
|
string sql = "";
|
var dynamicParams = new DynamicParameters();
|
try
|
{
|
//判断物料类型是否有关联物料
|
sql = @"select materiel_code from TK_Wrk_Man where materiel_code in (select materiel_code from TBom_Main where id=@bomid ) and bom_id=@bomid";
|
dynamicParams.Add("@bomid", bomid);
|
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 A.seq,B.partcode,B.partname,B.partspec,B.uom_code,T.name as uom_name,
|
A.base_quantity,A.loss_quantity,A.total_quantity,A.pn_type
|
from TBom_Deta A
|
left join TMateriel_Info B on A.smateriel_code=B.partcode
|
left join TUom T on B.uom_code=T.code
|
where A.m_id=@bomid";
|
dynamicParams.Add("@bomid", bomid);
|
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 DeleteBoIventory(string bomid)
|
{
|
var sql = "";
|
List<object> list = new List<object>();
|
var dynamicParams = new DynamicParameters();
|
try
|
{
|
list.Clear();
|
//判断物料类型是否有关联物料
|
sql = @"select materiel_code from TK_Wrk_Man where materiel_code in (select materiel_code from TBom_Main where id=@bomid ) and bom_id=@bomid";
|
dynamicParams.Add("@bomid", bomid);
|
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 TBom_Deta where m_id=@bomid";
|
list.Add(new { str = sql, parm = new { bomid = bomid } });
|
//删除物料清单主表
|
sql = @"delete TBom_Main where id=@bomid";
|
list.Add(new { str = sql, parm = new { bomid = bomid } });
|
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 RouteSearch(string routecode, string routename, string description, string createuser, int startNum, int endNum, string prop, string order)
|
{
|
var dynamicParams = new DynamicParameters();
|
string search = "";
|
try
|
{
|
if (routecode != "" && routecode != null)
|
{
|
search += "and A.code like '%'+@routecode+'%' ";
|
dynamicParams.Add("@routecode", routecode);
|
}
|
if (routename != "" && routename != null)
|
{
|
search += "and A.name like '%'+@routename+'%' ";
|
dynamicParams.Add("@routename", routename);
|
}
|
if (description != "" && description != null)
|
{
|
search += "and A.description like '%'+@description+'%' ";
|
dynamicParams.Add("@description", description);
|
}
|
if (createuser != "" && createuser != null)
|
{
|
search += "and U.username like '%'+@createuser+'%' ";
|
dynamicParams.Add("@createuser", createuser);
|
}
|
if (search == "")
|
{
|
search = "and 1=1 ";
|
}
|
// --------------查询指定数据--------------
|
var total = 0; //总条数
|
var sql = @"select A.id,A.code,A.name,A.description,A.enable,U.username as lm_user,A.lm_date from TFlw_Rout A
|
left join TUser U on A.lm_user=U.usercode
|
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 ViewRoute(string routecode)
|
{
|
string sql = "";
|
var dynamicParams = new DynamicParameters();
|
List<RoutEdit> list = new List<RoutEdit>();
|
try
|
{
|
//获取工艺路线信息
|
sql = @"select code,name,description,enable
|
from TFlw_Rout
|
where code=@routecode and is_delete<>'1'";
|
dynamicParams.Add("@routecode", routecode);
|
var data = DapperHelper.selectdata(sql, dynamicParams);
|
for (int i = 0; i < data.Rows.Count; i++)
|
{
|
RoutEdit rout = new RoutEdit();
|
rout.code = data.Rows[i]["CODE"].ToString();
|
rout.name = data.Rows[i]["NAME"].ToString();
|
rout.enable = data.Rows[i]["ENABLE"].ToString();
|
rout.description = data.Rows[i]["DESCRIPTION"].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
|
|
#region[工艺路线新增]
|
public static ToMessage AddUpdateRoute(string id, string opertype, string username, RoutEdit json)
|
{
|
var sql = "";
|
var dynamicParams = new DynamicParameters();
|
List<object> list = new List<object>();
|
try
|
{
|
if (opertype == "Add")
|
{
|
var sql0 = @"select * from TFlw_Rout where code=@code";
|
dynamicParams.Add("@code", json.code);
|
var data = DapperHelper.selectdata(sql0, dynamicParams);
|
if (data.Rows.Count > 0)
|
{
|
mes.code = "300";
|
mes.count = 0;
|
mes.Message = "操作失败,编码重复!";
|
mes.data = null;
|
return mes;
|
}
|
//新增工艺路线表
|
sql = @"insert into TFlw_Rout(code,name,description,enable,lm_user,lm_date) values(@code,@name,@description,@enable,@lm_user,@lm_date)";
|
list.Add(new { str = sql, parm = new { code = json.code, name = json.name, description = json.description, enable = json.enable, lm_user = username, lm_date = DateTime.Now.ToString() } });
|
for (int i = 0; i < json.Data.Rows.Count; i++)
|
{
|
string is_firststep = "N"; //是否首道工序
|
string is_laststep = "N"; //是否末道工序
|
if (json.Data.Rows[i]["SEQ"].ToString() == "1") //是否首道工序
|
{
|
is_firststep = "Y";
|
}
|
if (Convert.ToInt32(json.Data.Rows[i]["SEQ"].ToString()) == json.Data.Rows.Count) //是否末道工序
|
{
|
is_laststep = "Y";
|
}
|
//新增工艺路线关联工序表
|
sql = @"insert TFlw_Rtdt (rout_code,seq,step_code,first_choke,last_choke,lm_user,lm_date) values(@rout_code,@seq,@step_code,@first_choke,@last_choke,@lm_user,@lm_date)";
|
list.Add(new { str = sql, parm = new { rout_code = json.code, seq = Convert.ToInt32(json.Data.Rows[i]["SEQ"].ToString()), step_code = json.Data.Rows[i]["STEPCODE"].ToString(), first_choke = is_firststep, last_choke = is_laststep, lm_user = username, lm_date = 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 DeleteRoute(string routecode)
|
{
|
var sql = "";
|
List<object> list = new List<object>();
|
var dynamicParams = new DynamicParameters();
|
try
|
{
|
//判断工艺路路线是否被存货绑定
|
sql = @"select * from TMateriel_Route where route_code=@routecode";
|
dynamicParams.Add("@routecode", routecode);
|
var data_0 = DapperHelper.selectdata(sql, dynamicParams);
|
if (data_0.Rows.Count > 0)
|
{
|
mes.code = "300";
|
mes.count = 0;
|
mes.Message = "存货档案已关联工艺路线，不允许删除!";
|
mes.data = null;
|
return mes;
|
}
|
//判断工艺路线是否被工单引用(被引用则不能删除)
|
sql = @"select * from TK_Wrk_Man where route_code=@routecode";
|
dynamicParams.Add("@routecode", routecode);
|
var data = DapperHelper.selectdata(sql, dynamicParams);
|
if (data.Rows.Count > 0)
|
{
|
mes.code = "300";
|
mes.count = 0;
|
mes.Message = "工艺路线已被工单引用，不允许删除!";
|
mes.data = null;
|
return mes;
|
}
|
else
|
{
|
//判断当前工艺路线是否有设置节拍工价(有设置，提示先删除节拍工价设置)
|
sql = @"select * from TPrteEqp_Stad where route_code=@routecode";
|
dynamicParams.Add("@routecode", routecode);
|
var data0 = DapperHelper.selectdata(sql, dynamicParams);
|
if (data0.Rows.Count > 0)
|
{
|
mes.code = "300";
|
mes.count = 0;
|
mes.Message = "工艺路线已设置节拍工价，请先删除设置!";
|
mes.data = null;
|
return mes;
|
}
|
else
|
{
|
//删除工艺路线关联工序表
|
sql = @"delete TFlw_Rtdt where rout_code=@routecode";
|
list.Add(new { str = sql, parm = new { routecode = routecode } });
|
//删除工艺路线表
|
sql = @"delete TFlw_Rout where code=@routecode";
|
list.Add(new { str = sql, parm = new { routecode = routecode } });
|
//删除物料关联工艺路线表
|
sql = @"delete TMateriel_Route where route_code=@routecode";
|
list.Add(new { str = sql, parm = new { routecode = routecode } });
|
}
|
bool aa = DapperHelper.DoTransaction(list);
|
if (aa)
|
{
|
mes.code = "200";
|
mes.count = 0;
|
mes.Message = "删除成功!";
|
mes.data = null;
|
return mes;
|
}
|
else
|
{
|
mes.code = "300";
|
mes.count = 0;
|
mes.Message = "删除失败!";
|
mes.data = null;
|
return mes;
|
}
|
}
|
}
|
catch (Exception e)
|
{
|
mes.code = "300";
|
mes.count = 0;
|
mes.Message = e.Message;
|
mes.data = null;
|
return mes;
|
}
|
return mes;
|
}
|
#endregion
|
|
|
|
|
#region[工序下拉查询接口]
|
public static ToMessage StepSelect()
|
{
|
string sql = "";
|
try
|
{
|
//获取工序数据
|
sql = @"select stepcode,stepname from TStep where is_delete<>'1' and enable='Y'";
|
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 StepSearch(string stepcode, string stepname, string enable, string steptypecode, string createuser, int startNum, int endNum, string prop, string order)
|
{
|
var dynamicParams = new DynamicParameters();
|
string search = "";
|
try
|
{
|
if (stepcode != "" && stepcode != null)
|
{
|
search += "and A.stepcode like '%'+@stepcode+'%' ";
|
dynamicParams.Add("@stepcode", stepcode);
|
}
|
if (stepname != "" && stepname != null)
|
{
|
search += "and A.stepname like '%'+@stepname+'%' ";
|
dynamicParams.Add("@stepname", stepname);
|
}
|
if (enable != "" && enable != null)
|
{
|
search += "and A.enable=@enable ";
|
dynamicParams.Add("@enable", enable);
|
}
|
if (steptypecode != "" && steptypecode != null)
|
{
|
search += "and A.flwtype=@steptypecode ";
|
dynamicParams.Add("@steptypecode", steptypecode);
|
}
|
if (createuser != "" && createuser != null)
|
{
|
search += "and U.username=@createuser ";
|
dynamicParams.Add("@createuser", createuser);
|
}
|
if (search == "")
|
{
|
search = "and 1=1 ";
|
}
|
// --------------查询指定数据--------------
|
var total = 0; //总条数
|
var sql = @"select A.id,A.stepcode,A.stepname,A.flwtype,A.enable,A.descr,U.username as lm_user,A.lm_date,A.is_eqp,A.is_defect from TStep A
|
left join TUser U on A.lm_user=U.usercode
|
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 AddUpdateStep(string stepid, string stepcode, string stepname, string steptypecode, string enable, string description, string username, string operType)
|
{
|
var dynamicParams = new DynamicParameters();
|
List<object> list = new List<object>();
|
var sql_1 = "";
|
try
|
{
|
if (operType == "Add")
|
{
|
var sql0 = @"select * from TStep where stepcode=@stepcode";
|
dynamicParams.Add("@stepcode", stepcode);
|
var data = DapperHelper.selectdata(sql0, dynamicParams);
|
if (data.Rows.Count > 0)
|
{
|
mes.code = "300";
|
mes.count = 0;
|
mes.Message = "操作失败,编码重复!";
|
mes.data = null;
|
return mes;
|
}
|
var sql = @"insert into TStep(stepcode,stepname,flwtype,enable,descr,lm_user,lm_date)
|
values(@stepcode,@stepname,@steptypecode,@enable,@description,@username,@CreateDate)";
|
dynamicParams.Add("@stepcode", stepcode);
|
dynamicParams.Add("@stepname", stepname);
|
dynamicParams.Add("@steptypecode", steptypecode);
|
dynamicParams.Add("@enable", enable);
|
dynamicParams.Add("@description", description);
|
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")
|
{
|
list.Clear();
|
//如果当前工序使用状态位N(未启用),需判断当前工序对应工艺路线被引用的工单是否关闭
|
if (enable == "N")
|
{
|
var sql0 = @"select * from TFlw_Rtdt A
|
inner join TK_Wrk_Man B on A.rout_code=B.route_code
|
where A.step_code=@stepcode and B.status<>'CLOSED'";
|
dynamicParams.Add("@stepcode", stepcode);
|
var data = DapperHelper.selectdata(sql0, dynamicParams);
|
if (data.Rows.Count > 0)
|
{
|
mes.code = "300";
|
mes.count = 0;
|
mes.Message = "工序有为执行完的工单,工序状态不能为未启用!";
|
mes.data = null;
|
return mes;
|
}
|
}
|
//当前工序类型与关联工作站中的类型是否匹配,不匹配则清除关联工作站数据，同时清理工序标(关联工作站标识字段为N)
|
var sql_10 = @"select S.flwtype from TFlw_Rteqp A
|
inner join TStep S on A.step_code=S.stepcode
|
where A.step_code=@stepcode";
|
dynamicParams.Add("@stepcode", stepcode);
|
var data_10 = DapperHelper.selectdata(sql_10, dynamicParams);
|
if (data_10.Rows.Count > 0)
|
{
|
if (data_10.Rows[0]["FLWTYPE"].ToString() != steptypecode)
|
{
|
//删除工序关联工作站表
|
sql_1 = @"delete TFlw_Rteqp where step_code=@stepcode";
|
list.Add(new { str = sql_1, parm = new { stepcode = stepcode } });
|
//修改工序表关联工作站标识
|
sql_1 = @"update TStep set is_eqp='N' where stepcode=@stepcode";
|
list.Add(new { str = sql_1, parm = new { stepcode = stepcode } });
|
//删除设备节拍工价表
|
sql_1 = @"delete TPrteEqp_Stad where step_code=@stepcode";
|
list.Add(new { str = sql_1, parm = new { stepcode = stepcode } });
|
}
|
}
|
|
|
sql_1 = @"update TStep set stepname=@stepname,flwtype=@steptypecode,enable=@enable,descr=@description,
|
lm_user=@username,lm_date=@CreateDate where id=@stepid";
|
list.Add(new { str = sql_1, parm = new { stepid = stepid, stepname = stepname, steptypecode = steptypecode, enable = enable, description = description, 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 DeleteStep(string stepcode)
|
{
|
var sql = "";
|
List<object> list = new List<object>();
|
var dynamicParams = new DynamicParameters();
|
try
|
{
|
list.Clear();
|
//当前工序是否关联工艺路线
|
sql = @"select * from TFlw_Rtdt
|
where step_code=@stepcode";
|
dynamicParams.Add("@stepcode", stepcode);
|
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 TPrteEqp_Stad where step_code=@stepcode";
|
list.Add(new { str = sql, parm = new { stepcode = stepcode } });
|
//删除工序关联工作站表
|
sql = @"delete TFlw_Rteqp where step_code=@stepcode";
|
list.Add(new { str = sql, parm = new { stepcode = stepcode } });
|
//删除缺陷关联工序表
|
sql = @"delete TDefect_Step where step_code=@stepcode";
|
list.Add(new { str = sql, parm = new { stepcode = stepcode } });
|
//删除工序
|
sql = @"update TStep set is_delete='1' where stepcode=@stepcode";
|
list.Add(new { str = sql, parm = new { stepcode = stepcode } });
|
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 StepAssociationEqp(string stepcode)
|
{
|
string sql = "";
|
var dynamicParams = new DynamicParameters();
|
List<StepEqp> list = new List<StepEqp>();
|
try
|
{
|
//获取工作站集合(车间、外协供应商,包含已绑定工作站标识)
|
sql = @"select distinct T.org_code as wksp_code,T.org_name as wksp_name,'E' as type,(case when B.eqp_code is null then 'N' else 'Y' end) flag from TOrganization T
|
left join(
|
select distinct A.eqp_code,B.wksp_code from TFlw_Rteqp A
|
inner join TEqpInfo B on A.eqp_code=B.code
|
where A.step_code=@stepcode and A.is_delete<>'1' and B.is_delete<>'1'
|
) B on T.org_code=B.wksp_code where T.description='W' and is_delete<>'1'
|
UNION ALL
|
select distinct T.type as wksp_code,(case when T.type='211' then '供应商' when T.type='228' then '客户/供应商' end ) as wksp_name,'W' as type,(case when B.type is null then 'N' else 'Y' end) flag
|
from TCustomer T
|
left join(
|
select distinct A.eqp_code,B.type from TFlw_Rteqp A
|
inner join TCustomer B on A.eqp_code=B.code
|
where A.step_code=@stepcode and A.is_delete<>'1' and B.is_delete<>'1'
|
) B on T.type=B.type where T.type in('211','228') and T.is_delete<>'1'"; //226(客户)
|
dynamicParams.Add("@stepcode", stepcode);
|
var data = DapperHelper.selectdata(sql, dynamicParams);
|
for (int i = 0; i < data.Rows.Count; i++)
|
{
|
StepEqp rout = new StepEqp();
|
rout.code = data.Rows[i]["WKSP_CODE"].ToString();
|
rout.name = data.Rows[i]["WKSP_NAME"].ToString();
|
rout.type = data.Rows[i]["TYPE"].ToString();
|
rout.flag = data.Rows[i]["FLAG"].ToString();
|
rout.children = new List<StepEqpCn>();
|
if (rout.code == "211"|| rout.code == "228") //外协供方
|
{
|
//根据外协供方标识编码查找外协供方信息(包含已关联标识)
|
sql = @"select A.code,A.name,'W' as type,(case when B.eqp_code is null then 'N' else 'Y' end) flag
|
from TCustomer A
|
left join(
|
select distinct A.eqp_code from TFlw_Rteqp A
|
inner join TCustomer B on A.eqp_code=B.code
|
where A.step_code=@stepcode and B.type=@wxcode and A.is_delete<>'1' and B.is_delete<>'1'
|
) B on A.code=B.eqp_code where A.type=@wxcode and A.is_delete<>'1'";
|
dynamicParams.Add("@stepcode", stepcode);
|
dynamicParams.Add("@wxcode", rout.code);
|
var data0 = DapperHelper.selectdata(sql, dynamicParams);
|
for (int k = 0; k < data0.Rows.Count; k++)
|
{
|
StepEqpCn cn = new StepEqpCn();
|
cn.code = data0.Rows[k]["CODE"].ToString();//外协供方编码
|
cn.name = data0.Rows[k]["NAME"].ToString();//外协供方名称
|
cn.type = data0.Rows[k]["TYPE"].ToString();//工作站类型(E:设备 W:外协供方)
|
cn.flag = data0.Rows[k]["FLAG"].ToString();//关联标识
|
rout.children.Add(cn);
|
}
|
list.Add(rout);
|
}
|
else
|
{
|
//根据车间编码查找设备(包含已关联标识)
|
sql = @"select A.code,A.name,'E' as type,(case when B.eqp_code is null then 'N' else 'Y' end) flag
|
from TEqpInfo A
|
left join(
|
select distinct A.eqp_code from TFlw_Rteqp A
|
inner join TEqpInfo B on A.eqp_code=B.code
|
where A.step_code=@stepcode and A.is_delete<>'1' and B.is_delete<>'1'
|
) B on A.code=B.eqp_code where A.wksp_code=@wkspcode and A.is_delete<>'1'";
|
dynamicParams.Add("@stepcode", stepcode);
|
dynamicParams.Add("@wkspcode", data.Rows[i]["WKSP_CODE"].ToString());
|
var data0 = DapperHelper.selectdata(sql, dynamicParams);
|
for (int j = 0; j < data0.Rows.Count; j++)
|
{
|
StepEqpCn cn = new StepEqpCn();
|
cn.code = data0.Rows[j]["CODE"].ToString();//设备编码
|
cn.name = data0.Rows[j]["NAME"].ToString();//设备名称
|
cn.type = data0.Rows[j]["TYPE"].ToString();//工作站类型(E:设备 W:外协供方)
|
cn.flag = data0.Rows[j]["FLAG"].ToString();//关联标识
|
rout.children.Add(cn);
|
}
|
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
|
|
#region [工序定义关联工作站提交]
|
public static ToMessage SaveStepAssociationEqp(string stepcode, string username, List<ObjectData> json)
|
{
|
var sql = "";
|
List<object> list = new List<object>();
|
try
|
{
|
list.Clear();
|
if (json == null || json.Count <= 0)
|
{
|
//清除工序关联工作站表数据
|
sql = @"delete TFlw_Rteqp where step_code=@stepcode";
|
list.Add(new { str = sql, parm = new { stepcode = stepcode } });
|
//标记工序表关联工作站标识
|
sql = @"update TStep set is_eqp='N' where stepcode=@stepcode";
|
list.Add(new { str = sql, parm = new { stepcode = stepcode } });
|
}
|
else
|
{
|
//清除工序关联工作站表数据
|
sql = @"delete TFlw_Rteqp where step_code=@stepcode";
|
list.Add(new { str = sql, parm = new { stepcode = stepcode } });
|
//循环写入用户关联角色表
|
for (int i = 0; i < json.Count; i++)
|
{
|
sql = @"insert into TFlw_Rteqp(eqp_code,step_code,style,lm_user,lm_date) values(@eqp_code,@stepcode,@style,@lm_user,@lm_date)";
|
list.Add(new
|
{
|
str = sql,
|
parm = new
|
{
|
eqp_code = json[i].code,
|
stepcode = stepcode,
|
style = json[i].name,
|
lm_user = username,
|
lm_date = DateTime.Now.ToString()
|
}
|
});
|
}
|
//标记物料表关联工艺路线标识
|
sql = @"update TStep set is_eqp='Y' where stepcode=@stepcode";
|
list.Add(new { str = sql, parm = new { stepcode = stepcode } });
|
}
|
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 StepAssociationDefect(string stepcode)
|
{
|
string sql = "";
|
var dynamicParams = new DynamicParameters();
|
List<StepDefect> list = new List<StepDefect>();
|
try
|
{
|
//获取工序关联缺陷数据(包含关联标识)
|
sql = @"select A.code,A.name,(case when B.defect_code is null then 'N' else 'Y' end) flag from TDefect A
|
left join(
|
select distinct defect_code from TDefect_Step where step_code=@stepcode and is_delete<>'1'
|
) B on A.code=B.defect_code";
|
dynamicParams.Add("@stepcode", stepcode);
|
var data = DapperHelper.selectdata(sql, dynamicParams);
|
for (int i = 0; i < data.Rows.Count; i++)
|
{
|
StepDefect dft = new StepDefect();
|
dft.code = data.Rows[i]["CODE"].ToString();
|
dft.name = data.Rows[i]["NAME"].ToString();
|
dft.flag = data.Rows[i]["FLAG"].ToString();
|
list.Add(dft);
|
}
|
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
|
|
#region[工序关联缺陷提交]
|
public static ToMessage SaveStepAssociationDefect(string stepcode, string username, List<ObjectData> json)
|
{
|
var sql = "";
|
List<object> list = new List<object>();
|
try
|
{
|
list.Clear();
|
if (json == null || json.Count <= 0)
|
{
|
//清除工序关联缺陷表数据
|
sql = @"delete TDefect_Step where step_code=@stepcode";
|
list.Add(new { str = sql, parm = new { stepcode = stepcode } });
|
//标记工序表关联工作站标识
|
sql = @"update TStep set is_defect='N' where stepcode=@stepcode";
|
list.Add(new { str = sql, parm = new { stepcode = stepcode } });
|
}
|
else
|
{
|
//清除工序关联缺陷表数据
|
sql = @"delete TDefect_Step where step_code=@stepcode";
|
list.Add(new { str = sql, parm = new { stepcode = stepcode } });
|
//循环写入用户关联角色表
|
for (int i = 0; i < json.Count; i++)
|
{
|
sql = @"insert into TDefect_Step(defect_code,step_code,lm_user,lm_date) values(@defect_code,@stepcode,@lm_user,@lm_date)";
|
list.Add(new
|
{
|
str = sql,
|
parm = new
|
{
|
defect_code = json[i].code,
|
stepcode = stepcode,
|
lm_user = username,
|
lm_date = DateTime.Now.ToString()
|
}
|
});
|
}
|
//标记物料表关联工艺路线标识
|
sql = @"update TStep set is_defect='Y' where stepcode=@stepcode";
|
list.Add(new { str = sql, parm = new { stepcode = stepcode } });
|
}
|
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 PartSelect()
|
{
|
string sql = "";
|
try
|
{
|
//获取物料数据
|
sql = @"select M.partcode,M.partname,M.partspec,M.uom_code,T.name as uom_name,
|
M.stocktype_code,D.name as stocktype_name,M.stck_code,S.name as stck_name
|
from TMateriel_Info M
|
left join TUom T on M.uom_code=T.code
|
left join T_Dict D on M.stocktype_code=D.code
|
left join T_Sec_Stck S on M.stck_code=S.code
|
where M.is_delete<>'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[产品编码查找工艺路线下拉框]
|
public static ToMessage PartSelectRpute(string partcode)
|
{
|
string sql = "";
|
var dynamicParams = new DynamicParameters();
|
try
|
{
|
//通过产品编码查找关联的工艺路线信息
|
sql = @"select A.route_code,B.name as route_name
|
from TMateriel_Route A
|
inner join TFlw_Rout B on A.route_code=B.code
|
where A.materiel_code=@partcode and B.is_delete<>'1' and A.is_delete<>'1'";
|
dynamicParams.Add("@partcode", partcode);
|
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 RouteSelectStep(string routecode)
|
{
|
string sql = "";
|
var dynamicParams = new DynamicParameters();
|
try
|
{
|
//通过产品编码查找关联的工艺路线信息
|
sql = @"select B.stepcode as code,B.stepname as name,B.flwtype as flag
|
from TFlw_Rtdt A
|
inner join TStep B on A.step_code=B.stepcode
|
where A.rout_code=@routecode and B.is_delete<>'1' and A.is_delete<>'1'";
|
dynamicParams.Add("@routecode", routecode);
|
var data = DapperHelper.select<StepDefect>(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 StepSelectEqp(string stepcode)
|
{
|
string sql = "";
|
var dynamicParams = new DynamicParameters();
|
try
|
{
|
//通过产品编码查找关联的工艺路线信息
|
sql = @"select B.code,B.name from TFlw_Rteqp A
|
left join TEqpInfo B on A.eqp_code=B.code
|
where A.step_code=@stepcode and A.style='E' and A.is_delete<>'1' and B.is_delete<>'1'";
|
dynamicParams.Add("@stepcode", stepcode);
|
var data = DapperHelper.select<ObjectData>(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 StepSelectEqpList(string partcode, string routecode, string stepcode,string steptype, int startNum, int endNum, string prop, string order)
|
{
|
var dynamicParams = new DynamicParameters();
|
string search = "", sql = "";
|
int total = 0;
|
try
|
{
|
// --------------查询指定数据--------------
|
if (steptype == "Z") //自制工序
|
{
|
total = 0; //总条数
|
//sql = @"select B.code as eqpcode,B.name as eqpname,B.wksp_code,T.org_name as wksp_name,A.eqp_value,A.stand_value,A.cavity_qty,A.unprice
|
// from (
|
// select materiel_code, eqp_code,eqp_value,stand_value,cavity_qty,unprice from TPrteEqp_Stad
|
// where materiel_code=@partcode and route_code=@routecode and step_code=@stepcode
|
// union all
|
// select @partcode as materiel_code,eqp_code,'0' as eqp_value,'0' as stand_value,'0' as cavity_qty,'0' as unprice
|
// from TFlw_Rteqp where step_code=@stepcode and style='E' and @partcode+eqp_code+step_code not in(select materiel_code+eqp_code+step_code from TPrteEqp_Stad)
|
// ) A
|
// left join TEqpInfo B on A.eqp_code=B.code
|
// left join TOrganization T on B.wksp_code=T.org_code
|
// left join TMateriel_Info C on A.materiel_code=C.partcode
|
// where B.is_delete<>'1' and T.is_delete<>'1' and C.is_delete<>'1' ";
|
|
sql = @"select B.code as eqpcode,B.name as eqpname,B.wksp_code,T.org_name as wksp_name,A.eqp_value,A.stand_value,A.cavity_qty,A.unprice
|
from (
|
select materiel_code, eqp_code,eqp_value,stand_value,cavity_qty,unprice from TPrteEqp_Stad
|
where materiel_code=@partcode and route_code=@routecode and step_code=@stepcode
|
union all
|
select @partcode as materiel_code,C.eqp_code,'0' as eqp_value,'0' as stand_value,'0' as cavity_qty,'0' as unprice
|
from TFlw_Rout A
|
inner join TFlw_Rtdt B on A.code=B.rout_code
|
left join TFlw_Rteqp C on B.step_code=C.step_code
|
where A.code=@routecode and B.step_code=@stepcode and style='E' and @partcode+A.code+C.eqp_code+B.step_code
|
not in(select materiel_code+route_code+eqp_code+step_code from TPrteEqp_Stad where materiel_code=@partcode and route_code=@routecode and step_code=@stepcode)
|
) A
|
left join TEqpInfo B on A.eqp_code=B.code
|
left join TOrganization T on B.wksp_code=T.org_code
|
left join TMateriel_Info C on A.materiel_code=C.partcode
|
where B.is_delete<>'1' and T.is_delete<>'1' and C.is_delete<>'1'";
|
dynamicParams.Add("@partcode", partcode);
|
dynamicParams.Add("@routecode", routecode);
|
dynamicParams.Add("@stepcode", stepcode);
|
}
|
else //外协工序
|
{
|
total = 0; //总条数
|
//sql = @"select B.code as eqpcode,B.name as eqpname,'' as wksp_code,'' as wksp_name,A.eqp_value,A.stand_value,A.cavity_qty,A.unprice
|
// from (
|
// select materiel_code, eqp_code,eqp_value,stand_value,cavity_qty,unprice from TPrteEqp_Stad
|
// where materiel_code=@partcode and route_code=@routecode and step_code=@stepcode
|
// union all
|
// select @partcode as materiel_code,eqp_code,'0' as eqp_value,'0' as stand_value,'0' as cavity_qty,'0' as unprice
|
// from TFlw_Rteqp where step_code=@stepcode and style='W' and @partcode+eqp_code+step_code not in(select materiel_code+eqp_code+step_code from TPrteEqp_Stad)
|
// ) A
|
// left join TCustomer B on A.eqp_code=B.code
|
// where B.is_delete<>'1'";
|
sql = @"select B.code as eqpcode,B.name as eqpname,'' as wksp_code,'' as wksp_name,A.eqp_value,A.stand_value,A.cavity_qty,A.unprice
|
from (
|
select materiel_code, eqp_code,eqp_value,stand_value,cavity_qty,unprice from TPrteEqp_Stad
|
where materiel_code=@partcode and route_code=@routecode and step_code=@stepcode
|
union all
|
select @partcode as materiel_code,C.eqp_code,'0' as eqp_value,'0' as stand_value,'0' as cavity_qty,'0' as unprice
|
from TFlw_Rout A
|
inner join TFlw_Rtdt B on A.code=B.rout_code
|
left join TFlw_Rteqp C on B.step_code=C.step_code
|
where A.code=@routecode and B.step_code=@stepcode and style='W' and @partcode+A.code+C.eqp_code+B.step_code
|
not in(select materiel_code+route_code+eqp_code+step_code from TPrteEqp_Stad where materiel_code=@partcode and route_code=@routecode and step_code=@stepcode)
|
) A
|
left join TCustomer B on A.eqp_code=B.code
|
where B.is_delete<>'1'";
|
dynamicParams.Add("@partcode", partcode);
|
dynamicParams.Add("@routecode", routecode);
|
dynamicParams.Add("@stepcode", stepcode);
|
}
|
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 BeatRateSearch(string partcode, string routecode, string stepcode, string eqpcode, int startNum, int endNum, string prop, string order)
|
{
|
var dynamicParams = new DynamicParameters();
|
string search = "";
|
try
|
{
|
if (partcode != "" && partcode != null)
|
{
|
search += "and A.materiel_code=@partcode ";
|
dynamicParams.Add("@partcode", partcode);
|
}
|
if (routecode != "" && routecode != null)
|
{
|
search += "and A.route_code=@routecode ";
|
dynamicParams.Add("@routecode", routecode);
|
}
|
if (stepcode != "" && stepcode != null)
|
{
|
search += "and A.step_code=@stepcode ";
|
dynamicParams.Add("@stepcode", stepcode);
|
}
|
if (eqpcode != "" && eqpcode != null)
|
{
|
search += "and A.eqp_code=@eqpcode";
|
dynamicParams.Add("@eqpcode", eqpcode);
|
}
|
if (search == "")
|
{
|
search = "and 1=1 ";
|
}
|
// --------------查询指定数据--------------
|
var total = 0; //总条数
|
var sql = @"select A.materiel_code as partcode,B.partname,B.partspec,A.route_code,C.name as route_name,
|
D.stepcode,D.stepname,A.eqp_code,E.name as eqp_name,E.wksp_code,F.org_name as wksp_name,
|
A.eqp_value,A.stand_value,A.cavity_qty,A.unprice
|
from TPrteEqp_Stad A
|
left join TMateriel_Info B on A.materiel_code=B.partcode
|
left join TFlw_Rout C on A.route_code=C.code
|
left join TStep D on A.step_code=D.stepcode
|
left join TEqpInfo E on A.eqp_code=E.code
|
left join TOrganization F on E.wksp_code=F.org_code
|
where B.is_delete<>'1' and C.is_delete<>'1' and D.is_delete<>'1' and E.is_delete<>'1' and F.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 SaveBeatRate(string partcode, string routecode, string stepcode, string eqpcode, string wkshopcode, string eqp_value, string stand_value, string cavity_qty, string unprice, string username)
|
{
|
var sql = "";
|
List<object> list = new List<object>();
|
var dynamicParams = new DynamicParameters();
|
try
|
{
|
list.Clear();
|
//当前工序是否关联工艺路线
|
sql = @"select * from TPrteEqp_Stad
|
where materiel_code=@partcode and route_code=@routecode and step_code=@stepcode and eqp_code=@eqpcode";
|
dynamicParams.Add("@partcode", partcode);
|
dynamicParams.Add("@routecode", routecode);
|
dynamicParams.Add("@stepcode", stepcode);
|
dynamicParams.Add("@eqpcode", eqpcode);
|
var data = DapperHelper.selectdata(sql, dynamicParams);
|
if (data.Rows.Count > 0) //被设置关联过就修改
|
{
|
sql = @"update TPrteEqp_Stad set stand_value=@stand_value,eqp_value=@eqp_value,cavity_qty=@cavity_qty,unprice=@unprice,lm_user=@username,lm_date=@userdate
|
where materiel_code=@partcode and route_code=@routecode and step_code=@stepcode and eqp_code=@eqpcode ";
|
list.Add(new
|
{
|
str = sql,
|
parm = new
|
{
|
partcode = partcode,
|
routecode = routecode,
|
stepcode = stepcode,
|
eqpcode = eqpcode,
|
stand_value = stand_value,
|
eqp_value = eqp_value,
|
cavity_qty = cavity_qty,
|
unprice = unprice,
|
username = username,
|
userdate = DateTime.Now.ToString()
|
}
|
});
|
}
|
else
|
{
|
sql = @"insert into TPrteEqp_Stad(materiel_code,eqp_code,stand_value,route_code,unprice,eqp_value,cavity_qty,wkspcode,lm_user,lm_date,step_code)
|
values(@partcode,@eqpcode,@stand_value,@routecode,@unprice,@eqp_value,@cavity_qty,@wkspcode,@username,@userdate,@stepcode)";
|
list.Add(new
|
{
|
str = sql,
|
parm = new
|
{
|
partcode = partcode,
|
eqpcode = eqpcode,
|
stand_value = stand_value,
|
routecode = routecode,
|
unprice = unprice,
|
eqp_value = eqp_value,
|
cavity_qty = cavity_qty,
|
wkspcode = wkshopcode,
|
username = username,
|
userdate = DateTime.Now.ToString(),
|
stepcode = stepcode
|
}
|
});
|
}
|
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 SaveBeatRate(string partcode, string routecode, string stepcode, string eqpcode)
|
{
|
var sql = "";
|
List<object> list = new List<object>();
|
var dynamicParams = new DynamicParameters();
|
try
|
{
|
|
//删除节拍工价表
|
sql = @"delete TPrteEqp_Stad where materiel_code=@partcode and route_code=@routecode and step_code=@stepcode and eqp_code=@eqpcode";
|
list.Add(new
|
{
|
str = sql,
|
parm = new
|
{
|
partcode = partcode,
|
routecode = routecode,
|
stepcode = stepcode,
|
eqpcode = eqpcode
|
}
|
});
|
|
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
|
}
|
}
|
