<?php
require_once(VENDOR_PATH . DS . "autoload.php");

use PhpOffice\PhpSpreadsheet\IOFactory;

class Surveys
{
    private $conn;
    private $table = "surveyurls";
    private $tableClient = "clients";
    private $tableVendorDetails = "vendordetails";

    public $pid;
    public $cid;
    public $ourId;
    public $status;

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

    public function getVendorList()
    {
        $tempObject = new stdClass();

        $tempObject->data = null;

        $query = "SELECT
            c.cid,
            v.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->tableVendorDetails . " AS c
            LEFT JOIN " . $this->table . " as s ON c.cid = s.cid AND s.pid = '" . $this->pid . "'
            LEFT JOIN " . $this->tableClient . " as v ON v.cid = c.cid
            WHERE c.pid = '" . $this->pid . "' GROUP BY cid";

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

        return $stmt;
    }

    public function deleteOpenUrls()
    {
        $query = "DELETE FROM " . $this->table . " WHERE pid='" . $this->pid . "' AND cid='" . $this->cid . "' AND status = 'open'";

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

        if ($stmt->execute()) {
            return 228;
        }

        return 500;
    }

    public function deleteIndividual()
    {
        $query = "DELETE FROM " . $this->table . " WHERE our_id='" . $this->ourId . "'";

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

        if ($stmt->execute()) {
            return 228;
        }

        return 500;
    }

    public function list()
    {
        $query = "SELECT " . $this->table . ".* FROM " . $this->table . " WHERE " . $this->table . ".cid = '" . $this->cid . "' AND " . $this->table . ".pid='" . $this->pid . "'";
        $stmt = $this->conn->prepare($query);
        $stmt->execute();
        return $stmt;
    }
}