<?
require('lib.php');
require('tcpdf/tcpdf.php');


function print_label($pdf, $label_type, $baselineskip, $num, $namen, $straat, $huisnummer_toevoeging, $postcode, $plaats) {
	assert($num < 24);

	if ($label_type == 0) {
		$a4_p_marg_top = 12.9+2.5;
		$a4_p_marg_left = 7.25+2.5;
		$label_width = 66;
		$label_height = 33.9;
		$label_marg_top = 0;
		//$label_marg_bottom = 5;
		$label_marg_left = 0;
		$label_marg_right = 7.5;
	} else if ($label_type == 1) {
		$a4_p_marg_top = 5;
		$a4_p_marg_left = 8;
		$label_width = 70;
		$label_height = 37;
		$label_marg_top = 0;
		$label_marg_right = 16;
	} else assert(0);


	$row = (int)($num/3);
	$col = $num%3;
	$curr_x = $a4_p_marg_left + $label_width*$col;
	$curr_y = $a4_p_marg_top + $label_height*$row;

	if (preg_match('/^\d{1,6}$/', $huisnummer_toevoeging, $matches)) {
		$kix = $postcode.$matches[0];
	} else if (preg_match('/^(\d{1,6})([[:alpha:]])$/', $huisnummer_toevoeging, $matches)) {
		$kix = $postcode.$matches[1].'X'.$matches[2];
	} else if (preg_match('/^(\d{1,6})[ -]?(\w+)$/', $huisnummer_toevoeging, $matches)) {
		$kix = $postcode.$matches[1].'X'.substr($matches[2],0,6);
	}

	$line0 = $namen;
	$line1 = $straat.' '.$huisnummer_toevoeging;
	$line2 = $postcode.'  '.strtoupper($plaats);

	$pdf->SetXY($curr_x, $curr_y);
	$pdf->MultiCell($label_width - $label_marg_left - $label_marg_right, $baselineskip, $line0, 0, 'L', 0, 1, '', '', 1, 1, 0, 0, 2*$baselineskip, '', 1);
	$pdf->SetX($curr_x);
	//$pdf->SetXY($curr_x, $curr_y + $baselineskip);
	$pdf->Cell($label_width - $label_marg_left - $label_marg_right, $baselineskip, $line1, 0, 1, 'L',0, 0, 1);
	$pdf->SetX($curr_x);
	//$pdf->SetXY($curr_x, $curr_y + 2*$baselineskip);
	$pdf->Cell($label_width - $label_marg_left - $label_marg_right, $baselineskip, $line2, 0, 1, 'L',0, 0, 1);
	if ($kix) {
		$style = array(
		    'position' => '',
		    'border' => false,
			'hpadding' => false,
		    'vpadding' => 2,
		    'padding' => false,
		    'fgcolor' => array(0,0,0),
		    'bgcolor' => false, //array(255,255,255),
		);
		$pdf->write1DBarcode($kix, 'KIX', $curr_x, '', '', 9, 0.5, $style, 'T');

	}

}

header('Content-Type: text/plain');

if ($_GET['label_type'] == '0') {
	$label_type = 0;
} else if ($_GET['label_type'] == '1') {
	$label_type = 1;
} else {
	internal_error('label format '.$_GET['label_type'].' unsupported');
}

$query_string = 'FALSE';
if (!is_date($_GET['date'])) internal_error('no date set');
if ($_GET['start'] != (int)$_GET['start']) internal_error('start must be an integer');
if ($_GET['start'] < 0 || $_GET['start'] > 23) internal_error('start out of range');
foreach ($_GET['pid'] as $value) {
	if ($value != (int)$value) internal_error('address id must be integer');
	if ($value <= 0) internal_error('address id out of range');
	$query_string .= " OR ppl.ppl_id = $value";
}

//exit;

$date = mysqli_escape_data($_GET['date']);

$sub_query = <<<EOT
SELECT ppl.ppl_id, date_of_birth_nodes.date date_of_birth,
	f2p.priority f_priority, firstname_id,
	s2p.priority s_priority, surname_id,
	p2p.priority p_priority, phonenumber_id,
	e2p.priority e_priority, email_id,
	a2p.priority a_priority, address_id
FROM ppl 
LEFT JOIN datenodes AS date_of_birth_nodes ON date_of_birth = datenode_id
LEFT JOIN firstnames2ppl AS f2p ON ppl.ppl_id = f2p.ppl_id AND
	( f2p.established = 0 OR ( SELECT date FROM datenodes WHERE datenode_id = f2p.established ) <= '$date' ) AND
	( f2p.disestablished = 0 OR ( SELECT date FROM datenodes WHERE datenode_id = f2p.disestablished ) > '$date' )
LEFT JOIN surnames2ppl AS s2p ON ppl.ppl_id = s2p.ppl_id AND
	( s2p.established = 0 OR ( SELECT date FROM datenodes WHERE datenode_id = s2p.established ) <= '$date' ) AND
	( s2p.disestablished = 0 OR ( SELECT date FROM datenodes WHERE datenode_id = s2p.disestablished ) > '$date' )
LEFT JOIN phonenumbers2ppl AS p2p ON ppl.ppl_id = p2p.ppl_id AND
	( p2p.established = 0 OR ( SELECT date FROM datenodes WHERE datenode_id = p2p.established ) <= '$date' ) AND
	( p2p.disestablished = 0 OR ( SELECT date FROM datenodes WHERE datenode_id = p2p.disestablished ) > '$date' )
LEFT JOIN email2ppl AS e2p ON ppl.ppl_id = e2p.ppl_id AND
	( e2p.established = 0 OR ( SELECT date FROM datenodes WHERE datenode_id = e2p.established ) <= '$date' ) AND
	( e2p.disestablished = 0 OR ( SELECT date FROM datenodes WHERE datenode_id = e2p.disestablished ) > '$date' )
LEFT JOIN addresses2ppl AS a2p ON ppl.ppl_id = a2p.ppl_id AND
	( a2p.established = 0 OR ( SELECT date FROM datenodes WHERE datenode_id = a2p.established ) <= '$date' ) AND
	( a2p.disestablished = 0 OR ( SELECT date FROM datenodes WHERE datenode_id = a2p.disestablished ) > '$date' )
WHERE ( ( SELECT date FROM datenodes WHERE datenode_id = date_of_birth ) <= '$date' OR date_of_birth = 0 ) AND
	( date_of_death = 0 OR ( SELECT date FROM datenodes WHERE datenode_id = date_of_death ) > '$date' )
	AND ( $query_string )
ORDER BY f_priority DESC, a_priority DESC, s_priority DESC
EOT;

$query = <<<EOT
SELECT CONCAT(firstname, ' ', IF(prefix != '', CONCAT(prefix, ' '), ''), surname) name,
	street, number, zipcode, city
FROM ( $sub_query ) sub
LEFT JOIN addresses USING (address_id)
LEFT JOIN firstnames USING (firstname_id)
LEFT JOIN surnames USING (surname_id)
WHERE address_id IS NOT NULL
GROUP BY ppl_id
ORDER BY surname, firstname, prefix
EOT;
//LEFT JOIN email USING (email_id)
//LEFT JOIN phonenumbers USING (phonenumber_id)

//$outer_query = <<<EOT
//SELECT address_id, CONCAT(GROUP_CONCAT(firstname ORDER BY date_of_birth SEPARATOR ', '), ' ', surname) naam, street, number, zipcode, city  FROM ( $query ) sub0 GROUP BY address_id, surname_id
//EOT;

//$outer_outer_query = <<<EOT
//SELECT GROUP_CONCAT(naam SEPARATOR ', ') naam, street, number, zipcode, city FROM ( $outer_query ) sub1 GROUP BY address_id
//EOT;

$result = mysqli_query_safe($query);

//header('Content-Type: text/html');
//echo(sprint_table($result));
//exit;

$pdf = new TCPDF('P', 'mm');

$pdf->setPrintHeader(false);
$pdf->setPrintFooter(false);

$plaats = 'Utrecht';
$huisnummer_toevoeging = '11';
$straat = 'Ondiep-zuidzijde';
$namen = 'Rik Snel en Mandy de Ruiter PEOEPEOEP';
$postcode = '3551BX';

$mm_per_pt = 0.352777778;
//$a4_p_width = 210;
//$a4_p_height = 297;
//$a4_p_marg_top = 12.9+2.5;
//$a4_p_marg_bottom = 12.9;
//$a4_p_marg_left = 7.25+2.5;
//$a4_p_marg_right = 7.25;
$baselinefactor = 1.2;
$fontsize = 10*$mm_per_pt;

$pdf->SetCellPadding(0);
$pdf->SetMargins(0, 0, 0);
$pdf->SetAutoPageBreak(0);
$pdf->SetFont('Helvetica', '', $fontsize/$mm_per_pt);
$pdf->AddPage();
$labelno = $_GET['start'];

while (($row = mysqli_fetch_row($result))) {
	if ($labelno == 24) {
		$labelno = 0;
		$pdf->addPage();
	}
	print_label($pdf, $label_type, $baselinefactor*$fontsize, $labelno, $row[0], $row[1], $row[2], $row[3], $row[4]);
	$labelno++;
}
	
/* we'll send a .pdf file */
header("Content-type: application/pdf");

/* some red tape to avoid bugs and weird errormessages in IE */
header("Pragma: public");
header("Expires: 0");
header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
header("Cache-Control: public");

$pdf->Output();

/*
html_head('Family, Friends & Vague Aquiantances DB', <<<EOT
EOT
); ?>
<p>
<a href="newperson.php">invoeren</a>,
<a href="calendar.php">calendar</a>
</p>
<?  echo(sprint_table($result));

html_tail(); */
?>