<?php

	require_once 'class.db.php';

	include_once 'Mailin.php';

	require_once __DIR__ . '/esendex/autoload.php';

	// try{

		DataBase::getInstancia();

		// Insertamos los avisos a enviar para reservas de 72Horas

		$sql = 'SELECT reservations.id, reservations.date_start, DAY(date_start) as dayDate, MONTH(date_start) as monthDate, YEAR(date_Start) as yearDate, reservations.schedule_id, users.sendsms, users.sendemail
			  FROM reservations INNER JOIN boat ON reservations.boat_id = boat.id
			       INNER JOIN schedules ON schedules.id = reservations.schedule_id
                               INNER JOIN users ON users.id = reservations.user_id
			 WHERE date_start = (CURRENT_DATE + INTERVAL 3 DAY)
			   AND reservations.status_id = 2
			   AND reservations.id NOT IN (SELECT distinct reservation_id FROM reminder WHERE reminderType = "72h" AND sended = 1)';

		$resultados = DataBase::execute_query($sql, 'S');

		if (count($resultados) > 0) {
            foreach ($resultados as $r) {

                $fecha = '';
                if ($r['schedule_id'] == 1)
                    $fecha = mktime(9,30,0,$r['monthDate'], $r['dayDate'], $r['yearDate']);
                elseif ($r['schedule_id'] == 2)
                    $fecha = mktime(18,0,0,$r['monthDate'], $r['dayDate'], $r['yearDate']);
                elseif ($r['schedule_id'] == 3)
                    $fecha = mktime(9,30,0,$r['monthDate'], $r['dayDate'], $r['yearDate']);
                elseif ($r['schedule_id'] == 4)
                    $fecha = mktime(15,0,0,$r['monthDate'], $r['dayDate'], $r['yearDate']);

                $disp = '';
                if ($r['sendsms'] == '1' && $r['sendemail'] == '1')
                    $disp = 'both';
                elseif ($r['sendsms'] == '1')
                    $disp = 'sms';
                elseif ($r['sendemail'] == '1')
                    $disp = 'email';

                if ($fecha != '' && $disp != ''){
                    $sql = 'INSERT IGNORE INTO reminder ( reservation_id, reminderDate, reminderType, reminderDisp) VALUES ( '.$r['id'].', STR_TO_DATE("'.date("Y-m-d H:i", $fecha).'", "%Y-%m-%d %H:%i"), "72h", "'.$disp.'" )';
                    DataBase::execute_query($sql, 'S');
                }

            }
        }



		// Insertamos los avisos de 48Horas

		$sql = 'SELECT reservations.id, reservations.date_start, DAY(date_start) as dayDate, MONTH(date_start) as monthDate, YEAR(date_Start) as yearDate, reservations.schedule_id, users.sendsms, users.sendemail
			  FROM reservations INNER JOIN boat ON reservations.boat_id = boat.id
			       INNER JOIN schedules ON schedules.id = reservations.schedule_id
                               INNER JOIN users ON users.id = reservations.user_id
			 WHERE date_start = (CURRENT_DATE + INTERVAL 2 DAY)
			   AND reservations.status_id = 2
			   AND reservations.id NOT IN (SELECT distinct reservation_id FROM reminder WHERE reminderType = "48h" AND sended = 1)';

		$resultados = DataBase::execute_query($sql, 'S');

		if (count($resultados) > 0) {
            foreach ($resultados as $r) {

                $fecha = '';
                if ($r['schedule_id'] == 1)
                    $fecha = mktime(9,30,0,$r['monthDate'], $r['dayDate'], $r['yearDate']);
                elseif ($r['schedule_id'] == 2)
                    $fecha = mktime(18,0,0,$r['monthDate'], $r['dayDate'], $r['yearDate']);
                elseif ($r['schedule_id'] == 3)
                    $fecha = mktime(9,30,0,$r['monthDate'], $r['dayDate'], $r['yearDate']);
                elseif ($r['schedule_id'] == 4)
                    $fecha = mktime(15,0,0,$r['monthDate'], $r['dayDate'], $r['yearDate']);

                $disp = '';
                 if ($r['sendsms'] == '1'){
                    $disp = 'sms';
                 }
                if ($r['sendemail'] == '1'){
                    $disp = 'email';
                }
                if ($r['sendsms'] == '1' && $r['sendemail'] == '1'){
                    $disp = 'both';
                }

                if ($fecha != '' && $disp != ''){
                    $sql = 'INSERT IGNORE INTO reminder ( reservation_id, reminderDate, reminderType, reminderDisp) VALUES ( '.$r['id'].', STR_TO_DATE("'.date("Y-m-d H:i", $fecha).'", "%Y-%m-%d %H:%i"), "48h", "'.$disp.'" )';
                    DataBase::execute_query($sql, 'S');
                }

            }
        }


        // 24Horas - NO de ultima hora

    		$sql = 'SELECT reservations.id, reservations.date_start, DAY(date_start) as dayDate, MONTH(date_start) as monthDate, YEAR(date_Start) as yearDate, reservations.schedule_id, users.sendsms, users.sendemail
			  FROM reservations INNER JOIN boat ON reservations.boat_id = boat.id
			       INNER JOIN schedules ON schedules.id = reservations.schedule_id
                               INNER JOIN users ON users.id = reservations.user_id
			 WHERE date_start = (CURRENT_DATE + INTERVAL 1 DAY)
			   AND reservations.status_id = 2
			   AND reservations.id NOT IN (SELECT reservation_id FROM bookings24h)
			   AND reservations.id NOT IN (SELECT distinct reservation_id FROM reminder WHERE reminderType = "24h" AND sended = 1)';

	        $resultados = DataBase::execute_query($sql, 'S');

		    if (count($resultados) > 0) {
                foreach ($resultados as $r) {

        			$fecha = '';
                    if ($r['schedule_id'] == 1)
                        $fecha = mktime(9,30,0,$r['monthDate'], $r['dayDate'], $r['yearDate']);
                    elseif ($r['schedule_id'] == 2)
                        $fecha = mktime(18,0,0,$r['monthDate'], $r['dayDate'], $r['yearDate']);
                    elseif ($r['schedule_id'] == 3)
                        $fecha = mktime(9,30,0,$r['monthDate'], $r['dayDate'], $r['yearDate']);
                    elseif ($r['schedule_id'] == 4)
                        $fecha = mktime(15,0,0,$r['monthDate'], $r['dayDate'], $r['yearDate']);

                    $disp = '';
                    if ($r['sendsms'] == '1' && $r['sendemail'] == '1')
                        $disp = 'both';
                    elseif ($r['sendsms'] == '1')
                        $disp = 'sms';
                    elseif ($r['sendemail'] == '1')
                        $disp = 'email';

                    if ($fecha != '' && $disp != ''){
                        $sql = 'INSERT IGNORE INTO reminder ( reservation_id, reminderDate, reminderType, reminderDisp) VALUES ( '.$r['id'].', STR_TO_DATE("'.date("Y-m-d H:i", $fecha).'", "%Y-%m-%d %H:%i"), "24h", "'.$disp.'" )';
                        DataBase::execute_query($sql, 'S');
                    }

                }
            }


            // 24Horas - Reserva de último momento

    		$sql = 'SELECT distinct reservations.id,  reservations.date_start,  DAY(date_start) as dayDate, MONTH(date_start) as monthDate, YEAR(date_Start) as yearDate, reservations.schedule_id, users.sendsms, users.sendemail
    					  FROM reservations INNER JOIN boat ON reservations.boat_id = boat.id
    					       INNER JOIN schedules ON schedules.id = reservations.schedule_id
    				           INNER JOIN users ON users.id = reservations.user_id
    					 WHERE (to_seconds(NOW()) - to_seconds(reservations.created_at) BETWEEN 0 AND 86400)
    					       AND reservations.id IN (SELECT reservation_id FROM bookings24h)
    				           AND reservations.id NOT IN (SELECT DISTINCT reservation_id FROM reminder WHERE reminderType = "24h")
    					   AND reservations.status_id = 2';

    		$resultados = DataBase::execute_query($sql, 'S');

    		if (count($resultados) > 0) {
                foreach ($resultados as $r) {

            		$fecha = '';
                    if ($r['schedule_id'] == 1)
                        $fecha = mktime(9,30,0,$r['monthDate'], $r['dayDate'], $r['yearDate']);
                    elseif ($r['schedule_id'] == 2)
                        $fecha = mktime(18,0,0,$r['monthDate'], $r['dayDate'], $r['yearDate']);
                    elseif ($r['schedule_id'] == 3)
                        $fecha = mktime(9,30,0,$r['monthDate'], $r['dayDate'], $r['yearDate']);
                    elseif ($r['schedule_id'] == 4)
                        $fecha = mktime(15,0,0,$r['monthDate'], $r['dayDate'], $r['yearDate']);

                    $disp = '';
                    if ($r['sendsms'] == '1' && $r['sendemail'] == '1')
                        $disp = 'both';
                    elseif ($r['sendsms'] == '1')
                        $disp = 'sms';
                    elseif ($r['sendemail'] == '1')
                        $disp = 'email';

                    if ($fecha != '' && $disp != ''){
                        $sql = 'INSERT IGNORE INTO reminder ( reservation_id, reminderDate, reminderType, reminderDisp) VALUES ( '.$r['id'].', STR_TO_DATE("'.date("Y-m-d H:i", $fecha).'", "%Y-%m-%d %H:%i"), "24h", "'.$disp.'" )';
                        DataBase::execute_query($sql, 'S');

                        $sql = 'INSERT IGNORE INTO reminder ( reservation_id, reminderDate, reminderType, reminderDisp) VALUES ( '.$r['id'].', STR_TO_DATE("'.date("Y-m-d H:i", $fecha).'", "%Y-%m-%d %H:%i"), "c24", "'.$disp.'" )';
                        DataBase::execute_query($sql, 'S');

                    }


                }
            }


	        // Si pasan 30 minutos desde la reserva de 24h, se cancela

	        $sql = 'SELECT reservations.id, reservations.first_name, reservations.last_name, DATE_FORMAT(reservations.date_start, \'%d-%m-%Y\') as dateStart, reservations.reference_number, reservations.telephone, reservations.email, reservations.date_start, boat.name as boatName, schedules.name as scheduleName, users.sendsms, users.sendemail
	                  FROM reservations INNER JOIN boat ON reservations.boat_id = boat.id
					       INNER JOIN schedules ON schedules.id = reservations.schedule_id
				               INNER JOIN users ON users.id = reservations.user_id
	                 WHERE reservations.id IN (SELECT reservation_id  FROM `bookings24h` WHERE NOW() > DATE_ADD(created_at, INTERVAL +30 MINUTE))
	                   AND reservations.status_id NOT IN (1,3)';
	                 // (SELECT reservation_id FROM `bookings24h` WHERE created_at < DATE_ADD(NOW(), INTERVAL -30 MINUTE))

	        $resultados = DataBase::execute_query($sql, 'S');

	        if (count($resultados) > 0) {
                foreach ($resultados as $r)
                {

        			if ($r['id'] > 0){
        			    $sql = 'UPDATE reservations SET status_id = 3 WHERE id = '.$r['id'];
        			    DataBase::execute_query($sql, 'S');
        			    echo $sql . '<br/>';

        			    $sql = 'DELETE FROM bookings24h WHERE reservation_id = '.$r['id'];
        			    DataBase::execute_query($sql, 'S');
        			    echo $sql . '<br/>';
        			}

                    $disp = '';
                     if ($r['sendsms'] == '1' && $r['sendemail'] == '1')
                        $disp = 'both';
                    elseif ($r['sendsms'] == '1')
                        $disp = 'sms';
                    elseif ($r['sendemail'] == '1')
                        $disp = 'email';

                    if ($fecha != '' && $disp != ''){
                        $sql = 'INSERT IGNORE INTO reminder ( reservation_id, reminderDate, reminderType, reminderDisp) VALUES ( '.$r['id'].', STR_TO_DATE("'.date("Y-m-d H:i", time()+1800).'", "%Y-%m-%d %H:%i"), "now", "'.$disp.'" )';
                        DataBase::execute_query($sql, 'S');
                    }
                }
	        }

            // Cancelamos automaticamente las reservas pasadas que no estaban confirmadas
            $sql = 'UPDATE reservations SET status_id = 3 WHERE status_id NOT IN (1,3) AND id IN (SELECT reservation_id FROM reminder WHERE reminder.sended > 0 AND reminder.reminderType = \'24h\' AND reminder.reminderDate < CURRENT_TIMESTAMP)';
            //DataBase::execute_query($sql, 'S');


            /* ---------------------- BEGIN INCIDENCE SEND ------------------------ */

            	$sql = 'SELECT * FROM incidences WHERE sended = 0';
		        $resultados = DataBase::execute_query($sql, 'S');

		        if (count($resultados) > 0) {
                    foreach ($resultados as $res) {

                        $texto = '<table width="840" border="0" align="center" cellpadding="0" cellspacing="0" bgcolor="#FFFFFF">
                    <tbody>
                    <tr>
                        <td height="15" colspan="3"></td>
                    </tr>
                    <tr>
                        <td width="25"></td>
                        <td>
                            <table cellpadding="0" cellspacing="0" width="100%">
                                <tbody>
                                <tr>
                                    <td><img
                                            src="http://skipper.komunica.es/cron/logo.gif"
                                            width="200" height="109" class="CToWUd"></td>
                                </tr>
                                <tr>
                                    <td height="10"></td>
                                </tr>
                                <tr>
                                    <td>Nueva Incidencia: '.$res['comments'].'</td>
                                </tr>
                                <tr>
                                    <td height="10"></td>
                                </tr>
                                </tbody>
                            </table>
                        </td>
                        <td width="25"></td>
                    </tr>
                    <tr></tr>
                    </tbody>
                </table>';

                        $mailin5 = new Mailin('juan.garcia.mir@gmail.com', 'DnMLSq8aGA1czXOm');
                        $mailin5->addTo('juanantonio@embat.es', 'Admin')->
                        setFrom('bookings@skippersailingclub.com', 'Skipper')->
                        setSubject('Nueva incidencia')->
                        setHtml($texto);
                        $resultEmail = $mailin5->send();

                        $sql = 'UPDATE incidences SET sended = 1 WHERE id = '.$res['id'];
                        DataBase::execute_query($sql, 'S');
                    }
		        }

		/* ---------------------- END INCIDENCE SEND ------------------------ */


   /*	}catch(Exception $e){
        echo $e->getMessage();
        echo $e->getTraceAsString();
    }*/

?>
