protected static function _adjustmentReduction_Stok($tanggal1, $tanggal2)
{
return \App\AdjustmentDetail::join('produks', 'adjustment_details.relation_id', '=', 'produks.id')->join('adjustments', 'adjustment_details.adjustment_id', '=', 'adjustments.id')->whereBetween('adjustments.tanggal', [$tanggal1, $tanggal2])->where('type', 'produk')->where('state', 'reduction')->groupBy('produks.id')->select(['produks.id', 'produks.nama', DB::raw('SUM(adjustment_details.qty)qty')])->get()->toArray();
}
protected function _adjustmentPertahun(Request $request)
{
$tahun = $request->get('tahun') ? $request->get('tahun') : date('Y');
// Produk
# Increase
$produk_increase = \App\AdjustmentDetail::join('adjustments', 'adjustment_details.adjustment_id', '=', 'adjustments.id')->join('produks', 'adjustment_details.relation_id', '=', 'produks.id')->where(DB::raw('SUBSTRING(adjustments.tanggal, 1, 4)'), $tahun)->where('adjustment_details.type', 'produk')->where('adjustment_details.state', 'increase')->groupBy('produks.id')->select(['produks.nama', 'adjustment_details.type', DB::raw('(SUM(adjustment_details.harga*adjustment_details.qty)/SUM(adjustment_details.qty))harga_avg'), DB::raw('SUM(adjustment_details.qty)qty_stok'), DB::raw('(SUM(adjustment_details.harga*adjustment_details.qty))subtotal'), 'produks.satuan'])->get();
# Reduction
$produk_reduction = \App\AdjustmentDetail::join('adjustments', 'adjustment_details.adjustment_id', '=', 'adjustments.id')->join('produks', 'adjustment_details.relation_id', '=', 'produks.id')->where(DB::raw('SUBSTRING(adjustments.tanggal, 1, 4)'), $tahun)->where('adjustment_details.type', 'produk')->where('adjustment_details.state', 'reduction')->groupBy('produks.id')->select(['produks.nama', 'adjustment_details.type', DB::raw('(SUM(adjustment_details.harga*adjustment_details.qty)/SUM(adjustment_details.qty))harga_avg'), DB::raw('SUM(adjustment_details.qty)qty_stok'), DB::raw('(SUM(adjustment_details.harga*adjustment_details.qty))subtotal'), 'produks.satuan'])->get();
// Bahan
# Increase
$bahan_increase = \App\AdjustmentDetail::join('adjustments', 'adjustment_details.adjustment_id', '=', 'adjustments.id')->join('bahans', 'adjustment_details.relation_id', '=', 'bahans.id')->where(DB::raw('SUBSTRING(adjustments.tanggal, 1, 4)'), $tahun)->where('adjustment_details.type', 'bahan')->where('adjustment_details.state', 'increase')->groupBy('bahans.id')->select(['bahans.nama', 'adjustment_details.type', DB::raw('(SUM(adjustment_details.harga*adjustment_details.qty)/SUM(adjustment_details.qty))harga_avg'), DB::raw('SUM(adjustment_details.qty)qty_stok'), DB::raw('(SUM(adjustment_details.harga*adjustment_details.qty))subtotal'), 'bahans.satuan'])->get();
# Reduction
$bahan_reduction = \App\AdjustmentDetail::join('adjustments', 'adjustment_details.adjustment_id', '=', 'adjustments.id')->join('bahans', 'adjustment_details.relation_id', '=', 'bahans.id')->where(DB::raw('SUBSTRING(adjustments.tanggal, 1, 4)'), $tahun)->where('adjustment_details.type', 'bahan')->where('adjustment_details.state', 'reduction')->groupBy('bahans.id')->select(['bahans.nama', 'adjustment_details.type', DB::raw('(SUM(adjustment_details.harga*adjustment_details.qty)/SUM(adjustment_details.qty))harga_avg'), DB::raw('SUM(adjustment_details.qty)qty_stok'), DB::raw('(SUM(adjustment_details.harga*adjustment_details.qty))subtotal'), 'bahans.satuan'])->get();
return ['tanggal' => Carbon::createFromFormat('Y', $tahun), 'adjustments' => ['increase' => array_merge($produk_increase->toArray(), $bahan_increase->toArray()), 'reduction' => array_merge($produk_reduction->toArray(), $bahan_reduction->toArray())]];
}
