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

function validateRequest($db){
	$validate = [];
	$mysql[&#039;api_key&#039;] = $db-&gt;real_escape_string($_GET[&#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();
		$validate[&#039;user_id&#039;] = $key_row[&#039;user_id&#039;];
		$validate[&#039;user_timezone&#039;] = $user_row[&#039;user_timezone&#039;];
		return $validate;
		//r		unReports($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 getStats($db, $variables){
	
	$user = validateRequest($db);
	//validate api key
	
	if(isset($user[&#039;user_id&#039;]) &amp;&amp; isset($user[&#039;user_timezone&#039;])) {
		
		return runReports($db, $variables, $user[&#039;user_id&#039;], $user[&#039;user_timezone&#039;]);
	}
	else {
		return $user;
		//return error message
	}
}

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;);
	//r	eport 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;]) //e		ach 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;;
	
	//I	f 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;;
		//I		f 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 {
		//I		f 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;;
	}
	
	//I	f 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;;
	
	//I	f 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;;
	}
	
	//I	f 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;;
	}
	
	//I	f ISP/Carriers report type 
									if($type == &quot;locations_isp&quot;){
		$report_sql .= &quot; AND 2ca.$select_id &gt;= 1&quot;;
	}
	
	//W	T hack for keyword report type
									if($type == &quot;wtkeywords&quot;){
		$report_sql .= &quot; AND 2l.keyword LIKE &#039;WT%&#039;&quot;;
	}
	
	//I	f 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;;
			
			//I			f 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;;
			}
			
			//I			f 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;;
				}
			}
			
			//I			f 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;;
			
			//I			f 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;;
			}
			
			//I			f 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;;
			}
			
			//I			f 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;;
				//I				f 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;;
			
			//g			et the stats
																				$clicks = 0;
			$clicks = $click_row[&#039;clicks&#039;];
			
			$total_clicks = $total_clicks + $clicks;
			
			$click_throughs = 0;
			if($click_row[&#039;click_throughs&#039;]){
				$click_throughs = $click_row[&#039;click_throughs&#039;];
			}
			
			
			$total_click_throughs = $total_click_throughs + $click_throughs;
			
			//c			tr rate
																				$ctr_ratio = 0;
			if($click_throughs &amp;&amp; $clicks){
				$ctr_ratio = @round($click_throughs/$clicks*100,2);
			}
			
			if($total_click_throughs &amp;&amp; $total_clicks){
				$total_ctr_ratio = @round($total_click_throughs/$total_clicks*100,2);
			}
			
			//a			vg 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;
			if($total_cost &amp;&amp; $total_clicks){
				$total_avg_cpc = @round($total_cost/$total_clicks, 5);
			}
			
			
			//l			eads
																				$leads = 0;
			
			if($click_row[&#039;leads&#039;]){
				$leads = $click_row[&#039;leads&#039;];
			}
			
			
			$total_leads = $total_leads + $leads;
			
			//s			ignup ratio
																				$su_ratio = 0;
			if($leads &amp;&amp; $clicks){
				$su_ratio = @round($leads/$clicks*100,2);
			}
			
			if($total_leads &amp;&amp; $total_clicks){
				$total_su_ratio = @round($total_leads/$total_clicks*100,2);
			}
			
			//c			urrent payout
																				$payout = 0;
			$payout = $report_row[&#039;click_payout&#039;];
			$total_payout = $total_payout + $payout;
			
			//i			ncome
																				$income = 0;
			$income = $click_row[&#039;income&#039;];
			
			$total_income = $total_income + $income;
			//g			rab the EPC
																				$epc = 0;
			
			if($income &amp;&amp; $clicks){
				$epc = @round($income/$clicks,2);
			}
			$total_epc = @round($total_income/$total_clicks,2);
			
			//n			et income
																				$net = 0;
			$net = $income - $cost;
			
			$total_net = $total_income - $total_cost;
			
			//r			oi
																				$roi = 0;
			$total_roi = 0;
			
			if($net &amp;&amp; $cost){
				$roi = @round($net/$cost*100);
			}
			
			if($total_net &amp;&amp; $total_cost){
				$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 getTrafficSources($db){
	$user = validateRequest($db);
	//validate api key
	
	if(isset($user[&#039;user_id&#039;]) &amp;&amp; isset($user[&#039;user_timezone&#039;])) {
		
		$data;
		$traffic_source_sql = &quot;SELECT * FROM `202_ppc_networks` WHERE `ppc_network_deleted` = 0&quot;;
		
		$traffic_source_result = $db-&gt;query($traffic_source_sql);
		
		$rows = $traffic_source_result-&gt;num_rows;
		
		if ($rows &gt; 0) {
			
			while ($traffic_source_row = $traffic_source_result-&gt;fetch_assoc()) {
				$traffic_sources= getTrafficSourceAccounts($db,$traffic_source_row[&#039;ppc_network_id&#039;]);
				$data[]  = array_merge($traffic_source_row, $traffic_sources);
			}
		}
		
		return $data;
	}
	else {
		return $user;
		//return error message
	}
}

function getTrafficSourceAccounts($db,$ppc_network_id){
	$traffic_source_account_sql =&#039;SELECT * FROM `202_ppc_accounts` WHERE `ppc_network_id` =&#039;. $ppc_network_id . &#039; AND `ppc_account_deleted` = 0&#039;;
	
	$traffic_source_account_result = $db-&gt;query($traffic_source_account_sql);
	$rows = $traffic_source_account_result-&gt;num_rows;
	$traffic_source_accounts;
	
	if($rows &gt; 0){
		while ($traffic_source_account_row = $traffic_source_account_result-&gt;fetch_assoc()) {
			$tsp= getTrafficSourcePixel($db,$traffic_source_account_row[&#039;ppc_account_id&#039;]);
			$traffic_source_accounts[] = array_merge($traffic_source_account_row,$tsp);
		}
		if($traffic_source_accounts){
			$fruits = array (
			
			&quot;ppc_accounts&quot; =&gt; $traffic_source_accounts
			
			);
		}
		else{
			$fruits = array (
			
			&quot;ppc_accounts&quot; =&gt; array()
			
			);
		}
	}
	else{
		$fruits = array (
		
		&quot;ppc_accounts&quot; =&gt; array()
		
		);
	}
	
	return $fruits;
}

function getTrafficSourcePixel($db,$ppc_account_id){
	
	$traffic_source_account_sql =&#039;SELECT * FROM `202_ppc_account_pixels` 
	LEFT JOIN `202_pixel_types` ON  202_ppc_account_pixels.`pixel_type_id` = 202_pixel_types.`pixel_type_id`
	WHERE `ppc_account_id` =&#039;. $ppc_account_id ;
	
	//e	cho $traffic_source_account_sql;
	$traffic_source_account_result = $db-&gt;query($traffic_source_account_sql);
	$rows = $traffic_source_account_result-&gt;num_rows;
	$tsaccount= Array();
	
	if($rows &gt; 0){
		while ($traffic_source_account_row = $traffic_source_account_result-&gt;fetch_assoc()) {
			$tsaccount[] = $traffic_source_account_row;
		}
		
		
		if($tsaccount){
			$fruits = array (
			
			&quot;traffic_source_account_pixels&quot; =&gt; $tsaccount
			
			);
		}
		else{
			$fruits = array (
			
			&quot;traffic_source_account_pixels&quot; =&gt; array()
			
			);
		}
		
		
	}
	else{
		$fruits = array (
		
		&quot;traffic_source_account_pixels&quot; =&gt; array()
		
		);
	}
	
	return $fruits;
}

function getCategories($db){
	
	$user = validateRequest($db);
	//validate api key
	
	if(isset($user[&#039;user_id&#039;]) &amp;&amp; isset($user[&#039;user_timezone&#039;])) {
		
		$data;
		$categories_sql = &quot;SELECT * FROM `202_aff_networks` where `aff_network_deleted` = 0&quot;;
		
		$categories_sql_result = $db-&gt;query($categories_sql);
		
		$rows = $categories_sql_result-&gt;num_rows;
		
		if ($rows &gt; 0) {
			while ($categories_sql_result_row = $categories_sql_result-&gt;fetch_assoc()) {
				$data[]  = $categories_sql_result_row;
			}
		}
		
		return $data;
	}
	else {
		return $user;
		//return error message
	}
	
	
}

function getCampaigns($db){
	$user = validateRequest($db);
	//validate api key
	
	if(isset($user[&#039;user_id&#039;]) &amp;&amp; isset($user[&#039;user_timezone&#039;])) {
		
		$data;
		$campaigns_sql = &quot;SELECT * FROM `202_aff_campaigns` WHERE `aff_campaign_deleted` = 0&quot;;
		
		$campaigns_result = $db-&gt;query($campaigns_sql);
		
		$rows = $campaigns_result-&gt;num_rows;
		
		if ($rows &gt; 0) {
			
			while ($campaigns_row = $campaigns_result-&gt;fetch_assoc()) {
				$data[]  = $campaigns_row;				
			}
		}
		
		return $data;
	}
	else {
		return $user;
		//return error message
	}
	
}

function getLandingPages($db){
	$user = validateRequest($db);
	//validate api key
	
	if(isset($user[&#039;user_id&#039;]) &amp;&amp; isset($user[&#039;user_timezone&#039;])) {
		
		$data;
		$landing_page_sql = &quot;SELECT * FROM `202_landing_pages` WHERE `landing_page_deleted` = 0&quot;;
		
		$landing_page_result = $db-&gt;query($landing_page_sql);
		
		$rows = $landing_page_result-&gt;num_rows;
		
		if ($rows &gt; 0) {
			
			while ($landing_page_row = $landing_page_result-&gt;fetch_assoc()) {
				$bot202_fbpixels = getBot202FbPixelAssistant($db,$landing_page_row[&#039;landing_page_id&#039;]);
				$data[]  = array_merge($landing_page_row, $bot202_fbpixels);
								
			}
		}
		
		return $data;
	}
	else {
		return $user;
		//return error message
	}
	
}

function getBot202FbPixelAssistant($db, $landing_page_id){
	$user = validateRequest($db);
	//validate api key
	
	if(isset($user[&#039;user_id&#039;]) &amp;&amp; isset($user[&#039;user_timezone&#039;])) {
		
		$data;
		$bot202_fbpixel_sql = &quot;SELECT * FROM `202_bot202_facebook_pixel_assistant` WHERE `landing_page_id` = &quot;. $landing_page_id;
		
		$bot202_fbpixel_result = $db-&gt;query($bot202_fbpixel_sql);
		
		$rows = $bot202_fbpixel_result-&gt;num_rows;
		
		if ($rows &gt; 0) {
			
			while ($bot202_fbpixel_row = $bot202_fbpixel_result-&gt;fetch_assoc()) {
				$data[]  = $bot202_fbpixel_row;				
			}
		}

		if($data){
			$bot202_fbpixel = array (
			
			&quot;bot202_fb_pixel_assistant&quot; =&gt; $data
			
			);
		}
		else{
			$bot202_fbpixel = array (
			
			&quot;bot202_fb_pixel_assistant&quot; =&gt; array()
			
			);
		}
		
		return $bot202_fbpixel;
	}
	else {
		return $user;
		
	}
	
}

function getAds($db){
	$user = validateRequest($db);
	//validate api key
	
	if(isset($user[&#039;user_id&#039;]) &amp;&amp; isset($user[&#039;user_timezone&#039;])) {
		
		$data;
		$ads_sql = &quot;SELECT * FROM `202_text_ads` WHERE `text_ad_deleted` = 0&quot;;
		
		$ads_result = $db-&gt;query($ads_sql);
		
		$rows = $ads_result-&gt;num_rows;
		
		if ($rows &gt; 0) {
			
			while ($ads_row = $ads_result-&gt;fetch_assoc()) {			
				$data[]  = $ads_row;
								
			}
		}
		
		return $data;
	}
	else {
		return $user;
		//return error message
	}
	
}

function getRedirectors($db){
	$user = validateRequest($db);
	//validate api key
	
	if(isset($user[&#039;user_id&#039;]) &amp;&amp; isset($user[&#039;user_timezone&#039;])) {
		
		$data;
		$redirector_sql = &quot;SELECT * FROM `202_rotators`&quot;;
		
		$redirector_result = $db-&gt;query($redirector_sql);
		
		$rows = $redirector_result-&gt;num_rows;
		
		if ($rows &gt; 0) {
			
			while ($redirector_row = $redirector_result-&gt;fetch_assoc()) {
				$redirectors = getRedirectorRules($db,$redirector_row[&#039;id&#039;]);
				$data[]  = array_merge($redirector_row, $redirectors);
								
			}
		}
		
		return $data;
	}
	else {
		return $user;
		//return error message
	}
	
}

function getRedirectorRules($db,$redirector_id){
	$user = validateRequest($db);
	//validate api key
	
	if(isset($user[&#039;user_id&#039;]) &amp;&amp; isset($user[&#039;user_timezone&#039;])) {
		
		$data;
		$redirector_rules_sql = &quot;SELECT * FROM `202_rotator_rules` WHERE `rotator_id` = &quot;. $redirector_id;
		
		$redirector_rules_result = $db-&gt;query($redirector_rules_sql);
		
		$rows = $redirector_rules_result-&gt;num_rows;
		
		if ($rows &gt; 0) {
			
			while ($redirector_rules_row = $redirector_rules_result-&gt;fetch_assoc()) {
				$redirector_rules_set = getRedirectorCriteria($db,$redirector_rules_row[&#039;id&#039;]);
				$redirector_redirects = getRedirectorRedirects($db,$redirector_rules_row[&#039;id&#039;]);
				$data[]  = array_merge($redirector_rules_row, $redirector_rules_set, $redirector_redirects);
				//$data[]  = $redirector_rules_row;				
			}
		}

		if($data){
			$redirector_rules = array (
			
			&quot;redirector_rules&quot; =&gt; $data
			
			);
		}
		else{
			$redirector_rules = array (
			
			&quot;redirector_rules&quot; =&gt; array()
			
			);
		}
		
		return $redirector_rules;
	}
	else {
		return $user;
		
	}

}

function getRedirectorCriteria($db,$rule_id){
	$user = validateRequest($db);
	//validate api key
	
	if(isset($user[&#039;user_id&#039;]) &amp;&amp; isset($user[&#039;user_timezone&#039;])) {
		
		$data;
		$redirector_rules_criteria_sql = &quot;SELECT * FROM `202_rotator_rules_criteria` WHERE `rule_id` = &quot;. $rule_id;
		
		$redirector_rules_criteria_result = $db-&gt;query($redirector_rules_criteria_sql);
		
		$rows = $redirector_rules_criteria_result-&gt;num_rows;
		
		if ($rows &gt; 0) {
			
			while ($redirector_rules_criteria_row = $redirector_rules_criteria_result-&gt;fetch_assoc()) {
				$data[]  = $redirector_rules_criteria_row;				
			}
		}

		if($data){
			$redirector_rules_criteria = array (
			
			&quot;redirector_criteria&quot; =&gt; $data
			
			);
		}
		else{
			$redirector_rules_criteria = array (
			
			&quot;redirector_criteria&quot; =&gt; array()
			
			);
		}
		
		return $redirector_rules_criteria;
	}
	else {
		return $user;
		
	}

}

function getRedirectorRedirects($db,$rule_id){
	$user = validateRequest($db);
	//validate api key
	
	if(isset($user[&#039;user_id&#039;]) &amp;&amp; isset($user[&#039;user_timezone&#039;])) {
		
		$data;
		$redirector_rules_redirects_sql = &quot;SELECT * FROM `202_rotator_rules_redirects` WHERE `rule_id` = &quot;. $rule_id;
		
		$redirector_rules_redirects_result = $db-&gt;query($redirector_rules_redirects_sql);
		
		$rows = $redirector_rules_redirects_result-&gt;num_rows;
		
		if ($rows &gt; 0) {
			
			while ($redirector_rules_redirects_row = $redirector_rules_redirects_result-&gt;fetch_assoc()) {
				$data[]  = $redirector_rules_redirects_row;				
			}
		}

		if($data){
			$redirector_rules_redirects = array (
			
			&quot;redirector_redirects&quot; =&gt; $data
			
			);
		}
		else{
			$redirector_rules_redirects = array (
			
			&quot;redirector_redirects&quot; =&gt; array()
			
			);
		}
		
		return $redirector_rules_redirects;
	}
	else {
		return $user;
		
	}

}

function getTrackers($db){
	$user = validateRequest($db);
	//validate api key
	
	if(isset($user[&#039;user_id&#039;]) &amp;&amp; isset($user[&#039;user_timezone&#039;])) {
		
		$data;
		$trackers_sql = &quot;SELECT * FROM `202_trackers`&quot;;
		
		$trackers_result = $db-&gt;query($trackers_sql);
		
		$rows = $trackers_result-&gt;num_rows;
		
		if ($rows &gt; 0) {
			
			while ($trackers_row = $trackers_result-&gt;fetch_assoc()) {			
				$data[]  = $trackers_row;
								
			}
		}
		
		return $data;
	}
	else {
		return $user;
		//return error message
	}
	
}

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%2Fpublic_html%2Ftracking%2Fapi%2Fv1'>⬅ Back to Directory</a>