68 lines
1.7 KiB
SQL
68 lines
1.7 KiB
SQL
-- Create Bygma database views for import stats and current prices
|
|
-- These views simplify queries for the BygmaPrisbogImportService
|
|
|
|
USE tilbudgivern;
|
|
|
|
-- Drop views if they exist
|
|
DROP VIEW IF EXISTS v_bygma_import_stats;
|
|
DROP VIEW IF EXISTS v_bygma_current_prices;
|
|
|
|
-- Create v_bygma_import_stats view for import history
|
|
CREATE VIEW v_bygma_import_stats AS
|
|
SELECT
|
|
id,
|
|
import_batch_id AS batch_id,
|
|
filename AS file_name,
|
|
file_size,
|
|
total_rows,
|
|
successful_rows,
|
|
failed_rows,
|
|
new_products,
|
|
updated_products,
|
|
updated_prices,
|
|
status,
|
|
started_at,
|
|
completed_at,
|
|
imported_by,
|
|
duration_seconds,
|
|
CASE
|
|
WHEN total_rows > 0 THEN ROUND((successful_rows / total_rows) * 100, 2)
|
|
ELSE 0
|
|
END AS success_rate
|
|
FROM bygma_import_log
|
|
ORDER BY started_at DESC;
|
|
|
|
-- Create v_bygma_current_prices view for latest product prices
|
|
CREATE VIEW v_bygma_current_prices AS
|
|
SELECT
|
|
p.vareNr,
|
|
p.varegrp,
|
|
p.tekst,
|
|
p.enhed,
|
|
p.current_brutto_pris AS bruttoPris,
|
|
p.current_netto_pris AS nettopris,
|
|
p.ean_nr AS eannr,
|
|
p.std,
|
|
p.created_at,
|
|
p.updated_at,
|
|
p.is_active AS status,
|
|
ph.netto_pris AS latest_price,
|
|
ph.price_date AS latest_price_date,
|
|
CASE
|
|
WHEN ph.netto_pris IS NOT NULL AND p.current_netto_pris IS NOT NULL THEN
|
|
ROUND(((ph.netto_pris - p.current_netto_pris) / p.current_netto_pris) * 100, 2)
|
|
ELSE 0
|
|
END AS price_change_pct
|
|
FROM bygma_products p
|
|
LEFT JOIN (
|
|
SELECT
|
|
vareNr,
|
|
netto_pris,
|
|
price_date,
|
|
ROW_NUMBER() OVER (PARTITION BY vareNr ORDER BY price_date DESC) AS rn
|
|
FROM bygma_price_history
|
|
) ph ON p.vareNr = ph.vareNr AND ph.rn = 1
|
|
WHERE p.is_active = 1;
|
|
|
|
SHOW TABLES LIKE 'v_bygma%';
|