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.ViewModels.Financial;
using OASystem.Domain.ViewModels.Groups;
using OASystem.Infrastructure.Repositories.Groups;
using OASystem.Infrastructure.Repositories.System;
using SqlSugar;
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;
        

        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<Result> PostGroupReceivablesInfoByDiId(ForForeignReceivablesNewDto 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<ProceedsReceivedNewView>(groupReceivedSql).ToListAsync();

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

            return result;

        }

        /// <summary>
        /// 财务模块
        /// 收款账单 Add And Update
        /// </summary>
        /// <param name="diid"></param>
        /// <returns></returns>
        public async Task<Result> PostReceivablesSave(ForeignReceivablesSaveDto 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>
        /// 财务模块
        /// 收款账单 
        /// 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;
        }

        #endregion

    }
}