gistfile1.txt

<?php

class EventTable extends Doctrine_Table {

	public function getInstance() {
		return Doctrine_Core::getTable('Event');
	}
    
    public function create($product_id, $identity, $event_name, $properties) {

        $person = Doctrine_Core::getTable('Person')->findByAlias($product_id, $identity);

        if ($person == null) {
            $person = new Person();
            $person->setProductId($product_id);
            $person->save();

            $pa = new PersonAlias();
            $pa->setPersonId($person->getId());
            $pa->setAlias($identity);
            $pa->save();
        }
        
        $et = Doctrine_Core::getTable('EventType')->findOneByProductIdAndDescription($product_id, $event_name);
        if (!$et) {
            $et = new EventType();
            $et->setProductId($product_id);
            $et->setDescription($event_name);
            $et->setIsConversion(0);
            $et->save();
        }
        
        $event = new Event();
        $event->setProductId($product_id);
        $event->setPersonId($person->getId());
        $event->setEventTypeId($et->getId());
        $event->save();
        
        foreach ($properties as $key => $value) {
            $ep = new EventProperty();
            $ep->setEventId($event->getId());
            $ep->setName($key);
            $ep->setValue($value);
            $ep->save();
        }
                
    }
    
    public function getEventTable($product_id) {

        $sql = "SELECT
					DATE(event.created_at) AS `date`,
        			event.event_type_id,
        			event_type.description,
        			COUNT(*) AS `count`
        		FROM
        			event
        		INNER JOIN
        			event_type
        		ON
        			event.event_type_id = event_type.id
        		WHERE
        			event.product_id = ?
				AND
					event.created_at >= CURRENT_DATE - INTERVAL 30 DAY
        		GROUP BY
					DATE(event.created_at),
        			event_type.id
        		";
        
        $connection = Doctrine_Manager::connection();
        $statement = $connection->prepare($sql);
        $statement->execute(array($product_id));
        
        $rs = $statement->fetchAll(PDO::FETCH_ASSOC);

        return $rs;
                
    }
	
	public function getRevenue($product_id, $date_start, $date_end, $segment, $filters, $mode, $graph = false) {
		
		$et = Doctrine_core::getTable('EventType')->findOneByProductIdAndDescription($product_id, 'Ad Click');
		if (!$et) return array();
		
		$ad_click = $et->getId();
		
		if ($mode == 'first') {
			$agg = 'MIN';
		} else {
			$agg = 'MAX';
		}
		
		//Notes: type=lead/sale, sum case when lead then 1 else 0...
		
		$params = array();
		
		$sql = "
		SELECT
			DATE(sale.created_at) AS `date`,
			event_property.name,
			event_property.value,
			COUNT(sale.id) AS `conversions`,
			SUM(ep_sale.value) AS `revenue`
		FROM
			event sale
		INNER JOIN
			event_property ep_sale
		ON
			ep_sale.event_id = sale.id
		AND
			ep_sale.name = 'revenue'
		INNER JOIN
			event adclick
		ON
			adclick.person_id = sale.person_id
		AND
			adclick.event_type_id = $ad_click
		";
		
		$i = 0;
		foreach ($filters as $key => $value) {
			$ep = "ep" . $i;
			$sql .= "
			INNER JOIN
				event_property $ep
			ON
				{$ep}.event_id = adclick.id
			AND
				{$ep}.name = ? AND {$ep}.value = ?
			";
			$params[] = $key;
			$params[] = $value;
			$i++;
		}
		
		$sql .= "
		INNER JOIN
			(
			SELECT
				sale.id AS `sale_id`,
				{$agg}(adclick.id) AS `adclick_id`
			FROM
				event sale
			INNER JOIN
				event adclick
			ON
				sale.person_id = adclick.person_id
			AND
				adclick.event_type_id = $ad_click
			AND
				adclick.created_at < sale.created_at
			INNER JOIN
				event_property
			ON
				event_property.event_id = sale.id
			AND
				event_property.name = 'revenue'
			WHERE
				sale.product_id = $product_id
			AND
				sale.created_at BETWEEN '$date_start' AND '$date_end'
			GROUP BY
				sale.id
			) last_click
		ON
			adclick.id = last_click.adclick_id
		AND
			sale.id = last_click.sale_id
		INNER JOIN
			event_property
		ON
			event_property.event_id = adclick.id
		AND
			event_property.name = '$segment'
		WHERE
			sale.product_id = $product_id
		AND
			sale.created_at BETWEEN '$date_start' AND '$date_end'
		GROUP BY
		";
		
		if ($graph) {
			$sql .= "
				DATE(sale.created_at)
			";
		} else {
			$sql .= "
				event_property.value
			";
		}
		
		//echo $sql . "<br />";
				
        $connection = Doctrine_Manager::connection();
        $statement = $connection->prepare($sql);
        $statement->execute($params);
        $data = $statement->fetchAll(PDO::FETCH_ASSOC);
		
		$params = array();
		$sql = "
			SELECT
				DATE(event.created_at) AS `date`,
				event_property.value,
				COUNT(DISTINCT person_id) AS `people`,
				COUNT(*) AS `count`
			FROM
				event
			INNER JOIN
				event_property
			ON
				event_property.event_id = event.id AND event_property.name = '$segment'
		";

		$i = 0;
		foreach ($filters as $key => $value) {
			$ep = "ep" . $i;
			$sql .= "
			INNER JOIN
				event_property $ep
			ON
				{$ep}.event_id = event.id
			AND
				{$ep}.name = ? AND {$ep}.value = ?
			";
			$params[] = $key;
			$params[] = $value;
			$i++;
		}
			
		$sql .= "
			WHERE
				event.event_type_id = $ad_click
			AND
				event.created_at BETWEEN '$date_start' AND '$date_end'
			GROUP BY
		";

		if ($graph) {
			$sql .= "
				DATE(event.created_at)
			";
		} else {
			$sql .= "
				event_property.value
			";
		}

        $statement = $connection->prepare($sql);
        $statement->execute($params);
        $rs = $statement->fetchAll(PDO::FETCH_ASSOC);
		if ($graph) {
			foreach ($rs as $row) {
				$clicks[$row['date']] = $row['count'];
				$people[$row['date']] = $row['people'];
			}
			for ($i = strtotime($date_start); $i < strtotime($date_end); $i += 86400) {
				$date = date('Y-m-d', $i);
				$count = isset($clicks[$date]) ? $clicks[$date] : 0;
				$people = isset($people[$date]) ? $people[$date] : 0;
				$found = false;
				foreach ($data as $key => $row) {
					if ($row['date'] == $date) {
						$data[$key]['clicks'] = $count;
						$data[$key]['people'] = $count;
						$found = true;
					}
				}
				if (!$found) {
					$data[] = array('name' => $segment, 'date' => $date, 'clicks' => $count, 'people' => $people, 'conversions' => 0, 'revenue' => 0);
				}
			}
		} else {
			foreach ($rs as $clicks) {
				$found = false;
				foreach ($data as $key => $row) {
					if ($row['value'] == $clicks['value']) {
						$data[$key]['clicks'] = $clicks['count'];
						$data[$key]['people'] = $clicks['people'];
						$found = true;
					}
				}
				if (!$found) {
					$data[] = array('name' => $segment, 'value' => $clicks['value'], 'clicks' => $clicks['count'], 'people' => $clicks['people'], 'conversions' => 0, 'revenue' => 0);
				}
			}			
		}

		function cmp($a, $b) {
			return ($a['date'] < $b['date']) ? -1 : 1;
		}
		uasort($data, 'cmp');

		//die('<pre>' . print_r($data,1) . '</pre>');
		return $data;
	
	}

	public function getRetention($event_type_id, $datetype) {
				
		if ($datetype == 'week') {
			
			if (date('l') == 'Sunday') {
				$dates[] = date('Y-m-d');
			} else {
				$dates[] = date('Y-m-d', strtotime('last Sunday'));	
			}
			for ($i = 1; $i <= 7; $i++) {
				$dates[] = date('Y-m-d', strtotime('-7 days', strtotime($dates[$i - 1])));
			}
			$dates = array_reverse($dates);
			
			$groupby = 'YEARWEEK';
			$start = "STR_TO_DATE(CONCAT(YEARWEEK(MIN(event.created_at)), ' Sunday'), '%X%V %W') AS `start`";
			$ret = "STR_TO_DATE(CONCAT(YEARWEEK(MIN(ret.created_at)), ' Sunday'), '%X%V %W') AS `ret`";
			
		} else if ($datetype == 'day') {
			
			$dates[] = date('Y-m-d');
			for ($i = 1; $i <= 7; $i++) {
				$dates[] = date('Y-m-d', strtotime('-1 days', strtotime($dates[$i - 1])));
			}
			
			$dates = array_reverse($dates);
			
			$groupby = 'DATE';
			$start = "DATE(MIN(event.created_at)) AS `start`";
			$ret = "DATE(MIN(ret.created_at)) AS `ret`";
			
		} else {
			
			$dates[] = date('Y-n-d', mktime(0, 0, 0, date('n'), 1, date('Y')));
			for ($i = 1; $i <= 7; $i++) {
				$dates[] = date('Y-n-d', strtotime('-1 months', strtotime($dates[$i - 1])));
			}
			$dates = array_reverse($dates);
			
			$groupby = 'MONTH';
			$start = "CONCAT(YEAR(MIN(event.created_at)), '-', MONTH(MIN(event.created_at)), '-01') AS `start`";
			$ret = "CONCAT(YEAR(MIN(ret.created_at)), '-', MONTH(MIN(ret.created_at)), '-01') AS `ret`";
			
		}

		$sql = "
		SELECT
			{$start},
			{$ret},
			(SELECT COUNT(DISTINCT t.person_id) FROM event t WHERE t.event_type_id = ? AND t.CREATED_AT BETWEEN ? AND ?) AS `original`,
			COUNT(DISTINCT ret.person_id) AS `returns`
		FROM
			event
		LEFT OUTER JOIN
			event ret
		ON
			event.person_id = ret.person_id
		AND
			event.event_type_id = ret.event_type_id
		AND
			ret.created_at > event.created_at
		WHERE
			event.created_at BETWEEN ? AND ?
		AND
			ret.created_at > ?
		AND
			event.event_type_id = ?
		AND
			event.person_id NOT IN (SELECT t.person_id FROM event t WHERE t.event_type_id = ? AND t.created_at < ?)
		GROUP BY
			{$groupby}(ret.created_at)
		ORDER BY
			ret.created_at
		";
									
        $connection = Doctrine_Manager::connection();
        $statement = $connection->prepare($sql);
				
		for ($i = 0; $i < 7; $i++) {
			$params = array($event_type_id, $dates[$i], $dates[$i+1], $dates[$i], $dates[$i+1], $dates[0], $event_type_id, $event_type_id, $dates[$i]);
			$statement->execute($params);
			$data[] = $statement->fetchAll(PDO::FETCH_ASSOC);
		}
		
		return array($data, $dates);
		
	}
	
}