A free open source IT asset/license management system
<?php
namespace App\Models\Builders;
use App\Models\Asset;
use App\Models\Company;
use App\Models\Location;
use App\Models\Setting;
use App\Models\Statuslabel;
use App\Models\User;
use Carbon\Carbon;
use Illuminate\Database\Eloquent\Builder;
/**
* @template TModel of Asset
*
* @extends Builder<TModel>
*/
class AssetQueryBuilder extends Builder
{
public function hardware(): self
{
return $this->where('physical', '=', '1');
}
public function pending(): self
{
// Pluck IDs then whereIn. Do NOT replace with whereHas. whereHas
// generates a correlated EXISTS per row and causes severe
// slowdowns in withCount contexts.
$ids = Statuslabel::idsFor('pending');
return $this->whereIn('assets.status_id', $ids->isEmpty() ? [0] : $ids);
}
public function assetsByLocation(Location $location): self
{
return $this->where(function ($query) use ($location) {
$query->whereHas('assignedTo', function ($query) use ($location) {
$query->where([
['users.location_id', '=', $location->id],
['assets.assigned_type', '=', User::class],
])->orWhere([
['locations.id', '=', $location->id],
['assets.assigned_type', '=', Location::class],
])->orWhere([
['assets.rtd_location_id', '=', $location->id],
['assets.assigned_type', '=', Asset::class],
]);
})->orWhere(function ($query) use ($location) {
$query->where('assets.rtd_location_id', '=', $location->id);
$query->whereNull('assets.assigned_to');
});
});
}
public function rtd(): self
{
$ids = Statuslabel::idsFor('deployable');
return $this->whereNull('assets.assigned_to')
->whereIn('assets.status_id', $ids->isEmpty() ? [0] : $ids);
}
public function undeployable(): self
{
$ids = Statuslabel::idsFor('undeployable');
return $this->whereIn('assets.status_id', $ids->isEmpty() ? [0] : $ids);
}
public function notArchived(): self
{
$ids = Statuslabel::idsFor('not_archived');
return $this->whereIn('assets.status_id', $ids->isEmpty() ? [0] : $ids);
}
public function dueForAudit(Setting $settings): self
{
$interval = (int) $settings->audit_warning_days ?? 0;
$today = Carbon::now();
$interval_date = $today->copy()->addDays($interval)->format('Y-m-d');
return $this->whereNotNull('assets.next_audit_date')
->whereBetween('assets.next_audit_date', [$today->format('Y-m-d'), $interval_date])
->where('assets.archived', '=', 0)
->notArchived();
}
public function overdueForAudit(): self
{
return $this->whereNotNull('assets.next_audit_date')
->where('assets.next_audit_date', '<', Carbon::now()->format('Y-m-d'))
->where('assets.archived', '=', 0)
->notArchived();
}
public function pastEol(): self
{
return $this->whereNotNull('assets.asset_eol_date')
->where('assets.asset_eol_date', '<', Carbon::now()->format('Y-m-d'))
->notArchived();
}
public function dueOrOverdueForAudit(Setting $settings): self
{
return $this->where(function ($query) {
$query->overdueForAudit();
})->orWhere(function ($query) use ($settings) {
$query->dueForAudit($settings);
});
}
public function dueForCheckin(Setting $settings): self
{
$interval = (int) $settings->due_checkin_days ?? 0;
// expected_checkin is a DATETIME. Use startOfDay / endOfDay
// explicitly so an asset due back on the last day of the
// interval at (say) 2:30 PM still falls inside the range.
// Formatting only Y-m-d silently widens to midnight and would
// exclude anything later that day.
$windowStart = Carbon::now()->startOfDay();
$windowEnd = Carbon::now()->addDays($interval)->endOfDay();
return $this->whereNotNull('assets.expected_checkin')
->whereBetween('assets.expected_checkin', [$windowStart, $windowEnd])
->where('assets.archived', '=', 0)
->whereNotNull('assets.assigned_to')
->notArchived();
}
public function overdueForCheckin(): self
{
// expected_checkin is a DATETIME, so "overdue" is time-aware: an
// asset due back this morning at 09:00 is overdue by 09:01.
// Compare against Carbon::now() instead of today's midnight so
// mid-day dashboard views reflect what's actually late right
// now.
return $this->whereNotNull('assets.expected_checkin')
->where('assets.expected_checkin', '<', Carbon::now())
->where('assets.archived', '=', 0)
->whereNotNull('assets.assigned_to')
->notArchived();
}
public function dueOrOverdueForCheckin(Setting $settings): self
{
return $this->where(function ($query) {
$query->overdueForCheckin();
})->orWhere(function ($query) use ($settings) {
$query->dueForCheckin($settings);
});
}
public function assetsForShow(): self
{
// Pluck IDs then whereIn. Do NOT replace with whereHas.
if (Setting::getSettings()->show_archived_in_list != 1) {
$validStatusIds = Statuslabel::idsFor('not_archived');
return $this->whereIn('assets.status_id', $validStatusIds->isEmpty() ? [0] : $validStatusIds);
}
return $this;
}
public function archived(): self
{
$ids = Statuslabel::idsFor('archived');
return $this->whereIn('assets.status_id', $ids->isEmpty() ? [0] : $ids);
}
public function deployed(): self
{
return $this->where('assigned_to', '>', '0');
}
public function requestable(): self
{
$table = $this->getModel()->getTable();
return Company::scopeCompanyables($this->where($table.'.requestable', '=', 1))
->whereHas('status', function ($query) {
$query->where(function ($query) {
$query->where('deployable', '=', 1)
->where('archived', '=', 0); // you definitely can't request something that's archived
})->orWhere('pending', '=', 1); // we've decided that even though an asset may be 'pending', you can still request it
});
}
/**
* @param array<int, int> $modelIdListing
*/
public function inModelList(array $modelIdListing): self
{
return $this->whereIn('assets.model_id', $modelIdListing);
}
public function notYetAccepted(): self
{
return $this->where('accepted', '=', 'pending');
}
public function rejected(): self
{
return $this->where('accepted', '=', 'rejected');
}
public function accepted(): self
{
return $this->where('accepted', '=', 'accepted');
}
public function assignedSearch(string $search): self
{
$search = explode(' OR ', $search);
return $this->leftJoin('users as assets_users', function ($leftJoin) {
$leftJoin->on('assets_users.id', '=', 'assets.assigned_to')
->where('assets.assigned_type', '=', User::class);
})->leftJoin('locations as assets_locations', function ($leftJoin) {
$leftJoin->on('assets_locations.id', '=', 'assets.assigned_to')
->where('assets.assigned_type', '=', Location::class);
})->leftJoin('assets as assigned_assets', function ($leftJoin) {
$leftJoin->on('assigned_assets.id', '=', 'assets.assigned_to')
->where('assets.assigned_type', '=', Asset::class);
})->where(function ($query) use ($search) {
foreach ($search as $search) {
$query->whereHas('model', function ($query) use ($search) {
$query->whereHas('category', function ($query) use ($search) {
$query->where(function ($query) use ($search) {
$query->where('categories.name', 'LIKE', '%'.$search.'%')
->orWhere('models.name', 'LIKE', '%'.$search.'%')
->orWhere('models.model_number', 'LIKE', '%'.$search.'%');
});
});
})->orWhereHas('model', function ($query) use ($search) {
$query->whereHas('manufacturer', function ($query) use ($search) {
$query->where(function ($query) use ($search) {
$query->where('manufacturers.name', 'LIKE', '%'.$search.'%');
});
});
})->orWhere(function ($query) use ($search) {
$query->where('assets_users.first_name', 'LIKE', '%'.$search.'%')
->orWhere('assets_users.last_name', 'LIKE', '%'.$search.'%')
->orWhere('assets_users.username', 'LIKE', '%'.$search.'%')
->orWhere('assets_users.jobtitle', 'LIKE', '%'.$search.'%')
->orWhereMultipleColumns(
[
'assets_users.first_name',
'assets_users.last_name',
'assets_users.jobtitle',
], $search
)
->orWhere('assets_locations.name', 'LIKE', '%'.$search.'%')
->orWhere('assigned_assets.name', 'LIKE', '%'.$search.'%');
})->orWhere('assets.name', 'LIKE', '%'.$search.'%')
->orWhere('assets.asset_tag', 'LIKE', '%'.$search.'%')
->orWhere('assets.serial', 'LIKE', '%'.$search.'%')
->orWhere('assets.order_number', 'LIKE', '%'.$search.'%')
->orWhere('assets.notes', 'LIKE', '%'.$search.'%');
}
})->withTrashed()->whereNull('assets.deleted_at'); // workaround for laravel bug
}
/**
* Query builder scope to search the department ID of users
* assigned to assets.
*
* @param array<int, int> $search
*/
public function checkedOutToTargetInDepartment(array $search): self
{
return $this->leftJoin('users as assets_dept_users', function ($leftJoin) {
$leftJoin->on('assets_dept_users.id', '=', 'assets.assigned_to')
->where('assets.assigned_type', '=', User::class);
})->where(function ($query) use ($search) {
$query->whereIn('assets_dept_users.department_id', $search);
})->withTrashed()->whereNull('assets.deleted_at'); // workaround for laravel bug
}
public function orderModels(string $order): self
{
return $this
->join('models as asset_models', 'assets.model_id', '=', 'asset_models.id')
->orderBy('asset_models.name', $order);
}
public function orderModelNumber(string $order): self
{
return $this
->leftJoin('models as model_number_sort', 'assets.model_id', '=', 'model_number_sort.id')
->orderBy('model_number_sort.model_number', $order);
}
public function orderByCreatedByName(string $order): self
{
// addSelect rather than select, so prior withCount subqueries
// (which append their count-expressions to the select list)
// don't get wiped by the join and trigger an N+1 on the
// reload.
return $this
->leftJoin('users as admin_sort', 'assets.created_by', '=', 'admin_sort.id')
->addSelect('assets.*')
->orderBy('admin_sort.first_name', $order)
->orderBy('admin_sort.last_name', $order);
}
public function orderAssigned(string $order): self
{
return $this
->leftJoin('users as users_sort', function ($join) {
$join->on('assets.assigned_to', '=', 'users_sort.id')
->where('assets.assigned_type', '=', User::class);
})
->addSelect('assets.*')
->orderBy('users_sort.first_name', $order)
->orderBy('users_sort.last_name', $order);
}
public function orderStatus(string $order): self
{
return $this
->join('status_labels as status_sort', 'assets.status_id', '=', 'status_sort.id')
->orderBy('status_sort.name', $order);
}
public function orderCompany(string $order): self
{
return $this
->leftJoin('companies as company_sort', 'assets.company_id', '=', 'company_sort.id')
->orderBy('company_sort.name', $order);
}
/**
* Sort by a sync-adapter external-source column (primary_mac,
* primary_ip, os, os_version, last_seen). LeftJoin so unsynced
* assets sort as nulls rather than dropping out. Join is on the
* unique asset_id index in asset_external_sources, so cost is an
* index lookup per row.
*
* Column argument is whitelisted by the caller
* (AssetsController's sort switch) so it's never user-controlled
* at this layer, but we still validate against the known column
* set as defense-in-depth against a caller regression.
*/
public function orderExternalSource(string $order, string $column): self
{
if (! in_array($column, ['primary_mac', 'primary_ip', 'os', 'os_version', 'last_seen'], true)) {
return $this;
}
return $this
->leftJoin('asset_external_sources as ext_src_sort', 'assets.id', '=', 'ext_src_sort.asset_id')
->orderBy('ext_src_sort.'.$column, $order);
}
public function inCategory(string|array $category_id): self
{
return $this
->join('models as category_models', 'assets.model_id', '=', 'category_models.id')
->join('categories', 'category_models.category_id', '=', 'categories.id')
->whereIn('category_models.category_id', (! is_array($category_id) ? explode(',', $category_id) : $category_id));
}
public function byManufacturer(string|array $manufacturer_id): self
{
return $this
->join('models', 'assets.model_id', '=', 'models.id')
->join('manufacturers', 'models.manufacturer_id', '=', 'manufacturers.id')
->whereIn('models.manufacturer_id', (! is_array($manufacturer_id) ? explode(',', $manufacturer_id) : $manufacturer_id));
}
public function orderCategory(string $order): self
{
return $this
->join('models as order_model_category', 'assets.model_id', '=', 'order_model_category.id')
->join('categories as category_order', 'order_model_category.category_id', '=', 'category_order.id')
->orderBy('category_order.name', $order);
}
public function orderManufacturer(string $order): self
{
return $this
->join('models as order_asset_model', 'assets.model_id', '=', 'order_asset_model.id')
->leftJoin('manufacturers as manufacturer_order', 'order_asset_model.manufacturer_id', '=', 'manufacturer_order.id')
->orderBy('manufacturer_order.name', $order);
}
public function orderLocation(string $order): self
{
return $this
->leftJoin('locations as asset_locations', 'asset_locations.id', '=', 'assets.location_id')
->orderBy('asset_locations.name', $order);
}
public function orderRtdLocation(string $order): self
{
return $this
->leftJoin('locations as rtd_asset_locations', 'rtd_asset_locations.id', '=', 'assets.rtd_location_id')
->orderBy('rtd_asset_locations.name', $order);
}
public function orderSupplier(string $order): self
{
return $this
->leftJoin('suppliers as suppliers_assets', 'assets.supplier_id', '=', 'suppliers_assets.id')
->orderBy('suppliers_assets.name', $order);
}
public function orderByJobTitle(string $order): self
{
return $this
->leftJoin('users as users_sort', function ($join) {
$join->on('assets.assigned_to', '=', 'users_sort.id')
->where('assets.assigned_type', '=', User::class);
})
->addSelect('assets.*')
->orderBy('users_sort.jobtitle', $order);
}
public function byLocationId(int $search): self
{
return $this->where(function ($query) use ($search) {
$query->whereHas('location', function ($query) use ($search) {
$query->where('locations.id', '=', $search);
});
});
}
public function byDepreciationId(int $search): self
{
return $this
->join('models', 'assets.model_id', '=', 'models.id')
->join('depreciations', 'models.depreciation_id', '=', 'depreciations.id')
->where('models.depreciation_id', '=', $search);
}
}