<?php

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

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

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

		$sql_val = "SELECT customer,
					SUM(value) AS total_value
					FROM joblist
					AS `total_value`
					GROUP BY customer";
		$sql_bal = "SELECT customer,
					SUM(balance) AS total_balance
					FROM joblist
					AS `total_balance`
					GROUP By customer";

//var_dump($sql_bal);


		$customer = $_POST['customer'];
    	$statement = $connection->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(':total_value', $total_value, PDO::PARAM_STR);
	 	$stmt->execute();
	 	$totalv = $stmt->fetchAll(\PDO::FETCH_GROUP|\PDO::FETCH_ASSOC);

   	$sumv = array_sum(array_column($totalv, 'total_value'));

	 	var_dump($totalv);

// total SUM fro balance

		$stmt1 = $db1->prepare($sql_bal);
		$stmt1->bindParam(':total_balance', $total_balance, PDO::PARAM_INT);
	 	$stmt1->execute();
	 	$totalb = $stmt1->fetchAll(FETCH_ASSOC);
   	$sumb = array_sum(array_column($totalb, 'total_balance'));


  } 	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>
<P> </P>
<tfoot>

		<tr>
		<th id="value_result" align="right" colspan="3">Totals :</th>
		<td><class="total-column" colspan="4"><?php echo toCurrency($sumv); ?></td>
		<td><class="total-column" colspan="5"><?php echo toCurrency($sumb); ?></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"; ?>