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

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

namespace PRSDesktop.Integrations.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 BillOfMaterialsSQL = $@"
select 
  e.EXTN_CODE as {nameof(Code)}, 
  e.DESCR as {nameof(Description)}, 
  f.FINCOL_CODE as {nameof(Finish)}, 
  bb.bar_length as {nameof(Length)}, 
  bb.bars as {nameof(Quantity)}, 
  bb.cost as {nameof(Cost)} 
from 
  bom_cutplan bc 
  join extn e on bc.EXTN_LIB_ID = e.EXTN_LIB_ID 
  and bc.EXTN_ID = e.EXTN_ID 
  join fincol f on bc.FINCOL_LIB_ID = f.FINCOL_LIB_ID 
  and bc.FINCOL_ID = f.FINCOL_ID 
  join (
    select 
      BOM_CUTPLAN_ID, 
      BAR_LENGTH, 
      COST * bar_length as cost, 
      count(*) as bars 
    from 
      bom_bar 
    group by 
      BOM_CUTPLAN_ID, 
      bar_length, 
      cost
  ) bb on bb.BOM_CUTPLAN_ID = bc.BOM_CUTPLAN_ID 
  join (
    select 
      distinct bcp.BOM_CUTPLAN_ID, 
      q.QUOTE_ID, 
      q.quote_num, 
      q.QUOTE_NUM_SUFF, 
      q.quote_vers 
    from 
      bom_cutplan_piece bcp 
      join bom_piece bp on bcp.BOM_PIECE_ID = bp.BOM_PIECE_ID 
      join quote_item qi on bp.QUOTE_ITEM_ID = qi.QUOTE_ITEM_ID 
      join quote q on qi.QUOTE_ID = q.QUOTE_ID 
      and qi.quote_vers_start <= q.quote_vers and qi.quote_vers_stop >= q.quote_vers
  ) q on q.BOM_CUTPLAN_ID = bc.BOM_CUTPLAN_ID 
where 1=1 

UNION

SELECT 
    e.extn_code as {nameof(Code)}, 
    e.descr as {nameof(Description)}, 
    f.FINCOL_CODE as {nameof(Finish)}, 
    bp.piece_length as {nameof(Length)}, 
    sum(bp.piece_count) as {nameof(Quantity)}, 
    bp.COST as {nameof(Cost)}
FROM bom_piece bp 
join fincol f on bp.FINCOL_LIB_ID = f.FINCOL_LIB_ID and bp.FINCOL_ID = f.FINCOL_ID 
join extn e on e.extn_lib_id = bp.EXTN_LIB_ID and e.extn_id = bp.extn_id
join quote_item qi on bp.QUOTE_ITEM_ID = qi.QUOTE_ITEM_ID
join quote q on qi.quote_id = q.quote_id 
where 1=1
and qi.quote_vers_start <= q.quote_vers and qi.quote_vers_stop >= q.quote_vers
and bp.PIECE_TAG='C'
group by e.extn_code, e.descr, f.FINCOL_CODE, bp.PIECE_LENGTH, bp.cost

";
        
        public static String DesignSQL = $@"
select
    e.extn_code as {nameof(Code)},
    e.descr as {nameof(Description)},
    fc.fincol_code as {nameof(Finish)},
    round(bp.piece_length,2,1) as {nameof(Length)},
    e.cost_length * round(bp.piece_length,2,1) as {nameof(Cost)},
    sum(bp.PIECE_COUNT) as {nameof(Quantity)}
from
    bom_piece bp
        join
    quote_item qi on bp.quote_item_id = qi.quote_item_id
        left outer join
    fincol fc on bp.fincol_lib_id = fc.fincol_lib_id and bp.fincol_id = fc.fincol_id
        join
    extn e on bp.extn_lib_id = e.extn_lib_id and bp.extn_id = e.extn_id
where
    1=1
group by 
    e.extn_code, 
    e.descr, 
    fc.fincol_code, 
    bp.piece_length, 
    e.cost_length
";
        
        
    }
}