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


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

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

        /// <summary>
        /// 查询信息
        /// </summary>
        /// <returns>返回空或者对象信息</returns>
        public List<GroupCost> GetItaliaTranByDiid(int diid)
        {
            //调用获取单个对象的方法
            return excuteSql("select * from GroupCost where Diid = @diid and IsDel = 0 order by Id", new SqlParameter("@diid", diid));
        }


        /// <summary>
        /// 查询信息
        /// </summary>
        /// <returns>返回空或者对象信息</returns>
        public List<GroupCost> GetByDiid(int diid)
        {
            //调用获取单个对象的方法
            return excuteSql("select * from GroupCost where Diid = @diid  and IsDel = 0 and (Date!='' or ITIN!='') order by Id", new SqlParameter("@diid", diid));
        }
        /// <summary>
        /// 查询所有
        /// </summary>
        /// <returns></returns>
        public List<GroupCost> GetAll()
        {
            return excuteSql("select * from GroupCost where IsDel=0");
        }


        /// <summary>
        /// 增加
        /// </summary>
        /// <param name="cd"></param>
        /// <returns></returns>
        public bool AddItaliaTran(GroupCost hd)
        {
            string sql = "insert into GroupCost values(@Diid,@Day,@Date,@ITIN,@CarType,@CarCost,@CarNumber,@CarTiming,@TGS,@TGWH,@TGN,@TGOF,@TGM,@TGA,@TGTF,@TGEF,@CFS,@CFM,@CFOF,@B,@L,@D," +
                         "@TBR,@SGR,@JS_ES,@Suite,@ACCON,@TV,@IL,@IF,@EF,@B_R_F,@TE,@TGTips,@DRVTips,@PC,@TLF,@ECT,@Oper,@OpTime,@Isdel)";

            SqlParameter[] parameter = new SqlParameter[]{
                new SqlParameter("@Diid",hd.Diid),
                new SqlParameter("@Day",hd.DAY),
                new SqlParameter("@Date",hd.Date),
                new SqlParameter("@ITIN",hd.ITIN),
                new SqlParameter("@CarType",hd.CarType),
                new SqlParameter("@CarTiming",hd.CarTiming),
                new SqlParameter("@CarCost",hd.CarCost),
                new SqlParameter("@CarNumber",hd.CarNumber),
                new SqlParameter("@TGS",hd.TGS),
                new SqlParameter("@TGWH",hd.TGWH),
                new SqlParameter("@TGN",hd.TGN),
                new SqlParameter("@TGOF",hd.TGOF),
                new SqlParameter("@TGM",hd.TGM),
                new SqlParameter("@TGA",hd.TGA),
                new SqlParameter("@TGTF",hd.TGTF),
                new SqlParameter("@TGEF",hd.TGEF),
                new SqlParameter("@CFS",hd.CFS),
                new SqlParameter("@CFM",hd.CFM),
                new SqlParameter("@CFOF",hd.CFOF),
                new SqlParameter("@B",hd.B),
                new SqlParameter("@L",hd.L),
                new SqlParameter("@D",hd.D),
                new SqlParameter("@TBR",hd.TBR),
                new SqlParameter("@SGR",hd.SGR),
                new SqlParameter("@JS_ES",hd.JS_ES),
                new SqlParameter("@Suite",hd.Suite),
                new SqlParameter("@ACCON",hd.ACCON),
                new SqlParameter("@TV",hd.TV),
                new SqlParameter("@IL",hd.iL),
                new SqlParameter("@IF",hd.IF),
                new SqlParameter("@EF",hd.EF),
                new SqlParameter("@B_R_F",hd.B_R_F),
                new SqlParameter("@TE",hd.TE),
                new SqlParameter("@TGTips",hd.TGTips),
                new SqlParameter("@DRVTips",hd.DRVTips),
                new SqlParameter("@PC",hd.PC),
                new SqlParameter("@TLF",hd.TLF),
                new SqlParameter("@ECT",hd.ECT),
                new SqlParameter("@Oper",hd.Oper),
                new SqlParameter("@OpTime",hd.OpTime),
                new SqlParameter("@IsDel",hd.Isdel),
            };

            if (SqlHelper.ExecuteNonQuery(sql, CommandType.Text, parameter) > 0)
                return true;
            return false;
        }

        /// <summary>
        /// 编辑
        /// </summary>
        /// <param name="sdt"></param>
        /// <returns></returns>
        public bool EditItaliaTran(GroupCost hd)
        {
            string sql = "update GroupCost set Diid=@Diid,Day=@Day,Date=@Date,ITIN=@ITIN,CarType=@CarType,CarTiming=@CarTiming,CarCost=@CarCost,CarNumber=@CarNumber,TGS=@TGS,TGWH=@TGWH,TGN=@TGN,TGOF=@TGOF," +
                         "TGM=@TGM,TGA=@TGA,TGTF=@TGTF,TGEF=@TGEF,CFS=@CFS,CFM=@CFM,CFOF=@CFOF,B=@B,L=@L,D=@D,TBR=@TBR,SGR=@SGR,JS_ES=@JS_ES,Suite=@Suite,ACCON=@ACCON,TV=@TV," +
                         "IL=@IL,[IF]=@IF,EF=@EF,B_R_F=@B_R_F,TE=@TE,TGTips=@TGTips,DRVTips=@DRVTips,PC=@PC,TLF=@TLF,ECT=@ECT,Oper=@Oper,OpTime=@OpTime,Isdel=@Isdel where Id = @Id";

            SqlParameter[] parameter = new SqlParameter[]{
                new SqlParameter("@Diid",hd.Diid),
                new SqlParameter("@Day",hd.DAY),
                new SqlParameter("@Date",hd.Date),
                new SqlParameter("@ITIN",hd.ITIN),
                new SqlParameter("@CarType",hd.CarType),
                new SqlParameter("@CarTiming",hd.CarTiming),
                new SqlParameter("@CarCost",hd.CarCost),
                new SqlParameter("@CarNumber",hd.CarNumber),
                new SqlParameter("@TGS",hd.TGS),
                new SqlParameter("@TGWH",hd.TGWH),
                new SqlParameter("@TGN",hd.TGN),
                new SqlParameter("@TGOF",hd.TGOF),
                new SqlParameter("@TGM",hd.TGM),
                new SqlParameter("@TGA",hd.TGA),
                new SqlParameter("@TGTF",hd.TGTF),
                new SqlParameter("@TGEF",hd.TGEF),
                new SqlParameter("@CFS",hd.CFS),
                new SqlParameter("@CFM",hd.CFM),
                new SqlParameter("@CFOF",hd.CFOF),
                new SqlParameter("@B",hd.B),
                new SqlParameter("@L",hd.L),
                new SqlParameter("@D",hd.D),
                new SqlParameter("@TBR",hd.TBR),
                new SqlParameter("@SGR",hd.SGR),
                new SqlParameter("@JS_ES",hd.JS_ES),
                new SqlParameter("@Suite",hd.Suite),
                new SqlParameter("@ACCON",hd.ACCON),
                new SqlParameter("@TV",hd.TV),
                new SqlParameter("@IL",hd.iL),
                new SqlParameter("@IF",hd.IF),
                new SqlParameter("@EF",hd.EF),
                new SqlParameter("@B_R_F",hd.B_R_F),
                new SqlParameter("@TE",hd.TE),
                new SqlParameter("@TGTips",hd.TGTips),
                new SqlParameter("@DRVTips",hd.DRVTips),
                new SqlParameter("@PC",hd.PC),
                new SqlParameter("@TLF",hd.TLF),
                new SqlParameter("@ECT",hd.ECT),
                new SqlParameter("@Oper",hd.Oper),
                new SqlParameter("@OpTime",hd.OpTime),
                new SqlParameter("@IsDel",hd.Isdel),
                new SqlParameter("@Id",hd.Id)
            };

            if (SqlHelper.ExecuteNonQuery(sql, CommandType.Text, parameter) > 0)
                return true;
            return false;
        }

        /// <summary>
        /// 删除
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public bool DelItaliaTran(int id)
        {
            if (SqlHelper.ExecuteNonQuery("update GroupCost set IsDel = 1 where Id = @Id", CommandType.Text, new SqlParameter("@Id", id)) > 0)
                return true;
            return false;
        }
    }
}