ZLPMS/PMS.DBService/MoblieProblemRegist/MoblieProblemservice.cs

/*以下代码由数据访问服务工具自动生成
 *如有需要请自行修改
 *
 */
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using QWPlatform.DataIntface;
using QWPlatform.IService;
using PMS.EntityModels.MoblieProblemRegist;
using PMS.BusinessModels.MobileProblem;
using System.Data;
using QWPlatform.SystemLibrary.LogManager;
using QWPlatform.SystemLibrary.Utils;
using PMS.EntityModels.WorkFlow;
using PMS.BusinessModels.Account;
using QWPlatform.SystemLibrary;
using PMS.EntityModels.SysManager;
using PMS.EntityModels.Product;

namespace PMS.DBService.MoblieProblemRegist
{
    public class Moblieproblemservice : DataServiceBase
    {
        /// <summary>
        /// 重写数据工厂
        /// </summary>
        /// <param name="conName"></param>
        protected override void DBFctory(string conName)
        {
            base.DBFctory(conName);
        }

        /// <summary>
        /// 增加数据记录
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public int Add(MoblieProblemModel model)
        {
            model.SetDataFactory(this.DataFactoryObject);
            return model.Insert();
        }

        /// <summary>
        /// 增加数据记录
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public int Add(MoblieProblemModel model, ITransaction trans)
        {
            model.SetDataFactory(this.DataFactoryObject);
            if (trans == null)
            {
                return model.Insert();
            }
            else
            {
                return model.Insert(trans);
            }
        }

        /// <summary>
        /// 更新数据库记录
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public int Update(MoblieProblemModel model)
        {
            model.SetDataFactory(this.DataFactoryObject);

            List<string> where = new List<string>();
            where.Add("ID");

            return model.Update(where, string.Empty);
        }


        /// <summary>
        /// 更新数据库记录
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public int Update(MoblieProblemModel model, ITransaction trans)
        {
            model.SetDataFactory(this.DataFactoryObject);

            List<string> where = new List<string>();
            where.Add("ID");

            if (trans == null)
            {
                return model.Update(where, string.Empty);
            }
            else
            {
                return model.Update(trans, where, string.Empty);
            }
        }

        /// <summary>
        /// 删除数据记录
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public int Delete(MoblieProblemModel model)
        {
            model.SetDataFactory(this.DataFactoryObject);

            return model.Delete("ID");
        }

        /// <summary>
        /// 删除数据记录
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public int Delete(MoblieProblemModel model, ITransaction trans)
        {
            model.SetDataFactory(this.DataFactoryObject);
            if (trans == null)
            {
                return model.Delete("ID");
            }
            else
            {
                return model.Delete(trans, "ID");
            }
        }

        /// <summary>
        /// 查询数据对象并进行赋值
        /// </summary>
        /// <returns></returns>
        public MoblieProblemModel Select(MoblieProblemModel model)
        {
            model.SetDataFactory(this.DataFactoryObject);
            model.Select();
            return model;
        }

        /// <summary>
        /// 列表查询
        /// </summary>
        /// <returns></returns>
        public List<MoblieProblemModel> SelectList(MoblieProblemModel model)
        {
            model.SetDataFactory(this.DataFactoryObject);
            return model.SelectList<MoblieProblemModel>();
        }

        /// <summary>
        /// 调用存储过程
        /// </summary>
        /// <returns></returns>
		public void CallProcedure(MoblieProblemModel model)
        {
            this.ProcedureBuilder
                .Procedure("p_PT_项目问题记录_INSERT")
                .Paramter("ID_IN", model.ID)
                .Paramter("编号_IN", model.BH)
                .Paramter("项目ID_IN", model.XMID)
                .Paramter("站点ID_IN", model.ZDID)
                .Paramter("产品ID_IN", model.CPID)
                .Paramter("科室名_IN", model.KSM)
                .Paramter("用户姓名_IN", model.YHXM)
                .Paramter("来源_IN", model.LY)
                .Paramter("问题描述_IN", model.WTMS)
                .Paramter("登记时间_IN", model.DJSJ)
                .Paramter("登记人ID_IN", model.DJRID)
                .Paramter("当前处理人_IN", model.DQCLR)
                .Paramter("处理说明_IN", model.CLSM)
                .Paramter("状态_IN", model.ZT)
                .Paramter("项目处理_IN", model.XMCL)
                .Paramter("技术支持_IN", model.JSZC)
                .Paramter("研发协助_IN", model.YFXZ)
                .Paramter("产品问题_IN", model.CPWT)
                .Paramter("BH问题_IN", model.BHWT)
                .Paramter("问题标题_IN", model.WTBT)
                .Paramter("处理时间_IN", model.CLSJ)
                .Paramter("预计时间_IN", model.YJSJ)
                .Paramter("版本号_IN", model.BBH)
                .Paramter("用户ID_IN", model.YHID)
                .Execute();
        }
        public string ZLSubmitProblem(SubmitProblemFormModel model)
        {
            var exe = this.ProcedureBuilder
                        .Procedure("b_问题管理.P_ZL问题移动端登记")
                        .Paramter("产品编码_In", model.ProductCode)
                        .Paramter("机构ID_IN", model.InstitutionID)
                        .Paramter("项目ID_IN", model.ProjectID)
                        .Paramter("紧急程度_IN", model.ErginID)
                        .Paramter("紧急描述_IN", model.urgentDetail)
                        .Paramter("要求时间_IN", model.TRequireTime)
                        .Paramter("登记进入流程ID_IN", model.NextProcessID)
                        .Paramter("处理说明_In", model.DealDetail)
                        .Paramter("问题描述_IN", model.detail)
                        .Paramter("登记人_IN", model.registrant)
                        .Paramter("登记人ID_IN", model.registrantID)
                        .Paramter("登记人性质_IN", model.registrantProperty)
                        .Paramter("登记日期_IN", this.DataBaseBuilder.GetDateTime)
                        .Paramter("标题_IN", model.ProblemTitile)
                        .Paramter("来源_In", model.Origin)
                        .Paramter("问题性质_In", model.ProblemNature)
                        .Paramter("处理状态_In", model.DealStatus)
                        .ParamterOut("问题过程id_Out", DbType.String, 36)
                        ;
            exe.Execute();
            var str = exe.ParameterValue<string>("问题过程id_Out");
            return str;
        }
        public string CustomerSubmitProblem(SubmitProblemFormModel model)
        {
            var exe = this.ProcedureBuilder
                        .Procedure("b_问题管理.P_客户问题移动端登记")
                        .Paramter("问题描述_IN", model.detail)
                        .Paramter("登记人ID_IN", model.registrantID)
                        .Paramter("登记日期_IN", this.DataBaseBuilder.GetDateTime)
                        .Paramter("产品Code_IN", model.ProductCode)
                        .Paramter("标题_IN", model.ProblemTitile)
                         .Paramter("流程ID_In", model.ProcessID)
                         .Paramter("进入流程ID_IN", model.NextProcessID)
                        .ParamterOut("问题过程id_Out", DbType.String, 36);
            exe.Execute();
            var str = exe.ParameterValue<string>("问题过程id_Out");
            return str;
        }
        public string SubmitProblemBycode(SubmitProblemFormModel model)
        {
            try
            {
                ///如果未登陆，则按照扫描上的人员资源ID登记
                if (String.IsNullOrEmpty(model.registrantID))
                {
                    //获取登记人信息
                    var PersonDt = this.SelectBuilder.From("人员信息").Columns("ID,姓名").Where("资源ID", model.registrantSorID).Select();
                    if (PersonDt != null && PersonDt.Rows.Count > 0)
                    {
                        model.registrantID = PersonDt.Rows[0].GetValueByName<string>("ID");
                        model.registrant = PersonDt.Rows[0].GetValueByName<string>("姓名");
                    }
                    else
                    {
                        Logger.Instance.Error("调用SubmitProblemBycode查询用户不存在");
                        return "";
                    }

                }
                var exe = this.ProcedureBuilder
                            .Procedure("b_问题管理.P_问题移动端扫码登记")
                            .Paramter("问题描述_IN", model.detail)
                            .Paramter("登记人_IN", model.registrant)
                            .Paramter("登记人ID_IN", model.registrantID)
                            .Paramter("机构ID_IN", model.InstitutionID)
                            .Paramter("标题_IN", model.ProblemTitile)
                             .Paramter("产品Code_IN", model.ProductCode)
                            .Paramter("登记日期_IN", this.DataBaseBuilder.GetDateTime)
                            .Paramter("流程ID_In", model.ProcessID)
                             .Paramter("进入流程ID_IN", model.NextProcessID)
                            .ParamterOut("问题过程id_Out", DbType.String, 36);
                exe.Execute();
                var ProcessId = exe.ParameterValue<string>("问题过程id_Out");
                return ProcessId;
            }
            catch (Exception ex)
            {
                Logger.Instance.Error(ex.Message);
                return "";
            }

        }
        public int ImageUpload(string ProblemID, string AnnexID, string Name, string fileType)
        {

            return this.InsertBuilder
                       .Insert("产品问题附件")
                       .Column("ID", Guid.NewGuid().ToString())
                       .Column("问题ID", ProblemID)
                       .Column("类型", fileType)
                       .Column("附件ID", AnnexID)
                       .Column("添加时间", this.DataBaseBuilder.GetDateTime)
                       .Column("添加人", Name)
                       .Execute();
        }
        public DataTable QueryMyProblem(QueryMyProblemModel model, out string toltal)
        {
            var pro = this.ProcedureBuilder;
            pro.Procedure("b_问题管理.P_客户移动端问题查询")
              .Paramter("登记人id_In", model.CurrentUserId)
              .Paramter("查询类型_In", model.SelectType)
              .Paramter("开始时间_In", model.StartTimeD)
              .Paramter("结束时间_In", model.EndTimeD)
              .Paramter("开始行_In", model.StartNum)
              .Paramter("结束行_In", model.EndNum)
              .Paramter("是否解决_In", model.ProblemStatus)
              .ParamterOut("Resultlist", true)
              .ParamterOut("总行数_Out", DbType.Double, 5);
            var Code = 0;
            if (int.TryParse(model.SelectConditon, out Code))
            {
                pro.Paramter("查询条件_In", null)
                   .Paramter("搜索编号_In", Code);
            }
            else
            {
                pro.Paramter("查询条件_In", model.SelectConditon)
                   .Paramter("搜索编号_In", null);
            }
            pro.Execute();
            var dt = pro.ParameterValue<DataTable>("Resultlist");
            toltal = Convert.ToString(pro.ParameterValue<decimal>("总行数_Out"));
            return dt;

        }
        public DataTable QueryProblemByProject(QueryProblemByProjectModel model)
        {

            var pro = this.ProcedureBuilder;
            pro.Procedure("b_问题管理.P_项目人员移动端问题查询")
               .Paramter("当前项目_In", model.Project)
               .Paramter("当前机构_In", model.Institution)
               .Paramter("审核单位_In", model.CurrentUnit)
               .Paramter("问题性质_In", model.Status)
               .Paramter("紧急程度_In", model.priority)
               .Paramter("产品类型_In", model.ProductType)
               .Paramter("开始时间_In", model.StartTimeD)
               .Paramter("结束时间_In", model.EndTimeD)
               .Paramter("选项卡_In", model.Tab)
               .Paramter("开始行_In", model.StartNum)
               .Paramter("结束行_In", model.EndNum)
               .Paramter("是否解决_In", model.DealStatus)
               .Paramter("终止状态_In", model.IsEnd)
               .Paramter("当前登陆人ID_In", model.CurrentUserId)
               .ParamterOut("Resultlist", true);
            var Code = 0;
            if (int.TryParse(model.SelectConditon, out Code))
            {
                pro.Paramter("搜索条件_In", null)
                   .Paramter("搜索编号_In", Code);
            }
            else
            {
                pro.Paramter("搜索条件_In", model.SelectConditon)
                   .Paramter("搜索编号_In", null);
            }
            pro.Execute();
            var dt = pro.ParameterValue<DataTable>("Resultlist");
            return dt;
        }
        public DataTable QuerckSearch(QueryMyProblemModel model)
        {
            if (String.IsNullOrEmpty(model.SelectConditon))
            {
                return new DataTable();
            }
            var dt = this.SelectBuilder.From("人员信息").Columns("性质,渠道ID,站点ID").Where("ID", model.CurrentUserId).Select();
            var Property = dt.Rows[0].GetValueByName<int>("性质");
            var ChannelId = dt.Rows[0].GetValueByName<string>("渠道ID");
            var Institution = dt.Rows[0].GetValueByName<string>("站点ID");
            var sql = @"Select   to_char(t.登记日期,'YYYY/MM/DD  HH24:MI') as 登记日期,t.登记人,t.问题描述,t.问题标题,t.是否解决,t.id,t.是否终止,
                     ( select f.附件ID from 产品问题附件 f  where f.问题ID = t.id  and rownum = 1 and f.类型='1') as 附件路径,
                     (Select bm.显示名 from 基础编码 bm where bm.分类ID='D3EDEA7A-7F07-4DD3-9A6B-34DB16A6E982' and bm.代码=s.性质) AS 性质
                     from 问题记录 t,人员信息 s
                     where  t.登记人id=s.id(+)
                     and t.是否删除=0 ";
            var sqlbuilder = this.SqlBuilder;
            sqlbuilder.Parameters("search", model.SelectConditon);
            int numberCode = 0;
            if (int.TryParse(model.SelectConditon, out numberCode))
            {
                sql += @" and t.编号 =:search";
            }
            else
            {
                sql += @" and(t.问题标题 Like '%' ||:search || '%' or t.问题描述 Like '%' ||:search || '%' or

                         t.登记人 Like '%' ||:search || '%' ) ";
            }
            if (Property != 1)
            {
                sql += " and  t.渠道ID=:Channel ";
                sqlbuilder.Parameters("Channel", ChannelId);

            }
            //如果为机构管理员
            if (Property == 4 || Property == 3)
            {
                sql += " and  t.机构ID=:Institution ";
                sqlbuilder.Parameters("Institution", Institution);
            }
            sql += " Order By t.登记日期 desc";
            return sqlbuilder.SqlText(sql).Paging(model.Pages, model.rows).Select();

        }
        public DataTable GetPerson(string id)
        {
            var sql = "select b.id AS 项目ID,b.名称 AS 项目名称,a.性质,c.id AS 站点ID,c.名称 AS 站点名称 from 人员信息 a,项目信息 b, 站点信息 c where a.id = :id and a.项目ID = b.id and a.站点id = c.id";
            return this.SqlBuilder
                        .SqlText(sql)
                        .Parameters("id", id)
                        .Select();
        }
        public DataTable ProblemDetail(string id)
        {
            var pro = this.ProcedureBuilder
                      .Procedure("b_问题管理.P_移动端问题详细")
                      .Paramter("问题ID_In", id)
                      .ParamterOut("Resultlist", true);
            pro.Execute();
            var dt = pro.ParameterValue<DataTable>("Resultlist");
            return dt;
        }
        public int NewestStatus(string id)
        {
            return this.SelectBuilder.Columns("状态")
                                     .From("问题记录过程")
                                     .Where("问题ID", id)
                                     .OrderBy("记录时间 desc")
                                     .Select<int>();
        }
        /// <summary>
        /// 获取登记问题时所在的环节Id
        /// </summary>
        public int GetRegisLinkId(string id)
        {
            return this.SqlBuilder
                       .SqlText("select s.环节id from 问题记录过程 t,问题工作流程 s where t.当前流程id=s.id and t.问题ID=:id and t.状态=1")
                       .Parameters("id", id)
                       .Select<int>();
        }
        public int GetUserLinkId(int PersonProperty, string JobCode)
        {

            var SelectBuilder = this.SqlBuilder;
            var sqlText = @"Select t.ID from 问题流程环节 t where t.人员性质=:人员性质";
            SelectBuilder.Parameters("人员性质", PersonProperty);
            ///如果为本部人员
            if (PersonProperty == 1)
            {
                sqlText += @" and Exists
                           (select column_value from table(Select f_Split_String(x.职务代码1, ',') from 问题流程环节 x
                            where id = t.Id)
                            where Column_Value =:职务) ";
                SelectBuilder.Parameters("职务", Convert.ToInt32(JobCode));
            }
            SelectBuilder.SqlText(sqlText);
            return SelectBuilder.Select<int>();


        }
        public DataTable GetFileFormFTP(string id)
        {
            return this.SelectBuilder.Columns("a.附件ID,a.类型")
                 .From("产品问题附件 a,问题记录过程 b")
                 .Where("b.ID=a.过程ID")
                 .Where("b.问题ID", id)
                 .Where("b.状态", 1)
                 .Select();
        }
        public ButtonAuthorityModel GetButtonauthority(int PersonProperty, string PersonID, string ProblemID)
        {
            var model = new ButtonAuthorityModel();
            model.Cancel = false;
            model.Confirm = false;
            model.Deal = false;
            model.Stop = false;
            model.Sub = false;
            return model;
        }
        public DataTable GetCount(string id)
        {
            var sql = @" with
                         sql as
                         (select Count(1) as djCount from 问题记录过程 a,问题记录 b
                                 where a.问题id=b.id
                                  and b.是否删除=0 and
                                  a.状态=1 and a.记录人id=:id ),
                        sq as (select Count(distinct a.问题ID) as sqCount from 问题记录过程 a,问题记录 b  where a.状态=5 and a.记录人id=:id and a.问题id=b.id and b.是否删除=0 ),
                        cl as( select Count(distinct a.问题ID) as clCount from 问题记录过程 a,问题记录 b where a.状态 in (2,3,4,6,7,8) and a.记录人id=:id and a.问题id=b.id and b.是否删除=0 )
   
                     Select djCount as 我登记的,sqCount as 我申请的,clCount as 我处理的 from 
                         sq, sql,cl ";
            return this.SqlBuilder.SqlText(sql).Parameters("id", id).Select();

        }
        public DataTable GetCountForCustom(string id)
        {
            var sql = @"select 
                        sum(1) AS 已登记,
                        sum(case when t.是否解决=0 then 1 else 0 end ) AS 未解决,
                        sum(case when t.是否解决=1 then 1 else 0 end ) AS 已解决
                        from 问题记录 t 
                        where t.登记人id=:id
                        and t.是否删除=0";
            return this.SqlBuilder.SqlText(sql).Parameters("id", id).Select();
        }
        public WorkFlowModel GetNewProblemWorkId(string InstitutionID, string ProjectID)
        {
            WorkFlowModel model = new WorkFlowModel();
            try
            {
                model.CategoryID = 1;
                //查询项目审核流程
                var ProjectCategoryID = this.SqlBuilder.SqlText("Select 工作流目录ID From 项目问题流程 Where 项目id=:ProjectID and 机构id is null")
                                         .Parameters("ProjectID", ProjectID).Select<int>();
                ///如果不为空则取项目流程
                if (ProjectCategoryID > 0)
                {
                    model.CategoryID = ProjectCategoryID;
                }
                //判断传入机构是否为空
                if (!String.IsNullOrEmpty(InstitutionID))
                {
                    var InstitutionCategoryID = this.SqlBuilder.SqlText("Select 工作流目录ID From 项目问题流程 Where 项目id=:ProjectID and 机构id=:InstitutionID")
                                                     .Parameters("ProjectID", ProjectID).Parameters("InstitutionID", InstitutionID).Select<int>();
                    ///如果不为空则取机构流程
                    if (InstitutionCategoryID > 0)
                    {
                        model.CategoryID = InstitutionCategoryID;
                    }
                }
                //取流程Id
                model.ID = this.SqlBuilder.SqlText("Select ID  From 问题工作流程 Where 目录id = :CategoryID And 启用 = 1 And Rownum = 1 Order By 序号 Asc")
                                        .Parameters("CategoryID", model.CategoryID).Select<int>();
                return model;
            }
            catch (Exception ex)
            {
                Logger.Instance.Error("调用方法GetNewProblemWorkId异常", ex);
                return null;
            }

        }

        #region 用户注册
        public string UserCheck(string PersonResource_id)
        {
            var pro = this.ProcedureBuilder
                        .Procedure("b_平台管理_登录管理.p_用户完整性判断")
                        .Paramter("In_人员资源ID", PersonResource_id)
                        .ParamterOut("Out_状态", DbType.String, 3);
            pro.Execute();
            var st = pro.ParameterValue<string>("Out_状态");
            return st;
        }
        public DataTable GetInstitutionsID(string id)
        {
            return this.SelectBuilder
                       .From("站点信息")
                       .Where("资源ID", id)
                       .Columns("ID,名称,项目ID")
                       .Select();
        }
        public string GetInstitutionsIDByUser(string UserId)
        {
            string id;
            id = this.SelectBuilder
                      .From("人员信息")
                      .Where("ID", UserId)
                      .Columns("站点ID")
                      .Select<string>();
            if (String.IsNullOrEmpty(id))
            {
                id = this.SelectBuilder
                      .From("人员信息")
                      .Where("资源ID", UserId)
                      .Columns("站点ID")
                      .Select<string>();
            }
            return id;
        }
        public DataTable GetExistInfo(string PersonResource_id)
        {
            var sql = @"select t.姓名,t.联系电话,t.部门名
                       from 人员信息 t
                       where t.资源ID =:PersonResource_id";
            return this.SqlBuilder.SqlText(sql).Parameters("PersonResource_id", PersonResource_id).Select();
        }
        public DataTable TeleGetUserInfo(string Tele)
        {
            var pro = this.ProcedureBuilder
                       .Procedure("b_平台管理_登录管理.p_手机获取用户信息")
                       .Paramter("In_手机号码", Tele)
                       .ParamterOut("Resultlist", true);
            pro.Execute();
            var st = pro.ParameterValue<DataTable>("Resultlist");
            return st;
        }
        public string UpdateResourceID(string UserGetId, string Tele, string edType)
        {
            var pro = this.ProcedureBuilder
                                   .Procedure("b_平台管理_登录管理.p_更新用户资源ID")
                                   .Paramter("In_手机号码", Tele)
                                   .Paramter("In_资源ID", UserGetId)
                                   .Paramter("In_更新类型", edType)
                                   .ParamterOut("是否成功_Out", DbType.String, 3);
            pro.Execute();
            var st = pro.ParameterValue<string>("是否成功_Out");
            return st;
        }
        public bool GetSMSCode(string telephone, string code)
        {
            try
            {
                DateTime dtime = this.DataBaseBuilder.GetDateTime;
                DateTime timeEnd = new DateTime();
                timeEnd = dtime.AddMinutes(10);
                var t = this.ProcedureBuilder
                                  .Procedure("b_平台管理_登录管理.p_保存短信验证码")
                                  .Paramter("In_验证码", code)
                                  .Paramter("In_手机号", telephone)
                                  .Paramter("In_获取时间", dtime)
                                  .Paramter("In_过期时间", timeEnd).Execute();
                return t == -1;

            }
            catch (Exception ex)
            {
                Logger.Instance.Error("短信保存数据库错误!", ex);
                return false;
            }
        }
        public string UserRegist(MobileUserRegistModel Model)
        {
            try
            {
                //判断账户是否存在
                var t = this.SelectBuilder.Columns("1").From("系统账户").Where("账户", Model.telephone).Exists();
                if (t)
                    return "4";

                var st = InsertBuilder.Insert("人员信息").Column("ID", Guid.NewGuid().ToString("N")).Column("渠道ID", Model.Channel).Column("姓名", Model.name).Column("联系电话", Model.telephone).Column("性质", 4).Column("状态", 1)
                   .Column("简码", Model.simple).Column("说明", "自助注册").Column("职务", "9").Column("所属医院", Model.hospitalname).Column("添加时间", this.DataBaseBuilder.GetDateTime)
                  .Execute();
                //var pro = this.ProcedureBuilder
                //                                  .Procedure("b_平台管理_登录管理.p_投诉用户注册")
                //                                  .Paramter("资源ID_IN", Model.UserGetId)
                //                                  .Paramter("In_站点ID", Model.SiteID)
                //                                  .Paramter("姓名_IN", Model.name)
                //                                  .Paramter("联系电话_IN", Model.telephone)
                //                                  .Paramter("部门名_IN", Model.Department)
                //                                  .Paramter("密码_In", Strings.StrToMD5(Model.password))
                //                                  .Paramter("验证码_In", Model.SMSCode)
                //                                  .Paramter("系统时间_In", this.DataBaseBuilder.GetDateTime)
                //                                  .ParamterOut("是否成功_Out", DbType.String, 3);
                //pro.Execute();
                //var st = pro.ParameterValue<string>("是否成功_Out");
                return st+"";
            }
            catch (Exception ex)
            {
                Logger.Instance.Error("调用UserRegist方法注册发生错误!", ex);
                return "1";
            }

        }
        /// <summary>
        /// 获取渠道信息
        /// </summary>
        /// <returns></returns>
        public DataTable GetChannel()
        {
            var dt = this.SelectBuilder.From("渠道信息").Where("状态",1).Columns("ID,名称").Select();
            return dt;
        }
        /// <summary>
        /// 获取渠道扩展信息
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public DataTable GetChannelExtend(string id)
        {
            var dt = this.SelectBuilder.From("渠道信息扩展表").Where("id", id).Where("状态", 1).Columns("手机号,微信ID").Select();
            return dt;
        }
        public string PasswordModifySubmit(string telephone, string Code, string password)
        {
            string status = string.Empty;
            //判断账户是否存在
            var accountExit = this.SelectBuilder.From("系统账户").Where("账户", telephone).Columns("ID").Select<int>();
            if (accountExit > 0)
            {
                /*判断验证码是否错误*/
                var CodeErr = this.SelectBuilder.From("短信验证码").Where("手机号", telephone).Where("验证码", Code).Columns("验证码").Select<int>();
                if (CodeErr < 1)
                {
                    return "验证码错误";//
                }
                //判断验证码是否过期
                var CodeTime = this.SelectBuilder.From("短信验证码").Where("手机号", telephone).Where("验证码", Code).Columns("过期时间").Select<DateTime>();
                if (CodeTime < this.DataBaseBuilder.GetDateTime)
                {
                    return "验证码过期";//
                }
                //修改密码
                var t = this.UpdateBuilder.Update("系统账户").Where("账户", telephone).Column("密码", Strings.MD5(password)).Execute();
                return t > 0 ? "1" : "修改失败";
            }
            else
            {
                return "用户不存在";//
            }
        }
        #endregion
        #region 问题操作相关业务
        public bool CrossJud(string ProblemId, int PersonProperty, string JobCode)
        {
            var Link = this.SelectBuilder.Columns("环节id").From("问题记录").Where("ID", ProblemId).Select<int>();
            var SelectBuilder = this.SelectBuilder.Columns("ID").From("问题流程环节").Where("人员性质", PersonProperty);
            if (PersonProperty == 1)
            {
                SelectBuilder.Where("职务代码", Convert.ToInt32(JobCode));
            }
            var UserLink = SelectBuilder.Select<int>();
            if (UserLink < Link)
            {
                return false;
            }
            return true;
        }
        public string IsMyProblem(string ProblemId, string PersonId)
        {
            var dt = this.SelectBuilder.Columns("受理人ID,指派人ID").From("问题记录").Where("ID", ProblemId).Select();
            var CurrentDealPersonId = dt.Rows[0].GetValueByName<string>("受理人ID");
            var CurrentAssginPersonId = dt.Rows[0].GetValueByName<string>("指派人ID");
            if (PersonId == CurrentDealPersonId || PersonId == CurrentAssginPersonId || (String.IsNullOrEmpty(CurrentDealPersonId) && String.IsNullOrEmpty(CurrentAssginPersonId)))
            {
                return "1";//为自己处理的问题
            }
            else
            {
                return "0";//不为自己处理的问题
            }
        }
        public DataTable GetPerson(string search, int PersonProperty, string ProblemId)
        {
            try
            {
                ///获取问题所在渠道和机构
                var Problemdt = this.SelectBuilder.From("问题记录").Where("ID", ProblemId).Columns("渠道ID,机构ID").Select();
                var companyID = Problemdt.Rows[0].GetValueByName<string>("渠道ID");
                var InstitutionID = Problemdt.Rows[0].GetValueByName<string>("机构ID");
                //查询人员
                var SqlBuilder = this.SqlBuilder;
                var sql = @" Select * From (Select s.ID,s.姓名,s.简码,(select t.显示名 from 基础编码 t where t.分类ID='56411293-988F-42CA-8B84-5A34DD37C7E6' and t.代码=s.职务) AS 职务,
                            (select qd.名称 from 渠道信息 qd where qd.ID=s.渠道ID) AS 公司名称
                         from 人员信息 s 
                         where  s.状态='1' and s.是否删除=0  
                         ";
                var sql2 = " Union all ";
                var sql3 = @"  Select s.ID,s.姓名,s.简码,(select t.显示名  from 基础编码 t where t.分类ID='56411293-988F-42CA-8B84-5A34DD37C7E6' and t.代码=s.职务) AS 职务 ,
                              (select qd.名称 from 渠道信息 qd where qd.ID=s.渠道ID) AS 公司名称
                             from 人员信息 s where s.站点ID = :站点ID_In
                             and s.性质 = '4'
                             and s.状态 = '1'
                             and s.是否删除=0
                              ";

                SqlBuilder.Parameters("站点ID_In", InstitutionID);
                string SQL = string.Empty;
                if (!String.IsNullOrEmpty(search.Trim()))
                {
                    sql += " and (s.姓名 Like '%' ||:search || '%' or s.简码 Like '%' ||:search || '%' )";
                    sql3 += " and (s.姓名 Like '%' ||:search || '%'  or s.简码 Like '%' ||:search || '%'  )";
                    SqlBuilder.Parameters("search", search);
                }
                if (PersonProperty == 1)
                {
                    sql += " and s.性质 in (1,2,5) and s.渠道id in(:CompanyID,'4A9B2065-65AB-4411-B528-968D26737EAE')  order by s.性质 asc )";
                }
                else if (PersonProperty == 2)
                {
                    sql += " and s.性质 in (2,5) and (s.渠道id=:CompanyID   OR s.渠道id in( SELECT j.共享渠道ID FROM  渠道人员共享 j WHERE j.渠道ID=:CompanyID)))";
                }
                else if (PersonProperty == 5)
                {
                    sql += " and s.性质 ='5' and s.渠道id=:CompanyID )";
                }
                SqlBuilder.Parameters("CompanyID", companyID);
                //判断是否为机构管理员
                if (PersonProperty == 4)
                {
                    SQL = sql3;
                }
                else
                {
                    SQL = sql + sql2 + sql3;
                }
                return SqlBuilder.SqlText(SQL).Select();
            }
            catch (Exception ex)
            {
                Logger.Instance.Error("获取人员信息调用数据库失败", ex);
                return new DataTable();
            }


        }
        public bool IsDealProcess(string ProblemId, int PersonProperty)
        {
            ///获取目录Id
            var categoryId = this.SelectBuilder.Columns("b.目录ID").From("问题记录 a,问题工作流程 b").Where("a.流程ID=b.ID").Where("a.ID", ProblemId).Select<int>();
            //获取环节ID
            var id = this.SelectBuilder.From("问题工作流程 a,问题流程环节 b").Columns("a.ID").Where("a.环节ID=b.ID").Where("a.目录ID", categoryId).Where("b.人员性质", PersonProperty)
                       .Select<int>();
            return id > 0;
        }
        public UserInfo GetPersonProperty(string PersonID)
        {
            var dt = this.SelectBuilder.Columns("性质,职务").From("人员信息").Where("ID", PersonID).Select();
            var User = new UserInfo();
            User.PersonProperty = dt.Rows[0].GetValueByName<int>("性质");
            User.JobCode = dt.Rows[0].GetValueByName<string>("职务");
            return User;
        }
        public DataTable GetProblemVison(string ProblemId)
        {
            return this.SelectBuilder.From("问题记录 a,产品版本配置 b").Columns("b.ID,b.版本号").Where("a.产品ID=b.产品ID").Where("a.ID", ProblemId).Where("b.状态!=2").OrderBy("b.版本号 desc").Select();
        }
        public DataTable GetProblemInfomation(string id)
        {
            return this.SelectBuilder.From("问题记录").Columns(" * ").Where("ID", id).Select();
        }
        public string ModifySubmit(SubmitProblemFormModel model)
        {
            try
            {
                var ProcessId = String.Empty;
                var t = UpdateBuilder.Update("问题记录").Where("ID", model.ProblemId)
                                                      .Column("问题标题", model.ProblemTitile)
                                                      .Column("问题描述", model.detail)
                                                      .Column("问题性质", model.ProblemNature)
                                                      .Column("来源代码", model.Origin)
                                                      .Column("紧急代码", model.ErginID)
                                                      .Column("要求时间", model.TRequireTime)
                                                      .Column("紧急描述", model.urgentDetail)
                                                      .Column("解决方案", model.DealDetail)
                                                      .Column("是否解决", model.DealStatus)
                                                      .Execute();
                if (t > 0)
                {
                    var dt = this.SqlBuilder.SqlText("select ID,当前流程ID,来源流程ID from 问题记录过程 where 问题ID=:id and 状态=1")
                                               .Parameters("id", model.ProblemId).Select();
                    ///如果已解决
                    if (model.DealStatus == "1")
                    {
                        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("状态", 7)
                                                            .Column("来源流程ID", CurrenProcessId)
                                                            .Column("描述", model.DealDetail)
                                                            .Execute();

                    }
                    ProcessId = dt.Rows[0].GetValueByName<string>("ID");
                }
                return ProcessId;

            }
            catch (Exception ex)
            {
                Logger.Instance.Error("调用ModifySubmit失败" + ex);
                return "";
            }
        }
        public string ModifySubmitForCustom(SubmitProblemFormModel model)
        {
            var ProcessId = String.Empty;
            var t = UpdateBuilder.Update("问题记录").Where("ID", model.ProblemId)
                                                  .Column("问题标题", model.ProblemTitile)
                                                  .Column("问题描述", model.detail)
                                                  .Execute();
            if (t > 0)
            {
                ProcessId = this.SqlBuilder.SqlText("select ID from 问题记录过程 where 问题ID=:id and 状态=1")
                                              .Parameters("id", model.ProblemId).Select<string>();
            }
            return ProcessId;
        }
        public bool IsModifyMyProblem(string personId, string problemId)
        {
            var t = this.SelectBuilder.From("问题记录").Columns("ID").Where("ID", problemId).Where("登记人ID", personId).Select<string>();
            return !String.IsNullOrEmpty(t);
        }
        public DataTable GetProblemFlie(string id)
        {
            var sql = @"select b.id,b.附件id,b.类型  from 问题记录过程 a,产品问题附件 b
                        where a.id=b.过程id
                        and a.问题id=:id
                        and a.状态=1";
            return this.SqlBuilder.SqlText(sql).Parameters("id", id).Select();
        }
        public int GetProblemByProcssId(string id)
        {
            var sql = @"select b.环节id from 问题记录过程 a,问题工作流程 b
                         where a.当前流程ID=b.id
                         and a.ID=:id";
            return this.SqlBuilder.SqlText(sql).Parameters("id", id).Select<int>();
        }
        public int GetProblemByWorkId(int id)
        {
            var sql = @"select 环节ID  from  问题工作流程 where ID=:id";
            return this.SqlBuilder.SqlText(sql).Parameters("id", id).Select<int>();
        }

        #endregion
        #region 用户评论
        public bool ProblemReamrkSubmit(commentsModel model, out string CommentsId)
        {
            var ID = Guid.NewGuid().ToString();
            using (var tran = this.DBTransaction.BeginTrans())
            {
                try
                {

                    var r = this.InsertBuilder.Insert("问题评价")
                                     .Column("ID", ID)
                                     .Column("问题ID", model.ProblemId)
                                     .Column("评论内容", model.Deatil)
                                     .Column("评论人", model.CommitPerson)
                                     .Column("评论人ID", model.CommitPersonId)
                                     .Column("评论时间", this.DataBaseBuilder.GetDateTime)
                                     .Column("处理速度", model.DealTime)
                                     .Column("服务态度", model.DealAttitude)
                                     .Column("处理质量", model.DealQuality)
                                     .Execute() > 0;
                    var Update = this.UpdateBuilder.Update("问题记录")
                                                 .Where("ID", model.ProblemId)
                                                 .Column("是否评价", 1)
                                                 .Execute() > 0;
                    tran.CommitTrans();
                    if (r && Update)
                    {
                        CommentsId = ID;
                        return true;
                    }
                    else
                    {
                        CommentsId = string.Empty;
                        return false;
                    }

                }
                catch (Exception ex)
                {
                    Logger.Instance.Error("调用方法ProblemReamrkSubmit失败，原因：", ex);
                    CommentsId = string.Empty;
                    tran.Rollback();
                    return false;
                }
            }

          
        }
        public bool CommitFlieUplod(CommitFlie model)
        {
            return this.InsertBuilder.Insert("问题评价附件")
                                     .Column("ID", Guid.NewGuid().ToString())
                                     .Column("评价ID", model.CommitId)
                                     .Column("附件类型", model.FlieType)
                                     .Column("附件ID", model.FlieID)
                                     .Column("添加时间", this.DataBaseBuilder.GetDateTime)
                                     .Column("添加人", model.Name)
                                     .Execute() > 0;
        }
        public bool IsCommit(string ProblemId, string PersonId)
        {
            var sql = @"select ID from 问题记录  where ID=:id
                             and 登记人ID=:personid
                             and 是否评价=0
                             and (是否解决=1 or 是否终止=1 )";
            return this.SqlBuilder.SqlText(sql).Parameters("id", ProblemId).Parameters("personid", PersonId).Exists();
        }
        public string getVersionPoblemExplain(string id)
        {
            try
            {
                var dt = this.SqlBuilder.SqlText("select 版本ID,说明 from 产品问题版本 where ID=:id").Parameters("id", id).Select();
                var mark = dt.Rows[0].GetValueByName<string>("说明");
                var VersionID = dt.Rows[0].GetValueByName<string>("版本ID");
                var dt2 = this.SqlBuilder.SqlText("select 附件说明,附件ID from 产品版本附件 where 产品版本ID=:VersionID").Parameters("VersionID", VersionID).Select();
                return Strings.ObjectToJson(new
                {
                    mark = mark,
                    File = dt2
                });
            }catch(Exception ex)
            {
                Logger.Instance.Error("获取产品版本附件失败，原因：" + ex);
                return "";
            }
           
            
        }
        public bool SaveLogger(string flieId, string flieVersionId, string personId)
        {
            return this.InsertBuilder.Insert("版本附件下载日志")
                                     .Column("ID", Guid.NewGuid().ToString())
                                     .Column("附件ID", flieId)
                                     .Column("版本附件ID", flieVersionId)
                                     .Column("下载人ID", personId)
                                     .Column("下载时间", this.DataBaseBuilder.GetDateTime)
                                     .Execute()>0;
        }

        /// <summary>
        /// 获取用户评价和待评价的问题
        /// </summary>
        /// <param name="PersonId">个人id</param>
        /// <param name="pageindex">开始页码</param>
        /// <param name="pagesize">页码大小</param>
        /// <param name="total">1</param>
        /// <returns></returns>
        public DataTable  GetQuestionList(string PersonId, int pageindex, int pagesize,int Tab, out int total )
        {
            var type=0;//待评价
             if(Tab==2)//已评价
            {
                type = 1;
            }
            total = 0;
            var sql = @"
select  *  from (
select rownum rn, dt.*from (
select t.ID, t.问题标题,t.问题描述,t.登记日期,t.编号,t.登记人,t.问题性质 ,t.修改说明 ,t.版本号 ,t.是否评价
,(Select Bm.显示名 From 基础编码 Bm  Where Bm.分类id =  'D3EDEA7A-7F07-4DD3-9A6B-34DB16A6E982' And Bm.代码 = s.性质) As 性质 from 问题记录 t,人员信息 s
where  t.登记人ID=:personid  and 是否评价=:type and t.登记人id = s.Id(+)
 and (是否解决=1 or 是否终止=1 )  order by  t.登记日期  desc) dt  where rownum <=:endsize)  where rn >=:beginsize";
            var dt= this.SqlBuilder.SqlText(sql).Parameters("type", type).Parameters("personid", PersonId).Parameters("beginsize", (pageindex - 1) * pagesize + 1).Parameters("endsize", (pageindex* pagesize)).Select();
            if (dt != null)
            {
                //获取总条数
                string totalSql = @"
select count(1) total from 问题记录 t ,人员信息 s where  t.登记人ID=:personid  and 是否评价=0  and t.登记人id = s.Id(+)  and (是否解决=1 or 是否终止=1 )  ";
                //获取总数信息
                DataTable tdt = this.SqlBuilder.SqlText(totalSql).Parameters("personid", PersonId).Select();
                if (tdt != null && tdt.Rows.Count > 0)
                {
                    total = Convert.ToInt32(tdt.Rows[0]["total"]);
                }
            }
            return dt;
        }
        /// <summary>
        /// 获取评价的内容
        /// </summary>
        /// <param name="PersonId">个人id</param>
        /// <param name="ProblemID">问题ID</param>
        /// <returns></returns>
        public DataTable GetEvaluationContent(string PersonId, string ProblemID)
        {
           
            var dt = this.SqlBuilder.SqlText(@"select t.id,
       t.问题id ProblemId,
       t.评论内容 Deatil,
       t.评论人 CommitPerson,
       t.评论人id CommitPersonId,
       t.评论时间,
       t.处理速度 DealTime,
       t.服务态度 DealAttitude,
       t.处理质量 DealQuality  from 问题评价 t   where  t.问题id=:ProblemID  and  t.评论人id=:PersonId").Parameters("PersonId", PersonId).Parameters("ProblemID", ProblemID).Select();
            return dt;
          
        }
        /// <summary>
        /// 获取评价文件ID
        /// </summary>
        /// <param name="ID"></param>
        /// <returns></returns>
        public DataTable GetEvaluationFile( string ID)
        {
       var dt = this.SqlBuilder.SqlText(@"select 
       t.附件类型,
       t.附件id
        from 问题评价附件 t where  t.评价id=:ID").Parameters("ID", ID).Select();
            return dt;
        }
            
        #endregion
        #region 微信登陆相关业务
        public bool  IsRegisteredWeChat(string openId, out string account, out string pwd)
        {
            bool isExist= this.SqlBuilder.SqlText("select ID from 系统账户 where 微信ID=:openId").Parameters("openId", openId).Exists();
            if (isExist)
            {
                var model = new AccountModel(this.DataFactoryObject);
                model.WXID = openId;
                model.Select();
                account = model.ZH;
                pwd = model.MM;
                return true;
            }
            else
            {
                account = "";
                pwd = "";
                return false;
            }
        }
        public bool IsAccountExist(string tele)
        {
            return this.SqlBuilder.SqlText("Select ID from 系统账户 where 账户=:tele").Parameters("tele", tele).Exists();
        }
        public bool WechatVerification(string telephone, string Code, string OpenId, out string pwd, out string msg)
        {
            ///验证短信验证码
            var ExpirationTime= this.SqlBuilder.SqlText("select 过期时间 from 短信验证码 where 手机号=:p and 验证码=:y")
                                  .Parameters("p", telephone).Parameters("y", Code)
                                  .Select<DateTime>();
            if(ExpirationTime== DateTime.MinValue)
            {
                pwd = "";
                msg = "验证码错误！";
                return false;
            }
            if (ExpirationTime < this.DataBaseBuilder.GetDateTime)
            {
                pwd = "";
                msg = "验证码已过期！";
                return false;
            }

            var t= this.UpdateBuilder.Column("微信ID", OpenId)
                                    .Where("账户", telephone)
                                    .Update("系统账户")
                                    .Execute()>0;
            if (t )
            {
                pwd = this.SqlBuilder.SqlText("select 密码 from 系统账户 where 账户=:account").Parameters("account", telephone).Select<string>();
                msg = "成功";
                return true;
            }
            else
            {
                pwd = "";
                msg = "手机号未关联账户，请联系管理员！";
                return false;
            }
        }
        #endregion
    }
}