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 TeamRateService
    {
        /// <summary>
        /// 获取全部团组汇率 - 分页
        /// </summary>
        /// <returns></returns>
        public List<TeamRate> GetAll(int pageIndex, out int sumPage, out int totalRecord, int diid, int ctable)
        {
            string sqlwhere = " Diid=" + diid + " and CTable=" + ctable;

            return PageBase<TeamRate>.excutePageSql(new TeamRate(), "TeamRate", "TeamRate", "*", "id desc", sqlwhere, 100, pageIndex, out sumPage, out totalRecord);
        }

        /// <summary>
        /// 获取全部团组汇率
        /// </summary>
        /// <returns></returns>
        public List<TeamRate> GetAll(int diid)
        {
            return excuteSql("select * from TeamRate where diid = '" + diid + "'");
        }

        /// <summary>
        /// 根据团组号和团组指向分类查询汇率信息
        /// </summary>
        /// <param name="p"></param>
        /// <param name="ctable"></param>
        /// <returns></returns>
        public TeamRate GetByDiidAndCtable(int diid, int ctable)
        {
            //调用获取单个对象的方法
            return excuteType("select * from TeamRate where Diid=" + diid + " and CTable=" + ctable, null);
        }


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


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

        /// <summary>
        /// 新增团组汇率
        /// </summary>
        /// <param name="tr"></param>
        /// <returns></returns>
        public bool AddTeamRate(TeamRate tr)
        {
            //if (SqlHelper.ExecuteNonQuery("insert into TeamRate values(@Diid,@CTable,@RateU,@RateE,@OtherRate,@OtherPrice)",
            //    CommandType.Text, 
            //    new SqlParameter("@Diid", tr.Diid), 
            //    new SqlParameter("@CTable", tr.CTable), 
            //    new SqlParameter("@RateU", tr.RateU), 
            //    new SqlParameter("@RateE", tr.RateE), 
            //    new SqlParameter("@OtherRate", tr.OtherRate), 
            //    new SqlParameter("@OtherPrice", tr.OtherPrice)) > 0)
            // RateJ RateH RateN  RateS RateA  RateC

            if (SqlHelper.ExecuteNonQuery("insert into TeamRate values(@Diid,@CTable,@OPer,@OPDate,@OtherRate,@OtherPrice,@RateU,@RateE," +
                                            "@RateJ,@RateH,@RateN,@RateS,@RateA,@RateC,@RateT,@RateBL,@RateHB,@RateFJD,@RateTL,@RateRP,@RatePeso,@RateMYR," +
                                            "@RateCZK,@RateMXN,@RateMOP,@RateARS,@RateHUF,@RateRUB)",
                   CommandType.Text,
                   new SqlParameter("@Diid", tr.Diid),
                   new SqlParameter("@CTable", tr.CTable),
                   new SqlParameter("@OPer", tr.OPer),
                   new SqlParameter("@OPDate", tr.OPDate),
                   new SqlParameter("@OtherRate", tr.OtherRate),
                   new SqlParameter("@OtherPrice", tr.OtherPrice),
                   new SqlParameter("@RateU", tr.RateU),
                   new SqlParameter("@RateE", tr.RateE),
                   new SqlParameter("@RateJ", tr.RateJ),
                   new SqlParameter("@RateH", tr.RateH),
                   new SqlParameter("@RateN", tr.RateN),
                   new SqlParameter("@RateS", tr.RateS),
                   new SqlParameter("@RateA", tr.RateA),
                   new SqlParameter("@RateC", tr.RateC),
                   new SqlParameter("@RateT", tr.RateT),
                   new SqlParameter("@RateBL", tr.RateBL),
                   new SqlParameter("@RateHB", tr.RateHB),
                   new SqlParameter("@RateFJD", tr.RateFJD),
                   new SqlParameter("@RateTL", tr.RateTL),
                   new SqlParameter("@RateRP", tr.RateRP),
                   new SqlParameter("@RatePeso", tr.RatePeso),
                   new SqlParameter("@RateMYR", tr.RateMYR),
                   new SqlParameter("@RateCZK", tr.RateCZK),
                   new SqlParameter("@RateMXN", tr.RateMXN),
                   new SqlParameter("@RateMOP", tr.RateMOP),
                   new SqlParameter("@RateARS", tr.RateARS),
                   new SqlParameter("@RateHUF", tr.RateHUF),
                   new SqlParameter("@RateRUB", tr.RateRUB)) > 0)
                return true;
            return false;
        }

        /// <summary>
        /// 修改团组汇率
        /// </summary>
        /// <param name="tr"></param>
        /// <returns></returns>
        public bool EditTeamRate(TeamRate tr)
        {

            // if (SqlHelper.ExecuteNonQuery("update TeamRate set RateU = @RateU,RateE = @RateE,OtherRate=@OtherRate,OtherPrice=@OtherPrice where ID=@ID", CommandType.Text, new SqlParameter("@RateU", tr.RateU), new SqlParameter("@RateE", tr.RateE), new SqlParameter("@OtherRate", tr.OtherRate), new SqlParameter("@OtherPrice", tr.OtherPrice), new SqlParameter("@ID", tr.ID)) > 0)


            if (SqlHelper.ExecuteNonQuery("update TeamRate set OPer=@OPer,OPDate=@OPDate,OtherRate=@OtherRate,OtherPrice=@OtherPrice,RateU = @RateU,RateE = @RateE,RateJ = @RateJ," +
                                            "RateH =@RateH,RateN = @RateN,RateS = @RateS,RateA = @RateA,RateC = @RateC,RateT = @RateT,RateBL=@RateBL,RateHB = @RateHB," +
                                            "RateFJD = @RateFJD,RateTL =@RateTL,RateRP =@RateRP,RatePeso=@RatePeso,RateMYR =@RateMYR,RateCZK =@RateCZK ,RateMXN=@RateMXN," +
                                            "RateMOP=@RateMOP,RateARS=@RateARS,RateHUF=@RateHUF,RateRUB=@RateRUB where ID=@ID",
                    CommandType.Text,
                    new SqlParameter("@OPer", tr.OPer),
                    new SqlParameter("@OPDate", tr.OPDate),
                    new SqlParameter("@OtherRate", tr.OtherRate),
                    new SqlParameter("@OtherPrice", tr.OtherPrice),
                    new SqlParameter("@RateU", tr.RateU),
                    new SqlParameter("@RateE", tr.RateE),
                    new SqlParameter("@RateJ", tr.RateJ),
                    new SqlParameter("@RateH", tr.RateH),
                    new SqlParameter("@RateN", tr.RateN),
                    new SqlParameter("@RateS", tr.RateS),
                    new SqlParameter("@RateA", tr.RateA),
                    new SqlParameter("@RateC", tr.RateC),
                    new SqlParameter("@RateT", tr.RateT),
                    new SqlParameter("@RateBL", tr.RateBL),
                    new SqlParameter("@RateHB", tr.RateHB),
                    new SqlParameter("@RateFJD", tr.RateFJD),
                    new SqlParameter("@RateTL", tr.RateTL),
                    new SqlParameter("@RateRP", tr.RateRP),
                    new SqlParameter("@RatePeso", tr.RatePeso),
                    new SqlParameter("@RateMYR", tr.RateMYR),
                    new SqlParameter("@RateCZK", tr.RateCZK),
                    new SqlParameter("@RateMXN", tr.RateMXN),
                    new SqlParameter("@RateMOP", tr.RateMOP),
                    new SqlParameter("@RateARS", tr.RateARS),
                    new SqlParameter("@RateHUF", tr.RateHUF),
                    new SqlParameter("@RateRUB", tr.RateRUB),
                    new SqlParameter("@ID", tr.ID)) > 0)
                return true;
            return false;
        }
    }
}