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

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

namespace PRSDesktop.Integrations.Logikal
{
    public class LogikalComponent : LogikalBOMItem, ILogikalComponent
    {
        public string Group { get; set; }
        public double PackSize { get; set; }
        public string Supplier { get; set; }

        public override void ValidateQuery(string sql, List<string> errors)
        {
            base.ValidateQuery(sql, errors);
            ValidateField(sql, nameof(PackSize), errors);
            ValidateField(sql, nameof(Group), errors);
            ValidateField(sql, nameof(Supplier), errors);
        }

        public static String SQL = 
$@"select
    a.[ArticleCode_BaseNumber] as [{nameof(Code)}], 
    a.[description] as [{nameof(Description)}], 
    a.[pusize] as [{nameof(PackSize)}], 
    case a.[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)}],
    coalesce(s.[CustomerNo],s.[ActiveTitle]) as [{nameof(Supplier)}],
    sum(a.[units]/a.[pusize]) as [{nameof(Quantity)}], 
    a.[Price]*a.[pusize] as [{nameof(Cost)}]
from
    articles a
join
    insertions i on a.[insertionid] = i.[insertionid]
join
    elevations e on i.[elevationid] = e.[elevationid]
left outer join
    suppliers s on a.[LK_SupplierID] = s.[SupplierID]
where
    a.[ArticleType] not in (5,7)
group by 
	a.[ArticleCode_BaseNumber],
	a.[description],
	a.[Price],
    a.[ArticleType],
    coalesce(s.[CustomerNo],s.[ActiveTitle])";
        
    }
    
    /*
select
    a.[ArticleCode_BaseNumber] as [{nameof(Code)}], 
    a.[description] as [{nameof(Description)}], 
    a.[pusize] as [{nameof(PackSize)}], 
    d.[DiscountGroup] as [{nameof(Group)}],
    coalesce(s.[CustomerNo],s.[ActiveTitle]) as [{nameof(Supplier)}],
    sum(a.[units]/a.[pusize]) as [{nameof(Quantity)}], 
    a.[Price]*a.[pusize] as [{nameof(Cost)}]
from
    articles a
join
    insertions i on a.[insertionid] = i.[insertionid]
join
    elevations e on i.[elevationid] = e.[elevationid]
join
    estimationdiscounts d on a.[LK_DiscountID] = d.[EstimationDiscountID]
left outer join
    suppliers s on a.[LK_SupplierID] = s.[SupplierID]
where
    a.[ArticleType] not in (5,7)
group by 
	a.[ArticleCode_BaseNumber],
	a.[description],
	a.[Price],
    d.[DiscountGroup],
    coalesce(s.[CustomerNo],s.[ActiveTitle])     
     */

}