using System.Collections.Generic;
using System.Data;
using Kreta.DataAccessManual.Interfaces;
using Kreta.DataAccessManual.Util;
using Kreta.Framework.Util;

namespace Kreta.DataAccessManual
{
    internal class LeptetesDal : DataAccessBase, ILeptetesDal
    {
        public LeptetesDal(DalHandler handler, GridParameters parameters) : base(handler, parameters)
        {
        }

        public LeptetesDal(DalHandler handler) : base(handler)
        {
        }

        public DataSet GetOsztalyLeptetesGridData(int tanevId)
        {
            string commandText = @"
                select
                    o.ID as ID
                    ,t.C_NEV as Tanev
                    ,ocs.C_NEV as OsztalyNeve
                    ,dic.C_NAME as Evfolyam
                    ,dic.ID as EvfolyamId
                    ,dic.C_ORDER as DicOrder
                    ,ISNULL(kovDic.C_NAME, 'Na') as KovEvfolyam
                    ,ISNULL(evtip.C_KOVETKEZOEVFOLYAMTIPUSID, 1296) as KovEvfolyamId
                from T_OSZTALY o
                join T_OSZTALYCSOPORT ocs on ocs.ID = o.ID and ocs.TOROLT = 'F'
                join T_TANEV t on t.ID = o.C_ALTANEVID and t.TOROLT = 'F'
                join T_DICTIONARYITEMBASE dic on dic.ID = ocs.C_EVFOLYAMTIPUSA and dic.C_TANEVID = t.ID
                join T_EVFOLYAMTIPUS evtip on evtip.ID = dic.ID and dic.C_TANEVID = evtip.C_ALTANEVID
                left join T_DICTIONARYITEMBASE kovDic on kovDic.ID = evtip.C_KOVETKEZOEVFOLYAMTIPUSID and kovDic.C_TANEVID = t.ID
                where
                  o.TOROLT = 'F' and o.C_ALTANEVID = :pTanevId
            ";

            var param = new List<CommandParameter>
            {
                new CommandParameter("pTanevId", tanevId),
            };

            return GetData(commandText, param);
        }

        public DataSet GetCsoportLeptetesGridData(int tanevId)
        {
            string commandText = @"
                select
                    cs.ID as ID
                    ,t.C_NEV as Tanev
                    ,ocs.C_NEV as CsoportNeve
                    ,dic.C_NAME as Evfolyam
                    ,dic.ID as EvfolyamId
                    ,cs.C_OSZTALYBONTASID as OsztalybontasId
                  ,obcs.C_NEV as Osztalybontas
                    ,ISNULL(kovDic.C_NAME, 'Na') as KovEvfolyam
                    ,ISNULL(evtip.C_KOVETKEZOEVFOLYAMTIPUSID, 1296) as KovEvfolyamId
                from T_CSOPORT cs
                join T_OSZTALYCSOPORT ocs on ocs.ID = cs.ID and ocs.TOROLT = 'F'
                join T_TANEV t on t.ID = cs.C_ALTANEVID and t.TOROLT = 'F'
                join T_DICTIONARYITEMBASE dic on dic.ID = ocs.C_EVFOLYAMTIPUSA and dic.C_TANEVID = t.ID
                join T_EVFOLYAMTIPUS evtip on evtip.ID = dic.ID and dic.C_TANEVID = evtip.C_ALTANEVID
                left join T_DICTIONARYITEMBASE kovDic on kovDic.ID = evtip.C_KOVETKEZOEVFOLYAMTIPUSID and kovDic.C_TANEVID = t.ID
                left join T_OSZTALYCSOPORT obcs on obcs.ID = cs.C_OSZTALYBONTASID and obcs.TOROLT = 'F'
                where
                  cs.TOROLT = 'F' and cs.C_ALTANEVID = :pTanevId
                    AND cs.C_TIPUSA <> 1067   --Alapf. müv. okt. zeneművészeti csoport - egyéni főtanszak
            ";

            var param = new List<CommandParameter>
            {
                new CommandParameter("pTanevId", tanevId),
            };

            return GetData(commandText, param);
        }
    }
}