/application/controllers/reports.php

<?php

class Reports_Controller extends Base_Controller {
    public $restful = true;

    public function __construct()
    {
        $this->filter('before', 'logged_in');
        $this->filter('before', 'read_only_admin_only')
            ->only(array(
                'available_items'
            ));
        $this->filter('before', 'admin_only')
            ->except(array(
                'available_items'
            ));
    }

    public function get_past_due_items()
    {
        if (!is_null(Item::past_due())) {
            $items = Item::past_due()->get();
        } else {
            $items = array();
        }

        return View::make('reports/_checked_out_items')
            ->with('title', 'Past Due Items')
            ->with('items', $items);
    }

    public function get_items_checked_out_today()
    {
        if (!is_null(Item::checked_out_today())) {
            $items = Item::checked_out_today()->get();
        } else {
            $items = array();
        }

        return View::make('reports/_checked_out_items')
            ->with('title', 'Items Checked Out Today')
            ->with('items', $items);
    }

    public function get_items_due_today()
    {
        if (!is_null(Item::due_today())) {
            $items = Item::due_today()->get();
        } else {
            $items = array();
        }

        return View::make('reports/_checked_out_items')
            ->with('title', 'Items Due Today')
            ->with('items', $items);
    }

    public function get_items_due_this_week()
    {
        if(!is_null(Item::due_this_week())) {
            $items = Item::due_this_week()->get();
        } else {
            $items = array();
        }

        return View::make('reports/_checked_out_items')
            ->with('title', 'Items Due This Week')
            ->with('items', $items);
    }

    public function get_current_holds()
    {
        $holds = Hold::current()
            ->order_by('end_date')
            ->get();

        return View::make('reports/current_holds')
            ->with('holds', $holds);
    }

    public function get_all_checkouts()
    {
        $transactions = CheckedOutTransaction::order_by('checked_out_date')
            ->order_by('renewal_due_date')
            ->order_by('due_date')
            ->get();

        return View::make('reports/_check_outs')
            ->with('title', 'All Checkouts')
            ->with('transactions', $transactions);
    }

    public function get_checkout_history()
    {
        $start_date = date('Y/m/d', strtotime('- 30 days'));
        $end_date = date('Y/m/d');

        return View::make('reports/checkout_history')
            ->with('start_date', $start_date)
            ->with('end_date', $end_date);
    }

    public function post_checkout_history()
    {
        $input = Input::get();

        $start_date = date('Y-m-d', strtotime($input['start_date']));
        $end_date = date('Y-m-d', strtotime($input['end_date']));

        $transactions = CheckedOutTransaction::order_by('checked_out_date')
            ->order_by('renewal_due_date')
            ->order_by('due_date')
            ->where('checked_out_date', '>=', $start_date)
            ->where('checked_out_date', '<=', $end_date)
            ->get();

        return View::make('reports/_check_outs')
            ->with('title', 'Checkouts between ' . $start_date . ' and ' . $end_date)
            ->with('transactions', $transactions);
    }

    public function get_items_checked_out_by_faculty()
    {
        $currently_checked_out = CheckedOutTransaction::still_checked_out()->get();

        $faculty_ids = [];
        // Only autocomplete for faculty that have items checked out.
        foreach($currently_checked_out as $c)
        {
            $f = $c->checked_out_to()->first();
            $faculty_ids[] = "&quot;" . $f->faculty_id . "&quot;";
        }

        $faculty_ids = array_unique($faculty_ids);

        if (count($faculty_ids) == 0) {
            Session::flash('success_message', 'There are currently no faculty with checked out items.');
        }

        return View::make('reports/items_checked_out_by_faculty')->with('faculty_ids', $faculty_ids);
    }

    public function post_items_checked_out_by_faculty()
    {
        $rules = array(
            'faculty_id' => 'required|size:9|match:/^E.{8}$/|exists:faculty,faculty_id'
        );

        $input = Input::get();
        $validation = Validator::make($input, $rules);

        if($validation->fails()) {
            return Redirect::to_action('reports/items_checked_out_by_faculty')->with_input()->with_errors($validation);
        }

        $faculty = Faculty::where_faculty_id($input['faculty_id'])->first();
        $transactions = CheckedOutTransaction::still_checked_out()->where_checked_out_to($faculty->id)->order_by('due_date', 'asc')->get();

        if(count($transactions) == 0) {
            return Redirect::to_action('reports/items_checked_out_by_faculty')->with_input()->with('error_message', 'The selected faculty member currently has no checked out items.');
        }

        $items = [];
        foreach ($transactions as $t) {
            $dd = $t->renewal_due_date ? $t->renewal_due_date : $t->due_date;
            $dd = date('l F j, Y', strtotime($dd)) . ' at ' . date('h:ia', strtotime($dd));
            $items[] = array(
                            'item' => $t->item_checked_out()->first(),
                            'checked_out_to' => $t->checked_out_to()->first(),
                            'due_date' => $dd
                            );
        }

        return View::make('reports/post_items_checked_out_by_faculty')->with('items', $items);
    }

    public function get_items_checked_out_per_day()
    {
        $counts = DB::query('SELECT Count(*) count, date_format(checked_out_date, \'%m-%d-%Y\') date FROM `checked_out_transactions` where date(checked_out_date) between curdate() - interval 30 day and curdate() group by date_format(checked_out_date, \'%m-%d-%Y\')');

        $values = [];

        /* Get the start and end date in the appropriate
         * format. This allows the inputs to display the
         * correct information.
         */
        if($counts) {
            $start_date = $counts[0]->date;
            $start_date = explode('-', $start_date);
            $start_date = $start_date[2] . '/' . $start_date[0] . '/' . $start_date[1];

            $end_date = $counts[count($counts) - 1]->date;
            $end_date = explode('-', $end_date);
            $end_date = $end_date[2] . '/' . $end_date[0] . '/' . $end_date[1];

            /* Store the counts and dates in a format
             * that nvd3 can use for graphing.
             */
            foreach($counts as $c)
            {
                $values[] = array('x' => $c->date , 'y' => $c->count);
            }
        } else {
            $start_date = date('Y-m-d');
            $end_date = date('Y-m-d');
        }

        $values = json_encode($values);

        return View::make('reports/items_checked_out_per_day')
            ->with('start_date', $start_date)
            ->with('end_date', $end_date)
            ->with('values', $values);
    }

    /*
     * This function is used for the AJAX request sent
     * by the HTTP GET version of this function. It returns
     * JSON with the values of the item counts between the
     * range of dates received in the request. The
     * graph is updated with this data
     */
    public function post_items_checked_out_per_day()
    {
        $input = Input::get();
        $start_date = date('Y-m-d', strtotime($input['start_date']));
        $end_date = date('Y-m-d', strtotime($input['end_date']));

        $counts = DB::query('SELECT Count(*) count, date_format(checked_out_date, \'%m-%d-%Y\') date FROM `checked_out_transactions` where date(checked_out_date) between ? and ? group by date_format(checked_out_date, \'%m-%d-%Y\')', array($start_date, $end_date));

        $values = [];
        foreach($counts as $c)
        {
            $values[] = array('x' => $c->date , 'y' => $c->count);
        }

        $values = json_encode($values);

        return $values;
    }

    public function get_number_of_items()
    {
        $query_items_by_category = <<<'EOQ'
SELECT count(*) number_of_items, c.name FROM `items` i
 join item_categories c
    on i.category = c.id
 group by c.name
EOQ;
        
        $query_items_by_status = <<<'EOQ'
SELECT count(*) number_of_items, status FROM `items`
  group by status
EOQ;

        $items_by_category = DB::query($query_items_by_category);
        $items_by_status = DB::query($query_items_by_status);

        $ibc_values = [];
        foreach($items_by_category as $ibc)
        {
            $ibc_values[] = array('key' => $ibc->name, 'y' => $ibc->number_of_items);
        }

        $ibs_values = [];
        foreach($items_by_status as $ibs)
        {
            $ibs_values[] = array('key' => $ibs->status, 'y' => $ibs->number_of_items);
        }

        $ibc_values = json_encode($ibc_values);
        $ibs_values = json_encode($ibs_values);

        return View::make('reports/number_of_items')
            ->with('ibc_values', $ibc_values)
            ->with('ibs_values', $ibs_values);
    }

    public function get_checked_out_items() {
        // Can't use Item::all_checked_out() here because
        // the ordering is not respected even if the order_by
        // is placed in the Item::all_checked_out() function
        // itself.
        $transactions = CheckedOutTransaction::still_checked_out()
            ->order_by('renewal_due_date')
            ->order_by('due_date')
            ->get();

        $items = [];

        foreach($transactions as $t) {
            $items[] = $t->item_checked_out()->first();
        }

        return View::make('reports/_checked_out_items')
            ->with('title', 'Items Currently Checked Out')
            ->with('items', $items);
    }

    public function get_available_items() {
        $items = Item::where_availability(true)->where('status', '=', 'good')->get();
        return View::make('reports/available_items')->with('items', $items);
    }

    public function get_damaged_lost_or_stolen() {
        $items = Item::where_status('damaged')
            ->or_where('status', '=', 'lost')
            ->or_where('status', '=', 'stolen')
            ->get();

        return View::make('reports/damaged_lost_or_stolen')
            ->with('items', $items);
    }

    public function get_surplus() {
        $items = Item::where_status('surplus')
            ->get();

        return View::make('reports/surplus')
            ->with('items', $items);
    }
}