Query Report with Chart ตัวอย่างการ Query ข้อมูลในการทำ Report

<?php
$mysqli = new mysqli($dbconfig['db_server'],$dbconfig['db_username'],$dbconfig['db_password'],$dbconfig['db_name']); 
if ($mysqli->connect_errno) {
 die( "Failed to connect to MySQL Vtiger: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error);
} 
$mysqli->set_charset("utf8");

switch (input_request("type")) {
    case "ReportTbl1":
  $query = "SELECT * FROM tbl1 ".$search;
        break;
    case "ReportTbl2":
  $query = "SELECT * FROM tbl2 ".$search;
        break;
}

$res = $mysqli->query($query);
$res_c = $mysqli->query($query);
if (!$res) {
    die('<p>Invalid query: ' . $mysqli->error.'</p>');
}
$num_rows = $res->num_rows;
//echo $query;
//echo $res->field_count;

//Export to Excel
if(input_request("export")==1){
include ("app/PHPExcel/Classes/PHPExcel.php");
// สร้าง object ของ Class  PHPExcel  ขึ้นมาใหม่
$objPHPExcel = new PHPExcel();

// กำหนดค่าต่างๆ
$objPHPExcel->getProperties()->setCreator("AppliCAD Co., Ltd.");
$objPHPExcel->getProperties()->setLastModifiedBy("AppliCAD Co., Ltd.");
$objPHPExcel->getProperties()->setTitle("Office 2007 XLSX ReportQuery Document");
$objPHPExcel->getProperties()->setSubject("Office 2007 XLSX ReportQuery Document");
$objPHPExcel->getProperties()->setDescription("ReportQuery from AppliCAD Co., Ltd.");

$sheet = $objPHPExcel->getActiveSheet();
$pageMargins = $sheet->getPageMargins();

$columnCharacter = array('A','B','C','D','E','F','G','H','I','J','K','L','M');

// margin is set in inches (0.5cm)
$margin = 0.5 / 2.54;

$pageMargins->setTop($margin);
$pageMargins->setBottom($margin);
$pageMargins->setLeft($margin);
$pageMargins->setRight(0);

$styleHeader = array(
  //'fill' => array('type' => PHPExcel_Style_Fill::FILL_SOLID,'color' => array('rgb' => 'ffff00')),
  'borders' => array('bottom' => array('style' => PHPExcel_Style_Border::BORDER_THIN)),
  'font'  => array(
  'bold'  => true,
  'size'  => 9,
  'name'  => 'Arial'
 ));

//Set หัว Column
$rowCell=1;
$c=0; 
while ($f = $res->fetch_field()) { 
 $objPHPExcel->setActiveSheetIndex(0)->setCellValue($columnCharacter[$c].$rowCell, $f->name);
 $c++;
}
$c = $c-1;
$objPHPExcel->getActiveSheet()->getStyle('A1:'.$columnCharacter[$c].'1')->applyFromArray($styleHeader);

//เขียนข้อมูล
$rowCell=2;
$c=0; 
while($row = $res->fetch_array(MYSQLI_NUM)){ $c++;
 for($i=0; $i < $res->field_count; $i++){
  $objPHPExcel->setActiveSheetIndex(0)->setCellValue($columnCharacter[$i].$rowCell, $row[$i]);
 }
 $rowCell++;
}
// 

// Rename worksheet
$objPHPExcel->getActiveSheet()->setTitle('ReportQuery');

// Set active sheet index to the first sheet, so Excel opens this as the first sheet
$objPHPExcel->setActiveSheetIndex(0);

$time_get_now = new DateTimeField(date("Y-m-d H:i:s"));
$time = str_replace("-","",$time_get_now->convertToDBFormat($time_get_now->getDisplayTime())); //แปลงมาเป็น Format Y-m-d H:i:s
$date = $time_get_now->convertToDBFormat($time_get_now->getDisplayDate()); //แปลงมาเป็น Format Y-m-d

list($h,$i,$s) = explode(":",$time);
$file_name = input_request("type")."_".$date."_".$h."_".$i."_".$s.")";

// Save Excel 2007 file
#echo date('H:i:s') . " Write to Excel2007 format\n";
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
ob_end_clean();
// We'll be outputting an excel file
header('Content-type: application/vnd.ms-excel');
// It will be called file.xls
header('Content-Disposition: attachment;filename="'.$file_name.'.xlsx"');
$objWriter->save('php://output'); 
exit();
}


?>
<script src="app/assets/js/jquery.min.js"></script>
<script src="app/assets/js/highcharts/highcharts.js"></script>
<script src="app/assets/js/highcharts/exporting.js"></script>

<style>
pre {
    width: auto;
    max-width: 1024px;
    overflow: auto;
    background-color: #eeeeee;
    word-break: normal !important;
    word-wrap: normal !important;
    white-space: pre !important;
}​
</style>

<script type="text/javascript">
$(function(){
 $('#showquery_c').on('click', function() {  
  $("#showquery").toggle();
 });
});

<?php
//Chart
 if($num_rows<=1000){
 if(input_request("charttype")=="pie"){ ?>
 //Pie
 $(function () {
    $('#chart').highcharts({
        chart: {
            plotBackgroundColor: null,
            plotBorderWidth: null,
            plotShadow: false,
            type: 'pie'
        },
        title: {
            text: '<?php echo input_request("type"); ?>'
        },
        tooltip: {
            pointFormat: '{series.name}: <b>{point.y:,.0f}  ({point.percentage:.1f}%)</b>'
        },
        plotOptions: {
            pie: {
                allowPointSelect: true,
                cursor: 'pointer',
                dataLabels: {
                    enabled: true,
                    format: '<b>{point.name}</b>: {point.y:,.0f} (<strong>{point.percentage:.1f} %</strong>)',
                    style: {
                        color: (Highcharts.theme && Highcharts.theme.contrastTextColor) || 'black'
                    }
                }
            }
        },
  credits: {
   enabled: false
  },
        series: [{
            name: "Total",
            colorByPoint: true,
            data: [
   <?php
   $c_field = $res_c->field_count-1;
    $c=0; while($row = $res_c->fetch_array(MYSQLI_NUM)){ $c++; ?>
   <?php if($c>1){ ?>,<?php } ?>
    {
     name: "<?php echo htmlspecialchars(addslashes(str_replace("\t","",str_replace("\n","",str_replace("\r","",$row[0]))))); ?>",
     y: <?php echo $row[$c_field]; ?>
    }
   <?php } ?>
   ]
        }]
    });
});
<?php }elseif(input_request("charttype")=="bar"){ ?>
$(function () {
    $('#chart').highcharts({
        chart: {
            type: 'column'
        },
        title: {
            text: '<?php echo input_request("type"); ?>'
        },
/*        subtitle: {
            text: ''
        },*/
        xAxis: {
            categories: [
   <?php
   $c_field = $res_c->field_count-1;
    $c=0; while($row = $res_c->fetch_array(MYSQLI_NUM)){ $c++; ?>
   <?php if($c>1){ ?>,<?php } 
   $data[] = $row[$c_field]; 
   ?>
                '<?php echo htmlspecialchars(addslashes(str_replace("\t","",str_replace("\n","",str_replace("\r","",$row[0]))))); ?>'
   <?php } ?>
            ],
            crosshair: true
        },
        yAxis: {
            min: 0,
            title: {
                text: 'Values'
            }
        },
        tooltip: {
            headerFormat: '<span style="font-size:10px">{point.key}</span><table>',
            pointFormat: '<tr><td style="color:{series.color};padding:0">{series.name}: </td>' +
                '<td style="padding:0"><b>{point.y:,.0f} </b></td></tr>',
            footerFormat: '</table>',
            shared: true,
            useHTML: true
        },
        plotOptions: {
            column: {
                pointPadding: 0.2,
                borderWidth: 0,
    dataLabels: {
     enabled: true
    }
   }
        },
  credits: {
   enabled: false
  },
        series: [{
            name: 'Values',
            data: [<?php echo join(',',$data); ?>]

        }]
    });
});
<?php
 } } 
//?>

</script> 

<span style="font-size:24px" ><?php echo input_request("type"); ?></span>
, <span>Date Between <?php echo "<strong>".$datetime_start."</strong> to <strong>".$datetime_end."</strong>"; ?> 
, [<a href="<?php echo $_SERVER['REQUEST_URI']; ?>&export=1">Export to excel</a>]</span>
, [ <span id="showquery_c" style="cursor:pointer;">Show Query</span> ]

<div id="showquery" style="display:none;">
<pre>
<?php echo $query; ?>
</pre>
</div>


<?php if($res->num_rows==0){ die("<p>-No Result-</p>"); } ?>
<?php if($num_rows<=1000){ ?>
<div id="chart" style="height:500px; width:100%"></div>
<?php }else{
 echo ", <strong>".$num_rows." records </strong> > 1,000 records Can't generate Chart";
} ?>
<table border=0 cellspacing=1 cellpadding=3 width="100%" class="lvt small">
 <tr>
  <td class="lvtCol">#</td>
 <?php while ($f = $res->fetch_field()) { ?>
  <td class="lvtCol"><strong><?php echo $f->table.".".$f->name; ?></strong></td>
 <?php } ?>
 </tr>
<?php $c=0; while($row = $res->fetch_array(MYSQLI_NUM)){ $c++; ?>
 <tr bgcolor=white onMouseOver="this.className='lvtColDataHover'" onMouseOut="this.className='lvtColData'">
  <td><?php echo $c; ?></td>
  <?php for($i=0; $i < $res->field_count; $i++){ ?>
  <td <?php if(is_numeric($row[$i])){ ?> style="text-align:right;" <?php } ?> nowrap="nowrap">
  <?php 
  echo is_numeric($row[$i]) ? number_format($row[$i]) : $row[$i]; 
  if(is_numeric($row[$i])){
   $sum[$i] = !isset($sum[$i]) ? $row[$i] : $sum[$i]+$row[$i];
  }
  ?>
  </td>
  <?php } ?>
 </tr>

<?php } ?>
 <tr>
 <td></td>
<?php for($i=0; $i < $res->field_count; $i++){ ?>
 <td style=" text-align:right;"><?php if(isset($sum[$i])){ echo '<strong>'.number_format($sum[$i]).'</strong>'; } ?></td>
<?php } ?>
</tr>

</table>
<?php
$res->close();
$res_c->close();
$mysqli->close(); 
?>