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

        //增Add
        public bool Add(VisaCustomerFamily Dov)
        {
            string sql = "insert into VisaCustomerFamily values(@DCId,@Appellation,@Name,@BirthDay,@BirthPlace,@Politics,@Client,@Address,@IsEu,@NameSnd,@BirthDaySnd,@Nationality,@IDCard,@Reletionship,@IsUSA);SELECT @@IDENTITY";

            SqlParameter[] parameter = new SqlParameter[]{
                new SqlParameter("@DCId",Dov.DCId),
                new SqlParameter("@Appellation",Dov.Appellation),
                new SqlParameter("@Name",Dov.Name),
                new SqlParameter("@BirthDay",Dov.BirthDay),
                new SqlParameter("@BirthPlace",Dov.BirthPlace),
                new SqlParameter("@Politics",Dov.Politics),
                new SqlParameter("@Client",Dov.Client),
                new SqlParameter("@Address",Dov.Address),
                new SqlParameter("@IsEu",Dov.IsEu),
                new SqlParameter("@NameSnd",Dov.NameSnd),
                new SqlParameter("@BirthDaySnd",Dov.BirthDaySnd),
                new SqlParameter("@Nationality",Dov.Nationality),
                new SqlParameter("@IDCard",Dov.IDCard),
                new SqlParameter("@Reletionship",Dov.Reletionship),
                new SqlParameter("@IsUSA",Dov.IsUSA)
            };

            if (SqlHelper.ExecuteNonQuery(sql, CommandType.Text, parameter) > 0)
                return true;
            return false;
        }
        //改Update
        public bool Edit(VisaCustomerFamily Dov)
        {
            string sql = "update VisaCustomerFamily set DCId=@DCId,Appellation=@Appellation,Name=@Name,BirthDay=@BirthDay,BirthPlace=@BirthPlace,Politics=@Politics,Client=@Client,Address=@Address,IsEu=@IsEu,NameSnd=@NameSnd,BirthDaySnd=@BirthDaySnd,Nationality=@Nationality,IDCard=@IDCard,Reletionship=@Reletionship,IsUSA=@IsUSA where Id = @Id";

            SqlParameter[] parameter = new SqlParameter[]{
                new SqlParameter("@DCId",Dov.DCId),
                new SqlParameter("@Appellation",Dov.Appellation),
                new SqlParameter("@Name",Dov.Name),
                new SqlParameter("@BirthDay",Dov.BirthDay),
                new SqlParameter("@BirthPlace",Dov.BirthPlace),
                new SqlParameter("@Politics",Dov.Politics),
                new SqlParameter("@Client",Dov.Client),
                new SqlParameter("@Address",Dov.Address),
                new SqlParameter("@IsEu",Dov.IsEu),
                new SqlParameter("@NameSnd",Dov.NameSnd),
                new SqlParameter("@BirthDaySnd",Dov.BirthDaySnd),
                new SqlParameter("@Nationality",Dov.Nationality),
                new SqlParameter("@IDCard",Dov.IDCard),
                new SqlParameter("@Reletionship",Dov.Reletionship),
                new SqlParameter("@IsUSA",Dov.IsUSA),
                new SqlParameter("@Id",Dov.Id)
            };

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

        public bool del(int DCID)
        {
            if (SqlHelper.ExecuteNonQuery("delete from VisaCustomerFamily where DCId=@DCId", CommandType.Text, new SqlParameter("@DCId", DCID)) > 0)
                return true;
            return false;
        }

        /// <summary>
        /// 根据客户表id查询所有家庭成员
        /// </summary>
        /// <param name="DCID"></param>
        /// <returns></returns>
        public List<VisaCustomerFamily> GetAllById(int DCID)
        {
            return excuteSql("select * from VisaCustomerFamily where DCId=@DCId  order by Id", new SqlParameter("@DCId", DCID));
        }

        public List<VisaCustomerFamily> GetNotEUByDCID(int DCID)
        {
            return excuteSql("select * from VisaCustomerFamily where DCId=@DCId and IsEu=0 order by Id", new SqlParameter("@DCId", DCID));
        }
        public List<VisaCustomerFamily> GetEUByDCID(int DCID)
        {
            return excuteSql("select * from VisaCustomerFamily where DCId=@DCId and IsEu=1 order by Id", new SqlParameter("@DCId", DCID));
        }

        public VisaCustomerFamily GetByID(int id)
        {
            return excuteType("select * from VisaCustomerFamily where Id=@Id", new SqlParameter("@Id", id));
        }
    }
}