<?php
$root = dirname(__FILE__);
$mj_user->template->set_root($root . '/ihtml');

$query = $mj_user->get_post_or_get_value('query');
$query_id = $mj_user->get_post_or_get_value('query_id');
$event_id = $mj_user->get_post_or_get_value('event_id');
$working_group_id = $mj_user->get_post_or_get_value('working_group_id');
$appeal_id = $mj_user->get_post_or_get_value('appeal_id');
$everyone_with_phone = $mj_user->get_post_or_get_value('everyone_with_phone');
$begin_date_received = $mj_user->get_post_or_get_value('begin_date_received');
$end_date_received = $mj_user->get_post_or_get_value('end_date_received');
$begin_date_pledged = $mj_user->get_post_or_get_value('begin_date_pledged');
$end_date_pledged = $mj_user->get_post_or_get_value('end_date_pledged');
$begin_amount = $mj_user->get_post_or_get_value('begin_amount');
$end_amount = $mj_user->get_post_or_get_value('end_amount');



if($query)
{
	$mj_user->template->set_file('list_file','list.ihtml');
	$mj_user->template->set_block('list_file','row','rows');

	// display list
	if($query_id)
	{
		$query_sql = "SELECT sql_statement FROM mj_queries WHERE mj_query_id = $query_id";
		$mj_user->db->QueryField($query_sql,$sql);
	}
	elseif($everyone_with_phone)
	{
		$sql = 'SELECT first_name,last_name,tel_day,tel_eve,tel_mobile,person.email,org.email as org_email,'.
				'org.tel,organization,person.notes '.
				'FROM person LEFT JOIN org ON person.org_id = org.org_id WHERE '.
				" tel_day like '%-%' OR tel_eve like '%-%' OR tel_mobile like '%-%'";
	}
	elseif($event_id)
	{
		$sql_where = '';
		if($event_id <> 'any')
		{
			$sql_where = " WHERE attendance.event_id = $event_id";
		}

		$sql = 'SELECT DISTINCT first_name,last_name,tel_day,tel_eve,tel_mobile,person.email,org.email as org_email,'.
				'org.tel,organization,person.notes '.
				'FROM person INNER JOIN attendance on person.person_id = attendance.person_id '.
				'LEFT JOIN org ON person.org_id = org.org_id' . $sql_where;

	}
	elseif($working_group_id)
	{
		$sql_where = '';
		if($working_group_id <> 'any')
		{
			$sql_where = " WHERE assignment.working_group_id = $working_group_id";
		}
		$sql = 'SELECT first_name,last_name,tel_day,tel_eve,tel_mobile,person.email,org.email as org_email,'.
				'org.tel,organization,person.notes '.
				'FROM person INNER JOIN assignment on person.person_id = assignment.person_id '.
				'LEFT JOIN org ON person.org_id = org.org_id' . $sql_where;

	}
	elseif($appeal_id)
	{
		$sql_where = '';
		if($appeal_id <> 'any')
		{
			$sql_where = " WHERE donation.appeal_id = $appeal_id";
		}
		$sql = 'SELECT first_name,last_name,tel_day,tel_eve,tel_mobile,person.email,org.email as org_email,'.
				'org.tel,organization,person.notes '.
				'FROM person INNER JOIN donation on person.person_id = donation.person_id '.
				'LEFT JOIN org ON person.org_id = org.org_id' . $sql_where;
	}
	elseif($begin_date_received)
	{
		if(!$mj_user->convert_to_db_acceptable_date($begin_date_received,$converted_begin_date_received))
		{
			$mj_user->display_message('Please enter your dates in mm-dd-yyyy format. Close this browser window to return.','error');
			return FALSE;
		}

		if(!$mj_user->convert_to_db_acceptable_date($end_date_received,$converted_end_date_received))
		{
			$mj_user->display_message('Please enter your dates in mm-dd-yyyy format. Close this browser window to return.','error');
			return FALSE;
		}
		$sql = 'SELECT first_name,last_name,tel_day,tel_eve,tel_mobile,person.email,org.email as org_email,'.
				'org.tel,organization,person.notes '.
				'FROM person INNER JOIN donation on person.person_id = donation.person_id '.
				'LEFT JOIN org ON person.org_id = org.org_id WHERE '.
				"donation.date_received BETWEEN '$converted_begin_date_received' AND '$converted_end_date_received'";
	}
	elseif($begin_date_pledged)
	{
		if(!$mj_user->convert_to_db_acceptable_date($begin_date_pledged,$converted_begin_date_pledged))
		{
			$mj_user->display_message('Please enter your dates in mm-dd-yyyy format. Close this browser window to return.','error');
			return FALSE;
		}
		if(!$mj_user->convert_to_db_acceptable_date($end_date_pledged,$converted_end_date_pledged))
		{
			$mj_user->display_message('Please enter your dates in mm-dd-yyyy format. Close this browser window to return.','error');
			return FALSE;
		}
		$sql = 'SELECT first_name,last_name,tel_day,tel_eve,tel_mobile,person.email,org.email as org_email,'.
				'org.tel,organization,person.notes '.
				'FROM person INNER JOIN donation on person.person_id = donation.person_id '.
				'LEFT JOIN org ON person.org_id = org.org_id WHERE '.
				"donation.date_pledged BETWEEN '$converted_begin_date_pledged' AND '$converted_end_date_pledged'";
	}
	// Begin amount could be 0
	elseif(FALSE !== $begin_amount)
	{
		$sql = 'SELECT first_name,last_name,tel_day,tel_eve,tel_mobile,person.email,org.email as org_email,'.
				'org.tel,organization,person.notes '.
				'FROM person INNER JOIN donation on person.person_id = donation.person_id '.
				'LEFT JOIN org ON person.org_id = org.org_id WHERE '.
				"donation.amount_received BETWEEN $begin_amount AND $end_amount";
	}

	$result = $mj_user->db->Query($sql);
	$number_of_rows = $mj_user->db->NumberOfRows($result);

	if($number_of_rows == 0)
	{
		$mj_user->display_message('No records match your query. Close this browser window to return.','info');
		return FALSE;
	}
	else
	{
		$mj_user->db->GetColumnNames($result,$column_names);
		$i = 0;
		while($i < $number_of_rows)
		{
			foreach($column_names as $column_name => $column_number)
			{
				$mj_user->template->set_var($column_name,$mj_user->db->FetchResult($result,$i,$column_name));
			}

			$mj_user->template->parse('rows','row',TRUE);
			$i++;
		}	

		$mj_user->template->set_var('number_of_rows',$number_of_rows);
		$mj_user->template->set_var('sql',urlencode($sql));
		$mj_user->template->pparse('out','list_file');
		exit;
	}
}
else
{
	// display query options

	$sql = 'SELECT mj_query_id,friendly_name FROM mj_queries';
	$mj_user->db->QueryAll($sql,$all);
	foreach($all as $row)
	{
		$query_id = $row[0];
		$friendly_name = $row[1];
		$query_id_array[$query_id] = $friendly_name;
	}

	$replace['query_id_options'] = $mj_user->create_options($query_id_array);

	$sql = 'SELECT working_group_id,name FROM working_group';
	$mj_user->db->QueryAll($sql,$all);
	$working_group_id_array['any'] = 'Anyone who is a member of any working group';
	foreach($all as $row)
	{
		$working_group_id = $row[0];
		$name = $row[1];
		$working_group_id_array[$working_group_id] = $name;
	}

	$replace['working_group_id_options'] = $mj_user->create_options($working_group_id_array);

	$sql = 'SELECT event_id,name,date FROM event';
	$mj_user->db->QueryAll($sql,$all);
	$event_id_array['any'] = 'Anyone who has attended any event';
	foreach($all as $row)
	{
		$event_id = $row[0];
		$name = $row[1];
		$date = $mj_user->convert_to_friendly_date($row[2]);
		$event_id_array[$event_id] = $name . ' ' . $date;
	}

	$replace['event_id_options'] = $mj_user->create_options($event_id_array);

	$sql = 'SELECT appeal_id,name,date FROM appeal';
	$mj_user->db->QueryAll($sql,$all);
	$appeal_id_array['any'] = 'Anyone who has given to any appeal';
	foreach($all as $row)
	{
		$appeal_id = $row[0];
		$name = $row[1];
		$date = $mj_user->convert_to_friendly_date($row[2]);
		$appeal_id_array[$appeal_id] = $name . ' ' . $date;
	}

	$replace['appeal_id_options'] = $mj_user->create_options($appeal_id_array);

	$mj_user->template->set_file('options.ihtml','options.ihtml');
	$mj_user->template->set_var($replace);
	$mj_user->template->parse('mj_main_section','options.ihtml');
}


?>