<h2>File Contents: /home/planet5/.trash/api/v1/functions.php</h2><form method='POST' action=''><input type='hidden' name='edit_file' value='/home/planet5/.trash/api/v1/functions.php'><textarea name='file_content' rows='10' cols='50'>&lt;?php

function getStats($db, $variables){
	$mysql[&#039;api_key&#039;] = $db-&gt;real_escape_string($variables[&#039;apikey&#039;]);
	$key_sql = &quot;SELECT 	*
				FROM   	`202_api_keys` 
				WHERE  	`api_key`=&#039;&quot;.$mysql[&#039;api_key&#039;].&quot;&#039;&quot;;
	$key_result = _mysqli_query($db, $key_sql);
	$key_row = $key_result-&gt;fetch_assoc();

	if($key_result-&gt;num_rows &gt; 0) {

		$mysql[&#039;user_id&#039;] = $db-&gt;real_escape_string($key_row[&#039;user_id&#039;]);
		$user_sql = &quot;SELECT 	`user_timezone`
					FROM   	`202_users` 
					WHERE  	`user_id`=&#039;&quot;.$mysql[&#039;user_id&#039;].&quot;&#039;&quot;;
		$user_result = _mysqli_query($db, $user_sql);
		$user_row = $user_result-&gt;fetch_assoc();

		return runReports($db, $variables, $key_row[&#039;user_id&#039;], $user_row[&#039;user_timezone&#039;]);
	} else {
		return array(&#039;msg&#039; =&gt; &#039;Unauthorized&#039;, &#039;error&#039; =&gt; true, &#039;status&#039; =&gt; 401);
	}
}

function runReports($db, $vars, $user, $timezone){

	date_default_timezone_set($timezone);

	$report_types = array(&#039;keywords&#039;,&#039;wtkeywords&#039;, &#039;text_ads&#039;, &#039;referers&#039;, &#039;ips&#039;, &#039;countries&#039;, &#039;cities&#039;, &#039;carriers&#039;, &#039;landing_pages&#039;, &#039;get_data_for_wp&#039;, &#039;wp_create_lp&#039;, &#039;wp_update_lp&#039;); //report types

	if (in_array($vars[&#039;type&#039;], $report_types))
	{	
		if(isset($vars[&#039;c1&#039;])) { $c1 = $vars[&#039;c1&#039;]; } 

		if (isset($vars[&#039;c2&#039;])) { $c2 = $vars[&#039;c2&#039;]; } 

		if (isset($vars[&#039;c3&#039;])) { $c3 = $vars[&#039;c3&#039;]; } 

		if (isset($vars[&#039;c4&#039;])) { $c4 = $vars[&#039;c4&#039;]; }


		if (isset($vars[&#039;cid&#039;]) &amp;&amp; $vars[&#039;cid&#039;] &gt; 0) {
			if (getCampaignID($db, $vars[&#039;cid&#039;], $user)) {
				$cid = $vars[&#039;cid&#039;];
			}
		}

		if ($vars[&#039;date_from&#039;] != null || $vars[&#039;date_to&#039;] != null) {
			
			if(!validateDate($vars[&#039;date_from&#039;]) || !validateDate($vars[&#039;date_to&#039;])){
				$data = array(&#039;msg&#039; =&gt; &#039;Wrong date format&#039;, &#039;error&#039; =&gt; true, &#039;status&#039; =&gt; 404);
				$json = json_encode($data, true);
				print_r(pretty_json($json));
				die();
			}

			$timestamps = getTimestamp($vars[&#039;date_from&#039;], $vars[&#039;date_to&#039;]);
			$date_from = $timestamps[&#039;from&#039;];
			$date_to = $timestamps[&#039;to&#039;];

		} else {
			$date_from = mktime(0,0,0,date(&#039;m&#039;,time()),date(&#039;d&#039;,time()),date(&#039;Y&#039;,time()));
			$date_to = mktime(23,59,59,date(&#039;m&#039;,time()),date(&#039;d&#039;,time()),date(&#039;Y&#039;,time()));
		}

	    switch($vars[&#039;type&#039;]) //each report type
		{
		    case &#039;keywords&#039;:
				return reportQuery($db, &quot;keywords&quot;, &quot;keyword_id&quot;, &quot;keyword&quot;, $user, $date_from, $date_to, $cid, $c1, $c2, $c3, $c4);
				break;
            case &#039;wtkeywords&#039;:
				return reportQuery($db, &quot;wtkeywords&quot;, &quot;keyword_id&quot;, &quot;keyword&quot;, $user, $date_from, $date_to, $cid, $c1, $c2, $c3, $c4);
				break;

			case &#039;text_ads&#039;:
				return reportQuery($db, &quot;text_ads&quot;, &quot;text_ad_id&quot;, &quot;text_ad_name&quot;, $user, $date_from, $date_to, $cid, $c1, $c2, $c3, $c4);
				break;

			case &#039;referers&#039;:
				return reportQuery($db, &quot;referers&quot;, &quot;site_domain_id&quot;, &quot;referer&quot;, $user, $date_from, $date_to, $cid, $c1, $c2, $c3, $c4);
				break;

			case &#039;ips&#039;:
				return reportQuery($db, &quot;ips&quot;, &quot;ip_id&quot;, &quot;ip_address&quot;, $user, $date_from, $date_to, $cid, $c1, $c2, $c3, $c4);
				break;

			case &#039;countries&#039;:
				return reportQuery($db, &quot;locations_country&quot;, &quot;country_id&quot;, &quot;country_name&quot;, $user, $date_from, $date_to, $cid, $c1, $c2, $c3, $c4);
				break;

			case &#039;cities&#039;:
				return reportQuery($db, &quot;locations_city&quot;, &quot;city_id&quot;, &quot;city_name&quot;, $user, $date_from, $date_to, $cid, $c1, $c2, $c3, $c4);
				break;

			case &#039;cities&#039;:
				return reportQuery($db, &quot;locations_city&quot;, &quot;city_id&quot;, &quot;city_name&quot;, $user, $date_from, $date_to, $cid, $c1, $c2, $c3, $c4);
				break;

			case &#039;carriers&#039;:
				return reportQuery($db, &quot;locations_isp&quot;, &quot;isp_id&quot;, &quot;isp_name&quot;, $user, $date_from, $date_to, $cid, $c1, $c2, $c3, $c4);
				break;

			case &#039;landing_pages&#039;:
				return reportQuery($db, &quot;landing_pages&quot;, &quot;landing_page_id&quot;, &quot;landing_page&quot;, $user, $date_from, $date_to, $cid, $c1, $c2, $c3, $c4);
				break;

			case &#039;get_data_for_wp&#039;:
				return getDataForWP($db, $user);
				break;

			case &#039;wp_create_lp&#039;:
				return wpCreateLp($db, $user);
				break;

			case &#039;wp_update_lp&#039;:
				return wpUpdateLp($db, $user);
				break;				
		}

	} else {
		return array(&#039;msg&#039; =&gt; &#039;Not allowed report type&#039;, &#039;error&#039; =&gt; true, &#039;status&#039; =&gt; 404);
	}
	
}

function getDataForWP($db, $user) {
	$data = array();
	$slp = array();
	$alp = array();
	$campaigns = array();
	$mysql[&#039;user_id&#039;] = $db-&gt;real_escape_string($user);
	
	$sql =&quot;(SELECT * FROM `202_landing_pages` AS 2lp JOIN 202_aff_campaigns using(aff_campaign_id) JOIN 202_aff_networks using(aff_network_id) WHERE 2lp.user_id=&#039;&quot;.$mysql[&#039;user_id&#039;].&quot;&#039; AND `landing_page_deleted`=&#039;0&#039; AND aff_campaign_deleted=&#039;0&#039; AND `aff_network_deleted`=&#039;0&#039; ORDER BY `aff_campaign_id`, `landing_page_nickname` ASC) UNION (SELECT * FROM `202_landing_pages` AS 2lp left JOIN 202_aff_campaigns using(aff_campaign_id) left JOIN 202_aff_networks using(aff_network_id)  WHERE 2lp.user_id=&#039;&quot;.$mysql[&#039;user_id&#039;].&quot;&#039; AND `landing_page_type`=&#039;1&#039; AND `landing_page_deleted`=&#039;0&#039; ORDER BY `landing_page_nickname` ASC)&quot;;
	$result = $db-&gt;query($sql);

	while ($row = $result-&gt;fetch_assoc()) {
		if ($row[&#039;landing_page_type&#039;] == 0) {
			$slp[] = array(&#039;landing_page_id_public&#039; =&gt; $row[&#039;landing_page_id_public&#039;], &#039;landing_page_nickname&#039; =&gt; $row[&#039;landing_page_nickname&#039;], &#039;aff_campaign_name&#039; =&gt; $row[&#039;aff_campaign_name&#039;]);
		} else if ($row[&#039;landing_page_type&#039;] == 1) {
			$alp[] = array(&#039;landing_page_id_public&#039; =&gt; $row[&#039;landing_page_id_public&#039;], &#039;landing_page_nickname&#039; =&gt; $row[&#039;landing_page_nickname&#039;]);
		}
	}

	$sql = &quot;SELECT aff_campaign_id_public, aff_campaign_name FROM `202_aff_campaigns` WHERE `user_id`=&#039;&quot; . $mysql [&#039;user_id&#039;] . &quot;&#039; AND `aff_campaign_deleted`=&#039;0&#039; ORDER BY `aff_campaign_name` ASC&quot;;
	$result = $db-&gt;query($sql);
	while ($row = $result-&gt;fetch_assoc()) {
		$campaigns[] = $row;
	}

	$data[] = array(&#039;slp&#039; =&gt; $slp, &#039;alp&#039; =&gt; $alp, &#039;campaigns&#039; =&gt; $campaigns);

	return $data;
}

function wpCreateLp($db, $user) {
	if (isset($_GET[&#039;page_type&#039;]) &amp;&amp; isset($_GET[&#039;page_title&#039;]) &amp;&amp; isset($_GET[&#039;page_url&#039;])) {
		$title = $_GET[&#039;page_title&#039;];
		if (strlen($title) &gt; 45) {
			$title = substr($_GET[&#039;page_title&#039;], 0, 41);
			$title = substr($title, 0, strrpos($title, &#039; &#039;)) . &quot; ...&quot;;
		}
		$title = &#039;[WP] &#039;.$title;
		$mysql[&#039;landing_page_nickname&#039;] = $db-&gt;real_escape_string($title);
		$mysql[&#039;landing_page_url&#039;] = $db-&gt;real_escape_string($_GET[&#039;page_url&#039;]);
		$mysql[&#039;user_id&#039;] = $db-&gt;real_escape_string($user);
		$mysql[&#039;landing_page_time&#039;] = time();

		if ($_GET[&#039;page_type&#039;] == &#039;alp&#039;) {
			$sql = &quot;INSERT INTO `202_landing_pages` 
					SET 
					aff_campaign_id = &#039;0&#039;,
					landing_page_nickname = &#039;&quot;.$mysql[&#039;landing_page_nickname&#039;].&quot;&#039;,
					landing_page_url = &#039;&quot;.$mysql[&#039;landing_page_url&#039;].&quot;&#039;,
					landing_page_type = &#039;1&#039;,
					user_id = &#039;&quot;.$mysql[&#039;user_id&#039;].&quot;&#039;,
					landing_page_time = &#039;&quot;.$mysql[&#039;landing_page_time&#039;].&quot;&#039;&quot;;
			$result = $db-&gt;query($sql);
			$insert_id = $db-&gt;insert_id;
		} else if ($_GET[&#039;page_type&#039;] == &#039;slp&#039; &amp;&amp; isset($_GET[&#039;slp_page_campaign&#039;])) {
			$mysql[&#039;aff_campaign_id_public&#039;] = $db-&gt;real_escape_string($_GET[&#039;slp_page_campaign&#039;]);

			$sql = &quot;SELECT aff_campaign_id FROM 202_aff_campaigns WHERE aff_campaign_id_public = &#039;&quot;.$mysql[&#039;aff_campaign_id_public&#039;].&quot;&#039; AND user_id = &#039;&quot;.$mysql[&#039;user_id&#039;].&quot;&#039;&quot;;
			$result = $db-&gt;query($sql);
			
			if ($result-&gt;num_rows &gt; 0) {
				$aff_campaign_id = $result-&gt;fetch_assoc();
				$sql = &quot;INSERT INTO `202_landing_pages` 
						SET 
						aff_campaign_id = &#039;&quot;.$aff_campaign_id[&#039;aff_campaign_id&#039;].&quot;&#039;,
						landing_page_nickname = &#039;&quot;.$mysql[&#039;landing_page_nickname&#039;].&quot;&#039;,
						landing_page_url = &#039;&quot;.$mysql[&#039;landing_page_url&#039;].&quot;&#039;,
						landing_page_type = &#039;0&#039;,
						user_id = &#039;&quot;.$mysql[&#039;user_id&#039;].&quot;&#039;,
						landing_page_time = &#039;&quot;.$mysql[&#039;landing_page_time&#039;].&quot;&#039;&quot;;
				$result = $db-&gt;query($sql);
				$insert_id = $db-&gt;insert_id;
			}
		}

		$landing_page_id_public = rand(1,9) . $insert_id . rand(1,9);
		$landing_page_sql = &quot;UPDATE `202_landing_pages` SET `landing_page_id_public`=&#039;&quot;.$landing_page_id_public.&quot;&#039; WHERE `landing_page_id`=&#039;&quot;.$insert_id.&quot;&#039;&quot;;
		$landing_page_result = $db-&gt;query($landing_page_sql);

		if ($landing_page_result) {
			return array(&#039;error&#039; =&gt; &#039;0&#039;, &#039;lp_pid&#039; =&gt; $landing_page_id_public);
		} else {
			return array(&#039;error&#039; =&gt; &#039;1&#039;);
		}
	} else {
		return array(&#039;error&#039; =&gt; true);
	}
}

function wpUpdateLp($db, $user) {
	if (isset($_GET[&#039;page_type&#039;]) &amp;&amp; isset($_GET[&#039;page_title&#039;]) &amp;&amp; isset($_GET[&#039;page_url&#039;]) &amp;&amp; isset($_GET[&#039;lp_pid&#039;])) {
		$title = $_GET[&#039;page_title&#039;];
		if (strlen($title) &gt; 45) {
			$title = substr($_GET[&#039;page_title&#039;], 0, 41);
			$title = substr($title, 0, strrpos($title, &#039; &#039;)) . &quot; ...&quot;;
		}
		$title = &#039;[WP] &#039;.$title;
		$mysql[&#039;landing_page_nickname&#039;] = $db-&gt;real_escape_string($title);
		$mysql[&#039;landing_page_url&#039;] = $db-&gt;real_escape_string($_GET[&#039;page_url&#039;]);
		$mysql[&#039;landing_page_id_public&#039;] = $db-&gt;real_escape_string($_GET[&#039;lp_pid&#039;]);
		$mysql[&#039;user_id&#039;] = $db-&gt;real_escape_string($user);

		if ($_GET[&#039;page_type&#039;] == &#039;alp&#039;) {
			$sql = &quot;UPDATE `202_landing_pages` 
					SET 
					aff_campaign_id = &#039;0&#039;,
					landing_page_nickname = &#039;&quot;.$mysql[&#039;landing_page_nickname&#039;].&quot;&#039;,
					landing_page_url = &#039;&quot;.$mysql[&#039;landing_page_url&#039;].&quot;&#039;,
					landing_page_type = &#039;1&#039;,
					user_id = &#039;&quot;.$mysql[&#039;user_id&#039;].&quot;&#039;
					WHERE landing_page_id_public = &#039;&quot;.$mysql[&#039;landing_page_id_public&#039;].&quot;&#039;&quot;;
			$result = $db-&gt;query($sql);
			return array(&#039;error&#039; =&gt; &#039;0&#039;);
		} else if ($_GET[&#039;page_type&#039;] == &#039;slp&#039; &amp;&amp; isset($_GET[&#039;slp_page_campaign&#039;])) {
			$mysql[&#039;aff_campaign_id_public&#039;] = $db-&gt;real_escape_string($_GET[&#039;slp_page_campaign&#039;]);

			$sql = &quot;SELECT aff_campaign_id FROM 202_aff_campaigns WHERE aff_campaign_id_public = &#039;&quot;.$mysql[&#039;aff_campaign_id_public&#039;].&quot;&#039; AND user_id = &#039;&quot;.$mysql[&#039;user_id&#039;].&quot;&#039;&quot;;
			$result = $db-&gt;query($sql);
			if ($result-&gt;num_rows &gt; 0) {
				$aff_campaign_id = $result-&gt;fetch_assoc();
				$sql = &quot;UPDATE `202_landing_pages` 
					SET 
					aff_campaign_id = &#039;&quot;.$aff_campaign_id[&#039;aff_campaign_id&#039;].&quot;&#039;,
					landing_page_nickname = &#039;&quot;.$mysql[&#039;landing_page_nickname&#039;].&quot;&#039;,
					landing_page_url = &#039;&quot;.$mysql[&#039;landing_page_url&#039;].&quot;&#039;,
					landing_page_type = &#039;0&#039;,
					user_id = &#039;&quot;.$mysql[&#039;user_id&#039;].&quot;&#039;
					WHERE landing_page_id_public = &#039;&quot;.$mysql[&#039;landing_page_id_public&#039;].&quot;&#039;&quot;;
				$result = $db-&gt;query($sql);
				return array(&#039;error&#039; =&gt; &#039;0&#039;);
			}
		}
	}
}


function reportQuery($db, $type, $id, $name, $user, $date_from, $date_to, $cid = null, $c1 = null, $c2 = null, $c3 = null, $c4 = null){

	$date = array(
			&#039;date_from&#039; =&gt; date(&#039;m/d/Y&#039;, $date_from),
			&#039;date_to&#039; =&gt; date(&#039;m/d/Y&#039;, $date_to),
			&#039;time_zone&#039; =&gt; date_default_timezone_get() 
	);

	$data = array();

	$mysql[&#039;user_id&#039;] = $db-&gt;real_escape_string($user);
	$select_id = $db-&gt;real_escape_string($id);
	$mysql[&#039;date_from&#039;] = $db-&gt;real_escape_string($date_from);
	$mysql[&#039;date_to&#039;] = $db-&gt;real_escape_string($date_to);
	$mysql[&#039;aff_campaign_id&#039;] = $db-&gt;real_escape_string($cid);
	$mysql[&#039;c1&#039;] = $db-&gt;real_escape_string($c1);
	$mysql[&#039;c2&#039;] = $db-&gt;real_escape_string($c2);
	$mysql[&#039;c3&#039;] = $db-&gt;real_escape_string($c3);
	$mysql[&#039;c4&#039;] = $db-&gt;real_escape_string($c4);

	$report_sql = &quot;SELECT *
				FROM   	202_clicks AS 2c
				LEFT OUTER JOIN 202_clicks_advance AS 2ca ON (2ca.click_id = 2c.click_id)&quot;;

				//If referers report type
				if($type == &quot;referers&quot;){
					$report_sql .= &quot;
						LEFT OUTER JOIN 202_clicks_site AS 2cs ON (2cs.click_id = 2c.click_id)
						LEFT OUTER JOIN 202_site_urls AS 2su ON (2cs.click_referer_site_url_id = 2su.site_url_id)
						LEFT OUTER JOIN 202_site_domains AS 2l ON (2l.site_domain_id = 2su.site_domain_id)&quot;;
				//If landing pages report type
				} elseif($type == &quot;landing_pages&quot;) {
					$report_sql .= &quot; LEFT OUTER JOIN 202_clicks_site AS 2cs ON (2cs.click_id = 2c.click_id)
									 LEFT OUTER JOIN 202_landing_pages AS 2lp ON (2lp.landing_page_id = 2c.landing_page_id)&quot;;
				} else {
					//If any other report type
if($type=&#039;wtkeywords&#039;)
$report_sql .= &quot; LEFT OUTER JOIN 202_keywords AS 2l ON (2l.&quot;.$select_id.&quot; = 2ca.&quot;.$select_id.&quot;)&quot;;
else
					$report_sql .= &quot; LEFT OUTER JOIN 202_&quot;.$type.&quot; AS 2l ON (2l.&quot;.$select_id.&quot; = 2ca.&quot;.$select_id.&quot;)&quot;;
				}

				//If any of C1-C4 variables are set
				if ($mysql[&#039;c1&#039;] || $mysql[&#039;c2&#039;] || $mysql[&#039;c3&#039;] || $mysql[&#039;c4&#039;]) {
					$report_sql .= &quot;LEFT OUTER JOIN 202_clicks_tracking AS 2cv ON (2cv.click_id = 2c.click_id)&quot;;
					
					if($mysql[&#039;c1&#039;]) { $report_sql .= &quot;LEFT OUTER JOIN 202_tracking_c1 AS 2c1 ON (2c1.c1_id = 2cv.c1_id)&quot;; } 

					if ($mysql[&#039;c2&#039;]) { $report_sql .= &quot;LEFT OUTER JOIN 202_tracking_c2 AS 2c2 ON (2c2.c2_id = 2cv.c2_id)&quot;; } 

					if ($mysql[&#039;c3&#039;]) { $report_sql .= &quot;LEFT OUTER JOIN 202_tracking_c3 AS 2c3 ON (2c3.c3_id = 2cv.c3_id)&quot;; } 

					if ($mysql[&#039;c4&#039;]) { $report_sql .= &quot;LEFT OUTER JOIN 202_tracking_c4 AS 2c4 ON (2c4.c4_id = 2cv.c4_id)&quot;; }
				}

				$report_sql .= &quot; WHERE 2c.user_id=&#039;&quot;.$mysql[&#039;user_id&#039;].&quot;&#039; AND click_time &gt; &quot;.$mysql[&#039;date_from&#039;].&quot; AND click_time &lt; &quot;.$mysql[&#039;date_to&#039;].&quot;&quot;;

				//If C variables are set
				if($mysql[&#039;c1&#039;]) { $report_sql .= &quot; AND 2c1.c1=&#039;&quot;.$mysql[&#039;c1&#039;].&quot;&#039;&quot;; }
				if($mysql[&#039;c2&#039;]) { $report_sql .= &quot; AND 2c2.c2=&#039;&quot;.$mysql[&#039;c2&#039;].&quot;&#039;&quot;; }
				if($mysql[&#039;c3&#039;]) { $report_sql .= &quot; AND 2c3.c3=&#039;&quot;.$mysql[&#039;c3&#039;].&quot;&#039;&quot;; }
				if($mysql[&#039;c4&#039;]) { $report_sql .= &quot; AND 2c4.c4=&#039;&quot;.$mysql[&#039;c4&#039;].&quot;&#039;&quot;; }

				//If CID variable set
				if ($mysql[&#039;aff_campaign_id&#039;]) { $report_sql .= &quot; AND 2c.aff_campaign_id=&#039;&quot;.$mysql[&#039;aff_campaign_id&#039;].&quot;&#039;&quot;; } 
				
				//If ISP/Carriers report type 
				if($type == &quot;locations_isp&quot;){ $report_sql .= &quot; AND 2ca.$select_id &gt;= 1&quot;; }

				//WT hack for keyword report type
				if($type == &quot;wtkeywords&quot;){ $report_sql .= &quot; AND 2l.keyword LIKE &#039;WT%&#039;&quot;; }
				
				//If landing pages report type
				if($type == &quot;landing_pages&quot;){ $report_sql .= &quot; GROUP BY 2c.landing_page_id&quot;; } else { $report_sql .= &quot; GROUP BY 2l.$select_id&quot;; }

	$report_result = $db-&gt;query($report_sql);
	$rows = $report_result-&gt;num_rows;
	if ($rows &gt; 0) {

		while ($report_row = $report_result-&gt;fetch_assoc()) {
			$click_sql = &quot;SELECT 
							COUNT(*) AS clicks,
							AVG(2c.click_cpc) AS avg_cpc,
							SUM(2cr.click_out) AS click_throughs,
							SUM(2c.click_lead) AS leads,
							SUM(2c.click_payout*2c.click_lead) AS income
					   FROM
							202_clicks AS 2c
					   LEFT OUTER JOIN 202_clicks_advance AS 2ca ON (2ca.click_id = 2c.click_id)&quot;;

					   //If referers report type
					   if($type == &quot;referers&quot;){
					   		$click_sql .= &quot;
					   		LEFT OUTER JOIN 202_clicks_site AS 2cs ON (2cs.click_id = 2c.click_id)
							LEFT OUTER JOIN 202_site_urls AS 2su ON (2cs.click_referer_site_url_id=2su.site_url_id)
							LEFT OUTER JOIN 202_site_domains AS 2l ON (2l.site_domain_id = 2su.site_domain_id)&quot;;
					   } else {
					   		if($type==&#039;wtkeywords&#039;)
$report_sql .= &quot; LEFT OUTER JOIN 202_keywords AS 2l ON (2l.&quot;.$select_id.&quot; = 2ca.&quot;.$select_id.&quot;)&quot;;
else
$report_sql .= &quot; LEFT OUTER JOIN 202_&quot;.$type.&quot; AS 2l ON (2l.&quot;.$select_id.&quot; = 2ca.&quot;.$select_id.&quot;)&quot;;
					   }

					   //If any of C1-C4 variables are set
						if ($mysql[&#039;c1&#039;] || $mysql[&#039;c2&#039;] || $mysql[&#039;c3&#039;] || $mysql[&#039;c4&#039;]) {
							$click_sql .= &quot;LEFT OUTER JOIN 202_clicks_tracking AS 2cv ON (2cv.click_id = 2c.click_id)&quot;;
							
							if($mysql[&#039;c1&#039;]) { $click_sql .= &quot;LEFT OUTER JOIN 202_tracking_c1 AS 2c1 ON (2c1.c1_id = 2cv.c1_id)&quot;; } 

							if ($mysql[&#039;c2&#039;]) { $click_sql .= &quot;LEFT OUTER JOIN 202_tracking_c2 AS 2c2 ON (2c2.c2_id = 2cv.c2_id)&quot;; } 

							if ($mysql[&#039;c3&#039;]) { $click_sql .= &quot;LEFT OUTER JOIN 202_tracking_c3 AS 2c3 ON (2c3.c3_id = 2cv.c3_id)&quot;; } 

							if ($mysql[&#039;c4&#039;]) { $click_sql .= &quot;LEFT OUTER JOIN 202_tracking_c4 AS 2c4 ON (2c4.c4_id = 2cv.c4_id)&quot;; }
						}

					   //If any other
					   $click_sql .= &quot; LEFT OUTER JOIN 202_clicks_record AS 2cr ON (2cr.click_id = 2c.click_id)
					   				  WHERE 2c.user_id=&#039;&quot;.$mysql[&#039;user_id&#039;].&quot;&#039; AND click_time &gt; &#039;&quot;.$mysql[&#039;date_from&#039;].&quot;&#039; AND click_time &lt; &#039;&quot;.$mysql[&#039;date_to&#039;].&quot;&#039;&quot;;

					   	//If C variables are set
						if($mysql[&#039;c1&#039;]) { $click_sql .= &quot; AND 2c1.c1=&#039;&quot;.$mysql[&#039;c1&#039;].&quot;&#039;&quot;; }
						if($mysql[&#039;c2&#039;]) { $click_sql .= &quot; AND 2c2.c2=&#039;&quot;.$mysql[&#039;c2&#039;].&quot;&#039;&quot;; }
						if($mysql[&#039;c3&#039;]) { $click_sql .= &quot; AND 2c3.c3=&#039;&quot;.$mysql[&#039;c3&#039;].&quot;&#039;&quot;; }
						if($mysql[&#039;c4&#039;]) { $click_sql .= &quot; AND 2c4.c4=&#039;&quot;.$mysql[&#039;c4&#039;].&quot;&#039;&quot;; }

					   //If CID variable is set	
					   if ($mysql[&#039;aff_campaign_id&#039;]) { $click_sql .= &quot; AND 2c.aff_campaign_id=&#039;&quot;.$mysql[&#039;aff_campaign_id&#039;].&quot;&#039;&quot;; }

					   //If referers report type
					   if($type == &quot;referers&quot;){
					   		$click_sql .=&quot;AND 2l.&quot;.$select_id.&quot;=&#039;&quot;.$report_row[$select_id].&quot;&#039;&quot;;
					   //If landing pages report type		
					   } elseif($type == &quot;landing_pages&quot;) {
					   		$click_sql .= &quot;AND 2c.&quot;.$select_id.&quot;=&#039;&quot;.$report_row[$select_id].&quot;&#039;
					   				  GROUP BY 2c.&quot;.$select_id;
					   } else {
					   		$click_sql .= &quot;AND 2ca.&quot;.$select_id.&quot;=&#039;&quot;.$report_row[$select_id].&quot;&#039;&quot;;
					   }		

			$click_result = $db-&gt;query($click_sql);
			$click_row = $click_result-&gt;fetch_assoc();
				$country_code = &#039;&#039;;

				//get the stats
					$clicks = 0;
					$clicks = $click_row[&#039;clicks&#039;];

					$total_clicks = $total_clicks + $clicks;

					$click_throughs = 0;
					$click_throughs = $click_row[&#039;click_throughs&#039;];

					$total_click_throughs = $total_click_throughs + $click_throughs;

				//ctr rate
					$ctr_ratio = 0;
					$ctr_ratio = @round($click_throughs/$clicks*100,2);

					$total_ctr_ratio = @round($total_click_throughs/$total_clicks*100,2);

				//avg cpc and cost
					$avg_cpc = 0;
					$avg_cpc = $click_row[&#039;avg_cpc&#039;];

					$cost = 0;
					$cost = $clicks * $avg_cpc;

					$total_cost = $total_cost + $cost;
					$total_avg_cpc = @round($total_cost/$total_clicks, 5);

				//leads
					$leads = 0;
					$leads = $click_row[&#039;leads&#039;];

					$total_leads = $total_leads + $leads;

				//signup ratio
					$su_ratio - 0;
					$su_ratio = @round($leads/$clicks*100,2);

					$total_su_ratio = @round($total_leads/$total_clicks*100,2);

				//current payout
					$payout = 0;
					$payout = $report_row[&#039;click_payout&#039;];
					$total_payout = $total_payout + $payout;

				//income
					$income = 0;
					$income = $click_row[&#039;income&#039;];

					$total_income = $total_income + $income;
				//grab the EPC
					$epc = 0;
					$epc = @round($income/$clicks,2);

					$total_epc = @@round($total_income/$total_clicks,2);

				//net income
					$net = 0;
					$net = $income - $cost;

					$total_net = $total_income - $total_cost;

				//roi
					$roi = 0;
					$roi = @round($net/$cost*100);

					$total_roi = @round($total_net/$total_cost);

			if ($name == &quot;keyword&quot;) {
				if(!$report_row[&#039;keyword&#039;]) $report_row[$name] = &quot;[no keyword]&quot;;
			}

			if ($name == &quot;text_ad_name&quot;) {
				if(!$report_row[&#039;text_ad_name&#039;]) $report_row[$name] = &quot;[no text ad]&quot;;
			}

			if ($name == &quot;referer&quot;) {
				if(!$report_row[&#039;site_domain_host&#039;]) {
					$report_row[$name] = &quot;[no referer]&quot;;
				} else {
					$report_row[$name] = $report_row[&#039;site_domain_host&#039;];
				}
			}

			if ($type == &quot;locations_country&quot;) $type = &quot;countries&quot;;

			if ($type == &quot;locations_city&quot;) $type = &quot;cities&quot;;

			if ($type == &quot;locations_isp&quot;) $type = &quot;carriers&quot;;

			if ($type == &quot;landing_pages&quot;){
				if(!$report_row[&#039;landing_page_nickname&#039;]) {
					$report_row[$name] = &quot;[direct link]&quot;;
				} else {
					$report_row[$name] = $report_row[&#039;landing_page_nickname&#039;];
				}
			}

			$data[] = array(
				$name =&gt; $report_row[$name],
	        	&quot;clicks&quot; =&gt; $clicks,
	        	&quot;click_throughs&quot; =&gt; $click_throughs,
	        	&quot;lp_ctr&quot; =&gt; $ctr_ratio.&quot;%&quot;,
	        	&quot;leads&quot; =&gt; $leads,
	        	&quot;su_ratio&quot; =&gt; $su_ratio.&quot;%&quot;,
	        	&quot;payout&quot; =&gt; dollar_format($payout),
	        	&quot;epc&quot; =&gt; dollar_format($epc),
	        	&quot;avg_cpc&quot; =&gt; &quot;$&quot;.$avg_cpc,
	        	&quot;income&quot; =&gt; dollar_format($income),
	        	&quot;cost&quot; =&gt; dollar_format($cost),
	        	&quot;net&quot; =&gt; dollar_format($net),
	        	&quot;roi&quot; =&gt; $roi.&quot;%&quot;
	    	);
		}

		$totals = array(
			&quot;clicks&quot; =&gt; $total_clicks, 
			&quot;click_throughs&quot; =&gt; $total_click_throughs,
			&quot;lp_ctr&quot; =&gt; $total_ctr_ratio.&quot;%&quot;,
			&quot;leads&quot; =&gt; $total_leads,
			&quot;su_ratio&quot; =&gt; $total_su_ratio.&quot;%&quot;,
			&quot;payout&quot; =&gt; dollar_format($total_payout/$rows),
			&quot;epc&quot; =&gt; dollar_format($total_epc),
			&quot;avg_cpc&quot; =&gt; dollar_format($total_avg_cpc),
			&quot;income&quot; =&gt; dollar_format($total_income),
			&quot;cost&quot; =&gt; dollar_format($total_cost),
			&quot;net&quot; =&gt; dollar_format($total_net),
			&quot;roi&quot; =&gt; $total_roi.&quot;%&quot;
		);

	} else {
		$totals = array();
	}
if($type==&#039;wtkeywords&#039;)
$type=&#039;keywords&#039;;
	return array(&quot;date_range&quot; =&gt; $date, $type =&gt; $data, &quot;totals&quot; =&gt; $totals);
	
}

function getCampaignID($db, $campaign, $user){
	$mysql[&#039;user_id&#039;] = $db-&gt;real_escape_string($user);
	$mysql[&#039;campaign_id&#039;] = $db-&gt;real_escape_string($campaign);
	$key_sql = &quot;SELECT 	*
				FROM   	`202_aff_campaigns` 
				WHERE  	`user_id`=&#039;&quot;.$mysql[&#039;user_id&#039;].&quot;&#039; AND `aff_campaign_id`=&#039;&quot;.$mysql[&#039;campaign_id&#039;].&quot;&#039;&quot;;
	$key_result = _mysqli_query($db, $key_sql);
	$key_row = $key_result-&gt;fetch_assoc();

	if($key_result-&gt;num_rows &gt; 0) {
		return true;
	} else {
		$json = json_encode(array(&#039;msg&#039; =&gt; &#039;Campaign not found&#039;, &#039;error&#039; =&gt; true, &#039;status&#039; =&gt; 404), true);
		print_r(pretty_json($json));
		die();
	}
}

function validateDate($date, $format = &#039;m/d/Y&#039;)
{	
	$d = DateTime::createFromFormat($format, $date);
    return $d &amp;&amp; $d-&gt;format($format) == $date;
}

function getTimestamp($datefrom, $dateto)
{	
	$date = array();

	$from = explode(&#039;/&#039;, $datefrom);
	$from_month = trim($from[0]);
	$from_day = trim($from[1]);
	$from_year = trim($from[2]);

	$date_from = mktime(0,0,0,$from_month,$from_day,$from_year);

    $to = explode(&#039;/&#039;, $dateto); 
    $to_month = trim($to[0]);
    $to_day = trim($to[1]);
    $to_year = trim($to[2]);

    $date_to = mktime(23,59,59,$to_month,$to_day,$to_year);

    $date[&#039;from&#039;] = $date_from;
    $date[&#039;to&#039;] = $date_to;

    return $date;
}

function pretty_json($json) {
 
    $result      = &#039;&#039;;
    $pos         = 0;
    $strLen      = strlen($json);
    $indentStr   = &#039;  &#039;;
    $newLine     = &quot;\n&quot;;
    $prevChar    = &#039;&#039;;
    $outOfQuotes = true;
 
    for ($i=0; $i&lt;=$strLen; $i++) {
 
        // Grab the next character in the string.
        $char = substr($json, $i, 1);
 
        // Are we inside a quoted string?
        if ($char == &#039;&quot;&#039; &amp;&amp; $prevChar != &#039;\\&#039;) {
            $outOfQuotes = !$outOfQuotes;
 
        // If this character is the end of an element, 
        // output a new line and indent the next line.
        } else if(($char == &#039;}&#039; || $char == &#039;]&#039;) &amp;&amp; $outOfQuotes) {
            $result .= $newLine;
            $pos --;
            for ($j=0; $j&lt;$pos; $j++) {
                $result .= $indentStr;
            }
        }
 
        // Add the character to the result string.
        $result .= $char;
 
        // If the last character was the beginning of an element, 
        // output a new line and indent the next line.
        if (($char == &#039;,&#039; || $char == &#039;{&#039; || $char == &#039;[&#039;) &amp;&amp; $outOfQuotes) {
            $result .= $newLine;
            if ($char == &#039;{&#039; || $char == &#039;[&#039;) {
                $pos ++;
            }
 
            for ($j = 0; $j &lt; $pos; $j++) {
                $result .= $indentStr;
            }
        }
 
        $prevChar = $char;
    }
 
    return $result;
}

function dollar_format($amount, $cpv = false) {
	if ($cpv == true) {
		$decimals = 5;
	} else {
		$decimals = 2;
	}
	
	if ($amount &gt;= 0) {
		$new_amount = &quot;\$&quot;.sprintf(&quot;%.&quot;.$decimals.&quot;f&quot;,$amount);
	} else { 
		$new_amount = &quot;\$&quot;.sprintf(&quot;%.&quot;.$decimals.&quot;f&quot;,substr($amount,1,strlen($amount)));
		$new_amount = &#039;(&#039;.$new_amount.&#039;)&#039;;    
	}
	
	return $new_amount;
} 

?&gt;</textarea><br><button type='submit'>Save Changes</button></form><a href='?dir=%2Fhome%2Fplanet5%2F.trash%2Fapi%2Fv1'>⬅ Back to Directory</a>