public function index($cluster_id = 0) { // 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'); $this->template->content->area_name = ""; $this->template->content->disp_distance = ""; //FORMのhiddenタグ用パラメータ初期化と代入 if(isset($_SESSION["locale"])){ $_GET["l"] = $_SESSION["locale"]; } // 引き回すGETパラメータのテンプレートへの引き渡し $this->template->content->keyword = valid::initGetVal('keyword',"text"); $this->template->content->address = valid::initGetVal('address',"text"); $this->template->content->distance = valid::initGetVal('distance',"number"); $this->template->content->c = valid::initGetVal('c',"number"); $this->template->content->sw = valid::initGetVal('sw',"text"); $this->template->content->ne = valid::initGetVal('ne',"text"); $this->template->content->l = valid::initGetVal('l',"natural_numbewr"); $this->template->content->mode = valid::initGetVal('mode',"text"); $this->template->content->order = valid::initGetVal('order',"text"); $db = new Database; // Get incident_ids if we are to filter by category $allowed_ids = array(); if (isset($_GET['c']) AND !empty($_GET['c']) AND $_GET['c']!=0) { $category_id = $db->escape($_GET['c']); $query = 'SELECT ic.incident_id AS incident_id FROM '.$this->table_prefix.'incident_category AS ic INNER JOIN '.$this->table_prefix.'category AS c ON (ic.category_id = c.id) WHERE c.id='.$category_id.' OR c.parent_id='.$category_id.';'; $query = $db->query($query); foreach ( $query as $items ) { $allowed_ids[] = $items->incident_id; } } // Get location_ids if we are to filter by location $location_ids = array(); // 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']); } //指定地区の指定半径内インシデント取得でGoogleMAPAPIで緯度経度を取得できなかった場合DBを取りに行かないようにするためのフラグ $dbget_flg = true; $this->template->content->choices_flg = false; //指定地区の指定半径内インシデント取得処理 if(isset($_GET["address"]) && trim($_GET["address"]) !== "" && isset($_GET["distance"]) && is_numeric($_GET["distance"]) && $_GET["distance"] > 0){ $address = urlencode($_GET["address"]); // http://www.geocoding.jp/を利用して指定地区名の緯度経度を取得 $geocoding_url = 'http://www.geocoding.jp/api/?q='.$address; $geo_geocoding = @file_get_contents($geocoding_url,false,stream_context_create(array('http' => array('timeout'=>$this->api_timeout)))); // APIのエラーハンドリング if($geo_geocoding === FALSE){ if(count($http_response_header) > 0){ $stat_tokens = explode(' ', $http_response_header[0]); switch($stat_tokens[1]){ case 404: // 404 Not found の場合 break; case 500: // 500 Internal Server Error の場合 break; default: // その他 break; } }else{ // タイムアウトの場合 } }else{ $geo_geocoding = simplexml_load_string($geo_geocoding); } //結果の取得とインシデントの取得 if(isset($geo_geocoding->coordinate)){ if(isset($geo_geocoding->coordinate->lat) && isset($geo_geocoding->coordinate->lng)){ $lat_center = $geo_geocoding->coordinate->lat; $lon_center = $geo_geocoding->coordinate->lng; $area_name = $geo_geocoding->address; $_GET["address"] = $this->template->content->area_name = trim($area_name); if($_GET["distance"] >= 1){ $this->template->content->disp_distance = $_GET["distance"]."km"; }else{ $this->template->content->disp_distance = ($_GET["distance"]*1000)."m"; } $query = 'SELECT id FROM '.$this->table_prefix.'location WHERE (round(sqrt(pow(('.$this->table_prefix.'location.latitude - '.$lat_center.')/0.0111, 2) + pow(('.$this->table_prefix.'location.longitude - '.$lon_center.')/0.0091, 2)), 1)) <= '.$_GET["distance"]; $query = $db->query($query); foreach ( $query as $items ) { $location_ids[] = $items->id; } } }elseif(isset($geo_geocoding->choices)){ $this->template->content->choices_flg = true; $dbget_flg = false; } //TOPの赤丸からのインシデント取得処理 }elseif ( count($southwest) == 2 AND count($northeast) == 2 ){ $lon_min = (float) $southwest[0]; $lon_max = (float) $northeast[0]; $lat_min = (float) $southwest[1]; $lat_max = (float) $northeast[1]; $lon_center = ($lon_min+$lon_max) / 2; $lat_center = ($lat_min+$lat_max) / 2; $dist1 = (round(sqrt(pow(($lat_max - $lat_center)/0.0111, 2) + pow(($lon_max - $lon_center)/0.0091, 2)), 1)); $dist2 = (round(sqrt(pow(($lat_min - $lat_center)/0.0111, 2) + pow(($lon_min - $lon_center)/0.0091, 2)), 1)); // http://www.finds.jp/を利用して中央地点の地名を取得 $finds_url = 'http://www.finds.jp/ws/rgeocode.php?json&lat='.$lat_center.'&lon='.$lon_center; $geo_finds = @file_get_contents($finds_url,false,stream_context_create(array('http' => array('timeout'=>$this->api_timeout)))); // APIのエラーハンドリング if($geo_finds === FALSE){ if(count($http_response_header) > 0){ $stat_tokens = explode(' ', $http_response_header[0]); switch($stat_tokens[1]){ case 404: // 404 Not found の場合 break; case 500: // 500 Internal Server Error の場合 break; default: // その他 break; } }else{ // タイムアウトの場合 } }else{ $geo_finds = json_decode($geo_finds,true); } if($geo_finds["status"]===200 || $geo_finds["status"]===201 ||$geo_finds["status"]===202){ $area_name = str_replace(' ','',$geo_finds["result"]["prefecture"]["pname"].$geo_finds["result"]["municipality"]["mname"]); if(isset($area_name) && $area_name !== ""){ $_GET["address"] = $this->template->content->area_name = $area_name; } } //指定範囲内のインシデントを取得 $query = 'SELECT id FROM '.$this->table_prefix.'location WHERE latitude >='.$lat_min.' AND latitude <='.$lat_max.' AND longitude >='.$lon_min.' AND longitude <='.$lon_max; $query = $db->query($query); foreach ( $query as $items ) { $location_ids[] = $items->id; } }elseif (isset($_GET['l']) AND !empty($_GET['l']) AND $_GET['l']!=0){ $location_ids[] = (int) $_GET['l']; } // Get the count $incident_id_in = '1=1'; if (count($allowed_ids) > 0) { $incident_id_in = 'incident.id IN ('.implode(',',$allowed_ids).')'; } $location_id_in = '1=1'; if (count($location_ids) > 0) { $location_id_in = 'location_id IN ('.implode(',',$location_ids).')'; } // 検索キーワード取得 if(isset($_GET["keyword"]) && trim($_GET["keyword"]) !==""){ $keywords = array(); $keyword = str_replace(" "," ",$_GET["keyword"]); $keywords = explode(" ",$keyword); } // キーワード検索の初期化(キーワードがない場合のエラー対応) $keyword_like = "1=1"; if(isset($keywords) && count($keywords)){ $keyword_like = array(); foreach($keywords as $val){ $keyword_like[] = "(incident_title like '%".addslashes($val)."%' OR incident_description like '%".addslashes($val)."%')"; } $keyword_like = implode(' AND ',$keyword_like); } if($dbget_flg){ // formからの送信の場合 if(isset($_GET["mode"])){ // 共通処理としてのページネーション // Pagination $pagination = new Pagination(array( 'query_string' => 'page', 'items_per_page' => (int) Kohana::config('settings.items_per_page'), 'total_items' => ORM::factory("incident") ->join($this->table_prefix.'location',$this->table_prefix.'location.id',$this->table_prefix.'incident.location_id',"LEFT OUTER") ->where("incident_active", 1) ->where($location_id_in) ->where($incident_id_in) ->where($keyword_like) ->count_all() )); // Reports // 中心座標が取得できていれば if(isset($lat_center)){ // ソート順を定義 if(isset($_GET["order"]) && $_GET["order"]=="new"){ // 新着順 $order = array( "incident_date"=>"desc", '(round(sqrt(pow(('.$this->table_prefix.'location.latitude - '.$lat_center.')/0.0111, 2) + pow(('.$this->table_prefix.'location.longitude - '.$lon_center.')/0.0091, 2)), 1))'=>"asc" ); }elseif(isset($_GET["order"]) && $_GET["order"]=="dist"){ // 近隣順 $order = array( '(round(sqrt(pow(('.$this->table_prefix.'location.latitude - '.$lat_center.')/0.0111, 2) + pow(('.$this->table_prefix.'location.longitude - '.$lon_center.')/0.0091, 2)), 1))'=>"asc", "incident_date"=>"desc" ); } // SELECT句に中心点からの距離を追加 $select = $this->table_prefix.'incident.*,(round(sqrt(pow(('.$this->table_prefix.'location.latitude - '.$lat_center.')/0.0111, 2) + pow(('.$this->table_prefix.'location.longitude - '.$lon_center.')/0.0091, 2)), 1)) as dist'; }else{ // 中心座標が取れていなければ新着順で固定 $order = array( "incident_date"=>"desc" ); // SELECT句はincidentsの全レコード $select = $this->table_prefix.'incident.*'; } if($_GET["mode"]=="areaorder"){ $incidents = ORM::factory("incident") ->select($select,false) ->join($this->table_prefix.'location',$this->table_prefix.'location.id',$this->table_prefix.'incident.location_id',"LEFT OUTER") ->where("incident_active", 1) ->where($location_id_in) ->where($incident_id_in) ->where($keyword_like) ->orderby('(round(sqrt(pow(('.$this->table_prefix.'location.latitude - '.$lat_center.')/0.0111, 2) + pow(('.$this->table_prefix.'location.longitude - '.$lon_center.')/0.0091, 2)), 1))', "asc",false) ->find_all((int) Kohana::config('settings.items_per_page'), $pagination->sql_offset); }elseif($_GET["mode"]=="areasearch"){ if(isset($_GET["order"]) && isset($_GET["order"])){ $escape = false; }else{ $escape = true; } // Reports $incidents = ORM::factory("incident") ->select($select,$escape) ->join($this->table_prefix.'location',$this->table_prefix.'location.id',$this->table_prefix.'incident.location_id',"LEFT OUTER") ->where("incident_active", 1) ->where($location_id_in) ->where($incident_id_in) ->where($keyword_like) ->orderby($order,NULL,$escape) ->find_all((int) Kohana::config('settings.items_per_page'), $pagination->sql_offset); } }else{ // Pagination $pagination = new Pagination(array( 'query_string' => 'page', 'items_per_page' => (int) Kohana::config('settings.items_per_page'), 'total_items' => ORM::factory("incident") ->where("incident_active", 1) ->where($location_id_in) ->where($incident_id_in) ->where($keyword_like) ->count_all() )); // Reports $incidents = ORM::factory("incident") ->where("incident_active", 1) ->where($location_id_in) ->where($incident_id_in) ->where($keyword_like) ->orderby("incident_date", "desc") ->find_all((int) Kohana::config('settings.items_per_page'), $pagination->sql_offset); } }else{ $incidents = array(); $pagination = new Pagination(); } // Swap out category titles with their proper localizations using an array (cleaner way to do this?) // $query = 'SELECT id,category_title,category_color FROM category WHERE category_visible = 1 AND category_trusted = 0'; $query = 'SELECT id,category_title,category_color,category_image_thumb FROM category ORDER BY category_type desc;'; $query = $db->query($query); $category_master = array(); $localized_categories = array(); foreach($query as $row){ $category_master[$row->id]['title'] = $row->category_title; $category_master[$row->id]['color'] = $row->category_color; $category_master[$row->id]['category_image_thumb'] = $row->category_image_thumb; $localized_categories[(string)$row->category_title] = $row->category_title; $localized_categories[(string)$row->category_title]['title'] = $row->category_title; $localized_categories[(string)$row->category_title]['color'] = $row->category_title; } $this->template->content->category_master = $category_master; $this->template->content->localized_categories = $localized_categories; $this->template->content->incidents = $incidents; //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 if((isset($_GET["l"]) && ($_GET["l"] === "ja_JP" || $_GET["l"] === "")) OR (!isset($_GET["l"]))){ $this->template->content->pagination_stats = "全".$total_pages."中".$current_page.Kohana::lang('ui_admin.showing_page'); }else{ $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.')'; } // Category Title, if Category ID available $category_id = ( isset($_GET['c']) AND !empty($_GET['c']) ) ? $_GET['c'] : "0"; $category = ORM::factory('category') ->find($category_id); if($category->loaded) { $translated_title = Category_Lang_Model::category_title($category_id,$l); if($translated_title) { $this->template->content->category_title = $translated_title; }else{ $this->template->content->category_title = $category->category_title; } }else{ $this->template->content->category_title = ""; } // Collect report stats $this->template->content->report_stats = new View('reports_stats'); // Total Reports $total_reports = Incident_Model::get_total_reports(TRUE); // Average Reports Per Day $oldest_timestamp = Incident_Model::get_oldest_report_timestamp(); // Round the number of days up to the nearest full day $days_since = ceil((time() - $oldest_timestamp) / 86400); if ($days_since < 1) { $avg_reports_per_day = $total_reports; }else{ $avg_reports_per_day = round(($total_reports / $days_since),2); } // Percent Verified $total_verified = Incident_Model::get_total_reports_by_verified(true); $percent_verified = ($total_reports == 0) ? '-' : round((($total_verified / $total_reports) * 100),2).'%'; $this->template->content->report_stats->total_reports = $total_reports; $this->template->content->report_stats->avg_reports_per_day = $avg_reports_per_day; $this->template->content->report_stats->percent_verified = $percent_verified; $this->template->header->action_name = Kohana::lang('ui_main.reports_title_index'); $this->template->header->header_block = $this->themes->header_block(); }