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


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

       /// <summary>
       /// 增加
       /// </summary>
       /// <returns></returns>
       public bool AddCustomerVisaInfo(CustomerVisaInfo c)
       {
           string sql = "insert into CustomerVisaInfo values(@DIId,@Name,@Sex,@PhoneNumber,@Dirthday,@NativePlace,@CardId,@PassportNo,@PassportsValidity,@UnitName,@Post,@UnitAddress,@HomeAddress,@FiveYearsAgo,@Operators,@OperatorDate,@VId,@IsDel,@Remark)";

           SqlParameter[] parameter = new SqlParameter[]{
                new SqlParameter("@DIId",c.DIId),
                new SqlParameter("@Name",c.Name),
                new SqlParameter("@Sex",c.Sex),
                new SqlParameter("@PhoneNumber",c.PhoneNumber),
                new SqlParameter("@Dirthday",c.Dirthday),
                new SqlParameter("@NativePlace",c.NativePlace),
                new SqlParameter("@CardId",c.CardId),
                new SqlParameter("@PassportNo",c.PassportNo),                
                new SqlParameter("@PassportsValidity",c.PassportsValidity),
                new SqlParameter("@UnitName",c.UnitName),
                new SqlParameter("@Post",c.Post),
                new SqlParameter("@UnitAddress",c.UnitAddress),
                new SqlParameter("@HomeAddress",c.HomeAddress),
                new SqlParameter("@FiveYearsAgo",c.FiveYearsAgo),
                new SqlParameter("@Operators",c.Operators),
                new SqlParameter("@OperatorDate",c.OperatorDate),
                new SqlParameter("@VId",c.VId),
                new SqlParameter("@IsDel",c.IsDel),
                new SqlParameter("@Remark",c.Remark)
            };
           if (SqlHelper.ExecuteNonQuery(sql, CommandType.Text, parameter) > 0)
               return true;
           return false;          
       }

       /// <summary>
       /// 获取全部 - 分页
       /// </summary>
       /// <returns></returns>
       public List<CustomerVisaInfo> GetCustomerVisaInfo(int pageIndex, out int sumPage, out int totalRecord, string tourCode, string visaClient, string arrayUsersId)
       {
           string sqlwhere = "IsDel = 0 and DIId = '" + tourCode + "' and Name like '%" + visaClient + "%' and Operators in (" + arrayUsersId + ")";

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

       public bool Del(int id)
       {
           if (SqlHelper.ExecuteNonQuery("update CustomerVisaInfo set IsDel = 1 where Id = @Id", CommandType.Text, new SqlParameter("@Id", id)) > 0)
               return true;
           return false;
       }

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


       public CustomerVisaInfo GetCustomerVisaInfoByIdList(int id, int diid)
       {
           string sql = "select * from CustomerVisaInfo where id =@id and diid = @diid and isdel=0 order by id asc";
           SqlParameter[] parameter = new SqlParameter[]{
                new SqlParameter("@id",id),
                new SqlParameter("@diid",diid)
            };
           return excuteType(sql, parameter);
       }


       public List<CustomerVisaInfo> GetCustomerVisaInfoByDiidList(int diid)
       {
           string sql = "select * from CustomerVisaInfo where diid = @diid and isdel=0 order by id asc";
           SqlParameter[] parameter = new SqlParameter[]{
                new SqlParameter("@diid",diid)
            };
           return excuteSql(sql, parameter);
       }

       //public CustomerVisaInfo GetCustomerVisaInfoByDiidList(int diid)
       //{
       //    string sql = "select * from CustomerVisaInfo where diid = @diid and isdel=0 order by id asc";
       //    SqlParameter[] parameter = new SqlParameter[]{
       //         new SqlParameter("@diid",diid)
       //     };
       //    return excuteType(sql, parameter);
       //}

       public CustomerVisaInfo GetCustomerVisaInfoByVIdList(int diid, int vid)
       {
           string sql = "select * from CustomerVisaInfo where DIId =@diid and VId = @vid and isdel=0 order by id asc";
           SqlParameter[] parameter = new SqlParameter[]{
                new SqlParameter("@DIId",diid),
                new SqlParameter("@VId",vid)
            };
           return excuteType(sql, parameter);
       }

       public CustomerVisaInfo GetCustomerVisaInfoByVisaClientList(string name)
       {
           string sql = "select * from CustomerVisaInfo where Name =@name and isdel=0 order by id desc";
           SqlParameter[] parameter = new SqlParameter[]{
                                new SqlParameter("@Name",name)
            };          
           return excuteType(sql, parameter);
       }

       /// <summary>
       /// 编辑
       /// </summary>
       /// <returns></returns>
       public bool EditCustomerVisaInfo(CustomerVisaInfo c)
       {
           string sql = "update CustomerVisaInfo set Name = @Name,Sex = @Sex,PhoneNumber = @PhoneNumber,Dirthday = @Dirthday,NativePlace = @NativePlace,CardId = @CardId,PassportNo = @PassportNo,PassportsValidity = @PassportsValidity,UnitName = @UnitName,Post = @Post,UnitAddress = @UnitAddress,HomeAddress = @HomeAddress,FiveYearsAgo = @FiveYearsAgo,Operators = @Operators,OperatorDate = @OperatorDate,VId = @VId,Remark = @Remark where Id = @Id";

           SqlParameter[] parameter = new SqlParameter[]{
                new SqlParameter("@Name",c.Name),
                new SqlParameter("@Sex",c.Sex),
                new SqlParameter("@PhoneNumber",c.PhoneNumber),
                new SqlParameter("@Dirthday",c.Dirthday),
                new SqlParameter("@NativePlace",c.NativePlace),
                new SqlParameter("@CardId",c.CardId),
                new SqlParameter("@PassportNo",c.PassportNo),                
                new SqlParameter("@PassportsValidity",c.PassportsValidity),
                new SqlParameter("@UnitName",c.UnitName),
                new SqlParameter("@Post",c.Post),
                new SqlParameter("@UnitAddress",c.UnitAddress),
                new SqlParameter("@HomeAddress",c.HomeAddress),
                new SqlParameter("@FiveYearsAgo",c.FiveYearsAgo),
                new SqlParameter("@Operators",c.Operators),
                new SqlParameter("@OperatorDate",c.OperatorDate),
                new SqlParameter("@VId",c.VId),
                new SqlParameter("@Remark",c.Remark),
                new SqlParameter("@Id",c.Id)
           };

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

    }
}