<?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);
		$db1 = new PDO($dsn, $username, $password, $options);
		$db2 = new PDO($dsn, $username, $password, $options);

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

		$sql_val = "SELECT customer,
							SUM(value) AS total_value
					FROM  joblist
					GROUP BY customer";

		$sql_bal = "SELECT customer,
						SUM(balance) AS total_balance
					FROM joblist
					GROUP BY customer";

//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 = $db1->prepare($sql_val);
//		$stmt->bindParam(':customer', $total_value, PDO::PARAM_INT);
	 	$stmt->execute();
	 	$totalv = $stmt->fetchAll(\PDO::FETCH_GROUP|\PDO::FETCH_ASSOC);
	 	$sumv = array_sum(array_column($totalv, 'total_value'));

 	   var_dump($totalv);


// total SUM for balance

		$stmt1 = $db2->prepare($sql_bal);
//		$stmt1->bindParam(':customer', $total_balance, PDO::PARAM_INT);
	 	$stmt1->execute();
	 	$totalb = $stmt1->fetchAll(\PDO::FETCH_GROUP|\PDO::FETCH_ASSOC);
   	$sumb = array_sum(array_column($totalb, 'total_balance'));
//var_dump($totalb);

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

}
?>

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


    <h2>Results</h2>

    <table>

<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>


			<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>


</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"; ?>