using AutoMapper;
using Microsoft.AspNetCore.Http;
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Metadata.Internal;
using Microsoft.Extensions.Localization;
using MS.Common.IDCode;
using MS.Component.Jwt.UserClaim;
using MS.DbContexts;
using MS.Entities.AccountingService_Models;
using MS.Entities.AuthService_Models;
using MS.Entities.Models;
using MS.Models;
using MS.UnitOfWork;
using MS.WebCore.Core;
using Newtonsoft.Json;
using NLog;
using NPOI.SS.Formula.Functions;
using Org.BouncyCastle.Tsp;
using System;
using System.Collections.Generic;
using System.Globalization;
using System.Linq;
using System.Linq.Expressions;
using System.Text;
using System.Text.Json;
using System.Threading.Tasks;

namespace MS.Services.PosTrxService
{
    public class PosTrxLineItemsService : BaseService, IPosTrxLineItemsService
    {
        Expression<Func<PosTrxLineItems, bool>> whereClause;
        private readonly IHttpContextAccessor _httpContextAccessor;
        private readonly JsonSerializerOptions _jsonSerializerOptions;
        Logger logger = LogManager.GetCurrentClassLogger();
        public RedisExample redisExample = new RedisExample();
        public PosTrxLineItemsService(IUnitOfWork<MSDbContext> unitOfWork, IMapper mapper, IdWorker idWorker, IClaimsAccessor claimsAccessor, IStringLocalizer localizer, IHttpContextAccessor httpContextAccessor, IUnitOfWork<AccountingContext> AccountingContext, IUnitOfWork<SQLiteContext> SQLiteContext, IUnitOfWork<AuthContext> AuthContext) : base(unitOfWork, mapper, idWorker, claimsAccessor, localizer, AccountingContext, SQLiteContext, AuthContext)
        {
            _httpContextAccessor = httpContextAccessor;
            _jsonSerializerOptions = new JsonSerializerOptions
            {
                PropertyNameCaseInsensitive = true // 根据需要进行配置
            };
        }
        public async Task<ExecuteResult> fixFuelItemTagAsync(string startTime)
        {

            DateTime dateTime = string.IsNullOrEmpty(startTime) ? DateTime.Now : DateTime.Parse(startTime);
            DateTime enddateTime = string.IsNullOrEmpty(startTime) ? DateTime.Now.AddHours(-5) : DateTime.Parse(startTime).AddHours(-5);
            //            var query = _unitOfWork.GetRepository<PosTrxes>().FromSql(string.Format(@"select a.* FROM public.""PosTrxes"" a 
            //left join public.""PosTrxLineItems"" b on a.""Id""  = b.""PosTrxId"" 
            //left join public.""PosTrxMops"" c on a.""Id""  = c.""PosTrxId"" 
            //left join public.""PosMops"" d on c.""PosMopId""  = d.""Id"" 
            //where b.""FuelItemTag""  is null
            //and b.""Id"" = '1aa78939-7aa7-42b3-92fc-e2f8e934ddbd'
            //and a.""CreatedDateTime""  >=  '{0}' ", "2023-03-01")).Include(a => a.PosTrxLineItems)
            //.Include(a => a.PosTrxMops)
            //.ThenInclude(a => a.PosMop).ToList();
            //var query = _unitOfWork.GetRepository<PosTrxes>().GetAll().Include(a => a.PosTrxLineItems)
            //    .Include.ToList();
            //Guid guid_ = new Guid("000147d3-dd35-4c8a-a354-42be800ca57d");
            var query = _unitOfWork.GetRepository<PosTrxes>().GetAll().Include(a => a.PosTrxLineItems)
.Include(a => a.PosTrxMops)
.ThenInclude(a => a.PosMop).Where(a => a.CreatedDateTime >= enddateTime && a.CreatedDateTime <= dateTime
&& a.PosTrxLineItems.Any(b => b.FuelItemTag == null) //&& a.Id == guid_
 ).ToList();

            //            var query = _unitOfWork.GetRepository<PosTrxes>().GetAll().Include(a => a.PosTrxLineItems)
            //.Include(a => a.PosTrxMops)
            //.ThenInclude(a => a.PosMop).Where(a => a.PosTrxLineItems.Any(b => b.FuelItemTag == null) && a.Id == guid_
            //).ToList();

            foreach (var item in query)
            {
                var posTrxLineItems = item.PosTrxLineItems;
                var Mop = item.PosTrxMops?.FirstOrDefault()?.PosMop;
                var PosTrxMops = item.PosTrxMops;
                var fuelItemsFromRealPump = posTrxLineItems.Where(i => !string.IsNullOrEmpty(i.FuelItemFdcTransactionSeqNo) &&
                                                          i.FuelItemSoldOnPumpNozzleId != null);
                if (Mop == null)
                    continue;

                //string mopjson = SerializeObject(Mop);
                //MS.WebCore.Logger.Logger.WriteLog("Mop：" + JsonConvert.SerializeObject(mopjson));
                if (fuelItemsFromRealPump.Any())
                {
                    MyTransaction SequenceNo;
                    foreach (var trxitem in fuelItemsFromRealPump)
                    {
                        if (!string.IsNullOrEmpty(trxitem.FuelItemTag) || trxitem.FuelItemFdcTransactionSeqNo == null)
                            continue;
                        MS.WebCore.Logger.Logger.WriteLog("FuelItemFdcTransactionSeqNo：" + trxitem.FuelItemFdcTransactionSeqNo);
                        SequenceNo = findCardInfoByBusinessUnitInfoAndSequenceNo(item.TargetBusinessUnitId.ToString(), trxitem.FuelItemFdcTransactionSeqNo, trxitem.FuelItemSoldOnPumpNozzleId.ToString(), item.CreatedDateTime, Mop.PaymentId == 4);
                        if (SequenceNo != null)
                        {
                            MS.WebCore.Logger.Logger.WriteLog("FuelItemFdcTransactionSeqNo：" + trxitem.FuelItemFdcTransactionSeqNo + "card_no：" + SequenceNo.card_no);
                            var carno = SequenceNo.card_no;
                            if (carno.Length <= 8)//感应卡
                            {
                                if (SequenceNo.card_type != 1)
                                    continue;
                            }
                            else //CPU卡
                            {
                                if (SequenceNo.card_type != 4)
                                    continue;
                            }

                            var AspNetUsers = _AuthContext.GetRepository<AspNetUsers>().GetAll()
                            .Include(a => a.UserSubIdentities)
                            .Where(a => a.UserSubIdentities.Any(b => b.Identity == SequenceNo.card_no && b.Type == FuelItemTagDto.SmartFuelIcCardSystemName)).FirstOrDefault();
                            string AspNetUsersjson = SerializeObject(AspNetUsers);
                            MS.WebCore.Logger.Logger.WriteLog("FuelItemFdcTransactionSeqNo：" + trxitem.FuelItemFdcTransactionSeqNo + "AspNetUsers: " + AspNetUsersjson);
                            if (AspNetUsers == null)
                            {
                                continue;
                            }
                            var tagInfo = new FuelItemTagDto()
                            {
                                TagLinkedServiceIdentityUser = new UserInfos
                                {
                                    Id = Guid.Parse(AspNetUsers.Id),
                                    UserName = AspNetUsers.UserName,
                                    Alias = AspNetUsers.Alias,
                                    RestrictedInBusinessUnits = { }
                                },
                                TagSystemName = FuelItemTagDto.SmartFuelIcCardSystemName,
                                TagId = SequenceNo.card_no,
                                TagType = FuelItemTagDto.SmartFuelIcCardTagName,
                                Detail = "This Fuel Item from real pump is authorized by an attandant's smartFuel IC card"
                            };
                            trxitem.FuelItemTag = JsonConvert.SerializeObject(tagInfo);
                            _unitOfWork.GetRepository<PosTrxLineItems>().Update(trxitem);
                            await _unitOfWork.SaveChangesAsync();//提交
                            MS.WebCore.Logger.Logger.WriteLog("FuelItemFdcTransactionSeqNo：" + trxitem.FuelItemFdcTransactionSeqNo + "FuelItemTag：" + trxitem.Id.ToString());
                        }
                        else if (Mop.PaymentId == 4)
                        {
                            var entity = _unitOfWork.GetRepository<NozzleAssignments>().GetAll()
                                .Where(a => a.NozzleId == trxitem.FuelItemSoldOnPumpNozzleId && a.TargetBusinessUnitId == item.TargetBusinessUnitId)
                                .FirstOrDefault();
                            if (entity != null)
                            {
                                var AspNetUsersV2 = _AuthContext.GetRepository<AspNetUsers>().GetAll()
                                .Include(a => a.UserSubIdentities)
                                .Include(a => a.IdentityUserBusiUnitM2m)
                                .Where(a => a.IdentityUserBusiUnitM2m
                                .Any(b => b.BusinessUnitId == item.TargetBusinessUnitId)
                                && a.UserName == entity.OperatorName)
                                .FirstOrDefault();
                                if (AspNetUsersV2 != null)
                                {
                                    var tagInfo = new FuelItemTagDto()
                                    {
                                        TagLinkedServiceIdentityUser = new UserInfos
                                        {
                                            Id = Guid.Parse(AspNetUsersV2.Id),
                                            UserName = AspNetUsersV2.UserName,
                                            Alias = BuidUploadOrNot(item.TargetBusinessUnitId.ToString()) ? "" : AspNetUsersV2.Alias
                                        },
                                        TagSystemName = FuelItemTagDto.SmartFuelIcCardSystemName,
                                        TagId = AspNetUsersV2.UserSubIdentities?.FirstOrDefault().Identity,
                                        TagType = FuelItemTagDto.SmartFuelIcCardTagName,
                                        Detail = "This Fuel Item from real pump is authorized by IC card"
                                    };
                                    trxitem.FuelItemTag = JsonConvert.SerializeObject(tagInfo);
                                    _unitOfWork.GetRepository<PosTrxLineItems>().Update(trxitem);
                                    await _unitOfWork.SaveChangesAsync();//提交
                                    MS.WebCore.Logger.Logger.WriteLog("FuelItemTag：" + trxitem.Id.ToString());
                                }
                            }
                        }
                    }

                }
            }
            return null;
        }
        public bool BuidUploadOrNot(string buid)
        {

            bool __ = buid == "db3b74ff-412c-45fb-9887-9b10aa179704"
                         || buid == "f8c634fd-a231-456f-bd36-c6ee856820bc"
                         || buid == "df7033c4-ea67-48e3-ac4d-344ce22c6dda"
                         || buid == "9e0f39d7-3e63-4448-9347-e5f6ea0b1c13"
                         || buid == "fad023b9-333e-4a77-bea9-b07c95e5cad3"
                         || buid == "0c999874-3f3e-434c-8ee7-cd57bc34c33d"
                         || buid == "3d2122d2-fb89-415a-8310-4d5646804cc0";
            return __;
        }
        public MyTransaction findCardInfoByBusinessUnitInfoAndSequenceNo(string businessUnitId, string trxSequenceNo, string nozzleNo, DateTime dateTime, bool operatorIcCard)
        {
            DateTime requestDateTimeStart = dateTime.AddMilliseconds(-2 * 24 * 60 * 60 * 1000);
            DateTime requestDateTimeEnd = dateTime.AddMilliseconds(3 * 24 * 60 * 60 * 1000);

            string requestDateTimeStartString = requestDateTimeStart.ToString("yyyy-MM-dd HH:mm:ss");
            string requestDateTimeEndString = requestDateTimeEnd.ToString("yyyy-MM-dd HH:mm:ss");
            string car = operatorIcCard ? " trx.oper_card_no = ci.card_no" : "trx.card_info_id = ci.id";
            var sql = string.Format(@"
     SELECT trx.trx_sequence_no, trx.transaction_time, ci.card_no, ci.card_type, ci.card_class, ci.holder,
            ai.offline_account_id, ai.belong_to, trx.nozzle_no 
            FROM public.card_info ci 
            LEFT JOIN public.offline_transaction_info trx ON {5}
            LEFT JOIN public.account_info ai ON ai.id = ci.account_info_id 
            WHERE trx.business_unit_info_id = '{0}'
            AND trx.trx_sequence_no = '{1}'
            AND trx.nozzle_no = '{2}'
            AND trx.transaction_time > '{3}' 
            AND trx.transaction_time < '{4}'
            AND trx.transaction_type = 10 
            AND trx.amount <> 0 
            order by trx.transaction_time desc", businessUnitId, trxSequenceNo, nozzleNo, requestDateTimeStartString, requestDateTimeEndString, car);

            var results = _AccountingContext.DbContext.TransactionInfoDtos
                .FromSqlRaw(sql)
                  .FirstOrDefault();
            return results;
        }
        public MyTransaction findCardInfoByBusinessUnitInfoAndSequenceNo_2(string businessUnitId, string trxSequenceNo, string nozzleNo, DateTime dateTime, bool operatorIcCard)
        {
            DateTime requestDateTimeStart = dateTime.AddMilliseconds(-2 * 24 * 60 * 60 * 1000);
            DateTime requestDateTimeEnd = dateTime.AddMilliseconds(3 * 24 * 60 * 60 * 1000);

            string requestDateTimeStartString = requestDateTimeStart.ToString("yyyy-MM-dd HH:mm:ss");
            string requestDateTimeEndString = requestDateTimeEnd.ToString("yyyy-MM-dd HH:mm:ss");
            string car = operatorIcCard ? " trx.opercardno = ci.card_no" : "trx.cardno = ci.id";
            var sql = string.Format(@"
        SELECT trx.ttc trx_sequence_no, trx.ttctime transaction_time, ci.card_no, ci.card_type, ci.card_class, ci.holder,
          ai.offline_account_id, ai.belong_to, trx.nozno nozzle_no 
          FROM public.card_info ci 
          LEFT JOIN public.trxtable trx ON trx.cardno = ci.card_no
          LEFT JOIN public.account_info ai ON ai.id = ci.account_info_id 
          WHERE trx.buid = '{0}'
          AND trx.ttc = '{1}'
          AND trx.nozno = '{2}'
          AND trx.ttctime > '{3}' 
          AND trx.ttctime < '{4}'
          order by trx.ttctime desc", businessUnitId, trxSequenceNo, nozzleNo, requestDateTimeStartString, requestDateTimeEndString);

            var results = _AccountingContext.DbContext.TransactionInfoDtos
                .FromSqlRaw(sql)
                  .FirstOrDefault();
            return results;
        }
        public MyTransaction findOperatorCardInfoByBusinessUnitInfoAndSequenceNo(string businessUnitId, string trxSequenceNo, string nozzleNo, DateTime dateTime)
        {
            DateTime requestDateTimeStart = dateTime.AddMilliseconds(-2 * 24 * 60 * 60 * 1000);
            DateTime requestDateTimeEnd = dateTime.AddMilliseconds(3 * 24 * 60 * 60 * 1000);

            string requestDateTimeStartString = requestDateTimeStart.ToString("yyyy-MM-dd HH:mm:ss");
            string requestDateTimeEndString = requestDateTimeEnd.ToString("yyyy-MM-dd HH:mm:ss");
            var sql = string.Format(@"
     SELECT trx.trx_sequence_no, trx.transaction_time, ci.card_no, ci.card_type, ci.card_class, ci.holder,
            ai.offline_account_id, ai.belong_to, trx.nozzle_no 
            FROM public.card_info ci 
            LEFT JOIN public.offline_transaction_info trx ON trx.oper_card_no = ci.card_no
            LEFT JOIN public.account_info ai ON ai.id = ci.account_info_id 
            WHERE trx.business_unit_info_id = '{0}'
            AND trx.trx_sequence_no = '{1}'
            AND trx.nozzle_no = '{2}'
            AND trx.transaction_time > '{3}' 
            AND trx.transaction_time < '{4}'
            AND trx.transaction_type = 10 
            AND trx.amount <> 0 
            order by trx.transaction_time desc", businessUnitId, trxSequenceNo, nozzleNo, requestDateTimeStartString, requestDateTimeEndString);

            var results = _AccountingContext.DbContext.TransactionInfoDtos
                .FromSqlRaw(sql)
                .FirstOrDefault();
            return results;
        }
        public string SerializeObject(object obj)
        {
            JsonSerializerSettings settings = new JsonSerializerSettings();
            settings.ReferenceLoopHandling = ReferenceLoopHandling.Ignore;
            string mopjson = JsonConvert.SerializeObject(obj, settings);
            return mopjson;
        }
        public DateTime GetDate(List<KeyValuePair<string, string>> hashValues)
        {
            string date = string.Empty;
            List<DateTime> dates = new List<DateTime>();
            foreach (var kvp in hashValues)
            {
                DateTime dateTime1 = DateTime.Parse(kvp.Key);
                dates.Add(dateTime1);
            }
            DateTime maxPlusOneMonth = dates.Max().AddMonths(1);
            return maxPlusOneMonth;
        }
        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        public async Task<ExecuteResult> InsertNozzleDataRedis()
        {
            var buidlist = redisExample.GetListFromRedis("TargetBusinessUnitId");
            foreach (var i in buidlist)
            {
                List<KeyValuePair<string, string>> hashValues = redisExample.GetListValues("NozzleData:" + i);
                string date = string.Empty;
                DateTime now = DateTime.Now;
                if (hashValues.Count > 0)
                {
                    var maxPlusOneMonth = GetDate(hashValues);//
                    date = maxPlusOneMonth.ToString("yyyy-MM-dd");
                    // 检查年份和月份是否相同  
                    bool isInCurrentMonth = maxPlusOneMonth.Year == now.Year && maxPlusOneMonth.Month == now.Month;
                    if (!isInCurrentMonth)
                    {
                        await NozzleDataAsync(i, date);
                    }
                }
                else
                {
                    List<KeyValuePair<string, string>> buiddate = redisExample.GetListValues("BUID" + i);
                    if (buiddate.Count > 0)
                    {
                        var maxPlusOneMonth = GetDate(buiddate);//
                        date = maxPlusOneMonth.ToString("yyyy-MM-dd");
                        bool isInCurrentMonth = maxPlusOneMonth.Year == now.Year && maxPlusOneMonth.Month == now.Month;
                        if (!isInCurrentMonth)
                        {
                            await NozzleDataAsync(i, date);
                        }
                    }
                    else
                    {
                        date = "2022-01-01";
                    }
                    await NozzleDataAsync(i, date);
                }
                redisExample.AddToHash("BUID:"+i, date, DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
            }
            // var hashkey = redisExample.HashKeyExists(key, date);
            return null;
        }
        /// <summary>
        /// 收支与灌存、油枪报表按月缓存redis
        /// </summary>
        /// <returns></returns>
        public async Task<ExecuteResult> NozzleDataAsync(string buid, string datetime)
        {
            _unitOfWork.DbContext.Database.SetCommandTimeout(180);
            DateTime today = DateTime.Parse(datetime);
            DateTime firstDayOfLastMonth = new DateTime(today.Year, today.Month, 1).AddMonths(-1);
            DateTime lastDayOfLastMonth = firstDayOfLastMonth.AddDays(DateTime.DaysInMonth(firstDayOfLastMonth.Year, firstDayOfLastMonth.Month) - 1);
            string formattedFirstDay = firstDayOfLastMonth.ToString("yyyy-MM-dd 00:00:00", CultureInfo.InvariantCulture);//获取上个月最后一天
            string formattedLastDay = lastDayOfLastMonth.ToString("yyyy-MM-dd 23:59:59", CultureInfo.InvariantCulture);//获取上个月第一天
            string sql = string.Format(@"select  b.""FuelItemSoldOnPumpNozzleId"",c.""ItemName"" ,b.""ItemPrice"" from
 (
select a.""ShiftId"",a.""Id"" from ""PosTrxes"" a left join ""PosTrxLineItems"" b on a.""Id""  = b.""PosTrxId"" 
where a.""CreatedDateTime""  <= '{0}'
and a.""TransactionType""  = 6
and a.""TargetBusinessUnitId""  = '{1}'
order by a.""ShiftId""  desc
limit 1
) d 
left join ""PosTrxes"" a on d.""Id"" = a.""Id"" 
left join ""PosTrxLineItems"" b on a.""Id""  = b.""PosTrxId"" 
left join ""PosItems"" c on b.""PosItemId""  = c.""Id"" 
order by b.""FuelItemSoldOnPumpNozzleId"" ", formattedFirstDay, buid);
            var fuelDetails = _unitOfWork.DbContext.fuelDetailsDtos
                                .FromSqlRaw(sql)
                                .ToList();//枪号、油品名称、单价

            sql = string.Format(@"  SELECT  
        b.""FuelItemSoldOnPumpNozzleId"",  
         MAX(CASE WHEN a.""CreatedDateTime"" <= '{0}' THEN ""FuelItemPumpTotalizerVolume"" END) AS VolumeOnPumpStart,  
        MAX(CASE WHEN a.""CreatedDateTime"" <= '{1}' THEN ""FuelItemPumpTotalizerVolume"" END) AS VolumeOnPumpEnd  
    FROM  
        ""PosTrxes"" a  
    JOIN  
        ""PosTrxLineItems"" b ON a.""Id"" = b.""PosTrxId""  
          left join ""PosItems"" c on b.""PosItemId""  = c.""Id"" 
    WHERE  
        a.""TargetBusinessUnitId"" = '{2}'  
         AND ((a.""CreatedDateTime"" >= '{3}'  and a.""CreatedDateTime"" <= '{0}' )
        or (a.""CreatedDateTime"" >= '{4}'  and a.""CreatedDateTime"" <= '{1}' ))
        AND a.""TransactionStatus"" = 1  
        AND (b.""FuelItemSoldOnPumpNozzleId"" IS NOT NULL  and b.""FuelItemSoldOnPumpNozzleId""  > 0)
        AND b.""Voided"" = FALSE  
        group by          b.""FuelItemSoldOnPumpNozzleId"""
        , firstDayOfLastMonth.ToString("yyyy-MM-dd 00:00:00")
        , lastDayOfLastMonth.ToString("yyyy-MM-dd 23:59:59")
        , buid
        , firstDayOfLastMonth.AddDays(-2).ToString("yyyy-MM-dd 00:00:00")
        , lastDayOfLastMonth.AddDays(-2).ToString("yyyy-MM-dd 23:59:59")
        );
            var startEndPumpPair = _unitOfWork.DbContext.pumpVolumeSummaries
                              .FromSqlRaw(sql)
                              .ToList();//获取起止泵数，获取范围使用开始时间与结束时间共同往前推4天，只查8天。

            sql = string.Format(@"
        SELECT  
    (case when b.""FuelItemSoldOnPumpNozzleId"" > 0 then b.""FuelItemSoldOnPumpNozzleId"" else 0 end) FuelItemSoldOnPumpNozzleId,  
   -- d.""PaymentId"" ,
   -- b.""GrossAmount"",
     --b.""NetAmount"",
    --b.""Qty""
    SUM(CASE WHEN d.""PaymentId"" NOT IN (12, 30) THEN b.""Qty"" ELSE 0 END) AS VolumeOnPos,  
    SUM(CASE WHEN d.""PaymentId"" = 12 THEN b.""Qty"" ELSE 0 END) AS PumpTestVolume,  
    SUM(CASE WHEN d.""PaymentId"" = 30 THEN b.""Qty"" ELSE 0 END) AS SelfUseVolume,
    SUM(CASE WHEN (b.""GrossAmount"" IS NOT null and d.""PaymentId"" not in(12,30)) THEN b.""GrossAmount"" ELSE 0 END) AS TotalPaid,  
    SUM(CASE WHEN (b.""NetAmount"" IS NOT null and d.""PaymentId"" not in(12,30)) THEN b.""NetAmount"" ELSE 0 END) AS TotalNetAmount,
    j.""Id"" GroupId,
    j.""Name"" GroupName,
    j.""GroupType"" GroupType,
    k.""Name"" ParentGroupName,
    h.""PaymentId"" ,
    h.""DisplayName"" DisplayName,
    h.""Name"" MopName,
    a.""TransactionType"" 
FROM  
     (select * from ""PosTrxes""
   where ""TargetBusinessUnitId"" = '{0}'  
   and ""CreatedDateTime"" >='{1}'  
   and ""CreatedDateTime"" <= '{2}'  
   AND ""TransactionStatus"" = 1 ) a  
LEFT JOIN ""PosTrxLineItems"" b ON a.""Id"" = b.""PosTrxId""  
LEFT JOIN ""PosTrxMops"" c ON c.""PosTrxId"" = a.""Id""  
LEFT JOIN ""PosMops"" d ON c.""PosMopId"" = d.""Id""  
left join ""PosItems"" e on b.""PosItemId""  = e.""Id"" 
left join ""PosItemAndGroup_M2M"" f on e.""Id""  = f.""PosItem_Id"" 
left join ""PosItemGroups"" j on f.""PosItemGroup_Id""  = j.""Id"" 
left join ""PosItemGroups"" k on j.""ParentGroupId""  = k.""Id"" 
left join ""Mops"" h on d.""PaymentId"" = h.""PaymentId"" 
WHERE  
  c.""ResultMessage"" = 'OK'
    AND b.""Voided"" = FALSE  
    and d.""PaymentId"" is not null
GROUP BY  
   b.""FuelItemSoldOnPumpNozzleId"",
   b.""FuelItemSoldOnPumpNozzleId"",
     j.""Id"" ,
    j.""Name"" ,
    j.""GroupType"" ,
    k.""Name"" ,
    h.""PaymentId"" ,
    h.""DisplayName"" ,
    h.""Name"",
     a.""TransactionType"" 
", buid, firstDayOfLastMonth, lastDayOfLastMonth.ToString("yyyy-MM-dd 23:59:59"));
            var salesFinanceInfo = _unitOfWork.DbContext.pumpTransactionSummaries
                             .FromSqlRaw(sql)
                             .ToList();//获取销售升数、回灌、自用、应收款、实收款

            List<NozzleTransaction> list = new List<NozzleTransaction>();
            foreach (var n in salesFinanceInfo)
            {
                NozzleTransaction nozzle = new NozzleTransaction();
                nozzle.FuelItemSoldOnPumpNozzleId = n.FuelItemSoldOnPumpNozzleId;

                nozzle.ItemName = fuelDetails.FirstOrDefault(_ => _.FuelItemSoldOnPumpNozzleId == n.FuelItemSoldOnPumpNozzleId)?.ItemName ?? "";

                nozzle.ItemPrice = fuelDetails.FirstOrDefault(_ => _.FuelItemSoldOnPumpNozzleId == n.FuelItemSoldOnPumpNozzleId)?.ItemPrice ?? 00.0M;
                nozzle.VolumeOnPos = n.VolumeOnPos;//销售升数
                nozzle.PumpTestVolume = n.PumpTestVolume;//回灌
                nozzle.SelfUseVolume = n.SelfUseVolume;//自用
                nozzle.TotalPaid = n.TotalPaid;//应收款
                nozzle.TotalNetAmount = n.TotalNetAmount;//实收款
                nozzle.VolumeOnPumpStart =
                    startEndPumpPair.FirstOrDefault(_ => _.FuelItemSoldOnPumpNozzleId == n.FuelItemSoldOnPumpNozzleId)
                    ?.VolumeOnPumpStart;//起泵数
                nozzle.VolumeOnPumpEnd =
                    startEndPumpPair.FirstOrDefault(_ => _.FuelItemSoldOnPumpNozzleId == n.FuelItemSoldOnPumpNozzleId)
                    ?.VolumeOnPumpEnd;//止泵数
                nozzle.GroupId = n.GroupId;//分组id
                nozzle.GroupName = n.GroupName;//分组名称
                nozzle.GroupType = n.GroupType;//分组类型
                nozzle.ParentGroupName = n.ParentGroupName;//父分组
                nozzle.PaymentId = n.PaymentId;//支付类型id
                nozzle.DisplayName = n.DisplayName;//支付类型名称
                nozzle.MopName = n.MopName;//支付类型名称
                nozzle.TransactionType = n.TransactionType;//订单类型
                list.Add(nozzle);
            }
            if (list.Count > 0)
            {
                string json = JsonConvert.SerializeObject(list);
                string key = "NozzleData" + ":" + buid;
                redisExample.AddToHash(key, datetime, json);
            }
            return null;
        }
        public async Task<ExecuteResult> fixFuelItemTag2Async(string startTime,string endTime,string buid)
        {
            Guid TargetBusinessUnitId = Guid.Parse(buid);
            DateTime dateTime =  DateTime.Parse(startTime);
            DateTime enddateTime = DateTime.Parse(endTime);
            //            var query = _unitOfWork.GetRepository<PosTrxes>().FromSql(string.Format(@"select a.* FROM public.""PosTrxes"" a 
            //left join public.""PosTrxLineItems"" b on a.""Id""  = b.""PosTrxId"" 
            //left join public.""PosTrxMops"" c on a.""Id""  = c.""PosTrxId"" 
            //left join public.""PosMops"" d on c.""PosMopId""  = d.""Id"" 
            //where b.""FuelItemTag""  is null
            //and b.""Id"" = '1aa78939-7aa7-42b3-92fc-e2f8e934ddbd'
            //and a.""CreatedDateTime""  >=  '{0}' ", "2023-03-01")).Include(a => a.PosTrxLineItems)
            //.Include(a => a.PosTrxMops)
            //.ThenInclude(a => a.PosMop).ToList();
            //var query = _unitOfWork.GetRepository<PosTrxes>().GetAll().Include(a => a.PosTrxLineItems)
            //    .Include.ToList();
            //Guid guid_ = new Guid("000147d3-dd35-4c8a-a354-42be800ca57d");
            var query = _unitOfWork.GetRepository<PosTrxes>().GetAll().Include(a => a.PosTrxLineItems)
.Include(a => a.PosTrxMops)
.ThenInclude(a => a.PosMop).Where(a => a.CreatedDateTime >= dateTime && a.CreatedDateTime <= enddateTime
&& a.TargetBusinessUnitId == TargetBusinessUnitId
&& a.PosTrxLineItems.Any(b => b.FuelItemTag == null) //&& a.Id == guid_
 ).ToList();

            //            var query = _unitOfWork.GetRepository<PosTrxes>().GetAll().Include(a => a.PosTrxLineItems)
            //.Include(a => a.PosTrxMops)
            //.ThenInclude(a => a.PosMop).Where(a => a.PosTrxLineItems.Any(b => b.FuelItemTag == null) && a.Id == guid_
            //).ToList();

            foreach (var item in query)
            {
                var posTrxLineItems = item.PosTrxLineItems;
                var Mop = item.PosTrxMops?.FirstOrDefault()?.PosMop;
                var PosTrxMops = item.PosTrxMops;
                var fuelItemsFromRealPump = posTrxLineItems.Where(i => !string.IsNullOrEmpty(i.FuelItemFdcTransactionSeqNo) &&
                                                          i.FuelItemSoldOnPumpNozzleId != null);
                if (Mop == null)
                    continue;

                //string mopjson = SerializeObject(Mop);
                //MS.WebCore.Logger.Logger.WriteLog("Mop：" + JsonConvert.SerializeObject(mopjson));
                if (fuelItemsFromRealPump.Any())
                {
                    MyTransaction SequenceNo;
                    foreach (var trxitem in fuelItemsFromRealPump)
                    {
                        if (!string.IsNullOrEmpty(trxitem.FuelItemTag) || trxitem.FuelItemFdcTransactionSeqNo == null)
                            continue;
                        MS.WebCore.Logger.Logger.WriteLog("FuelItemFdcTransactionSeqNo2：" + trxitem.FuelItemFdcTransactionSeqNo);
                        SequenceNo = findCardInfoByBusinessUnitInfoAndSequenceNo_2(item.TargetBusinessUnitId.ToString(), trxitem.FuelItemFdcTransactionSeqNo, trxitem.FuelItemSoldOnPumpNozzleId.ToString(), item.CreatedDateTime, Mop.PaymentId == 4);
                        if (SequenceNo != null)
                        {
                            MS.WebCore.Logger.Logger.WriteLog("FuelItemFdcTransactionSeqNo2：" + trxitem.FuelItemFdcTransactionSeqNo + "card_no：" + SequenceNo.card_no);
                            var carno = SequenceNo.card_no;
                            if (carno.Length <= 8)//感应卡
                            {
                                if (SequenceNo.card_type != 1)
                                    continue;
                            }
                            else //CPU卡
                            {
                                if (SequenceNo.card_type != 4)
                                    continue;
                            }

                            var AspNetUsers = _AuthContext.GetRepository<AspNetUsers>().GetAll()
                            .Include(a => a.UserSubIdentities)
                            .Where(a => a.UserSubIdentities.Any(b => b.Identity == SequenceNo.card_no && b.Type == FuelItemTagDto.SmartFuelIcCardSystemName)).FirstOrDefault();
                            string AspNetUsersjson = SerializeObject(AspNetUsers);
                            MS.WebCore.Logger.Logger.WriteLog("FuelItemFdcTransactionSeqNo2：" + trxitem.FuelItemFdcTransactionSeqNo + "AspNetUsers: " + AspNetUsersjson);
                            if (AspNetUsers == null)
                            {
                                continue;
                            }
                            var tagInfo = new FuelItemTagDto()
                            {
                                TagLinkedServiceIdentityUser = new UserInfos
                                {
                                    Id = Guid.Parse(AspNetUsers.Id),
                                    UserName = AspNetUsers.UserName,
                                    Alias =  AspNetUsers.Alias,
                                    RestrictedInBusinessUnits = { }
                                },
                                TagSystemName = FuelItemTagDto.SmartFuelIcCardSystemName,
                                TagId = SequenceNo.card_no,
                                TagType = FuelItemTagDto.SmartFuelIcCardTagName,
                                Detail = "This Fuel Item from real pump is authorized by an attandant's smartFuel IC card"
                            };
                            trxitem.FuelItemTag = JsonConvert.SerializeObject(tagInfo);
                            _unitOfWork.GetRepository<PosTrxLineItems>().Update(trxitem);
                            await _unitOfWork.SaveChangesAsync();//提交
                            MS.WebCore.Logger.Logger.WriteLog("FuelItemFdcTransactionSeqNo2：" + trxitem.FuelItemFdcTransactionSeqNo + "FuelItemTag：" + trxitem.Id.ToString());
                        }
                        else if (Mop.PaymentId == 4)
                        {
                            var entity = _unitOfWork.GetRepository<NozzleAssignments>().GetAll()
                                .Where(a => a.NozzleId == trxitem.FuelItemSoldOnPumpNozzleId && a.TargetBusinessUnitId == item.TargetBusinessUnitId)
                                .FirstOrDefault();
                            if (entity != null)
                            {
                                var AspNetUsersV2 = _AuthContext.GetRepository<AspNetUsers>().GetAll()
                                .Include(a => a.UserSubIdentities)
                                .Include(a => a.IdentityUserBusiUnitM2m)
                                .Where(a => a.IdentityUserBusiUnitM2m
                                .Any(b => b.BusinessUnitId == item.TargetBusinessUnitId)
                                && a.UserName == entity.OperatorName)
                                .FirstOrDefault();
                                if (AspNetUsersV2 != null)
                                {
                                    var tagInfo = new FuelItemTagDto()
                                    {
                                        TagLinkedServiceIdentityUser = new UserInfos
                                        {
                                            Id = Guid.Parse(AspNetUsersV2.Id),
                                            UserName = AspNetUsersV2.UserName,
                                            Alias = BuidUploadOrNot(item.TargetBusinessUnitId.ToString()) ? "" : AspNetUsersV2.Alias
                                        },
                                        TagSystemName = FuelItemTagDto.SmartFuelIcCardSystemName,
                                        TagId = AspNetUsersV2.UserSubIdentities?.FirstOrDefault().Identity,
                                        TagType = FuelItemTagDto.SmartFuelIcCardTagName,
                                        Detail = "This Fuel Item from real pump is authorized by IC card"
                                    };
                                    trxitem.FuelItemTag = JsonConvert.SerializeObject(tagInfo);
                                    _unitOfWork.GetRepository<PosTrxLineItems>().Update(trxitem);
                                    await _unitOfWork.SaveChangesAsync();//提交
                                    MS.WebCore.Logger.Logger.WriteLog("FuelItemTag：" + trxitem.Id.ToString());
                                }
                            }
                        }
                    }

                }
            }
            return null;
        }
    }
}
