Cbs_report_consumer_model.php

	function netacy_report()
	{
		$cbs_deal_id = 105;
		$report_date = date('Y-m-d', strtotime('yesterday'));
		$title = sprintf("CBS Report: %s: %s", $this->get_title($cbs_deal_id), $report_date);
		$payable_sql = <<<EOQ
SELECT sum(CASE cbs_entry_post_status_id
                WHEN 1 THEN gross
                WHEN 2 THEN gross
                WHEN 3 THEN gross
        END) payable_records
FROM cbs.cbs_reports
WHERE report_date = date(now() - interval 1 day) AND cbs_deal_id={$cbs_deal_id}
EOQ;

		$query = $this->db->query($payable_sql);
		$row = $query->row_array();
		$payable_records = $row['payable_records'];

		$query = $this->db->query("SELECT * FROM cbs_gross_capacity WHERE cbs_deal_id={$cbs_deal_id}");
		$row = $query->row_array();
		$matts_limit = $row['capacity'];

		if ($payable_records > $matts_limit) {
			$correction = $matts_limit / $payable_records;
		} else {
			$correction = 1;
		}

		$sql = <<<EOQ
SELECT
        report_date,
        source_domain,
        gross,
        round(({$correction} * payable),0) payable,
        round(({$correction} * validation_errors),0) validation_errors, 
        round(({$correction} * over_capacity),0) over_capacity,
        round((((({$correction} * payable) * 0.85) / 2 ) * 0.14),2) estimated
FROM (
	SELECT
        report_date,
        source_domain,
        sum(IF(cbs_entry_post_status_id < 4,gross,0)) gross,
        sum(IF(cbs_entry_post_status_id < 4,gross,0)) payable,
    	sum(IF(cbs_entry_post_status_id < 7 AND cbs_entry_post_status_id > 3,gross,0)) validation_errors,
        sum(IF(cbs_entry_post_status_id = 7,gross,0)) AS over_capacity
	FROM cbs.cbs_reports
        INNER JOIN cbs.cbs_deals USING(cbs_deal_id)
        INNER JOIN cbs.cbs_source_domains USING(cbs_source_domain_id)
	WHERE report_date = date(now() - interval 1 day) AND cbs_deal_id=105
	GROUP BY 1,2
) dataset
ORDER BY gross DESC;
EOQ;
		$query = $this->db->query($sql);

		return array(
			'title'  => $title,
			'report' => $query->result_array(),
			'sql'    => $sql,
			'error'  => $this->db->error(),
		);
	}