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

using AutoMapper;
using MathNet.Numerics.Statistics.Mcmc;
using NPOI.SS.Formula.Functions;
using OASystem.Domain;
using OASystem.Domain.Dtos.Groups;
using OASystem.Domain.Entities.Financial;
using OASystem.Domain.Entities.Groups;
using OASystem.Domain.Entities.Resource;
using OASystem.Domain.ViewModels.Groups;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Security.Cryptography;
using System.Text;
using System.Threading.Tasks;

namespace OASystem.Infrastructure.Repositories.Groups
{
    public class InvitationOfficialActivitiesRepository : BaseRepository<Grp_InvitationOfficialActivities, InvitationOfficialActivitiesListDto>
    {
        private readonly IMapper _mapper;
        public InvitationOfficialActivitiesRepository(SqlSugarClient sqlSugar, IMapper mapper) :
            base(sqlSugar)
        {
            this._mapper = mapper;
        }
        /// <summary>
        /// 根据商邀费用Id查询C表和商邀资料及费用表
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        public async Task<Result> InvitationOfficialActivitiesById(InvitationOfficialActivitiesByIdDto dto)
        {
            Result result = new Result() { Code = -2, Msg = "程序错误" };
            try
            {
                Grp_InvitationOfficialActivities grp_Invitation = _sqlSugar.Queryable<Grp_InvitationOfficialActivities>().First(a => a.Id == dto.Id && a.IsDel == 0);
                Grp_CreditCardPayment grp_CreditCard = _sqlSugar.Queryable<Grp_CreditCardPayment>().First(a => a.CId == dto.Id && a.IsDel == 0 && a.CTable==81);
                Res_InvitationOfficialActivityData res_InvitationOfficialActivityData = new Res_InvitationOfficialActivityData();
                if (grp_Invitation!=null)
                {
                    res_InvitationOfficialActivityData = _sqlSugar.Queryable<Res_InvitationOfficialActivityData>().First(a =>a.Country==grp_Invitation.InviterArea && a.UnitName==grp_Invitation.Inviter && a.IsDel == 0);
                }
                var data = new
                {
                    _Invitation = grp_Invitation,
                    _InvitationData = res_InvitationOfficialActivityData,
                    _CreditCard = grp_CreditCard,
                };
                result = new Result() { Code = 0, Msg = "查询成功！", Data = data };
            }
            catch (Exception)
            {
                result = new Result() { Code = -2, Msg = "程序错误" };
                throw;
            }
            return result;
        }
        /// <summary>
        /// 根据团组id查询商邀费用数据
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        public async Task<Result> InvitationOfficialActivitiesList(InvitationOfficialActivitiesListDto dto)
        {
            Result result = new Result() { Code = -2, Msg = "未知错误" };
            try
            {
                string sqlWhere = string.Empty;
                if (!string.IsNullOrWhiteSpace(dto.Inviter))
                {
                    sqlWhere += string.Format(@" And Inviter like '%{0}%'", dto.Inviter);
                }
                if (!string.IsNullOrWhiteSpace(dto.StartInviteTime) && !string.IsNullOrWhiteSpace(dto.EndInviteTime))
                {
                    sqlWhere += string.Format(@" And i.InviteTime between '{0}' and '{1}'", dto.StartInviteTime, dto.EndInviteTime);
                }
                sqlWhere += string.Format(@" And i.DiId={0} And i.IsDel={1}", dto.DiId, 0);

                if (!string.IsNullOrEmpty(sqlWhere.Trim()))
                {
                    Regex r = new Regex("And");
                    sqlWhere = r.Replace(sqlWhere, "Where", 1);
                }
                string sql = string.Format(@"select Id,DiId,InviterArea,Inviter,InviteTime,InviteCosts,(select name from Sys_SetData where id=i.Currency and IsDel={0}) 
                                             as Currency,IsGoOfficaiaBussiness,(select IsAuditGM from Grp_CreditCardPayment where CTable=81 and CId=i.Id 
                                             and IsDel={0}) as 'isAudit',Attachment from Grp_InvitationOfficialActivities i  {1}", 0, sqlWhere);
                List<InvitationOfficialActivitiesView> _DecreasePayments = await _sqlSugar.SqlQueryable<InvitationOfficialActivitiesView>(sql).ToListAsync();
                if (_DecreasePayments.Count != 0)
                {
                    if (dto.PageIndex != 0 && dto.PageSize != 0)
                    {

                        int count = _DecreasePayments.Count;
                        float totalPage = (float)count / dto.PageSize;//总页数
                        if (totalPage == 0) totalPage = 1;
                        else totalPage = (int)Math.Ceiling((double)totalPage);

                        List<InvitationOfficialActivitiesView> invitationOfficialActivities = new List<InvitationOfficialActivitiesView>();
                        for (int i = 0; i < dto.PageSize; i++)
                        {
                            var RowIndex = i + (dto.PageIndex - 1) * dto.PageSize;
                            if (RowIndex < _DecreasePayments.Count)
                            {
                                invitationOfficialActivities.Add(_DecreasePayments[RowIndex]);
                            }
                            else
                            {
                                break;
                            }
                        }
                        ListViewBase<InvitationOfficialActivitiesView> rst = new ListViewBase<InvitationOfficialActivitiesView>();
                        rst.DataList = invitationOfficialActivities;
                        rst.DataCount = count;
                        rst.CurrPageIndex = dto.PageIndex;
                        rst.CurrPageSize = dto.PageSize;
                        return result = new Result() { Code = 0, Msg = "查询成功！", Data = rst };
                    }
                    else
                    {
                        return result = new Result() { Code = 0, Msg = "查询成功！", Data = _DecreasePayments };
                    }
                }
                else
                {
                    if (dto.PageIndex != 0 && dto.PageSize != 0)
                    {
                        ListViewBase<InvitationOfficialActivitiesView> rst = new ListViewBase<InvitationOfficialActivitiesView>();
                        rst.DataList = _DecreasePayments;
                        rst.DataCount = _DecreasePayments.Count;
                        rst.CurrPageIndex = dto.PageIndex;
                        rst.CurrPageSize = dto.PageSize;
                        return result = new Result() { Code = 0, Msg = "暂无数据！", Data = rst };
                    }
                    else
                    {
                        return result = new Result() { Code = 0, Msg = "暂无数据！", Data = _DecreasePayments };
                    }
                   
                }
            }
            catch (Exception ex)
            {
                return result = new Result() { Code = -2, Msg = "未知错误" };
            }
        }
        /// <summary>
        /// 商邀费用操作（Status:1.新增,2.修改）
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        public async Task<Result> OpInvitationOfficialActivities(OpInvitationOfficialActivitiesDto dto)
        {
            Result result = new Result() { Code = -2, Msg = "程序错误！" };
            BeginTran();
            try
            {
                int id = 0;
                Grp_InvitationOfficialActivities grp_Invitation = _mapper.Map<Grp_InvitationOfficialActivities>(dto);
                if (dto.Status == 1)//添加
                {
                    string selectSql = string.Format(@"select * from Grp_InvitationOfficialActivities where InviterArea='{0}' and Inviter='{1}' and DiId={2} and IsDel={3}"
                                                    , dto.InviterArea, dto.Inviter, dto.DiId, 0);
                    var _InvitationOfficialActivities = await _sqlSugar.SqlQueryable<Grp_InvitationOfficialActivities>(selectSql).FirstAsync();//查询是否存在
                    if (_InvitationOfficialActivities != null)
                    {
                        return result = new Result() { Code = -1, Msg = "该数据已存在，请勿重复添加！" };
                    }
                    else
                    {
                        id = await AddAsyncReturnId(grp_Invitation);
                        if (id != 0)//修改或添加商邀资料
                        {
                            Res_InvitationOfficialActivityData res_InvitationData = _mapper.Map<Res_InvitationOfficialActivityData>(dto);
                            res_InvitationData.Country = dto.InviterArea;
                            res_InvitationData.UnitName = dto.Inviter;
                            res_InvitationData.Delegation = dto.DiId.ToString();
                            Res_InvitationOfficialActivityData ifNullUp = await _sqlSugar.Queryable<Res_InvitationOfficialActivityData>().FirstAsync
                                (a => a.Country == res_InvitationData.Country && a.City == res_InvitationData.City && a.UnitName == res_InvitationData.UnitName);
                            if (ifNullUp == null)///添加或修改商邀资料
                            {
                                res_InvitationData.Remark = dto.OtherInformation;
                                int DataID = await _sqlSugar.Insertable(res_InvitationData).ExecuteReturnIdentityAsync();
                                if (DataID != 0)
                                {
                                    result = new Result() { Code = 0, Msg = "添加成功！" };
                                }
                                else
                                {
                                    RollbackTran();
                                    result = new Result() { Code = -1, Msg = "添加失败！" };
                                }

                            }
                            else
                            {
                                int CTable = await _sqlSugar.Updateable<Res_InvitationOfficialActivityData>().Where(a => a.Id == ifNullUp.Id).SetColumns(a => new Res_InvitationOfficialActivityData
                                {
                                   Contact=dto.Contact,
                                   Tel=dto.Tel,
                                   Email=dto.Email,
                                   Fax=dto.Fax,
                                   Address=dto.Address,
                                   Remark=dto.OtherInformation,
                                }).ExecuteCommandAsync();
                            }
                            Grp_CreditCardPayment C = new Grp_CreditCardPayment();
                            C.PayDId = dto.PayDId;
                            C.ConsumptionPatterns = "";
                            C.ConsumptionDate = "";
                            C.CTDId = 0;
                            C.BankNo = "";
                            C.CardholderName = "";
                            C.PayMoney = dto.InviteCosts + dto.SendCost;
                            C.PaymentCurrency = dto.Currency;
                            //当天汇率
                            //if (!string.IsNullOrEmpty(hfRate.Value))
                            //    C.DayRate = hfRate.Value;
                            //else
                            //C.DayRate = "";
                            C.CompanyBankNo = "";
                            C.OtherBankName = "";
                            C.OtherSideNo = "";
                            C.OtherSideName = "";
                            C.Remark = "";
                            C.CreateUserId = dto.CreateUserId;
                            C.MFOperator = "";
                            C.MFOperatorDate = "";
                            C.IsAuditDM = 0;
                            C.AuditDMOperate = 0;
                            C.AuditDMDate = "";
                            C.IsAuditMF = 0;
                            C.AuditMFOperate = 0;
                            C.AuditMFDate = "";
                            C.IsAuditGM = 0;
                            C.AuditGMOperate = 21;
                            C.AuditGMDate = "";
                            C.IsPay = 0;
                            C.DIId = dto.DiId;
                            C.CId = id;
                            C.CTable = 81;
                            C.PayPercentage = 0;
                            C.PayThenMoney = 0;
                            C.PayPercentageOld = 0;
                            C.PayThenMoneyOld = 0;
                            C.UpdateDate = "";
                            C.Payee = dto.Payee;
                            C.OrbitalPrivateTransfer = dto.OrbitalPrivateTransfer;
                            C.ExceedBudget = 0;
                            //C.RMBPrice = 0.00f;

                            //设置该团组的汇率
                            Grp_TeamRate _TeamRate = _sqlSugar.Queryable<Grp_TeamRate>().First(a => a.DiId == dto.DiId && a.IsDel == 0 && a.CTable == 81);
                            if (_TeamRate != null)
                            {
                                if (dto.Currency == 49)
                                {
                                    C.DayRate = _TeamRate.RateU;
                                    C.RMBPrice = C.PayMoney * Convert.ToDecimal(_TeamRate.RateU);
                                    //ccp.PayMoney = ccp.PayMoney * float.Parse(tr.RateU); 
                                }
                                else if (dto.Currency == 51)
                                {
                                    C.DayRate = _TeamRate.RateE;
                                    C.RMBPrice = C.PayMoney * Convert.ToDecimal(_TeamRate.RateE);
                                    //ccp.PayMoney = ccp.PayMoney * float.Parse(tr.RateE); 
                                }
                                else
                                {
                                    C.DayRate = 1M;
                                    C.RMBPrice = C.PayMoney;
                                }
                            }
                            else
                            {
                                C.DayRate = 1M;
                                C.RMBPrice = C.PayMoney;
                            }
                            int cId = await _sqlSugar.Insertable(C).ExecuteReturnIdentityAsync();
                            if (cId != 0)
                            {
                                result = new Result() { Code = 0, Msg = "添加成功！" };
                            }
                            else
                            {
                                RollbackTran();
                                result = new Result() { Code = -1, Msg = "添加失败！" };
                            }
                        }
                    }
                }
                else//修改
                {
                    bool res = await UpdateAsync(a => a.Id == grp_Invitation.Id, a => new Grp_InvitationOfficialActivities
                    {
                        DiId = dto.DiId,
                        InviterArea = dto.InviterArea,
                        Inviter = dto.Inviter,
                        InviteTime = dto.InviteTime,
                        Attachment = dto.Attachment,
                        InviteCosts = dto.InviteCosts,
                        Currency = dto.Currency,
                        SendCost = dto.SendCost,
                        IsGoOfficaiaBussiness = dto.IsGoOfficaiaBussiness,
                        Remark = dto.Remark,
                    });
                    if (res)
                    {
                        Res_InvitationOfficialActivityData res_InvitationData = _mapper.Map<Res_InvitationOfficialActivityData>(dto);
                        res_InvitationData.Country = dto.InviterArea;
                        res_InvitationData.UnitName = dto.Inviter;
                        Res_InvitationOfficialActivityData ifNullUp = await _sqlSugar.Queryable<Res_InvitationOfficialActivityData>().FirstAsync
                            (a => a.Country == res_InvitationData.Country && a.City == res_InvitationData.City && a.UnitName == res_InvitationData.UnitName);
                        if (ifNullUp == null)///添加或修改商邀资料
                        {
                            res_InvitationData.Remark =dto.OtherInformation;
                            res_InvitationData.Delegation = dto.DiId.ToString();

                            int DataID = await _sqlSugar.Insertable(res_InvitationData).ExecuteReturnIdentityAsync();
                            if (DataID != 0)
                            {
                                result = new Result() { Code = 0, Msg = "添加成功！" };
                            }
                            else
                            {
                                RollbackTran();
                                result = new Result() { Code = -1, Msg = "添加失败！" };
                            }
                                
                        } else
                            {
                                int CTable = await _sqlSugar.Updateable<Res_InvitationOfficialActivityData>().Where(a => a.Id == ifNullUp.Id).SetColumns(a => new Res_InvitationOfficialActivityData
                                {
                                   Contact=dto.Contact,
                                   Tel=dto.Tel,
                                   Email=dto.Email,
                                   Fax=dto.Fax,
                                   Address=dto.Address,
                                   Remark=dto.OtherInformation,
                                }).ExecuteCommandAsync();
                            }
                       
                            Grp_CreditCardPayment grp_CreditCardPayment = _sqlSugar.Queryable<Grp_CreditCardPayment>().First(a => a.CId == grp_Invitation.Id && a.CTable==81 && a.IsDel == 0);
                            if (grp_CreditCardPayment != null)
                            {
                                grp_CreditCardPayment.PayMoney = dto.InviteCosts + dto.SendCost;
                                Grp_TeamRate _TeamRate = _sqlSugar.Queryable<Grp_TeamRate>().First(a => a.DiId == grp_Invitation.DiId && a.IsDel == 0 && a.IsDel==0 && a.CTable == 81);
                                if (_TeamRate != null)
                                {

                                    if (grp_Invitation.Currency == 49)
                                    {
                                        grp_CreditCardPayment.DayRate = _TeamRate.RateU;
                                        grp_CreditCardPayment.RMBPrice = grp_CreditCardPayment.PayMoney * Convert.ToDecimal(_TeamRate.RateU);
                                        //ccp.PayMoney = ccp.PayMoney * float.Parse(tr.RateU); 
                                    }
                                    else if (grp_Invitation.Currency == 51)
                                    {
                                        grp_CreditCardPayment.DayRate = _TeamRate.RateE;
                                        grp_CreditCardPayment.RMBPrice = grp_CreditCardPayment.PayMoney * Convert.ToDecimal(_TeamRate.RateE);
                                        //ccp.PayMoney = ccp.PayMoney * float.Parse(tr.RateE); 
                                    }
                                    else
                                    {
                                        grp_CreditCardPayment.DayRate = 1M;
                                        grp_CreditCardPayment.RMBPrice = grp_CreditCardPayment.PayMoney;
                                    }
                                }
                                else
                                {
                                    grp_CreditCardPayment.DayRate = 1M;
                                    grp_CreditCardPayment.RMBPrice = grp_CreditCardPayment.PayMoney;
                                }
                                int CTable = await _sqlSugar.Updateable<Grp_CreditCardPayment>().Where(a => a.Id == grp_CreditCardPayment.Id).SetColumns(a => new Grp_CreditCardPayment
                                {
                                    PayDId = dto.PayDId,
                                    PayMoney = grp_CreditCardPayment.PayMoney,
                                    PaymentCurrency = grp_Invitation.Currency,
                                    Payee = dto.Payee,
                                    OrbitalPrivateTransfer = dto.OrbitalPrivateTransfer,
                                    DayRate = grp_CreditCardPayment.DayRate,
                                    RMBPrice = grp_CreditCardPayment.RMBPrice,

                                }).ExecuteCommandAsync();
                                if (CTable != 0)
                                {
                                    result = new Result() { Code = 0, Msg = "修改成功！" };
                                }
                                else
                                {
                                    result = new Result() { Code = -1, Msg = "修改失败！" };
                                    RollbackTran();
                                }
                            }
                            else
                            {
                                RollbackTran();
                                result = new Result() { Code = -1, Msg = "修改失败！" };
                            }
                    }
                    else
                    {
                        RollbackTran();
                        result = new Result() { Code = -1, Msg = "修改失败！" };
                    }
                }
                CommitTran();
            }
            catch (Exception ex)
            {
                result = new Result() { Code = -2, Msg = "程序错误！" };
                throw;
            }
            return result;
        }
    }
}