<?php
defined('BASEPATH') OR exit('No direct script access allowed');
use PhpOffice\PhpSpreadsheet\Spreadsheet;

class Excel extends CI_Controller {
	public function __construct() {
		parent::__construct();
		cek_login();
	}

	public function export() {
        $spreadsheet = new Spreadsheet();
		$sheet = $spreadsheet->getActiveSheet();

		$style_col = [
			'font' => ['bold' => true],
			'alignment' => [
				'horizontal'  => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER, 
				'vertical' 	  => \PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER
			],
		];

        // Buat header tabel nya pada baris ke 3
		$sheet -> setCellValue ('A1', "Kode Barang"); 
		$sheet -> setCellValue ('B1', "Nama Barang"); 
		$sheet -> setCellValue ('C1', "Stok"); 
		$sheet -> setCellValue ('D1', "ID Satuan");
		$sheet -> setCellValue ('E1', "Harga Jual"); 
		$sheet -> setCellValue ('F1', "Harga Beli"); 
		$sheet -> setCellValue ('G1', "Tgl Kadaluarsa"); 
		$sheet -> setCellValue ('H1', "ID Kategori");
		$sheet -> setCellValue ('I1', "Is Retur");

        // Apply style header yang telah kita buat tadi ke masing-masing kolom header
		$sheet -> getStyle('A1') -> applyFromArray($style_col);
		$sheet -> getStyle('B1') -> applyFromArray($style_col);
		$sheet -> getStyle('C1') -> applyFromArray($style_col);
		$sheet -> getStyle('D1') -> applyFromArray($style_col);
		$sheet -> getStyle('E1') -> applyFromArray($style_col);
		$sheet -> getStyle('F1') -> applyFromArray($style_col);
		$sheet -> getStyle('G1') -> applyFromArray($style_col);
		$sheet -> getStyle('H1') -> applyFromArray($style_col);
		$sheet -> getStyle('I1') -> applyFromArray($style_col);

		$numrow = 2;
        $data   = $this -> app -> get_barang();
        
        foreach($data as $item) {
            $sheet -> setCellValue('A' . $numrow, $item->kode);
			$sheet -> setCellValue('B' . $numrow, $item->nama);
			$sheet -> setCellValue('C' . $numrow, $item->stok);
			$sheet -> setCellValue('D' . $numrow, $item->id_satuan);
			$sheet -> setCellValue('E' . $numrow, $item->harga_jual);
			$sheet -> setCellValue('F' . $numrow, $item->harga_beli);
			$sheet -> setCellValue('G' . $numrow, $item->tgl_kadaluarsa);
			$sheet -> setCellValue('H' . $numrow, $item->id_kategori);
			$sheet -> setCellValue('I' . $numrow, $item->is_retur);

			$numrow++; // Tambah 1 setiap kali looping
        }

        // Set width kolom
		$sheet -> getColumnDimension('A') -> setWidth(20); // Set width kolom A
		$sheet -> getColumnDimension('B') -> setWidth(40); // Set width kolom B
		$sheet -> getColumnDimension('C') -> setWidth(10); // Set width kolom C
		$sheet -> getColumnDimension('D') -> setWidth(10); // Set width kolom D
		$sheet -> getColumnDimension('E') -> setWidth(18); // Set width kolom E
		$sheet -> getColumnDimension('F') -> setWidth(18); // Set width kolom F
		$sheet -> getColumnDimension('G') -> setWidth(18); // Set width kolom G
		$sheet -> getColumnDimension('H') -> setWidth(15); // Set width kolom H
		$sheet -> getColumnDimension('I') -> setWidth(15); // Set width kolom H

        // Set orientasi kertas jadi LANDSCAPE
		$sheet -> getPageSetup() -> setOrientation(\PhpOffice\PhpSpreadsheet\Worksheet\PageSetup::ORIENTATION_LANDSCAPE);

		// Set judul file excel nya
		$sheet -> setTitle("Data Barang ");

		// Proses file excel
		header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
		header('Content-Disposition: attachment; filename="Data_Barang_'.date('Y-m-d::H:i:s').'.xlsx"'); 
		header('Cache-Control: max-age=0');

		$writer = new PhpOffice\PhpSpreadsheet\Writer\Xlsx($spreadsheet);
		$writer -> save('php://output');
    }

	public function import() {
		$file = $_FILES['file'];
		$name = $file['name'];
		$tmp  = $file['tmp_name'];
		$toko = $this -> session -> mart_toko;

		if(empty($name)) {
			echo json_encode(['success' => 0, 'msg' => 'Belum ada file yang diupload']);
			exit;
		}

		$reader 	 = new \PhpOffice\PhpSpreadsheet\Reader\Xlsx();
		$spreadsheet = $reader-> load ($tmp);
		$sheetData 	 = $spreadsheet -> getActiveSheet() -> toArray();

		$jmlAdd  = 0;
		$jmlUp 	 = 0;

		for ($i = 1; $i < count($sheetData) ; $i++) { 
			$kode_brg   = $sheetData[$i][0];
			$cek_brg    = $this -> db -> get_where ('barang', ['kode' => $kode_brg, 'id_toko' => $toko]) -> row();
			
			$sheetData[$i][6] = $sheetData[$i][6] ? date('Y-m-d', strtotime($sheetData[$i][6])) : null;

			if(empty($sheetData[$i][3]) || $sheetData[$i][3] == 0) {
				echo json_encode(['success' => 0, 'msg' => 'Import gagal! Pastikan id satuan tidak kosong']);
				exit;
			}

			if ($cek_brg) {
				$update  = [
					'nama' 		    	=> $sheetData[$i][1],
					'harga_beli' 		=> $sheetData[$i][5],
					'tgl_kadaluarsa' 	=> $sheetData[$i][6],
					'id_kategori' 		=> $sheetData[$i][7],
					'is_retur' 			=> $sheetData[$i][8],
				];

				$this -> db -> update ('barang', $update, ['kode' => $kode_brg, 'id_toko' => $toko]);
				$jmlUp++;

			} else {

				$import= [
					'kode' 				=> $sheetData[$i][0] ? $sheetData[$i][0] : rand(1111, 9999).date('dmy'),
					'nama' 				=> $sheetData[$i][1],
					'harga_beli' 		=> $sheetData[$i][5],
					'tgl_kadaluarsa' 	=> $sheetData[$i][6],
					'id_kategori' 		=> $sheetData[$i][7],
					'is_retur' 			=> $sheetData[$i][8],
					'id_toko'	 		=> $toko,
				];

				$this -> db -> insert('barang', $import);

				$idbrg  = $this -> db -> insert_id();
				$satuan = [
					'id_brg'	 => $idbrg,
					'stok' 		 => $sheetData[$i][2],
					'id_satuan'  => $sheetData[$i][3],
					'harga_jual' => $sheetData[$i][4],
					'id_toko'	 => $toko,
					'jenis' 	 => 1,
				];

				$riwayat = [
					'id_brg'	 => $idbrg,
					'jml' 		 => $sheetData[$i][2],
					'id_satuan'  => $sheetData[$i][3],
					'stok_awal'  => 0,
					'stok_akhir' => $sheetData[$i][2],
					'id_toko'	 => $toko,
					'id_admin'	 => $this -> session -> mart_id,
					'tgl'	 	 => date('Y-m-d H:i:s'),
					'status' 	 => 0,
				];

				$this -> db -> insert('barang_satuan', $satuan);
				$this -> db -> insert('riwayat_stok', $riwayat);
				$jmlAdd++;
			}
		}

		unlink($tmp);
		echo json_encode(['success' => 1, 'msg' => 'Import barang berhasil']);
	}

}

// Controller Excel
// Kode ini dibuat dan dikembangkan oleh codelingker