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_attendance_recordRepository : BaseRepository<RB_Attendance_Record>
    {
        /// <summary>
        /// 获取打卡信息
        /// </summary>
        /// <param name="empId">员工id</param>
        /// <param name="date">打卡日期</param>
        /// <returns></returns>
        public RB_Attendance_Record_Extend GetAttendRecod(int empId, string date)
        {
            return Get<RB_Attendance_Record_Extend>($"SELECT * from rb_attendance_record r where r.EmployeeId = {empId} AND DATE(r.Date)  = '{date}'").ToList().FirstOrDefault();
        }

        /// <summary>
        /// 根据手机标识码获取打卡信息
        /// </summary>
        /// <param name="phoneId">手机标识码</param>
        /// <param name="date">打卡日期</param>
        /// <returns></returns>
        public RB_Attendance_Record_Extend GetAttendRecodByPhoneId(string phoneId, string date)
        {
            return Get<RB_Attendance_Record_Extend>($"SELECT * from rb_attendance_record r where r.PhoneId = '{phoneId}' AND DATE(r.Date)  = '{date}'").ToList().FirstOrDefault();
        }

        /// <summary>
        /// 获取员工当月打卡记录
        /// </summary>
        /// <param name="empId">员工id</param>
        /// <param name="date">打卡日期</param>
        /// <returns></returns>
        public List<RB_Attendance_Record_Extend> PunchCardStatistical(int empId, string date)
        {
            return Get<RB_Attendance_Record_Extend>($"SELECT * from rb_attendance_record r where r.EmployeeId = {empId} and DATE_FORMAT(r.Date,'%Y-%m') = DATE_FORMAT('{date}','%Y-%m')").OrderBy(t=>t.Date).ToList();
        }

        /// <summary>
        /// 获取时间段里的打卡记录
        /// </summary>
        /// <param name="StartTime"></param>
        /// <param name="EndTime"></param>
        /// <param name="eid"></param>
        /// <returns></returns>
        public List<RB_Attendance_Record> GetAttendanceRecordForTimeList(string StartTime, string EndTime, int eid)
        {
            string where = " 1=1";
            if (!string.IsNullOrWhiteSpace(StartTime))
            {
                where += " and " + nameof(RB_Attendance_Record.Date) + ">='" + StartTime.Trim() + " 00:00:00" + "'";
            }
            if (!string.IsNullOrWhiteSpace(EndTime))
            {
                where += " and " + nameof(RB_Attendance_Record.Date) + "<='" + EndTime.Trim() + " 23:59:59" + "'";
            }
            if (eid > 0)
            {
                where += " and " + nameof(RB_Attendance_Record.EmployeeId) + "=" + eid;
            }
            List<RB_Attendance_Record> arList = Get<RB_Attendance_Record>($@"select * from Rb_attendance_record  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="BranchId"></param>
        /// <param name="employeeId"></param>
        /// <param name="isLeave"></param>
        /// <param name="rB_Group_id"></param>
        /// <returns></returns>
        public List<RB_Attendance_Record_Extend> GetEmployeeOvertimeHours(int pageIndex, int pageSize, out long count, string startTime, string endTime,int BranchId, int employeeId, int isLeave, int rB_Group_id)
        {
            string where = " 1=1";
            if (rB_Group_id > 0) {
                where += " and e." + nameof(RB_Employee.RB_Group_id) + "=" + rB_Group_id;
            }
            if (!string.IsNullOrEmpty(startTime))
            {
                where += " and r." + nameof(RB_Attendance_Record.Date) + ">='" + startTime + " 00:00:00" + "'";
            }
            if (!string.IsNullOrEmpty(endTime))
            {
                where += " and r." + nameof(RB_Attendance_Record.Date) + "<='" + endTime + " 23:59:59" + "'";
            }
            if (BranchId >= 0)
            {
                where += " and e." + nameof(RB_Employee.RB_Branch_id) + "=" + BranchId;
            }
            if (employeeId > 0)
            {
                where += " and r." + nameof(RB_Attendance_Record.EmployeeId) + "=" + employeeId;
            }
            if (isLeave >= 0)
            {
                where += " and e." + nameof(RB_Employee.IsLeave) + "=" + isLeave;
            }
            string sql = $@"SELECT r.EmployeeId,e.EmName,e.IsLeave,r.Date,r.OffStatus,r.OffDutyTime,TIMESTAMPDIFF(MINUTE,'2019-01-01 19:00',CONCAT('2019-01-01 ',DATE_FORMAT(r.OffDutyTime, '%H:%i'))) AS CQDay FROM rb_attendance_record r
LEFT JOIN rb_employee e on r.EmployeeId=e.EmployeeId
WHERE r.OffStatus=1 AND {where} and TIMESTAMPDIFF(MINUTE,'2019-01-01 19:00',CONCAT('2019-01-01 ',DATE_FORMAT(r.OffDutyTime, '%H:%i')))>0
ORDER BY r.Date desc";
            return GetPage<RB_Attendance_Record_Extend>(pageIndex, pageSize, out count, sql).ToList();
        }

        /// <summary>
        /// 加班总时长
        /// </summary>
        /// <param name="startTime"></param>
        /// <param name="endTime"></param>
        /// <param name="BranchId"></param>
        /// <param name="employeeId"></param>
        /// <param name="isLeave"></param>
        /// <param name="rB_Group_id"></param>
        /// <returns></returns>
        public int GetEmployeeOvertimeTotalHours(string startTime, string endTime,int BranchId, int employeeId, int isLeave, int rB_Group_id)
        {
            string where = " 1=1";
            if (rB_Group_id > 0)
            {
                where += " and e." + nameof(RB_Employee.RB_Group_id) + "=" + rB_Group_id;
            }
            if (!string.IsNullOrEmpty(startTime))
            {
                where += " and r." + nameof(RB_Attendance_Record.Date) + ">='" + startTime + " 00:00:00" + "'";
            }
            if (!string.IsNullOrEmpty(endTime))
            {
                where += " and r." + nameof(RB_Attendance_Record.Date) + "<='" + endTime + " 23:59:59" + "'";
            }
            if (employeeId > 0)
            {
                where += " and r." + nameof(RB_Attendance_Record.EmployeeId) + "=" + employeeId;
            }
            if (BranchId >= 0)
            {
                where += " and e." + nameof(RB_Employee.RB_Branch_id) + "=" + BranchId;
            }
            if (isLeave >= 0)
            {
                where += " and e." + nameof(RB_Employee.IsLeave) + "=" + isLeave;
            }
            string sql = $@"
SELECT SUM(TIMESTAMPDIFF(MINUTE,'2019-01-01 19:00',CONCAT('2019-01-01 ',DATE_FORMAT(r.OffDutyTime, '%H:%i')))) AS CQDay FROM rb_attendance_record r
LEFT JOIN rb_employee e on r.EmployeeId=e.EmployeeId
WHERE r.OffStatus=1 AND {where} and TIMESTAMPDIFF(MINUTE,'2019-01-01 19:00',CONCAT('2019-01-01 ',DATE_FORMAT(r.OffDutyTime, '%H:%i')))>0";
            var model = Get<RB_Attendance_Record_Extend>(sql).FirstOrDefault();
            if (model != null)
            {
                return model.CQDay;
            }
            else {
                return 0;
            }
        }
    }
}
