2248 lines
97 KiB
C#
2248 lines
97 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.Entities;
|
|
using Kreta.Framework.Util;
|
|
using SDA.DataProvider;
|
|
using SDA.Kreta.Entities;
|
|
using static SDA.Kreta.Entities.OrarendiOra;
|
|
|
|
namespace Kreta.DataAccessManual
|
|
{
|
|
internal class OrarendiOraDal : DataAccessBase, IOrarendiOraDal
|
|
{
|
|
public OrarendiOraDal(DalHandler handler) : base(handler) { }
|
|
public OrarendiOraDal(DalHandler handler, GridParameters parameters) : base(handler, parameters) { }
|
|
|
|
public void SetFoglalkozasToNull(int foglalkozasId, int felhasznaloId)
|
|
{
|
|
using (var command = new SDACommand())
|
|
{
|
|
command.Connection = UserContext.Instance.SDAConnection;
|
|
command.Transaction = UserContext.Instance.SDATransaction;
|
|
|
|
command.Parameters.Add("pFoglalkozasId", SDADBType.Int).Value = foglalkozasId;
|
|
command.Parameters.Add("pFelhasznaloId", SDADBType.Int).Value = felhasznaloId;
|
|
|
|
command.CommandText = @"
|
|
UPDATE T_ORARENDIORA_OSSZES
|
|
SET
|
|
C_FOGLALKOZASID = NULL
|
|
,SERIAL = SERIAL + 1
|
|
,LASTCHANGED = GETDATE()
|
|
,MODIFIER = :pFelhasznaloId
|
|
WHERE C_FOGLALKOZASID = :pFoglalkozasId";
|
|
|
|
command.ExecuteNonQuery();
|
|
}
|
|
|
|
DalHelper.Commit();
|
|
}
|
|
|
|
public DataSet GetTanevEsemenyeiOrarend(DateTime start, DateTime end)
|
|
{
|
|
var paramList = new List<CommandParameter>
|
|
{
|
|
new CommandParameter("pKezdete", start),
|
|
new CommandParameter("pVege", end)
|
|
};
|
|
|
|
var command = @"
|
|
SELECT
|
|
T_TANEVRENDJE_OSSZES.ID ID,
|
|
T_TANEVRENDJE_OSSZES.C_DATUM Datum,
|
|
T_TANEVRENDJE_OSSZES.C_NAPTIPUSA NapTip,
|
|
T_TANEVRENDJE_OSSZES.C_ORARENDINAP OrarendiNap,
|
|
T_TANEVRENDJE_OSSZES.C_MEGJEGYZES Megjegyzes,
|
|
(SELECT count(1) FROM T_OSZTALYCSOPORT_TANEVRENDJE WHERE C_TANEVRENDJEID = T_TANEVRENDJE_OSSZES.ID) KapcsCsopSzam
|
|
FROM
|
|
T_TANEVRENDJE_OSSZES
|
|
WHERE
|
|
T_TANEVRENDJE_OSSZES.TOROLT = 'F'
|
|
AND T_TANEVRENDJE_OSSZES.C_DATUM between :pKezdete AND :pVege
|
|
";
|
|
|
|
var ds = GetData(command, paramList, "NapTip");
|
|
|
|
return ds;
|
|
}
|
|
|
|
/// INFO @DevKornel: Mobil használja
|
|
public DataSet GetOrarend(int intezmenyId, int tanevId, DateTime start, DateTime end, int? tanarId, int? osztalyCsoportId, int? tanuloId, bool csakOrarend, OrarendTipusEnum orarendTipus, int? tantargyId, int? teremId, bool helyettesitesNelkul, OktNevelesiKategoriaEnum? feladatKategoria, DateTime? orakezdete = null, DateTime? oravege = null, int? hetnapja = null)
|
|
{
|
|
using (var command = new SDACommand())
|
|
{
|
|
command.Connection = UserContext.Instance.SDAConnection;
|
|
command.Transaction = UserContext.Instance.SDATransaction;
|
|
command.CommandType = CommandType.StoredProcedure;
|
|
command.CommandText = "sp_GetOrarend";
|
|
|
|
command.Parameters.Add("pIntezmenyId", intezmenyId);
|
|
command.Parameters.Add("pTanevId", tanevId);
|
|
|
|
command.Parameters.Add("pIdoszakKezdete", start.Date);
|
|
command.Parameters.Add("pIdoszakVege", end.Date);
|
|
if (tanarId.HasValue)
|
|
command.Parameters.Add("pTanarId", tanarId);
|
|
else
|
|
command.Parameters.Add("pTanarId", DBNull.Value);
|
|
|
|
if (osztalyCsoportId.HasValue)
|
|
command.Parameters.Add("pOsztalyCsoportId", osztalyCsoportId);
|
|
else
|
|
command.Parameters.Add("pOsztalyCsoportId", DBNull.Value);
|
|
|
|
if (tanuloId.HasValue)
|
|
command.Parameters.Add("pTanuloId", tanuloId);
|
|
else
|
|
command.Parameters.Add("pTanuloId", DBNull.Value);
|
|
|
|
command.Parameters.Add("pCsakOrarendiOrak", csakOrarend ? 1 : 0);
|
|
|
|
if (tantargyId.HasValue)
|
|
command.Parameters.Add("pTantargyId", tantargyId);
|
|
else
|
|
command.Parameters.Add("pTantargyId", DBNull.Value);
|
|
|
|
if (teremId.HasValue)
|
|
command.Parameters.Add("pTeremId", teremId);
|
|
else
|
|
command.Parameters.Add("pTeremId", DBNull.Value);
|
|
|
|
if (orakezdete.HasValue)
|
|
command.Parameters.Add("pOrakezdete", orakezdete);
|
|
else
|
|
command.Parameters.Add("pOrakezdete", DBNull.Value);
|
|
|
|
if (oravege.HasValue)
|
|
command.Parameters.Add("pOravege", oravege);
|
|
else
|
|
command.Parameters.Add("pOravege", DBNull.Value);
|
|
|
|
if (hetnapja.HasValue)
|
|
command.Parameters.Add("pHetnapja", hetnapja);
|
|
else
|
|
command.Parameters.Add("pHetnapja", DBNull.Value);
|
|
|
|
command.Parameters.Add("pIsHelyettesitesNelkul", helyettesitesNelkul ? 1 : 0);
|
|
|
|
switch (orarendTipus)
|
|
{
|
|
case OrarendTipusEnum.CsengetesiRendhezKotottOrarend:
|
|
command.Parameters.Add("pIsNapirend", (object)0);
|
|
break;
|
|
case OrarendTipusEnum.Napirend:
|
|
command.Parameters.Add("pIsNapirend", 1);
|
|
break;
|
|
case OrarendTipusEnum.Minden:
|
|
command.Parameters.Add("pIsNapirend", DBNull.Value);
|
|
break;
|
|
}
|
|
|
|
command.Parameters.Add("pFeladatKategoriaId", feladatKategoria.HasValue ? (int)feladatKategoria : (object)DBNull.Value);
|
|
|
|
var dts = new DataSet();
|
|
using (var adapter = new SDADataAdapter())
|
|
{
|
|
adapter.SelectCommand = command;
|
|
adapter.Fill(dts);
|
|
}
|
|
|
|
SetDNAME(dts.Tables[0], "Hetirend,TargykategoriaID");
|
|
SetBoolFields(dts.Tables[0], "Megtartott,Hianyzas,Keses,Ures,AdminAltalKiirt");
|
|
return dts;
|
|
}
|
|
}
|
|
|
|
/// INFO @DevKornel: Mobil használja
|
|
/// TODO @DevKornél: Mobil ezt hívja-> az end date-hez felsőbb rétegbe hozzá van adva +1 nap, hogy a filter zárt intervallumot határozzon meg
|
|
public DataSet GetOrarendForMobile(int intezmenyId, int tanevId, DateTime start, DateTime end, int? tanarId, int? osztalyCsoportId, int? tanuloId, bool csakOrarend, OrarendTipusEnum orarendTipus, int? tantargyId, int? teremId, bool helyettesitesNelkul, OktNevelesiKategoriaEnum? feladatKategoria, DateTime? orakezdete = null, DateTime? oravege = null, int? hetnapja = null)
|
|
{
|
|
using (var command = new SDACommand())
|
|
{
|
|
command.Connection = UserContext.Instance.SDAConnection;
|
|
command.Transaction = UserContext.Instance.SDATransaction;
|
|
command.CommandType = CommandType.StoredProcedure;
|
|
command.CommandText = "sp_GetOrarend";
|
|
|
|
command.Parameters.Add("pIntezmenyId", intezmenyId);
|
|
command.Parameters.Add("pTanevId", tanevId);
|
|
|
|
command.Parameters.Add("pIdoszakKezdete", start.Date);
|
|
command.Parameters.Add("pIdoszakVege", end.Date);
|
|
if (tanarId.HasValue)
|
|
command.Parameters.Add("pTanarId", tanarId);
|
|
else
|
|
command.Parameters.Add("pTanarId", DBNull.Value);
|
|
|
|
if (osztalyCsoportId.HasValue)
|
|
command.Parameters.Add("pOsztalyCsoportId", osztalyCsoportId);
|
|
else
|
|
command.Parameters.Add("pOsztalyCsoportId", DBNull.Value);
|
|
|
|
if (tanuloId.HasValue)
|
|
command.Parameters.Add("pTanuloId", tanuloId);
|
|
else
|
|
command.Parameters.Add("pTanuloId", DBNull.Value);
|
|
|
|
command.Parameters.Add("pCsakOrarendiOrak", csakOrarend ? 1 : 0);
|
|
|
|
if (tantargyId.HasValue)
|
|
command.Parameters.Add("pTantargyId", tantargyId);
|
|
else
|
|
command.Parameters.Add("pTantargyId", DBNull.Value);
|
|
|
|
if (teremId.HasValue)
|
|
command.Parameters.Add("pTeremId", teremId);
|
|
else
|
|
command.Parameters.Add("pTeremId", DBNull.Value);
|
|
|
|
if (orakezdete.HasValue)
|
|
command.Parameters.Add("pOrakezdete", orakezdete);
|
|
else
|
|
command.Parameters.Add("pOrakezdete", DBNull.Value);
|
|
|
|
if (oravege.HasValue)
|
|
command.Parameters.Add("pOravege", oravege);
|
|
else
|
|
command.Parameters.Add("pOravege", DBNull.Value);
|
|
|
|
if (hetnapja.HasValue)
|
|
command.Parameters.Add("pHetnapja", hetnapja);
|
|
else
|
|
command.Parameters.Add("pHetnapja", DBNull.Value);
|
|
|
|
command.Parameters.Add("pIsHelyettesitesNelkul", helyettesitesNelkul ? 1 : 0);
|
|
|
|
switch (orarendTipus)
|
|
{
|
|
case OrarendTipusEnum.CsengetesiRendhezKotottOrarend:
|
|
command.Parameters.Add("pIsNapirend", (object)0);
|
|
break;
|
|
case OrarendTipusEnum.Napirend:
|
|
command.Parameters.Add("pIsNapirend", 1);
|
|
break;
|
|
case OrarendTipusEnum.Minden:
|
|
command.Parameters.Add("pIsNapirend", DBNull.Value);
|
|
break;
|
|
}
|
|
|
|
command.Parameters.Add("pFeladatKategoriaId", feladatKategoria.HasValue ? (int)feladatKategoria : (object)DBNull.Value);
|
|
command.Parameters.Add("pOszlopok", "Id, OraKezdete, OraVege, OraTipus, TanarNev, HelyettesitesId, HelyettesitoTanarID, HelyettesitoTanarNev, Datum, GroupId, TantargyId, OsztCsopId, TanarID, Oraszam, Megtartott, Hianyzas, Keses, Hetirend, TeremNev, OsztalyNev, TargyNev, TargykategoriaID, KozpontiOraGroupId, KozpontilagToroltOraGroupId, TargykategoriaID, TargyNevForMobile, TanevRendOsztalyCsoportId, Tema, EvesOraSorszam, LASTCHANGED, CREATED");
|
|
|
|
var dts = new DataSet();
|
|
using (var adapter = new SDADataAdapter())
|
|
{
|
|
adapter.SelectCommand = command;
|
|
adapter.Fill(dts);
|
|
}
|
|
SetDNAME(dts.Tables[0], "Hetirend,TargykategoriaID");
|
|
SetBoolFields(dts.Tables[0], "Megtartott,Hianyzas,Keses");
|
|
return dts;
|
|
}
|
|
}
|
|
|
|
public DataSet GetOrarendForKozpontiOrak(int intezmenyId, int tanevId, DateTime start, DateTime end)
|
|
{
|
|
using (var command = new SDACommand())
|
|
{
|
|
command.Connection = UserContext.Instance.SDAConnection;
|
|
command.Transaction = UserContext.Instance.SDATransaction;
|
|
command.CommandType = CommandType.StoredProcedure;
|
|
command.CommandText = "uspGetKozpontiOrakOrarend";
|
|
|
|
command.Parameters.Add("pIntezmenyId", intezmenyId);
|
|
command.Parameters.Add("pTanevId", tanevId);
|
|
|
|
command.Parameters.Add("pIdoszakKezdete", start.Date);
|
|
command.Parameters.Add("pIdoszakVege", end.Date.AddDays(1));
|
|
|
|
var dts = new DataSet();
|
|
using (var adapter = new SDADataAdapter())
|
|
{
|
|
adapter.SelectCommand = command;
|
|
adapter.Fill(dts);
|
|
}
|
|
|
|
SetDNAME(dts.Tables[0], "Hetirend,TargykategoriaID");
|
|
SetBoolFields(dts.Tables[0], "Megtartott,Hianyzas,Keses,Ures,AdminAltalKiirt");
|
|
return dts;
|
|
}
|
|
}
|
|
|
|
public DataSet GetOralatogatasok(int intezmenyId, int tanevId, DateTime start, DateTime end, int tanarId)
|
|
{
|
|
using (var command = new SDACommand())
|
|
{
|
|
command.Connection = UserContext.Instance.SDAConnection;
|
|
command.Transaction = UserContext.Instance.SDATransaction;
|
|
command.CommandType = CommandType.StoredProcedure;
|
|
command.CommandText = "uspGetOralatogatasok";
|
|
|
|
command.Parameters.Add("pIntezmenyId", intezmenyId);
|
|
command.Parameters.Add("pTanevId", tanevId);
|
|
|
|
command.Parameters.Add("pIdoszakKezdete", start);
|
|
command.Parameters.Add("pIdoszakVege", end);
|
|
command.Parameters.Add("pTanarId", tanarId);
|
|
|
|
var dts = new DataSet();
|
|
using (var adapter = new SDADataAdapter())
|
|
{
|
|
adapter.SelectCommand = command;
|
|
adapter.Fill(dts);
|
|
}
|
|
|
|
SetDNAME(dts.Tables[0], "Hetirend");
|
|
SetBoolFields(dts.Tables[0], "Megtartott");
|
|
|
|
return dts;
|
|
}
|
|
}
|
|
|
|
public DataSet GetOrarendiOrakByTantargyId(int tantargyId, int tanevId)
|
|
{
|
|
var paramList = new List<CommandParameter>
|
|
{
|
|
new CommandParameter("pTantargyID", tantargyId),
|
|
new CommandParameter("pTanevId", tanevId)
|
|
};
|
|
|
|
const string commandText = @"
|
|
SELECT
|
|
T_ORARENDIORA_OSSZES.ID as ID,
|
|
T_ORARENDIORA_OSSZES.C_HETIREND as Hetirend,
|
|
T_ORARENDIORA_OSSZES.C_HETNAPJA as Hetnapja,
|
|
T_ORARENDIORA_OSSZES.C_ORASZAM as Ora,
|
|
T_TEREM_OSSZES.C_NEV as Terem,
|
|
T_ORARENDIORA_OSSZES.C_ORAERVENYESSEGKEZDETE as ErvenyessegKezdete,
|
|
T_ORARENDIORA_OSSZES.C_ORAERVENYESSEGVEGE as ErvenyessegVege,
|
|
ocs.C_NEV as OsztCsop
|
|
FROM T_ORARENDIORA_OSSZES
|
|
INNER JOIN T_TANTARGY_OSSZES on T_ORARENDIORA_OSSZES.C_TANTARGYID = T_TANTARGY_OSSZES.ID
|
|
INNER JOIN T_CSENGETESIRENDORA_OSSZES on T_ORARENDIORA_OSSZES.C_CSENGETESIRENDORAID = T_CSENGETESIRENDORA_OSSZES.ID
|
|
INNER JOIN T_TEREM_OSSZES on t_orarendiora_OSSZES.C_TEREMID = T_TEREM_OSSZES.ID
|
|
INNER JOIN T_OSZTALYCSOPORT_OSSZES ocs on T_ORARENDIORA_OSSZES.c_osztalycsoportid = ocs.id
|
|
WHERE
|
|
T_ORARENDIORA_OSSZES.TOROLT='F' AND T_TANTARGY_OSSZES.ID=:pTantargyID AND T_ORARENDIORA_OSSZES.C_TANEVID = :pTanevId
|
|
";
|
|
|
|
var ds = GetData(commandText, paramList, "Hetirend,Hetnapja");
|
|
return ds;
|
|
}
|
|
|
|
/// TODO: @DevKornél string.Format-ot hanyagolni kellene
|
|
public bool OrarendiOraTeremUtkozesEllenorzes(int id, DateTime ervenyessegKezdete, DateTime ervenyessegVege, int hetirend, int csengetetesiRendOra, int teremId, int hetNapja, DateTime? oraKezdete, DateTime? oraVege, bool isEgyediNap)
|
|
{
|
|
var paramlist = new List<CommandParameter>
|
|
{
|
|
new CommandParameter("pUjOraID", id),
|
|
new CommandParameter("pErvenyessegKezdete", ervenyessegKezdete.Date),
|
|
new CommandParameter("pErvenyessegVege", ervenyessegVege.Date),
|
|
new CommandParameter("pHetirend", hetirend),
|
|
new CommandParameter("pTeremID", teremId),
|
|
new CommandParameter("pOraKezdete", oraKezdete),
|
|
new CommandParameter("pOraVege", oraVege),
|
|
new CommandParameter("pIsEgyediNap", isEgyediNap ? 'T' : 'F')
|
|
};
|
|
|
|
var commandText = string.Format(@"
|
|
SELECT
|
|
T_ORARENDIORA_OSSZES.ID
|
|
,T_ORARENDIORA_OSSZES.C_ORAERVENYESSEGKEZDETE
|
|
,T_ORARENDIORA_OSSZES.C_ORAERVENYESSEGVEGE
|
|
,T_ORARENDIORA_OSSZES.C_TEREMID
|
|
,T_ORARENDIORA_OSSZES.C_HETNAPJA
|
|
,T_ORARENDIORA_OSSZES.C_CSENGETESIRENDORAID
|
|
FROM T_ORARENDIORA_OSSZES
|
|
INNER JOIN t_terem ON T_ORARENDIORA_OSSZES.C_TEREMID = t_terem.id
|
|
INNER JOIN T_OSZTALYCSOPORT_OSSZES ocs ON ocs.ID = T_ORARENDIORA_OSSZES.C_OSZTALYCSOPORTID AND ocs.TOROLT = 'F'
|
|
LEFT JOIN T_TANEVRENDJE_OSSZES tr ON tr.C_TANEVID = T_ORARENDIORA_OSSZES.C_TANEVID AND tr.C_INTEZMENYID = T_ORARENDIORA_OSSZES.C_INTEZMENYID AND tr.C_NAPTIPUSA = 1402 AND tr.TOROLT = 'F'
|
|
WHERE
|
|
T_ORARENDIORA_OSSZES.TOROLT='F'
|
|
AND T_ORARENDIORA_OSSZES.C_EGYEDINAP = :pIsEgyediNap
|
|
AND T_ORARENDIORA_OSSZES.ID != :pUjOraID
|
|
AND IIF(ocs.C_VEGZOSEVFOLYAM = 'T' AND T_ORARENDIORA_OSSZES.C_ORAERVENYESSEGVEGE > tr.C_DATUM, tr.C_DATUM, T_ORARENDIORA_OSSZES.C_ORAERVENYESSEGVEGE) > @pErvenyessegKezdete
|
|
AND T_ORARENDIORA_OSSZES.C_ORAERVENYESSEGKEZDETE < :pErvenyessegVege
|
|
AND T_ORARENDIORA_OSSZES.C_TEREMID = :pTeremID
|
|
AND (t_terem.C_TOBBORATLEHETTARTANI IS NULL OR t_terem.C_TOBBORATLEHETTARTANI = 'F')
|
|
|
|
AND (
|
|
(CAST(:pOraKezdete as TIME) >= CAST(T_ORARENDIORA_OSSZES.C_ORAKEZDETE as TIME) AND CAST(:pOraKezdete as TIME) < CAST(T_ORARENDIORA_OSSZES.C_ORAVEGE as TIME))
|
|
OR
|
|
(CAST(:pOraVege as TIME) > CAST(T_ORARENDIORA_OSSZES.C_ORAKEZDETE as TIME) AND CAST(:pOraVege as TIME) <= CAST(T_ORARENDIORA_OSSZES.C_ORAVEGE as TIME))
|
|
)
|
|
AND (T_ORARENDIORA_OSSZES.C_HETIREND = :pHetirend OR T_ORARENDIORA_OSSZES.C_HETIREND = {0} OR (:pHetirend = {0} AND :pErvenyessegKezdete <> :pErvenyessegVege))
|
|
", (int)HetiRendTipusEnum.MindegyikHet);
|
|
|
|
if (!isEgyediNap)
|
|
{
|
|
paramlist.Add(new CommandParameter("pHetNapja", hetNapja));
|
|
commandText += " AND T_ORARENDIORA_OSSZES.C_HETNAPJA = :pHetNapja";
|
|
}
|
|
|
|
var ds = GetData(commandText, paramlist);
|
|
return ds.Tables[0].Rows.Count > 0;
|
|
}
|
|
|
|
public void KozpontilagToroltOraVisszaallitasa(List<int> kozpontilagToroltOraIdList, int felhasznaloId)
|
|
{
|
|
var commandText = string.Format(@"
|
|
UPDATE T_ORARENDIORA_OSSZES SET
|
|
TOROLT = 'F',
|
|
C_KozpontilagToroltOraGroupId = null,
|
|
LASTCHANGED = GETDATE(),
|
|
MODIFIER = :pModifierId,
|
|
SERIAL = SERIAL + 1
|
|
WHERE ID IN ({0})
|
|
", SqlLogic.ParseListToParameter(kozpontilagToroltOraIdList));
|
|
|
|
using (SDACommand command = UserContext.Instance.SDAConnection.CreateCommand(commandText))
|
|
{
|
|
command.Transaction = UserContext.Instance.SDATransaction;
|
|
command.Parameters.Add("pModifierId", felhasznaloId);
|
|
command.ExecuteNonQuery();
|
|
DalHelper.Commit();
|
|
}
|
|
}
|
|
|
|
/// TODO: @DevKornél string.Format-ot hanyagolni kellene, legyen paraméter
|
|
public bool OrarendiOraTanarUtkozesEllenorzes(int id, DateTime ervenyessegKezdete, DateTime ervenyessegVege, int hetirend, int csengetetesiRendOra, List<int> tanarId, int hetNapja, DateTime? oraKezdete, DateTime? oraVege)
|
|
{
|
|
var paramlist = new List<CommandParameter>
|
|
{
|
|
new CommandParameter("pUjOraID", id),
|
|
new CommandParameter("pErvenyessegKezdete", ervenyessegKezdete.Date),
|
|
new CommandParameter("pErvenyessegVege", ervenyessegVege.Date),
|
|
new CommandParameter("pHetirend", hetirend),
|
|
new CommandParameter("pHetNapja", hetNapja),
|
|
new CommandParameter("pOraKezdete", oraKezdete),
|
|
new CommandParameter("pOraVege", oraVege)
|
|
};
|
|
|
|
var commandText = string.Format(@"
|
|
SELECT
|
|
T_ORARENDIORA_OSSZES.ID
|
|
,T_ORARENDIORA_OSSZES.C_ORAERVENYESSEGKEZDETE
|
|
,T_ORARENDIORA_OSSZES.C_ORAERVENYESSEGVEGE
|
|
,T_ORARENDIORA_OSSZES.C_HETNAPJA
|
|
,T_ORARENDIORA_OSSZES.C_CSENGETESIRENDORAID
|
|
FROM T_ORARENDIORA_OSSZES
|
|
JOIN T_FOGLALKOZAS_OSSZES on T_ORARENDIORA_OSSZES.C_FOGLALKOZASID = T_FOGLALKOZAS_OSSZES.id
|
|
WHERE
|
|
T_ORARENDIORA_OSSZES.TOROLT='F'
|
|
AND T_ORARENDIORA_OSSZES.ID != :pUjOraID
|
|
AND T_ORARENDIORA_OSSZES.C_ORAERVENYESSEGVEGE >= :pErvenyessegKezdete
|
|
AND T_ORARENDIORA_OSSZES.C_ORAERVENYESSEGKEZDETE <= :pErvenyessegVege
|
|
AND T_FOGLALKOZAS_OSSZES.C_TANARID IN (" + string.Join(",", tanarId) + @")
|
|
AND T_ORARENDIORA_OSSZES.C_HETNAPJA = :pHetNapja
|
|
AND (
|
|
(CAST(:pOraKezdete as TIME) >= CAST(T_ORARENDIORA_OSSZES.C_ORAKEZDETE as TIME) AND CAST(:pOraKezdete as TIME) < CAST(T_ORARENDIORA_OSSZES.C_ORAVEGE as TIME))
|
|
OR
|
|
(CAST(:pOraVege as TIME) > CAST(T_ORARENDIORA_OSSZES.C_ORAKEZDETE as TIME) AND CAST(:pOraVege as TIME) <= CAST(T_ORARENDIORA_OSSZES.C_ORAVEGE as TIME))
|
|
)
|
|
AND (T_ORARENDIORA_OSSZES.C_HETIREND = :pHetirend OR T_ORARENDIORA_OSSZES.C_HETIREND = {0} OR (:pHetirend = {0} AND :pErvenyessegKezdete <> :pErvenyessegVege))
|
|
", (int)HetiRendTipusEnum.MindegyikHet);
|
|
|
|
var ds = GetData(commandText, paramlist);
|
|
return ds.Tables[0].Rows.Count > 0;
|
|
}
|
|
|
|
/// TODO: @DevKornél string.Format-ot hanyagolni kellene, legyen paraméter
|
|
public bool OrarendiOraOsztalyCsoportUtkozesEllenorzes(int id, DateTime ervenyessegKezdete, DateTime ervenyessegVege, int hetirend, int csengetetesiRendOra, int osztalyCsoportId, int hetNapja, DateTime? oraKezdete, DateTime? oraVege)
|
|
{
|
|
var paramlist = new List<CommandParameter>
|
|
{
|
|
new CommandParameter("pUjOraID", id),
|
|
new CommandParameter("pErvenyessegKezdete", ervenyessegKezdete.Date),
|
|
new CommandParameter("pErvenyessegVege", ervenyessegVege.Date),
|
|
new CommandParameter("pHetirend", hetirend),
|
|
new CommandParameter("pHetNapja", hetNapja),
|
|
new CommandParameter("pOsztalyCsoportID", osztalyCsoportId),
|
|
new CommandParameter("pOraKezdete", oraKezdete),
|
|
new CommandParameter("pOraVege", oraVege)
|
|
};
|
|
|
|
var commandText = string.Format(@"
|
|
SELECT
|
|
T_ORARENDIORA_OSSZES.ID
|
|
,T_ORARENDIORA_OSSZES.C_ORAERVENYESSEGKEZDETE
|
|
,T_ORARENDIORA_OSSZES.C_ORAERVENYESSEGVEGE
|
|
,T_ORARENDIORA_OSSZES.C_HETNAPJA
|
|
,T_ORARENDIORA_OSSZES.C_CSENGETESIRENDORAID
|
|
FROM T_ORARENDIORA_OSSZES
|
|
JOIN T_FOGLALKOZAS_OSSZES on T_ORARENDIORA_OSSZES.C_FOGLALKOZASID = T_FOGLALKOZAS_OSSZES.id
|
|
WHERE
|
|
T_ORARENDIORA_OSSZES.TOROLT='F'
|
|
AND T_ORARENDIORA_OSSZES.ID != :pUjOraID
|
|
AND T_ORARENDIORA_OSSZES.C_ORAERVENYESSEGVEGE >= :pErvenyessegKezdete
|
|
AND T_ORARENDIORA_OSSZES.C_ORAERVENYESSEGKEZDETE <= :pErvenyessegVege
|
|
AND T_FOGLALKOZAS_OSSZES.C_OSZTALYCSOPORTID = :pOsztalyCsoportID
|
|
AND T_ORARENDIORA_OSSZES.C_HETNAPJA = :pHetNapja
|
|
AND (
|
|
(CAST(:pOraKezdete as TIME) >= CAST(T_ORARENDIORA_OSSZES.C_ORAKEZDETE as TIME) AND CAST(:pOraKezdete as TIME) < CAST(T_ORARENDIORA_OSSZES.C_ORAVEGE as TIME))
|
|
OR
|
|
(CAST(:pOraVege as TIME) > CAST(T_ORARENDIORA_OSSZES.C_ORAKEZDETE as TIME) AND CAST(:pOraVege as TIME) <= CAST(T_ORARENDIORA_OSSZES.C_ORAVEGE as TIME))
|
|
)
|
|
AND (T_ORARENDIORA_OSSZES.C_HETIREND = :pHetirend OR T_ORARENDIORA_OSSZES.C_HETIREND = {0} OR (:pHetirend = {0} AND :pErvenyessegKezdete <> :pErvenyessegVege))
|
|
", (int)HetiRendTipusEnum.MindegyikHet);
|
|
|
|
var ds = GetData(commandText, paramlist);
|
|
return ds.Tables[0].Rows.Count > 0;
|
|
}
|
|
|
|
public DataSet GetOsztalyCsoportOrarendiOrai(int osztalyCsoportId, int tanevId, bool isFromSzervezet)
|
|
{
|
|
const string commandText = @"
|
|
SELECT
|
|
T_ORARENDIORA_OSSZES.ID as ID,
|
|
T_ORARENDIORA_OSSZES.C_HETIREND as Hetirend,
|
|
T_ORARENDIORA_OSSZES.C_HETNAPJA as Nap,
|
|
T_ORARENDIORA_OSSZES.C_ORASZAM as Ora,
|
|
T_OSZTALYCSOPORT_OSSZES.C_NEV as DualisCsoportNeve,
|
|
T_TANTARGY_OSSZES.C_NEV as TantargyNev,
|
|
T_TEREM_OSSZES.C_NEV as TeremNev,
|
|
T_ORARENDIORA_OSSZES.C_ORAERVENYESSEGKEZDETE as ErvenyessegKezdete,
|
|
T_ORARENDIORA_OSSZES.C_ORAERVENYESSEGVEGE as ErvenyessegVege
|
|
FROM
|
|
T_ORARENDIORA_OSSZES
|
|
INNER JOIN
|
|
T_OSZTALYCSOPORT_OSSZES on T_OSZTALYCSOPORT_OSSZES.ID = T_ORARENDIORA_OSSZES.C_OSZTALYCSOPORTID AND T_OSZTALYCSOPORT_OSSZES.TOROLT='F'
|
|
INNER JOIN
|
|
T_TANTARGY_OSSZES on T_ORARENDIORA_OSSZES.C_TANTARGYID = T_TANTARGY_OSSZES.ID AND T_TANTARGY_OSSZES.TOROLT='F'
|
|
INNER JOIN
|
|
T_TEREM_OSSZES on T_ORARENDIORA_OSSZES.C_TEREMID = T_TEREM_OSSZES.ID AND T_TEREM_OSSZES.TOROLT='F'
|
|
WHERE
|
|
T_OSZTALYCSOPORT_OSSZES.ID = :pOsztalyCsoportID
|
|
AND T_ORARENDIORA_OSSZES.TOROLT='F' AND T_ORARENDIORA_OSSZES.C_TANEVID = :pTanevId
|
|
";
|
|
|
|
var param = new List<CommandParameter> { new CommandParameter("pOsztalyCsoportID", osztalyCsoportId), new CommandParameter("pTanevId", tanevId) };
|
|
|
|
var ds = GetData(commandText, param, "Hetirend,Nap");
|
|
return ds;
|
|
}
|
|
|
|
public DataSet GetOrarendiOrakByTeremId(int teremId, int tanevId)
|
|
{
|
|
var parameters = new List<CommandParameter>
|
|
{
|
|
new CommandParameter("pTeremID", teremId),
|
|
new CommandParameter("pTanevId", tanevId)
|
|
};
|
|
|
|
const string commandText = @"
|
|
SELECT
|
|
oo.ID ID
|
|
,oo.C_HETIREND Hetirend
|
|
,oo.C_HETNAPJA HetNapja
|
|
,oo.C_ORASZAM Ora
|
|
,ocs.C_NEV OsztalyCsoport
|
|
,tant.C_NEV TantargyNev
|
|
,t.C_NEV TeremNev
|
|
,oo.C_ORAERVENYESSEGKEZDETE ErvenyessegKezdete
|
|
,oo.C_ORAERVENYESSEGVEGE ErvenyessegVege
|
|
FROM T_ORARENDIORA_OSSZES oo
|
|
INNER JOIN T_TEREM_OSSZES t ON t.ID = oo.C_TEREMID
|
|
AND t.TOROLT = 'F'
|
|
LEFT JOIN T_OSZTALYCSOPORT_OSSZES ocs ON ocs.ID = oo.C_OSZTALYCSOPORTID
|
|
AND ocs.TOROLT = 'F'
|
|
LEFT JOIN T_TANTARGY_OSSZES tant ON tant.ID = oo.C_TANTARGYID
|
|
AND tant.TOROLT = 'F'
|
|
WHERE oo.TOROLT = 'F'
|
|
AND oo.C_TANEVID = @pTanevId
|
|
AND oo.C_TEREMID = @pTeremID
|
|
";
|
|
|
|
var ds = GetData(commandText, parameters, "Hetirend,Hetnapja");
|
|
return ds;
|
|
}
|
|
|
|
public DataSet GetNemElerhetoOrarendiElemekDataSet(int tanevId)
|
|
{
|
|
using (var command = new SDACommand())
|
|
{
|
|
command.Connection = UserContext.Instance.SDAConnection;
|
|
command.Transaction = UserContext.Instance.SDATransaction;
|
|
command.CommandType = CommandType.StoredProcedure;
|
|
command.CommandText = "uspGetNemElerhetoOrarendiElemekData";
|
|
|
|
command.Parameters.Add("pTanevId", tanevId);
|
|
|
|
var ds = new DataSet();
|
|
using (var adapter = new SDADataAdapter())
|
|
{
|
|
adapter.SelectCommand = command;
|
|
adapter.Fill(ds);
|
|
}
|
|
|
|
return ds;
|
|
}
|
|
}
|
|
|
|
public DataSet GetHetirendek(int tanevId)
|
|
{
|
|
var parameters = new List<CommandParameter>
|
|
{
|
|
new CommandParameter("pTanevId", tanevId)
|
|
};
|
|
|
|
const string commandText = @"
|
|
SELECT
|
|
nn.ID ID,
|
|
nn.C_NAPDATUMA Datum,
|
|
nn.C_HETIREND Hetirend
|
|
FROM
|
|
T_NAPTARINAP_OSSZES nn
|
|
WHERE
|
|
nn.TOROLT = 'F' and nn.C_TANEVID = :pTanevId
|
|
";
|
|
|
|
var ds = GetData(commandText, parameters, "Hetirend");
|
|
return ds;
|
|
}
|
|
|
|
public DataSet GetNaptariHetekHetirendek(int tanevId, bool replaceSzunetToMindenHet = false)
|
|
{
|
|
using (var sdaCommand = new SDACommand())
|
|
{
|
|
const string commandText = @"
|
|
SELECT
|
|
nh.ID as ID
|
|
,nh.C_HETKEZDONAPJA as Datum
|
|
,IIF( :pReplaceSzunetToMindenHet = 1,ISNULL(nh.C_HETIREND,1554),nh.C_HETIREND) as Hetirend
|
|
FROM
|
|
T_NAPTARIHET_OSSZES nh
|
|
WHERE
|
|
nh.TOROLT = 'F' and nh.C_TANEVID = :pTanevId
|
|
";
|
|
|
|
sdaCommand.Connection = UserContext.Instance.SDAConnection;
|
|
sdaCommand.Transaction = UserContext.Instance.SDATransaction;
|
|
sdaCommand.CommandType = CommandType.Text;
|
|
sdaCommand.CommandText = commandText;
|
|
|
|
sdaCommand.Parameters.Add("pTanevId", SDADBType.Int).Value = tanevId;
|
|
sdaCommand.Parameters.Add("pReplaceSzunetToMindenHet", SDADBType.Int).Value = replaceSzunetToMindenHet ? 1 : 0;
|
|
|
|
var dataSet = new DataSet();
|
|
using (var adapter = new SDADataAdapter())
|
|
{
|
|
adapter.SelectCommand = sdaCommand;
|
|
adapter.Fill(dataSet);
|
|
}
|
|
|
|
DataTable dataTable = dataSet.Tables[0];
|
|
SetDNAME(dataTable, "Hetirend");
|
|
DataTable result = SortingAndPaging(dataTable, GridParameters);
|
|
|
|
return result.AsDataSet();
|
|
}
|
|
}
|
|
|
|
public int GetHetirend(DateTime date)
|
|
{
|
|
var commandParameterList = new List<CommandParameter>
|
|
{
|
|
new CommandParameter("pDate", date.Date)
|
|
};
|
|
|
|
const string commandText = @"
|
|
SELECT
|
|
T_NAPTARINAP.ID ID
|
|
,T_NAPTARINAP.C_NAPDATUMA Datum
|
|
,T_NAPTARINAP.C_HETIREND Hetirend
|
|
FROM
|
|
T_NAPTARINAP
|
|
WHERE
|
|
T_NAPTARINAP.TOROLT = 'F'
|
|
AND CAST(C_NAPDATUMA AS DATE) = :pDate
|
|
";
|
|
|
|
var dataSet = GetData(commandText, commandParameterList, "Hetirend");
|
|
|
|
return (int)dataSet.Tables[0].Rows[0]["Hetirend"];
|
|
}
|
|
|
|
public DataSet GetIsMunkaszunetiNap(DateTime datum, int osztalycsoportId)
|
|
{
|
|
var date = datum.Date;
|
|
|
|
var parameters = new List<CommandParameter>
|
|
{
|
|
new CommandParameter("pDatum", date),
|
|
new CommandParameter("pOsztalycsoportId", osztalycsoportId)
|
|
};
|
|
|
|
// A belső CASE vizsgálja, hogy ha nem üres a tanévrendje tábla; a külső CASE lefedi azt is ha üres a tanévrendje tábla...
|
|
const string commandText = @"
|
|
IF EXISTS(
|
|
SELECT TOP 1 1 FROM T_OSZTALYCSOPORT_TANEVRENDJE ot
|
|
INNER JOIN T_TANEVRENDJE tr ON ot.C_TANEVRENDJEID=tr.ID AND tr.TOROLT='F' AND tr.C_DATUM =:pDatum AND tr.C_NAPTIPUSA=1386
|
|
WHERE ot.C_OSZTALYCSOPORTID =:pOsztalycsoportId
|
|
) SELECT 1
|
|
|
|
ELSE IF EXISTS(
|
|
SELECT TOP 1 1 FROM T_OSZTALYCSOPORT_TANEVRENDJE ot
|
|
INNER JOIN T_TANEVRENDJE tr ON ot.C_TANEVRENDJEID=tr.ID AND tr.TOROLT='F' AND tr.C_DATUM =:pDatum AND tr.C_NAPTIPUSA<>1386
|
|
WHERE ot.C_OSZTALYCSOPORTID =:pOsztalycsoportId
|
|
) SELECT 0
|
|
|
|
ELSE IF EXISTS(
|
|
SELECT TOP 1 1 FROM T_TANEVRENDJE WHERE TOROLT='F' AND C_DATUM =:pDatum AND C_NAPTIPUSA=1386 AND C_OSSZESCSOPORTRAVONATKOZIK = 'T'
|
|
) SELECT 1
|
|
ELSE
|
|
SELECT 0
|
|
";
|
|
|
|
DataSet ds = GetData(commandText, parameters);
|
|
|
|
return ds;
|
|
}
|
|
|
|
public bool OrarendiOraDuplikacioEllenorzes(int tanarId, int osztalyCsoportId, int teremId, int tantargyId, DateTime idopont, int oraszam)
|
|
{
|
|
using (var command = new SDACommand())
|
|
{
|
|
command.Connection = UserContext.Instance.SDAConnection;
|
|
command.Transaction = UserContext.Instance.SDATransaction;
|
|
|
|
command.CommandText = @"
|
|
SELECT
|
|
count(*)
|
|
FROM T_ORARENDIORA oo
|
|
join T_FOGLALKOZAS f on f.ID = oo.C_FOGLALKOZASID AND f.TOROLT = 'F'
|
|
left join T_FOGLALKOZASOK_TANAROK on T_FOGLALKOZASOK_TANAROK.C_FOGLALKOZASOKID = T_FOGLALKOZAS.ID
|
|
join T_TANITASIORA tto ON
|
|
tto.C_ORARENDIORAGROUPID = oo.C_ORARENDIORAGROUPID AND
|
|
tto.C_DATUM BETWEEN oo.C_ORAERVENYESSEGKEZDETE AND
|
|
oo.C_ORAERVENYESSEGVEGE and tto.C_ORAKEZDETE = :pIdopont and tto.TOROLT = 'F'
|
|
oo.C_ORAERVENYESSEGVEGE and tto.C_ORAKEZDETE = :pIdopont and tto.TOROLT = 'F'
|
|
WHERE
|
|
T_FOGLALKOZAS.C_TANARID = :pTanar
|
|
AND f.C_OSZTALYCSOPORTID = :pOsztalyCsoport
|
|
AND oo.C_TEREMID = :pTerem
|
|
AND f.C_TANTARGYID = :pTantargy
|
|
AND oo.C_ORASZAM = :pOraszam
|
|
AND oo.TOROLT = 'F'";
|
|
|
|
command.Parameters.Add("pTanar", tanarId);
|
|
command.Parameters.Add("pOsztalyCsoport", osztalyCsoportId);
|
|
command.Parameters.Add("pTerem", teremId);
|
|
command.Parameters.Add("pTantargy", tantargyId);
|
|
command.Parameters.Add("pIdopont", idopont);
|
|
command.Parameters.Add("pOraszam", oraszam);
|
|
|
|
return Convert.ToInt32(command.ExecuteScalar()) > 0;
|
|
}
|
|
}
|
|
|
|
public bool GetHetirendDictionaryItemIsUsed(int hetirendId)
|
|
{
|
|
var parameters = new List<CommandParameter>
|
|
{
|
|
new CommandParameter("pHetirendID", hetirendId)
|
|
};
|
|
|
|
const string command = @"
|
|
SELECT
|
|
(
|
|
CASE
|
|
WHEN
|
|
MaxTable.MaxValue = 1 THEN 1
|
|
ELSE 0
|
|
END
|
|
)
|
|
ReturnValue
|
|
FROM
|
|
(
|
|
SELECT
|
|
MAX (BoolValue) MaxValue
|
|
FROM
|
|
(
|
|
SELECT
|
|
(CASE
|
|
WHEN T_ORARENDIORA.C_HETIREND = :pHetirendID THEN 1
|
|
ELSE 0
|
|
END) BoolValue
|
|
FROM
|
|
T_ORARENDIORA
|
|
) BoolValuesTable
|
|
)
|
|
MaxTable
|
|
";
|
|
|
|
var ds = GetData(command, parameters);
|
|
var ret = Convert.ToBoolean(ds.Tables[0].Rows[0]["ReturnValue"]);
|
|
|
|
return ret;
|
|
}
|
|
|
|
public bool GetAlreadyHaveOrarendiOra(int tanevId)
|
|
{
|
|
string command = $@"
|
|
SELECT
|
|
CASE
|
|
WHEN
|
|
(SELECT COUNT(*) FROM T_ORARENDIORA WHERE T_ORARENDIORA.TOROLT = 'F' AND C_TANEVID = {tanevId}) > 0 THEN 1
|
|
ELSE 0
|
|
END ReturnValue
|
|
";
|
|
|
|
var ds = GetData(command);
|
|
var ret = Convert.ToBoolean(ds.Tables[0].Rows[0]["ReturnValue"]);
|
|
|
|
return ret;
|
|
}
|
|
|
|
public bool IsOrarendiOraHelyettesitoTanar(int tanoraId, int tanarId, int hetSorszam)
|
|
{
|
|
using (var command = new SDACommand
|
|
{
|
|
Connection = UserContext.Instance.SDAConnection,
|
|
Transaction = UserContext.Instance.SDATransaction
|
|
})
|
|
{
|
|
command.Parameters.Add("pTanoraID", tanoraId);
|
|
command.Parameters.Add("pTanarID", tanarId);
|
|
command.Parameters.Add("pHetsorszam", hetSorszam);
|
|
|
|
command.CommandText = @"
|
|
SELECT 1
|
|
FROM
|
|
T_ORARENDIORA
|
|
LEFT JOIN T_HELYETTESITESIIDOSZAK on (T_ORARENDIORA.ID = T_HELYETTESITESIIDOSZAK.C_HELYETTESITETTORARENDID
|
|
And T_HELYETTESITESIIDOSZAK.C_HETSORSZAMA = :pHetsorszam )
|
|
WHERE T_ORARENDIORA.TOROLT ='F' AND T_ORARENDIORA.ID = :pTanoraID AND T_HELYETTESITESIIDOSZAK.C_HELYETTESTANAROKID = :pTanarID
|
|
";
|
|
|
|
var result = command.ExecuteScalar();
|
|
if (result != null)
|
|
return true;
|
|
|
|
return false;
|
|
}
|
|
}
|
|
|
|
public (int? HelyettesitesId, int? ErrorId) SaveHelyettesites(int orarendiOraId, int tanarId, int helyettesitesTipus, string helyettesItesOka, int hetszam, DateTime helyettesitesNapja, bool isTuloraMentes)
|
|
{
|
|
var orarendiOra = Get(orarendiOraId);
|
|
|
|
if (VanHelyettesitoAzIdoszakra(orarendiOra, helyettesitesNapja))
|
|
{
|
|
var errorId = 4247; /*Már van bejegyzett helyettesítés az adott időszakra.*/
|
|
return (null, errorId);
|
|
}
|
|
|
|
if (orarendiOra.TanarId == tanarId)
|
|
{
|
|
var errorId = 4547; /*A helyettesítő tanár nem egyezhet meg az helyettesített tanárral.*/
|
|
return (null, errorId);
|
|
}
|
|
|
|
if (VanTanitasiOraAzIdoszakra(helyettesitesNapja, orarendiOra.OrarendiOraGroupId.Value))
|
|
{
|
|
var errorId = 4548; /*Neplózott órához nem vehető fel helyettesítés*/
|
|
return (null, errorId);
|
|
}
|
|
|
|
var helyettesitesiIdoszak = HelyettesitesiIdoszak.GiveAnInstance();
|
|
helyettesitesiIdoszak.HelyettesTanarokId = tanarId;
|
|
helyettesitesiIdoszak.HetSorszama = hetszam;
|
|
helyettesitesiIdoszak.HelyettesitesTipus = helyettesitesTipus;
|
|
helyettesitesiIdoszak.HelyettesitesOka = helyettesItesOka;
|
|
helyettesitesiIdoszak.HelyettesitettOrarendId = orarendiOraId;
|
|
helyettesitesiIdoszak.HelyettesitesNapja = helyettesitesNapja;
|
|
helyettesitesiIdoszak.IsTulora = isTuloraMentes;
|
|
|
|
helyettesitesiIdoszak.Insert();
|
|
DalHelper.Commit();
|
|
|
|
DalHelper.HelyettesitesDAL().UpdateDktFeladatHelyettesitoAlkalmazott(orarendiOraId, helyettesitesNapja, tanarId);
|
|
|
|
var helyettesitesId = helyettesitesiIdoszak.ID;
|
|
return (helyettesitesId, null);
|
|
}
|
|
|
|
public void GenerateTeljesOrarend(int intezmenyId, int tanevId, int? orarendioraId = null)
|
|
{
|
|
using (var command = new SDACommand())
|
|
{
|
|
command.Connection = UserContext.Instance.SDAConnection;
|
|
command.Transaction = UserContext.Instance.SDATransaction;
|
|
command.CommandType = CommandType.StoredProcedure;
|
|
command.CommandText = "uspGenerateTeljesOrarend";
|
|
|
|
command.Parameters.Add("pIntezmenyId", intezmenyId);
|
|
command.Parameters.Add("pTanevId", tanevId);
|
|
|
|
if (orarendioraId.IsEntityId())
|
|
{
|
|
command.Parameters.Add("pOrarendioraId", orarendioraId.Value);
|
|
}
|
|
|
|
command.ExecuteNonQuery();
|
|
|
|
DalHelper.Commit();
|
|
}
|
|
|
|
}
|
|
|
|
public void UpdateHelyettesitesGroupId(int helyettesitesId, int groupId)
|
|
{
|
|
var helyettesDal = DalHelper.HelyettesitesDAL();
|
|
|
|
var helyettes = helyettesDal.Get(helyettesitesId);
|
|
helyettes.GroupId = groupId;
|
|
helyettesDal.FullUpdate(helyettes);
|
|
}
|
|
|
|
public (int? HelyettesitesId, int? ErrorId) UpdateHelyettesitoTanar(int orarendiOraId, int helyettesitesId, int ujTanarId, int helyettesitesTipus, string helyettesitesOka, DateTime helyettesitesNapja, int? groupId, bool isTuloraMentes)
|
|
{
|
|
var orarendiOra = Get(orarendiOraId);
|
|
|
|
if (orarendiOra.TanarId == ujTanarId)
|
|
{
|
|
var errorId = 4547; /*A helyettesítő tanár nem egyezhet meg az helyettesített tanárral.*/
|
|
return (null, errorId);
|
|
}
|
|
|
|
if (orarendiOra.TanitasiOra.Any(a => a.OraKezdete <= helyettesitesNapja && a.OraVege >= helyettesitesNapja && !a.Torolt))
|
|
{
|
|
var errorId = 4548; /*Naplózott órához nem vehető fel helyettesítés*/
|
|
return (null, errorId);
|
|
}
|
|
|
|
var helyettesDal = DalHelper.HelyettesitesDAL();
|
|
|
|
var helyettes = helyettesDal.Get(helyettesitesId);
|
|
helyettes.HelyettesTanarokId = ujTanarId;
|
|
helyettes.HelyettesitesTipus = helyettesitesTipus;
|
|
helyettes.HelyettesitesOka = helyettesitesOka;
|
|
helyettes.GroupId = groupId;
|
|
helyettes.IsTulora = isTuloraMentes;
|
|
helyettesDal.FullUpdate(helyettes);
|
|
|
|
return (helyettes.ID, null);
|
|
}
|
|
|
|
public DataSet GetTanarFromHelyettesites(int helyettesitesId, int intezmenyId, int tanevId)
|
|
{
|
|
using (var command = new SDACommand())
|
|
{
|
|
command.Connection = UserContext.Instance.SDAConnection;
|
|
command.Transaction = UserContext.Instance.SDATransaction;
|
|
command.Parameters.Add("pHelyettesitesId", helyettesitesId);
|
|
command.Parameters.Add("pIntezmenyId", intezmenyId);
|
|
command.Parameters.Add("pTanevId", tanevId);
|
|
|
|
command.CommandText = @"
|
|
SELECT
|
|
helyettesitesiIdoszak.C_HELYETTESTANAROKID
|
|
,helyettesitesiIdoszak.C_HELYETTESITESTIPUS
|
|
,helyettesitesiIdoszak.C_GROUPID
|
|
,helyettesitesiIdoszak.C_HELYETTESITESOKA
|
|
,felhasznalo.C_NYOMTATASINEV
|
|
FROM
|
|
T_HELYETTESITESIIDOSZAK_OSSZES helyettesitesiIdoszak
|
|
LEFT JOIN
|
|
T_FELHASZNALO_OSSZES felhasznalo ON
|
|
felhasznalo.ID = helyettesitesiIdoszak.C_HELYETTESTANAROKID
|
|
AND felhasznalo.C_INTEZMENYID = :pIntezmenyId
|
|
AND felhasznalo.C_TANEVID = :pTanevId
|
|
AND felhasznalo.TOROLT = 'F'
|
|
INNER JOIN
|
|
T_ALKALMAZOTT_OSSZES alkalmazott ON
|
|
alkalmazott.ID = felhasznalo.ID
|
|
AND alkalmazott.C_ALINTEZMENYID = :pIntezmenyId
|
|
AND alkalmazott.C_ALTANEVID = :pTanevId
|
|
AND alkalmazott.TOROLT = 'F'
|
|
WHERE
|
|
helyettesitesiIdoszak.ID = :pHelyettesitesId
|
|
AND helyettesitesiIdoszak.C_INTEZMENYID = :pIntezmenyId
|
|
AND helyettesitesiIdoszak.C_TANEVID = :pTanevId
|
|
AND helyettesitesiIdoszak.TOROLT = 'F'
|
|
";
|
|
|
|
var ds = new DataSet();
|
|
using (var adapter = new SDADataAdapter())
|
|
{
|
|
adapter.SelectCommand = command;
|
|
adapter.Fill(ds);
|
|
}
|
|
|
|
return ds;
|
|
}
|
|
}
|
|
|
|
public DataSet GetCalendarMinMax(int intezmenyId, int tanevId)
|
|
{
|
|
using (var command = new SDACommand())
|
|
{
|
|
command.Connection = UserContext.Instance.SDAConnection;
|
|
command.Transaction = UserContext.Instance.SDATransaction;
|
|
command.CommandType = CommandType.StoredProcedure;
|
|
command.CommandText = "uspGetCalendarMinMax";
|
|
|
|
command.Parameters.Add("pIntezmenyId", intezmenyId);
|
|
command.Parameters.Add("pTanevId", tanevId);
|
|
|
|
var dts = new DataSet();
|
|
using (var adapter = new SDADataAdapter())
|
|
{
|
|
adapter.SelectCommand = command;
|
|
adapter.Fill(dts);
|
|
}
|
|
return dts;
|
|
}
|
|
|
|
}
|
|
|
|
public DataSet CheckTanarOra(DateTime startDate, DateTime endDate, int tanarId, int intezmenyId, int tanevId, int? oraszam, bool isNapirend, bool isEgyediHelyettesites)
|
|
{
|
|
using (var command = new SDACommand())
|
|
{
|
|
command.Connection = UserContext.Instance.SDAConnection;
|
|
command.Transaction = UserContext.Instance.SDATransaction;
|
|
command.CommandType = CommandType.StoredProcedure;
|
|
command.CommandText = "uspCheckTanarOra";
|
|
|
|
command.Parameters.Add(nameof(intezmenyId), intezmenyId);
|
|
command.Parameters.Add(nameof(tanevId), tanevId);
|
|
command.Parameters.Add(nameof(tanarId), tanarId);
|
|
command.Parameters.Add(nameof(startDate), startDate);
|
|
command.Parameters.Add(nameof(endDate), endDate);
|
|
command.Parameters.Add(nameof(oraszam), oraszam);
|
|
command.Parameters.Add(nameof(isNapirend), isNapirend);
|
|
command.Parameters.Add(nameof(isEgyediHelyettesites), isEgyediHelyettesites);
|
|
|
|
var dts = new DataSet();
|
|
using (var adapter = new SDADataAdapter())
|
|
{
|
|
adapter.SelectCommand = command;
|
|
adapter.Fill(dts);
|
|
}
|
|
return dts;
|
|
}
|
|
}
|
|
|
|
public DataSet GetNemKotottMunkaIdo(int intezmenyId, int tanevId, DateTime start, DateTime end, int tanarId)
|
|
{
|
|
using (SDACommand command = new SDACommand())
|
|
{
|
|
command.Connection = UserContext.Instance.SDAConnection;
|
|
command.Transaction = UserContext.Instance.SDATransaction;
|
|
command.CommandType = CommandType.StoredProcedure;
|
|
command.CommandText = "uspGetNemKotottMunkaIdo";
|
|
command.Parameters.Add("IntezmenyId", intezmenyId);
|
|
command.Parameters.Add("TanevId", tanevId);
|
|
command.Parameters.Add("TanarId", tanarId);
|
|
command.Parameters.Add("IdoszakKezdete", start);
|
|
command.Parameters.Add("IdoszakVege", end);
|
|
|
|
var dts = new DataSet();
|
|
using (var adapter = new SDADataAdapter())
|
|
{
|
|
adapter.SelectCommand = command;
|
|
adapter.Fill(dts);
|
|
}
|
|
|
|
SetDNAME(dts.Tables[0], "TorvenyKategoriaID");
|
|
SetBoolFields(dts.Tables[0], "Megtartott,TanarAltalTorolt");
|
|
return dts;
|
|
}
|
|
}
|
|
|
|
public DataSet GetNemKotottMunkaIdoByGroupId(int intezmenyId, int tanevId, DateTime start, DateTime end, string groupId)
|
|
{
|
|
using (SDACommand command = new SDACommand())
|
|
{
|
|
command.Connection = UserContext.Instance.SDAConnection;
|
|
command.Transaction = UserContext.Instance.SDATransaction;
|
|
command.CommandType = CommandType.StoredProcedure;
|
|
command.CommandText = "uspGetNemKotottMunkaIdoByGroupId";
|
|
command.Parameters.Add("IntezmenyId", intezmenyId);
|
|
command.Parameters.Add("TanevId", tanevId);
|
|
command.Parameters.Add("GroupId", groupId);
|
|
command.Parameters.Add("IdoszakKezdete", start);
|
|
command.Parameters.Add("IdoszakVege", end);
|
|
|
|
var dts = new DataSet();
|
|
using (var adapter = new SDADataAdapter())
|
|
{
|
|
adapter.SelectCommand = command;
|
|
adapter.Fill(dts);
|
|
}
|
|
|
|
SetDNAME(dts.Tables[0], "TorvenyKategoriaID");
|
|
SetBoolFields(dts.Tables[0], "Megtartott,TanarAltalTorolt");
|
|
return dts;
|
|
}
|
|
}
|
|
|
|
public DataSet GetOsztalyEsTanarOraiUtkozes(int intezmenyId, int tanevId, int tanarId, int osztalycsoportId, DateTime oraKezdete, DateTime oraVege)
|
|
{
|
|
using (var command = new SDACommand())
|
|
{
|
|
command.Connection = UserContext.Instance.SDAConnection;
|
|
command.Transaction = UserContext.Instance.SDATransaction;
|
|
command.CommandType = CommandType.StoredProcedure;
|
|
command.CommandText = "sp_GetUtkozes";
|
|
|
|
command.Parameters.Add("IntezmenyId", intezmenyId);
|
|
command.Parameters.Add("TanevId", tanevId);
|
|
|
|
command.Parameters.Add("OraKezdete", oraKezdete);
|
|
command.Parameters.Add("OraVege", oraVege);
|
|
command.Parameters.Add("TanarId", tanarId);
|
|
command.Parameters.Add("OsztalyCsoportId", osztalycsoportId);
|
|
|
|
var dts = new DataSet();
|
|
using (var adapter = new SDADataAdapter())
|
|
{
|
|
adapter.SelectCommand = command;
|
|
adapter.Fill(dts);
|
|
}
|
|
|
|
return dts;
|
|
}
|
|
}
|
|
|
|
public DataSet GetElozoOraAdatai(int tantargyId, int osztalycsoportId, int tanarId, DateTime oraKezdeteDatum, int intezmenyId, int tanevId)
|
|
{
|
|
using (var command = new SDACommand())
|
|
{
|
|
command.Connection = UserContext.Instance.SDAConnection;
|
|
command.Transaction = UserContext.Instance.SDATransaction;
|
|
command.CommandType = CommandType.StoredProcedure;
|
|
command.CommandText = "sp_GetElozoOraAdatai";
|
|
|
|
command.Parameters.Add(nameof(intezmenyId), intezmenyId);
|
|
command.Parameters.Add(nameof(oraKezdeteDatum), oraKezdeteDatum);
|
|
command.Parameters.Add(nameof(tanevId), tanevId);
|
|
|
|
command.Parameters.Add(nameof(tantargyId), tantargyId);
|
|
command.Parameters.Add(nameof(osztalycsoportId), osztalycsoportId);
|
|
command.Parameters.Add(nameof(tanarId), tanarId);
|
|
|
|
var dts = new DataSet();
|
|
using (var adapter = new SDADataAdapter())
|
|
{
|
|
adapter.SelectCommand = command;
|
|
adapter.Fill(dts);
|
|
}
|
|
return dts;
|
|
}
|
|
}
|
|
|
|
public IOrarendiOra Get()
|
|
{
|
|
return GiveAnInstance();
|
|
}
|
|
|
|
public IOrarendiOra Get(int id)
|
|
{
|
|
var entity = GiveAnInstance();
|
|
entity.LoadByID(id);
|
|
return entity;
|
|
}
|
|
|
|
public void Insert(IOrarendiOra dto, bool isTeljesTanev = false)
|
|
{
|
|
var entity = SetKAPOra(dto) as OrarendiOra;
|
|
entity.Importalt = false;
|
|
if (entity.OraKezdete.HasValue)
|
|
{
|
|
var temp = entity.OraKezdete.Value;
|
|
entity.OraKezdete = new DateTime(1900, 1, 1, temp.Hour, temp.Minute, 0);
|
|
}
|
|
if (entity.OraVege.HasValue)
|
|
{
|
|
var temp = entity.OraVege.Value;
|
|
entity.OraVege = new DateTime(1900, 1, 1, temp.Hour, temp.Minute, 0);
|
|
}
|
|
|
|
entity.Insert();
|
|
if (!entity.OrarendiOraGroupId.HasValue)
|
|
{
|
|
entity.OrarendiOraGroupId = entity.ID;
|
|
entity.Update();
|
|
}
|
|
|
|
dto.ID = entity.ID;
|
|
dto.OrarendiOraGroupId = entity.OrarendiOraGroupId;
|
|
|
|
var teremeDal = DalHelper.Terem();
|
|
|
|
var terem = teremeDal.Get(dto.TeremId);
|
|
teremeDal.FullUpdate(terem);
|
|
UpdateOrarend(dto.IntezmenyId, dto.TanevId, dto.OraErvenyessegKezdete, dto.OraErvenyessegVege, dto.ID, null, isTeljesTanev);
|
|
DalHelper.Commit();
|
|
}
|
|
|
|
public void UpdateOrarend(int intezmenyId, int tanevId, DateTime? idoszakKezdete, DateTime? idoszakVege, int? orarendioraId, int? osztalyCsoportId, bool isTeljesTanev = false)
|
|
{
|
|
using (SDACommand command = UserContext.Instance.SDAConnection.CreateCommand())
|
|
{
|
|
command.CommandType = CommandType.StoredProcedure;
|
|
command.Connection = UserContext.Instance.SDAConnection;
|
|
command.Transaction = UserContext.Instance.SDATransaction;
|
|
|
|
command.Parameters.Add("pIntezmenyId", intezmenyId);
|
|
command.Parameters.Add("pTanevId", tanevId);
|
|
|
|
if (idoszakKezdete.HasValue)
|
|
{
|
|
command.Parameters.Add("pIdoszakKezdete", idoszakKezdete);
|
|
}
|
|
else
|
|
{
|
|
command.Parameters.Add("pIdoszakKezdete", DBNull.Value);
|
|
}
|
|
|
|
if (idoszakVege.HasValue)
|
|
{
|
|
command.Parameters.Add("pIdoszakVege", idoszakVege);
|
|
}
|
|
else
|
|
{
|
|
command.Parameters.Add("pIdoszakVege", DBNull.Value);
|
|
}
|
|
|
|
if (orarendioraId == -1 || orarendioraId == null)
|
|
{
|
|
command.Parameters.Add("pOrarendiOraId", DBNull.Value);
|
|
}
|
|
else
|
|
{
|
|
command.Parameters.Add("pOrarendiOraId", orarendioraId);
|
|
}
|
|
if (osztalyCsoportId.HasValue)
|
|
{
|
|
command.Parameters.Add("pOsztalycsoportId", osztalyCsoportId);
|
|
}
|
|
else
|
|
{
|
|
command.Parameters.Add("pOsztalycsoportId", DBNull.Value);
|
|
}
|
|
|
|
command.Parameters.Add("pIsTeljesTanev", isTeljesTanev ? 1 : 0);
|
|
|
|
command.CommandText = "uspGenerateOrarend";
|
|
command.ExecuteNonQuery();
|
|
DalHelper.Commit();
|
|
}
|
|
}
|
|
|
|
public void DeleteOrarend(int intezmenyId, int tanevId, int id)
|
|
{
|
|
var commandText = @"
|
|
DELETE FROM T_ORAREND_OSSZES
|
|
WHERE
|
|
C_ORARENDIORAID = :pId AND C_TANEVID = :pTanevId AND C_INTEZMENYID = :pIntezmenyId
|
|
";
|
|
|
|
using (var command = UserContext.Instance.SDAConnection.CreateCommand(commandText))
|
|
{
|
|
command.Transaction = UserContext.Instance.SDATransaction;
|
|
command.Parameters.Add("pIntezmenyId", intezmenyId);
|
|
command.Parameters.Add("pTanevId", tanevId);
|
|
command.Parameters.Add("pId", id);
|
|
command.ExecuteNonQuery();
|
|
DalHelper.Commit();
|
|
}
|
|
}
|
|
|
|
public void Update(IOrarendiOra dto, bool isTeljesTanev = false)
|
|
{
|
|
var entity = SetKAPOra(dto) as OrarendiOra;
|
|
entity.KozpontiOraGroupId = null;
|
|
entity.Importalt = false;
|
|
if (entity.OraKezdete.HasValue)
|
|
{
|
|
var temp = entity.OraKezdete.Value;
|
|
entity.OraKezdete = new DateTime(1900, 1, 1, temp.Hour, temp.Minute, 0);
|
|
}
|
|
if (entity.OraVege.HasValue)
|
|
{
|
|
var temp = entity.OraVege.Value;
|
|
entity.OraVege = new DateTime(1900, 1, 1, temp.Hour, temp.Minute, 0);
|
|
}
|
|
|
|
entity.Update();
|
|
|
|
var teremeDal = DalHelper.Terem();
|
|
|
|
var terem = teremeDal.Get(dto.TeremId);
|
|
teremeDal.FullUpdate(terem);
|
|
UpdateOrarend(dto.IntezmenyId, dto.TanevId, dto.OraErvenyessegKezdete, dto.OraErvenyessegVege, dto.ID, null, isTeljesTanev);
|
|
DalHelper.Commit();
|
|
}
|
|
|
|
public void FullUpdate(IOrarendiOra dto, bool isTeljesTanev = false)
|
|
{
|
|
var entity = SetKAPOra(dto) as OrarendiOra;
|
|
entity.KozpontiOraGroupId = null;
|
|
entity.Importalt = false;
|
|
if (entity.OraKezdete.HasValue)
|
|
{
|
|
var temp = entity.OraKezdete.Value;
|
|
entity.OraKezdete = new DateTime(1900, 1, 1, temp.Hour, temp.Minute, 0);
|
|
}
|
|
if (entity.OraVege.HasValue)
|
|
{
|
|
var temp = entity.OraVege.Value;
|
|
entity.OraVege = new DateTime(1900, 1, 1, temp.Hour, temp.Minute, 0);
|
|
}
|
|
|
|
entity.FullUpdate();
|
|
UpdateOrarend(dto.IntezmenyId, dto.TanevId, dto.OraErvenyessegKezdete, dto.OraErvenyessegVege, dto.ID, null, isTeljesTanev);
|
|
DalHelper.Commit();
|
|
}
|
|
|
|
private IOrarendiOra SetKAPOra(IOrarendiOra orarendiOra)
|
|
{
|
|
orarendiOra.KAPOra = orarendiOra.IsDfhtOra
|
|
|| orarendiOra.IsRahangolodas
|
|
|| orarendiOra.IsTestmozgasAlapuAlprogram
|
|
|| orarendiOra.IsMuveszetAlapuAlprogram
|
|
|| orarendiOra.KIPOra
|
|
|| orarendiOra.KomplexOra
|
|
|| orarendiOra.IsEletgyakorlatAlapuAlprogram
|
|
|| orarendiOra.IsLogikaAlapuAlprogram
|
|
|| orarendiOra.IsTeorad
|
|
|| orarendiOra.IsDigitalisAlapuAlprogram;
|
|
return orarendiOra;
|
|
}
|
|
|
|
public void Delete(int id)
|
|
{
|
|
var entity = Get(id);
|
|
|
|
Delete(entity);
|
|
}
|
|
|
|
public void Delete(IOrarendiOra dto, bool isTeljesTanev = false)
|
|
{
|
|
var entity = dto as OrarendiOra;
|
|
entity.Importalt = false;
|
|
CheckHelyettesitesekBeforeDelete(entity);
|
|
DeleteOrarend(dto.IntezmenyId, dto.TanevId, dto.ID);
|
|
entity.OrarendiOraTulajdonsag.RemoveAll();
|
|
|
|
entity.Delete();
|
|
UpdateOrarend(dto.IntezmenyId, dto.TanevId, dto.OraErvenyessegKezdete, dto.OraErvenyessegVege, dto.ID, null, isTeljesTanev);
|
|
DalHelper.Commit();
|
|
}
|
|
|
|
public void DeleteTanevKezdeteElttiOrak(DateTime elsoTanitasiNap, int tanevId)
|
|
{
|
|
var list = LoadWithFilter($" AND TOROLT = 'F' AND C_TANEVID = {tanevId} AND C_ORAERVENYESSEGKEZDETE < CAST('{elsoTanitasiNap.ToString(Constants.ToStringPattern.SortableDateTimePattern)}' AS DATE)").ToList();
|
|
foreach (IOrarendiOra item in list)
|
|
{
|
|
item.Importalt = false;
|
|
if (item.OraErvenyessegVege < elsoTanitasiNap)
|
|
{
|
|
Delete(item);
|
|
}
|
|
else
|
|
{
|
|
item.OraErvenyessegKezdete = elsoTanitasiNap;
|
|
Update(item);
|
|
}
|
|
}
|
|
}
|
|
|
|
public void DeleteTanevVegeUtaniOrak(DateTime utolsoTanitasiNap, int tanevId)
|
|
{
|
|
var list = LoadWithFilter($" AND TOROLT = 'F' AND C_TANEVID = {tanevId} AND C_ORAERVENYESSEGVEGE > CAST('{utolsoTanitasiNap.ToString(Constants.ToStringPattern.SortableDateTimePattern)}' AS DATE)").ToList();
|
|
|
|
foreach (IOrarendiOra item in list)
|
|
{
|
|
item.Importalt = false;
|
|
if (item.OraErvenyessegKezdete > utolsoTanitasiNap)
|
|
{
|
|
Delete(item);
|
|
}
|
|
else
|
|
{
|
|
item.OraErvenyessegVege = utolsoTanitasiNap;
|
|
Update(item);
|
|
}
|
|
}
|
|
}
|
|
|
|
public bool NemkotottMunkaidoUtkozesEllenorzes(int intezmenyId, int tanevId, int tanarId, DateTime kezdet, DateTime veg, DateTime datum, int mindegyikHetHetiRendTipusId, int? hetirendId, string nemKotottMunkaIdoGroupId)
|
|
{
|
|
using (var command = new SDACommand())
|
|
{
|
|
command.Connection = UserContext.Instance.SDAConnection;
|
|
command.Transaction = UserContext.Instance.SDATransaction;
|
|
command.CommandType = CommandType.StoredProcedure;
|
|
command.CommandText = "uspGetNemkotottMunkaidoUtkozes";
|
|
|
|
command.Parameters.Add("pIntezmenyId", intezmenyId);
|
|
command.Parameters.Add("pTanevId", tanevId);
|
|
command.Parameters.Add("pTanarId", tanarId);
|
|
command.Parameters.Add("pDatum", datum);
|
|
command.Parameters.Add("pKezdet", kezdet);
|
|
command.Parameters.Add("pVeg", veg);
|
|
command.Parameters.Add("pMindenHetHetirendId", mindegyikHetHetiRendTipusId);
|
|
command.Parameters.Add("pHetirendId", hetirendId);
|
|
command.Parameters.Add("pNemKotottMunkaIdoGroupId", nemKotottMunkaIdoGroupId);
|
|
|
|
var dts = new DataSet();
|
|
using (var adapter = new SDADataAdapter())
|
|
{
|
|
adapter.SelectCommand = command;
|
|
adapter.Fill(dts);
|
|
}
|
|
return Convert.ToBoolean(dts.Tables[0].Rows[0]["HasUtkozes"]);
|
|
}
|
|
}
|
|
|
|
public List<int> GetElozoOranHianyzottTanulo(int tantargyId, int osztalycsoportId, DateTime datum, 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 = "uspGetElozoOranHianyzottTanulo";
|
|
|
|
command.Parameters.Add("pIntezmenyId", intezmenyId);
|
|
command.Parameters.Add("pTantargyId", tantargyId);
|
|
command.Parameters.Add("pOsztalycsoportId", osztalycsoportId);
|
|
command.Parameters.Add("pDatum", datum);
|
|
command.Parameters.Add("pTanevId", tanevId);
|
|
|
|
var dts = new DataSet();
|
|
using (var adapter = new SDADataAdapter())
|
|
{
|
|
adapter.SelectCommand = command;
|
|
adapter.Fill(dts);
|
|
}
|
|
|
|
var list = dts.Tables[0].AsEnumerable()
|
|
.Select(r => r.Field<int>("tanuloId"))
|
|
.ToList();
|
|
return list;
|
|
}
|
|
}
|
|
|
|
public int GetHetNapjaByDate(DateTime date)
|
|
{
|
|
var parameters = new List<CommandParameter>
|
|
{
|
|
new CommandParameter("phetDate", date.Date)
|
|
};
|
|
|
|
const string commandText = @"
|
|
SELECT C_HETNAPJA HetNap FROM T_NAPTARINAP_OSSZES
|
|
WHERE
|
|
T_NAPTARINAP_OSSZES.TOROLT = 'F' AND CAST(C_NAPDATUMA AS DATE) = :phetDate
|
|
";
|
|
|
|
var ds = GetData(commandText, parameters);
|
|
return (int)ds.Tables[0].Rows[0]["HetNap"];
|
|
}
|
|
|
|
public void FizikaiTorlesOrarendek(int intezmenyId, int tanevId, int userId)
|
|
{
|
|
using (SDACommand command = UserContext.Instance.SDAConnection.CreateCommand())
|
|
{
|
|
command.CommandType = CommandType.StoredProcedure;
|
|
command.Connection = UserContext.Instance.SDAConnection;
|
|
command.Transaction = UserContext.Instance.SDATransaction;
|
|
|
|
command.Parameters.Add("pIntezmenyId", intezmenyId);
|
|
command.Parameters.Add("pTanevId", tanevId);
|
|
command.Parameters.Add("pUserId", userId);
|
|
|
|
command.CommandText = "sp_FizikaiTorlesOrarendek";
|
|
command.ExecuteNonQuery();
|
|
DalHelper.Commit();
|
|
}
|
|
}
|
|
|
|
public int? CheckTanevRendjeOsztalyCsoportValidation(int osztalycsoportId, DateTime oraDatum)
|
|
{
|
|
int? result = null;
|
|
var parameters = new List<CommandParameter>
|
|
{
|
|
new CommandParameter("pOsztalycsoportId", osztalycsoportId),
|
|
new CommandParameter("pOraDatum", oraDatum)
|
|
};
|
|
|
|
const string commandText = @"
|
|
SELECT TOP(1) t.C_NAPTIPUSA as NapTipus FROM T_TANEVRENDJE t
|
|
LEFT JOIN T_OSZTALYCSOPORT_TANEVRENDJE ot ON ot.C_TANEVRENDJEID = t.ID
|
|
WHERE
|
|
t.C_DATUM = :pOraDatum AND t.TOROLT = 'F' AND t.C_AKTIV = 'T'
|
|
AND (ot.C_OSZTALYCSOPORTID = :pOsztalycsoportId OR ot.C_OSZTALYCSOPORTID IS NULL)
|
|
ORDER BY
|
|
ot.C_OSZTALYCSOPORTID DESC
|
|
";
|
|
|
|
var ds = GetData(commandText, parameters);
|
|
if (ds.Tables[0].Rows.Count > 0)
|
|
result = (int?)ds.Tables[0].Rows[0]["NapTipus"];
|
|
|
|
return result;
|
|
}
|
|
|
|
private bool VanTanitasiOraAzIdoszakra(DateTime helyettesitesNapja, int ooGroupId)
|
|
{
|
|
using (var command = new SDACommand())
|
|
{
|
|
command.Connection = UserContext.Instance.SDAConnection;
|
|
command.Transaction = UserContext.Instance.SDATransaction;
|
|
command.CommandText = @"SELECT 1 FROM T_TANITASIORA
|
|
WHERE
|
|
C_ORARENDIORAGROUPID = :pOoGroupId
|
|
AND C_DATUM = :pHelyettesitesNapja
|
|
AND TOROLT = 'F'
|
|
";
|
|
|
|
command.Parameters.Add("pOoGroupId", ooGroupId);
|
|
command.Parameters.Add("pHelyettesitesNapja", helyettesitesNapja);
|
|
|
|
var result = command.ExecuteScalar();
|
|
return result != null;
|
|
}
|
|
}
|
|
|
|
private bool VanHelyettesitoAzIdoszakra(IOrarendiOra orarendiOra, DateTime helyettesitesNapja)
|
|
{
|
|
var helyettesites = orarendiOra.HelyettesTanarok.Where(h => !h.Torolt && h.HelyettesitesNapja.Date == helyettesitesNapja.Date);
|
|
return helyettesites.Any();
|
|
}
|
|
|
|
public DataSet GetOraMentessegList(DateTime startDate, DateTime endDate, int osztalyCsoportId, int intezmenyId, int tanevId)
|
|
{
|
|
using (var command = new SDACommand())
|
|
{
|
|
command.Connection = UserContext.Instance.SDAConnection;
|
|
command.Transaction = UserContext.Instance.SDATransaction;
|
|
command.CommandType = CommandType.StoredProcedure;
|
|
command.CommandText = "sp_GetOraMentessegList";
|
|
|
|
command.Parameters.Add("intezmenyId", intezmenyId);
|
|
command.Parameters.Add("tanevId", tanevId);
|
|
command.Parameters.Add("osztalyCsoportId", osztalyCsoportId);
|
|
command.Parameters.Add("start", startDate);
|
|
command.Parameters.Add("end", endDate);
|
|
|
|
var dts = new DataSet();
|
|
using (var adapter = new SDADataAdapter())
|
|
{
|
|
adapter.SelectCommand = command;
|
|
adapter.Fill(dts);
|
|
}
|
|
return dts;
|
|
}
|
|
}
|
|
|
|
public DataSet GetNemNaplozottOrarendiOrak(NemNaplozottOrakPCO nemNaplozottOrakPCO)
|
|
{
|
|
var ds = new DataSet();
|
|
|
|
using (var command = new SDACommand())
|
|
{
|
|
command.Connection = UserContext.Instance.SDAConnection;
|
|
command.Transaction = UserContext.Instance.SDATransaction;
|
|
|
|
command.CommandType = CommandType.StoredProcedure;
|
|
command.CommandText = "uspGetNemNaplozottOrarendiOrak";
|
|
|
|
command.Parameters.Add("IntezmenyId", SDADBType.Int).Value = nemNaplozottOrakPCO.IntezmenyId;
|
|
command.Parameters.Add("TanevId", SDADBType.Int).Value = nemNaplozottOrakPCO.TanevId;
|
|
command.Parameters.Add("IdoszakKezdete", SDADBType.DateTime).Value = nemNaplozottOrakPCO.Start;
|
|
command.Parameters.Add("IdoszakVege", SDADBType.DateTime).Value = nemNaplozottOrakPCO.End;
|
|
if (nemNaplozottOrakPCO.TanarId.IsEntityId())
|
|
{
|
|
command.Parameters.Add("TanarId", SDADBType.Int).Value = nemNaplozottOrakPCO.TanarId;
|
|
}
|
|
if (nemNaplozottOrakPCO.OsztalyCsoportId.IsEntityId())
|
|
{
|
|
command.Parameters.Add("OsztalyCsoportId", SDADBType.Int).Value = nemNaplozottOrakPCO.OsztalyCsoportId;
|
|
}
|
|
if (nemNaplozottOrakPCO.TantargyId.IsEntityId())
|
|
{
|
|
command.Parameters.Add("TantargyId", SDADBType.Int).Value = nemNaplozottOrakPCO.TantargyId;
|
|
}
|
|
command.Parameters.Add("CsakAHelyettesiteseim", nemNaplozottOrakPCO.CsakAHelyettesiteseim);
|
|
command.Parameters.Add("HelyettesitettOraimIs", nemNaplozottOrakPCO.HelyettesitettOraimIs);
|
|
command.Parameters.Add("pDatumTol", nemNaplozottOrakPCO.DatumTol);
|
|
command.Parameters.Add("pDatumIg", nemNaplozottOrakPCO.DatumIg);
|
|
command.Parameters.Add("pOraszamTol", nemNaplozottOrakPCO.OraszamTol);
|
|
command.Parameters.Add("pOraszamIg", nemNaplozottOrakPCO.OraszamIg);
|
|
command.Parameters.Add("pIdopontTol", nemNaplozottOrakPCO.OraKezdetTol);
|
|
command.Parameters.Add("pIdopontIg", nemNaplozottOrakPCO.OraKezdetIg);
|
|
if (nemNaplozottOrakPCO.FeladatKategoriaId.IsEntityId())
|
|
{
|
|
command.Parameters.Add("pFeladatKategoriaId", SDADBType.Int).Value = nemNaplozottOrakPCO.FeladatKategoriaId;
|
|
}
|
|
if (nemNaplozottOrakPCO.FeladatEllatasiHelyId.IsEntityId())
|
|
{
|
|
command.Parameters.Add("pFeladatEllatasiHelyId", SDADBType.Int).Value = nemNaplozottOrakPCO.FeladatEllatasiHelyId;
|
|
}
|
|
|
|
using (var adapter = new SDADataAdapter())
|
|
{
|
|
adapter.SelectCommand = command;
|
|
adapter.Fill(ds);
|
|
}
|
|
}
|
|
|
|
SetDNAME(ds.Tables[0], "Hetirend,TargykategoriaID,HetNapja,HetNapjaValos");
|
|
SetBoolFields(ds.Tables[0], "Megtartott,Hianyzas,Keses,Ures,AdminAltalKiirt");
|
|
DataTable dt = SortingAndPaging(ds.Tables[0], GridParameters);
|
|
|
|
return dt.AsDataSet();
|
|
}
|
|
|
|
public DataSet GetTanarokNemNaplozottOrarendiOrakHetiEmailErtesito()
|
|
{
|
|
var ds = new DataSet();
|
|
|
|
using (var command = new SDACommand())
|
|
{
|
|
command.Connection = UserContext.Instance.SDAConnection;
|
|
command.Transaction = UserContext.Instance.SDATransaction;
|
|
|
|
command.CommandType = CommandType.StoredProcedure;
|
|
command.CommandText = "uspGetTanarokNemNaplozottOrarendiOrakForEmail";
|
|
|
|
using (var adapter = new SDADataAdapter())
|
|
{
|
|
adapter.SelectCommand = command;
|
|
adapter.Fill(ds);
|
|
}
|
|
}
|
|
|
|
return SortingAndPaging(ds.Tables[0], GridParameters).AsDataSet();
|
|
}
|
|
|
|
public bool IsOrarendiOraHasElmaradtOra(DateTime date, int orarendiOraGroupId)
|
|
{
|
|
using (var command = new SDACommand())
|
|
{
|
|
command.Connection = UserContext.Instance.SDAConnection;
|
|
command.Transaction = UserContext.Instance.SDATransaction;
|
|
|
|
command.Parameters.Add(nameof(orarendiOraGroupId), orarendiOraGroupId);
|
|
command.Parameters.Add(nameof(date), date);
|
|
|
|
command.CommandText = $@"
|
|
SELECT 1
|
|
FROM T_TANITASIORA
|
|
WHERE
|
|
C_ORARENDIORAGROUPID = @{nameof(orarendiOraGroupId)}
|
|
AND C_DATUM = @{nameof(date)}
|
|
AND TOROLT = 'F'
|
|
";
|
|
|
|
var result = command.ExecuteScalar();
|
|
return result != null;
|
|
}
|
|
}
|
|
|
|
public void UpdateOsszefuggoSzakGyakosOrakErvenyessegKezdete(DateTime newSzakGyakKezdoDatum, int tanevId)
|
|
{
|
|
using (var command = new SDACommand())
|
|
{
|
|
command.Connection = UserContext.Instance.SDAConnection;
|
|
command.Transaction = UserContext.Instance.SDATransaction;
|
|
command.CommandText = @"
|
|
UPDATE oo
|
|
SET oo.C_ORAERVENYESSEGKEZDETE = :pNewSzakGyakKezdoDatum
|
|
FROM T_ORARENDIORA_OSSZES oo
|
|
INNER JOIN T_CSOPORT_OSSZES cs ON cs.ID = oo.C_OSZTALYCSOPORTID AND cs.C_TIPUSA = :pSzakGyakCsoportTipus AND cs.TOROLT = 'F' AND cs.C_ALTANEVID = oo.C_TANEVID
|
|
WHERE oo.TOROLT = 'F'
|
|
AND oo.C_ORAERVENYESSEGKEZDETE < :pNewSzakGyakKezdoDatum
|
|
AND oo.C_TANEVID = :pTanevId
|
|
"
|
|
;
|
|
command.Parameters.Add("pTanevId", tanevId);
|
|
command.Parameters.Add("pNewSzakGyakKezdoDatum", newSzakGyakKezdoDatum);
|
|
command.Parameters.Add("pSzakGyakCsoportTipus", (int)CsoportTipusEnum.OsszefuggoSzakmaiGyakorlatiCsoport);
|
|
|
|
command.ExecuteNonQuery();
|
|
|
|
DalHelper.Commit();
|
|
}
|
|
}
|
|
|
|
public void UpdateOsszefuggoSzakGyakosOrakErvenyessegVege(DateTime newSzakGyakVegDatum, int tanevId)
|
|
{
|
|
using (var command = new SDACommand())
|
|
{
|
|
command.Connection = UserContext.Instance.SDAConnection;
|
|
command.Transaction = UserContext.Instance.SDATransaction;
|
|
command.CommandText = @"
|
|
UPDATE oo
|
|
SET oo.C_ORAERVENYESSEGVEGE = :pNewSzakGyakVegDatum
|
|
FROM T_ORARENDIORA_OSSZES oo
|
|
INNER JOIN T_CSOPORT_OSSZES cs ON cs.ID = oo.C_OSZTALYCSOPORTID AND cs.C_TIPUSA = :pSzakGyakCsoportTipus AND cs.TOROLT = 'F' AND cs.C_ALTANEVID = oo.C_TANEVID
|
|
WHERE oo.TOROLT = 'F'
|
|
AND oo.C_ORAERVENYESSEGVEGE > :pNewSzakGyakVegDatum
|
|
AND oo.C_TANEVID = :pTanevId
|
|
"
|
|
;
|
|
command.Parameters.Add("pTanevId", tanevId);
|
|
command.Parameters.Add("pNewSzakGyakVegDatum", newSzakGyakVegDatum);
|
|
command.Parameters.Add("pSzakGyakCsoportTipus", (int)CsoportTipusEnum.OsszefuggoSzakmaiGyakorlatiCsoport);
|
|
|
|
command.ExecuteNonQuery();
|
|
|
|
DalHelper.Commit();
|
|
}
|
|
}
|
|
|
|
public DataSet GetOrarendDataSet(int tanevId, int? oktNevelesiKategoriaId = null)
|
|
{
|
|
var commandParameterList = new List<CommandParameter>
|
|
{
|
|
new CommandParameter("pTanevId", tanevId),
|
|
oktNevelesiKategoriaId.IsEntityId() ?
|
|
new CommandParameter("pOktNevelesiKategoriaId", oktNevelesiKategoriaId.Value) :
|
|
new CommandParameter("pOktNevelesiKategoriaId", DBNull.Value)
|
|
};
|
|
|
|
string commandText = @"
|
|
SELECT
|
|
oo.ID Id
|
|
,oo.C_ORASZAM OraSorszam
|
|
,oo.C_HETNAPJA HetNapjaTipusId
|
|
,oo.C_ORAERVENYESSEGKEZDETE OraErvenyessegKezdete
|
|
,oo.C_ORAERVENYESSEGVEGE OraErvenyessegVege
|
|
,oo.C_ORAKEZDETE OraKezdete
|
|
,oo.C_ORAVEGE OraVege
|
|
,IIF(oo.C_CSENGETESIRENDID IS NULL, 'T', 'F') AS IsTanoranKivuliFoglalkozas
|
|
,oo.C_IMPORTALT Importalt
|
|
,ocs.ID OsztalyCsoportId
|
|
,ocs.C_NEV OsztalyCsoportNev
|
|
,tgy.ID TantargyId
|
|
,tgy.C_NEV TantargyNev
|
|
,fh.ID TanarId
|
|
,fh.C_NYOMTATASINEV TanarNev
|
|
,fh.C_SZULETESIDATUM TanarSzuletesiIdo
|
|
,t.ID TeremId
|
|
,t.C_NEV TeremNev
|
|
,dib.ID HetirendTipusId
|
|
,dib.C_NAME HetirendTipusNev
|
|
FROM T_ORARENDIORA_OSSZES oo
|
|
INNER JOIN T_OSZTALYCSOPORT_OSSZES ocs ON ocs.TOROLT = 'F' AND ocs.ID = oo.C_OSZTALYCSOPORTID AND ocs.C_TANEVID = oo.C_TANEVID
|
|
AND (@pOktNevelesiKategoriaId IS NULL OR ocs.C_FELADATKATEGORIAID = @pOktNevelesiKategoriaId)
|
|
INNER JOIN T_TANTARGY_OSSZES tgy ON tgy.TOROLT = 'F' AND tgy.ID = oo.C_TANTARGYID AND tgy.C_TANEVID = oo.C_TANEVID
|
|
INNER JOIN T_FELHASZNALO_OSSZES fh ON fh.TOROLT = 'F' AND fh.ID = oo.C_TANARID
|
|
INNER JOIN T_ALKALMAZOTT_OSSZES a ON a.TOROLT = 'F' AND a.C_ALTANEVID = oo.C_TANEVID AND a.ID = fh.ID
|
|
INNER JOIN T_TEREM_OSSZES t ON t.TOROLT = 'F' AND t.ID = oo.C_TEREMID AND t.C_TANEVID = oo.C_TANEVID
|
|
INNER JOIN T_HETIRENDTIPUS_OSSZES hr ON hr.TOROLT = 'F' AND hr.C_ALTANEVID = oo.C_TANEVID AND hr.ID = oo.C_HETIREND
|
|
INNER JOIN T_DICTIONARYITEMBASE_OSSZES dib ON dib.TOROLT = 'F' AND dib.C_TANEVID = hr.C_ALTANEVID AND dib.ID = hr.ID
|
|
WHERE oo.TOROLT = 'F' AND oo.C_TANEVID = @pTanevId
|
|
";
|
|
return GetData(commandText, commandParameterList);
|
|
}
|
|
|
|
public int TanevRendjeOraszamValidation(int osztalycsoportId, DateTime datum, int tanevId)
|
|
{
|
|
using (var command = new SDACommand())
|
|
{
|
|
command.Connection = UserContext.Instance.SDAConnection;
|
|
command.Transaction = UserContext.Instance.SDATransaction;
|
|
command.CommandType = CommandType.StoredProcedure;
|
|
command.CommandText = "uspGetTanevRendjeOraszamUtkozes";
|
|
|
|
command.Parameters.Add("osztalyCsoportId", osztalycsoportId);
|
|
command.Parameters.Add("napDatuma", datum);
|
|
command.Parameters.Add("tanevId", tanevId);
|
|
|
|
var dts = new DataSet();
|
|
using (var adapter = new SDADataAdapter())
|
|
{
|
|
adapter.SelectCommand = command;
|
|
adapter.Fill(dts);
|
|
}
|
|
return (int)dts.Tables[0].Rows[0]["MaxOra"];
|
|
}
|
|
}
|
|
|
|
private void CheckHelyettesitesekBeforeDelete(OrarendiOra ora)
|
|
{
|
|
var listHelyettesitesiIdoszak = ora.HelyettesTanarok;
|
|
foreach (var item in listHelyettesitesiIdoszak)
|
|
{
|
|
var helyettesitesiIdoszak = item as HelyettesitesiIdoszak;
|
|
helyettesitesiIdoszak.Delete();
|
|
}
|
|
}
|
|
|
|
public DataSet GetHazifeladatokListaja(int tanevId, HazifeladatokListajaPco pco)
|
|
{
|
|
using (var command = new SDACommand())
|
|
{
|
|
command.Connection = UserContext.Instance.SDAConnection;
|
|
command.Transaction = UserContext.Instance.SDATransaction;
|
|
command.CommandType = CommandType.StoredProcedure;
|
|
command.CommandText = "uspGetHazifeladatData";
|
|
command.Parameters.Add("pTanevId", SDADBType.Int).Value = tanevId;
|
|
command.Parameters.Add("pMindegyikHetTipusId", (int)HetiRendTipusEnum.MindegyikHet);
|
|
command.Parameters.Add("pFeladatTipusId", SDADBType.Int).Value = pco.FeladatTipusId.IsEntityId() ? pco.FeladatTipusId.Value : (int)FeladatTipusEnum.HaziFeladat;
|
|
|
|
if (pco.OraDatumaTolSrc.HasValue || pco.OraDatumaIgSrc.HasValue)
|
|
{
|
|
if (pco.OraDatumaTolSrc.HasValue)
|
|
{
|
|
command.Parameters.Add("pOraDatumaTol", SDADBType.DateTime).Value = pco.OraDatumaTolSrc.Value;
|
|
}
|
|
|
|
if (pco.OraDatumaIgSrc.HasValue)
|
|
{
|
|
command.Parameters.Add("pOraDatumaIg", SDADBType.DateTime).Value = pco.OraDatumaIgSrc.Value;
|
|
}
|
|
}
|
|
|
|
if (pco.TanarIdSrc.HasValue)
|
|
{
|
|
command.Parameters.Add("pTanarId", SDADBType.Int).Value = pco.TanarIdSrc.Value;
|
|
}
|
|
|
|
if (pco.TantargyIdSrc.HasValue)
|
|
{
|
|
command.Parameters.Add("pTantargyId", SDADBType.Int).Value = pco.TantargyIdSrc.Value;
|
|
}
|
|
|
|
if (!string.IsNullOrWhiteSpace(pco.OraTemaSrc))
|
|
{
|
|
command.Parameters.Add("pOraTema", SDADBType.String).Value = pco.OraTemaSrc;
|
|
}
|
|
|
|
if (pco.OsztalyCsoportIds.NotNullAndAny())
|
|
{
|
|
command.Parameters.Add("pOsztalyCsoportIds", SDADBType.String).Value = string.Join(",", pco.OsztalyCsoportIds);
|
|
}
|
|
|
|
if (!string.IsNullOrWhiteSpace(pco.HazifeladatTemaSrc))
|
|
{
|
|
command.Parameters.Add("pHazifeladatSzoveg", SDADBType.String).Value = pco.HazifeladatTemaSrc;
|
|
}
|
|
|
|
if (pco.RogzitesTolSrc.HasValue || pco.RogzitesIgSrc.HasValue)
|
|
{
|
|
if (pco.RogzitesTolSrc.HasValue)
|
|
{
|
|
command.Parameters.Add("pRogzitesTol", SDADBType.DateTime).Value = pco.RogzitesTolSrc.Value;
|
|
}
|
|
|
|
if (pco.RogzitesIgSrc.HasValue)
|
|
{
|
|
command.Parameters.Add("pRogzitesIg", SDADBType.DateTime).Value = pco.RogzitesIgSrc.Value;
|
|
}
|
|
}
|
|
|
|
if (pco.HataridoTolSrc.HasValue || pco.HataridoIgSrc.HasValue)
|
|
{
|
|
if (pco.HataridoTolSrc.HasValue)
|
|
{
|
|
command.Parameters.Add("pHataridoTol", SDADBType.DateTime).Value = pco.HataridoTolSrc.Value;
|
|
}
|
|
|
|
if (pco.HataridoIgSrc.HasValue)
|
|
{
|
|
command.Parameters.Add("pHataridoIg", SDADBType.DateTime).Value = pco.HataridoIgSrc.Value;
|
|
}
|
|
}
|
|
|
|
if (pco.FeladatKategoriaId.IsEntityId())
|
|
{
|
|
command.Parameters.Add("pFeladatKategoriaId", SDADBType.Int).Value = pco.FeladatKategoriaId.Value;
|
|
}
|
|
|
|
if (pco.FeladatEllatasiHelyId.IsEntityId())
|
|
{
|
|
command.Parameters.Add("pFeladatEllatasiHelyId", SDADBType.Int).Value = pco.FeladatEllatasiHelyId.Value;
|
|
}
|
|
|
|
if (pco.IsOnlineOra.HasValue)
|
|
{
|
|
command.Parameters.Add("pIsOnlineOra", pco.IsOnlineOra.ToBool());
|
|
}
|
|
|
|
var ds = new DataSet();
|
|
|
|
using (var adapter = new SDADataAdapter())
|
|
{
|
|
adapter.SelectCommand = command;
|
|
adapter.Fill(ds);
|
|
}
|
|
|
|
SetBoolFields(ds.Tables[0], "IsOnlineOra");
|
|
|
|
return ds;
|
|
}
|
|
}
|
|
|
|
public DataSet GetOrarendiOraTulajdonsag(int orarendiOraId, int tanevId)
|
|
{
|
|
using (var command = new SDACommand())
|
|
{
|
|
command.Connection = UserContext.Instance.SDAConnection;
|
|
command.Transaction = UserContext.Instance.SDATransaction;
|
|
command.CommandType = CommandType.StoredProcedure;
|
|
command.CommandText = "uspGetOrarendiOraTulajdonsag";
|
|
|
|
command.Parameters.Add("pOrarendiOraId", orarendiOraId);
|
|
command.Parameters.Add("pTanevId", tanevId);
|
|
|
|
var ds = new DataSet();
|
|
using (var adapter = new SDADataAdapter())
|
|
{
|
|
adapter.SelectCommand = command;
|
|
adapter.Fill(ds);
|
|
}
|
|
|
|
return ds;
|
|
}
|
|
}
|
|
|
|
public void SaveOrUpdateOrarendiOraTulajdonsag(Dictionary<int, bool> oraTulajdonsagok, int orarendiOraId, int intezmenyId, int tanevId)
|
|
{
|
|
using (var command = new SDACommand())
|
|
{
|
|
command.Connection = UserContext.Instance.SDAConnection;
|
|
command.Transaction = UserContext.Instance.SDATransaction;
|
|
command.Parameters.Add(nameof(orarendiOraId), orarendiOraId);
|
|
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_ORARENDIORATULAJDONSAG_OSSZES
|
|
SET
|
|
C_BOOLERTEK = @{nameof(value)}
|
|
WHERE
|
|
C_ORATULAJDONSAGID = @{nameof(key)}
|
|
AND C_ORARENDIORAID = @{nameof(orarendiOraId)}
|
|
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_ORARENDIORAID
|
|
,C_INTEZMENYID
|
|
,C_TANEVID
|
|
,TOROLT
|
|
FROM T_ORARENDIORATULAJDONSAG_OSSZES
|
|
WHERE C_ORATULAJDONSAGID = @{nameof(key)}
|
|
AND C_ORARENDIORAID = @{nameof(orarendiOraId)}
|
|
AND C_INTEZMENYID = @{nameof(intezmenyId)}
|
|
AND C_TANEVID = @{nameof(tanevId)}
|
|
AND TOROLT = 'F'
|
|
)
|
|
INSERT INTO
|
|
T_ORARENDIORATULAJDONSAG_OSSZES
|
|
(
|
|
C_ORATULAJDONSAGID
|
|
,C_ORARENDIORAID
|
|
,C_INTEZMENYID
|
|
,C_TANEVID
|
|
,C_BOOLERTEK
|
|
)
|
|
VALUES
|
|
(
|
|
@{nameof(key)}
|
|
,@{nameof(orarendiOraId)}
|
|
,@{nameof(intezmenyId)}
|
|
,@{nameof(tanevId)}
|
|
,'T'
|
|
)");
|
|
}
|
|
|
|
command.CommandText = commandTextBuilder.ToString();
|
|
command.ExecuteScalar();
|
|
}
|
|
}
|
|
}
|
|
|
|
public void DuplicateOrarendiOraTulajdonsag(int oldOrarendiOraId, int newOrarendiOraId)
|
|
{
|
|
using (var command = new SDACommand())
|
|
{
|
|
command.Connection = UserContext.Instance.SDAConnection;
|
|
command.Transaction = UserContext.Instance.SDATransaction;
|
|
command.Parameters.Add(nameof(oldOrarendiOraId), oldOrarendiOraId);
|
|
command.Parameters.Add(nameof(newOrarendiOraId), newOrarendiOraId);
|
|
|
|
command.CommandText = $@"
|
|
INSERT INTO T_ORARENDIORATULAJDONSAG_OSSZES
|
|
(
|
|
C_ORATULAJDONSAGID
|
|
,C_ORARENDIORAID
|
|
,C_INTEZMENYID
|
|
,C_TANEVID
|
|
,C_BOOLERTEK
|
|
)
|
|
SELECT
|
|
C_ORATULAJDONSAGID
|
|
,@{nameof(newOrarendiOraId)}
|
|
,C_INTEZMENYID
|
|
,C_TANEVID
|
|
,C_BOOLERTEK
|
|
FROM T_ORARENDIORATULAJDONSAG_OSSZES
|
|
WHERE
|
|
TOROLT = 'F' AND C_ORARENDIORAID = @{nameof(oldOrarendiOraId)}";
|
|
command.ExecuteScalar();
|
|
|
|
}
|
|
}
|
|
|
|
public int? CopyOrarendiElem(int copyId, DateTime date, int oraszam)
|
|
{
|
|
using (var command = new SDACommand())
|
|
{
|
|
command.Connection = UserContext.Instance.SDAConnection;
|
|
command.Transaction = UserContext.Instance.SDATransaction;
|
|
command.CommandType = CommandType.StoredProcedure;
|
|
command.CommandText = "uspCopyOrarendiElem";
|
|
|
|
command.Parameters.Add("copyId", copyId);
|
|
command.Parameters.Add("oraszam", oraszam);
|
|
command.Parameters.Add("date", date);
|
|
|
|
var dts = new DataSet();
|
|
using (var adapter = new SDADataAdapter())
|
|
{
|
|
adapter.SelectCommand = command;
|
|
adapter.Fill(dts);
|
|
}
|
|
DalHelper.Commit();
|
|
|
|
return dts.Tables.Count > 0 && dts.Tables[0].Rows.Count > 0 ? (int?)dts.Tables[0].Rows[0]["Result"] : (int?)default;
|
|
}
|
|
}
|
|
|
|
public DataSet GetOraTanuloi(int orarendiOraId)
|
|
{
|
|
var commandText =
|
|
$@"SELECT
|
|
f.ID AS TanuloId
|
|
,f.C_IDPEGYEDIAZONOSITO IdpUniqueId
|
|
,f.C_NYOMTATASINEV AS TanuloNev
|
|
FROM T_FELHASZNALO f
|
|
INNER JOIN T_TANULOCSOPORT tcs ON tcs.C_TANULOID = f.ID
|
|
AND tcs.C_BELEPESDATUM <= GETDATE()
|
|
AND (tcs.C_KILEPESDATUM IS NULL OR GETDATE() <= tcs.C_KILEPESDATUM)
|
|
INNER JOIN T_OSZTALYCSOPORT ocs ON ocs.ID = tcs.C_OSZTALYCSOPORTID
|
|
INNER JOIN T_ORARENDIORA oo ON oo.C_OSZTALYCSOPORTID = ocs.ID
|
|
WHERE oo.ID = @{nameof(orarendiOraId)}";
|
|
|
|
var commandParameterList = new List<CommandParameter>
|
|
{
|
|
new CommandParameter(nameof(orarendiOraId), orarendiOraId)
|
|
};
|
|
|
|
var dataSet = GetData(
|
|
commandText: commandText,
|
|
parameters: commandParameterList);
|
|
|
|
return dataSet;
|
|
}
|
|
|
|
public DataSet GetElsoOrarendiOraDatuma(int orarendiOraId)
|
|
{
|
|
var commandText =
|
|
$@"SELECT TOP 1
|
|
o.C_DATUM AS Datum
|
|
FROM T_ORAREND o
|
|
WHERE o.C_ORARENDIORAID = @{nameof(orarendiOraId)}
|
|
ORDER BY o.C_DATUM ASC";
|
|
|
|
var commandParameterList = new List<CommandParameter>
|
|
{
|
|
new CommandParameter(nameof(orarendiOraId), orarendiOraId)
|
|
};
|
|
|
|
var dataSet = GetData(
|
|
commandText: commandText,
|
|
parameters: commandParameterList);
|
|
|
|
return dataSet;
|
|
}
|
|
|
|
public void OrarendekTorlese(OrarendekTorlesePco orarendekTorlesePco)
|
|
{
|
|
using (var command = new SDACommand())
|
|
{
|
|
command.Connection = UserContext.Instance.SDAConnection;
|
|
command.Transaction = UserContext.Instance.SDATransaction;
|
|
command.CommandType = CommandType.StoredProcedure;
|
|
command.CommandText = "uspOrarendekTorlese";
|
|
|
|
command.Parameters.Add("pTanevId", orarendekTorlesePco.TanevId);
|
|
command.Parameters.Add("pUserId", orarendekTorlesePco.UserId);
|
|
command.Parameters.Add("pIntervallumKezdete", orarendekTorlesePco.IntervallumKezdete);
|
|
command.Parameters.Add("pIntervallumVege", orarendekTorlesePco.IntervallumVege);
|
|
command.Parameters.Add("pNezetTipus", (int)orarendekTorlesePco.NezetTipus);
|
|
|
|
if (!string.IsNullOrWhiteSpace(orarendekTorlesePco.FeladatellatasiHelyJson))
|
|
{
|
|
command.Parameters.Add("pFeladatellatasiHelyJson", orarendekTorlesePco.FeladatellatasiHelyJson);
|
|
}
|
|
else
|
|
{
|
|
command.Parameters.Add("pFeladatellatasiHelyJson", DBNull.Value);
|
|
}
|
|
|
|
if (!string.IsNullOrWhiteSpace(orarendekTorlesePco.OsztalyJson))
|
|
{
|
|
command.Parameters.Add("pOsztalyJson", orarendekTorlesePco.OsztalyJson);
|
|
}
|
|
else
|
|
{
|
|
command.Parameters.Add("pOsztalyJson", DBNull.Value);
|
|
}
|
|
|
|
if (!string.IsNullOrWhiteSpace(orarendekTorlesePco.CsoportJson))
|
|
{
|
|
command.Parameters.Add("pCsoportJson", orarendekTorlesePco.CsoportJson);
|
|
}
|
|
else
|
|
{
|
|
command.Parameters.Add("pCsoportJson", DBNull.Value);
|
|
}
|
|
|
|
command.ExecuteNonQuery();
|
|
|
|
DalHelper.Commit();
|
|
}
|
|
}
|
|
|
|
public DataSet GetExportAdatokForOrarendekTorlese(OrarendekTorlesePco orarendekTorlesePco)
|
|
{
|
|
using (var command = new SDACommand())
|
|
{
|
|
command.Connection = UserContext.Instance.SDAConnection;
|
|
command.Transaction = UserContext.Instance.SDATransaction;
|
|
command.CommandType = CommandType.StoredProcedure;
|
|
command.CommandText = "uspGetExportAdatokForOrarendekTorlese";
|
|
|
|
command.Parameters.Add("pTanevId", orarendekTorlesePco.TanevId);
|
|
command.Parameters.Add("pIntervallumKezdete", orarendekTorlesePco.IntervallumKezdete);
|
|
command.Parameters.Add("pIntervallumVege", orarendekTorlesePco.IntervallumVege);
|
|
command.Parameters.Add("pNezetTipus", (int)orarendekTorlesePco.NezetTipus);
|
|
|
|
if (!string.IsNullOrWhiteSpace(orarendekTorlesePco.FeladatellatasiHelyJson))
|
|
{
|
|
command.Parameters.Add("pFeladatellatasiHelyJson", orarendekTorlesePco.FeladatellatasiHelyJson);
|
|
}
|
|
else
|
|
{
|
|
command.Parameters.Add("pFeladatellatasiHelyJson", DBNull.Value);
|
|
}
|
|
|
|
if (!string.IsNullOrWhiteSpace(orarendekTorlesePco.OsztalyJson))
|
|
{
|
|
command.Parameters.Add("pOsztalyJson", orarendekTorlesePco.OsztalyJson);
|
|
}
|
|
else
|
|
{
|
|
command.Parameters.Add("pOsztalyJson", DBNull.Value);
|
|
}
|
|
|
|
if (!string.IsNullOrWhiteSpace(orarendekTorlesePco.CsoportJson))
|
|
{
|
|
command.Parameters.Add("pCsoportJson", orarendekTorlesePco.CsoportJson);
|
|
}
|
|
else
|
|
{
|
|
command.Parameters.Add("pCsoportJson", DBNull.Value);
|
|
}
|
|
|
|
var dataSet = new DataSet();
|
|
using (var sdaDataAdapter = new SDADataAdapter())
|
|
{
|
|
sdaDataAdapter.SelectCommand = command;
|
|
sdaDataAdapter.Fill(dataSet);
|
|
}
|
|
|
|
return dataSet;
|
|
}
|
|
}
|
|
|
|
public DataSet GetRogzitettOrak(int tanarId, DateTime start, DateTime end)
|
|
{
|
|
using (var command = new SDACommand())
|
|
{
|
|
command.Connection = UserContext.Instance.SDAConnection;
|
|
command.Transaction = UserContext.Instance.SDATransaction;
|
|
command.CommandType = CommandType.StoredProcedure;
|
|
command.CommandText = "uspGetRogzitettOrak";
|
|
|
|
command.Parameters.Add("pTanarId", tanarId);
|
|
command.Parameters.Add("pStart", start);
|
|
command.Parameters.Add("pEnd", end);
|
|
|
|
var ds = new DataSet();
|
|
using (var sdaDataAdapter = new SDADataAdapter())
|
|
{
|
|
sdaDataAdapter.SelectCommand = command;
|
|
sdaDataAdapter.Fill(ds);
|
|
}
|
|
|
|
return ds;
|
|
}
|
|
}
|
|
|
|
public DataSet GetElozoOrakAdatai(string orakAdatai, int intezmenyId, int tanevId)
|
|
{
|
|
using (var command = new SDACommand())
|
|
{
|
|
command.Connection = UserContext.Instance.SDAConnection;
|
|
command.Transaction = UserContext.Instance.SDATransaction;
|
|
command.CommandType = CommandType.StoredProcedure;
|
|
command.CommandText = "uspGetElozoOrakAdatai";
|
|
|
|
command.Parameters.Add(nameof(orakAdatai), orakAdatai);
|
|
command.Parameters.Add(nameof(intezmenyId), intezmenyId);
|
|
command.Parameters.Add(nameof(tanevId), tanevId);
|
|
|
|
var dataSet = new DataSet();
|
|
using (var adapter = new SDADataAdapter())
|
|
{
|
|
adapter.SelectCommand = command;
|
|
adapter.Fill(dataSet);
|
|
}
|
|
|
|
return dataSet;
|
|
}
|
|
}
|
|
}
|
|
}
|