Index: trunk/SQL/StoredProcedures/PROC_WYLICZ_I_ZAPISZ_DANE_O_KONTAKTACH_I_BUDZECIE.txt
===================================================================
--- trunk/SQL/StoredProcedures/PROC_WYLICZ_I_ZAPISZ_DANE_O_KONTAKTACH_I_BUDZECIE.txt (revision 472)
+++ trunk/SQL/StoredProcedures/PROC_WYLICZ_I_ZAPISZ_DANE_O_KONTAKTACH_I_BUDZECIE.txt (revision 472)
@@ -0,0 +1,263 @@
+
+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
+
+union all
+--dodajemy SAMPRESS
+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 truck_expo.dbo.KONTAKTY.Id_kontaktu, truck_expo.dbo.KONTAKTY.rodzaj as RODZAJ, 
+  symbol_agenta  AS AGENT, truck_expo.dbo.KONTAKTY.customerId, 
+  DATEPART(YEAR, truck_expo.dbo.KONTAKTY.data) AS ROK,  
+  DATEPART(Month, truck_expo.dbo.kontakty.data) As MS  
+  FROM truck_expo.dbo.KONTAKTY 
+  WHERE DATEPART(YEAR, truck_expo.dbo.KONTAKTY.data) = @ROK and DATEPART(Month, truck_expo.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 truck_expo.dbo.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 truck_expo.dbo.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 [SYMBOL AKWIZYTORA] AS B_AGENT, SUM(NETTO) AS BUDZET, COUNT(*) AS IL_REKLAM, 
+ DATEPART(YEAR,[FAKTURA DATA WYSTAWIENIA]) AS B_ROK, 
+ DATEPART(MONTH,[FAKTURA DATA WYSTAWIENIA]) AS B_MS 
+ FROM truck_expo.dbo.REKLAMA 
+ WHERE 
+  DATEPART(YEAR,[FAKTURA DATA WYSTAWIENIA])=@ROK  AND 
+  DATEPART(MONTH,[FAKTURA DATA WYSTAWIENIA])=@MS   AND 
+ [ZATWIERDZONO DO DRUKU]=1 and
+[FAKTURA WYSTAWIONO]=1 
+GROUP BY [SYMBOL AKWIZYTORA] , DATEPART(YEAR,[FAKTURA DATA WYSTAWIENIA]), DATEPART(MONTH,[FAKTURA DATA WYSTAWIENIA])
+) 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 truck_expo.dbo.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
+