private function excel($params)
{
try {
$condition = array();
$data = array();
$dataExcel = array();
if (!empty($params['a']) && !empty($params['z']) && !empty($params['cdate'])) {
$col_date = $params['cdate'];
$query = sprintf("%s BETWEEN '%s' and '%s' ", $params['cdate'], $params['a'], $params['z']);
$condition['conditions'] = $query;
}
$ar_adapter = \ActiveRecord\ConnectionManager::get_connection($this->app->mode);
$connection = $ar_adapter->connection;
$stmt = $connection->prepare("select (stocks.get_stock_harian(:awal, :akhir)).*");
$stmt->bindParam(':awal', $params['a']);
$stmt->bindParam(':akhir', $params['z']);
$stmt->execute();
$stocks = $stmt->fetchAll(PDO::FETCH_CLASS);
$dataExcel["B7:C7"] = array('align' => '', 'value' => sprintf("%s sampai dengan %s", $params['a'], $params['z']), 'border' => '');
$line = 10;
$i = 1;
$total = 0;
foreach ($stocks as $row) {
$awal = $row->keluar + $row->balance - $row->masuk;
$awal = $awal < 0 ? 0 : $awal;
$dataExcel["A" . $line] = array('align' => '', 'value' => $i, 'border' => 'all');
$dataExcel["B" . $line] = array('align' => 'center', 'value' => $row->item_kode, 'border' => 'all');
$dataExcel["C" . $line] = array('align' => 'center', 'value' => $row->item_nama, 'border' => 'all');
$dataExcel["D" . $line] = array('align' => 'right', 'value' => $awal, 'border' => 'all', 'num_format' => true);
$dataExcel["E" . $line] = array('align' => 'right', 'value' => $row->masuk, 'border' => 'all', 'num_format' => true);
$dataExcel["F" . $line] = array('align' => 'right', 'value' => $row->keluar, 'border' => 'all', 'num_format' => true);
$dataExcel["G" . $line] = array('align' => 'right', 'value' => $row->balance, 'border' => 'all', 'num_format' => true);
$dataExcel["H" . $line] = array('align' => 'center', 'value' => $row->warehouse_nama, 'border' => 'all');
$line++;
$i++;
$total += 1;
}
$dataExcel["A" . $line . ":c" . $line] = array('align' => 'right', 'value' => 'Total', 'border' => 'all');
$dataExcel["D" . $line] = array('align' => 'right', 'value' => "=SUM(D10:D" . ($line - 1) . ")", 'border' => 'all', 'num_format' => true);
$dataExcel["E" . $line] = array('align' => 'right', 'value' => "=SUM(E10:E" . ($line - 1) . ")", 'border' => 'all', 'num_format' => true);
$dataExcel["F" . $line] = array('align' => 'right', 'value' => "=SUM(F10:F" . ($line - 1) . ")", 'border' => 'all', 'num_format' => true);
$dataExcel["G" . $line] = array('align' => 'right', 'value' => "=SUM(G10:G" . ($line - 1) . ")", 'border' => 'all', 'num_format' => true);
$dataExcel["I" . $line . ":L" . $line] = array('align' => '', 'value' => '', 'border' => 'all');
$fileDetail = ZiUtil::defaultFileDetail();
$fileDetail->template = 'assets/temp/lap_stock.xlsx';
$fileDetail->title = "Laporan Stock";
$fileDetail->subject = "Laporan Stock";
$fileDetail->desc = "Laporan Stock RSMM";
$fileDetail->sheetTitle = "Stock ";
$fileDetail->filename = "LSH-" . sprintf("%s-sd-%s", $params['a'], $params['z']) . ".xlsx";
$filename = "LSH-" . sprintf("%s-sd-%s", $params['a'], $params['z']) . ".xlsx";
ZiUtil::generate($filename, $dataExcel, $fileDetail);
} catch (Exception $e) {
App::flash('error', $e);
}
}
private function excel($params)
{
try {
$condition = array();
$data = array();
$dataExcel = array();
if (!empty($params['a']) && !empty($params['z']) && !empty($params['cdate'])) {
$col_date = $params['cdate'];
$query = sprintf("%s BETWEEN '%s' and '%s' ", $params['cdate'], $params['a'], $params['z']);
$condition['conditions'] = $query;
}
$sales = SalesApotik::all($condition);
$dataExcel["B7:C7"] = array('align' => '', 'value' => sprintf("%s sampai dengan %s", $params['a'], $params['z']), 'border' => '');
$line = 10;
$i = 1;
$total = 0;
foreach ($sales as $row) {
$dataExcel["A" . $line] = array('align' => '', 'value' => $i, 'border' => 'all');
$dataExcel["B" . $line] = array('align' => 'center', 'value' => $row->posting_date, 'border' => 'all');
$dataExcel["C" . $line] = array('align' => 'center', 'value' => $row->id, 'border' => 'all');
$dataExcel["D" . $line] = array('align' => 'center', 'value' => $row->pasien_reg_no, 'border' => 'all');
$dataExcel["E" . $line] = array('align' => 'center', 'value' => $row->pasien_nama, 'border' => 'all');
$dataExcel["F" . $line] = array('align' => 'center', 'value' => $row->price_list, 'border' => 'all');
$dataExcel["G" . $line] = array('align' => 'center', 'value' => $row->kasir, 'border' => 'all');
$dataExcel["H" . $line] = array('align' => 'right', 'value' => $row->amount, 'border' => 'all', 'cur_format' => true);
$line++;
$i++;
$total += $row->amount;
}
$dataExcel["A" . $line . ":G" . $line] = array('align' => 'right', 'value' => 'Total', 'border' => 'all');
$dataExcel["H" . $line] = array('align' => 'right', 'value' => $total, 'border' => 'all', 'cur_format' => true);
$dataExcel["I" . $line . ":L" . $line] = array('align' => '', 'value' => '', 'border' => 'all');
$fileDetail = ZiUtil::defaultFileDetail();
$fileDetail->template = 'assets/temp/lap_invoice.xlsx';
$fileDetail->title = "Laporan Order";
$fileDetail->subject = "Laporan Order";
$fileDetail->desc = "Laporan Order RSMM";
$fileDetail->sheetTitle = "Order ";
$fileDetail->filename = "LPH-" . sprintf("%s-sd-%s", $params['a'], $params['z']) . ".xlsx";
$filename = "LPH-" . sprintf("%s-sd-%s", $params['a'], $params['z']) . ".xlsx";
ZiUtil::generate($filename, $dataExcel, $fileDetail);
} catch (Exception $e) {
App::flash('error', $e);
}
}
