<?php
/*$username = "androidappp";
 	$password = "A!ms99AA";
 	$database = "androidappp";
 	$server = "androidappp.db.4594972.hostedresource.com";*/
include("database.php");

// Opens a connection to a MySQL server
/*$connection = mysql_connect ($server, $username, $password);

if (!$connection) 
{
   die('Not connected : ' . mysql_error());
}

// Set the active MySQL database
$db_selected = mysql_select_db($database, $connection);

if (!$db_selected) 
{
  die ('Can\'t use db : ' . mysql_error());
}*/

// Select all the rows in the markers table
extract($_GET);

$q = "SELECT id FROM input_form WHERE ward_office= '$td1' and ward = '$sb2' and society_name  like '%$society%'";

$r = mysql_query($q);

	$query = " SELECT GROUP_CONCAT(lng, ',', lat, ',', '5' separator ' ') AS coordinates FROM markers WHERE aid in(SELECT id FROM input_form WHERE ward_office= '$td1' and ward = '$sb2' and society_name  like '%$society%' );";
	
	//echo $query;

	$result = mysql_query($query);
		
	$sql = "SELECT tree_name,height,width,CONCAT(lng, ',', lat, ',') AS coord FROM info WHERE aid in(SELECT id FROM input_form WHERE ward_office= '$td1' and ward = '$sb2' and society_name  like '%$society%' );";
	
	//echo  $sql;
	$res = mysql_query($sql);
	
	$num = mysql_num_rows($res);
			
	$query_ward="select * from input_form where ward_office= '$td1'";
	//echo "select * from input_form where id='$id'";
	$result_ward=mysql_query($query_ward);
	$row_ward=mysql_fetch_array($result_ward);
	$ward = $row_ward['ward_office'];
	$ward1 = $row_ward['ward'];
	
	$query_master_ward = mysql_query("select * from master_ward where wid='$ward'");
	$row_master_ward = mysql_fetch_array($query_master_ward);
	$wardName = $row_master_ward['wvalue'];
	
	$query_master_ward_drop = mysql_query("select * from master_ward_drop where did='$ward1'");
	$row_master_ward_drop = mysql_fetch_array($query_master_ward_drop);
	$wardName_drop = $row_master_ward_drop['dvalue'];
	
	$sql3="select count(i.tree_name) as count, t.tree_name as tree_name,t.scientific_name as scientific_name from info i,tree_name t where aid in(SELECT id FROM input_form WHERE ward_office= '$td1' and ward = '$sb2' and society_name  like '%$society%' ) and t.tid = i.tree_name group by tree_name";
	
	//echo $sql3;

	$sql4 = "select manual_tree as tree_name,count(manual_tree) as count from info where aid in(SELECT id FROM input_form WHERE ward_office= '$td1' and ward = '$sb2' and society_name  like '%$society%' ) and tree_name = '' and manual_tree <> ''  group by manual_tree";
	
	//echo $sql4."<br>";
	
	$sql5 = "select manual_sci_tree as tree_name,count(manual_sci_tree) as count from info where aid in(SELECT id FROM input_form WHERE ward_office= '$td1' and ward = '$sb2' and society_name  like '%$society%' ) and tree_name = '' and manual_sci_tree <> ''  group by manual_sci_tree";
	
	//echo $sql5."<br>";
	
	$query1_2="select * from info where aid in(SELECT id FROM input_form WHERE ward_office= '$td1' and ward = '$sb2' and society_name  like '%$society%' )";
	$rest1_2=mysql_query($query1_2);
	$res2_2 = "select t.growth_speed as speed,i.age as age from info i, carbon c, tree_name t where i.age = c.age and i.tree_name = t.tid and i.aid in(SELECT id FROM input_form WHERE ward_office= '$td1' and ward = '$sb2' and society_name  like '%$society%' )";
	 $carbon_query_2 = mysql_query($res2_2);
	
	$result3 = mysql_query($sql3);
	$result4 = mysql_query($sql4);
	$result5 = mysql_query($sql5);
	
	$n1_1 = @mysql_num_rows($result3);
	$n2_1 = @mysql_num_rows($result4);
	$n3_1 = @mysql_num_rows($result5);
	
	$no_of_species_1 = $n1_1+$n2_1+$n3_1;
	$total_carbon_2 = 0.0;
	while($row_2 = mysql_fetch_array($rest1_2))
	{
		$res_carbon_query_2 = mysql_fetch_array($carbon_query_2);
	  
		  if($res_carbon_query_2['speed'] != "")
		  {
				$res3_2 = "select `".$res_carbon_query_2['speed']."` from carbon where `age` = '" .$res_carbon_query_2['age']."'";
	  $carbon_2 = mysql_query($res3_2);
	  
	  //echo $res3_1;
		  
	  $res_carbon_2 = mysql_fetch_array($carbon_2);
		  }
		  
		  $total_carbon_2= $total_carbon_2+$res_carbon_2[$res_carbon_query_2['speed']];
		  
	}
	
	$sql_q="SELECT * FROM input_form WHERE id in(SELECT id FROM input_form WHERE ward_office= '$td1' and ward = '$sb2' and society_name  like '%$society%' )  ";
	$rest=@mysql_query($sql_q);
	$row =@mysql_fetch_object($rest);
	$plot_area = trim($row->plot_area);
	
	$query= @mysql_fetch_array(mysql_query("select * from required_tree"));
	$area = $query['area'];
	$tree = $query['tree'];
	$ans = (($plot_area)/($area));
	$deficit_trees = $ans-$num;
		
	if (!$result) 
	{
		die('Invalid query: ' . mysql_error());
	}
	if (!$result_ward) 
	{
		die('Invalid query: ' . mysql_error());
	}

// Start KML file, create parent node
$dom = new DOMDocument('1.0','UTF-8');

//Create the root KML element and append it to the Document
$node = $dom->createElementNS('http://earth.google.com/kml/2.1','kml');
$parNode = $dom->appendChild($node);
//Create a Folder element and append it to the KML element
$fnode = $dom->createElement('Folder');
$folderNode = $parNode->appendChild($fnode);
//Iterate through the MySQL results
$row = @mysql_fetch_assoc($result);
while($r1 = mysql_fetch_array($r)){

//Create a Placemark and append it to the document
$node = $dom->createElement('Placemark');
$placeNode = $folderNode->appendChild($node);

//Create an id attribute and assign it the value of id column
$placeNode->setAttribute('id','linestring1');

//Create name, description, and address elements and assign them the values of 
//the name, type, and address columns from the results

$nameNode = $dom->createElement('name','Property Name :'.$row_ward['society_name']);
$placeNode->appendChild($nameNode);
$descNode= $dom->createElement('description', 
htmlentities('Number of Trees Found in This Region : '.$num.'<br>Area of Tree : '.$row_ward['plot_area'].'<br> No. of Species Recorded : '.$no_of_species_1).'<br>Carbon Sequestration Potential of Trees : '.$total_carbon_2.'<br>No. of Deficit Trees : '.$deficit_trees);
$placeNode->appendChild($descNode);

//Create a LineString element
$lineNode = $dom->createElement('LineString');
$placeNode->appendChild($lineNode);
$exnode = $dom->createElement('extrude', '1');
$lineNode->appendChild($exnode);
$almodenode =$dom->createElement(altitudeMode,'relativeToGround');
$lineNode->appendChild($almodenode);

//Create a coordinates element and give it the value of the lng and lat columns from the results


	$q1 = " SELECT GROUP_CONCAT(lng, ',', lat, ',', '5' separator ' ') AS coordinates FROM markers WHERE aid = '".$r1['id']."'";
	
	$res1 = mysql_query($q1);
	$r2 = mysql_fetch_array($res1);
	echo $r2['coordinates'];
	$coorNode = $dom->createElement('coordinates',$r2['coordinates']);
	$lineNode->appendChild($coorNode);
}
//##############################################################################################################################3


while($row1 =  mysql_fetch_assoc($res))
{
		
		$tree_name = $row1['tree_name'];
		
		$tree_query = mysql_query("select * from tree_name where tid = '$tree_name'");
		$tree_result = mysql_fetch_array($tree_query);
	
		$node = $dom->createElement('Placemark');
		$placeNode = $folderNode->appendChild($node);
		
		//Create an id attribute and assign it the value of id column
		$placeNode->setAttribute('id','linestring1');
		
		//Create name, description, and address elements and assign them the values of 
		//the name, type, and address columns from the results
		
		$nameNode = $dom->createElement('name',$tree_result['tree_name']);
		$placeNode->appendChild($nameNode);
		$descNode= $dom->createElement('description', htmlentities('Property Name :'.$row_ward['society_name'].'<br>Surveyed Tree :'.$num.'<br>Tree Name : '.$tree_result['tree_name'].'<br>Scientific Name : '.$tree_result['scientific_name'].'<br> Height:'.$row1['height'].'<br> GBH :'.$row1['width']));
		$placeNode->appendChild($descNode);
		
		//Create a LineString element
		$lineNode = $dom->createElement('Point');
		$placeNode->appendChild($lineNode);
		$exnode = $dom->createElement('extrude', '1');
		$lineNode->appendChild($exnode);
		$almodenode =$dom->createElement(altitudeMode,'relativeToGround');
		$lineNode->appendChild($almodenode);
		
		//Create a coordinates element and give it the value of the lng and lat columns from the results
		
		$coorNode = $dom->createElement('coordinates',$row1['coord']);
		$lineNode->appendChild($coorNode);
}
$kmlOutput = $dom->saveXML();

//assign the KML headers. 

$fileName = "SmartSurvey_".$wardName."_".$wardName_drop;
//echo $fileName;
header('Content-type: application/vnd.google-earth.kml+xml');
header('Content-Disposition: attachment; filename="'.$fileName.'".kml"');
echo $kmlOutput;
?>