<?php 
include("../config.php");
$sd = mysqli_real_escape_string($conn, date('Y-m-d', strtotime($_GET['start_date'])));
$ed = mysqli_real_escape_string($conn, date('Y-m-d', strtotime($_GET['end_date'])));
$select  = "SELECT * FROM `users` WHERE `status` ='0'";
$result  = mysqli_query($conn,$select);
$total   = mysqli_num_rows($result);

$table       	= '';
$total_count    = 0;
if(count($total) > 0) {
	$total_fright = 0;
	while ($row = mysqli_fetch_array($result)) {
		if(!empty($sd) && !empty($ed)){
			
			$data = "SELECT (SELECT count(*) FROM lr_mst WHERE date(`created_datetime`) >= '".$sd."' AND date(`created_datetime`) <= '".$ed."' AND created_id = '".$row["id"]."') as lrdata,

				(SELECT count(*) FROM lhc WHERE date(`created_datetime`) >= '".$sd."' AND date(`created_datetime`) <= '".$ed."' AND created_id = '".$row["id"]."') as lhc_data,

				(SELECT count(*) FROM billing WHERE date(`created_datetime`) >= '".$sd."' AND date(`created_datetime`) <= '".$ed."' AND created_id = '".$row["id"]."') as bill_data,

				(SELECT count(*) FROM mr WHERE date(`created_datetime`) >= '".$sd."' AND date(`created_datetime`) <= '".$ed."' AND created_id = '".$row["id"]."') as mr_data,

				(SELECT count(*) FROM contract WHERE date(`created_datetime`) >= '".$sd."' AND date(`created_datetime`) <= '".$ed."' AND created_id = '".$row["id"]."') as contract_data,

				(SELECT count(*) FROM contract_bill WHERE date(`created_datetime`) >= '".$sd."' AND date(`created_datetime`) <= '".$ed."' AND created_id = '".$row["id"]."') as contract_bill_data,

				(SELECT count(*) FROM payment WHERE date(`created_datetime`) >= '".$sd."' AND date(`created_datetime`) <= '".$ed."' AND created_id = '".$row["id"]."' AND flag='BR') as br_data,

				(SELECT count(*) FROM payment WHERE date(`created_datetime`) >= '".$sd."' AND date(`created_datetime`) <= '".$ed."' AND created_id = '".$row["id"]."' AND flag='BP') as bp_data,

				(SELECT count(*) FROM payment WHERE date(`created_datetime`) >= '".$sd."' AND date(`created_datetime`) <= '".$ed."' AND created_id = '".$row["id"]."' AND flag='CR') as cr_data,

				(SELECT count(*) FROM payment WHERE date(`created_datetime`) >= '".$sd."' AND date(`created_datetime`) <= '".$ed."' AND created_id = '".$row["id"]."' AND flag='CP') as cp_data,

				(SELECT count(*) FROM journal_voucher WHERE date(`created_datetime`) >= '".$sd."' AND date(`created_datetime`) <= '".$ed."' AND created_id = '".$row["id"]."') as jv_data,

				(SELECT count(*) FROM tracking WHERE date(`created_datetime`) >= '".$sd."' AND date(`created_datetime`) <= '".$ed."' AND created_id = '".$row["id"]."') as tracking_data
				";

			$data_res = mysqli_query($conn,$data);
			$data_row = mysqli_fetch_assoc($data_res);

			// UPDATE

			$datas = "SELECT (SELECT count(*) FROM lr_mst WHERE date(`updated_datetime`) >= '".$sd."' AND date(`updated_datetime`) <= '".$ed."' AND updated_id = '".$row["id"]."') as lrdata,

				(SELECT count(*) FROM lhc WHERE date(`updated_datetime`) >= '".$sd."' AND date(`updated_datetime`) <= '".$ed."' AND updated_id = '".$row["id"]."') as lhc_data,

				(SELECT count(*) FROM billing WHERE date(`updated_datetime`) >= '".$sd."' AND date(`updated_datetime`) <= '".$ed."' AND updated_id = '".$row["id"]."') as bill_data,

				(SELECT count(*) FROM mr WHERE date(`updated_datetime`) >= '".$sd."' AND date(`updated_datetime`) <= '".$ed."' AND updated_id = '".$row["id"]."') as mr_data,

				(SELECT count(*) FROM contract WHERE date(`updated_datetime`) >= '".$sd."' AND date(`updated_datetime`) <= '".$ed."' AND updated_id = '".$row["id"]."') as contract_data,

				(SELECT count(*) FROM contract_bill WHERE date(`update_datetime`) >= '".$sd."' AND date(`update_datetime`) <= '".$ed."' AND update_id = '".$row["id"]."') as contract_bill_data,

				(SELECT count(*) FROM payment WHERE date(`updated_datetime`) >= '".$sd."' AND date(`updated_datetime`) <= '".$ed."' AND updated_id = '".$row["id"]."' AND flag='BR') as br_data,

				(SELECT count(*) FROM payment WHERE date(`updated_datetime`) >= '".$sd."' AND date(`updated_datetime`) <= '".$ed."' AND updated_id = '".$row["id"]."' AND flag='BP') as bp_data,

				(SELECT count(*) FROM payment WHERE date(`updated_datetime`) >= '".$sd."' AND date(`updated_datetime`) <= '".$ed."' AND updated_id = '".$row["id"]."' AND flag='CR') as cr_data,

				(SELECT count(*) FROM payment WHERE date(`updated_datetime`) >= '".$sd."' AND date(`updated_datetime`) <= '".$ed."' AND updated_id = '".$row["id"]."' AND flag='CP') as cp_data,

				(SELECT count(*) FROM journal_voucher WHERE date(`updated_datetime`) >= '".$sd."' AND date(`updated_datetime`) <= '".$ed."' AND updated_id = '".$row["id"]."') as jv_data,

				(SELECT count(*) FROM tracking WHERE date(`updated_datetime`) >= '".$sd."' AND date(`updated_datetime`) <= '".$ed."' AND updated_id = '".$row["id"]."') as tracking_data
				";

			$datas_res = mysqli_query($conn,$datas);
			$datas_row = mysqli_fetch_assoc($datas_res);
  			
		}
		$table .= '<tr class="text-center">';
		$table .= '<td>'.$row["name"].'</td>';
		$table .= '<td>'.$data_row['lrdata'].'</td>';
		$table .= '<td>'.$datas_row['lrdata'].'</td>';
		$table .= '<td>'.$data_row['lhc_data'].'</td>';
		$table .= '<td>'.$datas_row['lhc_data'].'</td>';
		$table .= '<td>'.$data_row['bill_data'].'</td>';
		$table .= '<td>'.$datas_row['bill_data'].'</td>';
		$table .= '<td>'.$data_row['mr_data'].'</td>';
		$table .= '<td>'.$datas_row['mr_data'].'</td>';
		$table .= '<td>'.$data_row['contract_data'].'</td>';
		$table .= '<td>'.$datas_row['contract_data'].'</td>';
		$table .= '<td>'.$data_row['contract_bill_data'].'</td>';
		$table .= '<td>'.$datas_row['contract_bill_data'].'</td>';
		$table .= '<td>'.$data_row['br_data'].'</td>';
		$table .= '<td>'.$datas_row['br_data'].'</td>';
		$table .= '<td>'.$data_row['bp_data'].'</td>';
		$table .= '<td>'.$datas_row['bp_data'].'</td>';
		$table .= '<td>'.$data_row['cr_data'].'</td>';
		$table .= '<td>'.$datas_row['cr_data'].'</td>';
		$table .= '<td>'.$data_row['cp_data'].'</td>';
		$table .= '<td>'.$datas_row['cp_data'].'</td>';
		$table .= '<td>'.$data_row['jv_data'].'</td>';
		$table .= '<td>'.$datas_row['jv_data'].'</td>';
		$table .= '<td>'.$data_row['tracking_data'].'</td>';
		$table .= '<td>'.$datas_row['tracking_data'].'</td>';
		$table .= '</tr>';
	}
	
}
$data = array('status' => 'success', 'total_count' => $total, 'tbody' => $table);
	echo json_encode($data);
    die();
?>