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 CalendarsService
    {
        /// <summary>
        /// 查询所有
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <param name="param">可变参数数组</param>
        /// <returns>返回集合</returns>
        List<Calendars> excuteSql(string sql, params SqlParameter[] param)
        {
            return ServiceBase<Calendars>.excuteSql(new Calendars(), "Calendar", sql, CommandType.Text, param);
        }


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


        /// <summary>
        /// 根据编号查询对象信息
        /// </summary>
        /// <param name="id">对象编号</param>
        /// <returns>返回空或者单个对象信息</returns>
        public Calendars GetCalendarsByID(int id)
        {
            //调用获取单个对象的方法
            return excuteType("select * from Calendar where Id = @id", new SqlParameter("@id", id));
        }

        /// <summary>
        /// 根据日期查询对象信息
        /// </summary>
        /// <param name="calendarDate"></param>
        /// <returns></returns>
        public Calendars GetCalendarsByCalendarDate(string calendarDate)
        {
            return excuteType("select * from Calendar where CalendarDate = @CalendarDate", new SqlParameter("@CalendarDate", calendarDate));
        }

        /// <summary>
        /// 根据日期查询集合
        /// </summary>
        /// <param name="yearMonth"></param>
        /// <returns></returns>
        public List<Calendars> GetCalendarsByCalendarDates(string yearMonth)
        {
            string yearMonthStart = Convert.ToDateTime(yearMonth + "-01").AddMonths(-1).ToString("yyyy-MM") + "-28";
            string yearMonthEnd = yearMonth + "-27";

            SqlParameter[] parameter = new SqlParameter[]{
                new SqlParameter("@yearMonthStart",yearMonthStart),
                new SqlParameter("@yearMonthEnd",yearMonthEnd)
            };

            return excuteSql("select * from Calendar where CalendarDate between @yearMonthStart and @yearMonthEnd order by CalendarDate asc", parameter);
        }

        /// <summary>
        /// 新增
        /// </summary>
        /// <param name="sdt">对象</param>
        public bool AddCalendar(Calendars c)
        {
            if (SqlHelper.ExecuteNonQuery("insert into Calendar values(@CalendarDate,@CTid)", CommandType.Text, new SqlParameter("@CalendarDate",c.CalendarDate),new SqlParameter("@CTid",c.CTid)) > 0)
                return true;
            return false;
        }

        /// <summary>
        /// 删除
        /// </summary>
        /// <param name="calendarDate"></param>
        /// <returns></returns>
        public bool DelCalendar(string calendarDate)
        {
            if (SqlHelper.ExecuteNonQuery("delete Calendar where CalendarDate = @CalendarDate", CommandType.Text, new SqlParameter("@CalendarDate", calendarDate)) > 0)
                return true;
            return false;
        }

        /// <summary>
        /// 获取工作天数
        /// </summary>
        /// <param name="starDate"></param>
        /// <param name="endDate"></param>
        /// <returns></returns>
        public int GetCalendarWeekCount(string starDate,string endDate)
        {
            string sql = "select count(distinct calendarDate) from Calendar where ctid = 0 and (calendardate between '"+starDate+"' and '"+endDate+"')";
            return (int)SqlHelper.ExecuteScalar(sql, CommandType.Text);
        }

        /// <summary>
        /// LiuChengYi   2014/5/5
        /// 根据月份获取天数
        /// </summary>
        /// <param name="months"></param>
        /// <returns></returns>
        public List<Calendars> GetCalendarMonths(string startDate, string endDate)
        {
            string sql = "SELECT * FROM dbo.Calendar WHERE CalendarDate BETWEEN '"+startDate+"' AND '"+endDate+"'  ORDER BY CalendarDate asc";
            return excuteSql(sql);
        }


        /// <summary>
        /// 获取指定日期内的日历
        /// </summary>
        /// <param name="startDate">开始日期</param>
        /// <param name="endDate">结束日期</param>
        /// <returns>倒序排列的日历表</returns>
        public List<Calendars> GetCalendar(string startDate, string endDate)
        {
            string sql = "SELECT * FROM dbo.Calendar WHERE CalendarDate BETWEEN '" + startDate + "' AND '" + endDate + "'  ORDER BY CalendarDate Desc";
            return excuteSql(sql);
        }
    }
}