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

namespace DAL
{
    public class Country_FeeCostServcies
    {

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

        /// <summary>
        /// 查询签证费用标准所有信息
        /// </summary>
        /// <param name="id">对象编号</param>
        /// <returns>返回空或者单个对象信息</returns>
        public List<Country_FeeCost> GetCountry_FeeCostAll()
        {
            //调用获取单个对象的方法
            return excuteSql("select * from Country_FeeCost where Isdel=0 ");
        }

        /// <summary>
        /// 查询分页（团组表和签证表关联的数据）
        /// </summary>
        /// <param name="pageIndex"></param>
        /// <param name="sumPage"></param>
        /// <param name="totalRecord"></param>
        /// <param name="Name"></param>
        /// <returns></returns>
        public List<Country_FeeCost> GetPageToAll(int pageIndex, out int sumPage, out int totalRecord, string Name,int[] ids)
        {

            string[] name = { "", "", "", "" };
            if (!string.IsNullOrEmpty(Name))
            {
                for (int i = 0; i < Name.Length; i++)
                {
                    name[i] = Name.Substring(i, 1);
                }
            }
            string sqlwhere = "isDel = 0 and in ("+ids+ ") and (CCountryName like '%" + name[0] + "%')";

            return PageBase<Country_FeeCost>.excutePageSql(new Country_FeeCost(), "Country_FeeCost", "Country_FeeCost", "*", "VisaId asc", sqlwhere, 5, pageIndex, out sumPage, out totalRecord);

        }

        /// <summary>
        /// 查询分页
        /// </summary>
        /// <param name="pageIndex"></param>
        /// <param name="sumPage"></param>
        /// <param name="totalRecord"></param>
        /// <param name="Name"></param>
        /// <returns></returns>
        public List<Country_FeeCost> GetPage(int pageIndex, out int sumPage, out int totalRecord, string Name)
        {
            string[] name = { "", "", "", "","","" };
            if (!string.IsNullOrEmpty(Name))
            {
                for (int i = 0; i < Name.Length; i++)
                {
                    name[i] = Name.Substring(i, 1);
                }
            }
            string sqlwhere = "isDel = 0 and (VisaCountry like '%" + name[0] + "%')";

            return PageBase<Country_FeeCost>.excutePageSql(new Country_FeeCost(), "Country_FeeCost", "Country_FeeCost", "* ", " VisaId asc", sqlwhere, 5, pageIndex, out sumPage, out totalRecord);

        }

        /// <summary>
        /// 获取单个对象
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <param name="param">可变参数数组</param>
        /// <returns>返回空或者单个对象</returns>
        Country_FeeCost excuteType(string sql, params SqlParameter[] param)
        {
            //查询结果放入对象集合
            List<Country_FeeCost> diList = excuteSql(sql, param);
            //判断集合是否为空
            if (diList == null || diList.Count == 0)
                //返回null
                return null;
            //返回单个对象
            return diList[0];
        }
        
        /// <summary>
        /// 根据编号查询对象信息
        /// </summary>
        /// <param name="id">对象编号</param>
        /// <returns>返回空或者单个对象信息</returns>
        public List<Country_FeeCost> GetCountry_FeeCostByID(int diid)
        {
            //调用获取单个对象的方法
            return excuteSql(@"select t2.* from  DelegationInfo t1, Country_FeeCost t2, DelegationInfoOrFeeCost t3
                                where t1.id=t3.diid and t2.VisaId = t3.cfcid and t2.isdel=0 and t3.isdel=0 and t1.id=" + diid);
        }

        /// <summary>
        /// 根据国家名查询对象信息
        /// </summary>
        /// <param name="id">对象编号</param>
        /// <returns>返回空或者单个对象信息</returns>
        public Country_FeeCost GetCountry_FeeCostByCountry(string countryName)
        {
            //调用获取单个对象的方法
            return excuteType("select * from Country_FeeCost where Isdel=0 and VisaCountry='" + countryName+"'");
        }

        /// <summary>
        /// 根据编号查询对象信息
        /// </summary>
        /// <param name="id">对象编号</param>
        /// <returns>返回空或者单个对象信息</returns>
        public Country_FeeCost GetCountry_FeeCostByIDOne(int cid)
        {
            //调用获取单个对象的方法
            return excuteType("select * from Country_FeeCost where Isdel=0 and VisaId=" + cid);
        }

        /// <summary>
        /// 修改这条签证的状态
        /// </summary>
        /// <param name="id"></param>
        /// <param name="userid"></param>
        /// <param name="datetime"></param>
        /// <returns></returns>
        public bool delCountry_FeeCost(int id, int userid, string datetime)
        {
            string sql = "update Country_FeeCost set Isdel=1, Oper=@Oper, OPDate=@OPDate where VisaId=@visaId";
            SqlParameter[] parameter = new SqlParameter[]{
                    new SqlParameter("@visaId",id),
                    new SqlParameter("@OPDate",datetime),
                    new SqlParameter("@Oper",userid)
                };
            if (SqlHelper.ExecuteNonQuery(sql, CommandType.Text, parameter) > 0)
                return true;
            return false;
        }


        ///修改签证信息
        ///
        public bool UpdateCountry_FeeCost(Country_FeeCost cf)
        {
            string sql = "update Country_FeeCost set VisaContinent=@VisaContinent,VisaCountry=@VisaCountry,IsVisaExemption=@IsVisaExemption,IsVisaOnArrival=@IsVisaOnArrival,"+
                         "IsElectronicSignature=@IsElectronicSignature,VisaPrice=@VisaPrice,VisaPriceDesc=@VisaPriceDesc,VisaTime=@VisaTime,UrgentTime=@UrgentTime,UrgentPrice=@UrgentPrice,"+
                         "UrgentPriceDesc=@UrgentPriceDesc,VisaRemark=@VisaRemark,Oper=@Oper,OPDate=@OPDate,isDel=@isDel where VisaId=@VisaId";

            SqlParameter[] parameter = new SqlParameter[]{
                    new SqlParameter("@VisaId",cf.VisaId),
                    new SqlParameter("@VisaContinent",cf.VisaContinent),
                    new SqlParameter("@VisaCountry",cf.VisaCountry),
                    new SqlParameter("@IsVisaExemption",cf.IsVisaExemption),
                    new SqlParameter("@IsVisaOnArrival",cf.IsVisaOnArrival),
                    new SqlParameter("@IsElectronicSignature",cf.IsElectronicSignature),
                    new SqlParameter("@VisaPrice",cf.VisaPrice),
                    new SqlParameter("@VisaPriceDesc",cf.VisaPriceDesc),
                    new SqlParameter("@VisaTime",cf.VisaTime),
                    new SqlParameter("@UrgentTime",cf.UrgentTime),
                    new SqlParameter("@UrgentPrice",cf.UrgentPrice),
                    new SqlParameter("@UrgentPriceDesc",cf.UrgentPriceDesc),
                    new SqlParameter("@VisaRemark",cf.VisaRemark),
                    new SqlParameter("@Oper",cf.Oper),
                    new SqlParameter("@OPDate",cf.OPDate),
                    new SqlParameter("@isDel",cf.isDel)
                };

            if (SqlHelper.ExecuteNonQuery(sql, CommandType.Text, parameter) > 0)
                return true;
            return false;
        }
        /// <summary>
        /// 更新签证表
        /// </summary>
        /// <param name="cf"></param>
        /// <returns></returns>
        public bool T0UpdateVisaFrom(Country_FeeCost cf)
        {
            string sql = "update Country_FeeCost set IsVisaExemption=@IsVisaExemption,IsVisaOnArrival=@IsVisaOnArrival,IsElectronicSignature=@IsElectronicSignature," +
                         "VisaTime=@VisaTime,VisaType=@VisaType,Oper=@Oper,OPDate=@OPDate,isDel=@isDel where VisaId=@VisaId";

            SqlParameter[] parameter = new SqlParameter[]{
                    new SqlParameter("@VisaId",cf.VisaId),
                    new SqlParameter("@IsVisaExemption",cf.IsVisaExemption),
                    new SqlParameter("@IsVisaOnArrival",cf.IsVisaOnArrival),
                    new SqlParameter("@IsElectronicSignature",cf.IsElectronicSignature),
                    new SqlParameter("@VisaTime",cf.VisaTime),
                    new SqlParameter("@VisaType",cf.VisaType),
                    new SqlParameter("@Oper",cf.Oper),
                    new SqlParameter("@OPDate",cf.OPDate),
                    new SqlParameter("@isDel",cf.isDel)
                };

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

        /// <summary>
        /// 添加签证费用标准信息
        /// </summary>
        /// <param name="Dov"></param>
        /// <returns></returns>
        public bool Add(Country_FeeCost cf)
        {
            string sql = "insert into Country_FeeCost values(@VisaContinent,@VisaCountry,@IsVisaExemption,@IsVisaOnArrival,@IsElectronicSignature,@VisaPrice,@VisaPriceDesc,@VisaType,"+
                                                            "@VisaTime,@IsUrgent,@UrgentTime,@UrgentPrice,@UrgentPriceDesc,@VisaAddress,@VisaRemark,@Oper,@OPDate,@isDel);SELECT @@IDENTITY";

            SqlParameter[] parameter = new SqlParameter[]{
                    new SqlParameter("@VisaContinent",cf.VisaContinent),
                    new SqlParameter("@VisaCountry",cf.VisaCountry),
                    new SqlParameter("@IsVisaExemption",cf.IsVisaExemption),
                    new SqlParameter("@IsVisaOnArrival",cf.IsVisaOnArrival),
                    new SqlParameter("@IsElectronicSignature",cf.IsElectronicSignature),
                    new SqlParameter("@VisaPrice",cf.VisaPrice),
                    new SqlParameter("@VisaPriceDesc",cf.VisaPriceDesc),
                    new SqlParameter("@VisaType",cf.VisaType),
                    new SqlParameter("@VisaTime",cf.VisaTime),
                    new SqlParameter("@IsUrgent",cf.IsUrgent),
                    new SqlParameter("@UrgentTime",cf.UrgentTime),
                    new SqlParameter("@UrgentPrice",cf.UrgentPrice),
                    new SqlParameter("@UrgentPriceDesc",cf.UrgentPriceDesc),
                    new SqlParameter("@VisaAddress",cf.VisaAddress),
                    new SqlParameter("@VisaRemark",cf.VisaRemark),
                    new SqlParameter("@Oper",cf.Oper),
                    new SqlParameter("@OPDate",cf.OPDate),
                    new SqlParameter("@isDel",cf.isDel)
                };

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

    }
}