using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using Kreta.DataAccessManual.Interfaces;
using Kreta.DataAccessManual.Util;

namespace Kreta.DataAccessManual
{
    internal class ElearningElerhetosegDal : DataAccessBase, IElearningElerhetosegDal
    {
        public ElearningElerhetosegDal(DalHandler handler) : base(handler)
        {
        }

        public DataSet GetElearningElerhetosegDataSet(int role, List<string> packages, int tanevId, int intezmenyId, bool isGondviselo, bool isTanulo, int userId, int? gondviseloId)
        {

            var paramsList = new List<CommandParameter>();
            paramsList.Add(new CommandParameter("pTanevId", tanevId));
            paramsList.Add(new CommandParameter("pIntezmenyId", intezmenyId));
            paramsList.Add(new CommandParameter("pSzerepkorTipusId", role));
            paramsList.Add(new CommandParameter("pUserId", userId));
            if (gondviseloId.HasValue)
            { paramsList.Add(new CommandParameter("pGondviseloId", gondviseloId)); }
            else
            { paramsList.Add(new CommandParameter("pGondviseloId", DBNull.Value)); }

            var command = new StringBuilder(@"
            SELECT distinct
             elearningElerhetoseg.C_ELEARNINGAZONOSITO    azonosito
            ,elearningElerhetoseg.C_KEZDODATUM            elerhetosegKezdete
            ,elearningElerhetoseg.C_VEGDATUM              elerhetosegVege
                      ,elearningElerhetoseg.C_SZEREPKORTIPUSID      szerepkorTipusId
            FROM
                T_ELEARNINGELERHETOSEG_OSSZES elearningElerhetoseg
            WHERE
                elearningElerhetoseg.C_TANEVID = :pTanevId
                AND elearningElerhetoseg.C_INTEZMENYID = :pIntezmenyId
                AND elearningElerhetoseg.Torolt = 'F'
                AND elearningElerhetoseg.C_SZEREPKORTIPUSID = :pSzerepkorTipusId
                AND elearningElerhetoseg.C_ROLEPACKAGE in (" + string.Join(",", packages.Select(x => string.Format("'{0}'", x))) + @")
            ");

            if (isGondviselo)
            {
                command.Append(" AND elearningElerhetoseg.C_ISGONDVISELONELMEGJELENIK = 'T'");
            }
            if (isTanulo)
            {
                command.Append(" AND elearningElerhetoseg.C_ISTANULONALMEGJELENIK = 'T'");
            }
            command.Append(@"
            UNION
            SELECT distinct
             elearningElerhetoseg.C_ELEARNINGAZONOSITO    azonosito
            ,elearningElerhetoseg.C_KEZDODATUM            elerhetosegKezdete
            ,elearningElerhetoseg.C_VEGDATUM              elerhetosegVege
                      ,elearningElerhetoseg.C_SZEREPKORTIPUSID      szerepkorTipusId
            FROM
                T_ELEARNINGFELHASZNALO_OSSZES ef
            INNER JOIN T_ELEARNINGELERHETOSEG_OSSZES elearningElerhetoseg ON ef.C_ELEARNINGELERHETOSEGID =  elearningElerhetoseg.ID AND elearningElerhetoseg.TOROLT ='F'
            WHERE
                ef.C_TANEVID = @pTanevId
                AND ef.C_INTEZMENYID = @pIntezmenyId
                AND ef.C_FELHASZNALOID = @pUserId
                AND (ef.C_GONDVISELOID = @pGondviseloId OR @pGondviseloId is null)
                AND ef.Torolt = 'F'
            ");

            var ds = this.GetData(command.ToString(), paramsList);

            return ds;
        }
    }
}