OA2023/OASystem/OASystem.Infrastructure/Repositories/Financial/ForeignReceivablesRepository.cs

using AutoMapper;
using MathNet.Numerics.Statistics.Mcmc;
using NPOI.SS.Formula.Functions;
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.Groups;
using OASystem.Infrastructure.Repositories.System;
using SqlSugar;
using SqlSugar.Extensions;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace OASystem.Infrastructure.Repositories.Financial
{
    /// <summary>
    /// 财务 - 团组应收款项 仓库
    /// 雷怡 2023.08.16 15：03
    /// </summary>
    public class ForeignReceivablesRepository : BaseRepository<Fin_ForeignReceivables, Fin_ForeignReceivablesView>
    {
        private readonly IMapper _mapper;
        private readonly DelegationInfoRepository _delegationRep;
        private readonly SetDataRepository _setDataRep;
        private readonly List<int> _portTypes = new List<int>() { 1, 2, 3 };

        public ForeignReceivablesRepository(SqlSugarClient sqlSugar, IMapper mapper, DelegationInfoRepository delegationRep, SetDataRepository setDataRep)
            : base(sqlSugar)
        {
            _mapper = mapper;
            _delegationRep = delegationRep;
            _setDataRep = setDataRep;
        }

        #region 关联已收款项

        /// <summary>
        /// 收款账单 数据源
        /// </summary>
        /// <returns></returns>
        public async Task<JsonView> GetDataSource(ForeignReceivablesDataSourcesDto dto)
        {
            JsonView result = new() { Code = StatusCodes.Status204NoContent };

            //已收款项 判断如果是市场部的人员进来的话 只显示自己的 其他的都显示全部的
            var userInfos = await _sqlSugar.Queryable<Sys_Users>()
                                           .InnerJoin<Sys_Department>((u, d) => u.DepId == d.Id)
                                           .Where((u, d) => u.IsDel == 0 && d.DepName.Contains("市场部") && u.Id == dto.CurrUserId)
                                           .ToListAsync();

            string sqlWhere = "";
            if (userInfos.Count > 0) sqlWhere = string.Format(@$" And JietuanOperator={dto.CurrUserId} ");

            string sql = string.Format(@$"Select Id,TeamName GroupName From  Grp_DelegationInfo 
                                             Where TeamName != '' And IsDel = 0 {sqlWhere} 
                                             Order By Id Desc");

            var _groupNameList = await _sqlSugar.SqlQueryable<GroupNameView>(sql).ToListAsync();

            //var groupNameData = await _delegationRep.GetGroupNameList(new GroupNameDto());
            var currencyData = await _setDataRep.GetSetDataBySTId(_setDataRep, 66); //币种
            var remittanceMethodData = await _setDataRep.GetSetDataBySTId(_setDataRep, 14); //汇款方式

            result.Code = StatusCodes.Status200OK;
            result.Msg = "成功！";
            result.Data = new
            {
                GroupNameData = _groupNameList,
                CurrencyData = currencyData.Data,
                RemittanceMethodData = remittanceMethodData.Data
            };

            return result;
        }

        /// <summary>
        /// 根据diid查询团组应收款项
        /// </summary>
        /// <param name="diid"></param>
        /// <returns></returns>
        public async Task<Result> GetGroupReceivablesInfoByDiId(ForForeignReceivablesInfoDto dto)
        {
            Result result = new() { Code = -2 };

            var groupInfoData = await _delegationRep.GetGroupInfo(new GroupInfoDto() { Id = dto.DiId });

            //应收款项
            string groupReceivedSql = string.Format(@"Select * From Fin_ForeignReceivables Where IsDel=0 And Diid={0}", dto.DiId);
            var groupReceivedList = await _sqlSugar.SqlQueryable<ForeignReceivablesView>(groupReceivedSql).ToListAsync();

            //已收款项
            string groupProceedsReceivedSql = string.Format(@"Select * From Fin_ProceedsReceived Where IsDel=0 And Diid={0}", dto.DiId);
            var groupProceedsReceivedList = await _sqlSugar.SqlQueryable<ProceedsReceivedView>(groupProceedsReceivedSql).ToListAsync();

            List<ProceedsReceivedView> NotFIDData = new List<ProceedsReceivedView>();
            if (dto.PortType == 1)
            {
                foreach (var item in groupReceivedList)
                {
                    item._ProceedsReceivedDatas = groupProceedsReceivedList.Where(s => s.FID == item.Id).ToList();
                }

                NotFIDData = groupProceedsReceivedList.Where(s => !groupReceivedList.Any(e => s.FID == e.Id)).ToList();

            }

            result.Code = 0;
            result.Msg = "查询成功!";
            result.Data = new
            {
                GroupInfo = groupInfoData.Data,
                GroupCollectionStatementData = new
                {
                    ReceivedData = groupReceivedList,
                    UnallocatedData = NotFIDData
                }
            };

            return result;

        }

        /// <summary>
        /// 应收款项 删除
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        public async Task<Result> _Del(DelForForeignReceivablesInfoDto dto)
        {
            Result result = new Result() { Code = -1, Msg = "程序错误!" };

            _sqlSugar.BeginTran();
            try
            {
                var res = await _sqlSugar.Updateable<Fin_ForeignReceivables>()
                .Where(it => it.Id == dto.Id)
                .SetColumns(it => new Fin_ForeignReceivables()
                {
                    IsDel = 1,
                    DeleteUserId = dto.UserId,
                    DeleteTime = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")
                }
                ).ExecuteCommandAsync();

                if (res > 0)
                {

                    await _sqlSugar.Updateable<Fin_ProceedsReceived>()
                                      .Where(a => a.FID == dto.Id)
                                      .SetColumns(a => new Fin_ProceedsReceived
                                      {
                                          IsDel = 1,
                                          DeleteUserId = dto.UserId,
                                          DeleteTime = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"),
                                      }).ExecuteCommandAsync();



                    _sqlSugar.CommitTran();
                    result.Msg = "删除成功！";
                    result.Code = 0;
                }
                else
                {
                    _sqlSugar.RollbackTran();
                    result.Msg = "删除失败!";
                    return result;

                }
            }
            catch (Exception ex)
            {
                _sqlSugar.RollbackTran();
                result.Msg = ex.Message;
                return result;
            }



            return result;
        }

        /// <summary>
        /// 财务模块
        /// 收款账单 Add And Update
        /// </summary>
        /// <param name="diid"></param>
        /// <returns></returns>
        public async Task<Result> PostReceivablesOperate(ForeignReceivablesAddAndUpdateDto dto)
        {
            Result result = new() { Code = -2 };

            if (dto.foreignReceivablesInfos.Count <= 0)
            {
                result.Msg = "收款账单没有信息，不能进行，添加或修改操作！！！";
                return result;
            }


            int addCount = 0, updateCount = 0;
            if (dto.PortType == 1)
            {
                List<Fin_ForeignReceivables> _ForeignReceivables = new List<Fin_ForeignReceivables>();
                foreach (var item in dto.foreignReceivablesInfos)
                {
                    _ForeignReceivables.Add(new Fin_ForeignReceivables()
                    {
                        Diid = dto.DiId,
                        Id = item.Id,
                        PriceName = item.PriceName,
                        Price = item.Price,
                        Count = item.Count,
                        Unit = item.Unit,
                        ItemSumPrice = item.ItemSumPrice,
                        Rate = item.Rate,
                        Currency = item.Currency,
                        AddingWay = item.AddingWay,
                        CreateUserId = dto.UserId,
                        CreateTime = DateTime.Now,
                        Remark = item.Remark
                    });
                }
                if (_ForeignReceivables.Count > 0)
                {
                    var x = _sqlSugar.Storageable(_ForeignReceivables).ToStorage();
                    addCount = x.AsInsertable.ExecuteCommand();        //不存在插入
                    updateCount = x.AsUpdateable.ExecuteCommand();    //存在更新
                }
                result.Code = 0;
                result.Msg = string.Format(@"操作成功!添加：{0}条；更新：{1}；", addCount, updateCount);
            }

            return result;
        }

        /// <summary>
        /// 根据diid查询团组应收款项
        /// </summary>
        /// <param name="diid"></param>
        /// <returns></returns>
        public async Task<Result> GetGroupReceivablesByDiid(int diid)
        {
            Result result = new() { Code = -2 };

            string sql = string.Format(@"Select * From Fin_ForeignReceivables Where IsDel=0 And Diid={0}", diid);

            var groupReceivedList = await _sqlSugar.SqlQueryable<Fin_ForeignReceivables>(sql).ToListAsync();

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

            return result;

        }

        /// <summary>
        /// 根据diid 数组 查询团组应收款项
        /// </summary>
        /// <param name="diid"></param>
        /// <returns></returns>
        public async Task<Result> GetGroupReceivablesByDiids(int[] diids)
        {
            Result result = new() { Code = -2 };

            //string sql = string.Format(@"Select * From Fin_ProceedsReceived Where IsDel=0 And Diid={0}", diids);

            var groupReceivedList = await _sqlSugar.Queryable<Fin_ForeignReceivables>()
                .Where(pr => pr.IsDel == 0 && diids.Contains(pr.Diid)).ToListAsync();

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

            return result;

        }

        #endregion

        #region 未关联已收款项

        /// <summary>
        /// 收款账单 数据源
        /// </summary>
        /// <returns></returns>
        public async Task<JsonView> PostDataSource(ForeignReceivablesDataSourcesDto dto)
        {
            JsonView result = new() { Code = StatusCodes.Status204NoContent };

            //已收款项 判断如果是市场部的人员进来的话 只显示自己的 其他的都显示全部的
            var userInfos = await _sqlSugar.Queryable<Sys_Users>()
                                           .InnerJoin<Sys_Department>((u, d) => u.DepId == d.Id)
                                           .Where((u, d) => u.IsDel == 0 && d.DepName.Contains("市场部") && u.Id == dto.CurrUserId)
                                           .ToListAsync();

            string sqlWhere = "";
            if (userInfos.Count > 0) sqlWhere = string.Format(@$" And JietuanOperator={dto.CurrUserId} ");

            string sql = string.Format(@$"Select Id,TeamName GroupName From  Grp_DelegationInfo 
                                             Where TeamName != '' And IsDel = 0 {sqlWhere} 
                                             Order By Id Desc");

            var _groupNameList = await _sqlSugar.SqlQueryable<GroupNameView>(sql).ToListAsync();

            //var groupNameData = await _delegationRep.GetGroupNameList(new GroupNameDto());
            var currencyData = await _setDataRep.GetSetDataBySTId(_setDataRep, 66); //币种
            var remittanceMethodData = await _setDataRep.GetSetDataBySTId(_setDataRep, 14); //汇款方式

            result.Code = StatusCodes.Status200OK;
            result.Msg = "成功！";
            result.Data = new
            {
                GroupNameData = _groupNameList,
                CurrencyData = currencyData.Data,
                RemittanceMethodData = remittanceMethodData.Data
            };

            return result;
        }

        /// <summary>
        /// 根据diid查询团组应收款项
        /// </summary>
        /// <param name="diid"></param>
        /// <returns></returns>
        public async Task<JsonView> PostGroupReceivablesInfoByDiId(ForForeignReceivablesNewDto dto)
        {
            JsonView result = new() { Code = 400, Msg = "" };

            //var groupInfoData = await _delegationRep.GetGroupInfo(new GroupInfoDto() { Id = dto.DiId });
            var groupInfoData = await _delegationRep.Query(x => x.Id == dto.DiId)
                .Select(x => new
                {
                    x.Id,
                    x.TeamName,
                    x.TourCode,
                    x.ClientName,
                    VisitCountry = x.VisitCountry.Replace("|","、"),
                    x.VisitDays,
                    x.VisitPNumber,
                    x.VisitStartDate,
                    x.VisitEndDate
                })
                .FirstAsync();


            //应收款项
            string groupReceivedSql = string.Format(@"Select *,su.CnName As AuditorName From Fin_ForeignReceivables  ffr
Left Join Sys_Users su On  ffr.Auditor = su.Id
Where ffr.IsDel=0 And ffr.Diid={0}", dto.DiId);
            var groupReceivedList = await _sqlSugar.SqlQueryable<ProceedsReceivedNewView>(groupReceivedSql).ToListAsync();

            result.Code = 200;
            result.Msg = "查询成功!";
            result.Data = new
            {
                GroupInfo = groupInfoData,
                GroupCollectionStatementData = groupReceivedList
            };

            return result;

        }

        /// <summary>
        /// 财务模块
        /// 收款账单 Add And Update
        /// </summary>
        /// <param name="diid"></param>
        /// <returns></returns>
        public async Task<JsonView> PostReceivablesSave(ForeignReceivablesSaveDto dto)
        {
            JsonView result = new() { Code = 4002 };

            if (dto.foreignReceivablesInfos.Count <= 0)
            {
                result.Msg = "收款账单没有信息，不能进行，添加或修改操作！！！";
                return result;
            }

            int addCount = 0, updateCount = 0;
             
            //if (dto.PortType == 1)
            //{
                //查询值是否更改
                var selectInfos = await _sqlSugar.Queryable<Fin_ForeignReceivables>().Where(it => it.Diid == dto.DiId && it.Status == 1).ToListAsync();

                List <Fin_ForeignReceivables> _ForeignReceivables = new List<Fin_ForeignReceivables>();
                foreach (var item in dto.foreignReceivablesInfos)
                {
                    int status = 0, auditor = 0;
                    string auditTime = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
                    var info = selectInfos.Find(x => x.Id == item.Id);
                    if (info != null)
                    {
                        if (info.ItemSumPrice == item.ItemSumPrice)
                        {
                            status = info.Status;
                            auditor = info.Auditor;
                            auditTime = info.AuditTime;
                        }
                    }

                    _ForeignReceivables.Add(new Fin_ForeignReceivables()
                    {
                        Diid = dto.DiId,
                        Id = item.Id,
                        PriceName = item.PriceName,
                        Price = item.Price,
                        Count = item.Count,
                        Unit = item.Unit,
                        ItemSumPrice = item.ItemSumPrice,
                        Rate = item.Rate,
                        Currency = item.Currency,
                        AddingWay = item.AddingWay,
                        CreateUserId = dto.UserId,
                        CreateTime = DateTime.Now,
                        Remark = item.Remark,
                        Status = status,
                        Auditor = auditor,
                        AuditTime = auditTime
                    });
                }
                if (_ForeignReceivables.Count > 0)
                {
                    var x = _sqlSugar.Storageable(_ForeignReceivables).ToStorage();
                    addCount = x.AsInsertable.ExecuteCommand();       //不存在插入
                    updateCount = x.AsUpdateable.IgnoreColumns(it => new 
                                    {  
                                        //it.Status, 
                                        //it.Auditor, 
                                        //it.AuditTime, 
                                        it.CreateUserId, 
                                        it.CreateTime 
                                    }).ExecuteCommand();    //存在更新
                }
                result.Code = 200;
                result.Msg = string.Format(@"操作成功!添加：{0}条；更新：{1}；", addCount, updateCount);
            //}

            return result;
        }

        /// <summary>
        /// 财务模块
        /// 收款账单 
        /// Audit
        /// </summary>
        /// <param name="diid"></param>
        /// <returns></returns>
        public async Task<JsonView> FeeAudit(FeeAuditDto dto)
        {
            JsonView result = new() { Code = 400 };
            //验证

            var info = await _sqlSugar.Queryable<Fin_ForeignReceivables>().FirstAsync(x => x.Id == dto.Id);
            if (info == null)
            {
                result.Msg = "数据不存在";
                return result;
            }

            if (info.AddingWay != 2)
            {
                result.Msg = "该条数据类型不是“实际报价”类型不可审核!";
                return result;
            }
           
            var AuditStatus = await _sqlSugar.Updateable<Fin_ForeignReceivables>()
                                      .SetColumns(a => new Fin_ForeignReceivables
                                      {
                                          Status = dto.Status,
                                          Auditor = dto.UserId,
                                          AuditTime = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"),
                                      })
                                      .Where(a => a.Id == dto.Id)
                                      .ExecuteCommandAsync();

            if (AuditStatus > 0)
            {
                result.Msg = "操作成功！";
                result.Code = 200;
            }
            else result.Msg = "操作失败！";
            return result;
        }


        /// <summary>
        /// 财务模块
        /// 收款账单 
        /// Del
        /// </summary>
        /// <param name="diid"></param>
        /// <returns></returns>
        public async Task<Result> PostReceivablesDel(ForeignReceivablesDelDto dto)
        {
            Result result = new() { Code = -2 };
           var delStatus = await _sqlSugar.Updateable<Fin_ForeignReceivables>()
                                     .SetColumns(a => new Fin_ForeignReceivables
                                     {
                                         IsDel = 1,
                                         DeleteUserId = dto.UserId,
                                         DeleteTime = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"),
                                     })
                                     .Where(a => a.Id == dto.Id)
                                     .ExecuteCommandAsync();

            if (delStatus > 0)
            {
                result.Msg = "操作成功！";
                result.Code = 0;
            }
            else
            {
                result.Msg = "操作成功！";
            }
            return result;
        }

        public void OverSpSeteceivables(OverSpSeteceivablesDto dto)
        {
            var querySaveReceivables = _sqlSugar.Queryable<Fin_ForeignReceivables>()
                .First(x => x.Diid == dto.DiId && x.AddingWay == 3 && x.IsDel == 0);

            //获取所有超支数据
            //整理超支数据
            //币种不同则计算rmb值
            var overspList = _sqlSugar.Queryable<Fin_GroupExtraCost, Grp_CreditCardPayment>
                ((e, c) => c.CTable == 1015 && c.CId == e.Id && c.IsDel == 0).
                Where((e, c) => e.IsDel == 0 && e.DiId == dto.DiId).
                Select((e, c) => new
                {
                    e.Price,
                    e.PriceCount,
                    e.Coefficient,
                    e.PriceSum,
                    c.RMBPrice,
                    c.DayRate,
                    e.PriceCurrency,
                    e.Remark,
                })
                .ToList().
                Where(x =>
                {
                    var count = 0;
                    var stringArr = new string[] { "SYsupervisorConfirm" , "SupervisorConfirm" , "ManagerConfirm" };
                    foreach (var item in stringArr)
                    {
                        var number = x.GetType()?.GetProperty(item)?.GetValue(x).ObjToInt();
                        if (number > 0)
                        {
                            count++;
                        }
                    }
                    return count > 1;

                }).ToList();
            var overspListGroup = overspList.GroupBy(x => x.PriceCurrency).ToList();
            string foreignReceivablesRemake = string.Empty;
            int count = 1;
            overspList.ForEach(x =>
            {
                foreignReceivablesRemake += $"{count}.{x.Remark}";
                count++;
            });

            if (querySaveReceivables != null)
            {
                if (overspList.Count() > 0)
                {
                    if (overspListGroup.Count() > 1)
                    {

                        querySaveReceivables.Currency = 836; //人民币
                        querySaveReceivables.Rate = 1;
                        querySaveReceivables.Price = overspListGroup.Sum(x => x.Sum(y => y.PriceSum * y.Coefficient * y.DayRate));
                        querySaveReceivables.ItemSumPrice = overspListGroup.Sum(x => x.Sum(y => y.PriceSum * y.Coefficient * y.DayRate));
                        querySaveReceivables.Remark = foreignReceivablesRemake;
                        querySaveReceivables.CreateTime = DateTime.Now;
                    }
                    else
                    {
                        querySaveReceivables.Currency = overspList[0].PriceCurrency;
                        querySaveReceivables.Rate = overspList[0].DayRate;
                        querySaveReceivables.Price = overspListGroup[0].Sum(x => x.PriceSum * x.Coefficient);
                        querySaveReceivables.ItemSumPrice = overspListGroup[0].Sum(x => x.PriceSum * x.Coefficient);
                        querySaveReceivables.Remark = foreignReceivablesRemake;
                        querySaveReceivables.AddingWay = 3;
                        querySaveReceivables.CreateTime = DateTime.Now;
                    }
                }
                else
                {
                    querySaveReceivables.Currency = 836; //人民币
                    querySaveReceivables.Rate = 1;
                    querySaveReceivables.Price = 0;
                    querySaveReceivables.ItemSumPrice = 0;
                    querySaveReceivables.Remark = "";
                    querySaveReceivables.CreateTime = DateTime.Now;
                    querySaveReceivables.Count = 1;
                }
                _sqlSugar.Updateable(querySaveReceivables).ExecuteCommand();
            }
            else
            {
                if (overspList.Count() > 0)
                {
                    if (overspListGroup.Count() > 1)
                    {
                        querySaveReceivables = new Fin_ForeignReceivables
                        {
                            CreateTime = DateTime.Now,
                            CreateUserId = dto.CreateUserId,
                            Diid = dto.DiId,
                            PriceName = "超支费用",
                            AddingWay = 3,
                            Count = 1,
                            Currency = 836,
                            ItemSumPrice = overspList.Sum(x => x.PriceSum * x.Coefficient * x.DayRate),
                            Price = overspList.Sum(x => x.PriceSum * x.Coefficient * x.DayRate),
                            Rate = 1,
                            IsDel = 0,
                            Remark = foreignReceivablesRemake,
                            Unit = "团",
                        };
                    }
                    else
                    {
                        querySaveReceivables = new Fin_ForeignReceivables
                        {
                            CreateTime = DateTime.Now,
                            CreateUserId = dto.CreateUserId,
                            Diid = dto.DiId,
                            PriceName = "超支费用",
                            AddingWay = 3,
                            Count = 1,
                            Currency = overspList[0].PriceCurrency,
                            ItemSumPrice = overspList.Sum(x => x.PriceSum * x.Coefficient),
                            Price = overspList.Sum(x => x.PriceSum * x.Coefficient),
                            Rate = overspList[0].DayRate,
                            IsDel = 0,
                            Remark = foreignReceivablesRemake,
                            Unit = "团",
                        };
                    }
                }
                _sqlSugar.Insertable(querySaveReceivables).ExecuteCommand();
            }
        }

        #endregion

    }
}