181 lines
8.4 KiB
Transact-SQL
181 lines
8.4 KiB
Transact-SQL
DROP PROCEDURE IF EXISTS uspGetGondviselokElerhetosegeiExport
|
|
GO
|
|
|
|
CREATE PROCEDURE uspGetGondviselokElerhetosegeiExport
|
|
(
|
|
@pTanevId int
|
|
,@pFeladatKategoriaId int = NULL--7553
|
|
,@pDatum datetime = NULL
|
|
,@pTanuloNeve nvarchar(255) = NULL
|
|
,@pNeme int = NULL
|
|
,@pSzuletesiIdoKezdet datetime = NULL
|
|
,@pSzuletesiIdoVeg datetime = NULL
|
|
,@pSzuletesiHely nvarchar(50) = NULL
|
|
,@pOktatasiAzonosito nvarchar(20) = NULL
|
|
,@pSNI char(1) = NULL
|
|
,@pBTM char(1) = NULL
|
|
,@pHatranyosHelyzetId int = NULL
|
|
,@pTorolt char(1) = 'F'
|
|
-- ocs
|
|
,@pOsztalybaSoroltTantervNelkul bit = NULL
|
|
,@pOsztalyCsoportId int = NULL
|
|
,@pVegzosEvfolyam char(1) = NULL
|
|
,@pFeladatellatasiHelyId int = NULL
|
|
,@pEvfolyamTipusaId int = NULL
|
|
,@pAktiv bit = NULL
|
|
,@pMuveszetiAgId int = NULL
|
|
)
|
|
AS BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
DECLARE @sql nvarchar(max) = ''
|
|
|
|
/* Field part */
|
|
SET @sql += '
|
|
SELECT
|
|
fh.C_VEZETEKNEV AS Vezeteknev
|
|
,fh.C_UTONEV As Utonev
|
|
,x.OsztalyNev AS OsztalyOk
|
|
,g.C_NEV AS GondvNev
|
|
,dib_rfok.C_NAME AS RokFok
|
|
,fh.C_OKTATASIAZONOSITO AS OktAzon
|
|
,fh.C_ANYJANEVE AS TanuloAnyjaNev
|
|
,fh.C_SZULETESIHELY AS TanuloSzulHely
|
|
,CONVERT(nvarchar(25), fh.C_SZULETESIDATUM, 102) AS TanuloSzulDatum
|
|
,tel.C_TELEFONSZAM AS Telefon
|
|
,e.C_EMAILCIM AS Email
|
|
,CONCAT(cim.C_IRANYITOSZAM,'' ''+cim.C_VAROS+'','','' ''+cim.C_KOZTERULET,'' ''+dibKtJell.C_NAME,'' ''+cim.C_HAZSZAM,'' ''+cim.C_EMELET+''. emelet'','' ''+cim.C_AJTO+''. ajtó'') AS Cim
|
|
,c_all.C_IRANYITOSZAM AS All_irsz
|
|
,c_all.C_VAROS AS All_varos
|
|
,c_all.C_KOZTERULET AS All_ktnev
|
|
,dib_all.C_NAME AS All_ktjell
|
|
,c_all.C_HAZSZAM AS All_hazszam
|
|
,c_all.C_EMELET AS All_emelet
|
|
,c_all.C_AJTO AS All_ajto
|
|
,c_ide.C_IRANYITOSZAM AS Ide_irsz
|
|
,c_ide.C_VAROS AS Ide_varos
|
|
,c_ide.C_KOZTERULET AS Ide_ktnev
|
|
,dib_ide.C_NAME AS Ide_ktjell
|
|
,c_ide.C_HAZSZAM AS Ide_hazszam
|
|
,c_ide.C_EMELET AS Ide_emelet
|
|
,c_ide.C_AJTO AS Ide_ajto
|
|
,c_tart.C_IRANYITOSZAM AS Tart_irsz
|
|
,c_tart.C_VAROS AS Tart_varos
|
|
,c_tart.C_KOZTERULET AS Tart_ktnev
|
|
,dib_tart.C_NAME AS Tart_ktjell
|
|
,c_tart.C_HAZSZAM AS Tart_hazszam
|
|
,c_tart.C_EMELET AS Tart_emelet
|
|
,c_tart.C_AJTO AS Tart_ajto
|
|
,c_na.C_IRANYITOSZAM AS Na_irsz
|
|
,c_na.C_VAROS AS Na_varos
|
|
,c_na.C_KOZTERULET AS Na_ktnev
|
|
,dib_na.C_NAME AS Na_ktjell
|
|
,c_na.C_HAZSZAM AS Na_hazszam
|
|
,c_na.C_EMELET AS Na_emelet
|
|
,c_na.C_AJTO AS Na_ajto
|
|
'
|
|
|
|
/* FROM part */
|
|
SET @sql += N'
|
|
FROM T_TANULO_OSSZES t
|
|
INNER JOIN T_FELHASZNALO_OSSZES fh ON fh.ID = t.ID
|
|
CROSS APPLY fnGetTanuloOsztalyString (fh.ID, @pDatum, @pFeladatKategoriaId, 1) x
|
|
LEFT JOIN T_GONDVISELO_OSSZES g ON g.TOROLT = ''F'' AND fh.ID = g.C_TANULOID
|
|
LEFT JOIN T_CIM_OSSZES c_all ON c_all.TOROLT = ''F'' AND c_all.C_GONDVISELOID = g.ID AND c_all.C_FELHASZNALOID = fh.ID AND c_all.C_CIMTIPUSA = 907
|
|
LEFT JOIN T_DICTIONARYITEMBASE_OSSZES dib_all ON dib_all.ID=c_all.C_KOZTERULETJELLEGE AND dib_all.C_TANEVID = @pTanevId AND dib_all.TOROLT = ''F''
|
|
LEFT JOIN T_CIM_OSSZES c_tart ON c_tart.TOROLT = ''F'' AND c_tart.C_GONDVISELOID = g.ID AND c_tart.C_FELHASZNALOID = fh.ID AND c_tart.C_CIMTIPUSA = 909
|
|
LEFT JOIN T_DICTIONARYITEMBASE_OSSZES dib_tart ON dib_tart.ID=c_tart.C_KOZTERULETJELLEGE AND dib_tart.C_TANEVID = @pTanevId AND dib_tart.TOROLT = ''F''
|
|
LEFT JOIN T_CIM_OSSZES c_ide ON c_ide.TOROLT = ''F'' AND c_ide.C_GONDVISELOID = g.ID AND c_ide.C_FELHASZNALOID = fh.ID AND c_ide.C_CIMTIPUSA = 908
|
|
LEFT JOIN T_DICTIONARYITEMBASE_OSSZES dib_ide ON dib_ide.ID=c_ide.C_KOZTERULETJELLEGE AND dib_ide.C_TANEVID = @pTanevId AND dib_ide.TOROLT = ''F''
|
|
LEFT JOIN T_CIM_OSSZES c_na ON c_na.TOROLT = ''F'' AND c_na.C_GONDVISELOID = g.ID AND c_na.C_FELHASZNALOID = fh.ID AND c_na.C_CIMTIPUSA = 906
|
|
LEFT JOIN T_DICTIONARYITEMBASE_OSSZES dib_na ON dib_na.ID=c_na.C_KOZTERULETJELLEGE AND dib_na.C_TANEVID = @pTanevId AND dib_na.TOROLT = ''F''
|
|
LEFT JOIN T_DICTIONARYITEMBASE_OSSZES dib_rfok ON dib_rfok.TOROLT = ''F'' AND g.C_ROKONSAGFOKA = dib_rfok.ID AND dib_rfok.C_TANEVID = @pTanevId
|
|
LEFT JOIN T_TELEFON_OSSZES tel ON tel.TOROLT = ''F'' AND tel.C_GONDVISELOID = g.ID AND tel.TOROLT = ''F'' AND tel.C_FELHASZNALOID = fh.ID
|
|
LEFT JOIN T_EMAIL_OSSZES e ON e.TOROLT = ''F'' AND e.C_GONDVISELOID = g.ID AND e.TOROLT = ''F'' AND e.C_FELHASZNALOID = fh.ID
|
|
LEFT JOIN T_CIM_OSSZES cim ON cim.C_FELHASZNALOID = fh.ID AND cim.C_GONDVISELOID = g.ID AND cim.TOROLT=''F'' AND cim.C_ALAPERTELMEZETT = ''T''
|
|
LEFT JOIN T_DICTIONARYITEMBASE_OSSZES dibKtJell ON dibKtJell.ID = cim.C_KOZTERULETJELLEGE AND dibKtJell.TOROLT=''F'' AND dibKtJell.C_TANEVID = @pTanevId'
|
|
|
|
/* WHERE part */
|
|
SET @sql += N'
|
|
WHERE t.C_ALTANEVID = @pTanevId
|
|
AND t.TOROLT = @pTorolt AND fh.TOROLT = @pTorolt'
|
|
+ IIF(@pTanuloNeve IS NULL, N'', N' AND fh.C_NYOMTATASINEV LIKE CONCAT(''%'',@pTanuloNeve,''%'')')
|
|
+ IIF(@pNeme IS NULL, N'', N' AND fh.C_NEME = @pNeme')
|
|
+ IIF(@pSzuletesiIdoKezdet IS NULL, N'', N' AND fh.C_SZULETESIDATUM >= @pSzuletesiIdoKezdet')
|
|
+ IIF(@pSzuletesiIdoVeg IS NULL, N'', N' AND fh.C_SZULETESIDATUM <= @pSzuletesiIdoVeg')
|
|
+ IIF(@pSzuletesiHely IS NULL, N'', N' AND fh.C_SZULETESIHELY LIKE CONCAT(''%'',@pSzuletesiHely,''%'')')
|
|
+ IIF(@pOktatasiAzonosito IS NULL, N'', N' AND fh.C_OKTATASIAZONOSITO LIKE CONCAT(''%'',@pOktatasiAzonosito,''%'')')
|
|
+ IIF(@pSNI IS NULL, N'', N' AND t.C_SAJATOSNEVELESU = @pSNI')
|
|
+ IIF(@pBTM IS NULL, N'', N' AND t.C_BTMPROBLEMAS = @pBTM')
|
|
+ IIF(@pHatranyosHelyzetId IS NULL, N'', N' AND t.C_HATRANYOSHELYZETU = @pHatranyosHelyzetId')
|
|
+ IIF(@pOsztalyCsoportId IS NULL AND @pVegzosEvfolyam IS NULL AND @pEvfolyamTipusaId IS NULL
|
|
AND @pFeladatellatasiHelyId IS NULL AND @pMuveszetiAgId IS NULL AND @pAktiv IS NULL AND @pOsztalybaSoroltTantervNelkul IS NULL, N'',N'
|
|
AND EXISTS (
|
|
SELECT
|
|
1
|
|
FROM T_TANULOCSOPORT_OSSZES tcs
|
|
INNER JOIN T_OSZTALY_OSSZES o ON o.ID = tcs.C_OSZTALYCSOPORTID AND o.TOROLT = ''F''
|
|
INNER JOIN T_OSZTALYCSOPORT_OSSZES ocs ON ocs.Id = o.ID AND ocs.TOROLT=''F''
|
|
WHERE tcs.TOROLT = ''F''
|
|
AND tcs.C_KILEPESDATUM IS NULL
|
|
AND tcs.C_TANEVID = @pTanevId
|
|
AND tcs.C_TANULOID = fh.ID'
|
|
+ IIF(@pOsztalyCsoportId IS NULL,N'',N' AND ocs.ID = @pOsztalyCsoportId')
|
|
+ IIF(@pVegzosEvfolyam IS NULL,N'',N' AND ocs.C_VEGZOSEVFOLYAM = @pVegzosEvfolyam')
|
|
+ IIF(@pEvfolyamTipusaId IS NULL,N'',N' AND ocs.C_EVFOLYAMTIPUSA = @pEvfolyamTipusaId')
|
|
+ IIF(@pFeladatellatasiHelyId IS NULL,N'',N' AND ocs.C_FELADATELLATASIHELYID = @pFeladatellatasiHelyId')
|
|
+ IIF(@pAktiv = 1,N' AND ocs.ID IS NOT NULL',N'')
|
|
+ IIF(@pMuveszetiAgId IS NULL,N'',N' AND ocs.C_MUVESZETIAGID = @pMuveszetiAgId')+'
|
|
)' + IIF(@pOsztalybaSoroltTantervNelkul IS NULL,N'',N' AND t.C_TANTERVID IS NULL') +'
|
|
')
|
|
/* ORDER part */
|
|
SET @sql += N' ORDER BY fh.C_NYOMTATASINEV'
|
|
|
|
EXEC sp_executesql @sql, N'
|
|
@pTanevId int
|
|
,@pFeladatKategoriaId int
|
|
,@pDatum datetime
|
|
,@pTanuloNeve nvarchar(255)
|
|
,@pNeme int
|
|
,@pSzuletesiIdoKezdet datetime
|
|
,@pSzuletesiIdoVeg datetime
|
|
,@pSzuletesiHely nvarchar(50)
|
|
,@pOktatasiAzonosito nvarchar(20)
|
|
,@pSNI char(1)
|
|
,@pBTM char(1)
|
|
,@pHatranyosHelyzetId int
|
|
,@pTorolt char(1)
|
|
-- ocs
|
|
,@pOsztalybaSoroltTantervNelkul bit
|
|
,@pOsztalyCsoportId int
|
|
,@pVegzosEvfolyam char(1)
|
|
,@pFeladatellatasiHelyId int
|
|
,@pEvfolyamTipusaId int
|
|
,@pAktiv bit
|
|
,@pMuveszetiAgId int
|
|
'
|
|
,@pTanevId = @pTanevId
|
|
,@pFeladatKategoriaId = @pFeladatKategoriaId
|
|
,@pDatum = @pDatum
|
|
,@pTanuloNeve = @pTanuloNeve
|
|
,@pNeme = @pNeme
|
|
,@pSzuletesiIdoKezdet = @pSzuletesiIdoKezdet
|
|
,@pSzuletesiIdoVeg = @pSzuletesiIdoVeg
|
|
,@pSzuletesiHely = @pSzuletesiHely
|
|
,@pOktatasiAzonosito = @pOktatasiAzonosito
|
|
,@pSNI = @pSNI
|
|
,@pBTM = @pBTM
|
|
,@pHatranyosHelyzetId = @pHatranyosHelyzetId
|
|
,@pTorolt = @pTorolt
|
|
-- ocs
|
|
,@pOsztalybaSoroltTantervNelkul = @pOsztalybaSoroltTantervNelkul
|
|
,@pOsztalyCsoportId = @pOsztalyCsoportId
|
|
,@pVegzosEvfolyam = @pVegzosEvfolyam
|
|
,@pFeladatellatasiHelyId = @pFeladatellatasiHelyId
|
|
,@pEvfolyamTipusaId = @pEvfolyamTipusaId
|
|
,@pAktiv = @pAktiv
|
|
,@pMuveszetiAgId = @pMuveszetiAgId
|
|
|
|
END
|
|
GO
|