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

        /// <summary>
        /// 获取单个对象
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <param name="param">可变参数数组</param>
        /// <returns>返回空或者单个对象</returns>
        DeleFile excuteType(string sql, params SqlParameter[] param)
        {
            //查询结果放入对象集合
            List<DeleFile> ctggdList = excuteSql(sql, param);
            //判断集合是否为空
            if (ctggdList == null || ctggdList.Count == 0)
                //返回null
                return null;
            //返回单个对象
            return ctggdList[0];
        }
        //增Add
        public bool AddDeleQZ(DeleFile Dov)
        {
            string sql = "insert into DeleFile values(@Diid,@Category,@Kind,@FileName,@FilePath,@Oper,@OpTime,@IsDel);SELECT @@IDENTITY";

            SqlParameter[] parameter = new SqlParameter[]{
                new SqlParameter("@Diid",Dov.Diid),
                new SqlParameter("@Category",Dov.Category),
                new SqlParameter("@Kind",Dov.Kind),
                new SqlParameter("@FileName",Dov.FileName),
                new SqlParameter("@FilePath",Dov.FilePath),
                new SqlParameter("@Oper",Dov.Oper),
                new SqlParameter("@OpTime",Dov.OpTime),
                new SqlParameter("@IsDel",Dov.IsDel)
            };

            if (SqlHelper.ExecuteNonQuery(sql, CommandType.Text, parameter) > 0)
                return true;
            return false;
        }
        //删Delete
        public bool DelDeleQZ(int id)
        {
            if (SqlHelper.ExecuteNonQuery("update DeleFile set IsDel = 1 where Id = @Id", CommandType.Text, new SqlParameter("@Id", id)) > 0)
                return true;
            return false;
        }
        //改Update
        public bool EditDeleQZ(DeleFile Dov)
        {
            string sql = "update DeleFile set Diid=@Diid,Category=@Category,Kind=@Kind,FileName=@FileName,FilePath=@FilePath,Oper=@Oper,OpTime=@OpTime,Isdel=@Isdel where Id = @Id";

            SqlParameter[] parameter = new SqlParameter[]{
                new SqlParameter("@Diid",Dov.Diid),
                new SqlParameter("@Category",Dov.Category),
                new SqlParameter("@Kind",Dov.Kind),
                new SqlParameter("@FileName",Dov.FileName),
                new SqlParameter("@FilePath",Dov.FilePath),
                new SqlParameter("@Oper",Dov.Oper),
                new SqlParameter("@OpTime",Dov.OpTime),
                new SqlParameter("@IsDel",Dov.IsDel),
                new SqlParameter("@Id",Dov.Id)
            };

            if (SqlHelper.ExecuteNonQuery(sql, CommandType.Text, parameter) > 0)
                return true;
            return false;
        }
        /// <summary>
        /// 根据团组id,附件一级类别查询数据
        /// </summary>
        /// <param name="diid">团组id</param>
        /// <param name="category">附件一级类别</param>
        /// <returns></returns>
        public DeleFile GetByDC(int diid, string category)
        {
            //调用获取单个对象的方法

            return excuteType("select * from DeleFile where IsDel=0 and Diid=@diid and Category=@category", new SqlParameter("@diid", diid), new SqlParameter("@category", category));
        }

        /// <summary>
        /// 根据团组id,附件一级类别查询数据
        /// </summary>
        /// <param name="diid">团组id</param>
        /// <param name="category">附件一级类别</param>
        /// <returns></returns>
        public List<DeleFile> GetLByDC(int diid, string category)
        {
            //调用获取单个对象的方法

            return excuteSql("select * from DeleFile where IsDel=0 and Diid=@diid and Category=@category", new SqlParameter("@diid", diid), new SqlParameter("@category", category));
        }

        /// <summary>
        /// 根据团组id,附件一级类别查询数据
        /// </summary>
        /// <param name="diid">团组id</param>
        /// <param name="category">附件一级类别</param>
        /// <returns></returns>
        public List<DeleFile> GetLByDCKS(int diid, string category, int kind)
        {
            //调用获取单个对象的方法

            return excuteSql("select * from DeleFile where IsDel=0 and Diid=@diid and Category=@category  and Kind=@Kind", new SqlParameter("@diid", diid), new SqlParameter("@category", category), new SqlParameter("@Kind", kind));
        }

        /// <summary>
        ///根据团组id,附件一级类别,附件二级类别查询数据
        /// </summary>
        /// <param name="diid">团组id</param>
        /// <param name="category">附件一级类别</param>
        /// <param name="kind">附件二级类别</param>
        /// <returns></returns>
        public DeleFile GetByDCK(int diid, string category, int kind)
        {
            //调用获取单个对象的方法

            return excuteType("select * from DeleFile where IsDel=0 and Diid=@diid and Category=@category and Kind=@Kind", new SqlParameter("@diid", diid), new SqlParameter("@category", category), new SqlParameter("@Kind", kind));
        }

        /// <summary>
        ///根据id,附件查询数据
        /// </summary>
        /// <param name="Id">id</param>
        /// <returns></returns>
        public DeleFile GetById(int Id)
        {
            //调用获取单个对象的方法

            return excuteType("select * from DeleFile where IsDel=0 and Id=@Id", new SqlParameter("@Id", Id));
        }

        /// <summary>
        /// 根据团组id查询所有附件
        /// </summary>
        /// <param name="diid">团组id</param>
        /// <returns></returns>
        public List<DeleFile> GetByDiid(int diid)
        {
            //调用获取单个对象的方法

            return excuteSql("select * from DeleFile where IsDel=0 and Diid=@diid ", new SqlParameter("@diid", diid));
        }
    }
}