using MadRunFabric.Common;
using MadRunFabric.Common.Options;
using Microsoft.Extensions.Logging;
using Microsoft.Extensions.Options;
using MongoDB.Driver;
using SignTokenApi.IRepositories;
using System;
using System.Collections.Generic;
using System.Linq;
using MadRunFabirc.Model;
using MadRunFabric.Model;

namespace SignTokenApi.Repositories
{
    public class Sys_User_AccountRepository : BaseRepository<Sys_User_Account, string>, ISys_User_AccountRepository
    {
        protected readonly ILogger<BaseRepository<Sys_User_Account, string>> _logger;
        protected readonly IMongoCollection<Sys_Department> _collection_sysdepartment;
        protected readonly IMongoCollection<Sys_Role_Info> _collection_sysroleinfo;
        protected readonly IMongoCollection<Sys_DictionaryValue> _collection_sys_dictionaryvalue;
        protected readonly IMongoCollection<Pro_Project_Info> _collection_proprojectinfo;
        protected readonly IMongoCollection<Sys_Post_Info> _collection_syspostinfo;

        public Sys_User_AccountRepository(IOptions<MongodbOptions> settings, ILogger<BaseRepository<Sys_User_Account, string>> logger) : base(settings, logger)
        {
            _collection_sysdepartment = _context.GetCollection<Sys_Department>();
            _collection_sysroleinfo = _context.GetCollection<Sys_Role_Info>();
            _collection_sys_dictionaryvalue = _context.GetCollection<Sys_DictionaryValue>();
            _collection_proprojectinfo = _context.GetCollection<Pro_Project_Info>();
            _collection_syspostinfo = _context.GetCollection<Sys_Post_Info>();
            _logger = logger;
        }

        /// <summary>
        /// Linq 关联查询 分页
        /// </summary>
        /// <param name="keyword"></param>
        /// <param name="provincecode"></param>
        /// <param name="citycode"></param>
        /// <param name="projectid"></param>
        /// <param name="protype"></param>
        /// <param name="systemid"></param>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="recordCount"></param>
        /// <returns></returns>
        public IEnumerable<object> GetListsByPage(string keyword, string deptid, string roleid, string projectid, int type, string allprojectrole, int pageindex, int pagesize, out int recordCount)
        {

            //关联查询
            var query =
                from usa in _collection.AsQueryable()
                join dept in _collection_sysdepartment.AsQueryable() on usa.dept_id equals dept.id into deptinfo
                join role in _collection_sysroleinfo.AsQueryable() on usa.role_id equals role.id into roleinfo
                where usa.delete_flag == false && usa.type != 2
                orderby usa.create_time descending
                select new
                {
                    usa.id,
                    usa.username,
                    usa.usercode,
                    usa.type,
                    usa.password,
                    usa.sex,
                    usa.see_flag,
                    usa.birthday,
                    usa.call_type,
                    usa.dept_id,
                    //dept_name = deptinfo.Count() > 0 ? deptinfo.First().departmenname : null,
                    usa.extensionnumber,
                    usa.@group,
                    usa.head_img,
                    usa.head_small_img,
                    usa.homephone,
                    usa.mail,
                    usa.mobile,
                    usa.qq,
                    usa.remark,
                    usa.role_id,
                    role_name = roleinfo.Count() > 0 ? roleinfo.First().role_name : null,
                    role_code = roleinfo.Count() > 0 ? roleinfo.First().role_code : null,
                    usa.seat_flag,
                    usa.seat_level,
                    usa.seat_right,
                    usa.weixin,
                    usa.weixin_img,
                    usa.weixin_name,
                    usa.sina,
                    usa.team_id,
                    usa.telephone,
                    usa.idcard,
                    usa.idcardno,
                    usa.certificate,
                    usa.entrytime,
                    usa.transfertime,
                    usa.quittime,
                    usa.postlist,
                    usa.projectlist
                };
            #region 查询条件
            if (!string.IsNullOrEmpty(keyword))
                query = query.Where(s => s.usercode.Contains(keyword) || s.username.Contains(keyword) || s.mobile.Contains(keyword) || s.telephone.Contains(keyword));
            if (type != -1)
                query = query.Where(it => it.type.Equals(type));
            if (!string.IsNullOrEmpty(deptid))
                query = query.Where(it => it.dept_id.Equals(deptid));
            if (!string.IsNullOrEmpty(roleid))
                query = query.Where(it => it.role_id.Equals(roleid));
            if (!string.IsNullOrEmpty(projectid))
                query = query.Where(it => it.projectlist.Contains(projectid));
            #endregion
            recordCount = query.Count();
            var list = query.Skip((pageindex - 1) * pagesize).Take(pagesize);

            //var userpostlist_query = from post in _collection_syspostinfo.AsQueryable() where post.isdelete == 0 select post;
            var userprolist_query = from pro in _collection_proprojectinfo.AsQueryable() where pro.isdelete == 0 select pro;

            var querys = list.ToList().Select(p =>
            {
                //var userpostlist = (from post in _collection_syspostinfo.AsQueryable() where p.postlist.Contains(post.id) && post.isdelete == 0 select post).ToList();
                //var userprolist = (from pro in _collection_proprojectinfo.AsQueryable() where p.projectlist.Contains(pro.id) && pro.isdelete == 0 select pro).ToList();
                //var userpostlist = userpostlist_query.Where(x=>p.postlist.Contains(x.id)).ToList();

                var userprolist = userprolist_query.Where(x=>p.projectlist.Contains(x.id)).ToList();

                int isallproject = 0;
                if (allprojectrole.Split("|").Contains(p.role_code))
                {
                    isallproject = 1;
                }

                //string postname = string.Join(',', userpostlist.Select(q => q.postname).ToList());
                string postname = string.Join(',', p.postlist.ToList());
                string projectname = string.Empty;
                if (isallproject == 0)
                {
                    projectname = string.Join(',', userprolist.Select(q => q.project_name).ToList());
                }
                return new
                {
                    p.id,
                    p.username,
                    p.usercode,
                    p.type,
                    //p.password,
                    p.sex,
                    p.see_flag,
                    p.birthday,
                    p.call_type,
                    p.dept_id,
                   // p.dept_name,
                    p.extensionnumber,
                    p.@group,
                    p.head_img,
                    p.head_small_img,
                    p.homephone,
                    p.mail,
                    p.mobile,
                    p.qq,
                    p.remark,
                    p.role_id,
                    p.role_name,
                    p.role_code,
                    isallproject,
                    p.seat_flag,
                    p.seat_level,
                    p.seat_right,
                    p.weixin,
                    p.weixin_img,
                    p.weixin_name,
                    p.sina,
                    p.team_id,
                    p.telephone,
                    p.idcard,
                    p.idcardno,
                    p.certificate,
                    p.entrytime,
                    p.transfertime,
                    p.quittime,
                    p.postlist,
                    postname ,
                    p.projectlist,
                    projectname 
                };
            });

            return querys;
        }

        /// <summary>
        /// Linq 关联查询 - App
        /// </summary>
        /// <param name="keyword"></param>
        /// <param name="provincecode"></param>
        /// <param name="citycode"></param>
        /// <param name="projectid"></param>
        /// <param name="protype"></param>
        /// <param name="systemid"></param>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="recordCount"></param>
        /// <returns></returns>
        public IEnumerable<object> GetAppLists(string keyword, string deptid, string roleid, string projectid, int type, string allprojectrole)
        {
            //关联查询
            var query =
                from usa in _collection.AsQueryable()
                join dept in _collection_sysdepartment.AsQueryable() on usa.dept_id equals dept.id into deptinfo
                join role in _collection_sysroleinfo.AsQueryable() on usa.role_id equals role.id into roleinfo
                where usa.delete_flag == false && usa.type != 2
                orderby usa.create_time descending
                select new
                {
                    usa.id,
                    usa.username,
                    usa.usercode,
                    usa.type,
                    usa.password,
                    usa.sex,
                    usa.see_flag,
                    usa.birthday,
                    usa.call_type,
                    usa.dept_id,
                    //dept_name = deptinfo.Count() > 0 ? deptinfo.First().departmenname : null,
                    usa.extensionnumber,
                    usa.@group,
                    usa.head_img,
                    usa.head_small_img,
                    usa.homephone,
                    usa.mail,
                    usa.mobile,
                    usa.qq,
                    usa.remark,
                    usa.role_id,
                    role_name = roleinfo.Count() > 0 ? roleinfo.First().role_name : null,
                    role_code = roleinfo.Count() > 0 ? roleinfo.First().role_code : null,
                    usa.seat_flag,
                    usa.seat_level,
                    usa.seat_right,
                    usa.weixin,
                    usa.weixin_img,
                    usa.weixin_name,
                    usa.sina,
                    usa.team_id,
                    usa.telephone,
                    usa.idcard,
                    usa.idcardno,
                    usa.certificate,
                    usa.entrytime,
                    usa.transfertime,
                    usa.quittime,
                    usa.postlist,
                    usa.projectlist
                };
            #region 查询条件
            if (!string.IsNullOrEmpty(keyword))
                query = query.Where(s => s.usercode.Contains(keyword) || s.username.Contains(keyword) || s.mobile.Contains(keyword) || s.telephone.Contains(keyword));
            if (type != -1)
                query = query.Where(it => it.type.Equals(type));
            if (!string.IsNullOrEmpty(deptid))
                query = query.Where(it => it.dept_id.Equals(deptid));
            if (!string.IsNullOrEmpty(roleid))
                query = query.Where(it => it.role_id.Equals(roleid));
            if (!string.IsNullOrEmpty(projectid))
                query = query.Where(it => it.projectlist.Contains(projectid));
            #endregion

            var querys = query.ToList().Select(p =>
            {
                //var userpostlist = (from post in _collection_syspostinfo.AsQueryable() where p.postlist.Contains(post.id) && post.isdelete == 0 select post).ToList();
                var userprolist = (from pro in _collection_proprojectinfo.AsQueryable() where p.projectlist.Contains(pro.id) && pro.isdelete == 0 select pro).ToList();

                int isallproject = 0;
                if (allprojectrole.Split("|").Contains(p.role_code))
                {
                    isallproject = 1;
                }

                string postname = string.Join(',', p.postlist.ToList());
                string projectname = string.Empty;
                if (isallproject == 0)
                {
                    projectname = string.Join(',', userprolist.Select(q => q.project_name).ToList());
                }
                return new
                {
                    p.id,
                    p.username,
                    p.usercode,
                    p.type,
                    //p.password,
                    p.sex,
                    p.see_flag,
                    p.birthday,
                    p.call_type,
                    p.dept_id,
                    //p.dept_name,
                    p.extensionnumber,
                    p.@group,
                    p.head_img,
                    p.head_small_img,
                    p.homephone,
                    p.mail,
                    p.mobile,
                    p.qq,
                    p.remark,
                    p.role_id,
                    p.role_name,
                    p.role_code,
                    isallproject,
                    p.seat_flag,
                    p.seat_level,
                    p.seat_right,
                    p.weixin,
                    p.weixin_img,
                    p.weixin_name,
                    p.sina,
                    p.team_id,
                    p.telephone,
                    p.idcard,
                    p.idcardno,
                    p.certificate,
                    p.entrytime,
                    p.transfertime,
                    p.quittime,
                    p.postlist,
                    postname,
                    p.projectlist,
                    projectname
                };
            });

            return querys;
        }

        /// <summary>
        /// Linq 关联查询详情
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public object GetDetails(string id)
        {
            //关联查询
            var query =
                from usa in _collection.AsQueryable()
                join dept in _collection_sysdepartment.AsQueryable() on usa.dept_id equals dept.id into deptinfo
                join role in _collection_sysroleinfo.AsQueryable() on usa.role_id equals role.id into roleinfo
                where usa.delete_flag == false && usa.id == id
                orderby usa.create_time descending
                select new
                {
                    usa.id,
                    usa.username,
                    usa.usercode,
                    usa.type,
                    usa.password,
                    usa.sex,
                    usa.see_flag,
                    usa.birthday,
                    usa.call_type,
                    usa.dept_id,
                    //dept_name = deptinfo.Count() > 0 ? deptinfo.First().departmenname : null,
                    usa.extensionnumber,
                    usa.@group,
                    usa.head_img,
                    usa.head_small_img,
                    usa.homephone,
                    usa.mail,
                    usa.mobile,
                    usa.qq,
                    usa.remark,
                    usa.role_id,
                    role_name = roleinfo.Count() > 0 ? roleinfo.First().role_name : null,
                    role_code = roleinfo.Count() > 0 ? roleinfo.First().role_code : null,
                    usa.seat_flag,
                    usa.seat_level,
                    usa.seat_right,
                    usa.sina,
                    usa.team_id,
                    usa.telephone,
                    usa.idcard,
                    usa.idcardno,
                    usa.certificate,
                    usa.entrytime,
                    usa.transfertime,
                    usa.quittime,
                    usa.postlist,
                    usa.projectlist
                };

            //var query_syspostinfo = from post in _collection_syspostinfo.AsQueryable() where post.isdelete == 0 select post;
            var query_proprojectinfo = from pro in _collection_proprojectinfo.AsQueryable() where pro.isdelete == 0 select pro;

            var userinfo = query.FirstOrDefault();
            if (userinfo != null)
            {
                //var userpostlist = query_syspostinfo.Where(x => userinfo.postlist.Contains(x.id));
                var userprolist = query_proprojectinfo.Where(x => userinfo.projectlist.Contains(x.id));

                var postnames = string.Join(',', userinfo.postlist.ToList());

                var model = new
                {
                    id = userinfo.id,
                    username = userinfo.username,
                    usercode = userinfo.usercode,
                    type = userinfo.type,
                    password = userinfo.password,
                    sex = userinfo.sex,
                    see_flag = userinfo.see_flag,
                    birthday = userinfo.birthday,
                    call_type = userinfo.call_type,
                    dept_id = userinfo.dept_id,
                    //dept_name = userinfo.dept_name,
                    extensionnumber = userinfo.extensionnumber,
                    @group = userinfo.@group,
                    head_img = userinfo.head_img,
                    head_small_img = userinfo.head_small_img,
                    homephone = userinfo.homephone,
                    mail = userinfo.mail,
                    mobile = userinfo.mobile,
                    qq = userinfo.qq,
                    remark = userinfo.remark,
                    role_id = userinfo.role_id,
                    role_name = userinfo.role_name,
                    role_code = userinfo.role_code,
                    seat_flag = userinfo.seat_flag,
                    seat_level = userinfo.seat_level,
                    seat_right = userinfo.seat_right,
                    sina = userinfo.sina,
                    team_id = userinfo.team_id,
                    telephone = userinfo.telephone,
                    idcard = userinfo.idcard,
                    idcardno = userinfo.idcardno,
                    certificate = userinfo.certificate,
                    entrytime = userinfo.entrytime,
                    transfertime = userinfo.transfertime,
                    quittime = userinfo.quittime,
                    postlist = userinfo.postlist,
                    //postname = string.Join(',', userpostlist.Select(q => q.postname).ToList()),
                    postname = postnames,
                    projectlist = userinfo.projectlist,
                    projectname = string.Join(',', userprolist.Select(q => q.project_name).ToList())
                };
                return model;
            }
            return null;
        }

        /// <summary>
        /// Linq 关联查询详情
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public object GetUserInfo(string usercode, string allprojectrole)
        {
            //关联查询
            var querys =
                from usa in _collection.AsQueryable()
                join dept in _collection_sysdepartment.AsQueryable() on usa.dept_id equals dept.id into deptinfo
                join role in _collection_sysroleinfo.AsQueryable() on usa.role_id equals role.id into roleinfo
                where usa.delete_flag == false && usa.usercode == usercode
                orderby usa.create_time descending
                select new
                {
                    usa.id,
                    usa.username,
                    usa.usercode,
                    usa.type,
                    usa.password,
                    usa.sex,
                    usa.see_flag,
                    usa.birthday,
                    usa.call_type,
                    usa.dept_id,
                    //dept_name = deptinfo.Count() > 0 ? deptinfo.First().departmenname : null,
                    usa.extensionnumber,
                    usa.@group,
                    usa.head_img,
                    usa.head_small_img,
                    usa.homephone,
                    usa.mail,
                    usa.mobile,
                    usa.qq,
                    usa.remark,
                    usa.role_id,
                    role_name = roleinfo.Count() > 0 ? roleinfo.First().role_name : null,
                    role_code = roleinfo.Count() > 0 ? roleinfo.First().role_code : null,
                    usa.seat_flag,
                    usa.seat_level,
                    usa.seat_right,
                    usa.weixin,
                    usa.weixin_img,
                    usa.weixin_name,
                    usa.sina,
                    usa.team_id,
                    usa.telephone,
                    usa.idcard,
                    usa.idcardno,
                    usa.certificate,
                    usa.entrytime,
                    usa.transfertime,
                    usa.quittime,
                    usa.postlist,
                    usa.projectlist
                };

           // var query_syspostinfo = from post in _collection_syspostinfo.AsQueryable() where post.isdelete == 0 select post;
            var query_proprojectinfo = from pro in _collection_proprojectinfo.AsQueryable() where pro.isdelete == 0 select pro;

            var userinfo = querys.FirstOrDefault();
            if (userinfo != null)
            {
                //var userpostlist = query_syspostinfo.Where(x => userinfo.postlist.Contains(x.id));
                var userprolist = query_proprojectinfo.Where(x => userinfo.projectlist.Contains(x.id));
                var postnames = string.Join(',', userinfo.postlist.ToList());
                int level = 0;
                switch (userinfo.role_code)
                {
                    case "YWYG": level = 1; break;
                    case "YWZG": level = 2; break;
                    case "YWFJL": level = 3; break;
                    case "YWJL": level = 4; break;
                    case "YWZJ": level = 5; break;
                    case "CJGL": level = 6; break;
                    case "XTWH": level = 7; break;
                }
                int isallproject = 0;
                if (allprojectrole.Split("|").Contains(userinfo.role_code) || userinfo.type == 2)
                {
                    isallproject = 1;
                }
                var model = new
                {
                    id = userinfo.id,
                    username = userinfo.username,
                    usercode = userinfo.usercode,
                    type = userinfo.type,
                    //p.password,
                    sex = userinfo.sex,
                    see_flag = userinfo.see_flag,
                    birthday = userinfo.birthday,
                    call_type = userinfo.call_type,
                    dept_id = userinfo.dept_id,
                    //dept_name = userinfo.dept_name,
                    extensionnumber = userinfo.extensionnumber,
                    @group = userinfo.@group,
                    head_img = userinfo.head_img,
                    head_small_img = userinfo.head_small_img,
                    homephone = userinfo.homephone,
                    mail = userinfo.mail,
                    mobile = userinfo.mobile,
                    qq = userinfo.qq,
                    remark = userinfo.remark,
                    role_id = userinfo.role_id,
                    role_name = userinfo.role_name,
                    role_code = userinfo.role_code,
                    level = level,
                    isallproject = isallproject,
                    seat_flag = userinfo.seat_flag,
                    seat_level = userinfo.seat_level,
                    seat_right = userinfo.seat_right,
                    weixin = userinfo.weixin,
                    weixin_img = userinfo.weixin_img,
                    weixin_name = userinfo.weixin_name,
                    sina = userinfo.sina,
                    team_id = userinfo.team_id,
                    telephone = userinfo.telephone,
                    idcard = userinfo.idcard,
                    idcardno = userinfo.idcardno,
                    certificate = userinfo.certificate,
                    entrytime = userinfo.entrytime,
                    transfertime = userinfo.transfertime,
                    quittime = userinfo.quittime,
                    postlist = userinfo.postlist,
                    //postname = string.Join(',', userpostlist.Select(q => q.postname).ToList()),
                    postname = postnames,
                    projectlist = userinfo.projectlist,
                    projectname = string.Join(',', userprolist.Select(q => q.project_name).ToList())
                };
                return model;
            }
            return null;
        }

        /// <summary>
        /// 列表 - 根据项目id，权限code获取列表
        /// </summary>
        /// <param name="projectid"></param>
        /// <param name="rolecode"></param>
        /// <returns></returns>
        public IEnumerable<object> GetProjectUser(string projectid, string rolecode)
        {
            //关联查询
            var query =
                from usa in _collection.AsQueryable()
                //join dept in _collection_sysdepartment.AsQueryable() on usa.dept_id equals dept.id into deptinfo
                join role in _collection_sysroleinfo.AsQueryable() on usa.role_id equals role.id into roleinfo
                where usa.delete_flag == false && usa.type != 2
                orderby usa.create_time descending
                select new
                {
                    usa.id,
                    usa.username,
                    usa.usercode,
                    //usa.type,
                    //usa.password,
                    //usa.sex,
                    //usa.see_flag,
                    //usa.birthday,
                    //usa.call_type,
                    //usa.dept_id,
                    //dept_name = deptinfo.Count() > 0 ? deptinfo.First().departmenname : null,
                    //usa.extensionnumber,
                    //usa.@group,
                    //usa.head_img,
                    //usa.head_small_img,
                    //usa.homephone,
                    //usa.mail,
                    //usa.mobile,
                    //usa.qq,
                    //usa.remark,
                    //usa.role_id,
                    role_name = roleinfo.Count() > 0 ? roleinfo.First().role_name : null,
                    role_code = roleinfo.Count() > 0 ? roleinfo.First().role_code : null,
                    //usa.seat_flag,
                    //usa.seat_level,
                    //usa.seat_right,
                    //usa.weixin,
                    //usa.weixin_img,
                    //usa.weixin_name,
                    //usa.sina,
                    //usa.team_id,
                    //usa.telephone,
                    //usa.idcard,
                    //usa.idcardno,
                    //usa.certificate,
                    //usa.entrytime,
                    //usa.transfertime,
                    //usa.quittime,
                    //usa.postlist,
                    usa.projectlist
                };
            #region 查询条件
            //if (!string.IsNullOrEmpty(rolecode))
            //    query = query.Where(it => it.role_code.Equals(rolecode));

            if (!string.IsNullOrEmpty(rolecode))
            {
                List<string> list_rolecode = new List<string>(rolecode.Split(','));
                query = query.Where(it => list_rolecode.Contains(it.role_code));
            }
            if (!string.IsNullOrEmpty(projectid))
                query = query.Where(it => it.projectlist.Contains(projectid));
            #endregion

            return query;
        }


        //华谊售后系统使用
        /// <summary>
        /// Linq 关联查询 分页
        /// </summary>
        /// <param name="keyword"></param>
        /// <param name="provincecode"></param>
        /// <param name="citycode"></param>
        /// <param name="projectid"></param>
        /// <param name="protype"></param>
        /// <param name="systemid"></param>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="recordCount"></param>
        /// <returns></returns>
        public IEnumerable<object> HyGetListsByPage(string keyword, string deptid, string roleid, string projectid, int type, string allprojectrole, int pageindex, int pagesize, out int recordCount)
        {

            //关联查询
            var query =
                from usa in _collection.AsQueryable()
                join dept in _collection_sys_dictionaryvalue.AsQueryable() on usa.dept_id equals dept.id into deptinfo
                join role in _collection_sysroleinfo.AsQueryable() on usa.role_id equals role.id into roleinfo
                where usa.delete_flag == false && usa.type != 2
                orderby usa.create_time descending
                select new
                {
                    usa.id,
                    usa.username,
                    usa.usercode,
                    usa.type,
                    usa.password,
                    usa.sex,
                    usa.see_flag,
                    usa.birthday,
                    usa.call_type,
                    usa.dept_id,
                    //dept_name = deptinfo.Count() > 0 ? deptinfo.First().name : null,
                    usa.extensionnumber,
                    usa.@group,
                    usa.head_img,
                    usa.head_small_img,
                    usa.homephone,
                    usa.mail,
                    usa.mobile,
                    usa.qq,
                    usa.remark,
                    usa.role_id,
                    role_name = roleinfo.Count() > 0 ? roleinfo.First().role_name : null,
                    role_code = roleinfo.Count() > 0 ? roleinfo.First().role_code : null,
                    usa.seat_flag,
                    usa.seat_level,
                    usa.seat_right,
                    usa.weixin,
                    usa.weixin_img,
                    usa.weixin_name,
                    usa.sina,
                    usa.team_id,
                    usa.telephone,
                    usa.idcard,
                    usa.idcardno,
                    usa.certificate,
                    usa.entrytime,
                    usa.transfertime,
                    usa.quittime,
                    usa.postlist,
                    usa.projectlist,
                    usa.create_user ,
                    usa.create_time 
                };
            #region 查询条件
            if (!string.IsNullOrEmpty(keyword))
                query = query.Where(s => s.usercode.Contains(keyword) || s.username.Contains(keyword) || s.mobile.Contains(keyword) || s.telephone.Contains(keyword));
            if (type != -1)
                query = query.Where(it => it.type.Equals(type));
            if (!string.IsNullOrEmpty(deptid))
                query = query.Where(it => it.dept_id.Equals(deptid));
            if (!string.IsNullOrEmpty(roleid))
                query = query.Where(it => it.role_id.Equals(roleid));
            if (!string.IsNullOrEmpty(projectid))
                query = query.Where(it => it.projectlist.Contains(projectid));
            #endregion
            recordCount = query.Count();
            var list = query.Skip((pageindex - 1) * pagesize).Take(pagesize);

            var userpostlist_query = from post in _collection_syspostinfo.AsQueryable() where post.isdelete == 0 select post;
            var userprolist_query = from pro in _collection_proprojectinfo.AsQueryable() where pro.isdelete == 0 select pro;

            var querys = list.ToList().Select(p =>
            {
                //var userpostlist = (from post in _collection_syspostinfo.AsQueryable() where p.postlist.Contains(post.id) && post.isdelete == 0 select post).ToList();
                //var userprolist = (from pro in _collection_proprojectinfo.AsQueryable() where p.projectlist.Contains(pro.id) && pro.isdelete == 0 select pro).ToList();
                //var userpostlist = userpostlist_query.Where(x => p.postlist.Contains(x.id)).ToList();
                var userprolist = userprolist_query.Where(x => p.projectlist.Contains(x.id)).ToList();

                int isallproject = 0;
                if (allprojectrole.Split("|").Contains(p.role_code))
                {
                    isallproject = 1;
                }

                string postname = string.Join(',', p.postlist.ToList());
                string projectname = string.Empty;
                if (isallproject == 0)
                {
                    projectname = string.Join(',', userprolist.Select(q => q.project_name).ToList());
                }
                return new
                {
                    p.id,
                    p.username,
                    p.usercode,
                    p.type,
                    //p.password,
                    p.sex,
                    p.see_flag,
                    p.birthday,
                    p.call_type,
                    p.dept_id,
                    //p.dept_name,
                    p.extensionnumber,
                    p.@group,
                    p.head_img,
                    p.head_small_img,
                    p.homephone,
                    p.mail,
                    p.mobile,
                    p.qq,
                    p.remark,
                    p.role_id,
                    p.role_name,
                    p.role_code,
                    isallproject,
                    p.seat_flag,
                    p.seat_level,
                    p.seat_right,
                    p.weixin,
                    p.weixin_img,
                    p.weixin_name,
                    p.sina,
                    p.team_id,
                    p.telephone,
                    p.idcard,
                    p.idcardno,
                    p.certificate,
                    p.entrytime,
                    p.transfertime,
                    p.quittime,
                    p.postlist,
                    postname,
                    p.projectlist,
                    projectname,
                    p.create_user,
                    p.create_time
                };
            });

            return querys;
        }

        /// <summary>
        /// Linq 关联查询 - App
        /// </summary>
        /// <param name="keyword"></param>
        /// <param name="provincecode"></param>
        /// <param name="citycode"></param>
        /// <param name="projectid"></param>
        /// <param name="protype"></param>
        /// <param name="systemid"></param>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="recordCount"></param>
        /// <returns></returns>
        public IEnumerable<object> HyGetAppLists(string keyword, string deptid, string roleid, string projectid, int type, string allprojectrole)
        {
            //关联查询
            var query =
                from usa in _collection.AsQueryable()
                join dept in _collection_sys_dictionaryvalue.AsQueryable() on usa.dept_id equals dept.id into deptinfo
                join role in _collection_sysroleinfo.AsQueryable() on usa.role_id equals role.id into roleinfo
                where usa.delete_flag == false && usa.type != 2
                orderby usa.create_time descending
                select new
                {
                    usa.id,
                    usa.username,
                    usa.usercode,
                    usa.type,
                    usa.password,
                    usa.sex,
                    usa.see_flag,
                    usa.birthday,
                    usa.call_type,
                    usa.dept_id,
                    dept_name = usa.dept_id,// deptinfo.Count() > 0 ? deptinfo.First().name : null,
                    usa.extensionnumber,
                    usa.@group,
                    usa.head_img,
                    usa.head_small_img,
                    usa.homephone,
                    usa.mail,
                    usa.mobile,
                    usa.qq,
                    usa.remark,
                    usa.role_id,
                    role_name = roleinfo.Count() > 0 ? roleinfo.First().role_name : null,
                    role_code = roleinfo.Count() > 0 ? roleinfo.First().role_code : null,
                    usa.seat_flag,
                    usa.seat_level,
                    usa.seat_right,
                    usa.weixin,
                    usa.weixin_img,
                    usa.weixin_name,
                    usa.sina,
                    usa.team_id,
                    usa.telephone,
                    usa.idcard,
                    usa.idcardno,
                    usa.certificate,
                    usa.entrytime,
                    usa.transfertime,
                    usa.quittime,
                    usa.postlist,
                    usa.projectlist,
                    usa.create_user,
                    usa.create_time
                };
            #region 查询条件
            if (!string.IsNullOrEmpty(keyword))
                query = query.Where(s => s.usercode.Contains(keyword) || s.username.Contains(keyword) || s.mobile.Contains(keyword) || s.telephone.Contains(keyword));
            if (type != -1)
                query = query.Where(it => it.type.Equals(type));
            if (!string.IsNullOrEmpty(deptid))
                query = query.Where(it => it.dept_id.Equals(deptid));
            if (!string.IsNullOrEmpty(roleid))
                query = query.Where(it => it.role_id.Equals(roleid));
            if (!string.IsNullOrEmpty(projectid))
                query = query.Where(it => it.projectlist.Contains(projectid));
            #endregion

            var querys = query.ToList().Select(p =>
            {
                //var userpostlist = (from post in _collection_syspostinfo.AsQueryable() where p.postlist.Contains(post.id) && post.isdelete == 0 select post).ToList();
                var userprolist = (from pro in _collection_proprojectinfo.AsQueryable() where p.projectlist.Contains(pro.id) && pro.isdelete == 0 select pro).ToList();

                int isallproject = 0;
                if (allprojectrole.Split("|").Contains(p.role_code))
                {
                    isallproject = 1;
                }

                string postname = string.Join(',', p.postlist.ToList());
                string projectname = string.Empty;
                if (isallproject == 0)
                {
                    projectname = string.Join(',', userprolist.Select(q => q.project_name).ToList());
                }
                return new
                {
                    p.id,
                    p.username,
                    p.usercode,
                    p.type,
                    //p.password,
                    p.sex,
                    p.see_flag,
                    p.birthday,
                    p.call_type,
                    p.dept_id,
                    p.dept_name,
                    p.extensionnumber,
                    p.@group,
                    p.head_img,
                    p.head_small_img,
                    p.homephone,
                    p.mail,
                    p.mobile,
                    p.qq,
                    p.remark,
                    p.role_id,
                    p.role_name,
                    p.role_code,
                    isallproject,
                    p.seat_flag,
                    p.seat_level,
                    p.seat_right,
                    p.weixin,
                    p.weixin_img,
                    p.weixin_name,
                    p.sina,
                    p.team_id,
                    p.telephone,
                    p.idcard,
                    p.idcardno,
                    p.certificate,
                    p.entrytime,
                    p.transfertime,
                    p.quittime,
                    p.postlist,
                    postname,
                    p.projectlist,
                    projectname,
                    p.create_user,
                    p.create_time
                };
            });

            return querys;
        }

        /// <summary>
        /// Linq 关联查询详情
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public object HyGetDetails(string id)
        {
            //关联查询
            var query =
                from usa in _collection.AsQueryable()
                join dept in _collection_sys_dictionaryvalue.AsQueryable() on usa.dept_id equals dept.id into deptinfo
                join role in _collection_sysroleinfo.AsQueryable() on usa.role_id equals role.id into roleinfo
                where usa.delete_flag == false && usa.id == id
                orderby usa.create_time descending
                select new
                {
                    usa.id,
                    usa.username,
                    usa.usercode,
                    usa.type,
                    usa.password,
                    usa.sex,
                    usa.see_flag,
                    usa.birthday,
                    usa.call_type,
                    usa.dept_id,
                    dept_name =  usa.dept_id,
                    //deptinfo.Count() > 0 ? deptinfo.First().name : null,
                    usa.extensionnumber,
                    usa.@group,
                    usa.head_img,
                    usa.head_small_img,
                    usa.homephone,
                    usa.mail,
                    usa.mobile,
                    usa.qq,
                    usa.remark,
                    usa.role_id,
                    role_name = roleinfo.Count() > 0 ? roleinfo.First().role_name : null,
                    role_code = roleinfo.Count() > 0 ? roleinfo.First().role_code : null,
                    usa.seat_flag,
                    usa.seat_level,
                    usa.seat_right,
                    usa.sina,
                    usa.team_id,
                    usa.telephone,
                    usa.idcard,
                    usa.idcardno,
                    usa.certificate,
                    usa.entrytime,
                    usa.transfertime,
                    usa.quittime,
                    usa.postlist,
                    usa.projectlist,
                    usa.create_user,
                    usa.create_time
                };

            //var query_syspostinfo = from post in _collection_syspostinfo.AsQueryable() where post.isdelete == 0 select post;
            var query_proprojectinfo = from pro in _collection_proprojectinfo.AsQueryable() where pro.isdelete == 0 select pro;

            var userinfo = query.FirstOrDefault();
            if (userinfo != null)
            {
                //var userpostlist = userinfo.postlist==null?;
                string postname = string.Join(',', userinfo.postlist.ToList());
                var userprolist = query_proprojectinfo.Where(x => userinfo.projectlist.Contains(x.id));

                var model = new
                {
                    id = userinfo.id,
                    username = userinfo.username,
                    usercode = userinfo.usercode,
                    type = userinfo.type,
                    password = userinfo.password,
                    sex = userinfo.sex,
                    see_flag = userinfo.see_flag,
                    birthday = userinfo.birthday,
                    call_type = userinfo.call_type,
                    dept_id = userinfo.dept_id,
                    dept_name = userinfo.dept_name,
                    extensionnumber = userinfo.extensionnumber,
                    @group = userinfo.@group,
                    head_img = userinfo.head_img,
                    head_small_img = userinfo.head_small_img,
                    homephone = userinfo.homephone,
                    mail = userinfo.mail,
                    mobile = userinfo.mobile,
                    qq = userinfo.qq,
                    remark = userinfo.remark,
                    role_id = userinfo.role_id,
                    role_name = userinfo.role_name,
                    role_code = userinfo.role_code,
                    seat_flag = userinfo.seat_flag,
                    seat_level = userinfo.seat_level,
                    seat_right = userinfo.seat_right,
                    sina = userinfo.sina,
                    team_id = userinfo.team_id,
                    telephone = userinfo.telephone,
                    idcard = userinfo.idcard,
                    idcardno = userinfo.idcardno,
                    certificate = userinfo.certificate,
                    entrytime = userinfo.entrytime,
                    transfertime = userinfo.transfertime,
                    quittime = userinfo.quittime,
                    postlist = userinfo.postlist,
                    postname = postname,
                    projectlist = userinfo.projectlist,
                    projectname = string.Join(',', userprolist.Select(q => q.project_name).ToList()),
                    create_user = userinfo.create_user,
                    create_time = userinfo.create_time
                    
                };
                return model;
            }
            return null;
        }

        /// <summary>
        /// Linq 关联查询详情
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public object HyGetUserInfo(string usercode, string allprojectrole)
        {
            //关联查询
            var querys =
                from usa in _collection.AsQueryable()
                join dept in _collection_sys_dictionaryvalue.AsQueryable() on usa.dept_id equals dept.id into deptinfo
                join role in _collection_sysroleinfo.AsQueryable() on usa.role_id equals role.id into roleinfo
                where usa.delete_flag == false && usa.usercode == usercode
                orderby usa.create_time descending
                select new
                {
                    usa.id,
                    usa.username,
                    usa.usercode,
                    usa.type,
                    usa.password,
                    usa.sex,
                    usa.see_flag,
                    usa.birthday,
                    usa.call_type,
                    usa.dept_id,
                    dept_name = usa.dept_id,
                    //deptinfo.Count() > 0 ? deptinfo.First().name : null,
                    usa.extensionnumber,
                    usa.@group,
                    usa.head_img,
                    usa.head_small_img,
                    usa.homephone,
                    usa.mail,
                    usa.mobile,
                    usa.qq,
                    usa.remark,
                    usa.role_id,
                    role_name = roleinfo.Count() > 0 ? roleinfo.First().role_name : null,
                    role_code = roleinfo.Count() > 0 ? roleinfo.First().role_code : null,
                    usa.seat_flag,
                    usa.seat_level,
                    usa.seat_right,
                    usa.weixin,
                    usa.weixin_img,
                    usa.weixin_name,
                    usa.sina,
                    usa.team_id,
                    usa.telephone,
                    usa.idcard,
                    usa.idcardno,
                    usa.certificate,
                    usa.entrytime,
                    usa.transfertime,
                    usa.quittime,
                    usa.postlist,
                    usa.projectlist,
                    usa.create_user,
                    usa.create_time
                };

            //var query_syspostinfo = from post in _collection_syspostinfo.AsQueryable() where post.isdelete == 0 select post;
            var query_proprojectinfo = from pro in _collection_proprojectinfo.AsQueryable() where pro.isdelete == 0 select pro;
            
            var userinfo = querys.FirstOrDefault();
            if (userinfo != null)
            {
                string postname = string.Join(',', userinfo.postlist.ToList());
                //var userpostlist = query_syspostinfo.Where(x => userinfo.postlist.Contains(x.id));
                var userprolist = query_proprojectinfo.Where(x => userinfo.projectlist.Contains(x.id));

                int level = 0;
                switch (userinfo.role_code)
                {
                    case "YWYG": level = 1; break;
                    case "YWZG": level = 2; break;
                    case "YWFJL": level = 3; break;
                    case "YWJL": level = 4; break;
                    case "YWZJ": level = 5; break;
                    case "CJGL": level = 6; break;
                    case "XTWH": level = 7; break;
                }
                int isallproject = 0;
                if (allprojectrole.Split("|").Contains(userinfo.role_code) || userinfo.type == 2)
                {
                    isallproject = 1;
                }
                var model = new
                {
                    id = userinfo.id,
                    username = userinfo.username,
                    usercode = userinfo.usercode,
                    type = userinfo.type,
                    //p.password,
                    sex = userinfo.sex,
                    see_flag = userinfo.see_flag,
                    birthday = userinfo.birthday,
                    call_type = userinfo.call_type,
                    dept_id = userinfo.dept_id,
                    dept_name = userinfo.dept_name,
                    extensionnumber = userinfo.extensionnumber,
                    @group = userinfo.@group,
                    head_img = userinfo.head_img,
                    head_small_img = userinfo.head_small_img,
                    homephone = userinfo.homephone,
                    mail = userinfo.mail,
                    mobile = userinfo.mobile,
                    qq = userinfo.qq,
                    remark = userinfo.remark,
                    role_id = userinfo.role_id,
                    role_name = userinfo.role_name,
                    role_code = userinfo.role_code,
                    level = level,
                    isallproject = isallproject,
                    seat_flag = userinfo.seat_flag,
                    seat_level = userinfo.seat_level,
                    seat_right = userinfo.seat_right,
                    weixin = userinfo.weixin,
                    weixin_img = userinfo.weixin_img,
                    weixin_name = userinfo.weixin_name,
                    sina = userinfo.sina,
                    team_id = userinfo.team_id,
                    telephone = userinfo.telephone,
                    idcard = userinfo.idcard,
                    idcardno = userinfo.idcardno,
                    certificate = userinfo.certificate,
                    entrytime = userinfo.entrytime,
                    transfertime = userinfo.transfertime,
                    quittime = userinfo.quittime,
                    postlist = userinfo.postlist,
                    postname = postname,
                    projectlist = userinfo.projectlist,
                    projectname = string.Join(',', userprolist.Select(q => q.project_name).ToList()),
                    create_user = userinfo.create_user,
                    create_time = userinfo.create_time
                };
                return model;
            }
            return null;
        }

        /// <summary>
        /// 列表 - 根据项目id，权限code获取列表
        /// </summary>
        /// <param name="projectid"></param>
        /// <param name="rolecode"></param>
        /// <returns></returns>
        public IEnumerable<object> HyGetProjectUser(string projectid, string rolecode)
        {
            //关联查询
            var query =
                from usa in _collection.AsQueryable()
                    //join dept in _collection_sysdepartment.AsQueryable() on usa.dept_id equals dept.id into deptinfo
                join role in _collection_sysroleinfo.AsQueryable() on usa.role_id equals role.id into roleinfo
                where usa.delete_flag == false && usa.type != 2
                orderby usa.create_time descending
                select new
                {
                    usa.id,
                    usa.username,
                    usa.usercode,
                    
                    role_name = roleinfo.Count() > 0 ? roleinfo.First().role_name : null,
                    role_code = roleinfo.Count() > 0 ? roleinfo.First().role_code : null,
                    
                    usa.projectlist
                };
            #region 查询条件
            //if (!string.IsNullOrEmpty(rolecode))
            //    query = query.Where(it => it.role_code.Equals(rolecode));

            if (!string.IsNullOrEmpty(rolecode))
            {
                List<string> list_rolecode = new List<string>(rolecode.Split(','));
                query = query.Where(it => list_rolecode.Contains(it.role_code));
            }
            if (!string.IsNullOrEmpty(projectid))
                query = query.Where(it => it.projectlist.Contains(projectid));
            #endregion

            return query;
        }
    }
}