/**
* Apply the scope to a given Eloquent query builder.
*
* @param \Illuminate\Database\Eloquent\Builder $builder
* @param \Illuminate\Database\Eloquent\Model $model
* @return void
*/
public function apply(Builder $builder, Model $model)
{
if (isset($model->sort)) {
$builder->selectraw("\n\t\t\t\t\t\t\tsum(IFNULL((SELECT sum((price - discount) * quantity) FROM transaction_details WHERE transaction_details.transaction_id = transactions.id and transaction_details.deleted_at is null),0)\n\t\t\t\t\t\t\t+ IFNULL((SELECT sum(price) FROM transaction_extensions WHERE transaction_extensions.transaction_id = transactions.id and transaction_extensions.deleted_at is null),0)\n\t\t\t\t\t\t\t+ transactions.shipping_cost - transactions.voucher_discount - transactions.unique_number\n\t\t\t\t\t\t\t) as amount\n\t\t\t\t\t\t")->orderby($model->sort, $model->sort_param);
} else {
$builder->selectraw("\n\t\t\t\t\t\tsum(IFNULL((SELECT sum((price - discount) * quantity) FROM transaction_details WHERE transaction_details.transaction_id = transactions.id and transaction_details.deleted_at is null),0)\n\t\t\t\t\t\t+ IFNULL((SELECT sum(price) FROM transaction_extensions WHERE transaction_extensions.transaction_id = transactions.id and transaction_extensions.deleted_at is null),0)\n\t\t\t\t\t\t+ transactions.shipping_cost - transactions.voucher_discount - transactions.unique_number\n\t\t\t\t\t\t) as amount\n\t\t\t\t\t");
}
}
/**
* Apply the scope to a given Eloquent query builder.
*
* @param \Illuminate\Database\Eloquent\Builder $builder
* @param \Illuminate\Database\Eloquent\Model $model
* @return void
*/
public function apply(Builder $builder, Model $model)
{
if (in_array(get_class($model), ['App\\Models\\Person', 'App\\Models\\Employee'])) {
$builder->selectraw('IFNULL(if(item="phone", value, ""), "not available") as phone')->selectraw('IFNULL(if(item="address", value, ""), "not available") as address')->selectraw('IFNULL(if(item="email", value, ""), "not available") as email')->leftjoin('contacts', function ($join) {
$join->on('persons.id', '=', 'contacts.person_id')->whereIn('contacts.person_type', ['App\\Models\\Person', 'App\\Models\\Employee'])->where('contacts.is_default', '=', true)->wherenull('contacts.deleted_at');
})->groupby('persons.id');
} else {
$builder->selectraw('IFNULL(if(item="phone", value, ""), "not available") as phone')->selectraw('IFNULL(if(item="address", value, ""), "not available") as address')->selectraw('IFNULL(if(item="email", value, ""), "not available") as email')->leftjoin('contacts', function ($join) {
$join->on('branches.id', '=', 'contacts.branch_id')->whereIn('contacts.branch_type', ['App\\Models\\Branch', 'App\\Models\\Employee'])->where('contacts.is_default', '=', true)->wherenull('contacts.deleted_at');
})->groupby('branches.id');
}
}
/**
* Apply the scope to a given Eloquent query builder.
*
* @param \Illuminate\Database\Eloquent\Builder $builder
* @param \Illuminate\Database\Eloquent\Model $model
* @return void
*/
public function apply(Builder $builder, Model $model)
{
if ($model->getTable() == 'tmp_vouchers') {
$builder->selectraw('IFNULL(sum(quota_logs.amount),0) as quota')->leftjoin('quota_logs', function ($join) {
$join->on('quota_logs.voucher_id', '=', 'tmp_vouchers.id')->wherenull('quota_logs.deleted_at');
})->groupby('tmp_vouchers.id');
} else {
$builder->selectraw('IFNULL(sum(quota_logs.amount),0) as quota_referral')->selectraw('IF(tmp_vouchers.type="referral", tmp_vouchers.code, "BALIN") as code_referral')->leftjoin('tmp_vouchers', function ($join) {
$join->on('tmp_vouchers.user_id', '=', 'users.id')->wherein('type', ['referral', 'promo_referral'])->wherenull('tmp_vouchers.deleted_at');
})->leftjoin('quota_logs', function ($join) {
$join->on('quota_logs.voucher_id', '=', 'tmp_vouchers.id')->wherenull('quota_logs.deleted_at');
})->groupby('users.id');
}
}
/**
* Apply the scope to a given Eloquent query builder.
*
* @param \Illuminate\Database\Eloquent\Builder $builder
* @param \Illuminate\Database\Eloquent\Model $model
* @return void
*/
public function apply(Builder $builder, Model $model)
{
if (isset($model->workend)) {
$end = $model->workend;
} else {
$end = 'now';
}
$prefix_person = 'hrps_';
//env('DB_PREFIX_HR_PERSON');
$prefix_empl = 'hres_';
//env('DB_PREFIX_HR_EMPLOYMENT');
$prefix_org = 'hrom_';
//env('DB_PREFIX_HR_ORGANISATION');
$builder->selectraw('CONCAT(' . $prefix_org . 'charts.name, " cabang ", ' . $prefix_org . 'branches.name) as newest_position')->selectraw($prefix_org . 'branches.organisation_id as organisation_id')->selectraw($prefix_org . 'charts.department as newest_department')->selectraw($prefix_empl . 'works.id as newest_work_id')->selectraw($prefix_empl . 'works.nik as newest_nik')->selectraw($prefix_empl . 'works.status as newest_status')->selectraw($prefix_empl . 'works.start as newest_work_start')->selectraw($prefix_empl . 'works.end as newest_work_end')->join(DB::raw($prefix_empl . 'works'), function ($join) use($end, $prefix_empl, $prefix_person) {
$join->on(DB::raw($prefix_person . 'persons.id'), '=', DB::raw($prefix_empl . 'works.person_id'))->where(function ($query) use($end, $prefix_empl) {
$query->where(function ($query) use($end, $prefix_empl) {
$query;
// $query->wherenull( DB::raw($prefix_empl.'works.end'))
// ->orwhere( DB::raw($prefix_empl.'works.end'), '>=', date('Y-m-d H:i:s', strtotime($end)));
})->wherenull(DB::raw($prefix_empl . 'works.deleted_at'));
});
})->join(DB::raw($prefix_org . 'charts'), function ($join) use($prefix_empl, $prefix_org) {
$join->on(DB::raw($prefix_empl . 'works.chart_id'), '=', DB::raw($prefix_org . 'charts.id'))->wherenull(DB::raw($prefix_org . 'charts.deleted_at'));
})->join(DB::raw($prefix_org . 'branches'), function ($join) use($prefix_org) {
$join->on(DB::raw($prefix_org . 'charts.branch_id'), '=', DB::raw($prefix_org . 'branches.id'))->wherenull(DB::raw($prefix_org . 'branches.deleted_at'));
})->groupby(DB::raw($prefix_person . 'persons.id'));
}
/**
* Apply the scope to a given Eloquent query builder.
*
* @param \Illuminate\Database\Eloquent\Builder $builder
* @param \Illuminate\Database\Eloquent\Model $model
* @return void
*/
public function apply(Builder $builder, Model $model)
{
$prefix_empl = 'hres_';
//env('DB_PREFIX_HR_EMPLOYMENT');
$builder->selectraw('tl1.grade as grade')->leftjoin(DB::raw($prefix_empl . 'grade_logs as tl1'), function ($join) use($prefix_empl) {
$join->on(DB::raw($prefix_empl . 'works.id'), '=', 'tl1.work_id')->on(DB::raw('(tl1.id = (select id from ' . $prefix_empl . 'grade_logs as tl2 where tl2.work_id <> tl1.work_id and tl2.deleted_at is null order by tl2.updated_at desc limit 1))'), DB::raw(''), DB::raw(''))->wherenull('tl1.deleted_at');
})->groupby(DB::raw($prefix_empl . 'works.id'));
}
/**
* Apply the scope to a given Eloquent query builder.
*
* @param \Illuminate\Database\Eloquent\Builder $builder
* @param \Illuminate\Database\Eloquent\Model $model
* @return void
*/
public function apply(Builder $builder, Model $model)
{
$builder->selectraw('IFNULL(users2.name, IFNULL(vouchers2.code,"EMPTY")) as reference_name')->leftjoin('point_logs', function ($join) {
$join->on('point_logs.user_id', '=', 'users.id')->whereIn('point_logs.reference_type', ['App\\Models\\User', 'App\\Models\\Voucher'])->wherenull('point_logs.deleted_at');
})->leftjoin(DB::raw('(SELECT name, id, deleted_at from users) as users2'), function ($join) {
$join->on('users2.id', '=', 'point_logs.reference_id')->where('point_logs.reference_type', '=', 'App\\Models\\User')->wherenull('users2.deleted_at');
})->leftjoin(DB::raw('(SELECT code, id, deleted_at from tmp_vouchers) as vouchers2'), function ($join) {
$join->on('vouchers2.id', '=', 'point_logs.reference_id')->where('point_logs.reference_type', '=', 'App\\Models\\Voucher')->wherenull('vouchers2.deleted_at');
});
}
/**
* Apply the scope to a given Eloquent query builder.
*
* @param \Illuminate\Database\Eloquent\Builder $builder
* @param \Illuminate\Database\Eloquent\Model $model
* @return void
*/
public function apply(Builder $builder, Model $model)
{
$builder->selectraw('persons.*')->selectraw('CONCAT(charts.name, " cabang ", branches.name) as jabatan')->join('works', function ($join) {
$join->on('persons.id', '=', 'works.person_id')->wherenull('works.end')->orwhere('works.end', '>=', date('Y-m-d H:i:s'))->wherenull('works.deleted_at');
})->join('works_authentications', function ($join) {
$join->on('works.id', '=', 'works_authentications.work_id')->wherenull('works_authentications.deleted_at');
})->join('charts', function ($join) {
$join->on('works.chart_id', '=', 'charts.id')->wherenull('charts.deleted_at');
})->join('branches', function ($join) {
$join->on('charts.branch_id', '=', 'branches.id')->wherenull('branches.deleted_at');
})->orderby('tmp_auth_group_id', 'asc')->groupby('persons.id');
}
/**
* Apply the scope to a given Eloquent query builder.
*
* @param \Illuminate\Database\Eloquent\Builder $builder
* @param \Illuminate\Database\Eloquent\Model $model
* @return void
*/
public function apply(Builder $builder, Model $model)
{
if (isset($model->workend)) {
$end = $model->workend;
} else {
$end = 'now';
}
$builder->selectraw('CONCAT(charts.name, " cabang ", branches.name) as current_job')->selectraw('works.id as current_work_id')->selectraw('works.calendar_id as current_calendar_id')->selectraw('works.grade as current_grade')->join('works', function ($join) use($end) {
$join->on('persons.id', '=', 'works.person_id')->where(function ($query) use($end) {
$query->wherenull('works.end')->orwhere('works.end', '>=', date('Y-m-d H:i:s', strtotime($end)));
});
})->join('charts', function ($join) {
$join->on('works.chart_id', '=', 'charts.id')->wherenull('charts.deleted_at');
})->join('branches', function ($join) {
$join->on('charts.branch_id', '=', 'branches.id')->wherenull('branches.deleted_at');
})->groupby('persons.id');
}
/**
* Apply the scope to a given Eloquent query builder.
*
* @param \Illuminate\Database\Eloquent\Builder $builder
* @param \Illuminate\Database\Eloquent\Model $model
* @return void
*/
public function apply(Builder $builder, Model $model)
{
$builder->selectraw("\n\t\t\t\t\tsum(IFNULL((SELECT abs(sum(amount)) FROM point_logs WHERE point_logs.reference_id = transactions.id and point_logs.deleted_at is null and point_logs.reference_type like '%Sale%' and point_logs.amount < 0),0)\n\t\t\t\t\t) as point_discount\n\t\t\t\t");
}
/**
* Apply the scope to a given Eloquent query builder.
*
* @param \Illuminate\Database\Eloquent\Builder $builder
* @param \Illuminate\Database\Eloquent\Model $model
* @return void
*/
public function apply(Builder $builder, Model $model)
{
$prefix_contact = 'hrom_';
$builder->selectraw('(SELECT IFNULL(value, "not available") from ' . $prefix_contact . 'contacts as contacts where contacts.contactable_id = ' . $model->getTable() . '.id and contacts.contactable_type like "%' . class_basename($model) . '" and type = "address" and is_default = 1 and contacts.deleted_at is null) as address')->selectraw('(SELECT IFNULL(value, "not available") from ' . $prefix_contact . 'contacts as contacts where contacts.contactable_id = ' . $model->getTable() . '.id and contacts.contactable_type like "%' . class_basename($model) . '" and type = "phone" and is_default = 1 and contacts.deleted_at is null) as phone')->selectraw('(SELECT IFNULL(value, "not available") from ' . $prefix_contact . 'contacts as contacts where contacts.contactable_id = ' . $model->getTable() . '.id and contacts.contactable_type like "%' . class_basename($model) . '" and type = "email" and is_default = 1 and contacts.deleted_at is null) as email')->groupby($model->getTable() . '.id');
}
/**
* Apply the scope to a given Eloquent query builder.
*
* @param \Illuminate\Database\Eloquent\Builder $builder
* @param \Illuminate\Database\Eloquent\Model $model
* @return void
*/
public function apply(Builder $builder, Model $model)
{
$builder->selectraw('IFNULL(marital_statuses.status,"tidak ada") as marital_status')->leftjoin('marital_statuses', function ($join) {
$join->on('persons.id', '=', 'marital_statuses.person_id')->on(DB::raw('(marital_statuses.id = (select id from marital_statuses as ms2 where ms2.person_id = marital_statuses.person_id and ms2.deleted_at is null order by ms2.id desc limit 1))'), DB::raw(''), DB::raw(''))->wherenull('marital_statuses.deleted_at');
});
}
/**
* Apply the scope to a given Eloquent query builder.
*
* @param \Illuminate\Database\Eloquent\Builder $builder
* @param \Illuminate\Database\Eloquent\Model $model
* @return void
*/
public function apply(Builder $builder, Model $model)
{
$builder->selectraw('IFNULL(transaction_logs.status, "na") as status')->lefttransactionlogstatus(['cart', 'wait', 'payment_process', 'paid', 'packed', 'shipping', 'delivered', 'canceled', 'abandoned'])->groupby('transactions.id');
}
/**
* Apply the scope to a given Eloquent query builder.
*
* @param \Illuminate\Database\Eloquent\Builder $builder
* @param \Illuminate\Database\Eloquent\Model $model
* @return void
*/
public function apply(Builder $builder, Model $model)
{
$builder->selectraw($model->getTable() . '.*');
}
/**
* Apply the scope to a given Eloquent query builder.
*
* @param \Illuminate\Database\Eloquent\Builder $builder
* @param \Illuminate\Database\Eloquent\Model $model
* @return void
*/
public function apply(Builder $builder, Model $model)
{
$builder->selectraw('images.thumbnail as thumbnail')->selectraw('images.image_xs as image_xs')->selectraw('images.image_sm as image_sm')->selectraw('images.image_md as image_md')->selectraw('images.image_lg as image_lg')->leftjoin('images', function ($join) use($model) {
$join->on('images.imageable_id', '=', $model->getTable() . '.id')->where('images.imageable_type', '=', get_class($model))->where('images.is_default', '=', true)->wherenull('images.deleted_at');
});
}
/**
* Apply the scope to a given Eloquent query builder.
*
* @param \Illuminate\Database\Eloquent\Builder $builder
* @param \Illuminate\Database\Eloquent\Model $model
* @return void
*/
public function apply(Builder $builder, Model $model)
{
$builder->selectraw('addresses.address as current_address')->selectraw('addresses.zipcode as current_zipcode')->selectraw('addresses.phone as current_phone')->leftjoin('addresses', function ($join) use($model) {
$join->on('addresses.owner_id', '=', $model->getTable() . '.id')->where('addresses.owner_type', '=', get_class($model))->wherenull('addresses.deleted_at');
});
}
/**
* Apply the scope to a given Eloquent query builder.
*
* @param \Illuminate\Database\Eloquent\Builder $builder
* @param \Illuminate\Database\Eloquent\Model $model
* @return void
*/
public function apply(Builder $builder, Model $model)
{
$builder->selectraw('(SELECT IFNULL(SUM(point_logs3.amount), 0) from point_logs as point_logs3 where point_logs3.user_id = users.id and point_logs3.expired_at >= CONVERT_TZ(NOW(),"+00:00","+07:00") and point_logs3.deleted_at is null) as total_point');
}
/**
* Apply the scope to a given Eloquent query builder.
*
* @param \Illuminate\Database\Eloquent\Builder $builder
* @param \Illuminate\Database\Eloquent\Model $model
* @return void
*/
public function apply(Builder $builder, Model $model)
{
$builder->selectraw("\n\t\t\t\t\t\tsum(IFNULL((SELECT sum(price) FROM transaction_extensions WHERE transaction_extensions.transaction_id = transactions.id and transaction_extensions.deleted_at is null),0)\n\t\t\t\t\t\t) as extend_cost\n\t\t\t\t\t");
}
/**
* Apply the scope to a given Eloquent query builder.
*
* @param \Illuminate\Database\Eloquent\Builder $builder
* @param \Illuminate\Database\Eloquent\Model $model
* @return void
*/
public function apply(Builder $builder, Model $model)
{
$builder->selectraw('IFNULL(SUM(quota),0) as workleave_quota')->leftjoin('person_workleaves', function ($join) {
$join->on('persons.id', '=', 'person_workleaves.person_id')->whereIn('person_workleaves.status', ['CN', 'CB'])->where('person_workleaves.start', '>=', 'NOW()')->where('person_workleaves.end', '<', 'NOW()')->wherenull('person_workleaves.deleted_at');
});
}
/**
* Apply the scope to a given Eloquent query builder.
*
* @param \Illuminate\Database\Eloquent\Builder $builder
* @param \Illuminate\Database\Eloquent\Model $model
* @return void
*/
public function apply(Builder $builder, Model $model)
{
$builder->selectraw('(SELECT IFNULL(COUNT(point_logs2.user_id), 0) from point_logs as point_logs2 where point_logs2.reference_id = users.id and point_logs2.reference_type like "%User" and point_logs2.deleted_at is null) as total_reference');
}
/**
* Apply the scope to a given Eloquent query builder.
*
* @param \Illuminate\Database\Eloquent\Builder $builder
* @param \Illuminate\Database\Eloquent\Model $model
* @return void
*/
public function apply(Builder $builder, Model $model)
{
$builder->selectraw("\n\t\t\t\t\t\tsum(IFNULL((SELECT sum((price - discount) * quantity) FROM transaction_details WHERE transaction_details.transaction_id = transactions.id and transaction_details.deleted_at is null),0)\n\t\t\t\t\t\t+ IFNULL((SELECT sum(price) FROM transaction_extensions WHERE transaction_extensions.transaction_id = transactions.id and transaction_extensions.deleted_at is null),0)\n\t\t\t\t\t\t+ IFNULL((SELECT sum(amount) FROM point_logs WHERE point_logs.reference_id = transactions.id and point_logs.deleted_at is null and point_logs.reference_type like '%Sale%' and point_logs.amount < 0),0)\n\t\t\t\t\t\t- IFNULL((SELECT sum(amount) FROM payments WHERE payments.transaction_id = transactions.id and payments.deleted_at is null),0)\n\t\t\t\t\t\t+ transactions.shipping_cost - transactions.voucher_discount - transactions.unique_number\n\t\t\t\t\t\t) as bills\n\t\t\t\t\t");
}
/**
* Apply the scope to a given Eloquent query builder.
*
* @param \Illuminate\Database\Eloquent\Builder $builder
* @param \Illuminate\Database\Eloquent\Model $model
* @return void
*/
public function apply(Builder $builder, Model $model)
{
$builder->selectraw('IFNULL(prices.price, 0) as price')->selectraw('IFNULL(prices.promo_price, 0) as promo_price')->selectraw('IFNULL(prices.started_at, NOW()) as price_start')->leftjoin('prices', function ($join) {
$join->on('prices.product_id', '=', 'products.id')->on(DB::raw('(prices.id = (select id from prices as tl2 where tl2.product_id = prices.product_id and tl2.deleted_at is null and tl2.started_at <= "' . date('Y-m-d H:i:s') . '" order by tl2.started_at desc limit 1))'), DB::raw(''), DB::raw(''))->where('prices.started_at', '<=', date('Y-m-d H:i:s'))->wherenull('prices.deleted_at');
});
}
