From bd14d44a46cd191d0deb15b6f752f9477b818398 Mon Sep 17 00:00:00 2001
From: yl <ykxkd@outlook.com>
Date: 星期五, 22 九月 2023 17:53:46 +0800
Subject: [PATCH] 修改车间看板判断条件
---
VueWebApi/DLL/DAL/ProductModelDAL.cs | 222 ++++++++++++++++++++++++++++++++++++++-----------------
1 files changed, 153 insertions(+), 69 deletions(-)
diff --git a/VueWebApi/DLL/DAL/ProductModelDAL.cs b/VueWebApi/DLL/DAL/ProductModelDAL.cs
index 0f15076..f803f51 100644
--- a/VueWebApi/DLL/DAL/ProductModelDAL.cs
+++ b/VueWebApi/DLL/DAL/ProductModelDAL.cs
@@ -27,7 +27,7 @@
try
{
//鑾峰彇璁惧绫诲瀷鏁版嵁
- sql = @"select code,name from T_Dict where dict_type='CHLX' ";
+ sql = @"select code,name,iparent_id from T_Dict where dict_type='CHLX' ";
var data = DapperHelper.selecttable(sql);
mes.code = "200";
mes.Message = "鏌ヨ鎴愬姛!";
@@ -421,12 +421,29 @@
#endregion
#region[瀛樿揣妗ｆ鏌ヨ]
- public static ToMessage InventoryFileSelect(string partcode, string partname, string partspec, string stocktypecode, string materialtypecode, string storehousecode, int startNum, int endNum, string prop, string order)
+ public static ToMessage InventoryFileSelect(string stu_torgcode,string stu_torgtypecode,string partcode, string partname, string partspec, string stocktypecode, string materialtypecode, string storehousecode, int startNum, int endNum, string prop, string order)
{
var dynamicParams = new DynamicParameters();
+ string search0 = "";
string search = "";
+ string search1 = "";
try
{
+ switch (stu_torgtypecode)
+ {
+ case "":
+ break;
+ case "D":
+ search1 += "and L.org_code=@stu_torgcode ";
+ dynamicParams.Add("@stu_torgcode", stu_torgcode);
+ break;
+ case "W":
+ search1 += "and F.org_code=@stu_torgcode ";
+ dynamicParams.Add("@stu_torgcode", stu_torgcode);
+ break;
+ default:
+ break;
+ }
if (partcode != "" && partcode != null)
{
search += "and A.partcode like '%'+@partcode+'%' ";
@@ -444,7 +461,8 @@
}
if (stocktypecode != "" && stocktypecode != null)
{
- search += "and D.code=@stocktypecode ";
+ search0+= " where code = @stocktypecode";
+ search += " and CTE.iparent_id<>'' and CTE.dict_type='CHLX'";
dynamicParams.Add("@stocktypecode", stocktypecode);
}
if (materialtypecode != "" && materialtypecode != null)
@@ -463,24 +481,80 @@
}
// --------------鏌ヨ鎸囧畾鏁版嵁--------------
var total = 0; //鎬绘潯鏁�
- var sql = @"select A.id,A.partcode,A.partname,A.partspec,A.uom_code,B.name as uom_name,D.code as stocktypecode,D.name as stocktypename,
- C.code as materialtypecode,C.name as materialtypename,A.stck_code,T.name as stck_name,A.maxqty,A.minqty,U.username as lm_user,
+ //var sql = @"select A.id,A.partcode,A.partname,A.partspec,A.uom_code,B.name as uom_name,D.code as stocktypecode,D.name as stocktypename,
+ // C.code as materialtypecode,C.name as materialtypename,A.stck_code,T.name as stck_name,A.maxqty,A.minqty,U.username as lm_user,
+ // A.lm_date,(case when isnull(M.materiel_code,'')='' then 'N' else 'Y' end) as proute_id,A.is_batchno,A.is_fifo,A.is_incheck,A.is_outcheck
+ // from TMateriel_Info A
+ // left join TUom B on A.uom_code=B.code
+ // left join TMateriel_Type C on A.materieltype_code=C.code
+ // left join T_Dict D on A.stocktype_code=D.code and D.dict_type='CHLX'
+ // left join T_Sec_Stck T on A.stck_code=T.code
+ // left join TUser U on A.lm_user=U.usercode
+ // left join (
+ // select distinct materiel_code from TMateriel_Route M
+ // left join TOrganization F on M.torg_code=F.org_code
+ // left join TOrganization as L on F.parent_id=L.id
+ // where 1=1 "+search1+") M on A.partcode=M.materiel_code where A.is_delete<>'1' " + search;
+ var sql0 = @"WITH CTE AS (
+ SELECT code,name,iparent_id,dict_type
+ FROM T_Dict " + search0 + " ";
+ sql0 += @"UNION ALL
+ SELECT t.code,t.name,t.iparent_id,t.dict_type
+ FROM T_Dict t
+ INNER JOIN CTE c ON c.code = t.iparent_id)
+ SELECT COUNT(1) as total FROM (
+ SELECT distinct A.id,A.partcode,A.partname,A.partspec,A.uom_code,B.name as uom_name,D.code as stocktypecode,D.name as stocktypename,D.iparent_id,
+ A.stck_code,T.name as stck_name,A.maxqty,A.minqty,U.username as lm_user,
A.lm_date,(case when isnull(M.materiel_code,'')='' then 'N' else 'Y' end) as proute_id,A.is_batchno,A.is_fifo,A.is_incheck,A.is_outcheck
- from TMateriel_Info A
- left join TUom B on A.uom_code=B.code
- left join TMateriel_Type C on A.materieltype_code=C.code
- left join T_Dict D on A.stocktype_code=D.code and D.dict_type='CHLX'
- left join T_Sec_Stck T on A.stck_code=T.code
- left join TUser U on A.lm_user=U.usercode
- left join (
- select distinct materiel_code from TMateriel_Route
- ) M on A.partcode=M.materiel_code
- where A.is_delete<>'1' " + search;
- var data = DapperHelper.GetPageList<object>(sql, dynamicParams, prop, order, startNum, endNum, out total);
+ FROM CTE
+ right join TMateriel_Info A on CTE.code=A.stocktype_code
+ left join TUom B on A.uom_code=B.code
+ left join T_Dict D on A.stocktype_code=D.code
+ left join T_Sec_Stck T on A.stck_code=T.code
+ left join TUser U on A.lm_user=U.usercode
+ left join (
+ select distinct materiel_code from TMateriel_Route M
+ left join TOrganization F on M.torg_code=F.org_code
+ left join TOrganization as L on F.parent_id=L.id
+ where 1=1 " + search1 + ") M on A.partcode=M.materiel_code where A.is_delete<>'1' " + search+") AS Result";
+ var data0 = DapperHelper.selectdata(sql0, dynamicParams);
+ if (data0.Rows.Count > 0)
+ {
+ total = Convert.ToInt32(data0.Rows[0]["total"].ToString());
+ }
+ else
+ {
+ total = 0;
+ }
+
+ var sql = @"WITH CTE AS (
+ SELECT code,name,iparent_id,dict_type
+ FROM T_Dict "+search0+" ";
+ sql += @"UNION ALL
+ SELECT t.code,t.name,t.iparent_id,t.dict_type
+ FROM T_Dict t
+ INNER JOIN CTE c ON c.code = t.iparent_id)
+ SELECT * FROM(SELECT *,ROW_NUMBER() OVER(ORDER BY lm_date desc) AS RowNum
+ FROM(
+ SELECT distinct A.id,A.partcode,A.partname,A.partspec,A.uom_code,B.name as uom_name,D.code as stocktypecode,D.name as stocktypename,D.iparent_id,
+ A.stck_code,T.name as stck_name,A.maxqty,A.minqty,U.username as lm_user,
+ A.lm_date,(case when isnull(M.materiel_code,'')='' then 'N' else 'Y' end) as proute_id,A.is_batchno,A.is_fifo,A.is_incheck,A.is_outcheck
+ FROM CTE
+ right join TMateriel_Info A on CTE.code=A.stocktype_code
+ left join TUom B on A.uom_code=B.code
+ left join T_Dict D on A.stocktype_code=D.code
+ left join T_Sec_Stck T on A.stck_code=T.code
+ left join TUser U on A.lm_user=U.usercode
+ left join (
+ select distinct materiel_code from TMateriel_Route M
+ left join TOrganization F on M.torg_code=F.org_code
+ left join TOrganization as L on F.parent_id=L.id
+ where 1=1 " + search1+") M on A.partcode=M.materiel_code where A.is_delete<>'1' "+search+ " ) AS Temp) AS Result WHERE RowNum >= "+ startNum + " AND RowNum <="+ endNum + " ORDER BY "+ prop + " "+order+"";
+ var data = DapperHelper.selectdata(sql, dynamicParams);
mes.code = "200";
mes.Message = "鏌ヨ鎴愬姛!";
mes.count = total;
- mes.data = data.ToList();
+ mes.data = data;
}
catch (Exception e)
{
@@ -494,7 +568,7 @@
#endregion
#region[瀛樿揣妗ｆ鏂板缂栬緫]
- public static ToMessage AddUpdateInventoryFile(string materialid, string materialcode, string materialname, string materialspec, string uomcode, string warehousecode, string stocktypecode, string materialtypecode, string minstockqty, string maxstockqty,string is_batchno,string is_fifo,string is_incheck,string is_outcheck, string username, string operType)
+ public static ToMessage AddUpdateInventoryFile(string materialid, string materialcode, string materialname, string materialspec, string uomcode, string warehousecode, string stocktypecode, string minstockqty, string maxstockqty,string is_batchno,string is_fifo,string is_incheck,string is_outcheck, string username, string operType)
{
var dynamicParams = new DynamicParameters();
try
@@ -520,7 +594,7 @@
dynamicParams.Add("@uomcode", uomcode);
dynamicParams.Add("@warehousecode", warehousecode);
dynamicParams.Add("@stocktypecode", stocktypecode);
- dynamicParams.Add("@materialtypecode", materialtypecode);
+ dynamicParams.Add("@materialtypecode", "");
dynamicParams.Add("@minstockqty", minstockqty);
dynamicParams.Add("@maxstockqty", maxstockqty);
dynamicParams.Add("@username", username);
@@ -556,7 +630,7 @@
dynamicParams.Add("@uomcode", uomcode);
dynamicParams.Add("@warehousecode", warehousecode);
dynamicParams.Add("@stocktypecode", stocktypecode);
- dynamicParams.Add("@materialtypecode", materialtypecode);
+ dynamicParams.Add("@materialtypecode", "");
dynamicParams.Add("@minstockqty", minstockqty);
dynamicParams.Add("@maxstockqty", maxstockqty);
dynamicParams.Add("@username", username);
@@ -657,6 +731,18 @@
}
//褰撳墠鐗╂枡鏄惁鐢熸垚鍑哄叆搴撹褰�
//鏈夋病鏈夎宸ュ崟寮曠敤
+ sql = @"select * from TK_Wrk_Man
+ where materiel_code=@materialcode";
+ dynamicParams.Add("@materialcode", materialcode);
+ var data3 = DapperHelper.selectdata(sql, dynamicParams);
+ if (data2.Rows.Count > 0)
+ {
+ mes.code = "300";
+ mes.count = 0;
+ mes.Message = "鐗╂枡宸叉湁鍏宠仈鐨勭敓浜у伐鍗�,涓嶅厑璁稿垹闄�!";
+ mes.data = null;
+ return mes;
+ }
//鍒犻櫎鐗╂枡
sql = @"delete TMateriel_Info where partcode=@materialcode";
@@ -760,63 +846,40 @@
public static ToMessage SaveInventoryFile(string stu_torgcode,string stu_torgtypecode, string partcode, string defaultroute_code, string username, List<PartRout> json)
{
var sql = "";
+ var dynamicParams = new DynamicParameters();
+ string search = "";
List<object> list = new List<object>();
try
{
list.Clear();
- if (json == null || json.Count <= 0)
+ switch (stu_torgtypecode)
{
- switch (stu_torgtypecode)
- {
- case "":
- //娓呴櫎鐗╂枡鍏宠仈宸ヨ壓璺嚎琛ㄦ暟鎹�
- sql = @"delete TMateriel_Route where materiel_code=@partcode";
- list.Add(new { str = sql, parm = new { partcode = partcode } });
- break;
- case "D":
- //娓呴櫎鐗╂枡鍏宠仈宸ヨ壓璺嚎琛ㄦ暟鎹�
- sql = @"delete from TMateriel_Route where id in (select A.id from TMateriel_Route A
+ case "":
+ //娓呴櫎鐗╂枡鍏宠仈宸ヨ壓璺嚎琛ㄦ暟鎹�
+ sql = @"delete TMateriel_Route where materiel_code=@partcode";
+ list.Add(new { str = sql, parm = new { partcode = partcode } });
+ break;
+ case "D":
+ //娓呴櫎鐗╂枡鍏宠仈宸ヨ壓璺嚎琛ㄦ暟鎹�
+ sql = @"delete from TMateriel_Route where id in (select A.id from TMateriel_Route A
left join TOrganization T on A.torg_code=T.org_code
left join TOrganization as L on T.parent_id=L.id
where 1=1 and A.materiel_code=@partcode and L.org_code=@stu_torgcode)";
- list.Add(new { str = sql, parm = new { partcode = partcode, stu_torgcode = stu_torgcode } });
- break;
- case "W":
- //娓呴櫎鐗╂枡鍏宠仈宸ヨ壓璺嚎琛ㄦ暟鎹�
- sql = @"delete from TMateriel_Route where id in (select A.id from TMateriel_Route A
+ list.Add(new { str = sql, parm = new { partcode = partcode, stu_torgcode = stu_torgcode } });
+ break;
+ case "W":
+ //娓呴櫎鐗╂枡鍏宠仈宸ヨ壓璺嚎琛ㄦ暟鎹�
+ sql = @"delete from TMateriel_Route where id in (select A.id from TMateriel_Route A
left join TOrganization T on A.torg_code=T.org_code
left join TOrganization as L on T.parent_id=L.id
where 1=1 and A.materiel_code=@partcode and T.org_code=@stu_torgcode)";
- list.Add(new { str = sql, parm = new { partcode = partcode, stu_torgcode = stu_torgcode } });
- break;
- }
+ list.Add(new { str = sql, parm = new { partcode = partcode, stu_torgcode = stu_torgcode } });
+ break;
}
- else
+ if (json != null && json.Count >0)
{
- switch (stu_torgtypecode)
- {
- case "":
- //娓呴櫎鐗╂枡鍏宠仈宸ヨ壓璺嚎琛ㄦ暟鎹�
- sql = @"delete TMateriel_Route where materiel_code=@partcode";
- list.Add(new { str = sql, parm = new { partcode = partcode } });
- break;
- case "D":
- //娓呴櫎鐗╂枡鍏宠仈宸ヨ壓璺嚎琛ㄦ暟鎹�
- sql = @"delete from TMateriel_Route where id in (select A.id from TMateriel_Route A
- left join TOrganization T on A.torg_code=T.org_code
- left join TOrganization as L on T.parent_id=L.id
- where 1=1 and A.materiel_code=@partcode and L.org_code=@stu_torgcode)";
- list.Add(new { str = sql, parm = new { partcode = partcode, stu_torgcode = stu_torgcode } });
- break;
- case "W":
- //娓呴櫎鐗╂枡鍏宠仈宸ヨ壓璺嚎琛ㄦ暟鎹�
- sql = @"delete from TMateriel_Route where id in (select A.id from TMateriel_Route A
- left join TOrganization T on A.torg_code=T.org_code
- left join TOrganization as L on T.parent_id=L.id
- where 1=1 and A.materiel_code=@partcode and T.org_code=@stu_torgcode)";
- list.Add(new { str = sql, parm = new { partcode = partcode, stu_torgcode = stu_torgcode } });
- break;
- }
+ //鐗╂枡+缁勭粐鏌ヨ鑺傛媿宸ヤ环琛ㄤ腑瀵瑰簲鐨勬墍鏈夊伐鑹鸿矾绾�,鐒跺悗鏍规嵁浼犺繃鏉ョ殑鍋氬姣旓紝鍐嶅幓鍒犻櫎鑺傛媿宸ヤ环琛�
+
//寰幆鍐欏叆鐢ㄦ埛鍏宠仈瑙掕壊琛�
for (int i = 0; i < json.Count; i++)
{
@@ -1905,12 +1968,19 @@
//鏍囪宸ュ簭琛ㄥ叧鑱斿伐浣滅珯鏍囪瘑
sql = @"update TStep set is_eqp='N' where stepcode=@stepcode";
list.Add(new { str = sql, parm = new { stepcode = stepcode } });
+ //鏍规嵁宸ュ簭娓呴櫎鑺傛媿宸ヤ环琛�
+ sql = @"delete TPrteEqp_Stad where step_code=@stepcode";
+ list.Add(new { str = sql, parm = new { stepcode = stepcode } });
}
else
{
//娓呴櫎宸ュ簭鍏宠仈宸ヤ綔绔欒〃鏁版嵁
sql = @"delete TFlw_Rteqp where step_code=@stepcode";
list.Add(new { str = sql, parm = new { stepcode = stepcode } });
+ //鏍规嵁宸ュ簭娓呴櫎鑺傛媿宸ヤ环琛�
+ sql = @"delete TPrteEqp_Stad where step_code=@stepcode";
+ list.Add(new { str = sql, parm = new { stepcode = stepcode } });
+
//寰幆鍐欏叆鐢ㄦ埛鍏宠仈瑙掕壊琛�
for (int i = 0; i < json.Count; i++)
{
@@ -2307,6 +2377,7 @@
{
var dynamicParams = new DynamicParameters();
string search = "";
+ string search1 = "";
try
{
switch (stu_torgtypecode)
@@ -2314,11 +2385,11 @@
case "":
break;
case "D":
- search += "and L.org_code=@stu_torgcode ";
+ search1 += "and L.org_code=@stu_torgcode ";
dynamicParams.Add("@stu_torgcode", stu_torgcode);
break;
case "W":
- search += "and F.org_code=@stu_torgcode ";
+ search1 += "and F.org_code=@stu_torgcode ";
dynamicParams.Add("@stu_torgcode", stu_torgcode);
break;
default:
@@ -2341,7 +2412,7 @@
}
if (eqpcode != "" && eqpcode != null)
{
- search += "and A.eqp_code=@eqpcode";
+ search += "and A.eqp_code=@eqpcode ";
dynamicParams.Add("@eqpcode", eqpcode);
}
if (search == "")
@@ -2358,9 +2429,22 @@
left join TFlw_Rout C on A.route_code=C.code
left join TStep D on A.step_code=D.stepcode
left join TEqpInfo E on A.eqp_code=E.code
- left join TOrganization F on E.wksp_code=F.org_code
+ left join TOrganization F on C.torg_code=F.org_code
left join TOrganization L on F.parent_id=L.id
- where B.is_delete<>'1' and C.is_delete<>'1' and D.is_delete<>'1' and E.is_delete<>'1' and F.is_delete<>'1' " + search;
+ where B.is_delete<>'1' and C.is_delete<>'1' and D.is_delete<>'1' and E.is_delete<>'1' and F.is_delete<>'1' " + search1 + search;
+ sql += @"union all
+ select A.materiel_code as partcode,B.partname,B.partspec,A.route_code,C.name as route_name,
+ D.stepcode,D.stepname,A.eqp_code,E.name as eqp_name,''as wksp_code,'' as wksp_name,
+ A.eqp_value,A.stand_value,A.cavity_qty,A.unprice
+ from TPrteEqp_Stad A
+ left join TMateriel_Info B on A.materiel_code=B.partcode
+ left join TFlw_Rout C on A.route_code=C.code
+ left join TStep D on A.step_code=D.stepcode
+ left join TCustomer E on A.eqp_code=E.code
+ left join TOrganization F on C.torg_code=F.org_code
+ left join TOrganization L on F.parent_id=L.id
+ where B.is_delete<>'1' and C.is_delete<>'1' and D.is_delete<>'1' and E.is_delete<>'1' " + search;
+
var data = DapperHelper.GetPageList<object>(sql, dynamicParams, prop, order, startNum, endNum, out total);
mes.code = "200";
mes.Message = "鏌ヨ鎴愬姛!";
--
Gitblit v1.9.3