SeaTec Table API Reference

SELECT 'grp-' + CAST(g.GroupCompanyId AS varchar(20))    AS source_id,
       COALESCE(g.ModifiedDate, g.CreatedDate)            AS source_modified_at,
       CAST(0 AS bit)                                     AS source_deleted,
       g.GroupCompanyName                                 AS name,
       'group'                                            AS kind,
       NULL                                               AS parent_source_id,
       NULL                                               AS external_ref,
       NULL                                               AS country,
       CASE WHEN ISNULL(g.Active, 1) = 1 THEN 1 ELSE 0 END AS is_active
FROM   dbo.GroupCompany g

UNION ALL

SELECT 'cst-' + CAST(c.CstId AS varchar(20)),
       COALESCE(c.modDate, c.crDate),
       CAST(0 AS bit),
       c.CstCompany,
       'manager',
       CASE WHEN c.GroupCompanyId IS NULL THEN NULL
            ELSE 'grp-' + CAST(c.GroupCompanyId AS varchar(20)) END,
       c.CustRefNo,
       co.CountryCode,
       CASE WHEN ISNULL(c.Active, 1) = 1 THEN 1 ELSE 0 END
FROM   dbo.Customer c
LEFT   JOIN dbo.CBM_Country co ON co.conId = c.conId

UNION ALL

SELECT 'own-' + CAST(o.OwnerId AS varchar(20)),
       COALESCE(o.modDate, o.crDate),
       CAST(0 AS bit),
       o.OwnerNm, 'owner', NULL, o.VatNo, co.CountryCode, 1
FROM   dbo.CBM_Owner o
LEFT   JOIN dbo.CBM_Country co ON co.conId = o.conId;

SELECT 'flt-' + CAST(f.FleetID AS varchar(20))          AS source_id,
       COALESCE(f.modDate, f.crDate)                      AS source_modified_at,
       CAST(0 AS bit)                                     AS source_deleted,
       'cst-' + CAST(f.fleetCustomer AS varchar(20))      AS organisation_source_id,
       f.fleetName                                        AS name
FROM   dbo.CBM_Fleet f;

SELECT 'ship-' + CAST(s.ShipId AS varchar(20))           AS source_id,
       COALESCE(s.modDate, s.crDate)                      AS source_modified_at,
       CAST(0 AS bit)                                     AS source_deleted,
       CASE WHEN s.ShipFleet IS NULL THEN NULL
            ELSE 'flt-' + CAST(s.ShipFleet AS varchar(20)) END  AS fleet_source_id,
       'cst-' + CAST(s.ShipCompany AS varchar(20))        AS organisation_source_id,
       CASE WHEN s.OwnerId IS NULL THEN NULL
            ELSE 'own-' + CAST(s.OwnerId AS varchar(20)) END    AS owner_source_id,
       s.ShipName                                         AS name,
       s.ShipIMOno                                        AS imo_no,
       t.Typeofshipname                                   AS ship_type,
       TRY_CAST(s.grosstonnage AS decimal(18,2))          AS gross_tonnage,
       TRY_CAST(s.yearofbuild AS int)                     AS year_built,
       CASE WHEN ISNULL(s.IsDisabled, 0) = 1 THEN 0 ELSE 1 END  AS is_active
FROM   dbo.Ship s
LEFT   JOIN dbo.Typeofship t ON t.Typeofshipid = s.typeofshipid;

SELECT 'unit-' + CAST(sm.shipModId AS varchar(20))       AS source_id,
       CASE WHEN sm.LastSampleDate > sm.crDate
            THEN sm.LastSampleDate ELSE sm.crDate END      AS source_modified_at,
       CAST(0 AS bit)                                      AS source_deleted,
       'ship-' + CAST(sm.shipId AS varchar(20))            AS ship_source_id,
       m.macName                                           AS name,
       'lube_oil'                                          AS stream,
       ot.ShortDesc                                        AS application,
       man.manName                                         AS make,
       mo.modNo                                            AS model,
       sm.SrNo                                             AS serial_no,
       og.oilGrade                                         AS oil_grade,
       ob.obrandName                                       AS oil_brand,
       TRY_CAST(sm.OilVol AS decimal(18,2))                AS charge_volume_l,
       sm.Schedule                                         AS sampling_interval_days,
       sm.LastSampleDate                                   AS last_sampled_at,
       CASE WHEN ISNULL(sm.IsCritical, 0) = 1 THEN 1 ELSE 0 END AS is_critical,
       sm.SeaTecMachineryId                                AS external_ref,
       CASE WHEN ISNULL(sm.IsDisabled, 0) = 1 THEN 0 ELSE 1 END AS is_active
FROM   dbo.ShipModel sm
JOIN   dbo.Machinery m    ON m.macId    = sm.macId
LEFT   JOIN dbo.Model mo  ON mo.modId   = sm.modId
LEFT   JOIN dbo.Manufacturer man ON man.manId = mo.manId
LEFT   JOIN dbo.OilType ot ON ot.otypeId = m.otypeId
LEFT   JOIN dbo.OilGrade og ON og.oilId  = sm.OilGradeId
LEFT   JOIN dbo.OilBrand ob ON ob.obrandId = og.obrandId;

SELECT 'st-lo-' + CAST(t.TestTypeId AS varchar(20)) AS source_id,
       GETUTCDATE()                                   AS source_modified_at,
       CAST(0 AS bit)                                 AS source_deleted,
       'lube_oil'                                     AS stream,
       t.TestType                                     AS name,
       NULL                                           AS short_code
FROM   dbo.LOTestType t

UNION ALL

SELECT 'st-fo-' + CAST(o.OilTypeId AS varchar(20)),
       COALESCE(o.ModifiedDate, o.CreatedDate),
       CAST(0 AS bit), 'fuel_oil', o.OilTypeName, NULL
FROM   dbo.FO_OilType o

UNION ALL

SELECT 'st-wa-' + CAST(w.WaterTypeId AS varchar(20)),
       COALESCE(w.ModifiedDate, w.CreatedDate),
       CAST(0 AS bit), 'water', w.WaterTypeName, w.WaterShortCode
FROM   dbo.WA_WaterType w;

SELECT 'p-fo-' + CAST(p.ParameterId AS varchar(20))  AS source_id,
       COALESCE(p.ModifiedDate, p.CreatedDate)        AS source_modified_at,
       CAST(0 AS bit)                                 AS source_deleted,
       'fuel_oil'                                     AS stream,
       p.PName                                        AS name,
       p.PShortName                                   AS short_name,
       'fo.' + p.PCSVName                             AS code,
       p.PUnit                                        AS unit,
       'number'                                       AS data_type,
       me.MethodName                                  AS method,
       b.BlockTitle                                   AS report_block,
       p.SequenceNo                                   AS sequence_no,
       CASE WHEN ISNULL(p.Active, 1) = 1 THEN 1 ELSE 0 END AS is_active
FROM   dbo.FO_Parameters p
LEFT   JOIN dbo.FO_Method me ON me.MethodId = p.PMethod
LEFT   JOIN dbo.FO_Block b   ON b.BlockId   = p.BlockId

UNION ALL

SELECT 'p-wa-' + CAST(p.ParameterId AS varchar(20)),
       COALESCE(p.ModifiedDate, p.CreatedDate),
       CAST(0 AS bit), 'water',
       p.ParamterName, p.ShortName,
       'wa.' + p.CsvHeaderName,
       p.Unit, 'number',
       me.MethodName, b.BlockName, p.SequenceNo,
       CASE WHEN ISNULL(p.IsActive, 1) = 1 THEN 1 ELSE 0 END
FROM   dbo.WA_Parameter p
LEFT   JOIN dbo.WA_Method me ON me.MethodId = p.MethodId
LEFT   JOIN dbo.WA_Block b   ON b.BlockId   = p.BlockId;

-- The same (code, column) pairs the pivot uses. Load once.
-- Unit and short name are picked up from Norms where the name matches;
-- fill the rest in by hand, once.

WITH lo(code, legacy_column, report_block) AS (
  SELECT * FROM (VALUES
    ('appearance', 'Appearance', 'Condition'),
    ('vis40', 'Vis40', 'Condition'),
    ('vis100', 'Vischk00', 'Condition'),
    ('vis_index', 'VisIndex', 'Condition'),
    ('flash_point', 'FlashPoint', 'Condition'),
    ('water_pct', 'Water', 'Condition'),
    ('water_kf_ppm', 'WaterKFppm', 'Condition'),
    ('emulsified_water', 'EmulsifiedWater', 'Condition'),
    ('tbn', 'BaseNumber', 'Condition'),
    ('tan', 'AcidNo', 'Condition'),
    ('soot_insolubles', 'SootInsolubles', 'Condition'),
    ('pentane_insolubles', 'PentaneInsolubles', 'Condition'),
    ('sulphated_ash', 'SulphatedAsh', 'Condition'),
    ('oxidation', 'Oxidation', 'Condition'),
    ('oxidation_abs', 'OxidationAbsolute', 'Condition'),
    ('oxidation_products', 'OxidationProducts', 'Condition'),
    ('nitration_abs', 'NitrationAbsolute', 'Condition'),
    ('sulphation_abs', 'SulphationAbsolute', 'Condition'),
    ('glycol', 'GLYCOL', 'Condition'),
    ('fuel_dilution', 'FUELDILUTION', 'Condition'),
    ('asphaltenes', 'Asphaltenes', 'Condition'),
    ('chlorine', 'Chlorine', 'Condition'),
    ('ph', 'pH', 'Condition'),
    ('specific_gravity', 'SpecificGravity', 'Condition'),
    ('conductivity', 'Conductivity', 'Condition'),
    ('liquid_corrosion', 'LiquidCorrosionConcentration', 'Condition'),
    ('vapour_corrosion', 'VapourPhaseCorrosion', 'Condition'),
    ('rocking_test', 'RockingTest', 'Condition'),
    ('sulphur', 'Sulpher', 'Condition'),
    ('fuel_p', 'FuelP', 'Condition'),
    ('oil_condition_index', 'OILCONDITIONINDEX', 'Condition'),
    ('water_scavenger', 'WaterScavenger', 'Condition'),
    ('calcium', 'Calcium', 'Additive elements'),
    ('magnesium', 'Magnesium', 'Additive elements'),
    ('zinc', 'Zinc', 'Additive elements'),
    ('phosphorus', 'Phosphorus', 'Additive elements'),
    ('barium', 'Barium', 'Additive elements'),
    ('boron', 'Boron', 'Additive elements'),
    ('iron', 'Iron', 'Wear metals'),
    ('copper', 'Copper', 'Wear metals'),
    ('lead', 'Lead', 'Wear metals'),
    ('tin', 'Tin', 'Wear metals'),
    ('chromium', 'Chromium', 'Wear metals'),
    ('aluminium', 'Aluminium', 'Wear metals'),
    ('nickel', 'Nickel', 'Wear metals'),
    ('titanium', 'Titanium', 'Wear metals'),
    ('silver', 'Silver', 'Wear metals'),
    ('manganese', 'Manganese', 'Wear metals'),
    ('vanadium', 'Vanadium', 'Wear metals'),
    ('molybdenum', 'Molybdenum', 'Wear metals'),
    ('silicon', 'Silicon', 'Contaminants'),
    ('sodium', 'Sodium', 'Contaminants'),
    ('lithium', 'Lithium', 'Contaminants'),
    ('pq_index', 'PQIndex', 'Ferrous debris and cleanliness'),
    ('ferrous_debris', 'FERROUSDEBRIS', 'Ferrous debris and cleanliness'),
    ('iso4406', 'ISOCODE', 'Ferrous debris and cleanliness'),
    ('iso_4um', '4um', 'Ferrous debris and cleanliness'),
    ('iso_6um', '6um', 'Ferrous debris and cleanliness'),
    ('iso_14um', '14um', 'Ferrous debris and cleanliness'),
    ('iso4407', 'ISOCODE4407', 'Ferrous debris and cleanliness'),
    ('iso4407_4um', '4um4407', 'Ferrous debris and cleanliness'),
    ('iso4407_6um', '6um4407', 'Ferrous debris and cleanliness'),
    ('iso4407_14um', '14um4407', 'Ferrous debris and cleanliness'),
    ('nas1638', 'NAS1638Class', 'Ferrous debris and cleanliness'),
    ('pc_4um', 'ParticleCount4', 'Ferrous debris and cleanliness'),
    ('pc_5um', 'ParticleCount5', 'Ferrous debris and cleanliness'),
    ('pc_6um', 'ParticleCount6', 'Ferrous debris and cleanliness'),
    ('pc_7um', 'ParticleCount7', 'Ferrous debris and cleanliness'),
    ('pc_10um', 'ParticleCount10', 'Ferrous debris and cleanliness'),
    ('pc_14um', 'ParticleCount14', 'Ferrous debris and cleanliness'),
    ('pc_20um', 'ParticleCount20', 'Ferrous debris and cleanliness'),
    ('pc_30um', 'ParticleCount30', 'Ferrous debris and cleanliness'),
    ('pc_5_15um', 'ParticleCount5-15um', 'Ferrous debris and cleanliness'),
    ('pc_15_25um', 'ParticleCount15-25um', 'Ferrous debris and cleanliness'),
    ('pc_25_50um', 'ParticleCount25-50um', 'Ferrous debris and cleanliness'),
    ('pc_50_100um', 'ParticleCount50-100um', 'Ferrous debris and cleanliness'),
    ('pc_gt_100um', 'ParticleCountGreater100um', 'Ferrous debris and cleanliness'),
    ('engine_hours', 'EngineHours', 'Operating context'),
    ('liner_hours', 'LinerHours', 'Operating context'),
    ('crown_hours', 'CrownHours', 'Operating context'),
    ('piston_ring_hours', 'PistonRingHours', 'Operating context'),
    ('feed_rate', 'FeedRate', 'Operating context'),
    ('fuel_sulphur_pct', 'FuelSulphurlevel', 'Operating context'),
    ('engine_load', 'EngineLoad', 'Operating context'),
    ('engine_rpm', 'EngineRpm', 'Operating context'),
    ('scav_temp', 'ScavTemp', 'Operating context'),
    ('scav_press', 'ScavPress', 'Operating context'),
    ('jcw_temp_in', 'JCWTmpIn', 'Operating context'),
    ('jcw_temp_out', 'JCWTmpOut', 'Operating context'),
    ('tc_rpm', 'TCRpm', 'Operating context'),
    ('sea_temp', 'SeaTemperature', 'Operating context'),
    ('ambient_temp', 'AmbientTemp', 'Operating context'),
    ('ambient_humidity', 'AmbientHumidity', 'Operating context'),
    ('mcr', 'MCR', 'Operating context'),
    ('sloc', 'SLOC', 'Operating context'),
    ('tc_cut_out', 'TcCutOut', 'Operating context'),
    ('fuel_valves_hr', 'FuelValvesHr', 'Operating context'),
    ('stroke_42', 'Stroke42', 'Operating context'),
    ('last_filter_change_hrs', 'LastFilterChangeHrs', 'Operating context'),
    ('whr_bypass', 'WHRByPass', 'Operating context')
  ) AS v(code, legacy_column, report_block)
)
SELECT 'p-lo-' + lo.code                          AS source_id,
       GETUTCDATE()                               AS source_modified_at,
       CAST(0 AS bit)                             AS source_deleted,
       'lube_oil'                                 AS stream,
       COALESCE(n.normName, lo.legacy_column)     AS name,
       n.ShortName                                AS short_name,
       'lo.' + lo.code                            AS code,
       COALESCE(n.UnitToDisplay, n.Unit)          AS unit,
       'number'                                   AS data_type,
       NULL                                       AS method,
       lo.report_block                            AS report_block,
       ROW_NUMBER() OVER (ORDER BY lo.report_block, lo.code) AS sequence_no,
       1                                          AS is_active
FROM   lo
LEFT   JOIN dbo.Norms n
       ON  REPLACE(n.normName, ' ', '') = lo.legacy_column
       OR  REPLACE(n.ShortName, ' ', '') = lo.legacy_column;

-- One row per lube-oil code with its legacy column and, where the
-- name matched, the Norms row. The limit queries and the optional
-- ColourCode join read this. Build it once, hand-fix the rows where
-- normId came back null, and keep it.

WITH lo(code, legacy_column, report_block) AS (
  SELECT * FROM (VALUES
    ('appearance', 'Appearance', 'Condition'),
    ('vis40', 'Vis40', 'Condition'),
    ('vis100', 'Vischk00', 'Condition'),
    ('vis_index', 'VisIndex', 'Condition'),
    ('flash_point', 'FlashPoint', 'Condition'),
    ('water_pct', 'Water', 'Condition'),
    ('water_kf_ppm', 'WaterKFppm', 'Condition'),
    ('emulsified_water', 'EmulsifiedWater', 'Condition'),
    ('tbn', 'BaseNumber', 'Condition'),
    ('tan', 'AcidNo', 'Condition'),
    ('soot_insolubles', 'SootInsolubles', 'Condition'),
    ('pentane_insolubles', 'PentaneInsolubles', 'Condition'),
    ('sulphated_ash', 'SulphatedAsh', 'Condition'),
    ('oxidation', 'Oxidation', 'Condition'),
    ('oxidation_abs', 'OxidationAbsolute', 'Condition'),
    ('oxidation_products', 'OxidationProducts', 'Condition'),
    ('nitration_abs', 'NitrationAbsolute', 'Condition'),
    ('sulphation_abs', 'SulphationAbsolute', 'Condition'),
    ('glycol', 'GLYCOL', 'Condition'),
    ('fuel_dilution', 'FUELDILUTION', 'Condition'),
    ('asphaltenes', 'Asphaltenes', 'Condition'),
    ('chlorine', 'Chlorine', 'Condition'),
    ('ph', 'pH', 'Condition'),
    ('specific_gravity', 'SpecificGravity', 'Condition'),
    ('conductivity', 'Conductivity', 'Condition'),
    ('liquid_corrosion', 'LiquidCorrosionConcentration', 'Condition'),
    ('vapour_corrosion', 'VapourPhaseCorrosion', 'Condition'),
    ('rocking_test', 'RockingTest', 'Condition'),
    ('sulphur', 'Sulpher', 'Condition'),
    ('fuel_p', 'FuelP', 'Condition'),
    ('oil_condition_index', 'OILCONDITIONINDEX', 'Condition'),
    ('water_scavenger', 'WaterScavenger', 'Condition'),
    ('calcium', 'Calcium', 'Additive elements'),
    ('magnesium', 'Magnesium', 'Additive elements'),
    ('zinc', 'Zinc', 'Additive elements'),
    ('phosphorus', 'Phosphorus', 'Additive elements'),
    ('barium', 'Barium', 'Additive elements'),
    ('boron', 'Boron', 'Additive elements'),
    ('iron', 'Iron', 'Wear metals'),
    ('copper', 'Copper', 'Wear metals'),
    ('lead', 'Lead', 'Wear metals'),
    ('tin', 'Tin', 'Wear metals'),
    ('chromium', 'Chromium', 'Wear metals'),
    ('aluminium', 'Aluminium', 'Wear metals'),
    ('nickel', 'Nickel', 'Wear metals'),
    ('titanium', 'Titanium', 'Wear metals'),
    ('silver', 'Silver', 'Wear metals'),
    ('manganese', 'Manganese', 'Wear metals'),
    ('vanadium', 'Vanadium', 'Wear metals'),
    ('molybdenum', 'Molybdenum', 'Wear metals'),
    ('silicon', 'Silicon', 'Contaminants'),
    ('sodium', 'Sodium', 'Contaminants'),
    ('lithium', 'Lithium', 'Contaminants'),
    ('pq_index', 'PQIndex', 'Ferrous debris and cleanliness'),
    ('ferrous_debris', 'FERROUSDEBRIS', 'Ferrous debris and cleanliness'),
    ('iso4406', 'ISOCODE', 'Ferrous debris and cleanliness'),
    ('iso_4um', '4um', 'Ferrous debris and cleanliness'),
    ('iso_6um', '6um', 'Ferrous debris and cleanliness'),
    ('iso_14um', '14um', 'Ferrous debris and cleanliness'),
    ('iso4407', 'ISOCODE4407', 'Ferrous debris and cleanliness'),
    ('iso4407_4um', '4um4407', 'Ferrous debris and cleanliness'),
    ('iso4407_6um', '6um4407', 'Ferrous debris and cleanliness'),
    ('iso4407_14um', '14um4407', 'Ferrous debris and cleanliness'),
    ('nas1638', 'NAS1638Class', 'Ferrous debris and cleanliness'),
    ('pc_4um', 'ParticleCount4', 'Ferrous debris and cleanliness'),
    ('pc_5um', 'ParticleCount5', 'Ferrous debris and cleanliness'),
    ('pc_6um', 'ParticleCount6', 'Ferrous debris and cleanliness'),
    ('pc_7um', 'ParticleCount7', 'Ferrous debris and cleanliness'),
    ('pc_10um', 'ParticleCount10', 'Ferrous debris and cleanliness'),
    ('pc_14um', 'ParticleCount14', 'Ferrous debris and cleanliness'),
    ('pc_20um', 'ParticleCount20', 'Ferrous debris and cleanliness'),
    ('pc_30um', 'ParticleCount30', 'Ferrous debris and cleanliness'),
    ('pc_5_15um', 'ParticleCount5-15um', 'Ferrous debris and cleanliness'),
    ('pc_15_25um', 'ParticleCount15-25um', 'Ferrous debris and cleanliness'),
    ('pc_25_50um', 'ParticleCount25-50um', 'Ferrous debris and cleanliness'),
    ('pc_50_100um', 'ParticleCount50-100um', 'Ferrous debris and cleanliness'),
    ('pc_gt_100um', 'ParticleCountGreater100um', 'Ferrous debris and cleanliness'),
    ('engine_hours', 'EngineHours', 'Operating context'),
    ('liner_hours', 'LinerHours', 'Operating context'),
    ('crown_hours', 'CrownHours', 'Operating context'),
    ('piston_ring_hours', 'PistonRingHours', 'Operating context'),
    ('feed_rate', 'FeedRate', 'Operating context'),
    ('fuel_sulphur_pct', 'FuelSulphurlevel', 'Operating context'),
    ('engine_load', 'EngineLoad', 'Operating context'),
    ('engine_rpm', 'EngineRpm', 'Operating context'),
    ('scav_temp', 'ScavTemp', 'Operating context'),
    ('scav_press', 'ScavPress', 'Operating context'),
    ('jcw_temp_in', 'JCWTmpIn', 'Operating context'),
    ('jcw_temp_out', 'JCWTmpOut', 'Operating context'),
    ('tc_rpm', 'TCRpm', 'Operating context'),
    ('sea_temp', 'SeaTemperature', 'Operating context'),
    ('ambient_temp', 'AmbientTemp', 'Operating context'),
    ('ambient_humidity', 'AmbientHumidity', 'Operating context'),
    ('mcr', 'MCR', 'Operating context'),
    ('sloc', 'SLOC', 'Operating context'),
    ('tc_cut_out', 'TcCutOut', 'Operating context'),
    ('fuel_valves_hr', 'FuelValvesHr', 'Operating context'),
    ('stroke_42', 'Stroke42', 'Operating context'),
    ('last_filter_change_hrs', 'LastFilterChangeHrs', 'Operating context'),
    ('whr_bypass', 'WHRByPass', 'Operating context')
  ) AS v(code, legacy_column, report_block)
)
SELECT lo.code, lo.legacy_column, lo.report_block, n.normId
INTO   dbo.lo_param_map
FROM   lo
LEFT   JOIN dbo.Norms n
       ON  REPLACE(n.normName, ' ', '') = lo.legacy_column
       OR  REPLACE(n.ShortName, ' ', '') = lo.legacy_column;

-- what still needs a hand-assigned normId
SELECT code, legacy_column FROM dbo.lo_param_map WHERE normId IS NULL;

-- lube oil: one report per upload batch per vessel
SELECT 'rpt-lo-' + CAST(r.CsvID AS varchar(20))
                 + '-' + CAST(r.VesselID AS varchar(20))  AS source_id,
       MAX(COALESCE(r.CorrectionCompletedDate, r.FTSamplePublishDate, r.AssessmentDate, r.DateReceived)) AS source_modified_at,
       CAST(0 AS bit)                                      AS source_deleted,
       'ship-' + CAST(r.VesselID AS varchar(20))           AS ship_source_id,
       'cst-' + CAST(MAX(r.CompanyID) AS varchar(20))      AS organisation_source_id,
       NULL                                                AS template_source_id,
       'lube_oil'                                          AS stream,
       MAX(r.FORMNO)                                       AS report_no,
       CASE WHEN MAX(CAST(r.isAssess AS int)) = 1
            THEN 'assessed' ELSE 'received' END            AS status,
       MAX(r.MDoilUnitRating)                              AS overall_rating,
       MIN(r.DateReceived)                                 AS received_at,
       MAX(r.FTSamplePublishDate)                          AS published_at,
       NULL                                                AS pdf_url
FROM   dbo.AssessmentReport r
WHERE  r.VesselID IS NOT NULL AND r.CsvID IS NOT NULL
GROUP  BY r.CsvID, r.VesselID

UNION ALL

-- fuel oil: the header is already the report
SELECT 'rpt-fo-' + CAST(h.FOReportHeaderId AS varchar(20)),
       COALESCE(h.ModifiedDate, h.CreatedDate),
       CAST(0 AS bit),
       'ship-' + CAST(h.ShipId AS varchar(20)),
       'cst-' + CAST(cs.CstId AS varchar(20)),
       'tpl-fo-' + CAST(h.ParameterTemplateId AS varchar(20)),
       'fuel_oil',
       h.PDFName,
       CASE WHEN h.PublishDate IS NOT NULL THEN 'published'
            WHEN h.UnpublishDate IS NOT NULL THEN 'unpublished'
            ELSE 'in_progress' END,
       h.OverAllGrade,
       h.UploadDate,
       h.PublishDateUTC,
       h.PDFName
FROM   dbo.FO_CSVHeader h
JOIN   dbo.Ship s  ON s.ShipId = h.ShipId
JOIN   dbo.Customer cs ON cs.CstId = s.ShipCompany

UNION ALL

-- water: same shape, from WA_CSVHeader
SELECT 'rpt-wa-' + CAST(h.WAReportHeaderId AS varchar(20)),
       COALESCE(h.PublishDate, h.CreatedDate),
       CAST(0 AS bit),
       'ship-' + CAST(h.ShipId AS varchar(20)),
       'cst-' + CAST(h.CompanyId AS varchar(20)),
       'tpl-wa-' + CAST(h.TemplateId AS varchar(20)),
       'water',
       h.TestReportNoOrCertificateRefNo,
       CASE WHEN h.IsPublish = 1 THEN 'published' ELSE 'in_progress' END,
       NULL,
       NULL,
       h.PublishDateUTC,
       h.PDFName
FROM   dbo.WA_CSVHeader h;

-- lube oil: one AssessmentReport row IS one sample
SELECT 'smp-lo-' + CAST(r.RSID AS varchar(20))            AS source_id,
       COALESCE(r.CorrectionCompletedDate, r.FTSamplePublishDate, r.AssessmentDate, r.DateReceived) AS source_modified_at,
       CAST(0 AS bit)                                     AS source_deleted,
       'rpt-lo-' + CAST(r.CsvID AS varchar(20))
                 + '-' + CAST(r.VesselID AS varchar(20))  AS report_source_id,
       'unit-' + CAST(sm.shipModId AS varchar(20))        AS equipment_unit_source_id,
       'st-lo-' + CAST(ISNULL(r.TestType, 1) AS varchar(20)) AS sample_type_source_id,
       r.SampleNumber                                     AS sample_no,
       r.BOTTLENO                                         AS bottle_no,
       r.DateSampled                                      AS sampled_at,
       r.DateDispatched                                   AS dispatched_at,
       r.DateReceived                                     AS received_at,
       r.Samplingpoint                                    AS sampling_point,
       r.SampledBy                                        AS sampled_by,
       TRY_CAST(r.UnitServiceHours AS decimal(18,2))      AS unit_hours,
       TRY_CAST(r.OilServiceHours AS decimal(18,2))       AS oil_hours,
       r.OilGrade                                         AS oil_grade,
       r.oilUnitRating                                    AS rating,
       NULL                                               AS latitude,
       NULL                                               AS longitude,
       (SELECT r.GOMId          AS gom_id,
               r.WorkOrderNumber AS work_order_no,
               r.DailyMakeUp     AS daily_make_up,
               r.FORMNO          AS form_no
        FOR JSON PATH, WITHOUT_ARRAY_WRAPPER)             AS context
FROM   dbo.AssessmentReport r
LEFT   JOIN dbo.ShipModel sm
       ON  sm.shipId = r.VesselID
       AND sm.macId  = r.MachineryID
       AND ISNULL(sm.SrNo, '') = ISNULL(r.SerialNo, '')
WHERE  r.VesselID IS NOT NULL AND r.CsvID IS NOT NULL;

-- fuel oil: the header is also the sample (FO_CSVDetails hangs off the header)
SELECT 'smp-fo-' + CAST(h.FOReportHeaderId AS varchar(20)),
       COALESCE(h.ModifiedDate, h.CreatedDate),
       CAST(0 AS bit),
       'rpt-fo-' + CAST(h.FOReportHeaderId AS varchar(20)),
       'unit-fo-' + CAST(h.ShipId AS varchar(20)),
       'st-fo-' + CAST(h.OilTypeId AS varchar(20)),
       h.PDFName, NULL, h.UploadDate, NULL, h.UploadDate,
       NULL, NULL, NULL, NULL, NULL, h.OverAllGrade, NULL, NULL,
       (SELECT h.LabComments    AS lab_comment,
               h.VesselComments AS vessel_comment,
               b.FO_BunkerOrTankName AS bunker_or_tank
        FOR JSON PATH, WITHOUT_ARRAY_WRAPPER)
FROM   dbo.FO_CSVHeader h
LEFT   JOIN dbo.FO_BunkerOrTank b ON b.FO_BunkerOrTankID = h.FOBunkerOrTankId

UNION ALL

-- water: one row per sample in the report
SELECT 'smp-wa-' + CAST(d.WACSVHeaderDetailsMappingId AS varchar(20)),
       COALESCE(d.SampleCollectiondate, d.LabReceiptDate),
       CAST(0 AS bit),
       'rpt-wa-' + CAST(d.WAReportHeaderId AS varchar(20)),
       'unit-wa-' + CAST(h.ShipId AS varchar(20))
                  + '-' + CAST(t.WaterTypeId AS varchar(20)),
       'st-wa-' + CAST(t.WaterTypeId AS varchar(20)),
       d.SampleNumber, NULL, d.SampleCollectiondate, NULL, d.LabReceiptDate,
       d.SamplingPoint, NULL, NULL, NULL, NULL, NULL,
       TRY_CAST(d.Latitude AS decimal(9,6)),
       TRY_CAST(d.Longitude AS decimal(9,6)),
       (SELECT d.Vessel_Comment AS vessel_comment,
               d.Lab_Comment    AS lab_comment,
               d.AWBNo          AS awb_no
        FOR JSON PATH, WITHOUT_ARRAY_WRAPPER)
FROM   dbo.WA_CSVHeaderDetailsMapping d
JOIN   dbo.WA_CSVHeader h  ON h.WAReportHeaderId = d.WAReportHeaderId
JOIN   dbo.WA_Template t   ON t.TemplateId = h.TemplateId;

SELECT 'asm-lo-' + CAST(r.RSID AS varchar(20))           AS source_id,
       COALESCE(r.CorrectionCompletedDate, r.AssessmentDate) AS source_modified_at,
       CAST(0 AS bit)                                     AS source_deleted,
       'rpt-lo-' + CAST(r.CsvID AS varchar(20))
                 + '-' + CAST(r.VesselID AS varchar(20))  AS report_source_id,
       'smp-lo-' + CAST(r.RSID AS varchar(20))            AS sample_source_id,
       r.oilUnitRating                                    AS rating,
       r.CommentOil                                       AS comment_oil,
       r.CommentUnit                                      AS comment_unit,
       r.CommentSum                                       AS summary,
       COALESCE(r.ActionSum, r.Action)                    AS action,
       CAST(r.ReviewBy AS varchar(20))                    AS assessed_by,
       r.AssessmentDate                                   AS assessed_at
FROM   dbo.AssessmentReport r
WHERE  r.isAssess = 1

UNION ALL

-- fuel oil keeps three separate comment fields on the header
SELECT 'asm-fo-' + CAST(h.FOReportHeaderId AS varchar(20)),
       COALESCE(h.ModifiedDate, h.CreatedDate),
       CAST(0 AS bit),
       'rpt-fo-' + CAST(h.FOReportHeaderId AS varchar(20)),
       'smp-fo-' + CAST(h.FOReportHeaderId AS varchar(20)),
       h.OverAllGrade,
       h.LabGradeComments,
       h.LabSpecificationComments,
       h.LabOperationalComments,
       NULL,
       CAST(h.ReviewedBy AS varchar(20)),
       h.PublishDateUTC
FROM   dbo.FO_CSVHeader h
WHERE  COALESCE(h.LabGradeComments, h.LabSpecificationComments,
                h.LabOperationalComments) IS NOT NULL;

SELECT 'rv-lo-' + CAST(r.RSID AS varchar(20)) + '-' + v.code  AS source_id,
       COALESCE(r.CorrectionCompletedDate, r.FTSamplePublishDate, r.AssessmentDate, r.DateReceived) AS source_modified_at,
       CAST(0 AS bit)                                     AS source_deleted,
       'smp-lo-' + CAST(r.RSID AS varchar(20))            AS sample_source_id,
       'p-lo-' + v.code                                   AS parameter_source_id,
       LTRIM(RTRIM(v.value_raw))                          AS value_raw,
       TRY_CAST(REPLACE(LTRIM(RTRIM(v.value_raw)), ',', '.') AS decimal(18,4)) AS value_num,
       NULL                                               AS unit,
       NULL                                               AS flag,   -- see note below
       CAST(0 AS bit)                                     AS is_retest,
       1                                                  AS revision,
       NULL                                               AS comment
FROM   dbo.AssessmentReport r
CROSS  APPLY (VALUES
    -- Condition
    ('appearance', r.Appearance),
    ('vis40', r.Vis40),
    ('vis100', r.Vischk00),
    ('vis_index', r.VisIndex),
    ('flash_point', r.FlashPoint),
    ('water_pct', r.Water),
    ('water_kf_ppm', r.WaterKFppm),
    ('emulsified_water', r.EmulsifiedWater),
    ('tbn', r.BaseNumber),
    ('tan', r.AcidNo),
    ('soot_insolubles', r.SootInsolubles),
    ('pentane_insolubles', r.PentaneInsolubles),
    ('sulphated_ash', r.SulphatedAsh),
    ('oxidation', r.Oxidation),
    ('oxidation_abs', r.OxidationAbsolute),
    ('oxidation_products', r.OxidationProducts),
    ('nitration_abs', r.NitrationAbsolute),
    ('sulphation_abs', r.SulphationAbsolute),
    ('glycol', r.GLYCOL),
    ('fuel_dilution', r.FUELDILUTION),
    ('asphaltenes', r.Asphaltenes),
    ('chlorine', r.Chlorine),
    ('ph', r.pH),
    ('specific_gravity', r.SpecificGravity),
    ('conductivity', r.Conductivity),
    ('liquid_corrosion', r.LiquidCorrosionConcentration),
    ('vapour_corrosion', r.VapourPhaseCorrosion),
    ('rocking_test', r.RockingTest),
    ('sulphur', r.Sulpher),
    ('fuel_p', r.FuelP),
    ('oil_condition_index', r.OILCONDITIONINDEX),
    ('water_scavenger', r.WaterScavenger),
    -- Additive elements
    ('calcium', r.Calcium),
    ('magnesium', r.Magnesium),
    ('zinc', r.Zinc),
    ('phosphorus', r.Phosphorus),
    ('barium', r.Barium),
    ('boron', r.Boron),
    -- Wear metals
    ('iron', r.Iron),
    ('copper', r.Copper),
    ('lead', r.Lead),
    ('tin', r.Tin),
    ('chromium', r.Chromium),
    ('aluminium', r.Aluminium),
    ('nickel', r.Nickel),
    ('titanium', r.Titanium),
    ('silver', r.Silver),
    ('manganese', r.Manganese),
    ('vanadium', r.Vanadium),
    ('molybdenum', r.Molybdenum),
    -- Contaminants
    ('silicon', r.Silicon),
    ('sodium', r.Sodium),
    ('lithium', r.Lithium),
    -- Ferrous debris and cleanliness
    ('pq_index', r.PQIndex),
    ('ferrous_debris', r.FERROUSDEBRIS),
    ('iso4406', r.ISOCODE),
    ('iso_4um', r.[4um]),
    ('iso_6um', r.[6um]),
    ('iso_14um', r.[14um]),
    ('iso4407', r.ISOCODE4407),
    ('iso4407_4um', r.[4um4407]),
    ('iso4407_6um', r.[6um4407]),
    ('iso4407_14um', r.[14um4407]),
    ('nas1638', r.NAS1638Class),
    ('pc_4um', r.ParticleCount4),
    ('pc_5um', r.ParticleCount5),
    ('pc_6um', r.ParticleCount6),
    ('pc_7um', r.ParticleCount7),
    ('pc_10um', r.ParticleCount10),
    ('pc_14um', r.ParticleCount14),
    ('pc_20um', r.ParticleCount20),
    ('pc_30um', r.ParticleCount30),
    ('pc_5_15um', r.[ParticleCount5-15um]),
    ('pc_15_25um', r.[ParticleCount15-25um]),
    ('pc_25_50um', r.[ParticleCount25-50um]),
    ('pc_50_100um', r.[ParticleCount50-100um]),
    ('pc_gt_100um', r.ParticleCountGreater100um),
    -- Operating context
    ('engine_hours', r.EngineHours),
    ('liner_hours', r.LinerHours),
    ('crown_hours', r.CrownHours),
    ('piston_ring_hours', r.PistonRingHours),
    ('feed_rate', r.FeedRate),
    ('fuel_sulphur_pct', r.FuelSulphurlevel),
    ('engine_load', r.EngineLoad),
    ('engine_rpm', r.EngineRpm),
    ('scav_temp', r.ScavTemp),
    ('scav_press', r.ScavPress),
    ('jcw_temp_in', r.JCWTmpIn),
    ('jcw_temp_out', r.JCWTmpOut),
    ('tc_rpm', r.TCRpm),
    ('sea_temp', r.SeaTemperature),
    ('ambient_temp', r.AmbientTemp),
    ('ambient_humidity', r.AmbientHumidity),
    ('mcr', r.MCR),
    ('sloc', r.SLOC),
    ('tc_cut_out', r.TcCutOut),
    ('fuel_valves_hr', r.FuelValvesHr),
    ('stroke_42', r.Stroke42),
    ('last_filter_change_hrs', r.LastFilterChangeHrs),
    ('whr_bypass', r.WHRByPass)
) AS v(code, value_raw)
WHERE  v.value_raw IS NOT NULL
  AND  LTRIM(RTRIM(v.value_raw)) <> '';

SELECT 'rv-fo-' + CAST(d.FOReportsDetailsId AS varchar(20)) AS source_id,
       COALESCE(d.ModifiedDate, d.CreatedDate)            AS source_modified_at,
       CAST(0 AS bit)                                     AS source_deleted,
       'smp-fo-' + CAST(d.FOReoprtsHeaderId AS varchar(20)) AS sample_source_id,
       'p-fo-' + CAST(d.ParameterId AS varchar(20))       AS parameter_source_id,
       d.Value                                            AS value_raw,
       TRY_CAST(REPLACE(d.Value, ',', '.') AS decimal(18,4)) AS value_num,
       NULL                                               AS unit,
       LOWER(d.ColorDesign)                               AS flag,
       ISNULL(d.ReTestFlag, 0)                            AS is_retest,
       ISNULL(d.Version, 1)                               AS revision,
       d.Comments                                         AS comment
FROM   dbo.FO_CSVDetails d
WHERE  ISNULL(d.ActiveVersion, 1) = 1

UNION ALL

SELECT 'rv-wa-' + CAST(d.WAReportsDetailsId AS varchar(20)),
       COALESCE(d.ModifiedDate, d.CreatedDate),
       CAST(0 AS bit),
       'smp-wa-' + CAST(d.WACSVHeaderDetailsMappingId AS varchar(20)),
       'p-wa-' + CAST(d.ParameterId AS varchar(20)),
       d.Value,
       TRY_CAST(REPLACE(d.Value, ',', '.') AS decimal(18,4)),
       NULL,
       CASE c.Colour WHEN 1 THEN 'red' WHEN 2 THEN 'yellow'
                     WHEN 3 THEN 'normal' END,
       ISNULL(d.ReTestFlag, 0), 1, d.Comments
FROM   dbo.WA_CSVDetails d
LEFT   JOIN dbo.WA_ColourCode c
       ON  c.WA_CSVHeaderDetailsMappingId = d.WACSVHeaderDetailsMappingId
       AND c.ParaID = d.ParameterId;

-- yellow band
SELECT 'lr-oil-' + CAST(o.onId AS varchar(20)) + '-y'   AS source_id,
       GETUTCDATE()                                     AS source_modified_at,
       CAST(0 AS bit)                                   AS source_deleted,
       'p-lo-' + map.code                               AS parameter_source_id,
       'oil_grade'                                      AS scope_kind,
       og.oilGrade                                      AS scope_ref,
       'yellow'                                         AS band,
       sy.sign                                          AS operator,
       o.val1Y                                          AS value_1,
       o.Val2Y                                          AS value_2,
       o.PercentY                                       AS percent,
       20                                               AS precedence
FROM   dbo.OilNorms o
JOIN   dbo.OilGrade og ON og.oilId = o.oilId
JOIN   dbo.lo_param_map map ON map.normId = o.normId
LEFT   JOIN dbo.Sign sy ON sy.signId = o.signIdY
WHERE  o.signIdY IS NOT NULL

UNION ALL

-- red band: same row, the R columns
SELECT 'lr-oil-' + CAST(o.onId AS varchar(20)) + '-r',
       GETUTCDATE(), CAST(0 AS bit),
       'p-lo-' + map.code, 'oil_grade', og.oilGrade, 'red',
       sr.sign, o.val1R, o.Val2R, o.PercentR, 20
FROM   dbo.OilNorms o
JOIN   dbo.OilGrade og ON og.oilId = o.oilId
JOIN   dbo.lo_param_map map ON map.normId = o.normId
LEFT   JOIN dbo.Sign sr ON sr.signId = o.signIdR
WHERE  o.signIdR IS NOT NULL;

-- fuel oil: FO_BlockParameterLimit holds Yellow1/2 and Red1/2 side by side
SELECT 'lr-fo-' + CAST(l.BlockParameterLimitId AS varchar(20)) + '-' + b.band,
       COALESCE(l.ModifiedDate, l.CreatedDate),
       CAST(0 AS bit),
       'p-fo-' + CAST(l.ParameterId AS varchar(20)),
       'fuel_grade',
       lim.FuelGradeName,
       b.band,
       op.Sign,
       b.v1, b.v2, NULL, 20
FROM   dbo.FO_BlockParameterLimit l
JOIN   dbo.FO_Limits lim ON lim.LimitsId = l.LimitId
LEFT   JOIN dbo.FO_Operator op ON op.OperatorId = l.OperatorId
CROSS  APPLY (VALUES ('yellow', l.Yellow1, l.Yellow2),
                     ('red',    l.Red1,    l.Red2)) AS b(band, v1, v2)
WHERE  b.v1 IS NOT NULL

UNION ALL

-- water: WA_ModelNorms, same idea, scoped to a template
SELECT 'lr-wa-' + CAST(n.TemplateParameterId AS varchar(20)) + '-' + b.band,
       GETUTCDATE(), CAST(0 AS bit),
       'p-wa-' + CAST(n.ParameterId AS varchar(20)),
       'template', t.TemplateName, b.band,
       sg.sign, b.v1, b.v2, NULL, 20
FROM   dbo.WA_ModelNorms n
JOIN   dbo.WA_Template t ON t.TemplateId = n.TemplateId
CROSS  APPLY (VALUES ('yellow', n.Val1Yellow, n.Val2Yellow, n.SignIdYellow),
                     ('red',    n.Val1Red,    n.Val2Red,    n.SignIdRed)) AS b(band, v1, v2, sid)
LEFT   JOIN dbo.WA_Sign sg ON sg.signId = b.sid
WHERE  b.v1 IS NOT NULL;

SELECT DISTINCT
       'rls-fo-' + CAST(d.FOReoprtsHeaderId AS varchar(20))
                 + '-' + CAST(d.ParameterId AS varchar(20)) + '-' + b.band AS source_id,
       COALESCE(d.ModifiedDate, d.CreatedDate)            AS source_modified_at,
       CAST(0 AS bit)                                     AS source_deleted,
       'rpt-fo-' + CAST(d.FOReoprtsHeaderId AS varchar(20)) AS report_source_id,
       'p-fo-' + CAST(d.ParameterId AS varchar(20))       AS parameter_source_id,
       b.band                                             AS band,
       op.Sign                                            AS operator,
       b.v1                                               AS value_1,
       b.v2                                               AS value_2
FROM   dbo.FO_CSVDetails d
LEFT   JOIN dbo.FO_Operator op ON op.OperatorId = d.OperatorId
CROSS  APPLY (VALUES ('yellow', d.Yellow1, d.Yellow2),
                     ('red',    d.Red1,    d.Red2)) AS b(band, v1, v2)
WHERE  ISNULL(d.ActiveVersion, 1) = 1 AND b.v1 IS NOT NULL

UNION ALL

SELECT 'rls-wa-' + CAST(n.Id AS varchar(20)) + '-' + b.band,
       GETUTCDATE(), CAST(0 AS bit),
       'rpt-wa-' + CAST(n.WAReportHeaderId AS varchar(20)),
       'p-wa-' + CAST(n.ParameterId AS varchar(20)),
       b.band, sg.sign, b.v1, b.v2
FROM   dbo.WA_ModelNorms_Reviewed n
CROSS  APPLY (VALUES ('yellow', n.Val1Yellow, n.Val2Yellow, n.SignIdYellow),
                     ('red',    n.Val1Red,    n.Val2Red,    n.SignIdRed)) AS b(band, v1, v2, sid)
LEFT   JOIN dbo.WA_Sign sg ON sg.signId = b.sid
WHERE  b.v1 IS NOT NULL;

-- one bunker unit per vessel that has ever had a fuel-oil report
SELECT DISTINCT
       'unit-fo-' + CAST(h.ShipId AS varchar(20))         AS source_id,
       GETUTCDATE()                                       AS source_modified_at,
       CAST(0 AS bit)                                     AS source_deleted,
       'ship-' + CAST(h.ShipId AS varchar(20))            AS ship_source_id,
       'Bunker fuel'                                      AS name,
       'fuel_oil'                                         AS stream,
       'bunker'                                           AS application,
       NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
       0                                                  AS is_critical,
       NULL                                               AS external_ref,
       1                                                  AS is_active
FROM   dbo.FO_CSVHeader h
WHERE  h.ShipId IS NOT NULL

UNION ALL

-- one unit per vessel per water type
SELECT DISTINCT
       'unit-wa-' + CAST(h.ShipId AS varchar(20))
                  + '-' + CAST(t.WaterTypeId AS varchar(20)),
       GETUTCDATE(), CAST(0 AS bit),
       'ship-' + CAST(h.ShipId AS varchar(20)),
       w.WaterTypeName + ' system',
       'water',
       LOWER(REPLACE(w.WaterTypeName, ' ', '_')),
       NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
       0, NULL, 1
FROM   dbo.WA_CSVHeader h
JOIN   dbo.WA_Template t  ON t.TemplateId = h.TemplateId
JOIN   dbo.WA_WaterType w ON w.WaterTypeId = t.WaterTypeId;

-- lube oil
SELECT COUNT(*) FROM dbo.AssessmentReport
WHERE VesselID IS NOT NULL AND CsvID IS NOT NULL;          -- = sample rows (lo)

SELECT COUNT(DISTINCT CAST(CsvID AS varchar(20)) + '-'
                    + CAST(VesselID AS varchar(20)))
FROM   dbo.AssessmentReport
WHERE  VesselID IS NOT NULL AND CsvID IS NOT NULL;          -- = report rows (lo)

-- result values: samples x populated columns, the number to expect
SELECT COUNT(*)
FROM   dbo.AssessmentReport r
CROSS  APPLY (VALUES (r.Iron), (r.Copper), (r.Vis40) /* … all columns … */) AS v(val)
WHERE  v.val IS NOT NULL AND LTRIM(RTRIM(v.val)) <> '';

-- fuel oil and water
SELECT COUNT(*) FROM dbo.FO_CSVDetails WHERE ISNULL(ActiveVersion,1) = 1;
SELECT COUNT(*) FROM dbo.WA_CSVDetails;