quickchart_birthdayyear.php

/report/quickchart_birthdayyear.php

https://bitbucket.org/3tierlogic/3tl.tabbuilder
PHP | 381 lines | 298 code | 52 blank | 31 comment | 18 complexity | 9beb636056203f8a06b5b5bb1180069e MD5 | raw file

<?php

require_once("db.php");
$con = mysql_connect($dbhost, $dbuser, $dbpasswd);		// connect to database
if (!$con) {											// error checking and handling
    die('Could not connect: ' . mysql_error());
}
mysql_select_db($dbname);


// add where condition to support date range.
function addCon($original_sql, $s, $e){

	// detect where;
	$pos = strpos($original_sql, 'where', 1); 
	if($pos == false){ // try capital again.
		$pos = strpos($original_sql, 'WHERE', 1);
	}
	
	$sub1 = substr($original_sql,0,$pos+1+4); // "select...where"
	//print "sub1: ".$sub1."<br/>";
	$sub2 = " cpresult_create_time between '".$s."' and '".$e."' and ";
	//print "sub2: ".$sub2."<br/>";
	$sub3 = substr($original_sql, $pos+1+4+1); // " ..."
	//print "sub3: ".$sub3."<br/>";
	
	$original_sql = "";
	$original_sql = $original_sql.$sub1;
	$original_sql = $original_sql.$sub2;
	$original_sql = $original_sql.$sub3;

	return $original_sql;
}

function nichi($raw){
	// 11/16 to 2011-11-16
	$done = str_replace('/',"-",$raw);
	//echo "done = ".$done."<br/>";
	$done = "2011-".$done;
	//echo "done = ".$done."<br/>";

	return $done;
}


// start of code.
$campaignID = $_REQUEST['cid'];
$sDate = $_REQUEST['sdate'];
$eDate = $_REQUEST['edate'];
$chartType = $_REQUEST['ctype'];
$countryCode = $_REQUEST['c'];

//echo $chartType."<br>";

switch ($chartType) {
	case "birthdayyear":
		$currentyear = date("Y");
		$agerangestr_data = "";
		//**** Check for under 18 - START ******
		$yearlimit = $currentyear - 18;
		$sql_bday = "SELECT cpresult_birth_year FROM sp_campaign_result where campaign_sid='".$campaignID."' AND cpresult_birth_year > ".$yearlimit;
		$sql_bday = addCon($sql_bday, $sDate, $eDate);
		$res_bday = mysql_query($sql_bday, $con);
		if (mysql_num_rows($res_bday) > 0) {
			$agerangestr_data = $agerangestr_data."['Under 18', ".mysql_num_rows($res_bday)."],";
		}
			$b0_17 = mysql_num_rows($res_bday);	
			$birthdayArray[0] = "Under 18";
			$birthdayTotalArray[0] = $b0_17;
		//}
		
		
		//**** Check for 18-24 range - START ******
		$yearlimit_high = $currentyear - 18;
		$yearlimit_low = $currentyear - 24;
		$sql_bday = "SELECT cpresult_birth_year FROM sp_campaign_result where campaign_sid='".$campaignID."' AND cpresult_birth_year >= ".$yearlimit_low." AND cpresult_birth_year <= ".$yearlimit_high;
		$sql_bday = addCon($sql_bday, $sDate, $eDate);
		$res_bday = mysql_query($sql_bday, $con); 
		if (mysql_num_rows($res_bday) > 0) {
			$agerangestr_data = $agerangestr_data."['18 - 24', ".mysql_num_rows($res_bday)."],";
		}
			$b18_24 = mysql_num_rows($res_bday);
			$birthdayArray[1] = "18 - 24";
			$birthdayTotalArray[1] = $b18_24;	
		//}
		
		//**** Check for Check 25 - 34 range - START ******
		$yearlimit_high = $currentyear - 25;
		$yearlimit_low = $currentyear - 34;
		$sql_bday = "SELECT cpresult_birth_year FROM sp_campaign_result where campaign_sid='".$campaignID."' AND cpresult_birth_year >= ".$yearlimit_low." AND cpresult_birth_year <= ".$yearlimit_high;
		$sql_bday = addCon($sql_bday, $sDate, $eDate);
		$res_bday = mysql_query($sql_bday, $con); 
		if (mysql_num_rows($res_bday) > 0) {
			$agerangestr_data = $agerangestr_data."['25 - 34', ".mysql_num_rows($res_bday)."],";
		}
			$b25_34 = mysql_num_rows($res_bday);
			$birthdayArray[2] = "25 - 34";
			$birthdayTotalArray[2] = $b25_34;
		//}
		
		//**** Check for 35 - 44 range - START ******
		$yearlimit_high = $currentyear - 35;
		$yearlimit_low = $currentyear - 44;
		$sql_bday = "SELECT cpresult_birth_year FROM sp_campaign_result where campaign_sid='".$campaignID."' AND cpresult_birth_year >= ".$yearlimit_low." AND cpresult_birth_year <= ".$yearlimit_high;
		$sql_bday = addCon($sql_bday, $sDate, $eDate);
		$res_bday = mysql_query($sql_bday, $con);
		if (mysql_num_rows($res_bday) > 0) { 
			$agerangestr_data = $agerangestr_data."['35 - 44', ".mysql_num_rows($res_bday)."],";
		}
			$b35_44 = mysql_num_rows($res_bday);
			$birthdayArray[3] = "35 - 44";
			$birthdayTotalArray[3] = $b35_44;
		//}
					
		//**** Check for  45 - 54  range - START ******
		$yearlimit_high = $currentyear - 45;
		$yearlimit_low = $currentyear - 54;
		$sql_bday = "SELECT cpresult_birth_year FROM sp_campaign_result where campaign_sid='".$campaignID."' AND cpresult_birth_year >= ".$yearlimit_low." AND cpresult_birth_year <= ".$yearlimit_high;
		$sql_bday = addCon($sql_bday, $sDate, $eDate);
		$res_bday = mysql_query($sql_bday, $con); 
		if (mysql_num_rows($res_bday) > 0) {
			$agerangestr_data = $agerangestr_data."['45 - 54', ".mysql_num_rows($res_bday)."],";
		}
			$b45_54 = mysql_num_rows($res_bday);
			$birthdayArray[4] = "45 - 54";
			$birthdayTotalArray[4] = $b45_54;
		//}
					
		//**** Check for  55 - 64 range - START ******
		$yearlimit_high = $currentyear - 55;
		$yearlimit_low = $currentyear - 64;
		$sql_bday = "SELECT cpresult_birth_year FROM sp_campaign_result where campaign_sid='".$campaignID."' AND cpresult_birth_year >= ".$yearlimit_low." AND cpresult_birth_year <= ".$yearlimit_high;
		$sql_bday = addCon($sql_bday, $sDate, $eDate);
		$res_bday = mysql_query($sql_bday, $con); 
		if (mysql_num_rows($res_bday) > 0) {
			$agerangestr_data = $agerangestr_data."['55 - 64', ".mysql_num_rows($res_bday)."],";
		}
			$b55_64 = mysql_num_rows($res_bday);
			$birthdayArray[5] = "55 - 64";
			$birthdayTotalArray[5] = $b55_64;
		//}
					
		//**** Check for  65+ range - START ******
		$yearlimit = $currentyear - 65;
		$sql_bday = "SELECT cpresult_birth_year FROM sp_campaign_result where campaign_sid='".$campaignID."' AND cpresult_birth_year <= ".$yearlimit ." AND cpresult_birth_year > 0";
		$res_bday = mysql_query($sql_bday, $con); 
		$sql_bday = addCon($sql_bday, $sDate, $eDate);
		if (mysql_num_rows($res_bday) > 0) {
			$agerangestr_data = $agerangestr_data."['65+', ".mysql_num_rows($res_bday)."],";
		}
			$b65_99 = mysql_num_rows($res_bday);
			$birthdayArray[6] = "65+";
			$birthdayTotalArray[6] = $b65_99;
		//}
		
		//** Check year=0 (No year entered)
		$sql_bday = "SELECT cpresult_birth_year FROM sp_campaign_result where campaign_sid='".$campaignID."' AND cpresult_birth_year = 0";
		$sql_bday = addCon($sql_bday, $sDate, $eDate);
		$res_bday = mysql_query($sql_bday, $con); 
		if (mysql_num_rows($res_bday) > 0) {
			$agerangestr_data = $agerangestr_data."{name:'No age data', y:".mysql_num_rows($res_bday).", sliced:true, selected:true}";
		}
			$b0_0 = mysql_num_rows($res_bday);
			$birthdayArray[7] = "No age data";
			$birthdayTotalArray[7] = $b0_0 ;
		//}
		
		//echo $agerangestr_data;
		$displayagerange_div = 1;
	break;
}



?>

<html>

<head>
<link href="style.css" rel="stylesheet" type="text/css" />
<style>
	div.panel{
		height:auto;
		display:none;
	}

	table#user-list tr.hover {
		color: #fff;
		background-color: #4c95e6;
	}	
	#containergraph {
		float: left
    	width: 33.3%;
		/* Min-height: */
		min-height: 400px;
		height: auto !important; 
		height: 400px;
	}
</style>

<script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.4.2/jquery.min.js"></script>
<link href="http://ajax.googleapis.com/ajax/libs/jqueryui/1.8.1/themes/base/jquery-ui.css" rel="stylesheet" type="text/css" />
<script src="http://ajax.googleapis.com/ajax/libs/jqueryui/1.8.1/jquery-ui.min.js"></script>
 <!-- Charts JS library: START -->
<script type="text/javascript" src="js/highcharts.js"></script>
<script type="text/javascript" src="js/themes/gray.js"></script>
<script type="text/javascript" src="js/modules/exporting.js"></script>
<!-- Charts JS library: END -->
 
 <style type="text/css">
      @import "DTmedia/css/demo_page.css";
      @import "DTmedia/css/demo_table.css";
</style>
<script class="jsbin" src="http://datatables.net/download/build/jquery.dataTables.js"></script>


    
<script>
var chart;
var field="<?=$chartType?>";
$(document).ready(function() {
	$('#example').dataTable({
        "bJQueryUI": true,
        <!--"sPaginationType": "full_numbers",-->
		"bPaginate": false,
		"bFilter": false,
		"bLengthChange": false,
		"bScrollInfinite": true,
        "bScrollCollapse": true,
        "sScrollY": "<?php echo $tableHeight ?>px"
    });
	
	
	switch(field){
		case "birthdayyear":{	
  			chart = new Highcharts.Chart({
      				chart: {
         				renderTo: 'container',
         				plotBackgroundColor: null,
         				plotBorderWidth: null,
         				plotShadow: false
     				 },
      				title: {
         				text: 'Age range breakdown'
      				},
      				tooltip: {
         				formatter: function() {
           				 	return '<b>'+ this.point.name +'</b>: '+ this.y +' entries';
							//return '<b>'+ this.point.name +'</b>: '+ this.percentage +' %';
        				 }
      				},
      				plotOptions: {
         				pie: {
           				 allowPointSelect: true,
           				 cursor: 'pointer',
           				 dataLabels: {
              				enabled: true,
              				color: Highcharts.theme.textColor || '#000000',
               				connectorColor: Highcharts.theme.textColor || '#000000',
               				formatter: function() {
                  				//return '<b>'+ this.point.name +'</b>: '+ this.y +' entries';
								return '<b>'+ this.point.name +'</b>: '+ Highcharts.numberFormat(this.percentage, 2) +' %';
								
               				}
            			}
         			  }
      				},
       				series: [{
         				type: 'pie',
         				name: 'Age Range breakdown',
         				data: [
            				<?php echo $agerangestr_data;?> 
         				]
      				}]
   			});
			break;
		}
		
		default: {
			chart = new Highcharts.Chart({
      				chart: {
        				renderTo: 'container',
         				plotBackgroundColor: null,
         				plotBorderWidth: null,
         				plotShadow: false
      				},
      				title: {
         				text: 'Browser market shares at a specific website, 2010'
      				},
      				tooltip: {
         				formatter: function() {
            				return '<b>'+ this.point.name +'</b>: '+ this.percentage +' %';
         				}
      				},
      				plotOptions: {
         				pie: {
            				allowPointSelect: true,
            				cursor: 'pointer',
           					dataLabels: {
               					enabled: true,
               					color: Highcharts.theme.textColor || '#000000',
               					connectorColor: Highcharts.theme.textColor || '#000000',
               					formatter: function() {
                  					return '<b>'+ this.point.name +'</b>: '+ this.percentage +' %';
               					}
            				}
         				}
      				},
       				series: [{
         				type: 'pie',
         				name: 'Browser share',
         				data: [
            				['Firefox',   45.0],
            				['IE',       26.8],
            					{
               						name: 'Chrome',    
               						y: 12.8,
               						sliced: true,
               						selected: true
            					},
            				['Safari',    8.5],
            				['Opera',     6.2],
            				['Others',   0.7]
         				]
      				}]
  			 });	
		}
	} //*** Switch
}); //**** function
   


</script>



</head>
<body>
<div id="container" style="width: 900px; height: 400px; margin: auto;"></div>
<div id="tableContainer" style="width: 900px; height: 400px; margin: auto;" >
<br><br><br><br>
<?php

											
							
							echo "<h2>Campaign summary from ".$sDate." to ".$eDate."</h2>";
							echo "<table cellpadding='0' cellspacing='0' border='2' class='display' id='example'>"; //id='user-list'>"; 
							echo "<thead><tr><th>Item</th><th>Age Range</th><th>Total Entries</th></tr></thead>";
							echo "<tbody>";
							
							$counter = 0;
							$t = 0;
						   	foreach ($birthdayArray as $agerangelabel) {
								if($counter % 2 != 0){
							  		echo "<tr class='odd gradeX'>";
								 }else{
							   		echo "<tr class='odd gradeA'>";
							 	}
								echo "<td class='center'>".($counter+1)."</td>";
								echo "<td class='center'>".$agerangelabel."</td>";
								echo "<td class='center'>".$birthdayTotalArray[$counter]."</td>";
								echo "</tr>";
								$t = $t + $birthdayTotalArray[$counter];
								$counter++;
								
							}
							echo "<tr class='odd gradeA'>";
							echo "<td class='center'>".($counter+1)."</td>";
							echo "<td class='center'><b>Total entries</b></td>";
							echo "<td class='center'><b>".$t."</b></td>";
							echo "</tr>";
							echo "</tbody>";
							echo "</table>";
						     
						
						   
					?>
</div>
</body>

</html>