OA2023/OASystem/OASystem.Infrastructure/Repositories/CRM/VisaDeleClientRepository.cs


using AutoMapper;
using Newtonsoft.Json;
using OASystem.Domain;
using OASystem.Domain.Dtos;
using OASystem.Domain.Dtos.CRM;
using OASystem.Domain.Dtos.UserDto;
using OASystem.Domain.Entities.Customer;
using OASystem.Domain.Entities.Groups;
using OASystem.Domain.Entities.Resource;
using OASystem.Domain.ViewModels.CRM;
using OASystem.Domain.ViewModels.Groups;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Runtime.Intrinsics.X86;
using System.Text;
using System.Threading.Tasks;

namespace OASystem.Infrastructure.Repositories.CRM
{
    /// <summary>
    /// 签证客户 仓库
    /// </summary>
    public class VisaDeleClientRepository : BaseRepository<Crm_DeleClient, VisaDeleClientView>
    {
        private readonly IMapper _mapper;
        public VisaDeleClientRepository(SqlSugarClient sqlSugar,IMapper mapper) :
            base(sqlSugar)
        {
            this._mapper = mapper;
           
        }

        /// <summary>
        /// 签证客户list
        /// </summary>
        /// <param name="loginDto"></param>
        /// <returns></returns>
        public async Task<Result> GetCrmList(DtoBase dto)
        {
            Result result = new Result() { Code = -2 };
            if (dto.PortType == 1 || dto.PortType == 2) 
            {
                string sql = string.Format(@"Select cdc.Id,LastName+FirstName ClientName,ccc.CompanyName,Sex,Marriage,
                                             	    Phone LandlinePhone,Tel,crmCard1.CertNo IDNo,crmCard2.CertNo PassportNo
                                             From Crm_DeleClient cdc
                                             Left Join Crm_CustomerCompany ccc On cdc.crmCompanyId = ccc.Id
                                             Left Join Crm_CustomerCert crmCard1 On crmCard1.SdId = 773 And  cdc.Id = crmCard1.DcId  And crmCard1.Isdel=0
                                             Left Join Crm_CustomerCert crmCard2 On crmCard2.SdId = 774 And  cdc.Id = crmCard2.DcId And  crmCard2.IsDel=0 where cdc.IsDel='0' order By cdc.CreateTime desc");

                var clientList = await _sqlSugar.SqlQueryable<VisaDeleClientListView>(sql).ToListAsync();

                if (clientList.Count > 0)
                {
                    result.Code = 0;
                    result.Msg = "成功!";
                    result.Data = clientList;
                }
                else
                {
                    result.Msg = "暂无数据!";
                }
            }

            return result;
        }


        /// <summary>
        /// 签证客户操作
        /// </summary>
        /// <param name="loginDto"></param>
        /// <returns></returns>
        public async Task<Result> CrmClinetoperation(LoginDto loginDto)
        {
            Result result = new Result() { Code = -2 };

            return result;
        }

        /// <summary>
        /// 签证客户 新增
        /// </summary>
        /// <param name="loginDto"></param>
        /// <returns></returns>
        public async Task<int> CrmClinetAdd(Crm_DeleClient client)
        {
            int addId = -1;

            


            return addId;
        }


        /// <summary>
        /// 根据身份证识别修改ocr添加
        /// </summary>
        /// <param name="client"></param>
        /// <returns></returns>
        public async Task<bool> SetCrmUpdPassIdCardOCR(SetCrmUpdPassIdCardOCRDto client)
        {

            string clientSql = string.Format(@"Select * From Crm_DeleClient Where LastName+FirstName='{0}' And Sex = {1}",
                                             client.ClientName, client.Sex);
            var clientInfo = await _sqlSugar.SqlQueryable<VisaDeleClientListView>(clientSql).FirstAsync();
            if(clientInfo == null) return false;

            string cardSql = string.Format(@"Select * From Crm_CustomerCert Where SdId=773 And DcId={0}", clientInfo.Id);
            var cardInfo = await _sqlSugar.SqlQueryable<CustomerCertView>(cardSql).FirstAsync();

            if (cardInfo == null)  //添加
            {
                int cerdAdd = await _sqlSugar.Insertable<Crm_CustomerCert>(new Crm_CustomerCert
                {
                    DcId = clientInfo.Id,
                    SdId = 773,
                    Country = "中国",
                    CertNo = client.CerdNo,
                    TargetCountry = "",
                    IssueDt = new DateTime(1990, 1, 1),
                    ExpiryDt = new DateTime(1990, 1, 1),
                    CreateUserId = client.UserId,
                    CreateTime = DateTime.Now,
                    DeleteUserId = null,
                    DeleteTime = "1990-01-01 00:00:00.000",
                    Remark = "",
                    IsDel = 0
                }).ExecuteReturnIdentityAsync();
                if (cerdAdd > 0) return true;

            }
            else  //修改
            {
                var cerdStatus = await _sqlSugar.Updateable<Crm_CustomerCert>()
                    .Where(c => c.Id == cardInfo.Id)
                    .SetColumns(c => new Crm_CustomerCert
                    {
                        CertNo = cardInfo.CertNo,
                        IDCardAddress = cardInfo.IDCardAddress
                    }).ExecuteCommandAsync();

                if (cerdStatus > 0) return true;
            }

            
            return false;
        }

        public async Task<Result> OpCustomer(DeleClientOpDto dto)
        {
           Result result = new Result() { Code = -2, Msg = "未知错误" };
            try
            {
                BeginTran();
                int deleId = 0;
                if (dto.Status==1)//添加
                {
                    string selectSql = string.Format(@"select * from Crm_DeleClient where  LastName+FirstName='{0}' and Phone='{1}' and IsDel='{2}'"
                                                      , dto.DeleClient.LastName+dto.DeleClient.FirstName, dto.DeleClient.Phone, 0);
                    var DeleClient = await _sqlSugar.SqlQueryable<Crm_DeleClient>(selectSql).FirstAsync();//查询是否存在
                    if (DeleClient != null)
                    {
                        result = new Result() { Code = -1, Msg = "该客户已存在，请勿重复添加！" };

                    }
                    else//不存在，可添加
                    {
                        Crm_DeleClient _CountryFeeCost = _mapper.Map<Crm_DeleClient>(dto.DeleClient);
                        int id = await AddAsyncReturnId(_CountryFeeCost);
                        if (id == 0)
                        {
                            result = new Result() { Code = -1, Msg = "添加失败！" };

                        }
                        result = new Result() { Code = 0, Msg = "添加成功！" };
                        deleId = id;
                        
                    }
                }
                else if (dto.Status == 2)//修改
                {
                    deleId=dto.DeleClient.Id;
                    bool res = await UpdateAsync(a => a.Id == dto.DeleClient.Id, a => new Crm_DeleClient
                    {
                        LastName = dto.DeleClient.LastName,
                        FirstName = dto.DeleClient.FirstName,
                        OldName = dto.DeleClient.OldName,
                        Pinyin = dto.DeleClient.Pinyin,
                        Sex = dto.DeleClient.Sex,
                        Marriage = dto.DeleClient.Marriage,
                        Phone = dto.DeleClient.Phone,
                        BirthProvince = dto.DeleClient.BirthProvince,
                        BirthCity = dto.DeleClient.BirthCity,
                        BirthDay = dto.DeleClient.BirthDay,
                        AirType = dto.DeleClient.AirType,
                        SeatPref = dto.DeleClient.SeatPref,
                        AirRemark = dto.DeleClient.AirRemark,
                        RoomType = dto.DeleClient.RoomType,
                        RoomPref = dto.DeleClient.RoomPref,
                        Tel = dto.DeleClient.Tel,
                        Email = dto.DeleClient.Email,
                        Address = dto.DeleClient.Address,
                        HighestEducation = dto.DeleClient.HighestEducation,
                        PostCodes = dto.DeleClient.PostCodes,
                        CrmCompanyId = dto.DeleClient.CrmCompanyId,
                        Job = dto.DeleClient.Job,
                        WorkState = dto.DeleClient.WorkState,
                        WorkDate = dto.DeleClient.WorkDate,
                        Wage = dto.DeleClient.Wage,
                        ClientPhone = dto.DeleClient.ClientPhone,
                        ClientFax = dto.DeleClient.ClientFax,
                        ClientEmail = dto.DeleClient.ClientEmail,
                        ClientLeader = dto.DeleClient.ClientLeader,
                        ClientLeaderJob = dto.DeleClient.ClientLeaderJob,
                        IsGetSchengen = dto.DeleClient.IsGetSchengen,
                        StartTime = dto.DeleClient.StartTime,
                        EndTime = dto.DeleClient.EndTime,
                        IsFinger = dto.DeleClient.IsFinger,
                        FingerDate = dto.DeleClient.FingerDate,
                        IsVisitUC = dto.DeleClient.IsVisitUC,
                        USADate = dto.DeleClient.USADate,
                        USADays = dto.DeleClient.USADays,
                        CanDate = dto.DeleClient.CanDate,
                        CanDays = dto.DeleClient.CanDays,
                        IsUSAVia = dto.DeleClient.IsUSAVia,
                        GetUSAVisaDate = dto.DeleClient.GetUSAVisaDate,
                        GetUPPlace = dto.DeleClient.GetUPPlace,
                        USAVisaCate = dto.DeleClient.USAVisaCate,
                        USAVisaCode = dto.DeleClient.USAVisaCode,
                        USAFinger = dto.DeleClient.USAFinger,
                        IsRejected = dto.DeleClient.IsRejected,
                        RejectedDate = dto.DeleClient.RejectedDate,
                        RejectedPlace = dto.DeleClient.RejectedPlace,
                        RejectedVisa = dto.DeleClient.RejectedVisa,
                        IsRevoke = dto.DeleClient.IsRevoke,
                        IsLose = dto.DeleClient.IsLose,
                        LoseDate = dto.DeleClient.LoseDate,
                        LoseCode = dto.DeleClient.LoseCode,
                        IsUSAVisa = dto.DeleClient.IsUSAVisa,
                        MateUSA = dto.DeleClient.MateUSA,
                        MateIden = dto.DeleClient.MateIden,
                        WHUSA = dto.DeleClient.WHUSA,
                        WHIden = dto.DeleClient.WHIden,
                        ParentUSA = dto.DeleClient.ParentUSA,
                        ParentIden = dto.DeleClient.ParentIden,
                        ChildUSA = dto.DeleClient.ChildUSA,
                        ChildIden = dto.DeleClient.ChildIden,
                        BroUSA = dto.DeleClient.BroUSA,
                        BroIden = dto.DeleClient.BroIden,
                        Social = dto.DeleClient.Social,
                        IsArmy = dto.DeleClient.IsArmy,
                        ArmyState = dto.DeleClient.ArmyState,
                        CostBearers = dto.DeleClient.CostBearers,
                        TableOpName = dto.DeleClient.TableOpName,
                        TableOpTel = dto.DeleClient.TableOpTel,
                        TableDate = dto.DeleClient.TableDate,
                        Party = dto.DeleClient.Party,
                        Nationality = dto.DeleClient.Nationality,
                        WeddingDate = dto.DeleClient.WeddingDate,
                        DivorceDate = dto.DeleClient.DivorceDate,
                        MateName = dto.DeleClient.MateName,
                        MateBirthDay = dto.DeleClient.MateBirthDay,
                        MateBirthCity = dto.DeleClient.MateBirthCity,
                        MateBirthCountry = dto.DeleClient.MateBirthCountry,
                        MateAddress = dto.DeleClient.MateAddress,
                        MateClient = dto.DeleClient.MateClient,
                        MateClientAddress = dto.DeleClient.MateClientAddress,
                        MateJob = dto.DeleClient.MateJob,
                        VisitCountry = dto.DeleClient.VisitCountry,
                        Paper = dto.DeleClient.Paper,
                        Papent = dto.DeleClient.Papent,
                        PhD = dto.DeleClient.PhD,
                        CreateUserId = dto.DeleClient.CreateUserId,
                        Remark = dto.DeleClient.Remark,
                    });
                    if (!res)
                    {
                        result = new Result() { Code = -1, Msg = "修改失败！" };
                    }
                    result = new Result() { Code = 0, Msg = "修改成功！" };
                    
                }
                //进行其他表
                if (result.Code == 0)
                {//家庭成员信息
                    foreach (CustomerFamily item in dto.CustomerFamily)
                    {
                        Crm_VisaCustomerFamily Crm_VisaCustomerFamily = _mapper.Map<Crm_VisaCustomerFamily>(item);
                        Crm_VisaCustomerFamily.DcId = deleId;
                        if (item.Id != 0)//修改
                        {
                            int res = await _sqlSugar.Updateable<Crm_VisaCustomerFamily>().Where(a => a.Id == Crm_VisaCustomerFamily.Id).SetColumns(a => new Crm_VisaCustomerFamily
                            {
                                Appellation = Crm_VisaCustomerFamily.Appellation,
                                Name = Crm_VisaCustomerFamily.Name,
                                BirthDay = Crm_VisaCustomerFamily.BirthDay,
                                BirthPlace = Crm_VisaCustomerFamily.BirthPlace,
                                Politics = Crm_VisaCustomerFamily.Politics,
                                Client = Crm_VisaCustomerFamily.Client,
                                Address = Crm_VisaCustomerFamily.Address,
                                IsEu = Crm_VisaCustomerFamily.IsEu,
                                NameSnd = Crm_VisaCustomerFamily.NameSnd,
                                BirthDaySnd = Crm_VisaCustomerFamily.BirthDaySnd,
                                Nationality = Crm_VisaCustomerFamily.Nationality,
                                IDCard = Crm_VisaCustomerFamily.IDCard,
                                Reletionship = Crm_VisaCustomerFamily.Reletionship,
                                IsUSA = Crm_VisaCustomerFamily.IsUSA,
                                Remark = Crm_VisaCustomerFamily.Remark,
                            }).ExecuteCommandAsync();
                            if (res==0)
                            {
                                result = new Result() { Code = -1, Msg = "家庭成员信息保存失败！" };
                                RollbackTran();
                            }
                        }
                        else if (item.Id == 0)//添加
                        {
                           int sss= await _sqlSugar.Insertable(Crm_VisaCustomerFamily).ExecuteReturnIdentityAsync();
                            if (sss == 0)
                            {
                                result = new Result() { Code = -1, Msg = "家庭成员信息保存失败！" };
                                RollbackTran();
                            }
                        }
                    }

                    //证件表信息
                    foreach (CustomerCerts item in dto.CustomerCert)
                    {
                        Crm_CustomerCert CustomerCert = _mapper.Map<Crm_CustomerCert>(item);
                        CustomerCert.DcId = deleId;
                        if (item.Id != 0)//修改
                        {
                            int res = await _sqlSugar.Updateable<Crm_CustomerCert>().Where(a => a.Id == CustomerCert.Id).SetColumns(a => new Crm_CustomerCert
                            {
                                DcId=CustomerCert.DcId,
                                SdId = CustomerCert.SdId,
                                CertNo = CustomerCert.CertNo,
                                Country = CustomerCert.Country,
                                Area = CustomerCert.Area,
                                TargetCountry = CustomerCert.TargetCountry,
                                IssueDt = CustomerCert.IssueDt,
                                ExpiryDt = CustomerCert.ExpiryDt,
                                IDCardAddress = CustomerCert.IDCardAddress,
                                CreateUserId = CustomerCert.CreateUserId,
                                Remark = CustomerCert.Remark,
                            }).ExecuteCommandAsync();
                            if (res == 0)
                            {
                                result = new Result() { Code = -1, Msg = "证件信息保存失败！" };
                                RollbackTran();
                            }
                        }
                        else if (item.Id == 0)//添加
                        {
                            int sss = await _sqlSugar.Insertable(CustomerCert).ExecuteReturnIdentityAsync();
                            if (sss == 0)
                            {
                                result = new Result() { Code = -1, Msg = "证件信息保存失败！" };
                                RollbackTran();
                            }
                        }
                    }
                    //客户工作经历表
                    foreach (VisaCustomerCompany item in dto.WorkExperience)
                    {
                        Crm_VisaCustomerCompany VisaCustomerCompany = _mapper.Map<Crm_VisaCustomerCompany>(item);
                        VisaCustomerCompany.DcId = deleId;
                        if (item.Id != 0)//修改
                        {
                            int res = await _sqlSugar.Updateable<Crm_VisaCustomerCompany>().Where(a => a.Id == VisaCustomerCompany.Id).SetColumns(a => new Crm_VisaCustomerCompany
                            {
                                DcId=VisaCustomerCompany.DcId,
                                Company = VisaCustomerCompany.Company,
                                CompanyAddress = VisaCustomerCompany.CompanyAddress,
                                Phone = VisaCustomerCompany.Phone,
                                Job = VisaCustomerCompany.Job,
                                LeaderName = VisaCustomerCompany.LeaderName,
                                WorkStart = VisaCustomerCompany.WorkStart,
                                WorkEnd = VisaCustomerCompany.WorkEnd,
                                WorkState = VisaCustomerCompany.WorkState,
                                CreateUserId = VisaCustomerCompany.CreateUserId,
                                Remark = VisaCustomerCompany.Remark,
                            }).ExecuteCommandAsync();
                            if (res == 0)
                            {
                                result = new Result() { Code = -1, Msg = "客户工作经历保存失败！" };
                                RollbackTran();
                            }
                        }
                        else if (item.Id == 0)//添加
                        {
                            int sss = await _sqlSugar.Insertable(VisaCustomerCompany).ExecuteReturnIdentityAsync();
                            if (sss == 0)
                            {
                                result = new Result() { Code = -1, Msg = "客户工作经历保存失败！" };
                                RollbackTran();
                            }
                        }
                    }
                    //客户学历表
                    foreach (VisaCustomerSchool item in dto.CustomerSchool)
                    {
                        Crm_VisaCustomerSchool VisaCustomerSchool = _mapper.Map<Crm_VisaCustomerSchool>(item);
                        VisaCustomerSchool.DcId = deleId;
                        if (item.Id != 0)//修改
                        {
                            int res = await _sqlSugar.Updateable<Crm_VisaCustomerSchool>().Where(a => a.Id == VisaCustomerSchool.Id).SetColumns(a => new Crm_VisaCustomerSchool
                            {
                                DcId = VisaCustomerSchool.DcId,
                                School = VisaCustomerSchool.School,
                                Address = VisaCustomerSchool.Address,
                                Teacher = VisaCustomerSchool.Teacher,
                                Education = VisaCustomerSchool.Education,
                                Subject = VisaCustomerSchool.Subject,
                                StudyStart = VisaCustomerSchool.StudyStart,
                                StudyEnd = VisaCustomerSchool.StudyEnd,
                                CreateUserId = VisaCustomerSchool.CreateUserId,
                                Remark = VisaCustomerSchool.Remark,
                            }).ExecuteCommandAsync();
                            if (res == 0)
                            {
                                result = new Result() { Code = -1, Msg = "客户学历信息保存失败！" };
                                RollbackTran();
                            }
                        }
                        else if (item.Id == 0)//添加
                        {
                            int sss = await _sqlSugar.Insertable(VisaCustomerSchool).ExecuteReturnIdentityAsync();
                            if (sss == 0)
                            {
                                result = new Result() { Code = -1, Msg = "客户学历信息保存失败！" };
                                RollbackTran();
                            }
                        }
                    }
                    CommitTran();
                }
            }
            catch (Exception ex)
            {
                return result = new Result() { Code = -2, Msg = "程序错误！" };
                throw;
            }
            return result;
        }

        public async Task<Result> DelCustomer(DeleClientDelDto dto)
        {
            Result result = new Result() { Code = -2, Msg = "未知错误" };
            try
            {
                bool isOk=await SoftDeleteByIdAsync<Crm_DeleClient>(dto.Id.ToString(),dto.DeleteUserId);
                if (isOk)//删除其他表数据
                {
                    //客户工作经历
                    await _sqlSugar.Updateable<Crm_VisaCustomerCompany>().Where(a => a.DcId==dto.Id).SetColumns(a => new Crm_VisaCustomerCompany()
                    {
                        IsDel = 1,
                        DeleteUserId = dto.DeleteUserId,
                        DeleteTime = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")
                    }).ExecuteCommandAsync();
                    //客户学历
                    await _sqlSugar.Updateable<Crm_VisaCustomerSchool>().Where(a => a.DcId == dto.Id).SetColumns(a => new Crm_VisaCustomerSchool()
                    {
                        IsDel = 1,
                        DeleteUserId = dto.DeleteUserId,
                        DeleteTime = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")
                    }).ExecuteCommandAsync();
                    //客户家庭成员信息
                    await _sqlSugar.Updateable<Crm_VisaCustomerFamily>().Where(a => a.DcId == dto.Id).SetColumns(a => new Crm_VisaCustomerFamily()
                    {
                        IsDel = 1,
                        DeleteUserId = dto.DeleteUserId,
                        DeleteTime = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")
                    }).ExecuteCommandAsync();
                    //客户证件表
                    await _sqlSugar.Updateable<Crm_CustomerCert>().Where(a => a.DcId == dto.Id).SetColumns(a => new Crm_CustomerCert()
                    {
                        IsDel = 1,
                        DeleteUserId = dto.DeleteUserId,
                        DeleteTime = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")
                    }).ExecuteCommandAsync();
                    return result = new Result() { Code = 0, Msg = "删除成功！" };
                }
            }
            catch (Exception ex)
            {
                return result = new Result() { Code = -2, Msg = "程序错误！" };
                throw;
            }
            return result;
        }
    }
}