From e691f2ecede6a962dfa76bb4a494ac1c80c9a389 Mon Sep 17 00:00:00 2001
From: yl <ykxkd@outlook.com>
Date: 星期六, 10 九月 2022 00:05:58 +0800
Subject: [PATCH] 设备点检记录导出接口去掉分页参数，设备保养记录查询、明细查询、导出(接口开发)
---
VueWebApi/DLL/DAL/DeviceManagerDAL.cs | 517 +++++++++++++++++++++++++++++++++++++++++++++++++++-----
1 files changed, 465 insertions(+), 52 deletions(-)
diff --git a/VueWebApi/DLL/DAL/DeviceManagerDAL.cs b/VueWebApi/DLL/DAL/DeviceManagerDAL.cs
index b4df942..89c0d93 100644
--- a/VueWebApi/DLL/DAL/DeviceManagerDAL.cs
+++ b/VueWebApi/DLL/DAL/DeviceManagerDAL.cs
@@ -663,27 +663,27 @@
{
if (checkitemcode != "" && checkitemcode != null)
{
- search += "and code like '%'+@checkitemcode+'%' ";
+ search += "and A.code like '%'+@checkitemcode+'%' ";
dynamicParams.Add("@checkitemcode", checkitemcode);
}
if (checkitemname != "" && checkitemname != null)
{
- search += "and name like '%'+@checkitemname+'%' ";
+ search += "and A.name like '%'+@checkitemname+'%' ";
dynamicParams.Add("@checkitemname", checkitemname);
}
if (checkdescr != "" && checkdescr != null)
{
- search += "and description=@checkdescr ";
+ search += "and A.description like '%'+@checkdescr+'%' ";
dynamicParams.Add("@checkdescr", checkdescr);
}
if (isqrcode != "" && isqrcode != null)
{
- search += "and isscan=@isqrcode ";
+ search += "and A.isscan=@isqrcode ";
dynamicParams.Add("@isqrcode", isqrcode);
}
if (cycle != "" && cycle != null)
{
- search += "and isscan=@cycle ";
+ search += "and A.cycle=@cycle ";
dynamicParams.Add("@cycle", cycle);
}
if (search == "")
@@ -693,8 +693,10 @@
//search = search.Substring(3);//鎴彇绱㈠紩2鍚庨潰鐨勫瓧绗�
// --------------鏌ヨ鎸囧畾鏁版嵁--------------
var total = 0; //鎬绘潯鏁�
- var sql = @"select id,code,name,description,isscan,cycle,lm_user,lm_date
- from TEqpchk_Item where is_delete<>'1' " + search;
+ 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 = "鏌ヨ鎴愬姛!";
@@ -842,7 +844,7 @@
#region[璁惧淇濆吇椤瑰垪琛ㄦ煡璇
- public static ToMessage DeviceMaiItemSearch(string maiitemcode, string maiitemname, string maidescr, string isqrcode, string cycle, int startNum, int endNum, string prop, string order)
+ 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 = "";
@@ -850,28 +852,23 @@
{
if (maiitemcode != "" && maiitemcode != null)
{
- search += "and code like '%'+@maiitemcode+'%' ";
+ search += "and A.code like '%'+@maiitemcode+'%' ";
dynamicParams.Add("@maiitemcode", maiitemcode);
}
if (maiitemname != "" && maiitemname != null)
{
- search += "and name like '%'+@maiitemname+'%' ";
+ search += "and A.name like '%'+@maiitemname+'%' ";
dynamicParams.Add("@maiitemname", maiitemname);
}
if (maidescr != "" && maidescr != null)
{
- search += "and description=@maidescr ";
+ search += "and A.description like '%'+@maidescr+'%' ";
dynamicParams.Add("@maidescr", maidescr);
}
if (isqrcode != "" && isqrcode != null)
{
- search += "and isscan=@isqrcode ";
+ search += "and A.isscan=@isqrcode ";
dynamicParams.Add("@isqrcode", isqrcode);
- }
- if (cycle != "" && cycle != null)
- {
- search += "and isscan=@cycle ";
- dynamicParams.Add("@cycle", cycle);
}
if (search == "")
{
@@ -880,8 +877,10 @@
//search = search.Substring(3);//鎴彇绱㈠紩2鍚庨潰鐨勫瓧绗�
// --------------鏌ヨ鎸囧畾鏁版嵁--------------
var total = 0; //鎬绘潯鏁�
- var sql = @"select id,code,name,description,isscan,cycle,lm_user,lm_date
- from TEqpmai_Item where is_delete<>'1' " + search;
+ 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 = "鏌ヨ鎴愬姛!";
@@ -900,20 +899,19 @@
#endregion
#region[璁惧淇濆吇椤规柊澧炵紪杈慮
- public static ToMessage AddUpdateDeviceMaiItem(string maiitemid, string maiitemcode, string maiitemname, string maiitemdescr, string cycle, string isqrcode, string usercode, string operType)
+ 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,cycle,lm_user,lm_date)
- values(@maiitemcode,@maiitemname,@checkitemdescr,@isqrcode,@cycle,@usercode,@CreateDate)";
+ 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("@cycle", cycle);
dynamicParams.Add("@usercode", usercode);
dynamicParams.Add("@CreateDate", DateTime.Now.ToString());
int cont = DapperHelper.SQL(sql, dynamicParams);
@@ -934,14 +932,13 @@
}
if (operType == "Update")
{
- var sql = @"update TEqpmai_Item set name=@maiitemname,description=@maiitemdescr,isscan=@isqrcode,cycle=@cycle,
+ 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("@cycle", cycle);
dynamicParams.Add("@usercode", usercode);
dynamicParams.Add("@CreateDate", DateTime.Now.ToString());
int cont = DapperHelper.SQL(sql, dynamicParams);
@@ -1037,17 +1034,17 @@
{
if (checkstandcode != "" && checkstandcode != null)
{
- search += "and code like '%'+@checkstandcode+'%' ";
+ search += "and A.code like '%'+@checkstandcode+'%' ";
dynamicParams.Add("@checkstandcode", checkstandcode);
}
if (checkstandname != "" && checkstandname != null)
{
- search += "and name like '%'+@checkstandname+'%' ";
+ search += "and A.name like '%'+@checkstandname+'%' ";
dynamicParams.Add("@checkstandname", checkstandname);
}
if (checkcontr != "" && checkcontr != null)
{
- search += "and iscontr=@checkcontr ";
+ search += "and A.iscontr=@checkcontr ";
dynamicParams.Add("@checkcontr", checkcontr);
}
if (search == "")
@@ -1057,8 +1054,10 @@
//search = search.Substring(3);//鎴彇绱㈠紩2鍚庨潰鐨勫瓧绗�
// --------------鏌ヨ鎸囧畾鏁版嵁--------------
var total = 0; //鎬绘潯鏁�
- var sql = @"select id,code,name,description,iscontr,is_checkeqp,lm_user,lm_date
- from TEqpchk_Main where is_delete<>'1' " + search;
+ 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 = "鏌ヨ鎴愬姛!";
@@ -1210,7 +1209,7 @@
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 } });
+ 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++)
{
@@ -1350,7 +1349,7 @@
// 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,(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
+ 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
@@ -1473,17 +1472,17 @@
{
if (repairstandcode != "" && repairstandcode != null)
{
- search += "and code like '%'+@repairstandcode+'%' ";
+ search += "and A.code like '%'+@repairstandcode+'%' ";
dynamicParams.Add("@repairstandcode", repairstandcode);
}
if (repairstandname != "" && repairstandname != null)
{
- search += "and name like '%'+@repairstandname+'%' ";
+ search += "and A.name like '%'+@repairstandname+'%' ";
dynamicParams.Add("@repairstandname", repairstandname);
}
if (repairstanddescr != "" && repairstanddescr != null)
{
- search += "and description like '%'+@repairstanddescr+'%' ";
+ search += "and A.description like '%'+@repairstanddescr+'%' ";
dynamicParams.Add("@repairstanddescr", repairstanddescr);
}
@@ -1494,8 +1493,10 @@
//search = search.Substring(3);//鎴彇绱㈠紩2鍚庨潰鐨勫瓧绗�
// --------------鏌ヨ鎸囧畾鏁版嵁--------------
var total = 0; //鎬绘潯鏁�
- var sql = @"select id,code,name,description,is_repaireqp,lm_user,lm_date
- from TEqpmai_Main where is_delete<>'1' " + search;
+ 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 = "鏌ヨ鎴愬姛!";
@@ -1545,16 +1546,17 @@
try
{
//鑾峰彇璁惧淇濆吇鏍囧噯淇℃伅
- sql = @"select code,name,description
+ 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);
- RoutEdit rout = new RoutEdit();
+ 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
@@ -1566,7 +1568,7 @@
return mes;
}
//鏍规嵁璁惧淇濆吇鏍囧噯缂栫爜鑾峰彇鍏宠仈鐨勪繚鍏婚」鐩俊鎭�
- sql = @"select B.code,B.name,A.chkdesc,A.isscan,A.cycle from TEqpmai_Deta A
+ 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);
@@ -1589,7 +1591,7 @@
#endregion
#region[璁惧淇濆吇鏍囧噯鏂板缂栬緫]
- public static ToMessage AddUpdateDeviceRepairStandArd(string opertype, RoutEdit json, string username)
+ public static ToMessage AddUpdateDeviceRepairStandArd(string opertype, EqpRepair json, string username)
{
var sql = "";
var dynamicParams = new DynamicParameters();
@@ -1599,13 +1601,13 @@
if (opertype == "Add")
{
//鏂板璁惧淇濆吇鏍囧噯涓昏〃
- sql = @"insert into TEqpmai_Main(code,name,description,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() } });
+ 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,cycle,chk_desc,isscan,lm_user,lm_date)
- values(@repairitem_seq,@repairitem_code,@repairitem_name,@code,@cycle,@repairdesc,@isscan,@lm_user,@lm_date)";
+ 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,
@@ -1615,7 +1617,6 @@
repairitem_code = json.Data.Rows[i]["REPAIRITEM_CODE"].ToString(),
repairitem_name = json.Data.Rows[i]["REPAIRITEM_NAME"].ToString(),
code = json.code,
- cycle = json.Data.Rows[i]["CYCLE"].ToString(),
repairdesc = json.Data.Rows[i]["REPAIRITEM_DESCR"].ToString(),
isscan = json.Data.Rows[i]["ISSCAN"].ToString(),
lm_user = username,
@@ -1645,13 +1646,13 @@
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 where code=@repairstaned_code";
- list.Add(new { str = sql, parm = new { repairstaned_code = json.code, repairstaned_name = json.name, repairstaned_desc = json.description } });
+ 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,cycle,chk_desc,isscan,lm_user,lm_date)
- values(@repairitem_seq,@repairitem_code,@repairitem_name,@code,@cycle,@repairdesc,@isscan,@lm_user,@lm_date)";
+ 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,
@@ -1661,7 +1662,6 @@
repairitem_code = json.Data.Rows[i]["REPAIRITEM_CODE"].ToString(),
repairitem_name = json.Data.Rows[i]["REPAIRITEM_NAME"].ToString(),
code = json.code,
- cycle = json.Data.Rows[i]["CYCLE"].ToString(),
repairdesc = json.Data.Rows[i]["REPAIRITEM_DESCR"].ToString(),
isscan = json.Data.Rows[i]["ISSCAN"].ToString(),
lm_user = username,
@@ -1787,7 +1787,7 @@
// 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,(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
+ 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
@@ -1898,5 +1898,418 @@
return mes;
}
#endregion
+
+
+
+ #region[璁惧鐐规璁板綍鏌ヨ]
+ public static ToMessage DeviceCheckTakeSearch(string wkshopcode, string eqpcode, string eqpname, string stanedname, string checkuser, string checkopendate, string checkclosedate, int startNum, int endNum, string prop, string order)
+ {
+ var dynamicParams = new DynamicParameters();
+ string search = "";
+ try
+ {
+ if (wkshopcode != "" && wkshopcode != null)
+ {
+ search += "and T.org_code=@wkshopcode ";
+ dynamicParams.Add("@wkshopcode", wkshopcode);
+ }
+ if (eqpcode != "" && eqpcode != null)
+ {
+ search += "and A.eqp_code like '%'+@eqpcode+'%' ";
+ dynamicParams.Add("@eqpcode", eqpcode);
+ }
+ if (eqpname != "" && eqpname != null)
+ {
+ search += "and E.name like '%'+@eqpname+'%' ";
+ dynamicParams.Add("@eqpname", eqpname);
+ }
+ if (stanedname != "" && stanedname != null)
+ {
+ search += "and M.name like '%'+@stanedname+'%' ";
+ dynamicParams.Add("@stanedname", stanedname);
+ }
+ if (checkuser != "" && checkuser != null)
+ {
+ search += "and A.chk_user like '%'+@checkuser+'%' ";
+ dynamicParams.Add("@checkuser", checkuser);
+ }
+ if (checkopendate != "" && checkopendate != null)
+ {
+ search += "and A.chk_date between @checkopendate and @checkclosedate ";
+ dynamicParams.Add("@checkopendate", checkopendate + " 00:00:00");
+ dynamicParams.Add("@checkclosedate", checkclosedate + " 23:59:59");
+ }
+ if (search == "")
+ {
+ search = "and 1=1 ";
+ }
+ //search = search.Substring(3);//鎴彇绱㈠紩2鍚庨潰鐨勫瓧绗�
+ // --------------鏌ヨ鎸囧畾鏁版嵁--------------
+ var total = 0; //鎬绘潯鏁�
+ var sql = @"select A.djwo,T.org_code,T.org_name,A.eqp_code,E.name as eqp_name,M.code as stanedcode,M.name as stanedname,
+ A.chk_user,A.chk_result,A.chk_date
+ from TEqpchk_Proc_Main A
+ left join TEqpInfo E on A.eqp_code=E.code
+ left join TEqpchk_Main M on A.eqpchkmain_code=M.code
+ left join TOrganization T on E.wksp_code=T.org_code
+ where T.description='W' " + 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 DeviceCheckSubTakeSearch(string djwo)
+ {
+ string sql = "";
+ var dynamicParams = new DynamicParameters();
+ try
+ {
+ sql = @"select A.seq,T.code as itemcode,T.name as itemname,A.result,A.chk_value,A.remark
+ from TEqpchk_Proc_Deta A
+ left join TEqpchk_Item T on A.eqpchkdeta_code=T.code
+ where A.djwo=@djwo";
+ dynamicParams.Add("@djwo", djwo);
+ 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 DeviceCheckTakeOutExcel(string wkshopcode, string eqpcode, string eqpname, string stanedname, string checkuser, string checkopendate, string checkclosedate)
+ {
+ var dynamicParams = new DynamicParameters();
+ string search = "";
+ try
+ {
+ if (wkshopcode != "" && wkshopcode != null)
+ {
+ search += "and T.org_code=@wkshopcode ";
+ dynamicParams.Add("@wkshopcode", wkshopcode);
+ }
+ if (eqpcode != "" && eqpcode != null)
+ {
+ search += "and A.eqp_code like '%'+@eqpcode+'%' ";
+ dynamicParams.Add("@eqpcode", eqpcode);
+ }
+ if (eqpname != "" && eqpname != null)
+ {
+ search += "and E.name like '%'+@eqpname+'%' ";
+ dynamicParams.Add("@eqpname", eqpname);
+ }
+ if (stanedname != "" && stanedname != null)
+ {
+ search += "and M.name like '%'+@stanedname+'%' ";
+ dynamicParams.Add("@stanedname", stanedname);
+ }
+ if (checkuser != "" && checkuser != null)
+ {
+ search += "and A.chk_user like '%'+@checkuser+'%' ";
+ dynamicParams.Add("@checkuser", checkuser);
+ }
+ if (checkopendate != "" && checkopendate != null)
+ {
+ search += "and A.chk_date between @checkopendate and @checkclosedate ";
+ dynamicParams.Add("@checkopendate", checkopendate + " 00:00:00");
+ dynamicParams.Add("@checkclosedate", checkclosedate + " 23:59:59");
+ }
+ 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.djwo ORDER BY B.eqpchkdeta_code) = 1 THEN A.djwo
+ ELSE ''END AS '鐐规鍗曞彿',
+ CASE WHEN
+ ROW_NUMBER() OVER (PARTITION BY E.wksp_code ORDER BY B.eqpchkdeta_code) = 1 THEN T.org_name
+ ELSE ''END AS '鐢熶骇杞﹂棿',
+ CASE WHEN
+ ROW_NUMBER() OVER (PARTITION BY A.eqp_code ORDER BY B.eqpchkdeta_code) = 1 THEN A.eqp_code
+ ELSE ''END AS '璁惧缂栧彿',
+ CASE WHEN
+ ROW_NUMBER() OVER (PARTITION BY A.eqp_code ORDER BY B.eqpchkdeta_code) = 1 THEN E.name
+ ELSE ''END AS '璁惧鍚嶇О',
+ CASE WHEN
+ ROW_NUMBER() OVER (PARTITION BY A.eqpchkmain_code ORDER BY B.eqpchkdeta_code) = 1 THEN A.eqpchkmain_code
+ ELSE ''END AS '鐐规鏍囧噯缂栫爜',
+ CASE WHEN
+ ROW_NUMBER() OVER (PARTITION BY A.eqpchkmain_code ORDER BY B.eqpchkdeta_code) = 1 THEN M.name
+ ELSE ''END AS '鐐规鏍囧噯鍚嶇О',
+ CASE WHEN
+ ROW_NUMBER() OVER (PARTITION BY A.chk_user ORDER BY B.eqpchkdeta_code) = 1 THEN A.chk_user
+ ELSE ''END AS '妫�楠屼汉鍛�',
+ CASE WHEN
+ ROW_NUMBER() OVER (PARTITION BY A.chk_result ORDER BY B.eqpchkdeta_code) = 1 THEN A.chk_result
+ ELSE ''END AS '妫�楠岀粨鏋�',
+ CASE WHEN
+ ROW_NUMBER() OVER (PARTITION BY A.chk_date ORDER BY B.eqpchkdeta_code) = 1 THEN A.chk_date
+ END AS '妫�楠屾椂闂�',
+ B.seq as '鐐规閮ㄤ綅搴忓彿',H.code '鐐规閮ㄤ綅缂栫爜',H.name '鐐规閮ㄤ綅鍚嶇О',B.result '鐐规缁撴灉',B.chk_value '鏁板��',B.remark '澶囨敞'
+ from TEqpchk_Proc_Main A
+ left join TEqpchk_Proc_Deta B on A.djwo=B.djwo
+ left join TEqpInfo E on A.eqp_code=E.code
+ left join TEqpchk_Main M on A.eqpchkmain_code=M.code
+ left join TOrganization T on E.wksp_code=T.org_code
+ left join TEqpchk_Item H on B.eqpchkdeta_code=H.code
+ where T.description='W' " + 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
+
+
+
+ #region[璁惧淇濆吇璁板綍鏌ヨ]
+ public static ToMessage DeviceCheckTakeSearch(string wkshopcode, string eqpcode, string eqpname, string stanedname, string repairuser, string repairopendate, string repairclosedate, string repairresult, int startNum, int endNum, string prop, string order)
+ {
+ var dynamicParams = new DynamicParameters();
+ string search = "";
+ try
+ {
+ if (wkshopcode != "" && wkshopcode != null)
+ {
+ search += "and T.org_code=@wkshopcode ";
+ dynamicParams.Add("@wkshopcode", wkshopcode);
+ }
+ if (eqpcode != "" && eqpcode != null)
+ {
+ search += "and A.eqp_code like '%'+@eqpcode+'%' ";
+ dynamicParams.Add("@eqpcode", eqpcode);
+ }
+ if (eqpname != "" && eqpname != null)
+ {
+ search += "and E.name like '%'+@eqpname+'%' ";
+ dynamicParams.Add("@eqpname", eqpname);
+ }
+ if (stanedname != "" && stanedname != null)
+ {
+ search += "and M.name like '%'+@stanedname+'%' ";
+ dynamicParams.Add("@stanedname", stanedname);
+ }
+ if (repairuser != "" && repairuser != null)
+ {
+ search += "and A.maint_user like '%'+@repairuser+'%' ";
+ dynamicParams.Add("@repairuser", repairuser);
+ }
+ if (repairopendate != "" && repairopendate != null)
+ {
+ search += "and A.maint_date between @repairopendate and @repairclosedate ";
+ dynamicParams.Add("@repairopendate", repairopendate + " 00:00:00");
+ dynamicParams.Add("@repairclosedate", repairclosedate + " 23:59:59");
+ }
+ if (repairresult != "" && repairresult != null)
+ {
+ search += "and A.maint_result=@repairresult ";
+ dynamicParams.Add("@repairresult", repairresult);
+ }
+ if (search == "")
+ {
+ search = "and 1=1 ";
+ }
+ //search = search.Substring(3);//鎴彇绱㈠紩2鍚庨潰鐨勫瓧绗�
+ // --------------鏌ヨ鎸囧畾鏁版嵁--------------
+ var total = 0; //鎬绘潯鏁�
+ var sql = @"select A.bywo,T.org_code,T.org_name,A.eqp_code,E.name as eqp_name,M.code as stanedcode,M.name as stanedname,A.maint_cyc,
+ A.maint_user,A.maint_result,A.maint_date
+ from TEqpmaint_Proc_Main A
+ left join TEqpInfo E on A.eqp_code=E.code
+ left join TEqpmai_Main M on A.eqpmaint_code=M.code
+ left join TOrganization T on E.wksp_code=T.org_code
+ where T.description='W' " + 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 DeviceRepairSubTakeSearch(string bywo)
+ {
+ string sql = "";
+ var dynamicParams = new DynamicParameters();
+ try
+ {
+ sql = @"select A.seq,T.code as itemcode,T.name as itemname,A.result,A.maint_value,A.remark
+ from TEqpmaint_Proc_Deta A
+ left join TEqpmai_Item T on A.eqpmaideta_code=T.code
+ where A.bywo=@bywo";
+ dynamicParams.Add("@bywo", bywo);
+ 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 DeviceRepairTakeOutExcel(string wkshopcode, string eqpcode, string eqpname, string stanedname, string repairuser, string repairopendate, string repairclosedate, string repairresult)
+ {
+ var dynamicParams = new DynamicParameters();
+ string search = "";
+ try
+ {
+ if (wkshopcode != "" && wkshopcode != null)
+ {
+ search += "and T.org_code=@wkshopcode ";
+ dynamicParams.Add("@wkshopcode", wkshopcode);
+ }
+ if (eqpcode != "" && eqpcode != null)
+ {
+ search += "and A.eqp_code like '%'+@eqpcode+'%' ";
+ dynamicParams.Add("@eqpcode", eqpcode);
+ }
+ if (eqpname != "" && eqpname != null)
+ {
+ search += "and E.name like '%'+@eqpname+'%' ";
+ dynamicParams.Add("@eqpname", eqpname);
+ }
+ if (stanedname != "" && stanedname != null)
+ {
+ search += "and M.name like '%'+@stanedname+'%' ";
+ dynamicParams.Add("@stanedname", stanedname);
+ }
+ if (repairuser != "" && repairuser != null)
+ {
+ search += "and A.maint_user like '%'+@repairuser+'%' ";
+ dynamicParams.Add("@repairuser", repairuser);
+ }
+ if (repairopendate != "" && repairopendate != null)
+ {
+ search += "and A.chk_date between @repairopendate and @repairclosedate ";
+ dynamicParams.Add("@repairopendate", repairopendate + " 00:00:00");
+ dynamicParams.Add("@repairclosedate", repairclosedate + " 23:59:59");
+ }
+ if (repairresult != "" && repairresult != null)
+ {
+ search += "and A.maint_result=@repairresult ";
+ dynamicParams.Add("@repairresult", repairresult);
+ }
+ 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.bywo ORDER BY B.eqpmaideta_code) = 1 THEN A.bywo
+ ELSE ''END AS '淇濆吇鍗曞彿',
+ CASE WHEN
+ ROW_NUMBER() OVER (PARTITION BY E.wksp_code ORDER BY B.eqpmaideta_code) = 1 THEN T.org_name
+ ELSE ''END AS '鐢熶骇杞﹂棿',
+ CASE WHEN
+ ROW_NUMBER() OVER (PARTITION BY A.eqp_code ORDER BY B.eqpmaideta_code) = 1 THEN A.eqp_code
+ ELSE ''END AS '璁惧缂栧彿',
+ CASE WHEN
+ ROW_NUMBER() OVER (PARTITION BY A.eqp_code ORDER BY B.eqpmaideta_code) = 1 THEN E.name
+ ELSE ''END AS '璁惧鍚嶇О',
+ CASE WHEN
+ ROW_NUMBER() OVER (PARTITION BY A.eqpmaint_code ORDER BY B.eqpmaideta_code) = 1 THEN A.eqpmaint_code
+ ELSE ''END AS '淇濆吇鏍囧噯缂栫爜',
+ CASE WHEN
+ ROW_NUMBER() OVER (PARTITION BY A.eqpmaint_code ORDER BY B.eqpmaideta_code) = 1 THEN M.name
+ ELSE ''END AS '淇濆吇鏍囧噯鍚嶇О',
+ CASE WHEN
+ ROW_NUMBER() OVER (PARTITION BY A.maint_cyc ORDER BY B.eqpmaideta_code) = 1 THEN A.maint_cyc
+ ELSE ''END AS '淇濆吇鍛ㄦ湡',
+ CASE WHEN
+ ROW_NUMBER() OVER (PARTITION BY A.maint_user ORDER BY B.eqpmaideta_code) = 1 THEN A.maint_user
+ ELSE ''END AS '淇濆吇浜哄憳',
+ CASE WHEN
+ ROW_NUMBER() OVER (PARTITION BY A.maint_result ORDER BY B.eqpmaideta_code) = 1 THEN A.maint_result
+ ELSE ''END AS '淇濆吇缁撴灉',
+ CASE WHEN
+ ROW_NUMBER() OVER (PARTITION BY A.maint_date ORDER BY B.eqpmaideta_code) = 1 THEN A.maint_date
+ END AS '淇濆吇鏃堕棿',
+ B.seq as '淇濆吇閮ㄤ綅搴忓彿',H.code '淇濆吇閮ㄤ綅缂栫爜',H.name '淇濆吇閮ㄤ綅鍚嶇О',B.result '淇濆吇缁撴灉',B.maint_value '鏁板��',B.remark '澶囨敞'
+ from TEqpmaint_Proc_Main A
+ left join TEqpmaint_Proc_Deta B on A.bywo=B.bywo
+ left join TEqpInfo E on A.eqp_code=E.code
+ left join TEqpmai_Main M on A.eqpmaint_code=M.code
+ left join TOrganization T on E.wksp_code=T.org_code
+ left join TEqpmai_Item H on B.eqpmaideta_code=H.code
+ where T.description='W' " + 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
}
}
\ No newline at end of file
--
Gitblit v1.9.3