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 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.descr like '%'+@defectdescr+'%' ";
|
dynamicParams.Add("@defectdescr", defectdescr);
|
}
|
|
// --------------查询指定数据--------------
|
var total = 0; //总条数
|
var sql = @"select A.code,A.name,A.descr,U.username as lm_user,A.lm_date from TDefect 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 AddUpdateDedect(string defectcode, string defectname, string defectdescr, string opertype, string username)
|
{
|
var sql = "";
|
var dynamicParams = new DynamicParameters();
|
List<object> list = new List<object>();
|
try
|
{
|
if (opertype == "Add")
|
{
|
//写入缺陷定义定义表
|
sql = @"insert into TDefect(code,name,descr,lm_user,lm_date)
|
values(@defectcode,@defectname,@defectdescr,@username,@CreateDate)";
|
list.Add(new
|
{
|
str = sql,
|
parm = new
|
{
|
defectcode = defectcode,
|
defectname = defectname,
|
defectdescr = defectdescr,
|
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;
|
}
|
}
|
if (opertype == "Update")
|
{
|
sql = @"update TDefect set name=@defectname,descr=@defectdescr,lm_user=@username,lm_date=@CreateDate where code=@defectcode";
|
list.Add(new
|
{
|
str = sql,
|
parm = new
|
{
|
defectcode = defectcode,
|
defectname = defectname,
|
defectdescr = defectdescr,
|
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 DeleteDedect(string defectcode)
|
{
|
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)
|
{
|
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 StepCheckStanedSearch(string stanedcode, string stanedname, string staneddescr, int startNum, int endNum, string prop, string order)
|
{
|
var dynamicParams = new DynamicParameters();
|
string search = "";
|
try
|
{
|
if (stanedcode != "" && stanedcode != null)
|
{
|
search += "and A.code like '%'+@stanedcode+'%' ";
|
dynamicParams.Add("@stanedcode", stanedcode);
|
}
|
if (stanedname != "" && stanedname != null)
|
{
|
search += "and A.name like '%'+@stanedname+'%' ";
|
dynamicParams.Add("@stanedname", stanedname);
|
}
|
if (staneddescr != "" && staneddescr != null)
|
{
|
search += "and A.descr like '%'+@staneddescr+'%' ";
|
dynamicParams.Add("@staneddescr", staneddescr);
|
}
|
|
// --------------查询指定数据--------------
|
var total = 0; //总条数
|
var sql = @"select A.code,A.name,A.descr,U.username as lm_user,A.lm_date from TStepCheckStandard 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 EditStepCheckStanedSearch(string defectcode)
|
{
|
string sql = "";
|
var dynamicParams = new DynamicParameters();
|
try
|
{
|
//获取工序检验标准信息
|
sql = @"select code,name,descr
|
from TStepCheckStandard
|
where code=@defectcode and is_delete<>'1'";
|
dynamicParams.Add("@defectcode", defectcode);
|
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]["DESCR"].ToString();
|
}
|
else
|
{
|
mes.code = "300";
|
mes.count = 0;
|
mes.Message = "无工序检验标准信息!";
|
mes.data = null;
|
return mes;
|
}
|
//根据工序检验标准编码获取关联的工序检验项目信息
|
sql = @"select B.code,B.name,A.stepcheckitem_desc from TStepCheckStandardSub A
|
left join TStepCheckItem B on A.stepcheckitem_code=B.code
|
where B.is_delete<>'1' and A.stepstaned_code=@stepstaned_code order by A.stepcheckitem_seq asc";
|
dynamicParams.Add("@stepstaned_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 StepCheckItemSelect()
|
{
|
string sql = "";
|
try
|
{
|
//获取工序点检项目数据
|
sql = @"select code,name,descr from TStepCheckItem 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 DeleteStepCheckStaned(string stanedcode)
|
{
|
var sql = "";
|
List<object> list = new List<object>();
|
var dynamicParams = new DynamicParameters();
|
try
|
{
|
sql = @"select * from TStepCheckRecord where checkstaned_code=@stanedcode";
|
dynamicParams.Add("@stanedcode", stanedcode);
|
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=@stanedcode";
|
list.Add(new { str = sql, parm = new { stanedcode = stanedcode } });
|
//删除工序检验标准项目子表
|
sql = @"delete TStepCheckStandardSub where stepstaned_code=@stanedcode";
|
list.Add(new { str = sql, parm = new { stanedcode = stanedcode } });
|
|
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 AddUpdateStepCheckStaned(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 TStepCheckStandard(code,name,descr,lm_user,lm_date) values(@code,@name,@descr,@lm_user,@lm_date)";
|
list.Add(new { str = sql, parm = new { code = json.code, name = json.name, descr = json.description, lm_user = username, lm_date = DateTime.Now.ToString() } });
|
for (int i = 0; i < json.Data.Rows.Count; i++)
|
{
|
//新增工序检验标准子表
|
sql = @"insert TStepCheckStandardSub (stepstaned_code,stepcheckitem_seq,stepcheckitem_code,stepcheckitem_desc,lm_user,lm_date)
|
values(@stepstaned_code,@stepcheckitem_seq,@stepcheckitem_code,@stepcheckitem_desc,@lm_user,@lm_date)";
|
list.Add(new { str = sql, parm = new { stepstaned_code = json.code, stepcheckitem_seq = Convert.ToInt32(json.Data.Rows[i]["STEPCHECKITEM_SEQ"].ToString()), stepcheckitem_code = json.Data.Rows[i]["STEPCHECKITEM_CODE"].ToString(), stepcheckitem_desc= json.Data.Rows[i]["STEPCHECKITEM_DESC"].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 TStepCheckStandardSub where stepstaned_code=@stepstaned_code";
|
list.Add(new { str = sql, parm = new { stepstaned_code = json.code} });
|
//修改工序检验标准主表
|
sql = @"update TStepCheckStandard set name=@stepstaned_name,descr=@stepstaned_desc where code=@stepstaned_code";
|
list.Add(new { str = sql, parm = new { stepstaned_code=json.code, stepstaned_name = json.name, stepstaned_desc=json.description } });
|
//新增工序检验标准子表
|
for (int i = 0; i < json.Data.Rows.Count; i++)
|
{
|
//新增工序检验标准子表
|
sql = @"insert TStepCheckStandardSub (stepstaned_code,stepcheckitem_seq,stepcheckitem_code,stepcheckitem_desc,lm_user,lm_date)
|
values(@stepstaned_code,@stepcheckitem_seq,@stepcheckitem_code,@stepcheckitem_desc,@lm_user,@lm_date)";
|
list.Add(new { str = sql, parm = new { stepstaned_code = json.code, stepcheckitem_seq = Convert.ToInt32(json.Data.Rows[i]["STEPCHECKITEM_SEQ"].ToString()), stepcheckitem_code = json.Data.Rows[i]["STEPCHECKITEM_CODE"].ToString(), stepcheckitem_desc = json.Data.Rows[i]["STEPCHECKITEM_DESC"].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 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 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 AddUpdateStepCheckItem(string checkitemcode, string checkitemname, string checkitemdescr, string opertype, string username)
|
{
|
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 = 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;
|
}
|
}
|
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 = 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 DeleteStepCheckItem(string checkitemcode)
|
{
|
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 = @"update TStepCheckItem set is_delete='1' 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 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 '%'+@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);
|
}
|
if (search == "")
|
{
|
search = "and 1=1 ";
|
}
|
search = search.Substring(3);//截取索引2后面的字符
|
// --------------查询指定数据--------------
|
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.lm_user=U.usercode
|
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 StepCheckTableSubSearch(string id)
|
{
|
string sql = "";
|
var dynamicParams = new DynamicParameters();
|
try
|
{
|
sql = @"select A.checkitem_seq,A.checkitem_code,A.checkitem_name,A.checkitem_descr,A.check_result,U.username as lm_user,A.lm_date
|
from TStepCheckRecordSub A
|
left join TUser U on A.lm_user=U.usercode
|
where A.m_id=@id";
|
dynamicParams.Add("@id", id);
|
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 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);
|
}
|
if (search == "")
|
{
|
search = "and 1=1 ";
|
}
|
search = search.Substring(3);//截取索引2后面的字符
|
// --------------查询指定数据--------------
|
var total = 0; //总条数
|
var sql = @"select
|
CASE WHEN
|
ROW_NUMBER() OVER (PARTITION BY A.wo_code ORDER BY B.checkitem_code) = 1 THEN A.wo_code
|
ELSE ''END AS '工单号',
|
CASE WHEN
|
ROW_NUMBER() OVER (PARTITION BY A.partcode ORDER BY B.checkitem_code) = 1 THEN M.partcode
|
ELSE ''END AS '产品编码',
|
CASE WHEN
|
ROW_NUMBER() OVER (PARTITION BY A.partcode ORDER BY B.checkitem_code) = 1 THEN M.partname
|
ELSE ''END AS '产品名称',
|
CASE WHEN
|
ROW_NUMBER() OVER (PARTITION BY A.partcode ORDER BY B.checkitem_code) = 1 THEN M.partspec
|
ELSE ''END AS '规格型号',
|
CASE WHEN
|
ROW_NUMBER() OVER (PARTITION BY A.step_code ORDER BY B.checkitem_code) = 1 THEN S.stepname
|
ELSE ''END AS '工序名称',
|
CASE WHEN
|
ROW_NUMBER() OVER (PARTITION BY A.checkstaned_code ORDER BY B.checkitem_code) = 1 THEN A.checkstaned_code
|
ELSE ''END AS '标准编码',
|
CASE WHEN
|
ROW_NUMBER() OVER (PARTITION BY A.checkstaned_code ORDER BY B.checkitem_code) = 1 THEN T.name
|
ELSE ''END AS '标准名称',
|
CASE WHEN
|
ROW_NUMBER() OVER (PARTITION BY A.check_user ORDER BY B.checkitem_code) = 1 THEN U.username
|
ELSE ''END AS '检验人员',
|
CASE WHEN
|
ROW_NUMBER() OVER (PARTITION BY A.check_typename ORDER BY B.checkitem_code) = 1 THEN A.check_typename
|
ELSE ''END AS '检验类型',
|
CASE WHEN
|
ROW_NUMBER() OVER (PARTITION BY A.check_result ORDER BY B.checkitem_code) = 1 THEN A.check_result
|
ELSE ''END AS '检验结果',
|
CASE WHEN
|
ROW_NUMBER() OVER (PARTITION BY A.check_descr ORDER BY B.checkitem_code) = 1 THEN A.check_descr
|
ELSE ''END AS '检验描述',
|
CASE WHEN
|
ROW_NUMBER() OVER (PARTITION BY A.check_qty ORDER BY B.checkitem_code) = 1 THEN A.check_qty
|
END AS '检验数量',
|
CASE WHEN
|
ROW_NUMBER() OVER (PARTITION BY A.lm_date ORDER BY B.checkitem_code) = 1 THEN A.lm_date
|
END AS '检验时间',
|
B.checkitem_seq as '检验项目序号',B.checkitem_code '检验项目编码',B.checkitem_name '检验项目名称',B.checkitem_descr '检验项目描述',B.check_result '检验项目结果'
|
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 TUser U on A.check_user=U.usercode
|
where " + 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
|
}
|
}
|
