Ejemplo n.º 1
0
 public static function json_timeline($controller, $category_ids, $on_the_back_end = true, $extra_where_text = "", $joins = array(), $custom_category_to_table_mapping = array())
 {
     $category_ids = explode(",", $category_ids, -1);
     //get rid of that trailing ","
     //a little flag to alert us to the presence of the "ALL CATEGORIES" category
     $is_all_categories = false;
     if (count($category_ids) == 0 || $category_ids[0] == '0') {
         $is_all_categories = true;
     }
     $controller->auto_render = FALSE;
     $db = new Database();
     $show_unapproved = "3";
     //1 show only approved, 2 show only unapproved, 3 show all
     $approved_text = " (1=1) ";
     if ($on_the_back_end) {
         //figure out if we're showing unapproved stuff or what.
         if (isset($_GET['u']) and !empty($_GET['u'])) {
             $show_unapproved = (int) $_GET['u'];
         }
         $approved_text = "";
         if ($show_unapproved == 1) {
             $approved_text = "incident.incident_active = 1 ";
         } else {
             if ($show_unapproved == 2) {
                 $approved_text = "incident.incident_active = 0 ";
             } else {
                 if ($show_unapproved == 3) {
                     $approved_text = " (incident.incident_active = 0 OR incident.incident_active = 1) ";
                 }
             }
         }
     } else {
         $approved_text = "incident.incident_active = 1 ";
     }
     $logical_operator = "or";
     if (isset($_GET['lo']) and !empty($_GET['lo'])) {
         $logical_operator = $_GET['lo'];
     }
     $interval = (isset($_GET["i"]) and !empty($_GET["i"])) ? $_GET["i"] : "month";
     // Get the Counts
     $select_date_text = "DATE_FORMAT(incident_date, '%Y-%m-01')";
     $groupby_date_text = "DATE_FORMAT(incident_date, '%Y%m')";
     if ($interval == 'day') {
         $select_date_text = "DATE_FORMAT(incident_date, '%Y-%m-%d')";
         $groupby_date_text = "DATE_FORMAT(incident_date, '%Y%m%d')";
     } elseif ($interval == 'hour') {
         $select_date_text = "DATE_FORMAT(incident_date, '%Y-%m-%d %H:%M')";
         $groupby_date_text = "DATE_FORMAT(incident_date, '%Y%m%d%H')";
     } elseif ($interval == 'week') {
         $select_date_text = "STR_TO_DATE(CONCAT(CAST(YEARWEEK(incident_date) AS CHAR), ' Sunday'), '%X%V %W')";
         $groupby_date_text = "YEARWEEK(incident_date)";
     }
     $graph_data = array();
     $graph_data[0] = array();
     $graph_data[0]['label'] = "Category Title";
     //is this used for anything?
     $graph_data[0]['color'] = '#' . self::merge_colors($category_ids, $custom_category_to_table_mapping);
     $graph_data[0]['data'] = array();
     $incidents = adminmap_reports::get_reports($category_ids, $approved_text, " " . $extra_where_text, $logical_operator, "incident.incident_date", "asc", -1, -1, $joins, $custom_category_to_table_mapping);
     $approved_IDs_str = "('-1')";
     if (count($incidents) > 0) {
         $i = 0;
         $approved_IDs_str = "(";
         foreach ($incidents as $incident) {
             $i++;
             $approved_IDs_str = $i > 1 ? $approved_IDs_str . ', ' : $approved_IDs_str;
             $approved_IDs_str = $approved_IDs_str . "'" . $incident->id . "'";
         }
         $approved_IDs_str = $approved_IDs_str . ") ";
     }
     $query = 'SELECT UNIX_TIMESTAMP(' . $select_date_text . ') AS time, COUNT(id) AS number FROM ' . adminmap_helper::$table_prefix . 'incident WHERE incident.id in' . $approved_IDs_str . ' GROUP BY ' . $groupby_date_text;
     $query = $db->query($query);
     foreach ($query as $items) {
         array_push($graph_data[0]['data'], array($items->time * 1000, $items->number));
     }
     echo json_encode($graph_data);
 }
Ejemplo n.º 2
0
 public static function get_reports_count($category_ids, $approved_text, $where_text, $logical_operator, $joins = array(), $custom_category_to_table_mapping = array())
 {
     $incidents_count = -1;
     //run a filter just in case someone wants to mess with this:
     $data = array("was_changed_by_plugin" => false, "category_ids" => $category_ids, "approved_text" => $approved_text, "where_text" => $where_text, "logical_operator" => $logical_operator, "incidents_count" => $incidents_count, "custom_category_to_table_mapping" => $custom_category_to_table_mapping);
     Event::run('ushahidi_filter.admin_map_get_reports_count', $data);
     //check if someone has changed this and see what we get
     //in case the filter changed the data, make sure it gets passed in
     if ($data["was_changed_by_plugin"]) {
         return $incidents_count = $data["incidents_count"];
     }
     //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('DISTINCT incident.*')->with('location');
         //->join('media', 'incident.id', 'media.incident_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]);
             }
         }
         //I hate finding the count this way because it forces you to download all
         //the incidents and not just a number, but if i use count_all() it sometimes gives
         //erroneous numbers, but doing it this way seems to work. I imagine
         //it has to do with the way count and distinct work together.
         $incidents_found = $incidents->where($approved_text . $where_text)->find_all();
         $incidents_count = count($incidents_found);
     } else {
         // OR up all the categories we're interested in
         $where_category = adminmap_reports::or_up_categories($category_ids, $custom_category_to_table_mapping);
         //if we're using OR
         if ($logical_operator == "or") {
             $incidents = ORM::factory('incident')->select('DISTINCT incident.*, COUNT(DISTINCT ' . Kohana::config('database.default.table_prefix') . 'incident.id) as incidents_found')->with('location')->join('incident_category', 'incident.id', 'incident_category.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)->find();
             $incidents_count = $incidents->incidents_found;
         } else {
             //based on what's in the custom cat mappings make some fancy selects
             $custom_cat_selects = adminmap_reports::create_custom_category_selects($category_ids, $custom_category_to_table_mapping);
             // 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' . $custom_cat_selects)->with('location')->join('incident_category', 'incident.id', 'incident_category.incident_id', 'LEFT')->join('category', 'incident_category.category_id', 'category.id')->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)->find_all();
             $incidents = self::post_process_and($category_ids, $incidents);
             $incidents_count = count($incidents);
         }
     }
     //end else we are using category IDs
     return $incidents_count;
 }
Ejemplo n.º 3
0
 /**
  * Lists the reports.
  * @param int $page
  */
 function index($page = 1, $group_id = false)
 {
     // Cacheable Controller
     $this->is_cachable = TRUE;
     $this->template->header->this_page = 'reports';
     $this->template->content = new View('reports');
     $this->themes->js = new View('reports_js');
     // Get locale
     $l = Kohana::config('locale.language.0');
     $db = new Database();
     if (!empty($_GET['status'])) {
         $status = strtolower($_GET['status']);
         if ($status == 'a') {
             $filter = 'incident.incident_active = 0';
         } elseif ($status == 'v') {
             $filter = 'incident.incident_verified = 0';
         } elseif ($status == 't') {
             $filter = 'incident.incident_verified = 1';
         } elseif ($status == 'f') {
             $filter = 'incident.incident_assigned = 1';
         } elseif ($status == 'd') {
             $filter = 'incident.incident_disputed = 0';
         } else {
             $status = "0";
             $filter = '1=1';
         }
     } else {
         $status = "0";
         $filter = "1=1";
     }
     // check, has the form been submitted?
     $form_error = FALSE;
     $form_saved = FALSE;
     $form_action = "";
     $db = new Database();
     // Category ID
     $category_ids = array();
     if (isset($_GET['c']) and !empty($_GET['c'])) {
         $category_ids = explode(",", $_GET['c']);
         //get rid of that trailing ","
     } else {
         $category_ids = array("0");
     }
     // logical operator
     $logical_operator = "or";
     if (isset($_GET['lo']) and !empty($_GET['lo'])) {
         $logical_operator = $_GET['lo'];
     }
     $approved_text = " incident.incident_active = 1 ";
     $location_where = "";
     // Break apart location variables, if necessary
     $southwest = array();
     if (isset($_GET['sw'])) {
         $southwest = explode(",", $_GET['sw']);
     }
     $northeast = array();
     if (isset($_GET['ne'])) {
         $northeast = explode(",", $_GET['ne']);
     }
     if (count($southwest) == 2 and count($northeast) == 2) {
         $lon_min = (double) $southwest[0];
         $lon_max = (double) $northeast[0];
         $lat_min = (double) $southwest[1];
         $lat_max = (double) $northeast[1];
         $location_where = ' AND (location.latitude >=' . $lat_min . ' AND location.latitude <=' . $lat_max . ' AND location.longitude >=' . $lon_min . ' AND location.longitude <=' . $lon_max . ') ';
     }
     $group_where = " AND ( " . $this->table_prefix . "simplegroups_groups_incident.simplegroups_groups_id = " . $group_id . ") ";
     $joins = groups::get_joins_for_groups($category_ids);
     $sg_category_to_table_mapping = groups::get_category_to_table_mapping();
     $reports_count = adminmap_reports::get_reports_count($category_ids, $approved_text, $location_where . " AND " . $filter . $group_where, $logical_operator, $joins, $sg_category_to_table_mapping);
     echo $reports_count . " " . Kohana::config('settings.items_per_page');
     // Pagination
     $pagination = new Pagination(array('query_string' => 'page', 'items_per_page' => (int) Kohana::config('settings.items_per_page'), 'total_items' => $reports_count));
     $incidents = adminmap_reports::get_reports($category_ids, $approved_text, $location_where . " AND " . $filter . $group_where, $logical_operator, "incident.incident_date", "asc", (int) Kohana::config('settings.items_per_page'), $pagination->sql_offset, $joins, $sg_category_to_table_mapping);
     //Set default as not showing pagination. Will change below if necessary.
     $this->template->content->pagination = "";
     // Pagination and Total Num of Report Stats
     if ($pagination->total_items == 1) {
         $plural = "";
     } else {
         $plural = "s";
     }
     if ($pagination->total_items > 0) {
         $current_page = $pagination->sql_offset / (int) Kohana::config('settings.items_per_page') + 1;
         $total_pages = ceil($pagination->total_items / (int) Kohana::config('settings.items_per_page'));
         if ($total_pages > 1) {
             // If we want to show pagination
             $this->template->content->pagination_stats = Kohana::lang('ui_admin.showing_page') . ' ' . $current_page . ' ' . Kohana::lang('ui_admin.of') . ' ' . $total_pages . ' ' . Kohana::lang('ui_admin.pages');
             $this->template->content->pagination = $pagination;
         } else {
             // If we don't want to show pagination
             $this->template->content->pagination_stats = $pagination->total_items . ' ' . Kohana::lang('ui_admin.reports');
         }
     } else {
         $this->template->content->pagination_stats = '(' . $pagination->total_items . ' report' . $plural . ')';
     }
     //locations
     $location_in = array();
     foreach ($incidents as $incident) {
         $location_in[] = $incident->location_id;
     }
     //check if location_in is not empty
     if (count($location_in) > 0) {
         // Get location names
         $query = 'SELECT id, location_name FROM ' . $this->table_prefix . 'location WHERE id IN (' . implode(',', $location_in) . ')';
         $locations_query = $db->query($query);
         $locations = array();
         foreach ($locations_query as $loc) {
             $locations[$loc->id] = $loc->location_name;
         }
     } else {
         $locations = array();
     }
     $this->template->content->locations = $locations;
     //categories
     $localized_categories = array();
     foreach ($incidents as $incident) {
         foreach ($incident->category as $category) {
             $ct = (string) $category->category_title;
             if (!isset($localized_categories[$ct])) {
                 $translated_title = Category_Lang_Model::category_title($category->id, $l);
                 $localized_categories[$ct] = $category->category_title;
                 if ($translated_title) {
                     $localized_categories[$ct] = $translated_title;
                 }
             }
         }
     }
     $this->template->content->localized_categories = $localized_categories;
     // Category Title, if Category ID available
     $category_title = "All Categories";
     $count = 0;
     foreach ($category_ids as $cat_id) {
         $category = ORM::factory('category')->find($cat_id);
         if ($category->loaded) {
             $count++;
             if ($count > 1) {
                 $category_title = $category_title . " " . strtoupper($logical_operator) . " ";
             }
             $category_title = $category_title . $category->category_title;
         }
     }
     $this->template->content->category_title = $category_title . ": ";
     //GET countries
     $countries = array();
     foreach (ORM::factory('country')->orderby('country')->find_all() as $country) {
         // Create a list of all categories
         $this_country = $country->country;
         if (strlen($this_country) > 35) {
             $this_country = substr($this_country, 0, 35) . "...";
         }
         $countries[$country->id] = $this_country;
     }
     $this->template->content->countries = $countries;
     $this->template->content->incidents = $incidents;
     $this->template->content->pagination = $pagination;
     $this->template->content->form_error = $form_error;
     $this->template->content->form_saved = $form_saved;
     $this->template->content->form_action = $form_action;
     // Total Reports
     $this->template->content->total_items = $pagination->total_items;
     // Status Tab
     $this->template->content->status = $status;
     $this->template->header->header_block = $this->themes->header_block();
 }
Ejemplo n.º 4
0
 /**
  * Lists the reports.
  * @param int $page
  */
 function index($page = 1)
 {
     // If user doesn't have access, redirect to dashboard
     if (!admin::permissions($this->user, "reports_view")) {
         url::redirect(url::site() . 'admin/dashboard');
     }
     $this->template->content = new View('adminmap/adminmap_reports');
     $this->template->content->title = Kohana::lang('ui_admin.reports');
     if (!empty($_GET['status'])) {
         $status = $_GET['status'];
         if (strtolower($status) == 'a') {
             $filter = 'incident.incident_active = 0';
         } elseif (strtolower($status) == 'v') {
             $filter = 'incident.incident_verified = 0';
         } else {
             $status = "0";
             $filter = '1=1';
         }
     } else {
         $status = "0";
         $filter = "1=1";
     }
     // check, has the form been submitted?
     $form_error = FALSE;
     $form_saved = FALSE;
     $form_action = "";
     if ($_POST) {
         $post = Validation::factory($_POST);
         //  Add some filters
         $post->pre_filter('trim', TRUE);
         // Add some rules, the input field, followed by a list of checks, carried out in order
         $post->add_rules('action', 'required', 'alpha', 'length[1,1]');
         $post->add_rules('incident_id.*', 'required', 'numeric');
         if ($post->validate()) {
             if ($post->action == 'a') {
                 foreach ($post->incident_id as $item) {
                     $update = new Incident_Model($item);
                     if ($update->loaded == true) {
                         if ($update->incident_active == 0) {
                             $update->incident_active = '1';
                         } else {
                             $update->incident_active = '0';
                         }
                         // Tag this as a report that needs to be sent out as an alert
                         if ($update->incident_alert_status != '2') {
                             // 2 = report that has had an alert sent
                             $update->incident_alert_status = '1';
                         }
                         $update->save();
                         $verify = new Verify_Model();
                         $verify->incident_id = $item;
                         $verify->verified_status = '1';
                         $verify->user_id = $_SESSION['auth_user']->id;
                         // Record 'Verified By' Action
                         $verify->verified_date = date("Y-m-d H:i:s", time());
                         $verify->save();
                         // Action::report_approve - Approve a Report
                         Event::run('ushahidi_action.report_approve', $update);
                     }
                 }
                 $form_action = strtoupper(Kohana::lang('ui_admin.approved'));
             } elseif ($post->action == 'u') {
                 foreach ($post->incident_id as $item) {
                     $update = new Incident_Model($item);
                     if ($update->loaded == true) {
                         $update->incident_active = '0';
                         // If Alert hasn't been sent yet, disable it
                         if ($update->incident_alert_status == '1') {
                             $update->incident_alert_status = '0';
                         }
                         $update->save();
                         $verify = new Verify_Model();
                         $verify->incident_id = $item;
                         $verify->verified_status = '0';
                         $verify->user_id = $_SESSION['auth_user']->id;
                         // Record 'Verified By' Action
                         $verify->verified_date = date("Y-m-d H:i:s", time());
                         $verify->save();
                         // Action::report_unapprove - Unapprove a Report
                         Event::run('ushahidi_action.report_unapprove', $update);
                     }
                 }
                 $form_action = strtoupper(Kohana::lang('ui_admin.unapproved'));
             } elseif ($post->action == 'v') {
                 foreach ($post->incident_id as $item) {
                     $update = new Incident_Model($item);
                     $verify = new Verify_Model();
                     if ($update->loaded == true) {
                         if ($update->incident_verified == '1') {
                             $update->incident_verified = '0';
                             $verify->verified_status = '0';
                         } else {
                             $update->incident_verified = '1';
                             $verify->verified_status = '2';
                         }
                         $update->save();
                         $verify->incident_id = $item;
                         $verify->user_id = $_SESSION['auth_user']->id;
                         // Record 'Verified By' Action
                         $verify->verified_date = date("Y-m-d H:i:s", time());
                         $verify->save();
                     }
                 }
                 $form_action = "VERIFIED";
             } elseif ($post->action == 'd') {
                 foreach ($post->incident_id as $item) {
                     $update = new Incident_Model($item);
                     if ($update->loaded == true) {
                         $incident_id = $update->id;
                         $location_id = $update->location_id;
                         $update->delete();
                         // Delete Location
                         ORM::factory('location')->where('id', $location_id)->delete_all();
                         // Delete Categories
                         ORM::factory('incident_category')->where('incident_id', $incident_id)->delete_all();
                         // Delete Translations
                         ORM::factory('incident_lang')->where('incident_id', $incident_id)->delete_all();
                         // Delete Photos From Directory
                         foreach (ORM::factory('media')->where('incident_id', $incident_id)->where('media_type', 1) as $photo) {
                             deletePhoto($photo->id);
                         }
                         // Delete Media
                         ORM::factory('media')->where('incident_id', $incident_id)->delete_all();
                         // Delete Sender
                         ORM::factory('incident_person')->where('incident_id', $incident_id)->delete_all();
                         // Delete relationship to SMS message
                         $updatemessage = ORM::factory('message')->where('incident_id', $incident_id)->find();
                         if ($updatemessage->loaded == true) {
                             $updatemessage->incident_id = 0;
                             $updatemessage->save();
                         }
                         // Delete Comments
                         ORM::factory('comment')->where('incident_id', $incident_id)->delete_all();
                         // Action::report_delete - Deleted a Report
                         Event::run('ushahidi_action.report_delete', $update);
                     }
                 }
                 $form_action = strtoupper(Kohana::lang('ui_admin.deleted'));
             }
             $form_saved = TRUE;
         } else {
             $form_error = TRUE;
         }
     }
     $db = new Database();
     // Category ID
     $category_ids = array();
     if (isset($_GET['c']) and !empty($_GET['c'])) {
         $category_ids = explode(",", $_GET['c']);
         //get rid of that trailing ","
     } else {
         $category_ids = array("0");
     }
     // logical operator
     $logical_operator = "or";
     if (isset($_GET['lo']) and !empty($_GET['lo'])) {
         $logical_operator = $_GET['lo'];
     }
     $show_unapproved = "3";
     //1 show only approved, 2 show only unapproved, 3 show all
     //figure out if we're showing unapproved stuff or what.
     if (isset($_GET['u']) and !empty($_GET['u'])) {
         $show_unapproved = (int) $_GET['u'];
     }
     $approved_text = "";
     if ($show_unapproved == 1) {
         $approved_text = "incident.incident_active = 1 ";
     } else {
         if ($show_unapproved == 2) {
             $approved_text = "incident.incident_active = 0 ";
         } else {
             if ($show_unapproved == 3) {
                 $approved_text = " (incident.incident_active = 0 OR incident.incident_active = 1) ";
             }
         }
     }
     // Start Date
     $start_date = (isset($_GET['s']) and !empty($_GET['s'])) ? (int) $_GET['s'] : "0";
     // End Date
     $end_date = (isset($_GET['e']) and !empty($_GET['e'])) ? (int) $_GET['e'] : "0";
     $filter .= $start_date ? " AND incident.incident_date >= '" . date("Y-m-d H:i:s", $start_date) . "'" : "";
     $filter .= $end_date ? " AND incident.incident_date <= '" . date("Y-m-d H:i:s", $end_date) . "'" : "";
     $location_where = "";
     // Break apart location variables, if necessary
     $southwest = array();
     if (isset($_GET['sw'])) {
         $southwest = explode(",", $_GET['sw']);
     }
     $northeast = array();
     if (isset($_GET['ne'])) {
         $northeast = explode(",", $_GET['ne']);
     }
     if (count($southwest) == 2 and count($northeast) == 2) {
         $lon_min = (double) $southwest[0];
         $lon_max = (double) $northeast[0];
         $lat_min = (double) $southwest[1];
         $lat_max = (double) $northeast[1];
         $location_where = ' AND (location.latitude >=' . $lat_min . ' AND location.latitude <=' . $lat_max . ' AND location.longitude >=' . $lon_min . ' AND location.longitude <=' . $lon_max . ') ';
     }
     $reports_count = adminmap_reports::get_reports_count($category_ids, $approved_text, $location_where . " AND " . $filter, $logical_operator);
     // Pagination
     $pagination = new Pagination(array('query_string' => 'page', 'items_per_page' => (int) Kohana::config('settings.items_per_page'), 'total_items' => $reports_count));
     $incidents = adminmap_reports::get_reports($category_ids, $approved_text, $location_where . " AND " . $filter, $logical_operator, "incident.incident_date", "asc", (int) Kohana::config('settings.items_per_page_admin'), $pagination->sql_offset);
     //GET countries
     $countries = array();
     foreach (ORM::factory('country')->orderby('country')->find_all() as $country) {
         // Create a list of all categories
         $this_country = $country->country;
         if (strlen($this_country) > 35) {
             $this_country = substr($this_country, 0, 35) . "...";
         }
         $countries[$country->id] = $this_country;
     }
     $this->template->content->countries = $countries;
     $this->template->content->incidents = $incidents;
     $this->template->content->pagination = $pagination;
     $this->template->content->form_error = $form_error;
     $this->template->content->form_saved = $form_saved;
     $this->template->content->form_action = $form_action;
     // Total Reports
     $this->template->content->total_items = $pagination->total_items;
     // Status Tab
     $this->template->content->status = $status;
     // Javascript Header
     $this->template->js = new View('admin/reports_js');
 }