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

using EyeSoft.Extensions;
using EyeSoft.Logging;
using NPOI.SS.Formula.Functions;
using OASystem.Domain;
using OASystem.Domain.Entities.Groups;
using OASystem.Domain.Entities.Resource;
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
        /// 3.其他费用-出行物资 98
        /// 4.保险 82
        /// </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; }

            var stids = new List<int>() { 17, 66, 91 };
            var setData = _sqlSugar.Queryable<Sys_SetData>().Where(x => x.IsDel == 0 && stids.Contains(x.STid)).ToList();
            string _teamCurrency = string.Empty;

            decimal _teamRate = 0.00M;
            var costContents = new List<GroupCostAuditView>();
            if (feeType != 4)
            {
                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;
                _teamRate = groupInfo.Rate;

                //币种验证  统一为currencycode三字码
                if (int.TryParse(_teamCurrency, out int currency)) _teamCurrency = setData.Find(x => x.Id == currency)?.Name ?? "";

                string costContentSql = $"Select * From Grp_GroupCost";
                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;
                            var dtBool = DateTime.TryParse(costContents[i].Date, out DateTime _dateTime);
                            if (dtBool)
                            {
                                costContents[i].CurrTime = _dateTime;
                            }
                        }
                    }
                    else
                    {
                        var dtBool = DateTime.TryParse(costContents[i].Date, out DateTime _dateTime);
                        if (dtBool)
                        {
                            costContents[i].CurrTime = _dateTime;
                        }
                    }
                }
            }
            
            if (feeType == 1)
            {
                //1089	对冲账或其他 不在审核范围
                var hotelCostInfo = _sqlSugar.Queryable<Grp_HotelReservations>().Where(x => x.IsDel == 0 && x.DiId == diId && x.Id == dataId && x.CheckType != 1089).First();
                var hotelCostDetails = _sqlSugar.Queryable<Grp_HotelReservationsContent>().Where(x => x.IsDel == 0 && x.DiId == diId && x.HrId == dataId).ToList();
                if (hotelCostInfo == null)
                {
                    _view.Msg = $"酒店费用数据未填写";
                    QuashAudit(76, diId, dataId);
                    return _view;
                }

                //酒店费用金额 == 0 不自动审核
                if (hotelCostInfo.CardPrice == 0.0000M)
                {
                    _view.Msg = $"酒店费用金额 == 0 不自动审核";
                    QuashAudit(76, diId, dataId);
                    return _view;
                }


                //获取C表汇率
                decimal _rate = 1.0000M;
                var roomFeeInfo = hotelCostDetails.Where(x => x.PriceType == 1).First();
                if (roomFeeInfo == null)
                {
                    _view.Msg = $"酒店房间费用付款数据未填写";
                    return _view;
                }
                _rate = roomFeeInfo.Rate == 0.0000M ? 1.0000M : roomFeeInfo.Rate;

                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 => x.CurrTime >= checkIn && x.CurrTime <= checkOut).ToList();
                if (hotelCostInfos.Count < 1) isAutoAudit = false;

                decimal otherFee = hotelCostDetails.Where(x => x.PriceType != 1).Sum(x => x.Price * (x.Rate == 0.0000M ? 1.0000M : x.Rate));
                if (otherFee > 0) { otherFee /= (checkOut - checkIn).Days; }
                var hotelCostInfosGroup = hotelCostInfos.GroupBy(x => x.Date);
                foreach (var item in hotelCostInfosGroup)
                {
                    var hotelSingleRoomFee = item.Sum(x => x.HotelSingleRoomFee) * _teamRate; //成本单间费用
                    var hotelDoubleRoomFee = item.Sum(x => x.HotelDoubleRoomFee) * _teamRate; //成本双人间费用
                    var hotelSuiteFee = item.Sum(x => x.HotelSuiteFee) * _teamRate;           //成本套房费用
                    var hotelSuiteRoomFee = item.Sum(x => x.HotelSuiteRoomFee) * _teamRate;   //成本其他房型间费用
                    //1.判断费用是否 <= 成本费用
                    //1.1 判断单间费用
                    decimal singleRoomPrice = (hotelCostInfo.SingleRoomPrice + otherFee) * _rate; //酒店录入费用
                    if (singleRoomPrice > 0) if (singleRoomPrice > hotelSingleRoomFee) isAutoAudit = false;

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

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

                    //1.4 判断其他房型费用
                    decimal otherRoomPrice = (hotelCostInfo.OtherRoomPrice + otherFee) * _rate;//酒店录入费用
                    if (otherRoomPrice > 0) if (otherRoomPrice > hotelSuiteRoomFee) 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 quashStatus = QuashAudit(76, diId, dataId);

                    if (quashStatus)
                    {
                        _view.Code = 200;
                        _view.Msg = "费用超团组成本,自动审核撤销成功!";
                        return _view;
                    }
                }
            }
            else if (feeType == 2)
            {
                //1.基础数据参数验证
                var priceType = new List<int>() {
                    1062  //1062	尾款
                };
                var opinfos = _sqlSugar.Queryable<Grp_CarTouristGuideGroundReservations>()
                                       .Where(x => x.IsDel == 0 && x.DiId == diId && x.Id == dataId && !priceType.Contains(x.PriceType))
                                       .First();
                //var opinfos = _sqlSugar.Queryable<Grp_CarTouristGuideGroundReservations>()
                //                       .Where(x => x.IsDel == 0 && x.DiId == diId && x.Id == dataId )
                //                       .First();
                if (opinfos == null)
                {
                    _view.Msg = $"OP费用费用类型属于“尾款”或者 内容未填写";
                    return _view;
                }
                //1.含超时费用/超支费用 手动审核
                if (opinfos.SelectCheck.Contains("超时") || 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;
                }
                //获取C表汇率
                decimal _opRate = 1.0000M;
                decimal _opPayPercentage = 1.0000M;
                var payInfo = _sqlSugar.Queryable<Grp_CreditCardPayment>().Where(x => x.IsDel == 0 && x.DIId == diId && x.CTable == 79 && x.CId == dataId).First();
                if (payInfo == null)
                {
                    _view.Msg = $"OP费用付款数据未填写";
                    return _view;
                }
                _opRate = payInfo.DayRate;
                _opPayPercentage = payInfo.PayPercentage / 100.00M;

                string opCurrencyName = setData.Find(x => x.Id == opContents[0].Currency)?.Name ?? "";

                //团组、OP币种 验证是否一致（一致：只比较金额，不计算汇率（2024-04-18））
                if (opCurrencyName.Equals(_teamCurrency))
                {
                    _opRate = payInfo.DayRate;
                    _teamRate = payInfo.DayRate;
                }


                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;
                }

                var noAuditFeeTypeIds = new List<int> {
                   982 ,//982 车超时费 -- 暂无
                   96  ,//96  接送机费 -- 暂无
                   97  ,//97  其他费用 -- 暂无
                   992 ,//992 住补费用 -- 暂无
                   1059,//1059    导游超时费用 -- 暂无
                   1070,//1070    尾款金额 -- 暂无
                   1071,//1071    其他额外费用 -- 暂无
                   1073,//1073    翻译超时费 -- 暂无
                   1074,//1074    早餐超支费用 -- 暂无
                   1075,//1075    午餐超支费用 -- 暂无
                   1076,//1076    晚餐超支费用 -- 暂无
                };
                //费用类型筛选 包含 feeTypeIds && 包含这些类型费用大于0
                var noAuditFeeContents = opContents.Where(x => x.Price > 0 && noAuditFeeTypeIds.Contains(x.SId)).ToList();
                if (noAuditFeeContents.Count > 0)
                {
                    _view.Msg = @$"OP费用含尾款/超支/超时费用,请手动审核";
                    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 * _opRate * _opPayPercentage > _teamRate * opCostInfo.Sum(x => x.CarFee) * _opPayPercentage) isAutoAudit = false;
                    //982 车超时费 -- 暂无
                    //92  导游费
                    var opGuideCost = item.FirstOrDefault(x => x.SId == 92);
                    if (opGuideCost != null) 
                        if (opGuideCost.Price * _opRate * _opPayPercentage > _teamRate * opCostInfo.Sum(x => x.GuideFee) * _opPayPercentage) isAutoAudit = false;
                    //94  导游景点费
                    var opGuideScenicCost = item.FirstOrDefault(x => x.SId == 94);
                    if (opGuideScenicCost != null) 
                        if (opGuideScenicCost.Price * _opRate * _opPayPercentage > _teamRate * opCostInfo.Sum(x => x.GuideScenicFee) * _opPayPercentage) isAutoAudit = false;
                    //95  导游小费
                    var opGuideTipCost = item.FirstOrDefault(x => x.SId == 95);
                    if (opGuideTipCost != null) 
                        if (opGuideTipCost.Price * _opRate * _opPayPercentage > _teamRate * opCostInfo.Sum(x => x.GuideTipFee) * _opPayPercentage) isAutoAudit = false;
                    //983 导游餐补
                    var opGuideMealCost = item.FirstOrDefault(x => x.SId == 983);
                    if (opGuideMealCost != null) 
                        if (opGuideMealCost.Price * _opRate * _opPayPercentage > _teamRate * opCostInfo.Sum(x => x.GuideMealFee) * _opPayPercentage) isAutoAudit = false;
                    //984 导游房补
                    var opGuideRoomCost = item.FirstOrDefault(x => x.SId == 984);
                    if (opGuideRoomCost != null) 
                        if (opGuideRoomCost.Price * _opRate * _opPayPercentage > _teamRate * opCostInfo.Sum(x => x.GuideRoomFee) * _opPayPercentage) isAutoAudit = false;
                    //985 导游交通
                    var opGuideTrafficCost = item.FirstOrDefault(x => x.SId == 985);
                    if (opGuideTrafficCost != null) 
                        if (opGuideTrafficCost.Price * _opRate * _opPayPercentage > _teamRate * opCostInfo.Sum(x => x.GuideTrafficFee) * _opPayPercentage) isAutoAudit = false;
                    //96  接送机费 -- 暂无
                    //97  其他费用 -- 暂无
                    //979 司机工资
                    var opDriverCost = item.FirstOrDefault(x => x.SId == 979);
                    if (opDriverCost != null) 
                        if (opDriverCost.Price * _opRate > _teamRate * _opPayPercentage * opCostInfo.Sum(x => x.DriverFee) * _opPayPercentage) isAutoAudit = false;
                    //980 司机小费
                    var opDriverTipCost = item.FirstOrDefault(x => x.SId == 980);
                    if (opDriverTipCost != null) 
                        if (opDriverTipCost.Price * _opRate * _opPayPercentage > _teamRate * opCostInfo.Sum(x => x.DriverTipFee) * _opPayPercentage) isAutoAudit = false;
                    //981 司机餐补
                    var opDriverMealCost = item.FirstOrDefault(x => x.SId == 981);
                    if (opDriverMealCost != null) 
                        if (opDriverMealCost.Price * _opRate * _opPayPercentage > _teamRate * opCostInfo.Sum(x => x.DriverMealFee) * _opPayPercentage) isAutoAudit = false;
                    //988 客户早餐费用
                    var opClientBreakfastCost = item.FirstOrDefault(x => x.SId == 988);
                    if (opClientBreakfastCost != null) 
                        if (opClientBreakfastCost.Price * _opRate * _opPayPercentage > _teamRate * opCostInfo.Sum(x => x.ClientBreakfastFee) * _opPayPercentage) isAutoAudit = false;
                    //93  客户午餐费用
                    var opClientLunchCost = item.FirstOrDefault(x => x.SId == 93);
                    if (opClientLunchCost != null) 
                        if (opClientLunchCost.Price * _opRate * _opPayPercentage > _teamRate * opCostInfo.Sum(x => x.ClientLunchFee) * _opPayPercentage) isAutoAudit = false;
                    //989 客户晚餐费用
                    var opClientDinnerCost = item.FirstOrDefault(x => x.SId == 989);
                    if (opClientDinnerCost != null) 
                        if (opClientDinnerCost.Price * _opRate * _opPayPercentage > _teamRate * opCostInfo.Sum(x => x.ClientDinnerFee) * _opPayPercentage) isAutoAudit = false;
                    //990 景点门票费
                    var opScenicTicketCost = item.FirstOrDefault(x => x.SId == 990);
                    if (opScenicTicketCost != null) 
                        if (opScenicTicketCost.Price * _opRate * _opPayPercentage > _teamRate * opCostInfo.Sum(x => x.ScenicTicketFee) * _opPayPercentage) isAutoAudit = false;
                    //991 饮料/零食/水果
                    var opDrinkSnackFruitCost = item.FirstOrDefault(x => x.SId == 991);
                    if (opDrinkSnackFruitCost != null) 
                        if (opDrinkSnackFruitCost.Price * _opRate * _opPayPercentage > _teamRate * opCostInfo.Sum(x => x.DrinkSnackFruitFee) * _opPayPercentage) isAutoAudit = false;
                    //992 住补费用 -- 暂无
                    //994 翻译费
                    var opTranslatorCost = item.FirstOrDefault(x => x.SId == 994);
                    if (opTranslatorCost != null) 
                        if (opTranslatorCost.Price * _opRate * _opPayPercentage > _teamRate * opCostInfo.Sum(x => x.TranslatorFee) * _opPayPercentage) 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
                    if (QuashAudit(79, diId, dataId))
                    {
                        _view.Code = 200;
                        _view.Msg = "费用超团组成本,自动审核撤销成功!";
                        return _view;
                    }
                }
            }
            else if (feeType == 3)
            {
                #region 出行物资的功能及相关费用自动审核

                var isAutoAudit = false;
                var currModule = 98; //其他款项
                int groupSize = 0; // 团组人数
                var groupDetails = await _sqlSugar.Queryable<Grp_DelegationInfo>().Where(x => x.IsDel == 0 && x.Id == diId).FirstAsync();
                if (groupDetails != null) groupSize = groupDetails.VisitPNumber;

                decimal otherSubTotal = costContents.Sum(x => x.TeFee);
                decimal groupCostCNYTotal = costContents.Sum(x => x.TeFee) * _teamRate * groupSize; //团组成本出行物资总金额
                if (groupCostCNYTotal <= 0.00M)
                {
                    _view.Msg = $"团组成本出行物资费用数据未填写";
                    QuashAudit(currModule, diId, dataId);
                    return _view;
                }

                var teNames = setData.Where(x => x.STid == 91).Select(x => x.Name).ToList();
                var otherFeeDatas = _sqlSugar.Queryable<Grp_DecreasePayments>()
                    .InnerJoin<Grp_CreditCardPayment>((dp, ccp) => dp.Id == ccp.CId && ccp.CTable == 98 && ccp.IsDel == 0)
                    .Where((dp, ccp) => dp.IsDel == 0 && ccp.IsDel == 0 && dp.DiId == diId)
                    .ToList();

                var ids = new List<int>();
                foreach (var item in otherFeeDatas)
                {
                    if (item.PriceName.Contains("、"))
                    {
                        var priceNames = item.PriceName.Split('、');
                        foreach (var priceName in priceNames)
                        {
                            if (teNames.Contains(priceName))
                            {
                                ids.Add(item.Id);
                                continue;
                            }
                        }
                    }
                    else if (teNames.Contains(item.PriceName))
                    {
                        ids.Add(item.Id);
                    }
                }
                ids = ids.Distinct().ToList();

                //验证该费用是不是出行物资费用 是：审核 不是：不审核
                if (!ids.Contains(dataId))
                {
                    _view.Msg = $"该费用不属于出行物资,不执行自动审核!";
                    QuashAudit(currModule, diId, dataId);
                    return _view;
                }

                decimal otherFeeCNYTotal = otherFeeDatas.Where(x => ids.Contains(x.Id)).Sum(x => x.FeeTotal);  //其他费用出行物资总金额
                if (otherFeeCNYTotal <= 0.00M)
                {
                    _view.Msg = $"其他款项出行物资费用数据未填写";
                    QuashAudit(currModule, diId, dataId);
                    return _view;
                }

                if (otherFeeCNYTotal > groupCostCNYTotal)
                {
                    _view.Msg = $"其他款项出行物资费用超出团组成本物资费用";
                    QuashAudit(currModule, diId, dataId);
                    return _view;
                }
                isAutoAudit = true;
                //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 == currModule && s.CId == dataId)
                        .ExecuteCommand();
                    if (ccpUpdate > 0)
                    {
                        _view.Code = 200;
                        _view.Msg = "自动审核执行成功";
                        return _view;
                    }
                }
                else
                {
                    //撤销该条数据的自动审核 --> 该条数据的审核状态是自动审核  3 --> 0
                    var quashStatus = QuashAudit(currModule, diId, dataId);

                    if (quashStatus)
                    {
                        _view.Code = 200;
                        _view.Msg = "费用超团组成本,自动审核撤销成功!";
                        return _view;
                    }
                }

                #endregion
            }
            else if (feeType == 4)
            {
                #region 保险费用录入自动审核
                var currModule = 82;
                var groupDetails = _sqlSugar.Queryable<Grp_DelegationInfo>().Where(x => x.IsDel == 0 && x.Id == diId).First(); 
                if (groupDetails == null)
                {
                    _view.Msg = $"团组信息为空，不可自动审核！";
                    return _view;
                }

                var insuranceCountryCostData = _sqlSugar.Queryable<Res_BasicInsuranceCost>().Where(x => x.IsDel == 0).ToList();
                if (!insuranceCountryCostData.Any())
                {
                    _view.Msg = $"保险国家费用信息为空，不可自动审核！";
                    return _view;
                }

                var insuranceType = _sqlSugar.Queryable<Grp_InsuranceCost>().Where(x => x.IsDel == 0 && x.Id != 2).Select(x => x.Id).ToList();
                var insuranceCostData1 = _sqlSugar.Queryable<Grp_Customers>()
                    .LeftJoin<Grp_CreditCardPayment>((c, ccp) => c.Id == ccp.CId && ccp.CTable == 82)
                    .Where((c, ccp) => c.IsDel == 0 && c.DiId == diId)
                    .Select((c, ccp) => new
                    {
                        c.Id,
                        c.Iid,
                        ccpId = ccp.Id,
                        CNYPrice = ccp.PayMoney * ccp.DayRate
                    })
                    .ToList();

                if (!insuranceCostData1.Any())
                {
                    _view.Msg = $"暂无保险数据，不可自动审核！";
                    return _view;
                }

                if (insuranceCostData1.Where(x => x.Iid == 2).ToList().Any())
                {
                    _view.Msg = $"保险数据为类型为“新数据请不要选此项”，不可自动审核！";
                    return _view;
                }

                #region 处理已审核通过的 “新数据请不要选此项” 数据
                var insuranceCostData2 = insuranceCostData1.Where(x => x.Iid == 2).ToList();

                if (insuranceCostData2.Any())
                {
                    //_view.Msg = $"保险数据为类型为“新数据请不要选此项”，不可自动审核！";
                    //return _view;
                    var ccpIds = insuranceCostData2.Select(x => x.ccpId).ToList();
                    var auditCcpIds = _sqlSugar.Queryable<Grp_CreditCardPayment>().Where(x => x.IsDel == 0 && ccpIds.Contains(x.Id) && x.IsAuditGM == 3).Select(x => x.Id).ToList();
                    if (auditCcpIds.Any())
                    {
                        var ccpUpd = _sqlSugar.Updateable<Grp_CreditCardPayment>()
                           .SetColumns(it => it.IsAuditGM == 0)
                           .SetColumns(it => it.AuditGMOperate == 0)
                           .SetColumns(it => it.AuditGMDate == string.Empty)
                           .Where(s => auditCcpIds.Contains(s.Id))
                           .ExecuteCommand();
                    }
                }

                #endregion

                //var insuranceCostData = insuranceCostData1.Where(x => insuranceType.Contains(x.Iid)).ToList();
                var insuranceCostData = insuranceCostData1.ToList();
                var currInsuranceInfo = insuranceCostData.Where(x => x.Id == dataId).FirstOrDefault();
                if (!insuranceCostData.Any() && currInsuranceInfo == null)
                {
                    _view.Msg = $"暂无保险数据，不可自动审核！";
                    return _view;
                }

                if (currInsuranceInfo.CNYPrice == 0.00M)
                {
                    _view.Msg = $"保险数据未录入费用信息，不可自动审核！";
                    QuashAudit(currModule, diId, dataId);
                    return _view;
                }

                var groupPeopleNum = groupDetails.VisitPNumber;
                var visitCountrys = groupDetails.VisitCountry.Split("|").ToList();

                if (!visitCountrys.Any())
                {
                    _view.Msg = $"出访国家为空，不可自动审核！";
                    return _view;
                }

                var basicCountrys = insuranceCountryCostData.Select(x => x.CountryName).ToList();
                var schengenCountry = visitCountrys.Intersect(basicCountrys); //申根国
                var unSchengenCountry = visitCountrys.Except(basicCountrys);  //非申根国

                var schengenCost = insuranceCountryCostData.Where(x => schengenCountry.Contains(x.CountryName)).Sum(x => x.Cost); // 申根国费用
                var unSchengenCost = unSchengenCountry.Count() * 35; // 非申根国费用

                var groupBudgetCost = (schengenCost + unSchengenCost) * groupPeopleNum;
                var groupActialCost = insuranceCostData.Sum(x => x.CNYPrice);

                if (groupActialCost > groupBudgetCost)
                {
                    _view.Msg = $"保险费用超出团组成本费用";
                    QuashAudit(currModule, diId, dataId);
                    return _view;
                }

                //自动审核
                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 == currModule && s.CId == dataId)
                                      .ExecuteCommand();
                if (ccpUpdate > 0)
                {
                    _view.Code = 200;
                    _view.Msg = "自动审核执行成功";
                }
                else _view.Msg = "自动审核执行失败";
                return _view;
                #endregion
            }
            else _view.Msg = $"请传入有效的feeType参数";

            return _view;
        }

        /// <summary>
        /// hotel、op 撤销自动审核的数据
        /// </summary>
        /// <param name="type">
        /// 酒店 76
        /// op   79
        /// </param>
        /// <param name="diId"></param>
        /// <param name="dataId"></param>
        /// <returns></returns>
        private bool QuashAudit(int type, int diId, int dataId)
        {
            //撤销该条数据的自动审核 --> 该条数据的审核状态是自动审核  3 --> 0
            var ccpInfo = _sqlSugar.Queryable<Grp_CreditCardPayment>()
                                   .Where(s => s.DIId == diId && s.CTable == type && 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)
                {
                    return true;
                }
            }

            return false;
        }

        //
        /// <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 string IsOverBudget(int feeType, int diId, int dataId)
        {

            string _view ="-";
            if (diId < 1) {  return _view; }
            if (dataId < 1) { 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;

            var groupInfo = _sqlSugar.Queryable<Grp_GroupCostParameter>().Where(x => x.IsDel == 0 && x.DiId == diId).First();
            if (groupInfo == null) { return _view; }
            _teamCurrency = groupInfo.Currency;
            //币种验证  统一为currencycode三字码
            if (int.TryParse(_teamCurrency, out int currency)) _teamCurrency = setData.Find(x => x.Id == currency)?.Name ?? "";


            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) {  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) return _view;

                //获取C表汇率
                decimal _rate = 1.0000M;
                var roomFeeInfo = hotelCostDetails.Where(x => x.PriceType == 1).First();
                if (roomFeeInfo == null) return _view;

                _rate = roomFeeInfo.Rate == 0.0000M ? 1.0000M : roomFeeInfo.Rate;

                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 * (x.Rate == 0.0000M ? 1.0000M : x.Rate));
                if (otherFee > 0) { otherFee /= (checkOut - checkIn).Days; }
                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) * _rate;
                    if (singleRoomPrice > 0) if (singleRoomPrice > hotelSingleRoomFee * _rate) isAutoAudit = false;

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

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

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

                //2.判断是否自动审核
                if (isAutoAudit)
                {
                    return $"未超预算";
                }
            }
            else if (feeType == 2)
            {
                //1.基础数据参数验证
                var priceType = new List<int>() { 1062 };
                var opinfos = _sqlSugar.Queryable<Grp_CarTouristGuideGroundReservations>()
                                       .Where(x => x.IsDel == 0 && x.DiId == diId && x.Id == dataId && !priceType.Contains(x.PriceType))
                                       .First();
                //var opinfos = _sqlSugar.Queryable<Grp_CarTouristGuideGroundReservations>()
                //                       .Where(x => x.IsDel == 0 && x.DiId == diId && x.Id == dataId )
                //                       .First();
                if (opinfos == null) return _view;
                //1.含超时费用/超支费用 手动审核
                if (opinfos.SelectCheck.Contains("超时") || opinfos.SelectCheck.Contains("超支") || opinfos.SelectCheck.Contains("尾款")) 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) return _view;

                //获取C表汇率
                decimal _rate = 1.0000M;
                var payInfo = _sqlSugar.Queryable<Grp_CreditCardPayment>().Where(x => x.IsDel == 0 && x.DIId == diId && x.CTable == 79 && x.Id == dataId).First();
                if (payInfo == null) return _view;
                _rate = payInfo.DayRate;

                string opCurrencyName = setData.Find(x => x.Id == opContents[0].Currency)?.Name ?? "";

                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)) 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) return _view;

                var noAuditFeeTypeIds = new List<int> {
                   982 ,//982 车超时费 -- 暂无
                   96  ,//96  接送机费 -- 暂无
                   97  ,//97  其他费用 -- 暂无
                   992 ,//992 住补费用 -- 暂无
                   1059,//1059    导游超时费用 -- 暂无
                   1070,//1070    尾款金额 -- 暂无
                   1071,//1071    其他额外费用 -- 暂无
                   1073,//1073    翻译超时费 -- 暂无
                   1074,//1074    早餐超支费用 -- 暂无
                   1075,//1075    午餐超支费用 -- 暂无
                   1076,//1076    晚餐超支费用 -- 暂无
                };
                //费用类型筛选 包含 feeTypeIds && 包含这些类型费用大于0
                var noAuditFeeContents = opContents.Where(x => x.Price > 0 && noAuditFeeTypeIds.Contains(x.SId)).ToList();
                if (noAuditFeeContents.Count > 0) 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 > _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 > _rate * opCostInfo.Sum(x => x.GuideFee)) isAutoAudit = false;
                    //94  导游景点费
                    var opGuideScenicCost = item.FirstOrDefault(x => x.SId == 94);
                    if (opGuideScenicCost != null) if (opGuideScenicCost.Price * _rate > _rate * opCostInfo.Sum(x => x.GuideScenicFee)) isAutoAudit = false;
                    //95  导游小费
                    var opGuideTipCost = item.FirstOrDefault(x => x.SId == 95);
                    if (opGuideTipCost != null) if (opGuideTipCost.Price * _rate > _rate * opCostInfo.Sum(x => x.GuideTipFee)) isAutoAudit = false;
                    //983 导游餐补
                    var opGuideMealCost = item.FirstOrDefault(x => x.SId == 983);
                    if (opGuideMealCost != null) if (opGuideMealCost.Price * _rate > _rate * opCostInfo.Sum(x => x.GuideMealFee)) isAutoAudit = false;
                    //984 导游房补
                    var opGuideRoomCost = item.FirstOrDefault(x => x.SId == 984);
                    if (opGuideRoomCost != null) if (opGuideRoomCost.Price * _rate > _rate * opCostInfo.Sum(x => x.GuideRoomFee)) isAutoAudit = false;
                    //985 导游交通
                    var opGuideTrafficCost = item.FirstOrDefault(x => x.SId == 985);
                    if (opGuideTrafficCost != null) if (opGuideTrafficCost.Price * _rate > _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 > _rate * opCostInfo.Sum(x => x.DriverFee)) isAutoAudit = false;
                    //980 司机小费
                    var opDriverTipCost = item.FirstOrDefault(x => x.SId == 980);
                    if (opDriverTipCost != null) if (opDriverTipCost.Price * _rate > _rate * opCostInfo.Sum(x => x.DriverTipFee)) isAutoAudit = false;
                    //981 司机餐补
                    var opDriverMealCost = item.FirstOrDefault(x => x.SId == 981);
                    if (opDriverMealCost != null) if (opDriverMealCost.Price * _rate > _rate * opCostInfo.Sum(x => x.DriverMealFee)) isAutoAudit = false;
                    //988 客户早餐费用
                    var opClientBreakfastCost = item.FirstOrDefault(x => x.SId == 988);
                    if (opClientBreakfastCost != null) if (opClientBreakfastCost.Price * _rate > _rate * opCostInfo.Sum(x => x.ClientBreakfastFee)) isAutoAudit = false;
                    //93  客户午餐费用
                    var opClientLunchCost = item.FirstOrDefault(x => x.SId == 93);
                    if (opClientLunchCost != null) if (opClientLunchCost.Price * _rate > _rate * opCostInfo.Sum(x => x.ClientLunchFee)) isAutoAudit = false;
                    //989 客户晚餐费用
                    var opClientDinnerCost = item.FirstOrDefault(x => x.SId == 989);
                    if (opClientDinnerCost != null) if (opClientDinnerCost.Price * _rate > _rate * opCostInfo.Sum(x => x.ClientDinnerFee)) isAutoAudit = false;
                    //990 景点门票费
                    var opScenicTicketCost = item.FirstOrDefault(x => x.SId == 990);
                    if (opScenicTicketCost != null) if (opScenicTicketCost.Price * _rate > _rate * opCostInfo.Sum(x => x.ScenicTicketFee)) isAutoAudit = false;
                    //991 饮料/零食/水果
                    var opDrinkSnackFruitCost = item.FirstOrDefault(x => x.SId == 991);
                    if (opDrinkSnackFruitCost != null) if (opDrinkSnackFruitCost.Price * _rate > _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 > _rate * opCostInfo.Sum(x => x.TranslatorFee)) isAutoAudit = false;
                    //1059    导游超时费用 -- 暂无
                    //1070    尾款金额 -- 暂无
                    //1071    其他额外费用 -- 暂无
                    //1073    翻译超时费 -- 暂无
                    //1074    早餐超支费用 -- 暂无
                    //1075    午餐超支费用 -- 暂无
                    //1076    晚餐超支费用 -- 暂无
                }

                //更改审核状态
                if (isAutoAudit) return _view;
            }
            else return _view; 

            return _view;
        }

    }
}