using Dapper;
|
using Newtonsoft.Json.Linq;
|
using System;
|
using System.Collections.Generic;
|
using System.Data;
|
using System.Data.SqlClient;
|
using System.Linq;
|
using System.Threading.Tasks;
|
using VueWebCoreApi.Models;
|
using VueWebCoreApi.Tools;
|
|
namespace VueWebCoreApi.DLL.DAL
|
{
|
public class QualityManagementDAL
|
{
|
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 DedectSearch(string defectcode, string defectname, string defectdescr, int startNum, int endNum, string prop, string order)
|
{
|
var dynamicParams = new DynamicParameters();
|
string search = "";
|
try
|
{
|
if (defectcode != "" && defectcode != null)
|
{
|
search += "and A.code like '%'+@defectcode+'%' ";
|
dynamicParams.Add("@defectcode", defectcode);
|
}
|
if (defectname != "" && defectname != null)
|
{
|
search += "and A.name like '%'+@defectname+'%' ";
|
dynamicParams.Add("@defectname", defectname);
|
}
|
if (defectdescr != "" && defectdescr != null)
|
{
|
search += "and A.description like '%'+@defectdescr+'%' ";
|
dynamicParams.Add("@defectdescr", defectdescr);
|
}
|
|
// --------------查询指定数据--------------
|
var total = 0; //总条数
|
var sql = @"select A.code,A.name,A.description,U.username as lm_user,A.lm_date from TDefect A
|
left join TUser U on A.lm_user=U.usercode
|
where 1=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 AddUpdateDedect(string defectcode, string defectname, string defectdescr, string opertype, User us)
|
{
|
var sql = "";
|
var dynamicParams = new DynamicParameters();
|
List<object> list = new List<object>();
|
try
|
{
|
if (opertype == "Add")
|
{
|
//写入缺陷定义定义表
|
sql = @"insert into TDefect(code,name,description,is_step,lm_user,lm_date)
|
values(@defectcode,@defectname,@description,@is_step,@username,@CreateDate)";
|
list.Add(new
|
{
|
str = sql,
|
parm = new
|
{
|
defectcode = defectcode,
|
defectname = defectname,
|
description = defectdescr,
|
is_step="N",
|
username = us.usercode,
|
CreateDate = DateTime.Now.ToString()
|
}
|
});
|
bool aa = DapperHelper.DoTransaction(list);
|
if (aa)
|
{
|
//写入操作记录表
|
LogHelper.DbOperateLog(us.usercode, "新增", "缺陷编码:" + defectcode + "缺陷名称:" + defectname, us.usertype);
|
mes.code = "200";
|
mes.count = 0;
|
mes.Message = "缺陷定义新增成功!";
|
mes.data = null;
|
}
|
else
|
{
|
mes.code = "300";
|
mes.count = 0;
|
mes.Message = "缺陷定义新增失败!";
|
mes.data = null;
|
}
|
}
|
if (opertype == "Update")
|
{
|
sql = @"update TDefect set name=@defectname,description=@description,lm_user=@username,lm_date=@CreateDate where code=@defectcode";
|
list.Add(new
|
{
|
str = sql,
|
parm = new
|
{
|
defectcode = defectcode,
|
defectname = defectname,
|
description = defectdescr,
|
username = us.usercode,
|
CreateDate = DateTime.Now.ToString()
|
}
|
});
|
bool aa = DapperHelper.DoTransaction(list);
|
if (aa)
|
{
|
//写入操作记录表
|
LogHelper.DbOperateLog(us.usercode, "修改", "缺陷编码:" + defectcode + "缺陷名称:" + defectname, us.usertype);
|
mes.code = "200";
|
mes.count = 0;
|
mes.Message = "缺陷定义修改成功!";
|
mes.data = null;
|
}
|
else
|
{
|
mes.code = "300";
|
mes.count = 0;
|
mes.Message = "缺陷定义修改失败!";
|
mes.data = null;
|
}
|
}
|
}
|
catch (Exception e)
|
{
|
mes.code = "300";
|
mes.count = 0;
|
mes.Message = e.Message;
|
mes.data = null;
|
}
|
return mes;
|
}
|
#endregion
|
|
#region[质量管理，缺陷定义删除]
|
public static ToMessage DeleteDedect(string defectcode, User us)
|
{
|
var sql = "";
|
List<object> list = new List<object>();
|
var dynamicParams = new DynamicParameters();
|
try
|
{
|
sql = @"select * from CSR_WorkRecord_Defect where defect_code=@defectcode";
|
dynamicParams.Add("@defectcode", defectcode);
|
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 TDefect where code=@defectcode";
|
list.Add(new { str = sql, parm = new { defectcode = defectcode } });
|
|
bool aa = DapperHelper.DoTransaction(list);
|
if (aa)
|
{
|
//写入操作记录表
|
LogHelper.DbOperateLog(us.usercode, "删除", "缺陷编码:" + defectcode, us.usertype);
|
mes.code = "200";
|
mes.count = 0;
|
mes.Message = "缺陷删除成功!";
|
mes.data = null;
|
}
|
else
|
{
|
mes.code = "300";
|
mes.count = 0;
|
mes.Message = "缺陷删除失败!";
|
mes.data = null;
|
}
|
}
|
catch (Exception e)
|
{
|
mes.code = "300";
|
mes.count = 0;
|
mes.Message = e.Message;
|
mes.data = null;
|
}
|
return mes;
|
}
|
#endregion
|
|
|
#region[工序检验项目列表查询]
|
public static ToMessage StepCheckItemSearch(string itemcode, string itemname, string itemdescr, int startNum, int endNum, string prop, string order)
|
{
|
var dynamicParams = new DynamicParameters();
|
string search = "";
|
try
|
{
|
if (itemcode != "" && itemcode != null)
|
{
|
search += "and A.code like '%'+@itemcode+'%' ";
|
dynamicParams.Add("@itemcode", itemcode);
|
}
|
if (itemname != "" && itemname != null)
|
{
|
search += "and A.name like '%'+@itemname+'%' ";
|
dynamicParams.Add("@itemname", itemname);
|
}
|
if (itemdescr != "" && itemdescr != null)
|
{
|
search += "and A.descr like '%'+@itemdescr+'%' ";
|
dynamicParams.Add("@itemdescr", itemdescr);
|
}
|
|
// --------------查询指定数据--------------
|
var total = 0; //总条数
|
var sql = @"select A.code,A.name,A.descr,U.username as lm_user,A.lm_date from TStepCheckItem A
|
left join TUser U on A.lm_user=U.usercode
|
where 1=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 AddUpdateStepCheckItem(string checkitemcode, string checkitemname, string checkitemdescr, string opertype, User us)
|
{
|
var sql = "";
|
var dynamicParams = new DynamicParameters();
|
List<object> list = new List<object>();
|
try
|
{
|
if (opertype == "Add")
|
{
|
//写入缺陷定义定义表
|
sql = @"insert into TStepCheckItem(code,name,descr,lm_user,lm_date)
|
values(@checkitemcode,@checkitemname,@checkitemdescr,@username,@CreateDate)";
|
list.Add(new
|
{
|
str = sql,
|
parm = new
|
{
|
checkitemcode = checkitemcode,
|
checkitemname = checkitemname,
|
checkitemdescr = checkitemdescr,
|
username = us.usercode,
|
CreateDate = DateTime.Now.ToString()
|
}
|
});
|
bool aa = DapperHelper.DoTransaction(list);
|
if (aa)
|
{
|
//写入操作记录表
|
LogHelper.DbOperateLog(us.usercode, "新增", "工序检验项目:" + checkitemcode, us.usertype);
|
mes.code = "200";
|
mes.count = 0;
|
mes.Message = "工序检验项目新增成功!";
|
mes.data = null;
|
}
|
else
|
{
|
mes.code = "300";
|
mes.count = 0;
|
mes.Message = "工序检验项目新增失败!";
|
mes.data = null;
|
}
|
}
|
if (opertype == "Update")
|
{
|
sql = @"update TStepCheckItem set name=@checkitemname,descr=@checkitemdescr,lm_user=@username,lm_date=@CreateDate where code=@checkitemcode";
|
list.Add(new
|
{
|
str = sql,
|
parm = new
|
{
|
checkitemcode = checkitemcode,
|
checkitemname = checkitemname,
|
checkitemdescr = checkitemdescr,
|
username = us.usercode,
|
CreateDate = DateTime.Now.ToString()
|
}
|
});
|
bool aa = DapperHelper.DoTransaction(list);
|
if (aa)
|
{
|
//写入操作记录表
|
LogHelper.DbOperateLog(us.usercode, "修改", "工序检验项目:" + checkitemcode, us.usertype);
|
mes.code = "200";
|
mes.count = 0;
|
mes.Message = "工序检验项目修改成功!";
|
mes.data = null;
|
}
|
else
|
{
|
mes.code = "300";
|
mes.count = 0;
|
mes.Message = "工序检验项目修改失败!";
|
mes.data = null;
|
}
|
}
|
}
|
catch (Exception e)
|
{
|
mes.code = "300";
|
mes.count = 0;
|
mes.Message = e.Message;
|
mes.data = null;
|
}
|
return mes;
|
}
|
#endregion
|
|
#region[工序检验项目删除]
|
public static ToMessage DeleteStepCheckItem(string checkitemcode, User us)
|
{
|
var sql = "";
|
List<object> list = new List<object>();
|
var dynamicParams = new DynamicParameters();
|
try
|
{
|
sql = @"select * from TStepCheckRecordSub where checkitem_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 TStepCheckItem where code=@checkitemcode";
|
list.Add(new { str = sql, parm = new { checkitemcode = checkitemcode } });
|
|
bool aa = DapperHelper.DoTransaction(list);
|
if (aa)
|
{
|
//写入操作记录表
|
LogHelper.DbOperateLog(us.usercode, "删除", "工序检验项目:" + checkitemcode, us.usertype);
|
mes.code = "200";
|
mes.count = 0;
|
mes.Message = "工序检验删除成功!";
|
mes.data = null;
|
}
|
else
|
{
|
mes.code = "300";
|
mes.count = 0;
|
mes.Message = "工序检验删除失败!";
|
mes.data = null;
|
}
|
}
|
catch (Exception e)
|
{
|
mes.code = "300";
|
mes.count = 0;
|
mes.Message = e.Message;
|
mes.data = null;
|
}
|
return mes;
|
}
|
#endregion
|
|
|
#region[工序质检方案列表查询]
|
public static ToMessage QualityInspectionSearch(string qualityinsptcode, string qualityinsptname, string status, string checktype, string sampltype, string suitobject, int startNum, int endNum, string prop, string order)
|
{
|
var dynamicParams = new DynamicParameters();
|
string search = "";
|
try
|
{
|
if (qualityinsptcode != "" && qualityinsptcode != null)
|
{
|
search += "and AA.code like '%'+@qualityinsptcode+'%' ";
|
dynamicParams.Add("@qualityinsptcode", qualityinsptcode);
|
}
|
if (qualityinsptname != "" && qualityinsptname != null)
|
{
|
search += "and AA.name like '%'+@qualityinsptname+'%' ";
|
dynamicParams.Add("@qualityinsptname", qualityinsptname);
|
}
|
if (status != "" && status != null)
|
{
|
search += "and AA.status=@status ";
|
dynamicParams.Add("@status", status);
|
}
|
if (checktype != "" && checktype != null)
|
{
|
search += "and AA.checktype=@checktype ";
|
dynamicParams.Add("@checktype", checktype);
|
}
|
if (sampltype != "" && sampltype != null)
|
{
|
search += "and AA.sampmethod=@sampltype ";
|
dynamicParams.Add("@sampltype", sampltype);
|
}
|
if (suitobject != "" && suitobject != null)
|
{
|
search += "and AA.suitobject=@suitobject ";
|
dynamicParams.Add("@suitobject", suitobject);
|
}
|
if (search == "")
|
{
|
search = "and 1=1 ";
|
}
|
search = search.Substring(3);//截取索引2后面的字符
|
// --------------查询指定数据--------------
|
var total = 0; //总条数
|
var sql = @"select * from(
|
select A.code,A.name,A.status,A.checktype,A.sampmethod,A.sampscare,A.suitobject,A.stepcode,S.stepname,A.descr,A.suitpart,A.lm_date,U.username,
|
case when A.suitobject='P' then
|
STUFF(( SELECT ';' + partname
|
FROM TMateriel_Info
|
where PATINDEX('%;' + RTRIM(TMateriel_Info.partcode) + ';%',';' + A.suitpart + ';')>0
|
FOR XML PATH('')), 1, 1,'')
|
when A.suitobject='PY'
|
then
|
STUFF(( SELECT ';' + name
|
FROM T_Dict
|
where PATINDEX('%;' + RTRIM(T_Dict.code) + ';%',';' + A.suitpart + ';')>0
|
FOR XML PATH('')), 1, 1,'')
|
end as suitpartname
|
from TStepCheckStandard A
|
left join TUser U on A.lm_user=U.usercode
|
left join TStep S on A.stepcode=S.stepcode
|
) as AA
|
where " + 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 QualityInspectionSeeEdit(string qualityinsptcode)
|
{
|
string sql = "";
|
var dynamicParams = new DynamicParameters();
|
try
|
{
|
sql = @"select A.stepcheckitem_seq,A.stepcheckitem_code,B.name as stepcheckitem_name,A.required,
|
A.numberjudge,A.unit,A.decimalnum,A.standvalue,A.uppervalue,A.lowervalue,A.stepcheckitem_desc
|
from TStepCheckStandardSub A
|
inner join TStepCheckItem B on A.stepcheckitem_code=B.code
|
where A.stepstaned_code=@qualityinsptcode";
|
dynamicParams.Add("@qualityinsptcode", qualityinsptcode);
|
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 QualityInspectionAddEditSave(string qualityinsptcode, string qualityinsptname, string status, string checktype, string sampmethod, string sampscare, string suitobject, string suitpart, string descr, string stepcode, string checkitem, string type, User us)
|
{
|
var sql = "";
|
List<object> list = new List<object>();
|
var dynamicParams = new DynamicParameters();
|
try
|
{
|
string date = DateTime.Now.ToString(); //获取系统时间
|
//获取质检列表检验项目数据
|
JArray arra = (JArray)Newtonsoft.Json.JsonConvert.DeserializeObject(checkitem);
|
list.Clear();
|
switch (type)
|
{
|
case "Add": //新增
|
//写入质检方案主表
|
sql = @"insert into TStepCheckStandard(code,name,status,checktype,sampmethod,sampscare,suitobject,suitpart,stepcode,descr,lm_user,lm_date)
|
values(@code,@name,@status,@checktype,@sampmethod,@sampscare,@suitobject,@suitpart,@stepcode,@descr,@lm_user,@lm_date)";
|
list.Add(new
|
{
|
str = sql,
|
parm = new
|
{
|
code = qualityinsptcode,
|
name = qualityinsptname,
|
status = status,
|
checktype = checktype,
|
sampmethod = sampmethod,
|
sampscare = sampscare,
|
suitobject = suitobject,
|
suitpart = suitpart,
|
stepcode = stepcode,
|
descr = descr,
|
lm_user = us.usercode,
|
lm_date = date
|
}
|
});
|
//写入质检方案子表
|
for (int i = 0; i < arra.Count; i++)
|
{
|
sql = @"insert into TStepCheckStandardSub(stepstaned_code,stepcheckitem_seq,stepcheckitem_code,required,numberjudge,unit,decimalnum,standvalue,uppervalue,lowervalue,stepcheckitem_desc,lm_user,lm_date)
|
values(@stepstaned_code,@stepcheckitem_seq,@stepcheckitem_code,@required,@numberjudge,@unit,@decimalnum,@standvalue,@uppervalue,@lowervalue,@stepcheckitem_desc,@lm_user,@lm_date)";
|
list.Add(new
|
{
|
str = sql,
|
parm = new
|
{
|
stepstaned_code = qualityinsptcode,
|
stepcheckitem_seq = Convert.ToInt32(arra[i]["stepcheckitem_seq"].ToString()),
|
stepcheckitem_code = arra[i]["stepcheckitem_code"].ToString(),
|
required = arra[i]["required"].ToString(),
|
numberjudge = arra[i]["numberjudge"].ToString(),
|
unit = arra[i]["unit"].ToString(),
|
decimalnum = arra[i]["decimalnum"].ToString(),
|
standvalue = arra[i]["standvalue"].ToString(),
|
uppervalue = arra[i]["uppervalue"].ToString(),
|
lowervalue = arra[i]["lowervalue"].ToString(),
|
stepcheckitem_desc = arra[i]["stepcheckitem_desc"].ToString(),
|
lm_user = us.usercode,
|
lm_date = date
|
}
|
});
|
}
|
break;
|
case "Update"://修改
|
//判断是否生成检验记录(入厂检验(InCheck) 出厂检验(OutCheck) FirstCheck（首检） PatroCheck(巡检) EndCheck(完工检))
|
sql = @"select * from TStepCheckRecord where checkstaned_code=@qualityinsptcode and check_type=@checktype";
|
dynamicParams.Add("@qualityinsptcode", qualityinsptcode);
|
dynamicParams.Add("@checktype", checktype);
|
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 from TStepCheckStandardSub where stepstaned_code=@code";
|
list.Add(new { str = sql, parm = new { code = qualityinsptcode } });
|
//修改质检方案主表
|
sql = @"update TStepCheckStandard set name=@name,status=@status,checktype=@checktype,sampmethod=@sampmethod,sampscare=@sampscare,
|
suitobject=@suitobject,suitpart=@suitpart,stepcode=@stepcode,descr=@descr
|
where code=@code";
|
list.Add(new
|
{
|
str = sql,
|
parm = new
|
{
|
code = qualityinsptcode,
|
name = qualityinsptname,
|
status = status,
|
checktype = checktype,
|
sampmethod = sampmethod,
|
sampscare = sampscare,
|
suitobject = suitobject,
|
suitpart = suitpart,
|
stepcode = stepcode,
|
descr = descr,
|
}
|
});
|
//新增质检方案子表
|
for (int i = 0; i < arra.Count; i++)
|
{
|
sql = @"insert into TStepCheckStandardSub(stepstaned_code,stepcheckitem_seq,stepcheckitem_code,required,numberjudge,unit,decimalnum,standvalue,uppervalue,lowervalue,stepcheckitem_desc,lm_user,lm_date)
|
values(@stepstaned_code,@stepcheckitem_seq,@stepcheckitem_code,@required,@numberjudge,@unit,@decimalnum,@standvalue,@uppervalue,@lowervalue,@stepcheckitem_desc,@lm_user,@lm_date)";
|
list.Add(new
|
{
|
str = sql,
|
parm = new
|
{
|
stepstaned_code = qualityinsptcode,
|
stepcheckitem_seq = Convert.ToInt32(arra[i]["stepcheckitem_seq"].ToString()),
|
stepcheckitem_code = arra[i]["stepcheckitem_code"].ToString(),
|
required = arra[i]["required"].ToString(),
|
numberjudge = arra[i]["numberjudge"].ToString(),
|
unit = arra[i]["unit"].ToString(),
|
decimalnum = arra[i]["decimalnum"].ToString(),
|
standvalue = arra[i]["standvalue"].ToString(),
|
uppervalue = arra[i]["uppervalue"].ToString(),
|
lowervalue = arra[i]["lowervalue"].ToString(),
|
stepcheckitem_desc = arra[i]["stepcheckitem_desc"].ToString(),
|
lm_user = us.usercode,
|
lm_date = date
|
}
|
});
|
}
|
break;
|
default:
|
break;
|
}
|
bool aa = DapperHelper.DoTransaction(list);
|
if (aa)
|
{
|
switch (type)
|
{
|
case "Add": //新增
|
//写入操作记录表
|
LogHelper.DbOperateLog(us.usercode, "新增", "工序质检方案:" + qualityinsptcode, us.usertype);
|
break;
|
case "Update": //修改
|
//写入操作记录表
|
LogHelper.DbOperateLog(us.usercode, "修改", "工序质检方案:" + qualityinsptcode, us.usertype);
|
break;
|
default:
|
break;
|
}
|
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 QualityInspectionDelete(string qualityinsptcode, User us)
|
{
|
var sql = "";
|
List<object> list = new List<object>();
|
var dynamicParams = new DynamicParameters();
|
try
|
{
|
//判断是否生成检验记录(入厂检验(InCheck) 出厂检验(OutCheck) FirstCheck（首检） PatroCheck(巡检) EndCheck(完工检))
|
sql = @"select * from TStepCheckRecord where checkstaned_code=@qualityinsptcode";
|
dynamicParams.Add("@qualityinsptcode", qualityinsptcode);
|
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 TStepCheckStandard where code=@code";
|
list.Add(new { str = sql, parm = new { code = qualityinsptcode } });
|
//删除质检方案子表
|
sql = @"delete TStepCheckStandardSub where stepstaned_code=@code";
|
list.Add(new { str = sql, parm = new { code = qualityinsptcode } });
|
|
bool aa = DapperHelper.DoTransaction(list);
|
if (aa)
|
{
|
//写入操作记录表
|
LogHelper.DbOperateLog(us.usercode, "删除", "质检方案:" + qualityinsptcode, us.usertype);
|
mes.code = "200";
|
mes.count = 0;
|
mes.Message = "质检方案删除成功!";
|
mes.data = null;
|
}
|
else
|
{
|
mes.code = "300";
|
mes.count = 0;
|
mes.Message = "质检方案删除失败!";
|
mes.data = null;
|
}
|
}
|
catch (Exception e)
|
{
|
mes.code = "300";
|
mes.count = 0;
|
mes.Message = e.Message;
|
mes.data = null;
|
}
|
return mes;
|
}
|
#endregion
|
|
|
#region[工序检验扫码获取任务信息]
|
public static ToMessage MesOrderStepCheckSearch(string orderstepqrcode, string checktype)
|
{
|
var sql = "";
|
string ordercode = "";
|
string stepcode = "";
|
var dynamicParams = new DynamicParameters();
|
Dictionary<string, object> list = new Dictionary<string, object>();
|
try
|
{
|
if (orderstepqrcode != "" && orderstepqrcode != null)
|
{
|
string[] arra = orderstepqrcode.Split(';');
|
if (arra.Length == 1) //工单号二维码
|
{
|
mes.code = "300";
|
mes.count = 0;
|
mes.Message = "请扫描工序条码!";
|
mes.data = null;
|
return mes;
|
}
|
if (arra.Length == 2) //工单号+工序号二维码
|
{
|
ordercode = arra[0]; //获取指定字符串前面的字符
|
stepcode = arra[1]; //获取指定字符串前面的字符
|
}
|
//通过扫描二维码信息查找任务信息
|
sql = @"select A.wo_code,A.good_qty,M.partcode,M.partname,M.partspec,M.stocktype_code,S.stepcode,S.stepname
|
from TK_Wrk_Step A
|
inner join TK_Wrk_Man P on A.wo_code=P.wo_code
|
left join TMateriel_Info M on P.materiel_code=M.partcode
|
left join TStep S on A.step_code=S.stepcode
|
where A.wo_code=@wo_code and A.step_code=@stepcode";
|
dynamicParams.Add("@wo_code", ordercode);
|
dynamicParams.Add("@stepcode", stepcode);
|
var data = DapperHelper.selectdata(sql, dynamicParams);
|
if (data.Rows.Count > 0)
|
{
|
if (decimal.Parse(data.Rows[0]["good_qty"].ToString()) > 0)
|
{
|
//获取绑定的质检方案、抽样方式、样本数
|
sql = @"select code as checkstandcode,name as checkstandname,sampmethod,sampscare
|
from TStepCheckStandard
|
where (PATINDEX('%,' + RTRIM(@partcode) + ',%',',' + suitpart + ',')>0 or PATINDEX('%,' + RTRIM(@stocktype_code) + ',%',',' + suitpart + ',')>0)
|
and checktype=@checktype and stepcode=@stepcode ";
|
dynamicParams.Add("@partcode", data.Rows[0]["partcode"].ToString());//物料编码
|
dynamicParams.Add("@stepcode", stepcode);//工序编码
|
dynamicParams.Add("@stocktype_code", data.Rows[0]["stocktype_code"].ToString());//存货类型编码
|
dynamicParams.Add("@checktype", checktype);//检验类型
|
var data1 = DapperHelper.selectdata(sql, dynamicParams);
|
if (data1.Rows.Count > 0)
|
{
|
list.Add("labcont", data);
|
list.Add("chekstand", data1);
|
mes.code = "200";
|
mes.count = 0;
|
mes.Message = "查询成功!";
|
mes.data = list;
|
}
|
else
|
{
|
mes.code = "300";
|
mes.count = 0;
|
mes.Message = "当前产品【" + data.Rows[0]["partname"].ToString() + "】,对应工序【" + data.Rows[0]["stepname"].ToString() + "】，未指定质检方案!";
|
mes.data = null;
|
return mes;
|
}
|
}
|
else
|
{
|
mes.code = "300";
|
mes.count = 0;
|
mes.Message = "当前工序任务无报工产出!";
|
mes.data = null;
|
return mes;
|
}
|
}
|
else
|
{
|
mes.code = "300";
|
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;
|
}
|
#endregion
|
|
#region[工序检验根据选择的检验方案查找检验项目]
|
public static ToMessage MesOrderStepCheckItemList(string checkstandcode)
|
{
|
string sql = "";
|
var dynamicParams = new DynamicParameters();
|
try
|
{
|
//质检方案编码获取检验项
|
sql = @"select A.stepcheckitem_seq, B.code,B.name,A.required,A.numberjudge,A.unit,A.decimalnum,
|
A.standvalue,A.uppervalue,A.lowervalue,A.stepcheckitem_desc
|
from TStepCheckStandardSub A
|
left join TStepCheckItem B on A.stepcheckitem_code=B.code
|
where A.stepstaned_code=@checkstandcode
|
order by A.stepcheckitem_seq";
|
dynamicParams.Add("@checkstandcode", checkstandcode);//质检方案编码
|
var data = DapperHelper.selectdata(sql, dynamicParams);
|
if (data.Rows.Count > 0)
|
{
|
mes.code = "200";
|
mes.count = 0;
|
mes.Message = "查询成功!";
|
mes.data = data;
|
}
|
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 SaveMesOrderStepCheckItem(string wocode, string partcode, string stepcode, string checkstandcode, string check_type, string sampmethod, string qualitystatus, string labqty, string sampleqty, string goodqty, string ngqty, string checkitemcont, User us)
|
{
|
var sql = "";
|
string checktypename = "";
|
List<object> list = new List<object>();
|
var dynamicParams = new DynamicParameters();
|
try
|
{
|
string datetime = DateTime.Now.ToString(); //获取系统时间
|
//获取检验项目信息
|
JArray arra = (JArray)Newtonsoft.Json.JsonConvert.DeserializeObject(checkitemcont);
|
list.Clear();
|
|
//写入检验记录主表
|
sql = @"insert into TStepCheckRecord(wo_code,partcode,step_code,checkstaned_code,check_user,check_type,check_typename,sampmethod,check_result,check_qty,good_qty,ng_qty,lm_user,lm_date)
|
values(@wo_code,@partcode,@step_code,@checkstaned_code,@check_user,@check_type,@check_typename,@sampmethod,@check_result,@check_qty,@good_qty,@ng_qty,@lm_user,@lm_date)";
|
switch (check_type)
|
{
|
case "FirstCheck":
|
checktypename = "首检";
|
break;
|
case "PatroCheck":
|
checktypename = "巡检";
|
break;
|
case "EndCheck":
|
checktypename = "完工检验";
|
break;
|
default:
|
break;
|
}
|
list.Add(new
|
{
|
str = sql,
|
parm = new
|
{
|
wo_code = wocode,
|
partcode = partcode,
|
step_code = stepcode,
|
checkstaned_code = checkstandcode,
|
check_user = us.usercode,
|
check_type = check_type,
|
check_typename = checktypename,
|
sampmethod = sampmethod,
|
check_result = qualitystatus,
|
check_qty = decimal.Parse(sampleqty),
|
good_qty = decimal.Parse(goodqty),
|
ng_qty = decimal.Parse(ngqty),
|
lm_user = us.usercode,
|
lm_date = datetime
|
}
|
});
|
//写入检验记录子表
|
for (int j = 0; j < arra.Count; j++)
|
{
|
sql = @"insert into TStepCheckRecordSub(m_id,checknum,checkitem_seq,checkitem_code,checkitem_name,checkitem_descr,check_value,check_result,lm_user,lm_date)
|
values(CONVERT(INT,IDENT_CURRENT('TStepCheckRecord')),@checknum,@checkiem_seq,@checkitem_code,@checkitem_name,@checkitem_descr,@check_value,@check_result,@lm_user,@lm_date)";
|
list.Add(new
|
{
|
str = sql,
|
parm = new
|
{
|
checknum = arra[j]["checknum"].ToString(),
|
checkiem_seq = arra[j]["checkiem_seq"].ToString(),
|
checkitem_code = arra[j]["checkitem_code"].ToString(),
|
checkitem_name = arra[j]["checkitem_name"].ToString(),
|
checkitem_descr = arra[j]["checkitem_descr"].ToString(),
|
check_value = arra[j]["check_value"].ToString(),
|
check_result = arra[j]["check_result"].ToString(),
|
lm_user = us.usercode,
|
lm_date = datetime
|
}
|
});
|
}
|
|
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 StepCheckTableSearch(string wocode, string partcode, string partname, string partapec, string stepname, string standname, string checktype, string checkresult, int startNum, int endNum, string prop, string order)
|
{
|
var dynamicParams = new DynamicParameters();
|
string search = "";
|
try
|
{
|
if (wocode != "" && wocode != null)
|
{
|
search += "and A.wo_code like '%'+@wocode+'%' ";
|
dynamicParams.Add("@wocode", wocode);
|
}
|
if (partcode != "" && partcode != null)
|
{
|
search += "and A.partcode like '%'+@partcode+'%' ";
|
dynamicParams.Add("@partcode", partcode);
|
}
|
if (partname != "" && partname != null)
|
{
|
search += "and B.partname like '%'+@partname+'%' ";
|
dynamicParams.Add("@partname", partname);
|
}
|
if (partapec != "" && partapec != null)
|
{
|
search += "and B.partspec like '%'+@partapec+'%' ";
|
dynamicParams.Add("@partapec", partapec);
|
}
|
if (stepname != "" && stepname != null)
|
{
|
search += "and S.stepname like '%'+@stepname+'%' ";
|
dynamicParams.Add("@stepname", stepname);
|
}
|
if (standname != "" && standname != null)
|
{
|
search += "and T.name like '%'+@standname+'%' ";
|
dynamicParams.Add("@standname", standname);
|
}
|
if (checktype != "" && checktype != null)
|
{
|
search += "and A.check_type=@checktype ";
|
dynamicParams.Add("@checktype", checktype);
|
}
|
if (checkresult != "" && checkresult != null)
|
{
|
search += "and A.check_result=@checkresult ";
|
dynamicParams.Add("@checkresult", checkresult);
|
}
|
// --------------查询指定数据--------------
|
var total = 0; //总条数(首检、巡检、完工检验)
|
var sql = @"select A.id,A.wo_code,A.partcode,B.partname,B.partspec,A.step_code,S.stepname,A.checkstaned_code,T.name as checkstaned_name,A.check_user,
|
A.check_type,A.check_result,A.check_descr,A.check_qty,U.username as lm_user,A.lm_date
|
from TStepCheckRecord A
|
left join TMateriel_Info B on A.partcode=B.partcode
|
left join TStep S on A.step_code=S.stepcode
|
left join TStepCheckStandard T on A.checkstaned_code=T.code
|
left join TUser U on A.check_user=U.usercode
|
where A.check_type in('FirstCheck','PatroCheck','EndCheck') " + 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 StepCheckTableSubSearch(string id)
|
{
|
string sql = "";
|
var dynamicParams = new DynamicParameters();
|
try
|
{
|
//存储过程名
|
sql = @"h_p_IFCLD_WuLiuCheckSubSelect";
|
dynamicParams.Add("@checkid", id);
|
DataTable dt = DapperHelper.selectProcedure(sql, dynamicParams);
|
if (dt.Rows.Count > 0)
|
{
|
mes.code = "200";
|
mes.count = 0;
|
mes.Message = "查询成功!";
|
mes.data = dt;
|
}
|
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 StepCheckTableOutExcel(string wocode, string partcode, string partname, string partapec, string stepname, string standname, string checktype, string checkresult, string prop, string order)
|
{
|
var dynamicParams = new DynamicParameters();
|
string search = "";
|
try
|
{
|
if (wocode != "" && wocode != null)
|
{
|
search += "and A.wo_code like '%'+@wocode+'%' ";
|
dynamicParams.Add("@wocode", wocode);
|
}
|
if (partcode != "" && partcode != null)
|
{
|
search += "and A.partcode like '%'+@stanedname+'%' ";
|
dynamicParams.Add("@partcode", partcode);
|
}
|
if (partname != "" && partname != null)
|
{
|
search += "and B.partname like '%'+@partname+'%' ";
|
dynamicParams.Add("@partname", partname);
|
}
|
if (partapec != "" && partapec != null)
|
{
|
search += "and B.partspec like '%'+@partapec+'%' ";
|
dynamicParams.Add("@partapec", partapec);
|
}
|
if (stepname != "" && stepname != null)
|
{
|
search += "and S.stepname like '%'+@stepname+'%' ";
|
dynamicParams.Add("@stepname", stepname);
|
}
|
if (standname != "" && standname != null)
|
{
|
search += "and T.name like '%'+@standname+'%' ";
|
dynamicParams.Add("@standname", standname);
|
}
|
if (checktype != "" && checktype != null)
|
{
|
search += "and A.check_type=@checktype ";
|
dynamicParams.Add("@checktype", checktype);
|
}
|
if (checkresult != "" && checkresult != null)
|
{
|
search += "and A.check_result=@checkresult ";
|
dynamicParams.Add("@checkresult", checkresult);
|
}
|
// --------------查询指定数据--------------
|
var total = 0; //总条数
|
var sql = @"select
|
CASE WHEN
|
ROW_NUMBER() OVER (PARTITION BY A.id ORDER BY B.id) = 1 THEN A.wo_code
|
ELSE ''END AS '工单号',
|
CASE WHEN
|
ROW_NUMBER() OVER (PARTITION BY A.id ORDER BY B.id) = 1 THEN M.partcode
|
ELSE ''END AS '产品编码',
|
CASE WHEN
|
ROW_NUMBER() OVER (PARTITION BY A.id ORDER BY B.id) = 1 THEN M.partname
|
ELSE ''END AS '产品名称',
|
CASE WHEN
|
ROW_NUMBER() OVER (PARTITION BY A.id ORDER BY B.id) = 1 THEN M.partspec
|
ELSE ''END AS '规格型号',
|
CASE WHEN
|
ROW_NUMBER() OVER (PARTITION BY A.id ORDER BY B.id) = 1 THEN S.stepname
|
ELSE ''END AS '工序名称',
|
CASE WHEN
|
ROW_NUMBER() OVER (PARTITION BY A.id ORDER BY B.id) = 1 THEN A.checkstaned_code
|
ELSE ''END AS '标准编码',
|
CASE WHEN
|
ROW_NUMBER() OVER (PARTITION BY A.id ORDER BY B.id) = 1 THEN T.name
|
ELSE ''END AS '标准名称',
|
CASE WHEN
|
ROW_NUMBER() OVER (PARTITION BY A.id ORDER BY B.id) = 1 THEN U.username
|
ELSE ''END AS '检验人员',
|
CASE WHEN
|
ROW_NUMBER() OVER (PARTITION BY A.id ORDER BY B.id) = 1 THEN A.check_typename
|
ELSE ''END AS '检验类型',
|
CASE WHEN
|
ROW_NUMBER() OVER (PARTITION BY A.id ORDER BY B.id) = 1 THEN A.check_result
|
ELSE ''END AS '检验结果',
|
CASE WHEN
|
ROW_NUMBER() OVER (PARTITION BY A.id ORDER BY B.id) = 1 THEN A.check_descr
|
ELSE ''END AS '检验描述',
|
CASE WHEN
|
ROW_NUMBER() OVER (PARTITION BY A.id ORDER BY B.id) = 1 THEN A.check_qty
|
END AS '样本数量',
|
CASE WHEN
|
ROW_NUMBER() OVER (PARTITION BY A.id ORDER BY B.id) = 1 THEN A.lm_date
|
END AS '检验时间',
|
B.checknum as 检验件次,B.checkitem_seq as '检验项目序号',B.checkitem_code '检验项目编码',B.checkitem_name '检验项目名称',B.checkitem_descr '检验项目描述',B.check_result '检验项目结果',
|
C.standvalue as '标准值',C.uppervalue as '上限值',C.lowervalue as '下限值',B.check_value as '实测值'
|
from TStepCheckRecord A
|
left join TStepCheckRecordSub B on A.id=B.m_id
|
left join TMateriel_Info M on A.partcode=M.partcode
|
left join TStep S on A.step_code=S.stepcode
|
left join TStepCheckStandard T on A.checkstaned_code=T.code
|
left join TStepCheckStandardSub C on A.checkstaned_code=C.stepstaned_code and B.checkitem_code=C.stepcheckitem_code
|
left join TUser U on A.check_user=U.usercode
|
where A.check_type in('FirstCheck','PatroCheck','EndCheck') " + search;
|
DataTable data = DapperHelper.selectdata(sql, dynamicParams);
|
data.TableName = "Table"; //设置DataTable的名称
|
string msg = DownLoad.DataTableToExcel(data, "工序检验记录");
|
mes.code = "200";
|
mes.Message = "查询成功!";
|
mes.count = total;
|
mes.data = msg;
|
}
|
catch (Exception e)
|
{
|
mes.code = "300";
|
mes.count = 0;
|
mes.Message = e.Message;
|
mes.data = null;
|
}
|
return mes;
|
}
|
#endregion
|
}
|
}
|
