287 lines
11 KiB
C#
287 lines
11 KiB
C#
using System;
|
|
using System.Collections.Generic;
|
|
using System.Data;
|
|
using System.Linq;
|
|
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 NaptariHetDAL : DataAccessBase, INaptariHetDal
|
|
{
|
|
public NaptariHetDAL(DalHandler handler) : base(handler)
|
|
{
|
|
}
|
|
|
|
public NaptariHetDAL(DalHandler handler, GridParameters parameters) : base(handler, parameters)
|
|
{
|
|
}
|
|
|
|
/// INFO @DevKornel: Mobil használja
|
|
public DataSet GetNaptariHetList(int tanevId, NaptariHetAndOrarendOverlapFilter naptariHetAndOrarendOverlapFilter = null)
|
|
{
|
|
var parameters = new List<CommandParameter>
|
|
{
|
|
new CommandParameter("pTanevId", tanevId)
|
|
};
|
|
|
|
var commandText = @"
|
|
SELECT
|
|
ID AS ID
|
|
,C_HETSORSZAMA AS HetSorszama
|
|
,C_HETKEZDONAPJA AS HetKezdoNapja
|
|
,C_HETUTOLSONAPJA AS HetUtolsoNapja
|
|
,C_HETIREND AS Hetirend
|
|
FROM
|
|
T_NAPTARIHET_OSSZES
|
|
WHERE
|
|
C_TANEVID = :pTanevId";
|
|
|
|
if (naptariHetAndOrarendOverlapFilter != null)
|
|
{
|
|
commandText += " AND C_HETKEZDONAPJA <= :pVegNapDatuma AND :pKezdoNapDatuma <= C_HETUTOLSONAPJA";
|
|
parameters.Add(new CommandParameter("pKezdoNapDatuma", naptariHetAndOrarendOverlapFilter.OrarendElemKezdoNapDatuma.Date));
|
|
parameters.Add(new CommandParameter("pVegNapDatuma", naptariHetAndOrarendOverlapFilter.OrarendElemVegNapDatuma.Date));
|
|
}
|
|
|
|
DataSet ds = GetData(commandText, parameters, dictionaryItemColumns: "Hetirend");
|
|
return ds;
|
|
}
|
|
|
|
public void SetHetirend(Dictionary<int, int?> modifiedHetirendList, int intezmenyId, int tanevId)
|
|
{
|
|
foreach (KeyValuePair<int, int?> modifiedHetirend in modifiedHetirendList)
|
|
{
|
|
int naptariHetId = modifiedHetirend.Key;
|
|
int? hetirend = modifiedHetirend.Value;
|
|
|
|
var naptariHet = NaptariHet.GiveAnInstance();
|
|
naptariHet.LoadByID(naptariHetId);
|
|
|
|
if (hetirend.HasValue)
|
|
{
|
|
naptariHet.HetiRend = hetirend.Value;
|
|
}
|
|
else
|
|
{
|
|
naptariHet.HetiRend = null;
|
|
}
|
|
|
|
naptariHet.Update();
|
|
|
|
var napIdList = GetNaptariNapIdsIntervallumonBelul(naptariHet.HetKezdonapja, naptariHet.HetUtolsoNapja);
|
|
foreach (var napId in napIdList)
|
|
{
|
|
var naptariNap = NaptariNap.GiveAnInstance();
|
|
naptariNap.LoadByID(napId);
|
|
|
|
var dal = new TanevRendjeDAL(null);
|
|
var ds = dal.GetTanevRendjeByDatum(naptariNap.NapDatuma, naptariNap.TanevId, naptariNap.IntezmenyId);
|
|
|
|
foreach (DataRow item in ds.Tables[0].Rows)
|
|
{
|
|
var tanevRendje = TanevRendje.GiveAnInstance();
|
|
tanevRendje.LoadByID(item.Field<int>("TanevrendId"));
|
|
|
|
if (hetirend.HasValue)
|
|
tanevRendje.HetiRend = hetirend.Value;
|
|
else
|
|
tanevRendje.HetiRend = (int)HetiRendTipusEnum.MindegyikHet;
|
|
|
|
tanevRendje.Update();
|
|
}
|
|
}
|
|
}
|
|
|
|
DalHelper.Commit();
|
|
}
|
|
|
|
public List<int> GetNaptariNapIdsIntervallumonBelul(DateTime kezd, DateTime veg)
|
|
{
|
|
var pList = new List<CommandParameter>(); ///TODO (@DevK.): Obj. init-el
|
|
pList.Add(new CommandParameter("pKezd", kezd));
|
|
pList.Add(new CommandParameter("pVeg", veg));
|
|
|
|
const string command = @"
|
|
select ID from T_NAPTARINAP nn
|
|
where
|
|
nn.TOROLT = 'F'
|
|
and nn.C_NAPDATUMA between :pKezd and :pVeg";
|
|
|
|
var ds = this.GetData(command, pList);
|
|
return ds.Tables[0].AsEnumerable().Select(x => x.Field<int>("ID")).ToList();
|
|
}
|
|
|
|
public DataSet GetHetirendHelyettesitesList(int tanevId, Dictionary<int, int?> modifiedHetirendList)
|
|
{
|
|
var parameters = new List<CommandParameter>
|
|
{
|
|
new CommandParameter("pTanevId", tanevId),
|
|
};
|
|
|
|
var commandText = @"
|
|
select
|
|
hi.C_HELYETTESITESNAPJA as Datum
|
|
,fHelyettesito.C_NYOMTATASINEV as Helyettesito
|
|
,fHelyettesitett.C_NYOMTATASINEV as fHelyettesitett
|
|
,ocs.C_NEV as OsztalyCsoport
|
|
,t.C_NEV as Tantargy
|
|
from T_HELYETTESITESIIDOSZAK_OSSZES hi
|
|
join T_ORARENDIORA_OSSZES oo on oo.ID = hi.C_HELYETTESITETTORARENDID
|
|
join T_FELHASZNALO_OSSZES fHelyettesito on fHelyettesito.Id = hi.C_HELYETTESTANAROKID
|
|
join T_FELHASZNALO_OSSZES fHelyettesitett on fHelyettesitett.ID = oo.C_TANARID
|
|
join T_OSZTALYCSOPORT_OSSZES ocs on ocs.ID = oo.C_OSZTALYCSOPORTID
|
|
join T_TANTARGY_OSSZES t on t.ID = oo.C_TANTARGYID
|
|
where
|
|
oo.C_HETIREND != 1554 and hi.TOROLT = 'F' and hi.C_TANEVID = :pTanevId and hi.C_HETSORSZAMA in (
|
|
select nh.C_HETSORSZAMA from T_NAPTARIHET_OSSZES nh where nh.ID in (
|
|
";
|
|
|
|
foreach (var item in modifiedHetirendList)
|
|
{
|
|
commandText += item.Key.ToString() + ",";
|
|
}
|
|
|
|
commandText = commandText.Substring(0, commandText.Length - 1);
|
|
commandText += " ) )";
|
|
|
|
DataSet ds = GetData(commandText, parameters);
|
|
return ds;
|
|
}
|
|
|
|
public int GetNaptariHetSorszamByDate(DateTime datum, int tanevId)
|
|
{
|
|
const string commandText = @"
|
|
SELECT C_HETSORSZAMA AS Sorszam
|
|
FROM T_NAPTARIHET_OSSZES nh
|
|
INNER JOIN T_TANEV_OSSZES t ON t.ID = nh.C_TANEVID AND t.TOROLT = 'F'
|
|
WHERE nh.C_HETKEZDONAPJA <=
|
|
CASE
|
|
WHEN @pDatum < t.C_KEZDONAP THEN t.C_KEZDONAP
|
|
WHEN @pDatum > t.C_UTOLSONAP THEN t.C_UTOLSONAP
|
|
ELSE @pDatum
|
|
END
|
|
AND CASE
|
|
WHEN @pDatum < t.C_KEZDONAP THEN t.C_KEZDONAP
|
|
WHEN @pDatum > t.C_UTOLSONAP THEN t.C_UTOLSONAP
|
|
ELSE @pDatum
|
|
END <= nh.C_HETUTOLSONAPJA
|
|
AND nh.C_TANEVID = :pTanevId
|
|
AND nh.TOROLT = 'F'
|
|
";
|
|
|
|
var paramList = new List<CommandParameter> {
|
|
new CommandParameter("pDatum", datum.Date),
|
|
new CommandParameter("pTanevId", tanevId)
|
|
};
|
|
|
|
var ds = GetData(commandText, parameters: paramList);
|
|
return ds.Tables[0].Rows[0].Field<int>("Sorszam");
|
|
}
|
|
|
|
public int? GetNaptariHetHetirendByDate(DateTime datum, int tanevId)
|
|
{
|
|
var commandText = $@"
|
|
SELECT C_HETIREND AS Hetirend
|
|
FROM T_NAPTARIHET_OSSZES nh
|
|
WHERE
|
|
nh.C_HETKEZDONAPJA <= @{nameof(datum)}
|
|
AND @{nameof(datum)} <= nh.C_HETUTOLSONAPJA
|
|
AND nh.C_TANEVID = @{nameof(tanevId)}
|
|
AND nh.TOROLT = 'F'";
|
|
|
|
var commandParameterList = new List<CommandParameter>
|
|
{
|
|
new CommandParameter(nameof(datum), datum),
|
|
new CommandParameter(nameof(tanevId), tanevId)
|
|
};
|
|
|
|
var ds = GetData(
|
|
commandText: commandText,
|
|
parameters: commandParameterList);
|
|
|
|
return ds.Tables[0].Rows.Count == 0 ? null : ds.Tables[0].Rows[0].Field<int?>("Hetirend");
|
|
}
|
|
public List<int> GetSortedHetirendIdList(int tanevId)
|
|
{
|
|
var hetirendList = HetiRendTipus.LoadWithFilter(string.Format("AND T_HETIRENDTIPUS_OSSZES.ID <> {0} AND T_DICTIONARYITEMBASE_OSSZES.C_TANEVID = {1} AND T_DICTIONARYITEMBASE_OSSZES.TOROLT = 'F' AND T_DICTIONARYITEMBASE_OSSZES.C_VISIBLE = 'T' ", (int)HetiRendTipusEnum.MindegyikHet, tanevId));
|
|
return hetirendList.OrderBy(x => x.Value).Select(x => x.ID).Distinct().ToList();
|
|
}
|
|
|
|
public void DeleteHetesek(int tanevId, List<int> szunetIds, int modifierUserId)
|
|
{
|
|
if (szunetIds != null && szunetIds.Any())
|
|
{
|
|
using (var command = new SDACommand())
|
|
{
|
|
command.Connection = UserContext.Instance.SDAConnection;
|
|
command.Transaction = UserContext.Instance.SDATransaction;
|
|
|
|
var commandText = @"
|
|
UPDATE h
|
|
SET
|
|
h.TOROLT = 'T',
|
|
h.LASTCHANGED = GETDATE(),
|
|
h.SERIAL = h.SERIAL + 1,
|
|
h.MODIFIER = :pUserId
|
|
FROM T_HETES_OSSZES h
|
|
INNER JOIN T_NAPTARIHET_OSSZES nh ON nh.C_HETSORSZAMA = h.C_HETSORSZAMA AND nh.C_TANEVID = h.C_TANEVID AND nh.TOROLT = 'F'
|
|
WHERE h.TOROLT = 'F'
|
|
AND h.C_TANEVID = :pTanevId
|
|
";
|
|
|
|
commandText += $" AND nh.ID IN ({string.Join(",", szunetIds)})";
|
|
|
|
command.CommandText = commandText;
|
|
command.Parameters.Add("pTanevId", tanevId);
|
|
command.Parameters.Add("pUserId", modifierUserId);
|
|
|
|
command.ExecuteNonQuery();
|
|
}
|
|
}
|
|
}
|
|
|
|
public void DeleteHelyettesites(int tanevId, Dictionary<int, int?> modifiedHetirendList)
|
|
{
|
|
var commandText = @"
|
|
update hi
|
|
set TOROLT = 'T'
|
|
from T_HELYETTESITESIIDOSZAK_OSSZES hi
|
|
join T_ORARENDIORA_OSSZES oo on oo.ID = hi.C_HELYETTESITETTORARENDID
|
|
where
|
|
oo.C_HETIREND != 1554 and hi.TOROLT = 'F' and hi.C_TANEVID = :pTanevId and hi.C_HETSORSZAMA in (
|
|
select nh.C_HETSORSZAMA from T_NAPTARIHET_OSSZES nh where nh.ID in (
|
|
";
|
|
|
|
foreach (var item in modifiedHetirendList)
|
|
{
|
|
commandText += item.Key.ToString() + ",";
|
|
}
|
|
|
|
commandText = commandText.Substring(0, commandText.Length - 1);
|
|
commandText += " ) )";
|
|
|
|
using (var command = new SDACommand())
|
|
{
|
|
command.Connection = UserContext.Instance.SDAConnection;
|
|
command.Transaction = UserContext.Instance.SDATransaction;
|
|
command.CommandText = commandText;
|
|
command.Parameters.Add("pTanevId", tanevId);
|
|
|
|
command.ExecuteNonQuery();
|
|
}
|
|
}
|
|
}
|
|
|
|
public class NaptariHetAndOrarendOverlapFilter
|
|
{
|
|
public DateTime OrarendElemKezdoNapDatuma { get; set; }
|
|
|
|
public DateTime OrarendElemVegNapDatuma { get; set; }
|
|
}
|
|
}
|