FCC/Applications/Gateway/POS/PosApp/App_PivotReport.cs

using AutoMapper;
using Edge.Core.UniversalApi;
using Gateway.POS.Models;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.DependencyInjection;
using Microsoft.Extensions.Logging;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Gateway.POS
{
    public partial class App
    {

        #region PivotReport

        [UniversalApi]
        public async Task<object> PivotReportGetFuelItemsByFuelProductNameForTimeRange(DateTime start, DateTime end)
        {
            using (var db = new PosAppDbContext())
            {
                var results = await db.FuelItems.Include(f => f.Transaction)
                    .Where(fi => fi.Transaction.ServerSideTimestamp >= start && fi.Transaction.ServerSideTimestamp <= end)
                    .GroupBy(fi => fi.FuelProductName)
                    .Select(g => new { FuelProductName = g.Key, SumQualtity = g.Sum(fis => fis.Qualtity), SumAmount = g.Sum(fis => fis.Amount) })
                    .ToListAsync();
                return results;
            }
        }

        [UniversalApi]
        public async Task<object> PivotReportGetPaymentsByFuelProductNameForTimeRange(DateTime start, DateTime end)
        {
            using (var db = new PosAppDbContext())
            {
                var results = await db.Payments.Include(f => f.Transaction)
                    .Where(fi => fi.Transaction.ServerSideTimestamp >= start && fi.Transaction.ServerSideTimestamp <= end)
                    .GroupBy(fi => fi.Method)
                    .Select(g => new { MethodName = g.Key, SumPaidAmount = g.Sum(fis => fis.PaidAmount), Count = g.Count() })
                    .ToListAsync();
                return results;
            }
        }

        [UniversalApi]
        public async Task<object> PivotReportGetOperatorSalesByOperatorNameForTimeRange(DateTime start, DateTime end)
        {
            using (var db = new PosAppDbContext())
            {
                var results = await db.Transactions.Include(t => t.Operator).Include(t => t.FuelItems).Include(t => t.Payments)
                    .Where(trx => trx.ServerSideTimestamp >= start && trx.ServerSideTimestamp <= end)
                    .GroupBy(trx => new { trx.OperatorId, trx.Operator.Name, Mop = trx.Payments.Select(pa => pa.Method).FirstOrDefault() })
                    .Select(g => new { Operator = g.Key, SumPaidAmount = g.Sum(trxs => trxs.NetAmount), TrxCount = g.Count() })
                    .ToListAsync();


                var ops = results.GroupBy(r => new { r.Operator.OperatorId, r.Operator.Name })
                    .Select(g => new { Operator = new { g.Key.OperatorId, g.Key.Name }, Sales = g.Select(gg => new { PaymentMethodName = gg.Operator.Mop, gg.SumPaidAmount, gg.TrxCount }) });

                //Dictionary<string, object> datas = new Dictionary<string, object>();
                //foreach (var op in ops)
                //{
                //    //if (!datas.ContainsKey(op.Key.OperatorId + "|" + op.Key.Name))
                //    //    datas.Add(op.Key.OperatorId + "|" + op.Key.Name);
                //    foreach (var r in results)
                //    {
                //        if (op.Key.OperatorId == r.Operator.OperatorId && op.Key.Name == r.Operator.Name)
                //        {

                //        }
                //    }
                //}
                return ops;
            }
        }

        [UniversalApi]
        public async Task<object> PivotReportGetNozzlesBySiteLevelNozzleIdForTimeRange(DateTime start, DateTime end)
        {
            using (var db = new PosAppDbContext())
            {
                var results = await db.FuelItems.Include(f => f.Transaction)
                    .Where(fi => fi.Transaction.ServerSideTimestamp >= start && fi.Transaction.ServerSideTimestamp <= end)
                    .GroupBy(fi => fi.SiteLevelNozzleId)
                    .Select(g => new
                    {
                        SiteLevelNozzleId = g.Key,
                        SumQualtity = g.Sum(fis => fis.Qualtity),
                        SumAmount = g.Sum(fis => fis.Amount),
                        MaxTotalVolumeDiff = g.Max(fis => fis.TotalVolume ?? 0) - g.Min(fis => fis.TotalVolume ?? 0),
                        FuelingCount = g.Count()
                    })
                    .ToListAsync();
                return results;
            }
        }

        #endregion
       
    }
}