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 string Supplier { get; set; }
        public string Finish { get; set; }
        public double PackSize { get; set; }

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

        public static String SQL = 
$@"select
    a.[ArticleCode_BaseNumber] as [{nameof(Code)}], 
    a.[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)}],
    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]
left outer join colors c2
    on trim(c2.[colorname]) = trim(Substr(a.[articlecode_ordercode],Instr(a.[articlecode_ordercode], '.') + 1))
left outer join colors c
    on c.[colorid] = coalesce(c2.[ColorID],a.[lk_ColorID])
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])     
     */

}