check-supplier-id-fit.sql

SELECT b.id AS business_case_id, 
       b.supplier_id AS business_case_supplier_id, 
       p.supplier_id AS product_supplier_id,
       b.product_id,
       b.status
FROM business_case b
JOIN product p ON b.product_id = p.id
WHERE b.supplier_id IS NOT NULL
  AND b.supplier_id != p.supplier_id
  AND b.status = 'čekáme na výsledek aukce';

get-data-for-contract-ending.sql

SELECT
    bc.id AS business_case_id,
    bc.user_id,
    ucd.value,
    -- merge tags
    CONCAT('– Ukončení smlouvy zajistěte k datu: ', 
        CASE 
            WHEN bc.last_contract_commitment_date IS NULL THEN 'smlouva na dobu neurčitou'
            ELSE TO_CHAR(bc.last_contract_commitment_date, 'DD. MM. YYYY')
        END
    ) AS last_contract_commitment_date,
    CONCAT(s.name, ' – odběrné místo: ', sp.ean_eic, ', ', LOWER(sp.commodity::text), ', ', TRIM(sp.street), ' ', sp.street_no, ', ', sp.zip, ' ', sp.city) AS supply_point_specification,
    CONCAT('– Fakturační adresa: ',
        CASE WHEN bd.customer_type = 'company' THEN
            bd.company_name
        ELSE
            CONCAT(bd.first_name, ' ', bd.last_name)
        END, ', ', TRIM(bd.street), ' ', bd.street_no, ', ', bd.zip, ' ', bd.city
    ) AS invoice_subject_specification
FROM
    business_case bc
    JOIN auction a ON bc.product_id = a.related_product_id
        AND bc.auction_id = a.id
    JOIN supply_point sp ON bc.supply_point_id = sp.id
    JOIN business_case_billing_detail bd ON bc.billing_details_id = bd.id
    JOIN user_contact_detail ucd ON bc.user_id = ucd.user_id AND type = 'email'
    JOIN supplier s ON bc.last_supplier_id = s.id
    JOIN supplier s2 ON bc.supplier_id = s2.id
WHERE
    bc.status IN ('předáno dodavateli', 'čekáme na podpis dokumentů uživatelem')
    AND a.related_product_id IS NOT NULL
    AND bc.product_id IS NOT NULL
    AND ucd.value IS NOT NULL
    AND s.name IS NOT NULL
    AND bc.last_contract_notice_given IS TRUE
    AND bc.user_id NOT IN(
		SELECT
			n.user_id FROM notification_log n
		WHERE
			n.template_id = ANY (ARRAY [16,150]) -- add gas template_id
);

get-notification-data-announce-auction.sql

SELECT
	bc.id AS business_case_id,
	bc.user_id,
	bc.status,
	bc.supplier_id,
	ucd.value,
	CONCAT(sp.ean_eic, ', ', TRIM(sp.street), ' ', sp.street_no, ', ', sp.zip, ' ', sp.city) AS supply_point_specification,
	CONCAT(
		CASE WHEN bd.customer_type = 'company' THEN
			CONCAT(bd.company_name)
		ELSE
			CONCAT(bd.first_name, ' ', bd.last_name)
		END, ', ', TRIM(bd.street), ' ', bd.street_no, ', ', bd.zip, ', ', bd.city) AS invoice_subject_specification
FROM
	business_case bc
	JOIN auction a ON bc.product_id = a.related_product_id
		AND bc.auction_id = a.id
	JOIN supply_point sp ON bc.supply_point_id = sp.id
	JOIN business_case_billing_detail bd ON bc.billing_details_id = bd.id
	JOIN user_contact_detail ucd ON bc.user_id = ucd.user_id AND type = 'email'
WHERE
	bc.status = 'čekáme na výsledek aukce'
	AND a.related_product_id IS NOT NULL
	AND bc.product_id IS NOT NULL
	AND ucd.value IS NOT NULL
	AND bc.user_id NOT IN(
		SELECT
			n.user_id FROM notification_log n
		WHERE
			n.template_id = ANY (ARRAY [16,150]) -- add gas template_id
);

update-product-id-by-auction-related-product-id.sql

-- Napojení vítězného produktu na OP    
UPDATE business_case bc
SET product_id = a.related_product_id
FROM auction a
WHERE bc.auction_id = a.id
  AND a.related_product_id IS NOT NULL
  AND bc.status = 'čekáme na výsledek aukce'
  AND bc.product_id IS NULL;

update-supplier-id-by-product.sql

UPDATE business_case b
SET supplier_id = p.supplier_id
FROM product p
WHERE b.product_id = p.id
  AND b.supplier_id IS NULL
  AND b.status = 'čekáme na výsledek aukce';