Mahna Mahna (Do doo be-do-do)

Initially, I wanted to use views to generate the Work Order reports Ken requested. It seemed straight forward. I would use the date format vs. unix timestamps combined with the date browser. Ken would be able to move forward and back through months and years. This worked - but Ken wanted a report similar to the Excel spreadsheet he had Bob already creating. This spreadsheet contained a list of department groups with each group's total work orders and total hours for the month.

Since views are lists of nodes, creating a view for this would have been challenging - if it was even possible. At the time, the department groups were simply the possible values list in a select field. I soon found out that this meant that the list was actually just a massive single text field in the database, and that parsing it out into a list that meant something would be nearly impossible.

The first change I made was to make each group a term in a new vocabulary. This meant that each group had its own row in the taxonomy terms table. Then, I created a page using the PHP input format to display the report.

The page takes an argument through the URL that determines the month or year displayed. To get the argument from the URL, the first line of code is:

$arg = explode('/', $_GET['q']);

If there is an argument starting with '2' (for 2000) set, the code parses out the year and month using substr(). If no argument is set, no report is generated - merely a link to the report for the current month or year. (This is generated by calling date('Y-m') which defaults to the current date.) I created next and previous links by incrementing the month or year and appending the date to end of the page link.

$this_year = substr($arg[2],0,4);
$this_month = substr($arg[2],5,2);
$datestamp = strtotime("$this_year-$this_month-15");
$next_page = date('Y-m', strtotime("+1 month", $datestamp));
$prev_page = date('Y-m', strtotime("-1 month", $datestamp));
$prev_link = l('< Previous Month', 'node/15/'. $prev_page, NULL, NULL, NULL, FALSE, TRUE);
$next_link = l('Next Month >', 'node/15/'. $next_page, NULL, NULL, NULL, FALSE, TRUE);

I created two queries: one to retrieve the total number of work orders associated with each group for the specified period and one to get the sum of hours spent for each. First, I pulled all the taxonomy terms (client groups) into an array.

$terms = taxonomy_get_tree($vid);

Next, I iterate over the array, executing the two queries for each term as well as keeping a running total of all work orders and all hours. The date fields for work orders in this case are user-readable strings. I'm merely checking that a substring of the database contents matches the date passed through the URL

foreach ( $terms as $term ) {
print "";
$count = db_result(db_query("SELECT COUNT(nid) FROM {content_type_work_order}
WHERE nid IN (SELECT td.nid FROM {term_node} as td WHERE td.tid = %d) and
vid IN (SELECT MAX(vid) as vid FROM {content_type_work_order} GROUP BY nid) and
field_closed_date_value IS NOT NULL and
field_work_order_status_value = 'Closed' and
YEAR(substring(field_closed_date_value, 1, 10)) = $this_year and
MONTH(substring(field_closed_date_value, 1, 10)) = $this_month ", $term->tid));
$sum = db_result(db_query("SELECT SUM(field_hours_to_resolve_value)
FROM {content_type_work_order}
WHERE nid IN (SELECT td.nid FROM {term_node} as td WHERE td.tid = %d) and
vid IN (SELECT MAX(vid) as vid FROM {content_type_work_order} GROUP BY nid) and
field_closed_date_value IS NOT NULL and
field_work_order_status_value = 'Closed' and
YEAR(substring(field_closed_date_value, 1, 10)) = $this_year and
MONTH(substring(field_closed_date_value, 1, 10)) = $this_month ", $term->tid));
print "" . $term->name . "";
if ($count) {
print "" . $count . "";
$total_count += $count;
if($sum) {
print "" . $sum . "";
$total_hours += $sum;
}
}
else {
print "0";
print "0";
}
print "";
}