250 lines
9.6 KiB
PHP
250 lines
9.6 KiB
PHP
<?php
|
|
|
|
namespace App\Http\Controllers;
|
|
|
|
use Illuminate\Http\Request;
|
|
use Illuminate\Support\Facades\DB;
|
|
use Carbon\Carbon;
|
|
use Barryvdh\DomPDF\Facade\Pdf;
|
|
|
|
class ProcurementController extends Controller
|
|
{
|
|
|
|
public function setPeriode(Request $request)
|
|
{
|
|
$periode = $request->input('periode');
|
|
session(['periode_pengadaan' => $periode]);
|
|
return redirect('/rencana-pengadaan');
|
|
}
|
|
public function index(Request $request)
|
|
{
|
|
$periodes = DB::table('prediction_results')
|
|
->selectRaw("CONCAT(tahun_prediksi,'-',LPAD(bulan_prediksi,2,'0')) as periode")
|
|
->distinct()
|
|
->orderBy('periode', 'desc')
|
|
->get();
|
|
$periodeList = $periodes->pluck('periode')->toArray();
|
|
$periodeStep = 1;
|
|
if (count($periodeList) >= 2) {
|
|
$first = Carbon::createFromFormat('Y-m', $periodeList[count($periodeList) - 1]);
|
|
$second = Carbon::createFromFormat('Y-m', $periodeList[count($periodeList) - 2]);
|
|
$periodeStep = abs($first->diffInMonths($second));
|
|
}
|
|
$minPeriode = $periodes->last()->periode ?? null;
|
|
$maxPeriode = $periodes->first()->periode ?? null;
|
|
$periode = session('periode_pengadaan', $maxPeriode);
|
|
$periodeDefault = $periode;
|
|
[$tahun, $bulan] = explode('-', $periode);
|
|
$tahun = (int) $tahun;
|
|
$bulan = (int) $bulan;
|
|
$tanggalAkhir = Carbon::create($tahun, $bulan, 1)->endOfMonth()->toDateString();
|
|
$stokSubquery = DB::table('stock_transaction_details as std')
|
|
->join('stock_transactions as st', 'std.no_transaksi', '=', 'st.no_transaksi')
|
|
->select(
|
|
'std.kode',
|
|
DB::raw('SUM(std.qty_masuk - std.qty_keluar) as stok')
|
|
)
|
|
->whereDate('st.tanggal', '<=', $tanggalAkhir)
|
|
->groupBy('std.kode');
|
|
$data = DB::table('prediction_results as pr')
|
|
->join('items as i', 'pr.kode', '=', 'i.kode')
|
|
->leftJoinSub($stokSubquery, 'stok_data', function ($join) {
|
|
$join->on('pr.kode', '=', 'stok_data.kode');
|
|
})
|
|
->where('pr.bulan_prediksi', $bulan)
|
|
->where('pr.tahun_prediksi', $tahun)
|
|
->where('i.kategori', 0)
|
|
->select(
|
|
'pr.kode',
|
|
'i.nama_barang',
|
|
'i.satuan',
|
|
'pr.jumlah_prediksi',
|
|
DB::raw('COALESCE(stok_data.stok, 0) as stok')
|
|
)
|
|
->get();
|
|
$rows = $data->map(function ($item) {
|
|
$stokReal = (int) $item->stok;
|
|
$stok = max(0, $stokReal);
|
|
$selisih = $stok - $item->jumlah_prediksi;
|
|
$usulan = $selisih < 0 ? abs($selisih) : 0;
|
|
return [
|
|
'kode' => $item->kode,
|
|
'nama_barang' => $item->nama_barang,
|
|
'satuan' => $item->satuan,
|
|
'prediksi' => $item->jumlah_prediksi,
|
|
'stok' => $stok,
|
|
'stok_real' => $stokReal,
|
|
'selisih' => $selisih,
|
|
'usulan' => $usulan,
|
|
'status' => $usulan > 0 ? 'Perlu Restock' : 'Aman',
|
|
];
|
|
});
|
|
$totalBarang = $rows->count();
|
|
$perluRestock = $rows->where('status', 'Perlu Restock')->count();
|
|
$aman = $rows->where('status', 'Aman')->count();
|
|
$totalUsulan = $rows->sum('usulan');
|
|
return view('rencana-pengadaan', compact(
|
|
'rows',
|
|
'minPeriode',
|
|
'maxPeriode',
|
|
'periodeDefault',
|
|
'periodeList',
|
|
'periodeStep',
|
|
'totalBarang',
|
|
'perluRestock',
|
|
'aman',
|
|
'totalUsulan'
|
|
));
|
|
}
|
|
|
|
public function cetak(Request $request)
|
|
{
|
|
$periode = session('periode_pengadaan');
|
|
[$tahun, $bulan] = explode('-', $periode);
|
|
$tahun = (int) $tahun;
|
|
$bulan = (int) $bulan;
|
|
$tanggalAkhir = Carbon::create($tahun, $bulan, 1)->endOfMonth()->toDateString();
|
|
$stokSubquery = DB::table('stock_transaction_details as std')
|
|
->join('stock_transactions as st', 'std.no_transaksi', '=', 'st.no_transaksi')
|
|
->select(
|
|
'std.kode',
|
|
DB::raw('SUM(std.qty_masuk - std.qty_keluar) as stok')
|
|
)
|
|
->whereDate('st.tanggal', '<=', $tanggalAkhir)
|
|
->groupBy('std.kode');
|
|
$data = DB::table('prediction_results as pr')
|
|
->join('items as i', 'pr.kode', '=', 'i.kode')
|
|
->leftJoinSub($stokSubquery, 'stok_data', function ($join) {
|
|
$join->on('pr.kode', '=', 'stok_data.kode');
|
|
})
|
|
->where('pr.bulan_prediksi', $bulan)
|
|
->where('pr.tahun_prediksi', $tahun)
|
|
->where('i.kategori', 0)
|
|
->select(
|
|
'pr.kode',
|
|
'i.nama_barang',
|
|
'i.satuan',
|
|
'pr.jumlah_prediksi',
|
|
DB::raw('COALESCE(stok_data.stok, 0) as stok')
|
|
)
|
|
->get();
|
|
$rows = $data->map(function ($item) {
|
|
$stok = max(0, (int) $item->stok);
|
|
$selisih = $stok - $item->jumlah_prediksi;
|
|
$usulan = $selisih < 0 ? abs($selisih) : 0;
|
|
return [
|
|
'kode' => $item->kode,
|
|
'nama_barang' => $item->nama_barang,
|
|
'satuan' => $item->satuan,
|
|
'prediksi' => $item->jumlah_prediksi,
|
|
'stok' => $stok,
|
|
'selisih' => $selisih,
|
|
'usulan' => $usulan,
|
|
'status' => $usulan > 0 ? 'Perlu Restock' : 'Aman',
|
|
];
|
|
});
|
|
$totalBarang = $rows->count();
|
|
$perluRestock = $rows->where('status', 'Perlu Restock')->count();
|
|
$totalUsulan = $rows->sum('usulan');
|
|
$pdf = Pdf::loadView('rencana-pengadaan-pdf', compact(
|
|
'rows',
|
|
'periode',
|
|
'totalBarang',
|
|
'perluRestock',
|
|
'totalUsulan'
|
|
))
|
|
->setPaper('a4', 'portrait');
|
|
return $pdf->download('rencana-pengadaan.pdf');
|
|
}
|
|
|
|
public function detail($kode, $periode)
|
|
{
|
|
[$tahun, $bulan] = explode('-', $periode);
|
|
$tahun = (int) $tahun;
|
|
$bulan = (int) $bulan;
|
|
$tanggalAkhir = Carbon::create($tahun, $bulan, 1)->endOfMonth()->toDateString();
|
|
$stok = DB::table('stock_transaction_details as std')
|
|
->join('stock_transactions as st', 'std.no_transaksi', '=', 'st.no_transaksi')
|
|
->where('std.kode', $kode)
|
|
->whereDate('st.tanggal', '<=', $tanggalAkhir)
|
|
->selectRaw('SUM(std.qty_masuk - std.qty_keluar) as stok')
|
|
->value('stok');
|
|
$stok = max(0, (int) $stok);
|
|
$barang = DB::table('items')
|
|
->where('kode', $kode)
|
|
->first();
|
|
$prediksi = DB::table('prediction_results')
|
|
->where('kode', $kode)
|
|
->where('bulan_prediksi', $bulan)
|
|
->where('tahun_prediksi', $tahun)
|
|
->first();
|
|
if (!$barang || !$prediksi) {
|
|
abort(404);
|
|
}
|
|
$selisih = $stok - $prediksi->jumlah_prediksi;
|
|
$usulan = $selisih < 0 ? abs($selisih) : 0;
|
|
$hasHistoricalPrediction = DB::table('item_usage_monthly as um')
|
|
->leftJoin('prediction_results as pr', function ($join) {
|
|
$join->on('um.kode', '=', 'pr.kode')
|
|
->on('um.tahun', '=', 'pr.tahun_prediksi')
|
|
->on('um.bulan', '=', 'pr.bulan_prediksi');
|
|
})
|
|
->where('um.kode', $kode)
|
|
->whereNotNull('pr.jumlah_prediksi')
|
|
->exists();
|
|
if (!$hasHistoricalPrediction) {
|
|
$riwayat = DB::table('item_usage_monthly as um')
|
|
->where('um.kode', $kode)
|
|
->select(
|
|
'um.tahun',
|
|
'um.bulan',
|
|
'um.total_pemakaian',
|
|
DB::raw('NULL as hasil_prediksi')
|
|
)
|
|
->orderBy('um.tahun', 'desc')
|
|
->orderBy('um.bulan', 'desc')
|
|
->limit(12)
|
|
->get()
|
|
->reverse()
|
|
->map(function ($item) {
|
|
$item->selisih_prediksi = null;
|
|
return $item;
|
|
})
|
|
->values();
|
|
} else {
|
|
$riwayat = DB::table('item_usage_monthly as um')
|
|
->leftJoin('prediction_results as pr', function ($join) {
|
|
$join->on('um.kode', '=', 'pr.kode')
|
|
->on(DB::raw('CAST(um.tahun AS UNSIGNED)'), '=', DB::raw('CAST(pr.tahun_prediksi AS UNSIGNED)'))
|
|
->on(DB::raw('CAST(um.bulan AS UNSIGNED)'), '=', DB::raw('CAST(pr.bulan_prediksi AS UNSIGNED)'));
|
|
})
|
|
->where('um.kode', $kode)
|
|
->select(
|
|
'um.tahun',
|
|
'um.bulan',
|
|
'um.total_pemakaian',
|
|
DB::raw('COALESCE(pr.jumlah_prediksi, 0) as hasil_prediksi')
|
|
)
|
|
->orderBy('um.tahun', 'desc')
|
|
->orderBy('um.bulan', 'desc')
|
|
->limit(12)
|
|
->get()
|
|
->reverse()
|
|
->map(function ($item) {
|
|
$item->selisih_prediksi = $item->hasil_prediksi - $item->total_pemakaian;
|
|
return $item;
|
|
})
|
|
->values();
|
|
}
|
|
return view('detail-rencana-pengadaan', compact(
|
|
'barang',
|
|
'prediksi',
|
|
'stok',
|
|
'selisih',
|
|
'usulan',
|
|
'periode',
|
|
'riwayat',
|
|
'hasHistoricalPrediction'
|
|
));
|
|
}
|
|
} |