74 lines
2.1 KiB
SQL
74 lines
2.1 KiB
SQL
-- SQL Queries til at hente monteringsopgaver for tilbud
|
|
|
|
-- 1. Hent komplet opgavebeskrivelse for et materiale
|
|
SELECT
|
|
product_name,
|
|
manufacturer,
|
|
time_estimate_per_unit,
|
|
time_unit,
|
|
skill_level,
|
|
installation_steps,
|
|
key_points,
|
|
weather_conditions
|
|
FROM installation_manuals
|
|
WHERE product_name LIKE '%Bølgeplade%';
|
|
|
|
-- 2. Hent installation steps som tekstliste (til opgavebeskrivelse)
|
|
SELECT
|
|
product_name,
|
|
GROUP_CONCAT(
|
|
CONCAT(step_num, '. ', step_text)
|
|
ORDER BY step_num
|
|
SEPARATOR '\n'
|
|
) as opgave_beskrivelse
|
|
FROM (
|
|
SELECT
|
|
product_name,
|
|
(@row_number:=@row_number + 1) as step_num,
|
|
JSON_UNQUOTE(JSON_EXTRACT(installation_steps, CONCAT('$[', @row_number - 1, ']'))) as step_text
|
|
FROM installation_manuals,
|
|
(SELECT @row_number:=0) as t
|
|
WHERE JSON_LENGTH(installation_steps) > 0
|
|
) steps
|
|
GROUP BY product_name;
|
|
|
|
-- 3. Beregn opgavetid for specifik mængde
|
|
SELECT
|
|
product_name,
|
|
time_estimate_per_unit,
|
|
50 as quantity_m2, -- Eksempel: 50 m²
|
|
(time_estimate_per_unit * 50) as total_hours,
|
|
CEIL((time_estimate_per_unit * 50) / 8) as work_days
|
|
FROM installation_manuals
|
|
WHERE product_name LIKE '%Bølgeplade%';
|
|
|
|
-- 4. Hent key points som bullet points (til tilbud)
|
|
SELECT
|
|
product_name,
|
|
JSON_UNQUOTE(JSON_EXTRACT(key_points, '$[0]')) as key_point_1,
|
|
JSON_UNQUOTE(JSON_EXTRACT(key_points, '$[1]')) as key_point_2,
|
|
JSON_UNQUOTE(JSON_EXTRACT(key_points, '$[2]')) as key_point_3,
|
|
JSON_UNQUOTE(JSON_EXTRACT(key_points, '$[3]')) as key_point_4,
|
|
JSON_UNQUOTE(JSON_EXTRACT(key_points, '$[4]')) as key_point_5
|
|
FROM installation_manuals;
|
|
|
|
-- 5. Formater installation steps til brug i PDF tilbud
|
|
SELECT
|
|
id,
|
|
product_name,
|
|
CONCAT(
|
|
'MONTAGEVEJLEDNING FOR ', UPPER(product_name), '\n\n',
|
|
'Estimeret tid: ', time_estimate_per_unit, ' timer per m²\n',
|
|
'Sværhedsgrad: ',
|
|
CASE skill_level
|
|
WHEN 'let' THEN 'Let ⭐'
|
|
WHEN 'medium' THEN 'Medium ⭐⭐'
|
|
WHEN 'svær' THEN 'Svær ⭐⭐⭐'
|
|
END, '\n\n',
|
|
'MONTERINGSTRIN:\n',
|
|
installation_steps,
|
|
'\n\nVEJRFORHOLD:\n',
|
|
COALESCE(weather_conditions, 'Ingen specifikke krav')
|
|
) as tilbud_beskrivelse
|
|
FROM installation_manuals;
|