kreta/Kreta.DataAccessManual/OralatogatasDAL.cs

using System.Collections.Generic;
using System.Data;
using System.Linq;
using Kreta.Core;
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 OralatogatasDAL : DataAccessBase, IOralatogatasDal
    {
        public OralatogatasDAL(DalHandler handler) : base(handler)
        {
        }

        public OralatogatasDAL(DalHandler handler, GridParameters gridParameters) : base(handler, gridParameters)
        {
        }

        public DataSet GetOralatogatasok(int tanevId, OralatogatasokKeresesePCO pco)
        {
            using (var command = new SDACommand())
            {
                command.Connection = UserContext.Instance.SDAConnection;
                command.Transaction = UserContext.Instance.SDATransaction;
                command.CommandType = CommandType.StoredProcedure;

                command.CommandText = "uspGetOralatogatasData";
                command.Parameters.Add("pTanevId", tanevId);
                command.Parameters.Add("pFelhasznaloId", pco.FelhasznaloId);

                if (pco.IdoszakKezdete.HasValue)
                {
                    command.Parameters.Add("pTol", pco.IdoszakKezdete.Value);
                }

                if (pco.IdoszakVege.HasValue)
                {
                    command.Parameters.Add("pIg", pco.IdoszakVege.Value);
                }

                command.Parameters.Add("pIsTanar", pco.IsTanar);
                if (pco.TanarId.IsEntityId())
                {
                    command.Parameters.Add("pTanarId", pco.TanarId);
                }

                if (pco.LatogatottPedagogusId.IsEntityId())
                {
                    command.Parameters.Add("pLatogatottPedagogusId", pco.LatogatottPedagogusId);
                }

                command.Parameters.Add("pIsKellKapcsolodoCsoportok", pco.IsKellKapcsolodoCsoportok);
                if (pco.OsztalyCsoportId.IsEntityId())
                {
                    command.Parameters.Add("pOsztalyCsoportId", pco.OsztalyCsoportId.Value);
                }

                if (pco.TantargyId.IsEntityId())
                {
                    command.Parameters.Add("pTantargyId", pco.TantargyId.Value);
                }

                if (pco.FeladatKategoriaId.IsEntityId())
                {
                    command.Parameters.Add("pFeladatKategoriaId", pco.FeladatKategoriaId.Value);
                }

                if (pco.FeladatEllatasiHelyId.IsEntityId())
                {
                    command.Parameters.Add("pFeladatEllatasiHelyId", pco.FeladatEllatasiHelyId.Value);
                }

                var ds = new DataSet();

                using (var adapter = new SDADataAdapter())
                {
                    adapter.SelectCommand = command;
                    adapter.Fill(ds);
                }

                SetBoolFields(ds.Tables[0], "IsSajatErtekeles");
                return ds;
            }
        }

        private void Delete(int id)
        {
            var oralatogatas = Get(id) as Oralatogatas;
            oralatogatas.Delete();
            DalHelper.Commit();
        }

        public void Delete(int oraId, int tanarId)
        {
            var ora = DalHelper.TanitasiOra().Get(oraId);

            var oraLatogatas = ora.Oralatogatasok.FirstOrDefault(x => x.ErtekeloId == tanarId && !x.Torolt);
            if (oraLatogatas != null)
            {
                INemKotottMunkaidoDal nemKotottMunkaidoDal = DalHelper.NemKotottMunkaido();
                var nkmId = nemKotottMunkaidoDal.GetNemKotottMunkaidoId(oraLatogatas.ID, tanarId);

                if (nkmId.IsEntityId())
                { nemKotottMunkaidoDal.Delete(nkmId); }

                Delete(oraLatogatas.ID);
            }
        }

        public void Insert(IOralatogatas dto)
        {
            var entity = dto as Oralatogatas;
            entity.Insert();
            DalHelper.Commit();
        }

        public void FullUpdate(IOralatogatas dto)
        {
            var entity = dto as Oralatogatas;
            entity.FullUpdate();
            DalHelper.Commit();
        }

        public IOralatogatas Get(int id)
        {
            var entity = Oralatogatas.GiveAnInstance();
            entity.LoadByID(id);
            return entity;
        }

        public IOralatogatas Get()
        {
            return Oralatogatas.GiveAnInstance();
        }

        public DataSet OralatogatasUtkozes(int tanarId, int oraId)
        {
            var ora = DalHelper.TanitasiOra().Get(oraId);

            var parameters = new List<CommandParameter>
            {
                new CommandParameter("pTanarId", tanarId),
                new CommandParameter("pKezdet", ora.OraKezdete),
                new CommandParameter("pVeg", ora.OraVege)
            };

            var commandText = @"
            SELECT 
                 COUNT(1) AS Count
                ,CONVERT(bit, 1) AS IsTanitasiOra 
            FROM T_TANITASIORA t
            WHERE t.TOROLT = 'F'
                AND (t.C_HELYETTESITOTANARID = @pTanarId OR (t.C_TANARID = @pTanarId AND t.C_HELYETTESITOTANARID IS NULL)) 
                AND t.C_ORAKEZDETE < @pVeg AND t.C_ORAVEGE > @pKezdet
            UNION ALL
            SELECT 
                 COUNT(1) AS Count
                ,CONVERT(bit, 0) AS IsTanitasiOra 
            FROM T_NEMKOTOTTMUNKAIDO nkm
            WHERE nkm.TOROLT = 'F'
                AND nkm.C_TANARID = @pTanarId 
                AND nkm.C_ADMINALTALTOROLT = 'F'
                AND nkm.C_KEZDETE < @pVeg AND nkm.C_VEGE > @pKezdet
            ";

            var ds = GetData(commandText, parameters);
            return ds;
        }
    }
}