/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
//Piechart using Highchart and MySQL Database Showing turnover of Different channel over a given period of time.
/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
//// CODE
////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
///////////////////////////////////////////////////////////////////////////////////////////////////
//// index.php
///////////////////////////////////////////////////////////////////////////////////////////////////<?php
include("db.php");
$obj=new DB;
$sql="select * from pakchanel";
//$obj->hc($sql);
$ch="NEO";
$x=$obj->hc($sql,$ch);
//print_r($x);
$results=$obj->getSqlTable($sql);
//echo'<pre>';print_r($results);
/*[["ABC",450000],["BBC",650000],["NDTV",120000],["ZEE",230000],["STAR PLUS",630000],["SONY",260000],["TIMES NOW",870000],["ZOOM",850000],["COLORS",190000],["QUBO",985600],["EI",670098],["NBC10",760657],["WHY",8100900]]*/
?>
<script src="http://ajax.googleapis.com/ajax/libs/jquery/1.8.2/jquery.min.js"></script>
<script src="http://code.highcharts.com/highcharts.js"></script>
<script src="http://code.highcharts.com/modules/exporting.js"></script>
<div id="selchanel" align="left">
<select id="selchannel" onchange="selchannel(this.value)">
<?php foreach($results as $result){ ?>
<option value="<?php echo $result['name']?>"><?php echo $result['name']?></option>
<?php }?>
</select>
<select id="selduration" onchange="selduration(this.value)">
<option value="thisweek">This week</option>
<option value="thismonth">This month</option>
<option value="thisyear">This year</option>
<option value="last3month">Last 3 month</option>
<option value="last6month">Last 6 month</option>
<option value="all">All</option>
</select>
</div>
<div id="display">
<div id="container" style="min-width: 310px; height: 400px; max-width: 600px; margin: 0 auto"></div>
<script>
var p=<?php echo json_encode($x, JSON_NUMERIC_CHECK);?>;
$(function () {
$('#container').highcharts({
chart: {
plotBackgroundColor: null,
plotBorderWidth: null,
plotShadow: false,
type: 'pie'
},
title: {
text: 'Pakistani Channel Perforance Report'
},
tooltip: {
pointFormat: '{series.name}: <b>{point.percentage:.1f}%</b>'
},
plotOptions: {
pie: {
allowPointSelect: true,
cursor: 'pointer',
dataLabels: {
enabled: true,
format: '<b>{point.name}</b>: {point.percentage:.1f} %',
style: {
color: (Highcharts.theme && Highcharts.theme.contrastTextColor) || 'black'
}
}
}
},
series: [{
name: "Brands",
colorByPoint: true,
data: p
}]//SERIES END
});
});
</script>
</div>
<script>
/////functions
function selchannel(x){
//alert(x);
$.ajax({
type:"POST",
url:"update.php",
data:{"channel":x},
success:function(data){
$("#display").html(data);
}
});
}
function selduration(betdate){
$.ajax({
type:"POST",
url:"betdate.php",
data:{"betdate":betdate},
success:function(data){
$("#display").html(data);
}
});
}
</script>
///////////////////////////////////////////////////////////////////////////////////////////////////
//// End index.php
//////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
//// db.php
///////////////////////////////////////////////////////////////////////////////////////////////////<?php
///////////////////////////////////////////////////////////////////////////////////////////////////
//// Muna Database Function Library
///////////////////////////////////////////////////////////////////////////////////////////////////
//// Database Functions
///////////////////////////////////////////////////////////////////////////////////////////////////
////
//// Usage:
//// $db = new DB(); //instantiate using default read-only connection
//// $db = new DB($user,$pass); //instantiate using a specific login/pass
////
///////////////////////////////////////////////////////////////////////////////////////////////////
$showsql = true;
//$db = new DB();
class DB {
private $dbhost = "localhost";
private $dbuser = "root";
private $dbpass = "";
private $dbname = "chart";
public $error = "";
function __construct()
{
}
public function getSqlTable($sql){
$resarr=array();
$con = mysql_connect($this->dbhost,$this->dbuser,$this->dbpass);
if (!$con) {
die("Could not connect to database!");
}
mysql_select_db($this->dbname, $con);
//mysql_select_db("esigns_prod", $con);
$result = mysql_query($sql, $con);
//true,selected: true
while($res=mysql_fetch_assoc($result)){
$resarr[]=$res;
}
mysql_close($con);
return $resarr;
//list = array('id' => $fid, 'name' => $fname);
}
public function hc($sql,$ch){
$resarr=array();
$rows=array();
$con = mysql_connect($this->dbhost,$this->dbuser,$this->dbpass);
if (!$con) {
die("Could not connect to database!");
}
mysql_select_db($this->dbname, $con);
//mysql_select_db("esigns_prod", $con);
$result = mysql_query($sql, $con);
//true,selected: true
while($res=mysql_fetch_assoc($result)){
if($res['name']==$ch){
$resarr[]=array('name'=>$res['name'],'y'=>$res['amount'],'sliced'=>"true",'selected'=>"true");
}
else{
$resarr[]=array('name'=>$res['name'],'y'=>$res['amount']);
}
}
mysql_close($con);
//return $rows;
$res=json_encode($resarr, JSON_NUMERIC_CHECK);
return $resarr;
//list = array('id' => $fid, 'name' => $fname);
}
//---------------
//foreach ($customers as $customer) {
// $row[0]=$customer->cust_name;
// $row[1]=$customer->monthly_income;
// array_push($rows,$row);
//}
//print json_encode($rows, JSON_NUMERIC_CHECK);
//---------------
public function ReportError ($sql, $message) {
//echo "SQL ERROR:\nMessage: $message\nSQL: $sql\n";
$this->error = $message;
}
//highchart
public function GetChart ($sql)
{
$output = Array();
$con = $this->GetDB();
if (!$con) {return -1;}
$result = mysql_query($sql, $con);
if ($result == 0) {return 0;}
$rows = Array();
while ($res = mysql_fetch_object($result)) //will ignore anything that comes after
{
$row[0]=$res->year;
$row[1]=$res->amount;
//$row[1]=$channel->Date;
array_push($rows,$row); //array_push(array,value1,value2...)
}
mysql_close($con);
//print_r($rows);
print_r(json_encode($rows, JSON_NUMERIC_CHECK));
}
//delete by muna
public function deleteRow($sql) {
$con = mysql_connect($this->dbhost,$this->dbuser,$this->dbpass);
if (!$con) {
die("Could not connect to database!");
}
mysql_select_db($this->dbname, $con);
//mysql_select_db("esigns_prod", $con);
$result = mysql_query($sql, $con);
mysql_close($con);
return $result;
}
//returns a 1d list of results (using the first column returned)
public function GetList2 ($sql) {
$con = mysql_connect($this->dbhost,$this->dbuser,$this->dbpass);
if (!$con) {
die("Could not connect to database!");
}
//mysql_select_db("esigns_prod", $con);
$result = mysql_query($sql, $con);
while ($row = mysql_fetch_array($result)) {
$results[] = $row[0];
}
mysql_close($con);
return $results;
}
public function GetRow2 ($sql) {
$con = mysql_connect($this->dbhost,$this->dbuser,$this->dbpass);
if (!$con) {
die("Could not connect to database!");
}
//mysql_select_db($this->dbname, $con);
mysql_select_db("esigns_prod", $con);
$result = mysql_query($sql, $con);
//echo $sql;
//var_dump($result);
//echo mysql_error($con);
//mysql_close($con);
return mysql_fetch_assoc($result);
}
public function GetDB ()
{
$con = mysql_connect($this->dbhost,$this->dbuser,$this->dbpass);
if (!$con)
{
die("Could not connect to database!");
}
mysql_select_db($this->dbname, $con);
return $con;
}
public function Clean($sql)
{
return mysql_real_escape_string($sql, $this->GetDB());
}
public function Query ($sql)
{
$output=0;
$con = $this->GetDB();
if (!$con) {return -1;}
$result = mysql_query($sql, $con);
if (!isset($result))
{
return 0;
}
if (!$result) {return 0;}
if ($row = mysql_fetch_row($result))
{
$output = $row[0];
}
mysql_close($con);
return $output;
}
//returns the first two columns in an sql query as a keyed array
public function GetArray ($sql)
{
$output = Array();
$con = $this->GetDB();
if (!$con) {return -1;}
$result = mysql_query($sql, $con);
if ($result == 0) {return 0;}
while ($row = mysql_fetch_row($result))
{
//verify there are two rows of data, otherwise return an indexed list of the first
$output[$row[0]] = $row[1];
}
mysql_close($con);
return $output;
}
//returns the first column of each row returned
public function GetList ($sql)
{
$output = Array();
$con = $this->GetDB();
if (!$con) {return -1;}
$result = mysql_query($sql, $con);
if ($result == 0) {return 0;}
while ($row = mysql_fetch_row($result))
{
array_push($output, $row[0]);
}
mysql_close($con);
return $output;
}
public function GetResult ($sql)
{
$con = $this->GetDB();
if (!$con) {return 0;}
$result = mysql_query($sql, $con);
//mysql_close($con);
return $result;
}
public function GetJSON ($sql)
{
$output = Array();
$con = $this->GetDB();
if (!$con) {return -1;}
$result = mysql_query($sql, $con);
if ($result == 0) {return 0;}
while ($row = mysql_fetch_row($result)) //will ignore anything that comes after
{
$data = Array();
for ($i=0; $i<mysql_num_fields($result); $i++) {
$data[mysql_fetch_field($result, $i)->name] = $row[$i];
}
array_push($output, $data);
}
mysql_close($con);
return json_encode($output);
}
public function GetTable ($sql)
{
$output = Array();
$con = $this->GetDB();
if (!$con) {return -1;}
$result = mysql_query($sql, $con);
if ($result == 0) {return 0;}
while ($row = mysql_fetch_row($result)) //will ignore anything that comes after
{
$data = Array();
for ($i=0; $i<mysql_num_fields($result); $i++) {
$data[mysql_fetch_field($result, $i)->name] = $row[$i];
}
array_push($output, $data);
}
mysql_close($con);
return $output;
}
public function InsertSql ($sql)
{
$output = Array();
$con = $this->GetDB();
if (!$con) {return -1;}
$result = mysql_query($sql, $con);
if ($result) {$output= 1;}
else $output= 0;
mysql_close($con);
return $output;
}
public function GetRow ($sql)
{
$output = Array();
$con = $this->GetDB();
if (!$con) {return -1;}
$result = mysql_query($sql, $con);
if ($result == 0) {return 0;}
if ($row = mysql_fetch_row($result)) //will ignore anything that comes after
{
$output = $row;
}
mysql_close($con);
return $output;
}
public function GetRowObject ($sql)
{
$output = Array();
$con = $this->GetDB();
if (!$con) {return -1;}
$result = mysql_query($sql, $con);
if ($result == 0) {return 0;}
if ($row = mysql_fetch_assoc($result)) //will ignore anything that comes after
{
$output = $row;
}
mysql_close($con);
return $output;
}
public function GetRows ($sql)
{
$output = Array();
$con = $this->GetDB();
if (!$con) {return -1;}
$result = mysql_query($sql, $con);
if ($result == 0) {return 0;}
while ($row = mysql_fetch_row($result)) //will ignore anything that comes after
{
array_push($output, $row);
}
mysql_close($con);
return $output;
}
public function Command ($sql)
{
global $showsql;
$this->error = "";
//if ($showsql) {echo "SQL: $sql\n";}
$con = $this->GetDB();
if (!$con) {return 0;}
$result = mysql_query($sql, $con);
$rows = mysql_affected_rows($con);
if ($rows < 1)
{
$this->error = mysql_error($con);
//$this->ReportError($sql, mysql_error($con));
//echo "DB ERROR: " . mysql_error($con) . "\n";
}
mysql_close($con);
return $rows;
}
}
?>
//// End db.php
//////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
//// betdate.php
///////////////////////////////////////////////////////////////////////////////////////////////////<?php
include("db.php");
session_start();
$obj=new DB;
$sql="select * from pakchanel";
//$obj->hc($sql);
$betdate=$_REQUEST['betdate'];
$sql="";
switch($betdate){
case "thisweek":
$dtst=date("Y-m-d", strtotime('this week', time()) ); //20150130 AND
$dtend=date("Y-m-d", strtotime("today") );//20151005 //sae in all case
$sql.="SELECT * FROM pakchanel where DATE(Date) BETWEEN str_to_date('".$dtst."','%Y-%m-%d') AND str_to_date('".$dtend."','%Y-%m-%d')";
break;
case "thismonth":
$dtst=date("Y-m-d", strtotime(date('01-m-Y')) );
$dtend=date("Y-m-d", strtotime("today") );//20151005
$sql.="SELECT * FROM pakchanel where DATE(Date) BETWEEN str_to_date('".$dtst."','%Y-%m-%d') AND str_to_date('".$dtend."','%Y-%m-%d')";
break;
case "thisyear":
$dtst=date("Y-m-d", strtotime(date('01-01-Y')) );
$dtend=date("Y-m-d", strtotime("today") );//20151005
$sql.="SELECT * FROM pakchanel where DATE(Date) BETWEEN str_to_date('".$dtst."','%Y-%m-%d') AND str_to_date('".$dtend."','%Y-%m-%d')";
break;
case "last3month":
$dtst=date("Y-m-d", strtotime('- 3 month') );
$dtend=date("Y-m-d", strtotime("today") );//20151005
$sql.="SELECT * FROM pakchanel where DATE(Date) BETWEEN str_to_date('".$dtst."','%Y-%m-%d') AND str_to_date('".$dtend."','%Y-%m-%d')";
break;
case "last6month":
$dtst=date("Y-m-d", strtotime('- 6 month') );
$dtend=date("Y-m-d", strtotime("today") );//20151005
$sql.="SELECT * FROM pakchanel where DATE(Date) BETWEEN str_to_date('".$dtst."','%Y-%m-%d') AND str_to_date('".$dtend."','%Y-%m-%d')";
break;
case "all":
$sql.="SELECT * FROM pakchanel";
break;
}
if(isset($_SESSION['channel'])){
echo $ch=$_SESSION['channel'];
}
else
{echo $ch="PTV";}
$x=$obj->hc($sql,$ch);
?>
<div id="container" style="min-width: 310px; height: 400px; max-width: 600px; margin: 0 auto"></div>
<script>
var p=<?php echo json_encode($x, JSON_NUMERIC_CHECK);?>;
$(function () {
$('#container').highcharts({
chart: {
plotBackgroundColor: null,
plotBorderWidth: null,
plotShadow: false,
type: 'pie'
},
title: {
text: 'My 1st display of pie chart'
},
tooltip: {
pointFormat: '{series.name}: <b>{point.percentage:.1f}%</b>'
},
plotOptions: {
pie: {
allowPointSelect: true,
cursor: 'pointer',
dataLabels: {
enabled: true,
format: '<b>{point.name}</b>: {point.percentage:.1f} %',
style: {
color: (Highcharts.theme && Highcharts.theme.contrastTextColor) || 'black'
}
}
}
},
series: [{
name: "Brands",
colorByPoint: true,
data: p
}]//SERIES END
});
});
</script>
//// End betdate.php
//////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
//// update.php
///////////////////////////////////////////////////////////////////////////////////////////////////<?php
session_start();
include("db.php");
$obj=new DB;
$sql="select * from pakchanel";
//$obj->hc($sql);
$ch=$_REQUEST['channel'];
$_SESSION['channel']=$ch;
$x=$obj->hc($sql,$ch);
?>
<div id="container" style="min-width: 310px; height: 400px; max-width: 600px; margin: 0 auto"></div>
<script>
var p=<?php echo json_encode($x, JSON_NUMERIC_CHECK);?>;
$(function () {
$('#container').highcharts({
chart: {
plotBackgroundColor: null,
plotBorderWidth: null,
plotShadow: false,
type: 'pie'
},
title: {
text: 'My 1st display of pie chart'
},
tooltip: {
pointFormat: '{series.name}: <b>{point.percentage:.1f}%</b>'
},
plotOptions: {
pie: {
allowPointSelect: true,
cursor: 'pointer',
dataLabels: {
enabled: true,
format: '<b>{point.name}</b>: {point.percentage:.1f} %',
style: {
color: (Highcharts.theme && Highcharts.theme.contrastTextColor) || 'black'
}
}
}
},
series: [{
name: "Brands",
colorByPoint: true,
data: p
}]//SERIES END
});
});
</script>
//// End update.php
///////////////////////////////////////////////////////////////////////////////////////////////////
