/*   *****************************************
     K r e i s    L i p p e,  Jörg Burzeja
     Stand: 11.02.2010
     Gültige Budgetstammdaten für den jungen Menschen mit Anbieter, Betragsart, doppischen Kriterien, und Suchfunktion
     Datenbank SQL 2000 mit PROSOZ 14plus v573
     Weitergabe nur durch Download mit Urheberhinweis
     Nutzungserlaubnis nur für Auswertungen der eigenen Datenbank (nicht in Beratungsterminen)
     ******************************************    */


declare @suche_jungerMensch varchar (30)
declare @suche_anbieter varchar (30)
declare @suche_bezirk varchar (30)
declare @suche_regio varchar (30)
declare @suche_sachbearbeiter varchar (30)
declare @suche_hilfeart varchar (30)
declare @suche_hilfeform varchar (30)
declare @suche_betragsart varchar (30)
declare @suche_leistungsart varchar (30)
declare @suche_anbietertyp varchar (30)

-- hier den/die Suchbegriffe (Teil des Namens)  - zwischen den Prozentzeichen - eingeben
set @suche_bezirk            = '%%'
set @suche_regio             = '%%'
set @suche_sachbearbeiter    = '%%'
set @suche_jungerMensch      = '%%'
set @suche_anbieter          = '%%'
set @suche_anbietertyp       = '%%'     
                                          /* P = Pflegestelle;
                                             J = Jugendhilfeanbieter */ 
set @suche_hilfeart          = '%%'
set @suche_leistungsart      = '%%'
set @suche_hilfeform         = '%%'
set @suche_betragsart        = '%%'     
                                          /* W = Entgeltsat mit Einheiten/Woche; 
                                             P = Entgelt; 
                                             F = Festwert; 
                                             M = manueller Betrag; 
                                             S = Betreuungssatz */
 


SELECT t1.bereich,
       t1.sachbearbeiter,
       t1.aktenzeichen,
       t1.nachname,
       t1.vorname,
       t1.geburtsdatum,
       t1.regionalbüro,
       t1.bezirk,
       t2.anbietertyp,
       t2.nn_beteiligter,
       t2.vn_beteiligter,
       t2.ort,
       t2.hilfeart,
       ISNULL (t2.bezeichnung,'') AS bezeichnung,
       t2.betragsart,
       t2.von,
       ISNULL (t2.bis,'') AS bis,
       t2.leistungsart,
       t2.hilfeform
       

FROM

(
SELECT DISTINCT
        vorgangsnummer, 
		vg.bereich AS [Bereich],
        be.bb AS [Sachbearbeiter],
        vg.aktenzeichen AS [Aktenzeichen],
        ad.nachname AS [Nachname],
        ad.vorname AS [Vorname],
        convert(varchar(10),pd.geburtsdatum,104) AS Geburtsdatum,
        sb.str_beschr AS Regionalbüro,
        sb.bezirk AS Bezirk,
        ad.ort AS [Wohnort Kind],
        ad1.ort AS [Wohnort Mutter],
        ad2.ort AS [Wohnort Vater]

FROM n_personendaten pd
     LEFT JOIN n_adressen ad          ON (ad.adressnummer = pd.zuordnungsnummer)
     LEFT JOIN n_vorgbeteiligte vb    ON (vb.adresse = ad.adressnummer)
     LEFT JOIN n_vorgang vg           ON (vg.vorgangsnummer = vb.vorgang)
     LEFT JOIN bere be                ON (vg.bereich = be.brs)
     LEFT JOIN n_beteiligtenarten ba  ON (vb.beteiligtenart = ba.betnummer)
     LEFT JOIN n_statbezirkext sbe    ON (sbe.bezirkid = vg.statbezirk)
     LEFT JOIN stat_bezirk sb         ON (sb.lnr = sbe.lnr)
     LEFT JOIN hekopf hk              ON (vg.muendelnr_alt = hk.mnr)

--JOIN auf Mutter
     LEFT JOIN n_adrbezugalt aa ON (hk.adrmu = aa.adrnr_alt)
     LEFT JOIN n_adressen ad1 ON (ad1.adressnummer = aa.adrnr_neu)

-- JOIN auf Vater
     LEFT JOIN n_adrbezugalt aa1 ON (hk.adrva = aa1.adrnr_alt)
     LEFT JOIN n_adressen ad2 ON (ad2.adressnummer = aa1.adrnr_neu)


WHERE ba.bezeichnung like 'Junger%'
        AND vg.bereich between 3000 AND 3999

-- Fälle ohne Endevorgang
        AND (vg.beginnvorgang <= getdate () )
        AND (vg.endevorgang is null OR vg.endevorgang = '' OR vg.endevorgang >= getdate () )

-- Fälle ohne Endezahlung
        AND (vg.endezahlung is null OR vg.endezahlung = '' OR vg.endezahlung >= getdate () )

        AND be.bb LIKE @suche_sachbearbeiter
        AND sb.bezirk LIKE @suche_bezirk
        AND ad.nachname LIKE @suche_jungerMensch
        AND sb.str_beschr LIKE @suche_regio

) AS t1 INNER JOIN

(SELECT  DISTINCT vorgangsnummer,
        (CASE 
            WHEN ab.anbietertyp = 'P'    THEN 'Pflegestelle'
            WHEN ab.anbietertyp = 'J'    THEN 'Jugendhilfeanbieter'
            ELSE 'unbekannt'
         END) AS anbietertyp,


        ad.nachname AS NN_Beteiligter,
        ad.vorname AS VN_Beteiligter,
        ad.ort AS Ort,
        ha.bez AS Hilfeart,
       (CASE 
            WHEN hs.kzbet = 'P'    THEN eg.bezeichnung
            WHEN hs.kzbet = 'W'    THEN eg.bezeichnung
            WHEN hs.kzbet = 'F'    THEN fw.fwbez
            WHEN hs.kzbet = 'M'    THEN hs.bezmanubetrag
            WHEN hs.kzbet = 'S'    THEN hs.bezmanubetrag
            ELSE 'unbekannt'
         END) AS Bezeichnung,

       (CASE 
            WHEN hs.kzbet = 'F'         THEN 'Festwert'
            WHEN hs.kzbet = 'P'         THEN 'Entgeltsatz'
            WHEN hs.kzbet = 'W'         THEN 'Entgeltsatz mit Einheiten/Woche'
            WHEN hs.kzbet = 'M'         THEN 'manueller Betrag'
            WHEN hs.kzbet = 'S'         THEN 'Betreuungssatz'
            ELSE 'unbekannt'
         END) AS Betragsart,
       convert(varchar(10),hs.datvon,104) as von,
       convert(varchar(10),hs.datbis,104) as bis,
       lst.bezeichnung AS Leistungsart,        /* Reiter Leistung */
       kst.bezeichnung AS Hilfeform,           /* Reiter Kostenstelle */
       zk1.bezeichnung AS [nicht_belegt],      /* Reiter Zusatzkontierung1 */
       zk2.bezeichnung AS Einnahmeart          /* Reiter Zusatzkontierung2 */

FROM    n_vorgang vg
        LEFT JOIN n_vorgbeteiligte vb        ON (vg.vorgangsnummer = vb.vorgang)
        LEFT JOIN n_benutzer bn              ON (vg.sachbearbeiter = bn.lfdnr)
        LEFT JOIN bere be                    ON (vg.bereich = be.brs)
        LEFT JOIN n_adressen ad              ON (vb.adresse = ad.adressnummer)
        LEFT JOIN n_personendaten pd         ON (vb.adresse = pd.zuordnungsnummer)
        LEFT JOIN hebuch hb                  ON (hb.mnr = vg.muendelnr_alt 
                                                 AND hb.bereich = vg.bereich 
                                                 AND hb.n_vorgbet_fk = vb.beteiligtennummer )
        LEFT JOIN hebush hs                  ON (hb.lnr = hs.vlnr)
        LEFT JOIN hilfeart ha                ON (hs.hifanr = ha.lnr)
        LEFT JOIN fewe fw                    ON (fw.fwnr = hs.fwnr)
        LEFT JOIN n_anbieter ab              ON (ad.adressnummer = ab.adressref)
        LEFT JOIN n_entgelt eg               ON (eg.anbieterjh_fk = ab.lfdnr and eg.lfdnr = hs.pfsatz)

-- join auf Doppik Merkmal 'Reiter Leistung' - Umbenennung in Leistung [bei Ausgaben]
        LEFT JOIN n_hilfeart2leistun h2l ON (hs.leistung_fk = h2l.leistung_fk)
        LEFT JOIN n_leistung lst ON (lst.lfdnr = h2l.leistung_fk)

-- join auf Doppik Merkmal 'Reiter Zusatzkontierung1' - nicht belegt
        LEFT JOIN n_hiart2zuko11 h2k1 ON (hs.zusatzkont1_fk = h2k1.zusatzkont1_fk)
        LEFT JOIN n_zusatzkont1 zk1 ON (zk1.lfdnr = h2k1.zusatzkont1_fk)

-- join auf Doppik Merkmal 'Reiter Zusatzkontierung2' - Umbenennung in Hilfeart [bei Einnahmen]
        LEFT JOIN n_hiart2zuko21 h2k2 ON (hs.zusatzkont2_fk = h2k2.zusatzkont2_fk)
        LEFT JOIN n_zusatzkont2 zk2 ON (zk2.lfdnr = h2k2.zusatzkont2_fk)

-- join auf Doppik Merkmal 'Reiter Kostenstelle' - Umbenennung in Hilfegruppe
        LEFT JOIN n_hiart2kst1 h2t1 ON (hs.kostenstelle_fk = h2t1.kostenstelle_fk)
        LEFT JOIN n_kostenstelle kst ON (kst.lfdnr = h2t1.kostenstelle_fk)

-- join auf Doppik Merkmal Produkt
        LEFT JOIN n_produkt pt ON (pt.lfdnr = ha.produkt_fk)

-- join auf Doppik Merkmal Paragraf
        LEFT JOIN n_paragraf pg ON (pg.lfdnr = ha.paragraf_fk)

WHERE (ab.anbietertyp LIKE @suche_anbietertyp) 
       AND (be.bsg = 3)
       
       AND hb.datvon < getdate ()
       AND (hb.datbis >= getdate ()
           OR hb.datbis IS NULL)
       
       AND hs.datvon < getdate ()
       AND (hs.datbis >= getdate ()
           OR hs.datbis IS NULL)

      AND (ad.nachname LIKE @suche_anbieter
         OR ad.vorname LIKE @suche_anbieter)
      AND (lst.bezeichnung LIKE @suche_leistungsart
          OR hs.bezmanubetrag LIKE @suche_leistungsart)
      AND hs.kzbet LIKE @suche_betragsart
      AND kst.bezeichnung LIKE @suche_hilfeform 
      AND ha.bez LIKE @suche_hilfeart


) AS t2 ON (t1.vorgangsnummer = t2.vorgangsnummer)

ORDER BY t1.bereich,
       t1.nachname,
       t1.vorname,
       t2.hilfeart,
       t2.leistungsart,
       t2.hilfeform