<?php include('mod/_mod_security.php'); ?>
<?php
	$criteria = '';
	$filter = '';
	if (!empty($_POST['vci_date_start']) && is_date($_POST['vci_date_start'])) :
		$criteria .= " AND c.update_date >= " . nzdate($_POST['vci_date_start']) . " ";
		$filter .= '<br> Updated start date: ' . $_POST['vci_date_start'];
	endif;
	if (!empty($_POST['vci_date_end']) && is_date($_POST['vci_date_end'])) :
		$criteria .= " AND c.update_date <= " . nzdatetime($_POST['vci_date_end'] . ' 23:59:59') . " ";
		$filter .= '<br> Updated end date: ' . $_POST['vci_date_end'];
	endif;
	if (!empty($_POST['vci_category_id'])) :
		$criteria .= " AND v.category_id = " . nz($_POST['vci_category_id'], '0') . " ";
		$filter .= '<br> Venue category: ' . lookup_db_field('categories', 'id', $_POST['vci_category_id'], 'description');
	endif;
	if (!empty($_POST['vci_status'])) :
		$criteria .= " AND v.status = " . nz($_POST['vci_status'], '0') . " ";
		$filter .= '<br> Venue status: ';
		switch ($_POST['vci_status']) :
			case '0' :
				$filter .= 'Inactive';
				break;
			case '1' :
				$filter .= 'Active';
				break;
			case '2' :
				$filter .= 'Pending';
				break;
		endswitch;
	endif;
	$sql = "SELECT
		v.id AS venue_id,
		v.name,
		ct.description AS category_description,
		v.street,
		v.city,
		v.state,
		v.zip,
		CASE
			WHEN c.status = 0 THEN 'Inactive'
			WHEN c.status = 1 THEN 'Active'
			WHEN c.status = 2 THEN 'Pending'
		END AS venue_status,
		c.id AS contact_id,
		c.contact_type,
		c.first_name,
		c.last_name,
		c.title,
		c.office_phone,
		c.cell_phone,
		c.other_phone,
		c.preferred_phone,
		CASE
			WHEN c.accepts_texts = 0 THEN 'Yes'
			WHEN c.accepts_texts = 1 THEN 'No'
		END AS phone_accepts_texts,
		c.email,
		CASE
			WHEN c.status = 0 THEN 'Inactive'
			WHEN c.status = 1 THEN 'Active'
			WHEN c.status = 2 THEN 'Pending'
		END AS contact_status,
		c.update_date
		FROM venues v 
		INNER JOIN contacts c ON (v.id = c.venue_id)
		INNER JOIN categories ct ON (v.category_id = ct.id)
		$criteria
		ORDER BY v.name, v.id, c.first_name, c.last_name
	";
	//error_log($sql);
	$result = $db->query($sql) or die('Database Error!');
	$row_count = $result->rowCount();
	if ($row_count <= 0) :
		echo 'No data for report!';
		exit();
	endif;
	
	$rpt = get_report_class($_POST['output_format']);
	$rpt->set_report_title('Venue Contact Report');
	$rpt->report_start();
	
	$rpt->header_start();
	if (!empty($filter)) :
		$rpt->header_row_start();
		$rpt->header_cell_add('Filters: '.$filter, ['colspan'=>'21', 'class'=>'large']);
		$rpt->header_row_end();
	endif;
	$rpt->header_row_start();
	$rpt->header_cell_add('Venue ID', ['width'=>'70']);
	$rpt->header_cell_add('Name', ['width'=>'180']);
	$rpt->header_cell_add('Category', ['width'=>'90']);
	$rpt->header_cell_add('Street', ['width'=>'120']);
	$rpt->header_cell_add('City', ['width'=>'100']);
	$rpt->header_cell_add('State', ['width'=>'70']);
	$rpt->header_cell_add('Zip', ['width'=>'70']);
	$rpt->header_cell_add('Venue Status', ['width'=>'90']);
	$rpt->header_cell_add('Contact ID', ['width'=>'70']);
	$rpt->header_cell_add('Contact Type', ['width'=>'70']);
	$rpt->header_cell_add('Title', ['width'=>'120']);
	$rpt->header_cell_add('First Name', ['width'=>'120']);
	$rpt->header_cell_add('Last Name', ['width'=>'120']);
	$rpt->header_cell_add('Office Phone', ['width'=>'120']);
	$rpt->header_cell_add('Cell Phone', ['width'=>'120']);
	$rpt->header_cell_add('Other Phone', ['width'=>'120']);
	$rpt->header_cell_add('Preferred Phone', ['width'=>'70']);
	$rpt->header_cell_add('Text', ['width'=>'70']);
	$rpt->header_cell_add('Email', ['width'=>'180']);
	$rpt->header_cell_add('Contact Status', ['width'=>'70']);
	$rpt->header_cell_add('Last Updated', ['width'=>'90']);
	$rpt->header_row_end();
	$rpt->header_end();
	
	$rpt->data_start();
	$tot_count = 0;
	while($row = $result->fetch(PDO::FETCH_OBJ)) :
		$rpt->data_row_start();
		$rpt->data_cell_add($row->venue_id);
		$rpt->data_cell_add($row->name);
		$rpt->data_cell_add($row->category_description);
		$rpt->data_cell_add($row->street);
		$rpt->data_cell_add($row->city);
		$rpt->data_cell_add($row->state);
		$rpt->data_cell_add($row->zip);
		$rpt->data_cell_add($row->venue_status);
		$rpt->data_cell_add($row->contact_id);
		$rpt->data_cell_add($row->contact_type);
		$rpt->data_cell_add($row->first_name);
		$rpt->data_cell_add($row->last_name);
		$rpt->data_cell_add($row->title);
		$rpt->data_cell_add($row->office_phone);
		$rpt->data_cell_add($row->cell_phone);
		$rpt->data_cell_add($row->other_phone);
		$rpt->data_cell_add($row->preferred_phone);
		$rpt->data_cell_add($row->phone_accepts_texts);
		$rpt->data_cell_add($row->email);
		$rpt->data_cell_add($row->contact_status);
		$rpt->data_cell_add(nzdate_display($row->update_date));
		$tot_count += 1;
		$rpt->data_row_end();
	endwhile;
	$rpt->data_row_start();
	$rpt->data_cell_add('Totals', ['colspan'=>21, 'align'=>'center']);
	$rpt->data_row_end();
	$rpt->data_row_start();
	$rpt->data_cell_add('Total contacts', ['colspan'=>3, 'align'=>'right']);
	$rpt->data_cell_add(number_format($tot_count, 0), ['align'=>'right']);
	$rpt->data_cell_add('');
	$rpt->data_row_end();
	$rpt->data_end();
	
	$rpt->report_end();
	$rpt->output_report();

?>