crmPRO/autoload/Domain/Users/VacationRepository.php

<?php
namespace Domain\Users;

class VacationRepository
{
  private $mdb;

  const TYPE_VACATION = 'urlop_wypoczynkowy';
  const TYPE_SICK = 'chorobowe';
  const TYPE_OTHER = 'inna_nieobecnosc';

  const TYPES = [
    self::TYPE_VACATION => 'Urlop wypoczynkowy',
    self::TYPE_SICK => 'Chorobowe',
    self::TYPE_OTHER => 'Inna nieobecność'
  ];

  const DEFAULT_DAYS_LIMIT = 26;

  private static bool $tables_checked = false;

  public function __construct( $mdb )
  {
    $this -> mdb = $mdb;

    if ( !self::$tables_checked )
    {
      $this -> ensureTables();
      self::$tables_checked = true;
    }
  }

  private function ensureTables(): void
  {
    $pdo = $this -> mdb -> pdo;

    $pdo -> exec( "
      CREATE TABLE IF NOT EXISTS `users_vacations` (
        `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
        `user_id` INT UNSIGNED NOT NULL,
        `date_from` DATE NOT NULL,
        `date_to` DATE NOT NULL,
        `type` VARCHAR(30) NOT NULL DEFAULT 'urlop_wypoczynkowy',
        `comment` TEXT,
        `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
        INDEX `idx_user_year` (`user_id`, `date_from`)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
    " );

    $pdo -> exec( "
      CREATE TABLE IF NOT EXISTS `users_vacation_limits` (
        `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
        `user_id` INT UNSIGNED NOT NULL,
        `year` SMALLINT UNSIGNED NOT NULL,
        `days_limit` TINYINT UNSIGNED NOT NULL DEFAULT 26,
        UNIQUE KEY `uq_user_year` (`user_id`, `year`)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
    " );
  }

  public function allByYear( int $year, ?int $user_id = null ): array
  {
    $where = [
      'AND' => [
        'date_from[>=]' => $year . '-01-01',
        'date_from[<=]' => $year . '-12-31'
      ],
      'ORDER' => [ 'date_from' => 'DESC' ]
    ];

    if ( $user_id )
      $where['AND']['user_id'] = $user_id;

    return $this -> mdb -> select( 'users_vacations', [
      '[>]users' => [ 'user_id' => 'id' ]
    ], [
      'users_vacations.id',
      'users_vacations.user_id',
      'users_vacations.date_from',
      'users_vacations.date_to',
      'users_vacations.type',
      'users_vacations.comment',
      'users.name',
      'users.surname'
    ], $where );
  }

  public function add( int $user_id, string $date_from, string $date_to, string $type, string $comment = '' )
  {
    if ( !isset( self::TYPES[ $type ] ) )
      return false;

    if ( $date_from > $date_to )
      return false;

    $this -> mdb -> insert( 'users_vacations', [
      'user_id' => $user_id,
      'date_from' => $date_from,
      'date_to' => $date_to,
      'type' => $type,
      'comment' => $comment
    ] );

    return $this -> mdb -> id();
  }

  public function getById( int $id ): ?array
  {
    $row = $this -> mdb -> get( 'users_vacations', '*', [ 'id' => $id ] );
    return $row ?: null;
  }

  public function update( int $id, int $user_id, string $date_from, string $date_to, string $type, string $comment = '' ): bool
  {
    if ( !isset( self::TYPES[ $type ] ) )
      return false;

    if ( $date_from > $date_to )
      return false;

    $this -> mdb -> update( 'users_vacations', [
      'user_id' => $user_id,
      'date_from' => $date_from,
      'date_to' => $date_to,
      'type' => $type,
      'comment' => $comment
    ], [ 'id' => $id ] );

    return true;
  }

  public function delete( int $id ): bool
  {
    $this -> mdb -> delete( 'users_vacations', [ 'id' => $id ] );
    return true;
  }

  public function getLimit( int $user_id, int $year ): int
  {
    $row = $this -> mdb -> get( 'users_vacation_limits', 'days_limit', [
      'user_id' => $user_id,
      'year' => $year
    ] );

    return $row !== null ? (int) $row : self::DEFAULT_DAYS_LIMIT;
  }

  public function setLimit( int $user_id, int $year, int $days_limit ): bool
  {
    $exists = $this -> mdb -> has( 'users_vacation_limits', [
      'user_id' => $user_id,
      'year' => $year
    ] );

    if ( $exists )
    {
      $this -> mdb -> update( 'users_vacation_limits', [
        'days_limit' => $days_limit
      ], [
        'user_id' => $user_id,
        'year' => $year
      ] );
    }
    else
    {
      $this -> mdb -> insert( 'users_vacation_limits', [
        'user_id' => $user_id,
        'year' => $year,
        'days_limit' => $days_limit
      ] );
    }

    return true;
  }

  public function getLimitsForYear( int $year ): array
  {
    $rows = $this -> mdb -> select( 'users_vacation_limits', '*', [
      'year' => $year
    ] );

    $limits = [];
    foreach ( $rows as $row )
      $limits[ (int) $row['user_id'] ] = (int) $row['days_limit'];

    return $limits;
  }

  public static function countBusinessDays( string $date_from, string $date_to ): int
  {
    $start = new \DateTime( $date_from );
    $end = new \DateTime( $date_to );
    $years = range( (int) $start -> format( 'Y' ), (int) $end -> format( 'Y' ) );
    $holidays = self::getPolishHolidays( $years );
    $days = 0;

    while ( $start <= $end )
    {
      $dow = (int) $start -> format( 'N' );
      if ( $dow <= 5 && !isset( $holidays[ $start -> format( 'Y-m-d' ) ] ) )
        $days++;
      $start -> modify( '+1 day' );
    }

    return $days;
  }

  private static function getPolishHolidays( array $years ): array
  {
    $holidays = [];

    foreach ( $years as $y )
    {
      // Stałe święta ustawowe
      $fixed = [
        "$y-01-01", // Nowy Rok
        "$y-01-06", // Trzech Króli
        "$y-05-01", // Święto Pracy
        "$y-05-03", // Święto Konstytucji
        "$y-08-15", // Wniebowzięcie NMP
        "$y-11-01", // Wszystkich Świętych
        "$y-11-11", // Święto Niepodległości
        "$y-12-25", // Boże Narodzenie
        "$y-12-26", // Drugi dzień Bożego Narodzenia
      ];

      foreach ( $fixed as $d )
        $holidays[ $d ] = true;

      // Wielkanoc i święta ruchome
      $easter = new \DateTime( date( 'Y-m-d', easter_date( $y ) ) );
      $easter_monday = ( clone $easter ) -> modify( '+1 day' );
      $corpus_christi = ( clone $easter ) -> modify( '+60 days' );

      $holidays[ $easter -> format( 'Y-m-d' ) ] = true;
      $holidays[ $easter_monday -> format( 'Y-m-d' ) ] = true;
      $holidays[ $corpus_christi -> format( 'Y-m-d' ) ] = true;
    }

    return $holidays;
  }

  public function usedDaysByYear( int $year, ?string $type = null ): array
  {
    $where = [
      'AND' => [
        'date_from[>=]' => $year . '-01-01',
        'date_from[<=]' => $year . '-12-31'
      ]
    ];

    if ( $type )
      $where['AND']['type'] = $type;

    $rows = $this -> mdb -> select( 'users_vacations', [ 'user_id', 'date_from', 'date_to' ], $where );

    $totals = [];
    foreach ( $rows as $row )
    {
      $uid = (int) $row['user_id'];
      if ( !isset( $totals[ $uid ] ) )
        $totals[ $uid ] = 0;
      $totals[ $uid ] += self::countBusinessDays( $row['date_from'], $row['date_to'] );
    }

    return $totals;
  }

  public function summaryByYear( int $year, array $users ): array
  {
    $limits = $this -> getLimitsForYear( $year );
    $used_map = $this -> usedDaysByYear( $year, self::TYPE_VACATION );
    $summary = [];

    foreach ( $users as $u )
    {
      $uid = (int) $u['id'];
      $limit = $limits[ $uid ] ?? self::DEFAULT_DAYS_LIMIT;
      $used = $used_map[ $uid ] ?? 0;

      $summary[] = [
        'user_id' => $uid,
        'name' => $u['name'] . ' ' . $u['surname'],
        'limit' => $limit,
        'used' => $used,
        'remaining' => $limit - $used
      ];
    }

    return $summary;
  }

  public function carryoverByYear( int $year, array $users ): array
  {
    // Znajdź najwcześniejszy rok z danymi (urlopy lub limity)
    $min_vacation_year = $this -> mdb -> get( 'users_vacations', 'date_from', [
      'ORDER' => [ 'date_from' => 'ASC' ],
      'LIMIT' => 1
    ] );
    $min_limit_year = $this -> mdb -> get( 'users_vacation_limits', 'year', [
      'ORDER' => [ 'year' => 'ASC' ],
      'LIMIT' => 1
    ] );

    $start_year = $year - 1; // domyślnie sprawdzaj przynajmniej rok wstecz
    if ( $min_vacation_year )
      $start_year = min( $start_year, (int) substr( $min_vacation_year, 0, 4 ) );
    if ( $min_limit_year )
      $start_year = min( $start_year, (int) $min_limit_year );

    if ( $start_year >= $year )
      return [];

    $carryover = [];

    foreach ( $users as $u )
    {
      $uid = (int) $u['id'];
      $total_remaining = 0;
      $year_details = [];

      for ( $y = $start_year; $y < $year; $y++ )
      {
        $limit = $this -> getLimit( $uid, $y );
        $used_map = $this -> usedDaysByYear( $y, self::TYPE_VACATION );
        $used = $used_map[ $uid ] ?? 0;
        $remaining = $limit - $used;

        if ( $remaining > 0 )
        {
          $total_remaining += $remaining;
          $year_details[] = [
            'year' => $y,
            'remaining' => $remaining
          ];
        }
      }

      if ( $total_remaining > 0 )
      {
        $carryover[] = [
          'user_id' => $uid,
          'name' => $u['name'] . ' ' . $u['surname'],
          'total' => $total_remaining,
          'years' => $year_details
        ];
      }
    }

    return $carryover;
  }
}