public function save_design()
{
if ($this->input->post('mode') == 'edit') {
$this->db->delete('designs', array('id' => $this->input->post('design_id')));
}
$faceMacket = str_replace('http://klever.media/', '', $this->input->post('faceMacket'));
$backMacket = str_replace('http://klever.media/', '', $this->input->post('backMacket'));
$face = $this->input->post('face');
$back = $this->input->post('back');
// get all fonts
$query = $this->db->get('fonts');
$fonts = array();
foreach ($query->result() as $font) {
$fonts[$font->family] = $font->source;
}
// generate pdf face template name and preview name
$face_pdf = 'uploads/redactor/face_' . md5(microtime(true)) . '.pdf';
$face_preview = 'uploads/redactor/face_' . md5(microtime(true)) . '.jpg';
// convert face image to pdf
$img = new Imagick($faceMacket);
$img->setresolution(300, 300);
$img->setcolorspace(Imagick::COLORSPACE_CMYK);
$img->resizeimage(1076, 720, Imagick::FILTER_LANCZOS, 1);
$img->setimageformat('pdf');
$img->writeimage($face_pdf);
// include TCPDF ana FPDI
include_once APPPATH . 'libraries/tcpdf/tcpdf.php';
include_once APPPATH . 'libraries/tcpdf/fpdi.php';
include_once APPPATH . 'libraries/tcpdf/include/tcpdf_fonts.php';
$fontMaker = new TCPDF_FONTS();
// создаём лист
$pdf = new FPDI('L', 'mm', array(91, 61), true, 'UTF-8', false);
$pdf->SetMargins(0, 0, 0, true);
$pdf->AddPage('L');
// загрузим ранее сохранённый шаблон
$pdf->setSourceFile($face_pdf);
$pdf->SetMargins(0, 0, 0, true);
$tplIdx = $pdf->importPage(1);
$pdf->useTemplate($tplIdx, null, null, 0, 0, true);
// установим опции для pdf
$pdf->setCellHeightRatio(1);
$pdf->setCellPaddings(0, 0, 0, 0);
$pdf->setCellMargins(0, 0, 0, 0);
$pdf->SetAutoPageBreak(false, 0);
$pdf->SetPrintHeader(false);
$pdf->SetPrintFooter(false);
if (!empty($face)) {
// отрисуем сначала изображения лица
foreach ($face as $item) {
if ($item['type'] == 'image') {
$pdf->Image($_SERVER['DOCUMENT_ROOT'] . '/' . str_replace('http://klever.media/', '', $item['content']), $this->px_to_mm($item['left']), $this->px_to_mm($item['top']), $this->px_to_mm($item['width']), '', '', '', '', false, 300);
}
}
// потом текст на лице
foreach ($face as $item) {
if ($item['type'] == 'text') {
$cmyk = $this->rgbToCmyk($item['color']);
$pdf->SetTextColor($cmyk['c'] * 100, $cmyk['m'] * 100, $cmyk['y'] * 100, $cmyk['k'] * 100);
// set font
$tcpdfFont = $fontMaker->addTTFfont(realpath('fonts/redactor/' . $fonts[$item['font']]));
$pdf->SetFont($tcpdfFont, '', $item['size'] / 2, '', 'false');
$pdf->Text($this->px_to_mm($item['left']), $this->px_to_mm($item['top']), $item['content'], false, false, true, 0, 0, 'L', false, '', 0, false, 'T', 'L', false);
}
}
}
// сохраним пдф лица
$pdf->Output($_SERVER['DOCUMENT_ROOT'] . '/' . $face_pdf, 'F');
// сделаем превью для пользователя
$im = new Imagick();
$im->setResolution(300, 300);
$im->readimage($face_pdf . '[0]');
$im->flattenimages();
$im->setImageFormat('jpg');
$im->resizeimage(1076, 720, Imagick::FILTER_LANCZOS, 1);
$im->writeImage($face_preview);
$im->clear();
$im->destroy();
//exec('$ convert ' . $_SERVER['DOCUMENT_ROOT'] . '/' . $face_pdf . ' ' . $_SERVER['DOCUMENT_ROOT'] . '/' . $face_preview);
// есть ли оборот
if (!empty($backMacket)) {
// generate pdf back template name and preview name
$back_pdf = 'uploads/redactor/back_' . md5(microtime(true)) . '.pdf';
$back_preview = 'uploads/redactor/back_' . md5(microtime(true)) . '.jpg';
// convert back image to pdf
$img = new Imagick($backMacket);
$img->setresolution(300, 300);
$img->setcolorspace(Imagick::COLORSPACE_CMYK);
$img->resizeimage(1076, 720, Imagick::FILTER_LANCZOS, 1);
$img->setimageformat('pdf');
$img->writeimage($back_pdf);
// создаём лист
$pdf = new FPDI('L', 'mm', array(91, 61), true, 'UTF-8', false);
$pdf->AddPage('L');
// загрузим ранее сохранённый шаблон
$pdf->setSourceFile($_SERVER['DOCUMENT_ROOT'] . '/' . $back_pdf);
$pdf->SetMargins(0, 0, 0, true);
$tplIdx = $pdf->importPage(1);
$pdf->useTemplate($tplIdx, null, null, 0, 0, true);
// установим опции для pdf
$pdf->SetMargins(0, 0, 0, true);
$pdf->setCellHeightRatio(1);
$pdf->setCellPaddings(0, 0, 0, 0);
$pdf->setCellMargins(1, 1, 1, 1);
$pdf->SetAutoPageBreak(false);
$pdf->SetPrintHeader(false);
$pdf->SetPrintFooter(false);
if (!empty($back)) {
// отрисуем сначала изображения оборота
foreach ($back as $item) {
if ($item['type'] == 'image') {
$pdf->Image($_SERVER['DOCUMENT_ROOT'] . '/' . str_replace('http://klever.media/', '', $item['content']), $this->px_to_mm($item['left']), $this->px_to_mm($item['top']), $this->px_to_mm($item['width']), '', '', '', '', false, 300);
}
}
// потом текст на обороте
foreach ($back as $item) {
if ($item['type'] == 'text') {
$cmyk = $this->rgbToCmyk($item['color']);
$pdf->SetTextColor($cmyk['c'] * 100, $cmyk['m'] * 100, $cmyk['y'] * 100, $cmyk['k'] * 100);
// set font
$tcpdfFont = $fontMaker->addTTFfont($_SERVER['DOCUMENT_ROOT'] . '/fonts/redactor/' . $fonts[$item['font']]);
$pdf->SetFont($tcpdfFont, '', $item['size'] / 2, '', 'false');
$pdf->Text($this->px_to_mm($item['left']), $this->px_to_mm($item['top']), $item['content'], false, false, true, 0, 0, 'L', false, '', 0, false, 'T', 'L', false);
}
}
}
// сохраним пдф оборота
$pdf->Output($_SERVER['DOCUMENT_ROOT'] . '/' . $back_pdf, 'F');
// сделаем превью для пользователя
$im = new Imagick();
$im->setResolution(300, 300);
$im->readimage($back_pdf . '[0]');
$im->setImageFormat('jpg');
$im->flattenimages();
$im->resizeimage(1076, 720, Imagick::FILTER_LANCZOS, 1);
$im->writeImage($back_preview);
$im->clear();
$im->destroy();
}
$this->db->insert('products', array('name' => 'cards_pvc', 'tiraj' => 100, 'weight' => 0.5, 'price' => 0.49, 'cost' => 49, 'macket' => 'my_macket'));
$product_id = $this->db->insert_id();
$this->db->insert('designs', array('product_id' => $product_id, 'theme_id' => $this->input->post('theme'), 'face_background' => $faceMacket, 'back_background' => empty($backMacket) ? NULL : $backMacket, 'face' => $face_preview, 'back' => empty($back_preview) ? '' : $back_preview, 'type' => 'system'));
$design_id = $this->db->insert_id();
$options = array();
if (!empty($face)) {
foreach ($face as $item) {
if ($item['type'] == 'text') {
$options[] = array('design_id' => $design_id, 'type' => 'text', 'front' => 'face', 'top' => $item['top'], 'left' => $item['left'], 'width' => NULL, 'height' => NULL, 'content' => $item['content'], 'font' => $item['font'], 'color' => $item['color'], 'size' => $item['size']);
} else {
$options[] = array('design_id' => $design_id, 'type' => 'image', 'front' => 'face', 'top' => $item['top'], 'left' => $item['left'], 'width' => $item['width'], 'height' => $item['height'], 'content' => $item['content'], 'font' => NULL, 'color' => NULL, 'size' => NULL);
}
}
}
if (!empty($back)) {
foreach ($back as $item) {
if ($item['type'] == 'text') {
$options[] = array('design_id' => $design_id, 'type' => 'text', 'front' => 'back', 'top' => $item['top'], 'left' => $item['left'], 'width' => NULL, 'height' => NULL, 'content' => $item['content'], 'font' => $item['font'], 'color' => $item['color'], 'size' => $item['size']);
} else {
$options[] = array('design_id' => $design_id, 'type' => 'image', 'front' => 'back', 'top' => $item['top'], 'left' => $item['left'], 'width' => $item['width'], 'height' => $item['height'], 'content' => $item['content'], 'font' => NULL, 'color' => NULL, 'size' => NULL);
}
}
}
if (count($options) > 0) {
$this->db->insert_batch('design_options', $options);
}
echo 'OK';
}
function generatePatientTrendReport($conn)
{
global $gTEXT;
$CountryName = $_POST['CountryName'];
require_once 'tcpdf/tcpdf.php';
require_once 'fpdf/fpdi.php';
$pdf = new FPDI();
$pdf->SetPrintHeader(false);
$pdf->SetPrintFooter(false);
$pdf->AddPage();
$pdf->SetFillColor(255, 255, 255);
$StartMonthId = $_POST['StartMonthId'];
$StartYearId = $_POST['StartYearId'];
$EndMonthId = $_POST['EndMonthId'];
$EndYearId = $_POST['EndYearId'];
$frequencyId = 1;
if ($_POST['MonthNumber'] != 0) {
$months = $_POST['MonthNumber'];
$monthIndex = date("m");
$yearIndex = date("Y");
settype($yearIndex, "integer");
if ($monthIndex == 1) {
$monthIndex = 12;
$yearIndex = $yearIndex - 1;
} else {
$monthIndex = $monthIndex - 1;
}
$months = $months - 1;
$d = cal_days_in_month(CAL_GREGORIAN, $monthIndex, $yearIndex);
$EndYearMonth = $yearIndex . "-" . str_pad($monthIndex, 2, "0", STR_PAD_LEFT) . "-" . $d;
$EndYearMonth = date('Y-m-d', strtotime($EndYearMonth));
$StartYearMonth = $yearIndex . "-" . str_pad($monthIndex, 2, "0", STR_PAD_LEFT) . "-" . "01";
$StartYearMonth = date('Y-m-d', strtotime($StartYearMonth));
$StartYearMonth = date("Y-m-d", strtotime(date("Y-m-d", strtotime($StartYearMonth)) . "-" . $months . " month"));
} else {
$startDate = $StartYearId . "-" . $StartMonthId . "-" . "01";
$StartYearMonth = date('Y-m-d', strtotime($startDate));
$d = cal_days_in_month(CAL_GREGORIAN, $EndMonthId, $EndYearId);
$endDate = $EndYearId . "-" . $EndMonthId . "-" . $d;
$EndYearMonth = date('Y-m-d', strtotime($endDate));
}
$monthListShort = array(1 => 'Jan', 2 => 'Feb', 3 => 'Mar', 4 => 'Apr', 5 => 'May', 6 => 'Jun', 7 => 'Jul', 8 => 'Aug', 9 => 'Sep', 10 => 'Oct', 11 => 'Nov', 12 => 'Dec');
$quarterList = array(3 => 'Jan-Mar', 6 => 'Apr-Jun', 9 => 'Jul-Sep', 12 => 'Oct-Dec');
$output = array('aaData' => array());
$aData = array();
$output2 = array();
if ($frequencyId == 1) {
$monthQuarterList = $monthListShort;
} else {
$monthQuarterList = $quarterList;
}
$month_list = array();
$startDate = strtotime($StartYearMonth);
$endDate = strtotime($EndYearMonth);
$index = 0;
// while ($endDate >= $startDate) {
// $month_list[$index] = date('M Y',$startDate);
// $index++;
// $startDate = strtotime( date('Y/m/d',$startDate).' 1 month');
// }
while ($endDate >= $startDate) {
if ($frequencyId == 1) {
$monthid = date('m', $startDate);
settype($monthid, "integer");
$ym = $monthListShort[$monthid] . ' ' . date('Y', $startDate);
$month_list[$index] = $ym;
$output['Categories'][] = $ym;
$index++;
} else {
$monthid = date('m', $startDate);
settype($monthid, "integer");
if ($monthid == 3 || $monthid == 6 || $monthid == 9 || $monthid == 12) {
$ym = $quarterList[$monthid] . ' ' . date('Y', $startDate);
$month_list[$index] = $ym;
$output['Categories'][] = $ym;
$index++;
}
}
$startDate = strtotime(date('Y/m/d', $startDate) . ' 1 month');
}
$html = '
<!-- EXAMPLE OF CSS STYLE -->
<style>
</style>
<body>
<h4 style="text-align:left;"><b>' . $gTEXT['Patient Trend Time Series Report of'] . ' ' . $CountryName . ' ' . $gTEXT['from'] . ' ' . date('M,Y', strtotime($StartYearMonth)) . ' ' . $gTEXT['to'] . ' ' . date('M,Y', strtotime($EndYearMonth)) . '</b></h4>
</body>';
$pdf->writeHTMLCell(0, 0, 17, '', $html, '', 1, 1, false, 'L', true, $spacing = 0);
$pdf->setSourceFile("pdfslice/PatientTrendChart.pdf");
$tplIdx = $pdf->importPage(1);
$pdf->useTemplate($tplIdx, 0, 0, 200);
//=====================================================Patient Trend Time Series Table=======================================================
$lan = $_REQUEST['lan'];
$countryId = $_POST['Country'];
$itemGroupId = $_POST['ItemGroupId'];
//$frequencyId = 1;// $_POST['FrequencyId'];
if ($lan == 'en-GB') {
$serviceTypeName = 'ServiceTypeName';
} else {
$serviceTypeName = 'ServiceTypeNameFrench';
}
// //////////////////
$sQuery = "SELECT a.ServiceTypeId, IFNULL(SUM(c.TotalPatient),0) TotalPatient\n\t\t\t, {$serviceTypeName} ServiceTypeName, a.STL_Color,c.Year,c.MonthId\n FROM t_servicetype a\n INNER JOIN t_formulation b ON a.ServiceTypeId = b.ServiceTypeId\n Inner JOIN t_cnm_patientoverview c \t\n\t\t\t\t\tON (c.FormulationId = b.FormulationId \n\t\t\t\t\t\tand STR_TO_DATE(concat(year,'/',monthid,'/02'), '%Y/%m/%d') \n\t\t\t\t\t\tbetween '" . $StartYearMonth . "' and '" . $EndYearMonth . "'\n \t\tAND (c.CountryId = " . $countryId . " OR " . $countryId . " = 0)\n\t\t\t\t\t\tAND (c.ItemGroupId = " . $itemGroupId . " OR " . $itemGroupId . " = 0)) \t\t \n GROUP BY a.ServiceTypeId, {$serviceTypeName}, a.STL_Color\n\t\t\t\t, c.Year, c.MonthId\n\t\t\t\tHAVING TotalPatient > 0\n\t\t ORDER BY a.ServiceTypeId asc,c.Year asc, c.MonthId asc;";
//echo $sQuery;
$rResult = safe_query($sQuery);
$total = mysql_num_rows($rResult);
$tmpServiceTypeId = -1;
$countServiceType = 1;
$count = 1;
$preServiceTypeName = '';
if ($total == 0) {
return;
}
//echo 'Rubel';
if ($total > 0) {
while ($row = mysql_fetch_assoc($rResult)) {
if (!is_null($row['TotalPatient'])) {
settype($row['TotalPatient'], "integer");
}
if ($tmpServiceTypeId != $row['ServiceTypeId']) {
if ($count > 1) {
array_unshift($output2, $countServiceType, $preServiceTypeName);
$aData[] = $output2;
unset($output2);
$countServiceType++;
}
$count++;
$preServiceTypeName = $row['ServiceTypeName'];
$count = 0;
while ($count < count($month_list)) {
$output2[] = null;
$count++;
}
$dataMonthYear = $monthQuarterList[$row['MonthId']] . ' ' . $row['Year'];
$count = 0;
while ($count < count($month_list)) {
if ($month_list[$count] == $dataMonthYear) {
$output2[$count] = $row['TotalPatient'];
}
$count++;
}
$tmpServiceTypeId = $row['ServiceTypeId'];
} else {
$dataMonthYear = $monthQuarterList[$row['MonthId']] . ' ' . $row['Year'];
$count = 0;
while ($count < count($month_list)) {
if ($month_list[$count] == $dataMonthYear) {
$output2[$count] = $row['TotalPatient'];
}
$count++;
}
$tmpServiceTypeId = $row['ServiceTypeId'];
}
}
array_unshift($output2, $countServiceType, $preServiceTypeName);
$aData[] = $output2;
//print_r($month_list);
$col = '<tr><th width="20" align="center"><b>SL</b></th>';
$col .= '<th width="35" align="left"><b>' . $gTEXT['Patient Type'] . '</b></th>';
$f = 0;
for ($f = 0; $f < count($month_list); $f++) {
$col .= '<th width="30" align="right"><b>' . $month_list[$f] . '</b></th>';
}
$col .= '</tr>';
$p = 0;
for ($p = 0; $p < count($aData); $p++) {
$col .= '<tr>';
for ($i = 0; $i < count($aData[$p]); $i++) {
$col .= '<td>' . $aData[$p][$i] . '</td>';
}
$col .= '</tr>';
}
$i = 1;
/* $col = '<tr><th width="38" align="center"><b>SL</b></th>';
$col.= '<th width="38" align="left"><b>'.$gTEXT['Patient Type'].'</b></th>';
$f=0;
for($f = 0; $f<count($rmonth_name); $f++){
$col.= '<th width="38" align="right"><b>'.$rmonth_name[$f].'</b></th>';
}
$col.='</tr><tr>';
$x=0;
for($x = 0; $x<count($art); $x++){
$col.= '<td width="38" align="right"><b>'.$art[$x].'</b></td>';
}
$col.='</tr><tr>';
$x=0;
for($x = 0; $x<count($rtk); $x++){
$col.= '<td width="38" align="right"><b>'.$rtk[$x].'</b></td>';
}
$col.='</tr><tr>';
$x=0;
for($x = 0; $x<count($pmtct); $x++){
$col.= '<td width="38" align="right"><b>'.$pmtct[$x].'</b></td>';
}
$col.='</tr>'; */
$html_head = "<span><b>" . $gTEXT['Patient Trend Time Series Data List'] . "</b></span>";
$pdf->SetFont('dejavusans', '', 9);
$pdf->writeHTMLCell(0, 0, 17, 125, $html_head, '', 0, 0, false, 'L', true);
$html = '
<!-- EXAMPLE OF CSS STYLE -->
<style>
td{
height: 6px;
line-height:3px;
}
</style>
<body>
<table width="550px" border="0.5" style="margin:0px auto;">' . $col . '</table></body>';
$pdf->SetFont('dejavusans', '', 7);
$pdf->writeHTMLCell(0, 0, 15, 140, $html, '', 1, 1, false, 'C', true);
$filePath = SITEDOCUMENT . 'administrator/components/com_jcode/source/report/pdfslice/PatientTrendReport.pdf';
if (file_exists($filePath)) {
unlink($filePath);
}
$pdf->Output('pdfslice/PatientTrendReport.pdf', 'F');
echo 'PatientTrendReport.pdf';
} else {
echo 'Processing Error';
}
}
function generateFundingStatusReport($conn)
{
global $gTEXT;
global $pdf;
$ItemGroup = $_POST['ItemGroup'];
$lan = $_POST['lan'];
if ($lan == 'en-GB') {
$SITETITLE = SITETITLEENG;
} else {
$SITETITLE = SITETITLEFRN;
}
$CountryName = $_POST['CountryName'];
$Year = $_POST['Year'];
require_once 'tcpdf/tcpdf.php';
require_once 'fpdf/fpdi.php';
$pdf = new FPDI();
$pdf->SetPrintHeader(false);
$pdf->SetPrintFooter(false);
$pdf->AddPage($orientation = L, $format = 'Letter', $keepmargins = false, $tocpage = false);
$pdf->SetFillColor(255, 255, 255);
$html_head = "<span style='text-align:center;font-size:10px;'><b>" . $SITETITLE . "</b></span><br>\n\t<span style='text-align:center;font-size:10px;'><b>" . $gTEXT['Funding Status Report of'] . " " . $CountryName . " " . $gTEXT['on'] . " " . $Year . "</b></span><br>\n\t<span style='text-align:center;font-size:10px;'><b>" . $gTEXT['Product Group'] . ": " . $ItemGroup . "</b></span>";
$html = '
<!-- EXAMPLE OF CSS STYLE -->
<style>
</style>
<body>
</body>';
$pdf->writeHTMLCell(0, 0, 15, '', $html_head, '', 1, 1, false, 'C', true, $spacing = 0);
$pdf->setSourceFile("pdfslice/FundingStatusChart.pdf");
$tplIdx = $pdf->importPage(1);
$pdf->useTemplate($tplIdx, 0, 0, 500);
}
function generateStockStatusReport($conn)
{
global $gTEXT;
$MonthName = $_POST['MonthName'];
$CountryName = $_POST['CountryName'];
require_once 'tcpdf/tcpdf.php';
require_once 'fpdf/fpdi.php';
$pdf = new FPDI();
$pdf->SetPrintHeader(false);
$pdf->SetPrintFooter(false);
//$pdf->SetAutoPageBreak(true, 1);
//$pdf->SetAutoPageBreak(TRUE, PDF_MARGIN_BOTTOM);
$pdf->AddPage();
$pdf->SetFillColor(255, 255, 255);
$html_head = "<span style='text-align:center;font-size:10px;'><b>" . $gTEXT['Stock Status at Different Level Report of'] . " " . $CountryName . " on " . $MonthName . ", " . $Year . "</b></span>";
$html = <<<EOF
<!-- EXAMPLE OF CSS STYLE -->
<style>
</style>
<body>
</body>
EOF;
$pdf->writeHTMLCell(0, 0, 40, '', $html_head, '', 1, 1, false, 'L', true);
$pdf->setSourceFile("pdfslice/StockStatusChart.pdf");
$tplIdx = $pdf->importPage(1);
$pdf->useTemplate($tplIdx, 6, 20, 200, 300);
$pdf->endPage();
//=====================================================Stock List Table=======================================================
$Year = $_POST['Year'];
$ItemGroupId = $_POST['ItemGroup'];
$Month = $_POST['Month'];
$CountryId = $_POST['Country'];
$ownnerTypeId = $_POST['OwnnerTypeId'];
$lan = $_REQUEST['lan'];
if ($lan == 'en-GB') {
$fLevelName = 'FLevelName';
} else {
$fLevelName = 'FLevelNameFrench';
}
if ($CountryId) {
$CountryId = " AND a.CountryId = " . $CountryId . " ";
}
$columnList = array();
$productName = 'Product Name';
$aData = array();
if ($ownnerTypeId == 1 || $ownnerTypeId == 2) {
$sQuery = "SELECT f.FLevelId, {$fLevelName} FLevelName, a.ItemNo, b.ItemName, f.ColorCode, IFNULL(SUM(ClStock),0) FacilitySOH, IFNULL(SUM(AMC),0) FacilityAMC\n\t\t\t, IFNULL(((SUM(ClStock))/(SUM(AMC))),0) MOS \n FROM t_cfm_stockstatus a \n INNER JOIN t_itemlist b ON a.ItemNo = b.ItemNo AND b.bKeyItem = 1 AND b.ItemGroupId = " . $ItemGroupId . "\n INNER JOIN t_cfm_masterstockstatus c ON a.CFMStockId = c.CFMStockId and c.StatusId = 5 AND c.ItemGroupId = " . $ItemGroupId . "\n INNER JOIN t_facility d ON a.FacilityId = d.FacilityId\n INNER JOIN t_facility_group_map e ON d.FacilityId = e.FacilityId AND e.ItemGroupId = " . $ItemGroupId . "\n INNER JOIN t_facility_level f ON d.FLevelId = f.FLevelId\n WHERE a.MonthId = " . $Month . " AND a.Year = '" . $Year . "' " . $CountryId . "\n\t\t\tAND d.OwnerTypeId = " . $ownnerTypeId . "\n GROUP BY f.FLevelId, {$fLevelName}, ItemNo, ItemName, f.ColorCode\n HAVING IFNULL(((SUM(ClStock))/(SUM(AMC))),0)>0\n\t\t\torder by ItemName,f.FLevelId;";
} else {
$sQuery = "SELECT f.FLevelId, {$fLevelName} FLevelName, a.ItemNo, b.ItemName, f.ColorCode, IFNULL(SUM(ClStock),0) FacilitySOH, IFNULL(SUM(AMC),0) FacilityAMC\n\t\t\t\t, IFNULL(((SUM(ClStock))/(SUM(AMC))),0) MOS \n\t\t\t\tFROM t_cfm_stockstatus a \n\t\t\t\tINNER JOIN t_itemlist b ON a.ItemNo = b.ItemNo AND b.bKeyItem = 1 AND b.ItemGroupId = " . $ItemGroupId . "\n\t\t\t\tINNER JOIN t_cfm_masterstockstatus c ON a.CFMStockId = c.CFMStockId and c.StatusId = 5 AND c.ItemGroupId = " . $ItemGroupId . "\n\t\t\t\tINNER JOIN t_facility d ON a.FacilityId = d.FacilityId\n\t\t\t\tINNER JOIN t_facility_group_map e ON d.FacilityId = e.FacilityId AND e.ItemGroupId = " . $ItemGroupId . "\n\t\t\t\tINNER JOIN t_facility_level f ON d.FLevelId = f.FLevelId\n\t\t\t\tWHERE a.MonthId = " . $Month . " AND a.Year = '" . $Year . "' " . $CountryId . "\n\t\t\t\tAND d.AgentType = " . $ownnerTypeId . "\n\t\t\t\tGROUP BY f.FLevelId, {$fLevelName}, ItemNo, ItemName, f.ColorCode\n\t\t\t\tHAVING IFNULL(((SUM(ClStock))/(SUM(AMC))),0)>0\n\t\t\t\torder by ItemName,f.FLevelId;";
}
//echo $sQuery;
$rResult = safe_query($sQuery);
$total = mysql_num_rows($rResult);
$tmpItemName = '';
$sl = 1;
$count = 0;
$preItemName = '';
//echo 'Rubel';
if ($total > 0) {
$data = array();
$headerList = array();
while ($row = mysql_fetch_assoc($rResult)) {
$data[] = $row;
}
foreach ($data as $row) {
////Duplicate value not push in array
//if (!in_array($row['FLevelName'], $headerList)) {
// $headerList[] = $row['FLevelName'];
//}
$headerList[$row['FLevelId']] = $row['FLevelName'];
}
//array_push($headerList,'National');
$headerList[999] = 'National';
foreach ($headerList as $key => $value) {
$columnList[] = $value;
//.' Level AMC';
$columnList[] = $value;
//.' Level SOH';
$columnList[] = $value;
//.' Level MOS';
}
$fetchDataList = array();
foreach ($data as $row) {
if ($tmpItemName != $row['ItemName']) {
if ($count > 0) {
$fetchDataList['999' . '2'] = number_format($fetchDataList['999' . '2']);
$fetchDataList['999' . '3'] = number_format($fetchDataList['999' . '3'], 1);
array_unshift($fetchDataList, $sl, $preItemName);
$aData[] = $fetchDataList;
$sl++;
}
$count++;
$preItemName = $row['ItemName'];
unset($fetchDataList);
foreach ($headerList as $key => $value) {
$fetchDataList[$key . '1'] = NULL;
$fetchDataList[$key . '2'] = NULL;
$fetchDataList[$key . '3'] = NULL;
}
$tmpItemName = $row['ItemName'];
}
$fLevelId = $row['FLevelId'];
$fetchDataList[$fLevelId . '1'] = number_format($row['FacilityAMC']);
$fetchDataList[$fLevelId . '2'] = number_format($row['FacilitySOH']);
$fetchDataList[$fLevelId . '3'] = number_format($row['MOS'], 1);
if ($fetchDataList['999' . '1'] < $row['FacilityAMC']) {
$fetchDataList['999' . '1'] = number_format($row['FacilityAMC']);
}
$fetchDataList['999' . '2'] += $row['FacilitySOH'];
$fetchDataList['999' . '3'] += $row['MOS'];
}
$fetchDataList['999' . '2'] = number_format($fetchDataList['999' . '2']);
$fetchDataList['999' . '3'] = number_format($fetchDataList['999' . '3'], 1);
array_unshift($fetchDataList, $sl, $preItemName);
$aData[] = $fetchDataList;
$col = '';
$col .= ' <tr><th rowspan="2" style="text-align:center; width:5%;"><b>SL</b></th>
<th rowspan="2" style="text-align:center; width:10%;"><b>' . $gTEXT['Product Name'] . '</b></th>';
$Header = '-1';
for ($i = 0; $i < count($columnList); $i++) {
if ($Header != $columnList[$i]) {
$col .= '<th colspan="3" style="text-align:center;width:90px;"><b>' . $columnList[$i] . '</b></th>';
$Header = $columnList[$i];
}
}
$index = 0;
$col .= '</tr><tr>';
for ($i = 0; $i < count($columnList); $i++) {
$index++;
if ($index == 1) {
$col .= '<th style="text-align:left; ">AMC</th>';
} else {
if ($index == 2) {
$col .= '<th style="text-align:left; ">SOH</th>';
} else {
if ($index == 3) {
$col .= '<th style="text-align:left; ">' . $gTEXT['MOS'] . '</th>';
}
}
}
if ($index == 3) {
$index = 0;
}
}
$col .= '</tr>';
$data = '';
for ($p = 0; $p < count($aData); $p++) {
$data .= '<tr>';
for ($i = 0; $i < count($aData[$p]); $i++) {
$data .= '<td>' . $aData[$p][$i] . '</td>';
}
$data .= '</tr>';
}
$pdf->startPage();
$html_head = "<span><b>" . $gTEXT['Stock Status at Different Level Data List'] . "</b></span>";
$pdf->SetFont('dejavusans', '', 9);
$pdf->writeHTMLCell(0, 0, 3, 10, $html_head, '', 0, 0, false, 'C', true);
$html = '
<!-- EXAMPLE OF CSS STYLE -->
<style>
td{
height: 6px;
line-height:3px;
}
th{
height:20;
font-size:6px;
}
</style>
<body>
<table width="510px" border="0.5" style="margin:0 auto;">
' . $col . '' . $data . '</table>
</body>';
$pdf->SetFont('dejavusans', '', 6);
$pdf->writeHTMLCell(0, 0, '', 20, $html, '', 1, 1, false, 'L', true);
$pdf->endPage();
$filePath = SITEDOCUMENT . 'administrator/components/com_jcode/source/report/pdfslice/StockStatusatDifferentLevelReport.pdf';
if (file_exists($filePath)) {
unlink($filePath);
}
$pdf->Output('pdfslice/StockStatusatDifferentLevelReport.pdf', 'F');
echo 'StockStatusatDifferentLevelReport.pdf';
} else {
echo 'Processing Error';
}
}
/**
* Creates a personalized exam file.
*
* @param unknown $examid
* @return NULL
*/
function emarking_download_exam($examid, $multiplepdfs = false, $groupid = null, progress_bar $pbar = null, $sendprintorder = false, $idprinter = null, $printanswersheet = false, $debugprinting = false)
{
global $DB, $CFG, $USER, $OUTPUT;
require_once $CFG->dirroot . '/mod/emarking/lib/openbub/ans_pdf_open.php';
// Validate emarking exam object
if (!($downloadexam = $DB->get_record('emarking_exams', array('id' => $examid)))) {
throw new Exception(get_string("invalidexamid", "mod_emarking"));
}
// Contexto del curso para verificar permisos
$context = context_course::instance($downloadexam->course);
if (!has_capability('mod/emarking:downloadexam', $context)) {
throw new Exception(get_string("invalidaccess", "mod_emarking"));
}
// Verify that remote printing is enable, otherwise disable a printing order
if ($sendprintorder && (!$CFG->emarking_enableprinting || $idprinter == null)) {
throw new Exception('Printing is not enabled or printername was absent ' . $idprinter);
}
// Validate course
if (!($course = $DB->get_record('course', array('id' => $downloadexam->course)))) {
throw new Exception(get_string("invalidcourse", "mod_emarking"));
}
// Validate course category
if (!($coursecat = $DB->get_record('course_categories', array('id' => $course->category)))) {
throw new Exception(get_string("invalidcategoryid", "mod_emarking"));
}
// We tell the user we are setting up the printing
if ($pbar) {
$pbar->update(0, 1, get_string('settingupprinting', 'mod_emarking'));
}
// Default value for enrols that will be included
if ($CFG->emarking_enrolincludes && strlen($CFG->emarking_enrolincludes) > 1) {
$enrolincludes = $CFG->emarking_enrolincludes;
}
// If the exam sets enrolments, we use those
if (isset($downloadexam->enrolments) && strlen($downloadexam->enrolments) > 1) {
$enrolincludes = $downloadexam->enrolments;
}
// Convert enrolments to array
$enrolincludes = explode(",", $enrolincludes);
// Produce all PDFs first separatedly
$filedir = $CFG->dataroot . "/temp/emarking/{$context->id}";
$fileimg = $filedir . "/qr";
$userimgdir = $filedir . "/u";
$pdfdir = $filedir . "/pdf";
emarking_initialize_directory($filedir, true);
emarking_initialize_directory($fileimg, true);
emarking_initialize_directory($userimgdir, true);
emarking_initialize_directory($pdfdir, true);
// Get all the files uploaded as forms for this exam
$fs = get_file_storage();
$files = $fs->get_area_files($context->id, 'mod_emarking', 'exams', $examid);
// We filter only the PDFs
$pdffileshash = array();
foreach ($files as $filepdf) {
if ($filepdf->get_mimetype() === 'application/pdf') {
$pdffileshash[] = array('hash' => $filepdf->get_pathnamehash(), 'filename' => $filepdf->get_filename(), 'path' => emarking_get_path_from_hash($filedir, $filepdf->get_pathnamehash()));
}
}
// Verify that at least we have a PDF
if (count($pdffileshash) < 1) {
throw new Exception(get_string("examhasnopdf", "mod_emarking"));
}
$students = emarking_get_students_for_printing($downloadexam->course);
$studentinfo = array();
$currenttemplate = 0;
// Fill studentnames with student info (name, idnumber, id and picture)
foreach ($students as $student) {
$studentenrolments = explode(",", $student->enrol);
// Verifies that the student is enrolled through a valid enrolment and that we haven't added her yet
if (count(array_intersect($studentenrolments, $enrolincludes)) == 0 || isset($studentinfo[$student->id])) {
continue;
}
// We create a student info object
$studentobj = new stdClass();
$studentobj->name = substr("{$student->lastname}, {$student->firstname}", 0, 65);
$studentobj->idnumber = $student->idnumber;
$studentobj->id = $student->id;
$studentobj->picture = emarking_get_student_picture($student, $userimgdir);
// Store student info in hash so every student is stored once
$studentinfo[$student->id] = $studentobj;
}
// We validate the number of students as we are filtering by enrolment
// type after getting the data
$numberstudents = count($studentinfo);
if ($numberstudents == 0) {
throw new Exception('No students to print/create the exam');
}
// Add the extra students to the list
for ($i = $numberstudents; $i < $numberstudents + $downloadexam->extraexams; $i++) {
$studentobj = new stdClass();
$studentobj->name = '..............................................................................';
$studentobj->idnumber = 0;
$studentobj->id = 0;
$studentobj->picture = $CFG->dirroot . "/pix/u/f1.png";
$studentinfo[] = $studentobj;
}
// Check if there is a logo file
$logofilepath = emarking_get_logo_file($filedir);
// If asked to do so we create a PDF witht the students list
if ($downloadexam->printlist == 1) {
$pdf = new FPDI();
$pdf->SetPrintHeader(false);
$pdf->SetPrintFooter(false);
emarking_draw_student_list($pdf, $logofilepath, $downloadexam, $course, $studentinfo);
$studentlistpdffile = $pdfdir . "/000-studentslist.pdf";
$pdf->Output($studentlistpdffile, "F");
// se genera el nuevo pdf
$pdf = null;
}
// Here we produce a PDF file for each student
$currentstudent = 0;
foreach ($studentinfo as $stinfo) {
// If we have a progress bar, we notify the new PDF being created
if ($pbar) {
$pbar->update($currentstudent + 1, count($studentinfo), $stinfo->name);
}
// We create the PDF file
$pdf = new FPDI();
$pdf->SetPrintHeader(false);
$pdf->SetPrintFooter(false);
// We use the next form available from the list of PDF forms sent
if ($currenttemplate >= count($pdffileshash) - 1) {
$currenttemplate = 0;
} else {
$currenttemplate++;
}
// Load the PDF from the filesystem as template
$path = $pdffileshash[$currenttemplate]['path'];
$originalpdfpages = $pdf->setSourceFile($path);
$pdf->SetAutoPageBreak(false);
// Add all pages in the template, adding the header if it corresponds
for ($pagenumber = 1; $pagenumber <= $originalpdfpages + $downloadexam->extrasheets; $pagenumber++) {
// Adding a page
$pdf->AddPage();
// If the page is not an extra page, we import the page from the template
if ($pagenumber <= $originalpdfpages) {
$template = $pdf->importPage($pagenumber);
$pdf->useTemplate($template, 0, 0, 0, 0, true);
}
// If we have a personalized header, we add it
if ($downloadexam->headerqr) {
emarking_draw_header($pdf, $stinfo, $downloadexam->name, $pagenumber, $fileimg, $logofilepath, $course, $originalpdfpages + $downloadexam->extrasheets);
}
}
// The filename will be the student id - course id - page number
$qrstringtmp = $stinfo->id > 0 ? "{$stinfo->id}-{$course->id}-{$pagenumber}" : "NN{$currentstudent}+1-{$course->id}-{$pagenumber}";
// Create the PDF file for the student
$pdffile = $pdfdir . "/" . $qrstringtmp . ".pdf";
$pdf->Output($pdffile, "F");
// Store the exam file for printing later
$stinfo->examfile = $pdffile;
$stinfo->number = $currentstudent + 1;
$stinfo->pdffilename = $qrstringtmp;
$currentstudent++;
}
$sqlprinter = "SELECT id, name, command\n\t\t\tFROM {emarking_printers}\n\t\t\tWHERE id = ?";
$printerinfo = $DB->get_record_sql($sqlprinter, array($idprinter));
// If we have to print directly
$debugprintingmsg = '';
if ($sendprintorder) {
// Check if we have to print the students list
if ($downloadexam->printlist == 1) {
$printresult = emarking_print_file($printerinfo->name, $printerinfo->command, $studentlistpdffile, $debugprinting);
if (!$printresult) {
$debugprintingmsg .= 'Problems printing ' . $studentlistpdffile . '<hr>';
} else {
$debugprintingmsg .= $printresult . '<hr>';
}
}
// Print each student
$currentstudent = 0;
foreach ($studentinfo as $stinfo) {
$currentstudent++;
if ($pbar != null) {
$pbar->update($currentstudent, count($studentinfo), get_string('printing', 'mod_emarking') . ' ' . $stinfo->name);
}
if (!isset($stinfo->examfile) || !file_exists($stinfo->examfile)) {
continue;
}
$printresult = emarking_print_file($printerinfo->name, $printerinfo->command, $stinfo->examfile, $debugprinting);
if (!$printresult) {
$debugprintingmsg .= 'Problems printing ' . $stinfo->examfile . '<hr>';
} else {
$debugprintingmsg .= $printresult . '<hr>';
}
}
if ($CFG->debug || $debugprinting) {
echo $debugprintingmsg;
}
// Notify everyone that the exam was printed
emarking_send_examprinted_notification($downloadexam, $course);
$downloadexam->status = EMARKING_EXAM_SENT_TO_PRINT;
$downloadexam->printdate = time();
$DB->update_record('emarking_exams', $downloadexam);
return true;
}
$examfilename = emarking_clean_filename($course->shortname, true) . "_" . emarking_clean_filename($downloadexam->name, true);
$zipdebugmsg = '';
if ($multiplepdfs) {
$zip = new ZipArchive();
$zipfilename = $filedir . "/" . $examfilename . ".zip";
if ($zip->open($zipfilename, ZipArchive::CREATE) !== true) {
throw new Exception('Could not create zip file');
}
// Check if we have to print the students list
if ($downloadexam->printlist == 1) {
$zip->addFile($studentlistpdffile);
}
// Add every student PDF to zip file
$currentstudent = 0;
foreach ($studentinfo as $stinfo) {
$currentstudent++;
if ($pbar != null) {
$pbar->update($currentstudent, count($studentinfo), get_string('printing', 'mod_emarking') . ' ' . $stinfo->name);
}
if (!isset($stinfo->examfile) || !file_exists($stinfo->examfile)) {
continue;
}
if (!$zip->addFile($stinfo->examfile, $stinfo->pdffilename . '.pdf')) {
$zipdebugmsg .= "Problems adding {$stinfo->examfile} to ZIP file using name {$stinfo->pdffilename} <hr>";
}
}
$zip->close();
if ($CFG->debug || $debugprinting) {
echo $zipdebugmsg;
}
// Notify everyone that the exam was downloaded
emarking_send_examdownloaded_notification($downloadexam, $course);
$downloadexam->status = EMARKING_EXAM_SENT_TO_PRINT;
$downloadexam->printdate = time();
$DB->update_record('emarking_exams', $downloadexam);
// Read zip file from disk and send to the browser
$file_name = basename($zipfilename);
header("Content-Type: application/zip");
header("Content-Disposition: attachment; filename=" . $examfilename . ".zip");
header("Content-Length: " . filesize($zipfilename));
readfile($zipfilename);
exit;
}
// We create the final big PDF file
$pdf = new FPDI();
$pdf->SetPrintHeader(false);
$pdf->SetPrintFooter(false);
// We import the students list if required
if ($downloadexam->printlist) {
emarking_import_pdf_into_pdf($pdf, $studentlistpdffile);
}
// Add every student PDF to zip file
$currentstudent = 0;
foreach ($studentinfo as $stinfo) {
$currentstudent++;
if (!isset($stinfo->examfile) || !file_exists($stinfo->examfile)) {
continue;
}
emarking_import_pdf_into_pdf($pdf, $stinfo->examfile);
}
// Notify everyone that the exam was downloaded
emarking_send_examdownloaded_notification($downloadexam, $course);
$downloadexam->status = EMARKING_EXAM_SENT_TO_PRINT;
$downloadexam->printdate = time();
$DB->update_record('emarking_exams', $downloadexam);
$pdf->Output($examfilename . '.pdf', 'D');
}
function generateNationalSummaryReport($conn)
{
global $gTEXT;
$Year = $_POST['Year'];
$MonthName = $_POST['MonthName'];
$CountryName = $_POST['CountryName'];
require_once 'tcpdf/tcpdf.php';
require_once 'fpdf/fpdi.php';
$pdf = new FPDI();
$pdf->SetPrintHeader(false);
$pdf->SetPrintFooter(false);
$pdf->AddPage();
$pdf->SetFillColor(255, 255, 255);
$html_head = "<span style='text-align:center;'><b>" . $gTEXT['National Stock Summary Report of'] . " " . $CountryName . " On " . $MonthName . ", " . $Year . "</b></span>";
$html = '
<!-- EXAMPLE OF CSS STYLE -->
<style>
</style>
<body>
<h4 style="text-align:center;"><b>' . $gTEXT['National Stock Summary Report of'] . ' ' . $CountryName . ' On ' . $MonthName . ',' . $Year . '</b></h4>
</body>';
$pdf->writeHTMLCell(0, 0, 30, '', $html_head, '', 1, 1, false, 'C', true, $spacing = 0);
$pdf->setSourceFile("pdfslice/NationalSummaryChart.pdf");
$tplIdx = $pdf->importPage(1);
$pdf->useTemplate($tplIdx, 6, 0, 200);
//=====================================================Summary List Table=======================================================
if ($Month == '1') {
$MonthName = "January";
} elseif ($Month == '2') {
$MonthName = "February";
} elseif ($Month == '3') {
$MonthName = "March";
} elseif ($Month == '4') {
$MonthName = "April";
} elseif ($Month == '5') {
$MonthName = "May";
} elseif ($Month == '6') {
$MonthName = "June";
} elseif ($Month == '7') {
$MonthName = "July";
} elseif ($Month == '8') {
$MonthName = "August";
} elseif ($Month == '9') {
$MonthName = "September";
} elseif ($Month == '10') {
$MonthName = "October";
} elseif ($Month == '11') {
$MonthName = "November";
} elseif ($Month == '12') {
$MonthName = "December";
}
$Year = $_POST['Year'];
$ItemGroupId = $_POST['ItemGroupId'];
$Month = $_POST['Month'];
$MonthName = $_POST['MonthName'];
$CountryId = $_POST['Country'];
$CountryName = $_POST['CountryName'];
$sql = " SELECT a.ItemNo, b.ItemName, SUM(DispenseQty) ReportedConsumption, SUM(ClStock) ReportedClosingBalance, SUM(AMC) AMC, IFNULL(((SUM(ClStock))/(SUM(AMC))),0) MOS \n \tFROM t_cnm_stockstatus a \n INNER JOIN t_itemlist b ON a.ItemNo = b.ItemNo AND b.bKeyItem = 1 AND b.ItemGroupId = " . $ItemGroupId . "\n \tINNER JOIN t_cnm_masterstockstatus c ON a.CNMStockId = c.CNMStockId AND a.CountryId = c.CountryId AND c.StatusId = 5 AND c.ItemGroupId = " . $ItemGroupId . "\n \t\tWHERE a.MonthId = " . $Month . " AND a.Year = " . $Year . "\n AND (a.CountryId = " . $CountryId . " OR " . $CountryId . " = 0) \t\n \tGROUP BY ItemNo, ItemName \n \tHAVING IFNULL(((SUM(ClStock))/(SUM(AMC))),0)>0";
$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['ItemName'][$f] = $rec['ItemName'];
//$data['ReportedConsumption'][$f]=number_format($rec['ReportedConsumption']);
$data['ReportedClosingBalance'][$f] = number_format($rec['ReportedClosingBalance']);
$data['AMC'][$f] = number_format($rec['AMC']);
$data['MOS'][$f] = number_format($rec['MOS'], 1);
$tblHTML .= '<tr style="page-break-inside:avoid;">
<td align="center" width="30" valign="middle">' . ($data['SL'][$f] + 1) . '</td>
<td align="left" width="200" valign="middle">' . $data['ItemName'][$f] . '</td>
<td align="right" width="90" valign="middle">' . $data['ReportedClosingBalance'][$f] . '</td>
<td align="right" width="120" valign="middle">' . $data['AMC'][$f] . '</td>
<td align="right" width="60" valign="middle">' . $data['MOS'][$f] . '</td>
</tr>';
$f++;
//<td align="right" width="90" valign="middle">'.$data['ReportedConsumption'][$f].'</td>
}
$html_head = "<span><b>" . $gTEXT['National Stock Summary List'] . "</b></span>";
$pdf->SetFont('dejavusans', '', 10);
$pdf->writeHTMLCell(0, 0, 10, 160, $html_head, '', 0, 0, false, 'C', true);
$html = '
<!-- EXAMPLE OF CSS STYLE -->
<style>
td{
height: 6px;
line-height:3px;
}
</style>
<body>
<table width="450px" border="0.5" style="margin:0 auto;">
<tr>
<th width="30" align="center"><b>SL</b></th>
<th width="200" align="left"><b>' . $gTEXT['Products'] . '</b></th>
<th width="90" align="right"><b>' . $gTEXT['Reported Closing Balance'] . '</b></th>
<th width="120" align="right"><b>' . $gTEXT['Average Monthly Consumption'] . '</b></th>
<th width="60" align="right"><b>' . $gTEXT['MOS'] . '</b></th>
</tr>' . $tblHTML . '</table></body>';
//<th width="90" align="right"><b>'.$gTEXT['Reported Consumption'].'</b></th>
$pdf->SetFont('dejavusans', '', 7);
$pdf->writeHTMLCell(0, 0, '', 170, $html, '', 1, 1, false, 'L', true);
$filePath = SITEDOCUMENT . 'administrator/components/com_jcode/source/report/pdfslice/NationalSummaryPage.pdf';
if (file_exists($filePath)) {
unlink($filePath);
}
$pdf->Output('pdfslice/NationalSummaryPage.pdf', 'F');
echo 'NationalSummaryPage.pdf';
} else {
echo 'Processing Error';
}
}
function generateStockoutTrendReport($conn)
{
global $gTEXT;
$CountryId = $_POST['Country'];
$months = $_POST['MonthNumber'];
$StartMonthId = $_POST['StartMonthId'];
$EndMonthId = $_POST['EndMonthId'];
$StartYearId = $_POST['StartYearId'];
$EndYearId = $_POST['EndYearId'];
$CountryName = $_POST['CountryName'];
$MonthName = $_GET['MonthName'];
if ($_POST['MonthNumber'] != 0) {
$months = $_POST['MonthNumber'];
$monthIndex = date("m");
$yearIndex = date("Y");
if ($monthIndex == 1) {
$monthIndex = 12;
$yearIndex = $yearIndex - 1;
} else {
$monthIndex = $monthIndex - 1;
$endDate = $yearIndex . "-" . $monthIndex . "-" . "01";
$startDate = date("Y-m-d", strtotime(date("Y-m-d", strtotime($endDate)) . "+" . -($months - 1) . " month"));
}
} else {
$startDate = $StartYearId . "-" . $StartMonthId . "-" . "01";
$endDate = $EndYearId . "-" . $EndMonthId . "-" . "01";
$months = getMonthsBtnTwoDate($startDate, $endDate) + 1;
$monthIndex = $EndMonthId;
$yearIndex = $EndYearId;
}
settype($yearIndex, "integer");
$month_name = array();
$Tdetails = array();
$sumRiskCount = array();
$sumTR = 0;
require_once 'tcpdf/tcpdf.php';
require_once 'fpdf/fpdi.php';
$pdf = new FPDI();
$pdf->SetPrintHeader(false);
$pdf->SetPrintFooter(false);
$pdf->AddPage();
$pdf->SetFillColor(255, 255, 255);
$html_head = '
<!-- EXAMPLE OF CSS STYLE -->
<style>
</style>
<body>
<h4 style="text-align:left;"><b>' . $gTEXT['Stockout trend Report of '] . ' ' . $CountryName . ' ' . $gTEXT['from'] . ' ' . date('M,Y', strtotime($startDate)) . ' ' . $gTEXT['to'] . ' ' . date('M,Y', strtotime($endDate)) . '</b></h4>
</body>';
$pdf->writeHTMLCell(0, 0, 12, '', $html_head, '', 1, 1, false, 'L', true, $spacing = 0);
$pdf->setSourceFile("pdfslice/StockoutTrendChart.pdf");
print_r();
$tplIdx = $pdf->importPage(1);
$pdf->useTemplate($tplIdx, -5, 0, 200);
//=====================================================Stockout Trend Table=======================================================
for ($i = 1; $i <= $months; $i++) {
$sql = " SELECT v.MosTypeId, MosTypeName, ColorCode, IFNULL(RiskCount,0) RiskCount FROM\n \t\t (SELECT p.MosTypeId, COUNT(*) RiskCount FROM (\n SELECT a.ItemNo, a.MOS,(SELECT MosTypeId FROM t_mostype x WHERE a.MOS >= x.MinMos AND a.MOS < x.MaxMos) MosTypeId\n\t\t\t\t FROM t_cnm_stockstatus a\n\t\t\t\t WHERE a.MOS IS NOT NULL AND a.MonthId = " . $monthIndex . " AND Year = " . $yearIndex . " AND (CountryId = " . $CountryId . " OR " . $CountryId . " = 0)) p \n\t\t\t\t GROUP BY p.MosTypeId) u\n\t\t\t\t RIGHT JOIN t_mostype v ON u.MosTypeId = v.MosTypeId\n\t\t\t\t GROUP BY v.MosTypeId";
mysql_query("SET character_set_results=utf8");
$result = mysql_query($sql);
$total = mysql_num_rows($result);
$Pdetails = array();
if ($total > 0) {
while ($aRow = mysql_fetch_array($result)) {
$Pdetails['MosTypeId'] = $aRow['MosTypeId'];
$Pdetails['MonthIndex'] = $monthIndex;
$Pdetails['MosTypeName'] = $aRow['MosTypeName'];
$Pdetails['RiskCount'] = $aRow['RiskCount'];
array_push($Tdetails, $Pdetails);
}
$mn = date("M", mktime(0, 0, 0, $monthIndex, 1, 0));
$mn = $mn . " " . $yearIndex;
array_push($month_name, $mn);
}
$monthIndex--;
if ($monthIndex == 0) {
$monthIndex = 12;
$yearIndex = $yearIndex - 1;
}
}
$veryHighRisk = array();
$highRisk = array();
$mediumRisk = array();
$lowRisk = array();
$noRisk = array();
$areaName = array();
$rmonth_name = array_reverse($month_name);
$RTdetails = array_reverse($Tdetails);
foreach ($RTdetails as $key => $value) {
$MosTypeId = $value['MosTypeId'];
$MonthIndex = $value['MonthIndex'];
$MosTypeName = $value['MosTypeName'];
$RiskCount = $value['RiskCount'];
if ($MosTypeId == 1) {
array_push($veryHighRisk, $RiskCount);
array_push($areaName, $MosTypeName);
} else {
if ($MosTypeId == 2) {
array_push($highRisk, $RiskCount);
array_push($areaName, $MosTypeName);
} else {
if ($MosTypeId == 3) {
array_push($mediumRisk, $RiskCount);
array_push($areaName, $MosTypeName);
} else {
if ($MosTypeId == 4) {
array_push($lowRisk, $RiskCount);
array_push($areaName, $MosTypeName);
} else {
if ($MosTypeId == 5) {
array_push($noRisk, $RiskCount);
array_push($areaName, $MosTypeName);
}
}
}
}
}
}
$vhr = array();
$hr = array();
$mr = array();
$lr = array();
$nr = array();
for ($i = 0; $i < count($veryHighRisk); $i++) {
$sumOfRiskCount = $veryHighRisk[$i] + $highRisk[$i] + $mediumRisk[$i] + $lowRisk[$i] + $noRisk[$i];
if ($sumOfRiskCount == 0) {
$sumOfRiskCount = 1;
}
$newPercentVHR = number_format($veryHighRisk[$i] * 100 / $sumOfRiskCount, 1);
$newPercentHR = number_format($highRisk[$i] * 100 / $sumOfRiskCount, 1);
$newPercentMR = number_format($mediumRisk[$i] * 100 / $sumOfRiskCount, 1);
$newPercentLR = number_format($lowRisk[$i] * 100 / $sumOfRiskCount, 1);
$newPercentNR = number_format($noRisk[$i] * 100 / $sumOfRiskCount, 1);
array_push($vhr, $newPercentVHR . "%");
array_push($hr, $newPercentHR . "%");
array_push($mr, $newPercentMR . "%");
array_push($lr, $newPercentLR . "%");
array_push($nr, $newPercentNR . "%");
}
$unique = array_reverse(array_unique($areaName));
array_unshift($vhr, "1", $unique[0]);
array_unshift($hr, "2", $unique[1]);
array_unshift($mr, "3", $unique[2]);
array_unshift($lr, "4", $unique[3]);
array_unshift($nr, "5", $unique[4]);
$col = '';
$col = '<tr><th width="38" align="left"><b>SL</b></th>';
$col .= '<th width="38" align="left"><b>' . $gTEXT['MOS Type Name'] . '</b></th>';
$f = 0;
for ($f = 0; $f < count($rmonth_name); $f++) {
$col .= '<th width="38" align="right"><b>' . $rmonth_name[$f] . '</b></th>';
}
$col .= '</tr><tr>';
$x = 0;
for ($x = 0; $x < count($vhr); $x++) {
$col .= '<td width="38" align="left"><b>' . $vhr[$x] . '</b></td>';
}
$col .= '</tr><tr>';
$x = 0;
for ($x = 0; $x < count($hr); $x++) {
$col .= '<td width="38" align="left"><b>' . $hr[$x] . '</b></td>';
}
$col .= '</tr><tr>';
$x = 0;
for ($x = 0; $x < count($mr); $x++) {
$col .= '<td width="38" align="left"><b>' . $mr[$x] . '</b></td>';
}
$col .= '</tr><tr>';
$x = 0;
for ($x = 0; $x < count($lr); $x++) {
$col .= '<td width="38" align="left"><b>' . $lr[$x] . '</b></td>';
}
$col .= '</tr><tr>';
$x = 0;
for ($x = 0; $x < count($nr); $x++) {
$col .= '<td width="38" align="left"><b>' . $nr[$x] . '</b></td>';
}
$col .= '</tr>';
$html_head = "<span><b>" . $gTEXT['Stockout Trend Data List'] . "</b></span>";
$pdf->SetFont('dejavusans', '', 9);
$pdf->writeHTMLCell(0, 0, 12, 110, $html_head, '', 0, 0, false, 'L', true);
$html = '
<!-- EXAMPLE OF CSS STYLE -->
<style>
td{
height: 6px;
line-height:3px;
}
</style>
<body>
<table width="550px" border="0.5" style="margin:0px auto;">' . $col . '</table></body>';
$pdf->SetFont('dejavusans', '', 7);
$pdf->writeHTMLCell(0, 0, 10, 120, $html, '', 1, 1, false, 'C', true);
$filePath = SITEDOCUMENT . 'administrator/components/com_jcode/source/report/pdfslice/StockoutTrendReport.pdf';
if (file_exists($filePath)) {
unlink($filePath);
}
$pdf->Output('pdfslice/StockoutTrendReport.pdf', 'F');
echo 'StockoutTrendReport.pdf';
}
function generatePipelineReport($conn)
{
global $gTEXT;
$year = $_POST['YearId'];
$MonthName = $_POST['MonthName'];
$CountryName = $_POST['CountryName'];
require_once 'tcpdf/tcpdf.php';
require_once 'fpdf/fpdi.php';
$pdf = new FPDI();
$pdf->SetPrintHeader(false);
$pdf->SetPrintFooter(false);
$pdf->AddPage();
$pdf->SetFillColor(255, 255, 255);
$html_head = "<span style='text-align:center;'><b>" . $gTEXT['National Stock Pipeline Information Report of'] . " " . $CountryName . " On " . $MonthName . ", " . $year . "</b></span>";
$html = '
<!-- EXAMPLE OF CSS STYLE -->
<style>
</style>
<body>
<h4 style="text-align:center;"><b>' . $gTEXT['National Stock Pipeline Information Report of'] . ' ' . $CountryName . ' ' . $gTEXT['on'] . ' ' . $MonthName . ',' . $year . '</b></h4>
</body>';
$pdf->writeHTMLCell(0, 0, 20, '', $html_head, '', 1, 1, false, 'C', true, $spacing = 0);
$pdf->setSourceFile("pdfslice/PipelineInfoChart.pdf");
$tplIdx = $pdf->importPage(1);
$pdf->useTemplate($tplIdx, 3, 0, 200, 400);
$pdf->endPage();
//=====================================================Pipeline Info List Table=======================================================
$monthId = $_POST['MonthId'];
$year = $_POST['YearId'];
$countryId = $_POST['CountryId'];
$itemGroupId = $_POST['ItemGroupId'];
$currentYearMonth = $_POST['YearId'] . "-" . $_POST['MonthId'] . "-" . "01";
$monthList = array('1' => 'January', '2' => 'February', '3' => 'March', '4' => 'April', '5' => 'May', '6' => 'June', '7' => 'July', '8' => 'August', '9' => 'September', '10' => 'October', '11' => 'November', '12' => 'December');
$sWhere = "";
if ($_POST['sSearch'] != "") {
$sWhere = " WHERE (a.ItemName LIKE '%" . mysql_real_escape_string($_POST['sSearch']) . "%'\n OR " . " a.AMC LIKE '%" . mysql_real_escape_string($_POST['sSearch']) . "%'\n OR " . " a.ClStock LIKE '%" . mysql_real_escape_string($_POST['sSearch']) . "%' \n OR " . " a.MOS LIKE '%" . mysql_real_escape_string($_POST['sSearch']) . "%'\n OR " . " b.Qty LIKE '%" . mysql_real_escape_string($_POST['sSearch']) . "%' \n )";
}
$sLimit = "";
if (isset($_GET['iDisplayStart']) && $_GET['iDisplayLength'] != '-1') {
$sLimit = "LIMIT " . intval($_GET['iDisplayStart']) . ", " . intval($_GET['iDisplayLength']);
}
$sOrder = "";
if (isset($_POST['iSortCol_0'])) {
$sOrder = " ORDER BY ";
for ($i = 0; $i < mysql_real_escape_string($_POST['iSortingCols']); $i++) {
$sOrder .= fnColumnToField_Item(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);
}
$sql = " SELECT ItemName, IFNULL(AMC,0) AMC, IFNULL(ClStock,0) ClStock, IFNULL(MOS,0) MOS, IFNULL(Qty,0) StockOnOrder \n FROM (SELECT\n t_cnm_masterstockstatus.CountryId,\n t_itemlist.ItemNo,\n t_itemlist.ItemName,\n SUM(t_cnm_stockstatus.AMC) AMC,\n SUM(t_cnm_stockstatus.ClStock) ClStock,\n SUM(t_cnm_stockstatus.MOS) MOS\n FROM t_cnm_stockstatus\n INNER JOIN t_cnm_masterstockstatus\n ON (t_cnm_stockstatus.CNMStockId = t_cnm_masterstockstatus.CNMStockId)\n INNER JOIN t_itemlist\n ON (t_cnm_stockstatus.ItemNo = t_itemlist.ItemNo)\n WHERE (t_cnm_masterstockstatus.Year = '{$year}'\n AND t_cnm_masterstockstatus.MonthId = {$monthId}\n AND t_cnm_masterstockstatus.CountryId = {$countryId}\n AND t_cnm_masterstockstatus.ItemGroupId = {$itemGroupId}\n AND t_cnm_masterstockstatus.StatusId = 5)\n GROUP BY t_cnm_masterstockstatus.CountryId, t_itemlist.ItemNo, t_itemlist.ItemName) a \n LEFT JOIN (SELECT\n CountryId, ItemNo, SUM(Qty) Qty\n FROM t_agencyshipment\n WHERE (ShipmentDate > CAST('{$currentYearMonth}' AS DATETIME) AND ShipmentStatusId = 2)\n GROUP BY CountryId, ItemNo) b\n ON a.CountryId = b.CountryId AND a.ItemNo = b.ItemNo\n " . $sWhere . "\n HAVING AMC>0 OR MOS>0 OR ClStock>0 OR StockOnOrder>0\n ORDER BY ItemName\n {$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['ItemName'][$f] = $rec['ItemName'];
$data['AMC'][$f] = number_format($rec['AMC']);
$data['ClStock'][$f] = number_format($rec['ClStock']);
$data['MOS'][$f] = number_format($rec['MOS'], 1);
$data['StockOnOrder'][$f] = $rec['StockOnOrder'] == 0 ? '' : $rec['StockOnOrder'];
$amc = $rec['AMC'] == 0 ? 1 : $rec['AMC'];
$stockOnOrderMOS = $rec['StockOnOrder'] / $amc;
$stockOnOrderMOS = $stockOnOrderMOS == 0 ? '' : number_format($stockOnOrderMOS, 1);
$totalMOS = number_format(number_format($rec['MOS'], 1) + $stockOnOrderMOS, 1);
$totalMOS = $totalMOS == 0 ? '' : $totalMOS;
//$data['StockOnOrderMOS'][$f] = $rec['StockOnOrderMOS']== 0? '' : $rec['StockOnOrderMOS'];
//$data['TotalMOS'][$f] = $rec['TotalMOS']== 0? '' : $rec['TotalMOS'];
$tblHTML .= '<tr style="page-break-inside:avoid;">
<td align="center" width="20" valign="middle">' . ($data['SL'][$f] + 1) . '</td>
<td align="left" width="150" valign="middle">' . $data['ItemName'][$f] . '</td>
<td align="right" width="50" valign="middle">' . $data['AMC'][$f] . '</td>
<td align="right" width="70" valign="middle">' . $data['ClStock'][$f] . '</td>
<td align="right" width="74" valign="middle">' . $data['MOS'][$f] . '</td>
<td align="right" width="50" valign="middle">' . $data['StockOnOrder'][$f] . '</td>
<td align="right" width="70" valign="middle">' . $stockOnOrderMOS . '</td>
<td align="right" width="50" valign="middle">' . $totalMOS . '</td>
</tr>';
$f++;
}
$pdf->startPage();
$html_head = "<span><b>" . $gTEXT['National Stock Pipeline Information List'] . "</b></span>";
$pdf->SetFont('dejavusans', '', 10);
$pdf->writeHTMLCell(0, 0, 10, 15, $html_head, '', 0, 0, false, 'C', true);
$html = '
<!-- EXAMPLE OF CSS STYLE -->
<style>
td{
height: 6px;
line-height:3px;
}
</style>
<body>
<table width="600px" border="0.5" style="margin:0 auto;">
<tr>
<th width="20" align="center"><b>SL</b></th>
<th width="150" align="left"><b>' . $gTEXT['Products'] . '</b></th>
<th width="50" align="right"><b>' . $gTEXT['AMC'] . '</b></th>
<th width="70" align="right"><b>' . $gTEXT['Available Stock'] . '</b></th>
<th width="74" align="right"><b>' . $gTEXT['MOS(Available)'] . '</b></th>
<th width="50" align="right"><b>' . $gTEXT['Stock on Order'] . '</b></th>
<th width="70" align="right"><b>' . $gTEXT['MOS(pipeline)'] . '</b></th>
<th width="50" align="right"><b>' . $gTEXT['Total MOS'] . '</b></th>
</tr>' . $tblHTML . '</table></body>';
$pdf->SetFont('dejavusans', '', 7);
$pdf->writeHTMLCell(0, 0, '', 25, $html, '', 1, 1, false, 'L', true);
$pdf->endPage();
$filePath = SITEDOCUMENT . 'administrator/components/com_jcode/source/report/pdfslice/PipelineInfoReport.pdf';
if (file_exists($filePath)) {
unlink($filePath);
}
$pdf->Output('pdfslice/PipelineInfoReport.pdf', 'F');
echo 'PipelineInfoReport.pdf';
} else {
echo 'Processing Error';
}
}
public function test()
{
// $rgb_arr = sscanf('rgb(243, 243, 243)', "rgb(%d, %d, %d)");
// print_r($this->rgbToCmyk($rgb_arr[0], $rgb_arr[1], $rgb_arr[2]));
// exit();
//
$img = new Imagick('test.jpg');
$img->resizeimage(1076, 720, Imagick::FILTER_LANCZOS, 1);
$img->setresolution(300, 300);
$img->setimageformat('pdf');
$img->writeimage('test.pdf');
include_once APPPATH . 'libraries/tcpdf/tcpdf.php';
include_once APPPATH . 'libraries/tcpdf/fpdi.php';
// создаём лист
$pdf = new FPDI('L', 'mm', array(91, 61), true, 'UTF-8', false);
$pdf->AddPage('L');
// загрузим ранее сохранённый шаблон
$pdf->setSourceFile('test.pdf');
$pdf->SetMargins(0, 0, 0, true);
$tplIdx = $pdf->importPage(1);
$pdf->useTemplate($tplIdx, null, null, 0, 0, true);
// установим опции для pdf
$pdf->SetMargins(0, 0, 0, true);
$pdf->setCellHeightRatio(1);
$pdf->setCellPaddings(0, 0, 0, 0);
$pdf->setCellMargins(1, 1, 1, 1);
$pdf->SetAutoPageBreak(false);
$pdf->SetPrintHeader(false);
$pdf->SetPrintFooter(false);
$pdf->Image('qwe.png', 30, 30, 20, '', '', '', '', false, 300);
$pdf->SetTextColor(0, 0, 0, 100);
$pdf->SetFont('helvetica', 'BI', 10, '', 'false');
$pdf->Text(12, 9, 'Black CMYK');
$pdf->Output($_SERVER['DOCUMENT_ROOT'] . '/uploads/fynal.pdf', 'F');
$im = new Imagick();
$im->setResolution(300, 300);
$im->readimage('uploads/fynal.pdf[0]');
$im->setImageFormat('jpeg');
$im->resizeimage(538, 360, Imagick::FILTER_LANCZOS, 1);
$im->writeImage('uploads/fynal.jpg');
$im->clear();
$im->destroy();
// include_once APPPATH . 'libraries/drawer.php';
//
// $drawer = new Drawer();
// $drawer->init();
// $drawer->setLayout();
// $drawer->setBackground('test.jpg');
// $drawer->drawImage('test.jpg', 10, 10, 30);
// $drawer->drawText('Maxim', 10, 10, 'TimesNewRoman', 20, 'rgb(77, 77, 77)');
// $drawer->savePdf($_SERVER['DOCUMENT_ROOT'] . '/1.pdf');
// $drawer->makePreview($_SERVER['DOCUMENT_ROOT'] . '/1.pdf', $_SERVER['DOCUMENT_ROOT'] . '/1.jpg');
}
function generatePatientRatioReport($conn)
{
global $gTEXT;
$CountryName = $_POST['CountryName'];
$MonthName = $_POST['MonthName'];
$ItemGroupName = $_POST['ItemGroupName'];
$countryId = $_POST['Country'];
$year = $_POST['YearId'];
$MonthId = $_POST['MonthId'];
$serviceType = $_POST['serviceType'];
require_once 'tcpdf/tcpdf.php';
require_once 'fpdf/fpdi.php';
$pdf = new FPDI();
$pdf->SetPrintHeader(false);
$pdf->SetPrintFooter(false);
$pdf->AddPage();
$pdf->SetFillColor(255, 255, 255);
if ($serviceType == '') {
$html_head = "<span style='text-align:center;font-size:10px;'><b>" . $gTEXT['All'] . " " . $gTEXT['Patient Ratio Report of'] . " " . $CountryName . " " . $gTEXT['on'] . " " . $MonthName . "," . $year . "</b></span>";
$html_h = '
<!-- EXAMPLE OF CSS STYLE -->
<style>
</style>
<body>
<h4 style="text-align:center;"><b>' . $gTEXT['Patient Ratio Report'] . ' ' . $gTEXT['on'] . ' ' . $MonthName . ',' . $year . '</b></h4>
<h4>' . $gTEXT['Country'] . ': ' . $CountryName . ', ' . $gTEXT['Service Type'] . ': ' . $gTEXT['All'] . ', ' . $gTEXT['Product Group'] . ': ' . $ItemGroupName . '</h4>
</body>';
$pdf->writeHTMLCell(0, 0, 10, '', $html_h, '', 1, 1, false, 'C', true, $spacing = 0);
} else {
$html_head = "<span style='text-align:center;font-size:10px;'><b> " . $serviceType . " " . $gTEXT['Patient Ratio Report of'] . " " . $CountryName . " " . $gTEXT['on'] . " " . $MonthName . "," . $year . "</b></span>";
$html_h = '
<!-- EXAMPLE OF CSS STYLE -->
<style>
</style>
<body>
<h4 style="text-align:center;"><b>' . $gTEXT['Patient Ratio Report'] . ' ' . $gTEXT['on'] . ' ' . $MonthName . ',' . $year . '</b></h4>
<h4>' . $gTEXT['Country'] . ': ' . $CountryName . ', ' . $gTEXT['Service Type'] . ': ' . $serviceType . ', ' . $gTEXT['Product Group'] . ': ' . $ItemGroupName . '</h4>
</body>';
$pdf->writeHTMLCell(0, 0, 10, '', $html_h, '', 1, 1, false, 'C', true, $spacing = 0);
}
$pdf->setSourceFile("pdfslice/patient_ratio_Chart.pdf");
$tplIdx = $pdf->importPage(1);
$pdf->useTemplate($tplIdx, 0, 20, 200);
//===================================================== Patient Ratio Table=======================================================
$countryId = $_POST['Country'];
$ItemGroupId = $_POST['ItemGroupId'];
$year = $_POST['YearId'];
$MonthId = $_POST['MonthId'];
$FormulationType = $_POST['serviceType'];
$lan = $_REQUEST['lan'];
if ($lan == 'en-GB') {
$formulationName = 'FormulationName';
} else {
$formulationName = 'FormulationNameFrench';
}
mysql_query('SET CHARACTER SET utf8');
$sq2 = "SELECT SQL_CALC_FOUND_ROWS t_regimen.FormulationId,{$formulationName} FormulationName,\n t_cnm_regimenpatient.RegimenId,t_regimen.RegimenName\n ,SUM(IFNULL(TotalPatient,0)) TotalPatient\n FROM t_cnm_regimenpatient\n INNER JOIN t_regimen ON t_cnm_regimenpatient.RegimenId = t_regimen.RegimenId\n INNER JOIN t_formulation ON t_regimen.FormulationId = t_formulation.FormulationId \n \n where (t_cnm_regimenpatient.CountryId = " . $countryId . " OR " . $countryId . " = 0)\n AND (t_cnm_regimenpatient.Year = '" . $year . "') \n AND (t_cnm_regimenpatient.MonthId = " . $MonthId . ") \n AND (t_cnm_regimenpatient.ItemGroupId = " . $ItemGroupId . ")\n AND ({$formulationName} = '" . $FormulationType . "' OR '" . $FormulationType . "' = '')\n AND t_formulation.bMajore = 1\t \n GROUP BY t_regimen.FormulationId,{$formulationName},\n t_cnm_regimenpatient.RegimenId,t_regimen.RegimenName\n ORDER BY t_regimen.FormulationId,t_cnm_regimenpatient.RegimenId;";
$rResult1 = safe_query($sq2);
$gTotal = 0;
$groupTotal = 0;
$count = 1;
$series1GroupTotal = array();
$series1GroupName = array();
$preServiceTypeId = -1;
$preServiceTypeName = '';
while ($row = mysql_fetch_assoc($rResult1)) {
if (!is_null($row['TotalPatient'])) {
settype($row['TotalPatient'], "integer");
$gTotal += $row['TotalPatient'];
}
if ($count > 1) {
if ($preServiceTypeId != $row['FormulationId']) {
$series1GroupTotal[$preServiceTypeId] = $groupTotal;
$series1GroupName[$preServiceTypeId] = $preServiceTypeName;
$groupTotal = 0;
}
}
$preServiceTypeId = $row['FormulationId'];
$preServiceTypeName = $row['FormulationName'];
$groupTotal += $row['TotalPatient'] == null ? 0 : $row['TotalPatient'];
$count++;
}
$series1GroupTotal[$preServiceTypeId] = $groupTotal;
$series1GroupName[$preServiceTypeId] = $preServiceTypeName;
$gTotal = $gTotal == 0 ? 1 : $gTotal;
$result3 = safe_query($sq2);
$total = mysql_num_rows($result3);
if ($total > 0) {
$tmpServiceTypeId = -1;
$TotalPercent = 0;
$serial = 1;
if ($FormulationType == '') {
$htm = '';
while ($aRow = mysql_fetch_array($result3)) {
if ($tmpServiceTypeId != $aRow['FormulationId']) {
$formulationName = trim(preg_replace('/\\s+/', ' ', addslashes($aRow['FormulationName'])));
$s1groupTotal = $series1GroupTotal[$aRow['FormulationId']];
$gPercent = number_format($s1groupTotal * 100 / ($gTotal == 0 ? 1 : $gTotal), 1) . ' %';
$TotalPercent += $gPercent;
$htm .= '<tr>
<td style="text-align: left;">' . $serial++ . '</td>
<td style="text-align: left;">' . $aRow['FormulationName'] . '</td>
<td style="text-align: right;">' . number_format($s1groupTotal) . '</td>
<td style="text-align: right;">' . $gPercent . '</td>
</tr>';
}
$tmpServiceTypeId = $aRow['FormulationId'];
}
$htm .= '<tr>
<td style="background-color:#ffffff;border-radius:2px;align:center;" colspan="2">Total</td>';
$htm .= ' <td style="background-color:#ffffff;border-radius:2px;text-align:right;" >' . number_format($gTotal) . '</td>';
$htm .= ' <td style="background-color:#ffffff;border-radius:2px;text-align:right;" >' . $TotalPercent . '%' . '</td>
</tr>';
} else {
$htm = '';
while ($aRow = mysql_fetch_array($result3)) {
$regimenName = trim(preg_replace('/\\s+/', ' ', addslashes($aRow['RegimenName'])));
$s1groupTotal = $series1GroupTotal[$aRow['FormulationId']];
$s1groupTotal1 = $s1groupTotal == 0 ? 1 : $s1groupTotal;
$totalPatient = $aRow['TotalPatient'];
settype($totalPatient, "float");
$fPercent = number_format($totalPatient * 100 / $s1groupTotal1, 1) . ' %';
$TotalPercent += $fPercent;
$htm .= '<tr>
<td style="text-align: left;">' . $serial++ . '</td>
<td style="text-align: left;">' . $aRow['RegimenName'] . '</td>
<td style="text-align: right;">' . number_format($aRow['TotalPatient']) . '</td>
<td style="text-align: right;">' . $fPercent . '</td>
</tr>';
}
$htm .= '<tr>
<td style="background-color:#ffffff;border-radius:2px;align:center;" colspan="2">Total</td>';
$htm .= ' <td style="background-color:#ffffff;border-radius:2px;text-align:right;" >' . number_format($s1groupTotal) . '</td>';
$htm .= ' <td style="background-color:#ffffff;border-radius:2px;text-align:right;" >' . $TotalPercent . '%' . '</td>
</tr>';
}
$html_head = "<span><b>" . $gTEXT['Patient Ratio Data List'] . "</b></span>";
$pdf->SetFont('dejavusans', '', 10);
$pdf->writeHTMLCell(0, 0, 10, 150, $html_head, '', 0, 0, false, 'C', true);
$html = '
<!-- EXAMPLE OF CSS STYLE -->
<style>
td{
height: 6px;
line-height:3px;
}
th{
height: 20;
font-size:9px;
}
</style>
<body>
<table width="650px" border="0.5" style="margin:0 auto;">
<tr style="page-break-inside:avoid;">
<th width="30" align="center"><b>SL#</b></th>
<th width="200" align="left"><b>' . $gTEXT['Type'] . '</b></th>
<th width="110" align="right"><b>' . $gTEXT['Patients'] . '</b></th>
<th width="110" align="right"><b>' . $gTEXT['Patient Percent'] . '</b></th>
</tr>' . $htm . '</table></body>';
//echo $htm;
$pdf->SetFont('dejavusans', '', 9);
$pdf->writeHTMLCell(0, 0, 20, 160, $html, '', 1, 1, false, 'L', true);
$filePath = SITEDOCUMENT . 'administrator/components/com_jcode/source/report/pdfslice/Patient_Ratio_Report.pdf';
if (file_exists($filePath)) {
unlink($filePath);
}
$pdf->Output('pdfslice/Patient_Ratio_Report.pdf', 'F');
echo 'Patient_Ratio_Report.pdf';
} else {
echo 'Processing Error';
}
}