<?php session_start(); ?>
<?php include("../include/config.php"); ?>

<?php

$columns = array
(
    "t.id",
    "t.transaction_no",
    "p.id",
    "CONCAT( r.room, '/', b.bed )",
    "rt.room_type",
    "t.a_status",
    "t.id",
    "t.id",
    
    "p.patient_no",
    "p.firstname",
    "p.middlename",
    "p.lastname",
    "p.birth_date",
    
    "t.date",
    "t.time",
    "t.a_status",
);
 
$table = "tbl_transaction t";
 
$joins = "

LEFT JOIN tbl_patient p ON t.patient_id = p.id

LEFT JOIN tbl_patient_type pt ON t.pt_type_id = pt.id

LEFT JOIN tbl_transaction_room tr ON t.id = tr.transaction_id AND tr.a_status=1

LEFT JOIN tbl_room r ON tr.room_id = r.id 

LEFT JOIN tbl_room_type rt ON r.room_type_id = rt.id

LEFT JOIN tbl_bed b ON tr.bed_id = b.id

JOIN tbl_user u ON t.user_id = u.id
 
";
 
// filtering
$sql_where = "";
if ($_GET['sSearch'] != "")
{
    $sql_where = "WHERE ";
    foreach ($columns as $column)
    {
        $sql_where .= $column . " LIKE '%" . mysql_real_escape_string( $_GET['sSearch'] ) . "%' OR ";
    }
    $sql_where = substr($sql_where, 0, -3);
}
 
// ordering
$sql_order = "";
if ( isset( $_GET['iSortCol_0'] ) )
{
    $sql_order = "ORDER BY  ";
    for ( $i = 0; $i < mysql_real_escape_string( $_GET['iSortingCols'] ); $i++ )
    {
        $sql_order .= $columns[$_GET['iSortCol_' . $i]] . " " . mysql_real_escape_string( $_GET['sSortDir_' . $i] ) . ", ";
    }
    $sql_order = substr_replace( $sql_order, "", -2 );
}
 
// paging
$sql_limit = "";
if ( isset( $_GET['iDisplayStart'] ) && $_GET['iDisplayLength'] != '-1' )
{
    $sql_limit = "LIMIT " . mysql_real_escape_string( $_GET['iDisplayStart'] ) . ", " . mysql_real_escape_string( $_GET['iDisplayLength'] );
}
 
$main_query = mysql_query("SELECT  SQL_CALC_FOUND_ROWS " . implode(", ", $columns) . " FROM {$table} {$joins} {$sql_where }AND t.pt_type_id ='1'{$sql_order} {$sql_limit}")
    or die(mysql_error());
 
// get the number of filtered rows
$filtered_rows_query = mysql_query("SELECT FOUND_ROWS()")
    or die(mysql_error());
$row = mysql_fetch_array($filtered_rows_query);
$response['iTotalDisplayRecords'] = $row[0];
 
// get the number of rows in total
$total_query = mysql_query("SELECT COUNT(id) FROM {$table}")
    or die(mysql_error());
$row = mysql_fetch_array($total_query);
$response['iTotalRecords'] = $row[0];
 
// send back the number requested
$response['sEcho'] = intval($_GET['sEcho']);
 
$response['aaData'] = array();
 
// finish getting rows from the main query
while ($row = mysql_fetch_row($main_query))
{
    

$row10_sub = substr($row[10],0,1);
$row[2]="$row[9] $row10_sub. $row[11] (p.id#:$row[8])";

$query_transaction_type2 = mysql_query("SELECT * FROM tbl_transaction_type WHERE id='$row[5]'"); 
while($row2= mysql_fetch_assoc($query_transaction_type2))
{
    $db_transaction_type2_transaction_type= $row2['transaction_type'];
    $db_transaction_type2_color= $row2['color'];
}
$row[5] = "<a href='#' class='tablectrl_small b$db_transaction_type2_color tipS' title='Options'><i class='fa fa-info-circle'></i> $db_transaction_type2_transaction_type</a>";


$seconds = time() - strtotime("$row[13] $row[14]");

$days = floor($seconds / 86400);
$seconds %= 86400;

$hours = floor($seconds / 3600);
$seconds %= 3600;

$minutes = floor($seconds / 60);
$seconds %= 60;

if($row[15]==1){
if($seconds <= 0){
$row[6] ="processing...";
}else{
$row[6] ="<b>$days</b>d <b>$hours</b>h <b>$minutes</b>m"; 
}
}
else
{
$row[6]="<center>-</center>";
}

    $response['aaData'][] = $row;
        
}

echo json_encode($response);
 
?>