/* Filtering - NOTE this does not match the built-in DataTables filtering which does it
* word by word on any field. It's possible to do here, but concerned about efficiency
* on very large tables, and MySQL's regex functionality is very limited
*/
$sWhere = "";
if ($_GET['sSearch'] != "") {
$sWhere = "WHERE ( engine LIKE '%" . mysql_real_escape_string($_GET['sSearch']) . "%' OR " . "browser LIKE '%" . mysql_real_escape_string($_GET['sSearch']) . "%' OR " . "platform LIKE '%" . mysql_real_escape_string($_GET['sSearch']) . "%' OR " . "version LIKE '%" . mysql_real_escape_string($_GET['sSearch']) . "%' OR " . "grade LIKE '%" . mysql_real_escape_string($_GET['sSearch']) . "%' )";
}
for ($i = 0; $i < $_GET['iColumns']; $i++) {
if ($_GET['sSearch_' . $i] != '') {
if ($sWhere != "") {
$sWhere .= " AND ";
} else {
$sWhere .= "WHERE ";
}
$sWhere .= fnColumnToField($i) . " LIKE '%" . mysql_real_escape_string($_GET['sSearch_' . $i]) . "%'";
}
}
$sQuery = "\n\t\tSELECT SQL_CALC_FOUND_ROWS id, engine, browser, platform, version, grade\n\t\tFROM ajax\n\t\t{$sWhere}\n\t\t{$sOrder}\n\t\t{$sLimit}\n\t";
$rResult = mysql_query($sQuery, $gaSql['link']) or fatal_error('MySQL Error: ' . mysql_errno());
$sQuery = "\n\t\tSELECT FOUND_ROWS()\n\t";
$rResultFilterTotal = mysql_query($sQuery, $gaSql['link']) or fatal_error('MySQL Error: ' . mysql_errno());
$aResultFilterTotal = mysql_fetch_array($rResultFilterTotal);
$iFilteredTotal = $aResultFilterTotal[0];
$sQuery = "\n\t\tSELECT COUNT(id)\n\t\tFROM ajax\n\t";
$rResultTotal = mysql_query($sQuery, $gaSql['link']) or fatal_error('MySQL Error: ' . mysql_errno());
$aResultTotal = mysql_fetch_array($rResultTotal);
$iTotal = $aResultTotal[0];
$sOutput = '{';
$sOutput .= '"sEcho": ' . intval($_GET['sEcho']) . ', ';
$sOutput .= '"iTotalRecords": ' . $iTotal . ', ';
$db=new DBConnection();
$searchColumns=array('commodities_groups_id','commodities_groups_name');
$searchLimit='';
if (isset($_GET['iDisplayStart'])&&$_GET['iDisplayLength']!='-1'){
$searchLimit='LIMIT '.$db->string_escape($_GET['iDisplayStart']).', '.$db->string_escape($_GET['iDisplayLength']).'';
}
/* Ordering */
if (isset($_GET['iSortCol_0'])){
$searchOrder="ORDER BY ";
for($i=0; $i<$db->string_escape($_GET['iSortingCols']); $i++ ){
$addComma='';
if($i!=0) $addComma.=', ';
$searchOrder.=$addComma.fnColumnToField($db->string_escape($_GET['iSortCol_'.$i])).' '.$db->string_escape($_GET['iSortDir_'.$i]).'';
}
}
$searchFor='';
if ($_GET['sSearch']!=''){
$searchFor.='WHERE ';
foreach ($searchColumns AS $Count=>$columnToSearch) {
$addOr='';
if($Count!=0) $addOr.=' OR ';
$searchFor.=$addOr.$columnToSearch.' LIKE "%'.$db->string_escape($_GET['sSearch']).'%"';
}
}
$searchQuery='SELECT SQL_CALC_FOUND_ROWS * FROM commodities_groups '.$searchFor.' '.$searchOrder.' '.$searchLimit.'';
$gaSql['user'] = "******";
$gaSql['password'] = "******";
$gaSql['db'] = "mailing";
$gaSql['server'] = "localhost";
$gaSql['link'] = mysql_pconnect($gaSql['server'], $gaSql['user'], $gaSql['password']) or die('Could not open connection to server');
mysql_select_db($gaSql['db'], $gaSql['link']) or die('Could not select database ' . $gaSql['db']);
/* Paging */
$sLimit = "";
if (isset($_GET['iDisplayStart']) && $_GET['iDisplayLength'] != '-1') {
$sLimit = "LIMIT " . mysql_real_escape_string($_GET['iDisplayStart']) . ", " . mysql_real_escape_string($_GET['iDisplayLength']);
}
/* Ordering */
if (isset($_GET['iSortCol_0'])) {
$sOrder = "ORDER BY ";
for ($i = 0; $i < mysql_real_escape_string($_GET['iSortingCols']); $i++) {
$sOrder .= fnColumnToField(mysql_real_escape_string($_GET['iSortCol_' . $i])) . "\n\t\t\t \t" . mysql_real_escape_string($_GET['sSortDir_' . $i]) . ", ";
}
$sOrder = substr_replace($sOrder, "", -2);
}
/* Filtering - NOTE this does not match the built-in DataTables filtering which does it
* word by word on any field. It's possible to do here, but concerned about efficiency
* on very large tables, and MySQL's regex functionality is very limited
*/
$sWhere = "";
if ($_GET['sSearch'] != "") {
$sWhere = "WHERE name LIKE '%" . mysql_real_escape_string($_GET['sSearch']) . "%' OR " . "city LIKE '%" . mysql_real_escape_string($_GET['sSearch']) . "%' OR " . "tel LIKE '%" . mysql_real_escape_string($_GET['sSearch']) . "%' OR " . "email LIKE '%" . mysql_real_escape_string($_GET['sSearch']) . "%' OR " . "www LIKE '%" . mysql_real_escape_string($_GET['sSearch']) . "%'";
}
$sQuery = "\n\t\tSELECT SQL_CALC_FOUND_ROWS idhouses, name, city, tel, email, www\n\t\tFROM houses\n\t\t{$sWhere}\n\t\t{$sOrder}\n\t\t{$sLimit}\n\t";
$rResult = mysql_query($sQuery, $gaSql['link']) or die(mysql_error());
$sQuery = "\n\t\tSELECT FOUND_ROWS()\n\t";
$rResultFilterTotal = mysql_query($sQuery, $gaSql['link']) or die(mysql_error());
function generateServiceReport($conn)
{
global $gTEXT;
require_once 'tcpdf/tcpdf.php';
//require_once('fpdf/fpdi.php');
//$pdf = new FPDI();
$pdf = new TCPDF(PDF_PAGE_ORIENTATION, PDF_UNIT, PDF_PAGE_FORMAT, true, 'UTF-8', false);
$pdf->SetPrintHeader(false);
$pdf->SetPrintFooter(false);
$pdf->SetMargins(PDF_MARGIN_LEFT, PDF_MARGIN_TOP, PDF_MARGIN_RIGHT);
$pdf->SetHeaderMargin(PDF_MARGIN_HEADER);
$pdf->SetFooterMargin(PDF_MARGIN_FOOTER);
$pdf->SetAutoPageBreak(TRUE, PDF_MARGIN_BOTTOM);
$pdf->setImageScale(PDF_IMAGE_SCALE_RATIO);
if (@file_exists(dirname(__FILE__) . '/lang/eng.php')) {
require_once dirname(__FILE__) . '/lang/eng.php';
$pdf->setLanguageArray($l);
}
$pdf->AddPage();
$pdf->SetFillColor(255, 255, 255);
//=====================================================National Inventory Table=======================================================
$Year = $_POST['Year'];
$Month = $_POST['Month'];
$CountryId = $_POST['CountryId'];
$ServiceType = $_POST['ServiceType'];
if ($CountryId) {
$CountryId = " AND a.CountryId = " . $CountryId . " ";
}
$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(mysql_real_escape_string($_POST['iSortCol_' . $i])) . "" . mysql_real_escape_string($_POST['sSortDir_' . $i]) . ", ";
}
$sOrder = substr_replace($sOrder, "", -2);
}
$sWhere = "";
if ($_POST['sSearch'] != "") {
$sWhere = " AND (FacilityName LIKE '%" . mysql_real_escape_string($_POST['sSearch']) . "%'\n OR NewPatient LIKE '%" . mysql_real_escape_string($_POST['sSearch']) . "%'\n OR TotalPatient LIKE '%" . mysql_real_escape_string($_POST['sSearch']) . "%') ";
}
$sql = "SELECT SQL_CALC_FOUND_ROWS a.FacilityId, FacilityName, IFNULL(SUM(a.NewPatient),0) NewPatient, IFNULL(SUM(a.TotalPatient),0) TotalPatient \n FROM t_cfm_patientoverview a\n INNER JOIN t_facility b ON a.FacilityId = b.FacilityId AND b.FLevelId = 99\t\n INNER JOIN t_formulation d ON a.FormulationId = d.FormulationId AND d.ServiceTypeId = " . $ServiceType . "\n INNER JOIN t_cfm_masterstockstatus f ON a.CFMStockId = f.CFMStockId AND StatusId = 5\n WHERE a.MonthId = " . $Month . " AND a.Year = '" . $Year . "' " . $CountryId . " {$sWhere} \n GROUP BY a.FacilityId, FacilityName\n \t{$sOrder} {$sLimit} ";
$result = mysql_query($sql, $conn);
$total = mysql_num_rows($result);
if ($total > 0) {
$data = array();
$f = 0;
$tblHTML = '';
while ($rec = mysql_fetch_array($result)) {
$data['SL'][$f] = $f;
$data['FacilityName'][$f] = $rec['FacilityName'];
$data['TotalPatient'][$f] = $rec['TotalPatient'] == 0 ? '' : $rec['TotalPatient'];
//$data['NewPatient'][$f]=$rec['NewPatient']== 0? '' : $rec['NewPatient'];
$tblHTML .= '<tr style="page-break-inside:avoid;">
<td align="center" width="50" valign="middle">' . ($data['SL'][$f] + 1) . '</td>
<td align="left" width="300" valign="middle">' . $data['FacilityName'][$f] . '</td>
<td align="right" width="300" valign="middle">' . $data['TotalPatient'][$f] . '</td>
</tr>';
//<td align="right" width="200" valign="middle">'.$data['NewPatient'][$f].'</td>
$f++;
}
$Year = $_POST['Year'];
$Month = $_POST['Month'];
$CountryId = $_POST['CountryId'];
$ServiceType = $_POST['ServiceType'];
if ($CountryId) {
$CountryId = " AND a.CountryId = " . $CountryId . " ";
}
$sql = "SELECT SQL_CALC_FOUND_ROWS a.FacilityId, FacilityName, IFNULL(SUM(a.NewPatient),0) NewPatient, IFNULL(SUM(a.TotalPatient),0) TotalPatient \n FROM t_cfm_patientoverview a\n INNER JOIN t_facility b ON a.FacilityId = b.FacilityId AND b.FLevelId = 99\t\n INNER JOIN t_formulation d ON a.FormulationId = d.FormulationId AND d.ServiceTypeId = " . $ServiceType . "\n INNER JOIN t_cfm_masterstockstatus f ON a.CFMStockId = f.CFMStockId AND StatusId = 5\n WHERE a.MonthId = " . $Month . " AND a.Year = '" . $Year . "' " . $CountryId . " \n GROUP BY a.FacilityId, FacilityName";
$result = mysql_query($sql);
$totalPatient = 0;
while ($aRow = mysql_fetch_object($result)) {
$totalPatient = $totalPatient + $aRow->TotalPatient;
}
$Year = $_POST['Year'];
$MonthName = $_POST['MonthName'];
$CountryName = $_POST['CountryName'];
$ServiceTypeName = $_POST['ServiceTypeName'];
$html = '
<!-- EXAMPLE OF CSS STYLE -->
<style>
</style>
<body>
<h4 style="text-align:center;"><b>' . $gTEXT['Facility Service Indicators Report of '] . ' ' . $CountryName . ' on ' . $MonthName . ',' . $Year . '</b></h4>
<h4 style="text-align:center;"><b>' . $gTEXT['Service Type'] . ': ' . $ServiceTypeName . '</b></h4>
<h4 style="text-align:center;"><b>' . $gTEXT['Total Patient'] . ' is ' . number_format($totalPatient) . ' </b></h4>
</body>';
$pdf->SetFont('dejavusans', '', 10);
$pdf->writeHTMLCell(0, 0, 10, 10, $html, '', 0, 0, false, 'C', true);
$html = '
<!-- EXAMPLE OF CSS STYLE -->
<style>
td{
height: 6px;
line-height:3px;
}
th{
height: 20;
font-size:10px;
}
</style>
<body>
<table width="600px" border="0.5" style="margin:0 auto;">
<tr>
<th width="50" align="center"><b>SL</b></th>
<th width="300" align="left"><b>' . $gTEXT['Name of Facility'] . '</b></th>
<th width="300" align="right"><b>' . $gTEXT['Number of Total Patients'] . '</b></th>
</tr>' . $tblHTML . '</table></body>';
//<th width="200" align="right"><b>'.$gTEXT['Number of New Patients'].'</b></th>
$pdf->SetFont('dejavusans', '', 7);
$pdf->writeHTMLCell(0, 0, 10, 40, $html, '', 1, 1, false, 'L', true);
$filePath = SITEDOCUMENT . 'administrator/components/com_jcode/source/report/pdfslice/ServiceReport.pdf';
if (file_exists($filePath)) {
unlink($filePath);
}
$pdf->Output('pdfslice/ServiceReport.pdf', 'F');
echo 'ServiceReport.pdf';
} else {
echo 'Processing Error';
}
}