USE [BI_Extracomptabel]
GO
/****** Object:  StoredProcedure [dbo].[Transacties_toevoegen_toekomstige_jaren]    Script Date: 01/17/2017 14:54:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Janneke Inia>
-- Create date: <20160209>
-- Description:	<Jaarlijkse transacties toevoegen voor dashboards met vaste prognoses in de toekomst>
-- =============================================
ALTER PROCEDURE [dbo].[Transacties_toevoegen_toekomstige_jaren]
AS
BEGIN

/*
20160208 Janneke Inia
STAP 1 TOEVOEGEN KENGETALLEN VOOR PROGNOSES TOEKOMSTIGE JAREN
Deze query levert de kengetallen op die je moet toevoegen aan de transactietabel.
Dat zijn de kengetallen behorende bij een bepaald dashboard waarbij:
	- de indicator gebruikt wordt;
	- het kengetal gebruikt wordt;
	- het kengetal niet geautomatiseerd is;
	- het type 'Prognose' is;
	- de naam een jaartal bevat.
Stappenplan:
	1. Vul de verschillende waarden in de query.
	2. Kopieer alle rijen naar excel en voeg nieuwe BI_kengetal_ID's toe.
	3. Kopieer vervolgens alle rijen zonder headers en plak deze in BI_Extracomptabel.dbo.BI_dashboard_kengetal.
*/

select		null BI_dashboard_kengetal_ID
,			left(ken.BI_kengetal_naam,len(ken.BI_kengetal_naam)-4)+'2019' BI_kengetal_naam
--Vul hier het jaar in waarvoor het kengetal gemaakt moet worden
,			cast('01-01-2017' as date) BI_kengetal_startdatum
--Vul hier de startdatum van het nieuwe kengetal in
,			null BI_kengetal_einddatum
,			ken.BI_kengetal_label
,			ken.BI_kengetal_type
,			ken.BI_kengetal_subtype
,			ken.BI_kengetal_periode
,			ken.BI_kengetal_soort
,			ken.BI_kengetal_geautomatiseerd_JN
,			ken.BI_kengetal_definitie_functioneel
,			ken.BI_kengetal_definitie_technisch
,			null BI_kengetal_laatst_gewijzigd
,			null BI_kengetal_laatst_gewijzigd_door
from		(
			select		distinct(transactie.BI_dashboard_transactie_kengetal_ID)
			from		BI_Extracomptabel.dbo.BI_dashboard_transactie transactie
			inner join	(
						select		k.*
						from		BI_Extracomptabel.dbo.BI_dashboard_indicator_kengetal_koppeltabel koppeltabel
						inner join	(
									select		indicator.BI_indicator_ID
									,			indicator.BI_indicator_naam
									from		BI_Extracomptabel.dbo.BI_dashboard_indicator indicator
									where		indicator.BI_indicator_einddatum is null
									--and			indicator.BI_indicator_dashboard = 'Tertiaaldashboard Directie'
									--Vul hier het dashboard waarvoor het kengetal aangemaakt moet worden in
									) i
									on			koppeltabel.BI_dashboard_indicator_ID = i.BI_indicator_ID
						inner join	(
									select		kengetal.BI_kengetal_ID
									,			kengetal.BI_kengetal_naam
									,			kengetal.BI_kengetal_type
									from		BI_Extracomptabel.dbo.BI_dashboard_kengetal kengetal
									where		kengetal.BI_kengetal_einddatum is null
									and			kengetal.BI_kengetal_geautomatiseerd_JN = 0
									) k
									on			koppeltabel.BI_dashboard_kengetal_ID = k.BI_kengetal_ID
						) koppel
						on			transactie.BI_dashboard_transactie_kengetal_ID = koppel.BI_kengetal_ID
			where		transactie.BI_dashboard_transactie_invulperiode like '2016%'
			--Vul hier het afgelopen jaar in
			and			koppel.BI_kengetal_type = 'Prognose'
			and			koppel.BI_kengetal_naam like '%2016'
			--Vul hier het afgelopen jaar in
			) trans
left join	(
			select		*
			from		BI_Extracomptabel.dbo.BI_dashboard_kengetal
			) ken
			on			trans.BI_dashboard_transactie_kengetal_ID = ken.BI_kengetal_ID
order by	trans.BI_dashboard_transactie_kengetal_ID


/*
20160208 Janneke Inia
STAP 2 TOEVOEGEN KOPPELINGEN VOOR PROGNOSES TOEKOMSTIGE JAREN
Deze query levert de rijen op die je moet toevoegen aan de koppeltabel.
Stappenplan:
	1. Vul de verschillende waarden in de query (gelijk aan de waarden uit stap 1).
	2. Kopieer alle rijen naar excel en kopieer de BI_kengetal_ID's uit stap 1 naar de tweede kolom.
	3. Kopieer vervolgens alle rijen zonder headers en plak deze in BI_Extracomptabel.dbo.BI_dashboard_indicator_kengetal_koppeltabel.
*/

select		ken.BI_dashboard_indicator_ID
,			null BI_dashboard_kengetal_ID
from		(
			select		distinct(transactie.BI_dashboard_transactie_kengetal_ID)
			from		BI_Extracomptabel.dbo.BI_dashboard_transactie transactie
			inner join	(
						select		k.*
						from		BI_Extracomptabel.dbo.BI_dashboard_indicator_kengetal_koppeltabel koppeltabel
						inner join	(
									select		indicator.BI_indicator_ID
									,			indicator.BI_indicator_naam
									from		BI_Extracomptabel.dbo.BI_dashboard_indicator indicator
									where		indicator.BI_indicator_einddatum is null
									--and			indicator.BI_indicator_dashboard = 'Tertiaaldashboard Directie'
									--Vul hier het dashboard waarvoor het kengetal aangemaakt moet worden in
									) i
									on			koppeltabel.BI_dashboard_indicator_ID = i.BI_indicator_ID
						inner join	(
									select		kengetal.BI_kengetal_ID
									,			kengetal.BI_kengetal_naam
									,			kengetal.BI_kengetal_type
									from		BI_Extracomptabel.dbo.BI_dashboard_kengetal kengetal
									where		kengetal.BI_kengetal_einddatum is null
									and			kengetal.BI_kengetal_geautomatiseerd_JN = 0
									) k
									on			koppeltabel.BI_dashboard_kengetal_ID = k.BI_kengetal_ID
						) koppel
						on			transactie.BI_dashboard_transactie_kengetal_ID = koppel.BI_kengetal_ID
			where		transactie.BI_dashboard_transactie_invulperiode like '2016%'
			--Vul hier het afgelopen jaar in
			and			koppel.BI_kengetal_type = 'Prognose'
			and			koppel.BI_kengetal_naam like '%2016'
			--Vul hier het afgelopen jaar in
			) trans
left join	(
			select		*
			from		BI_Extracomptabel.dbo.BI_dashboard_indicator_kengetal_koppeltabel
			) ken
			on			trans.BI_dashboard_transactie_kengetal_ID = ken.BI_dashboard_kengetal_ID
order by	trans.BI_dashboard_transactie_kengetal_ID


/*
20160208 Janneke Inia
STAP 3 TOEVOEGEN TRANSACTIES VOOR PROGNOSES TOEKOMSTIGE JAREN
Deze query levert de rijen op die je moet toevoegen aan de transactietabel.
Stappenplan:
	1. Vul de verschillende waarden in de query (gelijk aan de waarden uit stap 1).
	2. Kopieer alle rijen zonder headers en plak deze in BI_Extracomptabel.dbo.BI_dashboard_transactie.
*/

select		ikk2.kengetal_ID_nieuw BI_dashboard_transactie_kengetal_ID
,			case when len(ken.BI_dashboard_transactie_invulperiode) = 4
					then ken.BI_dashboard_transactie_invulperiode + 1
				when len(ken.BI_dashboard_transactie_invulperiode) = 5
					then ken.BI_dashboard_transactie_invulperiode + 10
				when len(ken.BI_dashboard_transactie_invulperiode) = 6
					then ken.BI_dashboard_transactie_invulperiode + 100
				end BI_dashboard_transactie_invulperiode
,			case when len(ken.BI_dashboard_transactie_periode) = 4
					then ken.BI_dashboard_transactie_periode + 1
				when len(ken.BI_dashboard_transactie_periode) = 5
					then ken.BI_dashboard_transactie_periode + 10
				when len(ken.BI_dashboard_transactie_periode) = 6
					then ken.BI_dashboard_transactie_periode + 100
				end BI_dashboard_transactie_periode
,			null BI_dashboard_transactie_waarde
,			null BI_dashboard_transactie_gewijzigd_op
,			null BI_dashboard_transactie_gewijzigd_door
from		(
			select		distinct(transactie.BI_dashboard_transactie_kengetal_ID)
			from		BI_Extracomptabel.dbo.BI_dashboard_transactie transactie
			inner join	(
						select		k.*
						from		BI_Extracomptabel.dbo.BI_dashboard_indicator_kengetal_koppeltabel koppeltabel
						inner join	(
									select		indicator.BI_indicator_ID
									,			indicator.BI_indicator_naam
									from		BI_Extracomptabel.dbo.BI_dashboard_indicator indicator
									where		indicator.BI_indicator_einddatum is null
									--and			indicator.BI_indicator_dashboard = 'Tertiaaldashboard Directie'
									--Vul hier het dashboard waarvoor het kengetal aangemaakt moet worden in
									) i
									on			koppeltabel.BI_dashboard_indicator_ID = i.BI_indicator_ID
						inner join	(
									select		kengetal.BI_kengetal_ID
									,			kengetal.BI_kengetal_naam
									,			kengetal.BI_kengetal_type
									from		BI_Extracomptabel.dbo.BI_dashboard_kengetal kengetal
									where		kengetal.BI_kengetal_einddatum is null
									and			kengetal.BI_kengetal_geautomatiseerd_JN = 0
									) k
									on			koppeltabel.BI_dashboard_kengetal_ID = k.BI_kengetal_ID
						) koppel
						on			transactie.BI_dashboard_transactie_kengetal_ID = koppel.BI_kengetal_ID
			where		transactie.BI_dashboard_transactie_invulperiode like '2016%'
			--Vul hier het afgelopen jaar in
			and			koppel.BI_kengetal_type = 'Prognose'
			and			koppel.BI_kengetal_naam like '%2016'
			--Vul hier het afgelopen jaar in
			) trans
left join	(
			select		*
			from		BI_Extracomptabel.dbo.BI_dashboard_transactie transactie
			where		transactie.BI_dashboard_transactie_invulperiode like '2016%'
			--Vul hier het afgelopen jaar in
			) ken
			on			trans.BI_dashboard_transactie_kengetal_ID = ken.BI_dashboard_transactie_kengetal_ID
left join	(
			select		koppeltabel.BI_dashboard_indicator_ID
			,			koppeltabel.BI_dashboard_kengetal_ID kengetal_ID_oud
			from		BI_Extracomptabel.dbo.BI_dashboard_indicator_kengetal_koppeltabel koppeltabel
			) ikk
			on			trans.BI_dashboard_transactie_kengetal_ID = ikk.kengetal_ID_oud
left join	(
			select		koppeltabel2.BI_dashboard_indicator_ID
			,			max(koppeltabel2.BI_dashboard_kengetal_ID) kengetal_ID_nieuw
			from		BI_Extracomptabel.dbo.BI_dashboard_indicator_kengetal_koppeltabel koppeltabel2
			group by	koppeltabel2.BI_dashboard_indicator_ID
			) ikk2
			on			ikk.BI_dashboard_indicator_ID = ikk2.BI_dashboard_indicator_ID
order by	trans.BI_dashboard_transactie_kengetal_ID


/*
20160208 Janneke Inia
STAP 4 TOEVOEGEN TRANSACTIES VOOR HET NIEUWE JAAR
Deze query levert de rijen op die je wilt toevoegen aan de transactietabel.
Dat zijn de transacties behorende bij een bepaald dashboard waarbij:
	- de indicator gebruikt wordt;
	- het kengetal gebruikt wordt;
	- het kengetal niet geautomatiseerd is.
Vul hiervoor twee waarden in:
	1. Het dashboard waarvoor de transacties moeten worden toegevoegd.
	2. Het afgelopen jaar.
Kopieer vervolgens alle rijen zonder headers en plak deze in BI_Extracomptabel.dbo.BI_dashboard_transactie
*/

select		transactie.BI_dashboard_transactie_kengetal_ID
,			case when len(transactie.BI_dashboard_transactie_invulperiode) = 4
					then transactie.BI_dashboard_transactie_invulperiode + 1
				when len(transactie.BI_dashboard_transactie_invulperiode) = 5
					then transactie.BI_dashboard_transactie_invulperiode + 10
				when len(transactie.BI_dashboard_transactie_invulperiode) = 6
					then transactie.BI_dashboard_transactie_invulperiode + 100
				end BI_dashboard_transactie_invulperiode
,			case when len(transactie.BI_dashboard_transactie_periode) = 4
					then transactie.BI_dashboard_transactie_periode + 1
				when len(transactie.BI_dashboard_transactie_periode) = 5
					then transactie.BI_dashboard_transactie_periode + 10
				when len(transactie.BI_dashboard_transactie_periode) = 6
					then transactie.BI_dashboard_transactie_periode + 100
				end BI_dashboard_transactie_periode
,			null BI_dashboard_transactie_waarde
,			null BI_dashboard_transactie_gewijzigd_op
,			null BI_dashboard_transactie_gewijzigd_door
from		BI_Extracomptabel.dbo.BI_dashboard_transactie transactie
inner join	(
			select		koppeltabel.BI_dashboard_kengetal_ID
			from		BI_Extracomptabel.dbo.BI_dashboard_indicator_kengetal_koppeltabel koppeltabel
			inner join	(
						select		indicator.BI_indicator_ID
						from		BI_Extracomptabel.dbo.BI_dashboard_indicator indicator
						where		indicator.BI_indicator_einddatum is null
						--and			indicator.BI_indicator_dashboard = 'Tertiaaldashboard RvC'
						--Vul hier het dashboard waarvoor je transacties wilt aanmaken in
						) i
						on			koppeltabel.BI_dashboard_indicator_ID = i.BI_indicator_ID
			inner join	(
						select		kengetal.BI_kengetal_ID
						from		BI_Extracomptabel.dbo.BI_dashboard_kengetal kengetal
						where		kengetal.BI_kengetal_einddatum is null
						and			kengetal.BI_kengetal_geautomatiseerd_JN = 0
						and			kengetal.BI_kengetal_naam not like '%2016'
						--Vul hier het afgelopen jaar in
						) k
						on			koppeltabel.BI_dashboard_kengetal_ID = k.BI_kengetal_ID
			group by	koppeltabel.BI_dashboard_kengetal_ID
			) koppel
			on			transactie.BI_dashboard_transactie_kengetal_ID = koppel.BI_dashboard_kengetal_ID
where		transactie.BI_dashboard_transactie_invulperiode like '2016%'
--Vul hier het afgelopen jaar in


/*
20160208 Janneke Inia
STAP 5 TOEVOEGEN TRANSACTIES VOOR HET LAATSTE JAAR
Deze query levert de rijen op die je wilt toevoegen aan de transactietabel.
Dat zijn de transacties behorende bij een bepaald dashboard waarbij:
	- de indicator gebruikt wordt;
	- het kengetal gebruikt wordt;
	- het kengetal niet geautomatiseerd is.
Vul hiervoor twee waarden in:
	1. Het dashboard waarvoor de transacties moeten worden toegevoegd.
	2. Het voorlaatste jaar.
Kopieer vervolgens alle rijen zonder headers en plak deze in BI_Extracomptabel.dbo.BI_dashboard_transactie
*/

select		transactie.BI_dashboard_transactie_kengetal_ID
,			case when len(transactie.BI_dashboard_transactie_invulperiode) = 4
					then transactie.BI_dashboard_transactie_invulperiode + 1
				when len(transactie.BI_dashboard_transactie_invulperiode) = 5
					then transactie.BI_dashboard_transactie_invulperiode + 10
				when len(transactie.BI_dashboard_transactie_invulperiode) = 6
					then transactie.BI_dashboard_transactie_invulperiode + 100
				end BI_dashboard_transactie_invulperiode
,			case when len(transactie.BI_dashboard_transactie_periode) = 4
					then transactie.BI_dashboard_transactie_periode + 1
				when len(transactie.BI_dashboard_transactie_periode) = 5
					then transactie.BI_dashboard_transactie_periode + 10
				when len(transactie.BI_dashboard_transactie_periode) = 6
					then transactie.BI_dashboard_transactie_periode + 100
				end BI_dashboard_transactie_periode
,			null BI_dashboard_transactie_waarde
,			null BI_dashboard_transactie_gewijzigd_op
,			null BI_dashboard_transactie_gewijzigd_door
from		BI_Extracomptabel.dbo.BI_dashboard_transactie transactie
inner join	(
			select		koppeltabel.BI_dashboard_kengetal_ID
			from		BI_Extracomptabel.dbo.BI_dashboard_indicator_kengetal_koppeltabel koppeltabel
			inner join	(
						select		indicator.BI_indicator_ID
						from		BI_Extracomptabel.dbo.BI_dashboard_indicator indicator
						where		indicator.BI_indicator_einddatum is null
						--and			indicator.BI_indicator_dashboard = 'Tertiaaldashboard RvC'
						--Vul hier het dashboard waarvoor je transacties wilt aanmaken in
						) i
						on			koppeltabel.BI_dashboard_indicator_ID = i.BI_indicator_ID
			inner join	(
						select		kengetal.BI_kengetal_ID
						from		BI_Extracomptabel.dbo.BI_dashboard_kengetal kengetal
						where		kengetal.BI_kengetal_einddatum is null
						and			kengetal.BI_kengetal_geautomatiseerd_JN = 0
						and			kengetal.BI_kengetal_naam not like '%2018'
						--Vul hier het voorlaatste jaar in
						) k
						on			koppeltabel.BI_dashboard_kengetal_ID = k.BI_kengetal_ID
			group by	koppeltabel.BI_dashboard_kengetal_ID
			) koppel
			on			transactie.BI_dashboard_transactie_kengetal_ID = koppel.BI_dashboard_kengetal_ID
where		transactie.BI_dashboard_transactie_invulperiode like '2018%'
--Vul hier het voorlaatste jaar in



/*
20160208 Janneke Inia
STAP 5 TOEVOEGEN TOELICHTINGEN VOOR HET NIEUWE JAAR
Deze query levert de rijen op die je wilt toevoegen aan de toelichtingen tabel.
Dat zijn de transacties behorende alle indicatoren die ook in het voorgaande jaar als toelichting beschikbaar waren
Vul hiervoor twee waarden in:
Het vorige en het huidige jaar
Kopieer vervolgens alle rijen zonder headers en plak deze in BI_Extracomptabel.dbo.BI_dashboard_transactie
*/

SELECT
BI_dashboard_toelichting_indicator_ID,
REPLACE(BI_dashboard_toelichting_periode, 2016, 2017) AS BI_dashboard_toelichting_periode,
-- Vul hier het vorige jaar en het huidige jaar in
'Toelichting' AS BI_dashboard_toelichting_manager_processeigenaar,
NULL AS BI_dashboard_toelichting_bedrijfsaccountant,
NULL AS BI_dashboard_document_bestand
FROM         BI_dashboard_toelichting
WHERE     (BI_dashboard_toelichting_periode LIKE '2016%')
-- Vul hier het voorgaande jaar in

END