Wian Drs
352 lines
14 KiB
PHP
352 lines
14 KiB
PHP
<?php
|
|
defined('BASEPATH') OR exit('No direct script access allowed');
|
|
|
|
class Dashboard extends MY_Controller
|
|
{
|
|
public function __construct()
|
|
{
|
|
parent::__construct();
|
|
$this->load->library('PerformanceCacheService');
|
|
}
|
|
|
|
public function index()
|
|
{
|
|
$companyId = (int) $this->companycontext->id();
|
|
$year = (int) date('Y');
|
|
$summary = $this->performancecacheservice->remember(
|
|
'dashboard:summary:' . $companyId . ':' . $year,
|
|
300,
|
|
function () use ($companyId, $year) {
|
|
return $this->financialSummary($companyId, $year);
|
|
}
|
|
);
|
|
$previous = $this->financialSummary($companyId, $year - 1);
|
|
$overview = $this->operationalOverview($companyId);
|
|
|
|
$data = array(
|
|
'active_menu' => 'dashboard',
|
|
'summary' => $summary,
|
|
'comparison' => array(
|
|
'revenue' => $this->percentageChange($summary['total_revenue'], $previous['total_revenue']),
|
|
'expense' => $this->percentageChange($summary['total_expense'], $previous['total_expense']),
|
|
'profit' => $this->percentageChange($summary['net_profit'], $previous['net_profit']),
|
|
),
|
|
'overview' => $overview,
|
|
'ai_insight' => $this->insights($companyId),
|
|
'dashboard_year' => $year,
|
|
'quick_links' => $this->quickLinks(),
|
|
'show_financial' => is_master_admin_user()
|
|
|| check_permission('reports', 'can_view')
|
|
|| check_permission('jurnal', 'can_view'),
|
|
);
|
|
|
|
$this->load->view('partials/header', $data);
|
|
$this->load->view('dashboard/layout', $data);
|
|
$this->load->view('partials/footer');
|
|
}
|
|
|
|
public function get_activity()
|
|
{
|
|
$this->activityQuery();
|
|
$rows = $this->db->order_by('activity_logs.created_at', 'DESC')->limit(10)->get()->result();
|
|
return $this->json($rows);
|
|
}
|
|
|
|
public function log_activity()
|
|
{
|
|
$data = array(
|
|
'active_menu' => 'dashboard',
|
|
'use_datatables' => true,
|
|
);
|
|
$this->load->view('partials/header', $data);
|
|
$this->load->view('dashboard/activity_logs', $data);
|
|
$this->load->view('partials/footer');
|
|
}
|
|
|
|
public function get_activity_datatable()
|
|
{
|
|
$draw = (int) $this->input->post('draw');
|
|
$start = max(0, (int) $this->input->post('start'));
|
|
$length = (int) $this->input->post('length');
|
|
$searchInput = $this->input->post('search');
|
|
$search = is_array($searchInput) ? trim((string) ($searchInput['value'] ?? '')) : '';
|
|
|
|
$this->activityQuery($search);
|
|
$recordsFiltered = (int) $this->db->count_all_results();
|
|
|
|
$this->activityQuery($search);
|
|
$this->db->order_by('activity_logs.id', 'DESC');
|
|
if ($length !== -1) {
|
|
$this->db->limit(max(1, $length), $start);
|
|
}
|
|
$rows = $this->db->get()->result();
|
|
|
|
$this->activityQuery();
|
|
$recordsTotal = (int) $this->db->count_all_results();
|
|
$result = array();
|
|
foreach ($rows as $row) {
|
|
$result[] = array(
|
|
'created_at' => $row->created_at,
|
|
'nama' => $row->nama ?: 'System',
|
|
'module' => $row->module,
|
|
'action' => $row->action,
|
|
'description' => $row->description,
|
|
'status' => $row->status,
|
|
'ip_address' => $row->ip_address,
|
|
);
|
|
}
|
|
|
|
return $this->json(array(
|
|
'draw' => $draw,
|
|
'recordsTotal' => $recordsTotal,
|
|
'recordsFiltered' => $recordsFiltered,
|
|
'data' => $result,
|
|
));
|
|
}
|
|
|
|
public function get_chart()
|
|
{
|
|
$year = (int) $this->input->get('year');
|
|
if ($year < 2000 || $year > 2200) {
|
|
$year = (int) date('Y');
|
|
}
|
|
|
|
$companyId = (int) $this->companycontext->id();
|
|
$rows = $this->db->query(
|
|
"SELECT MONTH(j.tanggal) month_no,
|
|
COALESCE(SUM(CASE WHEN a.tipe='revenue' THEN d.kredit-d.debit ELSE 0 END),0) revenue,
|
|
COALESCE(SUM(CASE WHEN a.tipe='expense' THEN d.debit-d.kredit ELSE 0 END),0) expense
|
|
FROM journal_details d
|
|
JOIN journals j ON j.id=d.journal_id
|
|
JOIN accounts a ON a.id=d.account_id
|
|
WHERE j.company_id=? AND j.status IN('posted','reversed')
|
|
AND a.kategori='laba_rugi' AND a.is_active=1 AND YEAR(j.tanggal)=?
|
|
GROUP BY MONTH(j.tanggal)
|
|
ORDER BY month_no",
|
|
array($companyId, $year)
|
|
)->result();
|
|
|
|
$months = array('Jan', 'Feb', 'Mar', 'Apr', 'Mei', 'Jun', 'Jul', 'Agu', 'Sep', 'Okt', 'Nov', 'Des');
|
|
$revenue = array_fill(0, 12, 0);
|
|
$expense = array_fill(0, 12, 0);
|
|
foreach ($rows as $row) {
|
|
$index = (int) $row->month_no - 1;
|
|
if ($index >= 0 && $index < 12) {
|
|
$revenue[$index] = (float) $row->revenue;
|
|
$expense[$index] = (float) $row->expense;
|
|
}
|
|
}
|
|
|
|
return $this->json(array(
|
|
'year' => $year,
|
|
'labels' => $months,
|
|
'revenue' => $revenue,
|
|
'expense' => $expense,
|
|
));
|
|
}
|
|
|
|
public function get_years()
|
|
{
|
|
$this->db->select('DISTINCT YEAR(tanggal) year', false)
|
|
->from('journals')
|
|
->where('company_id', (int) $this->companycontext->id())
|
|
->where_in('status', array('posted', 'reversed'))
|
|
->order_by('year', 'DESC');
|
|
$years = $this->db->get()->result();
|
|
if (!$years) {
|
|
$years = array((object) array('year' => (int) date('Y')));
|
|
}
|
|
return $this->json($years);
|
|
}
|
|
|
|
private function financialSummary($companyId, $year)
|
|
{
|
|
$row = $this->db->query(
|
|
"SELECT
|
|
COALESCE(SUM(CASE WHEN a.tipe='revenue' THEN d.kredit-d.debit ELSE 0 END),0) total_revenue,
|
|
COALESCE(SUM(CASE WHEN a.tipe='expense' THEN d.debit-d.kredit ELSE 0 END),0) total_expense
|
|
FROM journal_details d
|
|
JOIN journals j ON j.id=d.journal_id
|
|
JOIN accounts a ON a.id=d.account_id
|
|
WHERE j.company_id=? AND j.status IN('posted','reversed')
|
|
AND a.kategori='laba_rugi' AND a.is_active=1 AND YEAR(j.tanggal)=?",
|
|
array((int) $companyId, (int) $year)
|
|
)->row_array();
|
|
|
|
$revenue = (float) ($row['total_revenue'] ?? 0);
|
|
$expense = (float) ($row['total_expense'] ?? 0);
|
|
return array(
|
|
'total_revenue' => $revenue,
|
|
'total_expense' => $expense,
|
|
'net_profit' => $revenue - $expense,
|
|
);
|
|
}
|
|
|
|
private function operationalOverview($companyId)
|
|
{
|
|
$result = array(
|
|
'receivable_amount' => 0,
|
|
'receivable_count' => 0,
|
|
'receivable_overdue' => 0,
|
|
'payable_amount' => 0,
|
|
'payable_count' => 0,
|
|
'payable_overdue' => 0,
|
|
'inventory_value' => 0,
|
|
'active_items' => 0,
|
|
'pending_items' => 0,
|
|
'pending_approvals' => 0,
|
|
);
|
|
|
|
if ($this->db->table_exists('invoices')) {
|
|
$whereDeleted = $this->db->field_exists('deleted_at', 'invoices') ? ' AND deleted_at IS NULL' : '';
|
|
$row = $this->db->query(
|
|
"SELECT COUNT(*) total,
|
|
COALESCE(SUM(sisa_piutang),0) amount,
|
|
SUM(CASE WHEN jatuh_tempo<CURDATE() THEN 1 ELSE 0 END) overdue
|
|
FROM invoices
|
|
WHERE company_id=? AND workflow_status='posted' AND sisa_piutang>0.001" . $whereDeleted,
|
|
array($companyId)
|
|
)->row();
|
|
if ($row) {
|
|
$result['receivable_count'] = (int) $row->total;
|
|
$result['receivable_amount'] = (float) $row->amount;
|
|
$result['receivable_overdue'] = (int) $row->overdue;
|
|
}
|
|
}
|
|
|
|
if ($this->db->table_exists('supplier_invoices')) {
|
|
$companyFilter = $this->db->field_exists('company_id', 'supplier_invoices')
|
|
? ' AND company_id=?'
|
|
: '';
|
|
$bindings = $companyFilter === '' ? array() : array($companyId);
|
|
$row = $this->db->query(
|
|
"SELECT COUNT(*) total,
|
|
COALESCE(SUM(balance),0) amount,
|
|
SUM(CASE WHEN due_date<=CURDATE() THEN 1 ELSE 0 END) overdue
|
|
FROM supplier_invoices
|
|
WHERE status='partial' AND balance>0.001" . $companyFilter,
|
|
$bindings
|
|
)->row();
|
|
if ($row) {
|
|
$result['payable_count'] = (int) $row->total;
|
|
$result['payable_amount'] = (float) $row->amount;
|
|
$result['payable_overdue'] = (int) $row->overdue;
|
|
}
|
|
}
|
|
|
|
if ($this->db->table_exists('items')) {
|
|
$result['active_items'] = (int) $this->db
|
|
->where(array('company_id' => $companyId, 'status' => 'active'))
|
|
->count_all_results('items');
|
|
|
|
if ($this->db->table_exists('item_barcodes')) {
|
|
$pending = $this->db->query(
|
|
"SELECT COUNT(DISTINCT i.id) total
|
|
FROM items i
|
|
LEFT JOIN item_barcodes b ON b.item_id=i.id AND b.status='pending'
|
|
WHERE i.company_id=? AND (i.status='draft' OR b.id IS NOT NULL)",
|
|
array($companyId)
|
|
)->row();
|
|
$result['pending_items'] = (int) ($pending ? $pending->total : 0);
|
|
} else {
|
|
$result['pending_items'] = (int) $this->db
|
|
->where(array('company_id' => $companyId, 'status' => 'draft'))
|
|
->count_all_results('items');
|
|
}
|
|
}
|
|
|
|
if ($this->db->table_exists('inventory_ledger') && $this->db->field_exists('value', 'inventory_ledger')) {
|
|
$inventory = $this->db->query(
|
|
"SELECT COALESCE(SUM(CASE WHEN l.direction='in' THEN l.value ELSE -l.value END),0) amount
|
|
FROM inventory_ledger l JOIN items i ON i.id=l.item_id WHERE i.company_id=?",
|
|
array($companyId)
|
|
)->row();
|
|
$result['inventory_value'] = (float) ($inventory ? $inventory->amount : 0);
|
|
}
|
|
|
|
if ($this->db->table_exists('approval_requests')
|
|
&& (is_master_admin_user() || check_permission('approvals', 'can_view'))) {
|
|
$result['pending_approvals'] = (int) $this->db
|
|
->where('status', 'pending')
|
|
->count_all_results('approval_requests');
|
|
}
|
|
|
|
return $result;
|
|
}
|
|
|
|
private function insights($companyId)
|
|
{
|
|
if (!$this->db->table_exists('ai_insights')) {
|
|
return array();
|
|
}
|
|
$this->db->select('type,severity,message,meta,created_at')
|
|
->where('period', date('Y-m'));
|
|
if ($this->db->field_exists('company_id', 'ai_insights')) {
|
|
$this->db->where('company_id', $companyId);
|
|
}
|
|
return $this->db
|
|
->order_by("CASE severity WHEN 'critical' THEN 1 WHEN 'warning' THEN 2 ELSE 3 END", '', false)
|
|
->order_by('created_at', 'DESC')
|
|
->limit(8)
|
|
->get('ai_insights')
|
|
->result();
|
|
}
|
|
|
|
private function quickLinks()
|
|
{
|
|
$definitions = array(
|
|
array('feature' => 'invoices', 'action' => 'can_create', 'label' => 'Buat Invoice', 'description' => 'Catat tagihan penjualan', 'icon' => 'bi-receipt-cutoff', 'url' => 'invoices/create'),
|
|
array('feature' => 'purchases', 'action' => 'can_create', 'label' => 'Purchase Workflow', 'description' => 'Buat permintaan pembelian', 'icon' => 'bi-cart-plus', 'url' => 'purchases'),
|
|
array('feature' => 'jurnal', 'action' => 'can_create', 'label' => 'Jurnal Umum', 'description' => 'Catat jurnal manual', 'icon' => 'bi-journal-plus', 'url' => 'jurnal'),
|
|
array('feature' => 'items', 'action' => 'can_view', 'label' => 'Scan Persediaan', 'description' => 'Periksa barcode dan stok', 'icon' => 'bi-qr-code-scan', 'url' => 'inventoryprofessional#scanner'),
|
|
);
|
|
|
|
$links = array();
|
|
foreach ($definitions as $definition) {
|
|
if (is_master_admin_user() || check_permission($definition['feature'], $definition['action'])) {
|
|
$links[] = $definition;
|
|
}
|
|
}
|
|
return $links;
|
|
}
|
|
|
|
private function activityQuery($search = '')
|
|
{
|
|
$this->db->select('activity_logs.*,users.nama')
|
|
->from('activity_logs')
|
|
->join('users', 'users.id=activity_logs.user_id', 'left');
|
|
|
|
if ($this->db->field_exists('company_id', 'activity_logs')) {
|
|
$this->db->where('activity_logs.company_id', (int) $this->companycontext->id());
|
|
} elseif (!is_master_admin_user()) {
|
|
$this->db->where('activity_logs.user_id', (int) $this->session->userdata('user_id'));
|
|
}
|
|
|
|
if ($search !== '') {
|
|
$this->db->group_start()
|
|
->like('activity_logs.module', $search)
|
|
->or_like('activity_logs.action', $search)
|
|
->or_like('activity_logs.description', $search)
|
|
->or_like('activity_logs.status', $search)
|
|
->or_like('users.nama', $search)
|
|
->group_end();
|
|
}
|
|
}
|
|
|
|
private function percentageChange($current, $previous)
|
|
{
|
|
$previous = (float) $previous;
|
|
if (abs($previous) < 0.01) {
|
|
return null;
|
|
}
|
|
return round((((float) $current - $previous) / abs($previous)) * 100, 1);
|
|
}
|
|
|
|
private function json($payload)
|
|
{
|
|
return $this->output
|
|
->set_content_type('application/json', 'utf-8')
|
|
->set_output(json_encode($payload, JSON_UNESCAPED_UNICODE));
|
|
}
|
|
}
|