
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER Procedure [dbo].[PROC_WYLICZ_I_ZAPISZ_DANE_O_KONTAKTACH_I_BUDZECIE]
@ROK INTEGER = 2003,
@MS INTEGER = 2
As
update dbo.PLAN_TYLKO_CT  set 
w_budzet=wk_budzet,
w_spotkania=spotkanie,
w_tel=tel,
w_email=email,
w_list=list,
w_fax=fax,
w_n_klienci=nkl,
w_klienci=il_klientow,
w_reklamy=il_reklam
from 
(
--WYLICZENIE ILOSCI KONTAKTOW ITP
SELECT ROK, MS, AGENT, IL_KONTKTOW, IL_KLIENTOW, SPOTKANIE, FAX, EMAIL, LIST, TEL ,
ISNULL(IL_REKLAM,0) AS IL_REKLAM,  ROUND(ISNULL(BUDZET,0) ,0) AS wk_BUDZET, ISNULL(NKL,0) AS NKL, HANDEL, PRODUKT, RYNEK, FIRMA
FROM 
(
SELECT ROK , MS, AGENT,  COUNT(Id_kontaktu) AS IL_KONTKTOW, COUNT(DISTINCT customerId) AS IL_KLIENTOW, 
  
  SUM( CASE WHEN RODZAJ='TEL' THEN 1 ELSE 0 END) AS TEL,  
  SUM( CASE WHEN RODZAJ='FAX' THEN 1 ELSE 0 END) AS FAX,  
  SUM( CASE WHEN RODZAJ='LIST' THEN 1 ELSE 0 END) AS LIST,  
  SUM( CASE WHEN RODZAJ='E-MAIL' THEN 1 ELSE 0 END) AS EMAIL,  
  SUM( CASE WHEN RODZAJ='SPOTKANIE' THEN 1 ELSE 0 END) AS SPOTKANIE  
FROM 
(
SELECT dbo.KONTAKTY.Id_kontaktu, dbo.KONTAKTY.rodzaj as RODZAJ, 
  symbol_agenta  AS AGENT, dbo.KONTAKTY.customerId, 
  DATEPART(YEAR, dbo.KONTAKTY.data) AS ROK,  
  DATEPART(Month, dbo.kontakty.data) As MS  
  FROM dbo.KONTAKTY 
  WHERE DATEPART(YEAR, dbo.KONTAKTY.data) = @ROK and DATEPART(Month, dbo.kontakty.data) = @MS
) KO  
GROUP BY ROK, MS , AGENT
)  P_K 
--WYLICZENIE ILOSCI REKLAM
FULL OUTER JOIN
(
SELECT [SYMBOL AKWIZYTORA] AS R_AGENT, COUNT(*) AS IL_REKLAM_1,  
 DATEPART(YEAR,[DATA ZAMÓWIENIA]) AS R_ROK, 
 DATEPART(MONTH,[DATA ZAMÓWIENIA]) AS R_MS 
 FROM REKLAMA 
 WHERE 
  DATEPART(YEAR,[DATA ZAMÓWIENIA])=@ROK  AND 
  DATEPART(MONTH,[DATA ZAMÓWIENIA])=@MS   AND 
 [ZATWIERDZONO DO DRUKU]=1 
GROUP BY [SYMBOL AKWIZYTORA] , DATEPART(YEAR,[DATA ZAMÓWIENIA]), DATEPART(MONTH,[DATA ZAMÓWIENIA])
) P_R 
ON R_AGENT=AGENT AND ROK=R_ROK AND MS=R_MS
FULL OUTER JOIN
(
SELECT USERNAME,  DATEPART(YEAR,DATA) AS K_ROK,  DATEPART(MONTH,DATA) AS K_MS, COUNT(*) AS NKL FROM KLIENCI
 WHERE DATEPART(YEAR,DATA)=@ROK  AND  DATEPART(MONTH,DATA)=@MS
 GROUP BY USERNAME,  DATEPART(YEAR,DATA),  DATEPART(MONTH,DATA)
) AS P_NK
ON USERNAME=AGENT AND ROK=K_ROK AND MS=K_MS
--WYLICZENIE BUDŻETU
FULL OUTER JOIN
(
SELECT B_ROK, B_MS,
Sum(NETTO) AS BUDZET, 
count(LiczbaReklam) as IL_REKLAM,
b_agent as B_AGENT
FROM dbo.VIEW_POMOCNICZY_BUDZET 
where B_ROK =@ROK and
B_MS =@MS
GROUP BY B_ROK, B_MS,B_AGENT ) P_B 
ON B_AGENT=AGENT AND ROK=B_ROK AND MS=B_MS
-- WYLICZENIE DANYCH DO TESTU WIEDZY
FULL OUTER JOIN
(
SELECT user_name AS T_AGENT, DATEPART(YEAR,[DATA]) AS T_ROK, DATEPART(MONTH,[DATA]) AS T_MS, 
            SUM(HANDEL) AS HANDEL, SUM(PRODUKT) AS PRODUKT, SUM(RYNEK) AS RYNEK, SUM(FIRMA) AS FIRMA
FROM VIEW_ZESTAWIENIE_TEST_WIEDZY
 WHERE 
  DATEPART(YEAR,[DATA])=@ROK AND 
  DATEPART(MONTH,[DATA])=@MS   
GROUP BY user_name, DATEPART(YEAR,[DATA]) , DATEPART(MONTH,[DATA]) 
) P_T
ON T_AGENT=AGENT AND ROK=T_ROK AND MS=T_MS
) wl
--Końcówka instrukcji Update
inner join dbo.[PLAN_TYLKO_CT]  on wl.agent=[dbo].[PLAN_TYLKO_CT].symbol and wl.rok=[dbo].[PLAN_TYLKO_CT].rok and wl.ms=[dbo].[PLAN_TYLKO_CT].ms


update dbo.[PLAN]  set 
w_budzet=wk_budzet,
w_spotkania=spotkanie,
w_tel=tel,
w_email=email,
w_list=list,
w_fax=fax,
w_n_klienci=nkl,
w_klienci=il_klientow,
w_reklamy=il_reklam
from 
(
select ROK, MS, AGENT, sum(IL_KONTKTOW) as IL_KONTKTOW, sum(IL_KLIENTOW) as IL_KLIENTOW, sum(SPOTKANIE) as SPOTKANIE,
sum(FAX) as FAX, Sum(EMAIL) as EMAIL,sum(LIST) as LIST, sum(TEL) as TEL,
sum(IL_REKLAM) as IL_REKLAM, sum(wk_BUDZET) as wk_BUDZET, sum(NKL) as NKL, sum(HANDEL) as HANDEL, 
sum(PRODUKT) as PRODUKT, sum(RYNEK) as RYNEK, sum(FIRMA) as FIRMA
from
( SELECT ROK, MS, AGENT, IL_KONTKTOW, IL_KLIENTOW, SPOTKANIE, FAX, EMAIL, LIST, TEL ,
ISNULL(IL_REKLAM,0) AS IL_REKLAM,  ROUND(ISNULL(BUDZET,0) ,0) AS wk_BUDZET, ISNULL(NKL,0) AS NKL, HANDEL, PRODUKT, RYNEK, FIRMA
FROM 
(
SELECT ROK , MS, AGENT,  COUNT(Id_kontaktu) AS IL_KONTKTOW, COUNT(DISTINCT customerId) AS IL_KLIENTOW, 
  
  SUM( CASE WHEN RODZAJ='TEL' THEN 1 ELSE 0 END) AS TEL,  
  SUM( CASE WHEN RODZAJ='FAX' THEN 1 ELSE 0 END) AS FAX,  
  SUM( CASE WHEN RODZAJ='LIST' THEN 1 ELSE 0 END) AS LIST,  
  SUM( CASE WHEN RODZAJ='E-MAIL' THEN 1 ELSE 0 END) AS EMAIL,  
  SUM( CASE WHEN RODZAJ='SPOTKANIE' THEN 1 ELSE 0 END) AS SPOTKANIE  
FROM 
(
SELECT dbo.KONTAKTY.Id_kontaktu, dbo.KONTAKTY.rodzaj as RODZAJ, 
  symbol_agenta  AS AGENT, dbo.KONTAKTY.customerId, 
  DATEPART(YEAR, dbo.KONTAKTY.data) AS ROK,  
  DATEPART(Month, dbo.kontakty.data) As MS  
  FROM dbo.KONTAKTY 
  WHERE DATEPART(YEAR, dbo.KONTAKTY.data) = @ROK and DATEPART(Month, dbo.kontakty.data) = @MS
) KO  
GROUP BY ROK, MS , AGENT
)  P_K 
--WYLICZENIE ILOSCI REKLAM
FULL OUTER JOIN
(
SELECT [SYMBOL AKWIZYTORA] AS R_AGENT, COUNT(*) AS IL_REKLAM_1,  
 DATEPART(YEAR,[DATA ZAMÓWIENIA]) AS R_ROK, 
 DATEPART(MONTH,[DATA ZAMÓWIENIA]) AS R_MS 
 FROM REKLAMA 
 WHERE 
  DATEPART(YEAR,[DATA ZAMÓWIENIA])=@ROK  AND 
  DATEPART(MONTH,[DATA ZAMÓWIENIA])=@MS   AND 
 [ZATWIERDZONO DO DRUKU]=1 
GROUP BY [SYMBOL AKWIZYTORA] , DATEPART(YEAR,[DATA ZAMÓWIENIA]), DATEPART(MONTH,[DATA ZAMÓWIENIA])
) P_R 
ON R_AGENT=AGENT AND ROK=R_ROK AND MS=R_MS
FULL OUTER JOIN
(
SELECT USERNAME,  DATEPART(YEAR,DATA) AS K_ROK,  DATEPART(MONTH,DATA) AS K_MS, COUNT(*) AS NKL FROM KLIENCI
 WHERE DATEPART(YEAR,DATA)=@ROK  AND  DATEPART(MONTH,DATA)=@MS
 GROUP BY USERNAME,  DATEPART(YEAR,DATA),  DATEPART(MONTH,DATA)
) AS P_NK
ON USERNAME=AGENT AND ROK=K_ROK AND MS=K_MS
--WYLICZENIE BUDŻETU
FULL OUTER JOIN
(
SELECT B_ROK, B_MS,
Sum(NETTO) AS BUDZET, 
count(LiczbaReklam) as IL_REKLAM,
b_agent as B_AGENT
FROM dbo.VIEW_POMOCNICZY_BUDZET 
where B_ROK =@ROK and
B_MS =@MS
GROUP BY B_ROK, B_MS,B_AGENT ) P_B 
ON B_AGENT=AGENT AND ROK=B_ROK AND MS=B_MS
-- WYLICZENIE DANYCH DO TESTU WIEDZY
FULL OUTER JOIN
(
SELECT user_name AS T_AGENT, DATEPART(YEAR,[DATA]) AS T_ROK, DATEPART(MONTH,[DATA]) AS T_MS, 
            SUM(HANDEL) AS HANDEL, SUM(PRODUKT) AS PRODUKT, SUM(RYNEK) AS RYNEK, SUM(FIRMA) AS FIRMA
FROM VIEW_ZESTAWIENIE_TEST_WIEDZY
 WHERE 
  DATEPART(YEAR,[DATA])=@ROK AND 
  DATEPART(MONTH,[DATA])=@MS   
GROUP BY user_name, DATEPART(YEAR,[DATA]) , DATEPART(MONTH,[DATA]) 
) P_T
ON T_AGENT=AGENT AND ROK=T_ROK AND MS=T_MS
) CT_PLUS_SAMPRESS
group by rok, ms, agent) wl
--Końcówka instrukcji Update
inner join [plan] on wl.agent=[PLAN].symbol and wl.rok=[PLAN].rok and wl.ms=[PLAN].ms

GO

SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO

