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

using Newtonsoft.Json;
using OASystem.Domain;
using OASystem.Domain.Dtos.Financial;
using OASystem.Domain.Dtos.Groups;
using OASystem.Domain.Entities.Financial;
using OASystem.Domain.Entities.Groups;
using OASystem.Domain.ViewModels.Financial;
using OASystem.Domain.ViewModels.Groups;
using OASystem.Infrastructure.Repositories.System;
using Serilog;
using SqlSugar;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace OASystem.Infrastructure.Repositories.Groups
{
    /// <summary>
    /// 团组汇率 仓库
    /// </summary>
    public class TeamRateRepository : BaseRepository<Grp_TeamRate, TeamRateView>
    {
        private readonly SetDataRepository _setDataRep;
        private readonly DelegationInfoRepository _deleInfoRep;

        public TeamRateRepository(SqlSugarClient sqlSugar, SetDataRepository setDataRep, DelegationInfoRepository deleInfoRep)
            : base(sqlSugar)
        {
            _setDataRep = setDataRep;
            _deleInfoRep = deleInfoRep;
        }

        /// <summary>
        /// 团组汇率 Rep
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        public async Task<Result> GetGroupRateDataSource(TeamRateDto dto)
        {

            Result result = new() { Code = -2 };

            GroupNameDto groupNameDto = new GroupNameDto() { PortType = dto.PortType };
            var groups = await _deleInfoRep.GetGroupNameList(groupNameDto);

            var teamRateDatas = await _setDataRep.GetSetDataBySTId(_setDataRep, 66); // STid=66 全球币种

            dynamic _data = null;

            if (dto.PortType == 1)
            {
                List<Sys_SetData> sys_SetDatas = new List<Sys_SetData>();
                sys_SetDatas = JsonConvert.DeserializeObject<List<Sys_SetData>>(JsonConvert.SerializeObject(teamRateDatas.Data));
                List<TeamRateDescView> teamRateDescViews = new List<TeamRateDescView>();


                teamRateDescViews = sys_SetDatas.Select(it => new TeamRateDescView() { CurrencyName = it.Remark, CurrencyCode = it.Name, Rate = 0.0000M }).ToList();

                /*
                 * 放在首位币种
                 * 人名币 CNY，美元 USD，欧元 EUR，港币 HKD，日元 JPY ，澳大利 AUD,英镑 GBP
                 */

                //人名币
                var cnyData = teamRateDescViews.Find(it => it.CurrencyCode.Equals("CNY"));
                if (cnyData != null) { teamRateDescViews.Remove(cnyData); teamRateDescViews.Insert(0, cnyData); }

                //美元
                var usdData = teamRateDescViews.Find(it => it.CurrencyCode.Equals("USD"));
                if (usdData != null) { teamRateDescViews.Remove(usdData); teamRateDescViews.Insert(1, usdData); }

                //欧元
                var eurData = teamRateDescViews.Find(it => it.CurrencyCode.Equals("EUR"));
                if (eurData != null) { teamRateDescViews.Remove(eurData); teamRateDescViews.Insert(2, eurData); }

                //港币
                var hkdData = teamRateDescViews.Find(it => it.CurrencyCode.Equals("HKD"));
                if (hkdData != null) { teamRateDescViews.Remove(hkdData); teamRateDescViews.Insert(3, hkdData); }

                //日元
                var jpyData = teamRateDescViews.Find(it => it.CurrencyCode.Equals("JPY"));
                if (jpyData != null) { teamRateDescViews.Remove(jpyData); teamRateDescViews.Insert(4, jpyData); }

                //澳大利
                var audData = teamRateDescViews.Find(it => it.CurrencyCode.Equals("AUD"));
                if (audData != null) { teamRateDescViews.Remove(audData); teamRateDescViews.Insert(5, audData); }

                //英镑
                var gbpData = teamRateDescViews.Find(it => it.CurrencyCode.Equals("GBP"));
                if (gbpData != null) { teamRateDescViews.Remove(gbpData); teamRateDescViews.Insert(6, gbpData); }

                _data = new { GroupData = groups.Data, TeamRateData = teamRateDescViews };

            }
            else if (dto.PortType == 2)
            {

            }
            else if (dto.PortType == 3)
            {

            }



            result.Code = 0;
            result.Msg = "查询成功!";
            result.Data = _data;

            return result;

        }


        /// <summary>
        /// 团组汇率 更改数据
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        public async Task<Result> GetGroupRateChangeData()
        {

            Result result = new() { Code = -2 };

            string teamRateInfoSql = string.Format(@"Select * From Grp_TeamRate tr Where tr.IsDel = 0");

            var teamRateInfo = await _sqlSugar.SqlQueryable<Grp_TeamRate>(teamRateInfoSql).ToListAsync();

            dynamic _data = null;


            #region 团组汇率 - 详细汇率信息

            List<SetDataInfoView> otherCurrencyData1 = new List<SetDataInfoView>();
            var otherCurrencyData = await _setDataRep.GetSetDataBySTId(_setDataRep, 13);
            if (otherCurrencyData.Code == 0 && otherCurrencyData.Data != null)
            {
                string otherCurrencyStr = JsonConvert.SerializeObject(otherCurrencyData.Data);
                otherCurrencyData1 = JsonConvert.DeserializeObject<List<SetDataInfoView>>(otherCurrencyStr);
                //var otherCurrency = otherCurrencyData1
            }

            foreach (Grp_TeamRate item1 in teamRateInfo)
            {
                string rateStr = string.Empty;

                if (item1.RateU != 0) rateStr += "美元(USD):" + item1.RateU + "|";
                if (item1.RateE != 0) rateStr += "欧元(EUR):" + item1.RateE + "|";

                if (item1.RateJ != 0)
                    rateStr += "日元(JPY):" + item1.RateJ + "|";
                if (item1.RateH != 0)
                    rateStr += "港币(HKD):" + item1.RateH + "|";
                if (item1.RateN != 0)
                    rateStr += "新西兰元(NZD):" + item1.RateN + "|";
                if (item1.RateS != 0)
                    rateStr += "新加坡币(SGD):" + item1.RateS + "|";
                if (item1.RateA != 0)
                    rateStr += "澳大利亚元(AUD):" + item1.RateA + "|";
                if (item1.RateC != 0)
                    rateStr += "加拿大元(CAD):" + item1.RateC + "|";
                if (item1.RateT != 0)
                    rateStr += "泰国铢(THB):" + item1.RateT + "|";
                if (item1.RateBL != 0)
                    rateStr += "波兰兹罗提(PLN):" + item1.RateBL + "|";
                if (item1.RateHB != 0)
                    rateStr += "韩国元(KRW):" + item1.RateHB + "|";
                if (item1.RateFJD != 0)
                    rateStr += "斐济元(FJD):" + item1.RateFJD + "|";
                if (item1.RateTL != 0)
                    rateStr += "土耳其里拉(TRY):" + item1.RateTL + "|";
                if (item1.RateRP != 0)
                    rateStr += "印度尼西亚卢比(IDR):" + item1.RateRP + "|";
                if (item1.RatePeso != 0)
                    rateStr += "菲律宾比索(PHP):" + item1.RatePeso + "|";
                if (item1.RateMYR != 0)
                    rateStr += "林吉特(MYR):" + item1.RateMYR + "|";
                if (item1.RateCZK != 0)
                    rateStr += "捷克货币(CZK):" + item1.RateCZK + "|";
                if (item1.RateMXN != 0)
                    rateStr += "墨西哥比索(MXN):" + item1.RateMXN + "|";
                if (item1.RateMOP != 0)
                    rateStr += "澳门元(MOP):" + item1.RateMOP + "|";
                if (item1.RateARS != 0)
                    rateStr += "阿根廷比索(ARS):" + item1.RateARS + "|";
                if (item1.RateHUF != 0)
                    rateStr += "匈牙利福林(HUF):" + item1.RateHUF + "|";
                if (item1.RateRUB != 0)
                    rateStr += "卢布(RUB):" + item1.RateRUB + "|";



                if (!string.IsNullOrEmpty(item1.OtherRateCode))
                {


                    rateStr += $"{otherCurrencyData1.Find(it => it.Name.Equals(item1.OtherRateCode))?.Remark ?? "Unknown"}({item1.OtherRateCode}):{item1.OtherPrice}|";
                    
                    
                }

                if (!rateStr.Contains("CNY"))
                {
                    rateStr += "人名币(CNY):1.0000|";
                }

                if (rateStr.Length > 0)
                {
                    rateStr = rateStr.Substring(0, rateStr.Length - 1);
                }

                item1.Remark = rateStr;
            }

            #endregion

            try
            {

                var _result = _sqlSugar.Updateable(teamRateInfo)
                    .UpdateColumns(it => new { it.Remark }) //更新列
                    .WhereColumns(it => new { it.DiId, it.CTable })     //更新条件
                    .ExecuteCommand();

                result.Code = 0;
                result.Msg = "数据修改成功!";
                result.Data = _data;
            }
            catch (Exception ex)
            {

                result.Msg = ex.Message;
            }

            return result;

        }

        /// <summary>
        /// 团组汇率 Rep
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        public async Task<Result> GetGroupRateInfoByDiid(TeamRateInfoDto dto)
        {

            Result result = new() { Code = -2 };

            GroupInfoDto groupInfoDto = new GroupInfoDto() { Id = dto.Diid, PortType = dto.PortType, PageIndex = dto.PageIndex, PageSize = dto.PageSize };
            var groupInfo = await _deleInfoRep.GetGroupInfo(groupInfoDto);

            string teamRateInfoSql = string.Format(@"Select sd.Name,tr.* From Grp_TeamRate tr 
                                                     Left Join Sys_SetData  sd On sd.IsDel=0 And sd.STid=16  And tr.CTable = sd.Id 
                                                     Where tr.DiId = {0} And tr.IsDel = 0", dto.Diid);

            var teamRateInfo = await _sqlSugar.SqlQueryable<TeamRateInfoView>(teamRateInfoSql).ToListAsync();

            dynamic _data = null;

            if (dto.PortType == 1)
            {
                #region 团组汇率

                List<TeamRateModelView> teamRateModels = new List<TeamRateModelView>();

                foreach (TeamRateInfoView item in teamRateInfo)
                {
                    TeamRateModelView teamRateModelInfo = new TeamRateModelView();

                    teamRateModelInfo.Id = item.Id;
                    teamRateModelInfo.CTableId = item.CTable;
                    teamRateModelInfo.CTableName = item.Name;
                    List<TeamRateDescView> teamRateDescViews = new List<TeamRateDescView>();

                    #region 拆分remark里的汇率

                    if (item.Remark.Contains("|"))
                    {
                        string[] currencyArr = item.Remark.Split("|");
                        foreach (string currency in currencyArr)
                        {
                            string[] currency1 = currency.Split(":");
                            string[] currency2 = currency1[0].Split("(");

                            TeamRateDescView rateDescView = new TeamRateDescView()
                            {
                                CurrencyCode = currency2[1].Replace(")", "").TrimEnd(),
                                CurrencyName = currency2[0],
                                Rate = decimal.Parse(currency1[1]),
                            };
                            teamRateDescViews.Add(rateDescView);
                        }
                    }
                    else
                    {
                        if (!string.IsNullOrEmpty(item.Remark))
                        {
                            string[] currency1 = item.Remark.Split(":");
                            string[] currency2 = currency1[0].Split("(");

                            TeamRateDescView rateDescView = new TeamRateDescView()
                            {
                                CurrencyCode = currency2[1].Replace(")", "").TrimEnd(),
                                CurrencyName = currency2[0],
                                Rate = decimal.Parse(currency1[1]),
                            };
                            teamRateDescViews.Add(rateDescView);
                        }
                    }

                    #endregion

                    teamRateModelInfo.TeamRates = teamRateDescViews;
                    teamRateModels.Add(teamRateModelInfo);
                }

                #endregion
                _data = new { GroupInfo = groupInfo.Data, TeamRateData = teamRateModels };

            }
            else if (dto.PortType == 2)
            {

            }
            else if (dto.PortType == 3)
            {

            }

            result.Code = 0;
            result.Msg = "查询成功!";
            result.Data = _data;

            return result;

        }

        /// <summary>
        /// 团组汇率 更新 or 添加
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        public async Task<Result> PostGroupRateUpdate(TeamRateUpdateDto dto)
        {
            Result result = new() { Code = -2 };
            int addCount = 0, updateCount = 0;
            if (dto.PortType == 1)
            {
                try
                {
                    _sqlSugar.BeginTran();
                    int res = -1;
                    foreach (TeamRateUpdateInfo item in dto.teamRateUpdateInfos)
                    {
                        Grp_TeamRate teamRateInfo = new Grp_TeamRate();
                        string currencyStr = string.Empty;
                        foreach (TeamRateDescView currency in item.teamRates)
                        {
                            currencyStr += string.Format(@"{0}({1}):{2}|", currency.CurrencyName, currency.CurrencyCode, currency.Rate);
                        }
                        if (currencyStr.Length > 0)
                        {
                            currencyStr = currencyStr.Substring(0, currencyStr.Length - 1);
                        }

                        teamRateInfo.DiId = dto.DiId;
                        teamRateInfo.CreateUserId = dto.CreateUserId;
                        teamRateInfo.Id = item.Id;
                        teamRateInfo.CreateTime = DateTime.Now;
                        teamRateInfo.CTable = item.CTable;
                        teamRateInfo.Remark = currencyStr;

                        var teamRateDetails = _sqlSugar.Queryable<Grp_TeamRate>().Where(it => it.DiId == dto.DiId && it.CTable == item.CTable).First();
                        if (teamRateDetails == null) //添加
                        {
                            res = await _sqlSugar.Insertable(teamRateInfo).ExecuteCommandAsync();
                            if (res > 0)
                            {
                                addCount++;
                            }

                        }
                        else //修改
                        {
                            teamRateInfo.Id = teamRateDetails.Id;
                            res = await _sqlSugar.Updateable(teamRateInfo)
                                        .UpdateColumns(it => it.Remark)
                                        .WhereColumns(it => new { it.DiId, it.CTable })
                                        .ExecuteCommandAsync();
                            if (res > 0)
                            {
                                updateCount++;
                            }
                        }
                        if (res < 1)
                        {
                            _sqlSugar.RollbackTran();
                            result.Msg = "操作失败!";
                            break;
                        }

                    }

                    if (res > 0)
                    {
                        _sqlSugar.CommitTran();
                        result.Code = 0;
                        result.Msg = "操作成功";
                    }

                }
                catch (Exception ex)
                {

                    result.Msg = ex.Message;
                }


            }
            else if (dto.PortType == 2)
            {

            }
            else if (dto.PortType == 3)
            {

            }

            result.Code = 0;
            result.Msg = string.Format(@"操作成功!添加：{0}条；更新：{1}；", addCount, updateCount);

            return result;

        }

        /// <summary>
        /// 团组汇率 Rep
        /// 根据团组Id 查询汇率
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        public async Task<List<TeamRateModelView>> PostGroupRateInfoByDiId(int diId)
        {

            List<TeamRateModelView> teamRateModels = new List<TeamRateModelView>();
            string teamRateInfoSql = string.Format(@"Select sd.Name,tr.* From Grp_TeamRate tr 
                                                     Left Join Sys_SetData  sd On sd.IsDel=0 And sd.STid=16  And tr.CTable = sd.Id 
                                                     Where tr.DiId = {0} And tr.IsDel = 0", diId);

            var teamRateInfo = await _sqlSugar.SqlQueryable<TeamRateInfoView>(teamRateInfoSql).ToListAsync();

            #region 团组汇率


            foreach (TeamRateInfoView item in teamRateInfo)
            {
                TeamRateModelView teamRateModelInfo = new TeamRateModelView();

                teamRateModelInfo.Id = item.Id;
                teamRateModelInfo.CTableId = item.CTable;
                teamRateModelInfo.CTableName = item.Name;
                List<TeamRateDescView> teamRateDescViews = new List<TeamRateDescView>();

                #region 拆分remark里的汇率

                if (item.Remark.Contains("|"))
                {
                    string[] currencyArr = item.Remark.Split("|");
                    foreach (string currency in currencyArr)
                    {
                        string[] currency1 = currency.Split(":");
                        string[] currency2 = currency1[0].Split("(");

                        TeamRateDescView rateDescView = new TeamRateDescView()
                        {
                            CurrencyCode = currency2[1].Replace(")", "").TrimEnd(),
                            CurrencyName = currency2[0],
                            Rate = decimal.Parse(currency1[1]),
                        };
                        teamRateDescViews.Add(rateDescView);
                    }
                }

                #endregion

                teamRateModelInfo.TeamRates = teamRateDescViews;
                teamRateModels.Add(teamRateModelInfo);
            }

            #endregion


            return teamRateModels;

        }


        /// <summary>
        /// 团组汇率 币种 Item (来源：团组汇率)
        /// 根据 团组Id And 业务类型(CTable)Id
        /// api处理CTable = 285,默认返回CNY
        /// </summary>
        /// <param name="portType"></param>
        /// <param name="diId"></param>
        /// <param name="cTable"></param>
        /// <returns></returns>
        public async Task<TeamRateModelGeneralView> PostGroupTeamRateByDiIdAndCTableId(int portType, int diId, int cTable)
        {

            TeamRateModelGeneralView _view = new TeamRateModelGeneralView();

            if (diId < 1)
            {
                return _view;
            }
            if (cTable < 1)
            {
                return _view;
            }

            if (portType == 1 || portType == 2 || portType == 3)
            {
                string teamRateInfoSql = string.Format(@"Select sd.Name,tr.* From Grp_TeamRate tr 
                                                     Left Join Sys_SetData  sd On sd.IsDel=0 And sd.STid=16  And tr.CTable = sd.Id 
                                                     Where tr.IsDel = 0 And tr.DiId = {0} And tr.CTable = {1}", diId, cTable);

                var teamRateInfo = _sqlSugar.SqlQueryable<TeamRateInfoView>(teamRateInfoSql).ToList();

                #region 团组汇率

                #region 单独处理Ctable 币种汇率 默认 为CNY
                if (cTable == 285)
                {
                    _view = new TeamRateModelGeneralView()
                    {
                        Id = 0,
                        CTableId = 285,
                        CTableName = "其他款项与收款退还",
                        TeamRates = new List<TeamRateDescAddCurrencyIdView>() {
                                new TeamRateDescAddCurrencyIdView()
                                {
                                    CurrencyId = 836,
                                    CurrencyCode = "CNY",
                                    CurrencyName = "人民币",
                                    Rate = 1.0000M,
                                }
                            }
                    };
                    return _view;
                }

                #endregion

                List<SetDataInfoView> currencyDatas = new List<SetDataInfoView>();
                #region 获取所有币种

                string sql = string.Format(@"select * from Sys_SetData where STid = {0} and isdel = 0", 66);
                var DBdata = _setDataRep.GetListBySqlWithNolock(sql);

                if (DBdata == null || DBdata.Count == 0)
                {
                    return _view;
                }

                currencyDatas = DBdata.Select(x => new SetDataInfoView
                {
                    Name = x.Name,
                    Id = x.Id,
                    Remark = x.Remark,
                }).ToList();

                #endregion

                foreach (TeamRateInfoView item in teamRateInfo)
                {
                    TeamRateModelGeneralView teamRateModelInfo = new TeamRateModelGeneralView();

                    teamRateModelInfo.Id = item.Id;
                    teamRateModelInfo.CTableId = item.CTable;
                    teamRateModelInfo.CTableName = item.Name;
                    List<TeamRateDescAddCurrencyIdView> teamRateDescViews = new List<TeamRateDescAddCurrencyIdView>();

                    #region 拆分remark里的汇率

                    if (item.Remark.Contains("|"))
                    {
                        string[] currencyArr = item.Remark.Split("|");
                        foreach (string currency in currencyArr)
                        {
                            string[] currency1 = currency.Split(":");
                            string[] currency2 = currency1[0].Split("(");

                            string currencyCode = currency2[1].Replace(")", "").TrimEnd();
                            SetDataInfoView dataInfoView = new SetDataInfoView();
                            dataInfoView = currencyDatas.Where(it => it.Name == currencyCode).FirstOrDefault();
                            int currencyId = currencyDatas.Where(it => it.Name == currencyCode).FirstOrDefault().Id;
                            TeamRateDescAddCurrencyIdView rateDescView = new TeamRateDescAddCurrencyIdView()
                            {
                                CurrencyId = dataInfoView.Id,
                                CurrencyCode = currencyCode,
                                CurrencyName = currency2[0],
                                Rate = decimal.Parse(currency1[1]),
                            };
                            teamRateDescViews.Add(rateDescView);
                        }
                    }
                    else
                    {
                        if (!string.IsNullOrEmpty(item.Remark))
                        {
                            string[] currency1 = item.Remark.Split(":");
                            string[] currency2 = currency1[0].Split("(");

                            string currencyCode = currency2[1].Replace(")", "").TrimEnd();
                            SetDataInfoView dataInfoView = new SetDataInfoView();
                            dataInfoView = currencyDatas.Where(it => it.Name == currencyCode).FirstOrDefault();
                            int currencyId = currencyDatas.Where(it => it.Name == currencyCode).FirstOrDefault().Id;
                            TeamRateDescAddCurrencyIdView rateDescView = new TeamRateDescAddCurrencyIdView()
                            {
                                CurrencyId = dataInfoView.Id,
                                CurrencyCode = currencyCode,
                                CurrencyName = currency2[0],
                                Rate = decimal.Parse(currency1[1]),
                            };
                            teamRateDescViews.Add(rateDescView);
                        }
                    }

                    #endregion

                    if (teamRateDescViews.Count > 0)
                    {
                        teamRateDescViews = teamRateDescViews.OrderBy(it => it.CurrencyId).ToList();
                    }

                    teamRateModelInfo.TeamRates = teamRateDescViews;
                    _view = teamRateModelInfo;
                }

                if (_view.TeamRates == null)
                {
                    _view.TeamRates = new List<TeamRateDescAddCurrencyIdView>();
                }

                #endregion

            }
            return _view;
        }

        /// <summary>
        /// 获取团组相关模块的currencyList
        /// </summary>
        /// <param name="portType"></param>
        /// <param name="diId"></param>
        /// <param name="cTable"></param>
        /// <returns></returns>
        public async Task<List<TeamRateDescAddCurrencyIdView>> PostGroupTeamRateItemByDiIdAndCTableId(int portType, int diId, int cTable)
        {
            List<TeamRateDescAddCurrencyIdView> teamRateDescAddCurrencyIdViews = new List<TeamRateDescAddCurrencyIdView>();
            TeamRateModelGeneralView teamRateModelGeneralView = await PostGroupTeamRateByDiIdAndCTableId(portType, diId, cTable);
            teamRateDescAddCurrencyIdViews = teamRateModelGeneralView.TeamRates;

            return teamRateDescAddCurrencyIdViews;
        }
    }
}