USE [mediox_ost]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
if exists (select * from sysobjects where id = object_id('CENIK2_VIEW') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view CENIK2_VIEW
GO
CREATE VIEW CENIK2_VIEW AS
SELECT K.ZBOZI AS ZBOZI_ID, CASE WHEN druh = 'H' THEN 1 WHEN druh = 'P' THEN 3 ELSE 0 END AS DRUH,
S.POZITIVNI_LIST, S.VZP, S.SUKL, S.NAZEV + ' ' + S.FORMA as NAZEV, S.NAZEV_KRATKY, S.ATC, SUM(k.stav + k.rezervace) AS STAV, ROUND(AVG(k.cena_nakupni+k.dph_nakupni),2) AS NC, 
ROUND(AVG(coalesce(kan.cena_prodej_expedice, k.cena_prodej)),2) AS PC,
ROUND(AVG(
	CASE WHEN s.druh = 'H' THEN
		CASE WHEN ISNULL(s.cena_uhrada_zp_proc, 0) = 0 AND dbo.los_PCsDPHpoSleve(k.zbozi, k.dph, k.cena_vyrobni, coalesce(kan.cena_prodej_expedice, k.cena_prodej), GETDATE()) <= s.cena_uhrada_zp THEN
			dbo.los_PCsDPHpoSleve(k.zbozi, k.dph, k.cena_vyrobni, coalesce(kan.cena_prodej_expedice, k.cena_prodej), GETDATE())
		ELSE
			coalesce(kan.cena_prodej_expedice, k.cena_prodej)
		END
	ELSE
		coalesce(kan.cena_prodej_expedice, k.cena_prodej)
	END
),2) AS PC_PO_SLEVE,
CAST (CASE WHEN ISNULL(s.cena_uhrada_zp_proc, 0) <> 0 
THEN ROUND(AVG(coalesce(kan.cena_prodej_expedice, k.cena_prodej)) * s.cena_uhrada_zp_proc / 100 ,2) ELSE s.cena_uhrada_zp
END AS MONEY) AS UHRADA, 
CAST (ROUND (CASE WHEN ISNULL(cena_uhrada_zp_proc,0) = 0
THEN (CASE WHEN AVG(dbo.los_PCsDPHpoSleve(k.zbozi, k.dph, k.cena_vyrobni, coalesce(kan.cena_prodej_expedice, k.cena_prodej), GETDATE())) <= s.cena_uhrada_zp THEN 0 ELSE AVG(coalesce(kan.cena_prodej_expedice, k.cena_prodej)) - s.cena_uhrada_zp END)
ELSE (AVG(coalesce(kan.cena_prodej_expedice, k.cena_prodej)) * (1 - (s.cena_uhrada_zp_proc / 100))) END,2) AS MONEY) AS DOPLATEK,
CAST (ROUND (CASE WHEN ISNULL(cena_uhrada_zp_proc,0) = 0
THEN (CASE WHEN AVG(dbo.los_PCsDPHpoSleve(k.zbozi, k.dph, k.cena_vyrobni, coalesce(kan.cena_prodej_expedice, k.cena_prodej), GETDATE())) <= s.cena_uhrada_zp THEN CASE WHEN GETDATE() < CONVERT(DATETIME, '01.01.2012', 104) THEN 30 ELSE 0 END ELSE 
	(CASE WHEN AVG(coalesce(kan.cena_prodej_expedice, k.cena_prodej)) - s.cena_uhrada_zp > 30 OR GETDATE() >= CONVERT(DATETIME, '01.01.2012', 104) THEN AVG(coalesce(kan.cena_prodej_expedice, k.cena_prodej)) - s.cena_uhrada_zp ELSE 30 END) END)
ELSE 
	CASE WHEN (AVG(coalesce(kan.cena_prodej_expedice, k.cena_prodej)) * (1 - (s.cena_uhrada_zp_proc / 100))) > 30 OR GETDATE() >= CONVERT(DATETIME, '01.01.2012', 104) THEN (AVG(coalesce(kan.cena_prodej_expedice, k.cena_prodej)) * (1 - (s.cena_uhrada_zp_proc / 100))) ELSE 30 END
END,2) AS MONEY) AS DOPLATEK1,
CAST (ROUND (CASE WHEN ISNULL(cena_uhrada_zp_proc,0) = 0
THEN (CASE WHEN AVG(dbo.los_PCsDPHpoSleve(k.zbozi, k.dph, k.cena_vyrobni, coalesce(kan.cena_prodej_expedice, k.cena_prodej), GETDATE())) <= s.cena_uhrada_zp THEN CASE WHEN GETDATE() < CONVERT(DATETIME, '01.01.2012', 104) THEN 30 ELSE 0 END ELSE 
	(CASE WHEN 2 * (AVG(coalesce(kan.cena_prodej_expedice, k.cena_prodej)) - s.cena_uhrada_zp) > 30  OR GETDATE() >= CONVERT(DATETIME, '01.01.2012', 104) THEN 2 * (AVG(coalesce(kan.cena_prodej_expedice, k.cena_prodej)) - s.cena_uhrada_zp) ELSE 30 END) END)
ELSE 
	CASE WHEN 2 * (AVG(coalesce(kan.cena_prodej_expedice, k.cena_prodej)) * (1 - (s.cena_uhrada_zp_proc / 100))) > 30  OR GETDATE() >= CONVERT(DATETIME, '01.01.2012', 104) THEN 2 * (AVG(coalesce(kan.cena_prodej_expedice, k.cena_prodej)) * (1 - (s.cena_uhrada_zp_proc / 100))) ELSE 30 END
END,2) AS MONEY) AS DOPLATEK2,
CAST (ROUND (CASE WHEN ISNULL(cena_uhrada_zp_proc,0) = 0
THEN (CASE WHEN AVG(dbo.los_PCsDPHpoSleve(k.zbozi, k.dph, k.cena_vyrobni, coalesce(kan.cena_prodej_expedice, k.cena_prodej), GETDATE())) <= s.cena_uhrada_zp THEN CASE WHEN GETDATE() < CONVERT(DATETIME, '01.01.2012', 104) THEN 30 ELSE 0 END ELSE 
	(CASE WHEN 3 * (AVG(coalesce(kan.cena_prodej_expedice, k.cena_prodej)) - s.cena_uhrada_zp) > 30 OR GETDATE() >= CONVERT(DATETIME, '01.01.2012', 104) THEN 3 * (AVG(coalesce(kan.cena_prodej_expedice, k.cena_prodej)) - s.cena_uhrada_zp) ELSE 30 END) END)
ELSE 
	CASE WHEN 3 * (AVG(coalesce(kan.cena_prodej_expedice, k.cena_prodej)) * (1 - (s.cena_uhrada_zp_proc / 100))) > 30 OR GETDATE() >= CONVERT(DATETIME, '01.01.2012', 104) THEN 3 * (AVG(coalesce(kan.cena_prodej_expedice, k.cena_prodej)) * (1 - (s.cena_uhrada_zp_proc / 100))) ELSE 30 END
END,2) AS MONEY) AS DOPLATEK3
FROM karta k
JOIN sortiment_view s ON s.zbozi_id = k.zbozi
LEFT JOIN karta_pc_nabidka kan on kan.KARTA_ID = k.KARTA_ID -- ADDED 30.04.2018
WHERE k.stav + k.rezervace <> 0
GROUP BY k.zbozi, s.sukl, S.NAZEV + ' ' + S.FORMA, s.nazev_kratky, s.atc, CASE WHEN druh = 'H' THEN 1 WHEN druh = 'P' THEN 3 ELSE 0 END,
 s.pozitivni_list, s.vzp, s.cena_uhrada_zp, s.cena_uhrada_zp_proc
union
SELECT S.ZBOZI_ID AS ZBOZI_ID, CASE WHEN druh = 'H' THEN 1 WHEN druh = 'P' THEN 3 ELSE 0 END AS DRUH,
S.POZITIVNI_LIST, S.VZP, S.SUKL, S.NAZEV + ' ' + S.FORMA as NAZEV, S.NAZEV_KRATKY, s.ATC, s.stav + s.rezervace AS STAV, -1 as NC, S.CENA_MAX_PRODEJ AS PC,
S.CENA_MAX_PRODEJ AS PC_PO_SLEVE,
CAST (CASE WHEN ISNULL(s.cena_uhrada_zp_proc, 0) <> 0 
THEN ROUND(S.CENA_MAX_PRODEJ * s.cena_uhrada_zp_proc / 100 ,2) ELSE s.cena_uhrada_zp
END AS MONEY) AS UHRADA, 
CAST (ROUND (CASE WHEN ISNULL(cena_uhrada_zp_proc,0) = 0
THEN CASE WHEN S.CENA_MAX_PRODEJ <= s.cena_uhrada_zp THEN 0 ELSE S.CENA_MAX_PRODEJ - s.cena_uhrada_zp END
ELSE (S.CENA_MAX_PRODEJ * (1 - (s.cena_uhrada_zp_proc / 100))) END,2) AS MONEY) AS DOPLATEK,
CAST (ROUND (CASE WHEN ISNULL(cena_uhrada_zp_proc,0) = 0
THEN CASE WHEN S.CENA_MAX_PRODEJ <= s.cena_uhrada_zp THEN CASE WHEN GETDATE() < CONVERT(DATETIME, '01.01.2012', 104) THEN 30 ELSE 0 END ELSE 
	CASE WHEN S.CENA_MAX_PRODEJ - s.cena_uhrada_zp > 30  OR GETDATE() >= CONVERT(DATETIME, '01.01.2012', 104) THEN S.CENA_MAX_PRODEJ - s.cena_uhrada_zp ELSE 30 END END
ELSE 
	CASE WHEN (S.CENA_MAX_PRODEJ * (1 - (s.cena_uhrada_zp_proc / 100))) > 30  OR GETDATE() >= CONVERT(DATETIME, '01.01.2012', 104) THEN (S.CENA_MAX_PRODEJ * (1 - (s.cena_uhrada_zp_proc / 100))) ELSE 30 END
END,2) AS MONEY) AS DOPLATEK1,
CAST (ROUND (CASE WHEN ISNULL(cena_uhrada_zp_proc,0) = 0
THEN CASE WHEN S.CENA_MAX_PRODEJ <= s.cena_uhrada_zp THEN CASE WHEN GETDATE() < CONVERT(DATETIME, '01.01.2012', 104) THEN 30 ELSE 0 END ELSE 
	CASE WHEN 2 * (S.CENA_MAX_PRODEJ - s.cena_uhrada_zp) > 30  OR GETDATE() >= CONVERT(DATETIME, '01.01.2012', 104) THEN 2 * (S.CENA_MAX_PRODEJ - s.cena_uhrada_zp) ELSE 30 END END
ELSE 
	CASE WHEN 2 * (S.CENA_MAX_PRODEJ * (1 - (s.cena_uhrada_zp_proc / 100))) > 30  OR GETDATE() >= CONVERT(DATETIME, '01.01.2012', 104) THEN 2 * (S.CENA_MAX_PRODEJ * (1 - (s.cena_uhrada_zp_proc / 100))) ELSE 30 END
END,2) AS MONEY) AS DOPLATEK2,
CAST (ROUND (CASE WHEN ISNULL(cena_uhrada_zp_proc,0) = 0
THEN CASE WHEN S.CENA_MAX_PRODEJ <= s.cena_uhrada_zp THEN CASE WHEN GETDATE() < CONVERT(DATETIME, '01.01.2012', 104) THEN 30 ELSE 0 END ELSE 
	CASE WHEN 3 * (S.CENA_MAX_PRODEJ - s.cena_uhrada_zp) > 30  OR GETDATE() >= CONVERT(DATETIME, '01.01.2012', 104) THEN 3 * (S.CENA_MAX_PRODEJ - s.cena_uhrada_zp) ELSE 30 END END
ELSE 
	CASE WHEN 3 * (S.CENA_MAX_PRODEJ * (1 - (s.cena_uhrada_zp_proc / 100))) > 30 OR GETDATE() >= CONVERT(DATETIME, '01.01.2012', 104) THEN 3 * (S.CENA_MAX_PRODEJ * (1 - (s.cena_uhrada_zp_proc / 100))) ELSE 30 END
END,2) AS MONEY) AS DOPLATEK3
FROM sortiment_view s
WHERE s.stav + s.rezervace = 0