1.增加仓库看板接口 2.注释模具管理模块Excel下载模板 3.优化生产进度报表查询语句

yl

2024-03-15 45bdb4089c44841907614a2bdd8d694f382bd2d2

1.增加仓库看板接口
2.注释模具管理模块Excel下载模板
3.优化生产进度报表查询语句

 VueWebCoreApi/Controllers/KanBanManagerentController.cs

@@ -105,5 +105,63 @@
            return Json(mes);
        }
        #endregion



        #region[仓库看板,左上产品待入库列表]
        /// <summary>
        /// 仓库看板,左上产品待入库列表
        /// </summary>
        /// <returns></returns>
        [Route(template: "WareHouseTopLeftData")]
        [HttpGet]
        public JsonResult WareHouseTopLeftData()
        {
            mes = KanBanManagerentBLL.WareHouseTopLeftData();
            return Json(mes);
        }
        #endregion

        #region[仓库看板,左下产品待发货列表]
        /// <summary>
        /// 仓库看板,左下产品待发货列表
        /// </summary>
        /// <returns></returns>
        [Route(template: "WareHouseTopBottomData")]
        [HttpGet]
        public JsonResult WareHouseTopBottomData()
        {
            mes = KanBanManagerentBLL.WareHouseTopBottomData();
            return Json(mes);
        }
        #endregion

        #region[仓库看板,右上成品库、半成品库top5库存排行]
        /// <summary>
        /// 仓库看板,右上成品库、半成品库top5库存排行
        /// </summary>
        /// <returns></returns>
        [Route(template: "WareHouseRightTopData")]
        [HttpGet]
        public JsonResult WareHouseRightTopData()
        {
            mes = KanBanManagerentBLL.WareHouseRightTopData();
            return Json(mes);
        }
        #endregion

        #region[仓库看板,右下原料库库top5库存排行]
        /// <summary>
        /// 仓库看板,右下原料库库top5库存排行
        /// </summary>
        /// <returns></returns>
        [Route(template: "WareHouseRightBottomData")]
        [HttpGet]
        public JsonResult WareHouseRightBottomData()
        {
            mes = KanBanManagerentBLL.WareHouseRightBottomData();
            return Json(mes);
        }
        #endregion
    }
}

 VueWebCoreApi/DLL/BLL/KanBanManagerentBLL.cs

@@ -51,5 +51,35 @@
            return KanBanManagerentDAL.WorkShopCompreRightBottom();
        }
        #endregion



        #region[仓库看板,左上产品待入库列表]
        public static ToMessage WareHouseTopLeftData()
        {
            return KanBanManagerentDAL.WareHouseTopLeftData();
        }
        #endregion

        #region[仓库看板,左下产品待发货列表]
        public static ToMessage WareHouseTopBottomData()
        {
            return KanBanManagerentDAL.WareHouseTopBottomData();
        }
        #endregion

        #region[仓库看板,右上成品库、半成品库top5库存排行]
        public static ToMessage WareHouseRightTopData()
        {
            return KanBanManagerentDAL.WareHouseRightTopData();
        }
        #endregion

        #region[仓库看板,右下原料库库top5库存排行]
        public static ToMessage WareHouseRightBottomData()
        {
            return KanBanManagerentDAL.WareHouseRightBottomData();
        }
        #endregion
    }
}

 VueWebCoreApi/DLL/DAL/KanBanManagerentDAL.cs

@@ -208,5 +208,101 @@
            return mes;
        }
        #endregion



        #region[仓库看板,左上产品待入库列表]
        public static ToMessage WareHouseTopLeftData()
        {
            string sql = "";
            try
            {
                //获取仓库生产加工单待入库列表
                sql = @"select * from h_v_ALKKanBan_WareHouseTopLeft order by voucherdate";
                var data = DapperHelper.selecttable(sql);
                mes.code = "200";
                mes.message = "查询成功!";
                mes.data = data;
            }
            catch (Exception e)
            {
                mes.code = "300";
                mes.count = 0;
                mes.message = e.Message;
                mes.data = null;
            }
            return mes;
        }
        #endregion

        #region[仓库看板,左下产品待发货列表]
        public static ToMessage WareHouseTopBottomData()
        {
            string sql = "";
            try
            {
                //获取仓库生产加工单待入库列表
                sql = @"select * from h_v_ALKKanBan_WareHouseTopBottom order by voucherdate";
                var data = DapperHelper.selecttable(sql);
                mes.code = "200";
                mes.message = "查询成功!";
                mes.data = data;
            }
            catch (Exception e)
            {
                mes.code = "300";
                mes.count = 0;
                mes.message = e.Message;
                mes.data = null;
            }
            return mes;
        }
        #endregion

        #region[仓库看板,右上成品库、半成品库top5库存排行]
        public static ToMessage WareHouseRightTopData()
        {
            string sql = "";
            try
            {
                sql = @"select * from h_v_ALKKanBan_WareHouseRightTop";
                var data = DapperHelper.selecttable(sql);
                mes.code = "200";
                mes.message = "查询成功!";
                mes.data = data;
            }
            catch (Exception e)
            {
                mes.code = "300";
                mes.count = 0;
                mes.message = e.Message;
                mes.data = null;
            }
            return mes;
        }
        #endregion

        #region[仓库看板,右下原料库库top5库存排行]
        public static ToMessage WareHouseRightBottomData()
        {
            string sql = "";
            try
            {
                sql = @"select * from h_v_ALKKanBan_WareHouseRightBottom";
                var data = DapperHelper.selecttable(sql);
                mes.code = "200";
                mes.message = "查询成功!";
                mes.data = data;
            }
            catch (Exception e)
            {
                mes.code = "300";
                mes.count = 0;
                mes.message = e.Message;
                mes.data = null;
            }
            return mes;
        }
        #endregion
    }
}

 VueWebCoreApi/DLL/DAL/ReportManagerDAL.cs

@@ -20,24 +20,29 @@


        #region[生产进度报表]
        public static ToMessage ProductionScheduleReportSearch(string status, string wkshopcode, string wocode, string partcode, string partname, string partspec, string opendate, string closedate, int startNum, int endNum, string prop, string order)
        public static ToMessage ProductionScheduleReportSearch(string wkshopcode, string status, string wocode, string saleordercode, string routecode, string routename, string partcode, string partname, string partspec, string opendate, string closedate, int startNum, int endNum, string prop, string order)
        {
            var dynamicParams = new DynamicParameters();
            string search = "";
            try
            {
                if (wkshopcode != "" && wkshopcode != null)
                {
                    search += "and AA.wkshp_code=@wkshopcode ";
                    dynamicParams.Add("@wkshopcode", wkshopcode);
                }
                if (status != "" && status != null)
                {
                    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;
                    }
                }
@@ -46,15 +51,20 @@
                    search += "and AA.wo_code like '%'+@wocode+'%' ";
                    dynamicParams.Add("@wocode", wocode);
                }
                if (wkshopcode != "" && wkshopcode != null)
                if (saleordercode != "" && saleordercode != null)
                {
                    search += "and AA.wkshp_code=@wkshopcode ";
                    dynamicParams.Add("@wkshopcode", wkshopcode);
                    search += "and AA.saleOrderCode like '%'+@saleordercode+'%' ";
                    dynamicParams.Add("@saleordercode", saleordercode);
                }
                if (partcode != "" && partcode != null)
                if (routecode != "" && routecode != null)
                {
                    search += "and AA.partcode like '%'+@partcode+'%' ";
                    dynamicParams.Add("@partcode", partcode);
                    search += "and AA.route_code like '%'+@routecode+'%' ";
                    dynamicParams.Add("@routecode", routecode);
                }
                if (routename != "" && routename != null)
                {
                    search += "and AA.route_name like '%'+@routename+'%' ";
                    dynamicParams.Add("@routename", routename);
                }
                if (partcode != "" && partcode != null)
                {
@@ -84,22 +94,28 @@
                search = search.Substring(3);//截取索引2后面的字符
                // --------------查询指定数据--------------
                var total = 0; //总条数
                var sql = @"select  AA.saleOrderCode,AA.m_po,AA.wo_code,AA.wkshp_code,AA.wkshp_name,
                            (case when AA.status='START' then '执行中' when AA.status='CLOSED' then '已完成' else '未开始' end) as status,
                            AA.lm_date,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,m.wo_code,W.wkshp_code,F.torg_name as wkshp_name,W.status,W.lm_date,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
                             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
                            left join  TOrganization F on W.wkshp_code=F.torg_code
                            group by E.saleOrderCode,W.m_po,m.wo_code,W.wkshp_code,F.torg_name,W.status,W.lm_date,P.partcode,P.partname,P.partspec,m.plan_qty
                            ) AA  
                            where " + search;
                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 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=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,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 = "查询成功!";
@@ -118,24 +134,29 @@
        #endregion

        #region[生产进度报表导出]
        public static ToMessage ProductionScheduleReportExcelSearch(string status, string wkshopcode, string wocode,string partcode, string partname, string partspec, string opendate, string closedate)
        public static ToMessage ProductionScheduleReportExcelSearch(string wkshopcode, string status, string wocode, string saleordercode, string routecode, string routename, string partcode, string partname, string partspec, string opendate, string closedate)
        {
            var dynamicParams = new DynamicParameters();
            string search = "";
            try
            {
                if (wkshopcode != "" && wkshopcode != null)
                {
                    search += "and AA.wkshp_code=@wkshopcode ";
                    dynamicParams.Add("@wkshopcode", wkshopcode);
                }
                if (status != "" && status != null)
                {
                    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;
                    }
                }
@@ -144,10 +165,20 @@
                    search += "and AA.wo_code like '%'+@wocode+'%' ";
                    dynamicParams.Add("@wocode", wocode);
                }
                if (wkshopcode != "" && wkshopcode != null)
                if (saleordercode != "" && saleordercode != null)
                {
                    search += "and AA.wkshp_code=@wkshopcode ";
                    dynamicParams.Add("@wkshopcode", wkshopcode);
                    search += "and AA.saleOrderCode like '%'+@saleordercode+'%' ";
                    dynamicParams.Add("@saleordercode", saleordercode);
                }
                if (routecode != "" && routecode != null)
                {
                    search += "and AA.route_code like '%'+@routecode+'%' ";
                    dynamicParams.Add("@routecode", routecode);
                }
                if (routename != "" && routename != null)
                {
                    search += "and AA.route_name like '%'+@routename+'%' ";
                    dynamicParams.Add("@routename", routename);
                }
                if (partcode != "" && partcode != null)
                {
@@ -177,21 +208,30 @@
                search = search.Substring(3);//截取索引2后面的字符
                // --------------查询指定数据--------------
                var total = 0; //总条数
                var sql = @"select  AA.saleOrderCode as ERP源单号,AA.m_po as ERP生产订单,AA.wo_code as 生产工单号,AA.wkshp_code as 车间编码,AA.wkshp_name as 车间名称,
                            (case when AA.status='START' then '执行中' when AA.status='CLOSED' then '已完成' else '未开始' end) as 工单状态,
                            AA.lm_date 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,m.wo_code,W.status,W.lm_date,W.wkshp_code,F.torg_name as wkshp_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
                             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
                            left join  TOrganization F on W.wkshp_code=F.torg_code
                            group by E.saleOrderCode,W.m_po,m.wo_code,W.wkshp_code,F.torg_name,W.status,W.lm_date,P.partcode,P.partname,P.partspec,m.plan_qty
                            ) AA  

                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 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=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,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的名称

 VueWebCoreApi/Tools/ExcelList.cs

@@ -26,19 +26,19 @@
                //new ScoreReport("10_1","设备点检标准"),
                //new ScoreReport("11","设备保养项目"),
                //new ScoreReport("11_1","设备保养标准"),
                new ScoreReport("12","模具清单"),
                new ScoreReport("13","模具点检项目"),
                new ScoreReport("13_1","模具点检标准"),
                new ScoreReport("14","模具保养项目"),
                new ScoreReport("14_1","模具保养标准"),
                //new ScoreReport("12","模具清单"),
                //new ScoreReport("13","模具点检项目"),
                //new ScoreReport("13_1","模具点检标准"),
                //new ScoreReport("14","模具保养项目"),
                //new ScoreReport("14_1","模具保养标准"),
                new ScoreReport("15","工序设置"),
                //new ScoreReport("16","工艺路线"),
                //new ScoreReport("24","节拍工价"),
                new ScoreReport("17","质检标准"),
                new ScoreReport("18","缺陷定义"),
                new ScoreReport("19","生产订单"),
                new ScoreReport("20","库存查询"),
                new ScoreReport("21","物料清单"),
                //new ScoreReport("19","生产订单"),
                //new ScoreReport("20","库存查询"),
                //new ScoreReport("21","物料清单"),
            };
            return list;
        }

 VueWebCoreApi/appsettings.json

@@ -9,13 +9,13 @@
  "AllowedHosts": "*",
  //服务器环境
  "ConnectionStrings": {
    "DBServer": "Data Source=121.196.36.24,1533;Initial Catalog=vmes_JL;User ID=sa;Password=xkd@20230101;pooling=false",
    "DBServer": "Data Source=121.196.36.24,1533;Initial Catalog=vmes_alk;User ID=sa;Password=xkd@20230101;pooling=false",
    //企业编码
    "CompanyCode": "000",
    //企业名称
    "CompanyName": "新凯迪科技股份有限公司",
    //redis企业配置
    "Enterprise": "NewMES",
    "Enterprise": "AlkMES",
    //redis数据库链接配置ip
    "RedisConnIp": "127.0.0.1",
    //redis数据库链接配置端口号
@@ -25,7 +25,7 @@
    //session失效时间
    "InProc": "240",
    //Excel导出模板配置IP
    "FileIP": "http://121.196.36.24:8021/",
    "FileIP": "http://121.196.36.24:8031/",
    //jwtkey
    "SigningKey": "Hello World"
  }