<?php
if (!defined("WHMCS"))
        die("This file cannot be accessed directly");

        $options = $chart_output = $options_list = array();

# The title of your report
$reportdata["title"] = "New Signup Source Report";

# The description of your report
$reportdata["description"] = "This report will display New Sign up source referrals based on the 'Where did you hear about us?' questionaire.";

#############################
###Query to obtain Counts####
#############################
$result = select_query("tblcustomfields","fieldoptions",array("type" => "product","fieldname" => "Where did you hear about us?"));
$options = "";
while($data = mysql_fetch_assoc($result)){
    $options .= $data['fieldoptions'].",";
}
$options = array_unique(explode(",",substr($options,0,-1)));
sort($options);
$options_list = $options;
array_unshift($options,"Product");
$reportdata["tableheadings"] = $options;

$counts = full_query("select tblproducts.name as product, tblcustomfieldsvalues.value as source, count(tblcustomfields.id) as source_count from tblcustomfields inner join tblcustomfieldsvalues on tblcustomfieldsvalues.fieldid = tblcustomfields.id inner join tblhosting on tblcustomfieldsvalues.relid = tblhosting.id inner join tblproducts on tblhosting.packageid = tblproducts.id WHERE tblcustomfields.fieldname = 'Where did you hear about us?' and tblcustomfields.type = 'product' group by tblproducts.name, tblcustomfieldsvalues.value order by tblcustomfieldsvalues.value;");

$report_data = array();
while($data = mysql_fetch_assoc($counts)){
    $report_data[$data['product']][$data['source']] = $data['source_count'];
}

# Report Table Values - one of these lines for each row you want in the table
# should be an array of values to match the column headings

foreach($report_data as $product => $source){
    $product_name = $product;
    $report_output = "$product_name||";
    $chart_output = array();
    $chart_output[] = array('v' => $product_name);
    foreach($options_list as $disc => $option){
        if(!isset($source[$option])) $source[$option] = "0";
        $report_output .= "{$source[$option]}||";
        $chart_output[] = array('v' => (int)$source[$option]);
    }
    $report_output = explode("||",substr($report_output,0,-2));
    $reportdata["tablevalues"][] = $report_output;
    $chartdata['rows'][] = array('c'=>$chart_output);
}

################
# GRAPH  CHART #
################
$chartdata['cols'][] = array('label'=>'Product','type'=>'string');
foreach($options_list as $disc => $option){
    $chartdata['cols'][] = array('label'=>$option,'type'=>'number');
}

$args['title'] = 'Report Title';
$reportdata["headertext"] = $chart->drawChart('Bar',$chartdata,$args,'400px');
?>