public function ajaxLoad(Request $request)
{
if ($request->get('id')) {
$produk = Produk::with(['detail' => function ($query) {
$query->join('bahans', 'produk_details.bahan_id', '=', 'bahans.id');
}])->where('active', 1)->where('id', $request->get('id'))->first();
return ['id' => $produk->id, 'nama' => $produk->nama, 'satuan' => $produk->satuan, 'harga' => CountPrice($produk)];
} else {
$produk = Produk::leftJoin('produk_details', 'produks.id', '=', 'produk_details.produk_id')->where('nama', 'like', '%' . $request->get('q') . '%')->where('produks.active', 1)->whereNotIn('produks.id', explode('+', $request->get('except')))->select('produks.*')->groupBy('produks.id')->limit($request->get('page'));
$produk = $request->get('without_has_bahan') == 'Ya' ? $produk->whereNull('produk_details.id') : $produk;
$produk = $produk->get();
return $produk;
}
}
protected function _soldItemPertahun(Request $request)
{
$tahun = $request->get('tahun') ? $request->get('tahun') : date('Y');
$produk = Produk::leftJoin(DB::raw("(SELECT order_details.id, order_details.`order_id`, order_details.`produk_id`,\n order_details.`hpp`,\n order_details.`harga_jual`, order_details.`qty` AS qty_ori, IFNULL(order_detail_returns.`qty`, 0)qty_return,\n (order_details.`qty` - IFNULL(order_detail_returns.`qty`, 0))qty,\n order_details.`use_mark_up`, order_details.`mark_up`\n FROM order_details\n LEFT JOIN order_detail_returns ON order_details.`id` = order_detail_returns.`order_detail_id`\n INNER JOIN orders ON order_details.`order_id` = orders.`id`\n WHERE SUBSTRING(orders.`tanggal`, 1, 4) = '{$tahun}'\n AND orders.`state` = 'Closed'\n GROUP BY order_details.`id`)temp_order_produks"), function ($join) {
$join->on('produks.id', '=', 'temp_order_produks.produk_id');
})->where('produks.active', 1)->groupBy('produks.id')->select(['produks.id', 'produks.nama', DB::raw('ifnull((SUM(temp_order_produks.hpp)/COUNT(produks.id)), 0)hpp'), DB::raw('ifnull(ROUND(SUM(temp_order_produks.harga_jual)/COUNT(produks.id)), 0)harga_jual'), DB::raw('ifnull(SUM(temp_order_produks.qty), 0)terjual'), DB::raw('ifnull(SUM(temp_order_produks.hpp * temp_order_produks.qty), 0)AS total_hpp'), DB::raw('ifnull(SUM(temp_order_produks.harga_jual * temp_order_produks.qty), 0)AS subtotal'), DB::raw('ifnull((SUM(temp_order_produks.harga_jual * temp_order_produks.qty) - SUM(temp_order_produks.hpp * temp_order_produks.qty)), 0)AS laba'), DB::raw('ifnull(ROUND(((SUM(temp_order_produks.harga_jual * temp_order_produks.qty) - SUM(temp_order_produks.hpp * temp_order_produks.qty)) /
SUM(temp_order_produks.hpp * temp_order_produks.qty))*100), 0)laba_procentage')])->get();
return ['tanggal' => Carbon::createFromFormat('Y', $tahun), 'produks' => $produk];
}