prs/prs.desktop/Integrations/Logikal/Classes/LogikalProfile.cs

using InABox.Core;
using InABox.Integration.Logikal;
using System;
using System.Collections.Generic;

namespace PRSDesktop.Integrations.Logikal
{
    public class LogikalProfile : LogikalBOMItem, ILogikalProfile
    {
        public double Length { get; set; }
        public double PaintPerimeter { get; set; }
        public double AnodizePerimeter { get; set; }
        public double MillCost { get; set; }
        public string Group { get; set; }
        public string Supplier { get; set; }
        public string? Finish { get; set; }

        public override void ValidateQuery(string sql, List<string> errors)
        {
            base.ValidateQuery(sql, errors);
            ValidateField(sql, nameof(Length), errors);
            ValidateField(sql, nameof(Finish), errors);
            ValidateField(sql, nameof(Group), errors);
            ValidateField(sql, nameof(Supplier), errors);
            ValidateField(sql, nameof(PaintPerimeter), errors);
            ValidateField(sql, nameof(AnodizePerimeter), errors);
            ValidateField(sql, nameof(MillCost), errors);
        }

        public static String DesignSQL = 
$@"select
    p.[ArticleCode_BaseNumber] as [{nameof(Code)}],
    p.[description] as [{nameof(Description)}],
    upper(case c.[ColorTypeSupplier]
        when -1 then 
            case coalesce(c.[PowderID],'')
                when '' then c.[ColorName]
                else case coalesce(c.[Thickness],'')
                    when '' then c.[PowderID]
                    else printf('%s-%s',c.[PowderID],c.[Thickness])
                end
			end	
        else c.[ColorName]
    end) as [{nameof(Finish)}],
    p.[length_output] as [{nameof(Length)}],
    p.Perimeter as [{nameof(AnodizePerimeter)}],
	p.[PerimeterBoundaryBox] as [{nameof(PaintPerimeter)}],
    p.[Amount] as [{nameof(Quantity)}],
    case p.[ArticleType] when 1 then 'Profiles' when 2 then 'Profiles' when 5 then 'Gasket' when 7 then 'Gasket' when 8 then 'Components' when 16 then 'Components' when 18 then 'Components' when 30 then 'Fills' else '???' end as [{nameof(Group)}],
    p.[Price] as [{nameof(Cost)}],
    (p.[PriceGross]-p.[PriceCoating]) as [{nameof(MillCost)}],
    coalesce(s.[CustomerNo],s.[ActiveTitle]) as [{nameof(Supplier)}]
from
    profiles p 
left outer join colors c2
    on trim(c2.[colorname]) = trim(Substr(p.[articlecode_ordercode],Instr(p.[articlecode_ordercode], '.') + 1))
left outer join colors c
    on c.[colorid] = coalesce(c2.[ColorID],p.[lk_ColorID])     
join
    insertions i on p.[insertionid] = i.[insertionid]
join
    elevations e on i.[elevationid] = e.[elevationid]
left outer join
    suppliers s on p.[LK_SupplierID] = s.[SupplierID]";

        
        
        public static String BillOfMaterialsSQL = 
$@"select
    sum(p.[Amount]) as [{nameof(Quantity)}],
    case p.[ArticleType] when 2 then 'Profiles' when 5 then 'Gasket' when 8 then 'Components' when 30 then 'Fills' else '???' end as [{nameof(Group)}],
    coalesce(s.[CustomerNo],s.[ActiveTitle]) as [{nameof(Supplier)}],
    p.[ArticleCode_BaseNumber] as [{nameof(Code)}],
    p.[description] as [{nameof(Description)}],
	p.Perimeter as [{nameof(AnodizePerimeter)}],
	p.[PerimeterBoundaryBox] as [{nameof(PaintPerimeter)}],
    upper(case c.[ColorTypeSupplier]
        when -1 then 
            case coalesce(c.[PowderID],'')
                when '' then c.[ColorName]
                else case coalesce(c.[Thickness],'')
                    when '' then c.[PowderID]
                    else printf('%s-%s',c.[PowderID],c.[Thickness])
                end
			end	
        else c.[ColorName]
    end) as [{nameof(Finish)}],
    p.[Length] as [{nameof(Length)}],
    (p.[PriceGross]-p.[PriceCoating])*p.[Length] as [{nameof(MillCost)}],
    p.[PriceGross]*p.[Length] as [{nameof(Cost)}]
from
    profilebars p 
left outer join
    colors c on p.[lk_colorid] = c.[colorid]      
left outer join
    suppliers s on p.[SupplierID] = s.[SupplierID]
group by
	p.[ArticleCode],
	p.[Description],
	p.[Length],
	c.[ColorName],
	p.[ArticleType],
    coalesce(s.[CustomerNo],s.[ActiveTitle]),
	(p.[PriceGross]-p.[PriceCoating])*p.[Length],
	p.[Perimeter],
	p.[PerimeterBoundaryBox],
	p.[PriceGross]*p.[Length]
     ";

    }
    
    /* BillOf Materials
select
    sum(p.[Amount]) as [{nameof(Quantity)}],
    d.[DiscountGroup] as [{nameof(Group)}],
    coalesce(s.[CustomerNo],s.[ActiveTitle]) as [{nameof(Supplier)}],
    p.[ArticleCode] as [{nameof(Code)}],
    p.[description] as [{nameof(Description)}],
	p.Perimeter as [{nameof(AnodizePerimeter)}],
	p.[PerimeterBoundaryBox] as [{nameof(PaintPerimeter)}],
    c.[ColorName] as [{nameof(Finish)}],
    p.[Length] as [{nameof(Length)}],
    (p.[PriceGross]-p.[PriceCoating])*p.[Length] as [{nameof(MillCost)}],
    p.[PriceGross]*p.[Length] as [{nameof(Cost)}]
from
    profilebars p 
left outer join
    colors c on p.[lk_colorid] = c.[colorid]      
join 
    estimationdiscounts d on p.[LK_DiscountId] = d.[EstimationDiscountID]
left outer join
    suppliers s on p.[SupplierID] = s.[SupplierID]
group by
	p.[ArticleCode],
	p.[Description],
	p.[Length],
	c.[ColorName],
	d.[DiscountGroup],
    coalesce(s.[CustomerNo],s.[ActiveTitle]),
	(p.[PriceGross]-p.[PriceCoating])*p.[Length],
	p.[Perimeter],
	p.[PerimeterBoundaryBox],
	p.[PriceGross]*p.[Length]
     */
    
    /* Designs
     
     */


}