135 lines
4.3 KiB
Transact-SQL
135 lines
4.3 KiB
Transact-SQL
DROP PROCEDURE IF EXISTS uspGetTanarokNemNaplozottOrarendiOrakForEmail
|
|
GO
|
|
|
|
CREATE PROCEDURE uspGetTanarokNemNaplozottOrarendiOrakForEmail
|
|
AS BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
DECLARE
|
|
@tanarId int
|
|
,@intezmenyId int
|
|
,@tanevId int
|
|
,@idoszakKezdete datetime
|
|
,@idoszakVege datetime = CONVERT (date, GETDATE())
|
|
,@csakOrarendiOrak bit = 0
|
|
,@utolsoTanitasiNap datetime
|
|
|
|
CREATE TABLE #NemNaplozottResult (
|
|
NyomtatasiNev nvarchar(255)
|
|
,Email nvarchar(200)
|
|
,EmailGuid uniqueidentifier
|
|
,IntezmenyAzonosito nvarchar(30)
|
|
,IntezmenyNev nvarchar(255)
|
|
,IntezmenyEmailCim nvarchar(255)
|
|
,OsszesNemNaplozottTanorak int
|
|
,MultHetenNemNaplozottHelyettesitesek int
|
|
,MultHetenNemNaplozottTanorak int
|
|
)
|
|
|
|
DROP TABLE IF EXISTS #OrarendiOrak
|
|
CREATE TABLE #OrarendiOrak (
|
|
Datum datetime
|
|
,HelyettesitoTanarID int
|
|
,TanarID int
|
|
,OraTipus nvarchar (200)
|
|
)
|
|
|
|
DECLARE cur CURSOR LOCAL FOR
|
|
SELECT
|
|
alk.ID
|
|
,alk.C_ALINTEZMENYID
|
|
,alk.C_ALTANEVID
|
|
,tv.C_ELSOTANITASINAP
|
|
,tv.C_UTOLSOTANITASINAP
|
|
FROM T_ALKALMAZOTT alk
|
|
INNER JOIN T_USERPROFILE up ON up.C_FELHASZNALOID = alk.ID AND up.TOROLT = 'F'
|
|
INNER JOIN T_TANEV tv ON tv.ID = up.C_TANEVID AND tv.TOROLT = 'F'
|
|
INNER JOIN T_MUNKAUGYIADATOK ma ON ma.C_ALKALMAZOTTID = alk.ID AND ma.TOROLT = 'F'
|
|
INNER JOIN T_INTEZMENY i ON i.ID = alk.C_ALINTEZMENYID AND i.TOROLT = 'F'
|
|
WHERE CAST(up.C_PROFILE AS XML).value('data(/UserProfile/NemNaplozottTanorakEmail)[1]', 'NVARCHAR(MAX)') = 'True'
|
|
AND alk.TOROLT = 'F'
|
|
AND (ma.C_ALKALMAZASMEGSZUNESE >= GETDATE() OR ma.C_ALKALMAZASMEGSZUNESE IS NULL)
|
|
AND tv.C_AKTIV = 'T'
|
|
|
|
OPEN cur
|
|
FETCH NEXT FROM cur INTO @tanarId, @intezmenyId, @tanevId, @idoszakKezdete, @utolsoTanitasiNap
|
|
WHILE @@FETCH_STATUS = 0 BEGIN
|
|
|
|
IF @idoszakVege <= @utolsoTanitasiNap BEGIN
|
|
|
|
INSERT INTO #OrarendiOrak (
|
|
Datum
|
|
,HelyettesitoTanarID
|
|
,TanarID
|
|
,OraTipus
|
|
)
|
|
EXEC sp_GetOrarend
|
|
@pIntezmenyId = @intezmenyId
|
|
,@pTanevId = @tanevId
|
|
,@pIdoszakKezdete = @idoszakKezdete
|
|
,@pIdoszakVege = @idoszakVege
|
|
,@pTanarId = @tanarId
|
|
,@pCsakOrarendiOrak = @csakOrarendiOrak
|
|
,@pIsNapirend = null
|
|
,@pOszlopok = N'Datum,HelyettesitoTanarID,TanarID,OraTipus'
|
|
|
|
INSERT INTO #NemNaplozottResult (
|
|
NyomtatasiNev
|
|
,Email
|
|
,EmailGuid
|
|
,IntezmenyAzonosito
|
|
,IntezmenyNev
|
|
,IntezmenyEmailCim
|
|
,OsszesNemNaplozottTanorak
|
|
,MultHetenNemNaplozottHelyettesitesek
|
|
,MultHetenNemNaplozottTanorak
|
|
) SELECT
|
|
fh.C_NYOMTATASINEV
|
|
,e.C_EMAILCIM
|
|
,e.C_GUID
|
|
,i.C_AZONOSITO
|
|
,ia.C_NEV
|
|
,ia.C_EMAILCIM
|
|
,ISNULL(tbl.OsszesNemNaplozottTanorak, 0)
|
|
,ISNULL(tbl.MultHetenNemNaplozottHelyettesitesek, 0)
|
|
,ISNULL(tbl.MultHetenNemNaplozottTanorak, 0)
|
|
FROM T_FELHASZNALO fh
|
|
INNER JOIN T_EMAIL e ON e.C_FELHASZNALOID = fh.ID AND e.TOROLT = 'F'
|
|
OUTER APPLY (
|
|
SELECT
|
|
COUNT(1) AS OsszesNemNaplozottTanorak
|
|
,SUM(IIF(oo.HelyettesitoTanarID = @tanarId AND oo.Datum > CONVERT (date, DATEADD(DAY, -7,GETDATE())), 1, 0)) AS MultHetenNemNaplozottHelyettesitesek
|
|
,SUM(IIF(oo.Datum > CONVERT (date, DATEADD(DAY, -7, GETDATE())), 1, 0)) AS MultHetenNemNaplozottTanorak
|
|
FROM #OrarendiOrak oo
|
|
WHERE oo.OraTipus = 'OrarendiOra'
|
|
AND (oo.TanarID = fh.ID OR oo.HelyettesitoTanarID = fh.ID)
|
|
) tbl
|
|
INNER JOIN T_INTEZMENYADATOK ia ON ia.C_TANEVID = fh.C_TANEVID
|
|
INNER JOIN T_INTEZMENY i ON i.ID = ia.C_INTEZMENYID
|
|
WHERE e.C_EMAILCIM IS NOT NULL
|
|
AND e.C_GUID IS NOT NULL
|
|
AND e.C_ISPUBLIC = 'F'
|
|
AND e.C_ISHIBASANMEGADVA = 'F'
|
|
AND e.C_ALAPERTELMEZETT = 'T'
|
|
AND fh.ID = @tanarId
|
|
|
|
END
|
|
FETCH NEXT FROM cur INTO @tanarId, @intezmenyId, @tanevId, @idoszakKezdete, @utolsoTanitasiNap
|
|
END
|
|
CLOSE cur
|
|
DEALLOCATE cur
|
|
|
|
SELECT
|
|
nnr.NyomtatasiNev
|
|
,nnr.Email
|
|
,nnr.EmailGuid
|
|
,nnr.IntezmenyAzonosito
|
|
,nnr.IntezmenyEmailCim
|
|
,nnr.IntezmenyNev
|
|
,nnr.OsszesNemNaplozottTanorak
|
|
,nnr.MultHetenNemNaplozottTanorak
|
|
,nnr.MultHetenNemNaplozottHelyettesitesek
|
|
FROM #NemNaplozottResult nnr
|
|
ORDER BY nnr.NyomtatasiNev
|
|
END
|
|
GO
|