public function getClientDatatable($contactId, $entityType, $search)
{
$query = DB::table('invitations')->join('accounts', 'accounts.id', '=', 'invitations.account_id')->join('invoices', 'invoices.id', '=', 'invitations.invoice_id')->join('clients', 'clients.id', '=', 'invoices.client_id')->join('contacts', 'contacts.client_id', '=', 'clients.id')->where('invitations.contact_id', '=', $contactId)->where('invitations.deleted_at', '=', null)->where('invoices.invoice_type_id', '=', $entityType == ENTITY_QUOTE ? INVOICE_TYPE_QUOTE : INVOICE_TYPE_STANDARD)->where('invoices.is_deleted', '=', false)->where('clients.deleted_at', '=', null)->where('contacts.deleted_at', '=', null)->where('contacts.is_primary', '=', true)->where('invoices.is_recurring', '=', false)->select(DB::raw('COALESCE(clients.currency_id, accounts.currency_id) currency_id'), DB::raw('COALESCE(clients.country_id, accounts.country_id) country_id'), 'invitations.invitation_key', 'invoices.invoice_number', 'invoices.invoice_date', 'invoices.balance as balance', 'invoices.due_date', 'clients.public_id as client_public_id', DB::raw("COALESCE(NULLIF(clients.name,''), NULLIF(CONCAT(contacts.first_name, ' ', contacts.last_name),''), NULLIF(contacts.email,'')) client_name"), 'invoices.public_id', 'invoices.amount', 'invoices.start_date', 'invoices.end_date', 'invoices.partial');
$table = \Datatable::query($query)->addColumn('invoice_number', function ($model) use($entityType) {
return link_to('/view/' . $model->invitation_key, $model->invoice_number)->toHtml();
})->addColumn('invoice_date', function ($model) {
return Utils::fromSqlDate($model->invoice_date);
})->addColumn('amount', function ($model) {
return Utils::formatMoney($model->amount, $model->currency_id, $model->country_id);
});
if ($entityType == ENTITY_INVOICE) {
$table->addColumn('balance', function ($model) {
return $model->partial > 0 ? trans('texts.partial_remaining', ['partial' => Utils::formatMoney($model->partial, $model->currency_id, $model->country_id), 'balance' => Utils::formatMoney($model->balance, $model->currency_id, $model->country_id)]) : Utils::formatMoney($model->balance, $model->currency_id, $model->country_id);
});
}
return $table->addColumn('due_date', function ($model) {
return Utils::fromSqlDate($model->due_date);
})->make();
}