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

using NPOI.SS.Formula.Functions;
using OASystem.Domain;
using OASystem.Domain.Entities.Groups;
using OASystem.Domain.ViewModels.Groups;
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 FeeAuditRepository:BaseRepository<EntityBase,ViewBase>
    {
        public FeeAuditRepository(SqlSugarClient sqlSugar) : base(sqlSugar)
        { 
            
        }

        /// <summary>
        /// 费用自动审核
        /// </summary>
        /// <param name="feeType">
        /// 1.酒店 76 
        /// 2.op 79
        /// </param>
        /// <param name="diId">团组Id</param>
        /// <param name="dataId">数据Id(模块类型主表Id)</param>
        /// <returns></returns>
        public async Task<JsonView> FeeAutomaticAudit(int feeType, int diId, int dataId)
        {
            var _view = new JsonView() { Code = 201, Msg = "自动审核操作失败" };
            if (diId < 1) { _view.Msg = MsgTips.DiId; return _view; }
            if (dataId < 1) { _view.Msg = MsgTips.Id; return _view; }

            List<int> stids = new List<int>() { 17, 66 };
            var setData = _sqlSugar.Queryable<Sys_SetData>().Where(x => x.IsDel == 0 && stids.Contains(x.STid)).ToList();
            string _teamCurrency = string.Empty;
            decimal _rate = 0.00M;
            var groupInfo = _sqlSugar.Queryable< Grp_GroupCostParameter >().Where(x => x.IsDel ==0 && x.DiId == diId).First();
            if (groupInfo == null) { _view.Msg = $"团组成本信息未填写!"; return _view; }
            _teamCurrency = groupInfo.Currency;
            //币种验证  统一为currencycode三字码
            if (int.TryParse(_teamCurrency,out int currency))
            {
                _teamCurrency = setData.Find(x => x.Id == currency)?.Name ?? "";
            }
            _rate = groupInfo.Rate;

            string costContentSql = $"Select * From Grp_GroupCost";
            var costContents =  _sqlSugar.SqlQueryable<GroupCostAuditView>(costContentSql).Where(x => x.IsDel == 0 && x.Diid == diId).ToList();
            if (costContents.Count < 1) { _view.Msg = $"团组成本信息未填写!"; return _view; }

            //处理 成本详细信息 日期为空
            for (int i = 0; i < costContents.Count; i++)
            {
                if (string.IsNullOrEmpty( costContents[i].Date))
                {
                    int index = i - 1;
                    if (index >= 0)
                    {
                        costContents[i].Date = costContents[index].Date;
                    }
                }
            }

            if (feeType == 1)
            {
                var hotelCostInfo = _sqlSugar.Queryable<Grp_HotelReservations>().Where(x => x.IsDel == 0 && x.DiId == diId && x.Id == dataId).First();
                var hotelCostDetails = _sqlSugar.Queryable<Grp_HotelReservationsContent>().Where(x => x.IsDel == 0 && x.DiId == diId && x.HrId == dataId).ToList();
                if (hotelCostInfo == null)
                {
                    _view.Msg = $"酒店费用数据未填写";
                    return _view;
                }

                //验证币种是否相等
                string hotelCurrency = setData.Find(x => x.Id == hotelCostInfo.CardPriceCurrency)?.Name ?? "";
                if (hotelCurrency != _teamCurrency)
                {
                    if (!hotelCurrency.Equals("CNY"))
                    {
                        _view.Msg = $"酒店预订费用币种与团组成本币种不一致,请手动审核";
                        return _view;
                    }
                }
                else _rate = 1.0000M;

                bool isAutoAudit = true; //是否自动审核
                DateTime checkIn = Convert.ToDateTime(hotelCostInfo.CheckInDate),
                         checkOut = Convert.ToDateTime(hotelCostInfo.CheckOutDate);
                if (checkOut > checkIn) checkOut = checkOut.AddDays(-1); //房费计算，结束日期为前一天
                var hotelCostInfos = costContents.Where(x => Convert.ToDateTime(x.Date) >= checkIn && Convert.ToDateTime(x.Date) <= checkOut).ToList();
                if (hotelCostInfos.Count < 1) isAutoAudit = false;

                decimal otherFee = hotelCostDetails.Where(x => x.PriceType != 1).Sum(x => x.Price);
                if (otherFee > 0) { otherFee /= 3; }
                var hotelCostInfosGroup = hotelCostInfos.GroupBy(x => x.Date);
                foreach (var item in hotelCostInfosGroup)
                {
                    decimal hotelSingleRoomFee = item.Sum(x => x.HotelSingleRoomFee);
                    decimal hotelDoubleRoomFee = item.Sum(x => x.HotelDoubleRoomFee);
                    decimal hotelSuiteFee = item.Sum(x => x.HotelSuiteFee);
                    decimal hotelSuiteRoomFee = item.Sum(x => x.HotelSuiteRoomFee);
                    //1.判断费用是否 <= 成本费用
                    //1.1 判断单间费用
                    decimal singleRoomPrice = hotelCostInfo.SingleRoomPrice + otherFee;
                    if (singleRoomPrice > 0) if (singleRoomPrice > hotelSingleRoomFee * _rate) isAutoAudit = false;

                    //1.2 判断双人间费用
                    decimal doubleRoomPrice = hotelCostInfo.DoubleRoomPrice + otherFee;
                    if (doubleRoomPrice > 0) if (doubleRoomPrice > hotelDoubleRoomFee * _rate) isAutoAudit = false;

                    //1.3 判断套房费用
                    decimal suiteRoomPrice = hotelCostInfo.SuiteRoomPrice + otherFee;
                    if (suiteRoomPrice > 0) if (suiteRoomPrice > hotelSuiteFee * _rate) isAutoAudit = false;

                    //1.4 判断其他房型费用
                    decimal otherRoomPrice = hotelCostInfo.OtherRoomPrice + otherFee;
                    if (otherRoomPrice > 0) if (otherRoomPrice > hotelSuiteRoomFee * _rate) isAutoAudit = false;
                }

                //2.判断是否自动审核
                if (isAutoAudit)
                {
                    var ccpUpdate = _sqlSugar.Updateable<Grp_CreditCardPayment>()
                                       .SetColumns(it => it.IsAuditGM == 3)
                                       .SetColumns(it => it.AuditGMOperate == 4)
                                       .SetColumns(it => it.AuditGMDate == DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"))
                                       .Where(s => s.DIId == diId && s.CTable == 76 && s.CId == dataId)
                                       .ExecuteCommand();
                    if (ccpUpdate > 0)
                    {
                        _view.Code = 200;
                        _view.Msg = "自动审核执行成功";
                        return _view;
                    }
                }
                else {
                    //撤销该条数据的自动审核 --> 该条数据的审核状态是自动审核  3 --> 0
                    var ccpInfo = _sqlSugar.Queryable<Grp_CreditCardPayment>()
                                           .Where(s => s.DIId == diId && s.CTable == 76 && s.CId == dataId && s.IsAuditGM == 3)
                                           .First();
                    if (ccpInfo != null)
                    {
                        var ccpUpdate = _sqlSugar.Updateable<Grp_CreditCardPayment>()
                                       .SetColumns(it => it.IsAuditGM == 0)
                                       .SetColumns(it => it.AuditGMOperate == 0)
                                       .SetColumns(it => it.AuditGMDate == string.Empty)
                                       .Where(s => s.Id == ccpInfo.Id)
                                       .ExecuteCommand();
                        if (ccpUpdate > 0)
                        {
                            _view.Code = 200;
                            _view.Msg = "费用超团组成本,自动审核撤销成功!";
                            return _view;
                        }
                    }
                }
            }
            else if (feeType == 2)
            {
                //1.基础数据参数验证
                var priceType = setData.Where(x => x.Name.Contains("尾款")).Select(x =>x.Id).ToList();
                var opinfos = _sqlSugar.Queryable<Grp_CarTouristGuideGroundReservations>()
                                       .Where(x => x.IsDel == 0 && x.DiId == diId && x.Id == dataId && !priceType.Contains(x.PriceType))
                                       .First();
                if (opinfos == null)
                {
                    _view.Msg = $"OP费用数据未填写";
                    return _view;
                }
                //1.含超时费用/超支费用 手动审核
                if (opinfos.SelectCheck.Contains("超时") || opinfos.SelectCheck.Contains("超支"))
                {
                    _view.Msg = $"OP费用含超时费用,请手动审核";
                    return _view;
                }
                //1.参数验证
                var opCheckPriceTyeps = opinfos.SelectCheck.Split(',');
                var opCheckPriceTyepIds = setData.Where(x => opinfos.SelectCheck.Split(',').Contains(x.Name)).Select(x=>x.Id).ToList();
                var opContents = _sqlSugar.Queryable<Grp_CarTouristGuideGroundReservationsContent>()
                    .Where(x => x.IsDel == 0 && x.DiId == diId && x.CTGGRId == dataId && opCheckPriceTyepIds.Contains(x.SId))
                    .OrderBy(x => x.DatePrice, OrderByType.Asc)
                    .ToList();
                if (opContents.Count < 1)
                {
                    _view.Msg = $"OP费用费用内容未填写";
                    return _view;
                }
                string opCurrencyName = setData.Find(x => x.Id == opContents[0].Currency)?.Name ?? "";
                //1.1币种验证
                if (!opCurrencyName.Equals(_teamCurrency))
                {
                    if (!opCurrencyName.Equals("CNY"))
                    {
                        _view.Msg = $"OP费用币种与团组成本币种不一致,请手动审核";
                        return _view;
                    }
                }
                else _rate = 1.0000M;

                var opBasicDatas = setData.Where(x => x.STid == 17).ToList(); //费用类型基础数据
                bool isAutoAudit = true;
                if (!DateTime.TryParse(opinfos.ServiceStartTime, out DateTime startDt1) || !DateTime.TryParse(opinfos.ServiceEndTime, out DateTime endDt1))
                {
                    _view.Msg = $"OP费用服务起止日期格式不正确!";
                    return _view;
                }

                DateTime startDt = startDt1;
                DateTime endDt = endDt1;
                var opCostDatas = costContents.Where(it => Convert.ToDateTime(it.Date) >= startDt && Convert.ToDateTime(it.Date) <= endDt).ToList();
                if (opCostDatas.Count < 1)
                {
                    _view.Msg = $"该时间段内团组成本未填写!";
                    return _view;
                }

                //2.按天按项 检查费用是否超过预算 
                var opDayContent = opContents.GroupBy(x => x.DatePrice);

                foreach (var item in opDayContent)
                {
                    var opCostInfo = opCostDatas.Where(x => Convert.ToDateTime(x.Date) == item.Key).ToList();
                    if (opCostInfo.Count < 1) continue;

                    //车费 91
                    var opCarCost = item.FirstOrDefault(x => x.SId == 91);
                    if (opCarCost != null) if (opCarCost.Price > _rate * opCostInfo.Sum(x => x.CarFee)) isAutoAudit = false;

                    //982 车超时费 -- 暂无

                    //92  导游费
                    var opGuideCost = item.FirstOrDefault(x => x.SId == 92);
                    if (opGuideCost != null) if (opGuideCost.Price > _rate * opCostInfo.Sum(x => x.GuideFee)) isAutoAudit = false;
                    //94  导游景点费
                    var opGuideScenicCost = item.FirstOrDefault(x => x.SId == 94);
                    if (opGuideScenicCost != null) if (opGuideScenicCost.Price > _rate * opCostInfo.Sum(x => x.GuideScenicFee)) isAutoAudit = false;
                    //95  导游小费
                    var opGuideTipCost = item.FirstOrDefault(x => x.SId == 95);
                    if (opGuideTipCost != null) if (opGuideTipCost.Price > _rate * opCostInfo.Sum(x => x.GuideTipFee)) isAutoAudit = false;
                    //983 导游餐补
                    var opGuideMealCost = item.FirstOrDefault(x => x.SId == 983);
                    if (opGuideMealCost != null) if (opGuideMealCost.Price > _rate * opCostInfo.Sum(x => x.GuideMealFee)) isAutoAudit = false;
                    //984 导游房补
                    var opGuideRoomCost = item.FirstOrDefault(x => x.SId == 984);
                    if (opGuideRoomCost != null) if (opGuideRoomCost.Price > _rate * opCostInfo.Sum(x => x.GuideRoomFee)) isAutoAudit = false;
                    //985 导游交通
                    var opGuideTrafficCost = item.FirstOrDefault(x => x.SId == 985);
                    if (opGuideTrafficCost != null) if (opGuideTrafficCost.Price > _rate * opCostInfo.Sum(x => x.GuideTrafficFee)) isAutoAudit = false;
                    //96  接送机费 -- 暂无
                    //97  其他费用 -- 暂无
                    //979 司机工资
                    var opDriverCost = item.FirstOrDefault(x => x.SId == 979);
                    if (opDriverCost != null) if (opDriverCost.Price > _rate * opCostInfo.Sum(x => x.DriverFee)) isAutoAudit = false;
                    //980 司机小费
                    var opDriverTipCost = item.FirstOrDefault(x => x.SId == 980);
                    if (opDriverTipCost != null) if (opDriverTipCost.Price > _rate * opCostInfo.Sum(x => x.DriverTipFee)) isAutoAudit = false;
                    //981 司机餐补
                    var opDriverMealCost = item.FirstOrDefault(x => x.SId == 981);
                    if (opDriverMealCost != null) if (opDriverMealCost.Price > _rate * opCostInfo.Sum(x => x.DriverMealFee)) isAutoAudit = false;
                    //988 客户早餐费用
                    var opClientBreakfastCost = item.FirstOrDefault(x => x.SId == 988);
                    if (opClientBreakfastCost != null) if (opClientBreakfastCost.Price > _rate * opCostInfo.Sum(x => x.ClientBreakfastFee)) isAutoAudit = false;
                    //93  客户午餐费用
                    var opClientLunchCost = item.FirstOrDefault(x => x.SId == 93);
                    if (opClientLunchCost != null) if (opClientLunchCost.Price > _rate * opCostInfo.Sum(x => x.ClientLunchFee)) isAutoAudit = false;
                    //989 客户晚餐费用
                    var opClientDinnerCost = item.FirstOrDefault(x => x.SId == 989);
                    if (opClientDinnerCost != null) if (opClientDinnerCost.Price > _rate * opCostInfo.Sum(x => x.ClientDinnerFee)) isAutoAudit = false;
                    //990 景点门票费
                    var opScenicTicketCost = item.FirstOrDefault(x => x.SId == 990);
                    if (opScenicTicketCost != null) if (opScenicTicketCost.Price > _rate * opCostInfo.Sum(x => x.ScenicTicketFee)) isAutoAudit = false;
                    //991 饮料/零食/水果
                    var opDrinkSnackFruitCost = item.FirstOrDefault(x => x.SId == 991);
                    if (opDrinkSnackFruitCost != null) if (opDrinkSnackFruitCost.Price > _rate * opCostInfo.Sum(x => x.DrinkSnackFruitFee)) isAutoAudit = false;
                    //992 住补费用 -- 暂无
                    //994 翻译费
                    var opTranslatorCost = item.FirstOrDefault(x => x.SId == 994);
                    if (opTranslatorCost != null) if (opTranslatorCost.Price > _rate * opCostInfo.Sum(x => x.TranslatorFee)) isAutoAudit = false;
                    //1059    导游超时费用
                    //1070    尾款金额
                    //1071    其他额外费用
                    //1073    翻译超时费
                    //1074    早餐超支费用
                    //1075    午餐超支费用
                    //1076    晚餐超支费用
                }

                //更改审核状态
                if (isAutoAudit)
                {
                    var ccpUpdate = _sqlSugar.Updateable<Grp_CreditCardPayment>()
                                       .SetColumns(it => it.IsAuditGM == 3)
                                       .SetColumns(it => it.AuditGMOperate == 4)
                                       .SetColumns(it => it.AuditGMDate == DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"))
                                       .Where(s => s.DIId == diId && s.CTable == 79 && s.CId == dataId)
                                       .ExecuteCommand();
                    if (ccpUpdate > 0)
                    {
                        _view.Code = 200;
                        _view.Msg = "自动审核执行成功!";
                        return _view;
                    }
                }
                else
                {
                    //撤销该条数据的自动审核 --> 该条数据的审核状态是自动审核  3 --> 0
                    var ccpInfo = _sqlSugar.Queryable<Grp_CreditCardPayment>()
                                           .Where(s => s.DIId == diId && s.CTable == 79 && s.CId == dataId && s.IsAuditGM == 3)
                                           .First();
                    if (ccpInfo != null)
                    {
                        var ccpUpdate = _sqlSugar.Updateable<Grp_CreditCardPayment>()
                                       .SetColumns(it => it.IsAuditGM == 0)
                                       .SetColumns(it => it.AuditGMOperate == 0)
                                       .SetColumns(it => it.AuditGMDate == string.Empty)
                                       .Where(s => s.Id == ccpInfo.Id)
                                       .ExecuteCommand();
                        if (ccpUpdate > 0)
                        {
                            _view.Code = 200;
                            _view.Msg = "费用超团组成本,自动审核撤销成功!";
                            return _view;
                        }
                    }
                }



            }
            else _view.Msg = $"请传入有效的feeType参数";

            return _view;
        }

    }
}