班组、个人计件工资接口修改

yl

2022-11-16 5d90ebd84612c61c7c2903a7b2c979116cc74a48

 VueWebApi/DLL/DAL/ReportManagerDAL.cs

@@ -264,7 +264,7 @@
                }
                if (operopendate != "" && operopendate != null)
                {
                    search += "and A.lm_date between @operopendate and @operclosedate ";
                    search += "and B.report_date between @operopendate and @operclosedate ";
                    dynamicParams.Add("@operopendate", operopendate + " 00:00:00");
                    dynamicParams.Add("@operclosedate", operclosedate + " 23:59:59");
                }
@@ -276,7 +276,8 @@
                search = search.Substring(3);//截取索引2后面的字符
                // --------------查询指定数据--------------
                var total = 0; //总条数
                var sql = @"select distinct A.id,A.wo_code,M.partcode,M.partname,M.partspec,T.stepcode,T.stepname,A.task_qty,G.group_code,G.group_name,A.good_qty,isnull(S.unprice,0) as unprice,A.good_qty*isnull(S.unprice,0) as usermoney,U.username as lm_user,A.lm_date  
                var sql = @"select distinct A.id,A.wo_code,M.partcode,M.partname,M.partspec,T.stepcode,T.stepname,A.task_qty,G.group_code,G.group_name,A.good_qty,
                            isnull(S.unprice,0) as unprice,A.good_qty*isnull(S.unprice,0) as usermoney,U.username as lm_user,A.lm_date,B.report_date   
                            from TK_Wrk_Record A
                            inner join TK_Wrk_RecordSub B on A.id=B.m_id
                            inner join TK_Wrk_Step P on A.wo_code=P.wo_code and A.step_code=P.step_code and P.isend='Y'
@@ -376,7 +377,7 @@
                }
                if (operopendate != "" && operopendate != null)
                {
                    search += "and A.lm_date between @operopendate and @operclosedate ";
                    search += "and B.report_date between @operopendate and @operclosedate ";
                    dynamicParams.Add("@operopendate", operopendate + " 00:00:00");
                    dynamicParams.Add("@operclosedate", operclosedate + " 23:59:59");
                }
@@ -390,7 +391,7 @@
                var total = 0; //总条数
                var sql = @"select distinct A.wo_code as 工单编号,M.partcode as 产品编码,M.partname as 产品名称,M.partspec as 产品规格,
                            T.stepcode as 工序编码,T.stepname as 工序名称,A.task_qty as 任务数量,G.group_code as 班组编码,G.group_name as 班组名称,
                            A.good_qty as 报工数量,isnull(S.unprice,0) as 工序单价,A.good_qty*isnull(S.unprice,0) as 计件工资,U.username as 操作人员,A.lm_date as 操作时间 
                            A.good_qty as 报工数量,isnull(S.unprice,0) as 工序单价,A.good_qty*isnull(S.unprice,0) as 计件工资,U.username as 操作人员,A.lm_date as 操作时间,B.report_date as 报工时间 
                            from TK_Wrk_Record A
                            inner join TK_Wrk_RecordSub B on A.id=B.m_id
                            inner join TK_Wrk_Step P on A.wo_code=P.wo_code and A.step_code=P.step_code and P.isend='Y'
@@ -430,42 +431,42 @@
            {
                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 (groupcode != "" && groupcode != null)
                {
                    search += "and G.group_code=@groupcode ";
                    search += "and AA.group_code=@groupcode ";
                    dynamicParams.Add("@groupcode", groupcode);
                }
                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 B.report_date between @reportopendate and @reportclosedate ";
                    search += "and AA.report_date between @reportopendate and @reportclosedate ";
                    dynamicParams.Add("@reportopendate", reportopendate + " 00:00:00");
                    dynamicParams.Add("@reportclosedate", reportclosedate + " 23:59:59");
                }
@@ -477,7 +478,14 @@
                search = search.Substring(3);//截取索引2后面的字符
                // --------------查询指定数据--------------
                var total = 0; //总条数
                var sql = @"select distinct A.wo_code,M.partcode,M.partname,M.partspec,T.stepcode,T.stepname,A.task_qty,G.group_code,G.group_name,B.report_qty,isnull(S.unprice,0) as unprice,B.report_qty*isnull(S.unprice,0) as usermoney,U.username as lm_user,B.report_date  
                var sql = @"select AA.wo_code,AA.partcode,AA.partname,AA.partspec,AA.stepcode,AA.stepname,
                            AA.task_qty,AA.group_code,AA.group_name,AA.report_qty,AA.unprice,
                            AA.moneys/colum as usermoney,AA.username,AA.report_date,colum
                            from (
                            select distinct A.wo_code,M.partcode,M.partname,M.partspec,T.stepcode,T.stepname,
                            A.task_qty,G.group_code,G.group_name,B.report_qty,isnull(S.unprice,0) as unprice,
                            B.report_qty*isnull(S.unprice,0) as moneys,U.username,B.report_date,
                            (select distinct count(*)   from TK_Wrk_RecordSub S  where S.m_id=B.m_id) as colum
                            from TK_Wrk_Record A
                            inner join TK_Wrk_RecordSub B on A.id=B.m_id
                            inner join TK_Wrk_Step P on A.wo_code=P.wo_code and A.step_code=P.step_code and P.isend='Y'
@@ -487,6 +495,7 @@
                            left  join TMateriel_Info M on A.materiel_code=M.partcode
                            left  join TStep T on A.step_code=T.stepcode
                            left  join TUser U on B.report_person=U.usercode
                            ) as AA
                            where " + search;
                var data = DapperHelper.GetPageList<object>(sql, dynamicParams, prop, order, startNum, endNum, out total);
                mes.code = "200";
@@ -514,42 +523,42 @@
            {
                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 (groupcode != "" && groupcode != null)
                {
                    search += "and G.group_code=@groupcode ";
                    search += "and AA.group_code=@groupcode ";
                    dynamicParams.Add("@groupcode", groupcode);
                }
                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 B.report_date between @reportopendate and @reportclosedate ";
                    search += "and AA.report_date between @reportopendate and @reportclosedate ";
                    dynamicParams.Add("@reportopendate", reportopendate + " 00:00:00");
                    dynamicParams.Add("@reportclosedate", reportclosedate + " 23:59:59");
                }
@@ -561,9 +570,14 @@
                search = search.Substring(3);//截取索引2后面的字符
                // --------------查询指定数据--------------
                var total = 0; //总条数
                var sql = @"select distinct A.wo_code as 工单编号,M.partcode as 产品编码,M.partname as 产品名称,M.partspec as 产品规格,T.stepcode as 工序编码,T.stepname as 工序名称,
                            A.task_qty as 任务数量,G.group_code as 生产班组编码,G.group_name as 生产班组名称,B.report_qty as 报工数量,isnull(S.unprice,0) as 工序单价,
                            B.report_qty*isnull(S.unprice,0) as 计件工资,U.username as 报工人员,B.report_date as 报工时间  
                var sql = @"select AA.wo_code as 工单编号,AA.partcode as 产品编码,AA.partname as 产品名称,AA.partspec as 产品规格,AA.stepcode as 工序编码,AA.stepname as 工序名称,
                            AA.task_qty as 任务数量,AA.group_code as 生产班组编码,AA.group_name as 生产班组名称,AA.report_qty as 报工数量,AA.unprice as 工序单价,
                            AA.moneys/colum as 计件工资,AA.username as 报工人员,AA.report_date as 报工时间,colum as 班组报工人数
                            from (
                            select distinct A.wo_code,M.partcode,M.partname,M.partspec,T.stepcode,T.stepname,
                            A.task_qty,G.group_code,G.group_name,B.report_qty,isnull(S.unprice,0) as unprice,
                            B.report_qty*isnull(S.unprice,0) as moneys,U.username,B.report_date,
                            (select distinct count(*)   from TK_Wrk_RecordSub S  where S.m_id=B.m_id) as colum
                            from TK_Wrk_Record A
                            inner join TK_Wrk_RecordSub B on A.id=B.m_id
                            inner join TK_Wrk_Step P on A.wo_code=P.wo_code and A.step_code=P.step_code and P.isend='Y'
@@ -573,6 +587,7 @@
                            left  join TMateriel_Info M on A.materiel_code=M.partcode
                            left  join TStep T on A.step_code=T.stepcode
                            left  join TUser U on B.report_person=U.usercode
                            ) as AA
                            where " + search;
                DataTable data = DapperHelper.selectdata(sql, dynamicParams);
                data.TableName = "Table"; //设置DataTable的名称
@@ -628,12 +643,12 @@
                }
                if (defectcode != "" && defectcode != null)
                {
                    search += "and T.stepcode like '%'+@defectcode+'%' ";
                    search += "and F.code like '%'+@defectcode+'%' ";
                    dynamicParams.Add("@defectcode", defectcode);
                }
                if (defectname != "" && defectname != null)
                {
                    search += "and T.stepname like '%'+@defectname+'%' ";
                    search += "and F.name like '%'+@defectname+'%' ";
                    dynamicParams.Add("@defectname", defectname);
                }
                if (reportname != "" && reportname != null)
@@ -715,12 +730,12 @@
                }
                if (defectcode != "" && defectcode != null)
                {
                    search += "and T.stepcode like '%'+@defectcode+'%' ";
                    search += "and F.code like '%'+@defectcode+'%' ";
                    dynamicParams.Add("@defectcode", defectcode);
                }
                if (defectname != "" && defectname != null)
                {
                    search += "and T.stepname like '%'+@defectname+'%' ";
                    search += "and F.name like '%'+@defectname+'%' ";
                    dynamicParams.Add("@defectname", defectname);
                }
                if (reportname != "" && reportname != null)
@@ -992,5 +1007,320 @@
            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=@wkshopcode ";
                    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=@wkshopcode ";
                    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 eqpcode, 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=@wkshopcode ";
                    dynamicParams.Add("@wkshopcode", wkshopcode);
                }
                if (eqpcode != "" && eqpcode != null)
                {
                    search += "and A.eqp_code=@eqpcode ";
                    dynamicParams.Add("@eqpcode", eqpcode);
                }
                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 top 100 percent T.org_name as wkshp_name,A.eqp_code,E.name as eqp_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 ISNULL(SUM(CAST(DATEDIFF(ss, CC.start_date,CC.resp_date)/60 as INT)),0) from TAnDon_Task_Info CC where CC.wkshp_code=A.wkshp_code and CC.eqp_code=A.eqp_code and CC.type=A.type) 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
                             left join TEqpInfo E on A.eqp_code=E.code
                             where T.description='W' " + search+" group by A.wkshp_code,T.org_name,A.type,Y.name,A.eqp_code,E.name order by T.org_name,A.eqp_code ";
                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 eqpcode, 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=@wkshopcode ";
                    dynamicParams.Add("@wkshopcode", wkshopcode);
                }
                if (eqpcode != "" && eqpcode != null)
                {
                    search += "and A.eqp_code=@eqpcode ";
                    dynamicParams.Add("@eqpcode", eqpcode);
                }
                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 top 100 percent T.org_name as 生产车间,E.name as 设备名称,Y.name as 呼叫类型,
                             (select count(*) callcount  from TAnDon_Task_Info AA where AA.wkshp_code=A.wkshp_code and AA.eqp_code=A.eqp_code and AA.type=A.type) as 总呼叫次数,
                             (select count(*) repondcount  from TAnDon_Task_Info BB where BB.wkshp_code=A.wkshp_code and BB.eqp_code=A.eqp_code and BB.type=A.type and BB.status='CLOSED') as 总响应次数,
                             (select ISNULL(SUM(CAST(DATEDIFF(ss, CC.start_date,CC.resp_date)/60 as INT)),0) from TAnDon_Task_Info CC where CC.wkshp_code=A.wkshp_code and CC.eqp_code=A.eqp_code and CC.type=A.type) 总响应时长
                             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
                             left join TEqpInfo E on A.eqp_code=E.code
                             where T.description='W' " + search+" group by A.wkshp_code,T.org_name,A.type,Y.name,A.eqp_code,E.name order by T.org_name,A.eqp_code ";
                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
    }
}