/* Kreis Lippe, Jörg Burzeja - ohne Gewähr
   Jugendhilfeanbieter aus neuer Anbieterdatenbank
   weitere Werte im select mit Bezeichner FK-Tabelle aufnehmen 
   Heimentgelte */
   

declare @sucheanbieter varchar (15)
declare @sucheort varchar (15)

-- hier Suchstring Anbieter oder Ort eingeben - zwischen den Prozentzeichen
set @sucheanbieter = '%inde%'
set @sucheort = '%%'

select 'Jugendhilfeanbieter' AS Typ,
        a.nachname,
        a.vorname,
        a.strasse,
        a.plz,
        a.ort,
--        isnull ((a2.nachname + ','), '')  + (' ') + isnull (a2.Vorname, '') AS [Ansprechpartner],
		eg.bezeichnung AS [Bezeichnung],
--  	  eg.bemerkung,

		(case
        when eg.zahlungsmodus = 1 THEN 'Stunde'
        when eg.zahlungsmodus = 2 THEN 'Tag'
        when eg.zahlungsmodus = 3 THEN 'Woche'
        when eg.zahlungsmodus = 4 THEN 'Monat_fix'
        when eg.zahlungsmodus = 5 THEN 'Monat_anteilg'
        ELSE 'unbekannt' END) AS [Zahlungsmodus],
		eg.betrag AS [Betrag],
		convert(varchar(10),(eg.datumvon),104) AS [von-Datum],
		convert(varchar(10),(eg.datumbis),104) AS [bis-Datum]

from n_adressen a

-- joins auf die fk-Tabellen

        left join n_anbieter ab ON (a.adressnummer = ab.adressref)
        left join n_anbiet2aufnbed ab_af ON (ab.lfdnr = ab_af.anbieter_fk)
        left join n_anbiet2ausbild ab_ab ON (ab.lfdnr = ab_ab.anbieter_fk)
        left join n_anbiet2auskrit ab_ak ON (ab.lfdnr = ab_ak.anbieter_fk)
        left join n_anbiet2bildang ab_ba ON (ab.lfdnr = ab_ba.anbieter_fk)
        left join n_anbiet2eignung ab_eg ON (ab.lfdnr = ab_eg.anbieter_fk)
        left join n_anbiet2geolage ab_gl ON (ab.lfdnr = ab_gl.anbieter_fk)
        left join n_anbiet2rechtsf ab_rf ON (ab.lfdnr = ab_rf.anbieter_fk)
        left join n_anbiet2schular ab_sa ON (ab.lfdnr = ab_sa.anbieter_fk)
        left join n_anbiet2schuext ab_se ON (ab.lfdnr = ab_se.anbieter_fk)
        left join n_anbieter2einart ab_ea ON (ab.lfdnr = ab_ea.anbieter_fk)
        left join n_anbieter2scoring ab_sc ON (ab.lfdnr = ab_sc.anbieter_fk)
        left join n_anbieter2zusatza ab_za ON (ab.lfdnr = ab_za.anbieter_fk)
        left join n_ansprech ap ON (ap.adressnummer = ab.lfdnr)
        left join n_htier2anbiet ab_ht ON (ab.lfdnr = ab_ht.anbieter_fk)
        left join n_pflegestelle ps ON (a.adressnummer = ps.adressref)
        left join n_pflegestelleform psf ON (ps.adressref = psf.pflegestelleref)
        left join n_infraverkehr ifk ON (ps.adressref = ifk.infraref)
        left join n_pflegepersonen pps ON (pps.pflegestelleref = psf.pflegestelleref)


-- joins auf die Tabelle mit den Werten

        -- Zielgruppen / Symptome
        left join n_heimeaufnbed h_af ON (h_af.idaufn = ab_af.zielgruppe_fk)
        -- Ausbildungsmöglichkeiten
        left join n_heimeausbild h_ab ON (h_ab.idausb = ab_ab.ausbildungsmoeglic)
        -- Ausschlusskriterien
        left join n_ausschlusskriter ak ON (ak.lfdnr = ab_ak.ausschlusskriterie)
        -- Bildungsangebote intern,Bildungsangebote / Lehrgänge
        left join n_bildungsangebot ba ON (ba.lfdnr = ab_ba.bildungsangebot_fk)
        -- Ausrichtungen
        left join n_heimeeignung he ON (he.ideig = ab_eg.ausrichtung_fk)
        -- Geographische Lagen
        left join n_geographische_la gl ON (gl.lfdnr = ab_gl.geographischelage_)
        -- Rechtsformen / Weltanschauung
        left join n_rechtsform_welta wa ON (wa.lfdnr = ab_rf.rechtsformweltansc)
        -- Bildungsangebote intern,Schularten
        left join n_schularten sa ON (sa.lfdnr = ab_sa.schulart_fk)
        -- Bildungsangebote extern,Schularten
        left join n_schularten sa2 ON (sa2.lfdnr = ab_se.schulartext_fk)
        -- Einrichtungsarten
        left join n_heimeeinart ha ON (ha.lfdnr = ab_ea.einrichtungsart_fk)
                -- Zusatzmodul Scoring hier nicht vorhanden
        -- Zusatzangebote
        left join n_zusatzangebot za ON (za.lfdnr = ab_za.zusatzangebot_fk)
        -- Anprechpartner
        left join n_adressen a2 ON (ap.ansprechpartner = a2.adressnummer)
        -- Entgelte
        left join n_entgelt eg ON (eg.anbieterjh_fk = ab.lfdnr)
        -- Kapazität,Freie Plätze
        left join n_pflegeplatz pp ON (pp.pflegestelleref = ab.lfdnr)

        -- Pflegestelle - Pflegeformen
        left join n_pflegeform pf ON (pf.lfdnr = psf.pflegeformref)
        -- Pflegestelle - Allgemeine Daten,Besondere Eignung
        left join n_pflegeeignung pe ON (pe.lfdnr = psf.pflegeformref)
        -- Pflegestelle - Infrastrukur,Verkehrsmittel
        left join n_verkehrsmittel vm ON (vm.lfdnr = ifk.verkehrref)
        -- Pflegestelle - Infrastruktur,Wohnform
        left join n_wohnform wf ON (wf.lfdnr = ifk.verkehrref)
        -- Pflegestelle - Infrastruktur,Haustiere
        left join n_haustier ht ON (ht.lfdnr = ab_ht.haustier_fk)
        -- Pflegestelle - Pflegekinder
        left join n_pflegekinder pk ON (pk.pflegestelleref = ps.adressref)
        -- Pflegestelle - Pflegepersonen
        left join n_adressen a3 ON (pps.adressref = a3.adressnummer) 

where (ab.anbietertyp = 'j') 

	and (eg.datumbis is null OR eg.datumbis > getdate ())    /* nur gültige Entgelte anzeigen */
	and a.nachname like @sucheanbieter                       /* Filter gemäss getroffener Auswahl */
	and a.nachname like @sucheort                            /* Filter gemäss getroffener Auswahl */

group by a.nachname,
        a.vorname,
        a.strasse,
        a.plz,
        a.ort,
        ab.anbietertyp,
		eg.zahlungsmodus,
		eg.bezeichnung,
		eg.betrag,
		convert(varchar(10),(eg.datumvon),104),
		convert(varchar(10),(eg.datumbis),104)

order by 1,2,6,5,3,4,7,8,10