public function find($clientPublicId = null, $filter = null)
{
$query = DB::table('payments')->join('accounts', 'accounts.id', '=', 'payments.account_id')->join('clients', 'clients.id', '=', 'payments.client_id')->join('invoices', 'invoices.id', '=', 'payments.invoice_id')->join('contacts', 'contacts.client_id', '=', 'clients.id')->join('payment_statuses', 'payment_statuses.id', '=', 'payments.payment_status_id')->leftJoin('payment_types', 'payment_types.id', '=', 'payments.payment_type_id')->leftJoin('account_gateways', 'account_gateways.id', '=', 'payments.account_gateway_id')->leftJoin('gateways', 'gateways.id', '=', 'account_gateways.gateway_id')->where('payments.account_id', '=', \Auth::user()->account_id)->where('clients.deleted_at', '=', null)->where('contacts.is_primary', '=', true)->where('contacts.deleted_at', '=', null)->where('invoices.is_deleted', '=', false)->select('payments.public_id', DB::raw('COALESCE(clients.currency_id, accounts.currency_id) currency_id'), DB::raw('COALESCE(clients.country_id, accounts.country_id) country_id'), 'payments.transaction_reference', DB::raw("COALESCE(NULLIF(clients.name,''), NULLIF(CONCAT(contacts.first_name, ' ', contacts.last_name),''), NULLIF(contacts.email,'')) client_name"), 'clients.public_id as client_public_id', 'clients.user_id as client_user_id', 'payments.amount', 'payments.payment_date', 'payments.payment_status_id', 'payments.payment_type_id', 'invoices.public_id as invoice_public_id', 'invoices.user_id as invoice_user_id', 'invoices.invoice_number', 'contacts.first_name', 'contacts.last_name', 'contacts.email', 'payment_types.name as payment_type', 'payments.account_gateway_id', 'payments.deleted_at', 'payments.is_deleted', 'payments.user_id', 'payments.refunded', 'payments.expiration', 'payments.last4', 'payments.email', 'payments.routing_number', 'payments.bank_name', 'invoices.is_deleted as invoice_is_deleted', 'gateways.name as gateway_name', 'gateways.id as gateway_id', 'payment_statuses.name as payment_status_name');
if (!\Session::get('show_trash:payment')) {
$query->where('payments.deleted_at', '=', null);
}
if ($clientPublicId) {
$query->where('clients.public_id', '=', $clientPublicId);
}
if ($filter) {
$query->where(function ($query) use($filter) {
$query->where('clients.name', 'like', '%' . $filter . '%')->orWhere('invoices.invoice_number', 'like', '%' . $filter . '%')->orWhere('payments.transaction_reference', 'like', '%' . $filter . '%')->orWhere('gateways.name', 'like', '%' . $filter . '%')->orWhere('payment_types.name', 'like', '%' . $filter . '%')->orWhere('contacts.first_name', 'like', '%' . $filter . '%')->orWhere('contacts.last_name', 'like', '%' . $filter . '%')->orWhere('contacts.email', 'like', '%' . $filter . '%');
});
}
return $query;
}