1973 lines
108 KiB
C#
1973 lines
108 KiB
C#
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 AlkalmazottDal : DataAccessBase, IAlkalmazottDal
|
|
{
|
|
public AlkalmazottDal(DalHandler handler, GridParameters parameters) : base(handler, parameters)
|
|
{
|
|
}
|
|
|
|
public AlkalmazottDal(DalHandler handler) : base(handler)
|
|
{
|
|
}
|
|
|
|
public int GetAlklamazottMunkakorTipusa(int id)
|
|
{
|
|
var entity = Get(id);
|
|
|
|
return entity.MunkaugyiAdatok.First().MunkakorTipusa.Value;
|
|
}
|
|
|
|
public int? GetAlkalmazottFeladatEllatasiHelyId(int alkalmazottId, int tanevId)
|
|
{
|
|
using (SDACommand command = new SDACommand())
|
|
{
|
|
command.Connection = UserContext.Instance.SDAConnection;
|
|
command.Transaction = UserContext.Instance.SDATransaction;
|
|
command.CommandType = CommandType.StoredProcedure;
|
|
command.CommandText = "uspGetAlkalmazottFeladatEllatasiHelyId";
|
|
|
|
command.Parameters.Add("pTanevId", tanevId);
|
|
command.Parameters.Add("pAlkalmazottId", alkalmazottId);
|
|
|
|
var result = command.ExecuteScalar();
|
|
if (result != null && int.TryParse(result.ToString(), out int id))
|
|
{
|
|
return id;
|
|
}
|
|
|
|
return null;
|
|
}
|
|
}
|
|
|
|
/// INFO @DevKornel: Mobil használja
|
|
public DataSet GetAlkalmazottak(int tanevId, List<int> felhasznaloIds = null)
|
|
{
|
|
var felhasznaloIdsString = SqlLogic.ParseListToParameter(felhasznaloIds).ToString();
|
|
|
|
string commandText = @"
|
|
SELECT
|
|
f.ID
|
|
,f.C_NYOMTATASINEV AS Nev
|
|
,f.C_OKTATASIAZONOSITO AS OktatasiAzonosito
|
|
,IIF(f.C_NEVSORREND = 'T', f.C_UTONEV + ' ' + f.C_VEZETEKNEV, f.C_VEZETEKNEV + ' ' + f.C_UTONEV) AS NevElotagNelkul
|
|
FROM T_ALKALMAZOTT_OSSZES a
|
|
INNER JOIN T_FELHASZNALO_OSSZES f ON f.ID = a.ID AND f.C_TANEVID = a.C_ALTANEVID AND f.TOROLT = 'F'
|
|
WHERE a.TOROLT = 'F' AND a.C_ALTANEVID = :pTanevId
|
|
" + (!string.IsNullOrWhiteSpace(felhasznaloIdsString) ? $@" AND a.ID IN({ felhasznaloIdsString }) " : "") + @"
|
|
ORDER BY NevElotagNelkul
|
|
";
|
|
|
|
var param = new List<CommandParameter>
|
|
{
|
|
new CommandParameter("pTanevId", tanevId),
|
|
};
|
|
|
|
return GetData(commandText, param);
|
|
}
|
|
|
|
public DataSet GetAllAlkalmazottByIntezmeny(int intezmenyId, int tanevId)
|
|
{
|
|
using (var sdaConnection = DataUtil.GetReadOnlyConnection(UserContext.Instance.IntezmenyAzonosito))
|
|
{
|
|
sdaConnection.Open();
|
|
|
|
using (var command = sdaConnection.CreateCommand())
|
|
{
|
|
command.CommandType = CommandType.StoredProcedure;
|
|
command.CommandText = "uspGetAllAlkalmazottByIntezmeny";
|
|
|
|
command.Parameters.Add("pIntezmenyId", SDADBType.Int).Value = intezmenyId;
|
|
if (tanevId.IsEntityId())
|
|
{
|
|
command.Parameters.Add("pTanevId", SDADBType.Int).Value = tanevId;
|
|
}
|
|
|
|
var ds = new DataSet();
|
|
|
|
using (var adapter = new SDADataAdapter())
|
|
{
|
|
adapter.SelectCommand = command;
|
|
adapter.Fill(ds);
|
|
}
|
|
|
|
return ds;
|
|
}
|
|
}
|
|
}
|
|
|
|
public DataSet GetIskolaor(int tanevId)
|
|
{
|
|
using (SDACommand command = new SDACommand())
|
|
{
|
|
command.Connection = UserContext.Instance.SDAConnection;
|
|
command.Transaction = UserContext.Instance.SDATransaction;
|
|
command.CommandType = CommandType.StoredProcedure;
|
|
command.CommandText = "uspGetIskolaor";
|
|
|
|
command.Parameters.Add("pMunkakorTipusId", (int)MunkakorTipusEnum.iskolaor);
|
|
command.Parameters.Add("pTanevId", tanevId);
|
|
|
|
var dataSet = new DataSet();
|
|
using (var sdaDataAdapter = new SDADataAdapter())
|
|
{
|
|
sdaDataAdapter.SelectCommand = command;
|
|
sdaDataAdapter.Fill(dataSet);
|
|
}
|
|
|
|
return dataSet;
|
|
}
|
|
}
|
|
|
|
public DataSet GetAlkalmazottEszkozei(int userId, int tanevId, int intezmenyId)
|
|
{
|
|
using (SDACommand command = new SDACommand())
|
|
{
|
|
command.Connection = UserContext.Instance.SDAConnection;
|
|
command.Transaction = UserContext.Instance.SDATransaction;
|
|
command.CommandType = CommandType.StoredProcedure;
|
|
command.CommandText = "uspGetAlkalmazottEszkozei";
|
|
|
|
command.Parameters.Add("pUserId", userId);
|
|
command.Parameters.Add("pTanevId", tanevId);
|
|
command.Parameters.Add("pIntezmenyId", intezmenyId);
|
|
|
|
var dataSet = new DataSet();
|
|
using (var sdaDataAdapter = new SDADataAdapter())
|
|
{
|
|
sdaDataAdapter.SelectCommand = command;
|
|
sdaDataAdapter.Fill(dataSet);
|
|
}
|
|
|
|
SetDNAME(dataSet.Tables[0], "Tipus");
|
|
|
|
return dataSet;
|
|
}
|
|
}
|
|
|
|
public DataSet GetPedagogusIKTAdatszolgaltatasForGrid(int intezmenyId, 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 = "sp_GetPedagogusIKTAdatszolgaltatasForGrid";
|
|
|
|
command.Parameters.Add("pIntezmenyId", intezmenyId);
|
|
command.Parameters.Add("pTanevId", tanevId);
|
|
|
|
using (var adapter = new SDADataAdapter())
|
|
{
|
|
adapter.SelectCommand = command;
|
|
adapter.Fill(ds);
|
|
}
|
|
}
|
|
|
|
SetDNAME(ds.Tables[0], "IKTKompetenciaSzint,IKTEszkozhasznalatModja,ElsodlegesIKTEszkoz");
|
|
DataTable dt = SortingAndPaging(ds.Tables[0], GridParameters);
|
|
|
|
return dt.AsDataSet();
|
|
}
|
|
|
|
public int? GetAdminID(int tanevId, int intezmenyId)
|
|
{
|
|
using (SDACommand command = new SDACommand())
|
|
{
|
|
command.Connection = UserContext.Instance.SDAConnection;
|
|
command.Transaction = UserContext.Instance.SDATransaction;
|
|
command.CommandType = CommandType.StoredProcedure;
|
|
command.CommandText = "uspGetAdminId";
|
|
|
|
command.Parameters.Add("pTanevId", tanevId);
|
|
command.Parameters.Add("pIntezmenyId", intezmenyId);
|
|
command.Parameters.Add("pAdminUsername", Core.Constants.SpecialUserName.KretaAdminisztrator);
|
|
|
|
var result = command.ExecuteScalar();
|
|
if (result != null && int.TryParse(result.ToString(), out int id))
|
|
{
|
|
return id;
|
|
}
|
|
|
|
return null;
|
|
}
|
|
}
|
|
|
|
public int GetBetoltetlenAllashelyekSzama(int tanevId, int intezmenyId)
|
|
{
|
|
using (var command = new SDACommand())
|
|
{
|
|
command.Connection = UserContext.Instance.SDAConnection;
|
|
command.Transaction = UserContext.Instance.SDATransaction;
|
|
command.CommandType = CommandType.StoredProcedure;
|
|
command.CommandText = "uspGetBetoltetlenAllashelyekSzama";
|
|
|
|
command.Parameters.Add("pTanevId", tanevId);
|
|
command.Parameters.Add("pIntezmenyId", intezmenyId);
|
|
|
|
var result = command.ExecuteScalar();
|
|
return int.Parse(result.ToString());
|
|
}
|
|
}
|
|
|
|
public int GetSpecialisAlkalmazottakSzama(int tanevId, string nevEloTag)
|
|
{
|
|
using (var command = new SDACommand())
|
|
{
|
|
command.Connection = UserContext.Instance.SDAConnection;
|
|
command.Transaction = UserContext.Instance.SDATransaction;
|
|
|
|
command.Parameters.Add("pTanevId", tanevId);
|
|
|
|
command.CommandText = @"
|
|
SELECT COUNT(1)
|
|
FROM T_FELHASZNALO_OSSZES
|
|
WHERE C_NYOMTATASINEV LIKE '[[]" + nevEloTag + @" %' AND C_TANEVID = :pTanevId AND TOROLT = 'F'
|
|
";
|
|
|
|
var result = command.ExecuteScalar();
|
|
return int.Parse(result.ToString());
|
|
}
|
|
}
|
|
|
|
public DataSet GetSpecialisAlkalmazottakNeve(int tanevId, string nevEloTag)
|
|
{
|
|
var commandParams = new List<CommandParameter>
|
|
{
|
|
new CommandParameter("pTanevId", tanevId)
|
|
};
|
|
|
|
var commandText = @"
|
|
SELECT C_NYOMTATASINEV
|
|
FROM T_FELHASZNALO_OSSZES
|
|
WHERE C_NYOMTATASINEV LIKE '[[]" + nevEloTag + @" %' AND C_TANEVID = :pTanevId AND TOROLT = 'F'
|
|
";
|
|
|
|
return GetData(commandText, commandParams);
|
|
}
|
|
|
|
public DataSet SearchAlkalmazott(AlkalmazottSearchPco pco, int intezmenyId, int tanevId, string intezmenyAzonosito)
|
|
{
|
|
const string emailAlias = "T_EMAIL_OSSZES";
|
|
const string felhasznalobelepesAlias = "T_FELHASZNALOBELEPES_OSSZES";
|
|
const string felhasznaloAlias = "T_FELHASZNALO_OSSZES";
|
|
const string munkaugyiAlias = "munkaugyiadat";
|
|
const string foglalkozasAlias = "T_FOGLALKOZAS_OSSZES";
|
|
const string alkalmazottAlias = "T_ALKALMAZOTT_OSSZES";
|
|
const string munkakorTipusAlias = "T_MUNKAKORTIPUS_OSSZES";
|
|
const string munkaugyiAdatokAlias = "munkaugyiAdatok";
|
|
const string szervezetAlias = "szervezetAdatok";
|
|
|
|
var paramList = new List<CommandParameter>
|
|
{
|
|
new CommandParameter("pIntezmenyId", intezmenyId),
|
|
new CommandParameter("pTanevId", tanevId),
|
|
new CommandParameter("pSzervezetId", (object)pco.SzervezetId ?? DBNull.Value),
|
|
new CommandParameter("pAdatokHalmaza", (int)SzervezetAdatokHalmazaEnum.SzervezetEsAlSzervezetek),
|
|
};
|
|
|
|
var commandText = new StringBuilder($@"
|
|
SELECT DISTINCT
|
|
{felhasznaloAlias}.ID AS Id
|
|
,{felhasznaloAlias}.C_ELOTAG AS FelhasznaloElotag
|
|
,{felhasznaloAlias}.C_VEZETEKNEV AS FelhasznaloVezeteknev
|
|
,{felhasznaloAlias}.C_UTONEV AS FelhasznaloKeresztnev
|
|
,{felhasznaloAlias}.C_NEVSORREND AS FelhasznaloNevsorrend
|
|
,{felhasznaloAlias}.C_SZULETESIHELY AS FelhasznaloSzuletesiHely,
|
|
{felhasznaloAlias}.C_SZULETESIDATUM AS FelhasznaloSzuletesiIdo,
|
|
{felhasznaloAlias}.C_OKTATASIAZONOSITO AS FelhasznaloOktatasiAzonosito,
|
|
{felhasznalobelepesAlias}.C_BEJELENTKEZESINEV AS BejelentkezesiNev,
|
|
{szervezetAlias}.ID AS SzervezetId,
|
|
{szervezetAlias}.C_NEV AS SzervezetNev,
|
|
{szervezetAlias}.C_AZONOSITO AS SzervezetAzonosito,
|
|
{felhasznaloAlias}.CREATOR AS RogzitoId,
|
|
T_TANEV_OSSZES.C_NEV AS TanevNev,
|
|
[KR_{intezmenyAzonosito}_Schema].fnGetDokumentumDatumFormatum({felhasznaloAlias}.C_SZULETESIDATUM) AS SzuletesiDatumFormazott
|
|
FROM
|
|
T_FELHASZNALO_OSSZES
|
|
INNER JOIN
|
|
T_ALKALMAZOTT_OSSZES ON {alkalmazottAlias}.ID = {felhasznaloAlias}.ID AND {alkalmazottAlias}.TOROLT = 'F' AND {alkalmazottAlias}.C_ALTANEVID = {felhasznaloAlias}.C_TANEVID
|
|
LEFT JOIN
|
|
T_TANEV_OSSZES ON T_TANEV_OSSZES.ID = {alkalmazottAlias}.C_ALTANEVID AND T_TANEV_OSSZES.TOROLT = 'F'
|
|
LEFT JOIN
|
|
T_FELHASZNALOBELEPES_OSSZES ON {felhasznalobelepesAlias}.C_FELHASZNALOID = {felhasznaloAlias}.ID AND {felhasznalobelepesAlias}.TOROLT = 'F' AND {felhasznalobelepesAlias}.C_TANEVID = {felhasznaloAlias}.C_TANEVID
|
|
LEFT JOIN
|
|
T_FOGLALKOZAS_OSSZES ON {foglalkozasAlias}.C_TANARID = {felhasznaloAlias}.ID AND {foglalkozasAlias}.TOROLT = 'F' AND {foglalkozasAlias}.C_TANEVID = {felhasznaloAlias}.C_TANEVID
|
|
");
|
|
|
|
if (pco.FeladatEllatasiHelyId.HasValue || pco.SearchPedagogusFokozat.HasValue || !string.IsNullOrWhiteSpace(pco.SearchNyugdijas)
|
|
|| (pco.NABesorolasiFokozat == true) || (pco.NincsMunkakor == true) || (pco.NincsFoglalkoztatasModja == true)
|
|
|| (pco.NemCsillagosMunkakor == true) || (pco.NemBelepettAlkalmazottKetHet == true) || (pco.NincsBelepesAlkalmazott == true)
|
|
|| (pco.NincsEmail == true) || (pco.AktivAlkalmazottak == true)
|
|
|| (pco.IsTanarAlkalmazottMunkaKor == true))
|
|
{
|
|
commandText.Append($@"
|
|
INNER JOIN T_MUNKAUGYIADATOK_OSSZES {munkaugyiAlias} ON {munkaugyiAlias}.C_ALKALMAZOTTID = {alkalmazottAlias}.ID AND
|
|
ISNULL({munkaugyiAlias}.C_ALKALMAZASKEZDETE, GETDATE()) <= GETDATE()
|
|
AND ISNULL({munkaugyiAlias}.C_ALKALMAZASMEGSZUNESE, GETDATE()) >= GETDATE() AND {munkaugyiAlias}.C_TANEVID = :pTanevId AND {munkaugyiAlias}.TOROLT = 'F'
|
|
LEFT JOIN T_MUNKAKORTIPUS_OSSZES ON {munkakorTipusAlias}.ID = {munkaugyiAlias}.C_MUNKAKORTIPUSA AND {munkakorTipusAlias}.TOROLT = 'F' AND {munkakorTipusAlias}.C_ALTANEVID = {felhasznaloAlias}.C_TANEVID
|
|
");
|
|
}
|
|
else
|
|
{
|
|
commandText.Append($@"INNER JOIN
|
|
T_MUNKAUGYIADATOK_OSSZES {munkaugyiAlias} ON {munkaugyiAlias}.C_ALKALMAZOTTID = {felhasznaloAlias}.ID AND {munkaugyiAlias}.C_TANEVID = {felhasznaloAlias}.C_TANEVID AND {munkaugyiAlias}.TOROLT = 'F'
|
|
");
|
|
}
|
|
if (pco.NincsEmail == true)
|
|
{
|
|
commandText.Append($@"
|
|
LEFT JOIN
|
|
T_EMAIL_OSSZES ON {emailAlias}.C_FELHASZNALOID = {felhasznaloAlias}.ID AND {emailAlias}.TOROLT = 'F' AND {emailAlias}.C_ALAPERTELMEZETT = 'T' AND {emailAlias}.C_TANEVID = :pTanevId
|
|
");
|
|
}
|
|
|
|
if (pco.NincsAlkalmazottSZTSZ == true)
|
|
{
|
|
commandText.Append($@"
|
|
LEFT JOIN T_MUNKAUGYIADATOK_OSSZES {munkaugyiAdatokAlias} ON {munkaugyiAdatokAlias}.C_ALKALMAZOTTID = {felhasznaloAlias}.ID
|
|
AND {munkaugyiAdatokAlias}.C_TANEVID = {felhasznaloAlias}.C_TANEVID
|
|
AND {munkaugyiAdatokAlias}.TOROLT = 'F'
|
|
");
|
|
}
|
|
|
|
commandText.Append($@"
|
|
" + (pco.IsFromSzervezet ? " INNER" : " LEFT") + $@" JOIN T_SZERVEZET_OSSZES {szervezetAlias} ON {munkaugyiAlias}.C_SZERVEZETID = {szervezetAlias}.ID
|
|
AND {szervezetAlias}.C_TANEVID = {munkaugyiAlias}.C_TANEVID
|
|
AND {szervezetAlias}.TOROLT = 'F'
|
|
" + (pco.IsFromSzervezet ? " INNER" : " LEFT") + $@" JOIN (
|
|
SELECT * FROM fnGetLathatoSzervezetek(:pIntezmenyId, :pTanevId, :pSzervezetId, :pAdatokHalmaza)
|
|
) lathatokSzervezetek ON lathatokSzervezetek.ID = {szervezetAlias}.ID
|
|
|
|
");
|
|
|
|
// whereClause:
|
|
commandText.Append($@"
|
|
WHERE
|
|
{felhasznaloAlias}.TOROLT = 'F' AND {felhasznaloAlias}.C_TANEVID = :pTanevId
|
|
");
|
|
|
|
if (pco.NincsAlkalmazottSZTSZ == true)
|
|
{
|
|
commandText.Append($@"
|
|
AND {munkaugyiAdatokAlias}.C_BETOLTETLENALLASHELY = 'F'
|
|
AND {munkaugyiAdatokAlias}.C_MUNKAKORTIPUSA NOT IN (6709, 6710)
|
|
AND {felhasznaloAlias}.C_NYOMTATASINEV NOT LIKE '[[]%'
|
|
");
|
|
}
|
|
|
|
if (pco.IsTanarAlkalmazottMunkaKor == true)
|
|
{
|
|
commandText.Append($@"
|
|
AND {munkakorTipusAlias}.C_ALKALMAZOTTMUNKAKORTIPUSID = {(int)AlkalmazottMunkaKorTipusEnum.tanar}
|
|
");
|
|
}
|
|
|
|
commandText.Append(pco.ToWhereClause(paramList, emailAlias, felhasznalobelepesAlias, felhasznaloAlias, munkaugyiAlias, foglalkozasAlias, alkalmazottAlias, szervezetAlias));
|
|
return GetReadOnlyData(commandText.ToString(), paramList);
|
|
}
|
|
|
|
public DataSet GetAlkalmazottakAlapErtekekkel(int pTanevId, string pSzuletesiHely, DateTime pSzuletesiDatum, string pNevKulso, string pNevKulsoGyak, string pNevHittanOktat, string pNevBetoltetlen)
|
|
{
|
|
using (var sdaCommand = new SDACommand())
|
|
{
|
|
sdaCommand.Connection = UserContext.Instance.SDAConnection;
|
|
sdaCommand.Transaction = UserContext.Instance.SDATransaction;
|
|
sdaCommand.CommandType = CommandType.StoredProcedure;
|
|
|
|
sdaCommand.CommandText = "uspGetAlkalmazottakAlapErtekekkel";
|
|
|
|
sdaCommand.Parameters.Add("pTanevId", pTanevId);
|
|
sdaCommand.Parameters.Add("pSzuletesiHely", pSzuletesiHely);
|
|
sdaCommand.Parameters.Add("pSzuletesiDatum", pSzuletesiDatum);
|
|
sdaCommand.Parameters.Add("pNevKulso", pNevKulso);
|
|
sdaCommand.Parameters.Add("pNevKulsoGyak", pNevKulsoGyak);
|
|
sdaCommand.Parameters.Add("pNevHittanOktat", pNevHittanOktat);
|
|
sdaCommand.Parameters.Add("pNevBetoltetlen", pNevBetoltetlen);
|
|
|
|
var dataSet = new DataSet();
|
|
using (var adapter = new SDADataAdapter())
|
|
{
|
|
adapter.SelectCommand = sdaCommand;
|
|
adapter.Fill(dataSet);
|
|
}
|
|
|
|
return dataSet;
|
|
}
|
|
}
|
|
|
|
public IAlkalmazott Get()
|
|
{
|
|
return Alkalmazott.GiveAnInstance();
|
|
}
|
|
|
|
public IAlkalmazott Get(int id)
|
|
{
|
|
var entity = Alkalmazott.GiveAnInstance();
|
|
entity.LoadByID(id);
|
|
return entity;
|
|
}
|
|
|
|
public void Update(IAlkalmazott dto, bool needFollowUp = true)
|
|
{
|
|
var entity = dto as Alkalmazott;
|
|
|
|
entity.FullUpdate();
|
|
if (needFollowUp)
|
|
{
|
|
FollowUp(entity);
|
|
}
|
|
DalHelper.Commit();
|
|
}
|
|
|
|
public void FollowUpById(int id)
|
|
{
|
|
var entity = Alkalmazott.GiveAnInstance();
|
|
entity.LoadByID(id);
|
|
FollowUp(entity);
|
|
DalHelper.Commit();
|
|
}
|
|
|
|
public void Insert(IAlkalmazott dto, bool kellFollowUp = true)
|
|
{
|
|
var entity = dto as Alkalmazott;
|
|
|
|
entity.Insert(true);
|
|
dto.ID = entity.ID;
|
|
|
|
if (kellFollowUp)
|
|
{
|
|
FollowUp(entity);
|
|
}
|
|
|
|
DalHelper.Felhasznalo().UpdateAllEgyediAzonosito();
|
|
DalHelper.Commit();
|
|
}
|
|
|
|
public void Delete(int id)
|
|
{
|
|
var entity = this.Get(id) as Alkalmazott;
|
|
entity.TermekFelelose.RemoveAll();
|
|
entity.FelelosEszkoz.RemoveAll();
|
|
entity.OldalLatogatottsag.DeleteAll();
|
|
entity.PedagogusIKTAdatszolgaltatas.DeleteAll();
|
|
entity.TovabbiMunkaugyiAdatok.DeleteAll();
|
|
entity.eLearning.DeleteAll();
|
|
entity.ElearningFelhasznalo.DeleteAll();
|
|
entity.UserProfile.DeleteAll();
|
|
entity.KIRKontener.DeleteAll();
|
|
entity.FelhasznaloBelepes.DeleteAll();
|
|
entity.FelhasznaloBelepesTortenet.DeleteAll();
|
|
entity.DashboardUzenetFelhasznalo.DeleteAll();
|
|
entity.TanarEletpalyamodellje.DeleteAll();
|
|
entity.Cim.DeleteAll();
|
|
entity.Telefon.DeleteAll();
|
|
entity.Email.DeleteAll();
|
|
entity.MunkaugyiAdatok.DeleteAll();
|
|
entity.SAPTavollet.DeleteAll();
|
|
entity.TovabbkepzesAdatok.DeleteAll();
|
|
entity.Diakolimpiak.DeleteAll();
|
|
entity.EszkozIgenyles.DeleteAll();
|
|
DeleteKKVegzettsegek(entity);
|
|
|
|
var vegzettsegDal = DalHelper.VegzettsegDal();
|
|
entity.Vegzettseg.ToList().ForEach(vegzettseg =>
|
|
{
|
|
vegzettsegDal.Delete(vegzettseg);
|
|
});
|
|
|
|
entity.Tanmenete.RemoveAll();
|
|
|
|
entity.TanuloEsemeny.ToList().ForEach(te =>
|
|
{
|
|
te.Tanulo.RemoveAll();
|
|
te.OsztalyCsoport.RemoveAll();
|
|
te.Delete();
|
|
});
|
|
|
|
int adminUserId = DalHelper.Felhasznalo().GetFelhasznaloIdByUserName("Admin", entity.IntezmenyId, DalHelper.TanevDal().GetAktivTanevId());
|
|
|
|
foreach (var ora in entity.OrarendiOrai)
|
|
{
|
|
ora.OraTulajdonosId = adminUserId;
|
|
ora.UpdateAssociations();
|
|
}
|
|
|
|
List<Szerepkor> list = new List<Szerepkor>(entity.Szerepkor);
|
|
|
|
foreach (Szerepkor item in list)
|
|
{
|
|
entity.RemoveFromSzerepkor(item);
|
|
}
|
|
|
|
entity.Delete(true);
|
|
|
|
var followUpAlkalmazott = GetFollowupAlkalmazott(entity.ID);
|
|
if (followUpAlkalmazott.IsEntityId())
|
|
{
|
|
Delete(followUpAlkalmazott.Value);
|
|
}
|
|
|
|
DalHelper.Commit();
|
|
}
|
|
|
|
public string GetAlkalmazottNevById(int id, int tanevId, int intezmenyId)
|
|
{
|
|
using (var command = new SDACommand())
|
|
{
|
|
command.Connection = UserContext.Instance.SDAConnection;
|
|
command.Transaction = UserContext.Instance.SDATransaction;
|
|
command.CommandType = CommandType.StoredProcedure;
|
|
command.CommandText = "uspGetAlkalmazottNevById";
|
|
|
|
command.Parameters.Add("pUserId", id);
|
|
command.Parameters.Add("pTanevId", tanevId);
|
|
command.Parameters.Add("pIntezmenyId", intezmenyId);
|
|
|
|
var nev = command.ExecuteScalar();
|
|
return nev?.ToString() ?? string.Empty;
|
|
}
|
|
}
|
|
|
|
public void FollowUpAlkalmazott(int intezmenyId, int aktTanevId, int kovetkezoTanevId, int alkalmazottId)
|
|
{
|
|
using (SDACommand command = new SDACommand())
|
|
{
|
|
command.Connection = UserContext.Instance.SDAConnection;
|
|
command.Transaction = UserContext.Instance.SDATransaction;
|
|
|
|
command.CommandType = CommandType.StoredProcedure;
|
|
command.CommandText = "uspFollowUpAlkalmazott";
|
|
|
|
command.Parameters.Add("intezmenyId", intezmenyId);
|
|
command.Parameters.Add("aktTanevId", aktTanevId);
|
|
command.Parameters.Add("kovetkezoTanevId", kovetkezoTanevId);
|
|
command.Parameters.Add("alkalmazottId", alkalmazottId);
|
|
|
|
command.ExecuteNonQuery();
|
|
}
|
|
DalHelper.Commit();
|
|
}
|
|
|
|
public void FollowUpAlkalmazottVegzettseg(int intezmenyId, int aktTanevId, int kovetkezoTanevId, int vegzettsegId)
|
|
{
|
|
using (SDACommand command = new SDACommand())
|
|
{
|
|
command.Connection = UserContext.Instance.SDAConnection;
|
|
command.Transaction = UserContext.Instance.SDATransaction;
|
|
|
|
command.CommandType = CommandType.StoredProcedure;
|
|
command.CommandText = "uspFollowUpAlkalmazottVegzettseg";
|
|
|
|
command.Parameters.Add("intezmenyId", intezmenyId);
|
|
command.Parameters.Add("aktTanevId", aktTanevId);
|
|
command.Parameters.Add("kovetkezoTanevId", kovetkezoTanevId);
|
|
command.Parameters.Add("vegzettsegId", vegzettsegId);
|
|
|
|
command.ExecuteNonQuery();
|
|
}
|
|
DalHelper.Commit();
|
|
}
|
|
|
|
public void FollowUpAlkalmazottVegzettsegKK(int intezmenyId, int aktTanevId, int kovetkezoTanevId, int alkalmazottId)
|
|
{
|
|
using (SDACommand command = new SDACommand())
|
|
{
|
|
command.Connection = UserContext.Instance.SDAConnection;
|
|
command.Transaction = UserContext.Instance.SDATransaction;
|
|
|
|
command.CommandType = CommandType.StoredProcedure;
|
|
command.CommandText = "uspFollowUpAlkalmazottVegzettsegKK";
|
|
|
|
command.Parameters.Add("intezmenyId", intezmenyId);
|
|
command.Parameters.Add("aktTanevId", aktTanevId);
|
|
command.Parameters.Add("kovetkezoTanevId", kovetkezoTanevId);
|
|
command.Parameters.Add("alkalmazottId", alkalmazottId);
|
|
|
|
command.ExecuteNonQuery();
|
|
}
|
|
DalHelper.Commit();
|
|
}
|
|
|
|
public void FollowUpAlkalmazottPEP(int intezmenyId, int aktTanevId, int kovetkezoTanevId, int PEPId)
|
|
{
|
|
using (SDACommand command = new SDACommand())
|
|
{
|
|
command.Connection = UserContext.Instance.SDAConnection;
|
|
command.Transaction = UserContext.Instance.SDATransaction;
|
|
|
|
command.CommandType = CommandType.StoredProcedure;
|
|
command.CommandText = "uspFollowUpAlkalmazottPEP";
|
|
|
|
command.Parameters.Add("intezmenyId", intezmenyId);
|
|
command.Parameters.Add("aktTanevId", aktTanevId);
|
|
command.Parameters.Add("kovetkezoTanevId", kovetkezoTanevId);
|
|
command.Parameters.Add("PEPId", PEPId);
|
|
|
|
command.ExecuteNonQuery();
|
|
}
|
|
DalHelper.Commit();
|
|
}
|
|
|
|
public void FollowUpAlkalmazottTovabbiMunkaugyiAdatok(int intezmenyId, int aktTanevId, int kovetkezoTanevId, int tovabbiMunkaugyiAdatId)
|
|
{
|
|
using (SDACommand command = new SDACommand())
|
|
{
|
|
command.Connection = UserContext.Instance.SDAConnection;
|
|
command.Transaction = UserContext.Instance.SDATransaction;
|
|
|
|
command.CommandType = CommandType.StoredProcedure;
|
|
command.CommandText = "uspFollowUpAlkalmazottTovabbiMunkaugy";
|
|
|
|
command.Parameters.Add("intezmenyId", intezmenyId);
|
|
command.Parameters.Add("aktTanevId", aktTanevId);
|
|
command.Parameters.Add("kovetkezoTanevId", kovetkezoTanevId);
|
|
command.Parameters.Add("tovabbiMunkaugyiAdatId", tovabbiMunkaugyiAdatId);
|
|
|
|
command.ExecuteNonQuery();
|
|
}
|
|
DalHelper.Commit();
|
|
}
|
|
|
|
public DataSet GetInfoForLoginAlkalmazott(int alkalmazottId, int tanevId)
|
|
{
|
|
using (var command = new SDACommand())
|
|
{
|
|
command.Connection = UserContext.Instance.SDAConnection;
|
|
command.Transaction = UserContext.Instance.SDATransaction;
|
|
command.CommandType = CommandType.StoredProcedure;
|
|
command.CommandText = "uspGetInfoForLoginAlkalmazott";
|
|
|
|
command.Parameters.Add("pUserId", alkalmazottId);
|
|
command.Parameters.Add("pTanevId", tanevId);
|
|
|
|
var dataSet = new DataSet();
|
|
using (var sdaDataAdapter = new SDADataAdapter())
|
|
{
|
|
sdaDataAdapter.SelectCommand = command;
|
|
sdaDataAdapter.Fill(dataSet);
|
|
}
|
|
|
|
return dataSet;
|
|
}
|
|
}
|
|
|
|
public bool IsKozossegiSzolgalatKezelo(int alkalmazottId, int tanevId)
|
|
{
|
|
using (SDACommand command = new SDACommand())
|
|
{
|
|
command.Connection = UserContext.Instance.SDAConnection;
|
|
command.Transaction = UserContext.Instance.SDATransaction;
|
|
|
|
command.CommandType = CommandType.StoredProcedure;
|
|
command.CommandText = "sp_IsKozossegiSzolgalatKezelo";
|
|
|
|
command.Parameters.Add("pAlkalmazottId", alkalmazottId);
|
|
command.Parameters.Add("pTanevId", tanevId);
|
|
|
|
return Convert.ToBoolean(command.ExecuteScalar());
|
|
}
|
|
}
|
|
public bool IsPortasMunkakor(int alkalmazottId, int tanevId)
|
|
{
|
|
using (SDACommand command = new SDACommand())
|
|
{
|
|
command.Connection = UserContext.Instance.SDAConnection;
|
|
command.Transaction = UserContext.Instance.SDATransaction;
|
|
|
|
command.CommandType = CommandType.StoredProcedure;
|
|
command.CommandText = "uspGetIsPortasMunkakor";
|
|
|
|
command.Parameters.Add("pAlkalmazottId", alkalmazottId);
|
|
command.Parameters.Add("pTanevId", tanevId);
|
|
command.Parameters.Add("pPortasMunkakorTipusId", (int)MunkakorTipusEnum.portas);
|
|
|
|
return Convert.ToBoolean(command.ExecuteScalar());
|
|
}
|
|
}
|
|
|
|
public bool IsLEPKezelo(int alkalmazottId, int tanevId)
|
|
{
|
|
using (SDACommand command = new SDACommand())
|
|
{
|
|
command.Connection = UserContext.Instance.SDAConnection;
|
|
command.Transaction = UserContext.Instance.SDATransaction;
|
|
|
|
command.CommandType = CommandType.StoredProcedure;
|
|
command.CommandText = "uspIsLEPKezelo";
|
|
|
|
command.Parameters.Add("pAlkalmazottId", alkalmazottId);
|
|
command.Parameters.Add("pTanevId", tanevId);
|
|
|
|
return Convert.ToBoolean(command.ExecuteScalar());
|
|
}
|
|
}
|
|
|
|
public bool AlkalmazottHasMentorCsoport(int alkalmazottId, int tanevId, int mentorCsoportId)
|
|
{
|
|
using (var command = UserContext.Instance.SDAConnection.CreateCommand())
|
|
{
|
|
command.Transaction = UserContext.Instance.SDATransaction;
|
|
command.CommandText = @"
|
|
SELECT COUNT(1)
|
|
FROM T_CSOPORT_OSSZES
|
|
WHERE C_ALTANEVID = :pTanevId AND C_TIPUSA = :pMentorCsoportId
|
|
AND TOROLT = 'F' AND C_CSOPORTVEZETOID = :pAlkalmazottId
|
|
";
|
|
|
|
command.Parameters.Add("pAlkalmazottId", alkalmazottId);
|
|
command.Parameters.Add("pMentorCsoportId", mentorCsoportId);
|
|
command.Parameters.Add("pTanevId", tanevId);
|
|
var result = command.ExecuteScalar();
|
|
return int.Parse(result.ToString()) > 0;
|
|
}
|
|
}
|
|
|
|
public bool IsAlkalmazottPedagogus(int intezmenyId, int tanevId, int alkalmazottId)
|
|
{
|
|
using (SDACommand command = new SDACommand())
|
|
{
|
|
command.Connection = UserContext.Instance.SDAConnection;
|
|
command.Transaction = UserContext.Instance.SDATransaction;
|
|
|
|
command.CommandType = CommandType.StoredProcedure;
|
|
command.CommandText = "sp_IsAlkalmazottPedagogus";
|
|
|
|
command.Parameters.Add("pIntezmenyId", intezmenyId);
|
|
command.Parameters.Add("pTanevId", tanevId);
|
|
command.Parameters.Add("pAlkalmazottId", alkalmazottId);
|
|
command.Parameters.Add("pBesorolasiFokozatTipusNa", (int)BesorolasiFokozatTipusEnum.na);
|
|
|
|
return Convert.ToBoolean(command.ExecuteScalar());
|
|
}
|
|
}
|
|
|
|
public void FollowUpAlkalmazottCim(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 = "uspFollowUpFelhasznaloCim";
|
|
|
|
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 void FollowUpAlkalmazottEmail(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 = "uspFollowUpFelhasznaloEmail";
|
|
|
|
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 void FollowUpAlkalmazottTelefon(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();
|
|
}
|
|
|
|
#region Export
|
|
|
|
public DataSet GetAlkalmazottakLakcimmelExcelExport(int tanevId, AlkalmazottSearchPco pco, bool isShowId = false)
|
|
{
|
|
const string emailAlias = "email_filter";
|
|
const string felhasznalobelepesAlias = "T_FELHASZNALOBELEPES_OSSZES";
|
|
const string felhasznaloAlias = "felhasznalo";
|
|
const string munkaugyiAlias = "munkaugyiadat";
|
|
const string foglalkozasAlias = "T_FOGLALKOZAS_OSSZES";
|
|
const string alkalmazottAlias = "alkalmazott";
|
|
const string mukodesihelyAlias = "mukodesihely";
|
|
const string szervezetAlias = "szervezetAdatok";
|
|
|
|
var parameters = new List<CommandParameter> { new CommandParameter("pTanevId", tanevId) };
|
|
|
|
string munkaugyiAdatokJoin = string.Empty;
|
|
// Szűrés miatti join-ok:
|
|
if (pco.FeladatEllatasiHelyId.HasValue || pco.SearchPedagogusFokozat.HasValue || !string.IsNullOrWhiteSpace(pco.SearchNyugdijas)
|
|
|| (pco.NABesorolasiFokozat == true) || (pco.NincsMunkakor == true) || (pco.NincsFoglalkoztatasModja == true)
|
|
|| (pco.NemCsillagosMunkakor == true) || (pco.NemBelepettAlkalmazottKetHet == true) || (pco.NincsBelepesAlkalmazott == true)
|
|
|| (pco.NincsEmail == true) || (pco.AktivAlkalmazottak == true))
|
|
{
|
|
munkaugyiAdatokJoin = $@"
|
|
INNER JOIN T_MUNKAUGYIADATOK_OSSZES {munkaugyiAlias} ON {munkaugyiAlias}.C_ALKALMAZOTTID = {alkalmazottAlias}.ID AND
|
|
ISNULL({munkaugyiAlias}.C_ALKALMAZASKEZDETE, GETDATE()) <= GETDATE()
|
|
AND ISNULL({munkaugyiAlias}.C_ALKALMAZASMEGSZUNESE, GETDATE()) >= GETDATE() AND {munkaugyiAlias}.C_TANEVID = :pTanevId AND {munkaugyiAlias}.TOROLT = 'F'
|
|
";
|
|
}
|
|
else
|
|
{
|
|
munkaugyiAdatokJoin = $@"INNER JOIN
|
|
T_MUNKAUGYIADATOK_OSSZES {munkaugyiAlias} ON {munkaugyiAlias}.C_ALKALMAZOTTID = {felhasznaloAlias}.ID AND {munkaugyiAlias}.C_TANEVID = {felhasznaloAlias}.C_TANEVID AND {munkaugyiAlias}.TOROLT = 'F'";
|
|
}
|
|
|
|
var showId = isShowId ? $",{felhasznaloAlias}.ID AS FelhasznaloId" : string.Empty;
|
|
|
|
var commandText = new StringBuilder($@"
|
|
SELECT DISTINCT
|
|
{felhasznaloAlias}.C_ELOTAG AS '{FelhasznalokResource.Elotag}'
|
|
,{felhasznaloAlias}.C_VEZETEKNEV AS '{FelhasznalokResource.Vezetekneve}'
|
|
,{felhasznaloAlias}.C_UTONEV AS '{FelhasznalokResource.Utoneve}'
|
|
,tanev.C_NEV AS 'Tanév'
|
|
,CONVERT(VARCHAR, {felhasznaloAlias}.C_SZULETESIDATUM, 102) AS 'Születési dátuma'
|
|
,{felhasznaloAlias}.C_ANYJANEVE AS 'Anyja neve'
|
|
,IIF({felhasznaloAlias}.C_NEVSORREND = 'T', {felhasznaloAlias}.C_UTONEV + ' ' + {felhasznaloAlias}.C_VEZETEKNEV, {felhasznaloAlias}.C_VEZETEKNEV + ' ' + {felhasznaloAlias}.C_UTONEV) AS 'NeveElotagNelkul'
|
|
,IIF({felhasznaloAlias}.C_OKTATASIAZONOSITO IS NULL, '-', {felhasznaloAlias}.C_OKTATASIAZONOSITO) AS '{ AlkalmazottResource.PedagogusOktatasiAzonosito }'
|
|
,IIF(lakcim_allando.C_IRANYITOSZAM IS NULL, '-', lakcim_allando.C_IRANYITOSZAM) AS 'Állandó lakcím irányítószám'
|
|
,IIF(lakcim_allando.C_VAROS IS NULL, '-', lakcim_allando.C_VAROS) AS 'Állandó lakcím város'
|
|
,IIF(lakcim_allando.C_KOZTERULET IS NULL, '-', lakcim_allando.C_KOZTERULET) AS 'Állandó lakcím közterület neve'
|
|
,IIF(lakcim_allando.C_KOZTERULETJELLEGENEV IS NULL, '-', lakcim_allando.C_KOZTERULETJELLEGENEV) AS 'Állandó lakcím közterület jellege'
|
|
,IIF(lakcim_allando.C_HAZSZAM IS NULL, '-', lakcim_allando.C_HAZSZAM) AS 'Állandó lakcím házszám'
|
|
,IIF(lakcim_allando.C_EMELET IS NULL, '-', lakcim_allando.C_EMELET) AS 'Állandó lakcím emelet'
|
|
,IIF(lakcim_allando.C_AJTO IS NULL, '-', lakcim_allando.C_AJTO) AS 'Állandó lakcím ajtó'
|
|
,IIF(lakcim_ideiglenes.C_IRANYITOSZAM IS NULL, '-', lakcim_ideiglenes.C_IRANYITOSZAM) AS 'Ideiglenes lakcím irányítószám'
|
|
,IIF(lakcim_ideiglenes.C_VAROS IS NULL, '-', lakcim_ideiglenes.C_VAROS) AS 'Ideiglenes lakcím város'
|
|
,IIF(lakcim_ideiglenes.C_KOZTERULET IS NULL, '-', lakcim_ideiglenes.C_KOZTERULET) AS 'Ideiglenes lakcím közterület neve'
|
|
,IIF(lakcim_ideiglenes.C_KOZTERULETJELLEGENEV IS NULL, '-', lakcim_ideiglenes.C_KOZTERULETJELLEGENEV) AS 'Ideiglenes lakcím közterület jellege'
|
|
,IIF(lakcim_ideiglenes.C_HAZSZAM IS NULL, '-', lakcim_ideiglenes.C_HAZSZAM) AS 'Ideiglenes lakcím házszám'
|
|
,IIF(lakcim_ideiglenes.C_EMELET IS NULL, '-', lakcim_ideiglenes.C_EMELET) AS 'Ideiglenes lakcím emelet'
|
|
,IIF(lakcim_ideiglenes.C_AJTO IS NULL, '-', lakcim_ideiglenes.C_AJTO) AS 'Ideiglenes lakcím ajtó'
|
|
,IIF(lakcim_tartozkodas.C_IRANYITOSZAM IS NULL, '-', lakcim_tartozkodas.C_IRANYITOSZAM) AS 'Tartózkodási hely irányítószám'
|
|
,IIF(lakcim_tartozkodas.C_VAROS IS NULL, '-', lakcim_tartozkodas.C_VAROS) AS 'Tartózkodási hely város'
|
|
,IIF(lakcim_tartozkodas.C_KOZTERULET IS NULL, '-', lakcim_tartozkodas.C_KOZTERULET) AS 'Tartózkodási hely közterület neve'
|
|
,IIF(lakcim_tartozkodas.C_KOZTERULETJELLEGENEV IS NULL, '-', lakcim_tartozkodas.C_KOZTERULETJELLEGENEV) AS 'Tartózkodási hely közterület jellege'
|
|
,IIF(lakcim_tartozkodas.C_HAZSZAM IS NULL, '-', lakcim_tartozkodas.C_HAZSZAM) AS 'Tartózkodási hely házszám'
|
|
,IIF(lakcim_tartozkodas.C_EMELET IS NULL, '-', lakcim_tartozkodas.C_EMELET) AS 'Tartózkodási hely emelet'
|
|
,IIF(lakcim_tartozkodas.C_AJTO IS NULL, '-', lakcim_tartozkodas.C_AJTO) AS 'Tartózkodási hely ajtó'
|
|
,IIF(lakcim_intezmeny.C_IRANYITOSZAM IS NULL, '-', lakcim_intezmeny.C_IRANYITOSZAM) AS 'Intézménycím irányítószám'
|
|
,IIF(lakcim_intezmeny.C_VAROS IS NULL, '-', lakcim_intezmeny.C_VAROS) AS 'Intézménycím város'
|
|
,IIF(lakcim_intezmeny.C_KOZTERULET IS NULL, '-', lakcim_intezmeny.C_KOZTERULET) AS 'Intézménycím közterület neve'
|
|
,IIF(lakcim_intezmeny.C_KOZTERULETJELLEGENEV IS NULL, '-', lakcim_intezmeny.C_KOZTERULETJELLEGENEV) AS 'Intézménycím közterület jellege'
|
|
,IIF(lakcim_intezmeny.C_HAZSZAM IS NULL, '-', lakcim_intezmeny.C_HAZSZAM) AS 'Intézménycím házszám'
|
|
,IIF(lakcim_intezmeny.C_EMELET IS NULL, '-', lakcim_intezmeny.C_EMELET) AS 'Intézménycím emelet'
|
|
,IIF(lakcim_intezmeny.C_AJTO IS NULL, '-', lakcim_intezmeny.C_AJTO) AS 'Intézménycím ajtó'
|
|
,IIF(lakcim_na.C_IRANYITOSZAM IS NULL, '-', lakcim_na.C_IRANYITOSZAM) AS 'Ismeretlen lakcím irányítószám'
|
|
,IIF(lakcim_na.C_VAROS IS NULL, '-', lakcim_na.C_VAROS) AS 'Ismeretlen lakcím város'
|
|
,IIF(lakcim_na.C_KOZTERULET IS NULL, '-', lakcim_na.C_KOZTERULET) AS 'Ismeretlen lakcím közterület neve'
|
|
,IIF(lakcim_na.C_KOZTERULETJELLEGENEV IS NULL, '-', lakcim_na.C_KOZTERULETJELLEGENEV) AS 'Ismeretlen lakcím közterület jellege'
|
|
,IIF(lakcim_na.C_HAZSZAM IS NULL, '-', lakcim_na.C_HAZSZAM) AS 'Ismeretlen lakcím házszám'
|
|
,IIF(lakcim_na.C_EMELET IS NULL, '-', lakcim_na.C_EMELET) AS 'Ismeretlen lakcím emelet'
|
|
,IIF(lakcim_na.C_AJTO IS NULL, '-', lakcim_na.C_AJTO) AS 'Ismeretlen lakcím ajtó'
|
|
,IIF(email_hivatalos.C_EMAILCIM IS NULL, '-', email_hivatalos.C_EMAILCIM) AS 'Hivatalos e-mail cím'
|
|
,IIF(email_magan.C_EMAILCIM IS NULL, '-', email_magan.C_EMAILCIM) AS 'Magán e-mail cím'
|
|
,IIF(email_na.C_EMAILCIM IS NULL, '-', email_na.C_EMAILCIM) AS 'Ismeretlen e-mail cím'
|
|
,IIF(telefon_munkahely.C_TELEFONSZAM IS NULL, '-', telefon_munkahely.C_TELEFONSZAM) AS 'Munkahelyi telefonszám'
|
|
,IIF(telefon_vezetekes.C_TELEFONSZAM IS NULL, '-', telefon_vezetekes.C_TELEFONSZAM) AS 'Vezetékes telefonszám'
|
|
,IIF(telefon_mobil.C_TELEFONSZAM IS NULL, '-', telefon_mobil.C_TELEFONSZAM) AS 'Mobil telefonszám'
|
|
,IIF(telefon_fax.C_TELEFONSZAM IS NULL, '-', telefon_fax.C_TELEFONSZAM) AS 'Fax telefonszám'
|
|
,IIF(telefon_na.C_TELEFONSZAM IS NULL, '-', telefon_na.C_TELEFONSZAM) AS 'Ismeretlen telefonszám'
|
|
,{mukodesihelyAlias}.C_NEV + ' - ' + feladatellatasihely_dib.C_NAME AS 'Feladatellátási hely'
|
|
{showId}
|
|
FROM
|
|
T_FELHASZNALO_OSSZES {felhasznaloAlias}
|
|
INNER JOIN
|
|
T_ALKALMAZOTT_OSSZES {alkalmazottAlias} ON {alkalmazottAlias}.ID = {felhasznaloAlias}.ID AND {alkalmazottAlias}.TOROLT = 'F' AND {alkalmazottAlias}.C_ALTANEVID = {felhasznaloAlias}.C_TANEVID
|
|
{munkaugyiAdatokJoin}
|
|
LEFT JOIN T_CIM_OSSZES lakcim_allando ON lakcim_allando.TOROLT = 'F' AND lakcim_allando.C_FELHASZNALOID = {alkalmazottAlias}.ID AND {alkalmazottAlias}.C_ALTANEVID = lakcim_allando.C_TANEVID
|
|
AND EXISTS(SELECT TOP 1 1 FROM T_DICTIONARYITEMBASE_OSSZES lakcim_allando_dib WHERE lakcim_allando.C_CIMTIPUSA = lakcim_allando_DIB.ID AND {alkalmazottAlias}.C_ALTANEVID = lakcim_allando_dib.C_TANEVID AND lakcim_allando_dib.C_NAME = 'Állandó lakcím' AND lakcim_allando_dib.TOROLT = 'F')
|
|
LEFT JOIN T_CIM_OSSZES lakcim_tartozkodas ON lakcim_tartozkodas.TOROLT = 'F' AND lakcim_tartozkodas.C_FELHASZNALOID = {alkalmazottAlias}.ID AND {alkalmazottAlias}.C_ALTANEVID = lakcim_tartozkodas.C_TANEVID
|
|
AND EXISTS(SELECT TOP 1 1 FROM T_DICTIONARYITEMBASE_OSSZES lakcim_tartozkodas_dib WHERE lakcim_tartozkodas.C_CIMTIPUSA = lakcim_tartozkodas_dib.ID AND {alkalmazottAlias}.C_ALTANEVID = lakcim_tartozkodas_dib.C_TANEVID AND lakcim_tartozkodas_dib.C_NAME = 'Tartózkodási hely' AND lakcim_tartozkodas_dib.TOROLT = 'F')
|
|
LEFT JOIN T_CIM_OSSZES lakcim_ideiglenes ON lakcim_ideiglenes.TOROLT = 'F' AND lakcim_ideiglenes.C_FELHASZNALOID = {alkalmazottAlias}.ID AND {alkalmazottAlias}.C_ALTANEVID = lakcim_ideiglenes.C_TANEVID
|
|
AND EXISTS(SELECT TOP 1 1 FROM T_DICTIONARYITEMBASE_OSSZES lakcim_ideiglenes_dib WHERE lakcim_ideiglenes.C_CIMTIPUSA = lakcim_ideiglenes_dib.ID AND {alkalmazottAlias}.C_ALTANEVID = lakcim_ideiglenes_dib.C_TANEVID AND lakcim_ideiglenes_dib.C_NAME = 'Ideiglenes lakcím' AND lakcim_ideiglenes_dib.TOROLT = 'F')
|
|
LEFT JOIN T_CIM_OSSZES lakcim_intezmeny ON lakcim_intezmeny.TOROLT = 'F' AND lakcim_intezmeny.C_FELHASZNALOID = {alkalmazottAlias}.ID AND {alkalmazottAlias}.C_ALTANEVID = lakcim_intezmeny.C_TANEVID
|
|
AND EXISTS(SELECT TOP 1 1 FROM T_DICTIONARYITEMBASE_OSSZES lakcim_intezmeny_dib WHERE lakcim_intezmeny.C_CIMTIPUSA = lakcim_intezmeny_dib.ID AND {alkalmazottAlias}.C_ALTANEVID = lakcim_intezmeny_dib.C_TANEVID AND lakcim_intezmeny_dib.C_NAME = 'Intézménycím' AND lakcim_intezmeny_dib.TOROLT = 'F')
|
|
LEFT JOIN T_CIM_OSSZES lakcim_na ON lakcim_na.TOROLT = 'F' AND lakcim_na.C_FELHASZNALOID = {alkalmazottAlias}.ID AND {alkalmazottAlias}.C_ALTANEVID = lakcim_na.C_TANEVID
|
|
AND EXISTS(SELECT TOP 1 1 FROM T_DICTIONARYITEMBASE_OSSZES lakcim_na_dib WHERE lakcim_na.C_CIMTIPUSA = lakcim_na_dib.ID AND {alkalmazottAlias}.C_ALTANEVID = lakcim_na_dib.C_TANEVID AND lakcim_na_dib.C_NAME = 'Na' AND lakcim_na_dib.TOROLT = 'F')
|
|
INNER JOIN
|
|
T_TANEV_OSSZES tanev on tanev.ID = {alkalmazottAlias}.C_ALTANEVID and tanev.TOROLT = 'F'
|
|
LEFT JOIN T_EMAIL_OSSZES email_hivatalos ON email_hivatalos.TOROLT = 'F' and email_hivatalos.C_FELHASZNALOID = {alkalmazottAlias}.ID AND email_hivatalos.C_TANEVID = {alkalmazottAlias}.C_ALTANEVID AND (SELECT COUNT(1) FROM T_DICTIONARYITEMBASE_OSSZES email_hivatalos_dib WHERE email_hivatalos.C_EMAILTIPUSA = email_hivatalos_dib.ID AND {alkalmazottAlias}.C_ALTANEVID = email_hivatalos_dib.C_TANEVID AND email_hivatalos_dib.C_NAME = 'Hivatalos' AND email_hivatalos_dib.TOROLT = 'F') > 0
|
|
LEFT JOIN T_EMAIL_OSSZES email_magan ON email_magan.TOROLT = 'F' and email_magan.C_FELHASZNALOID = {alkalmazottAlias}.ID AND email_magan.C_TANEVID = {alkalmazottAlias}.C_ALTANEVID AND (SELECT COUNT(1) FROM T_DICTIONARYITEMBASE_OSSZES email_magan_dib WHERE email_magan.C_EMAILTIPUSA = email_magan_dib.ID AND {alkalmazottAlias}.C_ALTANEVID = email_magan_dib.C_TANEVID AND email_magan_dib.C_NAME = 'Magán' AND email_magan_dib.TOROLT = 'F') > 0
|
|
LEFT JOIN T_EMAIL_OSSZES email_na ON email_na.TOROLT = 'F' and email_na.C_FELHASZNALOID = {alkalmazottAlias}.ID AND email_na.C_TANEVID = {alkalmazottAlias}.C_ALTANEVID AND (SELECT COUNT(1) FROM T_DICTIONARYITEMBASE_OSSZES email_na_dib WHERE email_na.C_EMAILTIPUSA = email_na_dib.ID AND {alkalmazottAlias}.C_ALTANEVID = email_na_dib.C_TANEVID AND email_na_dib.C_NAME = 'Na' AND email_na_dib.TOROLT = 'F') > 0
|
|
LEFT JOIN T_TELEFON_OSSZES telefon_munkahely ON telefon_munkahely.TOROLT = 'F' and telefon_munkahely.C_FELHASZNALOID = {alkalmazottAlias}.ID AND telefon_munkahely.C_TANEVID = {alkalmazottAlias}.C_ALTANEVID AND (SELECT COUNT(1) FROM T_DICTIONARYITEMBASE_OSSZES telefon_munkahely_dib WHERE telefon_munkahely.C_TELEFONTIPUSA = telefon_munkahely_dib.ID AND {alkalmazottAlias}.C_ALTANEVID = telefon_munkahely_dib.C_TANEVID AND telefon_munkahely_dib.C_NAME = 'Munkahelyi' AND telefon_munkahely_dib.TOROLT = 'F') > 0
|
|
LEFT JOIN T_TELEFON_OSSZES telefon_vezetekes ON telefon_vezetekes.TOROLT = 'F' and telefon_vezetekes.C_FELHASZNALOID = {alkalmazottAlias}.ID AND telefon_vezetekes.C_TANEVID = {alkalmazottAlias}.C_ALTANEVID AND (SELECT COUNT(1) FROM T_DICTIONARYITEMBASE_OSSZES telefon_vezetekes_dib WHERE telefon_vezetekes.C_TELEFONTIPUSA = telefon_vezetekes_dib.ID AND {alkalmazottAlias}.C_ALTANEVID = telefon_vezetekes_dib.C_TANEVID AND telefon_vezetekes_dib.C_NAME = 'Vezetékes' AND telefon_vezetekes_dib.TOROLT = 'F') > 0
|
|
LEFT JOIN T_TELEFON_OSSZES telefon_mobil ON telefon_mobil.TOROLT = 'F' and telefon_mobil.C_FELHASZNALOID = {alkalmazottAlias}.ID AND telefon_mobil.C_TANEVID = {alkalmazottAlias}.C_ALTANEVID AND (SELECT COUNT(1) FROM T_DICTIONARYITEMBASE_OSSZES telefon_mobil_dib WHERE telefon_mobil.C_TELEFONTIPUSA = telefon_mobil_dib.ID AND {alkalmazottAlias}.C_ALTANEVID = telefon_mobil_dib.C_TANEVID AND telefon_mobil_dib.C_NAME = 'Mobil' AND telefon_mobil_dib.TOROLT = 'F') > 0
|
|
LEFT JOIN T_TELEFON_OSSZES telefon_fax ON telefon_fax.TOROLT = 'F' and telefon_fax.C_FELHASZNALOID = {alkalmazottAlias}.ID AND telefon_fax.C_TANEVID = {alkalmazottAlias}.C_ALTANEVID AND (SELECT COUNT(1) FROM T_DICTIONARYITEMBASE_OSSZES telefon_fax_dib WHERE telefon_fax.C_TELEFONTIPUSA = telefon_fax_dib.ID AND {alkalmazottAlias}.C_ALTANEVID = telefon_fax_dib.C_TANEVID AND telefon_fax_dib.C_NAME = 'Fax' AND telefon_fax_dib.TOROLT = 'F') > 0
|
|
LEFT JOIN T_TELEFON_OSSZES telefon_na ON telefon_na.TOROLT = 'F' and telefon_na.C_FELHASZNALOID = {alkalmazottAlias}.ID AND telefon_na.C_TANEVID = {alkalmazottAlias}.C_ALTANEVID AND (SELECT COUNT(1) FROM T_DICTIONARYITEMBASE_OSSZES telefon_na_dib WHERE telefon_na.C_TELEFONTIPUSA = telefon_na_dib.ID AND {alkalmazottAlias}.C_ALTANEVID = telefon_na_dib.C_TANEVID AND telefon_na_dib.C_NAME = 'Ismeretlen' AND telefon_na.TOROLT = 'F') > 0
|
|
INNER JOIN
|
|
T_FELADATELLATASIHELY_OSSZES feladatellatasihely ON feladatellatasihely.ID = {munkaugyiAlias}.C_FELADATELLATASIHELYID AND feladatellatasihely.C_TANEVID = {munkaugyiAlias}.C_TANEVID AND feladatellatasihely.TOROLT = 'F'
|
|
INNER JOIN
|
|
T_DICTIONARYITEMBASE_OSSZES feladatellatasihely_dib ON feladatellatasihely_dib.ID = feladatellatasihely.C_OKTATASINEVELESIFELADATTIPUS AND feladatellatasihely_dib.C_TANEVID = feladatellatasihely.C_TANEVID AND feladatellatasihely_dib.TOROLT = 'F'
|
|
INNER JOIN
|
|
T_MUKODESIHELY_OSSZES {mukodesihelyAlias} ON feladatellatasihely.C_INTEZMENYID = {mukodesihelyAlias}.C_INTEZMENYID AND {mukodesihelyAlias}.C_TANEVID = feladatellatasihely.C_TANEVID AND feladatellatasihely.C_MUKODESIHELYID = {mukodesihelyAlias}.ID AND {mukodesihelyAlias}.TOROLT = 'F'
|
|
LEFT JOIN
|
|
T_FELHASZNALOBELEPES_OSSZES ON {felhasznalobelepesAlias}.C_FELHASZNALOID = {felhasznaloAlias}.ID AND {felhasznalobelepesAlias}.TOROLT = 'F' AND {felhasznalobelepesAlias}.C_TANEVID = {felhasznaloAlias}.C_TANEVID
|
|
LEFT JOIN
|
|
T_FOGLALKOZAS_OSSZES ON {foglalkozasAlias}.C_TANARID = {felhasznaloAlias}.ID AND {foglalkozasAlias}.TOROLT = 'F' AND {foglalkozasAlias}.C_TANEVID = {felhasznaloAlias}.C_TANEVID
|
|
");
|
|
|
|
if (pco.NincsEmail == true)
|
|
{
|
|
commandText.Append($@"
|
|
LEFT JOIN
|
|
T_EMAIL_OSSZES {emailAlias} ON {emailAlias}.C_FELHASZNALOID = {felhasznaloAlias}.ID AND {emailAlias}.TOROLT = 'F' AND {emailAlias}.C_ALAPERTELMEZETT = 'T' AND {emailAlias}.C_TANEVID = :pTanevId
|
|
");
|
|
}
|
|
|
|
commandText.Append($@"
|
|
" + (pco.IsFromSzervezet ? " INNER" : " LEFT") + $@" JOIN T_SZERVEZET_OSSZES {szervezetAlias} ON {munkaugyiAlias}.C_SZERVEZETID = {szervezetAlias}.ID
|
|
AND {szervezetAlias}.C_TANEVID = {munkaugyiAlias}.C_TANEVID
|
|
AND {szervezetAlias}.TOROLT = 'F'
|
|
");
|
|
|
|
StringBuilder whereClause = pco.ToWhereClause(parameters, emailAlias, felhasznalobelepesAlias, felhasznaloAlias, munkaugyiAlias, foglalkozasAlias, alkalmazottAlias, szervezetAlias);
|
|
|
|
commandText.Append($@"
|
|
WHERE
|
|
{felhasznaloAlias}.TOROLT = 'F' AND
|
|
{felhasznaloAlias}.C_TANEVID = :pTanevId
|
|
{whereClause}
|
|
ORDER BY {felhasznaloAlias}.C_VEZETEKNEV, {felhasznaloAlias}.C_UTONEV
|
|
");
|
|
|
|
return GetData(commandText.ToString(), parameters);
|
|
}
|
|
|
|
public DataSet GetAlkalmazottakMunkaugyiAdataiExcelExport(int tanevId, bool isKlebersbergOrNSZFH, int intezmenyId, string intezmenyAzonosito, AlkalmazottSearchPco pco, bool isSelectedTanev21_22OrLater = false)
|
|
{
|
|
const string emailAlias = "T_EMAIL_OSSZES";
|
|
const string felhasznalobelepesAlias = "T_FELHASZNALOBELEPES_OSSZES";
|
|
const string felhasznaloAlias = "felhasznalo";
|
|
const string munkaugyiAlias = "munkaugyi";
|
|
const string foglalkozasAlias = "T_FOGLALKOZAS_OSSZES";
|
|
const string alkalmazottAlias = "alkalmazott";
|
|
const string mukodesihelyAlias = "mukodesihely";
|
|
const string szervezetAlias = "szervezetAdatok";
|
|
|
|
var parameters = new List<CommandParameter> { new CommandParameter("pTanevId", tanevId) };
|
|
parameters.Add(new CommandParameter("pIntezmenyId", intezmenyId));
|
|
parameters.Add(new CommandParameter("pIsKlebersberg", isKlebersbergOrNSZFH ? 1 : 0));
|
|
|
|
StringBuilder whereClause = pco.ToWhereClause(parameters, emailAlias, felhasznalobelepesAlias, felhasznaloAlias, munkaugyiAlias, foglalkozasAlias, alkalmazottAlias, szervezetAlias);
|
|
|
|
var commandText = new StringBuilder($@"
|
|
SELECT
|
|
{felhasznaloAlias}.ID AS FelhasznaloId
|
|
,0 AS Elsodleges
|
|
,{felhasznaloAlias}.C_ELOTAG AS '{FelhasznalokResource.Elotag}'
|
|
,{felhasznaloAlias}.C_VEZETEKNEV AS '{FelhasznalokResource.Vezetekneve}'
|
|
,{felhasznaloAlias}.C_UTONEV AS '{FelhasznalokResource.Utoneve}'
|
|
,IIF({felhasznaloAlias}.C_NEVSORREND = 'T', (ISNULL({felhasznaloAlias}.C_ELOTAG + ' ', '') +ISNULL({felhasznaloAlias}.C_UTONEV + ' ', '') + ISNULL({felhasznaloAlias}.C_VEZETEKNEV, '')), (ISNULL({felhasznaloAlias}.C_ELOTAG + ' ', '') +ISNULL({felhasznaloAlias}.C_VEZETEKNEV + ' ', '') + ISNULL({felhasznaloAlias}.C_UTONEV, ''))) AS '{FelhasznalokResource.AlkalmazottTeljesNeve}'
|
|
,COALESCE({felhasznaloAlias}.C_OKTATASIAZONOSITO, '') AS '{AlkalmazottResource.PedagogusOktatasiAzonosito}'
|
|
,tanev.C_NEV AS '{CommonResource.Tanev}'
|
|
,IIF({munkaugyiAlias}.C_BESOROLASIFOKOZAT IS NULL, '',besorolasiFokozat.C_NAME) AS '{AlkalmazottResource.PedagogusFokozat}'
|
|
,IIF({munkaugyiAlias}.C_MUNKAKORTIPUSA IS NULL, '', munkakor.C_NAME) AS '{AlkalmazottResource.Munkakor}'
|
|
,IIF({munkaugyiAlias}.C_MUNKAVISZONYTIPUSA IS NULL, '', foglalkoztatas.C_NAME) AS '{AlkalmazottResource.FoglalkoztatasiJogviszony}'
|
|
,IIF({munkaugyiAlias}.C_UTAZOGYOGYPEDAGOGUS = 'T', 'Igen', 'Nem') AS '{AlkalmazottResource.UtazoGyogypedagogus}'
|
|
,IIF({munkaugyiAlias}.C_FOGLALKOZTATASTIPUS IS NULL, '', foglalkoztatastipus.C_NAME) AS '{AlkalmazottResource.FoglalkoztatasTipusa}'
|
|
,IIF({munkaugyiAlias}.C_VEZETOIORASZAMOK IS NULL, '', vezetoioraszamoka.C_NAME) AS '{AlkalmazottResource.VezetoiOraszamOka}'
|
|
,[KR_{intezmenyAzonosito}_Schema].fnGetDokumentumPedagogusOraszam ({alkalmazottAlias}.ID, @pIsKlebersberg) AS '{AlkalmazottResource.KotelezoOraszama}'
|
|
,IIF({munkaugyiAlias}.C_MUNKAKORTIPUSA NOT IN (569,6345,6346,572,582,581,6404,571,586,588,587,6347,570,590,557,559,560,561,562,563,564,565,566,567,568,583,584,585,589,594,633,3026,6382,6388,6421,6438,7356), IIF({munkaugyiAlias}.C_KOTELEZOORASZAM IS NOT NULL, (CAST({munkaugyiAlias}.C_KOTELEZOORASZAM AS NVARCHAR(20))),''), '-') '{AlkalmazottResource.Oraszam}'
|
|
,IIF({munkaugyiAlias}.C_MUNKAIDOKEDVEZMENYOKA IS NULL, '', munkaidokedvezmenyoka.C_NAME) AS '{AlkalmazottResource.FeladattalTerheltOraszamOka}'
|
|
,IIF({munkaugyiAlias}.C_MUNKAIDOKEDVEZMENYORASZAM IS NULL, '', CAST({munkaugyiAlias}.C_MUNKAIDOKEDVEZMENYORASZAM as nvarchar(20))) AS '{AlkalmazottResource.FeladattalTerheltOraszam}'
|
|
,IIF({munkaugyiAlias}.C_MUNKAKORTIPUSA NOT IN (569,6345,6346,572,582,581,6404,571,586,588,587,6347,570,590,557,559,560,561,562,563,564,565,566,567,568,583,584,585,589,594,633,3026,6382,6388,6421,6438,7356), CAST(({munkaugyiAlias}.C_KOTELEZOORASZAM - ISNULL({munkaugyiAlias}.C_MUNKAIDOKEDVEZMENYORASZAM, 0)) AS NVARCHAR(20)), '-' ) AS '{AlkalmazottResource.PedagogusHetiOraszama}'
|
|
,IIF({munkaugyiAlias}.C_CSOKKENTETTMUNKAIDOS = 'T', 'Igen', 'Nem') AS '{AlkalmazottResource.CsokkentettMunkaidos}'
|
|
,IIF(convert(varchar(10), {munkaugyiAlias}.C_ALKALMAZASKEZDETE, 120) IS NULL, '', convert(varchar(10), {munkaugyiAlias}.C_ALKALMAZASKEZDETE, 120)) AS '{AlkalmazottResource.AlkalmazasKezdete}'
|
|
,IIF(convert(varchar(10), {munkaugyiAlias}.C_ALKALMAZASMEGSZUNESE, 120) IS NULL, '', convert(varchar(10), {munkaugyiAlias}.C_ALKALMAZASMEGSZUNESE, 120)) AS '{AlkalmazottResource.AlkalmazasVege}'
|
|
,IIF({munkaugyiAlias}.C_TARTOSHELYETTESITES = 'T', 'Igen', 'Nem') AS '{AlkalmazottResource.TartosanTavollevo}'
|
|
,IIF({munkaugyiAlias}.C_NYUGDIJAS = 'T', 'Igen', 'Nem') AS '{AlkalmazottResource.NyugdijasTovabbfoglalkoztatott}'
|
|
,IIF({munkaugyiAlias}.C_BETOLTETLENALLASHELY = 'T', 'Igen', 'Nem') AS '{AlkalmazottResource.BetoltetlenAllashely}'
|
|
,IIF({munkaugyiAlias}.C_ISKULSOALKALMAZOTT = 'T', 'Igen', 'Nem') AS '{AlkalmazottResource.KulsoAlkalmazott}'
|
|
,IIF({alkalmazottAlias}.C_FOALLAS = 'T', 'Igen', 'Nem') AS '{AlkalmazottResource.Foallasu}'
|
|
,IIF({munkaugyiAlias}.C_SZABADALLASHELY = 'T', 'Igen', 'Nem') AS '{AlkalmazottResource.SzabadAllashely}'
|
|
,IIF({alkalmazottAlias}.C_SZAKERTOIVIZSGAELNOKI = 'T', 'Igen', 'Nem') AS '{AlkalmazottResource.SzakertoiVagyVizsgaelnokiTevekenysegu}'
|
|
,IIF({alkalmazottAlias}.C_TOVABBKEPZES = 'T', 'Igen', 'Nem') AS '{AlkalmazottResource.SzakmaiTovabbkepzes}'
|
|
,IIF({alkalmazottAlias}.C_SZAKVIZSGA = 'T', 'Igen', 'Nem') AS '{AlkalmazottResource.Szakvizsga}'
|
|
,IIF({munkaugyiAlias}.C_SZAKERTOMESTERPEDAGOGUS = 'T', 'Igen', 'Nem') AS '{AlkalmazottResource.SzakertoMesterpedagogusKutatotanar}'
|
|
,{alkalmazottAlias}.C_SZTSZKOD AS '{AlkalmazottResource.AlkalmazottSZTSZKod}'
|
|
,{mukodesihelyAlias}.C_NEV + ' - ' + feladatellatasihely_dib.C_NAME AS '{CommonResource.Feladatellatasihely}'" + (isSelectedTanev21_22OrLater ? $@"
|
|
,IIF({munkaugyiAlias}.C_KEPESITESTIPUSID IS NULL, '', kepesitesTipus.C_NAME) AS '{AlkalmazottResource.KepesitesTipusId}'
|
|
,IIF({munkaugyiAlias}.C_ISINTERNETETOKTCELRAHASZNAL = 'T', 'Igen', 'Nem') AS '{AlkalmazottResource.IsInternetetOktCelraHasznal}'
|
|
,IIF({munkaugyiAlias}.C_ISINFORMATKEPISMRENDELKEZIK = 'T', 'Igen', 'Nem') AS '{AlkalmazottResource.IsInformatKepIsmRendelkezik}'
|
|
,IIF({munkaugyiAlias}.C_ISIKTESZKOZOKETLGALABB40HASZ = 'T', 'Igen', 'Nem') AS '{AlkalmazottResource.IsIktEszkozoketLgalabb40Hasznal}'" : "") + $@"
|
|
FROM
|
|
T_FELHASZNALO_OSSZES {felhasznaloAlias}
|
|
INNER JOIN
|
|
T_ALKALMAZOTT_OSSZES {alkalmazottAlias} ON {alkalmazottAlias}.ID = {felhasznaloAlias}.ID AND {alkalmazottAlias}.TOROLT = 'F' AND {alkalmazottAlias}.C_ALTANEVID = {felhasznaloAlias}.C_TANEVID
|
|
INNER JOIN
|
|
T_MUNKAUGYIADATOK_OSSZES {munkaugyiAlias} ON {munkaugyiAlias}.C_ALKALMAZOTTID = {felhasznaloAlias}.ID AND {munkaugyiAlias}.C_TANEVID = {felhasznaloAlias}.C_TANEVID AND {munkaugyiAlias}.TOROLT = 'F'
|
|
LEFT JOIN T_DICTIONARYITEMBASE_OSSZES besorolasiFokozat ON besorolasiFokozat.ID = {munkaugyiAlias}.C_BESOROLASIFOKOZAT AND besorolasiFokozat.C_TANEVID = {alkalmazottAlias}.C_ALTANEVID AND besorolasiFokozat.TOROLT = 'F'
|
|
LEFT JOIN T_DICTIONARYITEMBASE_OSSZES munkakor ON munkakor.ID = {munkaugyiAlias}.C_MUNKAKORTIPUSA AND munkakor.C_TANEVID = {alkalmazottAlias}.C_ALTANEVID AND munkakor.TOROLT = 'F'
|
|
LEFT JOIN T_DICTIONARYITEMBASE_OSSZES foglalkoztatas ON foglalkoztatas.ID = {munkaugyiAlias}.C_MUNKAVISZONYTIPUSA AND foglalkoztatas.C_TANEVID = {alkalmazottAlias}.C_ALTANEVID AND foglalkoztatas.TOROLT = 'F'
|
|
LEFT JOIN T_DICTIONARYITEMBASE_OSSZES foglalkoztatastipus ON {munkaugyiAlias}.C_FOGLALKOZTATASTIPUS = foglalkoztatastipus.ID AND foglalkoztatastipus.C_TANEVID = {alkalmazottAlias}.C_ALTANEVID AND foglalkoztatastipus.TOROLT = 'F'
|
|
LEFT JOIN T_DICTIONARYITEMBASE_OSSZES vezetoioraszamoka ON {munkaugyiAlias}.C_VEZETOIORASZAMOK = vezetoioraszamoka.ID AND vezetoioraszamoka.C_TANEVID = {alkalmazottAlias}.C_ALTANEVID AND vezetoioraszamoka.TOROLT = 'F'
|
|
LEFT JOIN T_DICTIONARYITEMBASE_OSSZES munkaidokedvezmenyoka ON {munkaugyiAlias}.C_MUNKAIDOKEDVEZMENYOKA = munkaidokedvezmenyoka.ID AND munkaidokedvezmenyoka.C_TANEVID = {alkalmazottAlias}.C_ALTANEVID AND munkaidokedvezmenyoka.TOROLT = 'F'
|
|
LEFT JOIN T_DICTIONARYITEMBASE_OSSZES kepesitesTipus ON {munkaugyiAlias}.C_KEPESITESTIPUSID = kepesitesTipus.ID AND kepesitesTipus.C_TANEVID = {alkalmazottAlias}.C_ALTANEVID AND kepesitesTipus.TOROLT = 'F'
|
|
INNER JOIN
|
|
T_FELADATELLATASIHELY_OSSZES feladatellatasihely ON feladatellatasihely.ID = {munkaugyiAlias}.C_FELADATELLATASIHELYID AND feladatellatasihely.C_TANEVID = {munkaugyiAlias}.C_TANEVID AND feladatellatasihely.TOROLT = 'F'
|
|
INNER JOIN
|
|
T_DICTIONARYITEMBASE_OSSZES feladatellatasihely_dib ON feladatellatasihely_dib.ID = feladatellatasihely.C_OKTATASINEVELESIFELADATTIPUS AND feladatellatasihely_dib.C_TANEVID = {alkalmazottAlias}.C_ALTANEVID AND feladatellatasihely_dib.TOROLT = 'F'
|
|
INNER JOIN
|
|
T_TANEV_OSSZES tanev on tanev.ID = {alkalmazottAlias}.C_ALTANEVID and tanev.TOROLT = 'F'
|
|
INNER JOIN
|
|
T_MUKODESIHELY_OSSZES {mukodesihelyAlias} ON feladatellatasihely.C_INTEZMENYID = {mukodesihelyAlias}.C_INTEZMENYID AND {mukodesihelyAlias}.C_TANEVID = feladatellatasihely.C_TANEVID AND feladatellatasihely.C_MUKODESIHELYID = {mukodesihelyAlias}.ID AND {mukodesihelyAlias}.TOROLT = 'F'
|
|
LEFT JOIN
|
|
T_FELHASZNALOBELEPES_OSSZES ON {felhasznalobelepesAlias}.C_FELHASZNALOID = {felhasznaloAlias}.ID AND {felhasznalobelepesAlias}.TOROLT = 'F' AND {felhasznalobelepesAlias}.C_TANEVID = {felhasznaloAlias}.C_TANEVID
|
|
LEFT JOIN
|
|
T_EMAIL_OSSZES ON {emailAlias}.C_FELHASZNALOID = {felhasznaloAlias}.ID AND {emailAlias}.TOROLT = 'F' AND {emailAlias}.C_ALAPERTELMEZETT = 'T' AND {emailAlias}.C_TANEVID = :pTanevId");
|
|
|
|
commandText.Append($@"
|
|
" + (pco.IsFromSzervezet ? " INNER" : " LEFT") + $@" JOIN T_SZERVEZET_OSSZES {szervezetAlias} ON {munkaugyiAlias}.C_SZERVEZETID = {szervezetAlias}.ID
|
|
AND {szervezetAlias}.C_TANEVID = {munkaugyiAlias}.C_TANEVID
|
|
AND {szervezetAlias}.TOROLT = 'F'
|
|
");
|
|
|
|
commandText.Append($@"
|
|
WHERE
|
|
{felhasznaloAlias}.TOROLT = 'F'
|
|
AND {felhasznaloAlias}.C_TANEVID = :pTanevId
|
|
AND {felhasznaloAlias}.C_INTEZMENYID = :pIntezmenyId
|
|
{whereClause}
|
|
ORDER BY {felhasznaloAlias}.C_VEZETEKNEV, {felhasznaloAlias}.C_UTONEV
|
|
");
|
|
|
|
return GetData(commandText.ToString(), parameters);
|
|
}
|
|
|
|
public DataSet GetAlkalmazottakSzemelyiAdataiExcelExport(int tanevId, AlkalmazottSearchPco pco)
|
|
{
|
|
const string emailAlias = "email_filter";
|
|
const string felhasznalobelepesAlias = "T_FELHASZNALOBELEPES_OSSZES";
|
|
const string felhasznaloAlias = "felhasznalo";
|
|
const string munkaugyiAlias = "munkaugyiadat";
|
|
const string foglalkozasAlias = "T_FOGLALKOZAS_OSSZES";
|
|
const string alkalmazottAlias = "alkalmazott";
|
|
const string mukodesihelyAlias = "mukodesihely";
|
|
const string szervezetAlias = "szervezetAdatok";
|
|
|
|
var parameters = new List<CommandParameter> { new CommandParameter("pTanevId", tanevId) };
|
|
|
|
string munkaugyiAdatokJoin = string.Empty;
|
|
// Szűrés miatti join-ok:
|
|
if (pco.FeladatEllatasiHelyId.HasValue || pco.SearchPedagogusFokozat.HasValue || !string.IsNullOrWhiteSpace(pco.SearchNyugdijas)
|
|
|| (pco.NABesorolasiFokozat == true) || (pco.NincsMunkakor == true) || (pco.NincsFoglalkoztatasModja == true)
|
|
|| (pco.NemCsillagosMunkakor == true) || (pco.NemBelepettAlkalmazottKetHet == true) || (pco.NincsBelepesAlkalmazott == true)
|
|
|| (pco.NincsEmail == true) || (pco.AktivAlkalmazottak == true))
|
|
{
|
|
munkaugyiAdatokJoin = $@"
|
|
INNER JOIN T_MUNKAUGYIADATOK_OSSZES {munkaugyiAlias} ON {munkaugyiAlias}.C_ALKALMAZOTTID = {alkalmazottAlias}.ID AND
|
|
ISNULL({munkaugyiAlias}.C_ALKALMAZASKEZDETE, GETDATE()) <= GETDATE()
|
|
AND ISNULL({munkaugyiAlias}.C_ALKALMAZASMEGSZUNESE, GETDATE()) >= GETDATE() AND {munkaugyiAlias}.C_TANEVID = :pTanevId AND {munkaugyiAlias}.TOROLT = 'F'
|
|
";
|
|
}
|
|
else
|
|
{
|
|
munkaugyiAdatokJoin = $@"INNER JOIN
|
|
T_MUNKAUGYIADATOK_OSSZES {munkaugyiAlias} ON {munkaugyiAlias}.C_ALKALMAZOTTID = {felhasznaloAlias}.ID AND {munkaugyiAlias}.C_TANEVID = {felhasznaloAlias}.C_TANEVID AND {munkaugyiAlias}.TOROLT = 'F'";
|
|
}
|
|
|
|
var commandText = new StringBuilder($@"
|
|
SELECT DISTINCT
|
|
{felhasznaloAlias}.C_NYOMTATASINEV AS '{FelhasznalokResource.Nev}'
|
|
,tanev.C_NEV AS '{AlkalmazottResource.AlkalmazottExportHeaderNameTanevNev}'
|
|
,IIF({felhasznaloAlias}.C_OKTATASIAZONOSITO IS NULL, '-', {felhasznaloAlias}.C_OKTATASIAZONOSITO) AS '{AlkalmazottResource.AlkalmazottExportHeaderNameOktatasiAzonosito}'
|
|
,ISNULL(dicNem.C_NAME,'-') AS '{AlkalmazottResource.Neme}'
|
|
,{felhasznaloAlias}.C_ELOTAG AS '{FelhasznalokResource.Elotag}'
|
|
,{felhasznaloAlias}.C_VEZETEKNEV AS '{AlkalmazottResource.AlkalmazottCsaladiNev}'
|
|
,{felhasznaloAlias}.C_UTONEV AS '{AlkalmazottResource.AlkalmazottUtonev}'
|
|
,{felhasznaloAlias}.C_SZULETESIVEZETEKNEV AS '{AlkalmazottResource.SzuletesiCsaladiNev}'
|
|
,{felhasznaloAlias}.C_SZULETESIUTONEV AS '{AlkalmazottResource.SzuletesiUtonev}'
|
|
,{felhasznaloAlias}.C_ANYJAVEZETEKNEVE AS '{AlkalmazottResource.AnyjaCsaladiNeve}'
|
|
,{felhasznaloAlias}.C_ANYJAUTONEVE AS '{AlkalmazottResource.AnyjaUtoneve}'
|
|
,{felhasznaloAlias}.C_SZULETESIHELY AS '{AlkalmazottResource.SzuletesiHely}'
|
|
,CONVERT(VARCHAR, {felhasznaloAlias}.C_SZULETESIDATUM, 102) AS '{AlkalmazottResource.SzuletesiIdo}'
|
|
,dicOrszag.C_NAME AS '{AlkalmazottResource.SzuletesiOrszag}'
|
|
,dicAnyanyelv.C_NAME AS '{AlkalmazottResource.Anyanyelve}'
|
|
,ISNULL(dicAllampolgar.C_NAME,'-') AS '{AlkalmazottResource.Allampolgarsag}'
|
|
,ISNULL(dicAllampolgar2.C_NAME,'-') AS '{AlkalmazottResource.Allampolgarsag2}'
|
|
,ISNULL(dicIgazolvany.C_NAME,'-') AS '{AlkalmazottResource.IgazolvanyTipus}'
|
|
,ISNULL({felhasznaloAlias}.C_IGAZOLVANYSZAM,'-') AS '{AlkalmazottResource.IgazolvanySzama}'
|
|
,ISNULL({felhasznaloAlias}.C_ADOAZONOSITOJEL,'-') AS '{AlkalmazottResource.AdoazonositoJel}'
|
|
,ISNULL({felhasznaloAlias}.C_TAJSZAM,'-') AS '{FelhasznalokResource.TAJszam}'
|
|
,ISNULL({felhasznaloAlias}.C_MEGJEGYZES,'-') AS '{AlkalmazottResource.Megjegyzes}'
|
|
FROM
|
|
T_FELHASZNALO_OSSZES {felhasznaloAlias}
|
|
INNER JOIN
|
|
T_ALKALMAZOTT_OSSZES {alkalmazottAlias} ON {alkalmazottAlias}.ID = {felhasznaloAlias}.ID AND {alkalmazottAlias}.TOROLT = 'F' AND {alkalmazottAlias}.C_ALTANEVID = {felhasznaloAlias}.C_TANEVID
|
|
{munkaugyiAdatokJoin}
|
|
LEFT JOIN T_DICTIONARYITEMBASE_OSSZES dicNem ON dicNem.ID = {felhasznaloAlias}.C_NEME AND {alkalmazottAlias}.C_ALTANEVID = dicNem.C_TANEVID AND dicNem.TOROLT = 'F'
|
|
LEFT JOIN T_DICTIONARYITEMBASE_OSSZES dicAllampolgar ON dicAllampolgar.ID = {felhasznaloAlias}.C_ALLAMPOLGARSAGA AND {alkalmazottAlias}.C_ALTANEVID = dicAllampolgar.C_TANEVID AND dicAllampolgar.TOROLT = 'F'
|
|
LEFT JOIN T_DICTIONARYITEMBASE_OSSZES dicAllampolgar2 ON dicAllampolgar2.ID = {felhasznaloAlias}.C_ALLAMPOLGARSAGA2 AND {alkalmazottAlias}.C_ALTANEVID = dicAllampolgar2.C_TANEVID AND dicAllampolgar2.TOROLT = 'F'
|
|
LEFT JOIN T_DICTIONARYITEMBASE_OSSZES dicIgazolvany ON dicIgazolvany.ID = {felhasznaloAlias}.C_IGAZOLVANYTIPUSA AND {alkalmazottAlias}.C_ALTANEVID = dicIgazolvany.C_TANEVID AND dicIgazolvany.TOROLT = 'F'
|
|
LEFT JOIN T_DICTIONARYITEMBASE_OSSZES dicOrszag ON dicOrszag.ID = {felhasznaloAlias}.C_SZULETESIORSZAG AND {felhasznaloAlias}.C_TANEVID = dicOrszag.C_TANEVID AND dicOrszag.TOROLT = 'F'
|
|
LEFT JOIN T_DICTIONARYITEMBASE_OSSZES dicAnyanyelv ON dicAnyanyelv.ID = {felhasznaloAlias}.C_ANYANYELVE AND {felhasznaloAlias}.C_TANEVID = dicAnyanyelv.C_TANEVID AND dicAnyanyelv.TOROLT = 'F'
|
|
INNER JOIN
|
|
T_TANEV_OSSZES tanev on tanev.ID = {alkalmazottAlias}.C_ALTANEVID and tanev.TOROLT = 'F'
|
|
INNER JOIN
|
|
T_FELADATELLATASIHELY_OSSZES feladatellatasihely ON feladatellatasihely.ID = {munkaugyiAlias}.C_FELADATELLATASIHELYID AND feladatellatasihely.C_TANEVID = {munkaugyiAlias}.C_TANEVID AND feladatellatasihely.TOROLT = 'F'
|
|
INNER JOIN
|
|
T_MUKODESIHELY_OSSZES {mukodesihelyAlias} ON feladatellatasihely.C_INTEZMENYID = {mukodesihelyAlias}.C_INTEZMENYID AND {mukodesihelyAlias}.C_TANEVID = feladatellatasihely.C_TANEVID AND feladatellatasihely.C_MUKODESIHELYID = {mukodesihelyAlias}.ID AND {mukodesihelyAlias}.TOROLT = 'F'
|
|
LEFT JOIN
|
|
T_FELHASZNALOBELEPES_OSSZES ON {felhasznalobelepesAlias}.C_FELHASZNALOID = {felhasznaloAlias}.ID AND {felhasznalobelepesAlias}.TOROLT = 'F' AND {felhasznalobelepesAlias}.C_TANEVID = {felhasznaloAlias}.C_TANEVID
|
|
LEFT JOIN
|
|
T_FOGLALKOZAS_OSSZES ON {foglalkozasAlias}.C_TANARID = {felhasznaloAlias}.ID AND {foglalkozasAlias}.TOROLT = 'F' AND {foglalkozasAlias}.C_TANEVID = {felhasznaloAlias}.C_TANEVID
|
|
");
|
|
|
|
if (pco.NincsEmail == true)
|
|
{
|
|
commandText.Append($@"
|
|
LEFT JOIN
|
|
T_EMAIL_OSSZES {emailAlias} ON {emailAlias}.C_FELHASZNALOID = {felhasznaloAlias}.ID AND {emailAlias}.TOROLT = 'F' AND {emailAlias}.C_ALAPERTELMEZETT = 'T' AND {emailAlias}.C_TANEVID = :pTanevId
|
|
");
|
|
}
|
|
|
|
commandText.Append($@"
|
|
" + (pco.IsFromSzervezet ? " INNER" : " LEFT") + $@" JOIN T_SZERVEZET_OSSZES {szervezetAlias} ON {munkaugyiAlias}.C_SZERVEZETID = {szervezetAlias}.ID
|
|
AND {szervezetAlias}.C_TANEVID = {munkaugyiAlias}.C_TANEVID
|
|
AND {szervezetAlias}.TOROLT = 'F'
|
|
");
|
|
|
|
StringBuilder whereClause = pco.ToWhereClause(parameters, emailAlias, felhasznalobelepesAlias, felhasznaloAlias, munkaugyiAlias, foglalkozasAlias, alkalmazottAlias, szervezetAlias);
|
|
|
|
commandText.Append($@"
|
|
WHERE
|
|
{felhasznaloAlias}.TOROLT = 'F' AND
|
|
{felhasznaloAlias}.C_TANEVID = :pTanevId
|
|
{whereClause}
|
|
ORDER BY {felhasznaloAlias}.C_VEZETEKNEV, {felhasznaloAlias}.C_UTONEV
|
|
");
|
|
|
|
return GetData(commandText.ToString(), parameters);
|
|
}
|
|
|
|
#endregion Export
|
|
|
|
private void DeleteKKVegzettsegek(Alkalmazott entity)
|
|
{
|
|
entity.KKTanitoVezgettseg.DeleteAll();
|
|
entity.KKGyogypedVegzettseg.DeleteAll();
|
|
|
|
foreach (var tanarVegzettseg in entity.KKTanarVegzettseg.Where(x => !x.Torolt))
|
|
{
|
|
foreach (var targyKategoria in tanarVegzettseg.KKTantargyKategoria.Where(x => !x.Torolt))
|
|
targyKategoria.Delete();
|
|
}
|
|
entity.KKTanarVegzettseg.DeleteAll();
|
|
|
|
foreach (var amiVegzetteseg in entity.KKAMIVegzettseg.Where(x => !x.Torolt))
|
|
{
|
|
foreach (var item in amiVegzetteseg.KKKlasszikusZene.Where(x => !x.Torolt))
|
|
item.Delete();
|
|
|
|
foreach (var item in amiVegzetteseg.KKElektroakuZene.Where(x => !x.Torolt))
|
|
item.Delete();
|
|
|
|
foreach (var item in amiVegzetteseg.KKJazzZene.Where(x => !x.Torolt))
|
|
item.Delete();
|
|
|
|
foreach (var item in amiVegzetteseg.KKNepzene.Where(x => !x.Torolt))
|
|
item.Delete();
|
|
|
|
foreach (var item in amiVegzetteseg.KKTerulet.Where(x => !x.Torolt))
|
|
item.Delete();
|
|
}
|
|
entity.KKAMIVegzettseg.DeleteAll();
|
|
}
|
|
|
|
public DataSet TanevValtasAlkalmazottTorlesKereses(int kovTanevId)
|
|
{
|
|
using (var command = new SDACommand())
|
|
{
|
|
command.Connection = UserContext.Instance.SDAConnection;
|
|
command.Transaction = UserContext.Instance.SDATransaction;
|
|
command.CommandType = CommandType.StoredProcedure;
|
|
command.CommandText = "uspTanevValtasAlkalmazottTorlesKereses";
|
|
|
|
command.Parameters.Add("pKovTanevId", kovTanevId);
|
|
|
|
var dataSet = new DataSet();
|
|
using (var sdaDataAdapter = new SDADataAdapter())
|
|
{
|
|
sdaDataAdapter.SelectCommand = command;
|
|
sdaDataAdapter.Fill(dataSet);
|
|
}
|
|
|
|
return dataSet;
|
|
}
|
|
}
|
|
|
|
public bool IsAlkalmazott(int felhasznaloId, int tanevId)
|
|
{
|
|
using (var command = new SDACommand())
|
|
{
|
|
command.Connection = UserContext.Instance.SDAConnection;
|
|
command.Transaction = UserContext.Instance.SDATransaction;
|
|
command.CommandType = CommandType.StoredProcedure;
|
|
command.CommandText = "uspIsAlkalmazott";
|
|
|
|
command.Parameters.Add("pUserId", felhasznaloId);
|
|
command.Parameters.Add("pTanevId", tanevId);
|
|
|
|
return Convert.ToBoolean(command.ExecuteScalar());
|
|
}
|
|
}
|
|
|
|
public DataSet GetAlkalmazottNeveEsEmailCime(int tanevId, int? emailTipus, IList<int> vezetoOraszamOkok, int? userId)
|
|
{
|
|
var paramList = new List<CommandParameter>
|
|
{
|
|
new CommandParameter("pTanevId", tanevId)
|
|
};
|
|
var commandText = new StringBuilder(@"
|
|
SELECT
|
|
fel.C_NYOMTATASINEV AS Nev " +
|
|
(!emailTipus.HasValue ? "" : " , ISNULL(e.C_EMAILCIM, '') AS EmailCim") +
|
|
(!emailTipus.HasValue ? "" : " , e.C_GUID AS EmailGuid") + @"
|
|
FROM T_MUNKAUGYIADATOK_OSSZES ma
|
|
INNER JOIN T_FELHASZNALO_OSSZES fel ON fel.ID = ma.C_ALKALMAZOTTID AND fel.TOROLT = 'F' AND fel.C_TANEVID = ma.C_TANEVID" +
|
|
(!emailTipus.HasValue ? "" : " LEFT JOIN T_EMAIL_OSSZES e ON e.C_FELHASZNALOID = ma.C_ALKALMAZOTTID AND e.C_EMAILTIPUSA = :pEmailTipus AND e.TOROLT = 'F' AND e.C_TANEVID = ma.C_TANEVID AND e.C_ISHIBASANMEGADVA = 'F'") + @"
|
|
WHERE ma.C_TANEVID = :pTanevId AND ma.TOROLT = 'F'
|
|
");
|
|
|
|
if (emailTipus.HasValue)
|
|
{
|
|
paramList.Add(new CommandParameter("pEmailTipus", emailTipus.Value));
|
|
}
|
|
|
|
if (vezetoOraszamOkok != null && vezetoOraszamOkok.Count > 0)
|
|
{
|
|
commandText.Append($" AND ma.C_VEZETOIORASZAMOK IN ({string.Join(",", vezetoOraszamOkok)})");
|
|
}
|
|
|
|
if (userId.HasValue)
|
|
{
|
|
paramList.Add(new CommandParameter("pUserId", userId));
|
|
commandText.Append(" AND ma.C_ALKALMAZOTTID = :pUserId");
|
|
}
|
|
|
|
return GetData(commandText.ToString(), paramList);
|
|
}
|
|
|
|
public DataSet GetAlkalmazottakNeveEsEmailCimeVzetoOraszamOkAlapjan(int tanevId, int emailTipus, int intezmenyId, List<int> vezetoOraszamOka)
|
|
{
|
|
var paramList = new List<CommandParameter>
|
|
{
|
|
new CommandParameter("pTanevId", tanevId),
|
|
new CommandParameter("pEmailTipus", emailTipus),
|
|
new CommandParameter("pIntezmenyId", intezmenyId)
|
|
};
|
|
|
|
var commandText = new StringBuilder($@"
|
|
SELECT
|
|
fel.C_NYOMTATASINEV AS Nev
|
|
,ISNULL(e.C_EMAILCIM, '') AS EmailCim
|
|
,e.C_GUID AS EmailGuid
|
|
FROM T_MUNKAUGYIADATOK_OSSZES ma
|
|
INNER JOIN T_FELHASZNALO_OSSZES fel ON fel.ID = ma.C_ALKALMAZOTTID AND fel.TOROLT = 'F' AND fel.C_TANEVID = ma.C_TANEVID
|
|
LEFT JOIN T_EMAIL_OSSZES e ON e.C_FELHASZNALOID = ma.C_ALKALMAZOTTID AND e.C_EMAILTIPUSA = :pEmailTipus AND e.TOROLT = 'F' AND e.C_TANEVID = ma.C_TANEVID AND e.C_ISHIBASANMEGADVA = 'F'
|
|
WHERE ma.C_TANEVID = :pTanevId AND ma.TOROLT = 'F' AND ma.C_INTEZMENYID = :pIntezmenyId
|
|
AND ma.C_VEZETOIORASZAMOK IN ({string.Join(", ", vezetoOraszamOka)})
|
|
");
|
|
|
|
return GetData(commandText.ToString(), paramList);
|
|
}
|
|
|
|
public string IsLeader(int pAlkalmazottId, int tanevId)
|
|
{
|
|
using (var command = new SDACommand())
|
|
{
|
|
command.Connection = UserContext.Instance.SDAConnection;
|
|
command.Transaction = UserContext.Instance.SDATransaction;
|
|
command.CommandType = CommandType.StoredProcedure;
|
|
command.CommandText = "uspIsLeader";
|
|
|
|
command.Parameters.Add("pTanevId", tanevId);
|
|
command.Parameters.Add("pAlkalmazottId", pAlkalmazottId);
|
|
|
|
var result = command.ExecuteScalar();
|
|
return result?.ToString();
|
|
}
|
|
}
|
|
|
|
public bool IsHrKezelo(int pAlkalmazottId, int tanevId)
|
|
{
|
|
using (var command = new SDACommand())
|
|
{
|
|
command.Connection = UserContext.Instance.SDAConnection;
|
|
command.Transaction = UserContext.Instance.SDATransaction;
|
|
command.CommandType = CommandType.StoredProcedure;
|
|
command.CommandText = "uspIsHrKezelo";
|
|
|
|
command.Parameters.Add("pTanevId", tanevId);
|
|
command.Parameters.Add("pAlkalmazottId", pAlkalmazottId);
|
|
|
|
var result = command.ExecuteScalar();
|
|
return result?.ToString() == "T";
|
|
}
|
|
}
|
|
|
|
public int GetAlkalmazottIdByGUID(string pAlkalmazottGuid, int pTanevId, int intezmenyId)
|
|
{
|
|
using (var command = new SDACommand())
|
|
{
|
|
command.Connection = UserContext.Instance.SDAConnection;
|
|
command.Transaction = UserContext.Instance.SDATransaction;
|
|
command.CommandType = CommandType.StoredProcedure;
|
|
command.CommandText = "uspGetAlkalmazottIdByGUID";
|
|
|
|
command.Parameters.Add("pAlkalmazottGuid", pAlkalmazottGuid);
|
|
command.Parameters.Add("pTanevId", pTanevId);
|
|
command.Parameters.Add("pIntezmenyId", intezmenyId);
|
|
|
|
var result = command.ExecuteScalar();
|
|
return int.Parse(result.ToString());
|
|
}
|
|
}
|
|
|
|
public DataSet GetAlkalmazottIDTavolletAlkalmazott(IList<int> alkalmazottIds, IList<string> alkalmazottSztszAzonositok)
|
|
{
|
|
string ids = null;
|
|
if (alkalmazottIds != null)
|
|
{
|
|
ids = SqlLogic.ParseListToParameter(alkalmazottIds).ToString();
|
|
}
|
|
|
|
var commandText = new StringBuilder($@"
|
|
SELECT
|
|
a.ID
|
|
,f.C_EGYEDIAZONOSITO
|
|
,f.C_NYOMTATASINEV
|
|
,a.C_SZTSZKOD
|
|
,mh.C_FUNKCIOTERULET
|
|
FROM T_ALKALMAZOTT a
|
|
INNER JOIN T_FELHASZNALO f ON f.ID = a.ID AND f.TOROLT = 'F'
|
|
INNER JOIN T_INTEZMENY i ON i.ID = a.C_ALINTEZMENYID AND i.TOROLT = 'F'
|
|
INNER JOIN T_TANEV tv ON tv.ID = a.C_ALTANEVID AND tv.C_AKTIV = 'T' AND tv.TOROLT = 'F'
|
|
INNER JOIN T_MUNKAUGYIADATOK ma
|
|
INNER JOIN T_FELADATELLATASIHELY fa
|
|
INNER JOIN T_MUKODESIHELY mh ON mh.ID = fa.C_MUKODESIHELYID AND mh.TOROLT = 'F'
|
|
ON fa.ID = ma.C_FELADATELLATASIHELYID AND fa.TOROLT = 'F'
|
|
ON ma.C_ALKALMAZOTTID = a.ID AND ma.TOROLT = 'F'
|
|
WHERE 1=1 AND a.TOROLT = 'F' ");
|
|
|
|
if (alkalmazottIds != null)
|
|
{
|
|
commandText.Append($" AND a.ID IN({ids}) ");
|
|
}
|
|
|
|
if (alkalmazottSztszAzonositok != null)
|
|
{
|
|
commandText.Append($" AND a.C_SZTSZKOD IN({SqlLogic.ParseListToParameter(alkalmazottSztszAzonositok)}) ");
|
|
}
|
|
|
|
var ds = GetData(commandText.ToString());
|
|
return ds;
|
|
}
|
|
|
|
public string GetSZTSZKOD(int pFelhasznaloId, int pTanevId)
|
|
{
|
|
using (var command = new SDACommand())
|
|
{
|
|
command.Connection = UserContext.Instance.SDAConnection;
|
|
command.Transaction = UserContext.Instance.SDATransaction;
|
|
command.CommandType = CommandType.StoredProcedure;
|
|
command.CommandText = "uspGetSZTSZKOD";
|
|
|
|
command.Parameters.Add("pFelhasznaloId", pFelhasznaloId);
|
|
command.Parameters.Add("pTanevId", pTanevId);
|
|
|
|
var sztsz = command.ExecuteScalar();
|
|
return sztsz?.ToString() ?? string.Empty;
|
|
}
|
|
}
|
|
|
|
public bool HasSZTSZKOD(int alkalmazottId, int tanevId)
|
|
{
|
|
using (var command = new SDACommand())
|
|
{
|
|
command.Connection = UserContext.Instance.SDAConnection;
|
|
command.Transaction = UserContext.Instance.SDATransaction;
|
|
command.CommandType = CommandType.StoredProcedure;
|
|
command.CommandText = "uspHasSZTSZKOD";
|
|
|
|
command.Parameters.Add("pUserId", alkalmazottId);
|
|
command.Parameters.Add("pTanevId", tanevId);
|
|
|
|
return Convert.ToBoolean(command.ExecuteScalar());
|
|
}
|
|
}
|
|
|
|
public bool HasValidMunkaviszony(int alkalmazottId, int tanevId)
|
|
{
|
|
using (var command = new SDACommand())
|
|
{
|
|
command.Connection = UserContext.Instance.SDAConnection;
|
|
command.Transaction = UserContext.Instance.SDATransaction;
|
|
command.CommandType = CommandType.StoredProcedure;
|
|
command.CommandText = "uspHasValidMunkaviszony";
|
|
|
|
command.Parameters.Add("pUserId", alkalmazottId);
|
|
command.Parameters.Add("pTanevId", tanevId);
|
|
|
|
return Convert.ToBoolean(command.ExecuteScalar());
|
|
}
|
|
}
|
|
|
|
public DataSet GetAlkalmazottakForTavolletjelento(int? alkalmazottFilterId, int tanevId, int intezmenyId)
|
|
{
|
|
using (var command = new SDACommand())
|
|
{
|
|
command.Connection = UserContext.Instance.SDAConnection;
|
|
command.Transaction = UserContext.Instance.SDATransaction;
|
|
command.CommandType = CommandType.StoredProcedure;
|
|
command.CommandText = "uspGetAlkalmazottakForTavolletjelento";
|
|
command.Parameters.Add("pTanevId", tanevId);
|
|
command.Parameters.Add("pIntezmenyId", intezmenyId);
|
|
|
|
|
|
if (alkalmazottFilterId.HasValue)
|
|
{
|
|
command.Parameters.Add("pAlkalmazottId", alkalmazottFilterId.Value);
|
|
}
|
|
else
|
|
{
|
|
command.Parameters.Add("pAlkalmazottId", DBNull.Value);
|
|
}
|
|
|
|
var dataSet = new DataSet();
|
|
using (var sdaDataAdapter = new SDADataAdapter())
|
|
{
|
|
sdaDataAdapter.SelectCommand = command;
|
|
sdaDataAdapter.Fill(dataSet);
|
|
}
|
|
|
|
return dataSet;
|
|
}
|
|
}
|
|
|
|
public DataSet GetAlkalmazottakSapAzonositoi(int? alkalmazottId, int tanevId, int intezmenyId)
|
|
{
|
|
using (var command = new SDACommand())
|
|
{
|
|
command.Connection = UserContext.Instance.SDAConnection;
|
|
command.Transaction = UserContext.Instance.SDATransaction;
|
|
command.CommandType = CommandType.StoredProcedure;
|
|
command.CommandText = "uspGetAlkalmazottakSapAzonositoi";
|
|
|
|
command.Parameters.Add("pAlkalmazottId", (object)alkalmazottId ?? DBNull.Value);
|
|
command.Parameters.Add("pTanevId", tanevId);
|
|
command.Parameters.Add("pIntezmenyId", intezmenyId);
|
|
|
|
var dataSet = new DataSet();
|
|
using (var sdaDataAdapter = new SDADataAdapter())
|
|
{
|
|
sdaDataAdapter.SelectCommand = command;
|
|
sdaDataAdapter.Fill(dataSet);
|
|
}
|
|
|
|
return dataSet;
|
|
}
|
|
}
|
|
|
|
public void UpdateAlkalmazottSzabadsagKeret(IDictionary<int, int> alkalmazottKeretek)
|
|
{
|
|
foreach (var alkalmazott in alkalmazottKeretek)
|
|
{
|
|
IAlkalmazott entity = Get(alkalmazott.Key);
|
|
entity.KotelezoEvesSzabadsagkeret = alkalmazott.Value;
|
|
Update(entity);
|
|
}
|
|
}
|
|
|
|
public List<KirAlkalmazott> SearchAlkalmazottForKir(FelhasznaloKirSearchPco felhasznaloKirSearchPco, int tanevId)
|
|
{
|
|
var commandText = new StringBuilder($@"
|
|
SELECT
|
|
Felhasznalo.ID AS Id
|
|
,Felhasznalo.C_OKTATASIAZONOSITO AS OktatasiAzonosito
|
|
,Felhasznalo.C_ELOTAG AS NevElotag
|
|
,Felhasznalo.C_VEZETEKNEV AS Vezeteknev
|
|
,Felhasznalo.C_UTONEV AS Utonev
|
|
,Felhasznalo.C_TAJSZAM AS TajSzam
|
|
,IIF(Felhasznalo.C_NEVSORREND = 'F',
|
|
Felhasznalo.C_VEZETEKNEV + ' ' + Felhasznalo.C_UTONEV,
|
|
Felhasznalo.C_UTONEV + ' ' + Felhasznalo.C_VEZETEKNEV) AS NevElotagNelkul
|
|
,Felhasznalo.C_NYOMTATASINEV AS NyomtatasiNev
|
|
,Felhasznalo.C_NEVSORREND AS NevSorrend
|
|
,Felhasznalo.C_SZULETESIVEZETEKNEV AS SzuletesiVezeteknev
|
|
,Felhasznalo.C_SZULETESIUTONEV AS SzuletesiUtonev
|
|
IIF(Felhasznalo.C_SZULETESINEVSORREND = 'F'
|
|
,Felhasznalo.C_SZULETESIVEZETEKNEV +' '+ Felhasznalo.C_SZULETESIUTONEV
|
|
,Felhasznalo.C_SZULETESIUTONEV +' '+ Felhasznalo.C_SZULETESIVEZETEKNEV) AS SzuletesiNevSorrenddel
|
|
,Felhasznalo.C_SZULETESINEV AS SzuletesiNev
|
|
,Felhasznalo.C_SZULETESINEVSORREND AS SzuletesiNevSorrend
|
|
,IIF(Felhasznalo.C_ANYJANEVESORREND = 'F', Felhasznalo.C_ANYJAVEZETEKNEVE, Felhasznalo.C_ANYJAUTONEVE) AS AnyjaVezetekNeve
|
|
,IIF(Felhasznalo.C_ANYJANEVESORREND = 'F', Felhasznalo.C_ANYJAUTONEVE, Felhasznalo.C_ANYJAVEZETEKNEVE) AS AnyjaUtoneve
|
|
IIF(Felhasznalo.C_ANYJANEVESORREND = 'F'
|
|
,Felhasznalo.C_ANYJAVEZETEKNEVE +' '+ Felhasznalo.C_ANYJAUTONEVE
|
|
,Felhasznalo.C_ANYJAUTONEVE +' '+ Felhasznalo.C_ANYJAVEZETEKNEVE) AS AnyjaNeveSorrenddel
|
|
,Felhasznalo.C_ANYJANEVE AS AnyjaNeve
|
|
,Felhasznalo.C_ANYJANEVESORREND AS AnyjaNeveSorrend
|
|
,Felhasznalo.C_SZULETESIDATUM AS SzuletesiDatum
|
|
,Felhasznalo.C_SZULETESIHELY AS SzuletesiHely
|
|
,SzuletesiOrszagDictionary.C_NAME AS SzuletesiOrszag
|
|
,AllampolgarsagaDictionary.C_NAME AS Allampolgarsag
|
|
,Allampolgarsaga2Dictionary.C_NAME AS Allampolgarsag2
|
|
,NemDictionary.C_NAME AS Nem
|
|
,Email.C_EMAILCIM AS Email
|
|
,Telefon.C_TELEFONSZAM AS Telefonszam
|
|
,MunkaugyiAdatok.C_ALKALMAZASKEZDETE AS AlkalmazasKezdete
|
|
,MunkaugyiAdatok.C_ALKALMAZASMEGSZUNESE AS AlkalmazasMegszunese
|
|
,FoglalkoztatasTipusDictionary.C_NAME AS FoglalkoztatasTipusa
|
|
FROM T_FELHASZNALO Felhasznalo
|
|
LEFT JOIN T_DICTIONARYITEMBASE SzuletesiOrszagDictionary ON SzuletesiOrszagDictionary.ID = Felhasznalo.C_SZULETESIORSZAG
|
|
AND SzuletesiOrszagDictionary.TOROLT = 'F'
|
|
AND SzuletesiOrszagDictionary.C_INTEZMENYID = Felhasznalo.C_INTEZMENYID
|
|
AND SzuletesiOrszagDictionary.C_TANEVID = Felhasznalo.C_TANEVID
|
|
LEFT JOIN T_DICTIONARYITEMBASE AllampolgarsagaDictionary ON AllampolgarsagaDictionary.ID = Felhasznalo.C_ALLAMPOLGARSAGA
|
|
AND AllampolgarsagaDictionary.TOROLT = 'F'
|
|
AND AllampolgarsagaDictionary.C_INTEZMENYID = Felhasznalo.C_INTEZMENYID
|
|
AND AllampolgarsagaDictionary.C_TANEVID = Felhasznalo.C_TANEVID
|
|
LEFT JOIN T_DICTIONARYITEMBASE Allampolgarsaga2Dictionary ON Allampolgarsaga2Dictionary.ID = Felhasznalo.C_ALLAMPOLGARSAGA2
|
|
AND Allampolgarsaga2Dictionary.TOROLT = 'F'
|
|
AND Allampolgarsaga2Dictionary.C_INTEZMENYID = Felhasznalo.C_INTEZMENYID
|
|
AND Allampolgarsaga2Dictionary.C_TANEVID = Felhasznalo.C_TANEVID
|
|
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_MUNKAUGYIADATOK MunkaugyiAdatok ON MunkaugyiAdatok.C_ALKALMAZOTTID = Felhasznalo.ID
|
|
AND MunkaugyiAdatok.TOROLT = 'F'
|
|
AND MunkaugyiAdatok.C_INTEZMENYID = Felhasznalo.C_INTEZMENYID
|
|
AND MunkaugyiAdatok.C_TANEVID = Felhasznalo.C_TANEVID
|
|
LEFT JOIN T_DICTIONARYITEMBASE FoglalkoztatasTipusDictionary ON FoglalkoztatasTipusDictionary.ID = MunkaugyiAdatok.C_FOGLALKOZTATASTIPUS
|
|
AND FoglalkoztatasTipusDictionary.TOROLT = 'F'
|
|
AND FoglalkoztatasTipusDictionary.C_INTEZMENYID = Felhasznalo.C_INTEZMENYID
|
|
AND FoglalkoztatasTipusDictionary.C_TANEVID = Felhasznalo.C_TANEVID
|
|
LEFT JOIN T_DICTIONARYITEMBASE NemDictionary ON NemDictionary.ID = Felhasznalo.C_NEME
|
|
AND NemDictionary.TOROLT = 'F'
|
|
AND NemDictionary.C_INTEZMENYID = Felhasznalo.C_INTEZMENYID
|
|
AND NemDictionary.C_TANEVID = Felhasznalo.C_TANEVID
|
|
WHERE Felhasznalo.TOROLT = 'F'
|
|
AND Felhasznalo.C_TANEVID = @{nameof(tanevId)}
|
|
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 commandParameterList = new List<CommandParameter>
|
|
{
|
|
new CommandParameter(nameof(tanevId), tanevId)
|
|
};
|
|
|
|
if (!string.IsNullOrWhiteSpace(felhasznaloKirSearchPco.Nev))
|
|
{
|
|
commandText.Append($@" AND LOWER(Felhasznalo.C_NYOMTATASINEV) LIKE '%' + :pNev + '%'");
|
|
commandParameterList.Add(new CommandParameter("pNev", felhasznaloKirSearchPco.Nev.ToLowerInvariant().Replace("[", "[[]")));
|
|
}
|
|
|
|
if (felhasznaloKirSearchPco.SzuletesiDatumTol.HasValue)
|
|
{
|
|
commandText.Append($@" AND Felhasznalo.C_SZULETESIDATUM >= :pSzuletesiDatumTol");
|
|
commandParameterList.Add(new CommandParameter("pSzuletesiDatumTol", felhasznaloKirSearchPco.SzuletesiDatumTol.Value));
|
|
}
|
|
|
|
if (felhasznaloKirSearchPco.SzuletesiDatumIg.HasValue)
|
|
{
|
|
commandText.Append($@" AND Felhasznalo.C_SZULETESIDATUM <= :pSzuletesiDatumIg");
|
|
commandParameterList.Add(new CommandParameter("pSzuletesiDatumIg", felhasznaloKirSearchPco.SzuletesiDatumIg.Value));
|
|
}
|
|
|
|
if (!string.IsNullOrWhiteSpace(felhasznaloKirSearchPco.OktatasiAzonosito))
|
|
{
|
|
commandText.Append($@" AND Felhasznalo.C_OKTATASIAZONOSITO LIKE :pOktatasiAzonosito + '%'");
|
|
commandParameterList.Add(new CommandParameter("pOktatasiAzonosito", felhasznaloKirSearchPco.OktatasiAzonosito));
|
|
}
|
|
|
|
if (!string.IsNullOrWhiteSpace(felhasznaloKirSearchPco.SzuletesiHely))
|
|
{
|
|
commandText.Append($@" AND Felhasznalo.C_SZULETESIHELY LIKE :pSzuletesiHely + '%'");
|
|
commandParameterList.Add(new CommandParameter("pSzuletesiHely", felhasznaloKirSearchPco.SzuletesiHely));
|
|
}
|
|
|
|
return GetData(commandText.ToString(), commandParameterList)
|
|
.ToDaoList<KirAlkalmazott>();
|
|
}
|
|
|
|
public List<KirCim> GetKirAlkalmazottCim(int intezmenyId, int tanevId, IEnumerable<int> felhasznaloId, CimTipusEnum cimTipusEnum)
|
|
{
|
|
var inParameters = new List<string>(felhasznaloId.Count());
|
|
|
|
var commandParameterList = new List<CommandParameter>
|
|
{
|
|
new CommandParameter(nameof(intezmenyId), intezmenyId),
|
|
new CommandParameter(nameof(tanevId), tanevId)
|
|
};
|
|
|
|
foreach (int item in felhasznaloId)
|
|
{
|
|
var name = $"felhasznaloId{item}";
|
|
|
|
commandParameterList.Add(new CommandParameter(name, item));
|
|
inParameters.Add("@" + name);
|
|
}
|
|
|
|
var commandText = $@"
|
|
SELECT
|
|
Cim.C_IRANYITOSZAM AS Iranyitoszam
|
|
,Cim.C_VAROS AS Varos
|
|
,Cim.C_KOZTERULET AS KozteruletNev
|
|
,Cim.C_KOZTERULETJELLEGENEV AS KozteruletJellege
|
|
,Cim.C_HAZSZAM AS Hazszam
|
|
,Cim.C_FELHASZNALOID AS FelhasznaloId
|
|
FROM T_CIM Cim
|
|
WHERE C_FELHASZNALOID IN ({ (inParameters.Count == 0 ? "NULL" : string.Join(",", inParameters)) })
|
|
AND Cim.C_ALAPERTELMEZETT = 'T'
|
|
AND Cim.TOROLT = 'F'
|
|
AND Cim.C_INTEZMENYID = @{nameof(intezmenyId)}
|
|
AND Cim.C_TANEVID = @{nameof(tanevId)}
|
|
AND Cim.C_CIMTIPUSA = {(int)cimTipusEnum}";
|
|
|
|
return GetData(commandText, commandParameterList)
|
|
.ToDaoList<KirCim>();
|
|
}
|
|
|
|
public KirAlkalmazottAlapadatok GetAlkalmazottAlapadatok(int id, int tanevId, int intezmenyId)
|
|
{
|
|
using (var command = new SDACommand())
|
|
{
|
|
command.Connection = UserContext.Instance.SDAConnection;
|
|
command.Transaction = UserContext.Instance.SDATransaction;
|
|
command.CommandType = CommandType.StoredProcedure;
|
|
command.CommandText = "uspGetAlkalmazottAlapadatok";
|
|
|
|
command.Parameters.Add("pId", id);
|
|
command.Parameters.Add("pTanevId", tanevId);
|
|
command.Parameters.Add("pIntezmenyId", intezmenyId);
|
|
|
|
var dataSet = new DataSet();
|
|
using (var sdaDataAdapter = new SDADataAdapter())
|
|
{
|
|
sdaDataAdapter.SelectCommand = command;
|
|
sdaDataAdapter.Fill(dataSet);
|
|
}
|
|
|
|
return dataSet.ToDaoList<KirAlkalmazottAlapadatok>()
|
|
.FirstOrDefault();
|
|
}
|
|
}
|
|
|
|
public KirAlkalmazottMunkaugyiAdatok GetAlkalmazottElsodlegesMunkaugyiAdatok(int id, int tanevId)
|
|
{
|
|
using (var command = new SDACommand())
|
|
{
|
|
command.Connection = UserContext.Instance.SDAConnection;
|
|
command.Transaction = UserContext.Instance.SDATransaction;
|
|
command.CommandType = CommandType.StoredProcedure;
|
|
command.CommandText = "uspGetAlkalmazottElsodlegesMunkaugyiAdatok";
|
|
|
|
command.Parameters.Add("pId", id);
|
|
command.Parameters.Add("pTanevId", tanevId);
|
|
|
|
var dataSet = new DataSet();
|
|
using (var sdaDataAdapter = new SDADataAdapter())
|
|
{
|
|
sdaDataAdapter.SelectCommand = command;
|
|
sdaDataAdapter.Fill(dataSet);
|
|
}
|
|
|
|
return dataSet.ToDaoList<KirAlkalmazottMunkaugyiAdatok>()
|
|
.FirstOrDefault();
|
|
}
|
|
}
|
|
|
|
public DataSet GetAlkalmazottWithBelepesselDataSet(int tanevId)
|
|
{
|
|
using (var sdaCommand = new SDACommand())
|
|
{
|
|
string commandText = @"
|
|
SELECT
|
|
felhasznalo.ID Id
|
|
,felhasznalo.C_NYOMTATASINEV Nev
|
|
,felhasznalo.C_ELOTAG Elotag
|
|
,felhasznalo.C_VEZETEKNEV Vezeteknev
|
|
,felhasznalo.C_UTONEV Utonev
|
|
,C_OKTATASIAZONOSITO OktatasiAzonosito
|
|
,C_SZULETESIDATUM SzuletesiDatum
|
|
,C_BEJELENTKEZESINEV FelhasznaloNev
|
|
,felhasznaloBelepes.C_IMPORTALT isImportalt
|
|
FROM
|
|
T_ALKALMAZOTT_OSSZES alkalmazott
|
|
INNER JOIN
|
|
T_FELHASZNALO_OSSZES felhasznalo ON alkalmazott.ID = felhasznalo.ID
|
|
INNER JOIN
|
|
T_FELHASZNALOBELEPES_OSSZES felhasznaloBelepes ON felhasznaloBelepes.C_FELHASZNALOID = alkalmazott.ID AND felhasznaloBelepes.TOROLT = 'F'
|
|
WHERE
|
|
alkalmazott.TOROLT = 'F'
|
|
AND alkalmazott.C_ALTANEVID = :pTanevId";
|
|
|
|
sdaCommand.Connection = UserContext.Instance.SDAConnection;
|
|
sdaCommand.Transaction = UserContext.Instance.SDATransaction;
|
|
sdaCommand.CommandType = CommandType.Text;
|
|
sdaCommand.CommandText = commandText;
|
|
|
|
sdaCommand.Parameters.Add("pTanevId", SDADBType.Int).Value = tanevId;
|
|
|
|
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 bool IsLetezoFelhasznalonev(string felhasznaloNev)
|
|
{
|
|
using (var command = new SDACommand())
|
|
{
|
|
command.Connection = UserContext.Instance.SDAConnection;
|
|
command.Transaction = UserContext.Instance.SDATransaction;
|
|
command.CommandType = CommandType.StoredProcedure;
|
|
command.CommandText = "uspIsLetezoFelhasznalonev";
|
|
|
|
command.Parameters.Add("pUserName", felhasznaloNev);
|
|
|
|
var result = command.ExecuteScalar();
|
|
if (result != null && result != System.DBNull.Value)
|
|
{
|
|
return true;
|
|
}
|
|
return false;
|
|
}
|
|
}
|
|
|
|
public DataSet GetAlkalmazottData(int tanevId)
|
|
{
|
|
using (var sdaCommand = new SDACommand())
|
|
{
|
|
sdaCommand.Connection = UserContext.Instance.SDAConnection;
|
|
sdaCommand.Transaction = UserContext.Instance.SDATransaction;
|
|
sdaCommand.CommandType = CommandType.StoredProcedure;
|
|
sdaCommand.CommandText = "uspGetAlkalmazottData";
|
|
|
|
sdaCommand.Parameters.Add("pTanevId", tanevId);
|
|
|
|
var dataSet = new DataSet();
|
|
using (var sdaDataAdapter = new SDADataAdapter())
|
|
{
|
|
sdaDataAdapter.SelectCommand = sdaCommand;
|
|
sdaDataAdapter.Fill(dataSet);
|
|
}
|
|
|
|
SetDNAME(dataSet.Tables[0], "NemeId,BesorolasiFokozatTipusId,MunkakorTipusId,MunkaviszonyTipusId,FelhasznaloSzuletesiOrszag,FelhasznaloAllampolgarsag1,FelhasznaloAllampolgarsag2,VezetoiOraszamOka,JogviszonyStatusza");
|
|
SetBoolFields(dataSet.Tables[0], "IsFoallas,IsTartosanTavolLevo,IsBetoltetlenAllashely,IsNyugdijas,IsKozpontilagSzinkronizalt");
|
|
|
|
return dataSet;
|
|
}
|
|
}
|
|
|
|
public DataSet GetEszkozIgenylesAlkalmazottakExportData(int tanevId, bool isKellTovabbiMunkaugyiAdatok)
|
|
{
|
|
using (var sdaCommand = new SDACommand())
|
|
{
|
|
sdaCommand.Connection = UserContext.Instance.SDAConnection;
|
|
sdaCommand.Transaction = UserContext.Instance.SDATransaction;
|
|
sdaCommand.CommandType = CommandType.StoredProcedure;
|
|
sdaCommand.CommandText = "uspGetEszkozIgenylesAlkalmazottakExportData";
|
|
|
|
sdaCommand.Parameters.Add("pTanevId", tanevId);
|
|
sdaCommand.Parameters.Add("pIsKellTovabbiMunkaugyiAdatok", isKellTovabbiMunkaugyiAdatok);
|
|
|
|
var dataSet = new DataSet();
|
|
using (var sdaDataAdapter = new SDADataAdapter())
|
|
{
|
|
sdaDataAdapter.SelectCommand = sdaCommand;
|
|
sdaDataAdapter.Fill(dataSet);
|
|
}
|
|
|
|
|
|
return dataSet;
|
|
}
|
|
}
|
|
|
|
public void FullUpdate(IAlkalmazott dto)
|
|
{
|
|
var entity = (Alkalmazott)dto;
|
|
|
|
entity.FullUpdate();
|
|
|
|
DalHelper.Commit();
|
|
}
|
|
|
|
public DataSet GetAlkalmazottTovabbkepzesek(int alkalmazottId)
|
|
{
|
|
using (var sdaCommand = new SDACommand())
|
|
{
|
|
sdaCommand.Connection = UserContext.Instance.SDAConnection;
|
|
sdaCommand.Transaction = UserContext.Instance.SDATransaction;
|
|
sdaCommand.CommandType = CommandType.StoredProcedure;
|
|
sdaCommand.CommandText = "uspGetAlkalmazottTovabbkepzesek";
|
|
|
|
sdaCommand.Parameters.Add("pAlkalmazottId", alkalmazottId);
|
|
|
|
var dataSet = new DataSet();
|
|
using (var sdaDataAdapter = new SDADataAdapter())
|
|
{
|
|
sdaDataAdapter.SelectCommand = sdaCommand;
|
|
sdaDataAdapter.Fill(dataSet);
|
|
}
|
|
|
|
return dataSet;
|
|
}
|
|
}
|
|
|
|
private int? GetFollowupAlkalmazott(int id)
|
|
{
|
|
using (var command = new SDACommand())
|
|
{
|
|
command.Connection = UserContext.Instance.SDAConnection;
|
|
command.Transaction = UserContext.Instance.SDATransaction;
|
|
command.CommandType = CommandType.StoredProcedure;
|
|
command.CommandText = "uspGetFollowupAlkalmazott";
|
|
|
|
command.Parameters.Add("pId", id);
|
|
|
|
var result = command.ExecuteScalar();
|
|
if (result != null && result != System.DBNull.Value)
|
|
{
|
|
return (int)result;
|
|
}
|
|
return null;
|
|
}
|
|
}
|
|
|
|
public int GetKovTanevIdByAktTanevId(int id)
|
|
{
|
|
using (var command = new SDACommand())
|
|
{
|
|
command.Connection = UserContext.Instance.SDAConnection;
|
|
command.Transaction = UserContext.Instance.SDATransaction;
|
|
command.CommandType = CommandType.StoredProcedure;
|
|
command.CommandText = "uspGetKovTanevIdByAktTanevId";
|
|
|
|
command.Parameters.Add("pId", id);
|
|
|
|
var result = command.ExecuteScalar();
|
|
int.TryParse(result?.ToString(), out int res);
|
|
return res;
|
|
}
|
|
}
|
|
|
|
public bool AnyKozpontilagSzinkorizaltAlkalmazottFromList(List<int> alkalmazottIdList, int tanevId)
|
|
{
|
|
var alkalmazottIdsString = SqlLogic.ParseListToParameter(alkalmazottIdList).ToString();
|
|
|
|
using (var command = new SDACommand())
|
|
{
|
|
command.Connection = UserContext.Instance.SDAConnection;
|
|
command.Transaction = UserContext.Instance.SDATransaction;
|
|
command.CommandType = CommandType.StoredProcedure;
|
|
command.CommandText = "uspAnyKozpontilagSzinkorizaltAlkalmazottFromList";
|
|
|
|
command.Parameters.Add("pAlkalmazottIdsString", alkalmazottIdsString);
|
|
command.Parameters.Add("pTanevId", tanevId);
|
|
|
|
var dataSet = new DataSet();
|
|
using (var sdaDataAdapter = new SDADataAdapter())
|
|
{
|
|
sdaDataAdapter.SelectCommand = command;
|
|
sdaDataAdapter.Fill(dataSet);
|
|
}
|
|
|
|
return dataSet.Tables[0].Rows.Count > 0;
|
|
}
|
|
}
|
|
|
|
public int? GetSzervezetId(int tanevId, int alkalmazottId)
|
|
{
|
|
using (var command = new SDACommand())
|
|
{
|
|
command.Connection = UserContext.Instance.SDAConnection;
|
|
command.Transaction = UserContext.Instance.SDATransaction;
|
|
|
|
command.Parameters.Add("pTanevId", tanevId);
|
|
command.Parameters.Add("pAlkalmazottId", alkalmazottId);
|
|
|
|
command.CommandText = @"
|
|
SELECT ma.C_SZERVEZETID
|
|
FROM T_MUNKAUGYIADATOK_OSSZES AS ma
|
|
INNER JOIN T_ALKALMAZOTT_OSSZES AS a ON a.ID = ma.C_ALKALMAZOTTID
|
|
AND a.C_ALTANEVID = ma.C_TANEVID
|
|
AND a.TOROLT = 'F'
|
|
WHERE ma.C_ALKALMAZOTTID = @pAlkalmazottId
|
|
AND ma.C_TANEVID = @pTanevId
|
|
AND ma.TOROLT = 'F'
|
|
";
|
|
|
|
var result = KretaConvert.ToNullableInt32(command.ExecuteScalar());
|
|
return result;
|
|
}
|
|
}
|
|
}
|
|
}
|