ZLPMS/PMS.DBService/Problems/QueryDBService.cs

using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using PMS.BusinessModels.Problem;
using QWPlatform.IService;
using QWPlatform.SystemLibrary;
using QWPlatform.SystemLibrary.LogManager;
using PMS.EntityModels.Product;
using PMS.BusinessModels.Account;
using QWPlatform.DataIntface;
using PMS.BusinessModels.MobileProblem;
using System.Configuration;
using PMS.Plugins.Common;

namespace PMS.DBService.Problems
{
    /// <summary>
    /// 查询问题的数据服务
    /// </summary> 
    public class QueryDBService : DataServiceBase
    {
        public static string IsMainMontrol = ConfigurationManager.AppSettings["IsMainMontrol"].ToStringEx();//  是否启用维保控制1启用 0停用
        #region 问题记录查询

        /// <summary>
        /// 综合查询并返回结果
        /// </summary>
        /// <param name="queryModel">查询模型</param>
        /// <returns></returns>
        public DataTable Query(QueryModel queryModel, out int total)
        {
            //是否跨渠道查询
            var iscoll = 0;
            var number = this.SqlBuilder.SqlText(@"SELECT count(1) FROM 人员信息,渠道人员共享
      WHERE 人员信息.ID =:人员ID AND 人员信息.渠道id = 渠道人员共享.共享渠道ID")
                .Parameters("人员ID", queryModel.currentUserID).Select<int>();
            if(number>0)
            {
                iscoll = 1;
            }

            total = 0;
            var sql = "";
             if (String.IsNullOrEmpty(queryModel.text))
            {
                sql = @"select ID,编号,
                            解决版本,
                           是否终止,
                           是否验证,
                           是否解决,
                           是否回退,
                           紧急代码,
                           是否受理,
                           问题性质,
                           问题类型,
                           问题标题, 解决版本状态,紧急补丁时间,
                            解决版本ID,
                            (select count(1) from 产品问题版本 jj where jj.问题id=t.id) as 是否加入版本,
                           研发处理人ID,测试处理人ID，研发处理人, 测试处理人,
                           (select 联系电话  from 人员信息 where id=t.登记人ID) as 联系电话,
                           (select 名称||'['||编码||']' from 问题分值管理 where id=t.技术支持分类ID) as 技术支持分类,
                           (select 编号 from 问题记录 where id=t.关联id  and 是否删除=0 and 是否终止=0 and 是否回退=0) as 关联编号,
                           (select 关联数量 from 问题记录 where id=t.id   and 是否删除 = 0 and 是否终止 = 0 and 是否回退 = 0) as 关联数量,
                            0 as 重复次数,
                           ( SELECT MAX(x.服务结束时间) 服务结束时间  FROM ZLDMS.项目维保记录 x,zldms.项目产品模块  c WHERE   c.项目ID=x.项目ID AND x.项目产品模块ID=c.id  AND  c.项目ID= t.项目ID and c.产品ID=t.产品ID)服务结束时间,
	                       --(select count(1) from 问题记录 where 重复问题ID=t.重复问题id   and 是否删除=0 and 是否终止=0 and 是否回退=0) as 重复次数,
                           (select 最后提交人 from (select 姓名 as 最后提交人, 问题id  from ( select r.姓名, g.问题id from 问题记录过程 g, 人员信息 r,系统账户 zh,系统角色关系 js where  g.状态 = 5 and g.当前流程id in (4, 9, 14, 20, 23)  and r.id = g.记录人id and r.id=zh.人员id and zh.id=js.账户id and js.角色id in (5,6,11) order by 记录时间 desc ) )   where 问题id =t.id and rownum = 1) as 最后提交人,";
                if (queryModel.type == 0)
                {
                    sql += "问题描述,";
                }
                if (IsMainMontrol == "1")//是否开启脱保参数管理
                {
                    sql += "( SELECT x.应用状态 FROM ZLDMS.项目产品模块 x WHERE x.项目ID= t.项目ID and x.产品ID=t.产品ID ) as 应用状态,";
                }
                else//不管控
                {
                    sql += " 1 as 应用状态,";
                }
                    //技术支持导出excel时增加【项目最后提交时间】和【技术支持最后受理时间】
                    if (queryModel.type == 3)
                {
                    sql += @" (select 记录时间 from (  select 问题id,记录时间 from 
                            (select row_number() over(partition by g.问题id order by g.记录时间 desc) s,g.问题id,g.记录时间 from 问题记录过程 g ,
                            人员信息 x where g.记录人id = x.id and x.职务 = 12 and g.状态 = 8) t where t.s=1) js where js.问题id=t.id) as 技术支持最后受理时间,
                            (select 记录时间 from (  select 问题id,记录时间 from (select row_number() over(partition by g.问题id order by g.记录时间 desc) s,g.问题id,g.记录时间 from 问题记录过程 g , 
                            人员信息 x where g.记录人id = x.id and x.职务 != 10  and x.职务 != 11  and x.职务 != 12  and x.职务 != 113 and g.状态 = 5) t where t.s=1) js where js.问题id=t.id) as 项目最后提交时间,";
                }
                sql += @"(select 名称 from 渠道信息 where id =  t.渠道id) as 渠道,
                           (select 名称 from 项目信息 where id = t.项目ID) as 项目,
                           (select 名称 from 站点信息 where id = t.机构id) as 机构，
                           (select 名称 from 产品系统配置 where id = t.产品id) as 产品,
                           (select 名称 from 产品模块功能 where id = t.模块id) as 模块,
                           产品id，
                           版本号,
                           (select 显示名 from 基础编码 where 分类id = '2DDA172A-499A-40CC-BA6A-29AB0FAEEDCB' and 代码= t.来源代码) as 来源,
                           登记人,
                           反馈人，
                           登记日期,
                           紧急描述,
                           要求时间,
                           (select 名称 from 问题流程环节 where id = t.环节id ) as 当前环节,
                           环节ID,
                           受理人,
                           受理人ID,
                           受理时间,
                           指派人,
                           指派时间,
                           (case 是否解决 when 1 then (select  ROUND(max(记录时间)-t.登记日期,2)*24 from 问题记录过程 c where t.Id =c.问题id )
                                                    when 0 then (select ROUND(sysdate - t.登记日期,2)*24 from dual)
                                                   end
                           ) as 总耗时,
                           (case when t.是否解决=0 and t.是否终止=0 Then
                                (select ROUND(sysdate-max(ss.记录时间),2)*24 from 问题记录过程 ss where ss.问题ID=t.ID)
                                Else 0 end
                            ) as 滞留时间,
                           (select count(1) from 产品问题附件 where 问题ID = t.Id) as 附件数量,
                           (select 姓名 from 人员信息 where ID=t.验证人ID) as 验证人姓名,
                           (select 名称 from 问题分值管理 where ID=t.分类ID) as 问题分类
                     from 问题记录 t 
                     where t.是否删除=0 ";
            }
            else
            {
                sql = "select ";
                Array arry = queryModel.text.Split(',');
                foreach (string item in arry)
                {
                    switch (item)
                    {
                        case "编号": sql += "编号,"; break;
                        case "是否终止": sql += "是否终止,"; break;
                        case "是否验证": sql += "是否验证,"; break;
                        case "是否解决": sql += "是否解决,"; break;
                        case "是否回退": sql += "是否回退,"; break;
                        case "紧急代码": sql += "紧急代码,"; break;
                        case "是否受理": sql += "是否受理,"; break;
                        case "问题性质": sql += "问题性质,"; break;
                        case "问题类型": sql += "问题类型,"; break;
                        case "问题标题": sql += "问题标题,"; break;
                        case "问题描述": sql += "问题描述,"; break;
                        case "渠道": sql += "(select 名称 from 渠道信息 where id =  t.渠道id) as 渠道,"; break;
                        case "项目": sql += "(select 名称 from 项目信息 where id = t.项目ID) as 项目,"; break;
                        case "机构": sql += "(select 名称 from 站点信息 where id = t.机构id) as 机构，"; break;
                        case "产品": sql += "(select 名称 from 产品系统配置 where id = t.产品id) as 产品,"; break;
                        case "模块": sql += "(select 名称 from 产品模块功能 where id = t.模块id) as 模块,"; break;
                        case "版本号": sql += "版本号,"; break;
                        case "来源": sql += "(select 显示名 from 基础编码 where 分类id = '2DDA172A-499A-40CC-BA6A-29AB0FAEEDCB' and 代码= t.来源代码) as 来源,"; break;
                        case "登记人": sql += "登记人,"; break;
                        case "反馈人": sql += "反馈人,"; break;
                        case "登记日期": sql += "登记日期,"; break;
                        case "紧急描述": sql += "紧急描述,"; break;
                        case "要求时间": sql += "要求时间,"; break;
                        case "当前环节": sql += "(select 名称 from 问题流程环节 where id = t.环节id ) as 当前环节,"; break;
                        case "环节ID": sql += "环节ID,"; break;
                        case "受理人": sql += "受理人,"; break;
                        case "受理人ID": sql += "受理人ID,"; break;
                        case "受理时间": sql += "受理时间,"; break;
                        case "指派人": sql += "指派人,"; break;
                        case "指派时间": sql += "指派时间,"; break;
                        case "总耗时": sql += @"(case 是否解决 when 1 then (select  ROUND(max(记录时间)-t.登记日期,2)*24 from 问题记录过程 c where t.Id =c.问题id )
                                                    when 0 then(select ROUND(sysdate - t.登记日期, 2) * 24 from dual)
                                                   end
                                                ) as 总耗时,"; break;
                        case "滞留时间": sql += @"(case when t.是否解决=0 and t.是否终止=0 Then
                                                    (select ROUND(sysdate-max(ss.记录时间),2)*24 from 问题记录过程 ss where ss.问题ID=t.ID)
                                                    Else 0 end
                                                ) as 滞留时间,"; break;
                        case "附件数量": sql += "(select count(1) from 产品问题附件 where 问题ID = t.Id) as 附件数量,"; break;
                        case "验证人姓名": sql += "(select 姓名 from 人员信息 where ID=t.验证人ID) as 验证人姓名,"; break;
                        case "问题分类": sql += "(select 名称 from 问题分值管理 where ID=t.分类ID) as 问题分类,"; break;
                        case "解决方案": sql += "解决方案,"; break;
                        case "解决版本": sql += "解决版本,"; break;
                    }

                }
                sql = sql.Substring(0, sql.Length - 1) + " from 问题记录 t where t.是否删除 = 0 ";
            }
            var sqlbuilder = this.SqlBuilder;

            //若没有勾选包含查询条件
            if (!queryModel.IncludeSearchContent)
            {
                if (!string.IsNullOrEmpty(queryModel.queryText))
                {
                    var numberCode = 0;
                    if (int.TryParse(queryModel.queryText.Trim(), out numberCode))
                    {//按编号查询
                        sql += " And (t.编号 = :编号)";
                        sqlbuilder.Parameters("编号", numberCode);
                    }
                    else
                    {//按标题查询
                        sql += " And (t.问题标题 like '%'||:查询关键字||'%' or t.问题描述 like '%'||:查询关键字||'%' or t.解决方案 like'%'||:查询关键字||'%' or t.回退说明 like'%'||:查询关键字||'% ' or t.终止说明 like'%'||:查询关键字||'%' )";
                        sqlbuilder.Parameters("查询关键字", queryModel.queryText.Trim());
                    }
                }
            }
            //直接根据快速查询条件查询（其他不包含条件）
            else
            {
                if (!string.IsNullOrEmpty(queryModel.queryText))
                {
                    var numberCode = 0;
                    if (int.TryParse(queryModel.queryText.Trim(), out numberCode))
                    {//按编号查询
                        sql += " And (t.编号 = :编号)";
                        sqlbuilder.Parameters("编号", numberCode);
                    }
                    else
                    {//按标题查询
                        sql += " And (t.问题标题 like '%'||:查询关键字||'%' or t.问题描述 like '%'||:查询关键字||'%' or t.解决方案 like'%'||:查询关键字||'%' or t.回退说明 like'%'||:查询关键字||'% ' or t.终止说明 like'%'||:查询关键字||'%' )";
                        sqlbuilder.Parameters("查询关键字", queryModel.queryText.Trim());
                    }
                }

                if (!string.IsNullOrEmpty(queryModel.companyId))
                {//渠道ID
                    sql += " And t.渠道ID =:渠道ID";
                    sqlbuilder.Parameters("渠道ID", queryModel.companyId);
                }

                if (!string.IsNullOrEmpty(queryModel.projectId))
                {//项目ID
                    sql += " And t.项目ID =:项目ID";
                    sqlbuilder.Parameters("项目ID", queryModel.projectId);
                }

                if (!string.IsNullOrEmpty(queryModel.orgId))
                {//机构ID
                    sql += " And t.机构ID =:机构ID";
                    sqlbuilder.Parameters("机构ID", queryModel.orgId);
                }

                if (!string.IsNullOrEmpty(queryModel.productId) || !string.IsNullOrEmpty(queryModel.productModuleId))
                {
                    sql += " And ( ";
                    //产品ID不为空，则需要根据产品来过滤
                    if (!string.IsNullOrEmpty(queryModel.productId))
                    {
                        sql += " 产品ID in (Select Column_Value From Table(f_Split_String(:产品ID, ','))) or";
                        sqlbuilder.Parameters("产品ID", queryModel.productId);
                    }

                    if (!string.IsNullOrEmpty(queryModel.productModuleId))
                    {//模块ID不为空
                        sql += " 模块ID in (Select Column_Value From Table(f_Split_String(:模块ID, ','))) ";
                        sqlbuilder.Parameters("模块ID", queryModel.productModuleId);
                    }
                    else
                    {
                        sql = sql.Substring(0, sql.Length - 2);
                    }
                    sql += " )";
                }

                if (queryModel.property > 0)
                {//问题性质
                    sql += " And t.问题性质 =:问题性质";
                    sqlbuilder.Parameters("问题性质", queryModel.property);
                }

                if (queryModel.priority > 0)
                {//紧急程度
                    sql += " And t.紧急代码 =:紧急代码";
                    sqlbuilder.Parameters("紧急代码", queryModel.priority);
                }

                if (!string.IsNullOrEmpty(queryModel.workflowNode))
                {//环节ID
                    sql += " And t.环节ID in (Select Column_Value From Table(f_Split_String(:环节ID, ',')))";
                    sqlbuilder.Parameters("环节ID", queryModel.workflowNode);
                    //2024-02-04 调整维保过期，项目可以看，技术和产品不允许显示
                    if(IsMainMontrol=="1")
                    {
                        if (queryModel.workflowNode == "4"|| queryModel.workflowNode == "5")//技术和产品
                        {

                            sql += " And exists (select 1 from 项目产品模块 p  where p.项目ID = t.项目ID and t.产品ID= p.产品ID And p.应用状态=1)";
                          
                        }

                    }


                }

                if (!string.IsNullOrEmpty(queryModel.DealPerson))
                {//问题处理人
                    sql += " And t.受理人 like '%' || :受理人 || '%' ";
                    sqlbuilder.Parameters("受理人", queryModel.DealPerson);
                }

                if (!string.IsNullOrEmpty(queryModel.RegistPerson))
                {//问题登记人
                    sql += " And t.登记人 like '%' || :登记人 || '%' ";
                    sqlbuilder.Parameters("登记人", queryModel.RegistPerson);
                }

                if (!string.IsNullOrEmpty(queryModel.DistributePerson))
                {//问题指派人
                    sql += " And t.指派人 like '%' || :指派人 || '%' ";
                    sqlbuilder.Parameters("指派人", queryModel.DistributePerson);
                }

                if (!string.IsNullOrEmpty(queryModel.myProblemCombox) || queryModel.myProblem)
                {
                    sql += " And (";
                    //与我相关
                    if (queryModel.myProblem)
                    {
                        sql += " (t.受理人ID =:用户ID or t.指派人ID = :用户ID )  or";
                    }

                    //我登记/指派/提交的问题，【，】分割
                    if (!string.IsNullOrEmpty(queryModel.myProblemCombox))
                    {
                        sql += @" (t.ID in (select distinct(s.问题id) from 问题记录过程 s
                                                 where  s.记录人ID=:用户ID 
                                                 and s.状态 in (Select Column_Value From Table(f_Split_String(:myProblemCombox, ',')))))";
                    }
                    else
                    {
                        sql = sql.Substring(0, sql.Length - 2);
                    }
                    sql += " )";
                    sqlbuilder.Parameters("用户ID", queryModel.currentUserID);
                    sqlbuilder.Parameters("myProblemCombox", queryModel.myProblemCombox);
                }

                if (queryModel.isend)
                {
                    sql += " And t.是否解决 = 1";
                }
                else if (!queryModel.showCloseProblem && queryModel.zzstartDate == DateTime.MinValue && queryModel.zzendDate == DateTime.MinValue)
                {
                   
                    //只看终止问题
                    if (queryModel.showTerminIssues)
                    {
                        sql += " And t.是否终止 = 1";
                    }
                    else
                    {
                        //不需要显示已解决与已关闭的问题(只显示正常处理的问题)
                        sql += " And t.是否终止 = 0 And t.是否解决 = 0";
                    }

                }

                if (queryModel.startDate != DateTime.MinValue)
                {
                    sql += " And t.登记日期 >=:开始日期";
                    sqlbuilder.Parameters("开始日期", queryModel.startDate);
                }

                //登记结束日期
                if (queryModel.endDate != DateTime.MinValue)
                {
                    queryModel.endDate = queryModel.endDate.AddDays(1);
                    sql += " And t.登记日期 <=:结束日期";
                    sqlbuilder.Parameters("结束日期", queryModel.endDate);
                }

                //解决/终止开始日期
                if (queryModel.zzstartDate != DateTime.MinValue)
                {
                    sql += " And (t.终止日期 >=:开始日期 or (select  to_date(to_char(记录时间,'yyyy-mm-dd'),'yyyy-mm-dd') 记录时间 from 问题记录过程 where 问题ID=t.id and 状态=7 and rownum=1 ) >=:开始日期 )";
                    sqlbuilder.Parameters("开始日期", queryModel.zzstartDate);
                }

                //解决/终止结束日期
                if (queryModel.zzendDate != DateTime.MinValue)
                {
                    sql += " And (t.终止日期 <=:结束日期 or (select  to_date(to_char(记录时间,'yyyy-mm-dd'),'yyyy-mm-dd') 记录时间 from 问题记录过程 where 问题ID=t.id and 状态=7  and rownum=1 ) <=:结束日期 )";
                    sqlbuilder.Parameters("结束日期", queryModel.zzendDate);
                }

                //只显示已受理的问题
                if (queryModel.showReceiveProblem)
                {
                    sql += " And t.是否受理 = 1";
                }

                //迭代
                if (!string.IsNullOrEmpty(queryModel.lteration))
                {
                    sql += " And t.迭代ID=:DDID";
                    sqlbuilder.Parameters("DDID", queryModel.lteration);
                }

                //登记版本
                if (!string.IsNullOrEmpty(queryModel.version))
                {
                    sql += " And t.版本号=:BBH";
                    sqlbuilder.Parameters("BBH", queryModel.version);
                }

                //研发处理人
                if (!string.IsNullOrEmpty(queryModel.YFCLRID))
                {
                    sql += " And t.研发处理人ID=:YFCLRID";
                    sqlbuilder.Parameters("YFCLRID", queryModel.YFCLRID);
                }

                //测试处理人
                if (!string.IsNullOrEmpty(queryModel.CSCLRID))
                {
                    sql += " And t.测试处理人ID=:CSCLRID";
                    sqlbuilder.Parameters("CSCLRID", queryModel.CSCLRID);
                }
                //受理人id
                if (!string.IsNullOrEmpty(queryModel.SLRID))
                {
                    sql += " And t.受理人ID=:SLRID";
                    sqlbuilder.Parameters("SLRID", queryModel.SLRID);
                }


                //解决版本 0:未指定 1:指定 2：全部
                if (queryModel.whether != 2)
                {
                    if (queryModel.whether == 0)
                    {
                        sql += " And t.解决版本 is  null";
                    }
                    else
                    {
                        //是否指定解决版本
                        if (!string.IsNullOrEmpty(queryModel.solutionversion))
                        {
                            sql += " And t.解决版本=:JJBBH";
                            sqlbuilder.Parameters("JJBBH", queryModel.solutionversion);
                        }
                        else
                        {
                            sql += " And t.解决版本 is not null";
                        }
                    }
                }

                //解决版本状态
                if (!string.IsNullOrEmpty(queryModel.JJBBZT))
                {
                    sql += " And t.解决版本状态=:JJBBZT";
                    sqlbuilder.Parameters("JJBBZT", queryModel.JJBBZT);
                }
            }

            //根据人员性质，加载允许的权限数据（中联性质的人员可以查询全部）
            //（1-中联;2-中联渠道;3-客户;4-医院管理员;5-卫计委管理员）
            if (queryModel.currentUserProperty == 2)
            {
                //中联渠道人员，允许查询渠道内的问题
                if (!string.IsNullOrWhiteSpace(queryModel.queryText))
                {
                    //放开权限
                    sql += @" And exists (select 1 from 人员信息
                                    where ID = :人员ID)";
                }
                else
                {
                    //未放开权限
                    if (queryModel.myProblem || !string.IsNullOrEmpty(queryModel.myProblemCombox) || !queryModel.projectId.IsNullOrEmpty()|| iscoll==0)//勾上待我处理、我登记、我分配，只看我自己机构的问题，不看其他渠道问题
                    {
                        sql += @" And exists (select 1 from 人员信息
                                where (人员信息.ID = :人员ID and t.渠道id = 人员信息.渠道id ) )
";
                    }
                    else
                    {
                        sql += @" And exists (select 1 from 人员信息
                                where (人员信息.ID = :人员ID and t.渠道id = 人员信息.渠道id ) )
or exists (select 1 from 人员信息,渠道人员共享
                                where (人员信息.ID =:人员ID and 人员信息.渠道id=渠道人员共享.共享渠道ID and t.渠道id = 渠道人员共享.渠道id )  
                          
                                )
";
                    }


          
                
                }

                sqlbuilder.Parameters("人员ID", queryModel.currentUserID);
            }
            else if (queryModel.currentUserProperty == 3)
            {//客户操作人员（只看自己的）
                sql += " And t.登记人ID = :登记人ID";
                sqlbuilder.Parameters("登记人ID", queryModel.currentUserID);
            }
            else if (queryModel.currentUserProperty == 4)
            {//机构管理员，只看本机构的
                sql += @" And exists (select 1 from 人员信息 p
                                where ID = :人员ID and t.渠道id= p.渠道id And t.机构id = p.站点id)";
                sqlbuilder.Parameters("人员ID", queryModel.currentUserID);
            }
            else if (queryModel.currentUserProperty == 5)
            {//卫计委管理员，允许查看该项目下的问题
                sql += @" And exists (select 1 from 人员信息 p
                                where ID = :人员ID and t.渠道id= p.渠道id )";
                sqlbuilder.Parameters("人员ID", queryModel.currentUserID);
            }

            // 是否显示内部产品问题
            if (!queryModel.insideProblem)
            {
                sql += " And t.内部产品问题=0";
            }

            //自定义排序
            sql += queryModel.oderBy;
            if (queryModel.page > 0 && queryModel.rows > 0)
            {
                return sqlbuilder.SqlText(sql)
                    .Paging(queryModel.page, queryModel.rows)
                    .Select(out total);
            }
            else
            {
                return sqlbuilder.SqlText(sql).Select();
            }


        }

        /// <summary>
        /// 重复问题列表所用的数据
        /// </summary>
        /// <param name="user"></param>
        /// <returns></returns>
        public DataTable AllProList(UserInfo userInfo)
        {
            string sql = "select  ID,编号,问题标题, '[' ||编号||']'||问题标题 as 显示名称  from 问题记录 where 是否删除=0 and 是否回退=0 and 是否终止=0";
            return SqlBuilder.SqlText(sql).Select();
        }

        //受理该问题（如果已处理返回false）
        public bool ReceiveProblem(string id, string userid, string name)
        {
            var sql = @"update 问题记录 
                        set 受理人 = :受理人,
                            受理人ID=:受理人ID, 
                            是否受理 = 1,
                            受理时间=sysdate 
                       where ID = :ID and ( 是否受理 = 0 or 受理人ID=:受理人ID or 指派人ID=:指派人ID)";

            return this.SqlBuilder.SqlText(sql)
                  .Parameters("受理人", name)
                  .Parameters("受理人ID", userid)
                  .Parameters("指派人ID", userid)
                  .Parameters("ID", id)
                  .Execute() > 0;
            //return this.UpdateBuilder
            //      .Column("受理人", name)
            //      .Column("受理人ID", userid)
            //      .Column("受理时间", this.DataBaseBuilder.GetDateTime)
            //      .Column("是否受理", 1)
            //      .Update("问题记录")
            //      .Where("ID", id)
            //      .Where("是否受理", 0) 
            //      .Execute() > 0;
        }
        /// <summary>
        /// 判断问题是否被他人受理
        /// </summary>
        /// <param name="id"></param>
        /// <param name="userid"></param>
        /// <returns></returns>
        public int IsReceive(string id, string userid)
        {
            var status = 0;
            var sql = @"select t.ID from  问题记录 t  where t.ID=:id
                             and  t.是否受理 = 1 ";
            if (!String.IsNullOrEmpty(this.SqlBuilder.SqlText(sql).Parameters("id", id).Select<string>()))
            {
                //如果已受理，判断是否为我受理
                sql += " and ( t.受理人ID=:userid or t.指派人ID=:userid )";
                var t = this.SqlBuilder.SqlText(sql).Parameters("id", id).Parameters("userid", userid).Select<string>();
                if (!String.IsNullOrEmpty(t))
                {
                    status = 2;
                }
                else
                {
                    status = 1;
                }
            }
            return status;
        }

        //强制受理这个问题
        public bool TryReceiveProblem(string id, string userid, string name)
        {
            return this.UpdateBuilder
                              .Column("受理人", name)
                              .Column("受理人ID", userid)
                              .Column("受理时间", this.DataBaseBuilder.GetDateTime)
                              .Column("是否受理", 1)
                              .Update("问题记录")
                              .Where("ID", id)
                              .Execute() > 0;
        }
        //获取最近处理描述
        public ProblemDealMessage GetRecentlyDetail(string ProblemId)
        {
            try
            {
                return new ProblemDealMessage
                {
                    DealDetail = this.SqlBuilder.SqlText("select * from (select  描述  from 问题记录过程 where 问题ID= :ProblemId and 状态=6 order by 记录时间 desc) where rownum=1").Parameters("ProblemId", ProblemId).Select<string>(),
                    SolveDetail = this.SqlBuilder.SqlText("select * from (select  描述  from 问题记录过程 where 问题ID= :ProblemId and 状态=7 order by 记录时间 desc) where rownum=1").Parameters("ProblemId", ProblemId).Select<string>()
                };
            }
            catch (Exception ex)
            {
                Logger.Instance.Error("调用GetRecentlyDetail出错", ex);
                return new ProblemDealMessage();
            }

        }


        //取消受理问题
        public bool CancelReceiveProblem(string id, string PersonId)
        {
            using (var tran = this.DBTransaction.BeginTrans())
            {
                try
                {
                    var up = this.UpdateBuilder
                      .Column("是否受理", 0)
                      .Column("受理人", DBNull.Value)
                      .Column("受理人ID", DBNull.Value)
                      .Column("受理时间", DBNull.Value)
                      .Update("问题记录")
                      .Where("ID", id)
                      .Execute(tran);
                    var dt = this.SqlBuilder.SqlText("Select * from (Select 当前流程ID,记录时间 from 问题记录过程 where 问题ID=:id order by 记录时间 desc) where rownum=1")
                                            .Parameters("id", id).Select();
                    var ProcessId = dt.Rows[0].GetValueByName<int>("当前流程ID");
                    //计算耗时
                    var ProcessTime = dt.Rows[0].GetValueByName<DateTime>("记录时间");
                    DateTime CurrentTime = this.DataBaseBuilder.GetDateTime;
                    TimeSpan time = CurrentTime - ProcessTime;
                    var PassTime = time.TotalMinutes / 60;
                    //添加过程
                    var inserProcess = this.InsertBuilder
                                         .Column("ID", Guid.NewGuid().ToString())
                                         .Column("问题ID", id)
                                         .Column("记录人ID", PersonId)
                                         .Column("记录时间", CurrentTime)
                                         .Column("当前流程ID", ProcessId)
                                         .Column("状态", 9)
                                         .Column("来源流程ID", ProcessId)
                                         .Column("耗时", PassTime)
                                         .Insert("问题记录过程")
                                         .Execute(tran);
                    tran.CommitTrans();
                    return (up > 0 && inserProcess > 0);

                }
                catch (Exception ex)
                {
                    tran.Rollback();
                    Logger.Instance.Error("取消受理失败", ex);
                    return false;
                }

            }
        }

        //查询用户信息
        public DataTable QueryUserInfo(string uid)
        {
            var sql = "select 性质,职务 from 人员信息 where ID=:ID";
            return this.SqlBuilder.SqlText(sql)
                .Parameters("ID", uid)
                .Select();
        }

        /// <summary>
        /// 查询需要处理的问题列表
        /// </summary>
        /// <param name="type">1：技术支持，2：研发处理</param>
        /// <param name="total">输出总记录数量</param>
        /// <returns>返回json数据</returns>
        public DataTable QueryNeedProccessList(int type, string productid, string productModuleId, int page, int rows, out int total)
        {
            total = 0;
            var sqlbuilder = this.SqlBuilder;
            var sql = @"select ID,编号,紧急代码,问题标题,问题性质,登记人,反馈人,登记日期,
                            (select 名称 from 渠道信息 where ID = 问题记录.渠道id) as 渠道,
                            (select 名称 from 产品系统配置 where id = 问题记录.产品id) as 产品,
                            (select count(1) from 产品问题附件 where 问题id = 问题记录.id) as 附件数量
                        from 问题记录 
                        where 是否删除 = 0 
                            And 是否终止 = 0 
                            And 是否解决 = 0 
                            And 是否受理 = 0";
            if (1 == type)
            {//技术支持（只显示技术支持需要处理的问题）
                sql += " And 环节ID=4";
            }
            else if (2 == type)
            {//研发支持（只显示研发需要处理的）
                sql += " And 环节ID=5";
            }
            else
            {//查询类型不明确
                return null;
            }

            if (!string.IsNullOrEmpty(productid) || !string.IsNullOrEmpty(productModuleId))
            {
                sql += " And ( ";
                //产品ID不为空，则需要根据产品来过滤
                if (!string.IsNullOrEmpty(productid))
                {
                    sql += " 产品ID in (Select Column_Value From Table(f_Split_String(:产品ID, ','))) or";
                    sqlbuilder.Parameters("产品ID", productid);
                }

                if (!string.IsNullOrEmpty(productModuleId))
                {//模块ID不为空
                    sql += " 模块ID in (Select Column_Value From Table(f_Split_String(:模块ID, ','))) ";
                    sqlbuilder.Parameters("模块ID", productModuleId);
                }
                else
                {
                    sql = sql.Substring(0, sql.Length - 2);
                }
                sql += " )";
            }

            sql += " Order By 编号 DESC";

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

        //处理问题时更新的问题附加信息

        public bool UpdateProblemInfo(ProblemProcessModel model, UserInfo user)
        {
            bool result;
            using (ITransaction transaction = base.DBTransaction.BeginTrans())
            {
                try
                {
                    WTJLModel model2 = this.GetModel(model.ProblemID);
                    bool identify = model.Identify;
                    if (identify)
                    {
                        base.InsertBuilder.Insert("项目个性化").Column("ID", Guid.NewGuid().ToString()).Column("项目ID", model2.XMID).Column("产品ID", model2.CPID).Column("修改人", user.Name).Column("修改时间", base.DataBaseBuilder.GetDateTime).Column("名称", model2.WTBT).Column("修改性质", (user.JobCode.ToInt32() == 10 || user.JobCode.ToInt32() == 11) ? 1 : ((user.JobCode.ToInt32() == 7 || user.JobCode.ToInt32() == 12 || user.JobCode.ToInt32() == 13) ? 2 : 3)).Column("状态", 1).Column("来源问题", model2.BH).Column("修改说明", (model2.XGSM == null) ? model.ProcessRemark : model2.XGSM).Execute();
                    }
                    else
                    {
                        base.SqlBuilder.SqlText("update 项目个性化 set 状态=2 where 来源问题=:BH").Parameters("BH", model2.BH).Execute();
                    }
                    base.UpdateBuilder.Update("问题记录").Column("红黑榜", model.RedBlack).Column("红黑榜理由", model.RedBlackContent).Column("黑榜问题描述", model2.WTMS)
                        .Column("问题类型", model.IsItem ? 1 : 2).Column("解决版本", model.SolveVersion.IsNullOrEmpty()?model2.JJBB: model.SolveVersion).Column("解决版本状态", model.SolveVersionstate).Column("BH版本", model.BHVersion).Column("BH编号", model.BHNumber).Column("常见问题", model.IsOften ? 1 : 0).Column("修改说明", model.AlterRemark).Column("升级说明", model.UpgrateRemark).Column("是否发布", model.IsRelease ? 1 : 0).Column("需要培训", model.IsTrain ? 1 : 0).Where("ID", model.ProblemID).Execute();
                    transaction.CommitTrans();
                    result = true;
                }
                catch (Exception ex)
                {
                    transaction.Rollback();
                    Logger.Instance.Error("处理问题操作的更新问题附加信息时失败,方法UpdateProblemInfo", ex);
                    result = false;
                }
            }
            return result;
        }

        //根据过程ID查询出相应的过程详细信息
        public dynamic QueryProcessInfo(string pid)
        {
            var sql = @"select t.编号，t.问题标题,t.ID,
                               (select p.姓名 from 人员信息 p where p.Id =r.记录人id ) as 处理人,
                               r.记录时间,
                               r.描述,
                               decode(r.状态,1,'登记',2,'回退',3,'终止',4,'分配',5,'提交',6,'处理',7,'完成',8,'受理') as 状态,
                               (Select Wm_Concat(附件id || '|' || 类型) From 产品问题附件 Where 过程ID = r.Id) As 附件 
       
                         from 问题记录过程 r, 问题记录 t
                         where r.ID = :过程ID
                               and r.问题id = t.Id";
            var dt = this.SqlBuilder.SqlText(sql)
                 .Parameters("过程ID", pid)
                 .Select();

            var list = dt.ToList<dynamic>();
            if (list != null && list.Count > 0)
            {
                return list[0];
            }

            return null;
        }

        //查询问题记录
        public EntityModels.Product.WTJLModel GetModel(string id)
        {
            EntityModels.Product.WTJLModel model = new EntityModels.Product.WTJLModel(this.DataFactoryObject);
            model.ID = id;
            model.Select();

            return model;
        }
        //获取问题记录datatable
        public DataTable GetProblemById(string id)
        {
            return this.SqlBuilder.SqlText("Select 是否终止,是否解决,环节ID,编号,ID from 问题记录 where ID=:id").Parameters("id", id).Select();
        }
        public string GetWTID(string Processid)
        {
            return this.SqlBuilder.SqlText("Select 问题ID from 问题记录过程 where ID=:id").Parameters("id", Processid).Select<string>();
        }

        //撤消问题
        public int UndoProblem(string id)
        {
            return this.UpdateBuilder
                .Column("是否终止", 0)
                .Column("是否解决", 0)
                .Update("问题记录")
                .Where("ID", id)
                .Execute();
        }
        public string CheckUpdateProcess(string ProcessId, string UserId)
        {
            var sql = @"select ID,记录人ID from 问题记录过程 
                        where 问题ID=(select 问题ID from 问题记录过程 where ID=:id)
                        order by 记录时间 desc";
            var dt = this.SqlBuilder.SqlText(sql).Parameters("id", ProcessId).Select();
            var CurrentProcess = dt.Rows[0].GetValueByName<string>("ID");
            var CurrenUserId = dt.Rows[0].GetValueByName<string>("记录人ID");
            if (CurrentProcess != ProcessId)
            {
                return "1";  //只能修改最新的流程
            }
            if (CurrenUserId != UserId)
            {
                return "2"; //只能修改自己提交的过程
            }
            else
            {
                return "3";
            }
        }
        public int UpdateProblemProcess(EntityModels.Product.WTJLGCModel model)
        {
            return this.UpdateBuilder.Column("描述", model.MS).Update("问题记录过程").Where("ID", model.ID).Execute();
        }
        //登记问题时获取推送人
        public List<string> GetPushPerson(string ProblemId)
        {
            try
            {
                var list = new List<string>();
                //获取问题信息
                var ProblemModel = GetModel(ProblemId);
                //如果为项目环节,通知项目服务人员
                if (ProblemModel.HJID <= 3)
                {
                    //获取项目服务人员
                    var ProjectPerson = this.SqlBuilder.SqlText("select 人员ID from 项目服务人员 where 项目ID=:id").Parameters("id", ProblemModel.XMID).Select();
                    //如果不为空则通知维护的项目人员
                    if (ProjectPerson != null && ProjectPerson.Rows.Count > 0)
                    {
                        foreach (DataRow row in ProjectPerson.Rows)
                        {
                            list.Add(row.GetValueByName<string>("人员ID"));
                        }
                    }
                    else
                    {
                        //通知渠道下所有人员
                        var ProjectPersonAll = this.SqlBuilder.SqlText("select ID from 人员信息 where 渠道ID=:id and 是否删除=0 and 性质=2").Parameters("id", ProblemModel.QDID).Select();
                        if (ProjectPersonAll != null && ProjectPersonAll.Rows.Count > 0)
                        {
                            foreach (DataRow row in ProjectPersonAll.Rows)
                            {
                                list.Add(row.GetValueByName<string>("ID"));
                            }
                        }
                    }

                }
                else if (ProblemModel.HJID == 4)
                {
                    //如果模块ID不为空
                    if (!string.IsNullOrEmpty(ProblemModel.MKID))
                    {
                        //获取测试人ID
                        var TestPerson = this.SqlBuilder.SqlText("select 测试人IDNEW from 产品模块功能 where ID=:id and 测试人IDNEW is not null").Parameters("id", ProblemModel.MKID).Select<string>();
                        if (!string.IsNullOrEmpty(TestPerson))
                        {
                            var listAll = TestPerson.Split(',');
                            foreach (string item in listAll)
                            {
                                list.Add(item);
                            }
                        }
                        else
                        {
                            //如果不存在测试人则通知所有总部人员
                            var SurpportPerson = this.SqlBuilder.SqlText("select ID from 人员信息 where  性质='1' and 是否删除=0 and 职务='12'").Select();
                            if (SurpportPerson != null && SurpportPerson.Rows.Count > 0)
                            {
                                foreach (DataRow row in SurpportPerson.Rows)
                                {
                                    list.Add(row.GetValueByName<string>("ID"));
                                }
                            }
                        }
                    }
                    else
                    {
                        //如果不存在模块则通知所有总部人员技术支持
                        var SurpportPerson = this.SqlBuilder.SqlText("select ID from 人员信息 where  性质='1' and 是否删除=0 and 职务='12'").Select();
                        if (SurpportPerson != null && SurpportPerson.Rows.Count > 0)
                        {
                            foreach (DataRow row in SurpportPerson.Rows)
                            {
                                list.Add(row.GetValueByName<string>("ID"));
                            }
                        }
                    }
                }

                return list;
            }
            catch (Exception ex)
            {
                Logger.Instance.Error("获取推送人发送错误,", ex);
                return new List<string>();
            }

        }
        //获取问题流程的主流程
        public List<MainProcessModel> GetMainProcess(string ProblemId)
        {
            try
            {
                var list = new List<MainProcessModel>();
                var mainSql = @"select ID,是否验证,环节ID,流程ID,登记人,登记日期,是否终止,终止日期,是否解决,指派人,指派时间,
                                 decode(环节ID,0,'已登记',1,'机构处理',2,'卫计委处理',3,'项目处理',4,'技术支持处理',5,'研发处理') as 环节名,
                                 (select  decode(t.性质,1,'总部',2,'渠道人员',3,'客户',4,'医院管理员',5,'卫计委管理员') as 职务  from 人员信息 t where t.ID=登记人ID) as 职务,
                                    (select  decode(s.性质,1,'总部',2,'渠道人员',3,'客户',4,'医院管理员',5,'卫计委管理员') as 指派人职务  from 人员信息 s where s.ID=指派人ID) as 指派人职务
                                  from 问题记录 where ID=:id";
                var CurrentDt = this.SqlBuilder.SqlText(mainSql).Parameters("id", ProblemId).Select();
                //添加登记过程
                var RegistPerson = CurrentDt.Rows[0].GetValueByName<string>("登记人");//登记人
                var AssigneePerson = CurrentDt.Rows[0].GetValueByName<string>("指派人");
                var AssigneeTime = CurrentDt.Rows[0].GetValueByName<DateTime>("指派时间");
                list.Add(new MainProcessModel { StatusID = 1, LinkId = 0, LinkName = "登记", LinkUserName = RegistPerson, LinkTime = CurrentDt.Rows[0].GetValueByName<DateTime>("登记日期"), job = CurrentDt.Rows[0].GetValueByName<string>("职务") });
                //获取当前环节
                var CurrentLink = CurrentDt.Rows[0].GetValueByName<int>("环节ID");
                if (CurrentLink > 0)
                {

                    for (var i = 1; i <= CurrentLink; i++)
                    {
                        var sql = @"select *  from 
                                 (select s.当前流程ID,t.姓名,
                                  decode(:CurrentLink,0,'已登记',1,'机构处理',2,'卫计委处理',3,'项目处理',4,'技术支持处理',5,'研发处理') as 环节名,
                                   s.记录时间,s.描述,s.ID,s.状态,
                                  decode(t.性质,1,'总部',2,'渠道人员',3,'客户',4,'医院管理员',5,'卫计委管理员') as 职务
                                 from 问题记录过程 s,人员信息 t
                                 where s.当前流程ID in (select ID from 问题工作流程 where 环节ID=:CurrentLink)
                                 and s.记录人ID= t.ID
                                 and s.问题ID=:ProblemId
                                 and s.状态!= 1
                                 order by s.记录时间 desc) 
                                 where rownum=1";
                        var processDt = this.SqlBuilder.SqlText(sql).Parameters("CurrentLink", i).Parameters("ProblemId", ProblemId).Select();
                        if (processDt != null && processDt.Rows.Count > 0)
                        {
                            var CurrentRegisName = processDt.Rows[0].GetValueByName<string>("姓名");
                            var CurrentRegisJob = processDt.Rows[0].GetValueByName<string>("职务");
                            var Status = processDt.Rows[0].GetValueByName<int>("状态");
                            var CurrentRegislist = new MainProcessModel { LinkId = i, LinkName = processDt.Rows[0].GetValueByName<string>("环节名") };
                            ///如果不为提交或回退、指派
                            if (Status != 2 && Status != 5 && Status != 9 && Status != 10 & Status != 4)
                            {
                                CurrentRegislist.LinkUserName = CurrentRegisName;
                                CurrentRegislist.job = CurrentRegisJob;
                                CurrentRegislist.LinkTime = processDt.Rows[0].GetValueByName<DateTime>("记录时间");
                                CurrentRegislist.LinkDeatil = processDt.Rows[0].GetValueByName<string>("描述");
                                CurrentRegislist.ProcessId = processDt.Rows[0].GetValueByName<string>("ID");
                            }
                            else
                            {
                                if (Status == 5 && i == CurrentLink && !string.IsNullOrEmpty(AssigneePerson))
                                {
                                    CurrentRegislist.LinkUserName = "提交给→" + AssigneePerson;
                                    CurrentRegislist.job = CurrentRegisJob;
                                    CurrentRegislist.LinkTime = AssigneeTime;
                                }
                                //指派
                                if (Status == 4)
                                {
                                    CurrentRegislist.LinkUserName = "处理人变更→" + AssigneePerson;
                                    CurrentRegislist.job = CurrentRegisJob;
                                    CurrentRegislist.LinkTime = AssigneeTime;
                                    CurrentRegislist.ProcessId = processDt.Rows[0].GetValueByName<string>("ID");
                                }

                            }

                            list.Add(CurrentRegislist);
                        }
                    }
                    //是否终止
                    if (CurrentDt.Rows[0].GetValueByName<int>("是否终止") == 1)
                    {
                        list.Add(new MainProcessModel { StatusID = 3, LinkName = "已终止", LinkTime = CurrentDt.Rows[0].GetValueByName<DateTime>("终止日期") });
                    }
                    //是否完成
                    if (CurrentDt.Rows[0].GetValueByName<int>("是否解决") == 1)
                    {//是否验证
                        if (CurrentDt.Rows[0].GetValueByName<int>("是否验证") == 1)
                        {
                            list.Add(new MainProcessModel { StatusID = 11, LinkName = "已验证" });
                        }
                        else
                        {
                            list.Add(new MainProcessModel { StatusID = 7, LinkName = "已完成" });
                        }

                    }
                }
                return list;
            }
            catch (Exception ex)
            {
                Logger.Instance.Error("获取问题流程的主流程失败，原因:" + ex);
                return new List<MainProcessModel>();
            }


        }

        #endregion

        #region 其他操作
        /// <summary>
        /// 判断是否处理过该问题
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public bool IsDealed(string id, string PersonId)
        {
            return this.SqlBuilder.SqlText("select ID from 问题记录过程 where 问题ID=:id and 记录人ID=:PersonId").Parameters("id", id).Parameters("PersonId", PersonId).Exists();
        }
        /// <summary>
        /// 判断问题是否完结并处理推送评价
        /// </summary>
        /// <param name="id"></param>
        /// <param name="PersonId"></param>
        /// <returns></returns>
        public bool IsConfirmClosing(string id, string PersonId)
        {
            var state= this.SqlBuilder.SqlText("select ID from 问题记录 where id=:id and 登记人ID=:PersonId   and (是否解决=1 or 是否终止=1 )").Parameters("id", id).Parameters("PersonId", PersonId).Exists();
      if(state)
            {
                //UpdateBuilder.Update("问题记录").Column("").Where("id", id).Execute();
            }
            return state;


        }

        /// <summary>
        /// 问题归类
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public bool DoClassifi(string id, string ClssID,int nature, UserInfo user)
        {
            if (user.PersonProperty == 1)
            {
                return (base.UpdateBuilder.Update("问题记录").Where("ID", id).Column("技术支持分类ID", ClssID).Column("问题性质", nature).Execute() > 0);
            }
            else
            {
                return (base.UpdateBuilder.Update("问题记录").Where("ID", id).Column("分类ID", ClssID).Column("问题性质", nature).Execute() > 0);
            }
        }
        #endregion

        #region 超时推送
        /// <summary>
        /// 是否开启超时推送
        /// </summary>
        /// <param name="user"></param>
        /// <returns></returns>
        public bool GetIsOverTime(UserInfo user)
        {
            var sqlbulider = this.SqlBuilder;
            var sql = @"select b.ID from 问题流程环节 b where b.人员性质=:PersonProperty and b.超时启用=1 ";
            sqlbulider.Parameters("PersonProperty", user.PersonProperty);
            ///如果为本部人员
            if (user.PersonProperty == 1)
            {
                sql += @" and Exists
                           (select column_value from table(Select f_Split_String(x.职务代码1, ',') from 问题流程环节 x
                            where id = b.Id)
                            where Column_Value =:Job) ";
                sqlbulider.Parameters("Job", Convert.ToInt32(user.JobCode));
            }
            return sqlbulider.SqlText(sql).Exists();
        }
        /// <summary>
        /// 获取受理超时
        /// 
        /// </summary>
        /// <param name="user"></param>
        /// <returns></returns>
        public DataTable GetAcceptOverTime(UserInfo user)
        {
            try
            {
                //获取推送人所在环节
                var sqlbulider = this.SqlBuilder;
                var sql = @"select b.ID from 问题流程环节 b where b.人员性质=:PersonProperty ";
                sqlbulider.Parameters("PersonProperty", user.PersonProperty);
                ///如果为本部人员
                if (user.PersonProperty == 1)
                {
                    sql += @" and Exists
                           (select column_value from table(Select f_Split_String(x.职务代码1, ',') from 问题流程环节 x
                            where id = b.Id)
                            where Column_Value =:Job) ";
                    sqlbulider.Parameters("Job", Convert.ToInt32(user.JobCode));
                }
                var LinkId = sqlbulider.SqlText(sql).Select<int>();
                //如果不存在
                if (LinkId <= 0)
                {
                    return new DataTable();
                }
                //获取超时的问题
                var sql2 = @"select a.ID ,a.编号
                         from 问题记录 a,问题流程环节 b
                         where a.环节ID=b.ID
                         and a.是否受理=0
                         and a.是否终止=0
                         and a.是否解决=0
                         and a.是否删除=0
                         and b.ID=:LinkId
                         and b.超时启用=1
                         and (select ROUND(sysdate-max(ss.记录时间),3)*24*60 from 问题记录过程 ss where ss.问题ID=a.ID)>b.受理时间  order by a.编号 desc";
                var ProblemDt = this.SqlBuilder.SqlText(sql2).Parameters("LinkId", LinkId).Select();
                return ProblemDt;

            }
            catch (Exception ex)
            {
                Logger.Instance.Error("超时推送调用GetAcceptOverTime出错，原因:", ex);
                return new DataTable();
            }
        }
        /// <summary>
        /// 获取处理超时
        /// </summary>
        /// <param name="user"></param>
        public DataTable GetDealOverTime(UserInfo user)
        {
            try
            {
                //获取推送人所在环节
                var sqlbulider = this.SqlBuilder;
                var sql = @"select b.ID from 问题流程环节 b where b.人员性质=:PersonProperty ";
                sqlbulider.Parameters("PersonProperty", user.PersonProperty);
                ///如果为本部人员
                if (user.PersonProperty == 1)
                {
                    sql += @" and Exists
                           (select column_value from table(Select f_Split_String(x.职务代码1, ',') from 问题流程环节 x
                            where id = b.Id)
                            where Column_Value =:Job) ";
                    sqlbulider.Parameters("Job", Convert.ToInt32(user.JobCode));
                }
                var LinkId = sqlbulider.SqlText(sql).Select<int>();
                //如果不存在
                if (LinkId <= 0)
                {
                    return new DataTable();
                }
                //获取超时的问题
                var sql2 = @"select a.ID ,a.编号
                         from 问题记录 a,问题流程环节 b
                         where a.环节ID=b.ID
                         and a.是否受理=1
                         and a.是否终止=0
                         and a.是否解决=0
                         and a.是否删除=0
                         and (a.受理人ID=:PersonId or  a.指派人ID=:PersonId)
                         and b.ID=:LinkId
                         and b.超时启用=1
                         and (select ROUND(sysdate-max(ss.记录时间),3)*24*60 from 问题记录过程 ss where ss.问题ID=a.ID)>b.处理时间  order by a.编号 desc";
                var ProblemDt = this.SqlBuilder.SqlText(sql2).Parameters("LinkId", LinkId).Parameters("PersonId", user.PersonID).Select();
                return ProblemDt;

            }
            catch (Exception ex)
            {
                Logger.Instance.Error("超时推送调用GetDealOverTime出错，原因:", ex);
                return new DataTable();
            }
        }
        #endregion

        #region 首页查询使用
        /// <summary>
        /// 首页查询统计数量
        /// </summary>
        /// <param name="uid">用户id</param>
        /// <param name="userprop">用户性质</param>
        /// <param name="companyid">渠道ID</param>
        /// <param name="projectid">项目ID</param>
        /// <param name="orgid">机构ID</param>
        /// <returns>返回数据表</returns>
        public DataTable QueryNotFinishProblem(string uid, int userprop, string companyid, string projectid, string orgid)
        {
            var sqlbuilder = this.SqlBuilder;
            var sql = @"with tab as (select 
                          *
                         from 问题记录 t
                        where 是否解决 = 0
                            and 是否终止= 0
                            and 是否删除 = 0
                        ";

            if (userprop == 2)
            {//人员性质2：渠道人员，查看当前渠道未解决的问题
                sql += " And 渠道ID = :渠道ID";
                sqlbuilder.Parameters("渠道ID", companyid);
            }
            else if (userprop == 3)
            {//客户人员：只看自己登记未解决的问题  
                sql += " And 登记人 = :登记人ID";
                sqlbuilder.Parameters("登记人ID", uid);
            }
            else if (userprop == 4)
            {//医院管理员：查看自己机构问题
                sql += " And 机构ID = :机构ID";
                if (!string.IsNullOrEmpty(orgid))
                {
                    sqlbuilder.Parameters("机构ID", orgid);
                }
                else
                {
                    Logger.Instance.Warn("当前人员未设置机构ID");
                }
            }
            else if (userprop == 5)
            {//卫计委人员
                sql += " And 项目ID = :项目ID";
                sqlbuilder.Parameters("项目ID", projectid);

            }
            sql += ")";

            sql += @"select '未解决的BUG' as 类型, sum(1) as 数量 from tab where tab.问题性质 = 1
                        union
                        select '未完成的任务',sum(1) from tab where tab.问题性质 = 4
                        union
                        select '待完成的需求',sum(1) from tab where tab.问题性质 = 3
                        union
                        select '未回复的咨询',sum(1) from tab where tab.问题性质 = 2
                        union 
                        select '指派给我的问题',sum(1) from tab where tab.指派人ID =:人员ID";

            sqlbuilder.Parameters("人员ID", uid);

            //返回查询的表结果
            return sqlbuilder.SqlText(sql)
                .Select();
        }

        public DataTable NotFinishProblemList(string uid, int userprop, string companyid, string projectid, string orgid, int Type, int page, int rows, out int total)
        {
            var sqlbuilder = this.SqlBuilder;
            var sql = @" select 
                          t.编号,t.问题性质,t.问题标题,t.登记日期,t.受理人,t.登记人,t.指派人,t.ID,
                          (select 名称 from 问题流程环节 where id = t.环节id ) as 当前环节,
                           (select 名称 from 渠道信息 where id =  t.渠道id) as 渠道,
                           (select 名称 from 项目信息 where id = t.项目ID) as 项目,
                           (select 名称 from 站点信息 where id = t.机构id) as 机构，
                            (select 名称 from 产品系统配置 where id = t.产品id) as 产品,
                            (case t.是否解决 when 1 then (select  ROUND(max(记录时间)-t.登记日期,2)*24 from 问题记录过程 c where t.Id =c.问题id )
                                                    when 0 then (select ROUND(sysdate - t.登记日期,2)*24 from dual)
                                                   end
                           ) as 总耗时 
                         from 问题记录 t
                        where t.是否解决 = 0
                            and t.是否终止= 0
                            and t.是否删除 = 0
                        ";

            if (userprop == 2)
            {//人员性质2：渠道人员，查看当前渠道未解决的问题
                sql += " And t.渠道ID = :渠道ID";
                sqlbuilder.Parameters("渠道ID", companyid);
            }
            else if (userprop == 3)
            {//客户人员：只看自己登记未解决的问题  
                sql += " And t.登记人 = :登记人ID";
                sqlbuilder.Parameters("登记人ID", uid);
            }
            else if (userprop == 4)
            {//医院管理员：查看自己机构问题
                sql += " And t.机构ID = :机构ID";
                if (!string.IsNullOrEmpty(orgid))
                {
                    sqlbuilder.Parameters("机构ID", orgid);
                }
                else
                {
                    Logger.Instance.Warn("当前人员未设置机构ID");
                }
            }
            else if (userprop == 5)
            {//卫计委人员
                sql += " And t.项目ID = :项目ID ";
                sqlbuilder.Parameters("项目ID", projectid);

            }

            if (Type != 5)
            {
                sql += " And t.问题性质 =:Type";
                sqlbuilder.Parameters("Type", Type);
            }
            else
            {
                sql += $" And t.指派人ID ='{uid}'";
            }

            sql += " order by t.编号 desc";
            //返回查询的表结果
            return sqlbuilder.SqlText(sql).Paging(page, rows)
                .Select(out total);
        }


        /// <summary>
        /// 查询各渠道的问题总数
        /// </summary>
        /// <returns></returns>
        public DataTable QueryCompanyProblemCount()
        {
            var sql = @"Select 名称, (Select Count(1) From 问题记录 Where 渠道id = t.Id And 问题性质 = 1 and 是否删除 = 0 and 是否解决 = 0 and 是否终止 = 0) As Bug,
                                       (Select Count(1) From 问题记录 Where 渠道id = t.Id And 问题性质 = 2 and 是否删除 = 0 and 是否解决 = 0 and 是否终止 = 0) As 任务,
                                       (Select Count(1) From 问题记录 Where 渠道id = t.Id And 问题性质 = 3 and 是否删除 = 0 and 是否解决 = 0 and 是否终止 = 0) As 需求,
                                       (Select Count(1) From 问题记录 Where 渠道id = t.Id And 问题性质 = 4 and 是否删除 = 0 and 是否解决 = 0 and 是否终止 = 0) As 咨询
                                From 渠道信息 t
                                 Where t.状态=1";
            return this.SqlBuilder.SqlText(sql)
                .Select();
        }

        public DataTable QueryOrgProblemCount(string companyid)
        {
            var sql = @"select 名称,
                               (select count(1) from 问题记录 where 机构id = t.id and 问题性质 = 1) as Bug,
                               (select count(1) from 问题记录 where 机构id = t.id and 问题性质 = 2) as 任务,
                               (select count(1) from 问题记录 where 机构id = t.id and 问题性质 = 3) as 需求,
                               (select count(1) from 问题记录 where 机构id = t.id and 问题性质 = 4) as 咨询
                         from 站点信息 t where id in(
                        select 机构ID from 问题记录
                        where 渠道id = :渠道ID
                        group by 机构ID)";

            return this.SqlBuilder.Parameters("渠道ID", companyid)
                                  .SqlText(sql)
                                  .Select();
        }

        //根据机构ID，查询出该机构下人员提出的问题
        public DataTable QueryOrgPersonProblemCount(string orgid)
        {
            var sql = @"select 姓名 as 名称,
                                       (select count(1) from 问题记录 where 登记人ID = t.id and 问题性质 = 1) as Bug,
                                       (select count(1) from 问题记录 where 登记人ID = t.id and 问题性质 = 2) as 任务,
                                       (select count(1) from 问题记录 where 登记人ID = t.id and 问题性质 = 3) as 需求,
                                       (select count(1) from 问题记录 where 登记人ID = t.id and 问题性质 = 4) as 咨询
                                 from 人员信息 t where id in(
                                select 登记人ID from 问题记录
                                where 机构ID =:机构ID
                                group by 登记人ID) ";
            return this.SqlBuilder.Parameters("机构ID", orgid)
                      .SqlText(sql)
                      .Select();
        }

        //查询与我相关的问题
        public DataTable QueryMyProblemList(string userid)
        {
            var sql = @"Select * from (Select ID, 编号, 问题性质, 
                               (Select 名称 From 问题流程环节 Where ID = t.环节id) As 当前环节,
                               问题标题,
                               登记人,
                               问题描述,
                               登记日期,
                               指派时间
                        From 问题记录 t
                        Where (登记人id = :人员ID Or 指派人id = :人员ID or 受理人id =:人员ID) And t.是否删除 = 0 and t.是否解决=0 and t.是否终止=0 
                        order by 编号 desc) where  rownum <=5 ";
            return this.SqlBuilder.SqlText(sql)
                .Parameters("人员ID", userid)
                .Select();
        }

        //查询常见问题
        public DataTable QueryOftenProblemList()
        {
            var sql = @"select ID,编号,问题标题,版本号,登记日期,登记人,问题描述 from 问题记录 t where 常见问题= 1 and 是否删除=0 and rownum <=5";
            return this.SqlBuilder.SqlText(sql)
                 .Select();
        }

        //查询出问题所停留的环节
        public DataTable QueryAllProblemStopWorkFlow(int personProp, string companyid, string orgid, string userid)
        {
            var sql = @"Select 名称, (Select Count(1) From 问题记录 r 
                   Where r.环节id = t.Id
                         And r.是否终止 = 0
                         And r.是否解决 = 0 
                         And r.是否删除 = 0 ";
            var sqlbuilder = this.SqlBuilder;
            if (personProp == 2 || personProp == 5)
            {//渠道(项目人员，卫计委显示该渠道下的问题数量)
                sql += @" And r.渠道ID = :渠道ID ";
                sqlbuilder.Parameters("渠道ID", companyid);
            }
            else if (personProp == 3)
            {//客户显示自己提交的问题
                sql += @" And r.登记人ID = :登记人ID ";
                sqlbuilder.Parameters("登记人ID", userid);
            }
            else if (personProp == 4)
            {//医院管理员
                sql += @" And r.机构ID = :机构ID ";
                sqlbuilder.Parameters("机构ID", orgid);
            }

            sql += @" ) As 数量
                    From 问题流程环节 t
                    Order By 序号";

            return sqlbuilder.SqlText(sql).Select();
        }
        #endregion

        #region 版本问题查询
        public DataTable GetVersionProblem(string SearchText, string ProductId, string VersionId,string projectId, string AcceptedId, string sort, string order, int page, int rows, out int total)
        {
            var sql = @"select a.编号,a.问题性质,a.问题标题,a.ID,a.问题描述,s.ID as 产品问题版本ID,s.是否紧急,s.紧急补丁时间,
                          (select decode(性质,1,'中联本部',2,'中联渠道',3,'客户',4,'医院管理员',5,'卫计委') from 人员信息 where ID=a.登记人ID ) as 性质,
                          (select 版本号 from 产品版本配置 where ID=s.版本ID) as 版本,
                          (select 名称 from 产品系统配置 where id = s.产品id) as 产品,
                          (select 名称 from 产品模块功能 where id = a.模块id) as 模块,
                          s.说明,a.登记人,a.登记日期,a.受理人,
                      decode(a.解决版本状态,1,'登记',2,'修改',3,'测试',4,'待发布',5,'终止') 解决版本状态,
                           (select 名称 from 渠道信息 where id =  a.渠道id) as 渠道,
                           (select 名称 from 项目信息 where id = a.项目ID) as 项目,
                           (select 名称 from 站点信息 where id = a.机构id) as 机构
                         from   问题记录 a,产品问题版本 s
                         where a.ID = s.问题ID ";
            var sqlbuilder = this.SqlBuilder;
            if (!string.IsNullOrEmpty(ProductId))
            {
                sql += " and s.产品ID in (Select Column_Value From Table(f_Split_String(:ProductId, ','))) ";
                sqlbuilder.Parameters("ProductId", ProductId);
            }
            if (!string.IsNullOrEmpty(AcceptedId))//受理人id
            {
                sql += " and a.受理人ID= :AcceptedId ";
                sqlbuilder.Parameters("AcceptedId", AcceptedId);
            }

            if (!string.IsNullOrEmpty(VersionId))
            {
                sql += " and s.版本ID=:VersionId ";
                sqlbuilder.Parameters("VersionId", VersionId);
            }
            if (!string.IsNullOrEmpty(SearchText))
            {
                sql += " and  (a.问题标题 like '%'||:查询关键字||'%' or a.编号 like '%'||:查询关键字||'%' ) ";
                sqlbuilder.Parameters("查询关键字", SearchText);
            }
            if (!string.IsNullOrEmpty(projectId))
            {//项目ID
                sql += " And a.项目ID =:项目ID";
                sqlbuilder.Parameters("项目ID", projectId);
            }
            var orders = sort + order;
            var orderBy = "  order by a.编号 desc";
            switch (orders)
            {
                case "编号asc":
                    orderBy  = " order by a.编号 asc";
                    break;
                case "编号desc":
                    orderBy = " order by a.编号 desc";
                    break;
                case "受理人asc":
                    orderBy = " order by a.受理人 asc";
                    break;
                case "受理人desc":
                    orderBy = " order by a.受理人 desc";
                    break;
                case "解决版本状态asc":
                    orderBy = " order by a.解决版本状态 asc";
                    break;
                case "解决版本状态desc":
                    orderBy = " order by a.解决版本状态 desc";
                    break;
                case "登记日期asc":
                    orderBy = " order by a.登记日期 asc";
                    break;
                case "登记日期desc":
                    orderBy = " order by a.登记日期 desc";
                    break;
                case "项目asc":
                    orderBy = " order by  a.项目ID asc";
                    break;
                case "项目desc":
                    orderBy = " order by  a.项目ID desc";
                    break;
            }
                sql += orderBy;

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

        /// <summary>
        /// 添加备注
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public string AddNotes(SubmitProblemFormModel model)
        {
            try
            {
                var ProcessId = String.Empty;
                //var t = UpdateBuilder.Update("问题记录").Where("ID", model.ProblemId).Column("解决版本状态", model.解决版本状态) .Execute();
                    var dt = this.SqlBuilder.SqlText("select ID,当前流程ID,来源流程ID from 问题记录过程 where 问题ID=:id and 状态=1")
                                               .Parameters("id", model.ProblemId).Select();
                    var CurrenProcessId = dt.Rows[0].GetValueByName<int>("当前流程ID");
                    InsertBuilder.Insert("问题记录过程").Column("ID", Guid.NewGuid().ToString())
                                                        .Column("问题ID", model.ProblemId)
                                                        .Column("记录人ID", model.registrantID)
                                                        .Column("记录时间", this.DataBaseBuilder.GetDateTime)
                                                        .Column("当前流程ID", CurrenProcessId)
                                                        .Column("状态", model.Status)
                                                        .Column("来源流程ID", CurrenProcessId)
                                                        .Column("描述", model.DealDetail)
                                                        .Execute();
                    ProcessId = dt.Rows[0].GetValueByName<string>("ID");
                
                return ProcessId;

            }
            catch (Exception ex)
            {
                Logger.Instance.Error("添加备注失败失败" + ex);
                return "";
            }
        }
        #endregion
    }
}