From 4f57c3e602eef9ec929a2315183fa28b21cd0987 Mon Sep 17 00:00:00 2001
From: yl <ykxkd@outlook.com>
Date: 星期三, 29 三月 2023 10:51:33 +0800
Subject: [PATCH] 订单下达获取工单语句修改、生产工单手工创建获取单据号接口
---
VueWebApi/DLL/DAL/DeviceManagerDAL.cs | 276 +++++++++++++++++++++++++++++++++++++++++++++++++++++-
1 files changed, 268 insertions(+), 8 deletions(-)
diff --git a/VueWebApi/DLL/DAL/DeviceManagerDAL.cs b/VueWebApi/DLL/DAL/DeviceManagerDAL.cs
index dc7d500..930c240 100644
--- a/VueWebApi/DLL/DAL/DeviceManagerDAL.cs
+++ b/VueWebApi/DLL/DAL/DeviceManagerDAL.cs
@@ -1286,7 +1286,9 @@
//鍒犻櫎璁惧鐐规鏍囧噯鍏宠仈鐐规椤圭洰瀛愯〃
sql = @"delete TEqpchk_Deta where eqpchk_main_code=@checkstand_code";
list.Add(new { str = sql, parm = new { checkstand_code = checkstand_code } });
-
+ //鍒犻櫎璁惧鐐规鏍囧噯鍏宠仈璁惧琛�
+ sql = @"delete TEqpchk_Eqp where eqpchk_main_code=@checkstand_code";
+ list.Add(new { str = sql, parm = new { checkstand_code = checkstand_code } });
bool aa = DapperHelper.DoTransaction(list);
if (aa)
{
@@ -1351,9 +1353,8 @@
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
+ select A.code,A.name,'' as 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 (
@@ -1362,7 +1363,7 @@
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";
+ ) 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);
@@ -1723,6 +1724,9 @@
//鍒犻櫎璁惧淇濆吇鏍囧噯鍏宠仈淇濆吇椤圭洰瀛愯〃
sql = @"delete TEqpmai_Deta where eapmai_code=@repairstand_code";
list.Add(new { str = sql, parm = new { repairstand_code = repairstand_code } });
+ //鍒犻櫎璁惧淇濆吇鏍囧噯鍏宠仈璁惧琛�
+ sql = @"delete TEqpmai_Eqp where eapmai_code=@repairstand_code";
+ list.Add(new { str = sql, parm = new { repairstand_code = repairstand_code } });
bool aa = DapperHelper.DoTransaction(list);
if (aa)
@@ -1789,9 +1793,8 @@
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
+ select A.code,A.name,'' as 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 (
@@ -1800,7 +1803,7 @@
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";
+ ) 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);
@@ -2251,7 +2254,7 @@
{
search = "and 1=1 ";
}
- search = search.Substring(3);//鎴彇绱㈠紩2鍚庨潰鐨勫瓧绗�
+ //search = search.Substring(3);//鎴彇绱㈠紩2鍚庨潰鐨勫瓧绗�
// --------------鏌ヨ鎸囧畾鏁版嵁--------------
var total = 0; //鎬绘潯鏁�
var sql = @"select
@@ -2311,5 +2314,262 @@
return mes;
}
#endregion
+
+
+
+ #region[璁惧缁翠慨璁板綍鏌ヨ]
+ public static ToMessage DeviceUpdateSearch(string wkshopcode, string eqpcode, string eqpname, string reportuser, string repairuser, string vrifcatuser, string reportopendate, string reportclosedate, string repairopendate, string repairclosedate, string vrifcatopendate, string vrifcatclosedate, int startNum, int endNum, string prop, string order)
+ {
+ var dynamicParams = new DynamicParameters();
+ string search = "";
+ try
+ {
+ if (wkshopcode != "" && wkshopcode != null)
+ {
+ search += "and A.wksp_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 (reportuser != "" && reportuser != null)
+ {
+ search += "and B.request_person like '%'+@reportuser+'%' ";
+ dynamicParams.Add("@reportuser", reportuser);
+ }
+ if (repairuser != "" && repairuser != null)
+ {
+ search += "and A.repair_person like '%'+@repairuser+'%' ";
+ dynamicParams.Add("@repairuser", repairuser);
+ }
+ if (vrifcatuser != "" && vrifcatuser != null)
+ {
+ search += "and A.verify_person like '%'+@vrifcatuser+'%' ";
+ dynamicParams.Add("@vrifcatuser", vrifcatuser);
+ }
+ if (reportopendate != "" && reportopendate != null)
+ {
+ search += "and B.request_date between @reportopendate and @reportclosedate ";
+ dynamicParams.Add("@reportopendate", reportopendate + " 00:00:00");
+ dynamicParams.Add("@reportclosedate", reportclosedate + " 23:59:59");
+ }
+ if (repairopendate != "" && repairopendate != null)
+ {
+ search += "and A.repair_date between @repairopendate and @repairclosedate ";
+ dynamicParams.Add("@repairopendate", repairopendate + " 00:00:00");
+ dynamicParams.Add("@repairclosedate", repairclosedate + " 23:59:59");
+ }
+ if (vrifcatopendate != "" && vrifcatopendate != null)
+ {
+ search += "and A.verify_date between @vrifcatopendate and @vrifcatclosedate ";
+ dynamicParams.Add("@vrifcatopendate", vrifcatopendate + " 00:00:00");
+ dynamicParams.Add("@vrifcatclosedate", vrifcatclosedate + " 23:59:59");
+ }
+
+ if (search == "")
+ {
+ search = "and 1=1 ";
+ }
+ //search = search.Substring(3);//鎴彇绱㈠紩2鍚庨潰鐨勫瓧绗�
+ // --------------鏌ヨ鎸囧畾鏁版嵁--------------
+ var total = 0; //鎬绘潯鏁�
+ var sql = @"select A.repair_code,A.wksp_code,T.org_name as wksp_name,A.eqp_code,E.name as eqp_name,B.request_person,B.request_date,
+ A.repair_person,A.repair_date,CAST(datediff(minute, B.request_date,A.repair_date)/60.0 AS decimal(9,1)) as cycleDate,
+ A.verify_person,A.verify_date,(case when A.verify_result='OK' then '閫氳繃' when A.verify_result='NG' then '涓嶉�氳繃' end)as verify_result
+ from TEqp_Repair A
+ left join TEqp_RepairRequest B on A.source_wo=B.docu_code
+ left join TEqpInfo E on A.eqp_code=E.code
+ left join TOrganization T on A.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 DeviceUpdateSubSearch(string repair_code)
+ {
+ string sql = "";
+ var dynamicParams = new DynamicParameters();
+ Dictionary<object, object> Dict = new Dictionary<object, object>();
+ try
+ {
+ //鑾峰彇璁惧鎶ヤ慨鐢宠鏁版嵁
+ sql = @"select B.docu_code,
+ (
+ case when B.source='A' then '璁惧鐐规'
+ when B.source='B' then '璁惧淇濆吇'
+ when B.source='C' then '瀹夌伅鍛煎彨'
+ when B.source='C' then '鎵嬪伐鍒涘缓' end
+ ) as source,B.failure_descript from TEqp_Repair A
+ left join TEqp_RepairRequest B on A.source_wo=B.docu_code
+ where A.repair_code=@repair_code";
+ dynamicParams.Add("@repair_code", repair_code);
+ var data = DapperHelper.selectdata(sql, dynamicParams);
+ DeviceReport report = new DeviceReport();
+ report.faultsource = data.Rows[0]["source"].ToString();
+ report.faultdescr = data.Rows[0]["failure_descript"].ToString();
+ //鑾峰彇璁惧鎶ヤ慨鐢宠鍥剧墖鏁版嵁
+ sql = @"select M.img1url from TEqp_Repair A
+ left join TEqp_RepairRequest B on A.source_wo=B.docu_code
+ left join TEqp_RepairImage M on B.docu_code=M.source_wo
+ where A.repair_code=@repair_code and M.wo_type='REPA'";
+ dynamicParams.Add("@repair_code", repair_code);
+ var data0 = DapperHelper.selectdata(sql, dynamicParams);
+ report.faultimage = data0;
+ Dict.Add("data1", report);
+
+ //鑾峰彇璁惧缁翠慨鏁版嵁
+ sql = @"select A.failure_descript,A.is_shutdown,A.repair_content,A.repair_part from TEqp_Repair A
+ left join TEqp_RepairRequest B on A.source_wo=B.docu_code
+ where A.repair_code=@repair_code";
+ dynamicParams.Add("@repair_code", repair_code);
+ var data1 = DapperHelper.selectdata(sql, dynamicParams);
+ DeviceRepair repair = new DeviceRepair();
+ repair.faultdescr = data1.Rows[0]["failure_descript"].ToString();
+ repair.isstoprepair = data1.Rows[0]["is_shutdown"].ToString();
+ repair.repaircontent = data1.Rows[0]["repair_content"].ToString();
+ repair.repairpart = data1.Rows[0]["repair_part"].ToString();
+ //鑾峰彇璁惧缁翠慨鍥剧墖鏁版嵁
+ sql = @"select M.img1url,M.img2url from TEqp_Repair A
+ left join TEqp_RepairImage M on A.repair_code=M.source_wo
+ where A.repair_code=@repair_code and M.wo_type='COMP'";
+ dynamicParams.Add("@repair_code", repair_code);
+ var data3 = DapperHelper.selectdata(sql, dynamicParams);
+ repair.repairimage = data3;
+ Dict.Add("data2", repair);
+ mes.code = "200";
+ mes.Message = "鏌ヨ鎴愬姛!";
+ mes.data = Dict;
+ }
+ catch (Exception e)
+ {
+ mes.code = "300";
+ mes.count = 0;
+ mes.Message = e.Message;
+ mes.data = null;
+ }
+ return mes;
+ }
+ #endregion
+
+ #region[璁惧缁翠慨璁板綍瀵煎嚭]
+ public static ToMessage DeviceUpdateOutExcel(string wkshopcode, string eqpcode, string eqpname, string reportuser, string repairuser, string vrifcatuser, string reportopendate, string reportclosedate, string repairopendate, string repairclosedate, string vrifcatopendate, string vrifcatclosedate)
+ {
+ var dynamicParams = new DynamicParameters();
+ string search = "";
+ try
+ {
+ if (wkshopcode != "" && wkshopcode != null)
+ {
+ search += "and A.wksp_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 (reportuser != "" && reportuser != null)
+ {
+ search += "and B.request_person like '%'+@reportuser+'%' ";
+ dynamicParams.Add("@reportuser", reportuser);
+ }
+ if (repairuser != "" && repairuser != null)
+ {
+ search += "and A.repair_person like '%'+@repairuser+'%' ";
+ dynamicParams.Add("@repairuser", repairuser);
+ }
+ if (vrifcatuser != "" && vrifcatuser != null)
+ {
+ search += "and A.verify_person like '%'+@vrifcatuser+'%' ";
+ dynamicParams.Add("@vrifcatuser", vrifcatuser);
+ }
+ if (reportopendate != "" && reportopendate != null)
+ {
+ search += "and B.request_date between @reportopendate and @reportclosedate ";
+ dynamicParams.Add("@reportopendate", reportopendate + " 00:00:00");
+ dynamicParams.Add("@reportclosedate", reportclosedate + " 23:59:59");
+ }
+ if (repairopendate != "" && repairopendate != null)
+ {
+ search += "and A.repair_date between @repairopendate and @repairclosedate ";
+ dynamicParams.Add("@repairopendate", repairopendate + " 00:00:00");
+ dynamicParams.Add("@repairclosedate", repairclosedate + " 23:59:59");
+ }
+ if (vrifcatopendate != "" && vrifcatopendate != null)
+ {
+ search += "and A.verify_date between @vrifcatopendate and @vrifcatclosedate ";
+ dynamicParams.Add("@vrifcatopendate", vrifcatopendate + " 00:00:00");
+ dynamicParams.Add("@vrifcatclosedate", vrifcatclosedate + " 23:59:59");
+ }
+
+ if (search == "")
+ {
+ search = "and 1=1 ";
+ }
+ //search = search.Substring(3);//鎴彇绱㈠紩2鍚庨潰鐨勫瓧绗�
+ // --------------鏌ヨ鎸囧畾鏁版嵁--------------
+ var total = 0; //鎬绘潯鏁�
+ var sql = @"select
+ A.repair_code as '缁翠慨鍗曞彿',
+ T.org_name as '鎵�灞炶溅闂�',
+ A.eqp_code as '璁惧缂栫爜',
+ E.name as '璁惧鍚嶇О',
+ B.request_person as '鎶ヤ慨浜哄憳',
+ B.request_date as '鎶ヤ慨鏃堕棿',
+ A.repair_person as '缁翠慨浜哄憳',
+ A.repair_date as '缁翠慨鏃堕棿',
+ CAST(datediff(minute, B.request_date,A.repair_date)/60.0 AS decimal(9,1)) as '缁翠慨鏃堕暱(灏忔椂)',
+ A.verify_person as '楠岃瘉浜哄憳',
+ A.verify_date as '楠岃瘉鏃堕棿',
+ (case when A.verify_result='OK' then '閫氳繃' when A.verify_result='NG' then '涓嶉�氳繃' end)as '楠岃瘉缁撴灉'
+ from TEqp_Repair A
+ left join TEqp_RepairRequest B on A.source_wo=B.docu_code
+ left join TEqpInfo E on A.eqp_code=E.code
+ left join TOrganization T on A.wksp_code=T.org_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