inabox/InABox.Core/Objects/AutoEntity/AutoEntitySummaryGenerator.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Linq.Expressions;
using System.Runtime.CompilerServices;

namespace InABox.Core
{
    /*
     * Heres what we want to model - a view that performs in-table summaries.  Currently we can define out-of-table lookups that sumamrise the values in another table,
     * but these all seem to generate multi-table queeries, which lose the performance benefits we're looking for.  The below "target" query is a single-table query
     *
	    select SUMMARYTABLE.* from 
			(SELECT 
			  [location.id],  
			  [product.id],  
			  [style.id],  
			  [job.id],  
			  [dimensions.unit.id], 
			  [dimensions.Quantity],  
			  [dimensions.length],  
			  [dimensions.width],  
			  [dimensions.Height],  
			  [dimensions.weight],  
			  [dimensions.value],  
			  [dimensions.unitsize],
			  sum(coalesce([RECEIVED],0.0)-coalesce([ISSUED],0.0)) AS Total,
			  sum(case when [JOBREQUISITIONITEM.ID] is null THEN coalesce([RECEIVED],0.0)-coalesce([ISSUED],0.0) ELSE 0.0 end) AS Available,
			  sum(case when [JOBREQUISITIONITEM.ID] is not null THEN coalesce([RECEIVED],0.0)-coalesce([ISSUED],0.0) ELSE 0.0 end) AS Allocated,
			  sum((coalesce([RECEIVED],0.0)-coalesce([ISSUED],0.0)) * coalesce(COST,0.0)) AS TotalCost,
			  sum((coalesce([RECEIVED],0.0)-coalesce([ISSUED],0.0)) * coalesce(COST,0.0)) / sum(coalesce([RECEIVED],0.0)-coalesce([ISSUED],0.0)) AS AverageCost
			FROM 
			  STOCKMOVEMENT
			GROUP BY
			  [location.id],  
			  [product.id],  
			  [style.id],  
			  [job.id],  
			  [dimensions.unit.id], 
			  [dimensions.Quantity],  
			  [dimensions.length],  
			  [dimensions.width],  
			  [dimensions.Height],  
			  [dimensions.weight],  
			  [dimensions.value],  
			  [dimensions.unitsize]
			  ) as SUMMARYTABLE
		WHERE
			SUMMARYTABLE.[PRODUCT.ID]='fc159c37-6a59-410b-b15c-8baed75349aa'

     */

    public enum AutoEntitySummaryAggregate
    {
	    Sum,
	    Average,
	    Count,
	    Minimum,
	    Maximum
    }
    
    public interface IAutoEntitySummaryAggregateColumn : IColumn
    {
	    AutoEntitySummaryAggregate Aggregate { get; }
	    IComplexFormulaGenerator Definition { get; }
    }
    
    public class AutoEntitySummaryAggregateColumn<TInterface,TType> : Column<TInterface>, IAutoEntitySummaryAggregateColumn
    {
	    
	    public AutoEntitySummaryAggregate Aggregate { get; }
	    
	    public ComplexFormulaGenerator<TType,object?> Definition { get; }
	    IComplexFormulaGenerator IAutoEntitySummaryAggregateColumn.Definition => Definition;
	    
	    public AutoEntitySummaryAggregateColumn(Expression<Func<TInterface, object?>> expression, AutoEntitySummaryAggregate aggregate, ComplexFormulaGenerator<TType,object?> definition) : base(expression)
	    {
		    Aggregate = aggregate;
		    Definition = definition;
	    }
	    
    }

    public interface IAutoEntitySummaryIDColumn : IColumn
    {
	    IColumn Source { get; }
    }
    
    public class AutoEntitySummaryIDColumn<TInterface,TType> : Column<TInterface>, IAutoEntitySummaryIDColumn
    {
	    
	    public Column<TType> Source { get; }
	    
	    IColumn IAutoEntitySummaryIDColumn.Source => Source;
	    
	    public AutoEntitySummaryIDColumn(Expression<Func<TInterface, object?>> expression, Expression<Func<TType, object?>> source) : base(expression)
	    {
		    Source = new Column<TType>(source);
	    }
	    
    }

    public interface IAutoEntitySummaryGenerator : IAutoEntityGenerator
    {
		new IAutoEntitySummaryIDColumn[] IDColumns();
	    IAutoEntitySummaryAggregateColumn[] AggregateColumns();
	    IFilter? HavingFilter { get; }
	    Type SourceType { get; }
    }

    
    public abstract class AutoEntitySummaryGenerator<TInterface,TType> : IAutoEntitySummaryGenerator
    {
	    public AutoEntitySummaryGenerator()
	    {
		    Configure();
	    }
	    
	    public bool Distinct => false;
	    
	    public Type Definition => typeof(TInterface);
	    public Type SourceType => typeof(TType);

	    private List<IColumn> _idColumns = new List<IColumn>();
	    private List<IAutoEntitySummaryAggregateColumn> _aggregateColumns = new List<IAutoEntitySummaryAggregateColumn>();
	    private Filter<TInterface>? _filter = null;
	    
	    public abstract void Configure();
	    
	    protected void GroupBy(Expression<Func<TInterface,object?>> column, Expression<Func<TType,object?>> source) 
		    => _idColumns.Add(new AutoEntitySummaryIDColumn<TInterface,TType>(column,source));
	    
	    protected void Aggregate(Expression<Func<TInterface,object?>> column,AutoEntitySummaryAggregate aggregate, ComplexFormulaGenerator<TType,object?> formula) 
		    => _aggregateColumns.Add(new AutoEntitySummaryAggregateColumn<TInterface,TType>(column,aggregate, formula));

	    protected void Having(Filter<TInterface> filter)
		    => _filter = filter;
	    
	    public IAutoEntitySummaryIDColumn[] IDColumns() => _idColumns.OfType<IAutoEntitySummaryIDColumn>().ToArray();
	    public IAutoEntitySummaryAggregateColumn[] AggregateColumns() => _aggregateColumns.ToArray();
	    public IFilter? HavingFilter => _filter;
	    
	    IColumn[] IAutoEntityGenerator.IDColumns => _idColumns.ToArray();

	    
    }


    
}