public function rekap($id)
{
$pesan = DetilSPPB::join('barang', 'barang.kode_barang', '=', 'detil_SPPB.kode_barang')->join('SPPB', 'detil_SPPB.no_SPPB', '=', 'SPPB.no_SPPB')->join('PO', 'SPPB.no_SPPB', '=', 'PO.no_SPPB')->join('invoice', 'PO.no_PO', '=', 'invoice.no_PO')->selectRaw('barang.kode_barang,sum(jml_pesan) AS pesan, nm_barang, satuan, jml_barang, tgl_SPPB, brand')->groupBy('kode_barang');
$beli = DetilSTTB::join('barang', 'barang.kode_barang', '=', 'detil_STTB.kode_barang')->join('STTB', 'detil_STTB.no_STTB', '=', 'STTB.no_STTB')->selectRaw('barang.kode_barang, sum(jml_beli) AS beli, nm_barang, satuan, jml_barang, tgl_STTB, brand')->groupBy('kode_barang');
$first = DB::table(DB::raw("({$pesan->toSql()}) as sub1"))->join(DB::raw("({$beli->toSql()}) as sub2"), 'sub2.kode_barang', '=', 'sub1.kode_barang', 'left')->where(DB::raw("YEAR(sub1.tgl_SPPB)"), $id)->selectRaw('sub1.kode_barang, sub1.pesan, sub2.beli, sub1.nm_barang, sub1.satuan, sub1.jml_barang, sub1.brand');
$data = array('id' => $id, 'data' => DB::table(DB::raw("({$pesan->toSql()}) as sub1"))->join(DB::raw("({$beli->toSql()}) as sub2"), 'sub2.kode_barang', '=', 'sub1.kode_barang', 'right')->where(DB::raw("YEAR(sub2.tgl_STTB)"), $id)->selectRaw('sub2.kode_barang, sub1.pesan, sub2.beli, sub2.nm_barang, sub2.satuan, sub2.jml_barang, sub2.brand')->union($first)->get());
$pdf = PDF::loadView('reports.rekap', $data)->setPaper('a4', 'landscape')->save('myfile.pdf');
return $pdf->stream();
}