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();
}
