/report/detail.php

<?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 codeToField($currentArray, $currentCode) {
	$codeFound = false;
	$counter = 0;
	$totalArray = count($currentArray);
	$thisCode = "";
	
	while (($codeFound == false) && ($counter <= $totalArray)) {
		if ($currentArray[$counter][0] == $currentCode) {
			$codeFound = true;
			$thisCode = $currentArray[$counter][1];
		} 
		$counter++;
	}
	return $thisCode;
	
}

$arrayFieldCode[] = array( 'firstname', 'cpresult_first_name');
$arrayFieldCode[] = array( 'lastname', 'cpresult_last_name');
$arrayFieldCode[] = array( 'birthdayyear', 'cpresult_birth_year');
$arrayFieldCode[] = array( 'birthdaymonth', 'cpresult_birth_month');
$arrayFieldCode[] = array( 'birthdayday', 'cpresult_birth_date');
$arrayFieldCode[] = array( 'email', 'cpresult_email');
$arrayFieldCode[] = array( 'mobilephone', 'cpresult_mobile');
$arrayFieldCode[] = array( 'gender', 'cpresult_gender_sid');
$arrayFieldCode[] = array( 'zipcode', 'cpresult_zip_code');
$arrayFieldCode[] = array( 'emailopt', 'cpresult_email_opt');
$arrayFieldCode[] = array( 'mobilecarrier', 'cpresult_carrier');
$arrayFieldCode[] = array( 'phonenumber', 'cpresult_home_phone');
$arrayFieldCode[] = array( 'address', 'cpresult_address');
$arrayFieldCode[] = array( 'city', 'cpresult_city');
$arrayFieldCode[] = array( 'state', 'cpresult_states');
$arrayFieldCode[] = array( 'country', 'cpresult_country_sid');
$arrayFieldCode[] = array( 'smsopt', 'cpresult_sms_post');
$arrayFieldCode[] = array( 'opencheckbox_1', 'cpresult_opencheckbox_1');
$arrayFieldCode[] = array( 'opencheckbox_2', 'cpresult_opencheckbox_2');



// start of code.
$campid = $_REQUEST['campid'];
$date = $_REQUEST['date'];
if($_REQUEST['date'] != "Total"){
	$date = $_REQUEST['date'];
}
$tord = $_REQUEST['tord'];

$sdate = $_REQUEST['sdate'];
$edate = $_REQUEST['edate'];

$csv_output = ""; 	// init var for csv.
$csv_hdr = ""; 		//create csv header.
$fieldList = "";
$fieldLabelsArray = array();
$fieldVariablesArray = array();
$counter=0;

/*
$sql = SELECT campaign_sid as campID, cpresult_user_id as facebookID, cpresult_first_name as firstname, cpresult_last_name as lastname, cpresult_mobile as mobileNumber, cpresult_email as emailAddress
FROM facebook.sp_campaign_result
where campaign_sid='CAMP0000000169' and date_format(cpresult_create_time,'%Y-%m-%d') = '2011-11-16'
 */
 
//*** Select the available fields for the current campaign
$sql_fields = "SELECT cpfldcfg_label, cpfldcfg_var_name, cpfldcfg_country_sid, cpfldcfg_is_mandatory FROM sp_campaign_config where campaign_sid='".$campid ."' and cpfldcfg_is_valid=1";
$res_fields = mysql_query($sql_fields, $con);
$totalFields = mysql_num_rows($res_fields);
while (list($field_label, $field_varname, $field_country, $field_mandatory) = mysql_fetch_array($res_fields)) {
	$field_varname = codeToField($arrayFieldCode,$field_varname);
	if ($field_varname !="") {
		$fieldList = $fieldList.$field_varname.",";
		$fieldLabelsArray[$counter]=$field_label;
		$fieldVariablesArray[$counter]=$field_varname;
		$csv_hdr .= $fieldLabelsArray[$counter].",";
		$counter++;
	}
}
$csv_hdr .= "Entry Date\n"; //ensure the last column entry starts a new line

$fieldList= $fieldList."cpresult_create_time";
$fieldVariablesArray[$counter]="cpresult_create_time";
$totalFields +=1;

$sql = "SELECT ".$fieldList;
$sql = $sql." ";
$sql = $sql."FROM sp_campaign_result";
$sql = $sql." ";

if($date == "Total"){
	$sql = $sql."where campaign_sid='".$campid."'";
}
else {
	$sql = $sql."where campaign_sid='".$campid."' and date_format(cpresult_create_time,'%Y-%m-%d') = '".$date."'";
}

if($tord == "d"){
	$sql = $sql." group by cpresult_user_id";
}

if(($sdate != null)&&($edate != null) ){
	$sql = addCon($sql, $sdate, $edate);
}

?>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" dir="ltr">
<head profile="http://gmpg.org/xfn/11">
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
<title>3 Tier Logic - Sweepstakes Tracker V1.0</title>

<style type="text/css">

table#user-list tr.hover {
	color: #fff;
	background-color: #4c95e6;
}

.pos_fixed{
	position:absolute;
	top:60px;
	left:150px;
}

</style>

<script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.4.2/jquery.min.js"></script>
<script type="text/javascript">
 
$(document).ready(function() {

	$('tr').hover(
        function () {
            $(this).addClass('hover');
        },
        function () {
            $(this).removeClass('hover');
        }
    );
    // We don't want the hover effect on our table header, so we unbind the event for our table row containing the th elements
    // $("tr.table-header").unbind('mouseenter mouseleave');

});
</script>

<!-- ****** Tables ************* -->
<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 type="text/javascript">
$(document).ready(function(){
	$('#example').dataTable({
        "bJQueryUI": true,
        <!--"sPaginationType": "full_numbers",-->
		"bPaginate": false,
		"bFilter": true,
		"bLengthChange": false,
		"bScrollInfinite": true
        <!--"bScrollCollapse": true,-->
        <!--"sScrollY": "<?php echo $tableHeight ?>px"-->
    });
	
	$(".panel").slideToggle("slow");
	
	$(".flip").click(function(){
		$(".panel").slideToggle("slow");
	});
});
</script>

</head>

<body>

<?php
//echo $sql."<br/>";

echo "<div id='div_table' align='center'>";


// run the sql again to get data...
$res = mysql_query($sql, $con);
if (mysql_num_rows($res) > 0) {
	//echo "<h4>total entry: ".mysql_num_rows($res)."</h4>";
	echo "<div align='center'>";
	echo "<table cellpadding='0' cellspacing='0' border='2' class='display' id='user-list'>";
	echo "<thead><tr>";
	//*** Dynamically generate the columns
	foreach ($fieldLabelsArray as $fieldLabel) {	 
		echo  "<th style='font-family:Arial, Helvetica, sans-serif; font-size:12px; color:#000'>".$fieldLabel."</th>";
	}
	echo  "<th style='font-family:Arial, Helvetica, sans-serif; font-size:12px; color:#000'>Entry Date</th>";
	echo "</thead>";
	echo "<tbody>";
	while($row = mysql_fetch_array($res)){
		echo "<tr>";
		foreach ($fieldVariablesArray as $fieldVariable) {
			echo "<td class='center'><p style='font-family:Arial, Helvetica, sans-serif; font-size:12px; color:#000'>".$row[$fieldVariable]."</td>";
			$currentFieldValue = $row[$fieldVariable];
			$currentFieldValue = str_replace('"', "", $currentFieldValue);
			$currentFieldValue = str_replace(',', "-", $currentFieldValue);
			$csv_output .= $currentFieldValue . ", ";
			
		}
		$csv_output .= "\n"; //ensure the last column entry starts a new line
		echo "</tr>";
		$i++;
		
	}
	echo "</tbody>";
	echo "</table>";
	echo "</div>";

}       

//echo "</div>";
?>
<!--<form class="pos_fixed" name="export" action="export.php" method="post">-->
<form  name="export" action="export.php" method="post">
    <input type="submit" value="Export table to CSV">
    <input type="hidden" value="<? echo $csv_hdr; ?>" name="csv_hdr">
    <input type="hidden" value="<? echo $csv_output; ?>" name="csv_output">
</form>
</div>
</body>

</html>