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

using AutoMapper;
using OASystem.Domain;
using OASystem.Domain.Dtos.Financial;
using OASystem.Domain.Entities.Financial;
using OASystem.Domain.Entities.Groups;
using OASystem.Domain.ViewModels.Financial;
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:24
    /// </summary>
    public class ProceedsReceivedRepository : BaseRepository<Fin_ProceedsReceived, Fin_ProceedsReceivedView>
    {
        private readonly IMapper _mapper;

        /// <summary>
        /// 
        /// </summary>
        /// <param name="sqlSugar"></param>
        /// <param name="mapper"></param>
        public ProceedsReceivedRepository(SqlSugarClient sqlSugar, IMapper mapper)
            : base(sqlSugar)
        {
            _mapper = mapper;
        }

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

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

            var groupReceivablesList = await _sqlSugar.SqlQueryable<Fin_ProceedsReceivedView>(sql).ToListAsync();

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

            return result;

        }

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


            var groupReceivablesList = await _sqlSugar.Queryable<Fin_ProceedsReceived>()
                .Where(fr => fr.IsDel == 0 && diids.Contains(fr.Diid)).ToListAsync();

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

            return result;

        }

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


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

            if (res > 0)
            {
                result.Msg = "删除成功！";
                result.Code = 0;
            }
            else
            {
                result.Msg = "删除失败!";
            }

            return result;
        }

        /// <summary>
        /// 已收款项 已关联应收款项
        /// Add And Update
        /// </summary>
        /// <param name="diid"></param>
        /// <returns></returns>
        public async Task<Result> PostAmountReceivedOperate(ProceedsReceivedDto dto)
        {
            Result result = new() { Code = -2 };

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


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

            return result;
        }


        /// <summary>
        /// 已收款项 已关联应收款项
        /// 分配已收款项至 应收项下
        /// </summary>
        /// <param name="diid"></param>
        /// <returns></returns>
        public async Task<Result> PostAllocateAmountReceived(AllocateAmountReceivedDto dto)
        {
            Result result = new() { Code = -2 };

            if (dto.SubIds.Count <= 0)
            {
                result.Msg = "请选择要添加的已收款项!";
                return result;
            }

            if (dto.PortType == 1)
            {

                List<Fin_ProceedsReceived> _proceedsReceived = new List<Fin_ProceedsReceived>();

                foreach (var id in dto.SubIds)
                {
                    _proceedsReceived.Add(new Fin_ProceedsReceived() { Id = id, FID = dto.ParentId });
                }

                var res = await _sqlSugar.Updateable(_proceedsReceived).
                    WhereColumns(it => new { it.Id })
                .UpdateColumns(it => new { it.FID })
                .ExecuteCommandAsync();

                result.Code = 0;

            }

            return result;
        }


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

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

            var groupReceivablesList = await _sqlSugar.SqlQueryable<Fin_ProceedsReceivedView>(sql).ToListAsync();

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

            return result;
        }

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

            string sql = string.Format(@"Select Id,Diid,SectionTime,Price,Currency,ReceivablesType,Client,CustomerName,CustomerTel, Remark From Fin_ProceedsReceived 
                                         Where IsDel=0 And Diid={0}", diid);

            var groupReceivablesList = await _sqlSugar.SqlQueryable<ProceedsReceived1View>(sql).ToListAsync();
            var groupInfo = await _sqlSugar.Queryable<Grp_DelegationInfo>().Where(x => x.IsDel == 0 && x.Id == diid).FirstAsync();
            result.Code = 0;
            result.Msg = "查询成功!";
            result.Data = new
            {
                UnSettleReason = groupInfo.PrUnSettleReason,
                groupReceivablesList
            };

            return result;
        }

        /// <summary>
        /// 已收款项
        /// Add And Update
        /// </summary>
        /// <param name="diid"></param>
        /// <returns></returns>
        public async Task<Result> PostAmountReceivedAddOrEdit(AmountReceivedAddOrEditDto dto)
        {
            Result result = new() { Code = -2 };

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

            int addCount = 0, updateCount = 0;
            if (dto.PortType == 1)
            {
                List<Fin_ProceedsReceived> _ProceedsReceived = new List<Fin_ProceedsReceived>();
                foreach (var item in dto._ProceedsReceivedInfos)
                {
                    _ProceedsReceived.Add(new Fin_ProceedsReceived()
                    {
                        Diid = dto.DiId,
                        Id = item.Id,
                        SectionTime = item.SectionTime,
                        Price = item.Price,
                        Currency = item.Currency,
                        ReceivablesType = item.ReceivablesType,
                        Client = item.Client,
                        CustomerName = item.CustomerName,
                        CustomerTel = item.CustomerTel,
                        FID = 0,
                        CreateUserId = dto.UserId,
                        CreateTime = DateTime.Now,
                        Remark = item.Remark
                    });
                }
                if (_ProceedsReceived.Count > 0)
                {
                    var x = _sqlSugar.Storageable(_ProceedsReceived).ToStorage();
                    addCount = x.AsInsertable.ExecuteCommand();        //不存在插入

                    _sqlSugar.BeginTran();
                    foreach (var item in _ProceedsReceived)
                    {
                        bool res = await UpdateAsync<Fin_ProceedsReceived>(s => s.Id == item.Id, s => new Fin_ProceedsReceived
                        {
                            SectionTime = item.SectionTime,
                            Price = item.Price,
                            Currency = item.Currency,
                            ReceivablesType = item.ReceivablesType,
                            Client = item.Client,
                            CustomerName = item.CustomerName,
                            CustomerTel = item.CustomerTel,
                            Remark = item.Remark
                        });
                        if (!res)
                        {
                            _sqlSugar.RollbackTran();
                            break;
                        }
                        updateCount++;
                    }
                    _sqlSugar.CommitTran();
                }
                result.Code = 0;
                result.Msg = string.Format(@"操作成功!添加：{0}条；更新：{1}；", addCount, updateCount);

                //修改团组状态
                int diId = dto.DiId;
                decimal sum_fr = 0M;
                decimal sum_pr = 0M;
                decimal sum_refund = 0M; //收款退还
                decimal sum_extra = 0M; //超支费用

                //1.应收
                string sql_fr = string.Format(@" Select * From Fin_ForeignReceivables Where IsDel=0 And Diid={0} ", diId);
                List<Fin_ForeignReceivables> list_fr = _sqlSugar.SqlQueryable<Fin_ForeignReceivables>(sql_fr).ToList();
                sum_fr = list_fr.Sum(s => s.ItemSumPrice);

                //2.已收
                string sql_pr = string.Format(@" Select * From Fin_ProceedsReceived Where IsDel=0 And Diid={0} ", diId);
                List<Fin_ProceedsReceived> list_pr = _sqlSugar.SqlQueryable<Fin_ProceedsReceived>(sql_pr).ToList();
                sum_pr = list_pr.Sum(s => s.Price);

                //3.收款退还
                string sql_other = string.Format(@"Select * From  Fin_PaymentRefundAndOtherMoney prao
					  Inner Join Grp_CreditCardPayment ccp On prao.Id = ccp.CId
					  Where ccp.CTable = 285 And ccp.IsPay = 1 And prao.IsDel = 0 And ccp.IsDel = 0 And prao.DiId = {0}", diId);
                List<Grp_CreditCardPayment> list_other = _sqlSugar.SqlQueryable<Grp_CreditCardPayment>(sql_other).ToList();
                sum_refund = list_other.Sum(s => s.RMBPrice);

                //4.超支
                string sql_extra = string.Format(@" Select c.* From Fin_GroupExtraCost f 
                    Inner join Grp_CreditCardPayment c On f.Id = c.CId 
                    Where c.CTable = 1015 And c.IsPay = 1 And f.IsDel = 0 And c.IsDel = 0 And f.DiId = {0} ", diId);
                List<Grp_CreditCardPayment> list_extra = _sqlSugar.SqlQueryable<Grp_CreditCardPayment>(sql_extra).ToList();
                sum_extra = list_extra.Sum(s => s.RMBPrice);

                decimal balance = ((sum_fr + sum_extra) - (sum_pr - sum_refund));

                var updateGroup = await _sqlSugar.Updateable<Grp_DelegationInfo>()
                    .SetColumnsIF(balance <= 0, x => x.IsSure == 1)
                    .SetColumns( x => x.PrUnSettleReason == dto.UnSettleReason)
                    .Where(it => it.Id == dto.DiId)
                    .ExecuteCommandAsync();
            }

            return result;
        }

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


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

            if (res > 0)
            {
                result.Msg = "删除成功！";
                result.Code = 0;
            }
            else
            {
                result.Msg = "删除失败!";
            }

            return result;
        }
    }
}