<?php
# Here we select companies from the database

# this defines getpostvars()
include('getpostvars.inc');

# here we send our POST variables off to be processed
$sort_column		= getgetvars('sort_column', 15);

# let's set up our database connection
include ('database.inc');

# let's print our header
$title	= 'Choose Company';
include ("header.inc");
?>

<big><b>Please choose a company from the list:</b></big>

<table border="1" cellpadding="2">
  <tr>
    <th><a href="choosecompany.php?sort_column=company_name">Company Name </a></th>
    <th>Address  </th>
    <th><a href="choosecompany.php?sort_column=city">City  </a></th>
    <th><a href="choosecompany.php?sort_column=state_id">State  </a></th>
    <th colspan=3>Action?  </th>
  </tr>

  <?php
  # Here we want to get a count of invoices by company, so we can embed the "Review Invoices" button.
  # Let's grab all of the counts stick them in a hash: $invoice_count{'$company_id'} = $count.
  $count_query		= 'select company_id, count(*) from transaction group by company_id';
  $result			= mysql_query($count_query, $link);
  while ( $invoice_count	= mysql_fetch_array($result, MYSQL_ASSOC) ) {
    # Here we create an associative array (a perl "hash") containing invoice counts per company_id.
	# We could make repeated calls to the DB in the table loop, but that's really inefficient.
	$company_id		= $invoice_count["company_id"];
	$count			= $invoice_count["count(*)"];
	$count_array[$company_id] 	= $count;
  }

  #
  # here we set up our company query
  $build_query		= "SELECT company_id, company_name, address1, city, state_id, url FROM company";
  #
  if ( $sort_column == '') {
    $sort_column		= 'company_name';
  }
  $build_query		.= " ORDER BY $sort_column";
  #
  $result			= mysql_query($build_query, $link);

  # need to write our query and get the variables right
  while ( $company_row = mysql_fetch_array($result, MYSQL_ASSOC) ) {
    $company_id		= $company_row["company_id"];
	$company_name	= $company_row["company_name"];
    $address1		= $company_row["address1"];
    $city			= $company_row["city"];
	$state_id		= $company_row["state_id"];
	$url			= $company_row["url"];
	# retrieve the invoice counts
	$count			= $count_array[$company_id];

    echo "<tr valign=\"top\">";
	# if URL exists, print it as a link
    if ( !( $url == '' ) ) {
      echo "<td><a href=\"" . $url . "\">" . $company_name . "</a> &nbsp;</td>";
    } else {
      echo "<td>" . $company_name . " &nbsp;</td>";
    }
    echo "<td>$address1 &nbsp;</td>";
    echo "<td>$city &nbsp;</td>";
    echo "<td>$state_id &nbsp;</td>";
	echo "<td>
	  <form action=\"createinvoice.php\" method=\"POST\" enctype=\"text\">
      <input type=hidden name=\"company_id\" value=\"$company_id\">
      <input type=submit value=\"Create Invoice\">
      </form> </td>\n";
	if ($count > 0) {
	  echo "<td>
	    <form action=\"listbycompany.php\" method=\"POST\" enctype=\"text\">
        <input type=hidden name=\"company_id\" value=\"$company_id\">
        <input type=submit value=\"Review Invoices ($count)\">
        </form> </td>\n";
	} else {
	  echo "<td>no invoices found</td>\n";
	}
	echo "<td>
	  <form action=\"updatecompany.php\" method=\"POST\" enctype=\"text\">
      <input type=hidden name=\"company_id\" value=\"$company_id\">
      <input type=submit value=\"Edit Company\">
      </form> </td></tr>\n";
  }

  ?>
</table>

<p>&nbsp;</p>
<b>To-do:</b>
<br>
edit company? (button added, need to code <b>updatecompany.php</b>)<br>
create invoice? (done)<br>
review invoices (done)<br>
sort by columns? (done)<br>
what else?<br>

<?php
#print_r($_POST);
#print_r($_GET);
?>

