<?php
/**
 * Created by PhpStorm.
 * User: Spencer
 * Date: 04/12/2015
 * Time: 10:00
 */

namespace respprog\Model;
use Zend\Db\TableGateway\TableGateway;
use Zend\Db\TableGateway\AbstractTableGateway;

use Zend\Db\Sql\Select;
use Zend\Paginator\Adapter\DbSelect;
use Zend\Db\ResultSet\ResultSet;
use Zend\Db\Sql\Sql;


class FilmTable extends AbstractTableGateway
{
    protected $tableGateway;

    public function __construct(TableGateway $tableGateway)
    {
        $this->tableGateway = $tableGateway;
    }

    public function fetchAll()
    {
        $resultSet = $this->tableGateway->select();
        return $resultSet;
    }

    public function getFilmById($id)
    {
        $resultSet = $this->tableGateway->select(array('idFilm' => $id));
        return $resultSet;
    }

    public function getFilmNotInWeek($mercredi)
    {
        $dateProg = date('Y-m-d', $mercredi);

        //la sous requette de la premiere requete
        $sql = new Sql($this->tableGateway->adapter);
        $subquery = $sql->select()->columns(array('p2.film'));
        $subquery->from(array('p'=> 'programmation'));
        $subquery->where(array('p2.date_debut_sem' => $dateProg));


        //La premiere requete
        $sql1 = new Sql($this->tableGateway->adapter);
        $query = $sql1->select()->columns(array('f.idFilm'));
        $query->from(array(
            'f' => $this->tableGateway->getTable()))
            ->join(array('p' => 'programmation'),'f.idFilm = p.film')
            ->where->notIn('p.film',$subquery);

        //la sous requete de la 2e requete
        $sql2 = new Sql($this->tableGateway->adapter);
        $subquery2 = $sql2->select()->columns(array('f2.idFilm'));
        $subquery2->from(array('f2' => $this->tableGateway->adapter))
            ->join(array('p' => 'programmation', 'p.film = f2.idFilm'));


        //La 2e requete
        $sql3 = new Sql($this->tableGateway->adapter);
        $query1 = $sql3->select()->columns(array('f.idFilm'));
        $query1->from(array('f'=>$this->tableGateway->adapter))
            ->where->notIn('f.idFilm',$subquery2);

        //L'union
        $query->combine($query1);

        $resultSet = $this->tableGateway->selectWith($query);

        return $resultSet;
    }

}