using QWPlatform.IService;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using QWPlatform.DataIntface;
using QWPlatform.SystemLibrary;
using PMS.BusinessModels.ProManager;
using System.Data;
using PMS.EntityModels.PersonManager;
using QWPlatform.IService.ServiceInterface;
using PMS.BusinessModels.Person;
using System.Security.Cryptography;
using PMS.EntityModels.SysManager;
using QWPlatform.SystemLibrary.LogManager;
using PMS.BusinessModels.Account;

namespace PMS.DBService.PersonManager
{
    public class PersonService : DataServiceBase
    {
        protected override void DBFctory(string conName)
        {
            base.DBFctory(conName);
        }

        #region 修改系统账户和系统角色关系表的操作
        public int Update_Account(PersonBusinessModel model, string role)
        {
            try
            {
                int x = 0;
                //根据人员ID获取账户信息
                DataTable dt = GetSysAccountInfo(model.ID);
                var XTID = dt.Rows[0]["ID"];
                var Pwd = dt.Rows[0]["密码"].ToString();
                string Account = dt.Rows[0]["账户"].ToString();
                if (Pwd != model.password1)
                {
                    Pwd = QWPlatform.SystemLibrary.Utils.Strings.MD5(model.password1);//密码加密
                }
                if (Account == model.account)
                {
                    //修改系统账户表            
                    x = UpdateBuilder.Update("系统账户")
                       .Column("密码", Pwd)
                       .Column("邮箱", model.email)
                       .Column("姓名", model.name.Trim())
                       .Where("ID", XTID)
                       .Execute();
                }
                else
                {
                    //保存信息前先判断输入的账号是否已经使用
                    dt = SelectBuilder.From("系统账户").Columns("*").Where("账户", model.account).Select();
                    if (dt.Rows.Count > 0)
                    {
                        return -1;
                    }
                    else
                    {
                        //修改系统账户表            
                        x = UpdateBuilder.Update("系统账户")
                           .Column("密码", Pwd)
                           .Column("邮箱", model.email)
                           .Column("姓名", model.name.Trim())
                           .Column("账户", model.account)
                           .Column("公司ID", model.channel)
                           .Where("ID", XTID)
                           .Execute();
                        UpdateBuilder.Update("人员信息").Where("ID", model.ID).Column("姓名", model.name.Trim()).Column("联系电话", model.contactPhone).Execute();
                    }
                }

                if (x == 1)
                {
                    //先清空账户角色
                    DeleteBulider.Delete("系统角色关系").Where("账户ID", XTID).Execute();
                    
                    //修改账户和角色的关系
                    string[] para = role.ToString().Split(',');
                    foreach (var row in para)
                    {
                        string sql = "select 系统角色关系_ID.nextval from dual";
                        dt = SqlBuilder.SqlText(sql).Select();
                        int JSID = int.Parse(dt.Rows[0]["nextval"].ToString());
                        x = InsertBuilder.Insert("系统角色关系")
                            .Column("ID",JSID)
                            .Column("账户ID", XTID)
                            .Column("角色ID", row)
                            .Execute();
                        x = 2;
                    }
                }
                return x;
            }
            catch (Exception ex)
            {
                Logger.Instance.Error("调用Update_Account时发生异常错误", ex);
                return 0;
            }
        }

        #endregion
        public DataTable GetPersonByChannel(string id = "")
        {
            var exe = this.ProcedureBuilder
                  .Procedure("b_平台管理_权限管理.p_获取项目相关人员信息")
                  .Paramter("In_项目Id", DBNull.Value)
                  .Paramter("In_渠道Id", id)
                  .ParamterOut("Resultlist", true);
            exe.Execute();
            var dt = exe.ParameterValue<DataTable>("Resultlist");
            return dt;
        }
        public DataTable GetPersonByJob(string Nature = "", string job = "")
        {
            var execute = this.ProcedureBuilder
                       .Procedure("B_基础管理_机构人员.p_人员信息条件_Select")
                       .Paramter("性质_In", Nature)
                       .Paramter("职务_In", job)
                       .ParamterOut("Resultlist", true);
            execute.Execute();
            var dt = execute.ParameterValue<DataTable>("Resultlist");
            return dt;
        }

        #region 删除人员
        public int Delete_Person(string id)
        {
            try
            {
                //修改人员信息的是否删除字段
                int r = UpdateBuilder.Update("人员信息").Column("是否删除", -1).Column("联系电话",DBNull.Value).Where("ID", id).Execute();
                if (r == 1)
                {
                    //先判断该人员是否拥有账户
                    DataTable dt = SelectBuilder.From("系统账户").Where("人员ID", id).Columns("ID").Select();
                    if (dt.Rows.Count > 0)
                    {
                       //清空账号相关信息
                        string AccountID= SelectBuilder.From("系统账户").Columns("ID").Where("人员ID", id).Select().Rows[0]["ID"].ToString();
                        DeleteBulider.Delete("系统账户权限").Where("账户ID", AccountID).Execute();
                        DeleteBulider.Delete("系统角色关系").Where("账户ID", AccountID).Execute();
                        DeleteBulider.Delete("系统账户").Where("ID", AccountID).Execute();
                    }
                    return 1;
                }
                return 0;
            }
            catch (Exception ex)
            {
                Logger.Instance.Error("调用Delete_Person时发生异常错误", ex);
                return 0;
            }

        }
        #endregion

        #region 批量删除
        public int Delete_Batch(string id)
        {
            try
            {
                var exe = this.ProcedureBuilder
                      .Procedure("B_基础管理_机构人员.p_人员信息条件_BatchDelete")
                      .Paramter("人员ID_In", id)
                      .ParamterOut("Resultlist", DbType.Double, 0);
                exe.Execute();
                var count = exe.ParameterValue<Decimal>("Resultlist");
                return int.Parse(count.ToString());
            }
            catch (Exception ex)
            {
                Logger.Instance.Error("调用Delete_Batch时发生异常错误", ex);
                return 0;
            }
        }
        #endregion

        #region 批量创建账户
        /// <summary>
        /// 传人员ID字符串进行批量创建账户
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public int Add_Account_Batch(string id)
        {
            var exe = this.ProcedureBuilder
                  .Procedure("B_基础管理_机构人员.p_人员信息条件_BatchAdd")
                  .Paramter("人员ID_In", id)
                  .ParamterOut("Resultlist", DbType.Double, 0);
            exe.Execute();
            var count = exe.ParameterValue<Decimal>("Resultlist");
            return int.Parse(count.ToString());

        }

        //添加数据权限
        public bool addAccountAuthod(int AccountId, string PersonId)
        {
            var result = false;
            //获取人员相关信息
            var dt = this.SelectBuilder.Columns("性质,渠道ID,项目ID").From("人员信息").Where("ID", PersonId).Select();
            var PersonProperty = dt.Rows[0].GetValueByName<int>("性质");
            var ChannelId = dt.Rows[0].GetValueByName<string>("渠道ID");
            var Project = dt.Rows[0].GetValueByName<string>("项目ID");
            ///如果为总部，分配全部渠道
            if (ChannelId == "4A9B2065-65AB-4411-B528-968D26737EAE")
            {
                //获取所有渠道
                var AllChannelId = this.SelectBuilder.Columns("ID").From("渠道信息").Where("状态=1").Select();
                var results = 0;
                foreach (DataRow dr in AllChannelId.Rows)
                {
                    var Channels = dr.GetValueByName<string>("ID");
                    //获取渠道下所有项目
                    var sql = @"select Wm_Concat(t.ID) AS 项目信息 from 项目信息 t where t.渠道ID=:id and t.启用=1";
                    var Projects = this.SqlBuilder.SqlText(sql).Parameters("id", Channels).Select<string>();
                    var model = new AccountDataModel(this.DataFactoryObject)
                    {
                        GSID = Channels,
                        ZHID = AccountId,
                        XMID = Projects
                    };
                    results += model.Insert();
                }
                return results > 0;
            }
            //其他人员性质
            if (PersonProperty == 1 || PersonProperty == 2 || PersonProperty == 5)
            {
                //获取渠道下所有项目
                var sql = @"select Wm_Concat(t.ID) AS 项目信息 from 项目信息 t where t.渠道ID=:id and t.启用=1";
                var ProjectAll = this.SqlBuilder.SqlText(sql).Parameters("id", ChannelId).Select<string>();
                var model = new AccountDataModel(this.DataFactoryObject)
                {
                    GSID = ChannelId,
                    ZHID = AccountId,
                    XMID = ProjectAll
                };
                result = model.Insert() > 0;
            }
            ///如果为医院管理员
            else if (PersonProperty == 4)
            {
                var modelf = new AccountDataModel(this.DataFactoryObject)
                {
                    GSID = ChannelId,
                    ZHID = AccountId,
                    XMID = Project
                };
                result = modelf.Insert() > 0;
            }
            //如果为客户
            else
            {
                result = true;
            }
            return result;
        }

        #endregion

        #region 修改人员
        public int SaveUpdatePerson(PersonBusinessModel model)
        {
            using (var tran = this.DBTransaction.BeginTrans())
            {
                try
                {
                    int x = 0;
                    string Befor_Phone = SelectBuilder.From("人员信息").Columns("联系电话").Where("ID", model.ID).Select().Rows[0]["联系电话"].ToString();
                    DataTable dt = SelectBuilder.From("系统账户").Columns("ID").Where("账户", model.contactPhone).Select();
                    //判断修改后的手机号是否已经被当做账户使用
                    if (Befor_Phone != model.contactPhone && dt.Rows.Count > 0)
                    {
                        return -1;
                    }

                    x = UpdateBuilder.Update("人员信息")
                                    .Where("ID", model.ID)
                                    .Column("姓名", model.name.Trim())
                                    .Column("简码", model.simple)
                                    .Column("性别", model.sex)
                                    .Column("出生日期", model.birthDate)
                                    .Column("职务", model.job)
                                    .Column("状态", 1)
                                    .Column("入职时间", model.entryDate)
                                    .Column("说明", model.explain)
                                    .Column("联系电话", model.contactPhone)
                                    .Column("QQ", model.qq)
                                    .Column("微信", model.weChat)
                                    .Column("电子邮箱", model.email)
                                    .Column("性质", model.nature)
                                    .Column("渠道ID", model.channel)
                                    .Column("项目ID", model.project)
                                    .Column("站点ID", model.station)
                                    .Execute(tran);
                    if (x == 1)
                    {
                        int r = SelectBuilder.From("系统账户").Columns("ID").Where("人员ID", model.ID).Select().Rows.Count;
                        if (r > 0)
                        {
                            x = UpdateBuilder.Update("系统账户").Where("人员ID", model.ID).Column("账户", model.contactPhone).Column("姓名", model.name.Trim()).Execute(tran);
                        }
                    }
                    tran.CommitTrans();
                    return x;
                }
                catch (Exception ex)
                {
                    tran.Rollback();
                    Logger.Instance.Error("调用SaveUpdatePerson时发生异常错误", ex);
                    return 0;
                }
            }
        }

        public int ChangeActive(string id)
        {
            SqlBuilder.SqlText("update 人员信息 z set z.状态=decode(z.状态,1,2,1) where z.id=:ryid").Parameters("ryid", id).Execute();
            return SqlBuilder.SqlText("update 系统账户 z set z.状态=decode(z.状态,1,0,1) where z.人员ID=:ryid").Parameters("ryid", id).Execute();
        }


        /// <summary>
        /// 批量修改
        /// </summary>
        /// <param name="model"></param>
        /// <param name="rYID"></param>
        /// <returns></returns>
        public int Batch_Save(PersonBusinessModel model, string RYID)
        {
             return UpdateBuilder.Update("人员信息")
                .Where("ID", RYID)
                .Column("性别", model.sex)
                .Column("出生日期", model.birthDate)
                .Column("入职时间", model.entryDate)
                .Column("性质", model.nature)
                .Column("职务", model.job)
                .Column("说明", model.explain)
                .Column("项目ID", model.project)
                .Column("站点ID", model.station)
                .Execute();
        }
        #endregion

        #region 添加人员
        public int SaveAddPerson(PersonBusinessModel model)
        {
            try
            {
                int r = 0;
                var RYID = Guid.NewGuid().ToString(); //人员信息ID
                DataTable dt = SelectBuilder.From("人员信息").Columns("*").Where("联系电话", model.contactPhone).Select();
                if (dt.Rows.Count != 0)
                {
                    return -1;
                }

                if(model.entryDate.ToString()== "0001/1/1 0:00:00")
                {
                    model.entryDate = DataBaseBuilder.GetDateTime;
                }

                if (model.birthDate.ToString() == "0001/1/1 0:00:00")
                {
                    model.birthDate = DataBaseBuilder.GetDateTime;
                }
                //添加人员信息
                r = InsertBuilder.Insert("人员信息")
                    .Column("ID", RYID)
                    .Column("姓名", model.name.Trim())
                    .Column("简码", model.simple)
                    .Column("性别", model.sex)
                    .Column("出生日期", model.birthDate)
                    .Column("职务", model.job)
                    .Column("状态", 1)
                    .Column("联系电话", model.contactPhone)
                    .Column("QQ", model.qq)
                    .Column("微信", model.weChat)
                    .Column("电子邮箱", model.email)
                    .Column("入职时间", model.entryDate)
                    .Column("说明", model.explain)
                    .Column("性质", model.nature)
                    .Column("渠道ID", model.channel)
                    .Column("项目ID", model.project)
                    .Column("站点ID", model.station)
                    .Column("添加时间", this.DataBaseBuilder.GetDateTime)
                    .Execute();
                return r;
            }
            catch (Exception ex)
            {
                Logger.Instance.Error("调用SaveAddPerson时发生异常错误", ex);
                return 0;
            }

        }
        #endregion

        #region 信息查询
        /// <summary>
        /// 通过产品Code获取项目机构树
        /// </summary>
        /// <param name="project"></param>
        /// <param name="Code"></param>
        /// <returns></returns>
        public DataTable GetProjectTreeByCode(string project, string Code)
        {
            var exe = this.ProcedureBuilder
                          .Procedure("p_基础管理_渠道项目机构.p_项目机构树信息_SelectByCode")
                          .Paramter("权限项目ID_In", project)
                          .Paramter("产品编码_In", Code)
                          .ParamterOut("Resultlist", true);
            exe.Execute();
            var dt = exe.ParameterValue<DataTable>("Resultlist");
            return dt;
        }
        public DataTable GetProjectCobo(string ChannelID, string project)
        {
            var exe = this.ProcedureBuilder
               .Procedure("p_基础管理_渠道项目机构.p_项目信息Cobo_SelectByQX")
               .Paramter("渠道ID_In", ChannelID)
               .Paramter("权限项目ID_In", project)
               .ParamterOut("Resultlist", true);
            exe.Execute();
            var dt = exe.ParameterValue<DataTable>("Resultlist");
            return dt;
        }
        public DataTable GetAllProject(string project)
        {
            var exe = this.ProcedureBuilder
               .Procedure("p_基础管理_渠道项目机构.p_所有项目信息Cobo_SelectByQX")
               .Paramter("权限项目ID_In", project)
               .ParamterOut("Resultlist", true);
            exe.Execute();
            var dt = exe.ParameterValue<DataTable>("Resultlist");
            return dt;
            //var sql = @"Select ID,  名称
            //              From 项目信息
            //              where 启用=1 and ID in ( "+ project+" )";
            //return this.SqlBuilder.SqlText(sql).Select();

        }

        /// <summary>
        /// 根据登录人获取渠道
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public DataTable GetAll_Channel(string channelID)
        {
            string QueryString = null;
            var exe = this.ProcedureBuilder
                  .Procedure("B_基础管理_机构人员.p_渠道信息_Select_All")
                  .Paramter("渠道权限ID_In", channelID)
                  .Paramter("查询条件_In", QueryString)
                  .ParamterOut("Resultlist", true);
            exe.Execute();
            var dt = exe.ParameterValue<DataTable>("Resultlist");
            return dt;
        }

        /// <summary>
        /// 根据渠道ID获取人员信息
        /// </summary>
        /// <param name="id"></param>
        /// <param name="page"></param>
        /// <param name="rows"></param>
        /// <returns></returns>
        public string GetAll_PersonInfo(string id, int page, int rows, string query, string nature, string project)
        {
            var exe = this.ProcedureBuilder
                  .Procedure("B_基础管理_机构人员.p_人员信息_Select")
                  .Paramter("查询条件_In", query)
                  .Paramter("人员性质_In", nature)
                  .Paramter("项目ID_In", project)
                  .Paramter("渠道ID_In", id)
                  .Paramter("开始行_In", page)
                  .Paramter("结束行_In", rows)
                  .ParamterOut("total", DbType.Double, 0)
                  .ParamterOut("Resultlist", true);
            exe.Execute();
            var dt = exe.ParameterValue<DataTable>("Resultlist");
            var total = exe.ParameterValue<decimal>("total");
            return dt.ToEasyUIGridJson(int.Parse(total.ToString()));
        }

        /// 获取角色
        /// </summary>
        /// <returns></returns>
        public DataTable GetRole(int personproperty)
        {
            string sql;
            if (personproperty == 1)
            {
                 sql = "select *  from 系统角色 where 公开=1 and 启用=1";
            }
            else
            {
                sql = "select *  from 系统角色 where 公开=1 and 启用=1 and (id=1 or id=2 or id=3 or id=4 or id=5)";
            }
            return SqlBuilder.SqlText(sql).Select();
        }

        /// <summary>
        /// 获取项目
        /// </summary>
        /// <returns></returns>
        public DataTable GetProject(string ChannelID, string query, string project)
        {
            var exe = this.ProcedureBuilder
                .Procedure("p_基础管理_渠道项目机构.p_项目信息_SelectByQX")
                .Paramter("渠道ID_In", ChannelID)
                .Paramter("权限项目ID_In", project)//取消项目授权
                .Paramter("查询条件_In", query)
                .ParamterOut("Resultlist", true);
            exe.Execute();
            var dt = exe.ParameterValue<DataTable>("Resultlist");
            return dt;
        }
        /// <summary>
        /// 获取项目机构树
        /// </summary>
        /// <param name="ChannelID"></param>
        /// <param name="project"></param>
        /// <returns></returns>
        public DataTable GetProjectTree(string project)
        {
            var exe = this.ProcedureBuilder
                           .Procedure("p_基础管理_渠道项目机构.p_项目机构树信息_SelectByQX")
                           .Paramter("权限项目ID_In", project)
                           .ParamterOut("Resultlist", true);
            exe.Execute();
            var dt = exe.ParameterValue<DataTable>("Resultlist");
            return dt;
        }

        /// <summary>
        /// 获取职务
        /// </summary>
        /// <returns></returns>
        public DataTable GetJob(UserInfo user)
        {
            if (user.PersonProperty == 1)
            {
                //本部人员
                string sql = "select 代码 as ID, 显示名 from 基础编码 where 分类ID='56411293-988F-42CA-8B84-5A34DD37C7E6' and 启用=1";
                return SqlBuilder.SqlText(sql).Select();
            }
            else
            {
                //渠道人员
                string sql = "select 代码 as ID, 显示名 from 基础编码 where 分类ID='56411293-988F-42CA-8B84-5A34DD37C7E6' and 启用=1 and (代码 =5 or 代码=3 or 代码=4 or 代码=8 or 代码=9)";
                return SqlBuilder.SqlText(sql).Select();
            }
        }

        /// <summary>
        /// 获取机构
        /// </summary>
        /// <returns></returns>
        public DataTable GetStation(string ProjectID)
        {
            string sql = "select *  from 站点信息 where 项目ID=:ProjectID";
            return SqlBuilder.SqlText(sql).Parameters("ProjectID", ProjectID).Select();
        }

        /// <summary>
        /// 获取项目
        /// </summary>
        /// <returns></returns>
        public DataTable GetCurrentProject(string id)
        {
            string sql = "select *  from 项目信息 where ID=:id";
            return SqlBuilder.SqlText(sql).Parameters("id", id).Select();
        }

        /// <summary>
        /// 根据人员ID获取渠道ID
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public DataTable GetChannelID(string id)
        {
            return SelectBuilder.From("人员信息").Columns("*").Where("ID", id).Select();
        }

        /// <summary>
        /// 根据账户ID获取系统账户的角色信息
        /// </summary>
        /// <returns></returns>
        public DataTable GetRoleInfo(string id)
        {
            //获取系统账户的ID
            DataTable dz = GetSysAccountInfo(id);
            if (dz.Rows.Count == 0)
            {
                return null;
            }
            var ID = dz.Rows[0]["ID"];

            //获取系统账户关系的角色ID
            string sql = "select * from  系统角色关系 where 账户ID=:ID";
            DataTable dt = SqlBuilder.SqlText(sql).Parameters("ID", ID).Select();
            return dt;
        }

        /// <summary>
        /// 获取性别
        /// </summary>
        /// <returns></returns>
        public DataTable GetSex()
        {
            string sql = "select 代码 as ID, 显示名 from 基础编码 where 分类ID='7985483F-7DA1-4BA7-AC97-52EB905B6092' and 启用=1";
            return SqlBuilder.SqlText(sql).Select();
        }


        /// <summary>
        /// 获取性质
        /// </summary>
        /// <returns></returns>
        public DataTable GetXZ(int PersonProperty)
        {
            if (PersonProperty == 1)
            {
                string sql = "select 代码 as ID, 显示名 from 基础编码 where 分类ID='D3EDEA7A-7F07-4DD3-9A6B-34DB16A6E982'  and 启用=1";
                return SqlBuilder.SqlText(sql).Select();
            }
            else
            {
                string sql = "select 代码 as ID, 显示名 from 基础编码 where 分类ID='D3EDEA7A-7F07-4DD3-9A6B-34DB16A6E982'  and 启用=1 and 代码!=1";
                return SqlBuilder.SqlText(sql).Select();
            }
        }

        /// <summary>
        /// 根据人员ID获取系统账户的信息
        /// </summary>
        /// <returns></returns>
        public DataTable GetSysAccountInfo(string id)
        {
            string sql = "select * from  系统账户 where 人员ID=:id";
            DataTable dt = SqlBuilder.SqlText(sql).Parameters("id", id).Select();
            return dt;
        }

        /// <summary>
        /// 根据人员ID获取人员信息表数据
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public DataTable GetInfoByID(string id)
        {
            string sql = "select * from  人员信息 where ID=:id";
            DataTable dt = SqlBuilder.SqlText(sql).Parameters("id", id).Select();
            return dt;
        }
        #endregion

        #region 生成单个账号
        //判断是否有账号
        public int CheckAccount(string id)
        {
            return SelectBuilder.From("系统账户").Columns("*").Where("人员ID", id).Select().Rows.Count;
        }

        //创建
        public int Add_Account(PersonBusinessModel model, string role)
        {
            using (var tran = this.DBTransaction.BeginTrans())
            {
                try
                {
                    int r = 0;

                    //判断该人员是否已经拥有账户
                    if (GetSysAccountInfo(model.ID).Rows.Count > 0)
                    {
                        return -2;
                    }

                    //保存信息前先判断输入的账号是否已经使用
                    DataTable dt = SelectBuilder.From("系统账户").Columns("*").Where("账户", model.contactPhone).Select();
                    if (dt.Rows.Count != 0)
                    {
                        return -1;
                    }

                    //取序列
                    string sql = "select 系统账户_ID.nextval from dual";
                    dt = SqlBuilder.SqlText(sql).Select();
                    int XTID = int.Parse(dt.Rows[0]["nextval"].ToString());
                    var PWD = QWPlatform.SystemLibrary.Utils.Strings.MD5(model.password1);
                    r = InsertBuilder.Insert("系统账户")
                           .Column("ID", XTID)
                           .Column("密码", PWD)
                           .Column("邮箱", model.email)
                           .Column("姓名", model.name)
                           .Column("状态", 1)
                           .Column("账户", model.contactPhone)
                           .Column("类型", 0)
                           .Column("公司ID", model.channel)
                           .Column("人员ID", model.ID)
                           .Execute(tran);
                    tran.CommitTrans();
                    //判断添加到系统账户的操作是否成功  
                    if (r == 1)
                    {
                        //如果创建账号时该人员没有电话则将该账号设为联系电话
                        if (SelectBuilder.From("人员信息").Columns("联系电话").Where("ID", model.ID).Select().Rows[0]["联系电话"].ToString().Length == 0)
                        {
                            UpdateBuilder.Update("人员信息").Column("联系电话", model.contactPhone).Where("ID", model.ID).Execute();
                        }
                        string[] para = role.ToString().Split(',');
                        foreach (var row in para)
                        {
                            //取序列
                            sql = "select 系统角色关系_ID.nextval from dual";
                            dt = SqlBuilder.SqlText(sql).Select();
                            int JSID = int.Parse(dt.Rows[0]["nextval"].ToString());
                            InsertBuilder.Insert("系统角色关系")
                                .Column("ID", JSID)
                                .Column("账户ID", XTID)
                                .Column("角色ID", row)
                                .Execute();
                        }
                        //添加数据权限
                        addAccountAuthod(XTID, model.ID);
                        r = 1;
                    }
                    return r;
                }
                catch (Exception ex)
                {
                    tran.Rollback();
                    Logger.Instance.Error("调用Add_Account时发生异常错误", ex);
                    return 0;
                }
            }
        }

        //创建人员时勾选自动生成账户
        public int AutoAccount(PersonBusinessModel model)
        {
            using (var tran = this.DBTransaction.BeginTrans())
            {
                try
                {
                    int r = 0;
                    var RYID = Guid.NewGuid().ToString(); //人员信息ID
                    DataTable dt = SelectBuilder.From("人员信息").Columns("*").Where("联系电话", model.contactPhone).Select();
                    if (dt.Rows.Count != 0)
                    {
                        return -2;
                    }

                    //添加人员信息
                    r = InsertBuilder.Insert("人员信息")
                        .Column("ID", RYID)
                        .Column("姓名", model.name.Trim())
                        .Column("简码", model.simple)
                        .Column("性别", model.sex)
                        .Column("出生日期", model.birthDate)
                        .Column("职务", model.job)
                        .Column("状态", 1)
                        .Column("联系电话", model.contactPhone)
                        .Column("QQ", model.qq)
                        .Column("微信", model.weChat)
                        .Column("电子邮箱", model.email)
                        .Column("入职时间", model.entryDate)
                        .Column("说明", model.explain)
                        .Column("性质", model.nature)
                        .Column("渠道ID", model.channel)
                        .Column("项目ID", model.project)
                        .Column("站点ID", model.station)
                        .Column("添加时间", this.DataBaseBuilder.GetDateTime)
                        .Execute(tran);
                    if (r == 1)
                    {
                        //保存信息前先判断输入的账号是否已经使用
                        dt = SelectBuilder.From("系统账户").Columns("*").Where("账户", model.contactPhone).Select();
                        if (dt.Rows.Count != 0)
                        {
                            return -1;
                        }

                        //取序列，创建账户
                        string sql = "select 系统账户_ID.nextval from dual";
                        dt = SqlBuilder.SqlText(sql).Select();
                        int XTID = int.Parse(dt.Rows[0]["nextval"].ToString());
                        r = InsertBuilder.Insert("系统账户")
                               .Column("ID", XTID)
                               .Column("密码", "202CB962AC59075B964B07152D234B70")
                               .Column("邮箱", model.email)
                               .Column("姓名", model.name.Trim())
                               .Column("状态", 1)
                               .Column("账户", model.contactPhone)
                               .Column("类型", 0)
                               .Column("公司ID", model.channel)
                               .Column("人员ID", RYID)
                               .Execute(tran);
                        tran.CommitTrans();
                        //判断添加到系统账户的操作是否成功  
                        if (r == 1)
                        {
                            //去序列
                            sql = "select 系统角色关系_ID.nextval from dual";
                            dt = SqlBuilder.SqlText(sql).Select();
                            int JSID = int.Parse(dt.Rows[0]["nextval"].ToString());
                            InsertBuilder.Insert("系统角色关系")
                                .Column("ID", JSID)
                                .Column("账户ID", XTID)
                                .Column("角色ID", 1)
                                .Execute();
                            //添加数据权限
                            addAccountAuthod(XTID, RYID);
                        }
                    }
                    
                    return r;
                }
                catch (Exception ex)
                {
                    tran.Rollback();
                    Logger.Instance.Error("调用AutoAccount时发生异常错误", ex);
                    return 0;
                }
            }
        }
        #endregion       


    }
}