<?php

//set the full system paths
define('BASE_PATH','/var/www/html/stg-fsp-schedula/schedula-api/');

//connect to db
require_once(BASE_PATH.'mysql_connect.php');

/*
APPOINTMENT_ID	MATCH_ID	MEMBER_ID	TYPE_ID	STATUS
123			3048089	7803679	82		Appointed
124			3048089	7825379	80 		Appointed
125			3048089	3900191	81		Appointed
126			3048089	2897478	81		Appointed
*/

//set the error message to diaplay if there's a problem
$error_message = "Invalid or missing options.";

		function IsPublished($fixture_id,$organisation_id) {
		
			//get the publish information
			$publish_info["day_query"] = mysql_query("SELECT * FROM organisations_settings WHERE org_id = " .$organisation_id ." AND setting = 'appointments.publish.day' LIMIT 0,1");
			$publish_info["time_query"] = mysql_query("SELECT * FROM organisations_settings WHERE org_id = " .$organisation_id ." AND setting = 'appointments.publish.time' LIMIT 0,1");
			$publish_info["period_query"] = mysql_query("SELECT * FROM organisations_settings WHERE org_id = " .$organisation_id ." AND setting = 'appointments.publish.period' LIMIT 0,1");
			
			//publish day
			$publish_info["day"] = mysql_result($publish_info["day_query"],0,"value");
			$publish_info["time"] = mysql_result($publish_info["time_query"],0,"value");
			$publish_info["period"] = mysql_result($publish_info["period_query"],0,"value");
			
			if(date("l") == $publish_info["day"] AND date("H:i:s") >= $publish_info["time"]) {
				$date_of_last_publish_day = strtotime($publish_info["day"]);
			} else {
				$date_of_last_publish_day = strtotime("last " .$publish_info["day"]);
			}
			
			$publish_date = date("Y-m-d",$date_of_last_publish_day);
			$publish_end_date = date("Y-m-d",($date_of_last_publish_day + ($publish_info["period"]*24*60*60)));
			
			$publish_date = date("Y-m-d",$date_of_last_publish_day);
			$publish_end_date = date("Y-m-d",($date_of_last_publish_day + ($publish_info["period"]*24*60*60)));
			
			//get the fixture date
			$fixture_query = mysql_query("SELECT date,start_time FROM fixtures_view WHERE id = " .$fixture_id);
			
			$fixture_date = mysql_result($fixture_query,0,"date");
			$fixture_time = mysql_result($fixture_query,0,"start_time");
			
			if($fixture_date >= date("Y-m-d")) {
				//filter based on the info above
				if(date("Y-m-d") > $publish_date OR (date("Y-m-d") == $publish_date AND date("H:i:s") >= $publish_info["time"])) {
					if($fixture_date > date("Y-m-d") OR ($fixture_date == date("Y-m-d") AND $fixture_time >= date("H:i:s"))) {
						if($fixture_date <= $publish_end_date) {
							return true;
						} else {
							return false;
						}
					} else {
						return false;
					}
				} else {
					return false;
				}
			} else {
				return true;
			}
			
		}

//make sure there's valid input
//?aID=XXX&datefrom=XXX&dateto=XXX

$assoc_id = $_GET["aID"];		//SP ASSOC ID
$date_from = $_GET["datefrom"];	//Date (format: yyyymmdd)
$date_to = $_GET["dateto"];		//Date (format: yyyymmdd)

if($assoc_id == "" OR $date_from == "" OR $date_to == "") {
	exit($error_message);
}

//determine that the ASSOC ID can be called (only those ASSOC_ID who are pulling both regos and fixtures from SP for the given season)
$correct_assoc_id = mysql_query("SELECT DISTINCT
	organisations.SP_assoc_id AS assoc_id
	FROM appointments_types
	INNER JOIN organisations ON appointments_types.organisation_id = organisations.id
	INNER JOIN sp_schedula_relationships ON organisations.id = sp_schedula_relationships.schedula_organisation_id
	INNER JOIN organisations_seasons ON sp_schedula_relationships.schedula_season_id = organisations_seasons.id
	WHERE sp_schedula_relationships.pull_fixtures = 'YES' AND
	sp_schedula_relationships.pull_regos = 'YES' AND
	organisations_seasons.`start` <= NOW() AND organisations_seasons.`end` >= NOW() AND
	organisations.SP_assoc_id = " .$assoc_id);

if(mysql_num_rows($correct_assoc_id) == 0) {
	exit($error_message);
}

//make sure that the dates are valid
if(strlen($date_from) != 8 OR strlen($date_to) != 8) {
	exit($error_message);
}

//format the dates for the database
$date_from = substr($date_from,0,4) ."-" .substr($date_from,4,2) ."-" .substr($date_from,6,2);
$date_to = substr($date_to,0,4) ."-" .substr($date_to,4,2) ."-" .substr($date_to,6,2);

//determine the appointments
$query = mysql_query("SELECT
	appointments.id AS appointment_id,
	appointments.fixture_id AS match_id,
	organisations_memberships.person_id AS member_id,
	appointments.type_id,
	appointments.`status`,
	fixtures_view.date AS fixture_date,
	fixtures_view.start_time AS fixture_start_time,
	organisations_seasons.organisation_id
	FROM
	appointments
	INNER JOIN organisations_memberships ON appointments.membership_id = organisations_memberships.id
	INNER JOIN organisations ON organisations_memberships.organisation_id = organisations.id
	INNER JOIN fixtures_view ON appointments.fixture_id = fixtures_view.id
	INNER JOIN sp_schedula_relationships ON organisations.id = sp_schedula_relationships.schedula_organisation_id
	INNER JOIN organisations_seasons ON organisations_memberships.organisation_id = organisations_seasons.organisation_id
	WHERE organisations.SP_assoc_id = " .$assoc_id ." AND (`status` = 'Appointed' OR `status` = 'Removed') AND
	fixtures_view.date >= '" .$date_from ."' AND fixtures_view.date <= '" .$date_to ."' AND
	organisations_seasons.`start` <= NOW() AND organisations_seasons.`end` >= NOW() AND
	sp_schedula_relationships.pull_fixtures = 'YES' AND sp_schedula_relationships.pull_regos = 'YES'");

echo "<html><body><pre>";

//display the titles
echo "APPOINTMENT_ID" .chr(9) ."MATCH_ID" .chr(9) ."MEMBER_ID" .chr(9) ."TYPE_ID" .chr(9) ."STATUS<br>";

for($i=0;$i<mysql_num_rows($query);$i++) {
	
	//determine if the fixture is published
	if(IsPublished(mysql_result($query,$i,"match_id"),mysql_result($query,$i,"organisation_id")) == true) {
		echo mysql_result($query,$i,"appointment_id") .chr(9) .mysql_result($query,$i,"match_id") .chr(9) .mysql_result($query,$i,"member_id") .chr(9) .mysql_result($query,$i,"type_id") .chr(9) .mysql_result($query,$i,"status") ."<br>";
	}
	
}

echo "</pre></body></html>";

?>