470 lines
17 KiB
PHP
470 lines
17 KiB
PHP
<?php
|
|
// Include database connection
|
|
require_once '../../routes/db_conn.php';
|
|
|
|
|
|
// Function to generate PDF report
|
|
function generatePDF($filters) {
|
|
require_once '../vendor/autoload.php'; // Require TCPDF or FPDF library
|
|
|
|
// Create new PDF document
|
|
$pdf = new TCPDF(PDF_PAGE_ORIENTATION, PDF_UNIT, PDF_PAGE_FORMAT, true, 'UTF-8', false);
|
|
|
|
// Set document information
|
|
$pdf->SetCreator('Ayula Store');
|
|
$pdf->SetAuthor('Ayula Store');
|
|
$pdf->SetTitle('Laporan Inventaris');
|
|
$pdf->SetSubject('Laporan Inventaris');
|
|
$pdf->SetKeywords('Laporan, Inventaris, Ayula Store');
|
|
|
|
// Set default header data
|
|
$pdf->SetHeaderData('logo.png', 30, 'Ayula Store', 'Laporan Inventaris', array(0,64,255), array(0,64,128));
|
|
$pdf->setFooterData(array(0,64,0), array(0,64,128));
|
|
|
|
// Set header and footer fonts
|
|
$pdf->setHeaderFont(Array(PDF_FONT_NAME_MAIN, '', PDF_FONT_SIZE_MAIN));
|
|
$pdf->setFooterFont(Array(PDF_FONT_NAME_DATA, '', PDF_FONT_SIZE_DATA));
|
|
|
|
// Set default monospaced font
|
|
$pdf->SetDefaultMonospacedFont(PDF_FONT_MONOSPACED);
|
|
|
|
// Set margins
|
|
$pdf->SetMargins(PDF_MARGIN_LEFT, PDF_MARGIN_TOP, PDF_MARGIN_RIGHT);
|
|
$pdf->SetHeaderMargin(PDF_MARGIN_HEADER);
|
|
$pdf->SetFooterMargin(PDF_MARGIN_FOOTER);
|
|
|
|
// Set auto page breaks
|
|
$pdf->SetAutoPageBreak(TRUE, PDF_MARGIN_BOTTOM);
|
|
|
|
// Set image scale factor
|
|
$pdf->setImageScale(PDF_IMAGE_SCALE_RATIO);
|
|
|
|
// Add a page
|
|
$pdf->AddPage();
|
|
|
|
// Set font
|
|
$pdf->SetFont('helvetica', '', 10);
|
|
|
|
// Get report data
|
|
$conn = new mysqli(DB_HOST, DB_USER, DB_PASS, DB_NAME);
|
|
if ($conn->connect_error) {
|
|
die("Connection failed: " . $conn->connect_error);
|
|
}
|
|
|
|
// Build query based on filters
|
|
$sql = "SELECT r.id_report, r.tanggal, r.jumlah, r.harga, r.image,
|
|
b.nama_barang, b.kode_barang,
|
|
jb.nama_jenis
|
|
FROM report r
|
|
JOIN barang b ON r.id_barang = b.id_barang
|
|
JOIN jenis_barang jb ON b.id_jenis = jb.id_jenis
|
|
WHERE 1=1";
|
|
|
|
$params = array();
|
|
$types = "";
|
|
|
|
if (!empty($filters['start_date']) && !empty($filters['end_date'])) {
|
|
$sql .= " AND DATE(r.tanggal) BETWEEN ? AND ?";
|
|
$params[] = $filters['start_date'];
|
|
$params[] = $filters['end_date'];
|
|
$types .= "ss";
|
|
} elseif (!empty($filters['month']) && !empty($filters['year'])) {
|
|
$sql .= " AND MONTH(r.tanggal) = ? AND YEAR(r.tanggal) = ?";
|
|
$params[] = $filters['month'];
|
|
$params[] = $filters['year'];
|
|
$types .= "ss";
|
|
} elseif (!empty($filters['year'])) {
|
|
$sql .= " AND YEAR(r.tanggal) = ?";
|
|
$params[] = $filters['year'];
|
|
$types .= "s";
|
|
}
|
|
|
|
if (!empty($filters['product_id'])) {
|
|
$sql .= " AND b.id_barang = ?";
|
|
$params[] = $filters['product_id'];
|
|
$types .= "i";
|
|
}
|
|
|
|
if (!empty($filters['category_id'])) {
|
|
$sql .= " AND b.id_jenis = ?";
|
|
$params[] = $filters['category_id'];
|
|
$types .= "i";
|
|
}
|
|
|
|
$sql .= " ORDER BY r.tanggal DESC";
|
|
|
|
$stmt = $conn->prepare($sql);
|
|
|
|
if (!empty($params)) {
|
|
$stmt->bind_param($types, ...$params);
|
|
}
|
|
|
|
$stmt->execute();
|
|
$result = $stmt->get_result();
|
|
|
|
// Add report title
|
|
$title = 'Laporan Inventaris Ayula Store';
|
|
$period = '';
|
|
|
|
if (!empty($filters['start_date']) && !empty($filters['end_date'])) {
|
|
$start = date('d M Y', strtotime($filters['start_date']));
|
|
$end = date('d M Y', strtotime($filters['end_date']));
|
|
$period = "Periode: $start - $end";
|
|
} elseif (!empty($filters['month']) && !empty($filters['year'])) {
|
|
$month = date('F', mktime(0, 0, 0, $filters['month'], 1));
|
|
$period = "Periode: $month {$filters['year']}";
|
|
} elseif (!empty($filters['year'])) {
|
|
$period = "Periode: Tahun {$filters['year']}";
|
|
}
|
|
|
|
// Add title and period
|
|
$pdf->SetFont('helvetica', 'B', 15);
|
|
$pdf->Cell(0, 10, $title, 0, 1, 'C');
|
|
$pdf->SetFont('helvetica', '', 12);
|
|
$pdf->Cell(0, 10, $period, 0, 1, 'C');
|
|
$pdf->Ln(5);
|
|
|
|
// Add summary information
|
|
$pdf->SetFont('helvetica', 'B', 12);
|
|
$pdf->Cell(0, 10, 'Ringkasan', 0, 1, 'L');
|
|
$pdf->SetFont('helvetica', '', 10);
|
|
|
|
$total_items = 0;
|
|
$total_amount = 0;
|
|
$reports_count = $result->num_rows;
|
|
|
|
// Create table headers
|
|
$pdf->SetFont('helvetica', 'B', 9);
|
|
$pdf->SetFillColor(230, 230, 230);
|
|
$pdf->Cell(15, 7, 'ID', 1, 0, 'C', 1);
|
|
$pdf->Cell(25, 7, 'Tanggal', 1, 0, 'C', 1);
|
|
$pdf->Cell(50, 7, 'Produk', 1, 0, 'C', 1);
|
|
$pdf->Cell(30, 7, 'Kategori', 1, 0, 'C', 1);
|
|
$pdf->Cell(15, 7, 'Jumlah', 1, 0, 'C', 1);
|
|
$pdf->Cell(25, 7, 'Harga', 1, 0, 'C', 1);
|
|
$pdf->Cell(30, 7, 'Total', 1, 1, 'C', 1);
|
|
|
|
// Add data rows
|
|
$pdf->SetFont('helvetica', '', 8);
|
|
|
|
while ($row = $result->fetch_assoc()) {
|
|
$total = $row['jumlah'] * $row['harga'];
|
|
$total_items += $row['jumlah'];
|
|
$total_amount += $total;
|
|
|
|
$pdf->Cell(15, 6, $row['id_report'], 1, 0, 'C');
|
|
$pdf->Cell(25, 6, date('d M Y', strtotime($row['tanggal'])), 1, 0, 'C');
|
|
$pdf->Cell(50, 6, $row['nama_barang'], 1, 0, 'L');
|
|
$pdf->Cell(30, 6, $row['nama_jenis'], 1, 0, 'L');
|
|
$pdf->Cell(15, 6, $row['jumlah'], 1, 0, 'C');
|
|
$pdf->Cell(25, 6, 'Rp ' . number_format($row['harga'], 0, ',', '.'), 1, 0, 'R');
|
|
$pdf->Cell(30, 6, 'Rp ' . number_format($total, 0, ',', '.'), 1, 1, 'R');
|
|
}
|
|
|
|
// Add totals row
|
|
$pdf->SetFont('helvetica', 'B', 9);
|
|
$pdf->Cell(120, 7, 'TOTAL', 1, 0, 'R', 1);
|
|
$pdf->Cell(15, 7, $total_items, 1, 0, 'C', 1);
|
|
$pdf->Cell(25, 7, '', 1, 0, 'C', 1);
|
|
$pdf->Cell(30, 7, 'Rp ' . number_format($total_amount, 0, ',', '.'), 1, 1, 'R', 1);
|
|
|
|
// Add summary before table
|
|
$pdf->SetY(60); // Position at beginning
|
|
$pdf->SetFont('helvetica', '', 10);
|
|
$pdf->Cell(60, 7, 'Total Laporan: ' . $reports_count, 0, 1, 'L');
|
|
$pdf->Cell(60, 7, 'Total Items: ' . $total_items, 0, 1, 'L');
|
|
$pdf->Cell(60, 7, 'Total Nilai: Rp ' . number_format($total_amount, 0, ',', '.'), 0, 1, 'L');
|
|
$pdf->Ln(5);
|
|
|
|
// Close and output PDF
|
|
$pdf->Output('Laporan_Inventaris_' . date('Y-m-d') . '.pdf', 'I');
|
|
|
|
$stmt->close();
|
|
$conn->close();
|
|
}
|
|
|
|
// Function to export to Excel
|
|
function exportExcel($filters) {
|
|
require_once '../vendor/autoload.php'; // Require PhpSpreadsheet
|
|
|
|
use PhpOffice\PhpSpreadsheet\Spreadsheet;
|
|
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
|
|
|
|
// Create new Spreadsheet object
|
|
$spreadsheet = new Spreadsheet();
|
|
$sheet = $spreadsheet->getActiveSheet();
|
|
|
|
// Set document properties
|
|
$spreadsheet->getProperties()
|
|
->setCreator('Ayula Store')
|
|
->setLastModifiedBy('Ayula Store')
|
|
->setTitle('Laporan Inventaris')
|
|
->setSubject('Laporan Inventaris Ayula Store')
|
|
->setDescription('Laporan Inventaris Ayula Store');
|
|
|
|
// Get report data
|
|
$conn = new mysqli(DB_HOST, DB_USER, DB_PASS, DB_NAME);
|
|
if ($conn->connect_error) {
|
|
die("Connection failed: " . $conn->connect_error);
|
|
}
|
|
|
|
// Build query based on filters
|
|
$sql = "SELECT r.id_report, r.tanggal, r.jumlah, r.harga, r.image,
|
|
b.nama_barang, b.kode_barang,
|
|
jb.nama_jenis
|
|
FROM report r
|
|
JOIN barang b ON r.id_barang = b.id_barang
|
|
JOIN jenis_barang jb ON b.id_jenis = jb.id_jenis
|
|
WHERE 1=1";
|
|
|
|
$params = array();
|
|
$types = "";
|
|
|
|
if (!empty($filters['start_date']) && !empty($filters['end_date'])) {
|
|
$sql .= " AND DATE(r.tanggal) BETWEEN ? AND ?";
|
|
$params[] = $filters['start_date'];
|
|
$params[] = $filters['end_date'];
|
|
$types .= "ss";
|
|
} elseif (!empty($filters['month']) && !empty($filters['year'])) {
|
|
$sql .= " AND MONTH(r.tanggal) = ? AND YEAR(r.tanggal) = ?";
|
|
$params[] = $filters['month'];
|
|
$params[] = $filters['year'];
|
|
$types .= "ss";
|
|
} elseif (!empty($filters['year'])) {
|
|
$sql .= " AND YEAR(r.tanggal) = ?";
|
|
$params[] = $filters['year'];
|
|
$types .= "s";
|
|
}
|
|
|
|
if (!empty($filters['product_id'])) {
|
|
$sql .= " AND b.id_barang = ?";
|
|
$params[] = $filters['product_id'];
|
|
$types .= "i";
|
|
}
|
|
|
|
if (!empty($filters['category_id'])) {
|
|
$sql .= " AND b.id_jenis = ?";
|
|
$params[] = $filters['category_id'];
|
|
$types .= "i";
|
|
}
|
|
|
|
$sql .= " ORDER BY r.tanggal DESC";
|
|
|
|
$stmt = $conn->prepare($sql);
|
|
|
|
if (!empty($params)) {
|
|
$stmt->bind_param($types, ...$params);
|
|
}
|
|
|
|
$stmt->execute();
|
|
$result = $stmt->get_result();
|
|
|
|
// Add title
|
|
$sheet->setCellValue('A1', 'LAPORAN INVENTARIS AYULA STORE');
|
|
$sheet->mergeCells('A1:G1');
|
|
$sheet->getStyle('A1')->getFont()->setBold(true)->setSize(14);
|
|
$sheet->getStyle('A1')->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);
|
|
|
|
// Add period
|
|
$period = '';
|
|
if (!empty($filters['start_date']) && !empty($filters['end_date'])) {
|
|
$start = date('d M Y', strtotime($filters['start_date']));
|
|
$end = date('d M Y', strtotime($filters['end_date']));
|
|
$period = "Periode: $start - $end";
|
|
} elseif (!empty($filters['month']) && !empty($filters['year'])) {
|
|
$month = date('F', mktime(0, 0, 0, $filters['month'], 1));
|
|
$period = "Periode: $month {$filters['year']}";
|
|
} elseif (!empty($filters['year'])) {
|
|
$period = "Periode: Tahun {$filters['year']}";
|
|
}
|
|
|
|
$sheet->setCellValue('A2', $period);
|
|
$sheet->mergeCells('A2:G2');
|
|
$sheet->getStyle('A2')->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);
|
|
|
|
// Add summary information
|
|
$total_items = 0;
|
|
$total_amount = 0;
|
|
$reports_count = $result->num_rows;
|
|
|
|
// Calculate totals
|
|
$data = [];
|
|
while ($row = $result->fetch_assoc()) {
|
|
$data[] = $row;
|
|
$total_items += $row['jumlah'];
|
|
$total_amount += ($row['jumlah'] * $row['harga']);
|
|
}
|
|
|
|
// Add summary section
|
|
$sheet->setCellValue('A4', 'Ringkasan:');
|
|
$sheet->getStyle('A4')->getFont()->setBold(true);
|
|
|
|
$sheet->setCellValue('A5', 'Total Laporan:');
|
|
$sheet->setCellValue('B5', $reports_count);
|
|
|
|
$sheet->setCellValue('A6', 'Total Items:');
|
|
$sheet->setCellValue('B6', $total_items);
|
|
|
|
$sheet->setCellValue('A7', 'Total Nilai:');
|
|
$sheet->setCellValue('B7', 'Rp ' . number_format($total_amount, 0, ',', '.'));
|
|
|
|
// Add table headers
|
|
$sheet->setCellValue('A9', 'ID');
|
|
$sheet->setCellValue('B9', 'Tanggal');
|
|
$sheet->setCellValue('C9', 'Produk');
|
|
$sheet->setCellValue('D9', 'Kategori');
|
|
$sheet->setCellValue('E9', 'Jumlah');
|
|
$sheet->setCellValue('F9', 'Harga');
|
|
$sheet->setCellValue('G9', 'Total');
|
|
|
|
// Style the headers
|
|
$sheet->getStyle('A9:G9')->getFont()->setBold(true);
|
|
$sheet->getStyle('A9:G9')->getFill()->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)->getStartColor()->setRGB('CCCCCC');
|
|
$sheet->getStyle('A9:G9')->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);
|
|
|
|
// Add data rows
|
|
$row_num = 10;
|
|
foreach ($data as $row) {
|
|
$total = $row['jumlah'] * $row['harga'];
|
|
|
|
$sheet->setCellValue('A' . $row_num, $row['id_report']);
|
|
$sheet->setCellValue('B' . $row_num, date('d M Y', strtotime($row['tanggal'])));
|
|
$sheet->setCellValue('C' . $row_num, $row['nama_barang']);
|
|
$sheet->setCellValue('D' . $row_num, $row['nama_jenis']);
|
|
$sheet->setCellValue('E' . $row_num, $row['jumlah']);
|
|
$sheet->setCellValue('F' . $row_num, 'Rp ' . number_format($row['harga'], 0, ',', '.'));
|
|
$sheet->setCellValue('G' . $row_num, 'Rp ' . number_format($total, 0, ',', '.'));
|
|
|
|
$row_num++;
|
|
}
|
|
|
|
// Add totals row
|
|
$sheet->setCellValue('A' . $row_num, 'TOTAL');
|
|
$sheet->mergeCells('A' . $row_num . ':D' . $row_num);
|
|
$sheet->setCellValue('E' . $row_num, $total_items);
|
|
$sheet->setCellValue('G' . $row_num, 'Rp ' . number_format($total_amount, 0, ',', '.'));
|
|
|
|
$sheet->getStyle('A' . $row_num . ':G' . $row_num)->getFont()->setBold(true);
|
|
$sheet->getStyle('A' . $row_num . ':G' . $row_num)->getFill()->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)->getStartColor()->setRGB('EEEEEE');
|
|
|
|
// Autosize columns
|
|
foreach(range('A', 'G') as $col) {
|
|
$sheet->getColumnDimension($col)->setAutoSize(true);
|
|
}
|
|
|
|
// Create a border for the table
|
|
$styleArray = [
|
|
'borders' => [
|
|
'allBorders' => [
|
|
'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN,
|
|
],
|
|
],
|
|
];
|
|
$sheet->getStyle('A9:G' . $row_num)->applyFromArray($styleArray);
|
|
|
|
// Output excel file
|
|
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
|
|
header('Content-Disposition: attachment;filename="Laporan_Inventaris_' . date('Y-m-d') . '.xlsx"');
|
|
header('Cache-Control: max-age=0');
|
|
|
|
$writer = new Xlsx($spreadsheet);
|
|
$writer->save('php://output');
|
|
|
|
$stmt->close();
|
|
$conn->close();
|
|
exit();
|
|
}
|
|
|
|
// Process report export request
|
|
if (isset($_GET['export']) && in_array($_GET['export'], ['pdf', 'excel'])) {
|
|
$filters = [
|
|
'start_date' => isset($_GET['start_date']) ? $_GET['start_date'] : '',
|
|
'end_date' => isset($_GET['end_date']) ? $_GET['end_date'] : '',
|
|
'month' => isset($_GET['filter_month']) ? $_GET['filter_month'] : '',
|
|
'year' => isset($_GET['filter_year']) ? $_GET['filter_year'] : '',
|
|
'product_id' => isset($_GET['product_id']) ? $_GET['product_id'] : '',
|
|
'category_id' => isset($_GET['category_id']) ? $_GET['category_id'] : ''
|
|
];
|
|
|
|
if ($_GET['export'] == 'pdf') {
|
|
generatePDF($filters);
|
|
} else {
|
|
exportExcel($filters);
|
|
}
|
|
exit();
|
|
}
|
|
|
|
// Handle AJAX request for chart data
|
|
if (isset($_GET['action']) && $_GET['action'] == 'get_chart_data') {
|
|
$conn = new mysqli(DB_HOST, DB_USER, DB_PASS, DB_NAME);
|
|
if ($conn->connect_error) {
|
|
die(json_encode(['error' => 'Connection failed: ' . $conn->connect_error]));
|
|
}
|
|
|
|
// Build query based on filters
|
|
$sql = "SELECT jb.nama_jenis, SUM(r.jumlah) as total_items, SUM(r.jumlah * r.harga) as total_value
|
|
FROM report r
|
|
JOIN barang b ON r.id_barang = b.id_barang
|
|
JOIN jenis_barang jb ON b.id_jenis = jb.id_jenis
|
|
WHERE 1=1";
|
|
|
|
$params = array();
|
|
$types = "";
|
|
|
|
if (!empty($_GET['start_date']) && !empty($_GET['end_date'])) {
|
|
$sql .= " AND DATE(r.tanggal) BETWEEN ? AND ?";
|
|
$params[] = $_GET['start_date'];
|
|
$params[] = $_GET['end_date'];
|
|
$types .= "ss";
|
|
} elseif (!empty($_GET['filter_month']) && !empty($_GET['filter_year'])) {
|
|
$sql .= " AND MONTH(r.tanggal) = ? AND YEAR(r.tanggal) = ?";
|
|
$params[] = $_GET['filter_month'];
|
|
$params[] = $_GET['filter_year'];
|
|
$types .= "ss";
|
|
} elseif (!empty($_GET['filter_year'])) {
|
|
$sql .= " AND YEAR(r.tanggal) = ?";
|
|
$params[] = $_GET['filter_year'];
|
|
$types .= "s";
|
|
}
|
|
|
|
if (!empty($_GET['product_id'])) {
|
|
$sql .= " AND b.id_barang = ?";
|
|
$params[] = $_GET['product_id'];
|
|
$types .= "i";
|
|
}
|
|
|
|
if (!empty($_GET['category_id'])) {
|
|
$sql .= " AND b.id_jenis = ?";
|
|
$params[] = $_GET['category_id'];
|
|
$types .= "i";
|
|
}
|
|
|
|
$sql .= " GROUP BY jb.nama_jenis";
|
|
|
|
$stmt = $conn->prepare($sql);
|
|
|
|
if (!empty($params)) {
|
|
$stmt->bind_param($types, ...$params);
|
|
}
|
|
|
|
$stmt->execute();
|
|
$result = $stmt->get_result();
|
|
|
|
$chart_data = [
|
|
'labels' => [],
|
|
'item_counts' => [],
|
|
'values' => []
|
|
];
|
|
|
|
while ($row = $result->fetch_assoc()) {
|
|
$chart_data['labels'][] = $row['nama_jenis'];
|
|
$chart_data['item_counts'][] = (int)$row['total_items'];
|
|
$chart_data['values'][] = (float)$row['total_value'];
|
|
}
|
|
|
|
echo json_encode($chart_data);
|
|
|
|
$stmt->close();
|
|
$conn->close();
|
|
exit();
|
|
} |