<?php

	require_once 'class.db.php';

	include_once 'Mailin.php';

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


	$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, reminder.reminderDate, reminder.reminderDisp, reminder.reminderType, reminder.id as reminderId,
	               (to_seconds(reminder.reminderDate) - to_seconds(CURRENT_TIMESTAMP())) as diffTiempo
    		  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
    			INNER JOIN reminder ON reminder.reservation_id = reservations.id AND reminder.sended < 1 AND reminder.reminderType != "c24"
    		 WHERE reservations.status_id = 2
    		   AND NOW() > DATE_ADD(reservations.created_at, INTERVAL +150 MINUTE))
    		  UNION
    		  (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, reminder.reminderDate, reminder.reminderDisp, reminder.reminderType, reminder.id as reminderId,
	               (to_seconds(reminder.reminderDate) - to_seconds(CURRENT_TIMESTAMP())) as diffTiempo
    		  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
    			INNER JOIN reminder ON reminder.reservation_id = reservations.id AND reminder.sended = 0 AND reminder.reminderType = "c24"
    		 	WHERE reservations.status_id = 2 AND reminder.sended = 0)';

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


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

                    // Checkeamos si tenemos que enviarlo
                    if (($r['reminderType'] == '72h' && $r['diffTiempo'] > 259200 )
												|| ($r['reminderType'] == '48h' && $r['diffTiempo'] > 172800)
												|| ($r['reminderType'] == '24h' && $r['diffTiempo'] > 86400)
												|| ($r['reminderType'] == 'now' && $r['diffTiempo'] < 0))
											{
                        break;
											}

                    if ($r['reminderType']  == 'now' || $r['reminderType'] == '24h'){

                        $sqlCancela = 'UPDATE reservations SET status_id = 3 WHERE id =' . $r['id'];
                        DataBase::execute_query($sqlCancela, 'S');
                    }

                    /* --------------------- ESSENDEX ------------------------ */

                  if ( $r['reminderDisp'] == 'sms' || $r['reminderDisp'] == 'both' ){


	                  if ($r['reminderType']  == 'now' || $r['reminderType'] == '24h'){

	                      $texto = 'Su reserva para el '. $r['date_start'] .' ha sido cancelada';

	                  }elseif ($r['reminderType'] == 'c24'){

	                      $confirmLink = 'http://skipper.komunica.es/conRes.php?number='.$r['reference_number'];
					    					$texto = 'Recuerde: dispone de 30 minutos para confirmar su reserva del ' . $r['dateStart'] . ' '. $r['scheduleName'] . ' clickando en '.$confirmLink;

	                  }else{

					    					$confirmLink = 'http://skipper.komunica.es/conRes.php?number='.$r['reference_number'];
					    					$texto = 'Recuerde: debe confirmar su reserva del ' . $r['dateStart'] . ' '. $r['scheduleName'] . ' desde la web o clickando en '.$confirmLink;

	                  }

		                $message = new \Esendex\Model\DispatchMessage(
		                    "Skipper", $r['telephone'], $texto,
		                    \Esendex\Model\Message::SmsType );

		                $authentication = new \Esendex\Authentication\LoginAuthentication(
		                    "EX0094931", "pedro@komunica.es", "Skippersailing2018" );
		                $service = new \Esendex\DispatchService($authentication);
		                $response = $service->send($message);

            			$result = $response->id();

    						}

    				/* -------------------- END ESSENDEX ---------------------- */


    				/* -------------------- EMAIL ------------------------------ */


    				if ( $r['reminderDisp'] == 'email' || $r['reminderDisp'] == 'both' ){

    				    if ($r['reminderType']  == 'now' || $r['reminderType'] == '24h'){

    				        $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>Apreciado Sr./Sra. '.$r['first_name'].' '. $r['last_name'] .',<br>Skipper Sailing Club ha cancelado su reserva
                                            correspondiente a:<br><b>Fecha inicio</b>: '.$r['dateStart'].' con horario de '.utf8_encode($r['scheduleName']).'<br><b>Fecha fin</b>: '.$r['dateStart'].'<br><b>Salidas consumidas</b>: <br><br>Para su barco asignado:<br><b>Barco</b>: '.$r['boatName'].'<br><br>El motivo de la cancelación es debido a que no hemos recibido ninguna confirmación entre las 72h y las 24h. antes de la fecha de la reserva.
                                        </td>
                                    </tr>
                                    <tr>
                                        <td height="10"></td>
                                    </tr>
                                    <tr>
                                        <td style="font-family:Arial,Helvetica,sans-serif;font-size:9px;font-weight:normal;color:rgb(153,153,153);padding:5px;text-align:center">
                                            Direccion: Calle Bernarregui, 8. Palma de Mallorca | Telefono <a href="tel:610%2061%2095%2047" target="_blank">610 619 547</a> |
                                            Email: <a href="mailto:bookings@skippersailingclub.com" style="font-family:Arial,Helvetica,sans-serif;font-size:9px;font-weight:normal;color:rgb(153,153,153);text-align:center;text-decoration:none"
                                                      target="_blank">bookings@skippersailingclub.com</a>
                                        </td>
                                    </tr>
                                    <tr>
                                        <td height="10"></td>
                                    </tr>
                                    <tr>
                                        <td style="font-family:Arial,Helvetica,sans-serif;font-size:10px;font-weight:bold;padding:5px;text-align:center;background-color:rgb(204,204,204)">
                                            SKIPPER SAILING CLUB
                                        </td>
                                    </tr>
                                    </tbody>
                                </table>
                            </td>
                            <td width="25"></td>
                        </tr>
                        <tr></tr>
                        </tbody>
                    </table>';

    				    }elseif( $r['reminderType'] == 'c24' ){

    				        $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>Apreciado Sr./Sra. '.$r['first_name'].' '. $r['last_name'] .',<br>Skipper Sailing Club le recuerda que su reserva
	                                                    correspondiente a:<br><b>Fecha inicio</b>: '.$r['dateStart'].' con horario de '.utf8_encode($r['scheduleName']).'<br><b>Fecha fin</b>: '.$r['dateStart'].'<br><b>Salidas consumidas</b>: <br><br>Para su barco asignado:<br><b>Barco</b>: '.$r['boatName'].'<br><br>Rogamos
	                                                    CONFIRME su(s) reserva(s) clickando <a href="http://skipper.komunica.es/conRes.php?number='.$r['reference_number'].'" target="_blank">aquí</a> antes de 30 minutos.
	                                                    <br>Si
	                                                    confirma esta reserva, se le descontarán las salidas correspondientes.<br>Si desea
	                                                    CANCELARLA(S), haga click <a href="http://skipper.komunica.es/canRes.php?number='.$r['reference_number'].'" target="_blank">aquí</a><br>
	                                                    <br>Si no confirma su reserva en un plazo de 30 minutos, su reserva será cancelada.<br><br>Atentamente,
	                                                    Skipper Sailing Club
	                                                </td>
	                                            </tr>
	                                            <tr>
	                                                <td height="10"></td>
	                                            </tr>
	                                            <tr>
	                                                <td style="font-family:Arial,Helvetica,sans-serif;font-size:9px;font-weight:normal;color:rgb(153,153,153);padding:5px;text-align:center">
	                                                    Direccion: Calle Bernarregui, 8. Palma de Mallorca | Telefono <a href="tel:610%2061%2095%2047" target="_blank">610 619 547</a> |
	                                                    Email: <a href="mailto:bookings@skippersailingclub.com" style="font-family:Arial,Helvetica,sans-serif;font-size:9px;font-weight:normal;color:rgb(153,153,153);text-align:center;text-decoration:none"
	                                                              target="_blank">bookings@skippersailingclub.com</a>
	                                                </td>
	                                            </tr>
	                                            <tr>
	                                                <td height="10"></td>
	                                            </tr>
	                                            <tr>
	                                                <td style="font-family:Arial,Helvetica,sans-serif;font-size:10px;font-weight:bold;padding:5px;text-align:center;background-color:rgb(204,204,204)">
	                                                    SKIPPER SAILING CLUB
	                                                </td>
	                                            </tr>
	                                            </tbody>
	                                        </table>
	                                    </td>
	                                    <td width="25"></td>
	                                </tr>
	                                <tr></tr>
	                              </tbody>
                                </table>';

    				    }else{

    				        $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>Apreciado Sr./Sra. '.$r['first_name'].' '. $r['last_name'] .',<br>Skipper Sailing Club le recuerda que su reserva
                                                      correspondiente a:<br><b>Fecha inicio</b>: '.$r['dateStart'].' con horario de '.utf8_encode($r['scheduleName']).'<br><b>Fecha fin</b>: '.$r['dateStart'].'<br><b>Salidas consumidas</b>: <br><br>Para su barco asignado:<br><b>Barco</b>: '.$r['boatName'].'<br><br>Rogamos
                                                      CONFIRME su(s) reserva(s) clickando <a href="http://skipper.komunica.es/conRes.php?number='.$r['reference_number'].'" target="_blank">aquí</a>.
                                                      <br>Si
                                                      confirma esta reserva, se le descontarán las salidas correspondientes.<br>Si desea
                                                      CANCELARLA(S), haga click <a href="http://skipper.komunica.es/canRes.php?number='.$r['reference_number'].'" target="_blank">aquí</a><br>
                                                      <br>Si no confirma su reserva en un plazo de 24 horas, su reserva será cancelada.<br><br>Atentamente,
                                                      Skipper Sailing Club
                                                  </td>
                                              </tr>
                                              <tr>
                                                  <td height="10"></td>
                                              </tr>
                                              <tr>
                                                  <td style="font-family:Arial,Helvetica,sans-serif;font-size:9px;font-weight:normal;color:rgb(153,153,153);padding:5px;text-align:center">
                                                      Direccion: Calle Bernarregui, 8. Palma de Mallorca | Telefono <a href="tel:610%2061%2095%2047" target="_blank">610 619 547</a> |
                                                      Email: <a href="mailto:bookings@skippersailingclub.com" style="font-family:Arial,Helvetica,sans-serif;font-size:9px;font-weight:normal;color:rgb(153,153,153);text-align:center;text-decoration:none"
                                                                target="_blank">bookings@skippersailingclub.com</a>
                                                  </td>
                                              </tr>
                                              <tr>
                                                  <td height="10"></td>
                                              </tr>
                                              <tr>
                                                  <td style="font-family:Arial,Helvetica,sans-serif;font-size:10px;font-weight:bold;padding:5px;text-align:center;background-color:rgb(204,204,204)">
                                                      SKIPPER SAILING CLUB
                                                  </td>
                                              </tr>
                                              </tbody>
                                          </table>
                                      </td>
                                      <td width="25"></td>
                                  </tr>
                                  <tr></tr>
                                </tbody>
                              </table>';

    				    }



                        $mailin3 = new Mailin('juan.garcia.mir@gmail.com', 'DnMLSq8aGA1czXOm');
                        $mailin3->addTo($r['email'], $r['first_name'])->
                        setFrom('bookings@skippersailingclub.com', 'Skipper')->
                        setReplyTo('bookings@skippersailingclub.com','Skipper')->
                        setSubject('Recordatorio reserva Skipper Sailing Club')->
                        setHtml($texto);
                        $resultEmail = $mailin3->send();

                    }


    								/* -------------------- END EMAIL -------------------------- */


										/* -------------------- UPDATE SO IT IS NOT SENDED AGAIN -------------------------- */
										$sql = 'UPDATE reminder SET sended = 1 WHERE id = '.$r['reminderId'] ;
										DataBase::execute_query($sql, 'S');

                }
    		}
