using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using Edu.Model.ViewModel.User;
using Edu.Model.Entity.User;
using Edu.Common.Enum.User;

namespace Edu.Repository.User
{
    /// <summary>
    /// 集团扩展方法
    /// </summary>
    public partial class Rb_attendanceRepository : BaseRepository<RB_Attendance>
    {
        /// <summary>
        /// 获取分页数据
        /// </summary>
        /// <param name="pageIndex">页码</param>
        /// <param name="pageSize">页大小</param>
        /// <param name="dmodel"></param>
        /// <param name="count">总条数</param>
        /// <param name="groupId">部门编号</param>
        /// <returns></returns>
        public List<RB_Attendance_Extend> GetPageList(int pageIndex, int pageSize, RB_Attendance_Extend dmodel, out long count, int groupId)
        {
            string where = " where 1=1 and b." + nameof(RB_School.Group_Id) + "=" + groupId;
            if (dmodel.RB_BranchId >= 0)
            {
                where += " and a." + nameof(RB_Attendance_Extend.RB_BranchId) + "=" + dmodel.RB_BranchId;
            }
            var pageList = GetPage<RB_Attendance_Extend>(pageIndex, pageSize, out count, " select * from(select a.*,b.SName as BranchName,(select count(*) from rb_account em where em.Status=0 and em.School_Id=a.RB_BranchId) as PeopleNum,(select GROUP_CONCAT(ws.Workday SEPARATOR '、') from rb_workdayseting ws where ws.AttendanceId=a.Id) as AttendanceDate from Rb_attendance a left join RB_School b on b.SId=a.RB_BranchId)t " + where+" order by a.Id desc").ToList();
            return pageList;
        }
        /// <summary>
        /// 获取分页最新
        /// </summary>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="dmodel"></param>
        /// <param name="count"></param>
        /// <param name="groupId"></param>
        /// <returns></returns>
        public List<RB_Attendance_Extend> GetPageListNew(int pageIndex, int pageSize, RB_Attendance_Extend dmodel, out long count, int groupId)
        {
            string where = " where 1=1 and t.RB_GroupId=" + groupId;
            if (dmodel.Status.HasValue && dmodel.Status >= 0) {
                where += $@" and t.Status={dmodel.Status}";
            }
            if (dmodel.RB_BranchId >= 0)
            {
                where += " and t.RB_BranchId=" + dmodel.RB_BranchId;
            }
            if (dmodel.IsBind.HasValue && dmodel.IsBind > 0) {
                where += " and t.IsBind=" + dmodel.IsBind;
            }
            var pageList = GetPage<RB_Attendance_Extend>(pageIndex, pageSize, out count, "SELECT * from (select a.Id,a.`Status`,a.UpdateTime,b.Id as RB_BranchId,b.RB_Group_Id as RB_GroupId,case when a.Id is NULL then 2 else 1 end AS IsBind ,e.EmName as UpdateName,b.BName as BranchName,(select count(*) from rb_employee em where em.IsLeave=0 and em.RB_Branch_id=b.Id) as PeopleNum,case when a.Id IS NOT NULL THEN (select GROUP_CONCAT(ws.Workday SEPARATOR '、') from rb_workdayseting ws where ws.AttendanceId=a.Id) ELSE '' END as AttendanceDate from  rb_branch b LEFT JOIN Rb_attendance a on b.Id=a.RB_BranchId left join rb_employee e on a.UpdateBy=e.EmployeeId )t  " + where + " order by t.IsBind,t.RB_BranchId asc").ToList();
            return pageList;
        }

        /// <summary>
        /// 验证
        /// </summary>
        /// <param name="aid"></param>
        /// <param name="groupid"></param>
        /// <param name="branchid"></param>
        /// <returns></returns>
        public bool ValidateName(int aid, int groupid, int branchid)
        {
            if (aid > 0)
            {
                var list = Get(" select * from Rb_attendance where " + nameof(RB_Attendance_Extend.Id) + "!=" + aid + " and " + nameof(RB_Attendance_Extend.RB_GroupId) + " =" + groupid + " and " + nameof(RB_Attendance_Extend.RB_BranchId) + "=" + branchid);
                if (list.Count() > 0)
                {
                    return true;
                }
            }
            else
            {
                var list = Get(" select * from Rb_attendance where " + nameof(RB_Attendance_Extend.RB_GroupId) + " =" + groupid + " and " + nameof(RB_Attendance_Extend.RB_BranchId) + "=" + branchid);
                if (list.Count() > 0)
                {
                    return true;
                }
            }
            return false;
        }

        /// <summary>
        /// 删除考勤内容
        /// </summary>
        /// <param name="aid"></param>
        /// <param name="trans">事务对象</param>
        /// <returns></returns>
        public bool DelAllList(int aid, IDbTransaction trans)
        {
            string sql = " delete from Rb_attendance_way where " + nameof(RB_Attendance_Way_Extend.Attendance_Id) + "=" + aid + ";";
            sql += " delete from Rb_technicaldates where " + nameof(RB_Technicaldates_Extend.AttendanceId) + "=" + aid + ";";
            sql += " delete from Rb_workdayseting where " + nameof(RB_WorkdaySeting_Extend.AttendanceId) + "=" + aid + ";";
            int count = Execute(sql, trans);
            return count > 0;
        }

        /// <summary>
        /// 获取考勤方式列表
        /// </summary>
        /// <param name="aid"></param>
        /// <param name="Type"></param>
        /// <returns></returns>
        public List<RB_Attendance_Way_Extend> GetAWList(int aid, int? Type)
        {
            string where = " where 1=1 and " + nameof(RB_Attendance_Way_Extend.Attendance_Id) + "=" + aid;
            if (Type.HasValue)
            {
                where += " and " + nameof(RB_Technicaldates_Extend.Type) + "=" + Type.Value;
            }
            var list = Get<RB_Attendance_Way_Extend>("select * from Rb_attendance_way " + where).ToList();
            return list;
        }
        /// <summary>
        /// 获取特殊日期
        /// </summary>
        /// <param name="aid"></param>
        /// <param name="Type"></param>
        /// <returns></returns>
        public List<RB_Technicaldates_Extend> GetTDList(int aid, int? Type)
        {
            string nowtime = DateTime.Now.ToString("yyyy-MM-dd");
            string where = " where 1=1 and " + nameof(RB_Technicaldates_Extend.Date) + ">='" + nowtime + "' and " + nameof(RB_Technicaldates_Extend.AttendanceId) + "=" + aid;
            if (Type.HasValue)
            {
                where += " and " + nameof(RB_Technicaldates_Extend.Type) + "=" + Type.Value;
            }
            var list = Get<RB_Technicaldates_Extend>("select * from Rb_technicaldates " + where).ToList();
            return list;
        }
        /// <summary>
        /// 获取工作日设置
        /// </summary>
        /// <param name="aid"></param>
        /// <returns></returns>
        public List<RB_WorkdaySeting_Extend> GetWDList(int aid)
        {
            var list = Get<RB_WorkdaySeting_Extend>("select * from Rb_workdayseting where " + nameof(RB_WorkdaySeting_Extend.AttendanceId) + "=" + aid).ToList();
            return list;
        }

        /// <summary>
        /// 获取每日打卡记录
        /// </summary>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="count"></param>
        /// <param name="StartTime"></param>
        /// <param name="EndTime"></param>
        /// <param name="eid"></param>
        /// <param name="bid"></param>
        /// <param name="DepartmentId"></param>
        /// <param name="RB_Group_id"></param>
        /// <returns></returns>
        public List<RB_Attendance_Record_Extend> GetEveryDayAttendanceRecordList(int pageIndex, int pageSize, out long count, string StartTime, string EndTime, int eid, int bid, int DepartmentId,int RB_Group_id)
        {
            string where = $@" 1=1 ";
            if (!string.IsNullOrWhiteSpace(StartTime))
            {
                where += " and a." + nameof(RB_Attendance_Record.Date) + ">='" + StartTime.Trim() + " 00:00:00" + "'";
            }
            if (!string.IsNullOrWhiteSpace(EndTime))
            {
                where += " and a." + nameof(RB_Attendance_Record.Date) + "<='" + EndTime.Trim() + " 23:59:59" + "'";
            }
            if (eid > 0)
            {
                where += " and a." + nameof(RB_Attendance_Record.EmployeeId) + "=" + eid;
            }
            if (bid >= 0)
            {
                where += " and e." + nameof(RB_Employee.RB_Branch_id) + "=" + bid;
            }
            if (RB_Group_id > 0)
            {
                where += " and e." + nameof(RB_Employee.RB_Group_id) + "=" + RB_Group_id;
            }
            if (DepartmentId > 0)
            {
                where += " and e." + nameof(RB_Employee.RB_Department_Id) + "=" + DepartmentId;
            }
            List<RB_Attendance_Record_Extend> arList = GetPage<RB_Attendance_Record_Extend>(pageIndex,pageSize,out count,$@"select e.EmName, d.DeptName as DepartmentName, p.PostName, date_format(a.Date, '%Y-%m-%d') as DateStr
, a.BeOnDutyTime, a.BeOnStatus, a.OffDutyTime, a.OffStatus,a.BeOnRelevance,a.BeOnRelevanceType,a.OffRelevance,a.OffRelevanceType
from Rb_attendance_record a 
left join rb_employee e on a.EmployeeId = e.EmployeeId 
left join rb_department d on e.RB_Department_Id = d.DeptId 
left join rb_post p on e.RB_Post_Id = p.PostId
where {where}
").ToList();
            return arList;
        }
        /// <summary>
        /// 获取原始记录
        /// </summary>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="count"></param>
        /// <param name="StartTime"></param>
        /// <param name="EndTime"></param>
        /// <param name="eid"></param>
        /// <param name="bid"></param>
        /// <param name="DepartmentId"></param>
        /// <param name="RB_Group_id"></param>
        /// <returns></returns>
        public List<RB_Attendance_Record_Extend> GetOriginalRecordList(int pageIndex,int pageSize,out long count,string StartTime, string EndTime, int eid, int bid,int DepartmentId,int RB_Group_id)
        {
            string where = $@" 1=1 and ((a.{nameof(RB_Attendance_Record.BeOnTime)}!='' and a.{nameof(RB_Attendance_Record.BeOnTime)} is not null) or (a.{nameof(RB_Attendance_Record.OffTime)}!='' and a.{nameof(RB_Attendance_Record.OffTime)} is not null))  ";
            if (!string.IsNullOrWhiteSpace(StartTime))
            {
                where += " and a." + nameof(RB_Attendance_Record.Date) + ">='" + StartTime.Trim() + " 00:00:00" + "'";
            }
            if (!string.IsNullOrWhiteSpace(EndTime))
            {
                where += " and a." + nameof(RB_Attendance_Record.Date) + "<='" + EndTime.Trim() + " 23:59:59" + "'";
            }
            if (eid > 0)
            {
                where += " and a." + nameof(RB_Attendance_Record.EmployeeId) + "=" + eid;
            }
            if (bid >= 0)
            {
                where += " and e." + nameof(RB_Employee.RB_Branch_id) + "=" + bid;
            }
            if (RB_Group_id > 0) {
                where += " and e." + nameof(RB_Employee.RB_Group_id) + "=" + RB_Group_id;
            }
            if (DepartmentId > 0)
            {
                where += " and e." + nameof(RB_Employee.RB_Department_Id) + "=" + DepartmentId;
            }
            List<RB_Attendance_Record_Extend> arList = GetPage<RB_Attendance_Record_Extend>(pageIndex,pageSize,out count,$@"select e.EmName, d.DeptName as DepartmentName, p.PostName, date_format(a.Date, '%Y-%m-%d') as DateStr
,a.*
from Rb_attendance_record a left
join rb_employee e on a.EmployeeId = e.Id left
join rb_department d on e.RB_Department_Id = d.DeptId left
join rb_post p on e.RB_Post_Id = p.PostId
where {where}
").ToList();
            return arList;
        }
        /// <summary>
        /// 获取打卡时间
        /// </summary>
        /// <param name="StartTime"></param>
        /// <param name="EndTime"></param>
        /// <param name="eid"></param>
        /// <param name="bid"></param>
        /// <param name="DepartmentId"></param>
        /// <param name="EmIdStr"></param>
        /// <param name="RB_Group_id"></param>
        /// <param name="IsLeader"></param>
        /// <returns></returns>
        public List<RB_Attendance_Record_Extend> GetClockTimeRecordList(string StartTime, string EndTime, int eid, int bid, int DepartmentId,string EmIdStr,int RB_Group_id,int IsLeader=1)
        {
            string where = $@" 1=1 and((e.IsLeave=0 and DATE_FORMAT(a.Date,'%Y-%m-%d')>=DATE_FORMAT(e.EmEntry,'%Y-%m-%d'))or (e.IsLeave=1 and DATE_FORMAT(a.Date,'%Y-%m-%d')>=DATE_FORMAT(e.EmEntry,'%Y-%m-%d') and DATE_FORMAT(a.Date,'%Y-%m-%d')<=DATE_FORMAT(e.EmLeave,'%Y-%m-%d')))";
            if (!string.IsNullOrWhiteSpace(StartTime))
            {
                where += " and a." + nameof(RB_Attendance_Record.Date) + ">='" + StartTime.Trim() + " 00:00:00" + "'";
            }
            if (!string.IsNullOrWhiteSpace(EndTime))
            {
                where += " and a." + nameof(RB_Attendance_Record.Date) + "<='" + EndTime.Trim() + " 23:59:59" + "'";
            }
            if (eid > 0)
            {
                where += " and a." + nameof(RB_Attendance_Record.EmployeeId) + "=" + eid;
            }
            else {
                if (!string.IsNullOrWhiteSpace(EmIdStr)) {
                    where += " and a." + nameof(RB_Attendance_Record.EmployeeId) + " in (" + EmIdStr+")";
                }
            }
            if (bid >= 0)
            {
                where += " and e." + nameof(RB_Employee.RB_Branch_id) + "=" + bid;
            }
            if (RB_Group_id >0)
            {
                where += " and e." + nameof(RB_Employee.RB_Group_id) + "=" + RB_Group_id;
            }
            if (DepartmentId > 0)
            {
                where += " and e." + nameof(RB_Employee.RB_Department_Id) + "=" + DepartmentId;
            }
            List<RB_Attendance_Record_Extend> arList = Get<RB_Attendance_Record_Extend>($@"select e.EmName, date_format(a.Date, '%Y-%m-%d') as DateStr
,a.*
from rb_employee e 
left join Rb_attendance_record a on a.EmployeeId = e.EmployeeId 
where {where}
").ToList();
            return arList;
        }
        /// <summary>
        /// 获取打卡时间内的所有用户
        /// </summary>
        /// <param name="StartTime"></param>
        /// <param name="EndTime"></param>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="count"></param>
        /// <param name="eid"></param>
        /// <param name="bid"></param>
        /// <param name="DepartmentId"></param>
        /// <param name="RB_Group_id"></param>
        /// <returns></returns>
        public List<RB_Employee_Extend> GetClockTimeEmployeeList(string StartTime, string EndTime, int pageIndex, int pageSize, out long count, int eid, int bid, int DepartmentId,int RB_Group_id)
        {
            string where = $@" 1=1 and( (e.IsLeave=0 and e.EmEntry<='{EndTime.Trim() + " 23:59:59"}')or (e.IsLeave=1 and e.EmLeave>='{StartTime.Trim() + " 00:00:00"}' and e.EmEntry<='{EndTime.Trim() + " 23:59:59"}'))";
            if (eid > 0)
            {
                where += " and e." + nameof(RB_Employee.EmployeeId) + "=" + eid;
            }
            if (bid >= 0)
            {
                where += " and e." + nameof(RB_Employee.RB_Branch_id) + "=" + bid;
            }
            if (RB_Group_id > 0)
            {
                where += " and e." + nameof(RB_Employee.RB_Group_id) + "=" + RB_Group_id;
            }
            if (DepartmentId > 0)
            {
                where += " and e." + nameof(RB_Employee.RB_Department_Id) + "=" + DepartmentId;
            }
            List<RB_Employee_Extend> arList = GetPage<RB_Employee_Extend>(pageIndex, pageSize, out count, $@"select e.EmployeeId,e.EmName, d.DeptName as DepartmentName, p.PostName
from rb_employee e left
join rb_department d on e.RB_Department_Id = d.DeptId left
join rb_post p on e.RB_Post_Id = p.PostId
where {where}
").ToList();
            return arList;
        }

        /// <summary>
        /// 获取每月打卡统计
        /// </summary>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="count"></param>
        /// <param name="StartTime"></param>
        /// <param name="EndTime"></param>
        /// <param name="eid"></param>
        /// <param name="bid"></param>
        /// <param name="DepartmentId"></param>
        /// <param name="RB_Group_id"></param>
        /// <returns></returns>
        public List<RB_Attendance_Record_Extend> GetMonthRecordList(int pageIndex, int pageSize, out long count, string StartTime, string EndTime, int eid, int bid,int DepartmentId,int RB_Group_id)
        {
            string where = $@" 1=1 ";
            if (!string.IsNullOrWhiteSpace(StartTime))
            {
                where += " and a." + nameof(RB_Attendance_Record.Date) + ">='" + StartTime.Trim() + " 00:00:00" + "'";
            }
            if (!string.IsNullOrWhiteSpace(EndTime))
            {
                where += " and a." + nameof(RB_Attendance_Record.Date) + "<='" + EndTime.Trim() + " 23:59:59" + "'";
            }
            if (eid > 0)
            {
                where += " and a." + nameof(RB_Attendance_Record.EmployeeId) + "=" + eid;
            }
            if (bid >= 0)
            {
                where += " and e." + nameof(RB_Employee.RB_Branch_id) + "=" + bid;
            }
            if (RB_Group_id > 0)
            {
                where += " and e." + nameof(RB_Employee.RB_Group_id) + "=" + RB_Group_id;
            }
            if (DepartmentId > 0)
            {
                where += " and e." + nameof(RB_Employee.RB_Department_Id) + "=" + DepartmentId;
            }
            TimeSpan ts = DateTime.Parse(EndTime) - DateTime.Parse(StartTime);
            int TotalDay = ts.Days+1;
            string sql = $@"
select t.EmployeeId,t.EmName,t.EmEntry,t.EmLeave,IFNULL(t.IsLeave,0) as IsLeave,t.DepartmentName,t.PostName
,IFNULL(t.AnnualLeave,0) as AnnualLeaveSum
,IFNULL(t.AffairLeave,0) as AffairLeaveSum
,IFNULL(t.SickLeave,0) as SickLeaveSum
,count(*) as CQDay

,case when t.IsLeave=0 then
          case when t.EmEntry>='{StartTime.Trim() + " 00:00:00"}' then
            DATEDIFF('{Convert.ToDateTime(EndTime).AddDays(1).ToString("yyyy-MM-dd")}',t.EmEntry)-count(*)
          else 
            {TotalDay}-count(*)
          end
else 
          case when t.EmLeave<='{EndTime.Trim() + " 23:59:59"}' and t.EmEntry<='{StartTime.Trim() + " 00:00:00"}' then
            DATEDIFF(t.EmLeave,'{Convert.ToDateTime(StartTime).AddDays(-1).ToString("yyyy-MM-dd")}')-count(*)
          else  case when t.EmLeave<='{EndTime.Trim() + " 23:59:59"}' and t.EmEntry>'{StartTime.Trim() + " 00:00:00"}' then
                DATEDIFF(t.EmLeave,t.EmEntry)+1-count(*)
                else
                    case when t.EmLeave>'{EndTime.Trim() + " 23:59:59"}' and t.EmEntry>'{StartTime.Trim() + " 00:00:00"}' then
						DATEDIFF('{Convert.ToDateTime(EndTime).AddDays(1).ToString("yyyy-MM-dd")}',t.EmEntry)-count(*)
					else
                        {TotalDay}-count(*)
                    end
                end
          end
end as XXDay
,sum(case when t.BeOnStatus={(int)AttendanceEnum.QJ} and t.OffStatus={(int)AttendanceEnum.QJ} then 1 else 
        case when t.BeOnStatus={(int)AttendanceEnum.QJ} or t.OffStatus={(int)AttendanceEnum.QJ} then 0.5 else 0 end
    END) as QJDay
,SUM(case when t.BeOnStatus={(int)AttendanceEnum.CD} then 1 else 0 END) as CDNum
,SUM(case when t.BeOnStatus={(int)AttendanceEnum.CD} then t.BeLateTime else 0 end) as CDTime
,sum(case when t.BeOnStatus={(int)AttendanceEnum.QK} and t.OffStatus!={(int)AttendanceEnum.QK} then 1 else 0 END) as SBQK
,sum(case when t.OffStatus={(int)AttendanceEnum.QK} and t.BeOnStatus!={(int)AttendanceEnum.QK} then 1 else 0 END) as XBQK
,sum(case when t.BeOnStatus={(int)AttendanceEnum.QK} and t.OffStatus={(int)AttendanceEnum.QK} then 1 else 0 end) as QTKG
from (
SELECT e.EmployeeId,e.EmName,e.EmEntry,e.EmLeave,IFNULL(e.IsLeave,0) as IsLeave,d.DeptName as DepartmentName,p.PostName
,IFNULL(LT.AnnualLeave,0) as AnnualLeave
,IFNULL(LT.AffairLeave,0) as AffairLeave
,IFNULL(LT.SickLeave,0) as SickLeave
,a.BeOnStatus,a.OffStatus,a.BeLateTime
,case when ((e.IsLeave=0 and DATE_FORMAT(a.Date,'%Y-%m-%d')>=DATE_FORMAT(e.EmEntry,'%Y-%m-%d') and e.EmEntry<='{EndTime.Trim() + " 23:59:59"}')or (e.IsLeave=1 and DATE_FORMAT(a.Date,'%Y-%m-%d')>=DATE_FORMAT(e.EmEntry,'%Y-%m-%d') and DATE_FORMAT(a.Date,'%Y-%m-%d')<=DATE_FORMAT(e.EmLeave,'%Y-%m-%d') and e.EmLeave>='{StartTime.Trim() + " 00:00:00"}' and e.EmEntry<='{EndTime.Trim() + " 23:59:59"}'))
then 1 else 0 end as RecordStatus

from rb_employee e left join  Rb_attendance_record a on a.EmployeeId=e.EmployeeId left join rb_department d on e.RB_Department_Id=d.DeptID left join rb_post p on e.RB_Post_Id=p.PostId
left join (SELECT EmployeeId,AskforleaveMonth,sum(case when AskforleaveType=1 THEN Duration else 0 end) as AnnualLeave,
sum(case when AskforleaveType=2 THEN Duration else 0 end) as AffairLeave,
sum(case when AskforleaveType=3 THEN Duration else 0 end) as SickLeave
from rb_attendance_leavetime WHERE AskforleaveMonth='{Convert.ToDateTime(StartTime).ToString("yyyy-MM")}' GROUP BY EmployeeId,AskforleaveMonth) LT on LT.EmployeeId=e.EmployeeId
where  {where}
)t where t.RecordStatus=1
GROUP BY t.EmployeeId,t.EmName,t.EmEntry,t.EmLeave,t.IsLeave,t.DepartmentName,t.PostName,t.AnnualLeave,t.AffairLeave,t.SickLeave
";
            List<RB_Attendance_Record_Extend> arList = GetPage<RB_Attendance_Record_Extend>(pageIndex, pageSize, out count,sql).ToList();
            return arList;
        }

        /// <summary>
        /// 四川和平专用  统计每月打卡
        /// </summary>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="count"></param>
        /// <param name="StartTime"></param>
        /// <param name="EndTime"></param>
        /// <param name="eid"></param>
        /// <param name="bid"></param>
        /// <param name="DepartmentId"></param>
        /// <param name="RB_Group_id"></param>
        /// <param name="TimeOneSplit"></param>
        /// <returns></returns>
        public List<RB_Attendance_Record_Extend> GetMonthRecordListForHPZY(int pageIndex, int pageSize, out long count, string StartTime, string EndTime, int eid, int bid, int DepartmentId, int RB_Group_id,string TimeOneSplit)
        {
            string where = $@" 1=1 ";
            if (!string.IsNullOrWhiteSpace(StartTime))
            {
                where += " and a." + nameof(RB_Attendance_Record.Date) + ">='" + StartTime.Trim() + " 00:00:00" + "'";
            }
            if (!string.IsNullOrWhiteSpace(EndTime))
            {
                where += " and a." + nameof(RB_Attendance_Record.Date) + "<='" + EndTime.Trim() + " 23:59:59" + "'";
            }
            if (eid > 0)
            {
                where += " and a." + nameof(RB_Attendance_Record.EmployeeId) + "=" + eid;
            }
            if (bid >= 0)
            {
                where += " and e." + nameof(RB_Employee.RB_Branch_id) + "=" + bid;
            }
            if (RB_Group_id > 0)
            {
                where += " and e." + nameof(RB_Employee.RB_Group_id) + "=" + RB_Group_id;
            }
            if (DepartmentId > 0)
            {
                where += " and e." + nameof(RB_Employee.RB_Department_Id) + "=" + DepartmentId;
            }
           
            string sql = $@"
select t.EmployeeId,t.EmName,t.EmEntry,t.EmLeave,IFNULL(t.IsLeave,0) as IsLeave,t.DepartmentName,t.AnnualLeaveDay
,count(*) as CQDay
,sum(case when t.BeOnStatus={(int)AttendanceEnum.QJ} and t.OffStatus={(int)AttendanceEnum.QJ} then 1 else 
        case when t.BeOnStatus={(int)AttendanceEnum.QJ} or t.OffStatus={(int)AttendanceEnum.QJ} then 0.5 else 0 end
    END) as QJDay
,sum((case when t.BeOnStatus=5 AND t.BeOnAskType=1 THEN 0.5 ELSE 0 END)+(case when t.OffStatus=5 AND t.OffAskType=1 THEN 0.5 ELSE 0 END)) as NJDay
,sum((case when t.BeOnStatus=5 AND t.BeOnAskType=2 THEN 0.5 ELSE 0 END)+(case when t.OffStatus=5 AND t.OffAskType=2 THEN 0.5 ELSE 0 END)) as SJDay
,sum((case when t.BeOnStatus=5 AND t.BeOnAskType=3 THEN 0.5 ELSE 0 END)+(case when t.OffStatus=5 AND t.OffAskType=3 THEN 0.5 ELSE 0 END)) as BJDay
,sum((case when t.BeOnStatus=5 AND t.BeOnAskType=6 THEN 0.5 ELSE 0 END)+(case when t.OffStatus=5 AND t.OffAskType=6 THEN 0.5 ELSE 0 END)) as HJDay
,SUM(case when t.BeOnStatus=2 then CASE WHEN t.BeLateTime<={TimeOneSplit} THEN 1 ELSE 0 END else 0 END) as OneCDNum
,SUM(case when t.BeOnStatus=2 then CASE WHEN t.BeLateTime>{TimeOneSplit} THEN 1 ELSE 0 END else 0 END) as TwoCDNum
,sum(case when t.BeOnStatus=0 and t.OffStatus=0 then 1 else 0 end) as QTKG
from (
SELECT e.EmployeeId,e.EmName,e.EmEntry,e.EmLeave,a.Date,IFNULL(e.IsLeave,0) as IsLeave,d.DeptSort as Sort,d.DeptId as DepartmentID,d.DeptName as DepartmentName,e.AnnualLeaveDay as AnnualLeaveDay
,a.BeOnStatus,a.OffStatus,a.BeLateTime,a.BeOnRelevance,a.BeOnRelevanceType,a.OffRelevance,a.OffRelevanceType
,case WHEN a.BeOnStatus=5 AND a.BeOnRelevanceType=1 THEN (SELECT AskforleaveType FROM Rb_Workflow_Askforleave ask WHERE ask.Id=a.BeOnRelevance) ELSE 0 END AS BeOnAskType
,case WHEN a.OffStatus=5 AND a.OffRelevanceType=1 THEN (SELECT AskforleaveType FROM Rb_Workflow_Askforleave ask WHERE ask.Id=a.OffRelevance) ELSE 0 END AS OffAskType
,case when ((e.IsLeave=0 AND e.EmEntry IS NULL) OR (e.IsLeave=0 and DATE_FORMAT(a.Date,'%Y-%m-%d')>=DATE_FORMAT(e.EmEntry,'%Y-%m-%d') and e.EmEntry<='{EndTime.Trim() + " 23:59:59"}')or (e.IsLeave=1 AND DATE_FORMAT(a.Date,'%Y-%m-%d')<=DATE_FORMAT(e.EmLeave,'%Y-%m-%d') and e.EmLeave>='{StartTime.Trim() + " 00:00:00"}'))
then 1 else 0 end as RecordStatus
from rb_employee e left join  Rb_attendance_record a on a.EmployeeId=e.EmployeeId left join rb_department d on e.RB_Department_Id=d.DeptID 
where {where}
ORDER BY d.DeptSort asc,d.DeptId asc,e.EmName DESC
)t where t.RecordStatus=1
GROUP BY t.EmployeeId,t.EmName,t.EmEntry,t.EmLeave,t.IsLeave,t.DepartmentName
ORDER BY t.Sort asc,t.DepartmentID asc,t.EmName DESC
";
            List<RB_Attendance_Record_Extend> arList = GetPage<RB_Attendance_Record_Extend>(pageIndex, pageSize, out count, sql).ToList();
            return arList;
        }

        /// <summary>
        /// 获取每日打卡记录
        /// </summary>
        /// <param name="StartTime"></param>
        /// <param name="EndTime"></param>
        /// <param name="eid"></param>
        /// <param name="status"></param>
        /// <returns></returns>
        public List<RB_Attendance_Record_Extend> GetRecordForStatusList(string StartTime, string EndTime, int eid, int status)
        {
            string where = $@" 1=1 ";
            switch (status)
            {
                case 1://迟到次数
                    where += " and a." + nameof(RB_Attendance_Record.BeOnStatus) + "=" + (int)AttendanceEnum.CD;
                    break;
                case 2://上班缺卡
                    where += " and a." + nameof(RB_Attendance_Record.BeOnStatus) + "=" + (int)AttendanceEnum.QK;
                    where += " and a." + nameof(RB_Attendance_Record.OffStatus) + "!=" + (int)AttendanceEnum.QK;
                    break;
                case 3://下班缺卡
                    where += " and a." + nameof(RB_Attendance_Record.OffStatus) + "=" + (int)AttendanceEnum.QK;
                    where += " and a." + nameof(RB_Attendance_Record.BeOnStatus) + "!=" + (int)AttendanceEnum.QK;
                    break;
                case 4://旷工
                    where += " and a." + nameof(RB_Attendance_Record.BeOnStatus) + "=" + (int)AttendanceEnum.QK;
                    where += " and a." + nameof(RB_Attendance_Record.OffStatus) + "=" + (int)AttendanceEnum.QK;
                    break;
            }
            if (!string.IsNullOrWhiteSpace(StartTime))
            {
                where += " and a." + nameof(RB_Attendance_Record.Date) + ">='" + StartTime.Trim() + " 00:00:00" + "'";
            }
            if (!string.IsNullOrWhiteSpace(EndTime))
            {
                where += " and a." + nameof(RB_Attendance_Record.Date) + "<='" + EndTime.Trim() + " 23:59:59" + "'";
            }
            if (eid > 0)
            {
                where += " and a." + nameof(RB_Attendance_Record.EmployeeId) + "=" + eid;
            }

            List<RB_Attendance_Record_Extend> arList = Get<RB_Attendance_Record_Extend>($@"select a.Id,a.EmployeeId, date_format(a.Date, '%Y-%m-%d') as DateStr
, a.BeOnDutyTime, a.BeOnStatus, a.OffDutyTime, a.OffStatus
from Rb_attendance_record a
left join rb_employee e on a.EmployeeId=e.EmployeeId 
where {where}
").ToList();
            return arList;
        }

        /// <summary>
        /// 根据所在集团公司  获取是否开启考勤
        /// </summary>
        /// <param name="groupId"></param>
        /// <param name="branchId"></param>
        /// <returns></returns>
        public int IsAttendanceForBranch(int groupId, int branchId)
        {
            string where = " where 1=1 and Status=0";
            if (groupId > 0)
            {
                where += " and " + nameof(RB_Attendance.RB_GroupId) + "=" + groupId;
            }
            if (branchId > 0)
            {
                where += " and " + nameof(RB_Attendance.RB_BranchId) + "=" + branchId;
            }
            List<RB_Attendance_Extend> list = Get<RB_Attendance_Extend>($@" select * from RB_Attendance {where}").ToList();
            if (list.Count > 0)
            {
                var model = list[0];
                return model.Id;
            }
            return 0;
        }

        /// <summary>
        /// 获取考勤列表
        /// </summary>
        /// <param name="groupId"></param>
        /// <param name="branchId"></param>
        /// <returns></returns>
        public List<RB_Attendance_Extend> GetList(int groupId, int branchId = -1) {
            string where = " where 1=1 and Status=0";
            if (groupId > 0)
            {
                where += " and " + nameof(RB_Attendance.RB_GroupId) + "=" + groupId;
            }
            if (branchId > 0)
            {
                where += " and " + nameof(RB_Attendance.RB_BranchId) + "=" + branchId;
            }
            List<RB_Attendance_Extend> list = Get<RB_Attendance_Extend>($@" select * from RB_Attendance {where}").ToList();
            return list;
        }

        /// <summary>
        /// 根据考勤id获取考勤方式
        /// </summary>
        /// <param name="aid"></param>
        /// <returns></returns>
        public List<RB_Attendance_Way_Extend> GetAttendanceWay(int aid)
        {
            var list = Get<RB_Attendance_Way_Extend>($@"select * from RB_Attendance_Way where Attendance_Id={aid}").ToList();
            return list;
        }

        /// <summary>
        /// 根据员工id获取考勤信息
        /// </summary>
        /// <param name="empId">员工id</param>
        /// <returns></returns>
        public RB_Attendance_Extend GetAttendanceByEmpId(int empId)
        {
            var list = Get<RB_Attendance_Extend>($@"SELECT * from rb_attendance a where a.RB_BranchId = (
                SELECT e.RB_Branch_id from rb_employee e where e.EmployeeId =  {empId}
                )").FirstOrDefault();
            return list;
        }

    }
}
