albert/kreta
1
0
Fork 0
Code Issues Pull requests Projects Releases Packages Wiki Activity
kreta/Kreta.DataAccessManual/FelhasznaloDAL.cs
skidoodle e124a47765 init
2024-03-13 00:33:46 +01:00

1951 lines
90 KiB
C#
Raw Permalink Blame History

using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using Kreta.Core;
using Kreta.Core.Domain;
using Kreta.Core.Logic;
using Kreta.DataAccess.Interfaces;
using Kreta.DataAccessManual.Interfaces;
using Kreta.DataAccessManual.ParameterClasses;
using Kreta.DataAccessManual.Util;
using Kreta.Enums;
using Kreta.Enums.ManualEnums;
using Kreta.Framework;
using Kreta.Framework.Util;
using Kreta.Resources;
using SDA.DataProvider;
using SDA.Kreta.Entities;
namespace Kreta.DataAccessManual
{
internal class FelhasznaloDal : DataAccessBase, IFelhasznaloDal
{
public FelhasznaloDal(DalHandler handler, GridParameters gridParameters) : base(handler, gridParameters)
{
}
public string GetUserProfile()
{
//performance optimized query
string profile = "";
using (SDA.DataProvider.SDACommand command = new SDA.DataProvider.SDACommand())
{
command.Connection = UserContext.Instance.SDAConnection;
command.Transaction = UserContext.Instance.SDATransaction;
command.CommandText = "select c_profile from t_userprofile where c_felhasznaloid = :pUSERID and TOROLT = 'F'";
command.Parameters.Add("pUSERID", SDA.DataProvider.SDADBType.Int).Value = UserContext.Instance.FelhasznaloId;
using (SDA.DataProvider.SDADataReader reader = command.ExecuteReader())
{
if (reader.Read())
{
profile = System.Web.HttpUtility.HtmlDecode(reader.GetString(0, ""));
}
}
}
return profile;
}
public string GetUserProfile(int userId)
{
string profile = "";
using (SDACommand command = new SDACommand())
{
command.Connection = UserContext.Instance.SDAConnection;
command.Transaction = UserContext.Instance.SDATransaction;
command.CommandText = "select c_profile from t_userprofile where c_felhasznaloid = :pUSERID and TOROLT = 'F'";
command.Parameters.Add("pUSERID", SDADBType.Int).Value = userId;
using (SDADataReader reader = command.ExecuteReader())
{
if (reader.Read())
{
profile = System.Web.HttpUtility.HtmlDecode(reader.GetString(0, ""));
}
}
}
return profile;
}
public int GetUserDefaultLCID(int userId)
{
using (SDA.DataProvider.SDACommand cmd = new SDA.DataProvider.SDACommand())
{
cmd.Connection = UserContext.Instance.SDAConnection;
cmd.Transaction = UserContext.Instance.SDATransaction;
cmd.CommandText = @"select C_ALAPERTELMEZETTLCID from t_felhasznalo where id = :pUserID and TOROLT = 'F'";
cmd.Parameters.Add("pUserID", userId);
object data = cmd.ExecuteScalar();
return data != DBNull.Value ? Convert.ToInt32(data) : 0;
}
}
public DataSet GetSajatAdatlapCim(int userId, bool isGondviselo)
{
var felhasznalo = isGondviselo ? "And [C_GONDVISELOID] = :pUserId And [C_GONDVISELOID] is not null" : " And [C_FELHASZNALOID] = :pUserId And [C_GONDVISELOID] is null";
var commandText = ($@"
SELECT [ID]
,[C_CIMTIPUSA] as CimTipus
,[C_IRANYITOSZAM] as Irsz
,[C_VAROS] as Varos
,[C_KOZTERULET] as Kozterulet
,[C_KOZTERULETJELLEGENEV] as KozteruletJellegeNev
,[C_HAZSZAM] as HazSzam
,[C_EPULET] as Epulet
,[C_LEPCSOHAZ] as LepcsoHaz
,[C_EMELET] as Emelet
,[C_AJTO] as Ajto
,[C_ALAPERTELMEZETT] as Alapertelmezett
FROM [T_CIM_OSSZES] where [TOROLT] ='F'
{felhasznalo}
");
var prams = new List<CommandParameter> { new CommandParameter("pUserId", userId) };
var ds = this.GetData(commandText, prams, "CimTipus", "Alapertelmezett");
return ds;
}
public DataSet GetTanuloAdatForGondviselo(int userId)
{
const string commandText = @"
SELECT
t.ID
,f.C_NYOMTATASINEV AS TanuloNev
,f.C_OKTATASIAZONOSITO AS OktAzon
,ocs.C_NEV AS OsztalyNev
,ofo.C_NYOMTATASINEV AS OsztalyfonokNev
,terem.C_NEV AS TeremNev
FROM T_GONDVISELO_OSSZES g
INNER JOIN T_TANULO t ON t.ID = g.C_TANULOID
INNER JOIN T_FELHASZNALO f ON f.ID = t.ID
INNER JOIN T_TANULOCSOPORT tcs ON tcs.C_TANULOID = t.ID
INNER JOIN T_OSZTALYCSOPORT ocs ON ocs.ID = tcs.C_OSZTALYCSOPORTID
INNER JOIN T_OSZTALY o ON o.ID = ocs.ID
LEFT JOIN T_FELHASZNALO ofo ON ofo.ID = o.C_OSZTALYFONOKID
LEFT JOIN T_TEREM terem ON terem.ID = ocs.C_TEREMID
WHERE g.ID = :pUserId";
var prams = new List<CommandParameter> { new CommandParameter("pUserId", userId) };
var ds = this.GetData(commandText, prams);
return ds;
}
public DataSet GetUserCim(int userId)
{
const string commandText = @"
SELECT
cim.[ID] AS ID
,[C_ORSZAG] AS Orszag
,[C_CIMTIPUSA] AS CimTipus
,[C_IRANYITOSZAM] AS Irsz
,[C_VAROS] AS Varos
,[C_KOZTERULET] AS Kozterulet
,[C_KOZTERULETJELLEGENEV] AS KozteruletJellegeNev
,[C_HAZSZAM] AS HazSzam
,[C_EPULET] AS Epulet
,[C_LEPCSOHAZ] AS LepcsoHaz
,[C_EMELET] AS Emelet
,[C_AJTO] AS Ajto
,[C_ALAPERTELMEZETT] AS Alapertelmezett
,[C_ISKOZPONTILAGSZINKRONIZALT] AS IsKozpontilagSzinkronizalt
FROM [T_CIM_OSSZES] cim
LEFT JOIN T_ALKALMAZOTT_OSSZES alkalmazott ON cim.C_FELHASZNALOID = alkalmazott.ID AND alkalmazott.TOROLT = 'F'
WHERE cim.[TOROLT] ='F' AND [C_FELHASZNALOID] = :pUserId AND [C_GONDVISELOID] IS NULL
";
var prams = new List<CommandParameter> { new CommandParameter("pUserId", userId) };
var ds = this.GetData(commandText, prams, "Orszag,CimTipus", "Alapertelmezett,IsKozpontilagSzinkronizalt");
return ds;
}
public DataSet GetUserEmailCim(int userId)
{
const string commandText = @"
Select e.[ID]
,e.[C_EMAILTIPUSA] as Tipus
,e.[C_EMAILCIM] as EmailCim
,e.[C_ALAPERTELMEZETT] as Alapertelmezett
,e.[C_ISHIBASANMEGADVA] as IsHibasanMegadva
From(
SELECT [ID]
FROM [T_FELHASZNALO_OSSZES] where [TOROLT] = 'F' And ID = :pUserId
) as f
/*Left Join (SELECT [ID]*/
Join (SELECT [ID]
,[C_EMAILTIPUSA]
,[C_EMAILCIM]
,[C_ALAPERTELMEZETT]
,[C_FELHASZNALOID]
,[C_ISHIBASANMEGADVA]
FROM [T_EMAIL_OSSZES] where [TOROLT] = 'F' And [C_FELHASZNALOID] is not null And [C_GONDVISELOID] is null) as e
On e.C_FELHASZNALOID = f.ID
";
var prams = new List<CommandParameter> { new CommandParameter("pUserId", userId) };
var ds = this.GetData(commandText, prams, "Tipus", "Alapertelmezett,IsHibasanMegadva");
return ds;
}
public DataSet GetUserTelefon(int userId)
{
const string commandText = @"
Select t.[ID]
,t.[C_TELEFONTIPUSA] as Tipus
,t.[C_ALAPERTELMEZETT] as Alapertelmezett
,t.[C_TELEFONSZAM] as TelefonSzam
From(
SELECT [ID]
FROM [T_FELHASZNALO_OSSZES] where [TOROLT] = 'F' And ID = :pUserId
) as f
/*Left Join (SELECT [ID]*/
Join (SELECT [ID]
,[C_TELEFONTIPUSA]
,[C_TELEFONSZAM]
,[C_ALAPERTELMEZETT]
,[C_FELHASZNALOID]
FROM [T_TELEFON_OSSZES] where [TOROLT] = 'F' And [C_FELHASZNALOID] is not null And [C_GONDVISELOID] is null) as t
On t.C_FELHASZNALOID = f.ID
";
var prams = new List<CommandParameter> { new CommandParameter("pUserId", userId) };
var ds = this.GetData(commandText, prams, "Tipus", "Alapertelmezett");
return ds;
}
public DataSet GetDatabases()
{
string commandText = @"SELECT name [DatabaseName] FROM sys.databases WHERE name LIKE 'Kreta_%' ORDER BY name";
DataSet ds = GetData(commandText);
return ds;
}
public DataSet Check4TFelhOktAzonValidation(List<int> tanevIds, int pCheckValidationType,
string pSzuletesiNevSorrend, string pSzuletesiNevElotag, string pSzuletesiCsaladiNev, string pSzuletesiUtonev,
string pAnyjaNevSorrend, string pAnyjaElotag, string pAnyjaCsaladiNev, string pAnyjaUtonev,
string pSzuletesiHely, DateTime? pSzuletesiDatum, string pOktatasiAzonosito, string pBejelentkezesiNev,
int? pFelvetelStatuszaFelveve = null, int? pFelvetelStatuszaNemFelveve = null)
{
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 = "uspCheck4TFelhOktAzonValidation";
command.Parameters.Add("pCheckValidationType", pCheckValidationType);
if (string.IsNullOrWhiteSpace(pSzuletesiNevSorrend)) { command.Parameters.Add("pSzuletesiNevSorrend", DBNull.Value); }
else { command.Parameters.Add("pSzuletesiNevSorrend", pSzuletesiNevSorrend); }
if (string.IsNullOrWhiteSpace(pSzuletesiNevElotag)) { command.Parameters.Add("pSzuletesiElotag", DBNull.Value); }
else { command.Parameters.Add("pSzuletesiElotag", pSzuletesiNevElotag); }
if (string.IsNullOrWhiteSpace(pSzuletesiCsaladiNev)) { command.Parameters.Add("pSzuletesiVezeteknev", DBNull.Value); }
else { command.Parameters.Add("pSzuletesiVezeteknev", pSzuletesiCsaladiNev); }
if (string.IsNullOrWhiteSpace(pSzuletesiUtonev)) { command.Parameters.Add("pSzuletesiUtonev", DBNull.Value); }
else { command.Parameters.Add("pSzuletesiUtonev", pSzuletesiUtonev); }
if (string.IsNullOrWhiteSpace(pAnyjaNevSorrend)) { command.Parameters.Add("pAnyjaNevSorrend", DBNull.Value); }
else { command.Parameters.Add("pAnyjaNevSorrend", pAnyjaNevSorrend); }
if (string.IsNullOrWhiteSpace(pAnyjaElotag)) { command.Parameters.Add("pAnyjaNevElotag", DBNull.Value); }
else { command.Parameters.Add("pAnyjaNevElotag", pAnyjaElotag); }
if (string.IsNullOrWhiteSpace(pAnyjaCsaladiNev)) { command.Parameters.Add("pAnyjaVezeteknev", DBNull.Value); }
else { command.Parameters.Add("pAnyjaVezeteknev", pAnyjaCsaladiNev); }
if (string.IsNullOrWhiteSpace(pAnyjaUtonev)) { command.Parameters.Add("pAnyjaUtonev", DBNull.Value); }
else { command.Parameters.Add("pAnyjaUtonev", pAnyjaUtonev); }
if (string.IsNullOrWhiteSpace(pSzuletesiHely)) { command.Parameters.Add("pSzuletesiHely", DBNull.Value); }
else { command.Parameters.Add("pSzuletesiHely", pSzuletesiHely); }
if (!pSzuletesiDatum.HasValue) { command.Parameters.Add("pSzuletesiDatum", DBNull.Value); }
else { command.Parameters.Add("pSzuletesiDatum", pSzuletesiDatum.Value); }
command.Parameters.Add("pTanevIds", string.Join(",", tanevIds));
if (!pFelvetelStatuszaFelveve.HasValue) { command.Parameters.Add("pFelvetelStatuszaFelveve", DBNull.Value); }
else { command.Parameters.Add("pFelvetelStatuszaFelveve", pFelvetelStatuszaFelveve); }
if (!pFelvetelStatuszaNemFelveve.HasValue) { command.Parameters.Add("pFelvetelStatuszaNemFelveve", DBNull.Value); }
else { command.Parameters.Add("pFelvetelStatuszaNemFelveve", pFelvetelStatuszaNemFelveve); }
if (string.IsNullOrWhiteSpace(pOktatasiAzonosito)) { command.Parameters.Add("pOktatasiAzonosito", DBNull.Value); }
else { command.Parameters.Add("pOktatasiAzonosito", pOktatasiAzonosito); }
if (string.IsNullOrWhiteSpace(pBejelentkezesiNev)) { command.Parameters.Add("pBejelentkezesiNev", DBNull.Value); }
else { command.Parameters.Add("pBejelentkezesiNev", pBejelentkezesiNev); }
using (var adapter = new SDADataAdapter())
{
adapter.SelectCommand = command;
adapter.Fill(ds);
}
}
return ds;
}
public void OktAzon4TAlapjanFollowUp(int validationType, int entityId, int anotherTanevEntityId)
{
using (SDA.DataProvider.SDACommand command = UserContext.Instance.SDAConnection.CreateCommand())
{
command.CommandType = CommandType.StoredProcedure;
command.Connection = UserContext.Instance.SDAConnection;
command.Transaction = UserContext.Instance.SDATransaction;
command.CommandText = "uspOktAzon4TAlapjanFollowUp";
command.Parameters.Add("pCheckValidationType", validationType);
command.Parameters.Add("pEntityId", entityId);
command.Parameters.Add("pAnotherTanevEntityId", anotherTanevEntityId);
command.ExecuteNonQuery();
DalHelper.Commit();
}
}
public int? GetAktualisFelhasznaloBelepesID(string bejelentkezesiNev)
{
using (var command = UserContext.Instance.SDAConnection.CreateCommand())
{
command.Transaction = UserContext.Instance.SDATransaction;
command.CommandText = @"
SELECT
ID
FROM
T_FELHASZNALOBELEPES
WHERE
UPPER(C_BEJELENTKEZESINEV) = UPPER(:pBejelentkezesiNev)
AND TOROLT = 'F'
AND EXISTS (SELECT 1 FROM T_TANEV WHERE TOROLT = 'F' AND C_AKTIV = 'T' AND T_TANEV.ID = C_TANEVID)";
command.Parameters.Add("pBejelentkezesiNev", bejelentkezesiNev);
var result = command.ExecuteScalar();
if (result != DBNull.Value && result != null)
{
return (int)result;
}
return null;
}
}
public string GetBejelentkezesiNev(Guid idpUniqueId)
{
using (var command = UserContext.Instance.SDAConnection.CreateCommand())
{
command.Transaction = UserContext.Instance.SDATransaction;
command.CommandText = @"
SELECT FelhasznaloBelepes.C_BEJELENTKEZESINEV
FROM T_FELHASZNALOBELEPES FelhasznaloBelepes
INNER JOIN T_FELHASZNALO Felhasznalo ON Felhasznalo.ID = FelhasznaloBelepes.C_FELHASZNALOID
AND Felhasznalo.TOROLT = 'F'
AND Felhasznalo.C_IDPEGYEDIAZONOSITO = :pIdpUniqueId
WHERE FelhasznaloBelepes.TOROLT = 'F'
AND FelhasznaloBelepes.C_GONDVISELOID IS NULL
AND EXISTS (SELECT 1
FROM T_TANEV Tanev
WHERE Tanev.TOROLT = 'F'
AND Tanev.C_AKTIV = 'T'
AND Tanev.ID = FelhasznaloBelepes.C_TANEVID)
UNION ALL
SELECT FelhasznaloBelepes.C_BEJELENTKEZESINEV
FROM T_FELHASZNALOBELEPES FelhasznaloBelepes
INNER JOIN T_GONDVISELO Gondviselo ON Gondviselo.ID = FelhasznaloBelepes.C_GONDVISELOID
AND Gondviselo.TOROLT = 'F'
AND Gondviselo.C_IDPEGYEDIAZONOSITO = :pIdpUniqueId
WHERE FelhasznaloBelepes.TOROLT = 'F'
AND EXISTS (SELECT 1
FROM T_TANEV Tanev
WHERE Tanev.TOROLT = 'F'
AND Tanev.C_AKTIV = 'T'
AND Tanev.ID = FelhasznaloBelepes.C_TANEVID)";
command.Parameters.Add("pIdpUniqueId", idpUniqueId);
string result = command.ExecuteScalar()?.ToString();
return result;
}
}
public int? GetKovFelhasznaloBelepesID(string bejelentkezesiNev)
{
using (var command = UserContext.Instance.SDAConnection.CreateCommand())
{
command.Transaction = UserContext.Instance.SDATransaction;
command.CommandText = @"
SELECT TOP 1
ID
FROM
T_FELHASZNALOBELEPES_OSSZES fb
WHERE
UPPER(fb.C_BEJELENTKEZESINEV) = UPPER(:pBejelentkezesiNev)
AND fb.TOROLT = 'F'
AND EXISTS (
SELECT 1 FROM T_TANEV_OSSZES t
WHERE
t.TOROLT = 'F' AND t.C_AKTIV = 'F' AND t.ID = fb.C_TANEVID AND
t.C_SORSZAM = (SELECT C_SORSZAM + 2 FROM T_TANEV where C_AKTIV = 'T' and TOROLT = 'F')
)
";
command.Parameters.Add("pBejelentkezesiNev", bejelentkezesiNev);
var result = command.ExecuteScalar();
if (result != DBNull.Value && result != null)
{
return (int)result;
}
return null;
}
}
public string GetAktualisFelhasznaloBelepesJelszo(string bejelentkezesiNev)
{
using (var command = UserContext.Instance.SDAConnection.CreateCommand())
{
command.Transaction = UserContext.Instance.SDATransaction;
command.CommandText = @"
SELECT C_JELSZO FROM T_FELHASZNALOBELEPES
WHERE UPPER(C_BEJELENTKEZESINEV) = UPPER(:pBejelentkezesiNev)
AND TOROLT='F'
AND EXISTS (SELECT 1 FROM T_TANEV WHERE TOROLT='F' AND C_AKTIV='T' AND T_TANEV.ID = C_TANEVID)
";
command.Parameters.Add("pBejelentkezesiNev", bejelentkezesiNev);
var result = command.ExecuteScalar();
if (result != DBNull.Value)
return (string)result;
return string.Empty;
}
}
public void UpdateProfile(int felhasznaloId, string profileXml)
{
var entity = Felhasznalo.GiveAnInstance();
entity.LoadByID(felhasznaloId);
UpdateProfile(entity, profileXml);
}
public void UpdateProfile(IFelhasznalo entity, string profileXml)
{
var entityFelhasznalo = (Felhasznalo)entity;
var userProfile = entityFelhasznalo.UserProfile.SingleOrDefault();
if (userProfile == null)
{
userProfile = UserProfile.GiveAnInstance();
userProfile.FelhasznaloId = entity.ID;
userProfile.Profile = profileXml;
userProfile.OsszFeltolthetoFajlKb = Constants.General.MaxFeltolthetoAdatmennyisegInKByte;
userProfile.TanevId = entity.TanevId;
userProfile.Insert();
}
else
{
userProfile.Profile = profileXml;
userProfile.Update();
}
DalHelper.Commit();
}
public DataSet CheckOktatasiAzonositoFelhasznaloBelepesUtkozes(List<int> tanevIds, string oktatasiAzonosito, string bejelentkezesiNev, int? felhasznaloId = null, bool isTanar = false)
{
var parameters = new List<CommandParameter>();
var felhasznaloJoin = isTanar ? "INNER JOIN T_ALKALMAZOTT_OSSZES a ON a.ID = f.ID AND a.TOROLT = 'F'" : "INNER JOIN T_TANULO_OSSZES t ON t.ID = f.ID AND t.TOROLT = 'F'";
var commandText = $@"
SELECT fb.C_BEJELENTKEZESINEV, fb.C_GONDVISELOID, f.*
FROM T_FELHASZNALO_OSSZES f
LEFT JOIN T_FELHASZNALOBELEPES_OSSZES fb on fb.C_FELHASZNALOID = f.ID AND f.TOROLT = 'F'
{felhasznaloJoin}
WHERE f.TOROLT = 'F'
AND f.C_TANEVID IN (" + string.Join(",", tanevIds) + @")
AND (fb.C_BEJELENTKEZESINEV = :pBejelentkezesiNev OR f.C_OKTATASIAZONOSITO = :pOktatasiAzonosito)
AND (:pFelhasznaloId IS NULL OR :pFelhasznaloId != f.ID)
";
if (!string.IsNullOrWhiteSpace(oktatasiAzonosito))
{
parameters.Add(new CommandParameter("pOktatasiAzonosito", oktatasiAzonosito));
}
else
{
parameters.Add(new CommandParameter("pOktatasiAzonosito", DBNull.Value));
}
if (!string.IsNullOrWhiteSpace(bejelentkezesiNev))
{
parameters.Add(new CommandParameter("pBejelentkezesiNev", bejelentkezesiNev));
}
else
{
parameters.Add(new CommandParameter("pBejelentkezesiNev", DBNull.Value));
}
if (felhasznaloId.HasValue)
{
parameters.Add(new CommandParameter("pFelhasznaloId", felhasznaloId));
}
else
{
parameters.Add(new CommandParameter("pFelhasznaloId", DBNull.Value));
}
var ds = GetData(commandText, parameters);
return ds;
}
public void Delete(int id)
{
var entity = Felhasznalo.GiveAnInstance();
entity.LoadByID(id);
entity.FelhasznaloBelepes.DeleteAll();
entity.Cim.DeleteAll();
entity.Delete();
DalHelper.Commit();
}
public void FullUpdate(IFelhasznalo dto)
{
var entity = dto as Felhasznalo;
entity.FullUpdate();
DalHelper.Commit();
}
public void Update(IFelhasznalo dto)
{
var entity = dto as Felhasznalo;
entity.Update();
DalHelper.Commit();
}
public IFelhasznalo Get()
{
return Felhasznalo.GiveAnInstance();
}
public IFelhasznalo Get(int id)
{
var entity = Felhasznalo.GiveAnInstance();
entity.LoadByID(id);
return entity;
}
public void Insert(IFelhasznalo dto)
{
var entity = dto as Felhasznalo;
entity.Insert();
dto.ID = entity.ID;
DalHelper.Felhasznalo().UpdateAllEgyediAzonosito();
DalHelper.Commit();
}
public void Update(IFelhasznalo dto, bool handler)
{
var entity = dto as Felhasznalo;
entity.Update(handler);
DalHelper.Commit();
}
public FelhasznaloDal(DalHandler handler) : base(handler)
{
}
public void HozzaferesGeneralas(int intezmenyId, int aktivTanevID, int userId, string belepesiAdatokXML)
{
using (SDA.DataProvider.SDACommand command = UserContext.Instance.SDAConnection.CreateCommand())
{
command.CommandType = CommandType.StoredProcedure;
command.Connection = UserContext.Instance.SDAConnection;
command.Transaction = UserContext.Instance.SDATransaction;
command.CommandText = "sp_HozzaferesGeneralas";
command.Parameters.Add("xml", belepesiAdatokXML);
command.Parameters.Add("tanevID", aktivTanevID);
command.Parameters.Add("intezmenyID", intezmenyId);
command.Parameters.Add("userId", userId);
command.ExecuteNonQuery();
DalHelper.Commit();
}
}
public void JelszoFrissites(int intezmenyId, int aktivTanevID, int userId, string belepesiAdatokXML, bool isTanulo, int modositoUserId)
{
using (SDA.DataProvider.SDACommand command = UserContext.Instance.SDAConnection.CreateCommand())
{
command.CommandType = CommandType.StoredProcedure;
command.Connection = UserContext.Instance.SDAConnection;
command.Transaction = UserContext.Instance.SDATransaction;
if (isTanulo)
command.CommandText = "sp_JelszoResetTanulo";
else
command.CommandText = "sp_JelszoResetGondviselo";
command.Parameters.Add("xml", belepesiAdatokXML);
command.Parameters.Add("tanevID", aktivTanevID);
command.Parameters.Add("intezmenyID", intezmenyId);
command.Parameters.Add("userId", userId);
command.Parameters.Add("modositoUserId", modositoUserId);
command.ExecuteNonQuery();
DalHelper.Commit();
}
}
public bool CheckFelhasznaloHasSzerepkor(int felhasznaloId, int szerepkorTipusId)
{
bool result;
using (SDACommand command = new SDACommand())
{
command.Connection = UserContext.Instance.SDAConnection;
command.Transaction = UserContext.Instance.SDATransaction;
command.CommandType = CommandType.StoredProcedure;
command.CommandText = "sp_CheckFelhasznaloHasSzerepkor";
command.Parameters.Add("pFelhasznaloId", felhasznaloId);
command.Parameters.Add("pSzerepkorTipusId", szerepkorTipusId);
result = Convert.ToBoolean(command.ExecuteScalar());
}
return result;
}
public int GetFelhasznaloIdByUserName(string userName, int intezmenyId, int tanevId)
{
using (var command = UserContext.Instance.SDAConnection.CreateCommand())
{
command.Transaction = UserContext.Instance.SDATransaction;
command.CommandText = @"
SELECT C_FELHASZNALOID FROM T_FELHASZNALOBELEPES
WHERE
UPPER(C_BEJELENTKEZESINEV) = UPPER(:pUserName)
AND TOROLT='F'
AND C_INTEZMENYID = :pIntezmenyId
AND C_TANEVID = :pTanevId
";
command.Parameters.Add("pUserName", userName);
command.Parameters.Add("pIntezmenyId", intezmenyId);
command.Parameters.Add("pTanevId", tanevId);
var result = command.ExecuteScalar();
return (int)result;
}
}
public DataSet GetAlkalmazottakByOktatasiAzonosito(string oktatasiAzonosito, int tanevId)
{
var parameters = new List<CommandParameter>
{
new CommandParameter("pOktatasiAzonosito", oktatasiAzonosito),
new CommandParameter("pTanevId", tanevId)
};
const string command = @"
SELECT
f.C_NYOMTATASINEV AS Nev
FROM T_FELHASZNALO_OSSZES f
INNER JOIN T_ALKALMAZOTT_OSSZES a ON a.ID = f.ID AND a.TOROLT = 'F'
WHERE f.TOROLT = 'F' AND f.C_TANEVID = :pTanevId AND f.C_OKTATASIAZONOSITO = :pOktatasiAzonosito
";
return GetData(command, parameters);
}
public DataSet GetTanulokByOktatasiAzonosito(string oktatasiAzonosito, int tanevId)
{
var parameters = new List<CommandParameter>
{
new CommandParameter("pOktatasiAzonosito", oktatasiAzonosito),
new CommandParameter("pTanevId", tanevId)
};
const string command = @"
SELECT
f.C_NYOMTATASINEV AS Nev
FROM T_FELHASZNALO_OSSZES f
INNER JOIN T_TANULO_OSSZES t ON t.ID = f.ID AND t.TOROLT = 'F'
WHERE f.TOROLT = 'F' AND f.C_TANEVID = :pTanevId AND f.C_OKTATASIAZONOSITO = :pOktatasiAzonosito
";
return GetData(command, parameters);
}
public string GetFelhasznaloOktatasiAzonosito(int id, int tanevId)
{
using (var command = UserContext.Instance.SDAConnection.CreateCommand())
{
command.Transaction = UserContext.Instance.SDATransaction;
command.CommandText = @"
SELECT
C_OKTATASIAZONOSITO
FROM T_FELHASZNALO_OSSZES
WHERE TOROLT = 'F' AND C_TANEVID = :pTanevId AND ID = :pId
";
command.Parameters.Add("pId", id);
command.Parameters.Add("pTanevId", tanevId);
var result = command.ExecuteScalar();
return result.ToString();
}
}
public string GetGondviseloFelhasznaloBelepesiNeve(int userId, int intezmenyId, int tanevId)
{
using (var command = UserContext.Instance.SDAConnection.CreateCommand())
{
command.Transaction = UserContext.Instance.SDATransaction;
command.CommandText = @"
SELECT fb.C_BEJELENTKEZESINEV FROM T_GONDVISELO g
JOIN T_FELHASZNALOBELEPES fb ON fb.C_GONDVISELOID = g.ID
WHERE g.ID = :pUserId
AND fb.C_INTEZMENYID = :pIntezmenyId
AND fb.C_TANEVID = :pTanevId
AND fb.TOROLT = 'F'
AND g.TOROLT = 'F'
";
command.Parameters.Add("pUserId", userId);
command.Parameters.Add("pIntezmenyId", intezmenyId);
command.Parameters.Add("pTanevId", tanevId);
var result = command.ExecuteScalar();
return (string)result;
}
}
public bool IsCsokkentettGondviselo(int gondviseloId, int intezmenyId, int tanevId)
{
using (var command = UserContext.Instance.SDAConnection.CreateCommand())
{
command.Transaction = UserContext.Instance.SDATransaction;
command.CommandText = @"
SELECT g.C_ISCSOKKENTETTGONDVISELO
FROM T_GONDVISELO g
WHERE g.ID = :pUserId
AND g.C_INTEZMENYID = :pIntezmenyId
AND g.C_TANEVID = :pTanevId
AND g.TOROLT = 'F'
";
command.Parameters.Add("pUserId", gondviseloId);
command.Parameters.Add("pIntezmenyId", intezmenyId);
command.Parameters.Add("pTanevId", tanevId);
var result = command.ExecuteScalar();
return result.ToString() == "T";
}
}
public string GetTanuloFelhasznaloBelepesiNeve(int userId, int intezmenyId, int tanevId)
{
using (var command = UserContext.Instance.SDAConnection.CreateCommand())
{
command.Transaction = UserContext.Instance.SDATransaction;
command.CommandText = @"
SELECT fb.C_BEJELENTKEZESINEV
FROM T_FELHASZNALOBELEPES fb
WHERE fb.C_FELHASZNALOID = :pUserId AND C_GONDVISELOID IS NULL
AND fb.C_INTEZMENYID = :pIntezmenyId
AND fb.C_TANEVID = :pTanevId
AND fb.TOROLT='F'
";
command.Parameters.Add("pUserId", userId);
command.Parameters.Add("pIntezmenyId", intezmenyId);
command.Parameters.Add("pTanevId", tanevId);
var result = command.ExecuteScalar();
return (string)result;
}
}
public DateTime GetTanuloFelhasznaloSzuletesiDatum(int userId, int intezmenyId, int tanevId)
{
using (var command = UserContext.Instance.SDAConnection.CreateCommand())
{
command.Transaction = UserContext.Instance.SDATransaction;
command.CommandText = @"
SELECT felhasznalo.C_SZULETESIDATUM as SzuletesiDatum
FROM T_FELHASZNALO AS felhasznalo
LEFT OUTER JOIN T_FELHASZNALOBELEPES as felhasznaloBelepes ON felhasznaloBelepes.C_FELHASZNALOID = felhasznalo.ID
WHERE felhasznalo.ID = :pUserId
AND felhasznalo.TOROLT='F'
AND felhasznaloBelepes.TOROLT='F'
AND felhasznaloBelepes.C_INTEZMENYID = :pIntezmenyId
AND felhasznaloBelepes.C_TANEVID = :pTanevId
";
command.Parameters.Add("pUserId", userId);
command.Parameters.Add("pIntezmenyId", intezmenyId);
command.Parameters.Add("pTanevId", tanevId);
return Convert.ToDateTime(command.ExecuteScalar());
}
}
public DateTime GetTanuloSzuletesiDatumByGondviseloId(int userId, int intezmenyId, int tanevId)
{
using (var command = UserContext.Instance.SDAConnection.CreateCommand())
{
command.Transaction = UserContext.Instance.SDATransaction;
command.CommandText = @"
SELECT felhasznalo.C_SZULETESIDATUM as SzuletesiDatum
FROM T_FELHASZNALO AS felhasznalo
LEFT OUTER JOIN T_FELHASZNALOBELEPES as felhasznaloBelepes ON felhasznaloBelepes.C_FELHASZNALOID = felhasznalo.ID
WHERE felhasznaloBelepes.C_GONDVISELOID = :pUserId
AND felhasznalo.TOROLT='F'
AND felhasznaloBelepes.TOROLT='F'
AND felhasznaloBelepes.C_INTEZMENYID = :pIntezmenyId
AND felhasznaloBelepes.C_TANEVID = :pTanevId
";
command.Parameters.Add("pUserId", userId);
command.Parameters.Add("pIntezmenyId", intezmenyId);
command.Parameters.Add("pTanevId", tanevId);
return Convert.ToDateTime(command.ExecuteScalar());
}
}
public bool GetVegzosEvfolyamTanulojaByUserId(int userId)
{
using (var command = UserContext.Instance.SDAConnection.CreateCommand())
{
command.Transaction = UserContext.Instance.SDATransaction;
command.CommandText = @"
SELECT vegzoso.ID
FROM(
SELECT [ID]
,[C_OSZTALYCSOPORTID]
,[C_TANULOID]
FROM [T_TANULOCSOPORT]
WHERE [TOROLT] = 'F'
AND C_BELEPESDATUM < GETDATE()
AND (C_KILEPESDATUM IS NULL OR C_KILEPESDATUM >= GETDATE())
AND C_TANULOID = :pUserId
) tcs
INNER JOIN (
SELECT o.[ID] FROM [T_OSZTALY] o
INNER JOIN [T_OSZTALYCSOPORT] ocs ON ocs.ID = o.ID
AND ocs.C_FELADATKATEGORIAID = @OktNevKatTipus
WHERE o.[TOROLT] = 'F' AND ocs.C_VEGZOSEVFOLYAM='T' AND ocs.TOROLT = 'F'
) AS vegzoso
ON tcs.C_OSZTALYCSOPORTID = vegzoso.ID";
command.Parameters.Add("pUserId", userId);
command.Parameters.Add("OktNevKatTipus", (int)OktNevelesiKategoriaEnum.NevelesOktatas);
object id = command.ExecuteScalar();
return id != null;
}
}
public DataSet GetFelhasznalok(int intezmenyId, int tanevId, FelhasznaloKeresesPCO pco, bool isHRModulEngedelyezett, bool isLepEngedelyezett)
{
List<CommandParameter> paramlist = new List<CommandParameter>();
paramlist.Add(new CommandParameter("pIntezmenyId", intezmenyId));
paramlist.Add(new CommandParameter("pTanevId", tanevId));
if (pco.FeladatKategoriaId.IsEntityId())
{
paramlist.Add(new CommandParameter(nameof(pco.FeladatKategoriaId), pco.FeladatKategoriaId.Value));
}
if (pco.FeladatEllatasiHelyId.HasValue)
{
paramlist.Add(new CommandParameter(nameof(pco.FeladatEllatasiHelyId), pco.FeladatEllatasiHelyId.Value));
}
StringBuilder wherestringfelhasznalo = new StringBuilder();
StringBuilder wherestringgondviselo = new StringBuilder();
/*TODO:Tobb Osztaly*//*OM-1616*/
StringBuilder CommandText = new StringBuilder($@"
SELECT
f.C_NYOMTATASINEV as 'FelhasznaloNyomtatasiNeve'
,IIF(f.C_NEVSORREND = 'T',
f.C_UTONEV + ' ' + f.C_VEZETEKNEV,
f.C_VEZETEKNEV + ' ' + f.C_UTONEV) as 'FelhasznaloNyomtatasiNeveElotagNelkul'
,IIF(t.ID IS NULL, 'Alkalmazott','Tanuló') as 'FelhasznaloTipusa'
,f.C_OKTATASIAZONOSITO AS 'OktatasiAzonosito'
,IIF(fb.TOROLT = 'F', fb.C_BEJELENTKEZESINEV, NULL) as 'FelhasznaloNev'
,IIF(fb.TOROLT = 'F', utolsoBelepes.utolsoBelepes, NULL) as 'UtolsoBelepes'
,IIF(fb.TOROLT = 'F', utolsoBelepes.utolsoBelepesMobil, NULL) as 'UtolsoBelepesMobil'
,IIF(fb.TOROLT = 'F', fb.C_JELSZOVALTOZTATASIDEJE, NULL) as 'JelszovaltoztatasIdeje'
,f.ID as 'ID'
,IIF(t.ID IS NULL, j.jogosultsagok,'e-Ellenőrző') as 'Jogosultsag'
,ft.C_NYOMTATASINEV as 'Tanulo'
,ft.ID as 'TanuloId'
,ISNULL(ofoosztaly.C_NEV, osztaly.C_NEV) as 'Osztaly'
,g.C_ISCSOKKENTETTGONDVISELO as 'CsokkentettGondviselo'
,NULL AS 'GondviseloBovitettJog'
,g.C_ISTORVENYESKEPVISELO as 'TorvenyesKepviselo'
,f.C_EGYEDIAZONOSITO as 'FelhasznaloEgyediAzonosito'
,NULL as 'GondviseloEgyediAzonosito'
,IIF((t.ID IS NOT NULL AND osztaly.ID IS NOT NULL) OR (a.ID IS NOT NULL AND ((m.C_ALKALMAZASKEZDETE IS NULL OR m.C_ALKALMAZASKEZDETE <= CAST(GETDATE() as DATE)) AND (m.C_ALKALMAZASMEGSZUNESE IS NULL OR m.C_ALKALMAZASMEGSZUNESE >= CAST(GETDATE() as DATE)))), 'Igen', 'Nem') as 'IsAktiv'
,ft.C_OKTATASIAZONOSITO AS 'TanuloOktatasiAzonosito'
,ft.C_SZULETESIDATUM AS 'TanuloSzuletesiDatum'
,g.C_ROKONSAGFOKA AS 'GondviseloRokonsagiFokId'
FROM T_FELHASZNALO_OSSZES f
LEFT JOIN T_ALKALMAZOTT_OSSZES a ON a.ID = f.ID AND a.TOROLT = 'F'
LEFT JOIN T_TANULO_OSSZES t ON t.ID = f.ID AND t.TOROLT = 'F'
LEFT JOIN T_FELHASZNALO_OSSZES ft ON ft.ID = t.ID AND ft.TOROLT = 'F'
LEFT JOIN (SELECT
ocs.ID
,ocs.C_FELADATKATEGORIAID
,ocs.C_FELADATELLATASIHELYID
,STUFF((SELECT ',' + ocs2.C_NEV
FROM T_OSZTALYCSOPORT_OSSZES ocs2
INNER JOIN T_TANULOCSOPORT_OSSZES tcs2 ON tcs2.C_OSZTALYCSOPORTID=ocs2.ID AND tcs.C_TANULOID = tcs2.C_TANULOID AND tcs2.TOROLT = 'F'
INNER JOIN T_OSZTALY_OSSZES o2 ON o2.ID = ocs2.ID AND o2.TOROLT = 'F'
WHERE ocs2.TOROLT = 'F'
FOR XML PATH(''), TYPE).value('.', 'varchar(max)'),1,1,'') as C_NEV,tcs.C_TANULOID
FROM T_TANULOCSOPORT_OSSZES tcs
INNER JOIN T_OSZTALYCSOPORT_OSSZES ocs ON tcs.C_OSZTALYCSOPORTID = ocs.ID AND ocs.TOROLT = 'F'
INNER JOIN T_OSZTALY_OSSZES o ON ocs.ID = o.ID AND o.TOROLT = 'F'
WHERE tcs.TOROLT = 'F' AND tcs.C_BELEPESDATUM <= GETDATE() AND (tcs.C_KILEPESDATUM IS NULL OR tcs.C_KILEPESDATUM > GETDATE())
GROUP BY ocs.ID, ocs.C_FELADATKATEGORIAID, ocs.C_FELADATELLATASIHELYID, tcs.C_TANULOID
) osztaly ON t.ID = osztaly.C_TANULOID
LEFT JOIN (
SELECT
STUFF((SELECT ',' + ocs2.C_NEV
FROM T_OSZTALYCSOPORT_OSSZES ocs2
INNER JOIN T_OSZTALY_OSSZES o2 ON o2.ID = ocs2.ID AND o2.TOROLT = 'F'
WHERE ocs2.TOROLT = 'F'
AND (o2.C_OSZTALYFONOKID = a.ID OR o2.C_OFOHELYETTESID = a.ID)
{(pco.OsztalyId.HasValue ? " AND o2.ID = @pOsztalyId " : "")}
FOR XML PATH(''), TYPE).value('.', 'varchar(max)'),1,1,'') as C_NEV
,a.ID AS ofoID
FROM T_OSZTALY_OSSZES o
INNER JOIN T_ALKALMAZOTT_OSSZES a ON a.ID = o.C_OSZTALYFONOKID OR a.ID = o.C_OFOHELYETTESID AND a.TOROLT = 'F'
WHERE o.TOROLT = 'F'
{(pco.OsztalyId.HasValue ? " AND o.ID = @pOsztalyId " : "")}
GROUP BY a.ID
) ofoosztaly ON a.ID = ofoosztaly.ofoID
OUTER APPLY (
SELECT
MAX(CASE WHEN C_IDP IN ('F', '_') THEN C_UTOLSOBELEPES END) AS utolsoBelepes,
MAX(CASE WHEN C_IDP IN ('T', '_') THEN C_UTOLSOBELEPESMOBIL END) AS utolsoBelepesMobil
FROM (
SELECT C_GONDVISELOID, C_FELHASZNALOID, C_TANEVID, '_' C_IDP, C_UTOLSOBELEPES, C_UTOLSOBELEPESMOBIL
FROM T_FELHASZNALOBELEPES_OSSZES
WHERE TOROLT = 'F'
AND C_BEJELENTKEZESINEV <> 'KRETA_TECHNICAL_FORI'
UNION ALL
SELECT C_GONDVISELOID, C_FELHASZNALOID, C_TANEVID, C_IDP, C_LASTACTIVITY, C_LASTACTIVITY
FROM T_FELHASZNALOBELEPESTORTENET_OSSZES
) b
WHERE b.C_FELHASZNALOID = f.ID
AND b.C_TANEVID = f.C_TANEVID
) utolsoBelepes
LEFT JOIN T_FELHASZNALOBELEPES_OSSZES fb ON fb.C_FELHASZNALOID = f.ID AND fb.TOROLT = 'F' AND fb.C_GONDVISELOID IS NULL AND fb.C_BEJELENTKEZESINEV NOT IN ('KRETA_TECHNICAL_FORI')
LEFT JOIN T_GONDVISELO_OSSZES g ON g.ID = fb.C_GONDVISELOID AND g.TOROLT = 'F'
LEFT JOIN T_MUNKAUGYIADATOK_OSSZES m ON m.C_ALKALMAZOTTID = f.ID AND m.C_TANEVID = f.C_TANEVID AND m.TOROLT = 'F'
LEFT JOIN T_FELADATELLATASIHELY_OSSZES fhely ON fhely.ID = m.C_FELADATELLATASIHELYID AND fhely.C_TANEVID = f.C_TANEVID AND fhely.TOROLT = 'F'
LEFT JOIN T_OKTATASINEVELESIFELADAT_OSSZES oktnevf ON oktnevf.ID = fhely.C_OKTATASINEVELESIFELADATTIPUS AND oktnevf.C_ALTANEVID = f.C_TANEVID AND oktnevf.TOROLT = 'F'
LEFT JOIN (
SELECT DISTINCT f2.ID,
STUFF((SELECT ', ' + szerepkorok.C_NAME
FROM
(SELECT f.ID felhasznaloID, d.C_NAME
FROM T_FELHASZNALO_OSSZES f
INNER JOIN T_FELHASZNALO_SZEREPKOR fsz ON fsz.C_FELHASZNALOID = f.ID
INNER JOIN T_SZEREPKOR_OSSZES sz ON sz.ID = fsz.C_SZEREPKORID AND sz.TOROLT = 'F'
INNER JOIN T_DICTIONARYITEMBASE_OSSZES d ON d.ID = sz.C_SZEREPKORTIPUS AND d.TOROLT = 'F'
");
wherestringfelhasznalo.Append($@" AND f.C_NYOMTATASINEV NOT IN ('{Constants.SpecialUserName.KretaAdminisztrator}', 'KRETA_TECHNICAL_FORI')");
if (pco.JogosultsagIdList != null && pco.JogosultsagIdList.Count > 0)
{
wherestringfelhasznalo.Append(" AND j.jogosultsagok IS NOT NULL ");
CommandText.Append(" AND d.ID IN(");
CommandText.Append(string.Join(", ", pco.JogosultsagIdList));
CommandText.Append(") ");
}
CommandText.Append($@"WHERE f.TOROLT = 'F'
GROUP BY f.ID, d.C_NAME
{(((isHRModulEngedelyezett && (pco.JogosultsagIdList == null || pco.JogosultsagIdList.Count == 0 || pco.JogosultsagIdList.Contains(int.Parse(FelhasznalokResource.HRModulKezeloMintJogosultsagValue))))) ? $@"
UNION ALL
SELECT a.ID AS felhasznaloID, '{FelhasznalokResource.HRModulKezeloMintJogosultsag}' AS C_NAME
FROM T_ALKALMAZOTT_OSSZES a
WHERE a.C_ISHRKEZELO = 'T'
AND a.TOROLT = 'F'" : "")}
{(((isLepEngedelyezett && (pco.JogosultsagIdList == null || pco.JogosultsagIdList.Count == 0 || pco.JogosultsagIdList.Contains(int.Parse(FelhasznalokResource.LEPModulKezeloMintJogosultsagValue))))) ? $@"
UNION ALL
SELECT a.ID AS felhasznaloID, '{FelhasznalokResource.LEPModulKezeloMintJogosultsag}' AS C_NAME
FROM T_ALKALMAZOTT_OSSZES a
WHERE a.C_LEPKEZELO = 'T'
AND a.TOROLT = 'F'" : "")}
{(((pco.JogosultsagIdList == null || pco.JogosultsagIdList.Count == 0 || pco.JogosultsagIdList.Contains(int.Parse(FelhasznalokResource.DiakOlimpiaKezeloMintJogosultsagValue)))) ? $@"
UNION ALL
SELECT a.ID AS felhasznaloID, '{FelhasznalokResource.DiakOlimpiaKezeloMintJogosultsag}' AS C_NAME
FROM T_ALKALMAZOTT_OSSZES a
WHERE a.C_DIAKOLIMPIA = 'T'
AND a.TOROLT = 'F'" : "")}
{(((pco.JogosultsagIdList == null || pco.JogosultsagIdList.Count == 0 || pco.JogosultsagIdList.Contains(int.Parse(FelhasznalokResource.KozSzolgKezeloMintJogosultsagValue)))) ? $@"
UNION ALL
SELECT a.ID AS felhasznaloID, '{FelhasznalokResource.KozSzolgKezeloMintJogosultsag}' AS C_NAME
FROM T_ALKALMAZOTT_OSSZES a
WHERE a.C_KOZOSSEGISZOLGALATKEZELO = 'T'
AND a.TOROLT = 'F'" : "")}
{(((pco.JogosultsagIdList == null || pco.JogosultsagIdList.Count == 0 || pco.JogosultsagIdList.Contains(int.Parse(FelhasznalokResource.OsztalyfonokMintJogosultsagValue)))) ? $@"
UNION ALL
SELECT f.ID, '{FelhasznalokResource.OsztalyfonokMintJogosultsag}'
FROM T_FELHASZNALO_OSSZES f
INNER JOIN T_OSZTALY_OSSZES o ON o.C_OSZTALYFONOKID = f.ID
GROUP BY f.ID" : "")}
{(((pco.JogosultsagIdList == null || pco.JogosultsagIdList.Count == 0 || pco.JogosultsagIdList.Contains(int.Parse(FelhasznalokResource.OsztalyfonokHelyettesMintJogosultsagValue)))) ? $@"
UNION ALL
SELECT f.ID, '{FelhasznalokResource.OsztalyfonokHelyettesMintJogosultsag}'
FROM T_FELHASZNALO_OSSZES f
INNER JOIN T_OSZTALY_OSSZES o ON o.C_OFOHELYETTESID = f.ID
GROUP BY f.ID" : "")}
) szerepkorok
WHERE szerepkorok.felhasznaloID = f2.ID
FOR XML PATH('')), 1, 2, '') jogosultsagok
FROM T_FELHASZNALO_OSSZES f2
WHERE f2.TOROLT = 'F'
) j ON j.ID = f.ID
WHERE
f.TOROLT = 'F'
AND f.C_TANEVID = :pTanevId
AND f.C_INTEZMENYID = :pIntezmenyId
");
if (pco.FeladatKategoriaId.IsEntityId())
{
CommandText.Append($" AND (osztaly.C_FELADATKATEGORIAID = @{nameof(pco.FeladatKategoriaId)} OR oktnevf.C_FELADATKATEGORIAID = @{nameof(pco.FeladatKategoriaId)})");
}
if (pco.FeladatEllatasiHelyId.HasValue)
{
CommandText.Append($" AND (osztaly.C_FELADATELLATASIHELYID = @{nameof(pco.FeladatEllatasiHelyId)} OR m.C_FELADATELLATASIHELYID = @{nameof(pco.FeladatEllatasiHelyId)})");
}
if (!string.IsNullOrWhiteSpace(pco.FelhasznaloNyomtatasiNeve))
{
wherestringfelhasznalo.Append($@"
AND (LOWER(f.C_NYOMTATASINEV) LIKE '%' + @{nameof(pco.FelhasznaloNyomtatasiNeve)} + '%')");
wherestringgondviselo.Append($@"
AND LOWER(g.C_NEV) LIKE '%' + @{nameof(pco.FelhasznaloNyomtatasiNeve)} + '%'");
paramlist.Add(new CommandParameter(nameof(pco.FelhasznaloNyomtatasiNeve), pco.FelhasznaloNyomtatasiNeve.ToLowerInvariant()));
}
if (pco.FelhasznaloTipusa.HasValue)
{
switch ((FelhasznaloTipusEnum)pco.FelhasznaloTipusa)
{
case FelhasznaloTipusEnum.Alkalmazott:
wherestringfelhasznalo.Append($" AND a.ID IS NOT NULL");
wherestringgondviselo.Append($" AND 1=0");
break;
case FelhasznaloTipusEnum.Gondviselo:
wherestringfelhasznalo.Append($" AND 1=0");
wherestringgondviselo.Append($" AND g.ID IS NOT NULL");
break;
case FelhasznaloTipusEnum.Tanulo:
wherestringfelhasznalo.Append($" AND t.ID IS NOT NULL");
wherestringgondviselo.Append($" AND 1=0");
break;
}
}
if (!string.IsNullOrWhiteSpace(pco.FelhasznaloNev))
{
wherestringfelhasznalo.Append($" AND LOWER(fb.C_BEJELENTKEZESINEV) LIKE '%' + @{nameof(pco.FelhasznaloNev)} + '%'");
wherestringgondviselo.Append($" AND LOWER(fb.C_BEJELENTKEZESINEV) LIKE '%' + @{nameof(pco.FelhasznaloNev)} + '%'");
paramlist.Add(new CommandParameter(nameof(pco.FelhasznaloNev), pco.FelhasznaloNev.ToLowerInvariant()));
}
if (pco.BelepesDatumTol.HasValue)
{
wherestringfelhasznalo.Append($" AND fb.C_UTOLSOBELEPES >= :pBelepesDatumTol");
wherestringgondviselo.Append($" AND fb.C_UTOLSOBELEPES >= :pBelepesDatumTol");
paramlist.Add(new CommandParameter("pBelepesDatumTol", pco.BelepesDatumTol.Value));
}
if (pco.BelepesDatumIg.HasValue)
{
wherestringfelhasznalo.Append($" AND fb.C_UTOLSOBELEPES <= :pBelepesDatumIg");
wherestringgondviselo.Append($" AND fb.C_UTOLSOBELEPES <= :pBelepesDatumIg");
paramlist.Add(new CommandParameter("pBelepesDatumIg", pco.BelepesDatumIg.Value));
}
if (pco.GondviseloBelepesNelkul.HasValue && pco.GondviseloBelepesNelkul.Value)
{
wherestringfelhasznalo.Append($" AND 1=0");
wherestringgondviselo.Append($" AND fb.C_BEJELENTKEZESINEV IS NULL");
}
if (pco.IsAktiv)
{
wherestringfelhasznalo.Append($" AND ((osztaly.ID IS NOT NULL) OR (a.ID IS NOT NULL AND ((m.C_ALKALMAZASKEZDETE IS NULL OR m.C_ALKALMAZASKEZDETE <= CAST(GETDATE() as DATE)) AND (m.C_ALKALMAZASMEGSZUNESE IS NULL OR m.C_ALKALMAZASMEGSZUNESE >= CAST(GETDATE() as DATE))))) ");
wherestringgondviselo.Append($" AND osztaly.ID IS NOT NULL ");
}
if (pco.JelszoValtozottE.HasValue)
{
switch ((IgenNemEnum)pco.JelszoValtozottE.Value)
{
case IgenNemEnum.Igen:
wherestringfelhasznalo.Append($" AND fb.C_JELSZOVALTOZTATASIDEJE IS NOT NULL");
wherestringgondviselo.Append($" AND fb.C_JELSZOVALTOZTATASIDEJE IS NOT NULL");
break;
case IgenNemEnum.Nem:
wherestringfelhasznalo.Append($" AND fb.C_JELSZOVALTOZTATASIDEJE IS NULL");
wherestringgondviselo.Append($" AND fb.C_JELSZOVALTOZTATASIDEJE IS NULL");
break;
}
}
if (pco.VanBelepese.HasValue)
{
switch ((IgenNemEnum)pco.VanBelepese.Value)
{
case IgenNemEnum.Igen:
wherestringfelhasznalo.Append($" AND fb.C_BEJELENTKEZESINEV IS NOT NULL");
wherestringgondviselo.Append($" AND fb.C_BEJELENTKEZESINEV IS NOT NULL");
break;
case IgenNemEnum.Nem:
wherestringfelhasznalo.Append($" AND fb.C_BEJELENTKEZESINEV IS NULL");
wherestringgondviselo.Append($" AND fb.C_BEJELENTKEZESINEV IS NULL");
break;
}
}
if (pco.CsokkentettGondviseloE.HasValue)
{
switch ((IgenNemEnum)pco.CsokkentettGondviseloE.Value)
{
case IgenNemEnum.Igen:
wherestringfelhasznalo.Append($" AND g.C_ISCSOKKENTETTGONDVISELO = 'T'");
wherestringgondviselo.Append($" AND g.C_ISCSOKKENTETTGONDVISELO = 'T'");
break;
case IgenNemEnum.Nem:
wherestringfelhasznalo.Append($" AND g.C_ISCSOKKENTETTGONDVISELO = 'F'");
wherestringgondviselo.Append($" AND g.C_ISCSOKKENTETTGONDVISELO = 'F'");
break;
}
}
if (pco.GondviseloBovitettJogE.HasValue)
{
switch ((IgenNemEnum)pco.GondviseloBovitettJogE.Value)
{
case IgenNemEnum.Igen:
wherestringfelhasznalo.Append($" AND g.C_ISCSOKKENTETTGONDVISELO = 'F'");
wherestringgondviselo.Append($" AND g.C_ISCSOKKENTETTGONDVISELO = 'F'");
break;
case IgenNemEnum.Nem:
wherestringfelhasznalo.Append($" AND g.C_ISCSOKKENTETTGONDVISELO = 'T'");
wherestringgondviselo.Append($" AND g.C_ISCSOKKENTETTGONDVISELO = 'T'");
break;
}
}
if (pco.TorvenyesKepviseloE.HasValue)
{
switch ((IgenNemEnum)pco.TorvenyesKepviseloE.Value)
{
case IgenNemEnum.Igen:
wherestringfelhasznalo.Append($" AND g.C_ISTORVENYESKEPVISELO = 'T'");
wherestringgondviselo.Append($" AND g.C_ISTORVENYESKEPVISELO = 'T'");
break;
case IgenNemEnum.Nem:
wherestringfelhasznalo.Append($" AND g.C_ISTORVENYESKEPVISELO = 'F'");
wherestringgondviselo.Append($" AND g.C_ISTORVENYESKEPVISELO = 'F'");
break;
}
}
if (pco.OsztalyId.HasValue)
{
wherestringfelhasznalo.Append($" AND osztaly.ID = :pOsztalyID OR ofoosztaly.ofoID IS NOT NULL");
wherestringgondviselo.Append($" AND osztaly.ID = :pOsztalyID");
paramlist.Add(new CommandParameter("pOsztalyID", pco.OsztalyId.Value));
}
if (!string.IsNullOrWhiteSpace(pco.Tanulo))
{
wherestringfelhasznalo.Append($" AND LOWER(ft.C_NYOMTATASINEV) LIKE '%' + :pTanulo + '%'");
wherestringgondviselo.Append($" AND LOWER(ft.C_NYOMTATASINEV) LIKE '%' + :pTanulo + '%'");
paramlist.Add(new CommandParameter("pTanulo", pco.Tanulo.ToLowerInvariant()));
}
CommandText.Append(wherestringfelhasznalo);
// TODO: utolsoBelepes-t kiszervezni functionbe refaktkor, és a többi helyen is azt hívni
CommandText.Append(@"
UNION
SELECT
g.C_NEV as 'FelhasznaloNyomtatasiNeve'
,g.C_NEV as 'FelhasznaloNyomtatasiNeveElotagNelkul'
,'Gondviselő' as 'FelhasznaloTipusa'
,NULL AS OktatasiAzonosito
,IIF(fb.TOROLT = 'F', fb.C_BEJELENTKEZESINEV, NULL) as 'FelhasznaloNev'
,IIF(fb.TOROLT = 'F', utolsoBelepes.utolsoBelepes, NULL) as 'UtolsoBelepes'
,IIF(fb.TOROLT = 'F', utolsoBelepes.utolsoBelepesMobil, NULL) as 'UtolsoBelepesMobil'
,IIF(fb.TOROLT = 'F', fb.C_JELSZOVALTOZTATASIDEJE, NULL) as 'JelszovaltoztatasIdeje'
,g.ID as 'Id'
,'e-Ellenőrző' as 'Jogosultsag'
,ft.C_NYOMTATASINEV as 'Tanulo'
,ft.ID as 'TanuloId'
,osztaly.C_NEV as 'Osztaly'
,g.C_ISCSOKKENTETTGONDVISELO as 'CsokkentettGondviselo'
,IIF(g.C_ISCSOKKENTETTGONDVISELO = 'T', 'F', 'T') AS GondviseloBovitettJog
,g.C_ISTORVENYESKEPVISELO as 'TorvenyesKepviselo'
,NULL as 'FelhasznaloEgyediAzonosito'
,g.C_EGYEDIAZONOSITO as 'GondviseloEgyediAzonosito'
,IIF(osztaly.ID IS NOT NULL , 'Igen', 'Nem') as 'IsAktiv'
,ft.C_OKTATASIAZONOSITO AS 'TanuloOktatasiAzonosito'
,ft.C_SZULETESIDATUM AS 'TanuloSzuletesiDatum'
,g.C_ROKONSAGFOKA AS 'GondviseloRokonsagiFokId'
FROM T_GONDVISELO_OSSZES g
LEFT JOIN T_TANULO_OSSZES t ON t.ID = g.C_TANULOID AND t.TOROLT = 'F'
LEFT JOIN T_FELHASZNALO_OSSZES ft ON ft.ID = t.ID AND ft.TOROLT = 'F'
LEFT JOIN (SELECT
ocs.ID
,ocs.C_FELADATKATEGORIAID
,ocs.C_FELADATELLATASIHELYID
,STUFF((SELECT ',' + ocs2.C_NEV
FROM T_OSZTALYCSOPORT_OSSZES ocs2
INNER JOIN T_TANULOCSOPORT_OSSZES tcs2 ON tcs2.C_OSZTALYCSOPORTID=ocs2.ID AND tcs.C_TANULOID = tcs2.C_TANULOID AND tcs2.TOROLT = 'F'
INNER JOIN T_OSZTALY_OSSZES o2 ON o2.ID = ocs2.ID AND o2.TOROLT = 'F'
WHERE ocs2.TOROLT = 'F'
FOR XML PATH(''), TYPE).value('.', 'varchar(max)'),1,1,'') as C_NEV,tcs.C_TANULOID
FROM T_TANULOCSOPORT_OSSZES tcs
INNER JOIN T_OSZTALYCSOPORT_OSSZES ocs ON tcs.C_OSZTALYCSOPORTID = ocs.ID AND ocs.TOROLT = 'F'
INNER JOIN T_OSZTALY_OSSZES o ON ocs.ID = o.ID AND o.TOROLT = 'F'
WHERE tcs.TOROLT = 'F' AND tcs.C_BELEPESDATUM <= GETDATE() AND (tcs.C_KILEPESDATUM IS NULL OR tcs.C_KILEPESDATUM > GETDATE())
GROUP BY ocs.ID, ocs.C_FELADATKATEGORIAID, ocs.C_FELADATELLATASIHELYID, tcs.C_TANULOID
) osztaly ON t.ID = osztaly.C_TANULOID
OUTER APPLY (
SELECT
MAX(CASE WHEN C_IDP IN ('F', '_') THEN C_UTOLSOBELEPES END) AS utolsoBelepes,
MAX(CASE WHEN C_IDP IN ('T', '_') THEN C_UTOLSOBELEPESMOBIL END) AS utolsoBelepesMobil
FROM (
SELECT C_GONDVISELOID, C_FELHASZNALOID, C_TANEVID, '_' C_IDP, C_UTOLSOBELEPES, C_UTOLSOBELEPESMOBIL
FROM T_FELHASZNALOBELEPES_OSSZES
WHERE TOROLT = 'F'
AND C_BEJELENTKEZESINEV <> 'KRETA_TECHNICAL_FORI'
UNION ALL
SELECT C_GONDVISELOID, C_FELHASZNALOID, C_TANEVID, C_IDP, C_LASTACTIVITY, C_LASTACTIVITY
FROM T_FELHASZNALOBELEPESTORTENET_OSSZES
) b
WHERE b.C_GONDVISELOID = g.ID
AND b.C_TANEVID = g.C_TANEVID
) utolsoBelepes
LEFT JOIN T_FELHASZNALOBELEPES_OSSZES fb ON fb.TOROLT = 'F' AND fb.C_GONDVISELOID = g.ID
LEFT JOIN (
SELECT DISTINCT fsz2.C_FELHASZNALOID,
STUFF((SELECT ', ' + szerepkorok.C_NAME
FROM
(SELECT f.ID felhasznaloID, d.C_NAME
FROM T_FELHASZNALO f
INNER JOIN T_FELHASZNALO_SZEREPKOR fsz ON fsz.C_FELHASZNALOID = f.ID
INNER JOIN T_SZEREPKOR_OSSZES sz ON sz.ID = fsz.C_SZEREPKORID AND sz.TOROLT = 'F'
INNER JOIN T_DICTIONARYITEMBASE_OSSZES d ON d.ID = sz.C_SZEREPKORTIPUS AND d.TOROLT = 'F'
");
if (pco.JogosultsagIdList != null && pco.JogosultsagIdList.Count > 0)
{
wherestringgondviselo.Append(" AND j.jogosultsagok IS NOT NULL ");
CommandText.Append(" AND d.ID IN(");
CommandText.Append(string.Join(", ", pco.JogosultsagIdList));
CommandText.Append(") ");
}
CommandText.Append(@"WHERE f.TOROLT = 'F'
GROUP BY f.ID, d.C_NAME) szerepkorok
WHERE szerepkorok.felhasznaloID = fsz2.C_FELHASZNALOID
FOR XML PATH('')), 1, 1, '') jogosultsagok
FROM T_FELHASZNALO_SZEREPKOR fsz2
) j ON j.C_FELHASZNALOID = fb.C_FELHASZNALOID
WHERE
g.TOROLT = 'F'
AND g.C_TANEVID = :pTanevId
AND g.C_INTEZMENYID = :pIntezmenyId
");
if (pco.FeladatKategoriaId.IsEntityId())
{
CommandText.Append($" AND osztaly.C_FELADATKATEGORIAID = @{nameof(pco.FeladatKategoriaId)}");
}
if (pco.FeladatEllatasiHelyId.HasValue)
{
CommandText.Append($" AND osztaly.C_FELADATELLATASIHELYID = @{nameof(pco.FeladatEllatasiHelyId)}");
}
CommandText.Append(wherestringgondviselo);
DataSet ds = GetData(CommandText.ToString(), paramlist, null, "CsokkentettGondviselo,TorvenyesKepviselo,GondviseloBovitettJog");
return ds;
}
public DataSet GetFelhasznalokOnlyAlkalmazottSzerepkor(int tanevId)
{
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 = "uspFelhasznalokWithOnlyAlkalmazottSzerepkor";
command.Parameters.Add("pTanevId", SDADBType.Int).Value = tanevId;
using (var adapter = new SDADataAdapter())
{
adapter.SelectCommand = command;
adapter.Fill(ds);
}
}
return ds;
}
public DataSet GetAdminFelhasznalok(string intezmenyAzonosito)
{
var parameters = new List<CommandParameter> { new CommandParameter(nameof(intezmenyAzonosito), intezmenyAzonosito),
new CommandParameter(nameof(SzerepkorTipusEnum.Adminisztrator), (int)SzerepkorTipusEnum.Adminisztrator) };
var command = $@"
SELECT f.ID
FROM T_FELHASZNALO f
INNER JOIN T_INTEZMENY i ON i.ID = f.C_INTEZMENYID AND i.TOROLT = 'F' AND i.C_AZONOSITO = @{nameof(intezmenyAzonosito)}
INNER JOIN T_TANEV t ON t.ID = f.C_TANEVID AND t.TOROLT = 'F' AND t.C_AKTIV = 'T' AND t.C_INTEZMENYID = f.C_INTEZMENYID
INNER JOIN T_FELHASZNALO_SZEREPKOR fsz ON fsz.C_FELHASZNALOID = f.ID
INNER JOIN T_SZEREPKOR sz ON sz.ID = fsz.C_SZEREPKORID AND sz.TOROLT = 'F' AND sz.C_SZEREPKORTIPUS = @{nameof(SzerepkorTipusEnum.Adminisztrator)}
WHERE f.C_NYOMTATASINEV <> '{Constants.SpecialUserName.KretaAdminisztrator}'
AND f.TOROLT = 'F'
";
return GetData(command, parameters);
}
public void FollowUpFelhasznaloEmail(int intezmenyId, int tanevId, int kovTanevId, int felhasznaloId)
{
using (SDACommand command = new SDACommand())
{
command.Connection = UserContext.Instance.SDAConnection;
command.Transaction = UserContext.Instance.SDATransaction;
command.CommandType = CommandType.StoredProcedure;
command.CommandText = "uspFollowUpFelhasznaloEmail";
command.Parameters.Add("intezmenyId", intezmenyId);
command.Parameters.Add("felhasznaloId", felhasznaloId);
command.Parameters.Add("kovetkezoTanevId", kovTanevId);
command.Parameters.Add("aktTanevId", tanevId);
command.ExecuteNonQuery();
DalHelper.Commit();
}
}
public void FollowUpCim(int intezmenyId, int tanevId, int kovTanevId, int felhasznaloId)
{
using (SDACommand command = new SDACommand())
{
command.Connection = UserContext.Instance.SDAConnection;
command.Transaction = UserContext.Instance.SDATransaction;
command.CommandType = CommandType.StoredProcedure;
command.CommandText = "uspFollowUpFelhasznaloCim";
command.Parameters.Add("intezmenyId", intezmenyId);
command.Parameters.Add("felhasznaloId", felhasznaloId);
command.Parameters.Add("kovetkezoTanevId", kovTanevId);
command.Parameters.Add("aktTanevId", tanevId);
command.ExecuteNonQuery();
DalHelper.Commit();
}
}
public void FollowUpTelefon(int intezmenyId, int tanevId, int kovTanevId, int alkalmazottId)
{
using (SDACommand command = new SDACommand())
{
command.Connection = UserContext.Instance.SDAConnection;
command.Transaction = UserContext.Instance.SDATransaction;
command.CommandType = CommandType.StoredProcedure;
command.CommandText = "uspFollowUpFelhasznaloTelefon";
command.Parameters.Add("intezmenyId", intezmenyId);
command.Parameters.Add("felhasznaloId", alkalmazottId);
command.Parameters.Add("kovetkezoTanevId", kovTanevId);
command.Parameters.Add("aktTanevId", tanevId);
command.ExecuteNonQuery();
DalHelper.Commit();
}
}
public KirFelhasznaloElerhetosegek GetFelhasznaloElerhetoseg(int felhasznaloId, int tanevId, bool isTanulo)
{
var whereString = "";
if (isTanulo)
{
whereString = @"AND EXISTS (SELECT 1
FROM T_TANULO_OSSZES Tanulo
WHERE Tanulo.C_ALTANEVID = Felhasznalo.C_TANEVID
AND Tanulo.C_ALINTEZMENYID = Felhasznalo.C_INTEZMENYID
AND Tanulo.ID = Felhasznalo.ID)";
}
else
{
whereString = @"AND EXISTS (SELECT 1
FROM T_ALKALMAZOTT Alkalmazott
WHERE Alkalmazott.C_ALTANEVID = Felhasznalo.C_TANEVID
AND Alkalmazott.C_ALINTEZMENYID = Felhasznalo.C_INTEZMENYID
AND Alkalmazott.ID = Felhasznalo.ID)";
}
var commandText = new StringBuilder($@"
SELECT
Felhasznalo.C_SZULETESINEV AS SzuletesiNev
,Felhasznalo.C_ANYJANEVE AS AnyjaNeve
,Felhasznalo.C_SZULETESIDATUM AS SzuletesiDatum
,Felhasznalo.C_SZULETESIHELY AS SzuletesiHely
,Email.C_EMAILCIM AS Email
,Telefon.C_TELEFONSZAM AS Telefonszam
,AllandoLakcim.C_IRANYITOSZAM AS AllandoLakcimIranyitoszam
,AllandoLakcim.C_VAROS AS AllandoLakcimVaros
,AllandoLakcim.C_KOZTERULET AS AllandoLakcimKozteruletNev
,AllandoLakcim.C_KOZTERULETJELLEGENEV AS AllandoLakcimKozteruletJellegeNev
,AllandoLakcim.C_HAZSZAM AS AllandoLakcimHazszam
,TartozkodasiCim.C_IRANYITOSZAM AS TartozkodasiCimIranyitoszam
,TartozkodasiCim.C_VAROS AS TartozkodasiCimVaros
,TartozkodasiCim.C_KOZTERULET AS TartozkodasiCimKozteruletNev
,TartozkodasiCim.C_KOZTERULETJELLEGENEV AS TartozkodasiCimKozteruletJellegeNev
,TartozkodasiCim.C_HAZSZAM AS TartozkodasiCimHazszam
FROM T_FELHASZNALO Felhasznalo
LEFT JOIN T_EMAIL Email ON Email.C_FELHASZNALOID = Felhasznalo.ID
AND Email.C_ALAPERTELMEZETT = 'T'
AND Email.TOROLT = 'F'
AND Email.C_INTEZMENYID = Felhasznalo.C_INTEZMENYID
AND Email.C_TANEVID = Felhasznalo.C_TANEVID
LEFT JOIN T_TELEFON Telefon ON Telefon.C_FELHASZNALOID = Felhasznalo.ID
AND Telefon.C_ALAPERTELMEZETT = 'T'
AND Telefon.TOROLT = 'F'
AND Telefon.C_INTEZMENYID = Felhasznalo.C_INTEZMENYID
AND Telefon.C_TANEVID = Felhasznalo.C_TANEVID
LEFT JOIN T_CIM AllandoLakcim ON AllandoLakcim.C_FELHASZNALOID = Felhasznalo.ID
AND AllandoLakcim.C_ALAPERTELMEZETT = 'T'
AND AllandoLakcim.TOROLT = 'F'
AND AllandoLakcim.C_INTEZMENYID = Felhasznalo.C_INTEZMENYID
AND AllandoLakcim.C_TANEVID = Felhasznalo.C_TANEVID
AND AllandoLakcim.C_CIMTIPUSA = 907
LEFT JOIN T_CIM TartozkodasiCim ON TartozkodasiCim.C_FELHASZNALOID = Felhasznalo.ID
AND TartozkodasiCim.C_ALAPERTELMEZETT = 'T'
AND TartozkodasiCim.TOROLT = 'F'
AND TartozkodasiCim.C_INTEZMENYID = Felhasznalo.C_INTEZMENYID
AND TartozkodasiCim.C_TANEVID = Felhasznalo.C_TANEVID
AND TartozkodasiCim.C_CIMTIPUSA = 909
WHERE Felhasznalo.TOROLT = 'F'
AND Felhasznalo.C_TANEVID = :pTanevId
AND Felhasznalo.ID = :pId
");
commandText.Append(whereString);
var commandParameterList = new List<CommandParameter>
{
new CommandParameter("pTanevId", tanevId),
new CommandParameter("pId", felhasznaloId)
};
return GetData(commandText.ToString(), commandParameterList)
.ToDaoList<KirFelhasznaloElerhetosegek>()
.FirstOrDefault();
}
public void UpdateAllEgyediAzonosito()
{
using (SDACommand command = new SDACommand())
{
command.Connection = UserContext.Instance.SDAConnection;
command.Transaction = UserContext.Instance.SDATransaction;
command.CommandType = CommandType.StoredProcedure;
command.CommandText = "uspUpdateAllEgyediAzonosito";
command.ExecuteNonQuery();
DalHelper.Commit();
}
}
public string GetFelhasznaloEgyediAzonosito(int intezmenyId, int userId)
{
using (var command = UserContext.Instance.SDAConnection.CreateCommand())
{
command.Transaction = UserContext.Instance.SDATransaction;
// NOTE: Itt azért kell az "_OSSZES" tanévszűrés nélkül, mert a besorolásoknál elő kell tudnunk szedni korábbi tanéves felhasználókat is.
command.CommandText = @"
SELECT f.C_EGYEDIAZONOSITO
FROM T_FELHASZNALO_OSSZES f
WHERE f.ID = :pUserId
AND f.C_INTEZMENYID = :pIntezmenyId
AND f.TOROLT = 'F'
";
command.Parameters.Add("pIntezmenyId", intezmenyId);
command.Parameters.Add("pUserId", userId);
var result = command.ExecuteScalar();
return (string)result;
}
}
public string GetFelhasznaloOktatasiAzonosito(int userId)
{
using (var command = UserContext.Instance.SDAConnection.CreateCommand())
{
command.Transaction = UserContext.Instance.SDATransaction;
command.CommandText = @"
SELECT f.C_OKTATASIAZONOSITO FROM T_FELHASZNALO f
WHERE f.ID = :pUserId
AND f.TOROLT = 'F'
";
command.Parameters.Add("pUserId", userId);
var result = command.ExecuteScalar();
return (string)result;
}
}
public int? GetFelhasznaloByEgyediAzonositoAndTanev(string egyediAzonosito, int tanevId)
{
using (var command = UserContext.Instance.SDAConnection.CreateCommand())
{
command.Transaction = UserContext.Instance.SDATransaction;
command.CommandText = @"
SELECT f.ID FROM T_FELHASZNALO_OSSZES f
WHERE f.C_EGYEDIAZONOSITO = :pEgyediAzonosito
AND f.C_TANEVID = :ptanevId
AND f.TOROLT = 'F'
";
command.Parameters.Add("pEgyediAzonosito", egyediAzonosito);
command.Parameters.Add("ptanevId", tanevId);
var result = command.ExecuteScalar();
return (int?)result;
}
}
public int? GetFelhasznaloByOktatasiAzonositoAndTanev(string okatatasiAzonosito, int tanevId)
{
using (var command = UserContext.Instance.SDAConnection.CreateCommand())
{
command.Transaction = UserContext.Instance.SDATransaction;
command.CommandText = @"
SELECT f.ID FROM T_FELHASZNALO_OSSZES f
WHERE f.C_OKTATASIAZONOSITO = :pOkatatasiAzonosito
AND f.C_TANEVID = :ptanevId
AND f.TOROLT = 'F'
";
command.Parameters.Add("pOkatatasiAzonosito", okatatasiAzonosito);
command.Parameters.Add("ptanevId", tanevId);
var result = command.ExecuteScalar();
return (int?)result;
}
}
public string GetGondviseloEgyediAzonosito(int userId)
{
using (var command = UserContext.Instance.SDAConnection.CreateCommand())
{
command.Transaction = UserContext.Instance.SDATransaction;
command.CommandText = @"
SELECT g.C_EGYEDIAZONOSITO FROM T_GONDVISELO g
WHERE g.ID = :pUserId
AND g.TOROLT = 'F'
";
command.Parameters.Add("pUserId", userId);
var result = command.ExecuteScalar();
return (string)result;
}
}
public string GetFunkcioTeruletSAPKod(int pFelhasznaloId, int pTanevId)
{
using (var command = new SDACommand())
{
command.Connection = UserContext.Instance.SDAConnection;
command.Transaction = UserContext.Instance.SDATransaction;
command.Parameters.Add(nameof(pFelhasznaloId), pFelhasznaloId);
command.Parameters.Add(nameof(pTanevId), pTanevId);
command.CommandText = $@"
SELECT
mh.C_FUNKCIOTERULET AS FunkcioTeruletSAPKod
FROM T_FELHASZNALO_OSSZES fh
LEFT JOIN T_MUNKAUGYIADATOK_OSSZES ma ON ma.C_ALKALMAZOTTID = fh.ID
AND ma.TOROLT = 'F'
AND ma.C_TANEVID = fh.C_TANEVID
LEFT JOIN T_FELADATELLATASIHELY_OSSZES feh ON feh.ID = ma.C_FELADATELLATASIHELYID
AND feh.TOROLT = 'F'
AND feh.C_TANEVID = fh.C_TANEVID
LEFT JOIN T_MUKODESIHELY_OSSZES mh ON mh.ID = feh.C_MUKODESIHELYID
AND mh.TOROLT = 'F'
AND mh.C_TANEVID = fh.C_TANEVID
WHERE fh.ID = @{nameof(pFelhasznaloId)}
AND fh.C_TANEVID = @{nameof(pTanevId)}
AND fh.TOROLT = 'F'";
var funkcioTeruletSAPKod = command.ExecuteScalar();
return funkcioTeruletSAPKod?.ToString() ?? string.Empty;
}
}
public DataSet GetFelhasznaloEmailByGuid(string pFelhasznaloEgyediAzonosito, int pIntezmenyId, int pTanevId)
{
List<CommandParameter> paramlist = new List<CommandParameter>
{
new CommandParameter(nameof(pFelhasznaloEgyediAzonosito), pFelhasznaloEgyediAzonosito),
new CommandParameter(nameof(pIntezmenyId), pIntezmenyId),
new CommandParameter(nameof(pTanevId), pTanevId)
};
string commandText = $@"
SELECT
f.ID AS 'AlkalmazottId'
,f.C_EGYEDIAZONOSITO AS 'AlkalmazottGuid'
,e.C_EMAILCIM AS 'EmailCim'
,e.C_GUID AS 'EmailGuid'
,f.C_NYOMTATASINEV AS 'AlkalmazottNev'
FROM T_EMAIL_OSSZES e
INNER JOIN T_FELHASZNALO_OSSZES f ON f.ID = e.C_FELHASZNALOID
WHERE e.TOROLT = 'F'
AND f.C_INTEZMENYID = @{nameof(pIntezmenyId)}
AND f.C_TANEVID = @{nameof(pTanevId)}
AND e.C_EMAILTIPUSA = 1025
AND f.C_EGYEDIAZONOSITO = @{nameof(pFelhasznaloEgyediAzonosito)}
";
var result = GetData(commandText, paramlist);
return result;
}
public bool IsTanuloOrGondviselo(int userId, int intezmenyId, int tanevId)
{
using (var command = UserContext.Instance.SDAConnection.CreateCommand())
{
command.Transaction = UserContext.Instance.SDATransaction;
command.CommandText = @"
SELECT ISNULL(SUM(cnt), 0) as cnt
FROM
(
SELECT 1 as cnt FROM T_TANULO t JOIN T_INTEZMENY i on i.ID = t.C_ALINTEZMENYID and i.C_ISARCHIV = 'T' WHERE t.ID = @pUserId and t.C_ALINTEZMENYID = @pIntezmenyId and t.C_ALTANEVID = @pTanevId
UNION ALL
SELECT 1 as cnt FROM T_GONDVISELO g JOIN T_INTEZMENY i on i.ID = g.C_INTEZMENYID and i.C_ISARCHIV = 'T' WHERE g.C_TANULOID = @pUserId and g.C_INTEZMENYID = @pIntezmenyId and g.C_TANEVID = @pTanevId
) x
";
command.Parameters.Add("pUserId", userId);
command.Parameters.Add("pIntezmenyId", intezmenyId);
command.Parameters.Add("pTanevId", tanevId);
object result = command.ExecuteScalar();
return int.Parse(result.ToString()) == 0;
}
}
public IEgyHaztartasbanElok GetEgyhaztartasbanElok()
{
var entity = EgyHaztartasbanElok.GiveAnInstance();
return entity;
}
public IEgyHaztartasbanElok GetEgyhaztartasbanElok(int id)
{
var entity = EgyHaztartasbanElok.GiveAnInstance();
entity.LoadByID(id);
return entity;
}
public void DeleteEgyhaztartasbanElok(int id)
{
var entity = EgyHaztartasbanElok.GiveAnInstance();
entity.LoadByID(id);
entity.Delete();
DalHelper.Commit();
}
public void InsertEgyhaztartasbanElok(IEgyHaztartasbanElok dto)
{
var entity = dto as EgyHaztartasbanElok;
entity.Insert();
DalHelper.Commit();
}
public void FullUpdateEgyhaztartasbanElok(IEgyHaztartasbanElok dto)
{
var entity = dto as EgyHaztartasbanElok;
entity.FullUpdate();
DalHelper.Commit();
}
public DataSet GetSapAdattisztasagExcel()
{
using (var sdaCommand = new SDACommand())
{
sdaCommand.Connection = UserContext.Instance.SDAConnection;
sdaCommand.Transaction = UserContext.Instance.SDATransaction;
sdaCommand.CommandType = CommandType.StoredProcedure;
sdaCommand.CommandText = "uspGetSapAdattisztasagExcel";
var dataSet = new DataSet();
using (var adapter = new SDADataAdapter())
{
adapter.SelectCommand = sdaCommand;
adapter.Fill(dataSet);
}
DataTable dataTable = dataSet.Tables[0];
DataTable result = SortingAndPaging(dataTable, GridParameters);
return result.AsDataSet();
}
}
public DataSet GetCovidFertozottekDataSet(int tanevId)
{
using (var sdaCommand = new SDACommand())
{
sdaCommand.Connection = UserContext.Instance.SDAConnection;
sdaCommand.Transaction = UserContext.Instance.SDATransaction;
sdaCommand.CommandType = CommandType.StoredProcedure;
sdaCommand.CommandText = "uspGetCovidFertozott";
sdaCommand.Parameters.Add("pTanevId", tanevId);
var dataSet = new DataSet();
using (var adapter = new SDADataAdapter())
{
adapter.SelectCommand = sdaCommand;
adapter.Fill(dataSet);
}
return dataSet;
}
}
public DataSet GetCovidFertozottekByTanarDataSet(int tanevId, int tanarId)
{
using (var sdaCommand = new SDACommand())
{
sdaCommand.Connection = UserContext.Instance.SDAConnection;
sdaCommand.Transaction = UserContext.Instance.SDATransaction;
sdaCommand.CommandType = CommandType.StoredProcedure;
sdaCommand.CommandText = "uspGetCovidFertozott";
sdaCommand.Parameters.Add("pTanevId", tanevId);
sdaCommand.Parameters.Add("pTanarId", tanarId);
var dataSet = new DataSet();
using (var adapter = new SDADataAdapter())
{
adapter.SelectCommand = sdaCommand;
adapter.Fill(dataSet);
}
return dataSet;
}
}
public DataSet UpdateFelhasznalokCovidAdatok(int tanevId, string tajSzamokJson)
{
using (var command = new SDACommand())
{
command.Connection = UserContext.Instance.SDAConnection;
command.Transaction = UserContext.Instance.SDATransaction;
command.CommandType = CommandType.StoredProcedure;
command.CommandText = "uspUpdateFelhasznalokCovidAdatok";
command.Parameters.Add("pTanevId", tanevId);
command.Parameters.Add("pTajSzamokJson", tajSzamokJson);
var ds = new DataSet();
using (var adapter = new SDADataAdapter())
{
adapter.SelectCommand = command;
adapter.Fill(ds);
}
DalHelper.Commit();
return ds;
}
}
public DataSet GetNapiErtesitesOsszefoglalo(int? tanuloId, int? tanuloIdp)
{
using (var sdaCommand = new SDACommand())
{
sdaCommand.Connection = UserContext.Instance.SDAConnection;
sdaCommand.Transaction = UserContext.Instance.SDATransaction;
sdaCommand.CommandType = CommandType.StoredProcedure;
sdaCommand.CommandText = "uspGetNapiOsszefoglaloErtesites";
if (tanuloId.HasValue)
{
sdaCommand.Parameters.Add("pTanuloId", tanuloId);
}
else
{
sdaCommand.Parameters.Add("pTanuloId", DBNull.Value);
}
if (tanuloIdp.HasValue)
{
sdaCommand.Parameters.Add("pTanuloIdp", tanuloIdp);
}
else
{
sdaCommand.Parameters.Add("pTanuloIdp", DBNull.Value);
}
var dataSet = new DataSet();
using (var adapter = new SDADataAdapter())
{
adapter.SelectCommand = sdaCommand;
adapter.Fill(dataSet);
}
return dataSet;
}
}
public List<int> GetAllIntezmenyiAdmin(int tanevId)
{
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 = "uspGetAllIntezmenyiAdmin";
command.Parameters.Add("pTanevId", SDADBType.Int).Value = tanevId;
using (var adapter = new SDADataAdapter())
{
adapter.SelectCommand = command;
adapter.Fill(ds);
}
}
return ds.Tables[0].AsEnumerable().Select(r => r.Field<int>("ID")).ToList();
}
public string GetBankszamlaVezetoBankNeve(string intezmenyAzonosito, string bankszamlaSzam)
{
using (var command = new SDACommand())
{
command.Connection = UserContext.Instance.SDAConnection;
command.Transaction = UserContext.Instance.SDATransaction;
command.CommandType = CommandType.Text;
command.CommandText = $@"SELECT [kr_{intezmenyAzonosito}_schema].fnGetBankszamlaVezetoBank('{bankszamlaSzam?.Replace("-", "") ?? string.Empty}')";
return command.ExecuteScalar()?.ToString() ?? string.Empty;
}
}
public void SaveBankszamlaAdatok(bool isTanulo, int felhasznaloId, string bankszamlaSzam, int? bankszamlaTulajdonosTipusID, string bankszamlaTulajNeve, int modifierId)
{
using (var command = new SDACommand())
{
command.Connection = UserContext.Instance.SDAConnection;
command.Transaction = UserContext.Instance.SDATransaction;
command.CommandText = $@"UPDATE
T_{(isTanulo ? "TANULO" : "ALKALMAZOTT")}_OSSZES
SET
C_BANKSZAMLASZAM = @bankszamlaSzam
,C_BANKSZAMLATULAJDONOSTIPUS{(isTanulo ? "ID" : "")} = @bankszamlaTulajdonosTipusID
,C_BANKSZAMLATULAJNEVE = @bankszamlaTulajNeve
,SERIAL += 1
,LASTCHANGED = GETDATE()
,MODIFIER = @modifierId
WHERE (ID = @felhasznaloId OR ELOZOTANEVIREKORDID = @felhasznaloId)
AND TOROLT = 'F'";
command.Parameters.Add(nameof(felhasznaloId), felhasznaloId);
if (bankszamlaSzam == null)
{
command.Parameters.Add(nameof(bankszamlaSzam), DBNull.Value);
}
else
{
command.Parameters.Add(nameof(bankszamlaSzam), bankszamlaSzam);
}
if (bankszamlaTulajdonosTipusID.IsEntityId())
{
command.Parameters.Add(nameof(bankszamlaTulajdonosTipusID), bankszamlaTulajdonosTipusID);
}
else
{
command.Parameters.Add(nameof(bankszamlaTulajdonosTipusID), DBNull.Value);
}
if (bankszamlaTulajNeve == null)
{
command.Parameters.Add(nameof(bankszamlaTulajNeve), DBNull.Value);
}
else
{
command.Parameters.Add(nameof(bankszamlaTulajNeve), bankszamlaTulajNeve);
}
command.Parameters.Add(nameof(modifierId), modifierId);
command.ExecuteNonQuery();
DalHelper.Commit();
}
}
}
}
Reference in a new issue View git blame Copy permalink