OA2023/OASystem/OASystem.Infrastructure/Repositories/Groups/TourClientListRepository.cs

using AutoMapper;
using OASystem.Domain;
using OASystem.Domain.Dtos.CRM;
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 StackExchange.Redis;
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,
                    LastedOpUserId = dto.UserId,
                    CreateUserId = dto.UserId
                };

                Crm_CustomerCert _CustomerCert = new Crm_CustomerCert()
                {
                    SdId = 773,
                    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 修改 身份证信息
                _CustomerCert.DcId = clientId;
                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>
        /// AddMultiple
        /// 添加多个
        /// </summary>
        /// <param name="portId"></param>
        /// <param name="id"></param>
        /// <returns></returns>
        public async Task<Result> _AddMultiple(TourClientListAddMultipleDto dto)
        {
            if (dto.DiId < 0)
            {
                _result.Msg = string.Format(@"请传入有效的团组Id参数!");
                return _result;
            }
            List<TourClientListInfo> _TourClientListInfos = dto.TourClientListInfos;
            if (_TourClientListInfos == null || _TourClientListInfos.Count < 0)
            {
                _result.Msg = string.Format(@"接团客户名单信息集合为空，不执行批量添加!");
                return _result;
            }

            List<Crm_DeleClient> _DeleClients = await _sqlSugar.Queryable<Crm_DeleClient>().Where(it => it.IsDel == 0).ToListAsync();
            List<Crm_CustomerCompany> _CustomerCompanies = await _sqlSugar.Queryable<Crm_CustomerCompany>().Where(it => it.IsDel == 0).ToListAsync();
            List<Crm_CustomerCert> _CustomerCerts = await _sqlSugar.Queryable<Crm_CustomerCert>().Where(it => it.IsDel == 0 && it.SdId == 773).ToListAsync(); // 身份证类型证件信息

            string _Msg = "";
            int _DiId = dto.DiId;
            int _UserId = dto.UserId;

            foreach (var item in _TourClientListInfos)
            {
                _sqlSugar.BeginTran();
                int companyId = -1;
                int clientId = -1;
                string clientName = string.Format(@"{0}{1}", item.LastName, item.FirstName);
                Crm_DeleClient _DeleClientInfo = _DeleClients.Where(it => it.LastName.Equals(item.LastName) &&
                                                                          it.FirstName.Equals(item.FirstName) &&
                                                                          it.Phone.Equals(item.Phone)).FirstOrDefault();
                if (_DeleClientInfo == null) //添加
                {
                    if (!string.IsNullOrEmpty(item.CompanyFullName))
                    {
                        var companyInfo = _CustomerCompanies.Where(it => it.CompanyFullName.Equals(item.CompanyFullName)).FirstOrDefault();
                        if (companyInfo != null) companyId = companyInfo.Id;
                        else
                        {
                            Crm_CustomerCompany _CustomerCompany = new Crm_CustomerCompany()
                            {
                                CompanyFullName = item.CompanyFullName,
                                LastedOpUserId = _UserId,
                                CreateUserId = _UserId
                            };

                            var companyAdd = await _sqlSugar.Insertable(_CustomerCompany).ExecuteReturnIdentityAsync();
                            if (companyAdd < 0)
                            {
                                _Msg += string.Format("{0} 公司信息添加失败!请前往客户信息确认!\r\n", clientName);
                            }
                            companyId = companyAdd;
                        }
                    }
                    
                    _DeleClientInfo = new Crm_DeleClient() 
                    {
                        LastName = item.LastName,
                        FirstName = item.FirstName,
                        CrmCompanyId = companyId,
                        Job = item.Job,
                        Sex = item.Sex,
                        Phone = item.Phone,
                        BirthDay = item.BirthDay,
                    };
                    var clientAdd = await _sqlSugar.Insertable(_DeleClientInfo).ExecuteReturnIdentityAsync();
                    if (clientAdd < 0)
                    {
                        _Msg += string.Format("{0} 基本信息添加失败,本条客户名单不添加!请重新添加!\r\n", clientName);
                        _sqlSugar.RollbackTran();
                        continue;
                    }
                    clientId = clientAdd;

                    if (!string.IsNullOrEmpty(item.IDCardNo))
                    {
                        Crm_CustomerCert _CustomerCert = _CustomerCerts.Where(it => it.DcId == clientId && it.CertNo.Equals(item.IDCardNo)).FirstOrDefault();
                        if (_CustomerCert == null)
                        {
                            _CustomerCert = new Crm_CustomerCert() 
                            {
                                DcId = clientId,
                                SdId = 773,
                                CertNo = item.IDCardNo,
                                CreateUserId = _UserId,
                            };
                            var customerCertAdd = await _sqlSugar.Insertable(_CustomerCert).ExecuteCommandAsync();
                            if (customerCertAdd < 0)
                            {
                                _Msg += string.Format("{0} 身份证信息添加失败!请前往客户信息确认!\r\n", clientName);
                            }
                        }
                    }

                }
                else //修改客户基本信息
                {
                    clientId = _DeleClientInfo.Id;

                    if (!string.IsNullOrEmpty(item.CompanyFullName))
                    {
                        var companyInfo = _CustomerCompanies.Where(it => it.CompanyFullName.Equals(item.CompanyFullName)).FirstOrDefault();
                        if (companyInfo != null) companyId = companyInfo.Id;
                        else
                        {
                            Crm_CustomerCompany _CustomerCompany = new Crm_CustomerCompany()
                            {
                                CompanyFullName = item.CompanyFullName,
                                LastedOpUserId = _UserId,
                                CreateUserId = _UserId
                            };

                            var companyAdd = await _sqlSugar.Insertable(_CustomerCompany).ExecuteReturnIdentityAsync();
                            if (companyAdd < 0)
                            {
                                _Msg += string.Format("{0} 公司信息添加失败!请前往客户信息确认!\r\n", clientName);
                            }
                            companyId = companyAdd;
                        }
                    }

                    Crm_DeleClient _DeleClient = new Crm_DeleClient() { 
                        LastName = item.LastName,
                        FirstName = item.FirstName,
                        Sex = item.Sex,
                        Phone = item.Phone,
                        BirthDay = item.BirthDay
                    };
                    var clientEdit = await _sqlSugar.Updateable(_DeleClient).UpdateColumns(it =>
                                                                                 new
                                                                                 {
                                                                                     it.LastName,
                                                                                     it.FirstName,
                                                                                     it.Sex,
                                                                                     it.Phone,
                                                                                     it.BirthDay
                                                                                 }
                                                                             )
                                                                            .Where(it => it.Id == clientId)
                                                                            .ExecuteCommandAsync();
                    if (clientEdit < 0)
                    {
                        _Msg += string.Format("{0} 基本信息修改失败,!请前往客户信息修改!\r\n", clientName);
                    }

                }


                //团组客户信息名单操作
                Grp_TourClientList _TourClientList = new Grp_TourClientList()
                {
                    DiId = _DiId,
                    ClientId = clientId,
                    ShippingSpaceTypeId = item.ShippingSpaceTypeId,
                    CreateUserId = _UserId
                };
                var tourClientAdd = await _sqlSugar.Insertable(_TourClientList).ExecuteReturnIdentityAsync();
                if (tourClientAdd < 0)
                {
                    _Msg += string.Format("{0} 接团客户名单添加失败!请检查数据完整性后重新添加!\r\n", clientName);
                    _sqlSugar.RollbackTran();
                    continue;
                }

                _sqlSugar.CommitTran();
            }

            if (!string.IsNullOrEmpty(_Msg)) _result.Msg = _Msg;
            else _result.Msg = string.Format("操作成功!");

            _result.Code = 0;

            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) 
            {

                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;
        }
    }
}