291 lines
13 KiB
PHP
291 lines
13 KiB
PHP
<?php
|
|
require_once 'db/config.php';
|
|
|
|
$error_message = null;
|
|
$records = [];
|
|
$total_pages = 1;
|
|
$page = isset($_GET['page']) ? (int)$_GET['page'] : 1;
|
|
$limit = 20; // Records per page for raw data table
|
|
$offset = ($page - 1) * $limit;
|
|
|
|
try {
|
|
$pdo = db();
|
|
|
|
// Get distinct values for filters
|
|
$sales_options_stmt = $pdo->query('SELECT DISTINCT NAMA_SALES FROM tabelmaster WHERE NAMA_SALES IS NOT NULL AND NAMA_SALES != "" ORDER BY NAMA_SALES');
|
|
$sales_options = $sales_options_stmt->fetchAll(PDO::FETCH_COLUMN);
|
|
|
|
$periode_options_stmt = $pdo->query('SELECT DISTINCT PERIODE FROM tabelmaster WHERE PERIODE IS NOT NULL AND PERIODE != "" ORDER BY PERIODE DESC');
|
|
$periode_options = $periode_options_stmt->fetchAll(PDO::FETCH_COLUMN);
|
|
|
|
// Handle form submission for analysis
|
|
$analysis_results = [];
|
|
$chart_labels_json = '[]';
|
|
$chart_data_json = '[]';
|
|
$selected_sales = $_POST['nama_sales'] ?? 'all';
|
|
$selected_periode = $_POST['periode'] ?? 'all';
|
|
|
|
if ($_SERVER['REQUEST_METHOD'] === 'POST' && isset($_POST['run_analysis'])) {
|
|
$sql = "
|
|
SELECT
|
|
MERK,
|
|
SUM(QTY) as total_qty,
|
|
SUM(QTY * CAST(REPLACE(HARGA, ",", "") AS DECIMAL(15,2))) as total_omzet,
|
|
AVG(CAST(REPLACE(HARGA, ",", "") AS DECIMAL(15,2))) as avg_price,
|
|
COUNT(DISTINCT NO_FAKTUR) as transactions_count
|
|
FROM
|
|
tabelmaster
|
|
WHERE 1=1";
|
|
|
|
$params = [];
|
|
if ($selected_sales !== 'all') {
|
|
$sql .= ' AND NAMA_SALES = :nama_sales';
|
|
$params[':nama_sales'] = $selected_sales;
|
|
}
|
|
if ($selected_periode !== 'all') {
|
|
$sql .= ' AND PERIODE = :periode';
|
|
$params[':periode'] = $selected_periode;
|
|
}
|
|
|
|
$sql .= ' GROUP BY MERK HAVING SUM(QTY) > 0 ORDER BY total_omzet DESC';
|
|
|
|
$analysis_stmt = $pdo->prepare($sql);
|
|
$analysis_stmt->execute($params);
|
|
$analysis_results = $analysis_stmt->fetchAll();
|
|
|
|
if (!empty($analysis_results)) {
|
|
$chart_labels = [];
|
|
$chart_data = [];
|
|
foreach ($analysis_results as $row) {
|
|
$chart_labels[] = $row['MERK'];
|
|
$chart_data[] = $row['total_omzet'];
|
|
}
|
|
$chart_labels_json = json_encode($chart_labels);
|
|
$chart_data_json = json_encode($chart_data);
|
|
}
|
|
}
|
|
|
|
|
|
// Get total records for raw data pagination
|
|
$total_stmt = $pdo->query('SELECT COUNT(*) FROM tabelmaster');
|
|
$total_records = $total_stmt->fetchColumn();
|
|
$total_pages = ceil($total_records / $limit);
|
|
|
|
// Fetch records for the current page of raw data
|
|
$stmt = $pdo->prepare('SELECT * FROM tabelmaster ORDER BY TANGGAL DESC, NO_FAKTUR DESC LIMIT :limit OFFSET :offset');
|
|
$stmt->bindParam(':limit', $limit, PDO::PARAM_INT);
|
|
$stmt->bindParam(':offset', $offset, PDO::PARAM_INT);
|
|
$stmt->execute();
|
|
$records = $stmt->fetchAll();
|
|
|
|
} catch (PDOException $e) {
|
|
$error_message = "Database error: " . $e->getMessage();
|
|
}
|
|
|
|
require_once 'header.php';
|
|
?>
|
|
|
|
<main class="container">
|
|
<?php if (isset($error_message)): ?>
|
|
<div class="alert alert-danger" role="alert">
|
|
<?php echo htmlspecialchars($error_message); ?>
|
|
</div>
|
|
<?php endif; ?>
|
|
|
|
<!-- Analysis Form -->
|
|
<div class="card mb-4">
|
|
<div class="card-header">
|
|
Analisa Penjualan per Merk
|
|
</div>
|
|
<div class="card-body">
|
|
<form method="POST" action="index.php#analysis-results">
|
|
<div class="row g-3 align-items-end">
|
|
<div class="col-md-5">
|
|
<label for="nama_sales" class="form-label">Nama Sales</label>
|
|
<select name="nama_sales" id="nama_sales" class="form-select">
|
|
<option value="all">Semua Sales</option>
|
|
<?php foreach ($sales_options as $sales): ?>
|
|
<option value="<?php echo htmlspecialchars($sales); ?>" <?php echo ($selected_sales === $sales) ? 'selected' : ''; ?> >
|
|
<?php echo htmlspecialchars($sales); ?>
|
|
</option>
|
|
<?php endforeach; ?>
|
|
</select>
|
|
</div>
|
|
<div class="col-md-5">
|
|
<label for="periode" class="form-label">Periode</label>
|
|
<select name="periode" id="periode" class="form-select">
|
|
<option value="all">Semua Periode</option>
|
|
<?php foreach ($periode_options as $periode): ?>
|
|
<option value="<?php echo htmlspecialchars($periode); ?>" <?php echo ($selected_periode === $periode) ? 'selected' : ''; ?> >
|
|
<?php echo htmlspecialchars($periode); ?>
|
|
</option>
|
|
<?php endforeach; ?>
|
|
</select>
|
|
</div>
|
|
<div class="col-md-2">
|
|
<button type="submit" name="run_analysis" class="btn btn-primary w-100">Jalankan</button>
|
|
</div>
|
|
</div>
|
|
</form>
|
|
</div>
|
|
</div>
|
|
|
|
<!-- Analysis Results -->
|
|
<?php if (!empty($analysis_results)): ?>
|
|
<div class="card mb-4" id="analysis-results">
|
|
<div class="card-header">
|
|
Hasil Analisa
|
|
</div>
|
|
<div class="card-body">
|
|
<div class="table-responsive">
|
|
<table class="table table-bordered table-hover">
|
|
<thead class="table-light">
|
|
<tr>
|
|
<th>Merk</th>
|
|
<th>Total Kuantitas</th>
|
|
<th>Total Omzet</th>
|
|
<th>Harga Rata-rata</th>
|
|
<th>Jumlah Transaksi</th>
|
|
</tr>
|
|
</thead>
|
|
<tbody>
|
|
<?php foreach ($analysis_results as $row): ?>
|
|
<tr>
|
|
<td><?php echo htmlspecialchars($row['MERK']); ?></td>
|
|
<td><?php echo number_format($row['total_qty']); ?></td>
|
|
<td>Rp <?php echo number_format($row['total_omzet'], 2, ",", "."); ?></td>
|
|
<td>Rp <?php echo number_format($row['avg_price'], 2, ",", "."); ?></td>
|
|
<td><?php echo number_format($row['transactions_count']); ?></td>
|
|
</tr>
|
|
<?php endforeach; ?>
|
|
</tbody>
|
|
</table>
|
|
</div>
|
|
<div class="mt-4">
|
|
<canvas id="merkChart"></canvas>
|
|
</div>
|
|
</div>
|
|
</div>
|
|
<?php elseif ($_SERVER['REQUEST_METHOD'] === 'POST' && isset($_POST['run_analysis'])): ?>
|
|
<div class="card mb-4" id="analysis-results">
|
|
<div class="card-header">
|
|
Hasil Analisa
|
|
</div>
|
|
<div class="card-body">
|
|
<div class="alert alert-info">Tidak ada data yang cocok dengan filter yang dipilih.</div>
|
|
</div>
|
|
</div>
|
|
<?php endif; ?>
|
|
|
|
|
|
<!-- Raw Data Table -->
|
|
<div class="card">
|
|
<div class="card-header">
|
|
Data Master
|
|
</div>
|
|
<div class="card-body">
|
|
<?php if (empty($records)): ?>
|
|
<div class="alert alert-info" role="alert">
|
|
No records found in the 'tabelmaster'. You can start by importing your data.
|
|
</div>
|
|
<?php else: ?>
|
|
<div class="table-responsive">
|
|
<table class="table table-striped table-hover">
|
|
<thead class="table-light">
|
|
<tr>
|
|
<th>Nama Sales</th>
|
|
<th>Tanggal</th>
|
|
<th>No Faktur</th>
|
|
<th>Merk</th>
|
|
<th>Outlet</th>
|
|
<th>Produk</th>
|
|
<th>Qty</th>
|
|
<th>Harga</th>
|
|
<th>Bulan</th>
|
|
<th>Tahun</th>
|
|
<th>Periode</th>
|
|
</tr>
|
|
</thead>
|
|
<tbody>
|
|
<?php foreach ($records as $row): ?>
|
|
<tr>
|
|
<td><?php echo htmlspecialchars($row['NAMA_SALES']); ?></td>
|
|
<td><?php echo htmlspecialchars($row['TANGGAL']); ?></td>
|
|
<td><?php echo htmlspecialchars($row['NO_FAKTUR']); ?></td>
|
|
<td><?php echo htmlspecialchars($row['MERK']); ?></td>
|
|
<td><?php echo htmlspecialchars($row['OUTLET']); ?></td>
|
|
<td><?php echo htmlspecialchars($row['PRODUK']); ?></td>
|
|
<td><?php echo htmlspecialchars($row['QTY']); ?></td>
|
|
<td><?php echo htmlspecialchars($row['HARGA']); ?></td>
|
|
<td><?php echo htmlspecialchars($row['BULAN']); ?></td>
|
|
<td><?php echo htmlspecialchars($row['TAHUN']); ?></td>
|
|
<td><?php echo htmlspecialchars($row['PERIODE']); ?></td>
|
|
</tr>
|
|
<?php endforeach; ?>
|
|
</tbody>
|
|
</table>
|
|
</div>
|
|
|
|
<!-- Pagination -->
|
|
<nav aria-label="Page navigation">
|
|
<ul class="pagination justify-content-center">
|
|
<li class="page-item <?php echo ($page <= 1) ? 'disabled' : ''; ?>">
|
|
<a class="page-link" href="?page=<?php echo $page - 1; ">Previous</a>
|
|
</li>
|
|
<li class="page-item <?php echo ($page >= $total_pages) ? 'disabled' : ''; ?>">
|
|
<a class="page-link" href="?page=<?php echo $page + 1; ">Next</a>
|
|
</li>
|
|
</ul>
|
|
</nav>
|
|
<?php endif; ?>
|
|
</div>
|
|
</div>
|
|
|
|
<script>
|
|
if (typeof Chart !== 'undefined' && <?php echo !empty($analysis_results) ? 'true' : 'false'; ?>) {
|
|
const ctx = document.getElementById('merkChart');
|
|
new Chart(ctx, {
|
|
type: 'bar',
|
|
data: {
|
|
labels: <?php echo $chart_labels_json; ?>,
|
|
datasets: [{
|
|
label: 'Total Omzet',
|
|
data: <?php echo $chart_data_json; ?>,
|
|
backgroundColor: 'rgba(13, 110, 253, 0.5)',
|
|
borderColor: 'rgba(13, 110, 253, 1)',
|
|
borderWidth: 1
|
|
}]
|
|
},
|
|
options: {
|
|
scales: {
|
|
y: {
|
|
beginAtZero: true,
|
|
ticks: {
|
|
callback: function(value, index, values) {
|
|
return 'Rp ' + new Intl.NumberFormat('id-ID').format(value);
|
|
}
|
|
}
|
|
}
|
|
},
|
|
plugins: {
|
|
tooltip: {
|
|
callbacks: {
|
|
label: function(context) {
|
|
let label = context.dataset.label || '';
|
|
if (label) {
|
|
label += ': ';
|
|
}
|
|
if (context.parsed.y !== null) {
|
|
label += 'Rp ' + new Intl.NumberFormat('id-ID').format(context.parsed.y);
|
|
}
|
|
return label;
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
});
|
|
}
|
|
</script>
|
|
|
|
<?php require_once 'footer.php'; ?>
|