<?php
# Here we are trying to list monthly and yearly activity

# Our boilerplate include file
include ('standardinclude.inc');

# here we send our POST variables off to be processed
#$company_id   = getpostvars('company_id', 11);       # left here an an example

###############################################################################################
#                                       print header                                          #
###############################################################################################

$title        = 'Reports';
include("$settingspath/header.inc");

###############################################################################################
#                          pull rows from transaction database                                #
###############################################################################################


# here we set up the fields we want to grab
$fields       = 'transaction_id, transaction_date, type_id, labor_hours, labor_rate, tax_rate';

$query        = "SELECT $fields FROM transaction WHERE type_id = 'IN' ORDER BY transaction_date";
$transactions = mysql_query($query, $link);

while ($row = mysql_fetch_array($transactions, MYSQL_ASSOC)) {
  # add up appropriate totals for each record
  # check each transaction for materials

  # what I want to do is look at the date of each transaction and parse by year and month
  # then crunch the numbers, adding them to arrays which I've created on-the-fly

  # here we have to map a few scalars from the array $row
  $transaction_id     = $row['transaction_id'];
  $transaction_date   = $row['transaction_date'];
  $type_id            = $row['type_id'];
  $labor_hours        = $row['labor_hours'];
  $labor_rate         = $row['labor_rate'];
  $tax_rate           = $row['tax_rate'];


  $date   = explode("-", $transaction_date);
  $year   = $date[0];
  # we can't have leading zeros in $month
  $thismonth  = $date[1];
  $month = $thismonth * 1;

  # here we have to add amounts to each month of each year, in arrays $receipts and $taxes, at least.
  # pad the year arrays with zeros, for each new year found
  # add to arrays with data from each transaction

  if ( !(array_key_exists($year, $receipts) )) {
    # NOTE: we're starting each of these 2-D arrays with 1, since there is no month "0"
    $receipts[$year]      = array(1 => 0,0,0,0,0,0,0,0,0,0,0,0);
    $taxes[$year]         = array(1 => 0,0,0,0,0,0,0,0,0,0,0,0);
    $taxexempt[$year]     = array(1 => 0,0,0,0,0,0,0,0,0,0,0,0);

  }

  $labortotal             = $labor_hours * $labor_rate;

###############################################################################################
#                                      materials used                                         #
###############################################################################################

  # initialize $subtotal
  $materialtotal = 0;

  # now we do our query and loop

  # here we set up the fields we want to grab
  $fields = 'material_id, transaction_id, quantity, unitprice';

  $materials = mysql_query("SELECT $fields FROM material WHERE transaction_id = $transaction_id");
  while ($row = mysql_fetch_array($materials, MYSQL_ASSOC)) {
    # handle the results of the select
    $extended       = $row["quantity"] * $row["unitprice"];
    $materialtotal  = $materialtotal + $extended;
  }

###############################################################################################
#                                     total per invoice                                       #
###############################################################################################

  $subtotal           = $materialtotal + $labortotal;
  $taxonmaterial      = ($materialtotal * $tax_rate) / 100;

  if ($is_labor_taxed == 1) {
    $taxonlabor       = ($labortotal * $tax_rate) / 100;
  } else {
    $taxonlabor       = 0;
    $exempt           = $labortotal;
  }

  $salestax           = $taxonmaterial + $taxonlabor;
  $grandtotal         = $subtotal + $salestax;

  # here we add the transaction to the 2-D arrays
  $receipts[$year][$month]  = $receipts[$year][$month] +$grandtotal;
  $taxes[$year][$month]     = $taxes[$year][$month] +$salestax;
  $taxexempt[$year][$month] = $taxexempt[$year][$month] +$exempt;
}




###############################################################################################
#                                      print tables                                           #
###############################################################################################

print "<h2>Monthly summaries by year</h2>\n";
# let's set up $monthnames hash
#$monthnames      = array(1 => 'January', 'February', 'March', 'April', 'May', 'June', 'July', 'August', 'September', 'October', 'November', 'December');
$monthnames      = array(1 => 'Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec');

# now we'll loop through the years
$years = array_keys($receipts);

foreach (array_reverse($years) as $yearskey => $year) {
  print "<table width=\"100%\" cols=13 border=1 cellspacing=0>\n";
  print "  <tr>\n";

  print "    <th align=\"left\"><big>$year</big></th>\n";
  for ($month = 1; $month <= 12; $month++) {
    print "    <th align=\"right\"><a href=\"reportbymonth.php?year=$year&month=$month\">$monthnames[$month]</a></th>\n";
  }
  print "  </tr>\n";
  print "  <tr>\n";

  print "    <td align=\"left\"><b>Receipts:<br>Taxes:<br>Exempt:</b></td>\n";
  for ($month = 1; $month <= 12; $month++) {
    $monthlyreceipts  = $receipts[$year][$month];
    $monthlytaxes     = $taxes[$year][$month];
    $monthlyexempt    = $taxexempt[$year][$month];
    print "    <td align=\"right\">";
    print number_format($monthlyreceipts,2) . "<br>";
    print number_format($monthlytaxes,2) . "<br>";
    print number_format($monthlyexempt,2);
    print "</td>\n";
    # now we total up each month for the yearly summary, below
    $yearly[$year]['totalreceipts']  = $yearly[$year]['totalreceipts'] + $monthlyreceipts;
    $yearly[$year]['totaltaxes']     = $yearly[$year]['totaltaxes'] + $monthlytaxes;
    $yearly[$year]['totalexempt']    = $yearly[$year]['totalexempt'] + $monthlyexempt;
  }

  print "  </tr>\n";
  print "</table>\n";
  print "<p>&nbsp;</p>\n";
}

print "<hr>";
print "<h2>Yearly summaries</h2>\n";
print "<table width=\"40%\" cols=4 border=1 cellspacing=0>\n";
print "  <tr>\n";
print "    <th align=\"left\">Year</td>\n";
print "    <th align=\"right\">Receipts</th>\n";
print "    <th align=\"right\">Taxes</th>\n";
print "    <th align=\"right\">Exempt</th>\n";
print "  </tr>\n";

foreach (array_reverse($years) as $yearskey => $year) {
  $totalreceipts  = $yearly[$year]['totalreceipts'];
  $totaltaxes     = $yearly[$year]['totaltaxes'];
  $totalexempt    = $yearly[$year]['totalexempt'];
  print "  <tr>\n";
  print "    <th align=\"left\">$year</th>\n";
  print "    <td align=\"right\">" . number_format($totalreceipts, 2) . "</td>\n";
  print "    <td align=\"right\">" . number_format($totaltaxes, 2) . "</td>\n";
  print "    <td align=\"right\">" . number_format($totalexempt, 2) . "</td>\n";
  print "  </tr>\n";
}

print "</table>\n";


###############################################################################################
#                                      print footer                                           #
###############################################################################################

include("$settingspath/plainfooter.inc");

?>
