1.优化生产进度报表查询语句

yl

2024-03-15 72fb3e7707c38e113eeab778565e4da209f04a3d

1.优化生产进度报表查询语句

 VueWebApi/Controllers/KanBanManagerentController.cs

@@ -24,7 +24,7 @@
        /// </summary>
        /// <returns></returns>
        [Route(template: "ProductionStockedInLeftTop")]
        [HttpPost]
        [HttpGet]
        public HttpResponseMessage ProductionStockedInLeftTop()
        {
            mes = KanBanManagerentBLL.ProductionStockedInLeftTop();
@@ -38,7 +38,7 @@
        /// </summary>
        /// <returns></returns>
        [Route(template: "ProductConsignmentLeftBottom")]
        [HttpPost]
        [HttpGet]
        public HttpResponseMessage ProductConsignmentLeftBottom()
        {
            mes = KanBanManagerentBLL.ProductConsignmentLeftBottom();
@@ -52,7 +52,7 @@
        /// </summary>
        /// <returns></returns>
        [Route(template: "ProductionStockedInRightTop")]
        [HttpPost]
        [HttpGet]
        public HttpResponseMessage ProductionStockedInRightTop()
        {
            mes = KanBanManagerentBLL.ProductionStockedInRightTop();
@@ -66,7 +66,7 @@
        /// </summary>
        /// <returns></returns>
        [Route(template: "ProductionStockedInRightBottom")]
        [HttpPost]
        [HttpGet]
        public HttpResponseMessage ProductionStockedInRightBottom()
        {
            mes = KanBanManagerentBLL.ProductionStockedInRightBottom();
@@ -82,7 +82,7 @@
        /// </summary>
        /// <returns></returns>
        [Route(template: "ProcureOutsourcLeftTop")]
        [HttpPost]
        [HttpGet]
        public HttpResponseMessage ProcureOutsourcLeftTop()
        {
            mes = KanBanManagerentBLL.ProcureOutsourcLeftTop();
@@ -96,7 +96,7 @@
        /// </summary>
        /// <returns></returns>
        [Route(template: "ProcureOutsourcLeftBottom")]
        [HttpPost]
        [HttpGet]
        public HttpResponseMessage ProcureOutsourcLeftBottom()
        {
            mes = KanBanManagerentBLL.ProcureOutsourcLeftBottom();
@@ -110,7 +110,7 @@
        /// </summary>
        /// <returns></returns>
        [Route(template: "ProcureOutsourcRightTop")]
        [HttpPost]
        [HttpGet]
        public HttpResponseMessage ProcureOutsourcRightTop()
        {
            mes = KanBanManagerentBLL.ProcureOutsourcRightTop();
@@ -124,7 +124,7 @@
        /// </summary>
        /// <returns></returns>
        [Route(template: "ProcureOutsourcRightBottom")]
        [HttpPost]
        [HttpGet]
        public HttpResponseMessage ProcureOutsourcRightBottom()
        {
            mes = KanBanManagerentBLL.ProcureOutsourcRightBottom();

 VueWebApi/DLL/DAL/ReportManagerDAL.cs

@@ -1637,13 +1637,13 @@
                    switch (status)
                    {
                        case "START":
                            search += "and AA.status='START' ";
                            search += "and AA.status='执行中' ";
                            break;
                        case "CLOSED":
                            search += "and AA.status='CLOSED' ";
                            search += "and AA.status='已完成' ";
                            break;
                        default:
                            search += "and AA.status<>'START' and AA.status<>'CLOSED' ";
                            search += "and AA.status='未开始' ";
                            break;
                    }
                }
@@ -1695,23 +1695,28 @@
                search = search.Substring(3);//截取索引2后面的字符
                // --------------查询指定数据--------------
                var total = 0; //总条数
                var sql = @"select  AA.saleOrderCode,AA.m_po,AA.wkshp_code,AA.wkshp_name,AA.wo_code,
                            (case when AA.status='START' then '执行中' when AA.status='CLOSED' then '已完成' else '未开始' end) as status,
                            AA.lm_date,AA.route_code,AA.route_name,AA.partcode,AA.partname,AA.partspec,AA.plan_qty,left(AA.concat_name,len(concat_name)-1) as concat_name  from
                var sql = @"select top 100 percent AA.saleOrderCode,AA.m_po,AA.wkshp_code,AA.wkshp_name,AA.wo_code,AA.status,AA.lm_date,
                            AA.route_code,AA.route_name,AA.partcode,AA.partname,AA.partspec,AA.plan_qty,left(AA.concat_name,len(concat_name)-1) as concat_name 
                            from (
                                 select E.saleOrderCode,W.m_po,W.wo_code,W.wkshp_code,F.org_name as wkshp_name,
                                 case when W.status='START' then '执行中' when W.status='CLOSED' then '已完成' else '未开始' end as status,
                                 W.lm_date,W.route_code,R.name as route_name,
                                 P.partcode,P.partname,P.partspec,W.plan_qty,
                            (
                            select E.saleOrderCode,W.m_po,m.wo_code,W.wkshp_code,F.org_name as wkshp_name,W.status,W.lm_date,W.route_code,R.name as route_name,P.partcode,P.partname,P.partspec,m.plan_qty,
                            (select s.stepname+'/'+cast(cast(n.good_qty as decimal(18,2)) AS varchar(50))+',' from TK_Wrk_Step n
                                   select s.stepname+'/'+cast(cast(n.good_qty as decimal(18,2)) AS varchar(50))+',' 
                                   from TK_Wrk_Step n
                             inner join TStep S on n.step_code=S.stepcode
                             where n.wo_code=m.wo_code for xml path('')) as concat_name
                            from TK_Wrk_Step m
                            inner join TK_Wrk_Man W on m.wo_code=W.wo_code
                            left join TKimp_Ewo E on W.m_po=E.wo
                            inner join TMateriel_Info P on W.materiel_code=p.partcode
                            inner join TFlw_Rout R on W.route_code=R.code
                                   where n.wo_code=W.wo_code for xml path('')
                                 ) as concat_name   
                                 from TK_Wrk_Man W
                                 left join (
                                   select wo,saleOrderCode   from TKimp_Ewo 
                                 ) as E on W.m_po=E.wo
                                 left join TMateriel_Info P on W.materiel_code=p.partcode
                                 left join TFlw_Rout R on W.route_code=R.code
                            left join  TOrganization F on W.wkshp_code=F.org_code
                            group by E.saleOrderCode,W.m_po,m.wo_code,W.wkshp_code,F.org_name,W.status,W.lm_date,W.route_code,R.name,P.partcode,P.partname,P.partspec,m.plan_qty
                            ) AA 
                            where " + search;
                                 group by E.saleOrderCode,W.m_po,W.wo_code,W.wkshp_code,F.org_name,W.status,W.lm_date,W.route_code,R.name,P.partcode,P.partname,P.partspec,W.plan_qty
                            ) as AA where " + search;
                var data = DapperHelper.GetPageList<object>(sql, dynamicParams, prop, order, startNum, endNum, out total);
                mes.code = "200";
                mes.Message = "查询成功!";
@@ -1746,13 +1751,13 @@
                    switch (status)
                    {
                        case "START":
                            search += "and AA.status='START' ";
                            search += "and AA.status='执行中' ";
                            break;
                        case "CLOSED":
                            search += "and AA.status='CLOSED' ";
                            search += "and AA.status='已完成' ";
                            break;
                        default:
                            search += "and AA.status<>'START' and AA.status<>'CLOSED' ";
                            search += "and AA.status='未开始' ";
                            break;
                    }
                }
@@ -1804,23 +1809,30 @@
                search = search.Substring(3);//截取索引2后面的字符
                // --------------查询指定数据--------------
                var total = 0; //总条数
                var sql = @"select  AA.saleOrderCode as 销售订单号,AA.m_po as 生产订单号,AA.wkshp_code as 车间编码,AA.wkshp_name as 车间名称,AA.wo_code as 生产工单号,
                            (case when AA.status='START' then '执行中' when AA.status='CLOSED' then '已完成' else '未开始' end) as 工单状态,
                            AA.lm_date as 单据日期,AA.route_code as 工艺路线编号,AA.route_name as 工艺路线名称,
                            AA.partcode as 产品编码,AA.partname as 产品名称,AA.partspec as 产品规格,AA.plan_qty as 任务数量,left(AA.concat_name,len(concat_name)-1) as 生产进度  from

                var sql = @"select top 100 percent AA.saleOrderCode as 销售订单号,AA.m_po as 生产订单号,AA.wkshp_code as 车间编码,AA.wkshp_name as 车间名称,AA.wo_code as 生产工单号,
                             AA.status as 工单状态,AA.lm_date as 单据日期,AA.route_code as 工艺路线编号,AA.route_name as 工艺路线名称,
                             AA.partcode as 产品编码,AA.partname as 产品名称,AA.partspec as 产品规格,AA.plan_qty as 任务数量,left(AA.concat_name,len(concat_name)-1) as 生产进度 
                             from (
                                 select E.saleOrderCode,W.m_po,W.wo_code,W.wkshp_code,F.org_name as wkshp_name,
                                 case when W.status='START' then '执行中' when W.status='CLOSED' then '已完成' else '未开始' end as status,
                                 W.lm_date,W.route_code,R.name as route_name,
                                 P.partcode,P.partname,P.partspec,W.plan_qty,
                            (
                            select E.saleOrderCode,W.m_po,m.wo_code,W.wkshp_code,F.org_name as wkshp_name,W.status,W.lm_date,W.route_code,R.name as route_name,P.partcode,P.partname,P.partspec,m.plan_qty,
                            (select s.stepname+'/'+cast(cast(n.good_qty as decimal(18,2)) AS varchar(50))+',' from TK_Wrk_Step n
                                   select s.stepname+'/'+cast(cast(n.good_qty as decimal(18,2)) AS varchar(50))+',' 
                                   from TK_Wrk_Step n
                             inner join TStep S on n.step_code=S.stepcode
                             where n.wo_code=m.wo_code for xml path('')) as concat_name
                            from TK_Wrk_Step m
                            inner join TK_Wrk_Man W on m.wo_code=W.wo_code
                            left join TKimp_Ewo E on W.m_po=E.wo
                            inner join TMateriel_Info P on W.materiel_code=p.partcode
                            inner join TFlw_Rout R on W.route_code=R.code
                                   where n.wo_code=W.wo_code for xml path('')
                                 ) as concat_name   
                                 from TK_Wrk_Man W
                                 left join (
                                   select wo,saleOrderCode   from TKimp_Ewo 
                                 ) as E on W.m_po=E.wo
                                 left join TMateriel_Info P on W.materiel_code=p.partcode
                                 left join TFlw_Rout R on W.route_code=R.code
                            left join  TOrganization F on W.wkshp_code=F.org_code
                            group by E.saleOrderCode,W.m_po,m.wo_code,W.wkshp_code,F.org_name,W.status,W.lm_date,W.route_code,R.name,P.partcode,P.partname,P.partspec,m.plan_qty
                            ) AA  
                                 group by E.saleOrderCode,W.m_po,W.wo_code,W.wkshp_code,F.org_name,W.status,W.lm_date,W.route_code,R.name,P.partcode,P.partname,P.partspec,W.plan_qty
                            ) as AA
                            where " + search;
                DataTable data = DapperHelper.selectdata(sql, dynamicParams);
                data.TableName = "Table"; //设置DataTable的名称