JW/EMiS.DataLogic/SystemDAL/DataRangeDAL.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using EMIS.DataLogic.Repositories;
using EMIS.ViewModel;
using EMIS.ViewModel.SystemView;
using System.Linq.Expressions;
using EMIS.Entities;

namespace EMIS.DataLogic.SystemDAL
{
    public class DataRangeDAL
    {
        public UserRepository UserRepository { get; set; }
        public RoleRepository RoleRepository { get; set; }
        public MenuRepository MenuRepository { get; set; }
        public ClassmajorRepository ClassmajorRepository { get; set; }
        public StaffManageCampusRepository StaffManageCampusRepository { get; set; }
        public StaffManageCollegeRepository StaffManageCollegeRepository { get; set; }
        public RoleDataRangeRepository RoleDataRangeRepository { get; set; }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="roleID"></param>
        /// <param name="menuNo"></param>
        /// <returns></returns>
        public SYS_DataRange GetRoleDataRange(Guid? roleID, string menuNo)
        {
            var query = (from rdr in RoleDataRangeRepository.GetList(x => x.RecordStatus >= (int)SYS_STATUS.USABLE && (roleID == null || x.RoleID == roleID))
                         where rdr.MenuNo == menuNo
                         select new
                         {
                             DataRange = (SYS_DataRange)rdr.DataRangeID,
                             Order = 0
                         }).Concat(
                         from r in RoleRepository.GetList(x => x.RecordStatus >= (int)SYS_STATUS.USABLE)
                         where r.RoleID == roleID
                         select new { 
                             DataRange = (SYS_DataRange)(r.DefaultDataRange ?? (int)SYS_DataRange.All),
                             Order = 1
                         }).OrderBy(x => x.Order).ThenBy(x => x.DataRange);

            var result = query.FirstOrDefault();
            if (result == null)
            {
                return SYS_DataRange.Department;
            }
            else
            {
                return result.DataRange;
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="userID"></param>
        /// <param name="menuNo"></param>
        /// <returns></returns>
        public SYS_DataRange GetUserDataRange(Guid? userID, string menuNo)
        {
            var roleQuery = (from rdr in RoleDataRangeRepository.GetList(x => x.RecordStatus >= (int)SYS_STATUS.USABLE)
                         where rdr.MenuNo == menuNo
                         select new
                         {
                             rdr.RoleID,
                             DataRange = (SYS_DataRange)rdr.DataRangeID,
                             Order = 0
                         }).Concat(
                         from r in RoleRepository.GetList(x => x.RecordStatus >= (int)SYS_STATUS.USABLE)
                         select new
                         {
                             RoleID = (Guid?)r.RoleID,
                             DataRange = (SYS_DataRange)(r.DefaultDataRange ?? (int)SYS_DataRange.All),
                             Order = 1
                         })
                         .GroupBy(x => x.RoleID)
                         .Select(x => x.OrderBy(w => w.Order).ThenBy(w => w.DataRange).FirstOrDefault());

            var query = (from u in UserRepository.GetList(x => x.UserID == userID) 
                         from r in u.Sys_Role 
                         join rdr in roleQuery on r.RoleID equals rdr.RoleID 
                         orderby rdr.DataRange 
                         select rdr
                         );

            var result = query.FirstOrDefault();

            if (result == null)
            {
                return SYS_DataRange.Department;
            }
            else
            {
                return result.DataRange;
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="exp"></param>
        /// <param name="roleExp"></param>
        /// <param name="menuExp"></param>
        /// <returns></returns>
        public IQueryable<RoleDataRangeView> GetRoleDataRange(Expression<Func<Sys_RoleDataRange, bool>> exp, 
                Expression<Func<Sys_Role, bool>> roleExp = null, Expression<Func<Sys_Menu, bool>> menuExp = null) 
        {
            if (roleExp == null) roleExp = (x => true);
            if (menuExp == null) menuExp = (x => true);

            var q = (from m in MenuRepository.GetList(menuExp)
                     join rdr in
                         (
                             from rdr in RoleDataRangeRepository.GetList(exp)
                             join r in RoleRepository.GetList(roleExp) on rdr.RoleID equals r.RoleID
                             select new { range = rdr, role = r }
                             ) on m.MenuNo equals rdr.range.MenuNo into drdr
                     from erdr in drdr.DefaultIfEmpty()
                     select new RoleDataRangeView { 
                         MenuNo = m.MenuNo,
                         MenuName = m.MenuName,
                         ParentMenuNo = m.ParentMenuNo,
                         OrderNo = m.OrderNo,
                         RoleID = (erdr == null) ? null : (Guid?)erdr.role.RoleID,
                         RoleDataRangeID = (erdr == null) ? null : (Guid?)erdr.range.RoleDataRangeID,
                         DataRangeID = (erdr == null) ? null : (int?)erdr.range.DataRangeID,
                         IsLeaf = m.IsLeaf ?? false
                     });

            return q;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="departmentID"></param>
        /// <param name="menuNo"></param>
        /// <returns></returns>
        public IQueryable<Sys_User> GetDataRangeUserByDepartmentID(Guid departmentID, string menuNo)
        {
            var q = (from rdr in RoleDataRangeRepository.GetList(x => x.RecordStatus >= (int)SYS_STATUS.USABLE)
                     from u in rdr.Sys_Role.Sys_User
                     from rd in u.CF_Staff.CF_StaffManageDepartment
                     where rdr.DataRangeID == (int)SYS_DataRange.Department
                     && rdr.MenuNo == menuNo && rd.DepartmentID == departmentID
                     select u).Concat(
                    from rdr in RoleDataRangeRepository.GetList(x => x.RecordStatus >= (int)SYS_STATUS.USABLE)
                    from u in rdr.Sys_Role.Sys_User
                    from c in u.CF_Staff.CF_StaffManageCollege
                    from dl in c.CF_College.CF_Department
                    where rdr.DataRangeID == (int)SYS_DataRange.College
                    && rdr.MenuNo == menuNo && dl.DepartmentID == departmentID
                    select u).Concat(
                    from rdr in RoleDataRangeRepository.GetList(x => x.RecordStatus >= (int)SYS_STATUS.USABLE)
                    from u in rdr.Sys_Role.Sys_User
                    from c in u.CF_Staff.CF_StaffManageCampus
                    from cl in c.CF_Campus.CF_College 
                    from dl in cl.CF_Department
                    where rdr.DataRangeID == (int)SYS_DataRange.Campus
                    && rdr.MenuNo == menuNo && dl.DepartmentID == departmentID
                    select u).Concat(
                    from rdr in RoleDataRangeRepository.GetList(x => x.RecordStatus >= (int)SYS_STATUS.USABLE)
                    from u in rdr.Sys_Role.Sys_User
                    where rdr.DataRangeID == (int)SYS_DataRange.All
                    && rdr.MenuNo == menuNo
                    select u).Concat(
                    from u in UserRepository.GetList(x => x.RecordStatus >= (int)SYS_STATUS.USABLE)
                    from r in u.Sys_Role
                    from rdr in r.Sys_RoleDataRange.Where(x => x.MenuNo == menuNo).DefaultIfEmpty()
                    from rd in u.CF_Staff.CF_StaffManageDepartment
                    where r.DefaultDataRange == (int)SYS_DataRange.Department
                    && rdr.RoleDataRangeID == null && rd.DepartmentID == departmentID
                    select u).Concat(
                    from u in UserRepository.GetList(x => x.RecordStatus >= (int)SYS_STATUS.USABLE)
                    from r in u.Sys_Role
                    from rdr in r.Sys_RoleDataRange.Where(x => x.MenuNo == menuNo).DefaultIfEmpty()
                    from rc in u.CF_Staff.CF_StaffManageCollege 
                    from dl in rc.CF_College.CF_Department 
                    where r.DefaultDataRange == (int)SYS_DataRange.College
                    && rdr.RoleDataRangeID == null && dl.DepartmentID == departmentID
                    select u).Concat(
                    from u in UserRepository.GetList(x => x.RecordStatus >= (int)SYS_STATUS.USABLE)
                    from r in u.Sys_Role
                    from rdr in r.Sys_RoleDataRange.Where(x => x.MenuNo == menuNo).DefaultIfEmpty()
                    from c in u.CF_Staff.CF_StaffManageCampus
                    from cl in c.CF_Campus.CF_College
                    from dl in cl.CF_Department 
                    where r.DefaultDataRange == (int)SYS_DataRange.Campus
                    && rdr.RoleDataRangeID == null && dl.DepartmentID == departmentID
                    select u).Concat(
                    from u in UserRepository.GetList(x => x.RecordStatus >= (int)SYS_STATUS.USABLE)
                    from r in u.Sys_Role
                    from rdr in r.Sys_RoleDataRange.Where(x => x.MenuNo == menuNo).DefaultIfEmpty()
                    where r.DefaultDataRange == (int)SYS_DataRange.All
                    && rdr.RoleDataRangeID == null
                    select u).Distinct();

            return q;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="collegeID"></param>
        /// <param name="menuNo"></param>
        /// <returns></returns>
        public IQueryable<Sys_User> GetDataRangeUserByCollegeID(Guid collegeID, string menuNo)
        {
            var q = (from rdr in RoleDataRangeRepository.GetList(x => x.RecordStatus >= (int)SYS_STATUS.USABLE)
                    from u in rdr.Sys_Role.Sys_User
                    from rd in u.CF_Staff.CF_StaffManageDepartment
                    where rdr.DataRangeID == (int)SYS_DataRange.Department
                    && rdr.MenuNo == menuNo && rd.CF_Department.CollegeID == collegeID
                    select u).Concat(
                    from rdr in RoleDataRangeRepository.GetList(x => x.RecordStatus >= (int)SYS_STATUS.USABLE)
                    from u in rdr.Sys_Role.Sys_User 
                    from rc in u.CF_Staff.CF_StaffManageCollege 
                    where rdr.DataRangeID == (int)SYS_DataRange.College 
                    && rdr.MenuNo == menuNo && rc.CollegeID == collegeID 
                    select u).Concat(
                    from rdr in RoleDataRangeRepository.GetList(x => x.RecordStatus >= (int)SYS_STATUS.USABLE)
                    from u in rdr.Sys_Role.Sys_User 
                    from c in u.CF_Staff.CF_StaffManageCampus 
                    from cl in c.CF_Campus.CF_College 
                    where rdr.DataRangeID == (int)SYS_DataRange.Campus 
                    && rdr.MenuNo == menuNo && cl.CollegeID == collegeID
                    select u).Concat(
                    from rdr in RoleDataRangeRepository.GetList(x => x.RecordStatus >= (int)SYS_STATUS.USABLE)
                    from u in rdr.Sys_Role.Sys_User
                    where rdr.DataRangeID == (int)SYS_DataRange.All
                    && rdr.MenuNo == menuNo
                    select u).Concat(
                    from u in UserRepository.GetList(x => x.RecordStatus >= (int)SYS_STATUS.USABLE)
                    from r in u.Sys_Role
                    from rdr in r.Sys_RoleDataRange.Where(x => x.MenuNo == menuNo).DefaultIfEmpty()
                    from rd in u.CF_Staff.CF_StaffManageDepartment
                    where r.DefaultDataRange == (int)SYS_DataRange.Department
                    && rdr.RoleDataRangeID == null && rd.CF_Department.CollegeID == collegeID
                    select u).Concat(
                    from u in UserRepository.GetList(x => x.RecordStatus >= (int)SYS_STATUS.USABLE) 
                    from r in u.Sys_Role
                    from rdr in r.Sys_RoleDataRange.Where(x => x.MenuNo == menuNo).DefaultIfEmpty() 
                    from rc in u.CF_Staff.CF_StaffManageCollege 
                    where r.DefaultDataRange == (int)SYS_DataRange.College 
                    && rdr.RoleDataRangeID == null && rc.CollegeID == collegeID
                    select u).Concat(
                    from u in UserRepository.GetList(x => x.RecordStatus >= (int)SYS_STATUS.USABLE)
                    from r in u.Sys_Role
                    from rdr in r.Sys_RoleDataRange.Where(x => x.MenuNo == menuNo).DefaultIfEmpty() 
                    from c in u.CF_Staff.CF_StaffManageCampus
                    from cl in c.CF_Campus.CF_College
                    where r.DefaultDataRange == (int)SYS_DataRange.Campus
                    && rdr.RoleDataRangeID == null && cl.CollegeID == collegeID
                    select u).Concat(
                    from u in UserRepository.GetList(x => x.RecordStatus >= (int)SYS_STATUS.USABLE)
                    from r in u.Sys_Role
                    from rdr in r.Sys_RoleDataRange.Where(x => x.MenuNo == menuNo).DefaultIfEmpty()
                    where r.DefaultDataRange == (int)SYS_DataRange.All
                    && rdr.RoleDataRangeID == null
                    select u).Distinct();

            return q;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="campusID"></param>
        /// <param name="menuNo"></param>
        /// <returns></returns>
        public IQueryable<Sys_User> GetDataRangeUserByCampusID(Guid campusID, string menuNo)
        {
            var q = (from rdr in RoleDataRangeRepository.GetList(x => x.RecordStatus >= (int)SYS_STATUS.USABLE)
                    from u in rdr.Sys_Role.Sys_User
                    from rd in u.CF_Staff.CF_StaffManageDepartment
                    where rdr.DataRangeID == (int)SYS_DataRange.Department
                    && rdr.MenuNo == menuNo && rd.CF_Department.CF_College.CampusID == campusID
                    select u).Concat(
                    from rdr in RoleDataRangeRepository.GetList(x => x.RecordStatus >= (int)SYS_STATUS.USABLE)
                    from u in rdr.Sys_Role.Sys_User
                    from rc in u.CF_Staff.CF_StaffManageCollege
                    where rdr.DataRangeID == (int)SYS_DataRange.College
                    && rdr.MenuNo == menuNo && rc.CF_College.CampusID == campusID
                    select u).Concat(
                    from rdr in RoleDataRangeRepository.GetList(x => x.RecordStatus >= (int)SYS_STATUS.USABLE)
                    from u in rdr.Sys_Role.Sys_User
                    from c in u.CF_Staff.CF_StaffManageCampus
                    where rdr.DataRangeID == (int)SYS_DataRange.Campus
                    && rdr.MenuNo == menuNo && c.CampusID == campusID
                    select u).Concat(
                    from rdr in RoleDataRangeRepository.GetList(x => x.RecordStatus >= (int)SYS_STATUS.USABLE)
                    from u in rdr.Sys_Role.Sys_User
                    where rdr.DataRangeID == (int)SYS_DataRange.All
                    && rdr.MenuNo == menuNo
                    select u).Concat(
                    from u in UserRepository.GetList(x => x.RecordStatus >= (int)SYS_STATUS.USABLE)
                    from r in u.Sys_Role
                    from rdr in r.Sys_RoleDataRange.Where(x => x.MenuNo == menuNo).DefaultIfEmpty()
                    from rd in u.CF_Staff.CF_StaffManageDepartment
                    where r.DefaultDataRange == (int)SYS_DataRange.Department
                    && rdr.RoleDataRangeID == null && rd.CF_Department.CF_College.CampusID == campusID
                    select u).Concat(
                    from u in UserRepository.GetList(x => x.RecordStatus >= (int)SYS_STATUS.USABLE)
                    from r in u.Sys_Role
                    from rdr in r.Sys_RoleDataRange.Where(x => x.MenuNo == menuNo).DefaultIfEmpty()
                    from rc in u.CF_Staff.CF_StaffManageCollege
                    where r.DefaultDataRange == (int)SYS_DataRange.College
                    && rdr.RoleDataRangeID == null && rc.CF_College.CampusID == campusID
                    select u).Concat(
                    from u in UserRepository.GetList(x => x.RecordStatus >= (int)SYS_STATUS.USABLE)
                    from r in u.Sys_Role
                    from rdr in r.Sys_RoleDataRange.Where(x => x.MenuNo == menuNo).DefaultIfEmpty()
                    from c in u.CF_Staff.CF_StaffManageCampus
                    where r.DefaultDataRange == (int)SYS_DataRange.Campus
                    && rdr.RoleDataRangeID == null && c.CampusID == campusID
                    select u).Concat(
                    from u in UserRepository.GetList(x => x.RecordStatus >= (int)SYS_STATUS.USABLE)
                    from r in u.Sys_Role
                    from rdr in r.Sys_RoleDataRange.Where(x => x.MenuNo == menuNo).DefaultIfEmpty()
                    where r.DefaultDataRange == (int)SYS_DataRange.All
                    && rdr.RoleDataRangeID == null
                    select u).Distinct();

            return q;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="userID"></param>
        /// <param name="menuNo"></param>
        /// <returns></returns>
        public IQueryable<Sys_User> GetDataRangeUserByUserID(Guid userID, string menuNo)
        {
            var q = (from rdr in RoleDataRangeRepository.GetList(x => x.RecordStatus >= (int)SYS_STATUS.USABLE)
                    from u in rdr.Sys_Role.Sys_User
                    from rd in u.CF_Staff.CF_StaffManageDepartment
                    from cu in rd.CF_Department.CF_College.CF_Staff
                    where rdr.DataRangeID == (int)SYS_DataRange.Department
                    && rdr.MenuNo == menuNo && cu.UserID == userID
                    select u).Concat(
                    from rdr in RoleDataRangeRepository.GetList(x => x.RecordStatus >= (int)SYS_STATUS.USABLE)
                    from u in rdr.Sys_Role.Sys_User
                    from rc in u.CF_Staff.CF_StaffManageCollege 
                    from cu in rc.CF_College.CF_Staff
                    where rdr.DataRangeID == (int)SYS_DataRange.College
                    && rdr.MenuNo == menuNo && cu.UserID == userID
                    select u).Concat(
                    from rdr in RoleDataRangeRepository.GetList(x => x.RecordStatus >= (int)SYS_STATUS.USABLE)
                    from u in rdr.Sys_Role.Sys_User
                    from c in u.CF_Staff.CF_StaffManageCampus
                    from cl in c.CF_Campus.CF_College
                    from cu in cl.CF_Staff
                    where rdr.DataRangeID == (int)SYS_DataRange.Campus
                    && rdr.MenuNo == menuNo && cu.UserID == userID
                    select u).Concat(
                    from rdr in RoleDataRangeRepository.GetList(x => x.RecordStatus >= (int)SYS_STATUS.USABLE)
                    from u in rdr.Sys_Role.Sys_User
                    where rdr.DataRangeID == (int)SYS_DataRange.All
                    && rdr.MenuNo == menuNo
                    select u).Concat(
                    from u in UserRepository.GetList(x => x.RecordStatus >= (int)SYS_STATUS.USABLE)
                    from r in u.Sys_Role
                    from rdr in r.Sys_RoleDataRange.Where(x => x.MenuNo == menuNo).DefaultIfEmpty()
                    from rd in u.CF_Staff.CF_StaffManageDepartment
                    from cu in rd.CF_Department.CF_College.CF_Staff
                    where r.DefaultDataRange == (int)SYS_DataRange.Department
                    && rdr.RoleDataRangeID == null && cu.UserID == userID
                    select u).Concat(
                    from u in UserRepository.GetList(x => x.RecordStatus >= (int)SYS_STATUS.USABLE)
                    from r in u.Sys_Role
                    from rdr in r.Sys_RoleDataRange.Where(x => x.MenuNo == menuNo).DefaultIfEmpty()
                    from rc in u.CF_Staff.CF_StaffManageCollege
                    from cu in rc.CF_College.CF_Staff
                    where r.DefaultDataRange == (int)SYS_DataRange.College
                    && rdr.RoleDataRangeID == null && cu.UserID == userID
                    select u).Concat(
                    from u in UserRepository.GetList(x => x.RecordStatus >= (int)SYS_STATUS.USABLE)
                    from r in u.Sys_Role
                    from rdr in r.Sys_RoleDataRange.Where(x => x.MenuNo == menuNo).DefaultIfEmpty()
                    from c in u.CF_Staff.CF_StaffManageCampus
                    from cl in c.CF_Campus.CF_College
                    from cu in cl.CF_Staff
                    where r.DefaultDataRange == (int)SYS_DataRange.Campus
                    && rdr.RoleDataRangeID == null && cu.UserID == userID
                    select u).Concat(
                    from u in UserRepository.GetList(x => x.RecordStatus >= (int)SYS_STATUS.USABLE)
                    from r in u.Sys_Role
                    from rdr in r.Sys_RoleDataRange.Where(x => x.MenuNo == menuNo).DefaultIfEmpty()
                    where r.DefaultDataRange == (int)SYS_DataRange.All
                    && rdr.RoleDataRangeID == null
                    select u).Distinct();

            return q;
        }

    }
}