Cacher is the code snippet organizer for pro developers

<?php

namespace App\Models\Aset;

use App\Traits\Aset\HasAsetStatuses;
use App\Traits\Filterable;
use Illuminate\Database\Eloquent\SoftDeletes;
use Illuminate\Support\Facades\DB;

class AssetDepreciation extends AsetBaseModel
{
    use SoftDeletes;
    use Filterable;
    use HasAsetStatuses;

    protected $fillable = [
        'asset_id',
        'year',
        'cost',
        'depreciation_rate',
        'depreciated_amount',
        'accumulated_depreciated_amount',
        'item_book_value',
        'user_id',
    ];

    public function asset()
    {
        return $this->belongsTo(Asset::class, 'asset_id', 'id');
    }

    /**
     * Get summary of year total book value (report)
     * @param $year
     * @param null $location_id
     * @param null $status
     * @return \Illuminate\Support\Collection
     */
    public function getAssetCurrentValueSummary($year, $location_id = null, $status = null)
    {
        $asset_depreciations_table = AssetDepreciation::name();
        $assets_table              = Asset::name();
        $assets_model_type         = Asset::class;
        $subcategories_table       = Subcategory::name();
        $categories_table          = \App\Models\Shared\Category::name();
        $model_status_table        = $this->getStatusTableName();

        // replace slash with double backlash to escape sql
        // if using Laravel, we do not need to escpape the usage of $assets_model_type
        // however when use raw sql, we need to escape also the usage of $assets_model_type

        $escaped_assets_model_type = str_replace("\\", "\\\\", $assets_model_type);

        if (empty($status)) {

            $status = [
                AssetStatus::ACTIVE,
                AssetStatus::DISPOSE_CANDIDATE,
            ];
        }

        $total_asset      = DB::raw('COUNT(*) as `total_asset`');
        $total_book_value = DB::raw("SUM(`$asset_depreciations_table`.`item_book_value`) AS `total_book_value`");

        $result = DB::table($asset_depreciations_table)

            ->join($assets_table, function ($join) use ($asset_depreciations_table, $assets_table, $model_status_table, $assets_model_type, $escaped_assets_model_type, $status)
            {
                $join->on("$asset_depreciations_table.asset_id", '=', "$assets_table.id");

                $join->whereExists(function ($query) use ($model_status_table, $assets_table, $assets_model_type, $escaped_assets_model_type, $status) {
                    $query
                        ->select('*')
                        ->from($model_status_table)
                        ->whereRaw("`$assets_table`.`id` = `$model_status_table`.`model_id`")
                        ->where("$model_status_table.model_type", $assets_model_type)
                        ->whereIn("$model_status_table.name", $status)
                        ->whereRaw("`id` in (select max(id) from `$model_status_table` where `model_type` = '$escaped_assets_model_type' group by `model_id`)")
                        ->whereRaw("`$model_status_table`.`deleted_at` is null");
                });

                $join->whereRaw("`$assets_table`.`deleted_at` is null");
            })

            ->leftJoin($subcategories_table, "$assets_table.subcategory_id", '=', "$subcategories_table.id")

            ->leftJoin($categories_table, "$subcategories_table.category_id", '=', "$categories_table.id")

            ->select("$assets_table.subcategory_id", "$categories_table.id as category_id",
                "$categories_table.title as category_title", $total_asset, $total_book_value)

            ->where('year', $year);

        if (!empty($location_id)) {
            $result = $result->where("$assets_table.location_id", $location_id);
        }

        $result = $result
            ->groupBy("$assets_table.subcategory_id")
            ->get();

        return $result;
    }
}

select
  `easet_assets`.`subcategory_id`,
  `ebelanjawan_categories`.`id` as `category_id`,
  `ebelanjawan_categories`.`title` as `category_title`,
  COUNT(*) as `total_asset`,
  SUM(`easet_asset_depreciations`.`item_book_value`) AS `total_book_value`
from
  `easet_asset_depreciations`
  inner join `easet_assets` on `easet_asset_depreciations`.`asset_id` = `easet_assets`.`id`
  and exists (
    select
      *
    from
      `easet_statuses`
    where
      `easet_assets`.`id` = `easet_statuses`.`model_id`
      and `easet_statuses`.`model_type` = "App\Models\Aset\Asset"
      and `easet_statuses`.`name` in ("ACTIVE", "DISPOSE_CANDIDATE")
      and `id` in (
        select
          max(id)
        from
          `easet_statuses`
        where
          `model_type` = 'App\\Models\\Aset\\Asset'
        group by
          `model_id`
      )
      and `easet_statuses`.`deleted_at` is null
  )
  and `easet_assets`.`deleted_at` is null
  left join `easet_subcategories` on `easet_assets`.`subcategory_id` = `easet_subcategories`.`id`
  left join `ebelanjawan_categories` on `easet_subcategories`.`category_id` = `ebelanjawan_categories`.`id`
where
  `year` = "2019"
group by
  `easet_assets`.`subcategory_id`