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

using AutoMapper;
using OASystem.Domain;
using OASystem.Domain.Dtos.Groups;
using OASystem.Domain.Entities.Financial;
using OASystem.Domain.Entities.Groups;
using OASystem.Domain.Entities.Resource;
using OASystem.Domain.ViewModels.Groups;
using OASystem.Infrastructure.Tools;
using System.Collections.Generic;

namespace OASystem.Infrastructure.Repositories.Groups
{
    public class CarTouristGuideGroundRepository:BaseRepository<Grp_CarTouristGuideGroundReservations, Grp_CarTouristGuideGroundReservationsContent>
    {
        private readonly IMapper _mapper;

        public CarTouristGuideGroundRepository(SqlSugarClient sqlSugar, IMapper mapper)
            : base(sqlSugar)
        {
            _mapper = mapper;
        }

        public async Task<Result> OpCarTouristGuideGround(OpCarTouristGuideGroundDto dto)
        {
            Result result = new Result() { Code = -2, Msg = "未知错误" };
            BeginTran();
            try
            {
                int id = 0;
                Grp_CarTouristGuideGroundReservations grp_CarTouristGuide = _mapper.Map<Grp_CarTouristGuideGroundReservations>(dto);
                if (dto.Status == 1)//添加
                {
                    Grp_CarTouristGuideGroundReservations grp_CarTouristGuideGround = _sqlSugar.Queryable<Grp_CarTouristGuideGroundReservations>().First(a => a.IsDel == 0 && a.Area == dto.Area && a.BusName == dto.BusName && a.ServiceGuide == dto.ServiceGuide && a.DiId==dto.DiId && a.BusTel==dto.BusTel && a.ServiceTel==dto.ServiceTel);
                    if (grp_CarTouristGuideGround != null)
                    {
                        return result = new Result() { Code = -1, Msg = "该笔费用已存在，请勿重复添加！" };
                    }
                    else
                    {
                        id = await AddAsyncReturnId(grp_CarTouristGuide);
                        if (id == 0)
                        {
                            RollbackTran();
                            result = new Result() { Code = -1, Msg = "添加失败，请稍后重试！" };
                        }
                        else
                        {
                            result = new Result() { Code = 0, Msg = "添加成功！" };
                        }

                    }

                }
                else if (dto.Status == 2)//修改
                {

                    bool res = await UpdateAsync(a => a.Id == dto.Id, a => new Grp_CarTouristGuideGroundReservations
                    {
                        Area = grp_CarTouristGuide.Area,
                        ServiceCompany = grp_CarTouristGuide.ServiceCompany,
                        ServiceGuide = grp_CarTouristGuide.ServiceGuide,
                        ServiceTel = grp_CarTouristGuide.ServiceTel,
                        BusName = grp_CarTouristGuide.BusName,
                        BusDescription = grp_CarTouristGuide.BusDescription,
                        BusTel = grp_CarTouristGuide.BusTel,
                        ServiceStartTime = grp_CarTouristGuide.ServiceStartTime,
                        ServiceEndTime = grp_CarTouristGuide.ServiceEndTime,
                        ServiceDescription = grp_CarTouristGuide.ServiceDescription,
                        QuotedPriceExplanation = grp_CarTouristGuide.QuotedPriceExplanation,
                        OrbitalPrivateTransfer = grp_CarTouristGuide.OrbitalPrivateTransfer,
                        Remark = grp_CarTouristGuide.Remark,
                    });
                    if (res)
                    {
                        id = dto.Id;
                        result = new Result() { Code = 0, Msg = "修改成功！" };
                    }
                    else
                    {
                        RollbackTran();
                        result = new Result() { Code = -1, Msg = "修改失败，请稍后重试！" };
                    }
                }
                if (id!=0)
                {
                    Res_LocalGuideData _LocalGuideDataCar = _sqlSugar.Queryable<Res_LocalGuideData>().First(a => a.UnitName == dto.BusName && a.ContactTel == dto.BusTel && a.IsDel == 0);
                    if (_LocalGuideDataCar == null)
                    {
                        Res_LocalGuideData res_LocalGuideCar = new Res_LocalGuideData();
                        res_LocalGuideCar.UnitName = dto.BusName;
                        res_LocalGuideCar.ContactTel = dto.BusTel;
                        res_LocalGuideCar.CreateUserId = dto.CreateUserId;
                        int cId = await _sqlSugar.Insertable(res_LocalGuideCar).ExecuteReturnIdentityAsync();
                    }
                    Res_LocalGuideData _LocalGuideDataService = _sqlSugar.Queryable<Res_LocalGuideData>().First(a => a.UnitName == dto.ServiceCompany && a.ContactTel == dto.ServiceTel && a.IsDel == 0);
                    if (_LocalGuideDataService == null)
                    {
                        Res_LocalGuideData res_LocalGuideService = new Res_LocalGuideData();
                        res_LocalGuideService.UnitName = dto.ServiceCompany;

                        res_LocalGuideService.ContactTel = dto.ServiceTel;
                        res_LocalGuideService.Contact = dto.ServiceTel;
                        res_LocalGuideService.CreateUserId = dto.CreateUserId;
                        int cId = await _sqlSugar.Insertable(res_LocalGuideService).ExecuteReturnIdentityAsync();
                    }
                }
                
                CommitTran();
            }
            catch (Exception ex)
            {
                result = new Result() { Code = -2, Msg = "未知错误" };
            }
            return result;
        }

        public async Task<Result> CarTouristGuideGroundContent(CarTouristGuideGroundContentDto dto)
        {
            Result result = new Result() { Code = -2, Msg = "未知错误" };
            try
            {
                List<Grp_GroupsTaskAssignment> grp_GroupsTaskAssignment = Query<Grp_GroupsTaskAssignment>(a => a.IsDel == 0 && a.UId == dto.UserId && a.CTId == 79).ToList();

                string DiId = "0";
                foreach (var item in grp_GroupsTaskAssignment)
                {
                    DiId += item.DIId + ",";
                }
                if (DiId != "0")
                {
                    DiId = DiId.Substring(0, DiId.Length - 1);
                }
                string sql = string.Format(@"select * from Grp_DelegationInfo where Id in({0}) and IsDel={1}", DiId, 0);

                //团组下拉框
                List<Grp_DelegationInfo> _Delegations = _sqlSugar.SqlQueryable<Grp_DelegationInfo>(sql).ToList();
                List<ShareGroupInfoIIView> grp_Delegations = _mapper.Map<List<ShareGroupInfoIIView>>(_Delegations);
                //支付方式
                List<Sys_SetData> Payment = _sqlSugar.Queryable<Sys_SetData>().Where(a => a.STid == 14 && a.IsDel == 0).ToList();
                List<SetDataInfoView> _Payment = _mapper.Map<List<SetDataInfoView>>(Payment);

                //币种
                List<Sys_SetData> CurrencyList = _sqlSugar.Queryable<Sys_SetData>().Where(a => a.STid == 66 && a.IsDel == 0).ToList();
                List<SetDataInfoView> _CurrencyList = _mapper.Map<List<SetDataInfoView>>(CurrencyList);

                //列表初始化
                List<Sys_SetData> TableInitialization = _sqlSugar.Queryable<Sys_SetData>().Where(a => a.STid == 17 && a.IsDel == 0).ToList();
                List<SetDataInfoView> _TableInitialization = _mapper.Map<List<SetDataInfoView>>(TableInitialization);
                _TableInitialization= _TableInitialization.OrderBy(a=>a.Name).ToList();

                var data = new
                {
                    Payment = _Payment,
                    CurrencyList = _CurrencyList,
                    Delegations = grp_Delegations,
                    TableInitialization = _TableInitialization
                };
                return result = new Result() { Code = 0, Msg = "查询成功",Data=data };

            }
            catch (Exception)
            {
                return result = new Result() { Code = -2, Msg = "未知错误" };
                throw;
            }
        }

        public async Task<Result> OpCarTouristGuideGroundContentById(CarTouristGuideGroundIdDto dto)
        {
            Result result = new Result() { Code = -2, Msg = "未知错误" };
            try
            {
               
                List<Grp_CarTouristGuideGroundReservationsContent> grp_CarTouristsContent = _sqlSugar.Queryable<Grp_CarTouristGuideGroundReservationsContent>().Where(a=>a.IsDel==0 && a.CTGGRId==dto.Id).ToList();
                List<CarTouristGuideGroundReservationsContentView> carTouristGuides = _mapper.Map<List<CarTouristGuideGroundReservationsContentView>>(grp_CarTouristsContent);
                foreach (var item in carTouristGuides)
                {
                    item.SidName= _sqlSugar.Queryable<Sys_SetData>().First(a => a.Id == item.SId).Name;
                }
                Grp_CreditCardPayment CreditCardPayment = _sqlSugar.Queryable<Grp_CreditCardPayment>().First(a => a.CId == dto.Id && a.IsDel == 0 && a.CTable == 79);
                CarTouristCreditCardPaymentView _CreditCardPayment = _mapper.Map<CarTouristCreditCardPaymentView>(CreditCardPayment);
                if (_CreditCardPayment!=null)
                {
                    _CreditCardPayment.PayName = _sqlSugar.Queryable<Sys_SetData>().First(a => a.Id == _CreditCardPayment.PayDId).Name;
                    _CreditCardPayment.PaymentCurrencyName = _sqlSugar.Queryable<Sys_SetData>().First(a => a.Id == _CreditCardPayment.PaymentCurrency).Name;
                    if (_CreditCardPayment.OrbitalPrivateTransfer==0) _CreditCardPayment.OrbitalPrivateTransferStr = "公转";
                    else _CreditCardPayment.OrbitalPrivateTransferStr = "私转";
                }
                var data = new
                {
                    CarTouristsContent = carTouristGuides,
                    CreditCardPayment = _CreditCardPayment,
                };
                return result = new Result() { Code = 0, Msg = "查询成功", Data = data };

            }
            catch (Exception)
            {
                return result = new Result() { Code = -2, Msg = "未知错误" };
                throw;
            }
        }
        /// <summary>
        /// 根据diid查询op费用列表
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        public async Task<Result> QueryCarTouristGuideGroundByDiId(CarTouristGuideGroundDto dto)
        {
            Result result = new Result() { Code = -2, Msg = "未知错误" };
            if (dto.PortType == 0 || string.IsNullOrWhiteSpace(dto.PortType.ToString()))
            {
                return result = new Result() { Code = -1, Msg = "请传入PortType参数，请求端口分类 1 Web 2 Android 3 IOS" };
            }
            try
            {
                #region SQL条件拼接

                string sqlWhere = string.Empty;
                sqlWhere += string.Format(@"And t.DIId={0} and t.isdel={1}", dto.DiId, 0);
                string UserId = "";
                List<Grp_GroupsTaskAssignment> gtaUIdList = _sqlSugar.Queryable<Grp_GroupsTaskAssignment>().Where(a => a.DIId == dto.DiId && a.IsDel == 0 && a.CTId == 79).ToList();
                foreach (Grp_GroupsTaskAssignment gta in gtaUIdList)
                    UserId += gta.UId + ",";

                if (!string.IsNullOrWhiteSpace(UserId))
                {
                    UserId = UserId.Substring(0, UserId.Length - 1);
                }
                else
                {
                    UserId = "0";
                }
                
                sqlWhere += string.Format(@" And t.CreateUserId in ({0})", UserId);
                if (!string.IsNullOrEmpty(sqlWhere.Trim()))
                {
                    Regex r = new Regex("And");
                    sqlWhere = r.Replace(sqlWhere, "Where", 1);
                }

                #endregion
                int startIndex = (dto.PageIndex - 1) * dto.PageSize + 1;
                int endIndex = startIndex + dto.PageSize - 1;
                if (dto.PortType == 1)
                {
                    string sql = string.Format(@"select t.*,s.Name as CurrencyStr,c.IsAuditGM,c.PayPercentage,c.PayMoney,c.PayDid from 
                                                Grp_CarTouristGuideGroundReservations t
                                                left Join Grp_CreditCardPayment c on CTable=79 and c.CId=t.Id  and c.isdel=0
                                                left Join Sys_SetData s on s.Id=c.PaymentCurrency {0}
                                                order by c.IsAuditGM,c.PayPercentage desc", sqlWhere);
                    List<Grp_CarTouristGuideGroundView> infoViews = _sqlSugar.SqlQueryable<Grp_CarTouristGuideGroundView>(sql).ToList();
                    foreach (var item in infoViews)
                    {
                        item.ServiceQuotedPrice = item.PayMoney;
                        item.PayThenMoney = Math.Round(item.PayPercentage/100 * item.ServiceQuotedPrice, 2);
                        item.RemainingBalance = Math.Round(item.ServiceQuotedPrice- item.PayPercentage / 100 * item.ServiceQuotedPrice);
                        Sys_SetData s = _sqlSugar.Queryable<Sys_SetData>().First(a=>a.IsDel==0 && a.Id==item.PayDid);
                        if (s!=null)
                        {
                            item.PayStrd = s.Name;
                        }
                    }
                    return result = new Result() { Code = 0, Msg = "查询成功！", Data = infoViews };
                }
                else if (dto.PortType == 2 || dto.PortType == 3)
                {
                    string sql = string.Format(@"Select * From (	
                                            Select row_number() over (order by c.IsAuditGM,c.PayPercentage desc) as RowNumber,t.*,s.Name as CurrencyStr,c.IsAuditGM,c.PayPercentage,c.PayDid
                                            from 
                                            Grp_CarTouristGuideGroundReservations t
                                            Join Grp_CreditCardPayment c on CTable=79 and c.CId=t.Id  and c.isdel=0
                                            left Join Sys_SetData s on s.Id=c.PaymentCurrency {0}
                                            ) temp Where RowNumber Between {1} and {2}", sqlWhere, startIndex, endIndex);

                    List<Grp_CarTouristGuideGroundView> grp_CarTourists = _sqlSugar.SqlQueryable<Grp_CarTouristGuideGroundView>(sql).ToList();
                    foreach (var item in grp_CarTourists)
                    {
                        item.ServiceQuotedPrice = item.PayMoney;
                        item.PayThenMoney = Math.Round(item.PayPercentage / 100 * item.ServiceQuotedPrice, 2);
                        item.RemainingBalance = Math.Round(item.ServiceQuotedPrice - item.PayPercentage / 100 * item.ServiceQuotedPrice);
                        Sys_SetData s = _sqlSugar.Queryable<Sys_SetData>().First(a => a.IsDel == 0 && a.Id == item.PayDid);
                        if (s != null)
                        {
                            item.PayStrd = s.Name;
                        }
                    }
                    string CountSql = string.Format(@"Select COUNT(1) as Count From (	
                                                        select t.*,s.Name as CurrencyStr,c.IsAuditGM,c.PayPercentage from 
                                                        Grp_CarTouristGuideGroundReservations t
                                                        Join Grp_CreditCardPayment c on CTable=79 and c.CId=t.Id  and c.isdel=0
                                                        left Join Sys_SetData s on s.Id=t.CId {0}
                                                        ) temp ", sqlWhere);
                    DataCount dataCount = _sqlSugar.SqlQueryable<DataCount>(CountSql).First();
                    if (dataCount != null)
                    {
                        int count = dataCount.Count;
                        float totalPage = (float)count / dto.PageSize;//总页数
                        if (totalPage == 0) totalPage = 1;
                        else totalPage = (int)Math.Ceiling((double)totalPage);


                        ListViewBase<Grp_CarTouristGuideGroundView> rst = new ListViewBase<Grp_CarTouristGuideGroundView>();
                        rst.DataList = grp_CarTourists;
                        rst.DataCount = count;
                        rst.CurrPageIndex = dto.PageIndex;
                        rst.CurrPageSize = dto.PageSize;
                        return result = new Result() { Code = 0, Msg = "查询成功！", Data = rst };
                    }
                }
            }
            catch (Exception)
            {
                return result = new Result() { Code = -2, Msg = "未知错误" };
                throw;
            }
            return result;
        }
        /// <summary>
        /// OP费用录入填写详情
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        public async  Task<Result> OpCarTouristGuideGroundContent(OpCarTouristGuideGroundContentDto dto)
        {
            Result result = new Result() { Code = -2, Msg = "未知错误" };
            try
            {
                BeginTran();
                List<Grp_CarTouristGuideGroundReservationsContent> carTouristList = new List<Grp_CarTouristGuideGroundReservationsContent>();
                foreach (var item in dto.OPContentList)
                {
                    Grp_CarTouristGuideGroundReservationsContent s = new Grp_CarTouristGuideGroundReservationsContent();
                    s.Id= item.Id;
                    s.DiId = dto.DiId;
                    s.CTGGRId = dto.CTGGRId;
                    s.SId = item.SId;
                    s.Price=item.Price;
                    s.PriceContent = item.priceContent;
                    s.Currency = dto.Currency;
                    //s.Remark=item.Remark;
                    s.CreateUserId = dto.CreateUserId;
                    carTouristList.Add(s);
                }
                foreach (var item in carTouristList)
                {
                   
                    Grp_CarTouristGuideGroundReservationsContent QueryData = _sqlSugar.Queryable<Grp_CarTouristGuideGroundReservationsContent>().First(a => a.SId == item.SId && a.Id == item.Id && a.DiId == item.DiId && a.IsDel==0);
                    if (QueryData==null)
                    {
                        if (item.Price!=0)
                        {
                            int id = await _sqlSugar.Insertable(item).ExecuteReturnIdentityAsync();
                            if (id == 0)
                            {
                                RollbackTran();
                                return result = new Result() { Code = -2, Msg = "添加失败" };
                            }
                        }
                       
                    }
                    else
                    {
                        int CTable = await _sqlSugar.Updateable<Grp_CarTouristGuideGroundReservationsContent>().Where(a => a.Id == item.Id).SetColumns(a => new Grp_CarTouristGuideGroundReservationsContent
                        {
                            Price = item.Price,
                            PriceContent = item.PriceContent,
                            Currency = dto.Currency,
                            Remark = item.Remark,
                        }).ExecuteCommandAsync();
                        if (CTable==0)
                        {
                            RollbackTran();
                            return result = new Result() { Code = -1, Msg = "修改失败，请稍后重试！" };
                        }
                    }
                }
                //修改C表数据
                List<Grp_CarTouristGuideGroundReservationsContent> carTouristListEnd = _sqlSugar.Queryable<Grp_CarTouristGuideGroundReservationsContent>().Where(a=>a.DiId==dto.DiId && a.CTGGRId==dto.CTGGRId && a.IsDel==0).ToList();
                Grp_CreditCardPayment c=new Grp_CreditCardPayment();
                c.PaymentCurrency = dto.Currency;
                foreach (var item in carTouristListEnd)
                {
                    c.PayMoney += item.Price;
                }
                c.PayPercentage = dto.PayPercentage;
                c.ConsumptionDate= DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
                c.CTable = 79;
                c.CId = dto.CTGGRId;
                c.IsAuditGM = 0;
                c.PayDId=dto.PayDId;
                c.DIId = dto.DiId;
                c.OrbitalPrivateTransfer = dto.OrbitalPrivateTransfer;
                c.Payee=dto.Payee;
                c.CreateUserId=dto.CreateUserId;
                if (c.PayDId == 72)
                {
                    c.IsPay = 1;
                }
                c.RMBPrice = c.PayMoney;
                c.DayRate = 1;
                Grp_TeamRate _TeamRate = _sqlSugar.Queryable<Grp_TeamRate>().First(a => a.DiId == dto.DiId && a.IsDel == 0 && a.CTable == 79);
                List<CurrencyInfo> currencyInfos = new List<CurrencyInfo>();
                if (_TeamRate != null)
                {
                    Sys_SetData _SetData = _sqlSugar.Queryable<Sys_SetData>().First(a => a.IsDel == 0 && a.Id == c.PaymentCurrency);
                    if (_SetData != null)
                    {
                        currencyInfos = CommonFun.GetCurrencyChinaToList(_TeamRate.Remark);
                        CurrencyInfo CurrencyRate = currencyInfos.FirstOrDefault(a => a.CurrencyCode == _SetData.Name);
                        if (CurrencyRate != null)
                        {
                            c.RMBPrice = c.PayMoney * Convert.ToDecimal(CurrencyRate.Rate);
                            c.DayRate = CurrencyRate.Rate;
                        }
                    }
                }
                Grp_CreditCardPayment grp_CreditCard = _sqlSugar.Queryable<Grp_CreditCardPayment>().First(a=>a.CId==dto.CTGGRId && a.IsDel==0 && a.CTable==79 && a.DIId==dto.DiId);
                if (grp_CreditCard!=null)//修改
                {
                    int CTable = await _sqlSugar.Updateable<Grp_CreditCardPayment>().Where(a => a.CId == dto.CTGGRId && a.CTable == 79 && a.DIId==dto.DiId).SetColumns(a => new Grp_CreditCardPayment
                    {
                        PaymentCurrency = c.PaymentCurrency,
                        PayMoney = c.PayMoney,
                        PayPercentage = c.PayPercentage,
                        CTable = c.CTable,
                        PayDId = c.PayDId,
                        OrbitalPrivateTransfer = c.OrbitalPrivateTransfer,
                        Payee = c.Payee,
                        IsPay = c.IsPay,
                        RMBPrice = c.RMBPrice,
                        DayRate = c.DayRate,
                    }).ExecuteCommandAsync();
                    if (CTable == 0)
                    {
                        RollbackTran();
                        return result = new Result() { Code = -2, Msg = "修改失败" };
                    }
                }
                else//添加
                {
                    int id = await _sqlSugar.Insertable(c).ExecuteReturnIdentityAsync();
                    if (id == 0)
                    {
                        RollbackTran();
                        return result = new Result() { Code = -2, Msg = "添加失败" };
                    }
                }
                int CarTouristGuideCTable = await _sqlSugar.Updateable<Grp_CarTouristGuideGroundReservations>().Where(a => a.Id == dto.CTGGRId && a.DiId == dto.DiId).SetColumns(a => new Grp_CarTouristGuideGroundReservations
                {
                    
                CId=dto.Currency,
                ServiceQuotedPrice=c.PayMoney,
                OrbitalPrivateTransfer = dto.OrbitalPrivateTransfer,
                }).ExecuteCommandAsync();
                if (CarTouristGuideCTable == 0)
                {
                    RollbackTran();
                    return result = new Result() { Code = -2, Msg = "修改失败" };
                }
                result = new Result() { Code =0, Msg = "保存成功" };
                CommitTran();
            }
            catch (Exception)
            {
                return result = new Result() { Code = -2, Msg = "未知错误" };
                throw;
            }
            return result;
        }
    }
}