<?php

if (isset($_POST['submit'])) {
  try {
    require "config.php";
    require "common.php";
    function toCurrency($number)
		{
 			return '$' . number_format($number,2);
		}

   	$db = new PDO($dsn, $username, $password, $options);

		$sql = "SELECT
					*
				FROM joblist
				WHERE customer = :customer";

		$sql_val = "SELECT a.customer,
  							SUM(a.value) AS tvalue
							FROM joblist AS a
						INNER JOIN joblist AS b
  						ON a.customer = b.customer
						GROUP BY a.customer";


		$sql_bal = "SELECT *
							FROM (SELECT SUM(balance) AS tbalance
										FROM joblist
										GROUP BY customer) AS total_balance
								WHERE tbalance = :tbalance";

//var_dump($sql_bal);


		$customer = $_POST['customer'];
    	$statement = $db->prepare($sql);
    	$statement->bindParam(':customer', $customer, PDO::PARAM_STR);
   	$statement->execute();
	 	$result = $statement->fetchAll();
// total SUM for value


		$stmt = $db->prepare($sql_val);
		$stmt->bindParam(':tvalue', $sum_value, PDO::PARAM_STR);
	 	$stmt->execute();
	 	$totalval = $stmt->fetchAll(\PDO::FETCH_ASSOC);

	 	$sumv = 	array(array_column($totalval, 'total_value'));
//	  	print_r($sumv);
      var_dump($sumv);

// total SUM for balance

		$stmt1 = $db->prepare($sql_bal);
		$stmt1->bindParam(':tbalance', $sum_balance, PDO::PARAM_INT);
	 	$stmt1->execute();
	 	$totalbal = $stmt1->fetchAll(\PDO::FETCH_ASSOC);
//   	$sumb = array_sum(array_column($totalbal, 'total_b'));
// var_dump($totalbal);

 } catch(PDOException $error) {
		echo $sql . "<br>" . $error->getMessage();
		die();
		}
}

?>

<?php
if (isset($_POST['submit'])) {
  if ($result && $statement->rowCount() > 0){?>


    <h2>Results</h2>

    <table>
     <thead>
<tr>
  <th>Work Order #</th>
  <th>Customer</th>
  <th>Description</th>
  <th>Value</th>
  <th>Balance</th>
  <th>Status</th>
  <th>Notes</th>
  </tr>
      </thead>
	<tbody>

<?php foreach ($result as $row){ ?>

      <tr>
<td><?php echo escape($row["work_order"]); ?></td>
<td><?php echo escape($row["customer"]); ?></td>
<td><?php echo escape($row["description"]); ?></td>
<td><?php echo toCurrency($row["value"]); ?></td>
<td><?php echo tocurrency($row["balance"]); ?></td>
<td><?php echo escape($row["status"]); ?></td>
<td><?php echo escape($row["notes"]); ?> </td>
      </tr>
   <?php } ?>
	</tbody>
<tfoot>

<tr>
	<th id="value_result" align="right" colspan="3">Totals :</th>
	<td><class="total-column" colspan="4"><?php echo toCurrency($row["value"]); ?></td>
	<td><class="total-column" colspan="5"><?php echo toCurrency($row["balance"]); ?></td>
</tr>
</tfoot>

</table>



  <?php } else { ?>
    > No results found for <?php echo escape($_POST['customer']); ?>.
  <?php }
} ?>

<?php require "templates/header.php";?>

<h2>Find Company/Customer Name</h2>

<form method="post">
  <label for="customer">Customer/Company Name</label>
  <input type="text" id="customer" name="customer">
  <input type="submit" name="submit" value="View Results">
</form>


<p><a href="welcome.php">Back to home</a></p>

<?php require "templates/footer.php"; ?>