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

namespace Edu.Repository.User
{
    /// <summary>
    /// 工作流程审核记录表
    /// </summary>
    public partial class Rb_Workflow_AuditrecordRepository : BaseRepository<Rb_Workflow_Auditrecord>
    {
        /// <summary>
        /// 我的审批
        /// </summary>
        /// <param name="searchKey">搜索关键字</param>
        /// <param name="empId">员工id</param>
        /// <param name="appAuditStatus">app获取审核状态</param>
        /// <param name="auditType">审核类型 1待我审批，2我已审批的</param>
        /// <param name="applyType">申请筛选模板类型</param>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="rowCount">总条数</param>
        /// <returns></returns>
        public List<Rb_Workflow_Auditrecord_Extend> MyAuditPageList(string searchKey, int empId, AppAuditStatusEnum appAuditStatus, int auditType, int applyType, int pageIndex, int pageSize, out long rowCount)
        {

            string sql = $@"SELECT t.WorkFlowId,t.AuditStatus,t.TemplateType from (
        (SELECT a.*,'请假' as TemplateName,e.EmName,ask.CreateTime as AuditCreateTime,ask.`Status` from rb_workflow_auditrecord a INNER  JOIN rb_workflow_askforleave ask 
        ON a.WorkFlowId =  ask.Id and a.TemplateType = 1 and a.AuditEmId = {empId}
        INNER JOIN rb_employee e ON e.EmployeeId = ask.CreateBy)UNION
        (SELECT a.*,'出差' as TemplateName,e.EmName,ask.CreateTime as AuditCreateTime,ask.`Status` from rb_workflow_auditrecord a INNER  JOIN rb_workflow_evection ask 
        ON a.WorkFlowId =  ask.Id and a.TemplateType = 2 and a.AuditEmId = {empId}
        INNER JOIN rb_employee e ON e.EmployeeId = ask.CreateBy)UNION
        (SELECT a.*,'外出' as TemplateName,e.EmName,ask.CreateTime as AuditCreateTime,ask.`Status` from rb_workflow_auditrecord a INNER  JOIN rb_workflow_goout ask 
        ON a.WorkFlowId =  ask.Id and a.TemplateType = 3 and a.AuditEmId = {empId}
        INNER JOIN rb_employee e ON e.EmployeeId = ask.CreateBy)UNION
        (SELECT a.*,'补卡' as TemplateName,e.EmName,ask.CreateTime as AuditCreateTime,ask.`Status` from rb_workflow_auditrecord a INNER  JOIN rb_workflow_reissuecard ask 
        ON a.WorkFlowId =  ask.Id and a.TemplateType = 4 and a.AuditEmId = {empId}
        INNER JOIN rb_employee e ON e.EmployeeId = ask.CreateBy)
        ) as t";

            StringBuilder sb = new StringBuilder();
            sb.Append($" where AuditEmId = {empId}");

            if (!string.IsNullOrWhiteSpace(searchKey))
            {
                sb.Append($" AND (TemplateName LIKE '%{searchKey}%' or EmName LIKE '%{searchKey}%')");
            }
            else
            {
                if (auditType == 1)
                {
                    sb.Append($" AND AuditStatus={(int)WFRrocessStatus.NotAudit}");
                }
                else if(auditType == 2)
                {
                    sb.Append($" AND AuditStatus !={(int)WFRrocessStatus.NotAudit} AND AuditStatus !={(int)WFRrocessStatus.OtherHaveAudit}");
                    if (appAuditStatus != AppAuditStatusEnum.All)
                    {
                        sb.Append($" AND AuditStatus !={(int)WFRrocessStatus.NotAudit} AND AuditStatus !={(int)WFRrocessStatus.OtherHaveAudit}");
                    }
                }
                if (applyType != 0)
                {
                    sb.Append($" AND TemplateType={applyType}");
                }
                if (appAuditStatus == AppAuditStatusEnum.AuditComplete)
                {
                    sb.Append($" AND (`Status`={(int)UserWFAuditStatus.Through} Or `Status`={(int)UserWFAuditStatus.NotThrough})");
                }
                else if (appAuditStatus == AppAuditStatusEnum.InComplete)
                {
                    sb.Append($" AND `Status`={(int)UserWFAuditStatus.InReview}");
                }
                else if (appAuditStatus == AppAuditStatusEnum.ToWithdraw)
                {
                    sb.Append($" AND `Status`={(int)UserWFAuditStatus.ToWithdraw}");
                }
            }
            sb.Append($" ORDER BY AuditCreateTime DESC");
            return GetPage<Rb_Workflow_Auditrecord_Extend>(pageIndex, pageSize, out rowCount, $"{sql} {sb.ToString()}").ToList();
        }

        /// <summary>
        /// 获取待我审批的数量
        /// </summary>
        /// <param name="empId"></param>
        /// <param name="appAuditStatus"></param>
        /// <param name="auditType"></param>
        /// <returns></returns>
        public int MyAuditCount(int empId, AppAuditStatusEnum appAuditStatus, int auditType) {
            string sql = $@"SELECT count(*) as CountNum from (
        (SELECT a.*,'请假' as TemplateName,e.EmName,ask.CreateTime as AuditCreateTime,ask.`Status` from rb_workflow_auditrecord a INNER  JOIN rb_workflow_askforleave ask 
        ON a.WorkFlowId =  ask.Id and a.TemplateType = 1 and a.AuditEmId = {empId}
        INNER JOIN rb_employee e ON e.EmployeeId = ask.CreateBy)UNION
        (SELECT a.*,'出差' as TemplateName,e.EmName,ask.CreateTime as AuditCreateTime,ask.`Status` from rb_workflow_auditrecord a INNER  JOIN rb_workflow_evection ask 
        ON a.WorkFlowId =  ask.Id and a.TemplateType = 2 and a.AuditEmId = {empId}
        INNER JOIN rb_employee e ON e.EmployeeId = ask.CreateBy)UNION
        (SELECT a.*,'外出' as TemplateName,e.EmName,ask.CreateTime as AuditCreateTime,ask.`Status` from rb_workflow_auditrecord a INNER  JOIN rb_workflow_goout ask 
        ON a.WorkFlowId =  ask.Id and a.TemplateType = 3 and a.AuditEmId = {empId}
        INNER JOIN rb_employee e ON e.EmployeeId = ask.CreateBy)UNION
        (SELECT a.*,'补卡' as TemplateName,e.EmName,ask.CreateTime as AuditCreateTime,ask.`Status` from rb_workflow_auditrecord a INNER  JOIN rb_workflow_reissuecard ask 
        ON a.WorkFlowId =  ask.Id and a.TemplateType = 4 and a.AuditEmId = {empId}
        INNER JOIN rb_employee e ON e.EmployeeId = ask.CreateBy)
        ) as t";

            StringBuilder sb = new StringBuilder();
            sb.Append($" where AuditEmId = {empId}");

            if (auditType == 1)
            {
                sb.Append($" AND AuditStatus={(int)WFRrocessStatus.NotAudit}");
            }
            else if (auditType == 2)
            {
                sb.Append($" AND AuditStatus !={(int)WFRrocessStatus.NotAudit} AND AuditStatus !={(int)WFRrocessStatus.OtherHaveAudit}");
                if (appAuditStatus != AppAuditStatusEnum.All)
                {
                    sb.Append($" AND AuditStatus !={(int)WFRrocessStatus.NotAudit} AND AuditStatus !={(int)WFRrocessStatus.OtherHaveAudit}");
                }
            }
            if (appAuditStatus == AppAuditStatusEnum.AuditComplete)
            {
                sb.Append($" AND (`Status`={(int)UserWFAuditStatus.Through} Or `Status`={(int)UserWFAuditStatus.NotThrough})");
            }
            else if (appAuditStatus == AppAuditStatusEnum.InComplete)
            {
                sb.Append($" AND `Status`={(int)UserWFAuditStatus.InReview}");
            }
            else if (appAuditStatus == AppAuditStatusEnum.ToWithdraw)
            {
                sb.Append($" AND `Status`={(int)UserWFAuditStatus.ToWithdraw}");
            }
            
            sb.Append($" ORDER BY AuditCreateTime DESC");
            var obj= ExecuteScalar($"{sql} {sb.ToString()}");
            if (obj != null)
            {
                return Convert.ToInt32(obj);
            }
            else {
                return 0;
            }
        }

        /// <summary>
        /// 根据工作流id 和 模版类型获取审核详情
        /// </summary>
        /// <param name="WorkFlowId"></param>
        /// <param name="TemplateType"></param>
        /// <returns></returns>
        public List<Rb_Workflow_Auditrecord_Extend> GetAuditRecordList(int WorkFlowId, int TemplateType)
        {
            string where = $@" where 1=1 ";
            if (WorkFlowId > 0)
            {
                where += $@" and ar.{nameof(Rb_Workflow_AuditRelevance.WorkFlowId) }={WorkFlowId}";
            }
            if (TemplateType > 0)
            {
                where += $@" and ar.{nameof(Rb_Workflow_AuditRelevance.TemplateType)}={TemplateType}";
            }
            string sql = $@"SELECT e.EmName as AuditEmName,e.EmLoginMobile,e.EmAccount as AuditEmAccout,e.EmPhoto as AuditEmPhoto,ar.* from rb_workflow_auditrecord ar left join rb_employee e on e.EmployeeId=ar.AuditEmId {where}";
            return Get<Rb_Workflow_Auditrecord_Extend>(sql).ToList();
        }
        /// <summary>
        /// 根据工作流id 审核id 和 模版类型获取审核详情
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public List<Rb_Workflow_Auditrecord_Extend> GetFinaceAuditRecordList(Rb_Workflow_Auditrecord_Extend model)
        {
            string where = $@" where 1=1 ";
            if (model.Id > 0)
            {
                where += $@" and ar.{nameof(Rb_Workflow_Auditrecord.Id) }={model.Id}";
            }
            if (model.WorkFlowId > 0)
            {
                where += $@" and ar.{nameof(Rb_Workflow_Auditrecord.WorkFlowId) }={model.WorkFlowId}";
            }
            if (model.TemplateType > 0)
            {
                where += $@" and ar.{nameof(Rb_Workflow_Auditrecord.TemplateType)}={(int)model.TemplateType}";
            }
            if (model.AuditId > 0)
            {
                where += $@" and ar.{nameof(Rb_Workflow_Auditrecord.AuditId)}={model.AuditId}";
            }
            if (model.AuditEmId > 0)
            {
                where += $@" and ar.{nameof(Rb_Workflow_Auditrecord.AuditEmId)}={model.AuditEmId}";
            }
            if (model.AuditStatus > 0)
            {
                where += $@" and ar.{nameof(Rb_Workflow_Auditrecord.AuditStatus)}={(int)model.AuditStatus}";
            }
            string sql = $@"SELECT e.EmName as AuditEmName,e.EmAccount as AuditEmAccout,e.EmPhoto as AuditEmPhoto,ar.* from rb_workflow_auditrecord ar left join rb_employee e on e.EmployeeId=ar.AuditEmId {where}";
            return Get<Rb_Workflow_Auditrecord_Extend>(sql).ToList();
        }

        /// <summary>
        /// 我的发起的审批（通用）
        /// </summary>
        /// <param name="searchKey">搜索关键字</param>
        /// <param name="empId">员工id</param>
        /// <param name="appAuditStatus">app获取审核状态</param>
        /// <param name="applyType">申请筛选类型</param>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="rowCount">总条数</param>
        /// <returns></returns>
        public List<Rb_Workflow_Auditrecord_Extend> MyInitiateAudit(string searchKey, int empId, AppAuditStatusEnum appAuditStatus, int applyType, int pageIndex, int pageSize, out long rowCount)
        {
            string sql = $@"SELECT t.id as WorkFlowId, t.`Status`,t.CreateBy,t.CreateTime,t.TemplateType from (
            (SELECT a.id,a.`Status`,a.TemplateId,a.CreateBy,a.CreateTime,t.TemplateType,t.`Name` as TemplateName FROM rb_workflow_askforleave a INNER JOIN rb_workflow_template t on t.id = a.TemplateId where a.CreateBy = {empId}) UNION 
             (SELECT a.id,a.`Status`,a.TemplateId,a.CreateBy,a.CreateTime,t.TemplateType,t.`Name` as TemplateName FROM rb_workflow_evection a INNER JOIN rb_workflow_template t on t.id = a.TemplateId where a.CreateBy ={empId}) UNION
            (SELECT a.id,a.`Status`,a.TemplateId,a.CreateBy,a.CreateTime,t.TemplateType,t.`Name` as TemplateName FROM rb_workflow_goout a INNER JOIN rb_workflow_template t on t.id = a.TemplateId where a.CreateBy = {empId}) UNION 
            (SELECT a.id,a.`Status`,a.TemplateId,a.CreateBy,a.CreateTime,t.TemplateType,t.`Name` as TemplateName FROM rb_workflow_reissuecard a INNER JOIN rb_workflow_template t on t.id = a.TemplateId where a.CreateBy = {empId}) 
            ) as t";

            StringBuilder sb = new StringBuilder();
            sb.Append($"where CreateBy = {empId}");

            if (!string.IsNullOrWhiteSpace(searchKey))
            {
                sb.Append($" AND TemplateName LIKE '%{searchKey}%'");
            }
            else
            {
                if (appAuditStatus == AppAuditStatusEnum.AuditComplete)
                {
                    sb.Append($" AND (`Status`={(int)UserWFAuditStatus.Through} Or `Status`={(int)UserWFAuditStatus.NotThrough})");
                }
                else if (appAuditStatus == AppAuditStatusEnum.InComplete)
                {
                    sb.Append($" AND `Status`={(int)UserWFAuditStatus.InReview}");
                }
                else if (appAuditStatus == AppAuditStatusEnum.ToWithdraw)
                {
                    sb.Append($" AND `Status`={(int)UserWFAuditStatus.ToWithdraw}");
                }
                if (applyType != 0)
                {
                    sb.Append($" AND TemplateType={applyType}");
                }
            }
            sb.Append($" ORDER BY CreateTime DESC");
            return GetPage<Rb_Workflow_Auditrecord_Extend>(pageIndex, pageSize, out rowCount, $"{sql} {sb.ToString()}").ToList();
        }
        /// <summary>
        /// 获取历史记录每月数量
        /// </summary>
        /// <param name="searchKey"></param>
        /// <param name="empId"></param>
        /// <param name="appAuditStatus"></param>
        /// <param name="applyType"></param>
        /// <param name="MonthList"></param>
        /// <returns></returns>
        public List<Rb_Workflow_Auditrecord_Extend> ProposerHostoryMonthNum(string searchKey, int empId, AppAuditStatusEnum appAuditStatus, int applyType,List<string> MonthList)
        {
            string sql = $@"SELECT date_format(tt.CreateTime, '%Y-%m') as MonthStr,count(*) as TotalCount from (
            (SELECT a.id,a.`Status`,a.TemplateId,a.CreateBy,a.CreateTime,t.TemplateType,t.`Name` as TemplateName FROM rb_workflow_askforleave a INNER JOIN rb_workflow_template t on t.id = a.TemplateId where a.CreateBy = {empId}) UNION 
             (SELECT a.id,a.`Status`,a.TemplateId,a.CreateBy,a.CreateTime,t.TemplateType,t.`Name` as TemplateName FROM rb_workflow_evection a INNER JOIN rb_workflow_template t on t.id = a.TemplateId where a.CreateBy ={empId}) UNION
            (SELECT a.id,a.`Status`,a.TemplateId,a.CreateBy,a.CreateTime,t.TemplateType,t.`Name` as TemplateName FROM rb_workflow_goout a INNER JOIN rb_workflow_template t on t.id = a.TemplateId where a.CreateBy = {empId}) UNION 
            (SELECT a.id,a.`Status`,a.TemplateId,a.CreateBy,a.CreateTime,t.TemplateType,t.`Name` as TemplateName FROM rb_workflow_reissuecard a INNER JOIN rb_workflow_template t on t.id = a.TemplateId where a.CreateBy = {empId}) 
            ) as tt";

            StringBuilder sb = new StringBuilder();
            sb.Append($"where CreateBy = {empId}");

            if (!string.IsNullOrWhiteSpace(searchKey))
            {
                sb.Append($" AND TemplateName LIKE '%{searchKey}%'");
            }
            else
            {
                if (appAuditStatus == AppAuditStatusEnum.AuditComplete)
                {
                    sb.Append($" AND (`Status`={(int)UserWFAuditStatus.Through} Or `Status`={(int)UserWFAuditStatus.NotThrough})");
                }
                else if (appAuditStatus == AppAuditStatusEnum.InComplete)
                {
                    sb.Append($" AND `Status`={(int)UserWFAuditStatus.InReview}");
                }
                else if (appAuditStatus == AppAuditStatusEnum.ToWithdraw)
                {
                    sb.Append($" AND `Status`={(int)UserWFAuditStatus.ToWithdraw}");
                }
                if (applyType != 0)
                {
                    sb.Append($" AND TemplateType={applyType}");
                }
            }
            string MonthStr = "";
            foreach (var item in MonthList) {
                MonthStr += "'" + item + "',";
            }
            if (MonthStr != "") {
                MonthStr = MonthStr.Substring(0, MonthStr.Length - 1);
            }
            sb.Append($" and DATE_FORMAT(CreateTime,'%Y-%m') in ({MonthStr})  GROUP BY date_format(tt.CreateTime, '%Y-%m')");
            return Get<Rb_Workflow_Auditrecord_Extend>($"{sql} {sb.ToString()}").ToList();
        }
        /// <summary>
        /// 获取当前数据第一条是否属于当月第一条数据
        /// </summary>
        /// <param name="searchKey"></param>
        /// <param name="empId"></param>
        /// <param name="appAuditStatus"></param>
        /// <param name="applyType"></param>
        /// <param name="time"></param>
        /// <returns></returns>
        public int ProposerHostoryMonthNumBeFore(string searchKey, int empId, AppAuditStatusEnum appAuditStatus, int applyType, DateTime time)
        {
            string sql = $@"SELECT count(*) as TotalCount from (
            (SELECT a.id,a.`Status`,a.TemplateId,a.CreateBy,a.CreateTime,t.TemplateType,t.`Name` as TemplateName FROM rb_workflow_askforleave a INNER JOIN rb_workflow_template t on t.id = a.TemplateId where a.CreateBy = {empId}) UNION 
             (SELECT a.id,a.`Status`,a.TemplateId,a.CreateBy,a.CreateTime,t.TemplateType,t.`Name` as TemplateName FROM rb_workflow_evection a INNER JOIN rb_workflow_template t on t.id = a.TemplateId where a.CreateBy ={empId}) UNION
            (SELECT a.id,a.`Status`,a.TemplateId,a.CreateBy,a.CreateTime,t.TemplateType,t.`Name` as TemplateName FROM rb_workflow_goout a INNER JOIN rb_workflow_template t on t.id = a.TemplateId where a.CreateBy = {empId}) UNION 
            (SELECT a.id,a.`Status`,a.TemplateId,a.CreateBy,a.CreateTime,t.TemplateType,t.`Name` as TemplateName FROM rb_workflow_reissuecard a INNER JOIN rb_workflow_template t on t.id = a.TemplateId where a.CreateBy = {empId}) 
            ) as t";

            StringBuilder sb = new StringBuilder();
            sb.Append($"where CreateBy = {empId}");

            if (!string.IsNullOrWhiteSpace(searchKey))
            {
                sb.Append($" AND TemplateName LIKE '%{searchKey}%'");
            }
            else
            {
                if (appAuditStatus == AppAuditStatusEnum.AuditComplete)
                {
                    sb.Append($" AND (`Status`={(int)UserWFAuditStatus.Through} Or `Status`={(int)UserWFAuditStatus.NotThrough})");
                }
                else if (appAuditStatus == AppAuditStatusEnum.InComplete)
                {
                    sb.Append($" AND `Status`={(int)UserWFAuditStatus.InReview}");
                }
                else if (appAuditStatus == AppAuditStatusEnum.ToWithdraw)
                {
                    sb.Append($" AND `Status`={(int)UserWFAuditStatus.ToWithdraw}");
                }
                if (applyType != 0)
                {
                    sb.Append($" AND TemplateType={applyType}");
                }
            }
            sb.Append($" and DATE_FORMAT(CreateTime,'%Y-%m')='{time.ToString("yyyy-MM")}' and CreateTime>'{time.ToString("yyyy-MM-dd HH:mm:ss")}'");
            return Convert.ToInt32(ExecuteScalar($"{sql} {sb.ToString()}"));
        }



    }
}
