<?php
/**
 * @copyright (c) Kufer Software GmbH, Loisachstrasse 4, D-84453 Mühldorf
 */

declare(strict_types = 1);

namespace Kufer\Web\Classes\Dbfilter;

use Kufer\Web\Classes\Identifier\Identifier;


final class DozentenSQLFilter extends PersonenNrSQLFilter
{
  public const FILTER_OPTION_EINSATZBEZIRK = 'einsatzbezirk';
  public const FILTER_OPTION_BRANCHE = 'branche';
  public const FILTER_OPTION_MITARBEITER_ODER_LEITER_VON_VERANSTALTER = 'mitarbeiterOderLeiterVonVeranstalter';
  public const FILTER_OPTION_DOZENTEN2 = 'dozenten2';
  public const FILTER_OPTION_DOZENTEN_BEZIRKE = 'dozentenbezirke';
  public const FILTER_OPTION_DOZENTEN = 'dozenten';
  public const FILTER_OPTION_KENNZEICHEN = 'kennzeichen';
  public const FILTER_OPTION_BEREICHSRECHTE = 'bereichsrechte';
  public const FILTER_OPTION_KURSE = 'kurse';

  protected function __construct()
  {
    parent::__construct();

    $this->registerDb('Inaktiv', 'inaktiv', static::TYPE_BOOLEAN_W_F);
    $this->registerDb('Verstorben', 'verstorben', static::TYPE_BOOLEAN_W_F);
    $this->registerDb('ShownOnWeb', 'internet', static::TYPE_BOOLEAN_NOT_W_F);
    $this->registerDb('ShownOnWebInKurs', 'webdoznichtkurs', static::TYPE_BOOLEAN_NOT_W_F);
    $this->registerDb('Typ', 'typ', static::TYPE_STRING);
    $this->registerDb('Geschlecht', 'geschlecht', static::TYPE_STRING);
    $this->registerDb('Personalnummer', 'personalnr', static::TYPE_STRING);

    for ($i = 1; $i <= 16; $i++) {
      $this->registerDb("Freidef$i", "krit$i", static::TYPE_STRING);
    }

    $fnAddToBuilder = $this->addToBuilder();

    $this->registerForeignDb(
      'Fachbereich',
      $fnAddToBuilder,
      static function(\DBOperation $dboperation, $fachbereich): \SelectQueryBuilder {
        $dbwFaecher = new \DBWorkFachAdr($dboperation->dbnr);

        return $dbwFaecher->findByKennzQuery($fachbereich)->select('nr');
      }
    );

    $this->registerForeignDb(
      'FachId',
      $fnAddToBuilder,
      static function(\DBOperation $dboperation, $value): \SelectQueryBuilder {
        $builder = new \SelectQueryBuilder($dboperation);

        return $builder->select('dnr')
                ->from('doz_fach')
                ->and('fach_nr = :fachId:')
                ->paramInt('fachId', $value);
      }
    );

    $this->registerForeignDb(
      'EinsatzbezirkName',
      $fnAddToBuilder,
      static function(\DBOperation $dboperation, $einsatzbezirk): \SelectQueryBuilder {
        return (new \SelectQueryBuilder($dboperation))
          ->select('dnr')
          ->from('doz_bezirk')
          ->where('bezirk LIKE :einsatzbezirk:')
          ->paramString('einsatzbezirk', $einsatzbezirk);
      }
    );

    $this->registerForeignDb(
      'BranchenText',
      $fnAddToBuilder,
      function(\DBOperation $dboperation, $branchename): \SelectQueryBuilder {
        $dbwBranchen = new \DBWorkBranchen($dboperation->dbnr);

        return $dbwBranchen->getDnrsByBranchenNameQuery($branchename);
      }
    );
  }

  public function getTablePraeffix(): string
  {
    return 'd';
  }

  public function getDbTyp(): string
  {
    return 'D';
  }

  
  public function isMitarbeiterOderLeiterVonVeranstalter(): IFilter
  {
    $this->applyFilterCurrent(
      static::FILTER_OPTION_MITARBEITER_ODER_LEITER_VON_VERANSTALTER,
      function(\SelectQueryBuilder $builder): \SelectQueryBuilder {
        $dbwBeziehung = \DBWorkBeziehung::getDefaultInstance($builder->getDBOperation()->dbnr);
        $var = $builder->uniqueVar();

        return $builder->and(":table:.nr IN (:$var:)")
          ->paramSelectQuery($var, $dbwBeziehung->getMitarbeiterUndLeiterQuery()->select('bz.nr2'));
      }
    );

    return $this;
  }

  public function hasBereichsrechte(\Benutzer $user): DozentenSQLFilter
  {
    $this->applyFilterCurrent(
      static::FILTER_OPTION_BEREICHSRECHTE,
      function(\SelectQueryBuilder $builder) use ($user): \SelectQueryBuilder {
        $bereichsrechteSql = \BereichsrechteAPIDozent::buildBereichsRechteSQLStrBase($user->getIdentifier(), $this->getTablePraeffix() . '.', $builder->getDBOperation()->dbparser);

        return $builder
            ->and($bereichsrechteSql);
      }
    );

    return $this;
  }

  public function applyDozenten2Filter(Dozenten2SQLFilter $filter): DozentenSQLFilter
  {
    $filter->makeImmutable();

    if ($filter->isEmpty()) {
      return $this;
    }

    $this->applyFilterCurrent(
      static::FILTER_OPTION_DOZENTEN2,
      function(\SelectQueryBuilder $builder) use ($filter): \SelectQueryBuilder {
        $doz2Builder = $filter->createBuilder($builder->getDBOperation())
          ->select('nr')
          ->from('dozenten2', 'd2');

        return \call_user_func($this->addToBuilder(), $doz2Builder, $builder);
      }
    );

    return $this;
  }

  public function applyDozentenBezirkeFilter(DozentenBezirkeSQLFilter $filter): DozentenSQLFilter
  {
    $filter->makeImmutable();

    if ($filter->isEmpty()) {
      return $this;
    }

    $this->applyFilterCurrent(
      static::FILTER_OPTION_DOZENTEN_BEZIRKE,
      function(\SelectQueryBuilder $builder) use ($filter): \SelectQueryBuilder {
        $dozBezirkeBuilder = $filter->createBuilder($builder->getDBOperation())
          ->select('dnr')
          ->from('doz_bezirk');

        return \call_user_func($this->addToBuilder(), $dozBezirkeBuilder, $builder);
      }
    );

    return $this;
  }

  public function applyVeranstalterFilter(DozentenSQLFilter $filter): DozentenSQLFilter
  {
    $filter->makeImmutable();

    if ($filter->isEmpty()) {
      return $this;
    }

    $this->applyFilterCurrent(
      static::FILTER_OPTION_DOZENTEN,
      function(\SelectQueryBuilder $builder) use ($filter): \SelectQueryBuilder {
        if ($filter->isEmpty()) {
          return $builder;
        }

        $dozBuilder = ((new \DBWorkDozenten($builder->getDBOperation()->dbnr)))->findByFilterQuery($filter)
          ->select('d.nr');

        $dbwBeziehung = new \DBWorkBeziehung($builder->getDBOperation()->dbnr);
        $dozVeranstalterBuilder = $dbwBeziehung->getMitarbeiterUndLeiterDnrsVonVeranstalterQuery($dozBuilder);

        return \call_user_func($this->addToBuilder(), $dozVeranstalterBuilder, $builder);
      }
    );

    return $this;
  }

  public function applyKennzeichenFilter(KennzeichenSQLFilter $filter): DozentenSQLFilter
  {
    $filter->makeImmutable();

    if ($filter->isEmpty()) {
      return $this;
    }

    $this->applyFilterCurrent(
      static::FILTER_OPTION_KENNZEICHEN,
      function(\SelectQueryBuilder $builder) use ($filter): \SelectQueryBuilder {
        $kennzBuilder = (new \DBWorkKennz($builder->getDBOperation()->dbnr))->findByFilterQuery($filter)
          ->select('ke.kennz');

        $kennzAdBuilder = (new \DBWorkKennzAd($builder->getDBOperation()->dbnr))->getAllQuery()
          ->select('ka.nr')
          ->and('ka.kennz IN(:kennzQuery:)')
          ->and('ka.typ = :typD:')
          ->paramSelectQuery('kennzQuery', $kennzBuilder)
          ->paramString('typD', 'D');

        return \call_user_func($this->addToBuilder(), $kennzAdBuilder, $builder);
      }
    );

    return $this;
  }

  public function applyKurseFilter(KurseSQLFilter $filter): DozentenSQLFilter
  {
    $filter->makeImmutable();

    if ($filter->isEmpty()) {
      return $this;
    }

    $this->applyFilterCurrent(
      static::FILTER_OPTION_KURSE,
      function(\SelectQueryBuilder $builder) use ($filter): \SelectQueryBuilder {
        $dozKurseFilter = DozentenKurseSQLFilter::create();
        $dozKurseFilter->applyKurseFilter($filter);

        $dozKurseBuilder = (\DBWorkDozKurs::getDefaultInstance($builder->getDBOperation()->dbnr))->findByFilterQuery($dozKurseFilter)
          ->select('dozent');

        return \call_user_func($this->addToBuilder(), $dozKurseBuilder, $builder);
      }
    );

    return $this;
  }
}