1585 lines
77 KiB
C#
1585 lines
77 KiB
C#
using System;
|
|
using System.Collections.Generic;
|
|
using System.Data;
|
|
using System.Linq;
|
|
using System.Text;
|
|
using Kreta.Core;
|
|
using Kreta.Core.Logic;
|
|
using Kreta.DataAccess.Interfaces;
|
|
using Kreta.DataAccessManual.Interfaces;
|
|
using Kreta.DataAccessManual.ParameterClasses;
|
|
using Kreta.DataAccessManual.Util;
|
|
using Kreta.Enums;
|
|
using Kreta.Enums.ManualEnums;
|
|
using Kreta.Framework;
|
|
using Kreta.Framework.Util;
|
|
using Kreta.Resources;
|
|
using SDA.DataProvider;
|
|
using SDA.Kreta.Entities;
|
|
|
|
namespace Kreta.DataAccessManual
|
|
{
|
|
internal class TanitasiOraDal : DataAccessBase, ITanitasiOraDal
|
|
{
|
|
public TanitasiOraDal(DalHandler handler) : base(handler) { }
|
|
public TanitasiOraDal(DalHandler handler, GridParameters parameters) : base(handler, parameters) { }
|
|
|
|
public ITanitasiOra Get()
|
|
{
|
|
return TanitasiOra.GiveAnInstance();
|
|
}
|
|
|
|
public ITanitasiOra Get(int id)
|
|
{
|
|
var entity = TanitasiOra.GiveAnInstance();
|
|
entity.LoadByID(id);
|
|
return entity;
|
|
}
|
|
|
|
public DataSet GetTantargyMegtartottTanorai(int tantargyID, int tanevId)
|
|
{
|
|
List<CommandParameter> ParamList = new List<CommandParameter>(); /// TODO(@DevKornél): mehetnének list-be object initializerrel
|
|
ParamList.Add(new CommandParameter("pTantargyID", tantargyID));
|
|
ParamList.Add(new CommandParameter("pTanevId", tanevId));
|
|
|
|
string CommandText = @"
|
|
select
|
|
T_TANITASIORA_OSSZES.ID AS ID,
|
|
T_OSZTALYCSOPORT_OSSZES.C_NEV AS OsztalyCsoport,
|
|
T_TANITASIORA_OSSZES.C_TEMA AS Tema,
|
|
T_TANITASIORA_OSSZES.C_DATUM AS Datum,
|
|
T_TANITASIORA_OSSZES.C_ORASZAM AS Oraszam
|
|
from T_TANITASIORA_OSSZES
|
|
INNER JOIN T_OSZTALYCSOPORT_OSSZES ON T_TANITASIORA_OSSZES.C_OSZTALYCSOPORTID = T_OSZTALYCSOPORT_OSSZES.ID
|
|
INNER JOIN T_TANTARGY_OSSZES ON T_TANITASIORA_OSSZES.C_TANTARGYID = T_TANTARGY_OSSZES.ID
|
|
WHERE
|
|
T_TANITASIORA_OSSZES.TOROLT='F' AND T_TANITASIORA_OSSZES.C_MEGTARTOTT='T'
|
|
AND T_TANTARGY_OSSZES.ID = :pTantargyID
|
|
AND T_TANTARGY_OSSZES.C_TANEVID = :pTanevId
|
|
";
|
|
|
|
DataSet ds = GetData(CommandText, ParamList);
|
|
return ds;
|
|
}
|
|
|
|
public DataSet GetTanorakDataSet(TanoraKeresesePCO pco, int? tanevId, int intezmenyId)
|
|
{
|
|
using (var command = new SDACommand())
|
|
{
|
|
command.Connection = UserContext.Instance.SDAConnection;
|
|
command.Transaction = UserContext.Instance.SDATransaction;
|
|
command.CommandType = CommandType.StoredProcedure;
|
|
command.CommandText = "uspGetTanoraData";
|
|
|
|
command.Parameters.Add("pTanevId", tanevId);
|
|
command.Parameters.Add("pIntezmenyId", intezmenyId);
|
|
command.Parameters.Add("pOktNevKategoriaId", pco.FeladatKategoria);
|
|
command.Parameters.Add("pFeladatEllatasiHelyId", pco.FeladatEllatasiHely);
|
|
command.Parameters.Add("pIdoszakTol", pco.Tol);
|
|
command.Parameters.Add("pIdoszakIg", pco.Ig);
|
|
command.Parameters.Add("pOsztalyCsoportId", pco.OsztCsop);
|
|
command.Parameters.Add("pKapcsolodoCsoportok", pco.KellKapcsolodoCsoportok);
|
|
command.Parameters.Add("pCsoportTipusId", pco.CsopTip);
|
|
command.Parameters.Add("pTantargyId", pco.Targy);
|
|
command.Parameters.Add("pPedagogusId", pco.Tanar);
|
|
command.Parameters.Add("pTeremId", pco.Terem);
|
|
command.Parameters.Add("pSorszamozando", pco.Sorszamozando);
|
|
command.Parameters.Add("pFoglalkozasId", pco.Fogl);
|
|
command.Parameters.Add("pElmaradt", pco.Elmaradt);
|
|
command.Parameters.Add("pParhuzamosOra", pco.ParhuzamosOra);
|
|
command.Parameters.Add("pTulora", pco.Tulora);
|
|
command.Parameters.Add("pTamopOra", pco.TAMOPOra);
|
|
command.Parameters.Add("pKipOra", pco.KIPOra);
|
|
command.Parameters.Add("pVekop73317", pco.VEKOP73317);
|
|
command.Parameters.Add("pKapOra", pco.KAPOra);
|
|
command.Parameters.Add("pMultikulturalisOra", pco.MultikulturalisOra);
|
|
command.Parameters.Add("pKomplexOra", pco.KomplexOra);
|
|
command.Parameters.Add("pNemSzakrendszeruOra", pco.NemSzakrendszeruOra);
|
|
command.Parameters.Add("pIktTanora", pco.IKTTanora);
|
|
command.Parameters.Add("pNemzetisegiOra", pco.NemzetisegiOra);
|
|
command.Parameters.Add("pIsFelnottoktatasiSzerzodes", pco.IsFelnottoktatasiSzerzodes);
|
|
command.Parameters.Add("pOnlineOra", pco.OnlineOra);
|
|
command.Parameters.Add("pMindennaposTestneveles", pco.MindennaposTestneveles);
|
|
command.Parameters.Add("pIprTanora", pco.IPRTanora);
|
|
command.Parameters.Add("pDifferencialt", pco.Differencialt);
|
|
command.Parameters.Add("pKooperativ", pco.Kooperativ);
|
|
command.Parameters.Add("pMeres", pco.Meres);
|
|
command.Parameters.Add("pDrama", pco.Drama);
|
|
command.Parameters.Add("pTiop", pco.Tiop);
|
|
command.Parameters.Add("pBontott", pco.Bontott);
|
|
command.Parameters.Add("pHelyettesitett", pco.Helyettesitett);
|
|
command.Parameters.Add("pEfop32317", pco.EFOP32317);
|
|
command.Parameters.Add("pGinop623", pco.GINOP623);
|
|
command.Parameters.Add("pEfop31716EselyteremtesAKoznevelesben", pco.EFOP31716EselyteremtesAKoznevelesben);
|
|
command.Parameters.Add("pEfop33717", pco.EFOP33717);
|
|
command.Parameters.Add("pTiop11112120120001", pco.TIOP11112120120001);
|
|
command.Parameters.Add("pDfhtOra", pco.DFHTOra);
|
|
command.Parameters.Add("pRahangolodas", pco.Rahangolodas);
|
|
command.Parameters.Add("pTestmozgasAlapuAlprogram", pco.TestmozgasAlapuAlprogram);
|
|
command.Parameters.Add("pMuveszetAlapuAlprogram", pco.MuveszetAlapuAlprogram);
|
|
command.Parameters.Add("pEletgyakorlatAlapuAlprogram", pco.EletgyakorlatAlapuAlprogram);
|
|
command.Parameters.Add("pLogikaAlapuAlprogram", pco.LogikaAlapuAlprogram);
|
|
command.Parameters.Add("pTeOrad", pco.TeOrad);
|
|
command.Parameters.Add("pDigitalisAlapuAlprogram", pco.DigitalisAlapuAlprogram);
|
|
command.Parameters.Add("pEfop23517201700038Miapalya", pco.EFOP23517201700038Miapalya);
|
|
command.Parameters.Add("pHfRogzitve", pco.HFRogzitve);
|
|
command.Parameters.Add("pTanevTananyaganakIsmetlese", pco.TanevTananyaganakIsmetlese);
|
|
command.Parameters.Add("pTanevTananyaganakKiegeszitese", pco.TanevTananyaganakKiegeszitese);
|
|
command.Parameters.Add("pTanulokDigitalisKeszsegfejlesztese", pco.TanulokDigitalisKeszsegfejlesztese);
|
|
command.Parameters.Add("pProjektmunka", pco.Projektmunka);
|
|
command.Parameters.Add("pTanulokKompenzaciosIraskeszsegFejlesztese", pco.TanulokKompenzaciosIraskeszsegFejlesztese);
|
|
command.Parameters.Add("pTanulokKompenzaciosOlvasasiKepessegFejlesztese", pco.TanulokKompenzaciosOlvasasiKepessegFejlesztese);
|
|
command.Parameters.Add("pTanulokMatematikaiGondolkodasiKepessegenekKompenzaciosFejlesztese", pco.TanulokMatematikaiGondolkodasiKepessegenekKompenzaciosFejlesztese);
|
|
command.Parameters.Add("pTanulokNyelviKepessegenekKompenzaciosFejlesztese", pco.TanulokNyelviKepessegenekKompenzaciosFejlesztese);
|
|
command.Parameters.Add("pTanulokOnmegismeresenekSegitese", pco.TanulokOnmegismeresenekSegitese);
|
|
command.Parameters.Add("pTanulokOnallosagraNevelese", pco.TanulokOnallosagraNevelese);
|
|
command.Parameters.Add("pTanulokTanulasszervezesiKompenzaciosTechnikajanakErositese", pco.TanulokTanulasszervezesiKompenzaciosTechnikajanakErositese);
|
|
command.Parameters.Add("pTanulokOrientaciosTamogatasa", pco.TanulokOrientaciosTamogatasa);
|
|
command.Parameters.Add("pTanulokSzocialitasanakFejlesztese", pco.TanulokSzocialitasanakFejlesztese);
|
|
command.Parameters.Add("pTanulokTovabbhaladasatElokeszitoTevekenysegek", pco.TanulokTovabbhaladasatElokeszitoTevekenysegek);
|
|
command.Parameters.Add("pPedagogusAltalJelzettEgyeniEsetkezeles", pco.PedagogusAltalJelzettEgyeniEsetkezeles);
|
|
command.Parameters.Add("pProjektmunkaTamogatasa", pco.ProjektmunkaTamogatasa);
|
|
command.Parameters.Add("pDigitalisKeszsegfejlesztes", pco.DigitalisKeszsegfejlesztes);
|
|
command.Parameters.Add("pKozossegiMunkaTamogatasa", pco.KozossegiMunkaTamogatasa);
|
|
command.Parameters.Add("pTanoraElokeszitesenekTamogatasa", pco.TanoraElokeszitesenekTamogatasa);
|
|
command.Parameters.Add("pTanoraLebonyolitasanakTamogatasa", pco.TanoraLebonyolitasanakTamogatasa);
|
|
command.Parameters.Add("pNapkozisTevekenysegKeretebenEgyeniFejlesztes", pco.NapkozisTevekenysegKeretebenEgyeniFejlesztes);
|
|
command.Parameters.Add("pDigPlatformTipusId", pco.DigPlatformTipusId);
|
|
command.Parameters.Add("pDigEszkozTipusId", pco.DigEszkozTipusId);
|
|
command.Parameters.Add("pDigTamEszkozTipus", SqlLogic.ParseListToParameter(pco.DigTamEszkozTipus));
|
|
|
|
var ds = new DataSet();
|
|
using (var adapter = new SDADataAdapter())
|
|
{
|
|
adapter.SelectCommand = command;
|
|
adapter.Fill(ds);
|
|
}
|
|
|
|
SetDNAME(ds.Tables[0], "HetNapja,CsopTip");
|
|
SetBoolFields(ds.Tables[0], "HFRogzitve,OnlineOra");
|
|
return ds;
|
|
}
|
|
}
|
|
|
|
public DataSet GetAdminTorlendoKozpontiOrakForDate(DateTime start, DateTime end, int hanyadikora)
|
|
{
|
|
List<CommandParameter> ParamList = new List<CommandParameter>
|
|
{
|
|
new CommandParameter("pHanyadikora", hanyadikora),
|
|
new CommandParameter("pStart", new DateTime(start.Year, start.Month, start.Day)),
|
|
new CommandParameter("pDayNumber", (int)start.DayOfWeek),
|
|
new CommandParameter("pOraKezdete", new DateTime(1900, 1, 1, start.Hour, start.Minute, start.Second)),
|
|
new CommandParameter("pOraVege", new DateTime(1900, 1, 1, end.Hour, end.Minute, end.Second))
|
|
,new CommandParameter("OktNevKatTipus", (int)OktNevelesiKategoriaEnum.NevelesOktatas)
|
|
};
|
|
|
|
const string CommandText = @"
|
|
SELECT
|
|
o.ID
|
|
,ocs.C_NEV AS OsztalyCsoport
|
|
,fh.C_NYOMTATASINEV AS Tanar
|
|
,IIF(fh.C_NEVSORREND = 'T',
|
|
fh.C_UTONEV + ' ' + fh.C_VEZETEKNEV,
|
|
fh.C_VEZETEKNEV + ' ' + fh.C_UTONEV) AS TanarElotagNelkul
|
|
,tantargy.C_NEV AS Tantargy
|
|
,t.C_NEV AS Terem
|
|
,ISNULL(cs.C_OSZTALYBONTASID, osz.ID) AS OsztalyID
|
|
FROM T_ORARENDIORA o
|
|
INNER JOIN T_DICTIONARYITEMBASE hn ON o.C_HETNAPJA = hn.ID
|
|
LEFT JOIN T_OSZTALYCSOPORT ocs ON o.C_OSZTALYCSOPORTID = ocs.ID
|
|
AND ocs.C_FELADATKATEGORIAID = @OktNevKatTipus
|
|
LEFT JOIN T_FELHASZNALO fh ON o.C_TANARID = fh.ID
|
|
LEFT JOIN T_TANTARGY tantargy ON tantargy.ID = o.C_TANTARGYID
|
|
LEFT JOIN T_TEREM t ON o.C_TEREMID = t.ID
|
|
LEFT JOIN T_TANITASIORA tOra ON tOra.C_ORARENDIORAID = o.ID
|
|
LEFT JOIN T_CSOPORT cs ON cs.ID = ocs.ID
|
|
LEFT JOIN T_OSZTALY osz ON osz.ID = ocs.ID
|
|
WHERE :pStart BETWEEN o.C_ORAERVENYESSEGKEZDETE AND o.C_ORAERVENYESSEGVEGE AND o.C_ORASZAM = :pHanyadikora
|
|
AND hn.C_VALUE = :pDayNumber AND o.C_ORAKEZDETE = :pOraKezdete AND o.C_ORAVEGE = :pOraVege";
|
|
|
|
return GetData(CommandText, ParamList);
|
|
}
|
|
|
|
public DataSet GetAdminRogzitendoKozpontiOrak()
|
|
{
|
|
List<CommandParameter> paramList = new List<CommandParameter>
|
|
{
|
|
new CommandParameter("OktNevKatTipus", (int)OktNevelesiKategoriaEnum.NevelesOktatas)
|
|
};
|
|
|
|
// Órarend speciális adatok módosításnál frissíteni kell
|
|
const string CommandText = @"
|
|
SELECT
|
|
ocs.ID
|
|
,ocs.C_NEV AS Osztaly
|
|
,ISNULL(IIF(f.C_OKTATASIAZONOSITO IS NULL, LTRIM(f.C_NYOMTATASINEV), LTRIM(f.C_NYOMTATASINEV) + ' (' + f.C_OKTATASIAZONOSITO + ')'), '') AS Tanar
|
|
,ISNULL(t.C_NEV, '') AS Terem
|
|
,'' AS Tantargy
|
|
,ISNULL(o.C_OSZTALYFONOKID, 0) AS TanarID
|
|
,0 AS TantargyID
|
|
,ISNULL(ocs.C_TEREMID, 0) AS TeremID
|
|
,CASE WHEN LEFT(ocs.C_NEV, 1) BETWEEN '0' AND '9' AND ocs.C_NEV LIKE '%[^0-9]%' THEN CAST(LEFT(SUBSTRING(ocs.C_NEV, 1, PATINDEX('%[^0-9]%', ocs.C_NEV) - 1),9) AS INT) ELSE 2147483647 END NumberAndTextOrder
|
|
,1 AS Sorszamozando
|
|
,0 AS ParhuzamosOra
|
|
,0 AS Tulora
|
|
,0 AS TAMOPOra
|
|
,0 AS NemSzakrendszeruOra
|
|
,0 AS KAPOra
|
|
,0 AS VEKOP73317
|
|
,0 AS KIPOra
|
|
,0 AS IKTTanora
|
|
,0 AS NemzetisegiOra
|
|
,0 AS MindennaposTestneveles
|
|
,0 AS IPRTanora
|
|
,0 AS Differencialt
|
|
,0 AS Kooperativ
|
|
,0 AS Meres
|
|
,0 AS Drama
|
|
,0 AS Tiop
|
|
,0 AS Bontott
|
|
,0 AS Multikulturalis
|
|
,0 AS EFOP32317
|
|
,0 AS KomplexOra
|
|
,0 AS GINOP623
|
|
,0 AS EFOP31716EselyteremtesAKoznevelesben
|
|
,0 AS EFOP33717
|
|
,0 AS TIOP11112120120001
|
|
,0 AS EFOP23517201700038Miapalya
|
|
FROM T_OSZTALYCSOPORT ocs
|
|
INNER JOIN T_OSZTALY o ON ocs.ID = o.ID
|
|
LEFT JOIN T_TEREM t ON ocs.C_TEREMID = t.ID
|
|
LEFT JOIN T_FELHASZNALO f ON o.C_OSZTALYFONOKID = f.ID
|
|
WHERE ocs.C_FELADATKATEGORIAID = @OktNevKatTipus
|
|
ORDER BY NumberAndTextOrder ASC, ocs.C_NEV ASC";
|
|
|
|
return GetData(CommandText, paramList);
|
|
}
|
|
|
|
public DataSet GetTanorakForTanarHaladasiNaplo(HaladasiNaploTanoraKeresesePCO pco, int? tanevId, int intezmenyId)
|
|
{
|
|
var whereClause = new StringBuilder();
|
|
var parameters = new List<CommandParameter>
|
|
{
|
|
new CommandParameter("ptanevId", tanevId),
|
|
new CommandParameter("pintezmenyId", intezmenyId),
|
|
new CommandParameter("pTanar", pco.Tanar)
|
|
};
|
|
|
|
if (pco.OraKezdete.HasValue && !string.IsNullOrWhiteSpace(pco.OraKezdete.Value.ToString()))
|
|
{
|
|
parameters.Add(new CommandParameter("pOraKezdete", pco.OraKezdete.Value));
|
|
whereClause.Append(" AND tora.C_ORAKEZDETE < :pOraKezdete ");
|
|
}
|
|
|
|
if (pco.Tol.HasValue && !string.IsNullOrWhiteSpace(pco.Tol.Value.ToString()))
|
|
{
|
|
parameters.Add(new CommandParameter("pTol", pco.Tol.Value));
|
|
whereClause.Append(" AND tora.C_DATUM >= CAST(:pTol as date) ");
|
|
}
|
|
|
|
if (pco.Ig.HasValue && !string.IsNullOrWhiteSpace(pco.Ig.Value.ToString()))
|
|
{
|
|
parameters.Add(new CommandParameter("pIg", pco.Ig.Value));
|
|
whereClause.Append(" AND tora.C_DATUM <= CAST(:pIg as date) ");
|
|
}
|
|
|
|
if (pco.OsztalyCsoportId.HasValue)
|
|
{
|
|
parameters.Add(new CommandParameter("pOsztcsop", pco.OsztalyCsoportId));
|
|
whereClause.Append(" AND tora.C_OSZTALYCSOPORTID = :pOsztcsop");
|
|
}
|
|
|
|
if (pco.TanarId.HasValue)
|
|
{
|
|
parameters.Add(new CommandParameter("pTanarId", pco.TanarId));
|
|
whereClause.Append(" AND otf.ID = :pTanarId");
|
|
}
|
|
|
|
if (pco.TantargyId.HasValue)
|
|
{
|
|
parameters.Add(new CommandParameter("pTantargyId", pco.TantargyId));
|
|
whereClause.Append(" AND tora.C_TANTARGYID = :pTantargyId");
|
|
}
|
|
|
|
if (pco.Helyetesitett.HasValue)
|
|
{
|
|
if (pco.Helyetesitett > 0)
|
|
{
|
|
whereClause.Append(" AND tora.C_HELYETTESITOTANARID IS NOT NULL");
|
|
}
|
|
else
|
|
{
|
|
whereClause.Append(" AND tora.C_HELYETTESITOTANARID IS NULL");
|
|
}
|
|
}
|
|
whereClause.Append(" AND (tora.C_HELYETTESITOTANARID = :pTanar OR tora.C_TANARID = :pTanar)");
|
|
|
|
var commandText = @"
|
|
SELECT
|
|
tora.ID
|
|
,tora.C_DATUM AS Datum
|
|
,tora.C_BEJEGYZESIDEJE AS NaplozasDatuma
|
|
,tora.C_OSZTALYCSOPORTID AS OsztCsop
|
|
,ocs.C_NEV AS OsztCsopNev
|
|
,tora.C_TANTARGYID AS Targy
|
|
,tgy.C_NEV AS TargyNev
|
|
,tora.C_TANARID AS Tanar
|
|
,tora.C_HELYETTESITOTANARID AS HTanar
|
|
,otf.C_NYOMTATASINEV AS TanarNev
|
|
,CASE
|
|
WHEN tora.C_HELYETTESITOTANARID IS NULL THEN 'F'
|
|
ELSE 'T'
|
|
END AS IsHelyetesitett
|
|
,IIF(tora.C_MEGTARTOTT = 'T', CAST(tora.C_ORASZAM AS nvarchar), 'Elmaradt óra') AS OraSorsz
|
|
,IIF(tora.C_MEGTARTOTT = 'T', tora.C_TEMA, 'Elmaradt óra') AS Tema
|
|
,tora.C_HETNAPJA AS HetNapja
|
|
,tora.C_EGYEDINAP as IsEgyediNap
|
|
,tora.C_ORAKEZDETE AS OraKezdete
|
|
FROM T_TANITASIORA tora
|
|
LEFT JOIN T_OSZTALYCSOPORT ocs on ocs.ID = tora.C_OSZTALYCSOPORTID
|
|
LEFT JOIN T_TANTARGY tgy on tgy.ID = tora.C_TANTARGYID
|
|
LEFT JOIN T_FELHASZNALO otf ON ISNULL(tora.C_HELYETTESITOTANARID, tora.C_TANARID) = otf.ID
|
|
WHERE tora.TOROLT = 'F'";
|
|
|
|
commandText += whereClause;
|
|
|
|
return GetData(commandText, parameters, dictionaryItemColumns: "HetNapja", booleanColumns: "IsHelyetesitett,IsEgyediNap");
|
|
}
|
|
|
|
public DataSet getTanuloTanorai(int felhasznaloId)
|
|
{
|
|
List<CommandParameter> ParamList = new List<CommandParameter>(); /// TODO(@DevKornél): mehetnének list-be object initializerrel
|
|
ParamList.Add(new CommandParameter("pTanuloId", felhasznaloId));
|
|
|
|
string CommandText = @"select
|
|
T_TANITASIORA.ID ID,
|
|
T_TANITASIORA.C_DATUM Datum,
|
|
T_TANITASIORA.C_OSZTALYCSOPORTID OsztCsop,
|
|
T_OSZTALYCSOPORT.C_NEV OsztCsopNev,
|
|
T_OSZTALYCSOPORT.C_EVFOLYAMTIPUSA Evfolyam,
|
|
T_CSOPORT.C_TIPUSA CsopTip,
|
|
T_TANITASIORA.C_FOGLALKOZASID Fogl,
|
|
T_TANITASIORA.C_TANTARGYID Targy,
|
|
T_TANTARGY.C_NEV TargyNev,
|
|
Tanar =
|
|
case
|
|
when T_TANITASIORA.C_HELYETTESITOTANARID is not null then T_TANITASIORA.C_HELYETTESITOTANARID
|
|
else (T_TANITASIORA.C_TANARID)
|
|
end,
|
|
TanarNev =
|
|
case
|
|
when T_TANITASIORA.C_HELYETTESITOTANARID is not null then (select C_NYOMTATASINEV from T_FELHASZNALO where id = T_TANITASIORA.C_HELYETTESITOTANARID)
|
|
else (select C_NYOMTATASINEV from T_FELHASZNALO where id = T_TANITASIORA.C_TANARID)
|
|
end,
|
|
TanarNevElotagNelkul =
|
|
case
|
|
when T_TANITASIORA.C_HELYETTESITOTANARID is not null then
|
|
(select IIF(T_FELHASZNALO.C_NEVSORREND = 'T',
|
|
T_FELHASZNALO.C_UTONEV + ' ' + T_FELHASZNALO.C_VEZETEKNEV,
|
|
T_FELHASZNALO.C_VEZETEKNEV + ' ' + T_FELHASZNALO.C_UTONEV)
|
|
from T_FELHASZNALO where id = T_TANITASIORA.C_HELYETTESITOTANARID)
|
|
else (select IIF(T_FELHASZNALO.C_NEVSORREND = 'T',
|
|
T_FELHASZNALO.C_UTONEV + ' ' + T_FELHASZNALO.C_VEZETEKNEV,
|
|
T_FELHASZNALO.C_VEZETEKNEV + ' ' + T_FELHASZNALO.C_UTONEV)
|
|
from T_FELHASZNALO where id = T_TANITASIORA.C_TANARID)
|
|
end,
|
|
T_TANITASIORA.C_TEREMID Hely,
|
|
T_TEREM.C_NEV HelyNev,
|
|
T_TANITASIORA.C_ORASZAM OraSorsz,
|
|
T_TANITASIORA.C_TEMA Tema,
|
|
T_TANITASIORA.C_HETNAPJA HetNapja
|
|
FROM (SELECT [ID]
|
|
,[C_OSZTALYCSOPORTID]
|
|
,[C_TANULOID]
|
|
FROM [T_TANULOCSOPORT] where TOROLT = 'F'
|
|
and C_BELEPESDATUM < GETDATE()
|
|
and (C_KILEPESDATUM is null or C_KILEPESDATUM >= GETDATE())
|
|
and C_TANULOID = :pTanuloId) TanuloCsoport
|
|
left join (select
|
|
T_TANITASIORA.ID,
|
|
T_TANITASIORA.C_DATUM,
|
|
T_TANITASIORA.C_OSZTALYCSOPORTID,
|
|
T_TANITASIORA.C_FOGLALKOZASID,
|
|
T_TANITASIORA.C_TANTARGYID,
|
|
T_TANITASIORA.C_HELYETTESITOTANARID,
|
|
T_TANITASIORA.C_TANARID,
|
|
T_TANITASIORA.C_TEREMID,
|
|
T_TANITASIORA.C_ORASZAM,
|
|
T_TANITASIORA.C_TEMA,
|
|
T_TANITASIORA.C_ORARENDIORAGROUPID,
|
|
T_TANITASIORA.C_HETNAPJA
|
|
from T_TANITASIORA
|
|
where T_TANITASIORA.TOROLT = 'F' )
|
|
T_TANITASIORA on T_TANITASIORA.C_OSZTALYCSOPORTID = TanuloCsoport.C_OSZTALYCSOPORTID
|
|
left join (select T_CSOPORT.ID, T_CSOPORT.C_TIPUSA
|
|
from T_CSOPORT
|
|
INNER JOIN T_OSZTALYCSOPORT OsztalyCsoport ON OsztalyCsoport.ID = T_CSOPORT.ID
|
|
where T_CSOPORT.TOROLT = 'F' )T_CSOPORT on T_CSOPORT.ID = TanuloCsoport.C_OSZTALYCSOPORTID
|
|
left join (select ID, C_NEV, C_EVFOLYAMTIPUSA from T_OSZTALYCSOPORT where TOROLT = 'F' )T_OSZTALYCSOPORT on T_OSZTALYCSOPORT.ID = T_TANITASIORA.C_OSZTALYCSOPORTID
|
|
left join (select ID, C_NEV from T_TANTARGY where TOROLT = 'F' )T_TANTARGY on T_TANTARGY.ID = T_TANITASIORA.C_TANTARGYID
|
|
left join (select ID, C_NEV from T_TEREM where TOROLT = 'F' )T_TEREM on T_TEREM.ID = T_TANITASIORA.C_TEREMID
|
|
where T_TANITASIORA.ID is not null
|
|
";
|
|
|
|
DataSet ds = GetData(CommandText, ParamList, dictionaryItemColumns: "HetNapja,CsopTip,Evfolyam");
|
|
return ds;
|
|
}
|
|
|
|
public DataSet GetTanulasiEgysegDataSet(int tanevId)
|
|
{
|
|
const string CommandText = @"
|
|
SELECT
|
|
ID
|
|
,C_SORSZAM
|
|
,C_SZAKMAIRANYTIPUSID
|
|
,C_SZAKMATIPUSID
|
|
,C_SZOVEG
|
|
FROM T_TANULASIEGYSEG_OSSZES
|
|
WHERE C_TANEVID = :ptanevId AND TOROLT = 'F'";
|
|
|
|
var param = new List<CommandParameter> {
|
|
new CommandParameter("ptanevId", tanevId)
|
|
};
|
|
|
|
DataSet ds = GetData(CommandText, param);
|
|
return ds;
|
|
}
|
|
|
|
|
|
public bool TanitasiOraTeremUtkozesEllenorzes(int intezmenyId, int tanitasiOraID, int orarendiOraID, int teremID, int hetNapja, int hetirend, DateTime oraKezdete, DateTime oraVege)
|
|
{
|
|
var paramlist = new List<CommandParameter>(); /// TODO(@DevKornél): mehetnének list-be object initializerrel
|
|
paramlist.Add(new CommandParameter("pIntezmenyId", intezmenyId));
|
|
paramlist.Add(new CommandParameter("pTanitasiOraID", tanitasiOraID));
|
|
paramlist.Add(new CommandParameter("pOrarendiOraID", orarendiOraID));
|
|
paramlist.Add(new CommandParameter("pTeremID", teremID));
|
|
paramlist.Add(new CommandParameter("pHetNapja", hetNapja));
|
|
paramlist.Add(new CommandParameter("pHetirend", hetirend));
|
|
paramlist.Add(new CommandParameter("pHetirendMindenhet", (int)HetiRendTipusEnum.MindegyikHet));
|
|
paramlist.Add(new CommandParameter("pOraKezdeteDate", oraKezdete));
|
|
paramlist.Add(new CommandParameter("pOraKezdeteTime", oraKezdete.ToLongTimeString()));
|
|
paramlist.Add(new CommandParameter("pOraVegeDate", oraVege));
|
|
paramlist.Add(new CommandParameter("pOraVegeTime", oraVege.ToLongTimeString()));
|
|
|
|
var commandText = @"SELECT
|
|
oo.ID
|
|
FROM T_ORARENDIORA oo
|
|
INNER JOIN T_TEREM t ON oo.C_TEREMID = t.ID
|
|
INNER JOIN T_OSZTALYCSOPORT_OSSZES ocs ON ocs.ID = oo.C_OSZTALYCSOPORTID AND ocs.TOROLT = 'F'
|
|
LEFT JOIN T_TANEVRENDJE_OSSZES tr ON
|
|
tr.C_TANEVID = oo.C_TANEVID
|
|
AND tr.C_INTEZMENYID = oo.C_INTEZMENYID
|
|
AND tr.C_NAPTIPUSA = 1402
|
|
AND tr.TOROLT = 'F'
|
|
WHERE
|
|
(t.C_TOBBORATLEHETTARTANI IS NULL OR t.C_TOBBORATLEHETTARTANI = 'F')
|
|
AND oo.TOROLT = 'F'
|
|
AND oo.C_INTEZMENYID = :pIntezmenyId
|
|
AND oo.C_TEREMID = :pTeremID
|
|
AND oo.ID <> :pOrarendiOraID
|
|
AND (oo.C_HETIREND = :pHetirend OR oo.C_HETIREND = :pHetirendMindenhet OR :pHetirend = :pHetirendMindenhet)
|
|
AND oo.C_HETNAPJA = :pHetNapja
|
|
AND CAST(:pOraKezdeteDate AS DATE) BETWEEN oo.C_ORAERVENYESSEGKEZDETE AND IIF(ocs.C_VEGZOSEVFOLYAM = 'T' AND oo.C_ORAERVENYESSEGVEGE > tr.C_DATUM, tr.C_DATUM, oo.C_ORAERVENYESSEGVEGE)
|
|
AND CAST(:pOraKezdeteDate AS TIME) BETWEEN CAST(oo.C_ORAKEZDETE AS TIME) AND CAST(oo.C_ORAKEZDETE AS TIME)
|
|
|
|
UNION
|
|
|
|
SELECT
|
|
T_TANITASIORA.ID
|
|
FROM T_TANITASIORA
|
|
INNER JOIN T_TEREM ON T_TANITASIORA.C_TEREMID = T_TEREM.ID
|
|
WHERE
|
|
(T_TEREM.C_TOBBORATLEHETTARTANI IS NULL OR T_TEREM.C_TOBBORATLEHETTARTANI = 'F')
|
|
AND T_TEREM.C_INTEZMENYID = :pIntezmenyId
|
|
AND T_TANITASIORA.C_TEREMID = :pTeremID
|
|
AND T_TANITASIORA.ID <> :pTanitasiOraID
|
|
AND C_HETNAPJA = :pHetNapja
|
|
AND (:pOraKezdeteDate < T_TANITASIORA.C_ORAVEGE AND :pOraVegeDate > T_TANITASIORA.C_ORAKEZDETE)
|
|
AND T_TANITASIORA.TOROLT = 'F'";
|
|
|
|
DataSet ds = GetData(commandText, paramlist);
|
|
return ds.Tables[0].Rows.Count > 0;
|
|
}
|
|
|
|
public DataSet GetOsztalyCsoportMegtartottOrai(int ID, int? tanevId, int intezmenyId)
|
|
{
|
|
const string CommandText = @"
|
|
SELECT
|
|
tOra.C_ORAEVESSORSZAMA AS OraSorszam
|
|
,tOra.ID AS ID
|
|
,tOra.C_ORASZAM AS Oraszam
|
|
,tantargy.C_NEV AS Tantargy
|
|
,tOra.C_TEMA AS Tema
|
|
,tOra.C_DATUM AS Datum
|
|
,tOra.C_HETNAPJA AS Hetnapja
|
|
FROM T_OSZTALYCSOPORT_OSSZES osz
|
|
INNER JOIN T_TANITASIORA_OSSZES tOra ON osz.ID = tOra.C_OSZTALYCSOPORTID AND tOra.TOROLT = 'F' AND tOra.C_TANEVID = osz.C_TANEVID AND tora.C_MEGTARTOTT = 'T'
|
|
INNER JOIN T_TANTARGY_OSSZES tantargy ON tOra.C_TANTARGYID = tantargy.ID AND tantargy.TOROLT = 'F' AND tantargy.C_TANEVID = tOra.C_TANEVID
|
|
WHERE osz.ID = :pOsztalyCsoportID AND osz.TOROLT = 'F' AND osz.C_TANEVID = :ptanevId";
|
|
|
|
var param = new List<CommandParameter> {
|
|
new CommandParameter("pOsztalyCsoportID", ID),
|
|
new CommandParameter("ptanevId", tanevId),
|
|
new CommandParameter("pintezmenyId", intezmenyId)
|
|
};
|
|
|
|
DataSet ds = GetData(CommandText, param);
|
|
return ds;
|
|
}
|
|
|
|
public DataSet GetMegtartottOraByDatumAndOsztalyFonok(DateTime datum, int ofoId)
|
|
{
|
|
List<CommandParameter> p = new List<CommandParameter>(); /// TODO(@DevKornél): mehetnének list-be object initializerrel
|
|
p.Add(new CommandParameter("pDatum", datum));
|
|
p.Add(new CommandParameter("pOfoId", ofoId));
|
|
p.Add(new CommandParameter("OktNevKatTipus", (int)OktNevelesiKategoriaEnum.NevelesOktatas));
|
|
|
|
var command = @"select
|
|
T_TANITASIORA.ID,
|
|
Nev =
|
|
case
|
|
when T_TANITASIORA.C_HELYETTESITOTANARID is null then cast(T_TANITASIORA.C_ORASZAM as nvarchar) + ' - ' + T_TANTARGY.C_NEV + ' - ' + TANAR.C_NYOMTATASINEV
|
|
else cast(T_TANITASIORA.C_ORASZAM as nvarchar) + ' - ' + T_TANTARGY.C_NEV + ' - ' + HELYETTES.C_NYOMTATASINEV
|
|
end
|
|
from T_TANITASIORA
|
|
left join T_FOGLALKOZAS on T_FOGLALKOZAS.ID = T_TANITASIORA.C_FOGLALKOZASID and T_FOGLALKOZAS.TOROLT = 'F'
|
|
left join T_TANTARGY on T_TANTARGY.ID = T_FOGLALKOZAS.C_TANTARGYID and T_TANTARGY.TOROLT = 'F'
|
|
left join T_FELHASZNALO TANAR on TANAR.ID = T_TANITASIORA.C_ORATULAJDONOSID and TANAR.TOROLT = 'F'
|
|
left join T_FELHASZNALO HELYETTES on HELYETTES.ID = T_TANITASIORA.C_HELYETTESITOTANARID and HELYETTES.TOROLT = 'F'
|
|
left join T_OSZTALY on T_OSZTALY.ID = T_TANITASIORA.C_OSZTALYCSOPORTID and T_OSZTALY.TOROLT = 'F'
|
|
INNER JOIN T_OSZTALYCSOPORT OsztalyCsoport ON OsztalyCsoport.ID = T_OSZTALY.ID AND OsztalyCsoport.C_FELADATKATEGORIAID = @OktNevKatTipus
|
|
where T_TANITASIORA.TOROLT = 'F'
|
|
and T_TANITASIORA.C_MEGTARTOTT = 'T'
|
|
and T_TANITASIORA.C_DATUM = :pDatum
|
|
and (T_OSZTALY.C_OSZTALYFONOKID = :pOfoId or T_OSZTALY.C_OFOHELYETTESID = :pOfoId)";
|
|
|
|
return this.GetData(command, p);
|
|
}
|
|
|
|
public DataSet GetHelyettesitettTanorakForExport(int tanevId, string schema, TanoraKeresesePCO pco)
|
|
{
|
|
return GetTanorakForExport(tanevId, schema, pco, TanorakExportTipusEnum.Helyettesitett);
|
|
}
|
|
|
|
public DataSet GetElmaradtTanorakForExport(int tanevId, string schema, TanoraKeresesePCO pco)
|
|
{
|
|
return GetTanorakForExport(tanevId, schema, pco, TanorakExportTipusEnum.Elmaradt);
|
|
}
|
|
|
|
public int GetOrarendiOraByTanoraGroupId(int groupId, DateTime date)
|
|
{
|
|
List<CommandParameter> ParamList = new List<CommandParameter>(); /// TODO(@DevKornél): mehetnének list-be object initializerrel
|
|
ParamList.Add(new CommandParameter("pGroupId", groupId));
|
|
ParamList.Add(new CommandParameter("pDate", date));
|
|
|
|
string CommandText = @"
|
|
SELECT
|
|
ISNULL(ID, 0) AS ooId
|
|
FROM T_ORARENDIORA
|
|
WHERE
|
|
TOROLT='F'
|
|
AND C_ORARENDIORAGROUPID =:pGroupId
|
|
AND ((C_ORAERVENYESSEGKEZDETE <=:pDate AND C_ORAERVENYESSEGVEGE > :pDate) OR (C_ORAERVENYESSEGKEZDETE = C_ORAERVENYESSEGVEGE AND C_ORAERVENYESSEGKEZDETE = :pDate))
|
|
ORDER BY IIF(C_EGYEDINAP = 'T', 1, 0) DESC
|
|
";
|
|
|
|
DataSet ds = GetData(CommandText, ParamList);
|
|
return ds.Tables[0].Rows.Count > 0 ? Convert.ToInt32(ds.Tables[0].Rows[0]["ooId"]) : -1;
|
|
}
|
|
|
|
public int GetTanoraByOrarendiOraGroupId(int groupId, DateTime date)
|
|
{
|
|
List<CommandParameter> ParamList = new List<CommandParameter>();
|
|
ParamList.Add(new CommandParameter("pGroupId", groupId));
|
|
ParamList.Add(new CommandParameter("pDate", date));
|
|
|
|
string CommandText = @"
|
|
SELECT
|
|
ISNULL(ID, 0) AS Id
|
|
FROM T_TANITASIORA
|
|
WHERE
|
|
TOROLT='F'
|
|
AND C_ORARENDIORAGROUPID =:pGroupId
|
|
AND C_DATUM =:pDate
|
|
";
|
|
|
|
DataSet ds = GetData(CommandText, ParamList);
|
|
return ds.Tables[0].Rows.Count == 1 ? Convert.ToInt32(ds.Tables[0].Rows[0]["Id"]) : -1;
|
|
}
|
|
|
|
public void Delete(ITanitasiOra dto)
|
|
{
|
|
var oralatogatasDal = DalHelper.OralatogatasDal();
|
|
var entity = dto as TanitasiOra;
|
|
entity.Ertekelesek.DeleteAll(); // értékelések
|
|
entity.OraTanuloi.DeleteAll();
|
|
foreach (var oralatogatas in entity.Oralatogatasok)
|
|
{
|
|
oralatogatasDal.Delete(entity.ID, oralatogatas.ErtekeloId);
|
|
}
|
|
entity.TanitasiOraTulajdonsag.DeleteAll();
|
|
|
|
entity.FeltoltottFajl.RemoveAll();
|
|
|
|
foreach (var item in entity.TanuloEsemeny)
|
|
{
|
|
item.Tanulo.RemoveAll();
|
|
}
|
|
entity.TanuloEsemeny.DeleteAll();
|
|
entity.TanitasiOraDigTamEszkoz.DeleteAll();
|
|
entity.OraFile.DeleteAll();
|
|
|
|
entity.Delete();
|
|
|
|
DalHelper.Commit();
|
|
}
|
|
|
|
public void Delete(int id)
|
|
{
|
|
var entity = Get(id);
|
|
Delete(entity);
|
|
}
|
|
|
|
public void Insert(ITanitasiOra dto)
|
|
{
|
|
var entity = SetKAPOra(dto) as TanitasiOra;
|
|
entity.Insert();
|
|
|
|
dto.ID = entity.ID;
|
|
DalHelper.Commit();
|
|
}
|
|
|
|
public void Update(ITanitasiOra dto)
|
|
{
|
|
var entity = SetKAPOra(dto) as TanitasiOra;
|
|
entity.Update();
|
|
DalHelper.Commit();
|
|
}
|
|
|
|
public void FullUpdate(ITanitasiOra dto)
|
|
{
|
|
var entity = SetKAPOra(dto) as TanitasiOra;
|
|
entity.FullUpdate();
|
|
DalHelper.Commit();
|
|
}
|
|
|
|
private ITanitasiOra SetKAPOra(ITanitasiOra tanitasiOra)
|
|
{
|
|
tanitasiOra.KAPOra = tanitasiOra.IsDfhtOra
|
|
|| tanitasiOra.IsRahangolodas
|
|
|| tanitasiOra.IsTestmozgasAlapuAlprogram
|
|
|| tanitasiOra.IsMuveszetAlapuAlprogram
|
|
|| tanitasiOra.KIPOra
|
|
|| tanitasiOra.KomplexOra
|
|
|| tanitasiOra.IsEletgyakorlatAlapuAlprogram
|
|
|| tanitasiOra.IsLogikaAlapuAlprogram
|
|
|| tanitasiOra.IsTeorad
|
|
|| tanitasiOra.IsDigitalisAlapuAlprogram;
|
|
return tanitasiOra;
|
|
}
|
|
|
|
/// INFO @DevKornel: Mobil használja
|
|
public DataSet GetTanuloFeljegyzesek(int tanoraId)
|
|
{
|
|
using (SDACommand command = new SDACommand())
|
|
{
|
|
command.Connection = UserContext.Instance.SDAConnection;
|
|
command.Transaction = UserContext.Instance.SDATransaction;
|
|
command.CommandType = CommandType.StoredProcedure;
|
|
command.CommandText = "sp_GetTanuloFeljegyzesList";
|
|
|
|
command.Parameters.Add("tanoraId", tanoraId);
|
|
|
|
var dts = new DataSet();
|
|
using (var adapter = new SDADataAdapter())
|
|
{
|
|
adapter.SelectCommand = command;
|
|
adapter.Fill(dts);
|
|
}
|
|
DataTable dt = SortingAndPaging(dts.Tables[0], GridParameters);
|
|
|
|
return dt.AsDataSet();
|
|
}
|
|
}
|
|
|
|
/// INFO @DevKornel: Mobil használja
|
|
public DataSet GetTanuloMulasztasok(int tanoraId)
|
|
{
|
|
using (SDACommand command = new SDACommand())
|
|
{
|
|
command.Connection = UserContext.Instance.SDAConnection;
|
|
command.Transaction = UserContext.Instance.SDATransaction;
|
|
command.CommandType = CommandType.StoredProcedure;
|
|
command.CommandText = "sp_GetTanuloMulasztasList";
|
|
|
|
command.Parameters.Add("tanoraId", tanoraId);
|
|
|
|
var ds = new DataSet();
|
|
using (var adapter = new SDADataAdapter())
|
|
{
|
|
adapter.SelectCommand = command;
|
|
adapter.Fill(ds);
|
|
}
|
|
|
|
DataTable dt = SortingAndPaging(ds.Tables[0], GridParameters);
|
|
|
|
return dt.AsDataSet();
|
|
}
|
|
}
|
|
|
|
public void UpdateOrarendiOraGroupIds(int oOraId, int? oOraGroupId, DateTime newErvenyessegKezdete, DateTime newErvenyessegVege, DateTime modositasDatuma, int tipus, int modifier)
|
|
{
|
|
using (SDACommand command = new SDACommand())
|
|
{
|
|
command.Connection = UserContext.Instance.SDAConnection;
|
|
command.Transaction = UserContext.Instance.SDATransaction;
|
|
command.CommandType = CommandType.StoredProcedure;
|
|
command.CommandText = "uspUpdateTanitasiOraOrarendiOraGroupIds";
|
|
|
|
command.Parameters.Add("pOrarendiOraId", oOraId);
|
|
command.Parameters.Add("pOrarendiOraGroupId", oOraGroupId);
|
|
command.Parameters.Add("pModifier", modifier);
|
|
switch (tipus)
|
|
{
|
|
case (int)OraModositasiIdoszakTipus.TeljesIdoszak:
|
|
command.Parameters.Add("pDatumtol", newErvenyessegKezdete);
|
|
command.Parameters.Add("pDatumig", newErvenyessegVege);
|
|
break;
|
|
case (int)OraModositasiIdoszakTipus.AdottNapig:
|
|
command.Parameters.Add("pDatumtol", newErvenyessegKezdete);
|
|
command.Parameters.Add("pDatumig", modositasDatuma.Date);
|
|
break;
|
|
case (int)OraModositasiIdoszakTipus.AdottNaptol:
|
|
command.Parameters.Add("pDatumtol", modositasDatuma.Date);
|
|
command.Parameters.Add("pDatumig", newErvenyessegVege);
|
|
break;
|
|
case (int)OraModositasiIdoszakTipus.EgyOra:
|
|
command.Parameters.Add("pDatumtol", modositasDatuma.Date);
|
|
command.Parameters.Add("pDatumig", DBNull.Value);
|
|
break;
|
|
}
|
|
|
|
command.ExecuteNonQuery();
|
|
|
|
DalHelper.Commit();
|
|
}
|
|
}
|
|
|
|
public DataSet GetMegtartottTanorakForExport(int tanevId, string schema, TanoraKeresesePCO pco, bool isShowId = false)
|
|
{
|
|
return GetTanorakForExport(tanevId, schema, pco, TanorakExportTipusEnum.Megtartott, isShowId);
|
|
}
|
|
|
|
/// TODO(@DevKornél): schema string concat-al foglalkozni kell, url escape minimum
|
|
private DataSet GetTanorakForExport(int tanevId, string schema, TanoraKeresesePCO pco, TanorakExportTipusEnum tanorakExportTipus, bool isShowId = false)
|
|
{
|
|
StringBuilder clause = new StringBuilder();
|
|
StringBuilder clauseExecN = new StringBuilder();
|
|
StringBuilder clauseExec = new StringBuilder();
|
|
var fromClause = tanorakExportTipus != TanorakExportTipusEnum.Helyettesitett
|
|
? @"INNER JOIN T_FELHASZNALO beirotanar ON tanora.C_TANARID = beirotanar.ID AND beirotanar.C_INTEZMENYID = tanora.C_INTEZMENYID
|
|
LEFT JOIN T_ORARENDIORA eredetiora ON tanora.C_ORARENDIORAGROUPID = eredetiora.C_ORARENDIORAGROUPID
|
|
AND tanora.C_DATUM BETWEEN eredetiora.C_ORAERVENYESSEGKEZDETE AND eredetiora.C_ORAERVENYESSEGVEGE
|
|
AND eredetiora.C_INTEZMENYID = tanora.C_INTEZMENYID
|
|
LEFT JOIN T_FELHASZNALO eredetitanar ON eredetiora.C_TANARID = eredetitanar.ID AND eredetitanar.C_INTEZMENYID = tanora.C_INTEZMENYID
|
|
LEFT JOIN T_FELHASZNALO helyettes ON tanora.C_HELYETTESITOTANARID = helyettes.ID AND helyettes.C_INTEZMENYID = tanora.C_INTEZMENYID"
|
|
: @"INNER JOIN T_FELHASZNALO helyettes ON helyettes.ID = tanora.C_HELYETTESITOTANARID AND helyettes.C_INTEZMENYID = tanora.C_INTEZMENYID
|
|
LEFT JOIN T_FELHASZNALO eredetitanar ON IIF(tanora.C_ORATULAJDONOSID IS NULL OR tanora.C_ORATULAJDONOSID = tanora.C_HELYETTESITOTANARID, tanora.C_TANARID , tanora.C_ORATULAJDONOSID) = eredetitanar.ID AND eredetitanar.C_INTEZMENYID = tanora.C_INTEZMENYID";
|
|
|
|
var showId = isShowId ? ",OsztalyCsoportId, TanoraId, TantargyId, EredetiTanarId, HelyettesId, BeiroTanarId, TeremId" : string.Empty;
|
|
|
|
if (pco.Tol.HasValue && !string.IsNullOrWhiteSpace(pco.Tol.Value.ToString()))
|
|
{
|
|
clause.Append(" AND tanora.C_DATUM >= cast(@Tol as date) ");
|
|
|
|
clauseExecN.Append(" ,@Tol datetime");
|
|
clauseExec.Append(" ,@Tol = '").Append(pco.Tol.Value.ToShortDateString().Replace(".", "").Replace(" ", "")).Append("'");
|
|
}
|
|
if (pco.Ig.HasValue && !string.IsNullOrWhiteSpace(pco.Ig.Value.ToString()))
|
|
{
|
|
clause.Append(" AND tanora.C_DATUM <= cast(@Ig as date) ");
|
|
|
|
clauseExecN.Append(" ,@Ig datetime");
|
|
clauseExec.Append(" ,@Ig = '").Append(pco.Ig.Value.ToShortDateString().Replace(".", "").Replace(" ", "")).Append("'");
|
|
}
|
|
if (pco.OsztCsop.HasValue && !string.IsNullOrWhiteSpace(pco.OsztCsop.Value.ToString()))
|
|
{
|
|
if (pco.KellKapcsolodoCsoportok)
|
|
{
|
|
clause.Append(" AND tanora.C_OSZTALYCSOPORTID in (SELECT ID FROM fnGetKapcsolodoOsztalycsoportok(@Osztcsop))");
|
|
}
|
|
else
|
|
{
|
|
clause.Append(" AND tanora.C_OSZTALYCSOPORTID = @Osztcsop");
|
|
}
|
|
|
|
clauseExecN.Append(" ,@Osztcsop int");
|
|
clauseExec.Append(" ,@Osztcsop = ").Append(pco.OsztCsop);
|
|
}
|
|
if (pco.CsopTip.HasValue && !string.IsNullOrWhiteSpace(pco.CsopTip.Value.ToString()))
|
|
{
|
|
clause.Append(" AND csoport.C_TIPUSA = @Csoptip ");
|
|
|
|
clauseExecN.Append(" ,@Csoptip int");
|
|
clauseExec.Append(" ,@Csoptip = ").Append(pco.CsopTip);
|
|
}
|
|
if (pco.Fogl.HasValue && !string.IsNullOrWhiteSpace(pco.Fogl.Value.ToString()))
|
|
{
|
|
clause.Append(" AND tanora.C_FOGLALKOZASID = @Fogl");
|
|
|
|
clauseExecN.Append(" ,@Fogl int");
|
|
clauseExec.Append(" ,@Fogl = ").Append(pco.Fogl);
|
|
}
|
|
if (pco.Targy.HasValue && !string.IsNullOrWhiteSpace(pco.Targy.Value.ToString()))
|
|
{
|
|
clause.Append(" AND tanora.C_TANTARGYID = @Targy");
|
|
|
|
clauseExecN.Append(" ,@Targy int");
|
|
clauseExec.Append(" ,@Targy = ").Append(pco.Targy);
|
|
}
|
|
if (pco.Tanar.HasValue && !string.IsNullOrWhiteSpace(pco.Tanar.Value.ToString()))
|
|
{
|
|
clause.Append(" AND ISNULL(tanora.C_HELYETTESITOTANARID, tanora.C_TANARID) = @Tanar ");
|
|
|
|
clauseExecN.Append(" ,@Tanar int");
|
|
clauseExec.Append(" ,@Tanar = ").Append(pco.Tanar);
|
|
}
|
|
if (pco.Terem.HasValue && !string.IsNullOrWhiteSpace(pco.Terem.Value.ToString()))
|
|
{
|
|
clause.Append(" AND tanora.C_TEREMID = @Terem");
|
|
|
|
clauseExecN.Append(" ,@Terem int");
|
|
clauseExec.Append(" ,@Terem = ").Append(pco.Terem);
|
|
}
|
|
if (pco.FeladatEllatasiHely.HasValue && !string.IsNullOrWhiteSpace(pco.FeladatEllatasiHely.Value.ToString()))
|
|
{
|
|
clause.Append(" AND osztalycsoport.C_FELADATELLATASIHELYID = @FeladatEllatasiHely");
|
|
|
|
clauseExecN.Append(" ,@FeladatEllatasiHely int");
|
|
clauseExec.Append(" ,@FeladatEllatasiHely = ").Append(pco.FeladatEllatasiHely);
|
|
}
|
|
if (pco.FeladatKategoria.HasValue)
|
|
{
|
|
clause.Append(" AND osztalycsoport.C_FELADATKATEGORIAID = @OktNevKatTipus");
|
|
|
|
clauseExecN.Append(" ,@OktNevKatTipus int");
|
|
clauseExec.Append(" ,@OktNevKatTipus = ").Append(pco.FeladatKategoria.Value);
|
|
}
|
|
if (pco.Elmaradt.HasValue)
|
|
{
|
|
clause.Append(" AND tanora.C_MEGTARTOTT = @Elmaradt");
|
|
|
|
clauseExecN.Append(" ,@Elmaradt char(1)");
|
|
clauseExec.Append(" ,@Elmaradt = ").Append(pco.Elmaradt > 0 ? 'F' : 'T');
|
|
}
|
|
if (pco.Sorszamozando.HasValue)
|
|
{
|
|
clause.Append(" AND tanora.C_SORSZAMOZANDO = @Sorszamozando");
|
|
|
|
clauseExecN.Append(" ,@Sorszamozando char(1)");
|
|
clauseExec.Append(" ,@Sorszamozando = ").Append(pco.Sorszamozando > 0 ? 'F' : 'T');
|
|
}
|
|
if (pco.ParhuzamosOra.HasValue)
|
|
{
|
|
clause.Append(" AND tanora.C_PARHUZAMOSORA = @ParhuzamosOra");
|
|
|
|
clauseExecN.Append(" ,@ParhuzamosOra char(1)");
|
|
clauseExec.Append(" ,@ParhuzamosOra = ").Append(pco.ParhuzamosOra > 0 ? 'F' : 'T');
|
|
}
|
|
if (pco.Tulora.HasValue)
|
|
{
|
|
clause.Append(" AND tanora.C_TULORA = @Tulora");
|
|
|
|
clauseExecN.Append(" ,@Tulora char(1)");
|
|
clauseExec.Append(" ,@Tulora = ").Append(pco.Tulora > 0 ? 'F' : 'T');
|
|
}
|
|
if (pco.TAMOPOra.HasValue)
|
|
{
|
|
clause.Append(" AND tanora.C_TAMOPORA = @TAMOPOra");
|
|
|
|
clauseExecN.Append(" ,@TAMOPOra char(1)");
|
|
clauseExec.Append(" ,@TAMOPOra = ").Append(pco.TAMOPOra > 0 ? 'F' : 'T');
|
|
}
|
|
if (pco.KIPOra.HasValue)
|
|
{
|
|
clause.Append(" AND tanora.C_KIPORA = @KIPOra");
|
|
|
|
clauseExecN.Append(" ,@KIPOra char(1)");
|
|
clauseExec.Append(" ,@KIPOra = ").Append(pco.KIPOra > 0 ? 'F' : 'T');
|
|
}
|
|
if (pco.VEKOP73317.HasValue)
|
|
{
|
|
clause.Append(" AND tanora.C_VEKOP73317 = @VEKOP73317");
|
|
|
|
clauseExecN.Append(" ,@VEKOP73317 char(1)");
|
|
clauseExec.Append(" ,@VEKOP73317 = ").Append(pco.VEKOP73317 > 0 ? 'F' : 'T');
|
|
}
|
|
if (pco.KAPOra.HasValue)
|
|
{
|
|
clause.Append(" AND tanora.C_KAPORA = @KAPOra");
|
|
|
|
clauseExecN.Append(" ,@KAPOra char(1)");
|
|
clauseExec.Append(" ,@KAPOra = ").Append(pco.KAPOra > 0 ? 'F' : 'T');
|
|
}
|
|
if (pco.MultikulturalisOra.HasValue)
|
|
{
|
|
clause.Append(" AND tanora.C_MULTIKULTURALISORA = @MultikulturalisOra");
|
|
|
|
clauseExecN.Append(" ,@MultikulturalisOra char(1)");
|
|
clauseExec.Append(" ,@MultikulturalisOra = ").Append(pco.MultikulturalisOra > 0 ? 'F' : 'T');
|
|
}
|
|
if (pco.KomplexOra.HasValue)
|
|
{
|
|
clause.Append(" AND tanora.C_KOMPLEXORA = @KomplexOra");
|
|
|
|
clauseExecN.Append(" ,@KomplexOra char(1)");
|
|
clauseExec.Append(" ,@KomplexOra = ").Append(pco.KomplexOra > 0 ? 'F' : 'T');
|
|
}
|
|
if (pco.NemSzakrendszeruOra.HasValue)
|
|
{
|
|
clause.Append(" AND tanora.C_NEMSZAKRENDSZERUORA = @NemSzakrendszeruOra");
|
|
|
|
clauseExecN.Append(" ,@NemSzakrendszeruOra char(1)");
|
|
clauseExec.Append(" ,@NemSzakrendszeruOra = ").Append(pco.NemSzakrendszeruOra > 0 ? 'F' : 'T');
|
|
}
|
|
if (pco.IKTTanora.HasValue)
|
|
{
|
|
clause.Append(" AND tanora.C_IKTTANORA = @IKTTanora");
|
|
|
|
clauseExecN.Append(" ,@IKTTanora char(1)");
|
|
clauseExec.Append(" ,@IKTTanora = ").Append(pco.IKTTanora > 0 ? 'F' : 'T');
|
|
}
|
|
if (pco.NemzetisegiOra.HasValue)
|
|
{
|
|
clause.Append(" AND tanora.C_NEMZETISEGIORA = @NemzetisegiOra");
|
|
|
|
clauseExecN.Append(" ,@NemzetisegiOra char(1)");
|
|
clauseExec.Append(" ,@NemzetisegiOra = ").Append(pco.NemzetisegiOra > 0 ? 'F' : 'T');
|
|
}
|
|
if (pco.IsFelnottoktatasiSzerzodes.HasValue)
|
|
{
|
|
clause.Append(" AND tanora.C_ISFELNOTTOKTATASISZERZODES = @IsFelnottoktatasiSzerzodes");
|
|
|
|
clauseExecN.Append(" ,@IsFelnottoktatasiSzerzodes char(1)");
|
|
clauseExec.Append(" ,@IsFelnottoktatasiSzerzodes = ").Append(pco.IsFelnottoktatasiSzerzodes > 0 ? 'F' : 'T');
|
|
}
|
|
if (pco.MindennaposTestneveles.HasValue)
|
|
{
|
|
clause.Append(" AND tanora.C_MINDENNAPOSTESTNEVELES = @MindennaposTestneveles");
|
|
|
|
clauseExecN.Append(" ,@MindennaposTestneveles char(1)");
|
|
clauseExec.Append(" ,@MindennaposTestneveles = ").Append(pco.MindennaposTestneveles > 0 ? 'F' : 'T');
|
|
}
|
|
if (pco.IPRTanora.HasValue)
|
|
{
|
|
clause.Append(" AND tanora.C_IPRTANORA = @IPRTanora");
|
|
|
|
clauseExecN.Append(" ,@IPRTanora char(1)");
|
|
clauseExec.Append(" ,@IPRTanora = ").Append(pco.IPRTanora > 0 ? 'F' : 'T');
|
|
}
|
|
if (pco.Differencialt.HasValue)
|
|
{
|
|
clause.Append(" AND tanora.C_DIFFERENCIALT= @Differencialt");
|
|
|
|
clauseExecN.Append(" ,@Differencialt char(1)");
|
|
clauseExec.Append(" ,@Differencialt = ").Append(pco.Differencialt > 0 ? 'F' : 'T');
|
|
}
|
|
if (pco.Kooperativ.HasValue)
|
|
{
|
|
clause.Append(" AND tanora.C_KOOPERATIV = @Kooperativ");
|
|
|
|
clauseExecN.Append(" ,@Kooperativ char(1)");
|
|
clauseExec.Append(" ,@Kooperativ = ").Append(pco.Kooperativ > 0 ? 'F' : 'T');
|
|
}
|
|
if (pco.Meres.HasValue)
|
|
{
|
|
clause.Append(" AND tanora.C_MERES = @Meres");
|
|
|
|
clauseExecN.Append(" ,@Meres char(1)");
|
|
clauseExec.Append(" ,@Meres = ").Append(pco.Meres > 0 ? 'F' : 'T');
|
|
}
|
|
if (pco.Drama.HasValue)
|
|
{
|
|
clause.Append(" AND tanora.C_DRAMA = @Drama");
|
|
|
|
clauseExecN.Append(" ,@Drama char(1)");
|
|
clauseExec.Append(" ,@Drama = ").Append(pco.Drama > 0 ? 'F' : 'T');
|
|
}
|
|
if (pco.Tiop.HasValue)
|
|
{
|
|
clause.Append(" AND tanora.C_TIOP12 = @Tiop");
|
|
|
|
clauseExecN.Append(" ,@Tiop char(1)");
|
|
clauseExec.Append(" ,@Tiop = ").Append(pco.Tiop > 0 ? 'T' : 'F');
|
|
}
|
|
if (pco.Bontott.HasValue)
|
|
{
|
|
clause.Append(" AND tanora.C_BONTOTT = @Bontott");
|
|
|
|
clauseExecN.Append(" ,@Bontott char(1)");
|
|
clauseExec.Append(" ,@Bontott = ").Append(pco.Bontott > 0 ? 'F' : 'T');
|
|
}
|
|
if (pco.EFOP32317.HasValue)
|
|
{
|
|
clause.Append(" AND tanora.C_EFOP32317 = @EFOP32317");
|
|
|
|
clauseExecN.Append(" ,@EFOP32317 char(1)");
|
|
clauseExec.Append(" ,@EFOP32317 = ").Append(pco.EFOP32317 > 0 ? 'F' : 'T');
|
|
}
|
|
if (pco.GINOP623.HasValue)
|
|
{
|
|
clause.Append(" AND tanora.C_GINOP623 = @GINOP623");
|
|
|
|
clauseExecN.Append(" ,@GINOP623 char(1)");
|
|
clauseExec.Append(" ,@GINOP623 = ").Append(pco.GINOP623 > 0 ? 'F' : 'T');
|
|
}
|
|
if (pco.EFOP31716EselyteremtesAKoznevelesben.HasValue)
|
|
{
|
|
clause.Append(" AND tanora.C_EFOP31716 = @EFOP31716EselyteremtesAKoznevelesben");
|
|
|
|
clauseExecN.Append(" ,@EFOP31716EselyteremtesAKoznevelesben char(1)");
|
|
clauseExec.Append(" ,@EFOP31716EselyteremtesAKoznevelesben = ").Append(pco.EFOP31716EselyteremtesAKoznevelesben > 0 ? 'F' : 'T');
|
|
}
|
|
if (pco.EFOP33717.HasValue)
|
|
{
|
|
clause.Append(" AND tanora.C_EFOP33717 = @EFOP33717");
|
|
|
|
clauseExecN.Append(" ,@EFOP33717 char(1)");
|
|
clauseExec.Append(" ,@EFOP33717 = ").Append(pco.EFOP33717 > 0 ? 'F' : 'T');
|
|
}
|
|
if (pco.TIOP11112120120001.HasValue)
|
|
{
|
|
clause.Append(" AND tanora.C_TIOP1111212012001 = @TIOP11112120120001");
|
|
|
|
clauseExecN.Append(" ,@TIOP11112120120001 char(1)");
|
|
clauseExec.Append(" ,@TIOP11112120120001 = ").Append(pco.TIOP11112120120001 > 0 ? 'F' : 'T');
|
|
}
|
|
if (pco.DFHTOra.HasValue)
|
|
{
|
|
clause.Append(" AND tanora.C_ISDFHTORA = @DFHTOra");
|
|
|
|
clauseExecN.Append(" ,@DFHTOra char(1)");
|
|
clauseExec.Append(" ,@DFHTOra = ").Append(pco.DFHTOra > 0 ? 'F' : 'T');
|
|
}
|
|
if (pco.TeOrad.HasValue)
|
|
{
|
|
clause.Append(" AND tanora.C_ISTEORAD = @Teorad");
|
|
|
|
clauseExecN.Append(" ,@TeOrad char(1)");
|
|
clauseExec.Append(" ,@TeOrad = ").Append(pco.TeOrad > 0 ? 'F' : 'T');
|
|
}
|
|
if (pco.Rahangolodas.HasValue)
|
|
{
|
|
clause.Append(" AND tanora.C_ISRAHANGOLODAS = @Rahangolodas");
|
|
|
|
clauseExecN.Append(" ,@Rahangolodas char(1)");
|
|
clauseExec.Append(" ,@Rahangolodas = ").Append(pco.Rahangolodas > 0 ? 'F' : 'T');
|
|
}
|
|
if (pco.DigitalisAlapuAlprogram.HasValue)
|
|
{
|
|
clause.Append(" AND tanora.C_ISDIGITALISALAPUALPROGRAM = @DigitalisAlapuAlprogram");
|
|
|
|
clauseExecN.Append(" ,@DigitalisAlapuAlprogram char(1)");
|
|
clauseExec.Append(" ,@DigitalisAlapuAlprogram = ").Append(pco.DigitalisAlapuAlprogram > 0 ? 'F' : 'T');
|
|
}
|
|
if (pco.TestmozgasAlapuAlprogram.HasValue)
|
|
{
|
|
clause.Append(" AND tanora.C_ISTESTMOZGASALAPUALPROGRAM = @TestmozgasAlapuAlprogram");
|
|
|
|
clauseExecN.Append(" ,@TestmozgasAlapuAlprogram char(1)");
|
|
clauseExec.Append(" ,@TestmozgasAlapuAlprogram = ").Append(pco.TestmozgasAlapuAlprogram > 0 ? 'F' : 'T');
|
|
}
|
|
if (pco.EletgyakorlatAlapuAlprogram.HasValue)
|
|
{
|
|
clause.Append(" AND tanora.C_ISELETGYAKORLATALAPUALPROGRA = @EletgyakorlatAlapuAlprogram");
|
|
|
|
clauseExecN.Append(" ,@EletgyakorlatAlapuAlprogram char(1)");
|
|
clauseExec.Append(" ,@EletgyakorlatAlapuAlprogram = ").Append(pco.EletgyakorlatAlapuAlprogram > 0 ? 'F' : 'T');
|
|
}
|
|
if (pco.MuveszetAlapuAlprogram.HasValue)
|
|
{
|
|
clause.Append(" AND tanora.C_ISMUVESZETALAPUALPROGRAM = @MuveszetAlapuAlprogram");
|
|
|
|
clauseExecN.Append(" ,@MuveszetAlapuAlprogram char(1)");
|
|
clauseExec.Append(" ,@MuveszetAlapuAlprogram = ").Append(pco.MuveszetAlapuAlprogram > 0 ? 'F' : 'T');
|
|
}
|
|
if (pco.LogikaAlapuAlprogram.HasValue)
|
|
{
|
|
clause.Append(" AND tanora.C_ISLOGIKAALAPUALPROGRAM = @LogikaAlapuAlprogram");
|
|
|
|
clauseExecN.Append(" ,@LogikaAlapuAlprogram char(1)");
|
|
clauseExec.Append(" ,@LogikaAlapuAlprogram = ").Append(pco.LogikaAlapuAlprogram > 0 ? 'F' : 'T');
|
|
}
|
|
if (pco.EFOP23517201700038Miapalya.HasValue)
|
|
{
|
|
clause.Append(" AND tanora.C_EFOP23517 = @EFOP23517201700038Miapalya");
|
|
|
|
clauseExecN.Append(" ,@EFOP23517201700038Miapalya char(1)");
|
|
clauseExec.Append(" ,@EFOP23517201700038Miapalya = ").Append(pco.EFOP23517201700038Miapalya > 0 ? 'F' : 'T');
|
|
}
|
|
if (pco.Helyettesitett.HasValue)
|
|
{
|
|
clause.Append(pco.Helyettesitett == (int)IgenNemEnum.Igen
|
|
? $" AND tanora.C_HELYETTESITOTANARID IS NOT NULL"
|
|
: $" AND tanora.C_HELYETTESITOTANARID IS NULL");
|
|
}
|
|
if (pco.DigPlatformTipusId.HasValue)
|
|
{
|
|
clause.Append(" AND tanora.C_DIGPLATFORMTIPUSID = @pDigPlatformTipusId");
|
|
|
|
clauseExecN.Append(" ,@pDigPlatformTipusId int");
|
|
clauseExec.Append(" ,@pDigPlatformTipusId = ").Append(pco.DigPlatformTipusId.Value);
|
|
}
|
|
if (pco.DigEszkozTipusId.HasValue)
|
|
{
|
|
clause.Append(" AND tanora.C_DIGESZKOZTIPUSID = @pDigEszkozTipusId");
|
|
|
|
clauseExecN.Append(" ,@pDigEszkozTipusId int");
|
|
clauseExec.Append(" ,@pDigEszkozTipusId = ").Append(pco.DigEszkozTipusId.Value);
|
|
}
|
|
if (pco.DigTamEszkozTipus.NotNullAndAny())
|
|
{
|
|
clause.Append($@"
|
|
AND EXISTS(
|
|
SELECT 1
|
|
FROM T_TANITASIORADIGTAMESZKOZ todte
|
|
WHERE todte.C_TANITASIORAID = tanora.ID AND todte.C_DIGTAMESZKOZTIPUSID {SqlLogic.ParseListIntoSqlQuery("IN({0})", pco.DigTamEszkozTipus)})");
|
|
}
|
|
|
|
string command = $@"
|
|
DECLARE @columns NVARCHAR(MAX), @columnsWithAlias nvarchar(max), @columnsWithType nvarchar(max), @sql NVARCHAR(MAX);
|
|
|
|
SET @columns = N'';
|
|
SELECT @columns+=N', '+QUOTENAME([Name])
|
|
FROM
|
|
(
|
|
SELECT ID AS [Name]
|
|
FROM T_ORATULAJDONSAGTIPUS AS p
|
|
WHERE ID BETWEEN 8356 AND 8615
|
|
GROUP BY ID
|
|
) AS x;
|
|
SET @columnsWithAlias = N'';
|
|
SELECT @columnsWithAlias+=N', ISNULL('+QUOTENAME([Name]) + ', ''F'') AS ' +QUOTENAME([Name])
|
|
FROM
|
|
(
|
|
SELECT ID AS [Name]
|
|
FROM T_ORATULAJDONSAGTIPUS AS p
|
|
WHERE ID BETWEEN 8356 AND 8615
|
|
GROUP BY ID
|
|
) AS x;
|
|
SET @columnsWithType = N'';
|
|
SELECT @columnsWithType += N', '+QUOTENAME([Name]) + ' char'
|
|
FROM
|
|
(
|
|
SELECT ID AS [Name]
|
|
FROM T_ORATULAJDONSAGTIPUS AS p
|
|
WHERE ID BETWEEN 8356 AND 8615
|
|
GROUP BY ID
|
|
) AS x;
|
|
|
|
SET @sql = N'
|
|
SELECT
|
|
TanoraKezdete AS ''Tanóra kezdete''
|
|
,HetNapja AS ''Hét napja''
|
|
,Ora AS ''Óra''
|
|
,OraSorszam AS ''Óra sorszám''
|
|
,EredetiPedagogus AS ''Eredeti pedagógus''
|
|
,HelyettesitoPedagogus AS ''Helyettesítő pedagógus''
|
|
,HelyettesitesTipusa AS ''Helyettesítés típusa''
|
|
,OsztalyCsoport AS ''Osztály/Csoport''
|
|
,Tantargy AS ''Tantárgy''
|
|
,Terem AS ''Terem''
|
|
,ElmaradtMegtartottTanora AS ''Elmaradt/Megtartott tanóra''
|
|
,TanoraTemaja AS ''Tanóra témája''
|
|
,TanoraTanoranKivuliFoglalkozas AS ''Tanóra/Tanórán kívüli foglalkozás''
|
|
,TulOra
|
|
,IktOra
|
|
,IprOra
|
|
,MeresOra
|
|
,Bontott
|
|
,NemzetisegiOra
|
|
,DifferencialtOra
|
|
,DramaOra
|
|
,ParhuzamosOra
|
|
,TestnevelesOra
|
|
,KooperativOra
|
|
,Multikulturalis
|
|
,NemszakrendszeruOra
|
|
,IsFelnottoktatasiSzerzodes
|
|
,KAPOra
|
|
,DFHTora
|
|
,DFHTKIPora
|
|
,Rahangolodas
|
|
,TestmozgasAlapuAlprogram
|
|
,MuveszetAlapuAlprogram
|
|
,KIPOra
|
|
,Komplexora
|
|
,EletGyakorlatAlapuAlprogram
|
|
,LogikaiAlapuAlprogram
|
|
,TEorad
|
|
,DigitalisAlapuAlprogram
|
|
,TamopOra
|
|
,TiopOra
|
|
,EFOP32317
|
|
,EFOP33717
|
|
,VEKOP73317
|
|
,TIOP1111212012001
|
|
,EFOP31716
|
|
,GINOP623
|
|
,EFOP23517
|
|
,Helyettesitett
|
|
,IIF(DigPlatformTipus IS NULL OR DigPlatformTipus = ''Na'', '''', DigPlatformTipus) AS DigPlatformTipus
|
|
,IIF(DigEszkozTipus IS NULL OR DigEszkozTipus = ''Na'', '''', DigEszkozTipus) AS DigEszkozTipus
|
|
,IIF(DigTamEszkozTipus IS NULL OR DigTamEszkozTipus = ''Na'', '''', DigTamEszkozTipus) AS DigTamEszkozTipus
|
|
' + @columnsWithAlias + N'
|
|
{showId}
|
|
FROM (
|
|
SELECT
|
|
tanora.C_ORAKEZDETE AS TanoraKezdete
|
|
,hetnapja.C_NAME AS HetNapja
|
|
,tanora.C_ORASZAM AS Ora
|
|
,IIF(tanora.C_MEGTARTOTT = ''F'', ''{OrarendResource.ElmaradtOra}'', IIF(tanora.C_SORSZAMOZANDO = ''F'', ''-'', CONVERT(varchar(10), [KR_{schema}_Schema].fnGetEvesOraszamByTanitasiOraId(tanora.ID)))) AS OraSorszam
|
|
{(tanorakExportTipus != TanorakExportTipusEnum.Helyettesitett
|
|
? @",IIF(eredetitanar.C_NYOMTATASINEV IS NULL, beirotanar.C_NYOMTATASINEV, eredetitanar.C_NYOMTATASINEV) AS EredetiPedagogus
|
|
,IIF(helyettes.C_NYOMTATASINEV IS NULL, '''', helyettes.C_NYOMTATASINEV) AS HelyettesitoPedagogus
|
|
,IIF(helyettes.C_NYOMTATASINEV IS NULL, '''', helyettesitestipusa.C_NAME) AS HelyettesitesTipusa"
|
|
: @",eredetitanar.C_NYOMTATASINEV AS EredetiPedagogus
|
|
,helyettes.C_NYOMTATASINEV AS HelyettesitoPedagogus
|
|
,helyettesitestipusa.C_NAME AS HelyettesitesTipusa")}
|
|
,osztalycsoport.C_NEV AS OsztalyCsoport
|
|
,tantargy.C_NEV AS Tantargy
|
|
,terem.C_NEV AS Terem
|
|
,IIF(tanora.C_MEGTARTOTT = ''F'', ''{OrarendResource.ElmaradtOra}'', ''{OrarendResource.MegtartottOra}'') AS ElmaradtMegtartottTanora
|
|
,IIF(tanora.C_MEGTARTOTT = ''F'', ''-'', tanora.C_TEMA) AS TanoraTemaja
|
|
,IIF(tanora.C_CSENGETESIRENDID IS NOT NULL, ''Tanóra'', ''Tanórán kívüli foglalkozás'') AS TanoraTanoranKivuliFoglalkozas
|
|
,tanora.C_TULORA AS TulOra
|
|
,tanora.C_IKTTANORA AS IktOra
|
|
,tanora.C_IPRTANORA AS IprOra
|
|
,tanora.C_MERES AS MeresOra
|
|
,tanora.C_BONTOTT AS Bontott
|
|
,tanora.C_NEMZETISEGIORA AS NemzetisegiOra
|
|
,tanora.C_DIFFERENCIALT AS DifferencialtOra
|
|
,tanora.C_DRAMA AS DramaOra
|
|
,tanora.C_PARHUZAMOSORA AS ParhuzamosOra
|
|
,tanora.C_MINDENNAPOSTESTNEVELES AS TestnevelesOra
|
|
,tanora.C_KOOPERATIV AS KooperativOra
|
|
,tanora.C_MULTIKULTURALISORA AS Multikulturalis
|
|
,tanora.C_NEMSZAKRENDSZERUORA AS NemszakrendszeruOra
|
|
,tanora.C_ISFELNOTTOKTATASISZERZODES AS IsFelnottoktatasiSzerzodes
|
|
,tanora.C_KAPORA AS KAPOra
|
|
,tanora.C_ISDFHTORA AS DFHTora
|
|
,tanora.C_ISDFHTKIPORA AS DFHTKIPora
|
|
,tanora.C_ISRAHANGOLODAS AS Rahangolodas
|
|
,tanora.C_ISTESTMOZGASALAPUALPROGRAM AS TestmozgasAlapuAlprogram
|
|
,tanora.C_ISMUVESZETALAPUALPROGRAM AS MuveszetAlapuAlprogram
|
|
,tanora.C_KIPORA AS KIPOra
|
|
,tanora.C_KOMPLEXORA AS Komplexora
|
|
,tanora.C_ISELETGYAKORLATALAPUALPROGRA AS EletGyakorlatAlapuAlprogram
|
|
,tanora.C_ISLOGIKAALAPUALPROGRAM AS LogikaiAlapuAlprogram
|
|
,tanora.C_ISTEORAD AS TEorad
|
|
,tanora.C_ISDIGITALISALAPUALPROGRAM AS DigitalisAlapuAlprogram
|
|
,tanora.C_TAMOPORA AS TamopOra
|
|
,tanora.C_TIOP12 AS TiopOra
|
|
,tanora.C_EFOP32317 AS EFOP32317
|
|
,tanora.C_EFOP33717 AS EFOP33717
|
|
,tanora.C_VEKOP73317 AS VEKOP73317
|
|
,tanora.C_TIOP1111212012001 AS TIOP1111212012001
|
|
,tanora.C_EFOP31716 AS EFOP31716
|
|
,tanora.C_GINOP623 AS GINOP623
|
|
,tanora.C_EFOP23517 AS EFOP23517
|
|
,IIF(tanora.C_HELYETTESITOTANARID IS NOT NULL, ''T'', ''F'') AS Helyettesitett
|
|
,tot.C_ORATULAJDONSAGID
|
|
,tot.C_BOOLERTEK
|
|
,digEszkozTipus.C_NAME AS DigEszkozTipus
|
|
,digPlatformTipus.C_NAME AS DigPlatformTipus
|
|
,STUFF(
|
|
(SELECT '', '' + dib.C_NAME
|
|
FROM T_TANITASIORADIGTAMESZKOZ dte
|
|
INNER JOIN T_DICTIONARYITEMBASE dib on dib.ID = dte.C_DIGTAMESZKOZTIPUSID and dib.C_TANEVID = dte.C_TANEVID
|
|
WHERE dte.C_TANITASIORAID = tanora.ID
|
|
FOR XML PATH(''''))
|
|
, 1, 2, '''') AS DigTamEszkozTipus
|
|
,osztalycsoport.ID AS OsztalyCsoportId
|
|
,tanora.ID AS TanoraId
|
|
,tantargy.ID AS TantargyId
|
|
,eredetitanar.ID AS EredetiTanarId
|
|
,helyettes.ID AS HelyettesId
|
|
{(tanorakExportTipus != TanorakExportTipusEnum.Helyettesitett ? ",beirotanar.ID AS BeiroTanarId" : "")}
|
|
,terem.ID AS TeremId
|
|
FROM T_TANITASIORA tanora
|
|
{fromClause}
|
|
INNER JOIN T_OSZTALYCSOPORT osztalycsoport ON tanora.C_OSZTALYCSOPORTID = osztalycsoport.ID AND osztalycsoport.C_TANEVID = tanora.C_TANEVID
|
|
LEFT JOIN T_CSOPORT csoport ON csoport.ID = osztalycsoport.ID
|
|
INNER JOIN T_TANTARGY tantargy ON tanora.C_TANTARGYID = tantargy.ID AND tantargy.C_INTEZMENYID = tanora.C_INTEZMENYID
|
|
LEFT JOIN T_TEREM terem ON tanora.C_TEREMID = terem.ID AND terem.C_INTEZMENYID = tanora.C_INTEZMENYID
|
|
INNER JOIN T_DICTIONARYITEMBASE hetnapja ON hetnapja.ID = tanora.C_HETNAPJA AND hetnapja.C_INTEZMENYID = tanora.C_INTEZMENYID AND hetnapja.C_TANEVID = tanora.C_TANEVID
|
|
LEFT JOIN T_DICTIONARYITEMBASE helyettesitestipusa ON helyettesitestipusa.ID = tanora.C_HELYETTESITESTIPUSA AND helyettesitestipusa.C_INTEZMENYID = tanora.C_INTEZMENYID AND helyettesitestipusa.C_TANEVID = tanora.C_TANEVID
|
|
LEFT JOIN T_TANITASIORATULAJDONSAG_OSSZES tot ON tot.C_TANITASIORAID = tanora.ID AND tot.C_TANEVID = tanora.C_TANEVID AND tot.TOROLT = ''F''
|
|
LEFT JOIN T_DICTIONARYITEMBASE digEszkozTipus ON digEszkozTipus.ID = tanora.C_DIGESZKOZTIPUSID AND digEszkozTipus.C_TANEVID = tanora.C_TANEVID
|
|
LEFT JOIN T_DICTIONARYITEMBASE digPlatformTipus ON digPlatformTipus.ID = tanora.C_DIGPLATFORMTIPUSID AND digPlatformTipus.C_TANEVID = tanora.C_TANEVID
|
|
WHERE
|
|
{(tanorakExportTipus != TanorakExportTipusEnum.Helyettesitett
|
|
? $"tanora.C_MEGTARTOTT = '{(tanorakExportTipus == TanorakExportTipusEnum.Megtartott ? "'T'" : "'F'")}'"
|
|
: "tanora.C_HELYETTESITOTANARID IS NOT NULL")}
|
|
AND tanora.TOROLT = ''F'' AND tanora.C_TANEVID = @{nameof(tanevId)}";
|
|
command += clause;
|
|
command += @"
|
|
) AS p PIVOT (MIN(C_BOOLERTEK) FOR C_ORATULAJDONSAGID IN (' + STUFF(@columns, 1, 2, '') + N'
|
|
)) AS j
|
|
ORDER BY TanoraKezdete'";
|
|
|
|
command += @"
|
|
EXEC sp_executesql @sql
|
|
,N'@tanevId int " + clauseExecN + @"'
|
|
,@tanevId = " + tanevId + clauseExec;
|
|
|
|
var commandText = command;
|
|
|
|
//+ az OraTulajdonsagTipus-ok
|
|
var booleanColumns = "TulOra,TamopOra,NemszakrendszeruOra,IktOra,NemzetisegiOra,TestnevelesOra,IprOra,DifferencialtOra,KooperativOra,MeresOra,DramaOra,ParhuzamosOra,TiopOra,KIPOra,VEKOP73317,KAPOra,Bontott,Multikulturalis,EFOP32317,Komplexora,GINOP623,EFOP31716,EFOP33717,TIOP1111212012001,IsFelnottoktatasiSzerzodes,DFHTora,DFHTKIPora,TEorad,Rahangolodas,DigitalisAlapuAlprogram,TestmozgasAlapuAlprogram,EletGyakorlatAlapuAlprogram,MuveszetAlapuAlprogram,LogikaiAlapuAlprogram,Helyettesitett,EFOP23517,"
|
|
+ string.Join(",", Enum.GetValues(typeof(OraTulajdonsagTipusEnum)).Cast<int>().Where(x => x >= (int)OraTulajdonsagTipusEnum._2019_2020_as_tanev_tananyaganak_ismetlese && x <= (int)OraTulajdonsagTipusEnum.online_ora));
|
|
|
|
return GetData(commandText, null, booleanColumns: booleanColumns);
|
|
}
|
|
|
|
public DataSet GetTanitasiOraTulajdonsag(int tanitasiOraId, int tanevId)
|
|
{
|
|
using (var command = new SDACommand())
|
|
{
|
|
command.Connection = UserContext.Instance.SDAConnection;
|
|
command.Transaction = UserContext.Instance.SDATransaction;
|
|
command.CommandType = CommandType.StoredProcedure;
|
|
command.CommandText = "uspGetTanitasiOraTulajdonsag";
|
|
|
|
command.Parameters.Add("pTanitasiOraId", tanitasiOraId);
|
|
command.Parameters.Add("pTanevId", tanevId);
|
|
|
|
var ds = new DataSet();
|
|
using (var adapter = new SDADataAdapter())
|
|
{
|
|
adapter.SelectCommand = command;
|
|
adapter.Fill(ds);
|
|
}
|
|
|
|
return ds;
|
|
}
|
|
}
|
|
|
|
public bool HasTanitasiOraTulajdonsag(int tanitasiOraId, int intezmenyId, int tanevId)
|
|
{
|
|
var commandText = $@"
|
|
SELECT C_TANITASIORAID
|
|
FROM T_TANITASIORATULAJDONSAG_OSSZES
|
|
WHERE C_TANITASIORAID = @{nameof(tanitasiOraId)}
|
|
AND C_INTEZMENYID = @{nameof(intezmenyId)}
|
|
AND C_TANEVID = @{nameof(tanevId)}
|
|
AND TOROLT = 'F'";
|
|
|
|
DataSet ds = GetData(commandText, new List<CommandParameter> {
|
|
new CommandParameter(nameof(tanitasiOraId), tanitasiOraId),
|
|
new CommandParameter(nameof(intezmenyId), intezmenyId),
|
|
new CommandParameter(nameof(tanevId), tanevId)
|
|
});
|
|
|
|
return ds.Tables[0].Rows.Count > 0;
|
|
}
|
|
|
|
public void SaveOrUpdateTanitasiOraTulajdonsag(Dictionary<int, bool> oraTulajdonsagok, int tanitasiOraId, int intezmenyId, int tanevId)
|
|
{
|
|
using (var command = new SDACommand())
|
|
{
|
|
command.Connection = UserContext.Instance.SDAConnection;
|
|
command.Transaction = UserContext.Instance.SDATransaction;
|
|
command.Parameters.Add(nameof(tanitasiOraId), tanitasiOraId);
|
|
command.Parameters.Add(nameof(intezmenyId), intezmenyId);
|
|
command.Parameters.Add(nameof(tanevId), tanevId);
|
|
|
|
int key = 0;
|
|
string value = string.Empty;
|
|
var oraTulajdonsagKey = command.Parameters.Add(nameof(key), key);
|
|
var oraTulajdonsagValue = command.Parameters.Add(nameof(value), value);
|
|
foreach (var oraTulajdonsag in oraTulajdonsagok)
|
|
{
|
|
oraTulajdonsagKey.Value = oraTulajdonsag.Key.ToString();
|
|
oraTulajdonsagValue.Value = oraTulajdonsag.Value.ToSDABoolean();
|
|
|
|
StringBuilder commandTextBuilder = new StringBuilder($@"
|
|
UPDATE
|
|
T_TANITASIORATULAJDONSAG_OSSZES
|
|
SET
|
|
C_BOOLERTEK = @{nameof(value)}
|
|
WHERE
|
|
C_ORATULAJDONSAGID = @{nameof(key)}
|
|
AND C_TANITASIORAID = @{nameof(tanitasiOraId)}
|
|
AND C_INTEZMENYID = @{nameof(intezmenyId)}
|
|
AND C_TANEVID = @{nameof(tanevId)}
|
|
AND TOROLT = 'F'");
|
|
|
|
if (oraTulajdonsag.Value)
|
|
{
|
|
commandTextBuilder.AppendLine($@"
|
|
IF NOT EXISTS
|
|
(
|
|
SELECT
|
|
C_ORATULAJDONSAGID
|
|
,C_TANITASIORAID
|
|
,C_INTEZMENYID
|
|
,C_TANEVID
|
|
,TOROLT
|
|
FROM T_TANITASIORATULAJDONSAG_OSSZES
|
|
WHERE C_ORATULAJDONSAGID = @{nameof(key)}
|
|
AND C_TANITASIORAID = @{nameof(tanitasiOraId)}
|
|
AND C_INTEZMENYID = @{nameof(intezmenyId)}
|
|
AND C_TANEVID = @{nameof(tanevId)}
|
|
AND TOROLT = 'F'
|
|
)
|
|
INSERT INTO
|
|
T_TANITASIORATULAJDONSAG_OSSZES
|
|
(
|
|
C_ORATULAJDONSAGID
|
|
,C_TANITASIORAID
|
|
,C_INTEZMENYID
|
|
,C_TANEVID
|
|
,C_BOOLERTEK
|
|
)
|
|
VALUES
|
|
(
|
|
@{nameof(key)}
|
|
,@{nameof(tanitasiOraId)}
|
|
,@{nameof(intezmenyId)}
|
|
,@{nameof(tanevId)}
|
|
,'T'
|
|
)");
|
|
}
|
|
|
|
command.CommandText = commandTextBuilder.ToString();
|
|
command.ExecuteScalar();
|
|
}
|
|
}
|
|
}
|
|
|
|
public DataSet GetTanorakGridForNaplozasDataSet(int osztalyCsoportId, int tantargyId, int tanevId)
|
|
{
|
|
using (var command = new SDACommand())
|
|
{
|
|
command.Connection = UserContext.Instance.SDAConnection;
|
|
command.Transaction = UserContext.Instance.SDATransaction;
|
|
command.CommandType = CommandType.StoredProcedure;
|
|
command.CommandText = "uspGetTanorakGridForNaplozasData";
|
|
|
|
command.Parameters.Add("pOsztalyCsoportId", osztalyCsoportId);
|
|
command.Parameters.Add("pTantargyId", tantargyId);
|
|
command.Parameters.Add("pTanevId", tanevId);
|
|
|
|
var ds = new DataSet();
|
|
using (var adapter = new SDADataAdapter())
|
|
{
|
|
adapter.SelectCommand = command;
|
|
adapter.Fill(ds);
|
|
}
|
|
|
|
return ds;
|
|
}
|
|
}
|
|
|
|
public void SaveTanitasiOraDigTamEszkozList(List<int> pDigTamEszkozList, int pTanitasiOraId, int pIntezmenyId, int pTanevId, int pFelhasznaloId)
|
|
{
|
|
using (SDACommand command = new SDACommand())
|
|
{
|
|
command.Connection = UserContext.Instance.SDAConnection;
|
|
command.Transaction = UserContext.Instance.SDATransaction;
|
|
command.CommandType = CommandType.StoredProcedure;
|
|
command.CommandText = "uspSaveTanitasiOraDigTamEszkozList";
|
|
|
|
command.Parameters.Add(nameof(pIntezmenyId), pIntezmenyId);
|
|
command.Parameters.Add(nameof(pTanevId), pTanevId);
|
|
command.Parameters.Add(nameof(pTanitasiOraId), pTanitasiOraId);
|
|
command.Parameters.Add(nameof(pFelhasznaloId), pFelhasznaloId);
|
|
command.Parameters.Add(nameof(pDigTamEszkozList), string.Join(",", pDigTamEszkozList));
|
|
|
|
command.ExecuteNonQuery();
|
|
|
|
DalHelper.Commit();
|
|
}
|
|
}
|
|
|
|
public DateTime GetTanitasiOraLegutobbiModositasa(int tanitasiOraId)
|
|
{
|
|
var commandText = $@"
|
|
SELECT LASTCHANGED
|
|
FROM T_TANITASIORA_OSSZES
|
|
WHERE ID = {tanitasiOraId}
|
|
AND TOROLT = 'F'
|
|
";
|
|
|
|
var ds = GetData(commandText);
|
|
|
|
var lastChanged = ds.Tables[0].Rows[0].Field<DateTime>("LASTCHANGED");
|
|
return lastChanged;
|
|
}
|
|
|
|
public void UpdateTanitasiOraEvesSorszamTanitasiOra(int intezmenyId, int tanevId, int tanitasiOraId)
|
|
=> UpdateTanitasiOraEvesSorszam(intezmenyId, tanevId, tanitasiOraId: tanitasiOraId);
|
|
|
|
public void UpdateTanitasiOraEvesSorszamTeljesTanev(int intezmenyId, int tanevId)
|
|
=> UpdateTanitasiOraEvesSorszam(intezmenyId, tanevId);
|
|
|
|
private void UpdateTanitasiOraEvesSorszam(int intezmenyId, int tanevId, int? tantargyId = null, int? osztalyCsoportId = null, int? tanitasiOraId = null)
|
|
{
|
|
using (SDACommand command = new SDACommand())
|
|
{
|
|
command.Connection = UserContext.Instance.SDAConnection;
|
|
command.Transaction = UserContext.Instance.SDATransaction;
|
|
command.CommandType = CommandType.StoredProcedure;
|
|
command.CommandText = "uspUpdateTanoraEvesSorszam";
|
|
|
|
command.Parameters.Add("pIntezmenyId", intezmenyId);
|
|
command.Parameters.Add("pTanevId", tanevId);
|
|
command.Parameters.Add("pTantargyId", tantargyId);
|
|
command.Parameters.Add("pOsztalyCsoportId", osztalyCsoportId);
|
|
command.Parameters.Add("pTanitasiOraId", tanitasiOraId);
|
|
|
|
command.ExecuteNonQuery();
|
|
|
|
DalHelper.Commit();
|
|
}
|
|
}
|
|
|
|
public DataSet ListTanitasiOraKezdeteVege(IEnumerable<int> tanitasiOraIdList)
|
|
{
|
|
var commandText = $@"
|
|
SELECT
|
|
ID AS Id
|
|
,C_ORAKEZDETE AS OraKezdete
|
|
,C_ORAVEGE AS OraVege
|
|
,C_ORASZAM AS Oraszam
|
|
FROM T_TANITASIORA
|
|
WHERE ID IN ({string.Join(",", tanitasiOraIdList)})";
|
|
|
|
return GetData(commandText);
|
|
}
|
|
|
|
public DataSet GetDigitalisInformaciokByTanoraIds(IEnumerable<int> tanitasiOraIdList, int tanevId)
|
|
{
|
|
using (var command = new SDACommand())
|
|
{
|
|
command.Connection = UserContext.Instance.SDAConnection;
|
|
command.Transaction = UserContext.Instance.SDATransaction;
|
|
command.CommandType = CommandType.StoredProcedure;
|
|
command.CommandText = "uspGetDigitalisInformaciokByTanitasiOrak";
|
|
|
|
command.Parameters.Add("pTanitasiOraIdsString", string.Join(",", tanitasiOraIdList));
|
|
command.Parameters.Add("pTanevId", tanevId);
|
|
|
|
var ds = new DataSet();
|
|
using (var adapter = new SDADataAdapter())
|
|
{
|
|
adapter.SelectCommand = command;
|
|
adapter.Fill(ds);
|
|
}
|
|
|
|
return ds;
|
|
}
|
|
}
|
|
}
|
|
}
|