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 DeviceManagerDAL
|
{
|
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 DeviceTypeSearch(int startNum, int endNum, string prop, string order)
|
{
|
var dynamicParams = new DynamicParameters();
|
try
|
{
|
// --------------查询指定数据--------------
|
var total = 0; //总条数
|
var sql = @"select id, code,name,remark
|
from TEqpType
|
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 AddUpdateDeviceType(List<ObjectDataCont> json, string username)
|
{
|
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 TEqpType(code,name,lm_user,lm_date,remark) values(@code,@name,@username,@createdate,@description)";
|
list.Add(new
|
{
|
str = sql,
|
parm = new
|
{
|
code = json[i].code,
|
name = json[i].name,
|
username = username,
|
createdate = DateTime.Now.ToString(),
|
description = json[i].description
|
}
|
});
|
}
|
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 DeleteDeviceType(string devicetypecode)
|
{
|
var sql = "";
|
List<object> list = new List<object>();
|
var dynamicParams = new DynamicParameters();
|
try
|
{
|
list.Clear();
|
//判断设备类型下是否关联设备组
|
sql = @"select * from TEqpGroup where eqptype_code=@devicetypecode and is_delete<>'1'";
|
dynamicParams.Add("@devicetypecode", devicetypecode);
|
var data = DapperHelper.selectdata(sql, dynamicParams);
|
if (data.Rows.Count > 0)
|
{
|
mes.code = "300";
|
mes.count = 0;
|
mes.Message = "当前设备类型已有关联设备组,不允许删除!";
|
mes.data = null;
|
}
|
else
|
{
|
//判断设备有无关联设备类型
|
sql = @"select * from TEqpInfo where eqptype_code=@devicetypecode and is_delete<>'1'";
|
dynamicParams.Add("@devicetypecode", devicetypecode);
|
var data0 = DapperHelper.selectdata(sql, dynamicParams);
|
if (data0.Rows.Count > 0)
|
{
|
mes.code = "300";
|
mes.count = 0;
|
mes.Message = "当前设备类型已有关联设备组,不允许删除!";
|
mes.data = null;
|
}
|
else
|
{
|
sql = @"delete TEqpType where code=@devicetypecode";
|
list.Add(new { str = sql, parm = new { devicetypecode = devicetypecode } });
|
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 DeviceTypeSelect()
|
{
|
string sql = "";
|
var dynamicParams = new DynamicParameters();
|
List<TreeObejct> list = new List<TreeObejct>();
|
try
|
{
|
//获取设备类型数据
|
sql = @"select code,name from TEqpType 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 DeviceGroupSearch(int startNum, int endNum, string prop, string order)
|
{
|
var dynamicParams = new DynamicParameters();
|
try
|
{
|
// --------------查询指定数据--------------
|
var total = 0; //总条数
|
var sql = @"select A.code,A.name,A.eqptype_code,B.name as eqptype_name,A.remark
|
from TEqpGroup A
|
inner join TEqpType B on A.eqptype_code=B.code
|
where A.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 AddUpdateDeviceGroup(List<ObjectDataCont> json, string username)
|
{
|
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 TEqpGroup(code,name,lm_user,lm_date,remark,eqptype_code) values(@code,@name,@username,@createdate,@description,@eqptype_code)";
|
list.Add(new
|
{
|
str = sql,
|
parm = new
|
{
|
code = json[i].code,
|
name = json[i].name,
|
username = username,
|
createdate = DateTime.Now.ToString(),
|
description = json[i].description,
|
eqptype_code = json[i].group
|
}
|
});
|
}
|
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 DeleteDeviceGroup(string devicegroupcode)
|
{
|
var sql = "";
|
List<object> list = new List<object>();
|
var dynamicParams = new DynamicParameters();
|
try
|
{
|
list.Clear();
|
//判断设备组下是否关联设备
|
sql = @"select * from TEqpInfo where eqpgroup_code=@devicegroupcode and is_delete<>'1'";
|
dynamicParams.Add("@devicegroupcode", devicegroupcode);
|
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 TEqpGroup where code=@devicegroupcode";
|
list.Add(new { str = sql, parm = new { devicegroupcode = devicegroupcode } });
|
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 DeviceTypeSelectGroup(string eqptypecode)
|
{
|
string sql = "";
|
var dynamicParams = new DynamicParameters();
|
List<TreeObejct> list = new List<TreeObejct>();
|
try
|
{
|
//获取设备类型数据
|
sql = @"select code,name from TEqpGroup where eqptype_code=@eqptypecode and is_delete<>'1'";
|
dynamicParams.Add("@eqptypecode", eqptypecode);
|
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 WorkShopSelect()
|
{
|
string sql = "";
|
try
|
{
|
//获取设备类型数据
|
sql = @"select org_code as code,org_name as name from TOrganization where description='W' and 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 WorkShopSelectLine(string workshopcode)
|
{
|
string sql = "";
|
var dynamicParams = new DynamicParameters();
|
List<TreeObejct> list = new List<TreeObejct>();
|
try
|
{
|
//获取角色类型集合(包含绑定标识)
|
dynamicParams.Add("@workshop_code", workshopcode);
|
var data = DapperHelper.selectProcedure("h_p_T_WorkShopSelectLine", 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 DeviceMangerSearch(string deviceCode, string deviceName, string status, string workShop, string deviceType, string deviceGroup, int startNum, int endNum, string prop, string order)
|
{
|
var dynamicParams = new DynamicParameters();
|
string search = "";
|
try
|
{
|
if (deviceCode != "" && deviceCode != null)
|
{
|
search += "and A.code like '%'+@deviceCode+'%' ";
|
dynamicParams.Add("@deviceCode", deviceCode);
|
}
|
if (deviceName != "" && deviceName != null)
|
{
|
search += "and A.name like '%'+@deviceName+'%' ";
|
dynamicParams.Add("@deviceName", deviceName);
|
}
|
if (status != "" && status != null)
|
{
|
search += "and A.enable=@status ";
|
dynamicParams.Add("@status", status);
|
}
|
if (workShop != "" && workShop != null)
|
{
|
search += "and A.wksp_code=@workShop ";
|
dynamicParams.Add("@workShop", workShop);
|
}
|
if (deviceType != "" && deviceType != null)
|
{
|
search += "and A.eqptype_code=@deviceType ";
|
dynamicParams.Add("@deviceType", deviceType);
|
}
|
if (deviceGroup != "" && deviceGroup != null)
|
{
|
search += "and A.eqpgroup_code=@deviceGroup ";
|
dynamicParams.Add("@deviceGroup", deviceGroup);
|
}
|
if (search == "")
|
{
|
search = "and 1=1 ";
|
}
|
//search = search.Substring(3);//截取索引2后面的字符
|
// --------------查询指定数据--------------
|
var total = 0; //总条数
|
var sql = @"select A.id,A.code,A.name,A.eqptype_code,B.name as eqptype_name,A.eqpgroup_code,G.name as eqpgroup_name,A.enable,
|
A.input_date,A.wksp_code,T.org_name as wksp_name,A.Line_code,L.org_name as Line_name,A.opc_uom,A.operation_ration,U.username as lm_user,A.lm_date
|
from TEqpInfo A
|
left join TEqpType B on A.eqptype_code=B.code
|
left join TEqpGroup G on A.eqpgroup_code=G.code
|
left join TOrganization T on A.wksp_code=T.org_code
|
left join TOrganization L on A.Line_code=L.org_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 AddUpdateDeviceManger(string deviceid, string devicecode, string devicename, string devicetype, string devicegroup, string importdate, string workshop, string linecode, string status, string ratio, string usercode, string operType)
|
{
|
var dynamicParams = new DynamicParameters();
|
try
|
{
|
if (operType == "Add")
|
{
|
var sql = @"insert into TEqpInfo(code,name,eqptype_code,eqpgroup_code,wksp_code,line_code,input_date,operation_ration,enable,opc_uom,lm_user,lm_date)
|
values(@devicecode,@devicename,@devicetype,@devicegroup,@workshop,@linecode,@importdate,@ratio,@status,@uom,@usercode,@CreateDate)";
|
dynamicParams.Add("@devicecode", devicecode);
|
dynamicParams.Add("@devicename", devicename);
|
dynamicParams.Add("@devicetype", devicetype);
|
dynamicParams.Add("@devicegroup", devicegroup);
|
dynamicParams.Add("@workshop", workshop);
|
dynamicParams.Add("@linecode", linecode);
|
dynamicParams.Add("@importdate", importdate);
|
dynamicParams.Add("@ratio", ratio);
|
dynamicParams.Add("@status", status);
|
dynamicParams.Add("@uom", "台");
|
dynamicParams.Add("@usercode", usercode);
|
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 TEqpInfo set name=@devicename,eqptype_code=@devicetype,eqpgroup_code=@devicegroup,input_date=@importdate,
|
wksp_code=@workshop,line_code=@linecode,enable=@status,operation_ration=@ratio,lm_user=@usercode,lm_date=@CreateDate
|
where id=@deviceid";
|
dynamicParams.Add("@deviceid", deviceid);
|
dynamicParams.Add("@devicename", devicename);
|
dynamicParams.Add("@devicetype", devicetype);
|
dynamicParams.Add("@devicegroup", devicegroup);
|
dynamicParams.Add("@workshop", workshop);
|
dynamicParams.Add("@linecode", linecode);
|
dynamicParams.Add("@importdate", importdate);
|
dynamicParams.Add("@ratio", ratio);
|
dynamicParams.Add("@status", status);
|
dynamicParams.Add("@usercode", usercode);
|
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 DeleteDeviceManger(string devicecode)
|
{
|
var sql = "";
|
List<object> list = new List<object>();
|
var dynamicParams = new DynamicParameters();
|
try
|
{
|
list.Clear();
|
//判断设备是否有关联工艺路线的生产订单(未关闭),不允许删除
|
sql = @"select A.code,M.wo_code,R.name from TEqpInfo A
|
inner join TFlw_Rteqp B on A.code=B.eqp_code
|
inner join TStep C on B.step_code=C.stepcode
|
inner join TFlw_Rtdt D on C.stepcode=D.step_code
|
inner join TFlw_Rout R on D.rout_code=R.code
|
inner join TK_Wrk_Man M on R.code=M.route_code
|
where A.code=@devicecode";
|
dynamicParams.Add("@devicecode", devicecode);
|
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 TFlw_Rteqp
|
where eqp_code=@devicecode";
|
dynamicParams.Add("@devicecode", devicecode);
|
var data0 = 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 eqp_code=@devicecode";
|
dynamicParams.Add("@devicecode", devicecode);
|
var data1 = DapperHelper.selectdata(sql, dynamicParams);
|
if (data.Rows.Count > 0)
|
{
|
mes.code = "300";
|
mes.count = 0;
|
mes.Message = "当前设备已指定节拍工价,不允许删除!";
|
mes.data = null;
|
return mes;
|
}
|
|
//删除设备
|
sql = @"update TEqpInfo set is_delete='1' where code=@devicecode";
|
list.Add(new { str = sql, parm = new { devicecode = devicecode } });
|
//删除设备关联点检标准表
|
sql = @"delete TEqpchk_Eqp where eqp_code=@devicecode";
|
list.Add(new { str = sql, parm = new { devicecode = devicecode } });
|
//删除设备绑定保养标准表
|
sql = @"delete TEqpmai_Eqp where eqp_code=@devicecode";
|
list.Add(new { str = sql, parm = new { devicecode = devicecode } });
|
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 DeviceCheckItemSearch(string checkitemcode, string checkitemname, string checkdescr, string isqrcode, string cycle, int startNum, int endNum, string prop, string order)
|
{
|
var dynamicParams = new DynamicParameters();
|
string search = "";
|
try
|
{
|
if (checkitemcode != "" && checkitemcode != null)
|
{
|
search += "and A.code like '%'+@checkitemcode+'%' ";
|
dynamicParams.Add("@checkitemcode", checkitemcode);
|
}
|
if (checkitemname != "" && checkitemname != null)
|
{
|
search += "and A.name like '%'+@checkitemname+'%' ";
|
dynamicParams.Add("@checkitemname", checkitemname);
|
}
|
if (checkdescr != "" && checkdescr != null)
|
{
|
search += "and A.description like '%'+@checkdescr+'%' ";
|
dynamicParams.Add("@checkdescr", checkdescr);
|
}
|
if (isqrcode != "" && isqrcode != null)
|
{
|
search += "and A.isscan=@isqrcode ";
|
dynamicParams.Add("@isqrcode", isqrcode);
|
}
|
if (cycle != "" && cycle != null)
|
{
|
search += "and A.cycle=@cycle ";
|
dynamicParams.Add("@cycle", cycle);
|
}
|
if (search == "")
|
{
|
search = "and 1=1 ";
|
}
|
//search = search.Substring(3);//截取索引2后面的字符
|
// --------------查询指定数据--------------
|
var total = 0; //总条数
|
var sql = @"select A.id,A.code,A.name,A.description,A.isscan,A.cycle,U.username as lm_user,A.lm_date
|
from TEqpchk_Item 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 AddUpdateDeviceCheckItem(string checkitemid, string checkitemcode, string checkitemname, string checkitemdescr, string cycle, string isqrcode, string usercode, string operType)
|
{
|
var dynamicParams = new DynamicParameters();
|
try
|
{
|
if (operType == "Add")
|
{
|
var sql = @"insert into TEqpchk_Item(code,name,description,isscan,cycle,lm_user,lm_date)
|
values(@checkitemcode,@checkitemname,@checkitemdescr,@isqrcode,@cycle,@usercode,@CreateDate)";
|
dynamicParams.Add("@checkitemcode", checkitemcode);
|
dynamicParams.Add("@checkitemname", checkitemname);
|
dynamicParams.Add("@checkitemdescr", checkitemdescr);
|
dynamicParams.Add("@isqrcode", isqrcode);
|
dynamicParams.Add("@cycle", cycle);
|
dynamicParams.Add("@usercode", usercode);
|
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 TEqpchk_Item set name=@checkitemname,description=@checkitemdescr,isscan=@isqrcode,cycle=@cycle,
|
lm_user=@usercode,lm_date=@CreateDate
|
where id=@checkitemid";
|
dynamicParams.Add("@checkitemid", checkitemid);
|
dynamicParams.Add("@checkitemname", checkitemname);
|
dynamicParams.Add("@checkitemdescr", checkitemdescr);
|
dynamicParams.Add("@isqrcode", isqrcode);
|
dynamicParams.Add("@cycle", cycle);
|
dynamicParams.Add("@usercode", usercode);
|
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 DeleteDeviceCheckItem(string checkitemcode)
|
{
|
var sql = "";
|
List<object> list = new List<object>();
|
var dynamicParams = new DynamicParameters();
|
try
|
{
|
list.Clear();
|
//判断点检标准项目是否有关联点检标准,不允许删除
|
sql = @"select * from TEqpchk_Deta
|
where code=@checkitemcode";
|
dynamicParams.Add("@checkitemcode", checkitemcode);
|
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 TEqpchk_Item where code=@checkitemcode";
|
list.Add(new { str = sql, parm = new { checkitemcode = checkitemcode } });
|
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 DeviceMaiItemSearch(string maiitemcode, string maiitemname, string maidescr, string isqrcode, int startNum, int endNum, string prop, string order)
|
{
|
var dynamicParams = new DynamicParameters();
|
string search = "";
|
try
|
{
|
if (maiitemcode != "" && maiitemcode != null)
|
{
|
search += "and A.code like '%'+@maiitemcode+'%' ";
|
dynamicParams.Add("@maiitemcode", maiitemcode);
|
}
|
if (maiitemname != "" && maiitemname != null)
|
{
|
search += "and A.name like '%'+@maiitemname+'%' ";
|
dynamicParams.Add("@maiitemname", maiitemname);
|
}
|
if (maidescr != "" && maidescr != null)
|
{
|
search += "and A.description like '%'+@maidescr+'%' ";
|
dynamicParams.Add("@maidescr", maidescr);
|
}
|
if (isqrcode != "" && isqrcode != null)
|
{
|
search += "and A.isscan=@isqrcode ";
|
dynamicParams.Add("@isqrcode", isqrcode);
|
}
|
if (search == "")
|
{
|
search = "and 1=1 ";
|
}
|
//search = search.Substring(3);//截取索引2后面的字符
|
// --------------查询指定数据--------------
|
var total = 0; //总条数
|
var sql = @"select A.id,A.code,A.name,A.description,A.isscan,U.username as lm_user,A.lm_date
|
from TEqpmai_Item 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 AddUpdateDeviceMaiItem(string maiitemid, string maiitemcode, string maiitemname, string maiitemdescr, string isqrcode, string usercode, string operType)
|
{
|
var dynamicParams = new DynamicParameters();
|
try
|
{
|
if (operType == "Add")
|
{
|
var sql = @"insert into TEqpmai_Item(code,name,description,isscan,lm_user,lm_date)
|
values(@maiitemcode,@maiitemname,@maiitemdescr,@isqrcode,@usercode,@CreateDate)";
|
dynamicParams.Add("@maiitemcode", maiitemcode);
|
dynamicParams.Add("@maiitemname", maiitemname);
|
dynamicParams.Add("@maiitemdescr", maiitemdescr);
|
dynamicParams.Add("@isqrcode", isqrcode);
|
dynamicParams.Add("@usercode", usercode);
|
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 TEqpmai_Item set name=@maiitemname,description=@maiitemdescr,isscan=@isqrcode,
|
lm_user=@usercode,lm_date=@CreateDate
|
where id=@maiitemid";
|
dynamicParams.Add("@maiitemid", maiitemid);
|
dynamicParams.Add("@maiitemname", maiitemname);
|
dynamicParams.Add("@maiitemdescr", maiitemdescr);
|
dynamicParams.Add("@isqrcode", isqrcode);
|
dynamicParams.Add("@usercode", usercode);
|
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 DeleteDeviceMaiItem(string maiitemcode)
|
{
|
var sql = "";
|
List<object> list = new List<object>();
|
var dynamicParams = new DynamicParameters();
|
try
|
{
|
list.Clear();
|
//判断保养标准项目是否有关联保养标准,不允许删除
|
sql = @"select * from TEqpmai_Deta
|
where code=@maiitemcode";
|
dynamicParams.Add("@maiitemcode", maiitemcode);
|
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 TEqpmai_Item where code=@maiitemcode";
|
list.Add(new { str = sql, parm = new { maiitemcode = maiitemcode } });
|
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 DeviceCheckStandArdSearch(string checkstandcode, string checkstandname, string checkcontr, int startNum, int endNum, string prop, string order)
|
{
|
var dynamicParams = new DynamicParameters();
|
string search = "";
|
try
|
{
|
if (checkstandcode != "" && checkstandcode != null)
|
{
|
search += "and A.code like '%'+@checkstandcode+'%' ";
|
dynamicParams.Add("@checkstandcode", checkstandcode);
|
}
|
if (checkstandname != "" && checkstandname != null)
|
{
|
search += "and A.name like '%'+@checkstandname+'%' ";
|
dynamicParams.Add("@checkstandname", checkstandname);
|
}
|
if (checkcontr != "" && checkcontr != null)
|
{
|
search += "and A.iscontr=@checkcontr ";
|
dynamicParams.Add("@checkcontr", checkcontr);
|
}
|
if (search == "")
|
{
|
search = "and 1=1 ";
|
}
|
//search = search.Substring(3);//截取索引2后面的字符
|
// --------------查询指定数据--------------
|
var total = 0; //总条数
|
var sql = @"select A.id,A.code,A.name,A.description,A.iscontr,A.is_checkeqp,U.username as lm_user,A.lm_date
|
from TEqpchk_Main 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 DeviceCheckItemSelect()
|
{
|
string sql = "";
|
try
|
{
|
//获取设备点检项目数据
|
sql = @"select code,name,description from TEqpchk_Item 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 ViewDeviceCheckStanedSearch(string checkstand_code)
|
{
|
string sql = "";
|
var dynamicParams = new DynamicParameters();
|
try
|
{
|
//获取设备点检标准信息
|
sql = @"select code,name,description,iscontr
|
from TEqpchk_Main
|
where code=@checkstand_code and is_delete<>'1'";
|
dynamicParams.Add("@checkstand_code", checkstand_code);
|
var data = DapperHelper.selectdata(sql, dynamicParams);
|
RoutEdit rout = new RoutEdit();
|
if (data.Rows.Count > 0)
|
{
|
rout.code = data.Rows[0]["CODE"].ToString();
|
rout.name = data.Rows[0]["NAME"].ToString();
|
rout.description = data.Rows[0]["DESCRIPTION"].ToString();
|
rout.enable = data.Rows[0]["ISCONTR"].ToString();
|
}
|
else
|
{
|
mes.code = "300";
|
mes.count = 0;
|
mes.Message = "无点检标准信息!";
|
mes.data = null;
|
return mes;
|
}
|
//根据设备点检标准编码获取关联的检验项目信息
|
sql = @"select B.code,B.name,A.chkdesc,A.isscan,A.cycle from TEqpchk_Deta A
|
left join TEqpchk_Item B on A.code=B.code
|
where A.eqpchk_main_code=@checkstaned_code order by A.seq asc";
|
dynamicParams.Add("@checkstaned_code", rout.code);
|
var data0 = DapperHelper.selectdata(sql, dynamicParams);
|
rout.Data = data0;
|
|
mes.code = "200";
|
mes.Message = "查询成功!";
|
mes.data = rout;
|
}
|
catch (Exception e)
|
{
|
mes.code = "300";
|
mes.count = 0;
|
mes.Message = e.Message;
|
mes.data = null;
|
}
|
return mes;
|
}
|
#endregion
|
|
#region[设备点检标准新增编辑]
|
public static ToMessage AddUpdateDeviceCheckStandArd(string opertype, RoutEdit json, string username)
|
{
|
var sql = "";
|
var dynamicParams = new DynamicParameters();
|
List<object> list = new List<object>();
|
try
|
{
|
if (opertype == "Add")
|
{
|
//新增设备点检标准主表
|
sql = @"insert into TEqpchk_Main(code,name,description,iscontr,lm_user,lm_date) values(@code,@name,@descr,@iscontr,@lm_user,@lm_date)";
|
list.Add(new { str = sql, parm = new { code = json.code, name = json.name, descr = json.description, iscontr = json.enable, lm_user = username, lm_date = DateTime.Now.ToString() } });
|
for (int i = 0; i < json.Data.Rows.Count; i++)
|
{
|
//新增设备点检标准关联点检项子表
|
sql = @"insert TEqpchk_Deta (seq,code,name,eqpchk_main_code,cycle,chkdesc,isscan,lm_user,lm_date)
|
values(@checkitem_seq,@checkitem_code,@checkitem_name,@code,@cycle,@chkdesc,@isscan,@lm_user,@lm_date)";
|
list.Add(new
|
{
|
str = sql,
|
parm = new
|
{
|
checkitem_seq = Convert.ToInt32(json.Data.Rows[i]["CHECKITEM_SEQ"].ToString()),
|
checkitem_code = json.Data.Rows[i]["CHECKITEM_CODE"].ToString(),
|
checkitem_name = json.Data.Rows[i]["CHECKITEM_NAME"].ToString(),
|
code = json.code,
|
cycle = json.Data.Rows[i]["CYCLE"].ToString(),
|
chkdesc= json.Data.Rows[i]["CHECKITEM_DESCR"].ToString(),
|
isscan= json.Data.Rows[i]["ISSCAN"].ToString(),
|
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;
|
}
|
}
|
if (opertype == "Update")
|
{
|
//删除点检标准关联点检项目子表
|
sql = @"delete from TEqpchk_Deta where eqpchk_main_code=@checkstaned_code";
|
list.Add(new { str = sql, parm = new { checkstaned_code = json.code } });
|
//修改点检标准主表
|
sql = @"update TEqpchk_Main set name=@checkstaned_name,description=@checkstaned_desc,iscontr=@iscontr where code=@checkstaned_code";
|
list.Add(new { str = sql, parm = new { checkstaned_code = json.code, checkstaned_name = json.name, checkstaned_desc = json.description,iscontr = json.enable } });
|
//新增点检标准关联点检项目子表
|
for (int i = 0; i < json.Data.Rows.Count; i++)
|
{
|
sql = @"insert TEqpchk_Deta (seq,code,name,eqpchk_main_code,cycle,chkdesc,isscan,lm_user,lm_date)
|
values(@checkitem_seq,@checkitem_code,@checkitem_name,@code,@cycle,@chkdesc,@isscan,@lm_user,@lm_date)";
|
list.Add(new
|
{
|
str = sql,
|
parm = new
|
{
|
checkitem_seq = Convert.ToInt32(json.Data.Rows[i]["CHECKITEM_SEQ"].ToString()),
|
checkitem_code = json.Data.Rows[i]["CHECKITEM_CODE"].ToString(),
|
checkitem_name = json.Data.Rows[i]["CHECKITEM_NAME"].ToString(),
|
code = json.code,
|
cycle = json.Data.Rows[i]["CYCLE"].ToString(),
|
chkdesc = json.Data.Rows[i]["CHECKITEM_DESCR"].ToString(),
|
isscan = json.Data.Rows[i]["ISSCAN"].ToString(),
|
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 DeleteDeviceCheckStaned(string checkstand_code)
|
{
|
var sql = "";
|
List<object> list = new List<object>();
|
var dynamicParams = new DynamicParameters();
|
try
|
{
|
sql = @"select * from TEqpchk_Proc_Main where eqpchkmain_code=@checkstand_code";
|
dynamicParams.Add("@checkstand_code", checkstand_code);
|
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 TEqpchk_Main where code=@checkstand_code";
|
list.Add(new { str = sql, parm = new { checkstand_code = checkstand_code } });
|
//删除设备点检标准关联点检项目子表
|
sql = @"delete TEqpchk_Deta where eqpchk_main_code=@checkstand_code";
|
list.Add(new { str = sql, parm = new { checkstand_code = checkstand_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 DeviceCheckStanedAssociationEqp(string checkstand_code)
|
{
|
string sql = "";
|
var dynamicParams = new DynamicParameters();
|
List<StandEqp> list = new List<StandEqp>();
|
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 TEqpchk_Eqp A
|
inner join TEqpInfo B on A.eqp_code=B.code
|
where A.eqpchkmain_code=@checkstand_code 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'";
|
dynamicParams.Add("@checkstand_code", checkstand_code);
|
var data = DapperHelper.selectdata(sql, dynamicParams);
|
for (int i = 0; i < data.Rows.Count; i++)
|
{
|
StandEqp rout = new StandEqp();
|
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<StandEqpCn>();
|
//根据车间编码查找设备(包含已关联标识)
|
//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 TEqpchk_Eqp A
|
// inner join TEqpInfo B on A.eqp_code=B.code
|
// where A.eqpchkmain_code=@checkstand_code 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'";
|
sql = @"select * from(
|
select AA.code,AA.name,'E' as type,(case when BB.eqpchkmain_code is null then 'N' else 'Y' end) flag,(case when AA.eqpchkmain_code is null then 'N' else 'Y' end) flage1
|
from(
|
select A.code,A.name,B.eqpchkmain_code
|
from TEqpInfo A
|
left join TEqpchk_Eqp B on A.code=B.eqp_code
|
where A.wksp_code=@wkspcode and A.is_delete<>'1'
|
) as AA
|
left join (
|
select A.code,A.name,B.eqpchkmain_code
|
from TEqpInfo A
|
inner join TEqpchk_Eqp B on A.code=B.eqp_code
|
where A.wksp_code=@wkspcode and B.eqpchkmain_code=@checkstand_code and A.is_delete<>'1'
|
) as BB on AA.code=BB.code
|
) as CC where case when flag ='N' and flage1 ='Y' then 0 else 1 end=1";
|
dynamicParams.Add("@checkstand_code", checkstand_code);
|
dynamicParams.Add("@wkspcode", data.Rows[i]["WKSP_CODE"].ToString());
|
var data0 = DapperHelper.selectdata(sql, dynamicParams);
|
for (int j = 0; j < data0.Rows.Count; j++)
|
{
|
StandEqpCn cn = new StandEqpCn();
|
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 SaveDeviceCheckStanedAssociationEqp(string checkstand_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 TEqpchk_Eqp where eqpchkmain_code=@checkstand_code";
|
list.Add(new { str = sql, parm = new { checkstand_code = checkstand_code } });
|
//标记设备点检标准表关联工作站标识
|
sql = @"update TEqpchk_Main set is_checkeqp='N' where code=@checkstand_code";
|
list.Add(new { str = sql, parm = new { checkstand_code = checkstand_code } });
|
}
|
else
|
{
|
//清除设备点检标准关联设备表数据
|
sql = @"delete TEqpchk_Eqp where eqpchkmain_code=@checkstand_code";
|
list.Add(new { str = sql, parm = new { checkstand_code = checkstand_code } });
|
//循环写入设备点检标准关联设备表
|
for (int i = 0; i < json.Count; i++)
|
{
|
sql = @"insert into TEqpchk_Eqp(eqpchkmain_code,eqp_code,lm_user,lm_date) values(@checkstand_code,@eqp_code,@lm_user,@lm_date)";
|
list.Add(new
|
{
|
str = sql,
|
parm = new
|
{
|
eqp_code = json[i].code,
|
checkstand_code = checkstand_code,
|
lm_user = username,
|
lm_date = DateTime.Now.ToString()
|
}
|
});
|
}
|
//标记设备点检标准表关联工作站标识
|
sql = @"update TEqpchk_Main set is_checkeqp='Y' where code=@checkstand_code";
|
list.Add(new { str = sql, parm = new { checkstand_code = checkstand_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 DeviceRepairStandArdSearch(string repairstandcode, string repairstandname, string repairstanddescr, int startNum, int endNum, string prop, string order)
|
{
|
var dynamicParams = new DynamicParameters();
|
string search = "";
|
try
|
{
|
if (repairstandcode != "" && repairstandcode != null)
|
{
|
search += "and A.code like '%'+@repairstandcode+'%' ";
|
dynamicParams.Add("@repairstandcode", repairstandcode);
|
}
|
if (repairstandname != "" && repairstandname != null)
|
{
|
search += "and A.name like '%'+@repairstandname+'%' ";
|
dynamicParams.Add("@repairstandname", repairstandname);
|
}
|
if (repairstanddescr != "" && repairstanddescr != null)
|
{
|
search += "and A.description like '%'+@repairstanddescr+'%' ";
|
dynamicParams.Add("@repairstanddescr", repairstanddescr);
|
}
|
|
if (search == "")
|
{
|
search = "and 1=1 ";
|
}
|
//search = search.Substring(3);//截取索引2后面的字符
|
// --------------查询指定数据--------------
|
var total = 0; //总条数
|
var sql = @"select A.id,A.code,A.name,A.description,A.is_repaireqp,A.main_cycle,U.username as lm_user,A.lm_date
|
from TEqpmai_Main 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 DeviceRepairItemSelect()
|
{
|
string sql = "";
|
try
|
{
|
//获取设备保养项目数据
|
sql = @"select code,name,description from TEqpmai_Item 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 ViewDeviceRepairStanedSearch(string repairstand_code)
|
{
|
string sql = "";
|
var dynamicParams = new DynamicParameters();
|
try
|
{
|
//获取设备保养标准信息
|
sql = @"select code,name,description,main_cycle
|
from TEqpmai_Main
|
where code=@repairstand_code and is_delete<>'1'";
|
dynamicParams.Add("@repairstand_code", repairstand_code);
|
var data = DapperHelper.selectdata(sql, dynamicParams);
|
EqpRepair rout = new EqpRepair();
|
if (data.Rows.Count > 0)
|
{
|
rout.code = data.Rows[0]["CODE"].ToString();
|
rout.name = data.Rows[0]["NAME"].ToString();
|
rout.repaircycle= data.Rows[0]["MAIN_CYCLE"].ToString();
|
rout.description = data.Rows[0]["DESCRIPTION"].ToString();
|
}
|
else
|
{
|
mes.code = "300";
|
mes.count = 0;
|
mes.Message = "无保养标准信息!";
|
mes.data = null;
|
return mes;
|
}
|
//根据设备保养标准编码获取关联的保养项目信息
|
sql = @"select B.code,B.name,A.chk_desc,A.isscan,A.cycle from TEqpmai_Deta A
|
left join TEqpmai_Item B on A.code=B.code
|
where A.eapmai_code=@repairstand_code order by A.seq asc";
|
dynamicParams.Add("@repairstand_code", rout.code);
|
var data0 = DapperHelper.selectdata(sql, dynamicParams);
|
rout.Data = data0;
|
|
mes.code = "200";
|
mes.Message = "查询成功!";
|
mes.data = rout;
|
}
|
catch (Exception e)
|
{
|
mes.code = "300";
|
mes.count = 0;
|
mes.Message = e.Message;
|
mes.data = null;
|
}
|
return mes;
|
}
|
#endregion
|
|
#region[设备保养标准新增编辑]
|
public static ToMessage AddUpdateDeviceRepairStandArd(string opertype, EqpRepair json, string username)
|
{
|
var sql = "";
|
var dynamicParams = new DynamicParameters();
|
List<object> list = new List<object>();
|
try
|
{
|
if (opertype == "Add")
|
{
|
//新增设备保养标准主表
|
sql = @"insert into TEqpmai_Main(code,name,description,main_cycle,lm_user,lm_date) values(@code,@name,@descr,@main_cycle,@lm_user,@lm_date)";
|
list.Add(new { str = sql, parm = new { code = json.code, name = json.name, descr = json.description,main_cycle=json.repaircycle, lm_user = username, lm_date = DateTime.Now.ToString() } });
|
for (int i = 0; i < json.Data.Rows.Count; i++)
|
{
|
//新增设备保养标准关联保养项子表
|
sql = @"insert TEqpmai_Deta (seq,code,name,eapmai_code,chk_desc,isscan,lm_user,lm_date)
|
values(@repairitem_seq,@repairitem_code,@repairitem_name,@code,@repairdesc,@isscan,@lm_user,@lm_date)";
|
list.Add(new
|
{
|
str = sql,
|
parm = new
|
{
|
repairitem_seq = Convert.ToInt32(json.Data.Rows[i]["REPAIRITEM_SEQ"].ToString()),
|
repairitem_code = json.Data.Rows[i]["REPAIRITEM_CODE"].ToString(),
|
repairitem_name = json.Data.Rows[i]["REPAIRITEM_NAME"].ToString(),
|
code = json.code,
|
repairdesc = json.Data.Rows[i]["REPAIRITEM_DESCR"].ToString(),
|
isscan = json.Data.Rows[i]["ISSCAN"].ToString(),
|
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;
|
}
|
}
|
if (opertype == "Update")
|
{
|
//删除保养标准关联点检项目子表
|
sql = @"delete from TEqpmai_Deta where eapmai_code=@repairstaned_code";
|
list.Add(new { str = sql, parm = new { repairstaned_code = json.code } });
|
//修改保养标准主表
|
sql = @"update TEqpmai_Main set name=@repairstaned_name,description=@repairstaned_desc,main_cycle=@main_cycle where code=@repairstaned_code";
|
list.Add(new { str = sql, parm = new { repairstaned_code = json.code, repairstaned_name = json.name, repairstaned_desc = json.description, main_cycle =json.repaircycle} });
|
//新增点检标准关联点检项目子表
|
for (int i = 0; i < json.Data.Rows.Count; i++)
|
{
|
sql = @"insert TEqpmai_Deta (seq,code,name,eapmai_code,chk_desc,isscan,lm_user,lm_date)
|
values(@repairitem_seq,@repairitem_code,@repairitem_name,@code,@repairdesc,@isscan,@lm_user,@lm_date)";
|
list.Add(new
|
{
|
str = sql,
|
parm = new
|
{
|
repairitem_seq = Convert.ToInt32(json.Data.Rows[i]["REPAIRITEM_SEQ"].ToString()),
|
repairitem_code = json.Data.Rows[i]["REPAIRITEM_CODE"].ToString(),
|
repairitem_name = json.Data.Rows[i]["REPAIRITEM_NAME"].ToString(),
|
code = json.code,
|
repairdesc = json.Data.Rows[i]["REPAIRITEM_DESCR"].ToString(),
|
isscan = json.Data.Rows[i]["ISSCAN"].ToString(),
|
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 DeleteDeviceRepairStaned(string repairstand_code)
|
{
|
var sql = "";
|
List<object> list = new List<object>();
|
var dynamicParams = new DynamicParameters();
|
try
|
{
|
sql = @"select * from TEqpmaint_Proc_Main where eqpmaint_code=@repairstand_code";
|
dynamicParams.Add("@repairstand_code", repairstand_code);
|
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 TEqpmai_Main where code=@repairstand_code";
|
list.Add(new { str = sql, parm = new { repairstand_code = repairstand_code } });
|
//删除设备保养标准关联保养项目子表
|
sql = @"delete TEqpmai_Deta where eapmai_code=@repairstand_code";
|
list.Add(new { str = sql, parm = new { repairstand_code = repairstand_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 DeviceRepairStanedAssociationEqp(string repairstand_code)
|
{
|
string sql = "";
|
var dynamicParams = new DynamicParameters();
|
List<StandEqp> list = new List<StandEqp>();
|
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 TEqpmai_Eqp A
|
inner join TEqpInfo B on A.eqp_code=B.code
|
where A.eapmai_code=@repairstand_code 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'";
|
|
dynamicParams.Add("@repairstand_code", repairstand_code);
|
var data = DapperHelper.selectdata(sql, dynamicParams);
|
for (int i = 0; i < data.Rows.Count; i++)
|
{
|
StandEqp rout = new StandEqp();
|
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<StandEqpCn>();
|
//根据车间编码查找设备(包含已关联标识)
|
//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 TEqpmai_Eqp A
|
// inner join TEqpInfo B on A.eqp_code=B.code
|
// where A.eapmai_code=@repairstand_code 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'";
|
sql = @"select * from(
|
select AA.code,AA.name,'E' as type,(case when BB.eapmai_code is null then 'N' else 'Y' end) flag,(case when AA.eapmai_code is null then 'N' else 'Y' end) flage1
|
from(
|
select A.code,A.name,B.eapmai_code
|
from TEqpInfo A
|
left join TEqpmai_Eqp B on A.code=B.eqp_code
|
where A.wksp_code=@wkspcode and A.is_delete<>'1'
|
) as AA
|
left join (
|
select A.code,A.name,B.eapmai_code
|
from TEqpInfo A
|
inner join TEqpmai_Eqp B on A.code=B.eqp_code
|
where A.wksp_code=@wkspcode and B.eapmai_code=@repairstand_code and A.is_delete<>'1'
|
) as BB on AA.code=BB.code
|
) as CC where case when flag ='N' and flage1 ='Y' then 0 else 1 end=1";
|
dynamicParams.Add("@repairstand_code", repairstand_code);
|
dynamicParams.Add("@wkspcode", data.Rows[i]["WKSP_CODE"].ToString());
|
var data0 = DapperHelper.selectdata(sql, dynamicParams);
|
for (int j = 0; j < data0.Rows.Count; j++)
|
{
|
StandEqpCn cn = new StandEqpCn();
|
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 SaveDeviceRepairStanedAssociationEqp(string repairstand_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 TEqpmai_Eqp where eapmai_code=@repairstand_code";
|
list.Add(new { str = sql, parm = new { repairstand_code = repairstand_code } });
|
//标记设备保养标准表关联工作站标识
|
sql = @"update TEqpmai_Main set is_repaireqp='N' where code=@repairstand_code";
|
list.Add(new { str = sql, parm = new { repairstand_code = repairstand_code } });
|
}
|
else
|
{
|
//清除设备保养标准关联设备表数据
|
sql = @"delete TEqpmai_Eqp where eapmai_code=@repairstand_code";
|
list.Add(new { str = sql, parm = new { repairstand_code = repairstand_code } });
|
//循环写入设备保养标准关联设备表
|
for (int i = 0; i < json.Count; i++)
|
{
|
sql = @"insert into TEqpmai_Eqp(eapmai_code,eqp_code,lm_user,lm_date) values(@repairstand_code,@eqp_code,@lm_user,@lm_date)";
|
list.Add(new
|
{
|
str = sql,
|
parm = new
|
{
|
eqp_code = json[i].code,
|
repairstand_code = repairstand_code,
|
lm_user = username,
|
lm_date = DateTime.Now.ToString()
|
}
|
});
|
}
|
//标记设备保养标准表关联工作站标识
|
sql = @"update TEqpmai_Main set is_repaireqp='Y' where code=@repairstand_code";
|
list.Add(new { str = sql, parm = new { repairstand_code = repairstand_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
|
}
|
}
|
