//-----------------------------------------------------------------------
// <copyright file=" per_employee.cs">
// * FileName: per_employee.cs
// </copyright>
//-----------------------------------------------------------------------
using Microsoft.EntityFrameworkCore;
using Performance.DtoModels;
using Performance.EntityModels;
using Performance.Infrastructure.Models;
using Performance.Repository;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Linq.Expressions;

namespace Performance.Repository
{
    /// <summary>
    /// per_employee Repository
    /// </summary>
    public partial class PerforPeremployeeRepository : PerforRepository<per_employee>
    {

        public new PageList<per_employee> GetEntitiesForPaging(int pageNumber, int pageSize, Expression<Func<per_employee, bool>> exp)
        {
            IQueryable<per_employee> queryableAuthors = context.Set<per_employee>().Where(exp).OrderBy(w => w.IsVerify).ThenBy(t => t.Id);

            return PageList<per_employee>.Create(queryableAuthors, pageNumber, pageSize);
        }


        //public Comparison GetComparison(ComparisonPagingRequest request)
        //{
        //    var search = "";
        //    if (string.IsNullOrEmpty(request.SearchQuery))
        //        search = " 1=1 ";
        //    else
        //    {
        //        if (request.ViewName == "view_check_dept")
        //            search = $" ( AccountingUnit like '%{request.SearchQuery}%' )";
        //        else
        //            search = $" ( AccountingUnit like '%{request.SearchQuery}%' or JobNumber like '%{request.SearchQuery}%' or EmployeeName like '%{request.SearchQuery}%') ";
        //    }


        //    var result = new Comparison();
        //    var sql = $@"SELECT COUNT(*) FROM {request.ViewName} WHERE AllotId = @AllotId and {search}";
        //    result.TotalCount = DapperQuery<int>(sql, new { request.AllotId })?.FirstOrDefault() ?? 0;

        //    sql = $@"SELECT * FROM {request.ViewName} WHERE AllotId = @AllotId and {search} ORDER BY HospitalId,Year,Month,ABS(DIFF) DESC LIMIT {(request.PageIndex - 1) * request.PageSize},{request.PageSize}; ";

        //    result.Datas = DapperQuery<view_check_emp>(sql, new { request.AllotId })?.ToList();
        //    return result;
        //}

        /// <summary>
        /// 人员实发绩效比对
        /// </summary>
        /// <param name="allotId"></param>
        /// <param name="searchQuery"></param>
        /// <returns></returns>
        public Comparison CheckEmployeeRealGiveFeeDiff(int allotId, string searchQuery)
        {
            var queryData = @"
SELECT 
	HospitalId,Year,Month,AllotID,UnitType,AccountingUnit,JobNumber,MAX(EmployeeName) AS EmployeeName,
	SUM(RealGiveFeeExecl) AS RealGiveFeeExecl,SUM(RealGiveFeeCompute) AS RealGiveFeeCompute,SUM(RealGiveFeeExecl) - SUM(RealGiveFeeCompute) AS Diff 
FROM (
	SELECT * FROM view_check_emp_clinic WHERE AllotId = @allotId UNION ALL
	SELECT * FROM view_check_emp_employee WHERE AllotId = @allotId UNION ALL
	SELECT * FROM view_check_emp_logistics WHERE AllotId = @allotId
) TAB 
WHERE if(@searchQuery='','',AccountingUnit) LIKE @parm OR if(@searchQuery='','',JobNumber) LIKE @parm OR if(@searchQuery='','',EmployeeName) LIKE @parm
GROUP BY HospitalId,Year,Month,AllotID,UnitType,AccountingUnit,JobNumber
ORDER BY HospitalId,Year,Month,ABS(SUM(RealGiveFeeExecl) - SUM(RealGiveFeeCompute)) DESC 
";

            var queryCount = @"
SELECT COUNT(DISTINCT HospitalId,Year,Month,AllotID,UnitType,AccountingUnit,JobNumber) FROM (
	SELECT * FROM view_check_emp_clinic WHERE AllotId = @allotId UNION ALL
	SELECT * FROM view_check_emp_employee WHERE AllotId = @allotId UNION ALL
	SELECT * FROM view_check_emp_logistics WHERE AllotId = @allotId
) TAB 
WHERE if(@searchQuery='','',AccountingUnit) LIKE @parm OR if(@searchQuery='','',JobNumber) LIKE @parm OR if(@searchQuery='','',EmployeeName) LIKE @parm
";
            return new Comparison()
            {
                Datas = DapperQuery<view_check_emp>(queryData, new { allotId, searchQuery, parm = $"%{searchQuery}%" })?.ToList() ?? new List<view_check_emp>(),
                TotalCount = DapperQuery<int>(queryCount, new { allotId, searchQuery, parm = $"%{searchQuery}%" })?.FirstOrDefault() ?? 0,
            };
        }

        /// <summary>
        /// 科室实发绩效比对
        /// </summary>
        public Comparison CheckAccountingUnitRealGiveFeeDiff(int allotId, string searchQuery)
        {
            var queryData = @"
SELECT *,IFNULL(RealGiveFeeExecl,0) - IFNULL(RealGiveFeeCompute,0) AS Diff FROM (
	SELECT * FROM view_check_dept_account WHERE AllotId = @allotId UNION ALL
	SELECT * FROM view_check_dept_specialunit WHERE AllotId = @allotId
) TAB 
WHERE if(@searchQuery='','',AccountingUnit) LIKE @parm
ORDER BY HospitalId,Year,Month,ABS(DIFF) DESC 
";

            var queryCount = @"
SELECT count(0) FROM (
	SELECT * FROM view_check_dept_account WHERE AllotId = @allotId UNION ALL
	SELECT * FROM view_check_dept_specialunit WHERE AllotId = @allotId
) TAB 
WHERE if(@searchQuery='','',AccountingUnit) LIKE @parm
";
            return new Comparison()
            {
                Datas = DapperQuery<view_check_emp>(queryData, new { allotId, searchQuery, parm = $"%{searchQuery}%" })?.ToList() ?? new List<view_check_emp>(),
                TotalCount = DapperQuery<int>(queryCount, new { allotId, searchQuery, parm = $"%{searchQuery}%" })?.FirstOrDefault() ?? 0,
            };
        }

        public IDbConnection GetDbConnection()
        {
            return context.Database.GetDbConnection();
        }
    }
}
