eduVPN / vpn-server-api

        return $stmt->fetchAll(PDO::FETCH_ASSOC);

    }

    public function clientConnect($profileId, $commonName, $ip4, $ip6, DateTime $connectedAt)

    {

        // this query is so complex, because we want to store the user_id in the

        // log as well, not just the common_name... the user may delete the

        // certificate, or the user account may be deleted...

        $stmt = $this->db->prepare(

<<< 'SQL'

    INSERT INTO connection_log 

        (

            user_id,

            profile_id,

            common_name,

            ip4,

            ip6,

            connected_at

        ) 

    VALUES

        (

            (

                SELECT

                    u.user_id

                FROM 

                    users u, certificates c

                WHERE

                    u.id = c.user_id

                AND

                    c.common_name = :common_name

            ),                

            :profile_id, 

            :common_name,

            :ip4,

            :ip6,

            :connected_at

        )

SQL

        );

        $stmt->bindValue(':profile_id', $profileId, PDO::PARAM_STR);

        $stmt->bindValue(':common_name', $commonName, PDO::PARAM_STR);

        $stmt->bindValue(':ip4', $ip4, PDO::PARAM_STR);

        $stmt->bindValue(':ip6', $ip6, PDO::PARAM_STR);

        $stmt->bindValue(':connected_at', $connectedAt->format('Y-m-d H:i:s'), PDO::PARAM_STR);

        $stmt->execute();

        // XXX

        return 1 === $stmt->rowCount();

    }

    public function clientDisconnect($profileId, $commonName, $ip4, $ip6, DateTime $connectedAt, DateTime $disconnectedAt, $bytesTransferred)

    {

        return $stmt->fetchAll(PDO::FETCH_ASSOC);

    }

    public function addUserMessage($userId, $type, $message, DateTime $dateTime)

    {

        $userId = $this->getId($userId);

        $stmt = $this->db->prepare(

<<< 'SQL'

    INSERT INTO user_messages 

        (user_id, type, message, date_time) 

    VALUES

        (:user_id, :type, :message, :date_time)

SQL

        );

        $stmt->bindValue(':user_id', $userId, PDO::PARAM_INT);

        $stmt->bindValue(':type', $type, PDO::PARAM_STR);

        $stmt->bindValue(':message', $message, PDO::PARAM_STR);

        $stmt->bindValue(':date_time', $dateTime->format('Y-m-d H:i:s'), PDO::PARAM_STR);

        $stmt->execute();

        return 1 === $stmt->rowCount();

    }

    public function init()

    {