ZLPMS/PMS.DBService/Problems/ReportService.cs

namespace PMS.DBService.Problems
{
    using PMS.BusinessModels.Problem;
    using PMS.Plugins.Common;
    using QWPlatform.IService;
    using System;
    using System.Data;

    public class ReportService : DataServiceBase
    {
        //售后统计
        public DataTable GetAfterSale(AfterSaleModel model, out int total)
        {
            var sql = @"Select  t.编号,
				t.登记人, 
				t.反馈人,
				t.问题标题 As 问题描述,
				p.名称 As 产品,
                xm.名称 As 项目名称,
                jg.名称 As 机构名称,
				g.名称 As 产品模块,
				t.版本号 As 产品版本,
				t.登记日期,
				b.显示名 As 登记来源,
				Decode(t.是否解决, 0, '未解决', 1, '已解决') As 是否解决,
				Decode(t.是否终止, 0, '未终止', 1, '已终止') As 是否终止,
				t.受理时间 As 处理时间,
				t.解决方案 As 解决方案, 
				f.名称 As 问题分类,
				f.分值 问题分值,
				t.受理人 As 处理人, 
				Decode(t.是否验证, 0, '未验证', 1, '已验证') As 是否验证,
				x.姓名 As 验证人,
				Gc.记录时间
			 From 问题记录 t
			 Left Join 产品系统配置 p
			 On p.Id = t.产品id
			 Left Join 产品模块功能 g
			 On g.Id = t.模块id
			 Left Join 基础编码 b
			 On b.分类id = '2DDA172A-499A-40CC-BA6A-29AB0FAEEDCB' And b.代码 = t.来源代码
			 Left Join 问题分值管理 f
			 On f. ID = t.分类id
			 Left Join 人员信息 x
			 On x.Id = t.验证人id
            Left Join 项目信息 xm
            on t.项目id=xm.id
            Left Join 站点信息 jg
            on t.机构id=jg.id
			 Left Join (Select *
								 From (Select 记录时间, 状态, 问题id, Row_Number() Over(Partition By 问题id Order By 记录时间) 序号
												From 问题记录过程
												Where 状态 = 7)) Gc
			 On Gc.问题id = t.Id And Gc.序号 = 1
			 Where t.渠道id =:ChannelId And t.是否删除 = 0";
            var sqlbuild = this.SqlBuilder;
            sqlbuild.Parameters("ChannelId", model.ChannelId);
            ///如果有查询条件
            if (!string.IsNullOrEmpty(model.DealPerson))
            {
                sql += " and t.登记人 like '%'||:登记人||'%'";
                sqlbuild.Parameters("登记人", model.DealPerson);
            }
            if (!string.IsNullOrEmpty(model.AcceptorPerson))
            {
                sql += " and t.受理人 like '%'||:受理人||'%'";
                sqlbuild.Parameters("受理人", model.AcceptorPerson);
            }
            //如果根据登记时间查询
            if (model.startDate != DateTime.MinValue || model.endDate != DateTime.MinValue)
            {
                if (model.timetype == "1")
                {
                    if (model.startDate != DateTime.MinValue)
                    {
                        sql += $" And t.登记日期 >=to_date('{model.startDate}', 'YYYY-MM-DD hh24:mi:ss')";
                    }

                    if (model.endDate != DateTime.MinValue)
                    {
                        sql += $" And t.登记日期 <=to_date('{model.endDate}', 'YYYY-MM-DD hh24:mi:ss')";
                    }
                }
                else if (model.timetype == "2")
                {
                    if (model.startDate != DateTime.MinValue)
                    {
                        sql += $" And Gc.记录时间 >=to_date('{model.startDate}', 'YYYY-MM-DD hh24:mi:ss')";
                    }

                    if (model.endDate != DateTime.MinValue)
                    {
                        sql += $" And Gc.记录时间 <=to_date('{model.endDate}', 'YYYY-MM-DD hh24:mi:ss')";
                    }
                }
                sql += " and (t.是否解决=1 or t.是否终止=1) ";
            }

            sql += " order by t.编号 desc";
            return sqlbuild.SqlText(sql).Paging(model.page, model.rows).Select(out total);
        }

        public DataTable GetChannel()
        {
            return SelectBuilder.From("渠道信息").Columns("ID,名称").Where("状态", 1).Select();
        }

        public DataTable GetRing()
        {
            return SelectBuilder.From("问题流程环节").Columns("ID,名称").Where("ID!=0").Select();
        }
        //渠道问题统计
        public DataTable GetChannelStatistics(MonitoringModel model, out int total)
        {
            string sql = "select q.名称,count(*)服务数量 from 问题记录 w,渠道信息 q where w.渠道id=q.id and w.是否删除 =0";
            //如果根据时间查询
            if (model.startDate != DateTime.MinValue || model.endDate != DateTime.MinValue)
            {
                if (model.startDate != DateTime.MinValue)
                {
                    sql += $" And w.登记日期 >=to_date('{model.startDate}', 'YYYY-MM-DD hh24:mi:ss')";
                }

                if (model.endDate != DateTime.MinValue)
                {
                    //model.endDate = model.endDate.AddDays(1);
                    sql += $" And w.登记日期 <=to_date('{model.endDate}', 'YYYY-MM-DD hh24:mi:ss')";
                    //SqlBuilder.Parameters("结束日期", model.endDate);
                }
            }
            sql += " group by w.渠道id,q.名称 order by 服务数量 desc";
            return SqlBuilder.SqlText(sql).Paging(model.page, model.rows).Select(out total);
        }
        //反馈人统计
        public DataTable GetFeedbackStatistics(MonitoringModel model, out int total)
        {
            string sql = " select w.登记人 as 姓名,count(w.id)服务数量 from 问题记录 w where  w.是否删除 =0 ";
            if (!string.IsNullOrEmpty(model.Deal))
            {
                sql += " and w.登记人 like '%'||:登记人||'%'";
            }
            //如果根据时间查询
            if (model.startDate != DateTime.MinValue || model.endDate != DateTime.MinValue)
            {
                if (model.startDate != DateTime.MinValue)
                {
                    sql += $" And w.登记日期 >=to_date('{model.startDate}', 'YYYY-MM-DD hh24:mi:ss')";
                }

                if (model.endDate != DateTime.MinValue)
                {
                    //model.endDate = model.endDate.AddDays(1);
                    sql += $" And w.登记日期 <=to_date('{model.endDate}', 'YYYY-MM-DD hh24:mi:ss')";
                }
            }
            sql += " group by  w.登记人 order by 服务数量 desc";
            return SqlBuilder.SqlText(sql).Paging(model.page, model.rows).Parameters("登记人", model.Deal).Select(out total);
        }

        public DataTable GetMonitoring(MonitoringModel model, out int total)
        {
            var sql = @"select x.*,(nvl(x.项目处理1,0)+nvl(x.技术支持1,0)+nvl(x.研发处理1,0)) 总耗时 from ( select a.ID,
                        a.编号,a.问题标题,(select 名称 from 问题流程环节 where id=a.环节id) 当前环节1,
                        a.受理人,a.登记人,a.登记日期,(select 名称 from 渠道信息 where id =  a.渠道id) as 渠道,a.是否解决,a.是否终止,
        
                       nvl((select sum(ROUND(nvl(c.结束时间, sysdate) - c.记录时间, 2) * 24)
                          from 问题记录过程 c, 问题工作流程 d
                         where c.问题ID = a.ID
                           and c.当前流程id = d.id
                           and d.环节id <= 3),0) as 项目处理1,
                       nvl((select sum(ROUND(nvl(c.结束时间, sysdate) - c.记录时间, 2) * 24)
                          from 问题记录过程 c, 问题工作流程 d
                         where c.问题ID = a.ID
                           and c.当前流程id = d.id
                           and d.环节id = 4),0) as 技术支持1,
                       nvl((select sum(ROUND(nvl(c.结束时间, sysdate) - c.记录时间, 2) * 24)
                          from 问题记录过程 c, 问题工作流程 d
                         where c.问题ID = a.ID
                           and c.当前流程id = d.id
                           and d.环节id = 5),0) as 研发处理1,
                       (case
                         when a.是否解决 = 0 and a.是否终止 = 0 Then
                          (select ROUND(sysdate - max(ss.记录时间), 2) * 24
                             from 问题记录过程 ss
                            where ss.问题ID = a.ID)
                         Else
                          0
                       end) as 滞留时间

                  from 问题记录 a
                 where a.是否删除 = 0 ";
            if (model.State == "未完成")
            {
                sql += " and a.是否终止=0 and a.是否解决=0 ";
            }
            if (model.State == "完成")
            {
                sql += " and (a.是否终止=1 or a.是否解决=1) ";
            }
            if (model.CurrentLink != 0)
            {
                sql += " and a.环节ID=" + model.CurrentLink;
            }
            if (model.Channel != null)
            {
                sql += " and a.渠道ID='" + model.Channel.Trim() + "' ";
            }
            sql += " ) x ";
            sql += model.oderBy;
            return SqlBuilder.SqlText(sql).Paging(model.page, model.rows).Select(out total);
        }

        //研发问题统计
        public DataTable GetDevelopStatistics(MonitoringModel model, out int total)
        {
            string sql = "select count(1) 处理数 ,r.姓名  from 问题记录 w ,人员信息 r where  w.受理人id=r.id and nvl(w.是否解决,0)=1 and r.职务=11";
            //and w.受理时间 between to_date('2019-4-1','yyyy-mm-dd') and to_date('2019-6-30 23:59:59','yyyy-mm-dd hh24:mi:ss')";
            //根据时间查询
            if (model.startDate != DateTime.MinValue || model.endDate != DateTime.MinValue)
            {
                if (model.startDate != DateTime.MinValue)
                {
                    sql += $" And w.登记日期 >=to_date('{model.startDate}', 'YYYY-MM-DD hh24:mi:ss')";
                }

                if (model.endDate != DateTime.MinValue)
                {
                    //model.endDate = model.endDate.AddDays(1);
                    sql += $" And w.登记日期 <=to_date('{model.endDate}', 'YYYY-MM-DD hh24:mi:ss')";
                }
            }
            //受理人
            if (!String.IsNullOrEmpty(model.Deal))
            {
                sql += " and w.受理人 like '%'||:受理人||'%' ";
            }
            if (model.IsInside && !model.NoInside)
            {
                sql += " and w.内部产品问题=1";
            }
            else if (!model.IsInside && model.NoInside)
            {
                sql += " and w.内部产品问题=0";
            }
            sql += " group by r.姓名 order by 处理数 desc";
            return SqlBuilder.SqlText(sql).Paging(model.page, model.rows).Parameters("受理人", model.Deal).Select(out total);
        }

        //技术支持统计
        public DataTable GetSupportStatistics(MonitoringModel model, out int total)
        {
            string sql = @"select s.处理人,s.问题编号,
                            (select sum(耗时)from 问题记录过程 where 记录人id = s.记录人ID and 问题ID = s.问题ID)处理时间
                             from(select(select 姓名 from 人员信息 where ID = g.记录人id)处理人, (select 编号 from 问题记录 where id = g.问题id)问题编号,g.记录人ID,g.问题ID
                                from 问题记录过程 g
                             where(select 职务 from 人员信息 where id = g.记录人id and 是否删除 = 0) = 12 and ((select 环节ID from 问题记录 where id = g.问题ID)= 4 or (select l.环节id from 问题工作流程 l where l.id=g.当前流程id)=4) and (select 是否删除 from 问题记录 where id = g.问题ID and 是否解决=1) = 0";
            //如果根据时间查询
            if (model.startDate != DateTime.MinValue || model.endDate != DateTime.MinValue)
            {
                if (model.startDate != DateTime.MinValue)
                {
                    sql += $" And g.登记日期 >=to_date('{model.startDate}', 'YYYY-MM-DD hh24:mi:ss')";
                }

                if (model.endDate != DateTime.MinValue)
                {
                    //model.endDate = model.endDate.AddDays(1);
                    sql += $" And g.登记日期 <=to_date('{model.endDate}', 'YYYY-MM-DD hh24:mi:ss')";
                }
            }
            sql += " group by g.记录人ID,g.问题ID) s ";
            if (!String.IsNullOrEmpty(model.Deal))
            {
                sql += " where s.处理人 like '%'||:处理人||'%' ";
            }
            sql += " order by 处理人,问题编号";
            return SqlBuilder.SqlText(sql).Paging(model.page, model.rows).Parameters("处理人", model.Deal).Select(out total);
        }

        //未完成统计
        public DataTable GetNotYetStatistics(MonitoringModel model, out int total)
        {
            string sql = @"select 环节,受理人,编号,当前环节耗时,总耗时,登记日期 from 
	                        (select 编号,登记日期,
	                              (select 姓名 from 人员信息 where id=w.受理人id)受理人,
				                        (select 名称 from 问题流程环节 where id=环节id)环节,
                        (select ROUND(sysdate - w.登记日期,2)*24 from dual)总耗时，
                        (select (ROUND(sysdate - (select max(记录时间) from 问题记录过程 where 问题id=w.id),2)*24) from dual)当前环节耗时
                        from 问题记录 w where 是否删除=0 and (w.环节id=3 or w.环节id=4 or w.环节id=5) and 是否终止=0 and 是否解决=0";
            //(select 耗时 from 问题记录过程 where 记录时间 = (select max(记录时间) from 问题记录过程 where 问题id = w.id)and 问题id = w.id)当前环节耗时
            //(select sum(耗时)from 问题记录过程 where 问题id = w.id)总耗时,
            if (!String.IsNullOrEmpty(model.Deal))
            {
                sql += " and  受理人 like '%'||:受理人||'%'";
            }
            //如果根据时间查询
            if (model.startDate != DateTime.MinValue || model.endDate != DateTime.MinValue)
            {
                if (model.startDate != DateTime.MinValue)
                {
                    sql += $" And 登记日期 >=to_date('{model.startDate}', 'YYYY-MM-DD hh24:mi:ss')";
                }

                if (model.endDate != DateTime.MinValue)
                {
                    //model.endDate = model.endDate.AddDays(1);
                    sql += $" And 登记日期 <=to_date('{model.endDate}', 'YYYY-MM-DD hh24:mi:ss')";
                }
            }
            sql += ") order by  decode(环节,'研发处理',1,'技术支持',2,'项目处理',3),受理人";
            return SqlBuilder.SqlText(sql).Paging(model.page, model.rows).Parameters("受理人", model.Deal).Select(out total);
        }

        //超时统计(30分钟)
        public DataTable TimeoutStatistics(MonitoringModel model, out int total)
        {
            DataTable result = new DataTable();
            result.Columns.Add("ID");
            result.Columns.Add("编号");
            result.Columns.Add("问题标题");
            result.Columns.Add("模块");
            result.Columns.Add("反馈人");
            string sql = "select w.ID,w.编号,w.反馈人,w.问题标题,g.名称 as 模块  from 问题记录 w,产品模块功能 g where w.是否终止=0 and w.是否删除=0 and  w.模块ID=g.id ";
            //如果根据时间查询
            if (model.startDate != DateTime.MinValue || model.endDate != DateTime.MinValue)
            {
                if (model.startDate != DateTime.MinValue)
                {
                    sql += $" And 登记日期 >=to_date('{model.startDate}', 'YYYY-MM-DD hh24:mi:ss')";
                }

                if (model.endDate != DateTime.MinValue)
                {
                    sql += $" And 登记日期 <=to_date('{model.endDate}', 'YYYY-MM-DD hh24:mi:ss')";
                }
            }
            sql += " order by 登记日期 desc";
            DataTable dt = SqlBuilder.SqlText(sql).Select();
            foreach (DataRow item in dt.Rows)
            {
                DataTable dd = SqlBuilder.SqlText("select 状态,耗时 from 问题记录过程 where 问题id=:ID order by 记录时间").Parameters("ID", item.ItemArray[0]).Select();
                foreach (DataRow obj in dd.Rows)
                {
                    if (Convert.ToInt32(obj.ItemArray[0]) == 8 && Convert.ToInt32(obj.ItemArray[1]) > 0.5)
                    {
                        DataRow dr = result.NewRow();
                        dr["ID"] = item.ItemArray[0];
                        dr["编号"] = item.ItemArray[1];
                        dr["反馈人"] = item.ItemArray[2];
                        dr["问题标题"] = item.ItemArray[3];
                        dr["模块"] = item.ItemArray[4];
                        result.Rows.Add(dr);
                        break;
                    }
                }
            }
            total = result.Rows.Count;
            //真分页
            //DataTable NewTable = result.Clone();

            //var page = (model.page - 1) * model.rows;
            //var size = model.page * model.rows;

            //for (int i = page; i < size&&size<=result.Rows.Count; i++)
            //{
            //    NewTable.Rows.Add(dt.Rows[i].ItemArray);  //添加数据行
            //}
            //return NewTable;

            return result;
        }

        public DataTable GetList(int PersonProperty)
        {
            if (PersonProperty == 1)
            {
                return SelectBuilder.From("统计报表").Columns("*").Select();
            }
            else
            {
                string sql = "select * from 统计报表 where 查询人员性质 like '%'||:性质||'%' or 查询人员性质 is null ";
                return SqlBuilder.SqlText(sql).Parameters("性质", PersonProperty).Select();
            }
        }

        //贵阳售后统计问题积分
        public DataTable GuiyangProblems(AfterSaleModel model, out int total)
        {
            var sqlWhere = "";
            var sqlbuild = this.SqlBuilder;
            sqlbuild.Parameters("ChannelId", model.ChannelId);
            ///如果有查询条件
            if (!string.IsNullOrEmpty(model.DealPerson))
            {
                sqlWhere += " and t.登记人 like '%'||:登记人||'%'";
                sqlbuild.Parameters("登记人", model.DealPerson);
            }
            if (!string.IsNullOrEmpty(model.AcceptorPerson))
            {
                sqlWhere += " and t.受理人 like '%'||:受理人||'%'";
                sqlbuild.Parameters("受理人", model.AcceptorPerson);
            }

            //如果根据登记时间查询
            if (model.startDate != DateTime.MinValue || model.endDate != DateTime.MinValue)
            {
                if (model.timetype == "1")
                {
                    if (model.startDate != DateTime.MinValue)
                    {
                        sqlWhere += $" And t.登记日期 >=to_date('{model.startDate}', 'YYYY-MM-DD hh24:mi:ss')";
                    }

                    if (model.endDate != DateTime.MinValue)
                    {
                        sqlWhere += $" And t.登记日期 <=to_date('{model.endDate}', 'YYYY-MM-DD hh24:mi:ss')";
                    }
                }
                else if (model.timetype == "2")
                {
                    if (model.startDate != DateTime.MinValue)
                    {
                        sqlWhere += $" And Gc.记录时间 >=to_date('{model.startDate}', 'YYYY-MM-DD hh24:mi:ss')";
                    }

                    if (model.endDate != DateTime.MinValue)
                    {
                        sqlWhere += $" And Gc.记录时间 <=to_date('{model.endDate}', 'YYYY-MM-DD hh24:mi:ss')";
                    }
                }
                sqlWhere += " and (t.是否解决=1 or t.是否终止=1) ";

            }
            var sql = @" select 名称, 0 as 问题分值,sum(陈勇) as 陈勇, sum(池显) as 池显, sum(兰忠胜) as 兰忠胜, sum(陈运良) as 陈运良
  from (
 
 select  xm.名称, nvl(s.问题分值, 0) as 问题分值,
  
case when s.处理人='张晓厅' then nvl(问题分值, 0)
end as 张晓厅,
case when s.处理人='吴昊' then nvl(问题分值, 0)
end as 吴昊,
case when s.处理人='陈勇' then nvl(问题分值, 0)
end as 陈勇,
case when s.处理人='池显' then nvl(问题分值, 0)
end as 池显,
case when s.处理人='向成高' then nvl(问题分值, 0)
end as 向成高,
case when s.处理人='陈洪江' then nvl(问题分值, 0)
end as 陈洪江,
case when s.处理人='苏伟' then nvl(问题分值, 0)
end as 苏伟, 
 case when s.处理人='兰忠胜' then nvl(问题分值, 0)
end as 兰忠胜, 
case when s.处理人='陈运良' then nvl(问题分值, 0)
end as 陈运良,

case when s.处理人='刘彦' then nvl(问题分值, 0)
end as 刘彦, 

case when s.处理人='王转正' then nvl(问题分值, 0)
end as 王转正
         from (
Select  t.编号,
t.渠道id,
t.项目id,
        t.登记人, 
        t.反馈人,
        t.问题标题 As 问题描述,
        p.名称 As 产品,
        g.名称 As 产品模块,
        t.版本号 As 产品版本,
        t.登记日期,
        b.显示名 As 登记来源,
        Decode(t.是否解决, 0, '未解决', 1, '已解决') As 是否解决,
        Decode(t.是否终止, 0, '未终止', 1, '已终止') As 是否终止,
        t.受理时间 As 处理时间,
        t.解决方案 As 解决方案, 
        f.名称 As 问题分类,
        f.分值 问题分值,
        t.受理人 As 处理人, 
        Decode(t.是否验证, 0, '未验证', 1, '已验证') As 是否验证,
        x.姓名 As 验证人,
        Gc.记录时间
       From 问题记录 t
       Left Join 产品系统配置 p
       On p.Id = t.产品id
       Left Join 产品模块功能 g
       On g.Id = t.模块id
       Left Join 基础编码 b
       On b.分类id = '2DDA172A-499A-40CC-BA6A-29AB0FAEEDCB' And b.代码 = t.来源代码
       Left Join 问题分值管理 f
       On f. ID = t.分类id
       Left Join 人员信息 x
       On x.Id = t.受理人ID
       Left Join (Select *
                 From (Select 记录时间, 状态, 问题id, Row_Number() Over(Partition By 问题id Order By 记录时间) 序号
                        From 问题记录过程
                        Where 状态 = 7)) Gc
       On Gc.问题id = t.Id And Gc.序号 = 1
       
       Where t.渠道id =:ChannelId And t.是否删除 = 0  " + sqlWhere+@"
       
       ) s ,项目信息 xm where xm.id=s.项目id 
       ) ct group by  ct.名称";
           
       
            return sqlbuild.SqlText(sql).Paging(model.page, model.rows).Select(out total);
        }
        /// <summary>
        /// 获取渠道脱保数
        /// </summary>
        /// <param name="model"></param>
        /// <param name="total"></param>
        /// <returns></returns>
        public DataTable GeMaintenanceInquiry(MaintenanceModel model, out int total)
        {
            var sqlWhere = "";
            var sqlbuild = this.SqlBuilder;
            if(model.PersonProperty!=1)//总部
            {
                sqlWhere += " and x.渠道id =:ChannelId";
                sqlbuild.Parameters("ChannelId", model.ChannelId);
            }
            else
            {
                if (!string.IsNullOrEmpty(model.qdid))//总部筛选条件
                {
                    sqlWhere += " and x.渠道id =:ChannelId";
                    sqlbuild.Parameters("ChannelId", model.qdid);
                }
                    
            }
          
            if (model.type == 1)//脱保状态0 全部 ，1正常，
            {
              
                 sqlWhere += " and   wbjl.服务状态 = '在保'";
            }
            else if (model.type == 2)
            {
                sqlWhere += "  and   wbjl.服务状态 = '脱保'";
            }
                ///如果有查询条件
                if (!string.IsNullOrEmpty(model.name))
            {
                sqlWhere += " and (q.名称 like '%'||:name||'%' or x.名称 like '%'||:name||'%'   )";
                sqlbuild.Parameters("name", model.name);
            }
            //            var sql = @" SELECT  t.渠道名称，t.项目信息 AS 客户信息,t.名称 产品名称,t.服务状态,wbjl.服务开始时间,wbjl.服务结束时间,(SELECT  count(1) FROM  问题记录 j WHERE j.渠道id=t.渠道id AND j.项目id=t.项目id AND j.产品ID=t.产品ID AND  j.登记日期>(SYSDATE -60)) AS 登记问题数量,
            //(TRUNC( wbjl.服务结束时间-SYSDATE)) AS 维保天数,t.应用状态,wbjl.说明
            //FROM  (
            //select (q.名称)渠道名称,x.名称 as 项目信息 ,t.名称,c.应用状态,
            //case when  (c.应用状态)=1 then '在保'
            //else '脱保'
            //end 
            //as 服务状态,
            //(q.id) 渠道id,
            //(x.id) 项目id,
            //(c.产品id) 产品id,
            //c.id AS 项目产品模块ID
            //from 渠道信息 q , 项目信息 x  ,项目产品模块 c,产品系统配置 t 
            //where  q.id=x.渠道id  and   c.项目id=x.id 
            //and t.id=c.产品id
            //) t ,项目维保记录 wbjl 
            //WHERE  (wbjl.渠道ID(+)=t.渠道ID AND wbjl.项目ID(+)=t.项目ID AND  wbjl.项目产品模块ID(+)=t.项目产品模块ID)

            // " + sqlWhere + " order by t.渠道名称 , t.应用状态,wbjl.服务结束时间";

            var sql = @"select 
q.名称 AS 渠道名称,x.名称 AS 客户信息,p.名称 AS 产品名称 ,
wbjl.服务状态,
wbjl.服务开始时间,wbjl.服务结束时间,(SELECT  count(1) FROM  问题记录 j WHERE j.渠道id=q.id AND j.项目id=x.id AND j.产品ID=wbjl.产品ID AND  j.登记日期>(SYSDATE -60)) AS 登记问题数量,
(TRUNC( wbjl.服务结束时间-SYSDATE)) AS 维保天数,wbjl.说明
from 渠道信息 q , 项目信息 x ,产品系统配置 p,(select * from (
select 
wbjl.服务开始时间,wbjl.服务结束时间,wbjl.说明,wbjl.渠道ID,wbjl.项目ID,wbjl.项目产品模块ID,c.应用状态,c.产品ID,
case when  (c.应用状态)=1 then '在保'
else '脱保'
end 
as 服务状态,row_number() over(partition by wbjl.渠道ID,wbjl.项目ID,c.产品ID
order by wbjl.服务结束时间 desc) rn from 项目维保记录 wbjl,项目产品模块 c WHERE wbjl.项目产品模块ID=c.ID AND wbjl.项目ID=c.项目ID
) 
where rn=1
) wbjl 
where  q.id=x.渠道id  AND p.id=wbjl.产品ID
AND   wbjl.渠道ID=x.渠道ID AND wbjl.项目ID=x.id
" + sqlWhere + " order by q.名称 ,x.名称, wbjl.服务状态,wbjl.服务结束时间";


            return sqlbuild.SqlText(sql).Paging(model.page, model.rows).Select(out total);
        }


    }
}