<?php

    date_default_timezone_set('US/Eastern');
    $todays_date = date('d');
    $day_name = date('l');
    $current_month = date('F');
    $current_year = date('o');
    $days_in_month = cal_days_in_month(CAL_GREGORIAN, date('m'), $current_year);

// Header
 $line = array();
 $line[] = 'Day';
 $line[] = 'Day Name';
 $line[] = 'Month';
 $line[] = 'Building';
 $line[] = 'Branch';
 $line[] = 'Event 1 Title';
 $line[] = 'Event 1 Time';
 $line[] = 'Event 1 Duration';
 $line[] = 'Event 1 Room';
 $line[] = 'Event 1 Description';
 $line[] = 'Event 2 Title';
 $line[] = 'Event 2 Time';
 $line[] = 'Event 2 Duration';
 $line[] = 'Event 2 Room';
 $line[] = 'Event 2 Description';
 $line[] = 'Event 3 Title';
 $line[] = 'Event 3 Time';
 $line[] = 'Event 3 Duration';
 $line[] = 'Event 3 Room';
 $line[] = 'Event 3 Description';

    $servername = 'localhost';
    $username = 'root';
    $password = '';
    $db = 'prod_d7apps';

    $pdo = new PDO("mysql:host=$servername;dbname=$db", $username, $password);
//SQL Query
 $sql = "SELECT node.nid AS nid, node.title AS node_title, node.uid AS node_uid, mcgill_library_workshops_register.status AS mcgill_library_workshops_register_status, field_data_field_workshopsession_date.field_workshopsession_date_value AS field_data_field_workshopsession_date_field_workshopsession_, 'node' AS field_data_field_workshopsession_date_node_entity_type, 'node' AS field_data_field_workshopsession_branch_node_entity_type, 'node' AS field_data_field_workshopsession_location_node_entity_type, 'node' AS field_data_field_workshopsession_room_node_entity_type, 'node' AS field_data_field_workshopsession_notes_node_entity_type, 'node' AS field_data_field_workshopsession_ext_link_node_entity_type, 'mcgill_library_workshops_view_workshops:page_1' AS view_name
 FROM
 node AS node
 LEFT JOIN field_data_field_workshopsession_workshop AS field_data_field_workshopsession_workshop ON node.nid = field_data_field_workshopsession_workshop.entity_id AND (field_data_field_workshopsession_workshop.entity_type = 'node' AND field_data_field_workshopsession_workshop.deleted = '0')
 LEFT JOIN node AS node_field_data_field_workshopsession_workshop ON field_data_field_workshopsession_workshop.field_workshopsession_workshop_target_id = node_field_data_field_workshopsession_workshop.nid
 LEFT JOIN field_data_field_workshopsession_date AS field_data_field_workshopsession_date ON node.nid = field_data_field_workshopsession_date.entity_id AND (field_data_field_workshopsession_date.entity_type = 'node' AND field_data_field_workshopsession_date.deleted = '0')
 LEFT JOIN mcgill_library_workshops_register as mcgill_library_workshops_register ON node.nid = mcgill_library_workshops_register.nid
 WHERE (( (node.type IN  ('library_workshop_session')) AND (DATE_FORMAT(ADDTIME(field_data_field_workshopsession_date.field_workshopsession_date_value, SEC_TO_TIME(-18000)), '%Y-%m-%d\T%H') >= '2017-11-30T00') ))
 ORDER BY field_data_field_workshopsession_date_field_workshopsession_ ASC
 LIMIT 10 OFFSET 0";

 $statement = $pdo->prepare($sql);
 $statement->execute();

 $rows = $statement->fetchAll(PDO::FETCH_ASSOC);

// Getting the id, which will be used to bring info from other queries.

 $info = array();
 foreach($rows as $id){
 $info[$id['nid']] = $id;
 }
 $rows = $info;

//Getting Title of the event.

 foreach($rows as $query => $value){
 $entity_id = $value['nid'];

 $result = "SELECT title  FROM field_data_field_workshopsession_branch LEFT JOIN node ON nid = field_data_field_workshopsession_branch.field_workshopsession_branch_target_id  WHERE entity_type = 'node' AND entity_id ='".$entity_id."'";
 $statement1 = $pdo->prepare($result);
 $statement1->execute();

 $rows1 = $statement1->fetchAll(PDO::FETCH_ASSOC);
 $rows[$query]['title'] = $rows1[0]['title'];

 }

//Getting location and adding to main row array

 foreach($rows as $query => $value){
 $query = $value['nid'];
 $result2 = "SELECT field_workshopsession_location_value  FROM field_data_field_workshopsession_location LEFT JOIN node ON nid = field_data_field_workshopsession_location.field_workshopsession_location_value  WHERE entity_type = 'node' AND entity_id ='".$query."'";
 $statement2 = $pdo->prepare($result2);
 $statement2->execute();

 $rows2 = $statement2->fetchAll(PDO::FETCH_ASSOC);
 $rows[$query]['field_workshopsession_location_value'] = $rows2[0]['field_workshopsession_location_value'];

 }
//Getting Room Number.

 foreach($rows as $query => $value){
 $query = $value['nid'];
 $result3 = "SELECT field_workshopsession_room_value  FROM field_data_field_workshopsession_room LEFT JOIN node ON nid = field_data_field_workshopsession_room.field_workshopsession_room_value  WHERE entity_type = 'node' AND entity_id ='".$query."'";
 $statement3 = $pdo->prepare($result3);
 $statement3->execute();

 $rows3 = $statement3->fetchAll(PDO::FETCH_ASSOC);
 $rows[$query]['field_workshopsession_room_value'] = $rows3[0]['field_workshopsession_room_value'];
 }
//Getting the Duration of the event.

 foreach($rows as $query => $value){
 error_reporting(E_ALL & ~E_NOTICE);
 $query = $value['nid'];
 $result4 = "SELECT field_workshop_duration_value  FROM field_data_field_workshop_duration LEFT JOIN node ON nid = field_data_field_workshop_duration.field_workshop_duration_value WHERE entity_type = 'node' AND entity_id ='".$query."'";
 $statement4 = $pdo->prepare($result4);
 $statement4->execute();
 $rows4 = $statement4->fetchAll(PDO::FETCH_ASSOC);
 $rows[$query]['field_workshop_duration_value'] = $rows4[0]['field_workshop_duration_value'];
 }
//Getting the notes for events.

 foreach($rows as $query => $value){
 error_reporting(E_ALL & ~E_NOTICE);
 $query = $value['nid'];
 $result5 = "SELECT field_workshopsession_notes_value FROM `field_data_field_workshopsession_notes` WHERE entity_id = '".$query."'";
 $statement5 = $pdo->prepare($result5);
 $statement5->execute();

 $rows5 = $statement5->fetchAll(PDO::FETCH_ASSOC);
 $rows[$query]['field_workshopsession_notes_value'] = $rows5[0]['field_workshopsession_notes_value'];
 }
//getting the description of the events and adding to main array row
 foreach($rows as $query => $value){
 error_reporting(E_ALL & ~E_NOTICE);
 $query = $value['nid'];
 $result6 = "SELECT field_workshop_description_value  FROM field_data_field_workshop_description LEFT JOIN node ON nid = field_data_field_workshop_description.field_workshop_description_value WHERE entity_type = 'node' AND entity_id ='".$query."'";
 $statement6 = $pdo->prepare($result5);
 $statement6->execute();

 $rows6 = $statement6->fetchAll(PDO::FETCH_ASSOC);
 $rows[$query]['field_workshop_description_value'] = $rows6[0]['field_workshop_description_value'];
 // var_dump($rows3[0]['field_workshopsession_room_value']);
 }

 $rows = array_values($rows);

 echo '<pre>';
 print_r($rows);
 echo '</pre>';
 die();

$fileName = 'data';
 $csv_fileName = $fileName.date('Ymd',time()).'.csv';

 header('Content-Type: application/excel');
 header('Content-Disposition: attachment; filename="' . $csv_fileName . '"');

 $fp = fopen('php://output', 'w');

 $headers = fputcsv($fp, $line);

 foreach($rows as $row){
 $csv = fputcsv($fp, $row);
 file_put_contents('/fsm_php',$csv_fileName);
 }

 fclose($fp);

?>