<?php

require_once "db_connect.php";
header("Access-Control-Allow-Origin: *");
header("Content-Type: application/json; charset=UTF-8");

$start = $_GET['start_date'];
$end = $_GET['end_date'];

$output = array();
$queryIs = "select rl.province_id, COALESCE(count(rl.province_id),0) as count,COUNT(CASE WHEN rd.violent_nonviolent ='violent' THEN 1 END) AS violent,COUNT(CASE WHEN rd.violent_nonviolent ='nonviolent' THEN 1 END) AS nonviolent from reports_details rd join reports_tags rt on rd.report_id = rt.report_id join reports r on r.id = rd.report_id join reports_locations rl on rl.report_id = r.id where r.approve_status = 'approved' and rt.tag_id = 19 and r.eventdate between $start and $end";

if(mysqli_real_escape_string($db,$_GET['type']) == 'lock'){
 
   $queryIs = $queryIs." and rd.secondary_cause_code='991'";
} else if(mysqli_real_escape_string($db,$_GET['type']) == 'qua'){
  
   $queryIs =  $queryIs." and rd.secondary_cause_code='992'";
} else if(mysqli_real_escape_string($db,$_GET['type']) == 'rel'){
  
   $queryIs =  $queryIs." and rd.secondary_cause_code='993'";
} else if(mysqli_real_escape_string($db,$_GET['type']) == 'irr'){
 
   $queryIs =  $queryIs." and rd.secondary_cause_code='994'";
} else if(mysqli_real_escape_string($db,$_GET['type']) == 'pre'){
   
   $queryIs =  $queryIs." and rd.secondary_cause_code='995'";
} else if(mysqli_real_escape_string($db,$_GET['type']) == 'hea'){
   
   $queryIs =  $queryIs." and rd.secondary_cause_code='996'";
}
$queryIs = $queryIs." group by rl.province_id";
$query = mysqli_query($db,$queryIs);

//echo json_encode($queryIs);
$province_name = "";
while($row = mysqli_fetch_assoc($query)){
  //$myObj->id = $row['id'];
  //$myObj->title = $row['title'];
  if($row['province_id'] == 1){
     $province_name = 'Province 1';
  } else if($row['province_id'] == 2){
     $province_name = 'Province 2';
  } else if($row['province_id'] == 3){
  	 $province_name = 'Bagmati';
  } else if($row['province_id'] == 4){
  	 $province_name = 'Gandaki';
  } else if($row['province_id'] == 5){
  	 $province_name = 'Province 5';
  } else if($row['province_id'] == 6){
  	 $province_name = 'Karnali';
  } else if($row['province_id'] == 7){
  	 $province_name = 'Sudur Paschim';
  }
  $output[] = ['province' => (int)$row['province_id'], 'province_name' => $province_name, 'count' => (int)$row['count'], 'violent' => (int)$row['violent'], 'nonviolent'=> (int)$row['nonviolent']];
}
$myObj = (object) null;
$myObj->results = $output;
$myObj->type = $_GET['type'];
echo json_encode($myObj);
//echo $queryIs;
?>
