--Change date: <<<|||20200107|||>>>
ALTER proc [dbo].[r_Manadrapport] (@fradato date, @tildato date, @distrikt int = -1, @region varchar(MAX) = '-1') as
--declare @fradato date = '20180901'
--declare @tildato date = '20190930'
--declare @distrikt int = -1
--declare @region varchar(max) = '-1'

select
Bedrift.Navn as Bedriftnavn --A
,Soknad.SoknadID as 'Løpenr' --B
,Tinfo.Kode2 as Avsluttende_Tiltak --C
,isnull(Sinfo.Kode1,30) Sluttårsak --D
,case
	when ISNULL(Sinfo.Kode1,30) in (2,3,4,5,17,18)
	then coalesce((select Kode1 from Tekst inner join Tiltak on Tiltak.FormidletTilSektor = Tekst.TekstNr and Tekst.GrpNr = 404 and Tiltak.SoknadID=Soknad.SoknadID)
	,(select Kode1 from Tekst inner join Tiltak on Tiltak.Arbeidsdestinasjon = Tekst.TekstNr and Tekst.GrpNr = 400 and Tiltak.SoknadID=Soknad.SoknadID),'')
	else ''
	end as Arbeidsdestinasjon --E
,dbo.Kjonn(actor.Personnr) * -1 + 2 as Kjønn --F
,dbo.PersonnrTilFdtAr(actor.Personnr) as FødtÅr --G
,isnull(LandInfo.Kode2,0) as Landskode --H
,CONVERT(VARCHAR(15),soknad.TiltakOppstartdato, 111) as StartDato --I
,CONVERT(VARCHAR(15),soknad.TiltakAvsluttdato, 111) as SluttDato --J
--,(case
--	when exists (select * from Soknad s
--	inner join stilling on stilling.SoknadId = soknad.SoknadID
--	inner join actor virksomhet on Stilling.Virksomhet = virksomhet.actno
--	inner join tekst t on t.GrpNr = 47 and t.TekstNr = virksomhet.Kategori
--	where t.kode1 = 1)
--	then 1 else 2 end) as NHOmedlem
--,(case
--	when exists (select * from Soknad s
--	inner join stilling on stilling.SoknadId = soknad.SoknadID
--	inner join actor virksomhet on Stilling.Virksomhet = virksomhet.actno
--	inner join tekst t on t.GrpNr = 47 and t.TekstNr = virksomhet.Kategori
--	where t.kode1 not in(1,2,3,4,5))
--	then 1 else 2 end) as ikkeNHOmedlem
--,(case
--	when exists (select * from Soknad s
--	inner join stilling on stilling.SoknadId = soknad.SoknadID
--	inner join actor virksomhet on Stilling.Virksomhet = virksomhet.actno
--	inner join tekst t on t.GrpNr = 47 and t.TekstNr = virksomhet.Kategori
--	where t.kode1 = 2)
--	then 1 else 2 end) as interesseorganisasjon
--,(case
--	when exists (select * from Soknad s
--	inner join stilling on stilling.SoknadId = soknad.SoknadID
--	inner join actor virksomhet on Stilling.Virksomhet = virksomhet.actno
--	inner join tekst t on t.GrpNr = 47 and t.TekstNr = virksomhet.Kategori
--	where t.kode1 = 3)
--	then 1 else 2 end) as Kommunal
--,(case
--	when exists (select * from Soknad s
--	inner join stilling on stilling.SoknadId = soknad.SoknadID
--	inner join actor virksomhet on Stilling.Virksomhet = virksomhet.actno
--	inner join tekst t on t.GrpNr = 47 and t.TekstNr = virksomhet.Kategori
--	where t.kode1 = 4)
--	then 1 else 2 end) as FylkesKommunal
--,(case
--	when exists (select * from Soknad s
--	inner join stilling on stilling.SoknadId = soknad.SoknadID
--	inner join actor virksomhet on Stilling.Virksomhet = virksomhet.actno
--	inner join tekst t on t.GrpNr = 47 and t.TekstNr = virksomhet.Kategori
--	where t.kode1 = 5)
--	then 1 else 2 end) as Statlig
,case when ISNULL(Sinfo.Kode1,30) in (2,3,4,5,17,18) and sa.actno is not null
 then isnull((select OrgNo from Actor acn where acn.actno = sa.ActNo),'') else
 case when ISNULL(Sinfo.Kode1,30) in (2,3,4,5,17,18)
 then isnull((select OrgNo from Actor acn where acn.actno = sistestilling.Virksomhet),'') else '' end
 end as ArbOrg --K
,case when ISNULL(Sinfo.Kode1,30) in (2,3,4,5,17,18) and sa.actno is not null
 then isnull((select Arbeidstid from Actor acn where acn.actno = sa.ActNo),'') else
 case when ISNULL(Sinfo.Kode1,30) in (2,3,4,5,17,18)
 then isnull((select Arbeidstid from Actor acn where acn.actno = sistestilling.Virksomhet),'') else '' end
 end as ArbBedr --L

,case when ISNULL(Sinfo.Kode1,30) in (2,3,4,5,18)
then case when sa.actno is not null and Finfo.kode2 = 1 then 1 else 0 end
										else case when stinfo.kode2 = 1 then 1 else 2 end
										end as RingeriVannet --M
--,isnull(substring((select ', '+cast(t.Kode2 as nvarchar(50)) as 'data()'
--from Test
--inner join tekst t on t.GrpNr = 73 and t.TekstNr = test.Provetype
--where Test.SoknadID = soknad.SoknadID and t.kode2 between 1 and 8 for xml path('')),3,50),'') as Arbeidshindring --S
, isnull((SELECT distinct CONVERT(varchar(10), Tekst.Kode2)+','  AS [text()]
            FROM Arbeidshindring ARB1
			inner join Tekst on ARB1.Arbeidshindring = Tekst.TekstNr and GrpNr = 147
            WHERE ARB1.SoknadID = Soknad.SoknadID
--            ORDER BY ARB1.SoknadID
            FOR XML PATH ('')
			),'') as Arbeidshindring --N
,isnull(OrgListe.OrgListe,'') as OrgListe --O
from Soknad
inner join Actor on actor.actno = soknad.ActNo
inner join Tiltak on tiltak.SoknadID = soknad.SoknadID

--left join SoknadActor sa on sa.SoknadId = soknad.SoknadID and sa.type = (select TekstNr from Tekst where GrpNr = 68 and Kode1 = 100)
--left join SoknadActor sa on sa.SoknadId = soknad.SoknadID and sa.ActNo = tiltak.FormidletTilAktor
left join actor sa on tiltak.SoknadID = soknad.SoknadID and sa.actno = tiltak.FormidletTilAktor

outer apply(select top 1 Virksomhet from Stilling inner join Tekst on Tekst.GrpNr=79 and Tekst.TekstNr=Kategori where SoknadId=Soknad.SoknadID and Virksomhet is not null order by Kode2,Stillingprosent desc,Id desc) sistestilling

left join Actor Bedrift on Bedrift.actno=(Select Tekst1 from Tekst where GrpNr=64)
inner join TiltakTilbydt on TiltakTilbydt.ID = Soknad.Tiltak and (@region='-1' or TiltakTilbydt.Distrikt in (select Distrikt.ID from (select B.a.value('.','int') as l from (select cast('<M>'+replace(@region,',','</M><M>')+'</M>' as xml) as r) A cross apply r.nodes('/M') as B(a)) R inner join Distrikt on Distrikt.Region=R.l))
inner join TiltakTilsagn on TiltakTilsagn.id = TiltakTilbydt.Tilsagn
Left join Tekst Tinfo on Tinfo.TekstNr=TiltakTilsagn.TypeTiltak and Tinfo.GrpNr=13
left join Tekst Sinfo on Sinfo.TekstNr=Tiltak.Sluttarsak and Sinfo.GrpNr = 36
Left join Tekst Stinfo on (select kategori from actor where actno = sistestilling.Virksomhet) = Stinfo.TekstNr and Stinfo.GrpNr = 47
Left join Tekst Finfo on (select kategori from actor where actno = sa.actno) = Finfo.TekstNr and Finfo.GrpNr = 47
left join Tekst LandInfo on LandInfo.TekstNr = actor.Fødeland and LandInfo.GrpNr = 194
outer apply (select ((select distinct OrgNo+',' from Actor inner join Stilling on Stilling.Virksomhet=Actor.actno and isnull(Actor.OrgNo,'')<>'' and Stilling.SoknadId=Soknad.SoknadID for xml path(''))) as OrgListe) OrgListe
inner join FirmaView on 1=1
where Soknad.TiltakAvsluttdato between @fradato and @tildato
and ((Tinfo.Kode2 is not null) or (LEN(Tinfo.Kode2) > 0))
and Tinfo.Kode2 != 0
and (@distrikt = -1 or (@distrikt > -1 and TiltakTilbydt.Distrikt = @distrikt))
and dbo.DeltakerMott(soknad.soknadid) = 1