<?php

class Report
{
    private $conn;
    private $projectTable = "projects";
    private $urlsTable = "surveyurls";

    public $pid;
    public $fromDate;
    public $toDate;

    public function __construct($db)
    {
        $this->conn = $db;
    }

    public function projectReport()
    {
        $query = "SELECT
            p.pid,
            p.name,
            COUNT(s.survey_url) as 'total',
            SUM(case when s.status = 'open' then 1 else 0 end) AS open,
            SUM(case when s.status = 'started' then 1 else 0 end) AS started,
            SUM(case when s.status = 'complete' then 1 else 0 end) AS complete,
            SUM(case when s.status = 'screenout' then 1 else 0 end) AS screenout,
            SUM(case when s.status = 'quotafull' then 1 else 0 end) AS quotafull
        FROM " . $this->projectTable . " AS p
            LEFT JOIN " . $this->urlsTable . " AS s ON p.pid = s.pid WHERE s.pid= '" . $this->pid . "' AND s.updated_at BETWEEN '" . $this->fromDate . "' AND '" . $this->toDate . "'";

        $stmt = $this->conn->prepare($query);
        $stmt->execute();
        return $stmt;
    }
}