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

namespace DAL
{
    public class TravelDetailsService
    {
        List<TravelDetails> excuteSql(string sql, params SqlParameter[] param)
        {
            return ServiceBase<TravelDetails>.excuteSql(new TravelDetails(), "TravelDetails", sql, CommandType.Text, param);
        }
        TravelDetails excuteType(string sql, params SqlParameter[] param)
        {
            //查询结果放入对象集合
            List<TravelDetails> hdList = excuteSql(sql, param);
            //判断集合是否为空
            if (hdList == null || hdList.Count == 0)
                //返回null
                return null;
            //返回单个对象
            return hdList[0];
        }

        //增Add
        public bool Add(TravelDetails Dov)
        {
            string sql = "insert into TravelDetails values(@Diid,@OrderNum,@Date,@Time,@ActivityType,@Activity,@Address,@Remark,@OPer,@Opdate,@Isdel);SELECT @@IDENTITY";

            SqlParameter[] parameter = new SqlParameter[]{
                new SqlParameter("@Diid",Dov.Diid),
                new SqlParameter("@OrderNum",Dov.OrderNum),
                new SqlParameter("@Date",Dov.Date),
                new SqlParameter("@Time",Dov.Time),
                new SqlParameter("@ActivityType",Dov.ActivityType),
                new SqlParameter("@Activity",Dov.Activity),
                new SqlParameter("@Address",Dov.Address),
                new SqlParameter("@Remark",Dov.Remark),
                new SqlParameter("@OPer",Dov.OPer),
                new SqlParameter("@Opdate",Dov.Opdate),
                new SqlParameter("@Isdel",Dov.Isdel)
            };

            if (SqlHelper.ExecuteNonQuery(sql, CommandType.Text, parameter) > 0)
                return true;
            return false;
        }
        //改Update
        public bool Edit(TravelDetails Dov)
        {
            string sql = "update TravelDetails set Diid=@Diid,OrderNum=@OrderNum,Date=@Date,Time=@Time,ActivityType=@ActivityType,Activity=@Activity,"
                + "Address=@Address,Remark=@Remark,Oper=@Oper,Opdate=@Opdate,Isdel=@Isdel where Id = @Id";

            SqlParameter[] parameter = new SqlParameter[]{
                new SqlParameter("@Diid",Dov.Diid),
                new SqlParameter("@OrderNum",Dov.OrderNum),
                new SqlParameter("@Date",Dov.Date),
                new SqlParameter("@Time",Dov.Time),
                new SqlParameter("@ActivityType",Dov.ActivityType),
                new SqlParameter("@Activity",Dov.Activity),
                new SqlParameter("@Remark",Dov.Remark),
                new SqlParameter("@Address",Dov.Address),
                new SqlParameter("@OPer",Dov.OPer),
                new SqlParameter("@Opdate",Dov.Opdate),
                new SqlParameter("@Isdel",Dov.Isdel),
                new SqlParameter("@Id",Dov.Id)
            };

            if (SqlHelper.ExecuteNonQuery(sql, CommandType.Text, parameter) > 0)
                return true;
            return false;
        }
        //删
        public bool delOA(int id)
        {
            if (SqlHelper.ExecuteNonQuery("update TravelDetails set Isdel=1 where Id=@Id", CommandType.Text, new SqlParameter("@Id", id)) > 0)
                return true;
            return false;
        }

        public List<TravelDetails> GetByDiid(int diid)
        {
            return excuteSql("select * from TravelDetails where Isdel=0 and Diid=" + diid + " order by OrderNum");
        }
        /// <summary>
        /// GEN
        /// </summary>
        /// <param name="diid"></param>
        /// <returns></returns>
        public List<TravelDetails> GetByDiidAndDate(int diid, string date)
        {
            return excuteSql("select * from TravelDetails where Isdel=0 and Diid=" + diid + " and Date='" + date + "' order by OrderNum");
        }

        public TravelDetails getById(int id)
        {
            return excuteType("select * from TravelDetails where Isdel=0 and Id=@id", new SqlParameter("@id", id));
        }



        /// <summary>
        /// 删除指定范围
        /// </summary>
        /// <param name="Diid"></param>
        /// <param name="start"></param>
        /// <param name="end"></param>
        /// <returns></returns>
        public bool DelOld(int Diid, int start, int end)
        {
            if (SqlHelper.ExecuteNonQuery("delete from TravelDetails where Diid=" + Diid + " and ( Id>=" + start + " and Id<=" + end + ")", CommandType.Text) > 0)
                return true;
            return false;
        }

    }
}