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

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

namespace PRSDesktop.Integrations.Logikal
{


    
    public class LogikalStyle : LogikalItem, ILogikalStyle
    {

        public String Code { get; set; }

        public String Description { get; set; }
        
        public double Cost { get; set; }
        
        public AwgStyleType StyleType { get; set; }

        public override  void ValidateQuery(string sql, List<string> errors)
        {
            ValidateField(sql, nameof(Code), errors);
            ValidateField(sql, nameof(Description), errors);
            ValidateField(sql, nameof(Cost), errors);
            ValidateField(sql, nameof(StyleType), errors);
        }

        public static String SQL = $@"select distinct 
    
    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(Code)}],
	
    case c.[ColorTypeSupplier]
        when -1 then 
            case coalesce(c.[PowderID],'')
                when '' then c.[ColorName]
                else case coalesce(c.[Thickness],'')
                    when '' then printf('%s (%s)',c.[ColorName],c.[PowderID])
                    else printf('%s %s (%s)',c.[ColorName],c.[Thickness],c.[PowderID])
                end
            end	
        else coalesce(c.[ColorDescription],c.[ColorName])
    end as [{nameof(Description)}],
        
    case c.[ColorTypeSupplier] 
        when -1 then 
            case c.[ColorType]
                when 0 then {(int)AwgStyleType.Powdercoated}
                when 1 then {(int)AwgStyleType.Varnished}
                when 2 then {(int)AwgStyleType.Taped}
                when 3 then {(int)AwgStyleType.Anodised}
                else {(int)AwgStyleType.Mill}
            end
        else {(int)AwgStyleType.Intrinsic}
    end as [{nameof(StyleType)}],

	coalesce(b.[PriceCoating],0.0) as [{nameof(Cost)}]

from 
    Colors c
left outer join
    profilebars b on c.[ColorId] = b.[LK_ColorId]
where
    coalesce(c.[ColorName], '') <> ''";

    }
}

//case b.[ColorTypeInternal] WHEN 7 THEN 1 WHEN 335 THEN 2 WHEN 191 THEN 4 WHEN 141 THEN 5 ELSE 0 END AS [{nameof(StyleType)}],

/*
 * Further investigation into ProfileBars.ColorTypeInternal turns up an “18” (powdercoat?) and “6” (unsure)

I also found that I could go to Logikal /  User Database / Colors and export the Color Database to Excel. It doesn’t give us the numbers, but when I want to try and add a color, it gave me four options – Powdercoated, Varnished, Taped or Anodized.

So somehow, I’m expecting to have a maximum of 4 (or 5) unique numbers to identify the color type.  If -1 is unspecified, then

Powdercoated appears to be 7
Anodized should be either 11 (Colors.ColorTypeSupplier) or 335 (ProfileBars.ColorTypeInternal)
Varnished (Stainless?) might be 12 (Colors.ColorTypeSupplier) or 191 (ProfileBars.ColorTypeInternal)
Taped (PVC?) might be 0 (Colors.ColorTypeSupplier) or 141 (ProfileBars.ColorTypeInternal)

 */


/*
 * select distinct 
    c.[ColorName] as [{nameof(Code)}], 
    c.[ColorName] as [{nameof(Description)}] ,
    coalesce(b.[PriceCoating],0.0) as [{nameof(Cost)}],
	c.[ColorTypeSupplier],
	coalesce(b.ColorTypeInternal,0), 
    case c.[ColorTypeSupplier] WHEN 7 THEN 1 WHEN 11 THEN 3 WHEN 12 THEN 4 WHEN 0 THEN 5 ELSE 0 END AS [TreatmentType],
	case b.[ColorTypeInternal] WHEN 7 THEN 1 WHEN 335 THEN 2 WHEN 191 THEN 4 WHEN 141 THEN 5 ELSE 0 END AS [TreatmentType2]
from 
    Colors c
left outer join
        profilebars b on c.[ColorId] = b.[LK_ColorId]
*/