query

gistfile1.php

<?php

$time = UtilityPeer::convertTZ($args);
$offset = $time['offset'];
$minutes = $time['minutes'];
$timezone = $time['timezone'];
$args['ds'] = $time['ds'];
$args['de'] = $time['de'];
	
$num_months = 1;
if (isset($args['ds']) && isset($args['de']) && ($args['ds'] != $args['de'])) {
	$num_months = ceil((strtotime($args['de']) - strtotime($args['ds'])) / 60 / 60 / 24 / 31);
}

if (isset($args['graph'])) {
	$args['sort'] = "DATE(CONVERT_TZ(click.created_at,'GMT','$timezone'))";
	$args['order'] = 'ASC';
}

$sql = "SELECT SQL_CALC_FOUND_ROWS 
		  campaign.id AS `campaign_id`,
		  campaign.external_id,
		  campaign.external_type,
		  campaign.name AS `campaign_name`,
		  campaign.created_at,
		  campaign.updated_at,
		  campaign.network_id,
		  COUNT(click.id) AS `clicks`,
		  (SELECT
				COUNT(*) AS `clicks`
			FROM
				click
			INNER JOIN
				landing_page
					ON click.landing_page_id = landing_page.id
			INNER JOIN
				ad
					ON landing_page.ad_id = ad.id
			INNER JOIN
				ad_group
					ON ad.ad_group_id = ad_group.id
			WHERE
				ad_group.campaign_id = campaign.id 
	    		AND click.created_at >= '" . $args['ds'] . "' AND click.created_at <= '" . $args['de'] . "' 
	    		AND click.offer_click = 1
	      ) AS `offer_clicks`,
		  COALESCE(SUM(click.cost),0) + COALESCE(cost_table.cost,0) AS `cost`,
          COALESCE(action_table.`count`,0) AS `actions`,
          COALESCE(action_table.value, 0) AS `revenue`,
          COALESCE(action_table.value, 0) - (COALESCE(SUM(click.cost),0) + COALESCE(cost_table.cost,0)) AS `profit`,
		  COALESCE(COALESCE(action_table.`count`,0) / COUNT(click.id), 0) * 100 AS `convrate`,
		  COALESCE((COALESCE(SUM(click.cost),0) + COALESCE(cost_table.cost,0)) / COALESCE(action_table.`count`,0), 0) AS `costconv`,
		  COALESCE(
		  	(COALESCE(action_table.value, 0) - (COALESCE(SUM(click.cost),0) + COALESCE(cost_table.cost,0)))
		  	/
		  	(COALESCE(SUM(click.cost),0) + COALESCE(cost_table.cost,0))
		  ,0) * 100 AS `roi`";
		  
if (isset($args['ds']) && isset($args['de'])) {
	$sql .= ", DATE(CONVERT_TZ(click.created_at,'GMT','$timezone')) AS `date`";
}  

$sql .= "		  
		FROM
		  campaign
		LEFT OUTER JOIN
		  ad_group
		    ON ad_group.campaign_id = campaign.id
		LEFT OUTER JOIN
		  ad
		    ON ad.ad_group_id = ad_group.id
		LEFT OUTER JOIN
		  landing_page
		    ON landing_page.ad_id = ad.id
		LEFT OUTER JOIN
		  click
		    ON click.landing_page_id = landing_page.id 
		    	AND click.created_at >= '" . $args['ds'] . "' AND click.created_at <= '" . $args['de'] . "' 
		    	AND click.offer_click = 0 
		    	AND click.deleted = 0
		LEFT OUTER JOIN
			(
				SELECT 
					campaign.id AS `campaign_id`,
			  		COUNT(action.id) AS `count`,
			  		COALESCE(SUM(action.custom_amount),0) AS `value`,
			  		DATE(CONVERT_TZ(action.created_at,'GMT','$timezone')) AS `date` 
			  	FROM
			  		action
			  	INNER JOIN
			  		click
			  			ON click.id = action.click_id
			  	INNER JOIN
			  		landing_page
			  			ON landing_page.id = click.landing_page_id
			  	INNER JOIN
			  		ad
			  			ON ad.id = landing_page.ad_id
			  	INNER JOIN
			  		ad_group
			  			ON ad_group.id = ad.ad_group_id
			  	INNER JOIN
			  		campaign
			  			ON campaign.id = ad_group.campaign_id 
			  	WHERE
			  		action.deleted = 0
		    			AND action.created_at >= '" . $args['ds'] . "' AND action.created_at <= '" . $args['de'] . "' 
		    	";
		    	
				if (count($args['selected']) > 0) {
					$sql .= "AND campaign.id IN (" . implode(', ', $args['selected']) . ") ";
				}				 
				if (!empty($args['restrict_to'])) {
					$sql .= "AND campaign.id = " . $args['restrict_to'] . " ";
				}
		    	
				$sql .= " GROUP BY ";
						
				if (isset($args['graph'])) {
					$sql .= "DATE(CONVERT_TZ(action.created_at,'GMT','$timezone')) ";
				} else {
					$sql .= "campaign.id";
				}
		    	
		    	$sql .= "
		  	) AS `action_table` 
		  	";
		  	
		  	if (isset($args['graph'])) {
		  		$sql .= "ON action_table.`date` = DATE(CONVERT_TZ(click.created_at,'GMT','$timezone'))";
		  	} else {
		  		$sql .= "ON action_table.campaign_id = campaign.id";
		  	}
		
		
		$sql .= "
		LEFT OUTER JOIN
			(
				SELECT
					campaign.id AS `campaign_id`,
					COALESCE(
						SUM(
							CASE 
								WHEN ad_group.cost_type = 'monthly' 
								THEN ad_group.cost * $num_months 
								ELSE 
									CASE WHEN
										ad_group.cost_type = 'flat' 
										AND ad_group.created_at >= '{$args['ds']}' 
										AND ad_group.created_at <= '{$args['de']}' 
									THEN
										ad_group.cost
									ELSE
										0
									END
							END
						)
					,0) AS `cost`
				FROM
					ad_group
				INNER JOIN
					campaign
						ON campaign.id = ad_group.campaign_id
				WHERE
					ad_group.deleted = 0
					AND (ad_group.cost_type = 'monthly' OR ad_group.cost_type = 'flat')
				GROUP BY campaign.id
			) AS `cost_table`
				ON cost_table.campaign_id = campaign.id
		WHERE
		  campaign.deleted = 0 ";

if (count($args['selected']) > 0) {
	$sql .= "AND campaign.id IN (" . implode(', ', $args['selected']) . ") ";
}				 
if (!empty($args['restrict_to'])) {
	$sql .= "AND campaign.id = " . $args['restrict_to'] . " ";
}

$sql .= " GROUP BY ";
		
if (isset($args['graph'])) {
	$sql .= "DATE(CONVERT_TZ(click.created_at,'GMT','$timezone')) 
		  HAVING `date` IS NOT NULL";
} else {
	$sql .= "campaign.id";
}

$sql .= " ORDER BY ";

if (isset($args['sort']) && isset($args['order'])) {
	$sql .= $args['sort'] . " " . $args['order'] . ",";
}
		
$sql .= " campaign.id ASC ";

if (!isset($args['graph']))  
	$sql .= "LIMIT " . $args['offset'] . ", " . $args['limit'];