<?php
// connect to database
	db_connect();

// authorization
	switch ($_SESSION['valid_user']) {
		case 'admin':
		case 'mwaiver':
			$and = '';
			break;
		default:
			if (isset($_GET['type']) && $_GET['type'] == 'supervisor_billing_record' && is_supervisor($client)) {
				$u = $_SESSION['valid_user'];
				$and = "and P.supervisor like '%$u%'";
			}
			else {
				$and = "and R.uname = '".$_SESSION['valid_user']."'";
			}
	}

// setup mysql select statement
	switch ($allunpaid) {
		case 'notsubmitted':
			$condition = "and (U.date <= '$end' and U.paid is null)";
			break;
		case 'submitted':
			$condition = "and (U.date <= '$end' and U.paid = '0000-00-00')";
			break;
		default:
			$condition = "and U.date between '$start' and '$end'";
	}
	
//loop through clients

$result = mysql_query("select T.parent as parent, T.type as type, C.cID as cID, C.client as client from CONTRACTS as C left join RATES as R on C.cID = R.cID, TYPES as T where C.ctype = T.type and T.type != 'fsu' and C.view = 'true' group by C.cID order by client");

			$num_rows = mysql_num_rows($result);
			$clients = array();
			for ($i = 0; $i < $num_rows; $i++) {
				$clients[] = mysql_fetch_array($result);
			}
			
foreach ($clients as $client_array) {
		$client = $client_array['cID'];
		$clientName = $client_array['client'];		
	
	

// get the consultants that bill this contract if it has not already been set
	if (!isset($consultants)) {
		$cons_query = mysql_query("select
											R.uname as uname,
											CONCAT(C.lname, ', ', C.fname) as name,
											P.client as client
										from
											RATES as R,
											CONSULTANTS as C,
											CONTRACTS as P
										where R.uname = C.uname
											and R.cID = P.cID
											and R.cID = $client
											$and
										group by uname
										order by C.lname, C.fname
										");
		$num_cons = mysql_num_rows($cons_query);
		$consultants = array();
		for ($i=0; $i < $num_cons; $i++) {
			$consultants[] = mysql_fetch_array($cons_query);
		}
	}

// create a record for each consultant
	foreach ($consultants as $cons) {		
		$u = $cons['uname'];
		$contractor = $cons['client'];
		
		$result = mysql_query("select CONCAT(lname, ', ', fname) as name from CONSULTANTS where uname = '$u' limit 1");
		$name = mysql_fetch_array($result);
		$consultant = $name['name'];
		
		$result = mysql_query("select
											date_format(U.date, '%Y%m%d') as `key`,
											
											date_format(U.date, '%m/%d') as date,
											
											time_format(U.start, '%l:%i') as start,
											time_format(U.end, '%l:%i') as end,
											IF(R.type = 'day',
												U.units,
												(time_to_sec(U.end) - time_to_sec(U.start)) / 60 / 60
											) as units,
											U.location as location,
											U.description as description
										from
											$u as U,
											RATES as R
										where U.rID = R.rID
											and R.cID = $client
											$condition
										order by U.date, U.start
										");
		$num_results = ($result) ? mysql_num_rows($result) : 0;
		
		$location = mysql_query("select
											U.location as location,
											IF(R.type = 'day',
												sum(U.units),
												sum((time_to_sec(U.end) - time_to_sec(U.start)) / 60 / 60)
											) as units
										from
											$u as U,
											RATES as R
										where U.rID = R.rID
											and R.cID = $client
											$condition
										group by U.location, R.type
										order by U.location
										");
		$num_locations = ($location) ? mysql_num_rows($location) : 0;
		
		// mileage expenses
			$e = $u.'_exp';
			
			$expenses = array();
			$total_miles = 0;
			$exp_result = mysql_query("select
												date_format(U.date, '%Y%m%d') as date,
												U.amount as miles
											from
												$e as U,
												RATES as R
											where U.rID = R.rID
												and R.cID = $client
												and U.description = 'Mileage'
												$condition
											order by U.date
											");
			$num_expenses = ($exp_result) ? mysql_num_rows($exp_result) : 0;
			for ($i=0; $i < $num_expenses; $i++) {
				$row = mysql_fetch_array($exp_result);
				if (isset($expenses[$row['date']])) {
					$expenses[$row['date']] += $row['miles'];
				} else {
					$expenses[$row['date']] = $row['miles'];
				}
				$total_miles += $row['miles'];
			}
		
		if ($num_results > 0) {
		
		// xhtml
			?>
			<div class="record">
				<h1>Behavior Management Consultants, Inc.</h1>
				<h2>Billing Record</h2>
				<div class="consultant"><strong>Consultant:</strong> <?php echo $consultant; ?> </div>
				<div class="contractor"><strong>Contractor:</strong> <?php echo $clientName; ?></div>
				<div class="date"><strong>Period:</strong> <?php echo date('m/d/Y', strtotime($start)); ?> to <?php echo date('m/d/Y', strtotime($end)); ?></div>
				<?php if ($num_expenses) { ?>
				<div class="miles"><strong>Total Miles:</strong> <?php echo $total_miles; ?></div>
				<?php } ?>
				<table class="locations">
					<tr>
						<th class="text">Location</th>
						<th>Units</th>
					</tr>
					<?php
					$total = 0;
					for ($i=0; $i < $num_locations; $i++) {
						$row = mysql_fetch_array($location);
						$total += $row['units'];
						echo '<tr>';
							echo '<td class="text">'.$row['location'].'</td>';
							echo '<td>'.number_format($row['units'], 2, '.', ',').'</td>';
						echo '</tr>';
					}
					echo '<tr class="totals">';
						echo '<td class="text">Total</td>';
						echo '<td>'.number_format($total, 2, '.', ',').'</td>';
					echo '</tr>';
					?>
				</table>
				<table>
					<tr>
						<th>Date</th>
						<th>Start</th>
						<th>End</th>
						<th>Units</th>
						<th>Location</th>
					<?php
						if ($num_expenses) {
							echo '<th>Miles</th>';
						}
					?>
						<th>Description</th>
					</tr>
					<?php
					for ($j=0; $j < $num_results; $j++) {
						$row = mysql_fetch_array($result);
						echo '<tr>';
							echo '<td>'.$row['date'].'</td>';
							echo '<td>'.$row['start'].'</td>';
							echo '<td>'.$row['end'].'</td>';
							echo '<td>'.number_format($row['units'], 2, '.', ',').'</td>';
							echo '<td class="text">'.$row['location'].'</td>';
							
							if ($num_expenses) {
								if (isset($expenses[$row['key']])) {
									echo '<td>'.$expenses[$row['key']].'</td>';
									unset($expenses[$row['key']]);
								} else {
									echo '<td></td>';
								}
							}
							
							echo '<td class="text">'.stripslashes($row['description']).'</td>';
						echo '</tr>';
					}
					?>
				</table>
			</div>
		<?php
		}
	}
}