From 9c634fd767aec36ef97c3a814bf7a29c67d20ee1 Mon Sep 17 00:00:00 2001
From: yl <ykxkd@outlook.com>
Date: 星期五, 29 三月 2024 10:57:35 +0800
Subject: [PATCH] 修改单据重复显示问题，增加工单源单id关联
---
VueWebApi/DLL/DAL/AppAnalyticsDAL.cs | 349 +++++++++++++++++++++++++++++++++++++++++++++++++++------
1 files changed, 308 insertions(+), 41 deletions(-)
diff --git a/VueWebApi/DLL/DAL/AppAnalyticsDAL.cs b/VueWebApi/DLL/DAL/AppAnalyticsDAL.cs
index e78eac8..5e1c401 100644
--- a/VueWebApi/DLL/DAL/AppAnalyticsDAL.cs
+++ b/VueWebApi/DLL/DAL/AppAnalyticsDAL.cs
@@ -5,6 +5,7 @@
using System.Data.SqlClient;
using System.Linq;
using System.Web;
+using VueWebApi.Models;
using VueWebApi.Tools;
namespace VueWebApi.DLL.DAL
@@ -19,41 +20,50 @@
public static SqlParameter[] parameters; //瀹氫箟鍏ㄥ眬SqlParameter鍙傛暟鏁扮粍
#region[App鐢熶骇鐪嬫澘]
- public static ToMessage ProductionKanban(string usercode, string startopendate, string endclosedate,string defecttype)
+ public static ToMessage ProductionKanban(string stu_torgcode,string stu_torgtypecode,string usercode, string startopendate, string endclosedate,string defecttype)
{
Dictionary<object, object> dList = new Dictionary<object, object>();
var dynamicParams = new DynamicParameters();
string search = "";
try
{
+ switch (stu_torgtypecode)
+ {
+ case "":
+ break;
+ case "D":
+ search += "and L.org_code=@stu_torgcode ";
+ dynamicParams.Add("@stu_torgcode", stu_torgcode);
+ break;
+ case "W":
+ search += "and C.org_code=@stu_torgcode ";
+ dynamicParams.Add("@stu_torgcode", stu_torgcode);
+ break;
+ default:
+ break;
+ }
// --------------鐢熶骇鎬昏--------------
var sql = @"select
- sum(CASE AA.clmname WHEN '璁″垝鏁伴噺' THEN AA.clmqty ELSE 0 END) as 'plan_qty',
- sum(CASE AA.clmname WHEN '瀹屽伐鏁伴噺' THEN AA.clmqty ELSE 0 END) as 'end_qty',
- sum(CASE AA.clmname WHEN '鍚堟牸浜у嚭' THEN AA.clmqty ELSE 0 END) as 'good_qty',
- sum(CASE AA.clmname WHEN '缂洪櫡浜у嚭' THEN AA.clmqty ELSE 0 END) as 'defect_qty'
- from(
- select isnull(sum(A.plan_qty),0) as clmqty,'璁″垝鏁伴噺'as clmname from TK_Wrk_Man A
- where A.lm_date between @startopendate and @endclosedate
- union all
- select (isnull(sum(S.report_qty),0)+isnull(sum(S.ng_qty),0)+isnull(sum(S.bad_qty),0)) as endqty,'瀹屽伐鏁伴噺' as 瀹屽伐鏁伴噺 from TK_Wrk_Man A
- inner join TK_Wrk_Record R on A.wo_code=R.wo_code
- inner join TK_Wrk_RecordSub S on R.id=S.m_id
- inner join TK_Wrk_Step P on A.wo_code=P.wo_code and R.step_code=P.step_code and P.isend='Y'
- where A.lm_date between @startopendate and @endclosedate
- union all
- select isnull(sum(S.report_qty),0) as report_qty,'鍚堟牸浜у嚭' as 鍚堟牸浜у嚭 from TK_Wrk_Man A
- inner join TK_Wrk_Record R on A.wo_code=R.wo_code
- inner join TK_Wrk_RecordSub S on R.id=S.m_id
- inner join TK_Wrk_Step P on A.wo_code=P.wo_code and R.step_code=P.step_code and P.isend='Y'
- where A.lm_date between @startopendate and @endclosedate
- union all
- select (isnull(sum(S.ng_qty),0)+isnull(sum(S.bad_qty),0)) as ngqty,'缂洪櫡浜у嚭' as 缂洪櫡浜у嚭 from TK_Wrk_Man A
- inner join TK_Wrk_Record R on A.wo_code=R.wo_code
- inner join TK_Wrk_RecordSub S on R.id=S.m_id
- inner join TK_Wrk_Step P on A.wo_code=P.wo_code and R.step_code=P.step_code
- where A.lm_date between @startopendate and @endclosedate
- ) as AA";
+ sum(CASE AA.clmname WHEN '璁″垝鏁伴噺' THEN AA.clmqty ELSE 0 END) as 'plan_qty',
+ sum(CASE AA.clmname WHEN '鍚堟牸浜у嚭' THEN AA.clmqty ELSE 0 END) as 'good_qty',
+ sum(CASE AA.clmname WHEN '缂洪櫡浜у嚭' THEN AA.clmqty ELSE 0 END) as 'defect_qty'
+ from(
+ select isnull(sum(A.plan_qty),0) as clmqty,'璁″垝鏁伴噺'as clmname from TK_Wrk_Man A
+ left join TOrganization C on A.wkshp_code=C.org_code
+ left join TOrganization L on C.parent_id=L.id
+ where A.lm_date between @startopendate and @endclosedate "+search;
+ sql += @"union all
+ select isnull(sum(P.good_qty),0) as report_qty,'鍚堟牸浜у嚭' as 鍚堟牸浜у嚭 from TK_Wrk_Man A
+ inner join TK_Wrk_Step P on A.wo_code=P.wo_code and P.isend='Y'
+ left join TOrganization C on A.wkshp_code=C.org_code
+ left join TOrganization L on C.parent_id=L.id
+ where A.lm_date between @startopendate and @endclosedate "+search;
+ sql += @"union all
+ select (isnull(sum(P.ng_qty),0)+isnull(sum(P.bad_qty),0)) as ngqty,'缂洪櫡浜у嚭' as 缂洪櫡浜у嚭 from TK_Wrk_Man A
+ inner join TK_Wrk_Step P on A.wo_code=P.wo_code
+ left join TOrganization C on A.wkshp_code=C.org_code
+ left join TOrganization L on C.parent_id=L.id
+ where A.lm_date between @startopendate and @endclosedate "+search+") as AA";
dynamicParams.Add("@startopendate", startopendate + " 00:00:00");
dynamicParams.Add("@endclosedate", endclosedate + " 23:59:59");
var data0 = DapperHelper.selectdata(sql, dynamicParams);
@@ -63,9 +73,9 @@
select COUNT(A.id) as cluntqty,
case when A.status='START' then '鎵ц涓�' when A.status='CLOSED' then '宸茬粨鏉�' else '鏈紑濮�' end as wo_status
from TK_Wrk_Man A
- where A.lm_date between @startopendate and @endclosedate
- group by A.status
- ) as AA group by AA.wo_status";
+ left join TOrganization C on A.wkshp_code=C.org_code
+ left join TOrganization L on C.parent_id=L.id
+ where A.lm_date between @startopendate and @endclosedate "+search+" group by A.status) as AA group by AA.wo_status";
var data1 = DapperHelper.selectdata(sql1, dynamicParams);
dList.Add("dt1", data1);
// --------------缂洪櫡缁熻--------------
@@ -73,20 +83,22 @@
switch (defecttype)
{
case "prt": //鎸変骇鍝佺粺璁�
- sql2 = @"select top 5 isnull(sum(A.plan_qty),0) as plan_qty,M.partname,isnull(sum(B.defect_qty),0) as defect_qty from TK_Wrk_Man A
- inner join CSR_WorkRecord_Defect B on A.wo_code=B.wo_code and A.materiel_code=B.partnumber
+ sql2 = @"select top 5 sum(AA.plan_qty) as plan_qty,AA.partname,sum(AA.ng_qty)+sum(AA.bad_qty) as defect_qty from (
+ select A.plan_qty,M.partname,S.ng_qty,S.bad_qty from TK_Wrk_Man A
+ inner join (select wo_code, isnull(sum(ng_qty),0) as ng_qty,isnull(sum(bad_qty),0) as bad_qty from TK_Wrk_Step where ng_qty>0 or bad_qty>0 group by wo_code) S on A.wo_code=S.wo_code
inner join TMateriel_Info M on A.materiel_code=M.partcode
- where A.lm_date between @startopendate and @endclosedate and B.defect_qty>0
- group by M.partname
- order by defect_qty desc";
+ left join TOrganization C on A.wkshp_code=C.org_code
+ left join TOrganization L on C.parent_id=L.id
+ where A.lm_date between @startopendate and @endclosedate "+search+") as AA group by AA.partname order by defect_qty desc";
break;
case "stp"://鎸夊伐搴忕粺璁�
- sql2 = @"select top 5 isnull(sum(A.plan_qty),0) as plan_qty,S.stepname,isnull(sum(B.defect_qty),0) as defect_qty from TK_Wrk_Step A
- inner join CSR_WorkRecord_Defect B on A.wo_code=B.wo_code and A.step_code=B.step_code
- inner join TStep S on A.step_code=S.stepcode
- where A.lm_date between @startopendate and @endclosedate and B.defect_qty>0
- group by S.stepname
- order by defect_qty desc";
+ sql2 = @"select top 5 sum(AA.plan_qty) as plan_qty,AA.stepname,sum(AA.ng_qty)+sum(AA.bad_qty) as defect_qty from (
+ select A.plan_qty,T.stepname,S.ng_qty,S.bad_qty from TK_Wrk_Man A
+ inner join (select wo_code,step_code, isnull(sum(ng_qty),0) as ng_qty,isnull(sum(bad_qty),0) as bad_qty from TK_Wrk_Step where ng_qty>0 or bad_qty>0 group by wo_code,step_code) S on A.wo_code=S.wo_code
+ inner join TStep T on S.step_code=T.stepcode
+ left join TOrganization C on A.wkshp_code=C.org_code
+ left join TOrganization L on C.parent_id=L.id
+ where A.lm_date between @startopendate and @endclosedate "+search+") as AA group by AA.stepname order by defect_qty desc";
break;
default:
break;
@@ -107,5 +119,260 @@
return mes;
}
#endregion
+
+ #region[App鐢熶骇杩涘害鐪嬫澘鍗曟嵁淇℃伅]
+ public static ToMessage ProductionScheduleKanban(string stu_torgcode,string stu_torgtypecode, string ordertype, string partcode, string startopendate, string endclosedate)
+ {
+ Dictionary<object, object> dList = new Dictionary<object, object>();
+ List<AppScheduleKanban> list = new List<AppScheduleKanban>();
+ var dynamicParams = new DynamicParameters();
+ string search = "", search1 = "",sql ="";
+ DataTable dt;
+ 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 C.org_code=@stu_torgcode ";
+ dynamicParams.Add("@stu_torgcode", stu_torgcode);
+ break;
+ default:
+ break;
+ }
+ //鏍规嵁鏌ヨ鏉′欢,鏌ユ壘绗﹀悎瑕佹眰鐨勫崟鎹�
+ switch (ordertype)
+ {
+ case "SO": //鎸夐攢鍞鍗曠粺璁�
+ if (partcode != "" && partcode != null)
+ {
+ string[] selects = Array.ConvertAll<string, string>(partcode.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries), s => s.ToString()); //string鍒嗗壊杞瑂tring[]
+ search += " and A.materiel_code in @partcode ";
+ dynamicParams.Add("@partcode", selects.ToArray());
+ }
+ if (startopendate != "" && startopendate != null)
+ {
+ search += " and convert(varchar(100),A.saleOrderDate,21)>=@startopendate and convert(varchar(100),A.saleOrderDate,21)<=@endclosedate ";
+ dynamicParams.Add("@startopendate", startopendate + " 00:00:00");
+ dynamicParams.Add("@endclosedate", endclosedate + " 23:59:59");
+ }
+ //鑾峰彇鍗曟嵁淇℃伅
+ sql = @"select distinct saleOrderCode,A.saleOrderDate,sum(A.saleOrderqty) as saleOrderqty
+ from TKimp_Ewo A
+ where A.saleOrderCode<>'' " + search+ " group by A.saleOrderCode,A.saleOrderDate order by A.saleOrderDate desc";
+ dt = DapperHelper.selectlist(sql, dynamicParams);
+ if (dt.Rows.Count > 0)
+ {
+ for (int i = 0; i < dt.Rows.Count; i++)
+ {
+ AppScheduleKanban cmp = new AppScheduleKanban();
+ cmp.ordercode = dt.Rows[i]["saleOrderCode"].ToString();
+ cmp.orderqty = dt.Rows[i]["saleOrderqty"].ToString();
+ cmp.children = new List<AppScheduleKanbanSub>();
+ //鑾峰彇鍗曟嵁涓嬩骇鍝佸畬鎴愪俊鎭�
+ sql = @"select AA.wo,AA.partcode,AA.partname,AA.partspec,AA.saleOrderqty,isnull(BB.good_qty,0) as good_qty from(
+ select distinct E.wo,T.partcode,T.partname,T.partspec,sum(E.saleOrderqty) as saleOrderqty
+ from TKimp_Ewo E
+ left join TMateriel_Info T on E.materiel_code=T.partcode
+ where E.saleOrderCode=@saleOrderCode
+ group by E.wo,T.partcode,T.partname,T.partspec
+ ) as AA
+ left join
+ (
+ select M.m_po,sum(S.good_qty) as good_qty from TK_Wrk_Man M
+ inner join TK_Wrk_Step S on M.wo_code=S.wo_code
+ where S.isend='Y' and S.good_qty>0
+ group by M.m_po
+ ) as BB on AA.wo=BB.m_po";
+ dynamicParams.Add("@saleOrderCode", dt.Rows[i]["saleOrderCode"].ToString());
+ var data = DapperHelper.selectdata(sql, dynamicParams);
+ decimal good_qty = data.AsEnumerable().Select(d => d.Field<decimal>("good_qty")).Sum(); //鎶ュ伐鎬绘暟閲�
+ cmp.ordergoodqty = good_qty.ToString();
+ for (int j = 0; j < data.Rows.Count; j++)
+ {
+ AppScheduleKanbanSub cbp = new AppScheduleKanbanSub();
+ cbp.code= data.Rows[j]["partcode"].ToString();
+ cbp.name = data.Rows[j]["partname"].ToString();
+ cbp.spec = data.Rows[j]["partspec"].ToString();
+ cbp.planqty= data.Rows[j]["saleOrderqty"].ToString();
+ cbp.goodqty = data.Rows[j]["good_qty"].ToString();
+ cmp.children.Add(cbp);
+ }
+ list.Add(cmp);
+ }
+ mes.code = "200";
+ mes.Message = "success!";
+ mes.data = list;
+ return mes;
+ }
+ else
+ {
+ mes.code = "300";
+ mes.count = 0;
+ mes.Message = "鏃犵鍚堣姹傚崟鎹紒";
+ mes.data = null;
+ }
+ break;
+ case "MO": //鎸夌敓浜ц鍗曠粺璁�
+ if (partcode != "" && partcode != null)
+ {
+ string[] selects = Array.ConvertAll<string, string>(partcode.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries), s => s.ToString()); //string鍒嗗壊杞瑂tring[]
+ search += " and A.materiel_code in @partcode ";
+ dynamicParams.Add("@partcode", selects.ToArray());
+ }
+ if (startopendate != "" && startopendate != null)
+ {
+ search += " and convert(varchar(100),A.createdate,21)>=@startopendate and convert(varchar(100),A.createdate,21)<=@endclosedate ";
+ dynamicParams.Add("@startopendate", startopendate + " 00:00:00");
+ dynamicParams.Add("@endclosedate", endclosedate + " 23:59:59");
+ }
+ //鑾峰彇鍗曟嵁淇℃伅
+ sql = @"select distinct A.wo,A.createdate,sum(A.qty) as orderqty
+ from TKimp_Ewo A
+ where 1=1 "+search+" group by A.wo,A.createdate order by A.createdate desc";
+ dt = DapperHelper.selectlist(sql, dynamicParams);
+ if (dt.Rows.Count > 0)
+ {
+ for (int i = 0; i < dt.Rows.Count; i++)
+ {
+ AppScheduleKanban cmp = new AppScheduleKanban();
+ cmp.ordercode = dt.Rows[i]["wo"].ToString();
+ cmp.orderqty = dt.Rows[i]["orderqty"].ToString();
+ cmp.children = new List<AppScheduleKanbanSub>();
+ //鑾峰彇鍗曟嵁涓嬩骇鍝佸畬鎴愪俊鎭�
+ sql = @"select distinct E.wo,T.partcode,T.partname,T.partspec,sum(E.qty) as orderqty,isnull(BB.good_qty,0) as good_qty
+ from TKimp_Ewo E
+ left join TMateriel_Info T on E.materiel_code=T.partcode
+ left join (
+ select M.m_po,sum(S.good_qty) as good_qty from TK_Wrk_Man M
+ inner join TK_Wrk_Step S on M.wo_code=S.wo_code
+ where S.isend='Y' and S.good_qty>0
+ group by M.m_po
+ ) as BB on E.wo=BB.m_po
+ where E.wo=@wo
+ group by E.wo,T.partcode,T.partname,T.partspec,BB.good_qty";
+ dynamicParams.Add("@wo", dt.Rows[i]["wo"].ToString());
+ var data = DapperHelper.selectdata(sql, dynamicParams);
+ decimal good_qty = data.AsEnumerable().Select(d => d.Field<decimal>("good_qty")).Sum(); //鎶ュ伐鎬绘暟閲�
+ cmp.ordergoodqty = good_qty.ToString();
+ for (int j = 0; j < data.Rows.Count; j++)
+ {
+ AppScheduleKanbanSub cbp = new AppScheduleKanbanSub();
+ cbp.code = data.Rows[j]["partcode"].ToString();
+ cbp.name = data.Rows[j]["partname"].ToString();
+ cbp.spec = data.Rows[j]["partspec"].ToString();
+ cbp.planqty = data.Rows[j]["orderqty"].ToString();
+ cbp.goodqty = data.Rows[j]["good_qty"].ToString();
+ cmp.children.Add(cbp);
+ }
+ list.Add(cmp);
+ }
+ mes.code = "200";
+ mes.Message = "success!";
+ mes.data = list;
+ return mes;
+ }
+ else
+ {
+ mes.code = "300";
+ mes.count = 0;
+ mes.Message = "鏃犵鍚堣姹傚崟鎹紒";
+ mes.data = null;
+ }
+ break;
+ case "PO": //鎸夌敓浜у伐鍗曠粺璁�
+ if (partcode != "" && partcode != null)
+ {
+ string[] selects = Array.ConvertAll<string, string>(partcode.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries), s => s.ToString()); //string鍒嗗壊杞瑂tring[]
+ search += " and A.materiel_code in @partcode ";
+ dynamicParams.Add("@partcode", selects.ToArray());
+ }
+ if (startopendate != "" && startopendate != null)
+ {
+ search += " and convert(varchar(100),A.lm_date,21)>=@startopendate and convert(varchar(100),A.lm_date,21)<=@endclosedate ";
+ dynamicParams.Add("@startopendate", startopendate + " 00:00:00");
+ dynamicParams.Add("@endclosedate", endclosedate + " 23:59:59");
+ }
+ //鑾峰彇鍗曟嵁淇℃伅
+ sql = @"select distinct A.wo_code,T.partcode,T.partname,T.partspec,A.lm_date,sum(A.plan_qty) as orderqty
+ from TK_Wrk_Man A
+ left join TOrganization C on A.wkshp_code=C.org_code
+ left join TOrganization L on C.parent_id=L.id
+ left join TMateriel_Info T on A.materiel_code=T.partcode
+ where 1=1 " + search+search1+" group by A.wo_code,T.partcode,T.partname,T.partspec,A.lm_date order by A.lm_date desc";
+ dt = DapperHelper.selectlist(sql, dynamicParams);
+ if (dt.Rows.Count > 0)
+ {
+ for (int i = 0; i < dt.Rows.Count; i++)
+ {
+ AppScheduleKanban cmp = new AppScheduleKanban();
+ cmp.ordercode = dt.Rows[i]["wo_code"].ToString();
+ cmp.partcode= dt.Rows[i]["partcode"].ToString();
+ cmp.partname = dt.Rows[i]["partname"].ToString();
+ cmp.partspec = dt.Rows[i]["partspec"].ToString();
+ cmp.orderqty = dt.Rows[i]["orderqty"].ToString();
+ cmp.children = new List<AppScheduleKanbanSub>();
+ //鑾峰彇鍗曟嵁涓嬩骇鍝佸畬鎴愪俊鎭�
+ sql = @"select M.wo_code,T.stepcode,T.stepname,S.plan_qty,S.good_qty,S.isend
+ from TK_Wrk_Man M
+ inner join TK_Wrk_Step S on M.wo_code=S.wo_code
+ left join TStep T on S.step_code=T.stepcode
+ where M.wo_code=@wo_code
+ order by S.seq ";
+ dynamicParams.Add("@wo_code", dt.Rows[i]["wo_code"].ToString());
+ var data = DapperHelper.selectdata(sql, dynamicParams);
+ if (data.Rows.Count > 0)
+ {
+ cmp.ordergoodqty = data.AsEnumerable().Where(t => t.Field<string>("isend") == "Y").ToList().Select(c => c.Field<decimal>("good_qty")).First().ToString();
+ for (int j = 0; j < data.Rows.Count; j++)
+ {
+ AppScheduleKanbanSub cbp = new AppScheduleKanbanSub();
+ cbp.code = data.Rows[j]["stepcode"].ToString();
+ cbp.name = data.Rows[j]["stepname"].ToString();
+ cbp.spec = data.Rows[j]["isend"].ToString();
+ cbp.planqty = data.Rows[j]["plan_qty"].ToString();
+ cbp.goodqty = data.Rows[j]["good_qty"].ToString();
+ cmp.children.Add(cbp);
+ }
+ list.Add(cmp);
+ }
+ else
+ {
+ cmp.ordergoodqty = "0";
+ list.Add(cmp);
+ }
+ }
+ mes.code = "200";
+ mes.Message = "success!";
+ mes.data = list;
+ return mes;
+ }
+ else
+ {
+ mes.code = "300";
+ mes.count = 0;
+ mes.Message = "鏃犵鍚堣姹傚崟鎹紒";
+ mes.data = null;
+ }
+ break;
+ default:
+ break;
+ }
+ }
+ 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