Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- <?php
- include('include/conn.php');
- $akun_id = isset($_GET['akun_id'])?$_GET['akun_id']:'1';
- $bulan = isset($_GET['bulan'])?$_GET['bulan']:date('m');
- $cabang= isset($_GET['cabang'])?$_GET['cabang']:'admin';
- $tahun = isset($_GET['tahun'])?$_GET['tahun']:date('Y');
- // nama file
- $namaFile = "laporan_arus_kas.xls";
- // Function penanda awal file (Begin Of File) Excel
- function xlsBOF() {
- echo pack("ssssss", 0x809, 0x8, 0x0, 0x10, 0x0, 0x0);
- return;
- }
- // Function penanda akhir file (End Of File) Excel
- function xlsEOF() {
- echo pack("ss", 0x0A, 0x00);
- return;
- }
- // Function untuk menulis data (angka) ke cell excel
- function xlsWriteNumber($Row, $Col, $Value) {
- echo pack("sssss", 0x203, 14, $Row, $Col, 0x0);
- echo pack("d", $Value);
- return;
- }
- // Function untuk menulis data (text) ke cell excel
- function xlsWriteLabel($Row, $Col, $Value) {
- $L = strlen($Value);
- echo pack("ssssss", 0x204, 8 + $L, $Row, $Col, 0x0, $L);
- echo $Value;
- return;
- }
- header("Cache-Control: no-cache, no-store, must-revalidate");
- header("Content-Type: application/vnd.ms-excel");
- header("Content-Disposition: attachment; filename=laporan_arus_kas.xls");
- // memanggil function penanda awal file excel
- xlsBOF();
- // ------ membuat kolom pada excel --- //
- //mengisi pada cell A1 (baris ke-0, kolom ke-0)
- xlsWriteLabel(0, 0, "LAPORAN ARUS KAS TAHUN " . $tahun . " BULAN " . $bulan);
- // -------- menampilkan data --------- //
- // koneksi ke mysql
- mysql_connect("localhost", "root", "");
- mysql_select_db("akuntansix");
- // query menampilkan semua data
- $query = mysql_query(
- "SELECT
- SUM(IF(`akun`.`klasifikasi_id`='1' OR `akun`.`klasifikasi_id`='1',
- `jurnal`.`debit`-`jurnal`.`kredit`,
- `jurnal`.`kredit`-`jurnal`.`debit`)) AS saldo
- FROM
- `akun`
- INNER JOIN `jurnal`
- ON `jurnal`.`akun_id` =`akun`.`id`
- INNER JOIN `transaksi`
- ON `jurnal`.`transaksi_id` =`transaksi`.`id`
- WHERE
- `akun`.`id` = '$akun_id'
- AND transaksi.username='$cabang'
- AND MONTH(transaksi.tanggal) < '$bulan'
- AND YEAR(transaksi.tanggal) = '$tahun'",
- $conn) or die(mysql_error());
- $ra = mysql_fetch_array($query);
- $sa=$ra['saldo'];
- // nilai awal untuk baris cell
- xlsWriteLabel(2, 0, "NOTA");
- xlsWriteLabel(2, 1, "TANGGAL");
- xlsWriteLabel(2, 2, "KODE");
- xlsWriteLabel(2, 3, "KETERANGAN");
- xlsWriteLabel(2, 4, "DEBIT");
- xlsWriteLabel(2, 5, "KREDIT");
- xlsWriteLabel(2, 6, "SALDO");
- xlsWriteLabel(3, 0, "Saldo awal (saldo bulan sebelumnya)");
- xlsWriteNumber(3, 6, $sa);
- $noBarisCell = 4;
- // nilai awal untuk nomor urut data
- $noData = 1;
- $query = mysql_query("SELECT
- `transaksi`.`tanggal`,
- `akun`.`kode`,
- `transaksi`.`keterangan`,
- `akun`.`nama`,
- IF(`transaksi`.`jenis`='KM',
- IF(`akun`.`klasifikasi_id`='1' OR `akun`.`klasifikasi_id`='1',
- `jurnal`.`debit`-`jurnal`.`kredit`,
- `jurnal`.`kredit`-`jurnal`.`debit`),
- IF(`akun`.`klasifikasi_id`='1' OR `akun`.`klasifikasi_id`='1',
- `jurnal`.`kredit`-`jurnal`.`debit`,
- `jurnal`.`debit`-`jurnal`.`kredit`)) AS saldo,
- `transaksi`.`jumlah`,
- `transaksi`.`jenis`
- FROM
- `akun`
- INNER JOIN `jurnal`
- ON `jurnal`.`akun_id` =`akun`.`id`
- INNER JOIN `transaksi`
- ON `jurnal`.`transaksi_id` = `transaksi`.`id`
- WHERE
- `transaksi`.`username` = '$cabang'
- AND MONTH(transaksi.tanggal)='$bulan'
- AND YEAR(transaksi.tanggal)='$tahun'
- GROUP BY `transaksi`.`keterangan`
- ORDER BY `jurnal`.`akun_id` ASC",
- $conn) or die(mysql_error());
- $saldo = $sa;
- while ($data = mysql_fetch_array($query)) {
- $saldo+=$data['saldo'];
- // menampilkan data nim
- xlsWriteLabel($noBarisCell, 0, $noData);
- // menampilkan data nilai
- xlsWriteLabel($noBarisCell, 1, date("Y-m-d", strtotime($data['tanggal'])));
- // menampilkan data nilai
- xlsWriteLabel($noBarisCell, 2, $data['kode']);
- // menampilkan data nilai
- xlsWriteLabel($noBarisCell, 3, $data['keterangan']);
- // menampilkan data nilai
- if($data["jenis"] == "KM"){
- xlsWriteNumber($noBarisCell, 4, $data['jumlah']);
- xlsWriteNumber($noBarisCell, 5, 0);
- }else{
- xlsWriteNumber($noBarisCell, 4, 0);
- xlsWriteNumber($noBarisCell, 5, $data['jumlah']);
- }
- xlsWriteNumber($noBarisCell, 6, $saldo);
- // increment untuk no. baris cell dan no. urut data
- $noBarisCell++;
- $noData++;
- }
- // memanggil function penanda akhir file excel
- xlsEOF();
- exit();
- ?>
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement