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 VisaCustomerService
    {
        List<VisaCustomer> excuteSql(string sql, params SqlParameter[] param)
        {
            return ServiceBase<VisaCustomer>.excuteSql(new VisaCustomer(), "VisaCustomer", sql, CommandType.Text, param);
        }
        VisaCustomer excuteType(string sql, params SqlParameter[] param)
        {
            //查询结果放入对象集合
            List<VisaCustomer> hdList = excuteSql(sql, param);
            //判断集合是否为空
            if (hdList == null || hdList.Count == 0)
                //返回null
                return null;
            //返回单个对象
            return hdList[0];
        }

        //增Add
        public bool Add(VisaCustomer Dov, out int id)
        {
            string sql = "insert into VisaCustomer values(@Diid,@Type,@LastName,@FirstName,@OldName,@Marriage,@Phone,@BirthCity,@BirthProvince,"
            + "@BirthDay,@Tel,@Email,@IdCard,@IdStartDate,@IdEndDate,@IdCardAddress,@Address,@HighestEducation,@PostCodes,@Client,@ClientAddress,@ClientPostCodes,@Job,@WorkDate,@Wage,@ClientPhone,"
            + "@ClientFax,@ClientEmail,@ClientLeader,@ClientLeaderJob,@WorkState,@IsGetSchengen,@StartTime,@EndTime,"
            + "@IsFinger,@FingerDate,@CostBearers,@TableOpName,@TableOpTel,@TableDate,"
            + "@Party,@Nationality,@Sex,@WeddingDate,@DivorceDate,@MateName,@MateBirthDay,@MateBirthCity,@MateBirthCountry,@MateAddress,@MateClient,@MateClientAddress,"
            + "@MateJob,@VisitCountry,@Paper,@Papent,@PhD,@IsVisitUC,@USADate,@USADays,@CanDate,@CanDays,@IsUSAVia,@GetUSAVisaDate,@GetUPPlace,"
            + "@USAVisaCate,@USAVisaCode,@USAFinger,@IsRejected,@RejectedDate,@RejectedPlace,@RejectedVisa,@IsRevoke,@IsLose,@LoseDate,@LoseCode,@IsUSAVisa,"
            + "@MateUSA,@MateIden,@WHUSA,@WHIden,@ParentUSA,@ParentIden,@ChildUSA,@ChildIden,@BroUSA,@BroIden,@Social,@IsArmy,@ArmyState,@ApplyDate,"
            + "@Oper,@OpDate,@passportNumber,@Isdel);SELECT @@IDENTITY";

            SqlParameter[] parameter = new SqlParameter[]{
                            new SqlParameter("Diid",Dov.Diid),
                            new SqlParameter("Type",Dov.Type),
                            new SqlParameter("LastName",Dov.LastName),
                            new SqlParameter("FirstName",Dov.FirstName),
                            new SqlParameter("OldName",Dov.OldName),
                            new SqlParameter("Marriage",Dov.Marriage),
                            new SqlParameter("Phone",Dov.Phone),
                            new SqlParameter("BirthCity",Dov.BirthCity),
                            new SqlParameter("BirthProvince",Dov.BirthProvince),
                            new SqlParameter("BirthDay",Dov.BirthDay),
                            new SqlParameter("Tel",Dov.Tel),
                            new SqlParameter("Email",Dov.Email),
                            new SqlParameter("IdCard",Dov.IdCard),
                            new SqlParameter("IdStartDate",Dov.IdStartDate),
                            new SqlParameter("IdEndDate",Dov.IdEndDate),
                            new SqlParameter("IdCardAddress",Dov.IdCardAddress),
                            new SqlParameter("HighestEducation",Dov.HighestEducation),
                            new SqlParameter("Address",Dov.Address),
                            new SqlParameter("PostCodes",Dov.PostCodes),
                            new SqlParameter("Client",Dov.Client),
                            new SqlParameter("ClientAddress",Dov.ClientAddress),
                            new SqlParameter("ClientPostCodes",Dov.ClientPostCodes),
                            new SqlParameter("Job",Dov.Job),
                            new SqlParameter("WorkDate",Dov.WorkDate),
                            new SqlParameter("Wage",Dov.Wage),
                            new SqlParameter("ClientPhone",Dov.ClientPhone),
                            new SqlParameter("ClientFax",Dov.ClientFax),
                            new SqlParameter("ClientEmail",Dov.ClientEmail),
                            new SqlParameter("ClientLeader",Dov.ClientLeader),
                            new SqlParameter("ClientLeaderJob",Dov.ClientLeaderJob),
                            new SqlParameter("WorkState",Dov.WorkState),
                            new SqlParameter("IsGetSchengen",Dov.IsGetSchengen),
                            new SqlParameter("StartTime",Dov.StartTime),
                            new SqlParameter("EndTime",Dov.EndTime),
                            new SqlParameter("IsFinger",Dov.IsFinger),
                            new SqlParameter("FingerDate",Dov.FingerDate),
                            new SqlParameter("CostBearers",Dov.CostBearers),
                            new SqlParameter("TableOpName",Dov.TableOpName),
                            new SqlParameter("TableOpTel",Dov.TableOpTel),
                            new SqlParameter("TableDate",Dov.TableDate),
                            new SqlParameter("Party",Dov.Party),
                            new SqlParameter("Nationality",Dov.Nationality),
                            new SqlParameter("Sex",Dov.Sex),
                            new SqlParameter("WeddingDate",Dov.WeddingDate),
                            new SqlParameter("DivorceDate",Dov.DivorceDate),
                            new SqlParameter("MateName",Dov.MateName),
                            new SqlParameter("MateBirthDay",Dov.MateBirthDay),
                            new SqlParameter("MateBirthCity",Dov.MateBirthCity),
                            new SqlParameter("MateBirthCountry",Dov.MateBirthCountry),
                            new SqlParameter("MateAddress",Dov.MateAddress),
                            new SqlParameter("MateClient",Dov.MateClient),
                            new SqlParameter("MateClientAddress",Dov.MateClientAddress),
                            new SqlParameter("MateJob",Dov.MateJob),
                            new SqlParameter("VisitCountry",Dov.VisitCountry),
                            new SqlParameter("Paper",Dov.Paper),
                            new SqlParameter("Papent",Dov.Papent),
                            new SqlParameter("PhD",Dov.PhD),
                            new SqlParameter("IsVisitUC",Dov.IsVisitUC),
                            new SqlParameter("USADate",Dov.USADate),
                            new SqlParameter("USADays",Dov.USADays),
                            new SqlParameter("CanDate",Dov.CanDate),
                            new SqlParameter("CanDays",Dov.CanDays),
                            new SqlParameter("IsUSAVia",Dov.IsUSAVia),
                            new SqlParameter("GetUSAVisaDate",Dov.GetUSAVisaDate),
                            new SqlParameter("GetUPPlace",Dov.GetUPPlace),
                            new SqlParameter("USAVisaCate",Dov.USAVisaCate),
                            new SqlParameter("USAVisaCode",Dov.USAVisaCode),
                            new SqlParameter("USAFinger",Dov.USAFinger),
                            new SqlParameter("IsRejected",Dov.IsRejected),
                            new SqlParameter("RejectedDate",Dov.RejectedDate),
                            new SqlParameter("RejectedPlace",Dov.RejectedPlace),
                            new SqlParameter("RejectedVisa",Dov.RejectedVisa),
                            new SqlParameter("IsRevoke",Dov.IsRevoke),
                            new SqlParameter("IsLose",Dov.IsLose),
                            new SqlParameter("LoseDate",Dov.LoseDate),
                            new SqlParameter("LoseCode",Dov.LoseCode),
                            new SqlParameter("IsUSAVisa",Dov.IsUSAVisa),
                            new SqlParameter("MateUSA",Dov.MateUSA),
                            new SqlParameter("MateIden",Dov.MateIden),
                            new SqlParameter("WHUSA",Dov.WHUSA),
                            new SqlParameter("WHIden",Dov.WHIden),
                            new SqlParameter("ParentUSA",Dov.ParentUSA),
                            new SqlParameter("ParentIden",Dov.ParentIden),
                            new SqlParameter("ChildUSA",Dov.ChildUSA),
                            new SqlParameter("ChildIden",Dov.ChildIden),
                            new SqlParameter("BroUSA",Dov.BroUSA),
                            new SqlParameter("BroIden",Dov.BroIden),
                            new SqlParameter("Social",Dov.Social),
                            new SqlParameter("IsArmy",Dov.IsArmy),
                            new SqlParameter("ArmyState",Dov.ArmyState),
                            new SqlParameter("ApplyDate",Dov.ApplyDate),
                            new SqlParameter("Oper",Dov.Oper),
                            new SqlParameter("OpDate",Dov.OpDate),
                            new SqlParameter("@passportNumber",Dov.passportNumber),
                            new SqlParameter("Isdel",Dov.Isdel)
            };

            int obj = Convert.ToInt32(SqlHelper.ExecuteScalar(sql, CommandType.Text, parameter));

            if (obj > 0)
            {
                id = obj;
                return true;
            }
            id = 0;
            return false;
        }
        //改Update
        public bool Edit(VisaCustomer Dov)
        {
            string sql = "update VisaCustomer set Type=@Type,LastName=@LastName,FirstName=@FirstName,OldName=@OldName,Marriage=@Marriage,Phone=@Phone,"
                + "BirthCity=@BirthCity,BirthProvince=@BirthProvince,BirthDay=@BirthDay,Tel=@Tel,Email=@Email,IdCard=@IdCard,IdStartDate=@IdStartDate,IdEndDate=@IdEndDate,"
                + "IdCardAddress=@IdCardAddress,HighestEducation=@HighestEducation,Address=@Address,PostCodes=@PostCodes,"
                + "Client=@Client,ClientAddress=@ClientAddress,ClientPostCodes=@ClientPostCodes,Job=@Job,WorkDate=@WorkDate,Wage=@Wage,"
                + "ClientPhone=@ClientPhone,ClientFax=@ClientFax,ClientEmail=@ClientEmail,ClientLeader=@ClientLeader,"
                + "ClientLeaderJob=@ClientLeaderJob,WorkState=@WorkState,IsGetSchengen=@IsGetSchengen,StartTime=@StartTime,EndTime=@EndTime,"
                + "IsFinger=@IsFinger,FingerDate=@FingerDate,CostBearers=@CostBearers,"
                + "TableOpName=@TableOpName,TableOpTel=@TableOpTel,TableDate=@TableDate,Party=@Party,Nationality=@Nationality,"
                + "Sex=@Sex,WeddingDate=@WeddingDate,DivorceDate=@DivorceDate,MateName=@MateName,MateBirthDay=@MateBirthDay,MateBirthCity=@MateBirthCity,"
                + "MateBirthCountry=@MateBirthCountry,MateAddress=@MateAddress,MateClient=@MateClient,MateClientAddress=@MateClientAddress,MateJob=@MateJob,"
                + "VisitCountry=@VisitCountry,Paper=@Paper,Papent=@Papent,PhD=@PhD,IsVisitUC=@IsVisitUC,USADate=@USADate,USADays=@USADays,CanDate=@CanDate,"
                + "CanDays=@CanDays,IsUSAVia=@IsUSAVia,GetUSAVisaDate=@GetUSAVisaDate,GetUPPlace=@GetUPPlace,USAVisaCate=@USAVisaCate,USAVisaCode=@USAVisaCode,USAFinger=@USAFinger,"
                + "IsRejected=@IsRejected,RejectedDate=@RejectedDate,RejectedPlace=@RejectedPlace,RejectedVisa=@RejectedVisa,IsRevoke=@IsRevoke,IsLose=@IsLose,"
                + "LoseDate=@LoseDate,LoseCode=@LoseCode,IsUSAVisa=@IsUSAVisa,MateUSA=@MateUSA,MateIden=@MateIden,WHUSA=@WHUSA,WHIden=@WHIden,ParentUSA=@ParentUSA,"
                + "ParentIden=@ParentIden,ChildUSA=@ChildUSA,ChildIden=@ChildIden,BroUSA=@BroUSA,BroIden=@BroIden,Social=@Social,IsArmy=@IsArmy,ArmyState=@ArmyState,"
                + "ApplyDate=@ApplyDate,Oper=@Oper,OpDate=@OpDate,passportNumber=@passportNumber,Isdel=@Isdel where Id = @Id";

            SqlParameter[] parameter = new SqlParameter[]{
                            new SqlParameter("Type",Dov.Type),
                            new SqlParameter("LastName",Dov.LastName),
                            new SqlParameter("FirstName",Dov.FirstName),
                            new SqlParameter("OldName",Dov.OldName),
                            new SqlParameter("Marriage",Dov.Marriage),
                            new SqlParameter("Phone",Dov.Phone),
                            new SqlParameter("BirthCity",Dov.BirthCity),
                            new SqlParameter("BirthProvince",Dov.BirthProvince),
                            new SqlParameter("BirthDay",Dov.BirthDay),
                            new SqlParameter("Tel",Dov.Tel),
                            new SqlParameter("Email",Dov.Email),
                            new SqlParameter("IdCard",Dov.IdCard),
                            new SqlParameter("IdStartDate",Dov.IdStartDate),
                            new SqlParameter("IdEndDate",Dov.IdEndDate),
                            new SqlParameter("IdCardAddress",Dov.IdCardAddress),
                            new SqlParameter("HighestEducation",Dov.HighestEducation),
                            new SqlParameter("Address",Dov.Address),
                            new SqlParameter("PostCodes",Dov.PostCodes),
                            new SqlParameter("Client",Dov.Client),
                            new SqlParameter("ClientAddress",Dov.ClientAddress),
                            new SqlParameter("ClientPostCodes",Dov.ClientPostCodes),
                            new SqlParameter("Job",Dov.Job),
                            new SqlParameter("WorkDate",Dov.WorkDate),
                            new SqlParameter("Wage",Dov.Wage),
                            new SqlParameter("ClientPhone",Dov.ClientPhone),
                            new SqlParameter("ClientFax",Dov.ClientFax),
                            new SqlParameter("ClientEmail",Dov.ClientEmail),
                            new SqlParameter("ClientLeader",Dov.ClientLeader),
                            new SqlParameter("ClientLeaderJob",Dov.ClientLeaderJob),
                            new SqlParameter("WorkState",Dov.WorkState),
                            new SqlParameter("IsGetSchengen",Dov.IsGetSchengen),
                            new SqlParameter("StartTime",Dov.StartTime),
                            new SqlParameter("EndTime",Dov.EndTime),
                            new SqlParameter("IsFinger",Dov.IsFinger),
                            new SqlParameter("FingerDate",Dov.FingerDate),
                            new SqlParameter("CostBearers",Dov.CostBearers),
                            new SqlParameter("TableOpName",Dov.TableOpName),
                            new SqlParameter("TableOpTel",Dov.TableOpTel),
                            new SqlParameter("TableDate",Dov.TableDate),
                            new SqlParameter("Party",Dov.Party),
                            new SqlParameter("Nationality",Dov.Nationality),
                            new SqlParameter("Sex",Dov.Sex),
                            new SqlParameter("WeddingDate",Dov.WeddingDate),
                            new SqlParameter("DivorceDate",Dov.DivorceDate),
                            new SqlParameter("MateName",Dov.MateName),
                            new SqlParameter("MateBirthDay",Dov.MateBirthDay),
                            new SqlParameter("MateBirthCity",Dov.MateBirthCity),
                            new SqlParameter("MateBirthCountry",Dov.MateBirthCountry),
                            new SqlParameter("MateAddress",Dov.MateAddress),
                            new SqlParameter("MateClient",Dov.MateClient),
                            new SqlParameter("MateClientAddress",Dov.MateClientAddress),
                            new SqlParameter("MateJob",Dov.MateJob),
                            new SqlParameter("VisitCountry",Dov.VisitCountry),
                            new SqlParameter("Paper",Dov.Paper),
                            new SqlParameter("Papent",Dov.Papent),
                            new SqlParameter("PhD",Dov.PhD),
                            new SqlParameter("IsVisitUC",Dov.IsVisitUC),
                            new SqlParameter("USADate",Dov.USADate),
                            new SqlParameter("USADays",Dov.USADays),
                            new SqlParameter("CanDate",Dov.CanDate),
                            new SqlParameter("CanDays",Dov.CanDays),
                            new SqlParameter("IsUSAVia",Dov.IsUSAVia),
                            new SqlParameter("GetUSAVisaDate",Dov.GetUSAVisaDate),
                            new SqlParameter("GetUPPlace",Dov.GetUPPlace),
                            new SqlParameter("USAVisaCate",Dov.USAVisaCate),
                            new SqlParameter("USAVisaCode",Dov.USAVisaCode),
                            new SqlParameter("USAFinger",Dov.USAFinger),
                            new SqlParameter("IsRejected",Dov.IsRejected),
                            new SqlParameter("RejectedDate",Dov.RejectedDate),
                            new SqlParameter("RejectedPlace",Dov.RejectedPlace),
                            new SqlParameter("RejectedVisa",Dov.RejectedVisa),
                            new SqlParameter("IsRevoke",Dov.IsRevoke),
                            new SqlParameter("IsLose",Dov.IsLose),
                            new SqlParameter("LoseDate",Dov.LoseDate),
                            new SqlParameter("LoseCode",Dov.LoseCode),
                            new SqlParameter("IsUSAVisa",Dov.IsUSAVisa),
                            new SqlParameter("MateUSA",Dov.MateUSA),
                            new SqlParameter("MateIden",Dov.MateIden),
                            new SqlParameter("WHUSA",Dov.WHUSA),
                            new SqlParameter("WHIden",Dov.WHIden),
                            new SqlParameter("ParentUSA",Dov.ParentUSA),
                            new SqlParameter("ParentIden",Dov.ParentIden),
                            new SqlParameter("ChildUSA",Dov.ChildUSA),
                            new SqlParameter("ChildIden",Dov.ChildIden),
                            new SqlParameter("BroUSA",Dov.BroUSA),
                            new SqlParameter("BroIden",Dov.BroIden),
                            new SqlParameter("Social",Dov.Social),
                            new SqlParameter("IsArmy",Dov.IsArmy),
                            new SqlParameter("ArmyState",Dov.ArmyState),
                            new SqlParameter("ApplyDate",Dov.ApplyDate),
                            new SqlParameter("Oper",Dov.Oper),
                            new SqlParameter("OpDate",Dov.OpDate),
                            new SqlParameter("Isdel",Dov.Isdel),
                            new SqlParameter("@passportNumber",Dov.passportNumber),
                            new SqlParameter("Id",Dov.Id)
            };

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


        public List<VisaCustomer> 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 (LastName like '%" + name[0] + "%' or FirstName like '%" + name[1] + "%')";

            return PageBase<VisaCustomer>.excutePageSql(new VisaCustomer(), "VisaCustomer", "VisaCustomer", "*", "id asc", sqlwhere, 20, pageIndex, out sumPage, out totalRecord);

        }

        public List<VisaCustomer> GetByDiid(int diid)
        {
            return excuteSql("select * from VisaCustomer where diid=" + diid + " and Isdel=0;");
        }


        /// <summary>
        /// 
        /// </summary>
        /// <param name="LastName">姓</param>
        /// <param name="Name">名</param>
        /// <param name="Tel">电话</param>
        /// <returns></returns>
        public VisaCustomer GetByTypeNameTel(string LastName, string Name,  string Tel)
        {

            return excuteType("select * from VisaCustomer where IsDel = 0 and FirstName = '" + Name + "' and LastName = '" + LastName + "' and Tel='" + Tel + "'");
        }

        public VisaCustomer GetByName( string LastName, string FirstName)
        {
            return excuteType("select * from VisaCustomer where LastName='"+LastName+"' and FirstName='"+FirstName+"' and Isdel=0;");
        }


        public VisaCustomer GetByDiidAndName(int Diid,string LastName, string FirstName)
        {
            return excuteType("select * from VisaCustomer where Diid=" + Diid + " and LastName='" + LastName + "' and FirstName='" + FirstName + "' and Isdel=0;");
        }

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

        }

        /// <summary>
        /// 按签证表类型删除
        /// </summary>
        /// <param name="id"></param>
        /// <param name="type"></param>
        /// <returns></returns>
        public bool DelByType(int id,int type)
        {
            string sql = "update VisaCustomer set Isdel=1 where Id=@Id and Type=@Type";

            SqlParameter[] parameter = new SqlParameter[]{
                            new SqlParameter("Type",type),
                            new SqlParameter("Id",id)
            };

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

        public VisaCustomer GetByID(int id)
        {

            return excuteType("select * from VisaCustomer where IsDel = 0 and Id = " + id);

        }
        /// <summary>
        /// 根据有编号修改diid
        /// </summary>
        /// <param name="id">id</param>
        /// <param name="diid">diid</param>
        public bool GetUpdateDiid(int id, int diid)
        {
            if ((SqlHelper.ExecuteNonQuery("update VisaCustomer set Diid = @diid where Id=@Id", CommandType.Text, new SqlParameter("@diid", diid), new SqlParameter("@Id", id))) > 0)
                return true;
            return false;

        }

        /// <summary>
        /// 用于签证客户表检索功能
        /// 操作者：雷怡  
        /// 操作时间：2021.7.15 17:52
        /// </summary>
        /// <param name="LastName">姓</param>
        /// <param name="FirstName">名</param>
        /// <param name="IdCard">身份证</param>
        /// <param name="id">条件查询的数据编号</param>
        /// <returns></returns>
        public List<VisaCustomer> GetByNameAndCard(string LastName, string FirstName, string IdCard, int id)
        {
            string sql = "select * from VisaCustomer where Isdel=0 and id != '" + id + "' ";

            if (LastName != "" || LastName != null)
            {
                sql += " and LastName = '" + LastName + "' and FirstName = '" + FirstName + "'";
            }

            sql += " and Idcard = '" + IdCard + "'";

            return excuteSql(sql);

        }


        /// <summary>
        /// 身份证查询信息
        /// </summary>
        /// <param name="IdCard"></param>
        /// <returns></returns>
        public List<VisaCustomer> GetByNameAndCard(string IdCard)
        {
            string sql = "select * from VisaCustomer where Isdel=0 and Idcard = '" + IdCard + "' ";
            return excuteSql(sql);

        }





        /// <summary>
        /// 雷怡 2021-08-31 11：28
        /// 查询所有
        /// </summary>
        /// <returns></returns>
        public List<VisaCustomer> GetAll()
        {
            return excuteSql("select * from VisaCustomer where Isdel=0;");
        }
    }
}