dashdata();
}
public function memberlist(){
global $bko_users_members_access_list;
$mysql = "SELECT 'Select' AS select, id,username,firstname,lastname, last_login,loc,"
. " '' AS ACT "
. " "
. " FROM members WHERE id>0 ";
if (isset($bko_users_members_access_list) && $bko_users_members_access_list!="") {
$mysql .= " AND id IN (${bko_users_members_access_list})";
}
$mysql.= "ORDER BY id ASC";
$mysql = "SELECT count(*) AS count FROM members WHERE id>0";
if (isset($bko_users_members_access_list) && $bko_users_members_access_list!="") {
$mysql .= " AND id IN (${bko_users_members_access_list})";
}
$r = $this->read_replica->query($mysql);
$f = $r->row_array();
$this->load->library( 'pagination' );
$config = array();
//$query = $this->db->query( $mysql );
$config["total_rows"] = $f['count']; //$query->num_rows();
$config["base_url"] = base_url() . "/dash/memberlist";
$config["per_page"] = 15;
$config["uri_segment"] = 3;
$config["num_links"] = 5;
$config['full_tag_open'] = "
";
$this->table->set_template( $this->template );
//coalesce(xx,'')
$mysql = "SELECT 'Select' AS select, id,"
. "username,firstname||' '||lastname AS name,decision_group, last_login,loc,"
. " ' ' ||"
. " CASE WHEN status = 0 OR login_failures >= 5 THEN '' "
. " WHEN status = 1 OR login_failures < 5 THEN '' "
. " ELSE '' "
. " END "
. " AS ACT "
. " "
. " FROM members WHERE id>0 ";
if (isset($bko_users_members_access_list) && $bko_users_members_access_list!="") {
$mysql .= " AND id IN (${bko_users_members_access_list})";
}
$mysql .= "ORDER BY id ASC LIMIT " . $config["per_page"] . " OFFSET " . $page . " ";
$query = $this->read_replica->query( $mysql );
$this->table->set_heading( array( 'data' => 'Select', 'style' => 'width:40px' ), array( 'data' => 'ID',
'style' => 'width:30px'
), 'Email', 'Personalty','Name', 'Last Login', 'Location', array( 'data' => 'ACT',
'style' => 'width:40px; text-align: center;'
) );
$data['member_table'] = $this->table->generate( $query );
$data["links"] = $this->pagination->create_links();
$data['google_api_key'] = $savvyext->cfgReadChar( 'google.api_key' );
$data["page_title"] = "";
$data["page_link"] = $this->pagination->create_links();
$q = "SELECT count(id) AS dcount ,added::date AS dt FROM members WHERE added + '27 days' > now() GROUP BY added::date ORDER BY added::date LIMIT 7";
$query = $this->read_replica->query($q);
$data['plot_signup'] = $query->result_array();
$q = "SELECT count(id),created::date FROM trackedemail_item GROUP BY created::date ORDER BY created DESC LIMIT 30";
$query = $this->read_replica->query($q);
$data['plot_emaildownload'] = $query->result_array();
//How many Farm records generated daliy - last 10 days [SG & US] - 2 plots
$q = "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='SG' 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='SG'
GROUP BY date_trunc('day',created)) AS b ON (b.created=a.created) ORDER BY a.created DESC";
$query = $this->read_replica->query( $q );
$data['records_generated_daliy_SG'] = $this->table->generate( $query );
$q = "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='US' 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='US'
GROUP BY date_trunc('day',created)) AS b ON (b.created=a.created) ORDER BY a.created DESC";
$query = $this->read_replica->query( $q );
$data['records_generated_daliy_US'] = $this->table->generate( $query );
$this->renderAdminPage( 'view_start', $data );
// echo 'Ameye Olu';
}
public function dashpage() {
global $savvyext;
$this->load->helper( 'url' );
$data = array();
$this->load->library( 'table' );
$this->table->set_template( $this->template );
$mysql = "SELECT '' AS action ,username,firstname,lastname FROM members";
$query = $this->read_replica->query( $mysql );
$this->table->set_heading( array( 'data' => 'ACTION',
'style' => 'width:90px'
), 'USERNAME', 'FIRSTNAME', 'LASTNAME' );
$data['member_table'] = $this->table->generate( $query );
$data['google_api_key'] = $savvyext->cfgReadChar( 'google.api_key' );
//$data['js'] = array('https://maps.googleapis.com/maps/api/js?key=AIzaSyDvjiRTxngOQyBP4zpqFlZuiquc0ROvo9c&callback=initMap');
$this->renderAdminPage( 'view_dash', $data );
// echo 'Ameye Olu';
}
}