ZLPMS/PMS.DBService/SysManager/AccountDBService.cs

using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using PMS.BusinessModels.Account;
using PMS.BusinessModels.Person;
using PMS.EntityModels.MedicalRecordManager;
using PMS.EntityModels.SysManager;
using PMS.Plugins.Common;
using QWPlatform.DataIntface;
using QWPlatform.IService;
using QWPlatform.Models;
using QWPlatform.SystemLibrary;

namespace PMS.DBService.SysManager
{
    /// <summary>
    /// 创 建 人：王海洋
    /// 创建日期：2018-12-8
    /// 功能描述：系统账户数据层
    /// </summary>
    public class AccountDBService : DataServiceBase
    {
        protected override void DBFctory(string conName)
        {
            base.DBFctory(conName);
        }

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

        /// <summary>
        /// 增加数据记录
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public int Add(AccountModel 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(AccountModel model)
        {
            model.SetDataFactory(this.DataFactoryObject);
            return model.Where("ID")
                        .Update("登录时间", "登录IP", "在线");
        }


        /// <summary>
        /// 更新数据库记录
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public int Update(AccountModel 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(AccountModel model)
        {
            model.SetDataFactory(this.DataFactoryObject);

            return model.Delete("ID");
        }

        /// <summary>
        /// 删除数据记录
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public int Delete(AccountModel 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 AccountModel Select(AccountModel model)
        {
            model.SetDataFactory(this.DataFactoryObject);
            model.Select();
            return model;
        }

        /// <summary>
        /// 根据账户ID获取相应的
        /// </summary>
        /// <param name="uid"></param>
        /// <returns></returns>
        public DataTable GetAccountInfo(int uid)
        {//todo:
            var sql = @"Select a.Id,a.人员id,a.账户, a.微信id, a.姓名, a.邮箱,a.类型,a.渠道所有项目, c.名称 as 渠道名称,p.站点id as 机构ID, p.职务,
                            (select 显示名 from 基础编码 where 分类ID  ='56411293-988F-42CA-8B84-5A34DD37C7E6' and 代码 = p.职务) as 职务名称,
                            (select wm_concat(角色id) from 系统角色关系 r where r.账户id = a.Id ) as 角色ID,
                            a.公司ID, p.项目ID, p.性质
                        From 系统账户 a, 渠道信息 c,人员信息 p
                        Where a.公司id = c.Id
                        and  a.人员id = p.id(+)
                        and a.Id = :ID ";
            return this.SqlBuilder.SqlText(sql)
                .Parameters("ID", uid)
                .Select();
        }

        //执行登录 
        public AccountModel Login(string account, string pwd)
        {
            var dt = this.SelectBuilder.Columns("*")
                .From("系统账户")
                .Where("账户", account)
                .Where("密码", pwd)
                .Select();

            var list = dt.ToModels<AccountModel>();
            if (list != null)
            {
                return list.First();
            }

            return null;
        }

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

        //获取机构信息
        public DataTable GetOrgs()
        {
            return this.SelectBuilder.Columns("ID", "编码", "名称")
                  .From("渠道信息")
                  .OrderBy("编码 ASC")
                  .Select();
        }



        //根据机构ID获取账户列表
        public DataTable SelectAccountByOrgId(string id, int page, int rows, out int total)
        {
            return this.SelectBuilder
                 .Columns("ID", "账户", "姓名", "邮箱", "状态", "类型", "登录时间", "登录IP", "渠道所有项目")
                 .From("系统账户")
                 .Where("公司ID", id)
                 .Where("人员ID is not null")
                 .Paging(page, rows)
                 .OrderBy("姓名 ASC")
                 .Select(out total);
        }

        //获取角色目录
        public DataTable SelectRoles(int page, int rows, out int total)
        {
            return this.SelectBuilder.Columns("*")
                 .From("系统角色")
                 .Paging(page, rows)
                 .OrderBy("序号 ASC")
                 .Select(out total);
        }

        //获取已启用的角色
        public DataTable SelectRolesForActive(int page, int rows, out int total)
        {
            return this.SelectBuilder.Columns("*")
                     .From("系统角色")
                     .Where("启用", 1)
                     .Paging(page, rows)
                     .OrderBy("序号 ASC")
                     .Select(out total);
        }

        //根据角色ID，查询出该角色的授权信息
        public DataTable SelectRoleAuth(int roleid)
        {
            //var sql = @"select * from (
            //                    Select t.Id, t.上级id, t.名称, t.图标, Decode(:角色ID, p.角色id, 'true', '') As Ck, p.功能id列表,p.角色id
            //                      From 系统模块 t, 系统角色权限 p
            //                      Where t.启用 = 1 And t.Id = p.模块id(+) 
            //                      Start With t.上级id Is Null
            //                      Connect By Prior t.Id = t.上级id
            //                      Order Siblings By t.序号 ) x
            //                    where x.角色ID = 1 or X.角色ID is null";

            var sql = @"select 模块ID From 系统角色权限 
                        where 授权节点 = 1
                        and 角色ID = :角色ID";

            var dt = this.SqlBuilder
                .SqlText(sql)
                .Parameters("角色ID", roleid)
                .Select();
            return dt;
        }

        /// <summary>
        /// 查询出已授权的账户数据授权
        /// </summary>
        /// <param name="id">账号id</param>
        /// <param name="AuthorizeAll">0默认读授权，1读取渠道下面所有项目</param>
        /// <returns></returns>
        public DataTable SelectAccountAuthData(int id, int AuthorizeAll=0, string CompanyID="")
        {
            return this.SelectBuilder.Columns("*")
            .From("系统账户权限")
            .Where("账户ID", id)
            .Select();
            //if (id == 0)
            //{
            //    return this.SelectBuilder.Columns("*")
            //  .From("系统账户权限")
            //  .Where("账户ID", id)
            //  .Select();
            //}
            //else
            //{
            //   var sql = @"select 渠道ID as  公司ID, ID  as 项目ID from 项目信息 where 渠道ID =:CompanyID";
            //    return SqlBuilder.SqlText(sql).Parameters("CompanyID", CompanyID).Select();
            //}
           

          
        }

        //根据模块ID与角色ID，获取已授权的功能
        public DataTable SelectRoleAuthFuns(int roleid, int moduleid)
        {
            var sql = @"select t.Id,
                               t.模块id,
                               t.按钮id, 
                               b.名称,
                               b.标识,
                               b.图标,
                               t.序号,
                               t.分组名,
                               nvl2(f.column_value,'true','false') as ck
                        from 系统模块按钮 t,系统角色权限 p, TABLE(f_split_string(p.功能id列表,',')) f,系统按钮 b
                        where t.模块id = :模块ID 
                              and t.模块id = p.模块id(+)
                              and t.按钮id = f.column_value(+)
                              and p.角色id(+) = :角色ID
                              and t.按钮id = b.id
                        order by t.序号";
            var dt = this.SqlBuilder
               .SqlText(sql)
               .Parameters("模块ID", moduleid)
               .Parameters("角色ID", roleid)
               .Select();
            return dt;
        }


        //添加角色权限
        public int InsertRoleAuth(RoleAuthModel model)
        {
            model.SetDataFactory(this.DataFactoryObject);
            return model.Insert();
        }

        //删除角色权限
        public int DeleteRoleAuth(RoleAuthModel model)
        {
            model.SetDataFactory(this.DataFactoryObject);
            return model
                 .Where("模块ID")
                 .Where("角色ID")
                 .Delete();
        }

        //添加账户的数据权限信息
        public int InsertAuthDataToAccount(AccountDataModel model)
        {
            model.SetDataFactory(this.DataFactoryObject);
            return model.Insert();
        }

        //清除所有数据权限
        public int RemoveAllAuth(string id)
        {
            return DeleteBulider.Delete("系统账户权限").Where("账户ID", id).Execute();
        }


        /// <summary>
        /// 删除数据权限
        /// </summary>
        /// <param name="uid">账户ID</param>
        public void DeleteDataAuth(int uid)
        {
            var model = new AccountDataModel(this.DataFactoryObject);
            model.ZHID = uid;
            model.Where("账户ID")
                .Delete();
        }

        //添加数据权限
        public void AddAuth(int uid,string channel,string projectId)
        {
            var ID = SqlBuilder.SqlText("select 系统账户权限_ID.NEXTVAL from dual").Select().Rows[0]["NEXTVAL"].ToString();
            InsertBuilder.Insert("系统账户权限").Column("账户ID", uid).Column("公司ID", channel).Column("项目ID", projectId).Column("ID", ID).Execute();
        }

        /// <summary>
        /// 添加 一个用户角色授权
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public int AddUserRoleAuth(AccountRoleModel model)
        {
            model.SetDataFactory(this.DataFactoryObject);
            return model.Insert();
        }

        //将项目授权到指定的账户上
        public int AuthProjectToAccount(int uid, string orgid, string projectid)
        {
            var pids = this.SelectBuilder.Columns("项目ID")
                .From("系统账户权限")
                .Where("账户ID", uid)
                .Where("公司ID", orgid)
                .Select<string>();

            if (!string.IsNullOrEmpty(pids))
            {//更新原来的授权信息
                var val = pids + "," + projectid;
                return this.UpdateBuilder.Column("项目ID", val)
                    .Update("系统账户权限")
                    .Where("账户ID", uid)
                    .Where("公司ID", orgid)
                    .Execute();
            }
            else
            {//需要插入一条新记录(授权完成)
                var model = new AccountDataModel(this.DataFactoryObject)
                {
                    GSID = orgid,
                    ZHID = uid,
                    XMID = projectid
                };

                return model.Insert();
            }
        }

        /// <summary>
        /// 根据角色查询出当前模块可用的按钮
        /// </summary>
        /// <param name="str_roles">角色列表</param>
        /// <param name="menuPath">访问路径</param>
        /// <param name="groupName">分组名</param>
        /// <returns></returns>
        public DataTable SelectModuleButtonsForUserRole(string str_roles, string menuPath, string groupName, bool isSuperAdmin)
        {
            var sql = @"/*根据角色，及当前的访问路径，分组名称查询出可使用的按钮*/
                        Select Distinct b.Id,b.名称,b.标识,b.图标,b.序号,b.颜色,m.分组名 
                        From 系统角色权限 p, 
                              系统按钮 b, 
                              系统模块按钮 m, 
                              Table(f_split_string(p.功能id列表,',')) pb
                        Where p.角色ID in (Select Column_Value from Table(f_split_string(:角色IDs,',')))
                              and p.模块ID = (select id from 系统模块 where 地址 like '%'|| :路径 ||'%') 
                              and m.模块id = p.模块id
                              and m.按钮id = pb.column_value
                              and b.Id = pb.column_value
                              and m.分组名= :分组名
                              order by 序号 asc ";
            var dt = this.SqlBuilder
                 .SqlText(sql)
                 .Parameters("角色IDs", str_roles)
                 .Parameters("路径", menuPath)
                 .Parameters("分组名", groupName)
                 .Select();

            return dt;
        }

        //修改密码
        public bool ChangePassword(int id, string md5pwd)
        {
            return this.UpdateBuilder
                .Column("密码", md5pwd)
                .Column("密码变更时间", DateTime.Now.AddDays(60))
                .Update("系统账户")
                .Where("ID", id)
                .Execute() > 0;
        }

        /// <summary>
        /// 检查密码是否输入正确
        /// </summary>
        /// <param name="id">账户ID</param>
        /// <param name="md5pwd">密码</param>
        /// <returns></returns>
        public bool CheckOldPwd(int id, string md5pwd)
        {
            return this.SelectBuilder.Columns("1")
                .From("系统账户")
                .Where("ID", id)
                .Where("密码", md5pwd)
                .Exists();
        }

        //删除当前用户指定角色权限
        public int DeleteUserRole(int userid)
        {
            var model = new AccountRoleModel(this.DataFactoryObject);
            model.ZHID = userid;
            return model.Where("账户ID")
                 .Delete();
        }

        //查询当前账户已授权的角色
        public DataTable SelectAccountRoles(int uid)
        {
            var sql = @"select r.Id,r.名称 from 系统角色关系 t,系统角色 r
                        where r.Id = t.角色id and t.账户id =:账户ID";

            return this.SqlBuilder.SqlText(sql)
                .Parameters("账户ID", uid)
                .Select();
        }

        //查询出渠道与渠道下的项目信息（用于授权数据使用）
        public DataTable SelectOrgProjectTree()
        {
            var sql = @"Select * 
                            From (Select t.Id, t.名称 as 名称, Null As 渠道id,1 as 是否渠道,t.编码 From 渠道信息 t 
                                         where t.状态 = 1  
                                 Union 
                                 Select p.Id, p.名称|| '（项目）' as 名称, p.渠道id,0 as 是否渠道, '' From 项目信息 p
                                 where p.渠道id is not null and p.启用=1
      
                                 ) x
                            Start With x.渠道id Is Null
                            Connect By Prior x.Id = x.渠道id
                            order by x.编码 asc";
            return this
                .SqlBuilder
                .SqlText(sql)
                .Select();
        }

        //根据角色ID，查询出该角色下的用户列表
        public DataTable GetUserByRoleId(int roleid, int rows, int page, out int total)
        {
            var sql = @"select t.Id,t.账户,t.姓名,t.邮箱,t.状态,(select 名称 from 渠道信息 x where x.Id=t.公司id) as 公司,
                               (select decode(p.性质,1,'中联',2,'渠道',3,'客户',4,'医疗管理员',5,'卫计委管理员') from 人员信息 p where p.Id = t.人员id) as 性质
                        from 系统账户 t,系统角色关系 r
                        where t.Id= r.账户id and r.角色id=:角色ID and t.账户 is not null
                        ";
            total = 0;
            return this.SqlBuilder.SqlText(sql)
                 .Parameters("角色ID", roleid) 
                 .Paging(page, rows)
                 .Select(out total);
        }

        /// <summary>
        /// 保存配置文件
        /// </summary>
        /// <param name="account"></param>
        /// <param name="myconfigInfo"></param>
        /// <returns></returns>
        public bool SaveConfig(int account, NotefiyConfigInfo myconfigInfo)
        {
            //序列化对象
            var bytes = QWPlatform.SystemLibrary.Serialize.SerializeHelper.SerializeBinary(myconfigInfo);

            //参数名称
            var settingName = "notefiy";

            var r = this.SelectBuilder.Columns("1")
                 .From("参数配置")
                 .Where("所属账户", account)
                 .Where("参数名", PMS.BusinessModels.PMSCONSTS.MYCONFIG)
                 .Exists();
            if (!r)
            {//不存在，插入记录
                return this.InsertBuilder
                 .Column("参数名", PMS.BusinessModels.PMSCONSTS.MYCONFIG)
                 .Column("参数内容", bytes)
                 .Column("所属账户", account)
                 .Insert("参数配置")
                 .Execute() > 0;
            }
            else
            {//更新记录
                return this.UpdateBuilder.Column("参数内容", bytes)
                    .Update("参数配置")
                    .Where("所属账户", account)
                    .Where("参数名", PMS.BusinessModels.PMSCONSTS.MYCONFIG)

                    .Execute() > 0;
            }


        }
        //保存消息配置
        public bool SaveMessageInfo(Mssage_config Mssage_config, int account)
        {
            // 序列化对象
            var bytes = QWPlatform.SystemLibrary.Serialize.SerializeHelper.SerializeBinary(Mssage_config);
            var r = this.SelectBuilder.Columns("1")
                .From("参数配置")
                .Where("所属账户", account)
                .Where("参数名", PMS.BusinessModels.PMSCONSTS.MESAAGECONFIG)
                .Exists();
            if (!r)
            {//不存在，插入记录
                return this.InsertBuilder
                 .Column("参数名", PMS.BusinessModels.PMSCONSTS.MESAAGECONFIG)
                 .Column("参数内容", bytes)
                 .Column("所属账户", account)
                 .Insert("参数配置")
                 .Execute() > 0;
            }
            else
            {//更新记录
                return this.UpdateBuilder.Column("参数内容", bytes)
                    .Update("参数配置")
                    .Where("所属账户", account)
                    .Where("参数名", PMS.BusinessModels.PMSCONSTS.MESAAGECONFIG)
                    .Execute() > 0;
            }

        }
        //获取消息配置
        public Mssage_config GetMessageInfo(int account)
        {
            var bytes = this.SelectBuilder.Columns("参数内容")
               .From("参数配置")
               .Where("参数名", PMS.BusinessModels.PMSCONSTS.MESAAGECONFIG)
               .Where("所属账户", account)
               .Select<byte[]>();
            if (bytes != null)
            {
                return QWPlatform.SystemLibrary.Serialize.SerializeHelper.DeserializeBinary(bytes) as Mssage_config;
            }

            return null;
        }

        /// <summary>
        /// 获取个人配置信息
        /// </summary>
        /// <param name="accoutn"></param>
        /// <returns></returns>
        public NotefiyConfigInfo GetConfigInfo(int account)
        {
            var bytes = this.SelectBuilder.Columns("参数内容")
                .From("参数配置")
                .Where("参数名", PMS.BusinessModels.PMSCONSTS.MYCONFIG)
                .Where("所属账户", account)
                .Select<byte[]>();
            if (bytes != null)
            {
                return QWPlatform.SystemLibrary.Serialize.SerializeHelper.DeserializeBinary(bytes) as NotefiyConfigInfo;
            }

            return null;
        }

        /// <summary>
        /// 修改个人信息
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public int UpdatePersonInfo(PersonBusinessModel model)
        {

            int r=UpdateBuilder.Update("人员信息").Column("姓名", model.name).Column("简码", model.simple).Column("性别", model.sex).Column("联系电话", model.contactPhone).Column("QQ", model.qq).Column("微信", model.weChat).Column("电子邮箱", model.email).Where("ID", model.ID).Execute();
            DataTable dt=SelectBuilder.From("系统账户").Where("账户", model.contactPhone).Columns("ID").Select();
            if (r!=0&&dt.Rows.Count>0)
            {
                r = UpdateBuilder.Update("系统账户").Where("人员ID", model.ID).Column("账户", model.contactPhone).Column("姓名", model.name).Column("邮箱", model.email).Execute();
            }
            return r;
        }

        /// <summary>
        /// 获取人员参数配置信息
        /// </summary>
        /// <param name="personId"></param>
        /// <returns></returns>
        public NotefiyConfigInfo GetNotifyConfigInfoByUserId(string personId)
        {
            var sql = @"select s.邮箱, p.参数内容 from 系统账户 s, 参数配置 p
                            where s.Id =p.所属账户  and p.参数名=:参数名 and s.人员id=:人员ID";
            var dt = this.SqlBuilder.SqlText(sql)
                .Parameters("参数名", PMS.BusinessModels.PMSCONSTS.MYCONFIG)
                .Parameters("人员ID", personId)
                .Select();

            if (dt != null && dt.Rows.Count > 0)
            {
                var email = dt.GetValueByName<string>("邮箱");
                var datas = dt.GetValueByName<byte[]>("参数内容");
                var configInfo = QWPlatform.SystemLibrary.Serialize.SerializeHelper.DeserializeBinary(datas) as NotefiyConfigInfo;
                if (configInfo != null)
                {
                    configInfo.Email = email;
                    return configInfo;
                }
            }

            return null;
        }
       /// <summary>
       /// 根据个人id获取系统账号信息
       /// </summary>
       /// <param name="personId"></param>
       /// <returns></returns>
        public DataTable GetAccountInfo(string personId) {
            DataTable dt = SelectBuilder.From("系统账户").Where("人员ID", personId).Columns("*").Select();
            return dt;
        }
        /// <summary>
        /// 根据手机号获取系统账号信息
        /// </summary>
        /// <param name="tel"></param>
        /// <returns></returns>
        public DataTable GetTelAccountInfo(string tel)
        {
            DataTable dt = SelectBuilder.From("系统账户").Where("账户", tel).Columns("*").Select();
            return dt;
        }
        /// <summary>
        /// 查询字典
        /// </summary>
        /// <param name="ID"></param>
        /// <returns></returns>
        public List<BasicDictionaryModel> SelectNature(string ID)
        {
            List<BasicDictionaryModel> List = new List<BasicDictionaryModel>();
            var dt = this.SelectBuilder.From("基础编码").Columns("ID,显示名 as 名称,代码,是否默认,说明,扩展").Where("分类ID", ID).Where("启用", 1).OrderBy("代码 asc").Select();
            if (dt.Rows.Count > 0)
            {
                List = Tools.ConvertToList<BasicDictionaryModel>(dt);
            }

            return List;

        }

    }
}