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

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

        /// <summary>
        /// 根据ID查询
        /// </summary>
        /// <returns></returns>
        public DeleClient GetById(int id)
        {
            return excuteType("select * from DeleClient where Isdel=0 and Id=" + id);
        }
        /// <summary>
        /// 根据姓和名查询
        /// </summary>
        /// <param name="LastName">姓</param>
        /// <param name="FirstName">名</param>
        /// <returns></returns>
        public DeleClient GetByName(string LastName, string FirstName)
        {
            return excuteType("select * from DeleClient where Isdel=0 and LastName='" + LastName + "' and Name='" + FirstName + "';");
        }

        /// <summary>
        /// 根据姓和名查询
        /// </summary>
        /// <param name="LastName">姓</param>
        /// <param name="FirstName">名</param>
        /// <returns></returns>
        public DeleClient GetByDiidAndName(int Diid, string LastName, string FirstName)
        {
            return excuteType("select * from DeleClient where Isdel=0 and Diid=" + Diid + " and LastName='" + LastName + "' and Name='" + FirstName + "';");
        }

        /// <summary>
        /// 查询所有
        /// </summary>
        /// <returns></returns>
        public List<DeleClient> GetAll()
        {
            return excuteSql("select * from DeleClient where Isdel=0 ");
        }

        /// <summary>
        /// 按团组查询所有
        /// </summary>
        /// <param name="DIID"></param>
        /// <returns></returns>
        public List<DeleClient> GetAll(int DIID)
        {
            return excuteSql("select * from DeleClient where Isdel=0 and Diid=" + DIID + " order by Id");
        }

        /// <summary>
        /// 按团组编号查询所有
        /// </summary>
        /// <param name="DIID"></param>
        /// <returns></returns>
        public List<DeleClient> GetByDiid(int DIID)
        {
            return excuteSql("select * from DeleClient where Isdel=0 and Diid=" + DIID);
        }

        /// <summary>
        /// 按团组编号查询所有
        /// </summary>
        /// <param name="DIID"></param>
        /// <returns></returns>
        public List<DeleClient> GetByDiid(int DIID, int typeId)
        {
            return excuteSql("select * from DeleClient where Isdel=0 and Diid=" + DIID + " and Type=" + typeId);
        }
        /// <summary>
        /// 按团组查询所有
        /// </summary>
        /// <param name="DIID"></param>
        /// <returns></returns>
        public List<DeleClient> GetAll(int DIID, String airtype)
        {
            return excuteSql("select * from DeleClient where Isdel=0 and Diid=" + DIID + " and AirType='" + airtype + "'");
        }

        //增Add
        public bool Add(DeleClient Dov)
        {
            string sql = "insert into DeleClient values(@Diid,@Name,@LastName,@Pinyin,@Company,@GroupUnit,@Job,@Phone,@Sex,@IDcard,@PassprotType,@PassportNo,@passportCountry,@IssuePlace,@IssueDate,@ExpiryDate,"
                        + "@Birthday,@AirType,@AirRemark,@RoomType,@Remark,@OPer,@OPdate,@Isdel);SELECT @@IDENTITY";

            SqlParameter[] parameter = new SqlParameter[]{
                    new SqlParameter("@Diid",Dov.Diid),
                    new SqlParameter("@Name",Dov.Name),
                    new SqlParameter("@LastName",Dov.LastName),
                    new SqlParameter("@Pinyin",Dov.Pinyin),
                    new SqlParameter("@Company",Dov.Company),
                    new SqlParameter("@GroupUnit",Dov.GroupUnit),
                    new SqlParameter("@Job",Dov.Job),
                    new SqlParameter("@Phone",Dov.Phone),
                    new SqlParameter("@Sex",Dov.Sex),
                    new SqlParameter("@IDcard",Dov.IDcard),
                    new SqlParameter("@PassprotType",Dov.PassprotType),
                    new SqlParameter("@PassportNo",Dov.PassportNo),
                    new SqlParameter("@passportCountry",Dov.passportCountry),
                    new SqlParameter("@IssuePlace",Dov.IssuePlace),
                    new SqlParameter("@IssueDate",Dov.IssueDate),
                    new SqlParameter("@ExpiryDate",Dov.ExpiryDate),
                    new SqlParameter("@Birthday",Dov.Birthday),
                    new SqlParameter("@AirType",Dov.AirType),
                    new SqlParameter("@AirRemark",Dov.AirRemark),
                    new SqlParameter("@RoomType",Dov.RoomType),
                    new SqlParameter("@Remark",Dov.Remark),
                    new SqlParameter("@OPer",Dov.OPer),
                    new SqlParameter("@OPdate",Dov.OPdate),
                    new SqlParameter("@Isdel",Dov.Isdel)
                };

            if (SqlHelper.ExecuteNonQuery(sql, CommandType.Text, parameter) > 0)
                return true;
            return false;
        }
        //改Update
        public bool Edit(DeleClient Dov)
        {
            string sql = "update DeleClient set Diid=@Diid,Name=@Name,LastName=@LastName,Pinyin=@Pinyin,Company=@Company,Job=@Job,Phone=@Phone,Sex=@Sex,IDcard=@IDcard,PassprotType=@PassprotType," +
                         "PassportNo=@PassportNo,passportCountry=@passportCountry,IssuePlace=@IssuePlace,IssueDate=@IssueDate,ExpiryDate=@ExpiryDate,Birthday=@Birthday,AirType=@AirType,AirRemark=@AirRemark,RoomType=@RoomType," +
                         "Remark=@Remark,OPer=@OPer,OPdate=@OPdate,Isdel=@Isdel,GroupUnit=@GroupUnit where Id=@Id";

            SqlParameter[] parameter = new SqlParameter[]{
                    new SqlParameter("@Diid",Dov.Diid),
                    new SqlParameter("@Name",Dov.Name),
                    new SqlParameter("@LastName",Dov.LastName),
                    new SqlParameter("@Pinyin",Dov.Pinyin),
                    new SqlParameter("@Company",Dov.Company),
                    new SqlParameter("@GroupUnit",Dov.GroupUnit),
                    new SqlParameter("@Job",Dov.Job),
                    new SqlParameter("@Phone",Dov.Phone),
                    new SqlParameter("@Sex",Dov.Sex),
                    new SqlParameter("@IDcard",Dov.IDcard),
                    new SqlParameter("@PassprotType",Dov.PassprotType),
                    new SqlParameter("@PassportNo",Dov.PassportNo),
                    new SqlParameter("@passportCountry",Dov.passportCountry),
                    new SqlParameter("@IssuePlace",Dov.IssuePlace),
                    new SqlParameter("@IssueDate",Dov.IssueDate),
                    new SqlParameter("@ExpiryDate",Dov.ExpiryDate),
                    new SqlParameter("@Birthday",Dov.Birthday),
                    new SqlParameter("@AirType",Dov.AirType),
                    new SqlParameter("@AirRemark",Dov.AirRemark),
                    new SqlParameter("@RoomType",Dov.RoomType),
                    new SqlParameter("@Remark",Dov.Remark),
                    new SqlParameter("@OPer",Dov.OPer),
                    new SqlParameter("@OPdate",Dov.OPdate),
                    new SqlParameter("@Isdel",Dov.Isdel),
                    new SqlParameter("@Id",Dov.Id)
            };

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

        /// <summary>
        /// orc识别修改
        /// </summary>
        /// <param name="Dov"></param>
        /// <returns></returns>
        public bool DeleUpd_ORC(DeleClient Dov)
        {
            string sql = "update DeleClient PassportNo=@PassportNo,passportCountry=@passportCountry,IssuePlace=@IssuePlace," +
                         "IssueDate=@IssueDate,ExpiryDate=@ExpiryDate,OPer=@OPer,OPdate=@OPdate,Isdel=@Isdel where Id = @Id";

            SqlParameter[] parameter = new SqlParameter[]{
                    new SqlParameter("@PassportNo",Dov.PassportNo),
                    new SqlParameter("@passportCountry",Dov.passportCountry),
                    new SqlParameter("@IssuePlace",Dov.IssuePlace),
                    new SqlParameter("@IssueDate",Dov.IssueDate),
                    new SqlParameter("@ExpiryDate",Dov.ExpiryDate),
                    new SqlParameter("@OPer",Dov.OPer),
                    new SqlParameter("@OPdate",Dov.OPdate),
                    new SqlParameter("@Isdel",Dov.Isdel),
                    new SqlParameter("@Id",Dov.Id)
            };

            if (SqlHelper.ExecuteNonQuery(sql, CommandType.Text, parameter) > 0)
                return true;
            return false;
        }
        //删
        public bool delOA(int id)
        {
            if (SqlHelper.ExecuteNonQuery("update DeleClient set Isdel=1 where Id=@Id", CommandType.Text, new SqlParameter("@Id", id)) > 0)
                return true;
            return false;
        }

        public DeleClient GetByNameAndPhone(string name, string Phone)
        {
            return excuteType("select * from DeleClient where Isdel=0 and Name='" + name + "' and Phone='" + Phone + "'");
        }

        public DeleClient GetByNamesAndPhoneOrId(int diid, string LastName, string name, string Phone, string IdCard)
        {
            return excuteType("select * from DeleClient where diid=" + diid + " and (((Isdel=0 and  LastName='" + LastName + "' and Name='" + name + "') or Phone='" + Phone + "') or IDcard='" + IdCard + "')");
        }

        public DeleClient GetByNamesAndPhoneOrId(string LastName, string name, string Phone, string IdCard)
        {
            return excuteType("select * from DeleClient where ((Isdel=0 and  LastName='" + LastName + "' and Name='" + name + "') or Phone='" + Phone + "') or IDcard='" + IdCard + "'");
        }


        public bool updTelAndIdcard(DeleClient Dov)
        {
            string sql = "update DeleClient set Phone=@Phone,IDcard=@IDcard,OPer=@OPer,OPdate=@OPdate,Isdel=@Isdel where Id=@Id";
            SqlParameter[] parameter = new SqlParameter[]{
                    new SqlParameter("@Phone",Dov.Phone),
                    new SqlParameter("@IDcard",Dov.IDcard),
                    new SqlParameter("@OPer",Dov.OPer),
                    new SqlParameter("@OPdate",Dov.OPdate),
                    new SqlParameter("@Isdel",Dov.Isdel),
                    new SqlParameter("@Id",Dov.Id)
            };

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


        /// <summary>
        /// 根据团号查询舱位类型
        /// 20210827 贾文滔
        /// </summary>
        /// <param name="diid"></param>
        /// <returns></returns>
        public List<string> GetAirSeatType(int diid)
        {
            DataTable dt = SqlHelper.TransferProcedure("[dbo].[GetAirSeatType]", CommandType.StoredProcedure, new SqlParameter("@diid", diid));

            List<string> AirTypeList = new List<string>();
            if (dt != null && dt.Rows.Count > 0)
            {
                try
                {
                    foreach (DataRow row in dt.Rows)
                    {
                        AirTypeList.Add(row["AirType"].ToString());
                    }
                    return AirTypeList;
                }
                catch
                {
                    return null;
                }
            }
            else
            {
                return null;
            }
        }


        public bool AddS(List<DeleClient> Dlist)
        {
            SqlCommand cmd = SqlHelper.createCon().CreateCommand();

            cmd.Connection.Open();
            SqlTransaction trans = cmd.Connection.BeginTransaction();
            try
            {
                foreach (DeleClient Dov in Dlist)
                {
                    string sql = "insert into DeleClient values(@Diid,@Name,@LastName,@Pinyin,@Company,@Job,@Phone,@Sex,@IDcard,@PassprotType,@PassportNo,@passportCountry,@IssuePlace,@IssueDate,@ExpiryDate,"
                        + "@Birthday,@AirType,@AirRemark,@RoomType,@Remark,@OPer,@OPdate,@Isdel);SELECT @@IDENTITY";

                    SqlParameter[] parameter = new SqlParameter[]{
                    new SqlParameter("@Diid",Dov.Diid),
                    new SqlParameter("@Name",Dov.Name),
                    new SqlParameter("@LastName",Dov.LastName),
                    new SqlParameter("@Pinyin",Dov.Pinyin),
                    new SqlParameter("@Company",Dov.Company),
                    new SqlParameter("@Job",Dov.Job),
                    new SqlParameter("@Phone",Dov.Phone),
                    new SqlParameter("@Sex",Dov.Sex),
                    new SqlParameter("@IDcard",Dov.IDcard),
                    new SqlParameter("@PassprotType",Dov.PassprotType),
                    new SqlParameter("@PassportNo",Dov.PassportNo),
                    new SqlParameter("@passportCountry",Dov.passportCountry),
                    new SqlParameter("@IssuePlace",Dov.IssuePlace),
                    new SqlParameter("@IssueDate",Dov.IssueDate),
                    new SqlParameter("@ExpiryDate",Dov.ExpiryDate),
                    new SqlParameter("@Birthday",Dov.Birthday),
                    new SqlParameter("@AirType",Dov.AirType),
                    new SqlParameter("@AirRemark",Dov.AirRemark),
                    new SqlParameter("@RoomType",Dov.RoomType),
                    new SqlParameter("@Remark",Dov.Remark),
                    new SqlParameter("@OPer",Dov.OPer),
                    new SqlParameter("@OPdate",Dov.OPdate),
                    new SqlParameter("@Isdel",Dov.Isdel)
                };

                    cmd.CommandText = sql;
                    cmd.ExecuteNonQuery();
                }
                trans.Commit();
                cmd.Connection.Close();
                return true;
            }
            catch
            {
                trans.Rollback();
                cmd.Connection.Close();
                return false;
            }


        }
        /// <summary>
        /// 修改组团单位
        /// </summary>
        /// <param name="Dov"></param>
        /// <returns></returns>
        public bool DropCompanyEdit(string id, string DropCompany)
        {
            string sql = "update DeleClient set GroupUnit=@DropCompany where id in("+id+")";
            SqlParameter[] parameter = new SqlParameter[]{
                    new SqlParameter("@DropCompany",DropCompany),
            };
            if (SqlHelper.ExecuteNonQuery(sql, CommandType.Text, parameter) > 0)
                return true;
            return false;
        }
    }
}