<?php
	error_reporting(0);
	$servername = "localhost";
	$username = "adclubuser";
	$password = "W;yCg0H0k4z5";
	$dbname = "adclubmadras";

	// Create connection
	$conn = mysqli_connect($servername, $username, $password, $dbname);
	// Check connection
	if(!$conn){
	    die("Connection failed: " . mysqli_connect_error());
	}
	$sql = "select * from `wp_members`";
	$result = $conn->query($sql);
	$filename = "MembersList.xls";

	$contents = "<table border='1' cellpadding=0 cellspacing=0>
		<tr>
			<th><strong>Unique ID</strong></th>
			<th><strong>Category</strong></th>
			<th><strong>Organization</strong></th>
			<th><strong>Contact Person</strong></th>
			<th><strong>Address 1</strong></th>
			<th><strong>Address 2</strong></th>
			<th><strong>Phone</strong></th>
			<th><strong>Mobile</strong></th>
			<th><strong>Email</strong></th>
			<th><strong>Facebook</strong></th>
			<th><strong>Twitter</strong></th>
			<th><strong>Linkedin</strong></th>
			<th><strong>Members</strong></th>
			<th><strong>DOReg</strong></th>
			<th><strong>DORen</strong></th>
			<th><strong>Status</strong></th>
			<th><strong>Price</strong></th>
			<th><strong>Total Members</strong></th>
			<th><strong>Renewal</strong></th>
			<th><strong>Payment Status</strong></th>
			<th><strong>Payment Mode</strong></th>
			<th><strong>DOExp</strong></th>
		</tr>
	";
	$i = 0;
	while($report_rec = $result->fetch_assoc()){
		$i = $i + 1;
		$unique_id = $report_rec["unique_id"];
		$mem_category = $report_rec["mem_category"];
		$org_name = $report_rec["org_name"];
		$con_person = $report_rec["con_person"];
		$address1 = $report_rec["address1"];
		$address2 = $report_rec["address2"];
		$phone = $report_rec["phone"];
		$mobile = $report_rec["mobile"];
		$email = $report_rec["email"];
		$facebook = $report_rec["facebook"];
		$twitter = $report_rec["twitter"];
		$linkedin = $report_rec["linkedin"];
		$date_registered = $report_rec["date_registered"];
		$date_renewed = $report_rec["date_renewed"];
		$status = $report_rec["status"];
		$price = $report_rec["price"];
		$total_mem = $report_rec["total_mem"];
		$renewal = $report_rec["renewal"];
		$paymentStatus = $report_rec["paymentStatus"];
		$paymentMode = $report_rec["paymentMode"];
		$expDate = $report_rec["expiryDate"];
		$nominees = json_decode($report_rec["members"]);

		$members = [];
		$i = 0;

		foreach($nominees as $nominee){
	        $members[$i]["name"] = $nominee->name;
	        $members[$i]["designation"] = $nominee->designation;
	        $members[$i]["email"] = $nominee->email;
	        $members[$i]["mobile"] = $nominee->mobile;
	        $i++;
	    }
	    if(count($members) > 0){
	    	//echo $members[0]['name'];
	    	//echo count($members);die();
	    }
	    //print_r($members);die();
	    $table = "<table>
	    			<tr>
	    				<th align='left'>Name</th>
	    				<th align='left'>Designation</th>
	    				<th align='left'>Email</th>
	    				<th align='left'>Mobile</th>
	    			</tr>
	    ";
	    for($j=0;$j<count($members);$j++)
	    {    
	     	$table .=  "<tr><td>".$members[$j]['name']."</td>";
	     	$table .=  "<td>".$members[$j]['designation']."</td>";
	     	$table .=  "<td>".$members[$j]['email']."</td>";
	     	$table .=  "<td>".$members[$j]['mobile']."</td></tr>";
	    }
	    $table .= "</table>";
		$list = "<ul><li>" . implode("</li><li>", $members) . "</li></ul>";
		$contents .= '<tr>
			<td>'.$unique_id.'</td>
			<td>'.$mem_category.'</td>
			<td>'.$org_name.'</td>
			<td>'.$con_person.'</td>
			<td>'.$address1.'</td>
			<td>'.$address2.'</td>
			<td>'.$phone.'</td>
			<td>'.$mobile.'</td>
			<td>'.$email.'</td>
			<td>'.$facebook.'</td>
			<td>'.$twitter.'</td>
			<td>'.$linkedin.'</td>
			<td>'.$table.'</td>
			<td>'.$date_registered.'</td>
			<td>'.$date_renewed.'</td>
			<td>'.$status.'</td>
			<td>'.$price.'</td>
			<td>'.$total_mem.'</td>
			<td>'.$renewal.'</td>
			<td>'.$paymentStatus.'</td>
			<td>'.$paymentMode.'</td>
			<td>'.$expDate.'</td>
		</tr>';
	}
?>
<?php
	$contents .= "</table>";
	//echo $contents;die();
	$contents1 = "<html><head><meta http-equiv='Content-Type' content='text/html; charset=utf-8'/></head><body>";
	header("Content-type: application/octet-stream");
	header('Content-type: application/ms-excel');
	header('Content-Disposition: attachment; filename='.$filename);
	header("Pragma: no-cache");
	header("Expires: 0");
	$contents1 .= $contents;
	$contents1 .= "</body></html>";
	echo $contents1;
	$conn->close();
?>