dev-chiefworks
64 lines
1.9 KiB
PHP
64 lines
1.9 KiB
PHP
<?php if (!defined('BASEPATH')) {
|
|
exit('No direct script access allowed');
|
|
}
|
|
|
|
class Farm_record_model extends CI_Model
|
|
{
|
|
private $read_replica;
|
|
|
|
public function __construct()
|
|
{
|
|
parent::__construct();
|
|
$this->read_replica = $this->load->database('savvy_replica', TRUE);
|
|
}
|
|
|
|
public function get_farm_records(
|
|
$filters
|
|
) {
|
|
$query =
|
|
"SELECT
|
|
a.*,
|
|
b.total AS success,
|
|
CASE
|
|
WHEN a.total > 0 THEN round(100.0 * b.total / a.total, 2)
|
|
ELSE 0.0 END AS success_rate
|
|
FROM
|
|
(
|
|
SELECT
|
|
date_trunc('day', aa.created) AS created,
|
|
count(*) AS total
|
|
FROM
|
|
quotes aa
|
|
LEFT JOIN address ab ON
|
|
ab.id = aa.location_start_id
|
|
WHERE
|
|
aa.created>now() - interval '9 days'
|
|
AND ab.country = '{$filters['country']}'
|
|
GROUP BY
|
|
date_trunc('day', aa.created)
|
|
) AS a
|
|
LEFT JOIN
|
|
(
|
|
SELECT
|
|
date_trunc('day', ba.created) AS created,
|
|
count(*) AS total
|
|
FROM
|
|
quotes ba
|
|
LEFT JOIN address bb ON
|
|
bb.id = ba.location_start_id
|
|
WHERE
|
|
ba.created > now() - interval '9 days'
|
|
AND ba.cost > 0
|
|
AND bb.country = '{$filters['country']}'
|
|
GROUP BY
|
|
date_trunc('day', created)
|
|
) AS b
|
|
ON (b.created = a.created)
|
|
ORDER BY
|
|
a.created DESC
|
|
";
|
|
|
|
return $this->read_replica->query($query);
|
|
}
|
|
}
|