USE [aa_bis]
GO
/****** Object:  UserDefinedFunction [dbo].[get_lista_klientow_z_pomocatech_F_BASE]    Script Date: 18.10.2017 15:10:01 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[get_lista_klientow_z_pomocatech_F_BASE] (@id_klienta INT,@subscriptions subscriptionsType READONLY,@kategorie kategorieType READONLY,@ftype INT)
RETURNS @klienci TABLE(id_klienta INT,typ INT)
AS
BEGIN
-- 2 ver

DECLARE @date_ref datetime
DECLARE @Months12Ago datetime, @Months25Ago datetime
DECLARE @F1 INT = 1
DECLARE @F2 INT = 2

--declare @klienci TABLE(id_klienta int INDEX IX1 CLUSTERED,typ int)

SET @Months12Ago = DATEADD(DAY, 14, DATEADD(MONTH, -12, DATEADD(dd, 0, DATEDIFF(dd, 0, getdate()))))
SET @Months25Ago = DATEADD(MONTH, -37, DATEADD(dd, 0, DATEDIFF(dd, 0, getdate())))

DECLARE @first_license TABLE (id_klienta INT, id_sprzedawcy INT, dt_first_lic datetime, rn INT)
DECLARE @last_tech_supp TABLE (id_klienta INT, id_sprzedawcy INT, dt_last_tech_supp datetime, rn INT)
    INSERT INTO @first_license
    SELECT *
    FROM (
    SELECT id_klienta, id_sprzedawcy, data_sprzedazy AS dt_first_lic, ROW_NUMBER() OVER(PARTITION BY id_klienta ORDER BY data_sprzedazy ASC) AS rn   -- pierwsza licencja bezterminowa
    FROM s_faktury WHERE EXISTS (
    SELECT id_faktury FROM s_faktury_pozycje WHERE s_faktury_pozycje.id_faktury = s_faktury.id_faktury AND EXISTS (SELECT [id_produktu] FROM [dbo].[s_produkty] WHERE typ_produktu=1 AND id_rodzaju_kodu IN (SELECT id_rodzaju_kodu FROM dbo.fun_get_id_rodzaju_kodakt(-1)) AND id_kategorii IN(SELECT id_kategorii FROM @kategorie) AND s_produkty.id_produktu=s_faktury_pozycje.id_produktu))
    -- -1    licencja
    -- 365    licencja roczna
    AND typ_faktury IN (10,7,12)
    ) AS w1 WHERE rn=1

    UNION

    SELECT *
    FROM (
    SELECT id_klienta, id_sprzedawcy, data_sprzedazy AS dt_first_lic, ROW_NUMBER() OVER(PARTITION BY id_klienta ORDER BY data_sprzedazy DESC) AS rn   -- ostatnia licencja roczna
    FROM s_faktury WHERE EXISTS (
    SELECT id_faktury FROM s_faktury_pozycje WHERE s_faktury_pozycje.id_faktury = s_faktury.id_faktury AND EXISTS (SELECT [id_produktu] FROM [dbo].[s_produkty] WHERE typ_produktu=1 AND id_rodzaju_kodu IN (SELECT id_rodzaju_kodu FROM dbo.fun_get_id_rodzaju_kodakt(365))  AND id_kategorii IN(SELECT id_kategorii FROM @kategorie) AND s_produkty.id_produktu=s_faktury_pozycje.id_produktu))
    -- -1    licencja
    -- 365    licencja roczna
    AND typ_faktury IN (10,7,12)
    ) AS w1 WHERE rn=1

    -- tech. supp

    INSERT INTO @last_tech_supp
    SELECT * FROM (
    SELECT id_klienta, id_sprzedawcy, data_sprzedazy AS dt_last_tech_supp, ROW_NUMBER() OVER(PARTITION BY id_klienta ORDER BY data_sprzedazy DESC) AS rn -- ostatnia subskrypcja aktualizacji
    FROM s_faktury WHERE EXISTS (
    SELECT id_faktury FROM s_faktury_pozycje WHERE s_faktury_pozycje.id_faktury = s_faktury.id_faktury AND EXISTS (SELECT [id_produktu] FROM [dbo].[s_produkty] WHERE id_produktu IN (SELECT subscription FROM @subscriptions) AND s_produkty.id_produktu=s_faktury_pozycje.id_produktu)) --244
    -- 204    Subskrybcja na aktualizację oprogramowania ProDentis i opiekę techniczną 12 miesięcy.
    -- 215    Subskrybcja na aktualizację oprogramowania Protetis i opiekę techniczną 12 miesięcy
    -- 242    Subskrybcja na aktualizację oprogramowania MEDICUS i opiekę techniczną 12 miesięcy.
    -- 251    Subskrybcja na aktualizację oprogramowania ProDentis i opiekę techniczną 12 miesięcy + NFZ na 2017r
    -- 268    Subskrybcja na aktualizację programu MEDICUS z NFZ dla stomatologii i opiekę techniczną 12 miesięcy
    AND typ_faktury IN (10,7,12)
    -- 7    Faktura VAT
    -- 10    Faktura Fikcyjna
    -- 12    FVATIS Sp. z o.o.
    ) AS w2 WHERE rn=1
    --group by id_klienta

    -- klienci -> zakup licencji i pomoc techniczna
    INSERT INTO @klienci
    SELECT s_klienci.id_klienta,1 AS typ
    FROM (
    SELECT isnull(fl.id_klienta,lts.id_klienta) AS id_klienta, dt_first_lic, dt_last_tech_supp
    FROM (SELECT * FROM @first_license WHERE (dt_first_lic<getdate() AND dt_first_lic>@Months12Ago)) AS fl
    FULL JOIN (SELECT * FROM @last_tech_supp WHERE (dt_last_tech_supp<getdate() AND dt_last_tech_supp>@Months12Ago)) AS lts ON fl.id_klienta=lts.id_klienta) AS f
    JOIN s_klienci ON s_klienci.id_klienta = f.id_klienta
    WHERE isnull(@id_klienta, s_klienci.id_klienta) = s_klienci.id_klienta
    --and  ( and dt_last_tech_supp is null))
    ORDER BY dt_last_tech_supp, dt_first_lic

    INSERT INTO @klienci
    SELECT id_klienta,3 AS typ
    FROM @first_license fl
     WHERE isnull(@id_klienta, fl.id_klienta) = fl.id_klienta AND NOT EXISTS (SELECT  id_klienta FROM @klienci WHERE id_klienta = fl.id_klienta)
    UNION
    SELECT id_klienta,3 AS typ
    FROM @last_tech_supp lts
    WHERE isnull(@id_klienta, lts.id_klienta) = lts.id_klienta AND NOT EXISTS (SELECT  id_klienta FROM @klienci WHERE id_klienta = lts.id_klienta)

    IF(@ftype =@F2 OR (@ftype = @F1 AND  @id_klienta IS NOT NULL))
    BEGIN
        INSERT INTO @klienci
        SELECT s_klienci.id_klienta,2 AS typ
        FROM s_klienci
        LEFT JOIN crm_klienci_infodod ON crm_klienci_infodod.id_klienta = s_klienci.id_klienta
    WHERE isnull(@id_klienta,s_klienci.id_klienta) =s_klienci.id_klienta AND NOT EXISTS (SELECT  id_klienta FROM @klienci WHERE id_klienta = s_klienci.id_klienta)
        AND opieka_techniczna_do IS NOT NULL AND opieka_techniczna_do >= GETDATE()
    END
--end
RETURN
END