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);