|
Server : Apache/2.4.18 (Ubuntu) System : Linux canvaswebdesign 3.13.0-71-generic #114-Ubuntu SMP Tue Dec 1 02:34:22 UTC 2015 x86_64 User : oppastar ( 1041) PHP Version : 7.0.33-0ubuntu0.16.04.15 Disable Function : pcntl_alarm,pcntl_fork,pcntl_waitpid,pcntl_wait,pcntl_wifexited,pcntl_wifstopped,pcntl_wifsignaled,pcntl_wifcontinued,pcntl_wexitstatus,pcntl_wtermsig,pcntl_wstopsig,pcntl_signal,pcntl_signal_dispatch,pcntl_get_last_error,pcntl_strerror,pcntl_sigprocmask,pcntl_sigwaitinfo,pcntl_sigtimedwait,pcntl_exec,pcntl_getpriority,pcntl_setpriority, Directory : /var/www/laciasmara.com/public_html/shop/application/models/ |
Upload File : |
<?php if (!defined('BASEPATH')) exit('No direct script access allowed');
class Customer_m extends MY_Model
{
protected $_table_name = 'customers';
protected $_primary_key = 'id_customers';
protected $_order_by = 'id_customers';
public $rules = array(
'email' => array(
'field' => 'login_email',
'label' => 'Email',
'rules' => 'trim|required|valid_email'
),
'password' => array(
'field' => 'login_password',
'label' => 'Password',
'rules' => 'trim|required'
)
);
function __construct()
{
parent::__construct();
}
//function for login
public function login($email)
{
// Check if the provided email exists in either the email or email_alt fields
$customer = $this->get_by(
"(email = '$email' OR email_alt = '$email') AND password = '" . $this->hash($this->input->post('password')) . "' AND status = 1",
TRUE
);
if (count($customer)) {
// If a customer is found, log them in
$customer_data = array(
'customer_name' => $customer->name,
'reseller_id' => $customer->reseller_id,
'customer_email' => $customer->email,
'customer_id' => $customer->id_customers,
'customer_loggedin' => TRUE,
'customer_type' => 'regular',
'customer_district' => $customer->district,
'customer_province' => $customer->province,
'customer_country' => $customer->country,
);
$this->session->set_userdata(array('customer' => $customer_data));
return true;
}
return false;
}
public function login_by_email($email)
{
$customer = $this->_getCustomerByEmailAndPassword($email);
if (empty($customer)) {
return false;
}
$this->_setCustomerSession($customer);
return true;
}
private function _getCustomerByEmailAndPassword($email)
{
$hashed_password = $this->hash($this->input->post('login_password'));
return $this->get_by(
"(email = '$email' OR email_alt = '$email')
AND password = '$hashed_password'
AND status = 1",
TRUE
);
}
public function get_customer_addresses($customer_id)
{
$this->db->select('customer_addresses.*, customers.is_first');
$this->db->from('customer_addresses');
$this->db->join('customers', 'customers.id_customers = customer_addresses.customer_id', 'left');
$this->db->where('customer_addresses.customer_id', $customer_id);
$this->db->order_by('customer_addresses.is_default', 'DESC');
$this->db->order_by('customer_addresses.created_at', 'ASC');
$query = $this->db->get();
return $query->result();
}
public function get_customer_address_by_id($customer_id, $address_id)
{
$this->db->select('customer_addresses.*, customers.is_first');
$this->db->from('customer_addresses');
$this->db->join('customers', 'customers.id_customers = customer_addresses.customer_id', 'left');
$this->db->where('customer_addresses.customer_id', $customer_id);
$this->db->where('customer_addresses.id', $address_id);
return $this->db->get()->row();
}
public function is_first_address_by_district($customer_id, $district_id)
{
$this->db->where('customer_id', $customer_id);
$this->db->where('rajaongkir_district_id', $district_id);
$this->db->where('is_first', 1);
$address = $this->db->get('customer_addresses')->row();
return $address ? true : false;
}
public function set_default($id, $customer_id)
{
$this->db->where('id', $id);
$this->db->where('customer_id', $customer_id);
return $this->db->update('customer_addresses', ['is_default' => 1]);
}
public function unset_default($customer_id)
{
$this->db->where('customer_id', $customer_id);
return $this->db->update('customer_addresses', ['is_default' => 0]);
}
public function get_address_by_id($id)
{
return $this->db->get_where('customer_addresses', ['id' => $id])->row();
}
public function delete_address($id)
{
return $this->db->delete('customer_addresses', ['id' => $id]);
}
public function get_other_address($customer_id, $exclude_id)
{
$this->db->where('customer_id', $customer_id);
$this->db->where('id !=', $exclude_id);
$this->db->order_by('created_at', 'ASC');
return $this->db->get('customer_addresses')->row();
}
private function _setCustomerSession($customer)
{
$customer_data = [
'customer_name' => $customer->name,
'reseller_id' => $customer->reseller_id,
'customer_email' => $customer->email,
'customer_id' => $customer->id_customers,
'customer_loggedin' => TRUE,
'customer_type' => 'regular',
'customer_district' => $customer->district,
'customer_province' => $customer->province,
'customer_country' => $customer->country,
];
$this->session->set_userdata(['customer' => $customer_data]);
}
public function email_exists($email)
{
return $this->db->where('email', $email)
->count_all_results('customers') > 0;
}
public function login_sms()
{
$customer = $this->get_by(array(
'phone' => $this->security->xss_clean($this->input->post('phone'))
), TRUE);
if (count($customer)) {
//if customer is exist in database, then log them in..
$customer_data = array(
'customer_name' => $customer->name,
'customer_email' => $customer->email,
'customer_id' => $customer->id_customers,
'customer_loggedin' => TRUE,
'customer_type' => 'regular'
);
$this->session->set_userdata(array('customer' => $customer_data));
}
}
//function for 1st login after registration
public function first_login_guest()
{
$customer = $this->get_by(array(
'email' => $this->security->xss_clean($this->input->post('register_email')),
/* 'password' => NULL */
), TRUE);
if (count($customer)) {
//if customer is exist in database, then log them in..
$customer_data = array(
'customer_name' => $customer->name,
'customer_email' => $customer->email,
'customer_id' => $customer->id_customers,
'customer_loggedin' => TRUE,
'customer_type' => 'regular'
);
$this->session->set_userdata(array('customer' => $customer_data));
}
}
//function for logout
public function logout()
{
$this->session->unset_userdata('customer');
}
//IS USED: function to check if logged in, true if loggedin
public function loggedin()
{
return (bool) $this->session->userdata('customer')['customer_loggedin'];
}
//function for hashing SHA512
public function hash($string)
{
return hash('sha512', $string . config_item('encryption_key'));
//password is salted with encryption key, and then use sha512
}
//count existing email
function cek_existing_email($email, $customer_id)
{
$this->db->select('id_customers');
$this->db->from('customers');
$this->db->where('email', $email);
if ($customer_id != NULL) {
$this->db->where('id_customers !=', $customer_id);
}
$query = $this->db->get();
return $query->num_rows();
}
//count existing phone
function cek_existing_phone($phone, $customer_id)
{
$this->db->select('id_customers');
$this->db->from('customers');
$this->db->where('phone', $phone);
if ($customer_id != NULL) {
$this->db->where('id_customers !=', $customer_id);
}
$query = $this->db->get();
return $query->num_rows();
}
//count smscode
function cek_smscode($code)
{
$this->db->select('id_sms_code');
$this->db->from('sms_code');
$this->db->where('phone', $this->security->xss_clean($this->input->post('phone')));
$this->db->where('sms_code', $code);
$query = $this->db->get();
return $query->num_rows();
}
//get customer by id customer
function get_customer($id_customer)
{
$this->db->select('*');
$this->db->from('customers');
$this->db->where('id_customers', $id_customer);
$query = $this->db->get();
return $query->row();
}
function get_customer_by_id($id_customer)
{
$this->db->select('name, phone, join_date');
$this->db->from('customers');
$this->db->where('id_customers', $id_customer);
$query = $this->db->get();
return $query->row_array();
}
//update customer profile
function update_profile($id, $data)
{
$this->db->where('id_customers', $id);
$this->db->update('customers', $data);
}
//get shipping
function get_shipping($id_customer)
{
$this->db->select('*');
$this->db->from('customers');
$this->db->where('id_customers', $id_customer);
$query = $this->db->get();
return $query->row();
}
//update shipping
function update_shipping($id, $data)
{
$this->db->where('id_customers', $id);
$this->db->update('customers', $data);
}
//function to return a new user
public function get_new()
{
$user = new stdClass();
$user->name = '';
$user->sex_type = '';
$user->newsletter = '';
$user->is_delete = '';
$user->title = '';
$user->email = '';
$user->phone = '';
$user->password = '';
$user->address = '';
$user->postcode = '';
$user->status = '';
$user->reseller_id = '';
$user->current_pointreward = '';
$user->dropship = '';
$user->shipping_address = '';
$user->shipping_postcode = '';
$user->type = '';
$user->is_first = '';
return $user;
}
//function count all record for customers
public function record_count()
{
$this->db->select('*')->from('customers')->where('is_delete', 'no');
$count = $this->db->get()->num_rows();
return $count;
}
//get all customer with pagination included
function get_all_customers($limit, $start)
{
$this->db->select('*,TIMESTAMPDIFF(YEAR, birthday, CURDATE()) AS age');
$this->db->from('customers');
$this->db->order_by('join_date', 'ASC');
$this->db->limit($limit, $start);
$query = $this->db->get();
return $query->result();
}
function get_all_ranking()
{
$this->db->select('orders.customer_id, customers.email, customers.phone , customers.name as name, COUNT(*) as jumlah');
$this->db->from('orders');
$this->db->join('customers', 'customers.id_customers = orders.customer_id');
$this->db->where('payment_status', 5);
$this->db->where('reseller_id', NULL);
$this->db->where_not_in('orders.customer_id', array(21, 815, 2615, 1427, 2190, 2496, 1964, 2768, 2611, 2283, 2796));
$this->db->group_by('orders.customer_id');
$this->db->order_by('jumlah', 'DESC');
$query = $this->db->get();
return $query->result();
}
function get_all_ranking_nominal()
{
$this->db->select('orders.customer_id, customers.email, customers.phone, customers.name as name, COUNT(*) as jumlah, SUM(orders.grand_total_amount) as grand_total_amount');
$this->db->from('orders');
$this->db->join('customers', 'customers.id_customers = orders.customer_id');
$this->db->where('payment_status', 5);
$this->db->where('reseller_id', NULL);
$this->db->where_not_in('orders.customer_id', array(21, 815, 2615, 1427, 2190, 2496, 1964, 2768, 2611, 2283, 2796));
$this->db->group_by('orders.customer_id');
$this->db->order_by('grand_total_amount', 'DESC');
$query = $this->db->get();
return $query->result();
}
function get_all_ranking_filtered($startDate, $endDate)
{
$this->db->select('orders.customer_id, customers.email, customers.phone , customers.name as name, COUNT(*) as jumlah');
$this->db->from('orders');
$this->db->join('customers', 'customers.id_customers = orders.customer_id');
$this->db->where('payment_status', 5);
$this->db->where('order_date >=', $startDate);
$this->db->where('order_date <=', $endDate);
$this->db->where('reseller_id', NULL);
$this->db->where_not_in('orders.customer_id', array(21, 815, 2615, 1427, 2190, 2496, 1964, 2768, 2611, 2283, 2796));
$this->db->group_by('orders.customer_id');
$this->db->order_by('jumlah', 'DESC');
$query = $this->db->get();
return $query->result();
}
function get_all_ranking_nominal_filtered($startDate, $endDate)
{
$this->db->select('orders.customer_id, customers.email, customers.phone, customers.name as name, COUNT(*) as jumlah, SUM(orders.grand_total_amount) as grand_total_amount');
$this->db->from('orders');
$this->db->join('customers', 'customers.id_customers = orders.customer_id');
$this->db->join('orders_detail', 'orders.id_orders = orders_detail.orders_id'); // Assuming there's a join condition between orders and orders_detail
$this->db->where('orders.payment_status', 5);
$this->db->where('orders.order_date >=', $startDate);
$this->db->where('orders.order_date <=', $endDate);
$this->db->where('customers.reseller_id', NULL);
$this->db->where_not_in('orders.customer_id', array(21, 815, 2615, 1427, 2190, 2496, 1964, 2768, 2611, 2283, 2796));
$this->db->group_by('orders.customer_id');
$this->db->order_by('grand_total_amount', 'DESC');
$query = $this->db->get();
return $query->result();
}
//excel export customer data
function excel_export()
{
$this->db->select('name, email, phone, birthday, address, province, district, subdistrict, type');
$this->db->from('customers');
$this->db->order_by('join_date', 'DESC');
$query = $this->db->get();
return $query->result();
}
public function checkUser($user_data)
{
// Memeriksa apakah user sudah ada
$this->db->select('id_customers');
$this->db->from('customers');
$this->db->where('oauth_uid', $user_data['oauth_uid']);
$query = $this->db->get();
if ($query->num_rows() > 0) {
// User sudah ada, update datanya
$this->db->where('oauth_uid', $user_data['oauth_uid']);
$this->db->update('customers', $user_data);
return $query->row()->id_customers; // Kembalikan ID user yang ada
} else {
// User belum ada, insert data baru
$this->db->insert('customers', $user_data);
return $this->db->insert_id(); // Kembalikan ID user yang baru ditambahkan
}
}
public function get_user_by_email($email)
{
return $this->db->get_where('customers', ['email' => $email])->row();
}
public function get_user_by_id($id)
{
return $this->db->get_where('customers', ['id_customers' => $id])->row();
}
public function fetch_all_customers()
{
$this->db->select('*');
$this->db->from('customers');
$this->db->where('type', 'regular');
$query = $this->db->get();
return $query->result();
}
public function fetch_all_retailers()
{
$this->db->select('*');
$this->db->from('customers');
$this->db->where('type', 'regular');
$this->db->where('reseller_id IS NOT NULL');
$this->db->where('reseller_id !=', '');
$query = $this->db->get();
return $query->result();
}
public function fetch_customer_by_id($customer_id)
{
return $this->db->get_where('customers', ['id_customers' => $customer_id])->row();
}
// Customer Statistic
/**
* Get date range based on filter
*/
public function get_date_range($date_filter, $start_date = null, $end_date = null)
{
$ranges = [
'today' => [
'start' => date('Y-m-d'),
'end' => date('Y-m-d')
],
'yesterday' => [
'start' => date('Y-m-d', strtotime('-1 day')),
'end' => date('Y-m-d', strtotime('-1 day'))
],
'last7days' => [
'start' => date('Y-m-d', strtotime('-6 days')),
'end' => date('Y-m-d')
],
'last30days' => [
'start' => date('Y-m-d', strtotime('-29 days')),
'end' => date('Y-m-d')
],
'thisMonth' => [
'start' => date('Y-m-01'),
'end' => date('Y-m-t')
],
'lastMonth' => [
'start' => date('Y-m-01', strtotime('first day of last month')),
'end' => date('Y-m-t', strtotime('last day of last month'))
],
'thisYear' => [
'start' => date('Y-01-01'),
'end' => date('Y-12-31')
],
'lastYear' => [
'start' => date('Y-01-01', strtotime('-1 year')),
'end' => date('Y-12-31', strtotime('-1 year'))
]
];
if ($date_filter === 'custom' && $start_date && $end_date) {
return [
'start' => date('Y-m-d', strtotime($start_date)),
'end' => date('Y-m-d', strtotime($end_date))
];
}
return $ranges[$date_filter] ?? $ranges['last7days'];
}
/**
* Get previous period for comparison (YoY)
*/
public function get_previous_period($start_date, $end_date)
{
$start = new DateTime($start_date);
$end = new DateTime($end_date);
return [
'start' => $start->modify('-1 year')->format('Y-m-d'),
'end' => $end->modify('-1 year')->format('Y-m-d')
];
}
/**
* Get new customers count
*/
public function get_new_customers($start_date, $end_date, $type = 'regular')
{
return $this->db->where('type', $type)
->where('is_delete', 'no')
->where('DATE(join_date) >=', $start_date)
->where('DATE(join_date) <=', $end_date)
->count_all_results('customers');
}
/**
* Get total customers by type
*/
public function get_total_customers_by_type()
{
$query = $this->db->query("
SELECT
type,
COUNT(*) as total
FROM customers
WHERE is_delete = 'no'
GROUP BY type
");
$result = [];
foreach ($query->result_array() as $row) {
$result[$row['type']] = (int)$row['total'];
}
return $result;
}
/**
* Get gender breakdown
*/
public function get_gender_breakdown($start_date, $end_date)
{
$query = $this->db->query("
SELECT
COALESCE(sex_type, 'tidak disebutkan') as gender,
COUNT(*) as total
FROM customers
WHERE type = 'regular'
AND is_delete = 'no'
AND DATE(join_date) >= ?
AND DATE(join_date) <= ?
GROUP BY sex_type
", [$start_date, $end_date]);
return $query->result_array();
}
/**
* Get age breakdown
*/
public function get_age_breakdown($start_date, $end_date)
{
$current_year = date('Y');
$query = $this->db->query("
SELECT
all_ranges.age_range,
COALESCE(customer_counts.total, 0) as total
FROM (
SELECT 'tidak disebutkan' as age_range, 1 as sort_order
UNION ALL SELECT '<18', 2
UNION ALL SELECT '18-24', 3
UNION ALL SELECT '25-34', 4
UNION ALL SELECT '35-44', 5
UNION ALL SELECT '45-54', 6
UNION ALL SELECT '55-64', 7
UNION ALL SELECT '65+', 8
) all_ranges
LEFT JOIN (
SELECT
CASE
WHEN birthday IS NULL OR birthday = '' THEN 'tidak disebutkan'
WHEN (? - YEAR(STR_TO_DATE(birthday, '%Y-%m-%d'))) < 18 THEN '<18'
WHEN (? - YEAR(STR_TO_DATE(birthday, '%Y-%m-%d'))) BETWEEN 18 AND 24 THEN '18-24'
WHEN (? - YEAR(STR_TO_DATE(birthday, '%Y-%m-%d'))) BETWEEN 25 AND 34 THEN '25-34'
WHEN (? - YEAR(STR_TO_DATE(birthday, '%Y-%m-%d'))) BETWEEN 35 AND 44 THEN '35-44'
WHEN (? - YEAR(STR_TO_DATE(birthday, '%Y-%m-%d'))) BETWEEN 45 AND 54 THEN '45-54'
WHEN (? - YEAR(STR_TO_DATE(birthday, '%Y-%m-%d'))) BETWEEN 55 AND 64 THEN '55-64'
ELSE '65+'
END as age_range,
COUNT(*) as total
FROM customers
WHERE type = 'regular'
AND is_delete = 'no'
AND DATE(join_date) >= ?
AND DATE(join_date) <= ?
GROUP BY age_range
) customer_counts ON all_ranges.age_range = customer_counts.age_range
ORDER BY all_ranges.sort_order
", [
$current_year, // 1
$current_year, // 2
$current_year, // 3
$current_year, // 4
$current_year, // 5
$current_year, // 6
$start_date, // 7
$end_date // 8
]);
return $query->result_array();
}
/**
* Get location breakdown (Province & City) from customer_addresses
* Menggunakan tabel customer_addresses yang baru
*/
public function get_location_breakdown($start_date, $end_date, $limit = 10)
{
// Top Provinces
$province_query = $this->db->query("
SELECT
COALESCE(ca.province, 'Belum diisi') as province,
COUNT(DISTINCT c.id_customers) as total
FROM customers c
LEFT JOIN customer_addresses ca ON c.id_customers = ca.customer_id AND ca.is_default = 1
WHERE c.type = 'regular'
AND c.is_delete = 'no'
AND DATE(c.join_date) >= ?
AND DATE(c.join_date) <= ?
GROUP BY ca.province
ORDER BY total DESC
LIMIT ?
", [$start_date, $end_date, $limit]);
// Top Cities
$city_query = $this->db->query("
SELECT
COALESCE(ca.city, 'Belum diisi') as city,
COALESCE(ca.province, 'Belum diisi') as province,
COUNT(DISTINCT c.id_customers) as total
FROM customers c
LEFT JOIN customer_addresses ca ON c.id_customers = ca.customer_id AND ca.is_default = 1
WHERE c.type = 'regular'
AND c.is_delete = 'no'
AND DATE(c.join_date) >= ?
AND DATE(c.join_date) <= ?
GROUP BY ca.city, ca.province
ORDER BY total DESC
LIMIT ?
", [$start_date, $end_date, $limit]);
// Top Districts
$district_query = $this->db->query("
SELECT
COALESCE(ca.district, 'Belum diisi') as district,
COALESCE(ca.city, 'Belum diisi') as city,
COUNT(DISTINCT c.id_customers) as total
FROM customers c
LEFT JOIN customer_addresses ca ON c.id_customers = ca.customer_id AND ca.is_default = 1
WHERE c.type = 'regular'
AND c.is_delete = 'no'
AND DATE(c.join_date) >= ?
AND DATE(c.join_date) <= ?
GROUP BY ca.district, ca.city
ORDER BY total DESC
LIMIT ?
", [$start_date, $end_date, $limit]);
return [
'provinces' => $province_query->result_array(),
'cities' => $city_query->result_array(),
'districts' => $district_query->result_array()
];
}
/**
* Get customer registration trend (daily/weekly/monthly)
*/
public function get_registration_trend($start_date, $end_date, $group_by = 'day')
{
$date_format = [
'day' => '%Y-%m-%d',
'week' => '%Y-%u',
'month' => '%Y-%m',
'year' => '%Y'
];
$format = $date_format[$group_by] ?? $date_format['day'];
$query = $this->db->query("
SELECT
DATE_FORMAT(join_date, ?) as period,
COUNT(*) as total
FROM customers
WHERE type = 'regular'
AND is_delete = 'no'
AND DATE(join_date) >= ?
AND DATE(join_date) <= ?
GROUP BY period
ORDER BY period ASC
", [$format, $start_date, $end_date]);
return $query->result_array();
}
/**
* Get customer source breakdown
*/
public function get_source_breakdown($start_date, $end_date)
{
$query = $this->db->query("
SELECT
CASE
WHEN source IS NULL OR source = '' THEN 'Direct/Unknown'
ELSE source
END as source,
COUNT(*) as total
FROM customers
WHERE type = 'regular'
AND is_delete = 'no'
AND DATE(join_date) >= ?
AND DATE(join_date) <= ?
GROUP BY source
ORDER BY total DESC
", [$start_date, $end_date]);
return $query->result_array();
}
/**
* Get newsletter subscribers
*/
public function get_newsletter_stats($start_date, $end_date)
{
$query = $this->db->query("
SELECT
newsletter,
COUNT(*) as total
FROM customers
WHERE type = 'regular'
AND is_delete = 'no'
AND DATE(join_date) >= ?
AND DATE(join_date) <= ?
GROUP BY newsletter
", [$start_date, $end_date]);
return $query->result_array();
}
/**
* Get OAuth provider breakdown
*/
public function get_oauth_breakdown($start_date, $end_date)
{
$query = $this->db->query("
SELECT
CASE
WHEN oauth_provider IS NULL THEN 'Email/Password'
ELSE oauth_provider
END as provider,
COUNT(*) as total
FROM customers
WHERE type = 'regular'
AND is_delete = 'no'
AND DATE(join_date) >= ?
AND DATE(join_date) <= ?
GROUP BY provider
ORDER BY total DESC
", [$start_date, $end_date]);
return $query->result_array();
}
/**
* Get dropshipper stats
*/
public function get_dropshipper_stats($start_date, $end_date)
{
$query = $this->db->query("
SELECT
dropship,
COUNT(*) as total
FROM customers
WHERE type = 'regular'
AND is_delete = 'no'
AND DATE(join_date) >= ?
AND DATE(join_date) <= ?
GROUP BY dropship
", [$start_date, $end_date]);
return $query->result_array();
}
/**
* Get affiliate stats
*/
public function get_affiliate_stats()
{
$query = $this->db->query("
SELECT
CASE
WHEN affiliate IS NULL THEN 'non_affiliate'
ELSE affiliate
END as status,
COUNT(*) as total
FROM customers
WHERE type = 'regular'
AND is_delete = 'no'
GROUP BY status
");
return $query->result_array();
}
/**
* Get customer address statistics
*/
public function get_address_stats()
{
// Customers with multiple addresses
$multiple_addresses = $this->db->query("
SELECT
COUNT(DISTINCT customer_id) as total
FROM customer_addresses
GROUP BY customer_id
HAVING COUNT(*) > 1
")->num_rows();
// Average addresses per customer
$avg_addresses = $this->db->query("
SELECT
AVG(address_count) as avg_count
FROM (
SELECT
customer_id,
COUNT(*) as address_count
FROM customer_addresses
GROUP BY customer_id
) as address_counts
")->row()->avg_count ?? 0;
// Customers with complete address data
$complete_addresses = $this->db->query("
SELECT
COUNT(DISTINCT ca.customer_id) as total
FROM customer_addresses ca
WHERE ca.province IS NOT NULL
AND ca.province != ''
AND ca.city IS NOT NULL
AND ca.city != ''
AND ca.district IS NOT NULL
AND ca.district != ''
AND ca.postal_code IS NOT NULL
AND ca.postal_code != ''
")->row()->total ?? 0;
// Total customers with addresses
$total_with_address = $this->db->query("
SELECT COUNT(DISTINCT customer_id) as total
FROM customer_addresses
")->row()->total ?? 0;
return [
'customers_with_multiple_addresses' => (int)$multiple_addresses,
'average_addresses_per_customer' => round($avg_addresses, 2),
'customers_with_complete_address' => (int)$complete_addresses,
'total_customers_with_address' => (int)$total_with_address,
'percentage_complete' => $total_with_address > 0
? round(($complete_addresses / $total_with_address) * 100, 2)
: 0
];
}
/**
* Get data quality metrics
*/
public function get_data_quality_metrics($start_date, $end_date)
{
$query = $this->db->query("
SELECT
COUNT(*) as total_customers,
SUM(CASE WHEN birthday IS NULL OR birthday = '' THEN 1 ELSE 0 END) as missing_birthday,
SUM(CASE WHEN phone IS NULL OR phone = '' THEN 1 ELSE 0 END) as missing_phone,
SUM(CASE WHEN sex_type IS NULL THEN 1 ELSE 0 END) as missing_gender,
SUM(CASE WHEN email_alt IS NULL OR email_alt = '' THEN 1 ELSE 0 END) as missing_alt_email
FROM customers
WHERE type = 'regular'
AND is_delete = 'no'
AND DATE(join_date) >= ?
AND DATE(join_date) <= ?
", [$start_date, $end_date]);
$result = $query->row_array();
$total = (int)$result['total_customers'];
if ($total === 0) {
return [
'total_customers' => 0,
'missing_birthday' => 0,
'missing_phone' => 0,
'missing_gender' => 0,
'missing_alt_email' => 0,
'completeness_score' => 0
];
}
// Calculate completeness score (percentage of complete profiles)
$missing_count = $result['missing_birthday'] + $result['missing_phone'] + $result['missing_gender'];
$completeness = 100 - (($missing_count / ($total * 3)) * 100);
return [
'total_customers' => $total,
'missing_birthday' => (int)$result['missing_birthday'],
'missing_phone' => (int)$result['missing_phone'],
'missing_gender' => (int)$result['missing_gender'],
'missing_alt_email' => (int)$result['missing_alt_email'],
'completeness_score' => round($completeness, 2)
];
}
/**
* Get customer with point rewards
*/
public function get_point_rewards_stats()
{
$query = $this->db->query("
SELECT
COUNT(*) as customers_with_points,
SUM(current_pointreward) as total_points,
AVG(current_pointreward) as avg_points,
MAX(current_pointreward) as max_points
FROM customers
WHERE type = 'regular'
AND is_delete = 'no'
AND current_pointreward > 0
");
return $query->row_array();
}
}