using AutoMapper;
using OASystem.Domain;
using OASystem.Domain.Dtos.Groups;
using OASystem.Domain.Entities.Customer;
using OASystem.Domain.Entities.Groups;
using OASystem.Domain.ViewModels.Groups;
using OASystem.Infrastructure.Tools;
using Org.BouncyCastle.Utilities.Encoders;
using System;
using System.Collections.Generic;
using System.Globalization;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace OASystem.Infrastructure.Repositories.Groups
{
    /// <summary>
    /// 接团客户名单 
    /// 仓库
    /// </summary>
    public class TourClientListRepository :BaseRepository<Grp_TourClientList,TourClientListView>
    {
        private readonly Result _result;
        private readonly IMapper _mapper;

        public TourClientListRepository(SqlSugarClient sqlSugar, IMapper mapper)
            : base(sqlSugar)
        {
            _result = new Result() { Code = -1, Msg = "操作失败!" };
            _mapper = mapper;
        }

        /// <summary>
        /// 根据团组Id查询List
        /// </summary>
        /// <param name="portId"></param>
        /// <param name="diId"></param>
        /// <returns></returns>
        public async Task<Result> _ItemByDiId(int portId, int diId)
        {
            if (portId == 1 || portId == 2 || portId == 3 ) // 1 web 2 Android 3 ios
            {
                string sql = string.Format(@"Select tcl.Id,tcl.DiId,temp.* From Grp_TourClientList tcl
                                                 Left Join 
	                                                 (Select dc.Id As DcId,dc.LastName,dc.FirstName,ccom.CompanyFullName,dc.Job,cc.CertNo As IDCardNo,dc.Sex 
	                                                 From Crm_DeleClient dc
	                                                 Left Join Crm_CustomerCompany ccom On dc.CrmCompanyId = ccom.Id  And ccom.IsDel = 0
	                                                 Left Join Crm_CustomerCert cc On dc.Id = cc.DcId And cc.SdId = 773 And cc.IsDel = 0
	                                                 Where dc.IsDel = 0) temp 
                                                 On temp.DcId =tcl.ClientId  
                                                 Where tcl.IsDel = 0 And tcl.DiId = {0}", diId);
                var data = await _sqlSugar.SqlQueryable<TourClientListByDiIdView>(sql).ToListAsync();
                _result.Code = 0;
                _result.Data = data;
            }
            else
            {
                _result.Msg = "请传入有效的PortType参数,1 Web 2 Android 3 IOS!";
            }
            return _result;
        }

        /// <summary>
        /// 基础数据 Init
        /// </summary>
        /// <param name="portId"></param>
        /// <returns></returns>
        public async Task<Result> _BasicDataInit(int portId)
        {
            if (portId == 1 || portId == 2 || portId == 3) // 1 web 2 Android 3 ios
            {
                
                var setData = await _sqlSugar.Queryable<Sys_SetData>().Where(it => it.IsDel == 0) .ToListAsync();

                var shippingSpaceTypeData = setData.Where(it => it.STid == 44).ToList(); //舱位类型
                List<SetDataInfoView> _ShippingSpaceTypeData = _mapper.Map<List<SetDataInfoView>>(shippingSpaceTypeData);

                //var passportTypeData = setData.Where(it => it.STid == 74).ToList(); //护照类型
                //List<SetDataInfoView> _PassportTypeData = _mapper.Map<List<SetDataInfoView>>(passportTypeData);

                //客户信息资料
                var clientInfoSql = string.Format(@"Select dc.Id As DcId,dc.LastName,dc.FirstName,dc.Pinyin,dc.Sex,ccom.CompanyFullName,dc.Job,
											               cc1.CertNo As IDCardNo,dc.Phone,dc.BirthDay
	                                                From Crm_DeleClient dc
	                                                Left Join Crm_CustomerCompany ccom On dc.CrmCompanyId = ccom.Id  And ccom.IsDel = 0
	                                                Left Join Crm_CustomerCert cc1 On dc.Id = cc1.DcId And cc1.SdId = 773 And cc1.IsDel = 0
	                                                Where dc.IsDel = 0");
                var clientData = await _sqlSugar.SqlQueryable<TourClientListDetailsView>(clientInfoSql).ToListAsync();

                //公司信息 
                var clientCompanySql = string.Format(@"Select Id,CompanyFullName From Crm_CustomerCompany Where IsDel = 0");
                var clientCompanyData = await _sqlSugar.SqlQueryable<CustomerCompanyCiew>(clientCompanySql).ToListAsync();

                var _view = new {
                    ShippingSpaceTypeData = _ShippingSpaceTypeData,
                    ClientData = clientData,
                    ClientCompanyData = clientCompanyData
                    //PassportTypeData = _PassportTypeData
                };
                _result.Code = 0;
                _result.Data = _view;
            }
            else
            {
                _result.Msg = "请传入有效的PortType参数,1 Web 2 Android 3 IOS!";
            }
            return _result;
        }

        /// <summary>
        /// 根据Id查询Details
        /// </summary>
        /// <param name="portId"></param>
        /// <param name="id"></param>
        /// <returns></returns>
        public async Task<Result> _Details(int portId, int id)
        {
            if (portId == 1 || portId == 2 || portId == 3) // 1 web 2 Android 3 ios
            {
                string sql = string.Format(@"Select tcl.Id,tcl.DiId,temp.*,tcl.ShippingSpaceTypeId,tcl.ShippingSpaceSpecialNeeds,
                                             tcl.HotelSpecialNeeds,tcl.MealSpecialNeeds,tcl.Remark
                                             From Grp_TourClientList tcl
                                             Left Join 
	                                             (Select dc.Id As DcId,dc.LastName,dc.FirstName,dc.Pinyin,dc.Sex,ccom.CompanyFullName,dc.Job,
	                                                 cc1.CertNo As IDCardNo,dc.Phone,dc.BirthDay,cc2.PassportType,cc2.CertNo As PassportNo,cc2.Country,
	                                                 cc2.Area,cc2.IssueDt,cc2.ExpiryDt
	                                                 From Crm_DeleClient dc
	                                                 Left Join Crm_CustomerCompany ccom On dc.CrmCompanyId = ccom.Id  And ccom.IsDel = 0
	                                                 Left Join Crm_CustomerCert cc1 On dc.Id = cc1.DcId And cc1.SdId = 773 And cc1.IsDel = 0
	                                                 Left Join Crm_CustomerCert cc2 On dc.Id = cc2.DcId And cc2.SdId = 774 And cc2.IsDel = 0
	                                                 Where dc.IsDel = 0) temp 
                                             On temp.DcId =tcl.ClientId  
                                             Where tcl.IsDel = 0 And tcl.Id = {0}", id);
                var data = await _sqlSugar.SqlQueryable<TourClientListDetailsView>(sql).FirstAsync();
                if (data != null) 
                {
                    data.BirthDay = data.BirthDay.DateFormat("yyyy-MM-dd");
                    //data.IssueDt = data.IssueDt.DateFormat("yyyy-MM-dd");
                    //data.ExpiryDt = data.ExpiryDt.DateFormat("yyyy-MM-dd");
                    _result.Code = 0;
                    _result.Data = data;
                }
            }
            else
            {
                _result.Msg = "请传入有效的PortType参数,1 Web 2 Android 3 IOS!";
            }
            return _result;
        }

        /// <summary>
        /// Add Or Edit
        /// </summary>
        /// <param name="portId"></param>
        /// <param name="id"></param>
        /// <returns></returns>
        public async Task<Result> _AddOrEdit(TourClientListAddOrEditDto dto)
        {
            if (string.IsNullOrEmpty(dto.CompanyFullName))
            {
                _result.Msg = "客户单位名称为空!";
                return _result;
            }

            if (string.IsNullOrEmpty(dto.IDCardNo))
            {
                _result.Msg = "客户身份证No为空!";
                return _result;
            }

            if (dto.ShippingSpaceTypeId < 0)
            {
                _result.Msg = "舱位类型为空!";
                return _result;
            }

            if (dto.Id < 0) 
            {

                #region 参数处理
                _sqlSugar.BeginTran();
                int clientId = -1;
                int crmCompanyId = -1;
                var clientInfo = await _sqlSugar.Queryable<Crm_DeleClient>().Where(it => it.IsDel == 0 &&
                                                                                         it.LastName.Equals(dto.LastName) &&
                                                                                         it.FirstName.Equals(dto.FirstName) &&
                                                                                         it.Phone.Equals(dto.Phone)
                                                                                   ).FirstAsync();

                Crm_CustomerCompany _CustomerCompany = new Crm_CustomerCompany()
                {
                    CompanyFullName = dto.CompanyFullName,
                    CreateUserId = dto.UserId
                };

                Crm_CustomerCert _CustomerCert = new Crm_CustomerCert()
                {
                    SdId = 733,
                    CertNo = dto.IDCardNo,
                    CreateUserId = dto.UserId
                };

                Crm_DeleClient _DeleClient = new Crm_DeleClient()
                {
                    LastName = dto.LastName,
                    FirstName = dto.FirstName,
                    Pinyin = dto.Pinyin,
                    Phone = dto.Phone,
                    Sex = dto.Sex,
                    BirthDay = dto.BirthDay,
                    Job = dto.Job,
                    CreateUserId = dto.UserId
                };

                //客户单位/公司 操作
                //1.添加 or 修改 公司基本信息
                if (clientInfo == null)
                {
                    var companyInfo = await _sqlSugar.Queryable<Crm_CustomerCompany>().Where(it => it.IsDel == 0 &&
                                                                                                   it.CompanyFullName.Equals(dto.CompanyFullName)
                                                                                             ).FirstAsync();
                    if (companyInfo != null) crmCompanyId = companyInfo.Id;
                    else
                    {
                        var companyAdd = await _sqlSugar.Insertable(_CustomerCompany).ExecuteReturnIdentityAsync();
                        if (companyAdd < 0)
                        {
                            _result.Msg = "客户公司信息添加失败!";
                            _sqlSugar.RollbackTran();
                            return _result;
                        }
                        crmCompanyId = companyAdd;
                    }
                }
                else
                {
                    var companyInfo = await _sqlSugar.Queryable<Crm_CustomerCompany>().Where(it => it.IsDel == 0 &&
                                                                                                   it.Id == clientInfo.CrmCompanyId
                                                                                             ).FirstAsync();
                    if (companyInfo != null)
                    {
                        crmCompanyId = companyInfo.Id;
                        if (!companyInfo.CompanyFullName.Equals(dto.CompanyFullName))
                        {
                            companyInfo.CompanyFullName = dto.CompanyFullName;
                            var companyEdit = await _sqlSugar.Updateable(companyInfo).UpdateColumns(it =>
                                                                                 new
                                                                                 {
                                                                                     it.CompanyFullName,
                                                                                 }
                                                                             )
                                                                            .Where(it => it.Id == companyInfo.Id)
                                                                            .ExecuteCommandAsync();
                            if (companyEdit < 0)
                            {
                                _result.Msg = "客户公司信息修改失败!";
                                _sqlSugar.RollbackTran();
                                return _result;
                            }
                        }
                    }
                    else
                    {
                        var companyAdd = await _sqlSugar.Insertable(_CustomerCompany).ExecuteReturnIdentityAsync();
                        if (companyAdd < 0)
                        {
                            _result.Msg = "客户公司信息添加失败!";
                            _sqlSugar.RollbackTran();
                            return _result;
                        }
                        crmCompanyId = companyAdd;
                    }
                }

                //客户人员 操作
                //2.添加 or 修改 客户基本信息
                if (clientInfo != null)  //该客户存在 修改信息 
                {
                    clientId = clientInfo.Id;
                    var clientEdit = await _sqlSugar.Updateable(_DeleClient).UpdateColumns(it =>
                                                                                 new
                                                                                 {
                                                                                     it.LastName,
                                                                                     it.FirstName,
                                                                                     it.Pinyin,
                                                                                     it.Sex,
                                                                                     it.Phone,
                                                                                     it.BirthDay
                                                                                 }
                                                                             )
                                                                            .Where(it => it.Id == clientId)
                                                                            .ExecuteCommandAsync();
                    if (clientEdit < 0)
                    {
                        _result.Msg = "客户基础信息修改失败!";
                        _sqlSugar.RollbackTran();
                        return _result;
                    }
                }
                else //不存在添加 客户信息
                {
                    _DeleClient.CrmCompanyId = crmCompanyId;
                    var clientAdd = await _sqlSugar.Insertable(_DeleClient).ExecuteReturnIdentityAsync();
                    if (clientAdd < 0)
                    {
                        _result.Msg = "客户基本信息添加失败!";
                        _sqlSugar.RollbackTran();
                        return _result;
                    }
                    clientId = clientAdd;

                }

                //客户身份证操作
                //3.添加 or 修改 身份证信息
                if (clientInfo == null)
                {
                    var certInfo = await _sqlSugar.Queryable<Crm_CustomerCert>().Where(it => it.IsDel == 0 &&
                                                                                             it.SdId == 773 &&      //卡类型 身份证
                                                                                             it.CertNo == dto.IDCardNo    //人员Id
                                                                                       ).FirstAsync();
                    if (certInfo == null)
                    {
                        var certAdd = await _sqlSugar.Insertable(_CustomerCert).ExecuteReturnIdentityAsync();
                        if (certAdd < 0)
                        {
                            _result.Msg = "客户身份证添加失败!";
                            _sqlSugar.RollbackTran();
                            return _result;
                        }
                    }
                }
                else
                {
                    var certInfo = await _sqlSugar.Queryable<Crm_CustomerCert>().Where(it => it.IsDel == 0 &&
                                                                                             it.SdId == 773 &&      //卡类型 身份证
                                                                                             it.DcId == clientInfo.Id    //人员Id
                                                                                       ).FirstAsync();
                    if (certInfo == null)
                    {
                        var certAdd = await _sqlSugar.Insertable(_CustomerCert).ExecuteReturnIdentityAsync();
                        if (certAdd < 0)
                        {
                            _result.Msg = "客户身份证添加失败!";
                            _sqlSugar.RollbackTran();
                            return _result;
                        }
                    }
                    else
                    {
                        var certEdit = await _sqlSugar.Updateable(_CustomerCert).UpdateColumns(it =>
                                                                                 new
                                                                                 {
                                                                                     it.CertNo,
                                                                                 }
                                                                             )
                                                                            .Where(it => it.Id == certInfo.Id)
                                                                            .ExecuteCommandAsync();
                        if (certEdit < 0)
                        {
                            _result.Msg = "客户身份证修改失败!";
                            _sqlSugar.RollbackTran();
                            return _result;
                        }
                    }
                }


                //团组客户信息名单操作
                Grp_TourClientList _TourClientList = new Grp_TourClientList()
                {
                    DiId = dto.DiId,
                    ClientId = clientId,
                    ShippingSpaceTypeId = dto.ShippingSpaceTypeId,
                    ShippingSpaceSpecialNeeds = dto.ShippingSpaceSpecialNeeds,
                    HotelSpecialNeeds = dto.HotelSpecialNeeds,
                    MealSpecialNeeds = dto.MealSpecialNeeds,
                    Remark = dto.Remark,
                    CreateUserId = dto.UserId
                };

                #endregion

                if (dto.Id == 0) // 添加
                {
                    var tourClientAdd = await _sqlSugar.Insertable(_TourClientList).ExecuteReturnIdentityAsync();
                    if (tourClientAdd < 0)
                    {
                        _result.Msg = "接团客户名单添加失败!";
                        _sqlSugar.RollbackTran();
                        return _result;
                    }
                }
                else if (dto.Id > 0) //修改
                {
                    var certEdit = await _sqlSugar.Updateable(_TourClientList).UpdateColumns(it =>
                                                                                new
                                                                                {
                                                                                    it.ClientId,
                                                                                    it.ShippingSpaceTypeId,
                                                                                    it.ShippingSpaceSpecialNeeds,
                                                                                    it.HotelSpecialNeeds,
                                                                                    it.MealSpecialNeeds,
                                                                                    it.Remark,
                                                                                }
                    )
                                                                           .Where(it => it.Id == dto.Id)
                                                                           .ExecuteCommandAsync();
                    if (certEdit < 0)
                    {
                        _result.Msg = "接团客户名单失败修改!";
                        _sqlSugar.RollbackTran();
                        return _result;
                    }
                }
                
                _result.Code = 0;
                _sqlSugar.CommitTran();

            }
            else
            {
                _result.Msg = "请传入有效的id参数!";
            }
            return _result;
        }

        /// <summary>
        /// Del
        /// </summary>
        /// <param name="portId"></param>
        /// <param name="id"></param>
        /// <returns></returns>
        public async Task<Result> _Del(int id, int userId)
        {

            if (id < 0) // 1 web 2 Android 3 ios
            {

                Grp_TourClientList _TourClientList = new Grp_TourClientList()
                {
                    Id = id,
                    IsDel = 1,
                    DeleteUserId = userId,
                    DeleteTime = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")
                };

                var certEdit = await _sqlSugar.Updateable(_TourClientList).UpdateColumns(it =>
                                                                                new
                                                                                {
                                                                                    it.IsDel,
                                                                                    it.DeleteUserId,
                                                                                    it.DeleteTime,
                                                                                }
                    )
                                                                           .Where(it => it.Id == id)
                                                                           .ExecuteCommandAsync();
                if (certEdit < 0)
                {
                    _result.Msg = string.Format(@"接团客户名单删除修改!");
                    return _result;
                }
                _result.Code = 0;
            }
            else
            {
                _result.Msg = string.Format(@"请传入有效的Id参数");
            }

            return _result;
        }
    }
}