public static function get_reports_list_by_cat($category_ids, $approved_text, $where_text, $logical_operator, $order_by = "incident.incident_date", $order_by_direction = "asc", $joins = array(), $custom_category_to_table_mapping = array())
{
$incidents = null;
//check if we're showing all categories, or if no category info was selected then return everything
if (count($category_ids) == 0 || $category_ids[0] == '0') {
// Retrieve all markers
$incidents = ORM::factory('incident')->select('incident.*, category.category_color as color, category.category_title as category_title, category.id as cat_id, ' . 'parent_cat.category_title as parent_title, parent_cat.category_color as parent_color, parent_cat.id as parent_id, ' . '(0=1) AS is_parent')->with('location')->join('incident_category', 'incident.id', 'incident_category.incident_id', 'LEFT')->join('media', 'incident.id', 'media.incident_id', 'LEFT')->join('category', 'incident_category.category_id', 'category.id', 'LEFT')->join('category as parent_cat', 'category.parent_id', 'parent_cat.id', 'LEFT');
//run code to add in extra joins
foreach ($joins as $join) {
if (count($join) < 4) {
$incidents = $incidents->join($join[0], $join[1], $join[2]);
} else {
$incidents = $incidents->join($join[0], $join[1], $join[2], $join[3]);
}
}
$incidents = $incidents->where($approved_text . $where_text)->orderby($order_by, $order_by_direction)->find_all();
} else {
//there are category filters to be concerned with
// or up all the categories we're interested in
// OR up all the categories we're interested in
$where_category = reports::or_up_categories($category_ids, $custom_category_to_table_mapping);
$test_text = reports::create_test_for_match($category_ids, $custom_category_to_table_mapping);
$custom_cat_selects = reports::create_custom_category_selects($category_ids, $custom_category_to_table_mapping);
//if we're using OR
if ($logical_operator == "or") {
$incidents = ORM::factory('incident')->select('incident.*, category.category_color as color, category.category_title as category_title, category.id as cat_id, ' . 'parent_cat.category_title as parent_title, parent_cat.category_color as parent_color, parent_cat.id as parent_id' . $test_text . ' ' . $custom_cat_selects)->with('location')->join('incident_category', 'incident.id', 'incident_category.incident_id', 'RIGHT')->join('media', 'incident.id', 'media.incident_id', 'LEFT')->join('category', 'incident_category.category_id', 'category.id', 'LEFT')->join('category as parent_cat', 'category.parent_id', 'parent_cat.id', 'LEFT');
//run code to add in extra joins
foreach ($joins as $join) {
if (count($join) < 4) {
$incidents = $incidents->join($join[0], $join[1], $join[2]);
} else {
$incidents = $incidents->join($join[0], $join[1], $join[2], $join[3]);
}
}
$incidents = $incidents->where($approved_text . ' AND (' . $where_category . ')' . $where_text)->orderby($order_by, $order_by_direction)->orderby('incident.id')->find_all();
} else {
// Retrieve incidents by category
$incidents = ORM::factory('incident')->select('incident.*, category.category_color as color, category.category_title as category_title, category.id as cat_id, ' . 'parent_cat.category_title as parent_title, parent_cat.category_color as parent_color, parent_cat.id as parent_id' . $test_text . ' ' . $custom_cat_selects)->with('location')->join('incident_category', 'incident.id', 'incident_category.incident_id', 'LEFT')->join('category', 'incident_category.category_id', 'category.id')->join('media', 'incident.id', 'media.incident_id', 'LEFT')->join('category as parent_cat', 'category.parent_id', 'parent_cat.id', 'LEFT');
//run code to add in extra joins
foreach ($joins as $join) {
if (count($join) < 4) {
$incidents = $incidents->join($join[0], $join[1], $join[2]);
} else {
$incidents = $incidents->join($join[0], $join[1], $join[2], $join[3]);
}
}
$incidents = $incidents->where($approved_text . ' AND (' . $where_category . ')' . $where_text)->orderby($order_by, $order_by_direction)->orderby('incident.id')->find_all();
$incidents = self::post_process_and($category_ids, $incidents, $custom_category_to_table_mapping);
}
//end of else AND
}
//end of else we are using categories
return $incidents;
}
