|
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/controllers/admin/ |
Upload File : |
<?php if (! defined('BASEPATH')) exit('No direct script access allowed');
class Statistic extends Admin_Controller
{
function __construct()
{
parent::__construct();
$this->load->model('statistic_m');
$this->load->model('log_m');
$this->load->model('Product_m');
}
//this is to list all homepage
public function index()
{
//load view
$this->data['subview'] = 'admin/statistic/index';
$this->load->view('admin/templates/header', $this->data);
$this->load->view('admin/_layout_main', $this->data);
$this->load->view('admin/templates/footer', $this->data);
}
// Sales Statistics
public function sales_statistics()
{
$data['userdata'] = $this->session->userdata();
$data['title'] = 'Statistik Penjualan | Laciasmara';
$this->load->view('admin_new/layouts/header', $data);
$this->load->view('admin_new/statistics/sales_statistics');
$this->load->view('admin_new/layouts/footer');
}
// public function get_sales_data()
// {
// $date_filter = $this->input->get('date_filter', 'last7days');
// $start_date = $this->input->get('start_date');
// $end_date = $this->input->get('end_date');
// // Tentukan rentang tanggal berdasarkan filter
// switch ($date_filter) {
// case 'today':
// $start_date = date('Y-m-d');
// $end_date = date('Y-m-d');
// break;
// case 'yesterday':
// $start_date = date('Y-m-d', strtotime('-1 day'));
// $end_date = date('Y-m-d', strtotime('-1 day'));
// break;
// case 'last7days':
// $start_date = date('Y-m-d', strtotime('-6 days'));
// $end_date = date('Y-m-d');
// break;
// case 'last30days':
// $start_date = date('Y-m-d', strtotime('-29 days'));
// $end_date = date('Y-m-d');
// break;
// case 'thisMonth':
// $start_date = date('Y-m-01');
// $end_date = date('Y-m-t');
// break;
// case 'thisYear':
// $start_date = date('Y-01-01');
// $end_date = date('Y-12-31');
// break;
// case 'custom':
// if ($start_date && $end_date) {
// $startDate = date('Y-m-d', strtotime($start_date));
// $endDate = date('Y-m-d', strtotime($end_date));
// if ($startDate && $endDate) {
// $this->db->where('DATE(order_date) >=', $start_date);
// $this->db->where('DATE(order_date) <=', $end_date);
// }
// }
// break;
// default:
// $start_date = date('Y-m-d', strtotime('-6 days'));
// $end_date = date('Y-m-d');
// break;
// }
// // Order type filters
// $order_type_filters = [
// 'isWebsiteOrder' => $this->input->get('isWebsiteOrder'),
// 'isRetailerOrder' => $this->input->get('isRetailerOrder'),
// 'isTokopediaOrder' => $this->input->get('isTokopediaOrder'),
// 'isAllOrder' => $this->input->get('isAllOrder'),
// ];
// // Payment method filters
// $payment_filters = [
// 'isBCATransfer' => $this->input->get('isBCATransfer'),
// 'isMandiriTransfer' => $this->input->get('isMandiriTransfer'),
// 'isPaypal' => $this->input->get('isPaypal'),
// 'isDoku' => $this->input->get('isDoku')
// ];
// // Shipping method filters
// $shipping_filters = [
// 'isRegularShipping' => $this->input->get('isRegularShipping'),
// 'isTwoHourShipping' => $this->input->get('isTwoHourShipping'),
// 'isOneDayShipping' => $this->input->get('isOneDayShipping'),
// 'isNextDayShipping' => $this->input->get('isNextDayShipping')
// ];
// // Insurance filters
// $insurance_filters = [
// 'usingInsurance' => $this->input->get('usingInsurance'),
// 'notUsingInsurance' => $this->input->get('notUsingInsurance')
// ];
// // Search parameter
// $search = $this->input->get('search');
// // Sort parameter
// $sort = $this->input->get('sort', '');
// // Get sales data with all filters
// $sales_data = $this->statistic_m->get_sales_data(
// $start_date,
// $end_date,
// $payment_filters,
// $shipping_filters,
// $insurance_filters,
// $order_type_filters,
// $search,
// $sort
// );
// $this->output
// ->set_content_type('application/json')
// ->set_output(json_encode([
// 'sales' => $sales_data['sales'],
// 'start_date' => $start_date,
// 'end_date' => $end_date
// ]));
// }
public function get_sales_data()
{
$date_filter = $this->input->get('date_filter', 'last7days');
$start_date = $this->input->get('start_date');
$end_date = $this->input->get('end_date');
// Tentukan rentang tanggal berdasarkan filter
switch ($date_filter) {
case 'today':
$start_date = date('Y-m-d');
$end_date = date('Y-m-d');
break;
case 'yesterday':
$start_date = date('Y-m-d', strtotime('-1 day'));
$end_date = date('Y-m-d', strtotime('-1 day'));
break;
case 'last7days':
$start_date = date('Y-m-d', strtotime('-6 days'));
$end_date = date('Y-m-d');
break;
case 'last30days':
$start_date = date('Y-m-d', strtotime('-29 days'));
$end_date = date('Y-m-d');
break;
case 'thisMonth':
$start_date = date('Y-m-01');
$end_date = date('Y-m-t');
break;
case 'thisYear':
$start_date = date('Y-01-01');
$end_date = date('Y-12-31');
break;
case 'custom':
if ($start_date && $end_date) {
$start_date = date('Y-m-d', strtotime($start_date));
$end_date = date('Y-m-d', strtotime($end_date));
}
break;
default:
$start_date = date('Y-m-d', strtotime('-6 days'));
$end_date = date('Y-m-d');
break;
}
// Hitung tanggal tahun lalu untuk perbandingan
$previous_start_date = date('Y-m-d', strtotime('-1 year', strtotime($start_date)));
$previous_end_date = date('Y-m-d', strtotime('-1 year', strtotime($end_date)));
// Order type filters
$order_type_filters = [
'isWebsiteOrder' => $this->input->get('isWebsiteOrder'),
'isRetailerOrder' => $this->input->get('isRetailerOrder'),
'isTokopediaOrder' => $this->input->get('isTokopediaOrder'),
'isAllOrder' => $this->input->get('isAllOrder'),
];
// Payment method filters
$payment_filters = [
'isBCATransfer' => $this->input->get('isBCATransfer'),
'isMandiriTransfer' => $this->input->get('isMandiriTransfer'),
'isPaypal' => $this->input->get('isPaypal'),
'isDoku' => $this->input->get('isDoku')
];
// Shipping method filters
$shipping_filters = [
'isRegularShipping' => $this->input->get('isRegularShipping'),
'isTwoHourShipping' => $this->input->get('isTwoHourShipping'),
'isOneDayShipping' => $this->input->get('isOneDayShipping'),
'isNextDayShipping' => $this->input->get('isNextDayShipping')
];
// Insurance filters
$insurance_filters = [
'usingInsurance' => $this->input->get('usingInsurance'),
'notUsingInsurance' => $this->input->get('notUsingInsurance')
];
// Search parameter
$search = $this->input->get('search');
// Sort parameter
$sort = $this->input->get('sort', '');
// Get current period sales data with all filters
$current_sales_data = $this->statistic_m->get_sales_data(
$start_date,
$end_date,
$payment_filters,
$shipping_filters,
$insurance_filters,
$order_type_filters,
$search,
$sort
);
// Get previous year sales data with same filters
$previous_sales_data = $this->statistic_m->get_sales_data(
$previous_start_date,
$previous_end_date,
$payment_filters,
$shipping_filters,
$insurance_filters,
$order_type_filters,
$search,
$sort
);
// Get daily data for charts
$chart_data = $this->statistic_m->get_comparison_chart_data(
$start_date,
$end_date,
$previous_start_date,
$previous_end_date,
$payment_filters,
$shipping_filters,
$insurance_filters,
$order_type_filters
);
// Calculate summary statistics
$current_total = array_sum(array_column($current_sales_data['sales'], 'grand_total_amount'));
$previous_total = array_sum(array_column($previous_sales_data['sales'], 'grand_total_amount'));
$growth_percentage = ($previous_total > 0) ? (($current_total - $previous_total) / $previous_total * 100) : 100;
$this->output
->set_content_type('application/json')
->set_output(json_encode([
'current_sales' => $current_sales_data['sales'],
'previous_sales' => $previous_sales_data['sales'],
'start_date' => $start_date,
'end_date' => $end_date,
'previous_start_date' => $previous_start_date,
'previous_end_date' => $previous_end_date,
'current_total' => $current_total,
'previous_total' => $previous_total,
'growth_percentage' => round($growth_percentage, 2),
'chart_data' => $chart_data
]));
}
// Visit Statistics
public function visit_statistics()
{
$data['userdata'] = $this->session->userdata();
$data['title'] = 'Statistik Kunjungan | Laciasmara';
$this->load->view('admin_new/layouts/header', $data);
$this->load->view('admin_new/statistics/visit_statistics');
$this->load->view('admin_new/layouts/footer');
}
public function get_visit_data()
{
// Default filter: last 7 days
$date_filter = $this->input->get('date_filter', 'last7days');
$start_date = $this->input->get('start_date');
$end_date = $this->input->get('end_date');
// Tentukan rentang tanggal berdasarkan filter
switch ($date_filter) {
case 'today':
$start_date = date('Y-m-d');
$end_date = date('Y-m-d');
break;
case 'yesterday':
$start_date = date('Y-m-d', strtotime('-1 day'));
$end_date = date('Y-m-d', strtotime('-1 day'));
break;
case 'last7days':
$start_date = date('Y-m-d', strtotime('-6 days'));
$end_date = date('Y-m-d');
break;
case 'last30days':
$start_date = date('Y-m-d', strtotime('-29 days'));
$end_date = date('Y-m-d');
break;
case 'thisMonth':
$start_date = date('Y-m-01');
$end_date = date('Y-m-t');
break;
case 'thisYear':
$start_date = date('Y-01-01');
$end_date = date('Y-12-31');
break;
case 'custom':
if ($start_date && $end_date) {
$startDate = date('Y-m-d', strtotime($start_date));
$endDate = date('Y-m-d', strtotime($end_date));
if ($startDate && $endDate) {
$this->db->where('DATE(click_date) >=', $start_date);
$this->db->where('DATE(click_date) <=', $end_date);
}
}
break;
}
// Ambil data kunjungan
$visits_data = $this->statistic_m->get_visits_by_date_range($start_date, $end_date);
$top_traffic_sources = $this->statistic_m->get_top_traffic_sources($start_date, $end_date);
$top_internal_traffic_sources = $this->statistic_m->get_internal_traffic_sources($start_date, $end_date);
$engagement_metrics = $this->statistic_m->get_engagement_metrics($start_date, $end_date);
// Response JSON
$this->output
->set_content_type('application/json')
->set_output(json_encode([
'labels' => $visits_data['labels'],
'visits' => $visits_data['visits'],
'prev_visits' => $visits_data['prev_visits'],
'total_visits' => $visits_data['total_visits'],
'prev_total_visits' => $visits_data['prev_total_visits'],
'unique_visits' => $visits_data['unique_visits'],
'prev_unique_visits' => $visits_data['prev_unique_visits'],
'start_date' => $start_date,
'end_date' => $end_date,
'top_traffic_sources' => $top_traffic_sources,
'top_internal_traffic_sources' => $top_internal_traffic_sources,
'average_time_seconds' => $engagement_metrics['average_time_seconds'],
'average_time_formatted' => $engagement_metrics['average_time_formatted'],
'bounce_rate' => $engagement_metrics['bounce_rate'],
]));
}
// Customer Statistics
public function customer_statistics()
{
$data['userdata'] = $this->session->userdata();
$data['title'] = 'Statistik Pelanggan | Laciasmara';
$this->load->view('admin_new/layouts/header', $data);
$this->load->view('admin_new/statistics/customer_statistics');
$this->load->view('admin_new/layouts/footer');
}
public function get_customer_data()
{
// Default filter: last 7 days
$date_filter = $this->input->get('date_filter', 'last7days');
$start_date = $this->input->get('start_date');
$end_date = $this->input->get('end_date');
// Tentukan rentang tanggal berdasarkan filter
switch ($date_filter) {
case 'today':
$start_date = date('Y-m-d');
$end_date = date('Y-m-d');
break;
case 'yesterday':
$start_date = date('Y-m-d', strtotime('-1 day'));
$end_date = date('Y-m-d', strtotime('-1 day'));
break;
case 'last7days':
$start_date = date('Y-m-d', strtotime('-6 days'));
$end_date = date('Y-m-d');
break;
case 'last30days':
$start_date = date('Y-m-d', strtotime('-29 days'));
$end_date = date('Y-m-d');
break;
case 'thisMonth':
$start_date = date('Y-m-01');
$end_date = date('Y-m-t');
break;
case 'thisYear':
$start_date = date('Y-01-01');
$end_date = date('Y-12-31');
break;
case 'custom':
if ($start_date && $end_date) {
$start_date = date('Y-m-d', strtotime($start_date));
$end_date = date('Y-m-d', strtotime($end_date));
}
break;
}
// Periode sebelumnya (1 tahun)
$prev_start_date = date('Y-m-d', strtotime($start_date . ' -1 year'));
$prev_end_date = date('Y-m-d', strtotime($end_date . ' -1 year'));
// Hitung pelanggan baru dalam periode
$this->db->where('type', 'regular');
$this->db->where('DATE(join_date) >=', $start_date);
$this->db->where('DATE(join_date) <=', $end_date);
$new_customers_current = $this->db->count_all_results('customers');
// Hitung pelanggan baru periode sebelumnya
$this->db->where('type', 'regular');
$this->db->where('DATE(join_date) >=', $prev_start_date);
$this->db->where('DATE(join_date) <=', $prev_end_date);
$new_customers_prev = $this->db->count_all_results('customers');
// Perhitungan gender
$gender_query = $this->db->query("
SELECT
'male' as gender,
SUM(CASE WHEN sex_type = 'male' THEN 1 ELSE 0 END) as total
FROM customers
WHERE type = 'regular' AND
DATE(join_date) >= ? AND
DATE(join_date) <= ?
UNION ALL
SELECT
'female' as gender,
SUM(CASE WHEN sex_type = 'female' THEN 1 ELSE 0 END) as total
FROM customers
WHERE type = 'regular' AND
DATE(join_date) >= ? AND
DATE(join_date) <= ?
UNION ALL
SELECT
'others' as gender,
SUM(CASE WHEN sex_type = 'others' THEN 1 ELSE 0 END) as total
FROM customers
WHERE type = 'regular' AND
DATE(join_date) >= ? AND
DATE(join_date) <= ?
UNION ALL
SELECT
'tidak disebutkan' as gender,
SUM(CASE WHEN sex_type IS NULL THEN 1 ELSE 0 END) as total
FROM customers
WHERE type = 'regular' AND
DATE(join_date) >= ? AND
DATE(join_date) <= ?
", [$start_date, $end_date, $start_date, $end_date, $start_date, $end_date, $start_date, $end_date]);
$gender_breakdown = $gender_query->result_array();
// Perhitungan umur
$current_year = date('Y');
// Perhitungan umur dengan semua kategori
$age_query = $this->db->query("
SELECT
all_ranges.age_range,
COALESCE(customer_counts.total, 0) as total
FROM (
SELECT 'tidak disebutkan' as age_range
UNION ALL
SELECT '<21'
UNION ALL
SELECT '22-27'
UNION ALL
SELECT '28-38'
UNION ALL
SELECT '39-49'
UNION ALL
SELECT '50-60'
UNION ALL
SELECT '>60'
) all_ranges
LEFT JOIN (
SELECT
CASE
WHEN birthday IS NULL OR birthday = '' THEN 'tidak disebutkan'
WHEN (? - YEAR(STR_TO_DATE(birthday, '%Y-%m-%d'))) < 21 THEN '<21'
WHEN (? - YEAR(STR_TO_DATE(birthday, '%Y-%m-%d'))) BETWEEN 22 AND 27 THEN '22-27'
WHEN (? - YEAR(STR_TO_DATE(birthday, '%Y-%m-%d'))) BETWEEN 28 AND 38 THEN '28-38'
WHEN (? - YEAR(STR_TO_DATE(birthday, '%Y-%m-%d'))) BETWEEN 39 AND 49 THEN '39-49'
WHEN (? - YEAR(STR_TO_DATE(birthday, '%Y-%m-%d'))) BETWEEN 50 AND 60 THEN '50-60'
ELSE '>60'
END as age_range,
COUNT(*) as total
FROM customers
WHERE type = 'regular' 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
CASE all_ranges.age_range
WHEN 'tidak disebutkan' THEN 1
WHEN '<21' THEN 2
WHEN '22-27' THEN 3
WHEN '28-38' THEN 4
WHEN '39-49' THEN 5
WHEN '50-60' THEN 6
WHEN '>60' THEN 7
END
", [
$current_year,
$current_year,
$current_year,
$current_year,
$current_year,
$start_date,
$end_date
]);
$age_breakdown = $age_query->result_array();
$city_query = $this->db->query("
SELECT
COALESCE(id.district, 'Belum diisi') as city,
COUNT(c.id_customers) as total
FROM customers c
LEFT JOIN indonesia_districts id ON c.shipping_id_district = id.rajaongkir_id_district
WHERE c.type = 'regular' AND
DATE(c.join_date) >= ? AND
DATE(c.join_date) <= ?
GROUP BY id.district
ORDER BY total DESC
", [$start_date, $end_date]);
$city_breakdown = $city_query->result_array();
$province_query = $this->db->query("
SELECT
COALESCE(id.province, 'Belum diisi') as province,
COUNT(c.id_customers) as total
FROM customers c
LEFT JOIN indonesia_provinces id ON c.shipping_id_province = id.rajaongkir_province_id
WHERE c.type = 'regular' AND
DATE(c.join_date) >= ? AND
DATE(c.join_date) <= ?
GROUP BY id.province
ORDER BY total DESC
", [$start_date, $end_date]);
$province_breakdown = $province_query->result_array();
// Total pelanggan
$this->db->where('type', 'regular');
$total_customers = $this->db->count_all_results('customers');
$this->output
->set_content_type('application/json')
->set_output(json_encode([
'start_date' => $start_date,
'end_date' => $end_date,
'new_customers' => [
'current_period' => $new_customers_current,
'previous_period' => $new_customers_prev
],
'total_customers' => $total_customers,
'gender_breakdown' => $gender_breakdown,
'age_breakdown' => $age_breakdown,
'city_breakdown' => $city_breakdown,
'province_breakdown' => $province_breakdown
]));
}
// Product Statistics
public function product_statistics()
{
$data['userdata'] = $this->session->userdata();
$data['title'] = 'Statistik Produk | Laciasmara';
$this->load->view('admin_new/layouts/header', $data);
$this->load->view('admin_new/statistics/product_statistics');
$this->load->view('admin_new/layouts/footer', $data);
}
public function get_product_data()
{
// Default filter: last 7 days
$date_filter = $this->input->get('date_filter', 'last7days');
$start_date = $this->input->get('start_date');
$end_date = $this->input->get('end_date');
$searchTerm = $this->input->get('search', true);
// Tentukan rentang tanggal berdasarkan filter
switch ($date_filter) {
case 'today':
$start_date = date('Y-m-d');
$end_date = date('Y-m-d');
break;
case 'yesterday':
$start_date = date('Y-m-d', strtotime('-1 day'));
$end_date = date('Y-m-d', strtotime('-1 day'));
break;
case 'last7days':
$start_date = date('Y-m-d', strtotime('-6 days'));
$end_date = date('Y-m-d');
break;
case 'last30days':
$start_date = date('Y-m-d', strtotime('-29 days'));
$end_date = date('Y-m-d');
break;
case 'thisMonth':
$start_date = date('Y-m-01');
$end_date = date('Y-m-t');
break;
case 'thisYear':
$start_date = date('Y-01-01');
$end_date = date('Y-12-31');
break;
case 'custom':
if ($start_date && $end_date) {
$start_date = date('Y-m-d', strtotime($start_date));
$end_date = date('Y-m-d', strtotime($end_date));
}
break;
}
// Query untuk mendapatkan data produk dengan metrik
$this->db->select('
p.id_products,
p.title,
p.alias,
pi.image,
b.brand as brand_title,
COALESCE(view_counts.view_count, 0) as view_count,
COALESCE(sold_counts.total_sold, 0) as sold_count,
COALESCE(wishlist_counts.total_wishlist, 0) as wishlist_count,
COALESCE(cart_counts.total_cart, 0) as cart_count
');
$this->db->from('products p');
$this->db->join('product_details pd', 'p.id_products = pd.product_id', 'left');
$this->db->join('brands b', 'p.brand_id = b.id_brands', 'left');
$this->db->join('product_images pi', 'pi.product_details_id = pd.id AND pi.priority = 1 AND pi.status = 1', 'left');
// Subquery untuk jumlah dilihat (dari link_tracks)
$this->db->join('(
SELECT
SUBSTRING_INDEX(lt.link_url, "/product/", -1) as product_alias,
COUNT(*) as view_count
FROM
link_tracks lt
WHERE
lt.link_url LIKE "%/product/%" AND
DATE(lt.click_date) >= "' . $start_date . '" AND
DATE(lt.click_date) <= "' . $end_date . '"
GROUP BY
product_alias
) view_counts', 'p.alias = view_counts.product_alias', 'left');
// Subquery untuk jumlah terjual (dari orders_detail)
$this->db->join('(
SELECT
od.product_id,
COUNT(od.product_id) as total_sold
FROM
orders_detail od
JOIN
orders o ON od.orders_id = o.id_orders
WHERE
o.payment_status = 5 AND
DATE(o.order_date) >= "' . $start_date . '" AND
DATE(o.order_date) <= "' . $end_date . '"
GROUP BY
od.product_id
) sold_counts', 'p.id_products = sold_counts.product_id', 'left');
// Subquery untuk jumlah wishlist
$this->db->join('(
SELECT
product_id,
COUNT(*) as total_wishlist
FROM
wishlists
WHERE
DATE(created_at) >= "' . $start_date . '" AND
DATE(created_at) <= "' . $end_date . '"
GROUP BY
product_id
) wishlist_counts', 'p.id_products = wishlist_counts.product_id', 'left');
// Subquery untuk jumlah cart
$this->db->join('(
SELECT
product_id,
COUNT(*) as total_cart
FROM
cart_history
WHERE
DATE(created_at) >= "' . $start_date . '" AND
DATE(created_at) <= "' . $end_date . '"
GROUP BY
product_id
) cart_counts', 'p.id_products = cart_counts.product_id', 'left');
if (!empty($searchTerm)) {
$this->db->group_start();
$this->db->like('p.title', $searchTerm);
$this->db->or_like('b.brand', $searchTerm);
$this->db->group_end();
}
$this->db->where('p.product_status', '1'); // Only active products
$this->db->where('p.deleted_at', null);
$this->db->group_by('p.id_products');
$this->db->order_by('view_count', 'DESC');
$query = $this->db->get();
$product_breakdown = $query->result_array();
// Ambil total produk
$this->db->where('product_status', '1');
$total_products = $this->db->count_all_results('products');
// Ambil top 5 kategori berdasarkan penjualan
$this->db->select('
c.category as category_name,
COUNT(od.product_id) as total_sold
');
$this->db->from('orders_detail od');
$this->db->join('orders o', 'od.orders_id = o.id_orders', 'left');
$this->db->join('category_product cp', 'od.product_id = cp.id_product', 'left');
$this->db->join('categories c', 'cp.id_category = c.id_categories', 'left');
$this->db->where('o.payment_status', 5);
$this->db->where('DATE(o.order_date) >=', $start_date);
$this->db->where('DATE(o.order_date) <=', $end_date);
$this->db->group_by('c.id_categories');
$this->db->order_by('total_sold', 'DESC');
$this->db->limit(5);
$top_categories_query = $this->db->get();
$top_categories = $top_categories_query->result_array();
// Ambil produk dengan sold terbanyak (paling laris)
$this->db->select('p.title as product_name');
$this->db->from('products p');
$this->db->join('(
SELECT
od.product_id,
COUNT(od.product_id) as total_sold
FROM
orders_detail od
JOIN
orders o ON od.orders_id = o.id_orders
WHERE
o.payment_status = 5 AND
DATE(o.order_date) >= "' . $start_date . '" AND
DATE(o.order_date) <= "' . $end_date . '"
GROUP BY
od.product_id
) sold_counts', 'p.id_products = sold_counts.product_id', 'inner');
$this->db->where('p.product_status', '1');
$this->db->where('p.deleted_at', null);
$this->db->order_by('sold_counts.total_sold', 'DESC');
$this->db->limit(1);
$best_selling_query = $this->db->get();
$best_selling_product = $best_selling_query->row_array();
// Ambil produk dengan view terbanyak
$this->db->select('p.title as product_name');
$this->db->from('products p');
$this->db->join('(
SELECT
SUBSTRING_INDEX(lt.link_url, "/product/", -1) as product_alias,
COUNT(*) as view_count
FROM
link_tracks lt
WHERE
lt.link_url LIKE "%/product/%" AND
DATE(lt.click_date) >= "' . $start_date . '" AND
DATE(lt.click_date) <= "' . $end_date . '"
GROUP BY
product_alias
) view_counts', 'p.alias = view_counts.product_alias', 'inner');
$this->db->where('p.product_status', '1');
$this->db->where('p.deleted_at', null);
$this->db->order_by('view_counts.view_count', 'DESC');
$this->db->limit(1);
$most_viewed_query = $this->db->get();
$most_viewed_product = $most_viewed_query->row_array();
// Ambil produk dengan sold paling sedikit (kurang diminati)
$this->db->select('p.title as product_name');
$this->db->from('products p');
$this->db->join('(
SELECT
od.product_id,
COUNT(od.product_id) as total_sold
FROM
orders_detail od
JOIN
orders o ON od.orders_id = o.id_orders
WHERE
o.payment_status = 5 AND
DATE(o.order_date) >= "' . $start_date . '" AND
DATE(o.order_date) <= "' . $end_date . '"
GROUP BY
od.product_id
) sold_counts', 'p.id_products = sold_counts.product_id', 'inner');
$this->db->where('p.product_status', '1');
$this->db->where('p.deleted_at', null);
$this->db->order_by('sold_counts.total_sold', 'ASC');
$this->db->limit(1);
$least_selling_query = $this->db->get();
$least_selling_product = $least_selling_query->row_array();
// Ambil produk yang rusak
$this->db->select('COUNT(*) as total_defected, p.title as product_name');
$this->db->from('claim_garansi cg');
$this->db->join('orders_detail od', 'cg.no_order_detail_id = od.id_orders_detail');
$this->db->join('products p', 'od.product_id = p.id_products');
$this->db->group_by('p.title');
$defected_product_query = $this->db->get();
$defected_product = $defected_product_query->result_array();
// Ambil semua ID produk yang sudah pernah dipesan (sepanjang waktu, tidak terbatas rentang tanggal)
$this->db->select('DISTINCT(od.product_id)');
$this->db->from('orders_detail od');
$this->db->join('orders o', 'od.orders_id = o.id_orders');
$this->db->where_in('o.payment_status', [5, 8]); // Hanya transaksi yang sudah dibayar
// Hilangkan filter tanggal di sini
$ordered_query = $this->db->get();
$ordered_products = [];
// Kumpulkan ID ke array
if ($ordered_query->num_rows() > 0) {
foreach ($ordered_query->result_array() as $row) {
$ordered_products[] = $row['product_id'];
}
}
// Log untuk debugging
log_message('debug', 'All time ordered products count: ' . count($ordered_products));
// Query untuk produk yang tidak pernah dipesan sama sekali
$this->db->select('p.id_products, p.title as product_name, b.brand as brand_title, p.alias, pi.image, pd.sku');
$this->db->from('products p');
$this->db->join('brands b', 'p.brand_id = b.id_brands', 'left');
$this->db->join('product_details pd', 'p.id_products = pd.product_id', 'left');
$this->db->join('product_images pi', 'pi.product_details_id = pd.id AND pi.priority = 1 AND pi.status = 1', 'left');
$this->db->where('p.product_status', '1'); // Hanya produk aktif
$this->db->where('p.deleted_at', null);
// Filter produk yang NULL sebelum digunakan dalam where_not_in
$ordered_products = array_filter($ordered_products, function ($value) {
return $value !== NULL;
});
if (!empty($ordered_products)) {
$this->db->where_not_in('p.id_products', $ordered_products);
}
$this->db->order_by('p.title', 'ASC');
$never_ordered_query = $this->db->get();
log_message('debug', 'Never ordered all time SQL: ' . $this->db->last_query());
$never_ordered_products = $never_ordered_query->result_array();
$never_ordered_count = count($never_ordered_products);
$this->output
->set_content_type('application/json')
->set_output(json_encode([
'start_date' => $start_date,
'end_date' => $end_date,
'totals' => [
'products' => $total_products,
'never_ordered' => $never_ordered_count
],
'top_categories' => $top_categories,
'best_selling_product' => $best_selling_product ? $best_selling_product['product_name'] : null,
'most_viewed_product' => $most_viewed_product ? $most_viewed_product['product_name'] : null,
'least_selling_product' => $least_selling_product ? $least_selling_product['product_name'] : null,
'never_ordered_products' => $never_ordered_products,
'product_breakdown' => $product_breakdown,
'defected_product' => $defected_product
]));
}
public function stock_statistics()
{
$data['userdata'] = $this->session->userdata();
$data['title'] = 'Statistik Stok | Laciasmara';
$this->load->view('admin_new/layouts/header', $data);
$this->load->view('admin_new/statistics/stock_statistics');
$this->load->view('admin_new/layouts/footer', $data);
}
public function get_stock_data()
{
// Default filter: last 7 days
$date_filter = $this->input->get('date_filter', true) ?: 'last7days';
$start_date = $this->input->get('start_date', true);
$end_date = $this->input->get('end_date', true);
$searchTerm = $this->input->get('search', true);
$page = max(1, (int)$this->input->get('page', true) ?: 1);
$limit = max(1, (int)$this->input->get('limit', true) ?: 250);
$offset = ($page - 1) * $limit;
// Set date range based on filter
switch ($date_filter) {
case 'today':
$start_date = date('Y-m-d');
$end_date = date('Y-m-d');
break;
case 'yesterday':
$start_date = date('Y-m-d', strtotime('-1 day'));
$end_date = date('Y-m-d', strtotime('-1 day'));
break;
case 'last7days':
$start_date = date('Y-m-d', strtotime('-6 days'));
$end_date = date('Y-m-d');
break;
case 'last30days':
$start_date = date('Y-m-d', strtotime('-29 days'));
$end_date = date('Y-m-d');
break;
case 'thisMonth':
$start_date = date('Y-m-01');
$end_date = date('Y-m-t');
break;
case 'thisYear':
$start_date = date('Y-01-01');
$end_date = date('Y-12-31');
break;
case 'custom':
if ($start_date && $end_date) {
$start_date = date('Y-m-d', strtotime($start_date));
$end_date = date('Y-m-d', strtotime($end_date));
} else {
// Default to last 7 days if custom dates are invalid
$start_date = date('Y-m-d', strtotime('-6 days'));
$end_date = date('Y-m-d');
}
break;
default:
$start_date = date('Y-m-d', strtotime('-6 days'));
$end_date = date('Y-m-d');
break;
}
// Validasi tanggal
if (strtotime($start_date) > strtotime($end_date)) {
$this->output
->set_status_header(400)
->set_content_type('application/json')
->set_output(json_encode(['error' => 'Tanggal mulai tidak boleh melebihi tanggal akhir']));
return;
}
try {
// Get stock statistics for all products
$result = $this->get_stock_statistics($start_date, $end_date, $searchTerm);
// Calculate totals
$total_products = count($result);
$total_opening_stock = 0;
$total_incoming_stock = 0;
$total_outgoing_stock = 0;
$total_closing_stock = 0;
$top_moving_products = [];
$most_stocked_products = [];
if (!empty($result)) {
foreach ($result as $item) {
$total_opening_stock += $item['opening_stock'];
$total_incoming_stock += $item['incoming_stock'];
$total_outgoing_stock += $item['outgoing_stock'];
$total_closing_stock += $item['closing_stock'];
}
// Get top movers (products with highest outgoing stock)
$top_moving_products = $result;
usort($top_moving_products, function ($a, $b) {
return $b['outgoing_stock'] <=> $a['outgoing_stock'];
});
$top_moving_products = array_slice($top_moving_products, 0, 5);
// Get most stocked products (products with highest closing stock)
$most_stocked_products = $result;
usort($most_stocked_products, function ($a, $b) {
return $b['closing_stock'] <=> $a['closing_stock'];
});
$most_stocked_products = array_slice($most_stocked_products, 0, 5);
}
// Apply pagination to main result
$paginated_result = array_slice($result, $offset, $limit);
$this->output
->set_content_type('application/json')
->set_output(json_encode([
'start_date' => $start_date,
'end_date' => $end_date,
'summary' => [
'total_products' => $total_products,
'total_opening_stock' => $total_opening_stock,
'total_incoming_stock' => $total_incoming_stock,
'total_outgoing_stock' => $total_outgoing_stock,
'total_closing_stock' => $total_closing_stock
],
'top_moving_products' => $top_moving_products,
'most_stocked_products' => $most_stocked_products,
'stock_data' => $paginated_result,
'pagination' => [
'page' => (int)$page,
'limit' => (int)$limit,
'total_records' => $total_products,
]
]));
} catch (Exception $e) {
$this->output
->set_status_header(500)
->set_content_type('application/json')
->set_output(json_encode(['error' => 'Terjadi kesalahan dalam memproses data: ' . $e->getMessage()]));
}
}
private function get_stock_statistics($start_date, $end_date, $searchTerm = null)
{
// Konversi format tanggal untuk query
$start_datetime = date('Y-m-d 00:00:00', strtotime($start_date));
$end_datetime = date('Y-m-d 23:59:59', strtotime($end_date));
// Query untuk mendapatkan semua produk yang relevan
$this->db->select('s.id as stock_id, s.stock as current_stock, p.id_products as product_id, pd.id as product_detail_id, p.title as product_name, pd.sku, b.brand');
$this->db->from('stock s');
$this->db->join('products p', 'p.id_products = s.id_product', 'left');
$this->db->join('brands b', 'b.id_brands = p.brand_id', 'left');
$this->db->join('product_details pd', 'pd.id = s.id_product_detail', 'left');
// Tambahkan filter pencarian jika ada
if (!empty($searchTerm)) {
$this->db->group_start();
$this->db->like('p.title', $searchTerm);
$this->db->or_like('pd.sku', $searchTerm);
$this->db->group_end();
}
$products = $this->db->get()->result_array();
$result = [];
// Daftar tabel pergerakan stok yang akan diakumulasikan
$stock_tables = [
'stock_movement',
'stock_movement_keep',
'stock_movement_reject',
'stock_movement_sample'
];
foreach ($products as $product) {
$opening_stock = 0;
$incoming_stock = 0;
$outgoing_stock = 0;
foreach ($stock_tables as $table) {
// Cari stok awal dari setiap tabel (pergerakan terakhir sebelum start_date)
$this->db->select('total');
$this->db->from($table);
$this->db->where('stock_id', $product['stock_id']);
$this->db->where('datetime <', $start_datetime);
$this->db->order_by('datetime', 'DESC');
$this->db->limit(1);
$query = $this->db->get();
$table_opening_stock = 0;
if ($query->num_rows() > 0) {
$table_opening_stock = $query->row()->total;
} else {
// Jika tidak ada riwayat, untuk tabel pertama (stock_movement) kita gunakan stok saat ini dan hitung mundur
// Untuk tabel lainnya, kita asumsikan 0 karena tidak ada data historis
if ($table === 'stock_movement') {
$table_opening_stock = $product['current_stock'];
// Hitung total pergerakan stok dalam periode dari semua tabel
$total_in_all_tables = 0;
$total_out_all_tables = 0;
foreach ($stock_tables as $inner_table) {
$this->db->select('SUM(CASE WHEN type = "+" THEN stock_change ELSE 0 END) as total_in');
$this->db->select('SUM(CASE WHEN type = "-" THEN stock_change ELSE 0 END) as total_out');
$this->db->from($inner_table);
$this->db->where('stock_id', $product['stock_id']);
$this->db->where('datetime >=', $start_datetime);
$this->db->where('datetime <=', $end_datetime);
$total_movement = $this->db->get()->row();
$total_in_all_tables += ($total_movement->total_in ?? 0);
$total_out_all_tables += ($total_movement->total_out ?? 0);
}
// Hitung mundur untuk mendapatkan stok awal
$table_opening_stock = $table_opening_stock - $total_in_all_tables + $total_out_all_tables;
}
}
$opening_stock += $table_opening_stock;
// Hitung stok masuk dan keluar dalam periode untuk tabel ini
$this->db->select('SUM(CASE WHEN type = "+" THEN stock_change ELSE 0 END) as incoming_stock');
$this->db->select('SUM(CASE WHEN type = "-" THEN stock_change ELSE 0 END) as outgoing_stock');
$this->db->from($table);
$this->db->where('stock_id', $product['stock_id']);
$this->db->where('datetime >=', $start_datetime);
$this->db->where('datetime <=', $end_datetime);
$stock_movement = $this->db->get()->row();
$incoming_stock += ($stock_movement->incoming_stock ?? 0);
$outgoing_stock += ($stock_movement->outgoing_stock ?? 0);
}
// Hitung stok akhir
$closing_stock = $opening_stock + $incoming_stock - $outgoing_stock;
// Tambahkan ke hasil jika ada pergerakan atau stok tidak nol
if ($opening_stock > 0 || $incoming_stock > 0 || $outgoing_stock > 0 || $closing_stock > 0) {
$result[] = [
'product_id' => $product['product_id'],
'product_detail_id' => $product['product_detail_id'],
'product_name' => $product['product_name'],
'sku' => $product['sku'] ?? '-',
'brand' => $product['brand'] ?? '-',
'opening_stock' => (int)$opening_stock,
'incoming_stock' => (int)$incoming_stock,
'outgoing_stock' => (int)$outgoing_stock,
'closing_stock' => (int)$closing_stock
];
}
}
return $result;
}
public function get_product_stock_movement()
{
$product_detail_id = $this->input->get('product_detail_id', true);
$date_filter = $this->input->get('date_filter', true) ?: 'last7days';
$start_date = $this->input->get('start_date', true);
$end_date = $this->input->get('end_date', true);
if (!$product_detail_id) {
$this->output
->set_status_header(400)
->set_content_type('application/json')
->set_output(json_encode(['error' => 'Product detail ID diperlukan']));
return;
}
// Set date range based on filter (exactly like in get_stock_data method)
switch ($date_filter) {
case 'today':
$start_date = date('Y-m-d');
$end_date = date('Y-m-d');
break;
case 'yesterday':
$start_date = date('Y-m-d', strtotime('-1 day'));
$end_date = date('Y-m-d', strtotime('-1 day'));
break;
case 'last7days':
$start_date = date('Y-m-d', strtotime('-6 days'));
$end_date = date('Y-m-d');
break;
case 'last30days':
$start_date = date('Y-m-d', strtotime('-29 days'));
$end_date = date('Y-m-d');
break;
case 'thisMonth':
$start_date = date('Y-m-01');
$end_date = date('Y-m-t');
break;
case 'thisYear':
$start_date = date('Y-01-01');
$end_date = date('Y-12-31');
break;
case 'custom':
if ($start_date && $end_date) {
$start_date = date('Y-m-d', strtotime($start_date));
$end_date = date('Y-m-d', strtotime($end_date));
} else {
// Default to last 7 days if custom dates are invalid
$start_date = date('Y-m-d', strtotime('-6 days'));
$end_date = date('Y-m-d');
}
break;
default:
$start_date = date('Y-m-d', strtotime('-6 days'));
$end_date = date('Y-m-d');
break;
}
// Validasi tanggal
if (strtotime($start_date) > strtotime($end_date)) {
$this->output
->set_status_header(400)
->set_content_type('application/json')
->set_output(json_encode(['error' => 'Tanggal mulai tidak boleh melebihi tanggal akhir']));
return;
}
try {
$movement_detail = $this->get_stock_movement_detail($product_detail_id, $start_date, $end_date);
$this->output
->set_content_type('application/json')
->set_output(json_encode([
'start_date' => $start_date,
'end_date' => $end_date,
'product_info' => $movement_detail['product_info'],
'movements' => $movement_detail['movements']
]));
} catch (Exception $e) {
$this->output
->set_status_header(500)
->set_content_type('application/json')
->set_output(json_encode(['error' => 'Terjadi kesalahan dalam memproses data: ' . $e->getMessage()]));
}
}
public function get_stock_movement_detail($product_detail_id, $start_date, $end_date)
{
// Konversi format tanggal untuk query
$start_datetime = date('Y-m-d 00:00:00', strtotime($start_date));
$end_datetime = date('Y-m-d 23:59:59', strtotime($end_date));
// Dapatkan stock_id berdasarkan product_detail_id
$this->db->select('id as stock_id');
$this->db->from('stock');
$this->db->where('id_product_detail', $product_detail_id);
$stock_query = $this->db->get();
if ($stock_query->num_rows() == 0) {
return [
'product_info' => null,
'movements' => [],
'summary' => [
'opening_stock' => 0,
'incoming_stock' => 0,
'outgoing_stock' => 0,
'closing_stock' => 0
]
];
}
$stock_id = $stock_query->row()->stock_id;
// Dapatkan info produk
$this->db->select('p.id_products as product_id, p.title as product_name, pd.sku, b.brand');
$this->db->from('product_details pd');
$this->db->join('products p', 'p.id_products = pd.product_id', 'left');
$this->db->join('brands b', 'b.id_brands = p.brand_id', 'left');
$this->db->where('pd.id', $product_detail_id);
$product_info = $this->db->get()->row_array();
// Daftar tabel pergerakan stok
$stock_tables = [
'stock_movement' => 'Total',
'stock_movement_keep' => 'Keep',
'stock_movement_reject' => 'Reject',
'stock_movement_sample' => 'Sample'
];
$movements = [];
$total_opening_stock = 0;
$total_incoming_stock = 0;
$total_outgoing_stock = 0;
// Untuk setiap jenis stok, hitung stok awal
foreach ($stock_tables as $table => $stock_type) {
// Cari stok awal (pergerakan terakhir sebelum start_date)
$this->db->select('total');
$this->db->from($table);
$this->db->where('stock_id', $stock_id);
$this->db->where('datetime <', $start_datetime);
$this->db->order_by('datetime', 'asc');
$this->db->limit(1);
$query = $this->db->get();
$opening_stock = 0;
if ($query->num_rows() > 0) {
$opening_stock = $query->row()->total;
} else {
// Jika tidak ada riwayat, kita tidak bisa menghitung stok awal dengan pasti
if ($table === 'stock_movement') {
// Ambil stok saat ini
$this->db->select('stock');
$this->db->from('stock');
$this->db->where('id', $stock_id);
$current_stock = $this->db->get()->row()->stock ?? 0;
// Hitung total pergerakan stok dalam periode dari semua tabel
$total_in_all_tables = 0;
$total_out_all_tables = 0;
foreach ($stock_tables as $inner_table => $inner_type) {
$this->db->select('SUM(CASE WHEN type = "+" THEN stock_change ELSE 0 END) as total_in');
$this->db->select('SUM(CASE WHEN type = "-" THEN stock_change ELSE 0 END) as total_out');
$this->db->from($inner_table);
$this->db->where('stock_id', $stock_id);
$this->db->where('datetime >=', $start_datetime);
$this->db->where('datetime <=', $end_datetime);
$total_movement = $this->db->get()->row();
$total_in_all_tables += ($total_movement->total_in ?? 0);
$total_out_all_tables += ($total_movement->total_out ?? 0);
}
// Hitung mundur untuk mendapatkan stok awal
$opening_stock = $current_stock - $total_in_all_tables + $total_out_all_tables;
}
}
$total_opening_stock += $opening_stock;
// Hitung stok masuk dan keluar dalam periode
$this->db->select('SUM(CASE WHEN type = "+" THEN stock_change ELSE 0 END) as incoming');
$this->db->select('SUM(CASE WHEN type = "-" THEN stock_change ELSE 0 END) as outgoing');
$this->db->from($table);
$this->db->where('stock_id', $stock_id);
$this->db->where('datetime >=', $start_datetime);
$this->db->where('datetime <=', $end_datetime);
$stock_movement = $this->db->get()->row();
$incoming = $stock_movement->incoming ?? 0;
$outgoing = $stock_movement->outgoing ?? 0;
$total_incoming_stock += $incoming;
$total_outgoing_stock += $outgoing;
// Tambahkan summary per jenis stok ke informasi produk
$product_info[$stock_type . '_summary'] = [
'opening_stock' => (int)$opening_stock,
'incoming_stock' => (int)$incoming,
'outgoing_stock' => (int)$outgoing,
'closing_stock' => (int)($opening_stock + $incoming - $outgoing)
];
$running_balance = $opening_stock;
// Dapatkan semua pergerakan stok dari tabel ini
$this->db->select("'{$stock_type}' as stock_type, datetime, type, stock_change, total, remark");
$this->db->from($table);
$this->db->where('stock_id', $stock_id);
$this->db->where('datetime >=', $start_datetime);
$this->db->where('datetime <=', $end_datetime);
$this->db->order_by('datetime', 'ASC');
$query = $this->db->get();
$table_movements = $query->result_array();
foreach ($table_movements as &$movement) {
// Add beginning balance before this movement
$movement['beginning_balance'] = $running_balance;
// Update running balance
if ($movement['type'] == '+') {
$running_balance += $movement['stock_change'];
} else {
$running_balance -= $movement['stock_change'];
}
// Also store ending balance after this movement
$movement['ending_balance'] = $running_balance;
}
// harus nambahin reference_id, dan reference type
$movements = array_merge($movements, $table_movements);
}
// Hitung stok akhir total
$total_closing_stock = $total_opening_stock + $total_incoming_stock - $total_outgoing_stock;
// Urutkan semua pergerakan berdasarkan tanggal
usort($movements, function ($a, $b) {
return strtotime($b['datetime']) - strtotime($a['datetime']);
});
return [
'product_info' => $product_info,
'movements' => $movements,
'summary' => [
'opening_stock' => (int)$total_opening_stock,
'incoming_stock' => (int)$total_incoming_stock,
'outgoing_stock' => (int)$total_outgoing_stock,
'closing_stock' => (int)$total_closing_stock
]
];
}
public function banner_statistics()
{
$data['userdata'] = $this->session->userdata();
$data['title'] = 'Statistik Banner | Laciasmara';
$this->load->view('admin_new/layouts/header', $data);
$this->load->view('admin_new/statistics/banner_statistics');
$this->load->view('admin_new/layouts/footer', $data);
}
public function get_banners()
{
$dateFilter = $this->input->get('date_filter', true);
$startDate = $this->input->get('start_date', true);
$endDate = $this->input->get('end_date', true);
// Build main query
$this->db->select('
cb.carousel_banner_id,
cb.cta_text,
cb.cta_button_link,
cb.is_active,
cb.created_at,
COUNT(DISTINCT cbi.id) AS jumlah_tayang
');
$this->db->from('carousel_banners cb');
$this->db->join('carousel_banner_impressions cbi', 'cb.carousel_banner_id = cbi.banner_id', 'left');
// Filter berdasarkan tanggal
if ($dateFilter) {
switch ($dateFilter) {
case 'today':
$this->db->where('DATE(cbi.timestamp) = CURDATE()');
break;
case 'yesterday':
$this->db->where('DATE(cbi.timestamp) = DATE_SUB(CURDATE(), INTERVAL 1 DAY)');
break;
case 'last7days':
$this->db->where('cbi.timestamp >= DATE_SUB(CURDATE(), INTERVAL 7 DAY)');
break;
case 'last30days':
$this->db->where('cbi.timestamp >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)');
break;
case 'thisMonth':
$this->db->where('MONTH(cbi.timestamp) = MONTH(CURDATE()) AND YEAR(cbi.timestamp) = YEAR(CURDATE())');
break;
case 'thisYear':
$this->db->where('YEAR(cbi.timestamp) = YEAR(CURDATE())');
break;
case 'custom':
if ($startDate && $endDate) {
$startDate = date('Y-m-d', strtotime($startDate));
$endDate = date('Y-m-d', strtotime($endDate));
if ($startDate && $endDate) {
$this->db->where('DATE(cbi.timestamp) >=', $startDate);
$this->db->where('DATE(cbi.timestamp) <=', $endDate);
}
}
break;
}
} else {
// Default: data 7 hari terakhir jika tidak ada filter
$this->db->where('cbi.timestamp >= DATE_SUB(CURDATE(), INTERVAL 7 DAY)');
}
// Group by banner
$this->db->group_by('cb.carousel_banner_id');
// Execute query
$query = $this->db->get();
$all_banners = $query->result();
// Post-processing data
foreach ($all_banners as $banner) {
// Format status
if ($banner->is_active == 1) {
$banner->status_text = 'Active';
$banner->status_class = 'bg-green-100 text-green-700 px-2 py-1 rounded';
} else {
$banner->status_text = 'Inactive';
$banner->status_class = 'bg-red-100 text-red-700 px-2 py-1 rounded';
}
// Format created_at
$banner->created_at_formatted = date('d M Y H:i', strtotime($banner->created_at));
$banner->jumlah_klik = $this->count_banner_clicks($banner->cta_button_link, $dateFilter, $startDate, $endDate);
$banner->ctr = ($banner->jumlah_tayang > 0) ?
round(($banner->jumlah_klik / $banner->jumlah_tayang) * 100, 2) : 0;
$banner->ctr_formatted = $banner->ctr . '%';
}
// Return JSON response
echo json_encode($all_banners);
}
/**
* Hitung jumlah klik untuk banner berdasarkan parameter UTM dalam link
*
* @param string $cta_button_link Link dari banner dengan parameter UTM
* @param string $dateFilter Filter tanggal
* @param string $startDate Tanggal mulai untuk filter custom
* @param string $endDate Tanggal akhir untuk filter custom
* @return int Jumlah klik
*/
private function count_banner_clicks($cta_button_link, $dateFilter, $startDate = null, $endDate = null)
{
if (empty($cta_button_link)) {
return 0;
}
// Parse URL untuk mendapatkan base URL dan parameter UTM
$parsed_url = parse_url($cta_button_link);
$base_url = isset($parsed_url['scheme']) ? $parsed_url['scheme'] . '://' . $parsed_url['host'] . $parsed_url['path'] : '';
// Parse query parameters untuk mendapatkan UTM values
$utm_source = null;
$utm_medium = null;
$utm_campaign = null;
if (isset($parsed_url['query'])) {
parse_str($parsed_url['query'], $query_params);
// Ambil parameter UTM yang diperlukan
if (isset($query_params['utm_source'])) {
$utm_source = $query_params['utm_source'];
}
if (isset($query_params['utm_medium'])) {
$utm_medium = $query_params['utm_medium'];
}
if (isset($query_params['utm_campaign'])) {
$utm_campaign = $query_params['utm_campaign'];
}
}
// Buat query untuk menghitung klik
$this->db->select('COUNT(id) as total_clicks');
$this->db->from('link_tracks');
$this->db->where('link_url', $base_url);
// Tambahkan kondisi untuk parameter UTM jika ada
if ($utm_source) {
$this->db->where('utm_source', $utm_source);
}
if ($utm_medium) {
$this->db->where('utm_medium', $utm_medium);
}
if ($utm_campaign) {
$this->db->where('utm_campaign', $utm_campaign);
}
// Tambahkan filter tanggal
if ($dateFilter) {
switch ($dateFilter) {
case 'today':
$this->db->where('DATE(click_date) = CURDATE()');
break;
case 'yesterday':
$this->db->where('DATE(click_date) = DATE_SUB(CURDATE(), INTERVAL 1 DAY)');
break;
case 'last7days':
$this->db->where('click_date >= DATE_SUB(CURDATE(), INTERVAL 7 DAY)');
break;
case 'last30days':
$this->db->where('click_date >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)');
break;
case 'thisMonth':
$this->db->where('MONTH(click_date) = MONTH(CURDATE()) AND YEAR(click_date) = YEAR(CURDATE())');
break;
case 'thisYear':
$this->db->where('YEAR(click_date) = YEAR(CURDATE())');
break;
case 'custom':
if ($startDate && $endDate) {
$startDate = date('Y-m-d', strtotime($startDate));
$endDate = date('Y-m-d', strtotime($endDate));
if ($startDate && $endDate) {
$this->db->where('DATE(click_date) >=', $startDate);
$this->db->where('DATE(click_date) <=', $endDate);
}
}
break;
}
} else {
// Default: data 7 hari terakhir jika tidak ada filter
$this->db->where('click_date >= DATE_SUB(CURDATE(), INTERVAL 7 DAY)');
}
$result = $this->db->get()->row();
return $result ? (int)$result->total_clicks : 0;
}
}