939 lines
37 KiB
C#
939 lines
37 KiB
C#
using System;
|
|
using System.Collections.Generic;
|
|
using System.Data;
|
|
using System.Linq;
|
|
using Kreta.Core;
|
|
using Kreta.Core.Logic;
|
|
using Kreta.DataAccess.Interfaces;
|
|
using Kreta.DataAccessManual.Interfaces;
|
|
using Kreta.DataAccessManual.Util;
|
|
using Kreta.Enums;
|
|
using Kreta.Framework;
|
|
using Kreta.Framework.Util;
|
|
using SDA.DataProvider;
|
|
using SDA.Kreta.Entities;
|
|
|
|
namespace Kreta.DataAccessManual
|
|
{
|
|
internal class TanevRendjeDAL : DataAccessBase, ITanevRendjeDal
|
|
{
|
|
public TanevRendjeDAL(DalHandler handler) : base(handler)
|
|
{
|
|
}
|
|
|
|
public TanevRendjeDAL(DalHandler handler, GridParameters parameters) : base(handler, parameters)
|
|
{
|
|
}
|
|
|
|
public ITanevRendje Get() => TanevRendje.GiveAnInstance();
|
|
public ITanevRendje Get(int id)
|
|
{
|
|
var entity = TanevRendje.GiveAnInstance();
|
|
entity.LoadByID(id);
|
|
return entity;
|
|
}
|
|
|
|
public DataSet GetTanevRendjeData(int tanevId)
|
|
{
|
|
using (var sdaCommand = new SDACommand())
|
|
{
|
|
sdaCommand.Connection = UserContext.Instance.SDAConnection;
|
|
sdaCommand.Transaction = UserContext.Instance.SDATransaction;
|
|
sdaCommand.CommandType = CommandType.StoredProcedure;
|
|
sdaCommand.CommandText = "uspGetTanevRendjeData";
|
|
|
|
sdaCommand.Parameters.Add("pTanevId", tanevId);
|
|
|
|
var dataSet = new DataSet();
|
|
using (var sdaDataAdapter = new SDADataAdapter())
|
|
{
|
|
sdaDataAdapter.SelectCommand = sdaCommand;
|
|
sdaDataAdapter.Fill(dataSet);
|
|
}
|
|
|
|
SetDNAME(dataSet.Tables[0], "NapId,NapTipusId,HetirendId");
|
|
SetBoolFields(dataSet.Tables[0], "IsOrarendiNap,IsOsszesCsoportraVonatkozik,IsUresOrarend");
|
|
|
|
return dataSet;
|
|
}
|
|
}
|
|
|
|
public DataSet GetOsztalycsoportTanevrendje(DateTime? date, int osztalyCsoportId, int intezmenyId, int tanevId, bool? osszesCsoportVonatkozokat = null)
|
|
{
|
|
using (var command = new SDACommand())
|
|
{
|
|
command.Connection = UserContext.Instance.SDAConnection;
|
|
command.Transaction = UserContext.Instance.SDATransaction;
|
|
command.CommandType = CommandType.StoredProcedure;
|
|
command.CommandText = "uspGetOsztalycsoportTanevrendje";
|
|
|
|
command.Parameters.Add("pIntezmenyId", intezmenyId);
|
|
command.Parameters.Add("pTanevId", tanevId);
|
|
command.Parameters.Add("pOsztalyCsoportId", osztalyCsoportId);
|
|
|
|
if (osszesCsoportVonatkozokat.HasValue)
|
|
{
|
|
command.Parameters.Add("pOsszesCsoportVonatkozokat", osszesCsoportVonatkozokat.Value ? 1 : 0);
|
|
}
|
|
else
|
|
{
|
|
command.Parameters.Add("pOsszesCsoportVonatkozokat", DBNull.Value);
|
|
}
|
|
|
|
if (date.HasValue)
|
|
{
|
|
command.Parameters.Add("pDate", date.Value);
|
|
}
|
|
else
|
|
{
|
|
command.Parameters.Add("pDate", DBNull.Value);
|
|
}
|
|
|
|
var dts = new DataSet();
|
|
using (var adapter = new SDADataAdapter())
|
|
{
|
|
adapter.SelectCommand = command;
|
|
adapter.Fill(dts);
|
|
}
|
|
return dts;
|
|
}
|
|
}
|
|
|
|
/// INFO @DevKornel: Mobil használja
|
|
public DataSet ListTanevRendekOsztalyCsoportokkal(int tanevId, DateTime? start, DateTime? end, int? tanuloId = null, OktNevelesiKategoriaEnum? feladatKategoria = null)
|
|
{
|
|
var paramList = new List<CommandParameter>
|
|
{
|
|
new CommandParameter("pTanevId", tanevId)
|
|
};
|
|
|
|
if (feladatKategoria.HasValue)
|
|
{
|
|
paramList.Add(new CommandParameter("pFeladatKategoriaId", (int)feladatKategoria));
|
|
}
|
|
|
|
var command = @"
|
|
SELECT
|
|
tr.ID ID,
|
|
tr.C_DATUM Datum,
|
|
tr.C_NAPTIPUSA NapTip,
|
|
tr.C_ORARENDINAP OrarendiNap,
|
|
ocstr.C_OSZTALYCSOPORTID OsztalyCsoportId,
|
|
tr.C_HETNAPJA HetNapja,
|
|
tr.C_HETIREND Hetirend,
|
|
tr.C_OSSZESCSOPORTRAVONATKOZIK Global,
|
|
ISNULL(tr.C_MEGJEGYZES,'') as Megjegyzes
|
|
FROM T_TANEVRENDJE_OSSZES tr
|
|
LEFT JOIN T_OSZTALYCSOPORT_TANEVRENDJE ocstr ON C_TANEVRENDJEID = tr.ID
|
|
WHERE tr.TOROLT = 'F'
|
|
AND tr.C_TANEVID = :pTanevId
|
|
";
|
|
|
|
if (tanuloId.HasValue)
|
|
{
|
|
paramList.Add(new CommandParameter("pTanuloId", tanuloId));
|
|
command += @"
|
|
AND (tr.C_OSSZESCSOPORTRAVONATKOZIK = 'T'
|
|
OR ocstr.C_OSZTALYCSOPORTID IN (
|
|
SELECT
|
|
C_OSZTALYCSOPORTID
|
|
FROM T_TANULOCSOPORT tcs "
|
|
+ (feladatKategoria.HasValue ? @"
|
|
INNER JOIN T_OSZTALYCSOPORT_OSSZES ocs ON tcs.C_OSZTALYCSOPORTID = ocs.ID
|
|
AND ocs.TOROLT = 'F'
|
|
AND ocs.C_FELADATKATEGORIAID = @pFeladatKategoriaId" : "") + @"
|
|
WHERE C_TANULOID = :pTanuloId
|
|
AND tr.C_DATUM BETWEEN C_BELEPESDATUM AND ISNULL(C_KILEPESDATUM, tr.C_DATUM))
|
|
)
|
|
";
|
|
}
|
|
|
|
if (start.HasValue && end.HasValue)
|
|
{
|
|
paramList.Add(new CommandParameter("pKezdete", start));
|
|
paramList.Add(new CommandParameter("pVege", end));
|
|
|
|
command += " AND tr.C_DATUM between :pKezdete AND :pVege";
|
|
}
|
|
|
|
var ds = this.GetData(command, paramList, dictionaryItemColumns: "NapTip,HetNapja,Hetirend", booleanColumns: "OrarendiNap");
|
|
|
|
return ds;
|
|
}
|
|
|
|
public DataSet GetTanevredhezTartozoOsztCsopList(int tanevId, int tanevrendId, int? feladatKategoriaId, int? mukodesiHelyId, int? feladatEllatasiHelyId)
|
|
{
|
|
var parameters = new List<CommandParameter>();
|
|
parameters.Add(new CommandParameter("pTanevrendId", tanevrendId));
|
|
parameters.Add(new CommandParameter("ptanevId", tanevId));
|
|
|
|
var feladatKategoria = string.Empty;
|
|
var mukodesiHely = string.Empty;
|
|
var feladatEllatasiHely = string.Empty;
|
|
|
|
if (feladatKategoriaId.IsEntityId())
|
|
{
|
|
feladatKategoria = $"AND ocs.C_FELADATKATEGORIAID = {feladatKategoriaId}";
|
|
}
|
|
|
|
if (mukodesiHelyId.IsEntityId())
|
|
{
|
|
mukodesiHely = $"AND mukodesihely.ID = {mukodesiHelyId}";
|
|
}
|
|
|
|
if (feladatEllatasiHelyId.IsEntityId())
|
|
{
|
|
feladatEllatasiHely = $"AND ocs.C_FELADATELLATASIHELYID = {feladatEllatasiHelyId}";
|
|
}
|
|
|
|
string commandText = $@"
|
|
SELECT
|
|
ocs.ID
|
|
,te.C_NEV TanevNeve
|
|
,CASE WHEN (
|
|
SELECT COUNT(*) FROM T_OSZTALY_OSSZES WHERE ID = ocs.ID
|
|
) > 0 THEN 'Osztály'
|
|
WHEN (
|
|
SELECT COUNT(*) FROM T_CSOPORT_OSSZES WHERE ID = ocs.ID
|
|
) > 0 THEN 'Csoport' END OsztCsop
|
|
,ocs.C_NEV OsztCsopNev
|
|
,CASE WHEN (
|
|
SELECT COUNT(*) FROM T_OSZTALYCSOPORT_TANEVRENDJE where C_TANEVRENDJEID = @pTanevrendId AND C_OSZTALYCSOPORTID = ocs.ID
|
|
) > 0 THEN 1
|
|
ELSE 0 END Hozzarendelve
|
|
FROM T_OSZTALYCSOPORT_OSSZES ocs
|
|
LEFT JOIN T_TANEV_OSSZES te ON te.ID = ocs.C_TANEVID AND te.TOROLT = 'F'
|
|
LEFT JOIN T_CSOPORT_OSSZES cs ON cs.ID = ocs.ID AND cs.TOROLT = 'F'
|
|
INNER JOIN T_FELADATELLATASIHELY_OSSZES felhely ON felhely.ID = ocs.C_FELADATELLATASIHELYID AND felhely.C_INTEZMENYID = ocs.C_INTEZMENYID AND felhely.TOROLT = 'F'
|
|
INNER JOIN T_MUKODESIHELY_OSSZES mukodesihely ON mukodesihely.ID = felhely.C_MUKODESIHELYID AND mukodesihely.C_INTEZMENYID = ocs.C_INTEZMENYID AND mukodesihely.C_TANEVID = ocs.C_TANEVID AND mukodesihely.TOROLT = 'F'
|
|
WHERE ocs.TOROLT = 'F'
|
|
AND ocs.C_TANEVID = @ptanevId
|
|
AND cs.C_OSZTALYBONTASID IS NULL
|
|
{feladatKategoria}
|
|
{mukodesiHely}
|
|
{feladatEllatasiHely}";
|
|
|
|
var ds = this.GetData(commandText, parameters: parameters);
|
|
|
|
return ds;
|
|
}
|
|
|
|
public DataSet GetTanevHetirendTipusok(int tanevId)
|
|
{
|
|
var parameters = new List<CommandParameter>
|
|
{
|
|
new CommandParameter(nameof(tanevId), tanevId)
|
|
};
|
|
|
|
string commandText = $@"
|
|
SELECT DISTINCT
|
|
n.C_HETIREND AS HetirendId
|
|
,d.C_NAME Hetirend
|
|
FROM T_NAPTARIHET_OSSZES n
|
|
JOIN T_DICTIONARYITEMBASE_OSSZES d ON n.C_HETIREND = d.ID
|
|
AND n.C_TANEVID = d.C_TANEVID
|
|
AND d.TOROLT = 'F'
|
|
WHERE n.TOROLT = 'F'
|
|
AND n.C_TANEVID = @{nameof(tanevId)}
|
|
";
|
|
|
|
var ds = GetData(commandText, parameters);
|
|
return ds;
|
|
}
|
|
|
|
public DataSet GetTanevRendjeOsztalyCsoportok(int tanevId, List<int> tanevrendIds)
|
|
{
|
|
using (var sdaCommand = new SDACommand())
|
|
{
|
|
sdaCommand.Connection = UserContext.Instance.SDAConnection;
|
|
sdaCommand.Transaction = UserContext.Instance.SDATransaction;
|
|
sdaCommand.CommandType = CommandType.StoredProcedure;
|
|
sdaCommand.CommandText = "uspGetTanevRendjeOsztalyCsoportok";
|
|
|
|
sdaCommand.Parameters.Add("pTanevId", tanevId);
|
|
sdaCommand.Parameters.Add("pTanevrendIds", string.Join(",", tanevrendIds));
|
|
|
|
var dataSet = new DataSet();
|
|
using (var sdaDataAdapter = new SDADataAdapter())
|
|
{
|
|
sdaDataAdapter.SelectCommand = sdaCommand;
|
|
sdaDataAdapter.Fill(dataSet);
|
|
}
|
|
|
|
return dataSet;
|
|
}
|
|
}
|
|
|
|
public int GetTanevrendDefaultCsengetesiRend(int tanevId)
|
|
{
|
|
var parameters = new List<CommandParameter>();
|
|
parameters.Add(new CommandParameter("pTanevId", tanevId));
|
|
|
|
const string commandText = @"SELECT ID FROM T_CSENGETESIREND_OSSZES WHERE TOROLT = 'F' AND C_AKTIV = 'T' AND C_TANEVID = @pTanevId";
|
|
var ds = this.GetData(commandText, parameters);
|
|
DataRow dr = ds.Tables[0].Rows[0];
|
|
|
|
return int.Parse(dr["ID"].ToString());
|
|
}
|
|
|
|
public string GetTanevrendCsengetesiRendValue(int? id, int tanevId)
|
|
{
|
|
if (id.HasValue && id.Value > 0)
|
|
{
|
|
var commandText = string.Format(@"SELECT C_NEV FROM T_CSENGETESIREND_OSSZES WHERE TOROLT = 'F' AND ID = {0} AND C_TANEVID = {1}", id.Value, tanevId);
|
|
var ds = this.GetData(commandText);
|
|
DataRow dr = ds.Tables[0].Rows[0];
|
|
|
|
return dr["C_NEV"].ToString();
|
|
}
|
|
|
|
return string.Empty;
|
|
}
|
|
|
|
public DateTime GetElsoTanitasiNapDate(int intezmenyId, int tanevId)
|
|
{
|
|
DateTime defaultValue = DateTime.Today < new DateTime(DateTime.Today.Year, 9, 1) ? new DateTime(DateTime.Today.Year - 1, 9, 1) : new DateTime(DateTime.Today.Year, 9, 1);
|
|
var result = GetTanevRendjeDatumByNapTipus(intezmenyId, tanevId, (int)NapTipusEnum.elso_tanitasi_nap, defaultValue);
|
|
return result.Value;
|
|
}
|
|
|
|
public DateTime GetElsoFelevVege(int intezmenyId, int tanevId)
|
|
{
|
|
DateTime defaultValue = DateTime.Today < new DateTime(DateTime.Today.Year, 9, 1) ? new DateTime(DateTime.Today.Year, 1, 20) : new DateTime(DateTime.Today.Year + 1, 1, 20);
|
|
var result = GetTanevRendjeDatumByNapTipus(intezmenyId, tanevId, (int)NapTipusEnum.elso_felev_vege, defaultValue);
|
|
return result.Value;
|
|
}
|
|
|
|
public DateTime GetUtolsoTanitasiNapDate(int intezmenyId, int tanevId)
|
|
{
|
|
DateTime defaultValue = DateTime.Today < new DateTime(DateTime.Today.Year, 9, 1) ? new DateTime(DateTime.Today.Year, 6, 15) : new DateTime(DateTime.Today.Year + 1, 6, 15);
|
|
var result = GetTanevRendjeDatumByNapTipus(intezmenyId, tanevId, (int)NapTipusEnum.utolso_tanitasi_nap, defaultValue);
|
|
return result.Value;
|
|
}
|
|
|
|
public DateTime? GetNapDate(int intezmenyId, int tanevId, NapTipusEnum napEnum, bool returnDefaultValue = true)
|
|
{
|
|
DateTime defaultValue = DateTime.Today < new DateTime(DateTime.Today.Year, 9, 1) ? new DateTime(DateTime.Today.Year, 6, 15) : new DateTime(DateTime.Today.Year + 1, 6, 15);
|
|
var result = GetTanevRendjeDatumByNapTipus(intezmenyId, tanevId, (int)napEnum, returnDefaultValue ? defaultValue : (DateTime?)null);
|
|
return result;
|
|
}
|
|
|
|
private DateTime? GetTanevRendjeDatumByNapTipus(int intezmenyId, int tanevId, int napTipusId, DateTime? defaultValue)
|
|
{
|
|
var commandParameterList = new List<CommandParameter>
|
|
{
|
|
new CommandParameter("pIntezmenyId", intezmenyId),
|
|
new CommandParameter("pTanevId", tanevId)
|
|
};
|
|
|
|
var commandText = $@"SELECT
|
|
C_DATUM Datum
|
|
FROM
|
|
T_TANEVRENDJE_OSSZES
|
|
WHERE
|
|
T_TANEVRENDJE_OSSZES.C_INTEZMENYID = :pIntezmenyId
|
|
AND T_TANEVRENDJE_OSSZES.C_TANEVID = :pTanevId
|
|
AND T_TANEVRENDJE_OSSZES.TOROLT = 'F'
|
|
AND T_TANEVRENDJE_OSSZES.C_NAPTIPUSA = {napTipusId}";
|
|
|
|
var ds = GetData(commandText, commandParameterList);
|
|
if (ds.Tables[0].Rows.Count == 0)
|
|
{
|
|
return defaultValue;
|
|
}
|
|
var result = Convert.ToDateTime(ds.Tables[0].Rows[0]["Datum"]);
|
|
return result;
|
|
}
|
|
|
|
public DateTime? GetVegzosUtolsoTanitasiNap(int tanevId)
|
|
{
|
|
using (var command = new SDACommand())
|
|
{
|
|
command.Connection = UserContext.Instance.SDAConnection;
|
|
command.Transaction = UserContext.Instance.SDATransaction;
|
|
command.Parameters.Add("ptanevId", SDADBType.Int).Value = tanevId;
|
|
command.CommandText = @"
|
|
SELECT
|
|
C_DATUM Datum
|
|
FROM
|
|
T_TANEVRENDJE_OSSZES
|
|
WHERE
|
|
TOROLT = 'F'
|
|
AND C_TANEVID = @ptanevId
|
|
AND C_NAPTIPUSA = 1402
|
|
";
|
|
//1402 == Utolsó tanítási nap a végzős évfolyamokon
|
|
|
|
var result = command.ExecuteScalar();
|
|
|
|
bool isParsed = DateTime.TryParse((result ?? "").ToString(), out var datum);
|
|
|
|
DateTime? ret = null;
|
|
if (isParsed)
|
|
{
|
|
ret = datum;
|
|
}
|
|
|
|
return ret;
|
|
}
|
|
}
|
|
|
|
public List<int> GetMarFelvettKiemeltTanevEsemenyTipusok(int tanevId)
|
|
{
|
|
var ret = new List<int>();
|
|
|
|
const string commandText = @"
|
|
SELECT DISTINCT
|
|
C_NAPTIPUSA NapTipusa
|
|
FROM
|
|
T_TANEVRENDJE_OSSZES
|
|
WHERE
|
|
TOROLT = 'F'
|
|
AND C_TANEVID = @ptanevId
|
|
AND C_NAPTIPUSA IN (1394, 1395, 1400, 1402, 1403, 1404)
|
|
";
|
|
// 1394 == Első tanítási nap
|
|
// 1395 == Utolsó tanítási nap
|
|
// 1400 == Első félév vége
|
|
// 1402 == Utolsó tanítási nap a végzős évfolyamokon
|
|
// 1403 == I. negyedév vége
|
|
// 1404 == III. negyedév vége
|
|
|
|
var parameters = new List<CommandParameter>
|
|
{
|
|
new CommandParameter("ptanevId", tanevId)
|
|
};
|
|
var ds = GetData(commandText, parameters);
|
|
|
|
foreach (DataRow row in ds.Tables[0].Rows)
|
|
{
|
|
var napTipusa = Convert.ToInt32(row["NapTipusa"]);
|
|
ret.Add(napTipusa);
|
|
}
|
|
|
|
return ret;
|
|
}
|
|
|
|
public DataSet GetTanevRendjeByDatum(DateTime datum, int tanevId, int intezmenyId)
|
|
{
|
|
var param = new List<CommandParameter>();
|
|
param.Add(new CommandParameter("pDatum", datum));
|
|
param.Add(new CommandParameter("pTanevId", tanevId));
|
|
param.Add(new CommandParameter("pIntezmenyId", intezmenyId));
|
|
|
|
var command = @"
|
|
select
|
|
T_TANEVRENDJE_OSSZES.ID TanevrendId,
|
|
C_OSSZESCSOPORTRAVONATKOZIK IsGlobal,
|
|
C_ISURESORAREND as IsUresOrarend,
|
|
ocs.ID OsztalyCsoportId,
|
|
ocs.C_NEV OsztalyCsoportNev,
|
|
T_TANEVRENDJE_OSSZES.C_HETNAPJA HetNapja,
|
|
T_TANEVRENDJE_OSSZES.C_NAPTIPUSA NapTipusa,
|
|
(select C_ALAPHETNAPJA from T_NAPTARINAP where C_NAPDATUMA = @pDatum and C_TANEVID = @pTanevId and C_INTEZMENYID = @pIntezmenyId) EredetiHetNapja
|
|
from T_TANEVRENDJE_OSSZES
|
|
left join T_OSZTALYCSOPORT_TANEVRENDJE on T_TANEVRENDJE_OSSZES.ID = T_OSZTALYCSOPORT_TANEVRENDJE.C_TANEVRENDJEID
|
|
left join T_OSZTALYCSOPORT_OSSZES ocs on ocs.ID = T_OSZTALYCSOPORT_TANEVRENDJE.C_OSZTALYCSOPORTID and ocs.TOROLT = 'F'
|
|
where
|
|
T_TANEVRENDJE_OSSZES.C_DATUM = @pDatum and T_TANEVRENDJE_OSSZES.TOROLT = 'F'
|
|
";
|
|
return this.GetData(command.ToString(), param);
|
|
|
|
}
|
|
|
|
public DataSet GetTanevrendekByCsengetesiRend(int tanevId, int csRendId)
|
|
{
|
|
var param = new List<CommandParameter>();
|
|
param.Add(new CommandParameter("pCsRendID", csRendId));
|
|
param.Add(new CommandParameter("ptanevId", tanevId));
|
|
|
|
const string command = @"select
|
|
T_TANEVRENDJE_OSSZES.C_DATUM Datum,
|
|
T_TANEVRENDJE_OSSZES.C_MEGJEGYZES Nev,
|
|
T_TANEVRENDJE_OSSZES.C_NAPTIPUSA Tipus
|
|
from T_TANEVRENDJE_OSSZES
|
|
inner join
|
|
(
|
|
select ID from T_TANEV_OSSZES where TOROLT = 'F' and C_AKTIV = 'T'
|
|
) TANEV on TANEV.ID = T_TANEVRENDJE_OSSZES.C_TANEVID
|
|
where T_TANEVRENDJE_OSSZES.TOROLT = 'F'
|
|
and T_TANEVRENDJE_OSSZES.C_CSENGETESIRENDID = :pCsRendID
|
|
AND T_TANEVRENDJE_OSSZES.C_TANEVID = @ptanevId
|
|
";
|
|
return this.GetData(command, param, "Tipus");
|
|
}
|
|
|
|
public bool GetVanTanevRendjeEsemenyCsengetesiRenddel(DateTime datum, List<int> osztalyIdLista)
|
|
{
|
|
var param = new List<CommandParameter>();
|
|
param.Add(new CommandParameter("pDatum", datum));
|
|
|
|
string str = "";
|
|
if (osztalyIdLista.Count > 0)
|
|
{
|
|
for (int i = 0; i < osztalyIdLista.Count; ++i)
|
|
{
|
|
param.Add(new CommandParameter("pOsztalyCsoportID" + i, osztalyIdLista[i]));
|
|
str += (":pOsztalyCsoportID" + i + ",");
|
|
}
|
|
str = str.Remove(str.Length - 1);
|
|
}
|
|
|
|
var command = @"
|
|
SELECT
|
|
T_TANEVRENDJE.ID ID,
|
|
T_TANEVRENDJE.C_DATUM Datum,
|
|
T_TANEVRENDJE.C_OSSZESCSOPORTRAVONATKOZIK,
|
|
T_OSZTALYCSOPORT_TANEVRENDJE.C_OSZTALYCSOPORTID
|
|
FROM
|
|
T_TANEVRENDJE
|
|
LEFT JOIN T_OSZTALYCSOPORT_TANEVRENDJE ON T_OSZTALYCSOPORT_TANEVRENDJE.C_TANEVRENDJEID = T_TANEVRENDJE.ID
|
|
WHERE
|
|
T_TANEVRENDJE.TOROLT = 'F'
|
|
AND T_TANEVRENDJE.C_CSENGETESIRENDID NOT IN (SELECT ID FROM T_CSENGETESIREND WHERE TOROLT = 'F' AND C_AKTIV = 'T')
|
|
AND T_TANEVRENDJE.C_DATUM = :pDatum"
|
|
+ (osztalyIdLista.Count > 0 ? @" AND (T_TANEVRENDJE.C_OSSZESCSOPORTRAVONATKOZIK = 'T' OR T_OSZTALYCSOPORT_TANEVRENDJE.C_OSZTALYCSOPORTID IN (" + str + "))" : " AND T_TANEVRENDJE.C_OSSZESCSOPORTRAVONATKOZIK = 'T'")
|
|
;
|
|
|
|
var ds = GetData(command, param);
|
|
|
|
return ds.Tables[0].Rows.Count > 0;
|
|
}
|
|
|
|
public bool GetVanTanevRendjeEsemenyNemKotottMunkaido(int tanevId, DateTime startDatum, DateTime endDatum)
|
|
{
|
|
var param = new List<CommandParameter>();
|
|
param.Add(new CommandParameter("pStartDatum", startDatum.Date));
|
|
param.Add(new CommandParameter("pEndDatum", endDatum.Date));
|
|
param.Add(new CommandParameter("ptanevId", tanevId));
|
|
|
|
var command = @"
|
|
SELECT 1 FROM T_TANEVRENDJE_OSSZES TANEVRENDJE
|
|
WHERE
|
|
TOROLT = 'F' AND C_NAPTIPUSA IN (1389,1390,1386,1399,1391,1388,1392,1398,1397,1396)
|
|
AND C_DATUM BETWEEN :pStartDatum AND :pEndDatum
|
|
AND C_TANEVID = @ptanevId
|
|
UNION
|
|
SELECT 1 FROM T_NAPTARINAP n
|
|
LEFT JOIN T_TANEVRENDJE_OSSZES t ON t.C_DATUM = n.C_NAPDATUMA
|
|
WHERE
|
|
(n.C_NAPDATUMA = :pStartDatum OR n.C_NAPDATUMA = :pEndDatum)
|
|
AND n.C_HETNAPJA in (1413,1414)
|
|
AND (t.ID IS NULL OR t.C_NAPTIPUSA IN (1389,1390,1386,1399,1391,1388,1392,1398,1397,1396))
|
|
AND t.C_TANEVID = @ptanevId
|
|
";
|
|
var ds = GetData(command, param);
|
|
return ds.Tables[0].Rows.Count > 0;
|
|
}
|
|
|
|
public void Delete(ITanevRendje entity)
|
|
{
|
|
var tanevRendje = (TanevRendje)entity;
|
|
(tanevRendje).Delete();
|
|
DalHelper.Commit();
|
|
}
|
|
|
|
public void RemoveKapcsolodoOsztalyCsoportok(ITanevRendje entity)
|
|
{
|
|
((TanevRendje)entity).OsztalyCsoport.RemoveAll();
|
|
}
|
|
|
|
public int Insert(ITanevRendje entity, bool csoportos, Dictionary<int, bool> osztalyCsoportDic, int tanevId, int modifierId)
|
|
{
|
|
var dalEntity = (TanevRendje)entity;
|
|
dalEntity.Insert(true);
|
|
if (csoportos)
|
|
{
|
|
UpdateKapcsolodoOsztalyCsoportok(DalHelper, dalEntity, osztalyCsoportDic, tanevId, modifierId, !entity.EgyediNap);
|
|
}
|
|
else
|
|
{
|
|
DeleteKapcsolodoOsztalyCsoportok(dalEntity);
|
|
}
|
|
|
|
dalEntity.FullUpdate(true);
|
|
DalHelper.Commit();
|
|
return dalEntity.ID;
|
|
}
|
|
|
|
public void FullUpdate(ITanevRendje entity, bool csoportos, Dictionary<int, bool> osztalyCsoportDic, int tanevId, int modifierId, bool egyediNapKihagyas = false)
|
|
{
|
|
var dalEntity = (TanevRendje)entity;
|
|
dalEntity.FullUpdate(true);
|
|
if (csoportos)
|
|
{
|
|
UpdateKapcsolodoOsztalyCsoportok(DalHelper, dalEntity, osztalyCsoportDic, tanevId, modifierId, egyediNapKihagyas);
|
|
}
|
|
else
|
|
{
|
|
DeleteKapcsolodoOsztalyCsoportok(dalEntity);
|
|
}
|
|
|
|
dalEntity.FullUpdate(true);
|
|
DalHelper.Commit();
|
|
}
|
|
|
|
public bool IsDateHasGlobalTanevRend(DateTime date, int tanevId)
|
|
{
|
|
using (var command = new SDACommand())
|
|
{
|
|
command.Connection = UserContext.Instance.SDAConnection;
|
|
command.Transaction = UserContext.Instance.SDATransaction;
|
|
|
|
command.Parameters.Add(nameof(date), date);
|
|
command.Parameters.Add(nameof(tanevId), tanevId);
|
|
|
|
command.CommandText = $@"
|
|
SELECT COUNT(ID)
|
|
FROM T_TANEVRENDJE
|
|
WHERE C_OSSZESCSOPORTRAVONATKOZIK = 'T'
|
|
AND C_TANEVID = @{nameof(tanevId)}
|
|
AND C_DATUM = @{nameof(date)}
|
|
AND TOROLT = 'F'
|
|
";
|
|
|
|
var result = int.Parse(command.ExecuteScalar().ToString()) > 0;
|
|
return result;
|
|
}
|
|
}
|
|
|
|
private void UpdateKapcsolodoOsztalyCsoportok(IDalHandler h, TanevRendje entity, Dictionary<int, bool> osztalyCsoportDic, int tanevId, int modifierId, bool egyediNapKihagyas = false)
|
|
{
|
|
foreach (var osztalyCsoport in osztalyCsoportDic)
|
|
{
|
|
var ocs = (OsztalyCsoport)h.OsztalyCsoport().Get(osztalyCsoport.Key);
|
|
|
|
if (osztalyCsoport.Value)
|
|
{
|
|
//Ha osztály, akkor a kapcsolódó osztálybontásos csoportokhoz is le kell menteni
|
|
if (h.OsztalyCsoport().IsOsztaly(osztalyCsoport.Key, tanevId))
|
|
{
|
|
var osztalybontasosCsoportok = h.OsztalyCsoport().GetOsztalybontasosCsoportok(osztalyCsoport.Key, tanevId);
|
|
foreach (OsztalyCsoport osztalycsoport in osztalybontasosCsoportok)
|
|
{
|
|
if (!entity.OsztalyCsoport.Contains(osztalycsoport))
|
|
{
|
|
entity.AddToOsztalyCsoport(osztalycsoport);
|
|
}
|
|
}
|
|
}
|
|
|
|
if (!entity.OsztalyCsoport.Contains(ocs))
|
|
{
|
|
entity.AddToOsztalyCsoport(ocs);
|
|
}
|
|
|
|
if (!egyediNapKihagyas)
|
|
InsertEgyediNapOsztalycsoport(entity.ID, osztalyCsoport.Key, entity.IntezmenyId, entity.TanevId);
|
|
}
|
|
else
|
|
{
|
|
//Ha osztály, akkor a kapcsolódó osztálybontásos csoportoknál is törölni kell
|
|
if (h.OsztalyCsoport().IsOsztaly(osztalyCsoport.Key, tanevId))
|
|
{
|
|
var osztalybontasosCsoportok = h.OsztalyCsoport().GetOsztalybontasosCsoportok(osztalyCsoport.Key, tanevId);
|
|
foreach (OsztalyCsoport osztalycsoport in osztalybontasosCsoportok)
|
|
{
|
|
if (entity.OsztalyCsoport.Contains(osztalycsoport))
|
|
{
|
|
entity.RemoveFromOsztalyCsoport(osztalycsoport);
|
|
}
|
|
}
|
|
}
|
|
|
|
if (entity.OsztalyCsoport.Contains(ocs))
|
|
{
|
|
entity.RemoveFromOsztalyCsoport(ocs);
|
|
}
|
|
|
|
if (!egyediNapKihagyas)
|
|
DeleteEgyediNapOsztalycsoport(entity.ID, osztalyCsoport.Key, modifierId);
|
|
}
|
|
}
|
|
}
|
|
|
|
private void DeleteKapcsolodoOsztalyCsoportok(TanevRendje entity)
|
|
{
|
|
var osztalyCsoportList = entity.OsztalyCsoport.ToList();
|
|
foreach (var osztalyCsoport in osztalyCsoportList)
|
|
{
|
|
entity.RemoveFromOsztalyCsoport(osztalyCsoport);
|
|
}
|
|
}
|
|
|
|
public bool IsTanoraOrNapirendRogzitheto(int naptipusId, int intezmenyId, int tanevId)
|
|
{
|
|
var param = new List<CommandParameter>();
|
|
param.Add(new CommandParameter("pNaptipusId", naptipusId));
|
|
param.Add(new CommandParameter("pIntezmenyId", intezmenyId));
|
|
param.Add(new CommandParameter("pTanevId", tanevId));
|
|
|
|
var command = @"
|
|
SELECT 1 FROM T_NAPTIPUS_OSSZES nt
|
|
WHERE
|
|
nt.C_ALTANEVID = @pTanevId AND nt.C_ALINTEZMENYID = @pIntezmenyId
|
|
AND (nt.C_ISTANORAI = 'T' OR nt.C_ISTANORANKIVULI = 'T')
|
|
AND nt.ID = @pNaptipusId
|
|
";
|
|
|
|
var ds = GetData(command, param);
|
|
return ds.Tables[0].Rows.Count > 0;
|
|
}
|
|
|
|
public void InsertEgyediNap(int tanevRendjeId, 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 = "uspInsertEgyediNap";
|
|
command.Parameters.Add("pTanevId", tanevId);
|
|
command.Parameters.Add("pIntezmenyId", intezmenyId);
|
|
command.Parameters.Add("pTanevRendjeId", tanevRendjeId);
|
|
|
|
command.ExecuteNonQuery();
|
|
DalHelper.Commit();
|
|
}
|
|
}
|
|
|
|
public void InsertEgyediNapOsztalycsoport(int tanevRendjeId, 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 = "uspInsertEgyediNapOsztalycsoport";
|
|
command.Parameters.Add("pTanevId", tanevId);
|
|
command.Parameters.Add("pIntezmenyId", intezmenyId);
|
|
command.Parameters.Add("pTanevRendjeId", tanevRendjeId);
|
|
command.Parameters.Add("pOsztalycsoportId", osztalycsoportId);
|
|
command.Parameters.Add("pIsKapcsolodoCsoportokIs", 'T');
|
|
|
|
command.ExecuteNonQuery();
|
|
DalHelper.Commit();
|
|
}
|
|
}
|
|
|
|
public void DeleteEgyediNap(int tanevRendjeId, bool isKezzelRogzitesTorles, int modifierId)
|
|
{
|
|
using (var command = new SDACommand())
|
|
{
|
|
command.Connection = UserContext.Instance.SDAConnection;
|
|
command.Transaction = UserContext.Instance.SDATransaction;
|
|
command.CommandType = CommandType.StoredProcedure;
|
|
|
|
command.CommandText = "uspDeleteEgyediNap";
|
|
command.Parameters.Add("pTanevRendjeId", tanevRendjeId);
|
|
command.Parameters.Add("pIsKezzelRogzitesTorles", isKezzelRogzitesTorles ? "T" : "F");
|
|
command.Parameters.Add("pModifierId", modifierId);
|
|
|
|
command.ExecuteNonQuery();
|
|
DalHelper.Commit();
|
|
}
|
|
}
|
|
|
|
public void DeleteEgyediNapOsztalycsoport(int tanevRendjeId, int osztalycsoportId, int modifierId)
|
|
{
|
|
using (var command = new SDACommand())
|
|
{
|
|
command.Connection = UserContext.Instance.SDAConnection;
|
|
command.Transaction = UserContext.Instance.SDATransaction;
|
|
command.CommandType = CommandType.StoredProcedure;
|
|
|
|
command.CommandText = "uspDeleteEgyediNapOsztalycsoport";
|
|
command.Parameters.Add("pTanevRendjeId", tanevRendjeId);
|
|
command.Parameters.Add("pOsztalycsoportId", osztalycsoportId);
|
|
command.Parameters.Add("pIsKapcsolodoCsoportokIs", 'T');
|
|
command.Parameters.Add("pModifierId", modifierId);
|
|
|
|
command.ExecuteNonQuery();
|
|
DalHelper.Commit();
|
|
}
|
|
}
|
|
|
|
public bool GetKezzelrogzitettOrarendiOra(DateTime datum)
|
|
{
|
|
var param = new List<CommandParameter>();
|
|
param.Add(new CommandParameter("pDatum", datum));
|
|
|
|
var command = @"
|
|
SELECT 1 FROM T_ORARENDIORA_OSSZES oo
|
|
WHERE
|
|
oo.C_ORAERVENYESSEGKEZDETE = @pDatum
|
|
and oo.C_ORAERVENYESSEGVEGE = @pDatum
|
|
and oo.TOROLT = 'F' and oo.C_EGYEDINAP = 'T' and oo.C_ISKEZZELFELVETTEGYEDINAP = 'T'
|
|
";
|
|
|
|
var ds = GetData(command, param);
|
|
return ds.Tables[0].Rows.Count > 0;
|
|
}
|
|
public void GenerateOrarend(int intezmenyId, int tanevId, int? csoportId, DateTime? datum, int? napTipus)
|
|
{
|
|
List<int> lista = new List<int>() {
|
|
(int)NapTipusEnum.utolso_tanitasi_nap_a_vegzos_evfolyamokon,
|
|
(int)NapTipusEnum.utolso_tanitasi_nap,
|
|
(int)NapTipusEnum.utolso_tanitasi_nap_honvedelmi,
|
|
(int)NapTipusEnum.utolso_tanitasi_nap_keresztfeleves,
|
|
(int)NapTipusEnum.utolso_tanitasi_nap_rendeszet,
|
|
(int)NapTipusEnum.utolso_tanitasi_nap_reszszakkepesites
|
|
};
|
|
|
|
datum = !napTipus.HasValue || lista.Contains(napTipus.Value) ? null : datum;
|
|
DalHelper.OrarendiOra().UpdateOrarend(intezmenyId, tanevId, datum, datum, null, csoportId);
|
|
}
|
|
public void InsertTanevRendjeNewBontottCsoport(int osztalyId, int bontottCsoportId)
|
|
{
|
|
using (var command = new SDACommand())
|
|
{
|
|
command.Connection = UserContext.Instance.SDAConnection;
|
|
command.Transaction = UserContext.Instance.SDATransaction;
|
|
command.CommandType = CommandType.StoredProcedure;
|
|
|
|
command.CommandText = "uspInsertTanevRendjeNewBontottCsoport";
|
|
command.Parameters.Add("osztalyId", osztalyId);
|
|
command.Parameters.Add("bonottCsoportId", bontottCsoportId);
|
|
|
|
command.ExecuteNonQuery();
|
|
DalHelper.Commit();
|
|
}
|
|
}
|
|
|
|
public void DeleteTanevRendjeNewBontottCsoport(int osztalyId, int bontottCsoportId, int modifierId)
|
|
{
|
|
using (var command = new SDACommand())
|
|
{
|
|
command.Connection = UserContext.Instance.SDAConnection;
|
|
command.Transaction = UserContext.Instance.SDATransaction;
|
|
command.CommandType = CommandType.StoredProcedure;
|
|
|
|
command.CommandText = "uspDeleteTanevRendjeNewBontottCsoport";
|
|
command.Parameters.Add(nameof(osztalyId), osztalyId);
|
|
command.Parameters.Add(nameof(bontottCsoportId), bontottCsoportId);
|
|
command.Parameters.Add(nameof(modifierId), modifierId);
|
|
|
|
command.ExecuteNonQuery();
|
|
DalHelper.Commit();
|
|
}
|
|
}
|
|
|
|
public DataSet EnabledNemKotottMunkaidoRogzitese(DateTime date, int intezmenyId, int tanevId)
|
|
{
|
|
var paramList = new List<CommandParameter>();
|
|
paramList.Add(new CommandParameter("pDate", date.Date));
|
|
paramList.Add(new CommandParameter("pIntezmenyId", intezmenyId));
|
|
paramList.Add(new CommandParameter("pTanevId", tanevId));
|
|
|
|
var command = @"
|
|
SELECT
|
|
T_NAPTIPUS.C_ISLENEMKOTOTTMUNKAIDO
|
|
,T_TANEVRENDJE_OSSZES.C_OSSZESCSOPORTRAVONATKOZIK
|
|
FROM T_TANEVRENDJE_OSSZES
|
|
INNER JOIN T_NAPTIPUS on T_TANEVRENDJE_OSSZES.C_NAPTIPUSA = T_NAPTIPUS.ID
|
|
AND T_TANEVRENDJE_OSSZES.C_INTEZMENYID = T_NAPTIPUS.C_ALINTEZMENYID
|
|
AND T_TANEVRENDJE_OSSZES.C_TANEVID = T_NAPTIPUS.C_ALTANEVID
|
|
WHERE
|
|
T_TANEVRENDJE_OSSZES.TOROLT = 'F'
|
|
AND T_TANEVRENDJE_OSSZES.C_TANEVID = @pTanevId
|
|
AND T_TANEVRENDJE_OSSZES.C_INTEZMENYID = @pIntezmenyId
|
|
AND T_TANEVRENDJE_OSSZES.C_DATUM = @pDate
|
|
";
|
|
|
|
return this.GetData(command, paramList);
|
|
}
|
|
|
|
public bool HasTanitasiNap(DateTime date, List<int> tanitasiNapIdList, int tanevId)
|
|
{
|
|
var paramsList = new List<CommandParameter>()
|
|
{
|
|
new CommandParameter(nameof(date), date),
|
|
new CommandParameter(nameof(tanevId), tanevId)
|
|
};
|
|
|
|
var command = $@"
|
|
SELECT tr.C_NAPTIPUSA
|
|
FROM T_TANEVRENDJE_OSSZES tr
|
|
WHERE
|
|
tr.C_TANEVID = @{nameof(tanevId)}
|
|
AND tr.C_DATUM = @{nameof(date)}
|
|
AND tr.TOROLT = 'F'
|
|
AND tr.C_NAPTIPUSA IN ({SqlLogic.ParseListToParameter(tanitasiNapIdList)})
|
|
";
|
|
|
|
var ds = GetData(command, paramsList);
|
|
return ds.Tables[0].Rows.Count > 0;
|
|
}
|
|
|
|
public DataSet GetTanevRendjeNaptipusTulajdonsagokkal(int tanevId, int? osztalyCsoportId = null)
|
|
{
|
|
var paramsList = new List<CommandParameter>()
|
|
{
|
|
new CommandParameter("pTanevId", tanevId)
|
|
};
|
|
|
|
var command = $@"
|
|
SELECT tr.ID
|
|
,tr.C_DATUM
|
|
,tr.C_HETIREND
|
|
,tr.C_HETNAPJA
|
|
,tr.C_NAPTIPUSA
|
|
,tr.C_EGYEDINAP
|
|
,tr.C_OSSZESCSOPORTRAVONATKOZIK
|
|
,tr.C_ORARENDINAP
|
|
,tr.C_ELTERONAPDATUMA
|
|
,ocstr.C_OSZTALYCSOPORTID
|
|
,ocs.C_NEV
|
|
,nt.C_ISTANORAI
|
|
,nt.C_ISTANORANKIVULI
|
|
FROM T_TANEVRENDJE_OSSZES tr
|
|
INNER JOIN T_NAPTIPUS_OSSZES nt ON nt.ID = tr.C_NAPTIPUSA AND nt.C_ALTANEVID = tr.C_TANEVID AND nt.TOROLT = 'F'
|
|
LEFT JOIN T_OSZTALYCSOPORT_TANEVRENDJE ocstr ON ocstr.C_TANEVRENDJEID = tr.ID
|
|
LEFT JOIN T_OSZTALYCSOPORT_OSSZES ocs on ocs.ID = ocstr.C_OSZTALYCSOPORTID AND ocs.TOROLT = 'F'
|
|
WHERE tr.TOROLT = 'F'
|
|
AND tr.C_TANEVID = @pTanevId
|
|
";
|
|
if (osztalyCsoportId.IsEntityId())
|
|
{
|
|
paramsList.Add(new CommandParameter("pOsztalyCsoportId", osztalyCsoportId.Value));
|
|
command += @"
|
|
AND ocstr.C_OSZTALYCSOPORTID IS NULL or ocstr.C_OSZTALYCSOPORTID = @pOsztalyCsoportId
|
|
";
|
|
}
|
|
|
|
return GetData(command, paramsList);
|
|
}
|
|
|
|
public bool IsRendkivuliTanitasiNap(int tanevId, DateTime datum)
|
|
{
|
|
using (var command = new SDACommand())
|
|
{
|
|
command.Connection = UserContext.Instance.SDAConnection;
|
|
command.Transaction = UserContext.Instance.SDATransaction;
|
|
|
|
command.Parameters.Add("pTanevId", tanevId);
|
|
command.Parameters.Add("pDatum", datum.Date);
|
|
command.Parameters.Add("pNaptipusa", (int)NapTipusEnum.RendkivuliTanitasiNap);
|
|
|
|
command.CommandText = @"
|
|
IF EXISTS
|
|
(
|
|
SELECT 1
|
|
FROM T_TANEVRENDJE_OSSZES tr
|
|
WHERE tr.C_TANEVID = :pTanevId
|
|
AND tr.TOROLT = 'F'
|
|
AND tr.C_DATUM = :pDatum
|
|
AND tr.C_NAPTIPUSA = :pNaptipusa
|
|
)
|
|
SELECT 1
|
|
ELSE
|
|
SELECT 0
|
|
";
|
|
|
|
return Convert.ToBoolean(command.ExecuteScalar());
|
|
}
|
|
}
|
|
}
|
|
}
|