eduVPN / vpn-server-api

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

    }

    public function addCertificate($userId, $commonName, $displayName, DateTime $validFrom, DateTime $validTo)

    {

        $this->addUser($userId);

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

<<< 'SQL'

    INSERT INTO certificates 

        (common_name, user_id, display_name, valid_from, valid_to)

    VALUES

        (:common_name, :user_id, :display_name, :valid_from, :valid_to)

SQL

        );

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

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

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

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

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

        $stmt->execute();

        // XXX

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

    }

    public function getCertificates($userId)

    {

        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.user_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)

    {