333 lines
14 KiB
C#
333 lines
14 KiB
C#
using System.Collections.Generic;
|
|
using System.Data;
|
|
using System.Text;
|
|
using Kreta.DataAccess.Interfaces;
|
|
using Kreta.DataAccessManual.Interfaces;
|
|
using Kreta.DataAccessManual.ParameterClasses;
|
|
using Kreta.DataAccessManual.Util;
|
|
using Kreta.Framework;
|
|
using Kreta.Framework.Util;
|
|
using SDA.DataProvider;
|
|
using SDA.Kreta.Entities;
|
|
|
|
namespace Kreta.DataAccessManual
|
|
{
|
|
internal class OratervDAL : DataAccessBase, IOratervDal
|
|
{
|
|
public OratervDAL(DalHandler handler) : base(handler)
|
|
{ }
|
|
|
|
public OratervDAL(DalHandler handler, GridParameters parameters) : base(handler, parameters)
|
|
{ }
|
|
|
|
public DataSet GetOraterv(OratervPCO pco)
|
|
{
|
|
var parameters = new List<CommandParameter>();
|
|
StringBuilder commandText = new StringBuilder();
|
|
commandText.Append(@"SELECT ID as ID
|
|
,ID as OratervId
|
|
,ot.C_EVFOLYAM as EvFolyam
|
|
,ot.C_NEV as Nev
|
|
,ot.C_TANTERVID as TantervId
|
|
FROM T_ORATERV_OSSZES ot
|
|
WHERE
|
|
ot.TOROLT = 'F'
|
|
AND ot.C_TANEVID = :pTanevId");
|
|
|
|
if (pco.TantervId.HasValue && pco.TantervId.Value > 0)
|
|
{
|
|
commandText.Append($" AND ot.C_TANTERVID = @{nameof(pco.TantervId)} ");
|
|
parameters.Add(new CommandParameter(nameof(pco.TantervId), pco.TantervId.Value));
|
|
}
|
|
|
|
if (pco.EvfolyamID.HasValue && pco.EvfolyamID.Value > 0)
|
|
{
|
|
commandText.Append($" AND ot.C_EVFOLYAM = @{nameof(pco.EvfolyamID)} ");
|
|
parameters.Add(new CommandParameter(nameof(pco.EvfolyamID), pco.EvfolyamID.Value));
|
|
}
|
|
|
|
if (!string.IsNullOrWhiteSpace(pco.Nev))
|
|
{
|
|
if (pco.IsValidacio)
|
|
{
|
|
commandText.Append($" AND ot.C_NEV = @{nameof(pco.Nev)} ");
|
|
}
|
|
else
|
|
{
|
|
commandText.Append($" AND ot.C_NEV LIKE '%' + @{nameof(pco.Nev)} + '%' ");
|
|
}
|
|
parameters.Add(new CommandParameter(nameof(pco.Nev), pco.Nev));
|
|
}
|
|
|
|
parameters.Add(new CommandParameter("pTanevId", pco.TanevId));
|
|
|
|
return GetData(commandText.ToString(), parameters: parameters, dictionaryItemColumns: "EvFolyam");
|
|
}
|
|
|
|
public DataSet GetOratervTantargy(int ID, int tanevId)
|
|
{
|
|
var parameters = new List<CommandParameter>();
|
|
parameters.Add(new CommandParameter("pTanevId", tanevId));
|
|
|
|
string commandText = string.Format(@"
|
|
SELECT
|
|
ott.ID as ID
|
|
,ott.ID as OratervTargyID
|
|
,ott.C_EVESORASZAM as EvesSorszam
|
|
,tt.C_NEV as Tantargy
|
|
,ott.C_ORATERVID as OraTervId
|
|
FROM T_ORATERVTARGY_OSSZES ott
|
|
JOIN T_TANTARGY_OSSZES tt on tt.ID = ott.C_TANTARGYID AND tt.TOROLT = 'F'
|
|
WHERE
|
|
ott.TOROLT = 'F' AND ott.C_ORATERVID = {0} AND ott.C_TANEVID = :pTanevId
|
|
", ID);
|
|
|
|
return GetData(commandText, parameters: parameters);
|
|
}
|
|
|
|
public DataSet GetOratervTantargyakById(int oratervID, int tanevId)
|
|
{
|
|
var parameters = new List<CommandParameter>();
|
|
string commandText = string.Format(@"
|
|
SELECT
|
|
ott.ID as ID
|
|
FROM T_ORATERVTARGY_OSSZES ott
|
|
WHERE
|
|
ott.TOROLT = 'F' AND ott.C_ORATERVID = {0} AND ott.C_TANEVID = {1}
|
|
", oratervID, tanevId);
|
|
|
|
return GetData(commandText, parameters: parameters);
|
|
}
|
|
|
|
public DataSet GetTantargyak(int ID, int tanevId)
|
|
{
|
|
var parameters = new List<CommandParameter>();
|
|
|
|
string commandText = string.Format(@"
|
|
SELECT
|
|
tt.ID
|
|
,tt.C_NEV
|
|
FROM T_TANTARGY_OSSZES tt WHERE tt.TOROLT = 'F' AND tt.C_TANEVID = {1}
|
|
EXCEPT
|
|
SELECT
|
|
tt.ID
|
|
,tt.C_NEV
|
|
FROM T_ORATERVTARGY_OSSZES ott
|
|
JOIN T_TANTARGY_OSSZES tt on tt.ID = ott.C_TANTARGYID AND tt.TOROLT = 'F'
|
|
WHERE ott.TOROLT = 'F' AND ott.C_ORATERVID = {0} AND tt.C_TANEVID = {1}
|
|
", ID, tanevId);
|
|
|
|
return GetData(commandText, parameters: parameters);
|
|
}
|
|
|
|
public DataSet GetTantargyakModOraTervTantargy(int OraTervID, int OraTervTantargyID, int tanevId)
|
|
{
|
|
var parameters = new List<CommandParameter>();
|
|
parameters.Add(new CommandParameter("pTanevId", tanevId));
|
|
|
|
string commandText = string.Format(@"
|
|
SELECT
|
|
tt.ID
|
|
,tt.C_NEV
|
|
FROM T_TANTARGY_OSSZES tt WHERE tt.TOROLT = 'F' AND tt.C_TANEVID = :pTanevId
|
|
EXCEPT
|
|
SELECT
|
|
tt.ID
|
|
,tt.C_NEV
|
|
FROM T_ORATERVTARGY_OSSZES ott
|
|
JOIN T_TANTARGY_OSSZES tt on tt.ID = ott.C_TANTARGYID AND tt.TOROLT = 'F'
|
|
WHERE ott.TOROLT = 'F' AND ott.C_ORATERVID = {0} AND tt.C_TANEVID = :pTanevId
|
|
UNION
|
|
SELECT
|
|
tt.ID
|
|
,tt.C_NEV
|
|
FROM T_ORATERVTARGY_OSSZES ott
|
|
JOIN T_TANTARGY_OSSZES tt on tt.ID = ott.C_TANTARGYID AND tt.TOROLT = 'F'
|
|
WHERE ott.TOROLT = 'F' AND ott.ID = {1} AND tt.C_TANEVID = :pTanevId
|
|
", OraTervID, OraTervTantargyID);
|
|
|
|
return GetData(commandText, parameters: parameters);
|
|
}
|
|
|
|
public int IfModifyOrDeleteOraTerv(int ID)
|
|
{
|
|
var parameters = new List<CommandParameter>();
|
|
string commandText = string.Format(@"
|
|
SELECT
|
|
COUNT(*) as CNT
|
|
FROM T_ORATERVTARGY ott
|
|
WHERE ott.TOROLT = 'F' AND ott.C_ORATERVID = {0}
|
|
", ID);
|
|
|
|
var CommandText = commandText;
|
|
DataSet ds = GetData(CommandText, parameters: parameters);
|
|
return int.Parse(ds.Tables[0].Rows[0]["CNT"].ToString());
|
|
}
|
|
|
|
public void Insert(IOraTervTargy entity)
|
|
{
|
|
((OraTervTargy)entity).Insert();
|
|
FollowUp(entity);
|
|
DalHelper.Commit();
|
|
}
|
|
|
|
public void FullUpdate(IOraTervTargy entity)
|
|
{
|
|
((OraTervTargy)entity).FullUpdate();
|
|
FollowUp(entity);
|
|
DalHelper.Commit();
|
|
}
|
|
|
|
public void Delete(IOraTervTargy entity)
|
|
{
|
|
((OraTervTargy)entity).Delete();
|
|
DalHelper.Commit();
|
|
}
|
|
|
|
public void Insert(IOraTerv entity)
|
|
{
|
|
((OraTerv)entity).Insert();
|
|
FollowUp(entity);
|
|
DalHelper.Commit();
|
|
}
|
|
|
|
public void FullUpdate(IOraTerv entity)
|
|
{
|
|
((OraTerv)entity).FullUpdate();
|
|
FollowUp(entity);
|
|
DalHelper.Commit();
|
|
}
|
|
|
|
public void Delete(IOraTerv entity)
|
|
{
|
|
((OraTerv)entity).Delete();
|
|
DalHelper.Commit();
|
|
}
|
|
public IOraTerv Get() => OraTerv.GiveAnInstance();
|
|
public IOraTervTargy GetOraTervTargy() => OraTervTargy.GiveAnInstance();
|
|
public IOraTerv Get(int id)
|
|
{
|
|
var entity = OraTerv.GiveAnInstance();
|
|
entity.LoadByID(id);
|
|
return entity;
|
|
}
|
|
public IOraTervTargy GetOraTervTargy(int id)
|
|
{
|
|
var entity = OraTervTargy.GiveAnInstance();
|
|
entity.LoadByID(id);
|
|
return entity;
|
|
}
|
|
|
|
public void FollowUpOraterv(int intezmenyId, int aktTanevId, int kovetkezoTanevId, int oratervId)
|
|
{
|
|
using (SDACommand command = new SDACommand())
|
|
{
|
|
command.Connection = UserContext.Instance.SDAConnection;
|
|
command.Transaction = UserContext.Instance.SDATransaction;
|
|
command.CommandText = "uspFollowUpOraterv";
|
|
command.Parameters.Add("intezmenyId", intezmenyId);
|
|
command.Parameters.Add("aktTanevId", aktTanevId);
|
|
command.Parameters.Add("kovetkezoTanevId", kovetkezoTanevId);
|
|
command.Parameters.Add("oratervId", oratervId);
|
|
|
|
command.CommandType = CommandType.StoredProcedure;
|
|
command.ExecuteNonQuery();
|
|
DalHelper.Commit();
|
|
|
|
}
|
|
}
|
|
|
|
public void FollowUpOratervTargy(int intezmenyId, int aktTanevId, int kovetkezoTanevId, int oratervTargyId)
|
|
{
|
|
using (SDACommand command = new SDACommand())
|
|
{
|
|
command.Connection = UserContext.Instance.SDAConnection;
|
|
command.Transaction = UserContext.Instance.SDATransaction;
|
|
command.CommandText = "uspFollowUpOratervTargy";
|
|
command.Parameters.Add("intezmenyId", intezmenyId);
|
|
command.Parameters.Add("aktTanevId", aktTanevId);
|
|
command.Parameters.Add("kovetkezoTanevId", kovetkezoTanevId);
|
|
command.Parameters.Add("oratervTargyId", oratervTargyId);
|
|
|
|
command.CommandType = CommandType.StoredProcedure;
|
|
command.ExecuteNonQuery();
|
|
DalHelper.Commit();
|
|
}
|
|
}
|
|
|
|
public DataSet GetOratervDataSet(int tanevId)
|
|
{
|
|
using (var sdaCommand = new SDACommand())
|
|
{
|
|
string commandText = @"
|
|
SELECT
|
|
tt.ID AS TantervId
|
|
,tt.C_NEV AS TantervNev
|
|
,ot.ID AS Id
|
|
,ot.C_NEV AS OratervNev
|
|
,ot.C_EVFOLYAM AS EvfolyamId
|
|
FROM T_ORATERV_OSSZES ot
|
|
INNER JOIN T_TANTERV_OSSZES tt ON tt.ID = ot.C_TANTERVID AND tt.C_TANEVID = ot.C_TANEVID AND tt.TOROLT = 'F'
|
|
WHERE ot.C_TANEVID=:pTanevId
|
|
AND ot.TOROLT = 'F'
|
|
";
|
|
|
|
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];
|
|
SetDNAME(dataTable, "EvfolyamId");
|
|
|
|
return dataSet;
|
|
}
|
|
}
|
|
|
|
public DataSet GetOratervTantargyDataSet(int tanevId)
|
|
{
|
|
using (var sdaCommand = new SDACommand())
|
|
{
|
|
string commandText = @"
|
|
SELECT
|
|
ott.ID AS Id
|
|
,ott.C_ORATERVID AS OratervId
|
|
,ott.C_TANTARGYID AS TantargyId
|
|
,ott.C_EVESORASZAM AS EvesOraszam
|
|
,ot.C_NEV AS OratervNev
|
|
,t.C_NEV AS TantargyNev
|
|
FROM T_ORATERVTARGY ott
|
|
INNER JOIN T_ORATERV_OSSZES ot ON ot.ID = ott.C_ORATERVID AND ot.C_TANEVID = ott.C_TANEVID AND ot.TOROLT = 'F'
|
|
INNER JOIN T_TANTARGY_OSSZES t ON t.ID = ott.C_TANTARGYID AND t.C_TANEVID = ott.C_TANEVID AND t.TOROLT = 'F'
|
|
WHERE ott.C_TANEVID=:pTanevId
|
|
AND ott.TOROLT = 'F'
|
|
";
|
|
|
|
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);
|
|
}
|
|
|
|
return dataSet;
|
|
}
|
|
}
|
|
}
|
|
}
|