prs/prs.desktop/Integrations/V6/Classes/V6Profile.cs

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

namespace PRSDesktop.Integations.V6
{
    public class V6Profile : V6BOMItem, IV6Profile
    {
        public double Length { 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);
        }

        public static String SQL = 
            "SELECT \n" +
            $"  e.extn_code as {nameof(Code)},  \n" +
            $"  e.descr as {nameof(Description)}, \n" +
            $"  f.fincol_code as {nameof(Finish)}, \n" +
            $"  round(coalesce(bb.bar_length, 0.0) * 0.0254, 2) as {nameof(Length)}, \n" +
            $"  sum(bp.piece_count) as {nameof(Quantity)}, \n" +
            $"  round(bb.cost * coalesce(bb.bar_length, 0.0),2) as {nameof(Cost)} \n" +
            "FROM \n" +
            "  bom_piece bp \n" +
            "left outer join \n" +
            "  extn e on bp.extn_id = e.extn_id \n" +
            "left outer join \n" +
            "  quote_item qi on qi.quote_item_id = bp.quote_item_id \n" +
            "left outer join  \n" +
            "  quote q on q.quote_id = qi.quote_id \n" +
            "left outer join \n" +
            " (Select bcp.bom_piece_id, MAX(bcp.bom_cutplan_id) as bom_cutplan_id from bom_cutplan_piece bcp group by bcp.bom_piece_id) bcp on bcp.bom_piece_id = bp.bom_piece_id \n" +
            "left outer join \n" +
            "  bom_bar bb on bb.bom_cutplan_id = bcp.bom_cutplan_id  \n" +
            "left outer join \n" +
            "  fincol f on f.fincol_lib_id = bp.fincol_lib_id and f.fincol_id = bp.fincol_id \n" +
            "where \n" +
            "  1 = 1 \n" +
            "and \n" +
            "  bb.bar_length is  not null \n" +
            "group by \n" +
            "  e.extn_code, \n" +
            "  e.descr, \n" +
            "  f.fincol_code, \n" +
            "  bb.bar_length, \n" +
            "  bb.cost";
    }
}