From 1d65947983e77ba73e133632bce763cb9bcd6fcc Mon Sep 17 00:00:00 2001
From: yl <ykxkd@outlook.com>
Date: 星期三, 16 八月 2023 17:59:44 +0800
Subject: [PATCH] 生产报工、外协收料，不良数量根据不良原因分组统计
---
VueWebApi/DLL/DAL/ReportManagerDAL.cs | 70 +++++++++++++++++++++--------------
1 files changed, 42 insertions(+), 28 deletions(-)
diff --git a/VueWebApi/DLL/DAL/ReportManagerDAL.cs b/VueWebApi/DLL/DAL/ReportManagerDAL.cs
index c4fa069..ea4717b 100644
--- a/VueWebApi/DLL/DAL/ReportManagerDAL.cs
+++ b/VueWebApi/DLL/DAL/ReportManagerDAL.cs
@@ -694,52 +694,52 @@
{
if (wkshopcode != "" && wkshopcode != null)
{
- search += "and W.wkshp_code=@wkshopcode ";
+ search += "and AA.wkshp_code=@wkshopcode ";
dynamicParams.Add("@wkshopcode", wkshopcode);
}
if (wocode != "" && wocode != null)
{
- search += "and A.wo_code like '%'+@wocode+'%' ";
+ search += "and AA.wo_code like '%'+@wocode+'%' ";
dynamicParams.Add("@wocode", wocode);
}
if (partcode != "" && partcode != null)
{
- search += "and M.partcode like '%'+@partcode+'%' ";
+ search += "and AA.partcode like '%'+@partcode+'%' ";
dynamicParams.Add("@partcode", partcode);
}
if (partname != "" && partname != null)
{
- search += "and M.partname like '%'+@partname+'%' ";
+ search += "and AA.partname like '%'+@partname+'%' ";
dynamicParams.Add("@partname", partname);
}
if (partspec != "" && partspec != null)
{
- search += "and M.partspec like '%'+@partspec+'%' ";
+ search += "and AA.partspec like '%'+@partspec+'%' ";
dynamicParams.Add("@partspec", partspec);
}
if (stepname != "" && stepname != null)
{
- search += "and T.stepname like '%'+@stepname+'%' ";
+ search += "and AA.stepname like '%'+@stepname+'%' ";
dynamicParams.Add("@stepname", stepname);
}
if (defectcode != "" && defectcode != null)
{
- search += "and F.code like '%'+@defectcode+'%' ";
+ search += "and AA.defect_code like '%'+@defectcode+'%' ";
dynamicParams.Add("@defectcode", defectcode);
}
if (defectname != "" && defectname != null)
{
- search += "and F.name like '%'+@defectname+'%' ";
+ search += "and AA.defect_name like '%'+@defectname+'%' ";
dynamicParams.Add("@defectname", defectname);
}
if (reportname != "" && reportname != null)
{
- search += "and U.username like '%'+@reportname+'%' ";
+ search += "and AA.username like '%'+@reportname+'%' ";
dynamicParams.Add("@reportname", reportname);
}
if (reportopendate != "" && reportopendate != null)
{
- search += "and A.lm_date between @reportopendate and @reportclosedate ";
+ search += "and AA.lm_date between @reportopendate and @reportclosedate ";
dynamicParams.Add("@reportopendate", reportopendate + " 00:00:00");
dynamicParams.Add("@reportclosedate", reportclosedate + " 23:59:59");
}
@@ -751,16 +751,22 @@
search = search.Substring(3);//鎴彇绱㈠紩2鍚庨潰鐨勫瓧绗�
// --------------鏌ヨ鎸囧畾鏁版嵁--------------
var total = 0; //鎬绘潯鏁�
- var sql = @"select W.wkshp_code,L.org_name as wkshp_name,A.wo_code,M.partcode,M.partname,M.partspec,T.stepcode,T.stepname,P.plan_qty,A.defect_qty,
- F.code as defect_code,F.name as defect_name,A.style,U.username as lm_user,A.lm_date
+ var sql = @"select * from(
+ select W.wkshp_code,L.org_name as wkshp_name,A.wo_code,M.partcode,M.partname,M.partspec,T.stepcode,T.stepname,P.plan_qty,A.defect_qty,
+ A.defect_code,
+ STUFF((SELECT ',' + P.name
+ FROM TDefect P
+ WHERE CHARINDEX(',' + P.code + ',', ',' + A.defect_code + ',') > 0
+ FOR XML PATH('')), 1, 1, '') AS defect_name,
+ A.style,U.username as lm_user,A.lm_date
from CSR_WorkRecord_Defect A
inner join TK_Wrk_Step P on A.wo_code=P.wo_code and A.step_code=P.step_code
left join TMateriel_Info M on A.partnumber=M.partcode
left join TStep T on A.step_code=T.stepcode
- left join TDefect F on A.defect_code=F.code
left join TUser U on A.lm_user=U.usercode
left join TK_Wrk_Man W on A.wo_code=W.wo_code
left join TOrganization L on W.wkshp_code=L.org_code
+ ) as AA
where " + search;
var data = DapperHelper.GetPageList<object>(sql, dynamicParams, prop, order, startNum, endNum, out total);
mes.code = "200";
@@ -788,52 +794,52 @@
{
if (wkshopcode != "" && wkshopcode != null)
{
- search += "and W.wkshp_code=@wkshopcode ";
+ search += "and AA.wkshp_code=@wkshopcode ";
dynamicParams.Add("@wkshopcode", wkshopcode);
}
if (wocode != "" && wocode != null)
{
- search += "and A.wo_code like '%'+@wocode+'%' ";
+ search += "and AA.wo_code like '%'+@wocode+'%' ";
dynamicParams.Add("@wocode", wocode);
}
if (partcode != "" && partcode != null)
{
- search += "and M.partcode like '%'+@partcode+'%' ";
+ search += "and AA.partcode like '%'+@partcode+'%' ";
dynamicParams.Add("@partcode", partcode);
}
if (partname != "" && partname != null)
{
- search += "and M.partname like '%'+@partname+'%' ";
+ search += "and AA.partname like '%'+@partname+'%' ";
dynamicParams.Add("@partname", partname);
}
if (partspec != "" && partspec != null)
{
- search += "and M.partspec like '%'+@partspec+'%' ";
+ search += "and AA.partspec like '%'+@partspec+'%' ";
dynamicParams.Add("@partspec", partspec);
}
if (stepname != "" && stepname != null)
{
- search += "and T.stepname like '%'+@stepname+'%' ";
+ search += "and AA.stepname like '%'+@stepname+'%' ";
dynamicParams.Add("@stepname", stepname);
}
if (defectcode != "" && defectcode != null)
{
- search += "and F.code like '%'+@defectcode+'%' ";
+ search += "and AA.code like '%'+@defectcode+'%' ";
dynamicParams.Add("@defectcode", defectcode);
}
if (defectname != "" && defectname != null)
{
- search += "and F.name like '%'+@defectname+'%' ";
+ search += "and AA.name like '%'+@defectname+'%' ";
dynamicParams.Add("@defectname", defectname);
}
if (reportname != "" && reportname != null)
{
- search += "and U.username like '%'+@reportname+'%' ";
+ search += "and AA.username like '%'+@reportname+'%' ";
dynamicParams.Add("@reportname", reportname);
}
if (reportopendate != "" && reportopendate != null)
{
- search += "and A.lm_date between @reportopendate and @reportclosedate ";
+ search += "and AA.lm_date between @reportopendate and @reportclosedate ";
dynamicParams.Add("@reportopendate", reportopendate + " 00:00:00");
dynamicParams.Add("@reportclosedate", reportclosedate + " 23:59:59");
}
@@ -845,17 +851,25 @@
search = search.Substring(3);//鎴彇绱㈠紩2鍚庨潰鐨勫瓧绗�
// --------------鏌ヨ鎸囧畾鏁版嵁--------------
var total = 0; //鎬绘潯鏁�
- var sql = @"select W.wkshp_code as 杞﹂棿缂栫爜,L.org_name as 杞﹂棿鍚嶇О,A.wo_code as 宸ュ崟缂栧彿,M.partcode as 浜у搧缂栫爜,M.partname as 浜у搧鍚嶇О,M.partspec as 浜у搧瑙勬牸,T.stepcode as 宸ュ簭缂栫爜,
- T.stepname as 宸ュ簭鍚嶇О,P.plan_qty as 浠诲姟鏁伴噺,A.defect_qty as 涓嶈壇鏁伴噺,F.code as 缂洪櫡浠ｇ爜,F.name as 缂洪櫡鍚嶇О,
- (case when A.style='B' then '鎶ュ伐' when A.style='S' then '鏀舵枡' end) as '鎿嶄綔绫诲瀷',U.username as 鎿嶄綔浜哄憳,A.lm_date as 鎿嶄綔鏃堕棿
+ var sql = @"select AA.wkshp_code as 杞﹂棿缂栫爜,AA.wkshp_name as 杞﹂棿鍚嶇О,AA.wo_code as 宸ュ崟缂栧彿,AA.partcode as 浜у搧缂栫爜,AA.partname as 浜у搧鍚嶇О,AA.partspec as 浜у搧瑙勬牸,AA.stepcode as 宸ュ簭缂栫爜,
+ AA.stepname as 宸ュ簭鍚嶇О,AA.plan_qty as 浠诲姟鏁伴噺,AA.defect_qty as 涓嶈壇鏁伴噺,AA.defect_code as 缂洪櫡浠ｇ爜,AA.defect_name as 缂洪櫡鍚嶇О,
+ (case when AA.style='B' then '鎶ュ伐' when AA.style='S' then '鏀舵枡' end) as '鎿嶄綔绫诲瀷',AA.username as 鎿嶄綔浜哄憳,AA.lm_date as 鎿嶄綔鏃堕棿
+ from(
+ select W.wkshp_code,L.org_name as wkshp_name,A.wo_code,M.partcode,M.partname,M.partspec,T.stepcode,T.stepname,P.plan_qty,A.defect_qty,
+ A.defect_code,
+ STUFF((SELECT ',' + P.name
+ FROM TDefect P
+ WHERE CHARINDEX(',' + P.code + ',', ',' + A.defect_code + ',') > 0
+ FOR XML PATH('')), 1, 1, '') AS defect_name,
+ A.style,U.username,A.lm_date
from CSR_WorkRecord_Defect A
inner join TK_Wrk_Step P on A.wo_code=P.wo_code and A.step_code=P.step_code
left join TMateriel_Info M on A.partnumber=M.partcode
left join TStep T on A.step_code=T.stepcode
- left join TDefect F on A.defect_code=F.code
left join TUser U on A.lm_user=U.usercode
left join TK_Wrk_Man W on A.wo_code=W.wo_code
- left join TOrganization L on W.wkshp_code=L.org_code
+ left join TOrganization L on W.wkshp_code=L.org_code
+ ) as AA
where " + search;
DataTable data = DapperHelper.selectdata(sql, dynamicParams);
data.TableName = "Table"; //璁剧疆DataTable鐨勫悕绉�
--
Gitblit v1.9.3