<?php

/* hours.php
 * 
 * dynamically forms and executes a search query for running hour-counts
 * based on a wide variety of parameters
 */

require_once 'includes/common.php';

Util::verifyLogin();


// load usernames
$usernames = Database::getLookupArray( 'uid', 'name', 'select uid, concat( name_first, " ", name_last ) as name from users order by name_last' );

// load clients
$clients = Database::getLookupArray( 'cid', 'client', 'select cid, client from clients order by client' );

// load jobs
$jobs = Database::getLookupTable( 'jid', 'select jid, job, client_id from jobs order by job' );


// generate query based on selected parameters
if( !empty( $_REQUEST ) )
{
	$summary = array();

	// column grouping data
	$group_cols = array( 'concat( name_first, " ", name_last ) as name', 'client', array( 'job', 'jid as ID' ), 'date' );
	$group_tables = array( 'users', array( 'clients', 'jobs' ), 'jobs', '' );
  $group_joins = array( 'user_id=uid', array( 'client_id=cid', 'job_id=jid' ), 'job_id=jid', '' );
	$group_bys = array( 'user_id', 'client_id', 'job_id', 'date' );
	
	// sorting values, and table dependancies
	$sort_bys = array( 'name_last', 'client', 'job', 'date desc' );
	$sort_deps = array( 'users', 'clients', 'jobs', 'entries' );
	
	// get groupby's, ensuring at least group by user
	//if( empty( $_GET['g'] ) ) $_GET['g'] = array( 0 );
	$groupings = $_GET['g'];

	// setup arrays
	$columns = array();
        $tables = array( 'entries' ); // it's all about the entries
        $conditions = array( 'job_id!=0' ); // ignore breaks
	$groups = array();

	// add grouping data
	if( !empty( $groupings ) )
		foreach( $groupings as $g )
		{
			insertElements( $columns, $group_cols[ $g ] );
      insertElements( $tables, $group_tables[ $g ] );
      insertElements( $conditions, $group_joins[ $g ] );
      insertElements( $groups, $group_bys[ $g ] );
		}

	// extra stuff
	$columns[] = 'sum(duration) as hours';
	
				Util::verifyDateRange();
        $start = $_GET['s'];
        $end = $_GET['e'];
        if( $start )
        {
                if( $end )
                {
                        $conditions[] = "date>='$start'";
                        $conditions[] = "date<='$end'";
			$summary['start'] = $start;
			$summary['end'] = $end;
                }
                else
		{
                        $conditions[] = "date='$start'";
			$summary['start'] = $start;
		}
        }

        if( !empty( $_REQUEST['u'] ) )
                $conditions[] = 'user_id in (' . implode( ',', $_REQUEST['u'] ) . ')';

	if( !empty( $_REQUEST['cid'] ) )
	{
		insertElements( $tables, 'jobs' );
		insertElements( $conditions, 'job_id=jid' );
		insertElements( $conditions, "client_id=$_REQUEST[cid]" );
		$summary['client'] = $clients[ $_REQUEST['cid'] ];
	}

        if( !empty( $_REQUEST['jid'] ) )
        {
                $conditions[] = "job_id=$_REQUEST[jid]";
		if( $jobs[ $_REQUEST['jid'] ] == $summary['client'] )
			unset( $summary['client'] );

                $summary['job'] = "#$_REQUEST[jid] - " . $jobs[ $_REQUEST['jid'] ];
        }

	
	// determine sort (make sure dependant table exists)
	if( !array_key_exists( 'sort', $_GET ) )
		$_GET['sort'] = 3;
	
	if( !array_key_exists( $sort_deps[ $_GET['sort'] ], $tables ) )
		$_GET['sort'] = 3;
		
	$sort = $sort_bys[ $_GET['sort'] ];

	// finalize arrays
	$columns = implode( ', ', $columns );
        $tables = implode( ', ', $tables );
        $conditions = implode( ' and ', $conditions );
	$groups = empty( $groups ) ? '' : 'group by ' . implode( ', ', $groups );

	// build query
	$query = "select $columns from $tables where $conditions $groups order by $sort";

	$qstart = microtime();
	$results = Database::getRowArray( $query );
	$qend = microtime();
	$qtime = array_sum(explode(" ",$qend)) - array_sum(explode(" ",$qstart));
	
	$total = 0;
	foreach( $results as $entry )
	{
		$total += $entry['hours'];
	}
}

// prepare a nice key-lookup of selected users for smarty
// also build list of names for summary
if( array_key_exists( 'u', $_REQUEST ) && !array_key_exists( 'au', $_REQUEST ) )
{
	$selected_users = array();
	$userlist = array();
	foreach( $_REQUEST['u'] as $uid )
	{
		$selected_users[ $uid ] = true;
		$userlist[] = $usernames[ $uid ];
	}
	$summary[ 'users' ] = $userlist;
}
else
	$selected_users = false;

// assign data to the template
Template::assign( 'summary', $summary );	
Template::assign( 'usernames', $usernames );
Template::assign( 'selected_users', $selected_users );
Template::assign( 'clients', $clients );
Template::assign( 'jobs', $jobs );
Template::assign( 'results', $results );
Template::assign( 'total', $total );
Template::assign( 'query_time', $qtime );
Template::assign( 'query', $query );
Template::addStyleSheet( 'hours.css' );
Template::addJavascript( 'reports.js' );
Template::addStyleSheet( 'calendar.css' );
Template::addJavascript( 'calendar.js' );
Template::display( 'hours.tpl', 'Hour Counts' );


// inserts non-empty element(s) into a uniquely indexed array
// $insert may be a single value or an array
function insertElements( &$arr, $insert )
{
	if( !empty( $insert ) )
	{
		if( is_array( $insert ) )
			foreach( $insert as $val )
				$arr[ $val ] = $val;
		else
			$arr[ $insert ] = $insert;
	}
}

?>