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

<?php

$columns = array
(
     "tc.id",
     "tc.transaction_charge_no",
     "tc.remark",
     "tc.id",
     "CONCAT( u.firstname, ' ', u.lastname )",
     "tc.a_status",
     "tc.id",
     
     "tc.a_status",
);
 
$table = "tbl_transaction_charge tc";
 
$joins = "

JOIN tbl_user u ON tc.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 tc.transaction_id= '$_GET[transaction_id]' AND tc.a_status in ('1','3','5') {$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))
{
    
$db_transaction_charge_item2_total_nf = 0;
$query_transaction_charge_item2 = mysql_query("SELECT * FROM tbl_transaction_charge_item WHERE transaction_charge_id='$row[0]' AND a_status='1'");
while($row2= mysql_fetch_assoc($query_transaction_charge_item2))
{
$db_transaction_charge_item2_total = $row2['total'];
$db_transaction_charge_item2_total_nf += str_replace(' ','', str_replace(',00','', $db_transaction_charge_item2_total));
    //number format
    include("../include/number_format.php");

}
$row[3]  = "$db_currency_code1$db_transaction_charge_item2_total_nf_nf";
    
if($row[5]==1){
$row[5]="<a href='#' class='tablectrl_small bGreen tipS' title='Options'><i class='fa fa-check'></i> $lang[Active]</a>";    
}
else if($row[5]==3)
{
$row[5]="<a href='#' class='tablectrl_small bRed tipS' title='Options'><i class='fa fa-times'></i> $lang[Deleted]</a>";      
}
else if($row[5]==5)
{
$row[5]="<a href='#' class='tablectrl_small bGold tipS' title='Options'><i class='fa fa-check'></i> $lang[Paid]</a>";      
}

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

echo json_encode($response);
 
?>