Example #1
0
function getFormulationData($conn)
{
    global $gTEXT;
    $lan = $_POST['lan'];
    if ($lan == 'en-GB') {
        $GroupName = 'GroupName';
        $ServiceTypeName = 'ServiceTypeName';
    } else {
        $GroupName = 'GroupNameFrench';
        $ServiceTypeName = 'ServiceTypeNameFrench';
    }
    $condition = '';
    $sWhere = "";
    $itemGroupId = $_POST['itemGroupId'];
    if ($itemGroupId != 0) {
        $sWhere = ' WHERE ';
        $condition .= " a.ItemGroupId = '" . $itemGroupId . "' ";
    }
    $sLimit = "";
    if (isset($_POST['iDisplayStart'])) {
        $sLimit = " LIMIT " . mysql_real_escape_string($_POST['iDisplayStart']) . ", " . mysql_real_escape_string($_POST['iDisplayLength']);
    }
    $sOrder = "";
    if (isset($_POST['iSortCol_0'])) {
        $sOrder = " ORDER BY  ";
        for ($i = 0; $i < mysql_real_escape_string($_POST['iSortingCols']); $i++) {
            $sOrder .= fnColumnToField_formulation(mysql_real_escape_string($_POST['iSortCol_' . $i])) . "\n\t\t\t\t\t\t\t\t" . mysql_real_escape_string($_POST['sSortDir_' . $i]) . ", ";
        }
        $sOrder = substr_replace($sOrder, "", -2);
    }
    if ($_POST['sSearch'] != "") {
        if ($sWhere == '') {
            $sWhere = " WHERE ";
        } else {
            $condition .= " and ";
        }
        $condition .= "   (FormulationName LIKE '%" . mysql_real_escape_string($_POST['sSearch']) . "%'\n                    OR {$ServiceTypeName} LIKE '%" . mysql_real_escape_string($_POST['sSearch']) . "%'\n                    OR {$GroupName} LIKE '%" . mysql_real_escape_string($_POST['sSearch']) . "%'\n                    OR ColorCode LIKE '%" . mysql_real_escape_string($_POST['sSearch']) . "%') ";
    }
    $sql = "SELECT SQL_CALC_FOUND_ROWS FormulationId, FormulationName,FormulationNameFrench, a.ItemGroupId,{$GroupName} GroupName, a.ServiceTypeId,{$ServiceTypeName} ServiceTypeName, ColorCode\n\t\t\t\tFROM t_formulation a\n                INNER JOIN t_itemgroup b ON a.ItemGroupId = b.ItemGroupId\n                INNER JOIN t_servicetype c ON a.ServiceTypeId = c.ServiceTypeId\n                {$sWhere} {$condition} {$sOrder} {$sLimit} ";
    //echo $sql;
    $result = mysql_query($sql, $conn);
    $total = mysql_num_rows($result);
    $sQuery = "SELECT FOUND_ROWS()";
    $rResultFilterTotal = mysql_query($sQuery);
    $aResultFilterTotal = mysql_fetch_array($rResultFilterTotal);
    $iFilteredTotal = $aResultFilterTotal[0];
    $sOutput = '{';
    $sOutput .= '"sEcho": ' . intval($_POST['sEcho']) . ', ';
    $sOutput .= '"iTotalRecords": ' . $iFilteredTotal . ', ';
    $sOutput .= '"iTotalDisplayRecords": ' . $iFilteredTotal . ', ';
    $sOutput .= '"aaData": [ ';
    $serial = $_POST['iDisplayStart'] + 1;
    $y = "<a class='task-del itmEdit' href='javascript:void(0);'><span class='label label-info'>" . $gTEXT['Edit'] . "</span></a>";
    $z = "<a class='task-del itmDrop' style='margin-left:4px' href='javascript:void(0);'><span class='label label-danger'>" . $gTEXT['Delete'] . "</span></a>";
    $f = 0;
    while ($aRow = mysql_fetch_array($result)) {
        $FormulationName = crnl2br($aRow['FormulationName']);
        $GroupName = crnl2br($aRow['GroupName']);
        $ServiceTypeName = crnl2br($aRow['ServiceTypeName']);
        $ColorCode = mysql_real_escape_string('<span style="width:30px;height:15px;display:block;align:center;background:' . $aRow['ColorCode'] . ';"></span>');
        if ($f++) {
            $sOutput .= ',';
        }
        $sOutput .= "[";
        $sOutput .= '"' . $aRow['FormulationId'] . '",';
        //addslashes
        $sOutput .= '"' . $serial++ . '",';
        $sOutput .= '"' . $FormulationName . '",';
        //******* $FormulationName. '",';
        $sOutput .= '"' . $aRow['FormulationNameFrench'] . '",';
        $sOutput .= '"' . $aRow['GroupName'] . '",';
        $sOutput .= '"' . $aRow['ServiceTypeName'] . '",';
        $sOutput .= '"' . $ColorCode . '",';
        $sOutput .= '"' . $y . $z . '",';
        $sOutput .= '"' . $aRow['ItemGroupId'] . '",';
        $sOutput .= '"' . $aRow['ServiceTypeId'] . '",';
        $sOutput .= '"' . $aRow['ColorCode'] . '"';
        $sOutput .= "]";
    }
    $sOutput .= '] }';
    echo $sOutput;
}
$ItemGroupName = $_GET['ItemGroupName'];
$condition = '';
$sWhere = "";
if ($itemGroupId != 0) {
    $sWhere = ' WHERE ';
    $condition .= " a.ItemGroupId = '" . $itemGroupId . "' ";
}
$sLimit = "";
if (isset($_GET['iDisplayStart'])) {
    $sLimit = " LIMIT " . mysql_real_escape_string($_GET['iDisplayStart']) . ", " . mysql_real_escape_string($_GET['iDisplayLength']);
}
$sOrder = "";
if (isset($_GET['iSortCol_0'])) {
    $sOrder = " ORDER BY  ";
    for ($i = 0; $i < mysql_real_escape_string($_GET['iSortingCols']); $i++) {
        $sOrder .= fnColumnToField_formulation(mysql_real_escape_string($_GET['iSortCol_' . $i])) . "\n\t\t\t\t\t\t\t\t" . mysql_real_escape_string($_GET['sSortDir_' . $i]) . ", ";
    }
    $sOrder = substr_replace($sOrder, "", -2);
}
if ($_GET['sSearch'] != "") {
    if ($sWhere == '') {
        $sWhere = " WHERE ";
    } else {
        $condition .= " and ";
    }
    $condition .= "   (FormulationName LIKE '%" . mysql_real_escape_string($_GET['sSearch']) . "%'\n                    OR ServiceTypeName LIKE '%" . mysql_real_escape_string($_GET['sSearch']) . "%'\n                    OR GroupName LIKE '%" . mysql_real_escape_string($_GET['sSearch']) . "%'\n                    OR ColorCode LIKE '%" . mysql_real_escape_string($_GET['sSearch']) . "%') ";
}
$sql = "SELECT SQL_CALC_FOUND_ROWS FormulationId, FormulationName,FormulationNameFrench, a.ItemGroupId, GroupName, a.ServiceTypeId, ServiceTypeName, ColorCode\n\t\t\t\tFROM t_formulation a\n                INNER JOIN t_itemgroup b ON a.ItemGroupId = b.ItemGroupId\n                INNER JOIN t_servicetype c ON a.ServiceTypeId = c.ServiceTypeId\n                {$sWhere} {$condition} {$sOrder} {$sLimit} ";
mysql_query("SET character_set_results=utf8");
$r = mysql_query($sql);
$total = mysql_num_rows($r);