<?php

    include "config.php";
	include "functions.php";
	
    makeconnection();

    $aColumns = array('','case_reference', 'case_hdd_brand', 'case_hdd_model', 'case_hdd_interface', 'case_hdd_format', 'case_hdd_capacity', 'case_date_open', 'case_status');
 
    $sLimit = "";
    if (isset($_GET['iDisplayStart']) && $_GET['iDisplayLength'] != '-1')
    {
        $sLimit = "LIMIT " . intval($_GET['iDisplayStart']) . ", " .
            intval($_GET['iDisplayLength']);
    }

    $sOrder = "";
    if (isset($_GET['iSortCol_0']))
    {
        $sOrder = "ORDER BY  ";
        for ($i = 0; $i < intval($_GET['iSortingCols']); $i++)
        {
            if ($_GET['bSortable_' . intval($_GET['iSortCol_' . $i])] == "true")
            {
                $sOrder .= "`" . $aColumns[intval($_GET['iSortCol_' . $i])] . "` " .
                    ($_GET['sSortDir_' . $i] === 'asc' ? 'asc' : 'desc') . ", ";
            }
        }

        $sOrder = substr_replace($sOrder, "", -2);
        if ($sOrder == "ORDER BY")
        {
            $sOrder = "";
        }
    }

    $sWhere = " where case_customer_id = ".$_GET["customer_id"];
    if (isset($_GET['sSearch']) && $_GET['sSearch'] != "")
    {
        $sWhere .= " and (";
        for ($i = 0; $i < count($aColumns); $i++)
        {
            if (isset($_GET['bSearchable_' . $i]) && $_GET['bSearchable_' . $i] == "true")
            {
                $sWhere .= "`" . $aColumns[$i] . "` LIKE '%" . mysql_real_escape_string($_GET['sSearch']) . "%' OR ";
            }
        }
        $sWhere = substr_replace($sWhere, "", -3);
        $sWhere .= ')';
    }

    /* Individual column filtering */
    for ($i = 0; $i < count($aColumns); $i++)
    {
        if (isset($_GET['bSearchable_' . $i]) && $_GET['bSearchable_' . $i] == "true" && $_GET['sSearch_' . $i] != '')
        {
            if ($sWhere == "")
            {
                $sWhere = "WHERE ";
            }
            else
            {
                $sWhere .= " AND ";
            }
            $sWhere .= "`" . $aColumns[$i] . "` LIKE '%" . mysql_real_escape_string($_GET['sSearch_' . $i]) . "%' ";
        }
    }


    $sQuery = "SELECT SQL_CALC_FOUND_ROWS * FROM `case`  
		$sWhere
		$sOrder
		$sLimit
		";
    $rResult = mysql_query($sQuery) or fatal_error('MySQL Error: ' . mysql_errno());
  
    /* Data set length after filtering */
    $sQuery = "SELECT FOUND_ROWS()";
    $rResultFilterTotal = mysql_query($sQuery) or fatal_error('MySQL Error: ' . mysql_errno());
    $aResultFilterTotal = mysql_fetch_array($rResultFilterTotal);
    $iFilteredTotal = $aResultFilterTotal[0];

    /* Total data set length */
    $sQuery = "SELECT COUNT(`case_id`)  FROM `case` ";
    $rResultTotal = mysql_query($sQuery) or fatal_error('MySQL Error: ' . mysql_errno());
    $aResultTotal = mysql_fetch_array($rResultTotal);
    $iTotal = $aResultTotal[0];

    /*
     * Output
     */
    $output = array(
        "sEcho" => intval($_GET['sEcho']),
        "iTotalRecords" => $iTotal,
        "iTotalDisplayRecords" => $iFilteredTotal,
        "aaData" => array()
    );

    while ($aRow = mysql_fetch_array($rResult))
    {
        $row = array();
        
        $case_id = $aRow["case_id"];
        $case_status = $aRow["case_status"];
        $case_customer_id = $aRow["case_customer_id"];
        
        switch($case_status)
		{
			case "Open"			: $case_status_class = "label-info"; break;
			case "Resolved"		: $case_status_class = "label-success"; break;
			case "Not resolved"	: $case_status_class = "label-warning"; break;	
		}
        
        $row[] = '<span class="row-details row-details-close"></span>'; 
        $row[] = '<a href="./?p=case_add&case_id='.$case_id.'&customer_id='.$case_customer_id.'">'.$aRow["case_reference"].'</a>';       
        $row[] = $aRow["case_hdd_brand"];
		$row[] = $aRow["case_hdd_model"];
        $row[] = $aRow["case_hdd_interface"];
        $row[] = $aRow["case_hdd_format"];
        $row[] = $aRow["case_hdd_capacity"];
        $row[] = date("d/m/Y H:i:s", $aRow["case_date_open"]);
        $row[] = '<span class="label label-sm '.$case_status_class.'"> '.$case_status.' </span>';
        $row[] = '<input type="checkbox" name="chk_case_id[]" class="checkboxes" value="'.$case_id.'"/>';
        
        $row[] = $aRow["case_type"];
        $row[] = $aRow["case_hdd_rpm"];
        $row[] = $aRow["case_hdd_sn"] == NULL ? "/" : $aRow["case_hdd_sn"];
        $row[] = $aRow["case_hdd_parameters"] == NULL ? "/" : $aRow["case_hdd_parameters"];
        $row[] = $aRow["case_case_description"] == NULL ? "/" : $aRow["case_case_description"];
        $row[] = $aRow["case_solution_description"] == NULL ? "/" : $aRow["case_solution_description"];
        $row[] = $aRow["case_date_close"] == NULL ? "/" : date("d/m/Y H:i:s", $aRow["case_date_close"]);
        $row[] = $aRow["case_remark"] == NULL ? "/" : $aRow["case_remark"];
        
        $output['aaData'][] = $row;
    }

    echo json_encode($output);
    