<?php
/********************************************************************************
 * (c)1337 aheadware.com - All rights reserved
 ********************************************************************************/


class TB_Server_Control_Stats {

    public static function getDashboardManager( TB_Server_Core_RequestData $params )
    {
        $resp = new TB_Server_Core_Response();

        $sessionProfile = TB_Server_Core_Session::get()->getProfile();
        if (!isset($sessionProfile))
        {
            throw new \Exception('No session profile - no stats');
        }

        $team = TB_Shared_Ent_TeamData_Team::get( $params->get( 'groupId' ) );
        if ( !isset( $team ) )
        {
            throw new \Exception( 'No team - no stats' );
        }

        if ( false === $sessionProfile->isAdminOfTeam( $team->id ) )
        {
            throw new \Exception( 'Not admin of team.' );
        }

        $cntMembers = TB_Shared_Ent_TeamData_Profile::getNumProfilesByTeamId( $team->id );
        $cntAppointments = TB_Shared_Ent_TeamData_Appointment::getCountByTeamId( $team->id );
        $cntFeedback = TB_Shared_Ent_TeamData_Appointment::getCountByTeamIdAndAttendeeStatus( $team->id );
        $cntAccepted = TB_Shared_Ent_TeamData_Appointment::getCountByTeamIdAndAttendeeStatus( $team->id, TB_Shared_Ent_TeamData_Attendee::STATUS_ACCEPTED );
        $cntWaiting = TB_Shared_Ent_TeamData_Appointment::getCountByTeamIdAndAttendeeStatus( $team->id, TB_Shared_Ent_TeamData_Attendee::STATUS_WAITING );
        $attendances30 = TB_Shared_Ent_TeamData_Attendee::getStatsOfAcceptedAttendancesForTeamId( $team->id );

        $stats = array(
            'count_member' => $cntMembers,
            'count_appointment' => $cntAppointments,
            'count_feedback' => $cntFeedback,
            'count_accepted' => $cntAccepted,
            'count_waiting' => $cntWaiting,
            'attendances_30' => $attendances30
        );

        $resp->addData( 'stats', $stats );

        return $resp;

    }

    static public function getReporting( TB_Server_Core_RequestData $params )
    {
        $resp = new TB_Server_Core_Response();
        $sessionProfile = TB_Server_Core_Session::get()->getProfile();

        if ( !isset( $sessionProfile ) )
        {
            throw new \Exception( 'No session profile - no stats' );
        }

        $team = TB_Shared_Ent_TeamData_Team::get( $params->get( 'groupId' ) );
        if ( !isset( $team ) )
        {
            throw new \Exception( 'No team - no stats' );
        }

        if ( false === $sessionProfile->isAdminOfTeam( $team->id ) )
        {
            throw new \Exception( 'No admin rights - no stats' );
        }

        $members = TB_Shared_Ent_TeamData_Profile::getProfilesByTeamId( $team->id );

        $daysInPast = 14;
        $nowDt = TB_Server_Utils_Helper::getUTCNowDateTime();
        $timespanNowStart = $nowDt->sub( DateInterval::createFromDateString( $daysInPast .' days' ) );
        $timespanNowEnd = TB_Server_Utils_Helper::getUTCNowDateTime();

        $apps14 = TB_Shared_Ent_TeamData_Appointment::getByTeamIdAndTimespan( $team->id, $timespanNowStart, $timespanNowEnd );
        $attendees = [];

        foreach( $apps14 as $app )
        {
            $appAttendees = TB_Shared_Ent_TeamData_Attendee::getAttendeesWithStatusForAppointmentId( $app->id, 'accepted' );
            $resp->addData( 'attendees_' . $app->id, $appAttendees );
        }

        $resp->addData( 'members', $members );
        $resp->addData( 'apps', $apps14 );

        return $resp;
    }

    static public function getPlanning( TB_Server_Core_RequestData $params )
    {
        $resp = new TB_Server_Core_Response();
        $sessionProfile = TB_Server_Core_Session::get()->getProfile();

        if ( !isset( $sessionProfile ) )
        {
            throw new \Exception( 'No session profile - no stats' );
        }

        $team = TB_Shared_Ent_TeamData_Team::get( $params->get( 'groupId' ) );
        if ( !isset( $team ) )
        {
            throw new \Exception( 'No team - no stats' );
        }

        if ( false === $sessionProfile->isAdminOfTeam( $team->id ) )
        {
            throw new \Exception( 'No admin rights - no stats' );
        }

        $members = TB_Shared_Ent_TeamData_Profile::getProfilesByTeamId( $team->id );

        $daysInFuture = 7;
        $nowDt = TB_Server_Utils_Helper::getUTCNowDateTime();
        $timespanNowEnd = $nowDt->add( DateInterval::createFromDateString( $daysInFuture .' days' ) );
        $timespanNowStart = TB_Server_Utils_Helper::getUTCNowDateTime();

        $apps7 = TB_Shared_Ent_TeamData_Appointment::getByTeamIdAndTimespan( $team->id, $timespanNowStart, $timespanNowEnd );
        $attendees = [];

        foreach( $apps7 as $app )
        {
            $appAttendees = TB_Shared_Ent_TeamData_Attendee::getAttendeesWithStatusForAppointmentId( $app->id, 'accepted' );
            $resp->addData( 'attendees_' . $app->id, $appAttendees );
        }

        $resp->addData( 'members', $members );
        $resp->addData( 'apps', $apps7 );

        return $resp;
    }

    static public function getOverview( TB_Server_Core_RequestData $params )
    {
        $resp = new TB_Server_Core_Response();

        $sessionProfile = TB_Server_Core_Session::get()->getProfile();
        if ( !isset( $sessionProfile ) )
        {
            throw new \Exception( 'No session profile - no stats' );
        }

        $team = TB_Shared_Ent_TeamData_Team::get( $params->get( 'groupId' ) );
        if ( !isset( $team ) )
        {
            throw new \Exception( 'No team - no stats' );
        }

        if ( false === $sessionProfile->isAdminOfTeam( $team->id ) )
        {
            throw new \Exception( 'No admin rights - no stats' );
        }

        $members = TB_Shared_Ent_TeamData_Profile::getProfilesByTeamId( $team->id );
        $appointments = TB_Shared_Ent_TeamData_Appointment::getByTeamId( $team->id );
        $cntFeedback = 0;
        $cntAccepted = 0;
        $cntWaiting = 0;
        /** @var TB_Shared_Ent_TeamData_Appointment $appointment */
        foreach( $appointments as $appointment )
        {
            $attendees = TB_Shared_Ent_TeamData_Attendee::getByAppointmentId( $appointment->id );
            $cntFeedback += count( $attendees );
            /** @var TB_Shared_Ent_TeamData_Attendee $attendee */
            foreach( $attendees as $attendee )
            {
                switch( $attendee->status )
                {
                    case TB_Shared_Ent_TeamData_Attendee::STATUS_ACCEPTED:
                        $cntAccepted++;
                    break;

                    case TB_Shared_Ent_TeamData_Attendee::STATUS_WAITING:
                        $cntWaiting++;
                }
            }
        }

        $stats = array(
            'count_member' => count($members),
            'count_appointment' => count($appointments),
            'count_feedback' => $cntFeedback,
            'count_accepted' => $cntAccepted,
            'count_waiting' => $cntWaiting
        );

        $resp->addData( 'stats', $stats );

        return $resp;
    }

    static public function getOld( TB_Server_Core_RequestData $params )
    {
        $resp = new TB_Server_Core_Response();

        $sessionProfile = TB_Server_Core_Session::get()->getProfile();
        if ( !isset( $sessionProfile ) )
        {
            throw new \Exception( 'No session profile - no stats' );
        }

        $team = TB_Shared_Ent_TeamData_Team::get( $params->get( 'groupId' ) );
        if ( !isset( $team ) )
        {
            throw new \Exception( 'No team - no stats' );
        }

        if ( false === $sessionProfile->isAdminOfTeam( $team->id ) )
        {
            throw new \Exception( 'No admin rights - no stats' );
        }

        switch( $params->get( 'timespan' ) )
        {
            case "90":
                $timespanInDays = 90;
                break;

            case "30":
                $timespanInDays = 30;
                break;

            case "7":
            default:
                $timespanInDays = 7;
                break;
        }

        // Setup series and labels data
        $labels = array();
        $series = array(
            array_fill( 0, $timespanInDays, 0 ),    // numAttended
            array_fill( 0, $timespanInDays, 0 ),    // numDeclined
            array_fill( 0, $timespanInDays, 0 ),    // numWaiting
            array_fill( 0, $timespanInDays, 0 )     // numTotal
        );

        $labelIdx = TB_Server_Utils_Helper::getUTCNowDateTime()->sub( DateInterval::createFromDateString( $timespanInDays . ' days' ) );
        for( $i = 0; $i < $timespanInDays; $i++ )
        {
            $labelIdx = $labelIdx->add( DateInterval::createFromDateString( 1 . ' days' ) );
            array_push( $labels, $labelIdx->format( 'd.m.Y' ) );
        }

        // Last 30 days
        $nowDt = TB_Server_Utils_Helper::getUTCNowDateTime();
        $timespanNowStart = $nowDt->sub( DateInterval::createFromDateString( $timespanInDays .' days' ) );
        $timespanNowEnd = TB_Server_Utils_Helper::getUTCNowDateTime();

        $timespanBeforeEnd = TB_Server_Utils_Helper::getUTCNowDateTime()->sub( DateInterval::createFromDateString( $timespanInDays . ' days' ) );
        $timespanBeforeStart = $timespanBeforeEnd->sub( DateInterval::createFromDateString( (2 * $timespanInDays ) . ' days' ) );

        $nowApps = TB_Shared_Ent_TeamData_Appointment::getByTeamIdAndTimespan( $team->id, $timespanNowStart, $timespanNowEnd );
        $beforeApps = TB_Shared_Ent_TeamData_Appointment::getByTeamIdAndTimespan( $team->id, $timespanBeforeStart, $timespanBeforeEnd );

        $kpis = array(
            'members' => array(
                'now' => 0,
                'before' => 0
            ),
            'appointments' => array(
                'now' => count( $nowApps ),
                'before' => count( $beforeApps )
            ),
            'accepted' => array(
                'now' => 0,
                'before' => 0
            ),
            'feedbacks' => array(
                'now' => 0,
                'before' => 0
            ),
            'attended' => array(
                'now' => 0,
                'before' => 0
            ),
            'appointmentWorkload' => array(
                'now' => 0,
                'before' => 0
            ),
            'reliability' => array(
                'now' => 0,
                'before' => 0
            )
        );

        // Members
        $tps = TB_Shared_Ent_TeamData_Profile::getProfilesByTeamId( $team->id );
        /** @var TB_Shared_Ent_TeamData_Profile $tp */
        foreach( $tps as $tp )
        {
            $td = $tp->getTeamsData( $team->id );
            $joinDt = TB_Server_Utils_Helper::getDatetimeFromJsonDate( $td[ 'join_dt' ] );
            if ( $joinDt > $timespanNowStart && $joinDt < $timespanNowEnd )
            {
                $kpis[ 'members'][ 'now' ]++;
            }
            else if ( $joinDt > $timespanBeforeStart && $joinDt < $timespanBeforeEnd )
            {
                $kpis[ 'members' ][ 'before' ]++;
            }
        }

        // Appointment and feedbacks
        if ( 0 < count( $nowApps ) )
        {
            /** @var TB_Shared_Ent_TeamData_Appointment $nowApp */
            foreach( $nowApps as $nowApp )
            {
                $seriesIdx = array_search( $nowApp->start_dt->format( 'd.m.Y' ), $labels );
                if ( false === $seriesIdx )
                {
                    continue;
                }
                $attendees = TB_Shared_Ent_TeamData_Attendee::getByAppointmentId( $nowApp->id );
                $kpis[ 'feedbacks' ][ 'now' ] += count( $attendees );

                $series[ 3 ][ $seriesIdx ] = count( $attendees );

                /** @var TB_Shared_Ent_TeamData_Attendee $attendee */
                foreach ( $attendees as $attendee )
                {
                    if ( $attendee->status === TB_Shared_Ent_TeamData_Attendee::STATUS_ACCEPTED )
                    {
                        $kpis[ 'accepted' ][ 'now' ]++;
                        $series[ 0 ][ $seriesIdx ]++;
                    }
                    if ( $attendee->status === TB_Shared_Ent_TeamData_Attendee::STATUS_WAITING )
                    {
                        $kpis[ 'accepted' ][ 'now' ]++;
                        $series[ 2 ][ $seriesIdx ]++;
                    }
                    if ( $attendee->status === TB_Shared_Ent_TeamData_Attendee::STATUS_DECLINED )
                    {
                        $series[ 1 ][ $seriesIdx ]++;
                    }
                    if ( $attendee->has_attended === 1 )
                    {
                        $kpis[ 'attended' ][ 'now' ]++;
                    }
                }
            }

            $kpis[ 'appointmentWorkload' ][ 'now' ] = $kpis[ 'attended' ][ 'now' ] / (int)count( $nowApps );
            if ( 0 < $kpis[ 'accepted' ][ 'now' ] )
            {
                $kpis[ 'reliability' ][ 'now' ] = $kpis[ 'attended' ][ 'now' ] / $kpis[ 'accepted'][ 'now' ];
            }
        }

        if ( 0 < count( $beforeApps ) )
        {
            /** @var TB_Shared_Ent_TeamData_Appointment $beforeApp */
            foreach( $beforeApps as $beforeApp )
            {
                $attendees = TB_Shared_Ent_TeamData_Attendee::getByAppointmentId( $beforeApp->id );
                $kpis[ 'feedbacks' ][ 'before' ] += count( $attendees );
                /** @var TB_Shared_Ent_TeamData_Attendee $attendee */
                foreach ( $attendees as $attendee )
                {
                    if ( $attendee->status === TB_Shared_Ent_TeamData_Attendee::STATUS_ACCEPTED ||
                        $attendee->status === TB_Shared_Ent_TeamData_Attendee::STATUS_WAITING )
                    {
                        $kpis[ 'accepted' ][ 'before' ]++;
                    }
                    if ( $attendee->has_attended === 1 )
                    {
                        $kpis[ 'attended' ][ 'before' ]++;
                    }
                }
            }

            $kpis[ 'appointmentWorkload' ][ 'before' ] = $kpis[ 'attended' ][ 'before' ] / (int)count( $beforeApps );
            if ( 0 < $kpis[ 'accepted' ][ 'before' ] )
            {
                $kpis[ 'reliability' ][ 'before' ] = $kpis[ 'attended' ][ 'before' ] / $kpis[ 'accepted'][ 'before' ];
            }
        }

        $resp->addData( 'kpis', $kpis );


        /*
        $stats = array(
            'labels' => array(),
            'series' => array()
        );

        $appointsments = $nowApps;//TB_Shared_Ent_TeamData_Appointment::getByTeamId( $team->id );
        $appointmentIds = array();
        foreach ( $appointsments as $appointment )
        {
            array_push( $appointmentIds, $appointment->id );
        }


        $labels = array();
        $series = array(
            array(),    // numAttended
            array(),    // numDeclined
            array(),    // numWaiting
            array()     // numTotal
        );

        if ( 0 < count( $appointmentIds ) )
        {
            $in  = str_repeat('?,', count( $appointmentIds ) - 1) . '?';

            $sql  = "SELECT *, DATE_FORMAT( last_update_dt, '%d.%m.%Y' ) AS label FROM `attendee` ";
            $sql .= "WHERE appointment_id IN ($in) ORDER BY last_update_dt";

            $dbh = TB_Shared_Ent_TeamData_Attendee::getDbh();
            $stmt = $dbh->prepare( $sql );
            $stmt->execute( $appointmentIds );
            $attends = $stmt->fetchAll( PDO::FETCH_ASSOC );

            $attendLabel = '';
            $currentIdx = -1;
            foreach( $attends as $attend )
            {
                if ( $attendLabel != $attend[ 'label' ] )
                {
                    $currentIdx++;
                    $labels[ $currentIdx ] = $attend[ 'label' ];
                    $series[0][ $currentIdx ] = 0;
                    $series[1][ $currentIdx ] = 0;
                    $series[2][ $currentIdx ] = 0;
                    $series[3][ $currentIdx ] = 0;
                    $attendLabel = $attend[ 'label' ];
                }

                if ( TB_Shared_Ent_TeamData_Attendee::STATUS_ACCEPTED === $attend[ 'status' ] )
                {
                    $series[0][ $currentIdx ]++;
                    $series[3][ $currentIdx ]++;
                }
                else if ( TB_Shared_Ent_TeamData_Attendee::STATUS_DECLINED === $attend[ 'status' ] )
                {
                    $series[1][ $currentIdx ]++;
                    $series[3][ $currentIdx ]++;
                }
                else if ( TB_Shared_Ent_TeamData_Attendee::STATUS_WAITING === $attend[ 'status' ] )
                {
                    $series[2][ $currentIdx ]++;
                    $series[3][ $currentIdx ]++;
                }
            }
        }
        */

        $resp->addData( 'stats', array(
            'labels' => $labels,
            'series' => $series
        ) );

        return $resp;
    }

    static private function getKPIs( TB_Shared_Ent_TeamData_Team $team, $timespan = 30 )
    {
        $kpis = array(
            'members' => array(
                'now' => 0,
                'before' => 0
            ),
            'appointments' => array(
                'now' => 0,
                'before' => 0
            ),
            'accepted' => array(
                'now' => 0,
                'before' => 0
            ),
            'feedbacks' => array(
                'now' => 0,
                'before' => 0
            ),
            'appointmentWorkload' => array(
                'now' => 0,
                'before' => 0
            ),
            'reliability' => array(
                'now' => 0,
                'before' => 0
            )
        );

        // Last 30 days
        $nowDt = TB_Server_Utils_Helper::getUTCNowDateTime();
        $timespanNowStart = $nowDt->sub( DateInterval::createFromDateString('30 days' ) );
        $timespanNowEnd = $nowDt;

        $timespanBeforeEnd = $timespanNowStart;
        $timespanBeforeStart = $timespanBeforeEnd->sub( DateInterval::createFromDateString( '30 days' ) );

        // start: Members
        $members = TB_Shared_Ent_TeamData_Profile::getProfilesByTeamId( $team->id );
        $kpis[ 'members' ][ 'now' ] = count( $members );
        $membersBefore = 0;
        /** @var TB_Shared_Ent_TeamData_Profile $member */
        foreach( $members as $member )
        {
            $td = $member->getTeamsData( $team->id );
            $joinDt = new DateTime( $td[ 'join_dt' ] );
            if ( $joinDt < $timespanNowStart )
            {
                $membersBefore++;
            }
        }
        $kpis[ 'members' ][ 'before' ] = $membersBefore;
        // stop: Members

        // start: Appointments
        $attendees = array();
        $apps = TB_Shared_Ent_TeamData_Appointment::getByTeamId( $team->id );
        $kpis[ 'appointments' ][ 'now' ] = count( $apps );
        $appsBefore = 0;
        /** @var TB_Shared_Ent_TeamData_Appointment $app */
        foreach( $apps as $app )
        {
            $attendees = array_merge( $attendees, TB_Shared_Ent_TeamData_Attendee::getByAppointmentId( $app->id ) );

            $appStartDt = new DateTime( $app->start_dt );
            if ( $appStartDt < $timespanNowStart )
            {
                $appsBefore++;
            }
        }
        $kpis[ 'appointments' ][ 'before' ] = $appsBefore;
        // stop: Appointments

        // start: Feedbacks
        $kpis[ 'feedbacks' ][ 'now' ] = count( $attendees );
        $hasAccepted = 0;
        $hasAcceptedBefore = 0;
        $feedbacksBefore = 0;
        /** @var TB_Shared_Ent_TeamData_Attendee $attendee */
        foreach( $attendees as $attendee )
        {
            $attCreatedDt = new DateTime( $attendee->created_dt );
            if ( $attendee->status === TB_Shared_Ent_TeamData_Attendee::STATUS_ACCEPTED ||
                $attendee->status === TB_Shared_Ent_TeamData_Attendee::STATUS_WAITING ) {
                $hasAccepted++;
            }
            if ( $attCreatedDt < $timespanNowStart ) {
                $feedbacksBefore++;
                if ( $attendee->status === TB_Shared_Ent_TeamData_Attendee::STATUS_ACCEPTED ||
                    $attendee->status === TB_Shared_Ent_TeamData_Attendee::STATUS_WAITING ) {
                    $hasAcceptedBefore++;
                }
            }
        }
        $kpis[ 'feedbacks' ][ 'before' ] = $feedbacksBefore;
        $kpis[ 'accepted' ][ 'now' ] =   $hasAccepted;
        $kpis[ 'accepted' ][ 'before' ] = $hasAcceptedBefore;
        // stop: Feedbacks

    }

    /**
     * @param TB_Server_Core_RequestData $params
     * @return TB_Server_Core_Response
     */
    static public function dashboard( TB_Server_Core_RequestData $params ) {

        $resp = new TB_Server_Core_Response();

        $profile = TB_Server_Core_Session::get()->getProfile();
        $team = TB_Server_Core_Session::get()->getTeam();

        // Stats data
        $yesterday = self::getLatestDateString();
        $compareDate = self::getCompareDateString( $yesterday );
        $playerRank = self::getPlayerRank( $profile->id, $yesterday );
        $ranksTotal = self::getPlayerRankTotal( $profile->team_id, $yesterday );
        $playerRankCompare = self::getPlayerRank( $profile->id, $compareDate );
        $playerReliability = self::getPlayerReliability( $profile, $yesterday );
        $playerAttendeeReliability = self::getPlayerReliabilityAttendee( $profile, $yesterday );
        $teamAppointmentReactions = self::getTeamAppointmentReactions( $profile->team_id );
        $teamReliability = self::getTeamReliability( $profile->team_id, $yesterday );
        $teamAttendeeReliability = self::getTeamReliabilityAttendee( $profile->team_id, $yesterday );
        $generalAppointmentReactions = self::getGeneralAppointmentReactions();
        $generalReliability = self::getGeneralReliability( $yesterday );
        $generalAttendeeReliability = self::getGeneralReliabilityAttendee( $yesterday );
        $teamRanking = self::getTeamRanking( $profile->team_id, $yesterday );

        $newStats = array(
            'playerRank' => $playerRank,
            'ranksTotal' => $ranksTotal,
            'playerRankCompare' => $playerRankCompare,
            'playerReliability' => $playerReliability,
            'playerAttendeeReliability' => $playerAttendeeReliability,
            'teamAppointmentReactions' => $teamAppointmentReactions,
            'teamReliability' => $teamReliability,
            'teamAttendeeReliability' => $teamAttendeeReliability,
            'generalAppointmentReactions' => $generalAppointmentReactions,
            'generalReliability' => $generalReliability,
            'generalAttendeeReliability' => $generalAttendeeReliability,
            'teamRanking' => $teamRanking
         );

        $resp->addData( 'profile', $profile );
        $resp->addData( 'statsExt', $newStats );
        $resp->addData( 'team', $team );

        return $resp;

    }

    /**
     * @param $date
     */
    static protected function getCompareDateString( $date )
    {
        return date( 'Y-m-d', strtotime( $date ) - 7 * 60 * 60 * 24 );
    }

    /**
     *
     * @return false|string
     */
    static protected function getLatestDateString()
    {
        $dbh = TB_Shared_Db_TeamData::get();
        $sql = 'SELECT MAX(entry_dt) AS `max_date` FROM `stat_user`';
        $stmt = $dbh->prepare( $sql );
        $stmt->execute();
        $row = $stmt->fetch( PDO::FETCH_ASSOC );
        if ( isset( $row ) || isset( $row[ 'max_date' ] ) ) {
            return date( 'Y-m-d', strtotime( $row[ 'max_date' ] ) );
        } else {
            // Yesterday
            return date( 'Y-m-d', time() - 60 * 60 * 24 );
        }
    }

    /**
     * Get rank of player of specific date
     *
     * @param int $profileId
     * @param string $date 'YYYY-MM-DD'
     * @return null | int
     */
    static protected function getPlayerRank( $profileId, $date ) {

        $rank = NULL;

        $dbh = TB_Shared_Db_TeamData::get();
        $sql = "CALL proc_getRanking ( :profile_id, :when, @returnInt );SELECT @returnInt;";
        $stmt = $dbh->prepare( $sql );
        $stmt->bindParam( ':profile_id', $profileId, PDO::PARAM_INT );
        $stmt->bindParam( ':when', $date, PDO::PARAM_STR );
        $stmt->execute();
        $stmt->closeCursor();
        $row = $dbh->query( "SELECT @returnInt AS rank" )->fetch( PDO::FETCH_ASSOC );
        if ( isset( $row ) ) {
            $rank = ( $row !== false ) ? $row[ 'rank' ] : NULL;
        }

        return $rank;
    }

    /**
     * Get total amount of ranks
     * @param int $teamId
     * @param string $date 'YYYY-MM-DD'
     */
    static protected function getPlayerRankTotal( $teamId, $date ) {
        $total = NULL;

        $dbh = TB_Shared_Db_TeamData::get();
        $sql = 'SELECT COUNT(*) AS totalRanks FROM `stat_user` WHERE b_team_id = :team_id AND entry_dt = :when';
        $stmt = $dbh->prepare( $sql );
        $stmt->execute( array( 'team_id' => $teamId, ':when' => $date ) );
        $row = $stmt->fetch( PDO::FETCH_ASSOC );
        if ( isset( $row ) ) {
            $total = $row[ 'totalRanks' ];
        }

        return $total;
    }

    /**
     * Get player reliability in percentage
     *
     * @param TB_Shared_Ent_TeamData_Profile $profile
     * @param $date
     * @return null | float
     */
    static protected function getPlayerReliability( TB_Shared_Ent_TeamData_Profile $profile, $date ) {
        $reliability = NULL;

        $dbh = TB_Shared_Db_TeamData::get();
        $sql = 'SELECT tReagiert/tGesamt AS reliability FROM `stat_user` WHERE b_team_id = :team_id AND b_spieler_id = :profile_id AND entry_dt = :when';
        $stmt = $dbh->prepare( $sql );
        $stmt->execute( array( 'team_id' => $profile->team_id, ':profile_id' => $profile->id, ':when' => $date ) );
        $row = $stmt->fetch( PDO::FETCH_ASSOC );
        if ( isset( $row ) ) {
            $reliability = $row[ 'reliability' ];
        }

        return $reliability;
    }

    /**
     * Get players reliability for attendance of appointments
     *
     * @param TB_Shared_Ent_TeamData_Profile $profile
     * @param $date
     * @return null | float
     */
    static protected function getPlayerReliabilityAttendee( TB_Shared_Ent_TeamData_Profile $profile, $date ) {
        $reliabilityAttendee = NULL;

        $dbh = TB_Shared_Db_TeamData::get();
        $sql = 'SELECT tZusage/tReagiert AS reliabilityAttendee FROM `stat_user` WHERE b_team_id = :team_id AND b_spieler_id = :profile_id AND entry_dt = :when';
        $stmt = $dbh->prepare( $sql );
        $stmt->execute( array( 'team_id' => $profile->team_id, ':profile_id' => $profile->id, ':when' => $date ) );
        $row = $stmt->fetch( PDO::FETCH_ASSOC );
        if ( isset( $row ) ) {
            $reliabilityAttendee = $row[ 'reliabilityAttendee' ];
        }

        return $reliabilityAttendee;
    }

    /**
     * Get amount of appointment reactions for given team id
     * @param int $teamId
     * @return int
     */
    static protected function getTeamAppointmentReactions( $teamId ) {
        $reactions = 0;

        $dbh = TB_Shared_Db_TeamData::get();
        $sql = 'SELECT COUNT(*) AS numReaction FROM appointment t INNER JOIN attendee a ON (t.id = a.appointment_id) WHERE t.team_id = :team_id AND FROM_UNIXTIME(t.starttime)<=DATE_ADD(CURDATE(), INTERVAL 14 DAY)';
        $stmt = $dbh->prepare( $sql );
        $stmt->execute( array( 'team_id' => $teamId ) );
        $row = $stmt->fetch( PDO::FETCH_ASSOC );
        if ( isset( $row ) ) {
            $reactions = $row[ 'numReaction' ];
        }

        return $reactions;
    }

    /**
     * Get amount of appointment reactions
     * @return int
     */
    static protected function getGeneralAppointmentReactions() {
        $reactions = 0;

        $dbh = TB_Shared_Db_TeamData::get();
        $sql = 'SELECT COUNT(*) AS numReaction FROM appointment t INNER JOIN attendee a ON (t.id = a.appointment_id) WHERE FROM_UNIXTIME(t.starttime)<=DATE_ADD(CURDATE(), INTERVAL 14 DAY)';
        $stmt = $dbh->prepare( $sql );
        $stmt->execute();
        $row = $stmt->fetch( PDO::FETCH_ASSOC );
        if ( isset( $row ) ) {
            $reactions = $row[ 'numReaction' ];
        }

        return $reactions;
    }

    /**
     * Get average team reliability
     * @param int $teamId
     * @param string $date 'YYYY-MM-DD'
     * @return float
     */
    static protected function getTeamReliability( $teamId, $date ) {
        $reliability = 0;

        $dbh = TB_Shared_Db_TeamData::get();
        $sql = 'SELECT AVG(tReagiert/tGesamt) AS avgVerlaessl FROM `stat_user` WHERE entry_dt = :when AND b_team_id = :team_id';
        $stmt = $dbh->prepare( $sql );
        $stmt->execute( array( 'team_id' => $teamId, ':when' => $date ) );
        $row = $stmt->fetch( PDO::FETCH_ASSOC );
        if ( isset( $row ) ) {
            $reliability = $row[ 'avgVerlaessl' ];
        }

        return $reliability;
    }

    /**
     * Get average reliability in general
     * @param string $date 'YYYY-MM-DD'
     * @return float
     */
    static protected function getGeneralReliability( $date ) {
        $reliability = 0;

        $dbh = TB_Shared_Db_TeamData::get();
        $sql = 'SELECT AVG(tReagiert/tGesamt) AS avgVerlaessl FROM `stat_user` WHERE entry_dt = :when';
        $stmt = $dbh->prepare( $sql );
        $stmt->execute( array( ':when' => $date ) );
        $row = $stmt->fetch( PDO::FETCH_ASSOC );
        if ( isset( $row ) ) {
            $reliability = $row[ 'avgVerlaessl' ];
        }

        return $reliability;
    }

    /**
     * Get reliability for attendance of given team
     * @param int $teamId
     * @param string $date 'YYYY-MM-DD'
     * @return float
     */
    static protected function getTeamReliabilityAttendee( $teamId, $date ) {

        $reliabilityAttendee = 0;

        $dbh = TB_Shared_Db_TeamData::get();
        $sql = 'SELECT AVG(tZusage/tReagiert) AS avgZusagen FROM `stat_user` WHERE entry_dt = :when AND b_team_id = :team_id';
        $stmt = $dbh->prepare( $sql );
        $stmt->execute( array( 'team_id' => $teamId, ':when' => $date ) );
        $row = $stmt->fetch( PDO::FETCH_ASSOC );
        if ( isset( $row ) ) {
            $reliabilityAttendee = $row[ 'avgZusagen' ];
        }

        return $reliabilityAttendee;

    }

    /**
     * Get reliability for attendance in general
     * @param string $date 'YYYY-MM-DD'
     * @return float
     */
    static protected function getGeneralReliabilityAttendee( $date ) {

        $reliabilityAttendee = 0;

        $dbh = TB_Shared_Db_TeamData::get();
        $sql = 'SELECT AVG(tZusage/tReagiert) AS avgZusagen FROM `stat_user` WHERE entry_dt = :when';
        $stmt = $dbh->prepare( $sql );
        $stmt->execute( array(':when' => $date ) );
        $row = $stmt->fetch( PDO::FETCH_ASSOC );
        if ( isset( $row ) ) {
            $reliabilityAttendee = $row[ 'avgZusagen' ];
        }

        return $reliabilityAttendee;

    }

    /**
     * Get overall team ranking
     * @param int $teamId
     * @param string $date 'YYYY-MM-DD'
     * @return array
     */
    static protected function getTeamRanking( $teamId, $date ) {

        $teamRanking = array();

        $dbh = TB_Shared_Db_TeamData::get();
        $sql = 'select rank, b_spielerName, b_spieler_id, ReaktQuote, ZuQuote, AbQuote,
NoReaction from
(select b_spielerName, b_spieler_ID, tReagiert/tGesamt as
ReaktQuote, tZusage/tReagiert as ZuQuote, tAbsage/tReagiert as
AbQuote, tKeineReaktion/tGesamt as NoReaction,
 @prev := @curr,
 @curr := (tReagiert/tGesamt)+(tZusage/tReagiert),
 @rank1:= @rank1+1,
 @rank := IF(@prev = @curr, @rank, @rank1) as rank
 from stat_user, 
 (SELECT @curr := null, @prev := null, @rank1 := 0, @rank := 0) sel1
 where b_team_id = :team_id and entry_dt =
:when order by ReaktQuote desc, ZuQuote desc ) as tab';

        $stmt = $dbh->prepare( $sql );
        $stmt->execute( array( 'team_id' => $teamId, ':when' => $date ) );
        $rows = $stmt->fetchAll( PDO::FETCH_ASSOC );
        if ( isset( $rows ) ) {
            $teamRanking = $rows;
        }

        return $teamRanking;
    }


    /**
     * @param $teamId
     */
    static protected function getPlayerReliabilityRanking( $teamId ) {
        $sql = "select distinct concat(p.first_name, ' ',p.last_name) AS fullname,
            (select count(app.id)
            from appointment app
            where app.team_id = :team_id and FROM_UNIXTIME(app.starttime)>'2015-01-01') as Gesamt,
            count(*) as Counter,
            count(*)/(select count(app.id)
            from appointment app
            where app.team_id = :team_id and FROM_UNIXTIME(app.starttime)>'2015-01-01') as Anteil,
            (select
            count(*) as Counter
            from appointment t1
            inner join attendee a1 on (t1.id = a1.appointment_id)
            inner join profile p1 on (a1.profile_id = p1.id)
            where t1.team_id = :team_id and FROM_UNIXTIME(t1.starttime)>'2015-01-01'
            and a1.`status` = 'accepted' and a1.profile_id = p.id) as Zusage,
            (select
            count(*) as Counter
            from appointment t1
            inner join attendee a1 on (t1.id = a1.appointment_id)
            inner join profile p1 on (a1.profile_id = p1.id)
            where t1.team_id = :team_id and FROM_UNIXTIME(t1.starttime)>'2015-01-01'
            and a1.`status` = 'declined' and a1.profile_id = p.id) as Absage
            from appointment t
            inner join attendee a on (t.id = a.appointment_id)
            inner join profile p on (a.profile_id = p.id)
            where t.team_id = :team_id and FROM_UNIXTIME(t.starttime)>'2015-01-01'
            group by p.first_name,p.last_name
            order by Counter desc, Zusage desc";

        $dbh = TB_Shared_Db_TeamData::get();
        $stmt = $dbh->prepare( $sql );
        $stmt->execute( array( 'team_id' => $teamId ) );
        $res = $stmt->fetchAll( PDO::FETCH_ASSOC );

        return $res;
    }
}