using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Models;
using System.Data.SqlClient;
using System.Data;

namespace DAL
{
    /// <summary>
    /// 考勤审核多表联查数据访问类
    /// </summary>
    public class ViewUpCardAuditService
    {
        /// <summary>
        /// 查询所有
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <param name="param">可变参数数组</param>
        /// <returns>返回集合</returns>
        List<ViewUpCardAudit> excuteSql(string sql, params SqlParameter[] param)
        {
            return ServiceBase<ViewUpCardAudit>.excuteSql(new ViewUpCardAudit(), "ViewUpCardAudit", sql, CommandType.Text, param);
        }


        /// <summary>
        /// 获取单个对象
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <param name="param">可变参数数组</param>
        /// <returns>返回空或者单个对象</returns>
        ViewUpCardAudit excuteType(string sql, params SqlParameter[] param)
        {
            //查询结果放入对象集合
            List<ViewUpCardAudit> cdList = excuteSql(sql, param);
            //判断集合是否为空
            if (cdList == null || cdList.Count == 0)
                //返回null
                return null;
            //返回单个对象
            return cdList[0];
        }


        /// <summary>
        /// 查询信息
        /// </summary>
        /// <returns>返回空或者对象信息</returns>
        public List<ViewUpCardAudit> GetViewUpCardAudit(int uid, string yearMonth)
        {
            //调用获取单个对象的方法
            return excuteSql("select uca.Uid,uca.YearMonth,uca.IsAudit,ucac.upcardHours from UpCardAudit uca join UpCardAuditContent ucac on uca.id = ucac.UCAid where uca.isDel = 0 and ucac.isdel=0 and did = 31 and isAudit = 1 and uid = " + uid + " and yearMonth='" + yearMonth + "'");
        }

        /// <summary>
        /// 查询信息
        /// </summary>
        /// <returns>返回空或者对象信息</returns>
        public double GetViewUpCardAuditSumHours(int uid, string yearMonth)
        {
            object obj = SqlHelper.ExecuteScalar("select sum(ucac.upcardHours) from UpCardAudit uca join UpCardAuditContent ucac on uca.id = ucac.UCAid where uca.isDel = 0 and ucac.isdel=0 and did = 31 and isAudit = 1 and uid = " + uid + " and yearMonth='" + yearMonth + "'", CommandType.Text);
            double sumHours = 0;

            if (obj != null && !string.IsNullOrEmpty(obj.ToString()))
                sumHours = Convert.ToDouble(obj);
            //调用获取单个对象的方法
            return sumHours;
        }

        /// <summary>
        /// 查询信息
        /// </summary>
        /// <returns>返回空或者对象信息</returns>
        public double GetViewUpCardAuditSumHoursBingJia(int uid, string yearMonth)
        {
            object obj = SqlHelper.ExecuteScalar("select sum(ucac.upcardHours) from UpCardAudit uca join UpCardAuditContent ucac on uca.id = ucac.UCAid where uca.isDel = 0 and ucac.isdel=0 and did = 32 and isAudit = 1 and uid = " + uid + " and yearMonth='" + yearMonth + "'", CommandType.Text);
            double sumHours = 0;

            if (obj != null && !string.IsNullOrEmpty(obj.ToString()))
                sumHours = Convert.ToDouble(obj);
            //调用获取单个对象的方法
            return sumHours;
        }

        /// <summary>
        /// 查询员工加班抵休小时数
        /// </summary>
        /// <returns>返回空或者对象信息</returns>
        public double GetViewUpCardAuditSumHour(int uid, string yearMonth)
        {
            object obj = SqlHelper.ExecuteScalar("select sum(ucac.upcardHours) from UpCardAudit uca join UpCardAuditContent ucac on uca.id = ucac.UCAid where uca.isDel = 0 and ucac.isdel=0 and did = 31 and isAudit = 1 and uid = " + uid + " and yearMonth='" + yearMonth + "'", CommandType.Text);
            double sumHours = 0;

            if (obj != null && !string.IsNullOrEmpty(obj.ToString()))
                sumHours = Convert.ToDouble(obj);
            //调用获取单个对象的方法
            return sumHours;
        }

        /// <summary>
        /// 根据年月和员工id查询该员工事假信息
        /// </summary>
        /// <param name="uid"></param>
        /// <param name="yearMonth"></param>
        /// <returns></returns>
        public DataTable GetUpcardHoursByUidAndTime(int uid, string yearMonth, string startDate, string endDate)
        {
            return SqlHelper.TransferProcedure("Wage_QueryByUidAndMonth", CommandType.StoredProcedure, new SqlParameter("@uid", uid), new SqlParameter("@yearMonth", yearMonth), new SqlParameter("@startDate", startDate), new SqlParameter("@endDate", endDate));
        }



        /// <summary>
        /// 查询已审核的事假，年假，病假，加班抵修的日期
        /// </summary>
        /// <returns>返回空或者对象信息</returns>
        public List<ViewUpCardAudit> GetViewUpCardAuditDelMeal(int uid, string yearMonth)
        {
            //调用获取单个对象的方法
            return excuteSql("select uca.Uid,uca.YearMonth,uca.IsAudit,ucac.upcardHours,ucac.Did from UpCardAudit uca join UpCardAuditContent ucac on uca.id = ucac.UCAid where uca.isDel = 0 and ucac.isdel=0 and did in(31,32,33,281,340,293,299,568) and isAudit = 1 and uid = " + uid + " and yearMonth='" + yearMonth + "'");
        }

        /// <summary>
        /// 查询指定时间段已审核的事假，年假，病假，加班抵修的日期
        /// </summary>
        /// <returns>返回空或者对象信息</returns>
        public List<ViewUpCardAudit> GetViewUpCardAuditDelMeal(int uid, string yearMonth, string start, string end)
        {
            //调用获取单个对象的方法
            return excuteSql("select uca.Uid,uca.YearMonth,uca.IsAudit,ucac.upcardHours,ucac.Did from UpCardAudit uca join UpCardAuditContent ucac on uca.id = ucac.UCAid where uca.isDel = 0 and ucac.isdel=0 and did in(31,32,33,281,340,293,299,568) and isAudit = 1 and uid = " + uid + " and yearMonth='" + yearMonth + "' and  (ucac.UpCardDate BETWEEN '" + start + "' and '" + end + "')");
        }

    }
}