安灯报表开发

yl

2022-10-25 dd651f9ce0156fc7db869da04e0c2004a1811c52

 VueWebApi/DLL/DAL/ReportManagerDAL.cs

@@ -992,5 +992,308 @@
            return mes;
        }
        #endregion



        #region[安灯报表明细]
        public static ToMessage AnDonReportDefinitSearch(string wkshopcode, string calltypecode, string calluser, string callopendate, string callclosedate, string eqpcode, string eqpname,string responduser, string respondopendate, string respondclosedate, int startNum, int endNum, string prop, string order)
        {
            var dynamicParams = new DynamicParameters();
            string search = "";
            try
            {
                if (wkshopcode != "" && wkshopcode != null)
                {
                    search += "and A.wkshp_code=@style ";
                    dynamicParams.Add("@wkshopcode", wkshopcode);
                }
                if (calltypecode != "" && calltypecode != null)
                {
                    search += "and A.type=@calltypecode ";
                    dynamicParams.Add("@calltypecode", calltypecode);
                }
                if (calluser != "" && calluser != null)
                {
                    search += "and A.start_user like '%'+@calluser+'%' ";
                    dynamicParams.Add("@calluser", calluser);
                }
                if (eqpcode != "" && eqpcode != null)
                {
                    search += "and A.eqp_code like '%'+@eqpcode+'%' ";
                    dynamicParams.Add("@eqpcode", eqpcode);
                }
                if (eqpname != "" && eqpname != null)
                {
                    search += "and E.name like '%'+@eqpname+'%' ";
                    dynamicParams.Add("@eqpname", eqpname);
                }
                if (responduser != "" && responduser != null)
                {
                    search += "and A.resp_user like '%'+@responduser+'%' ";
                    dynamicParams.Add("@responduser", responduser);
                }
                if (callopendate != "" && callopendate != null)
                {
                    search += "and A.start_date between @callopendate and @callclosedate ";
                    dynamicParams.Add("@callopendate", callopendate + " 00:00:00");
                    dynamicParams.Add("@callclosedate", callclosedate + " 23:59:59");
                }
                if (respondopendate != "" && respondopendate != null)
                {
                    search += "and A.resp_date between @respondopendate and @respondclosedate ";
                    dynamicParams.Add("@respondopendate", respondopendate + " 00:00:00");
                    dynamicParams.Add("@respondclosedate", respondclosedate + " 23:59:59");
                }

                if (search == "")
                {
                    search = "and 1=1 ";
                }
                search = search.Substring(3);//截取索引2后面的字符
                // --------------查询指定数据--------------
                var total = 0; //总条数
                var sql = @"select A.wkshp_code,T.org_name as wkshp_name,A.eqp_code,E.name as eqp_name,Y.name as typename,
                            A.start_user,A.start_date,A.resp_user,A.resp_date,
                            CAST(CAST(datediff(second,A.start_date,A.resp_date) / (60*60*24) AS INT) AS VARCHAR) + '天'
                                    + CAST(CAST(datediff(second,A.start_date,A.resp_date) % 86400 / 3600 AS INT) AS VARCHAR) + '小时'
                                    + CAST(CAST(datediff(second,A.start_date,A.resp_date) % 3600 / 60 AS INT) AS VARCHAR) + '分'
                                    + CAST(CAST(datediff(second,A.start_date,A.resp_date) % 60 AS INT) AS VARCHAR) + '秒' AS respondcont,
                            (case when A.resp_user is null then '待响应' else '以响应' end) as status  
                            from TAnDon_Task_Info A
                            left join TOrganization T on A.wkshp_code=T.org_code
                            left join TEqpInfo E on A.eqp_code=E.code
                            left join TAnDonType Y on A.type=Y.code
                            where T.description='W' " + search;
                var data = DapperHelper.GetPageList<object>(sql, dynamicParams, prop, order, startNum, endNum, out total);
                mes.code = "200";
                mes.Message = "查询成功!";
                mes.count = total;
                mes.data = data.ToList();
            }
            catch (Exception e)
            {
                mes.code = "300";
                mes.count = 0;
                mes.Message = e.Message;
                mes.data = null;
            }
            return mes;
        }
        #endregion

        #region[安灯报表明细导出]
        public static ToMessage AnDonReportDefinitExcelSearch(string wkshopcode, string calltypecode, string calluser, string callopendate, string callclosedate, string eqpcode, string eqpname, string responduser, string respondopendate, string respondclosedate)
        {
            var dynamicParams = new DynamicParameters();
            string search = "";
            try
            {
                if (wkshopcode != "" && wkshopcode != null)
                {
                    search += "and A.wkshp_code=@style ";
                    dynamicParams.Add("@wkshopcode", wkshopcode);
                }
                if (calltypecode != "" && calltypecode != null)
                {
                    search += "and A.type=@calltypecode ";
                    dynamicParams.Add("@calltypecode", calltypecode);
                }
                if (calluser != "" && calluser != null)
                {
                    search += "and A.start_user like '%'+@calluser+'%' ";
                    dynamicParams.Add("@calluser", calluser);
                }
                if (eqpcode != "" && eqpcode != null)
                {
                    search += "and A.eqp_code like '%'+@eqpcode+'%' ";
                    dynamicParams.Add("@eqpcode", eqpcode);
                }
                if (eqpname != "" && eqpname != null)
                {
                    search += "and E.name like '%'+@eqpname+'%' ";
                    dynamicParams.Add("@eqpname", eqpname);
                }
                if (responduser != "" && responduser != null)
                {
                    search += "and A.resp_user like '%'+@responduser+'%' ";
                    dynamicParams.Add("@responduser", responduser);
                }
                if (callopendate != "" && callopendate != null)
                {
                    search += "and A.start_date between @callopendate and @callclosedate ";
                    dynamicParams.Add("@callopendate", callopendate + " 00:00:00");
                    dynamicParams.Add("@callclosedate", callclosedate + " 23:59:59");
                }
                if (respondopendate != "" && respondopendate != null)
                {
                    search += "and A.resp_date between @respondopendate and @respondclosedate ";
                    dynamicParams.Add("@respondopendate", respondopendate + " 00:00:00");
                    dynamicParams.Add("@respondclosedate", respondclosedate + " 23:59:59");
                }

                if (search == "")
                {
                    search = "and 1=1 ";
                }
                search = search.Substring(3);//截取索引2后面的字符
                // --------------查询指定数据--------------
                var total = 0; //总条数
                var sql = @"select A.wkshp_code as 车间编码,T.org_name as 车间名称,A.eqp_code as 设备编码,E.name as 设备名称,Y.name as 呼叫类型,
                            A.start_user as 呼叫人,A.start_date as 呼叫时间,A.resp_user as 响应人,A.resp_date as 响应时间,
                            CAST(CAST(datediff(second,A.start_date,A.resp_date) / (60*60*24) AS INT) AS VARCHAR) + '天'
                                    + CAST(CAST(datediff(second,A.start_date,A.resp_date) % 86400 / 3600 AS INT) AS VARCHAR) + '小时'
                                    + CAST(CAST(datediff(second,A.start_date,A.resp_date) % 3600 / 60 AS INT) AS VARCHAR) + '分'
                                    + CAST(CAST(datediff(second,A.start_date,A.resp_date) % 60 AS INT) AS VARCHAR) + '秒' AS 响应时长,
                            (case when A.resp_user is null then '待响应' else '以响应' end) as 状态  
                            from TAnDon_Task_Info A
                            left join TOrganization T on A.wkshp_code=T.org_code
                            left join TEqpInfo E on A.eqp_code=E.code
                            left join TAnDonType Y on A.type=Y.code
                            where T.description='W' " + search;
                DataTable data = DapperHelper.selectdata(sql, dynamicParams);
                data.TableName = "Table"; //设置DataTable的名称
                string msg = DownLoad.DataTableToExcel(data, "安灯明细报表");
                mes.code = "200";
                mes.Message = "查询成功!";
                mes.count = total;
                mes.data = msg;
            }
            catch (Exception e)
            {
                mes.code = "300";
                mes.count = 0;
                mes.Message = e.Message;
                mes.data = null;
            }
            return mes;
        }
        #endregion


        #region[安灯报表汇总]
        public static ToMessage AnDonReportSumSearch(string wkshopcode, string calltypecode, string callopendate, string callclosedate, string respondopendate, string respondclosedate, int startNum, int endNum, string prop, string order)
        {
            var dynamicParams = new DynamicParameters();
            string search = "";
            try
            {
                if (wkshopcode != "" && wkshopcode != null)
                {
                    search += "and A.wkshp_code=@style ";
                    dynamicParams.Add("@wkshopcode", wkshopcode);
                }
                if (calltypecode != "" && calltypecode != null)
                {
                    search += "and A.type=@calltypecode ";
                    dynamicParams.Add("@calltypecode", calltypecode);
                }
                if (callopendate != "" && callopendate != null)
                {
                    search += "and A.start_date between @callopendate and @callclosedate ";
                    dynamicParams.Add("@callopendate", callopendate + " 00:00:00");
                    dynamicParams.Add("@callclosedate", callclosedate + " 23:59:59");
                }
                if (respondopendate != "" && respondopendate != null)
                {
                    search += "and A.resp_date between @respondopendate and @respondclosedate ";
                    dynamicParams.Add("@respondopendate", respondopendate + " 00:00:00");
                    dynamicParams.Add("@respondclosedate", respondclosedate + " 23:59:59");
                }

                if (search == "")
                {
                    search = "and 1=1 ";
                }
                search = search.Substring(3);//截取索引2后面的字符
                // --------------查询指定数据--------------
                var total = 0; //总条数
                var sql = @"select T.org_name as wkshp_name,Y.name as calltypename,
                            (select count(*) callcount  from TAnDon_Task_Info AA where AA.wkshp_code=A.wkshp_code and AA.type=A.type) as callcount,
                            (select count(*) repondcount  from TAnDon_Task_Info BB where BB.wkshp_code=A.wkshp_code and BB.type=A.type and BB.status='CLOSED') as repondcount,
                            (select CAST(DATEDIFF(ss, start_date,resp_date)/60 as INT)  from TAnDon_Task_Info CC where CC.wkshp_code=A.wkshp_code and CC.type=A.type and CC.status='CLOSED') as repondtime
                            from TAnDon_Task_Info A
                            left join TOrganization T on A.wkshp_code=T.org_code
                            left join TAnDonType Y on A.type=Y.code
                            where T.description='W' "+search+" group by A.wkshp_code,T.org_name,A.type,Y.name ";
                var data = DapperHelper.GetPageList<object>(sql, dynamicParams, prop, order, startNum, endNum, out total);
                mes.code = "200";
                mes.Message = "查询成功!";
                mes.count = total;
                mes.data = data.ToList();
            }
            catch (Exception e)
            {
                mes.code = "300";
                mes.count = 0;
                mes.Message = e.Message;
                mes.data = null;
            }
            return mes;
        }
        #endregion

        #region[安灯报表汇总导出]
        public static ToMessage AnDonReportSumExcelSearch(string wkshopcode, string calltypecode, string callopendate, string callclosedate, string respondopendate, string respondclosedate)
        {
            var dynamicParams = new DynamicParameters();
            string search = "";
            try
            {
                if (wkshopcode != "" && wkshopcode != null)
                {
                    search += "and A.wkshp_code=@style ";
                    dynamicParams.Add("@wkshopcode", wkshopcode);
                }
                if (calltypecode != "" && calltypecode != null)
                {
                    search += "and A.type=@calltypecode ";
                    dynamicParams.Add("@calltypecode", calltypecode);
                }
                if (callopendate != "" && callopendate != null)
                {
                    search += "and A.start_date between @callopendate and @callclosedate ";
                    dynamicParams.Add("@callopendate", callopendate + " 00:00:00");
                    dynamicParams.Add("@callclosedate", callclosedate + " 23:59:59");
                }
                if (respondopendate != "" && respondopendate != null)
                {
                    search += "and A.resp_date between @respondopendate and @respondclosedate ";
                    dynamicParams.Add("@respondopendate", respondopendate + " 00:00:00");
                    dynamicParams.Add("@respondclosedate", respondclosedate + " 23:59:59");
                }

                if (search == "")
                {
                    search = "and 1=1 ";
                }
                search = search.Substring(3);//截取索引2后面的字符
                // --------------查询指定数据--------------
                var total = 0; //总条数
                var sql = @"select T.org_name as 生产车间,Y.name as 呼叫类型,
                            (select count(*) callcount  from TAnDon_Task_Info AA where AA.wkshp_code=A.wkshp_code and AA.type=A.type) as 呼叫次数,
                            (select count(*) repondcount  from TAnDon_Task_Info BB where BB.wkshp_code=A.wkshp_code and BB.type=A.type and BB.status='CLOSED') as 响应次数,
                            (select CAST(DATEDIFF(ss, start_date,resp_date)/60 as INT)  from TAnDon_Task_Info CC where CC.wkshp_code=A.wkshp_code and CC.type=A.type and CC.status='CLOSED') as 响应时长(分)
                            from TAnDon_Task_Info A
                            left join TOrganization T on A.wkshp_code=T.org_code
                            left join TAnDonType Y on A.type=Y.code
                            where T.description='W' " + search + " group by A.wkshp_code,T.org_name,A.type,Y.name ";
                DataTable data = DapperHelper.selectdata(sql, dynamicParams);
                data.TableName = "Table"; //设置DataTable的名称
                string msg = DownLoad.DataTableToExcel(data, "安灯汇总报表");
                mes.code = "200";
                mes.Message = "查询成功!";
                mes.count = total;
                mes.data = msg;
            }
            catch (Exception e)
            {
                mes.code = "300";
                mes.count = 0;
                mes.Message = e.Message;
                mes.data = null;
            }
            return mes;
        }
        #endregion
    }
}