app/Http/Controllers/ContactController.php
/**
     * Returns the database object for customer
     *
     * @return \Illuminate\Http\Response
     */
Returns the database object for customer
@return \Illuminate\Http\Response
Returns the database object for customer @return \ Illuminate \ Http \ Response
private function indexCustomer(Request $request)
    {
        try{
            if (!auth()->user()->can('customer.view')) {
                abort(403, 'Unauthorized action.');
            }
            $business_id = request()->session()->get('user.business_id');
            $query = Contact::leftjoin('transactions AS t', 'contacts.id', '=', 't.contact_id')
                        ->leftjoin('customer_groups AS cg', 'contacts.customer_group_id', '=', 'cg.id')
                        ->where('contacts.business_id', $business_id)
                        ->whereDate('contacts.created_at', '>=', $request['start_date'])
                        ->whereDate('contacts.created_at', '<=', $request['end_date'])
                        ->onlyCustomers()
                        ->select(
                            [
                                'contacts.contact_id', 
                                'contacts.name', 
                                'contacts.created_at',
                                'total_rp',
                                'cg.name as customer_group', 
                                'cg.subscription_cost',
                                'cg.subscription_pieces',
                                'city', 
                                'state', 
                                'country', 
                                'landmark', 
                                'mobile', 
                                'contacts.id', 
                                'is_default',
                                'custom_field1',
                                'custom_field2',
                                'custom_field3',
                                'custom_field4',
                                'renewal_count',
                                'total_paid_value',
                                DB::raw("SUM(IF(t.type = 'sell' AND t.status = 'final', final_total, 0)) as total_invoice"),
                                DB::raw("SUM(IF(t.type = 'sell' AND t.status = 'final', (SELECT SUM(IF(is_return = 1,-1*amount,amount)) FROM transaction_payments WHERE transaction_payments.transaction_id=t.id), 0)) as invoice_received"),
                                DB::raw("SUM(IF(t.type = 'sell_return', final_total, 0)) as total_sell_return"),
                                DB::raw("SUM(IF(t.type = 'sell_return', (SELECT SUM(amount) FROM transaction_payments WHERE transaction_payments.transaction_id=t.id), 0)) as sell_return_paid"),
                                DB::raw("SUM(IF(t.type = 'opening_balance', final_total, 0)) as opening_balance"),
                                DB::raw("SUM(IF(t.type = 'opening_balance', (SELECT SUM(IF(is_return = 1,-1*amount,amount)) FROM transaction_payments WHERE transaction_payments.transaction_id=t.id), 0)) as opening_balance_paid")
                            ])
                        ->groupBy('contacts.id');
            $contacts = Datatables::of($query)
                ->addColumn('address', '{{implode(array_filter([$custom_field4,$city,$state,$country,$landmark]), ", ")}}')
                ->editColumn(
                    'total_paid_value',
                    '<span class="display_currency total_paid_value" data-orig-value="{{ $total_paid_value }}" data-currency_symbol=true data-highlight=false>{{ $total_paid_value }}</span>'
                )
                ->addColumn(
                    'status',
                    '<h5 class="customer_sub_status_<?php echo $custom_field1 == "0" ? "off" : "on" ?>"><?php echo $custom_field1 == "0" ? "Not" : "Member" ?></h5>'
                )
                ->addColumn(
                    'action',
                    '<div class="btn-group">
                        <button type="button" class="btn btn-info dropdown-toggle btn-xs" 
                            data-toggle="dropdown" aria-expanded="false">' .
                            __("messages.actions") .
                            '<span class="caret"></span><span class="sr-only">Toggle Dropdown
                            </span>
                        </button>
                        <ul class="dropdown-menu dropdown-menu-right" role="menu">
                    @if(($total_invoice + $opening_balance - $invoice_received - $opening_balance_paid)  > 0)
                        <li><a href="{{action(\'TransactionPaymentController@getPayContactDue\', [$id])}}?type=sell" class="pay_sale_due"><i class="fa fa-money" aria-hidden="true"></i>@lang("contact.pay_due_amount")</a></li>
                    @endif
                    @if(($total_sell_return - $sell_return_paid)  > 0)
                        <li><a href="{{action(\'TransactionPaymentController@getPayContactDue\', [$id])}}?type=sell_return" class="pay_purchase_due"><i class="fa fa-money" aria-hidden="true"></i>@lang("lang_v1.pay_sell_return_due")</a></li>
                    @endif
                    @can("customer.view")
                        <li><a href="{{action(\'ContactController@show\', [$id])}}"><i class="fa fa-external-link" aria-hidden="true"></i> @lang("messages.view")</a></li>
                    @endcan
                    @can("customer.update")
                        <li><a href="{{action(\'ContactController@edit\', [$id])}}" class="edit_contact_button"><i class="glyphicon glyphicon-edit"></i> @lang("messages.edit")</a></li>
                    @endcan
                    @if(!$is_default)
                    @can("customer.delete")
                        <li><a href="{{action(\'ContactController@destroy\', [$id])}}" class="delete_contact_button"><i class="glyphicon glyphicon-trash"></i> @lang("messages.delete")</a></li>
                    @endcan
                    @endif 
                    </ul></div>'
                )
                ->editColumn('total_rp', '{{$total_rp ?? 0}}')
                ->editColumn('created_at', '{{@format_date($created_at)}}')
                ->filterColumn('address', function ($query, $keyword) {
                    $query->whereRaw("CONCAT(COALESCE(landmark, ''), ', ', COALESCE(city, ''), ', ', COALESCE(state, ''), ', ', COALESCE(country, '') ) like ?", ["%{$keyword}%"]);
                });
            $reward_enabled = (request()->session()->get('business.enable_rp') == 1) ? true : false;
            if (!$reward_enabled) {
                $contacts->removeColumn('total_rp');
            }
            return $contacts->rawColumns(['custom_field1','custom_field3','action','total_paid_value','status'])
                            ->make(true);
        }catch(\Exception $e){
            return $e->getMessage();
        }
    }
private function indexCustomer ( Request $ request ) { try { if ( ! auth ( ) -> user ( ) -> can ( 'customer.view' ) ) { abort ( 403 , 'Unauthorized action.' ) ; } $ business_id = request ( ) -> session ( ) -> get ( 'user.business_id' ) ; $ query = Contact :: leftjoin ( 'transactions AS t' , 'contacts.id' , '=' , 't.contact_id' ) -> leftjoin ( 'customer_groups AS cg' , 'contacts.customer_group_id' , '=' , 'cg.id' ) -> where ( 'contacts.business_id' , $ business_id ) -> whereDate ( 'contacts.created_at' , '>=' , $ request [ 'start_date' ] ) -> whereDate ( 'contacts.created_at' , '<=' , $ request [ 'end_date' ] ) -> onlyCustomers ( ) -> select ( [ 'contacts.contact_id' , 'contacts.name' , 'contacts.created_at' , 'total_rp' , 'cg.name as customer_group' , 'cg.subscription_cost' , 'cg.subscription_pieces' , 'city' , 'state' , 'country' , 'landmark' , 'mobile' , 'contacts.id' , 'is_default' , 'custom_field1' , 'custom_field2' , 'custom_field3' , 'custom_field4' , 'renewal_count' , 'total_paid_value' , DB :: raw ( " SUM(IF(t.type = 'sell' AND t.status = 'final', final_total, 0)) as total_invoice " ) , DB :: raw ( " SUM(IF(t.type = 'sell' AND t.status = 'final', (SELECT SUM(IF(is_return = 1,-1*amount,amount)) FROM transaction_payments WHERE transaction_payments.transaction_id=t.id), 0)) as invoice_received " ) , DB :: raw ( " SUM(IF(t.type = 'sell_return', final_total, 0)) as total_sell_return " ) , DB :: raw ( " SUM(IF(t.type = 'sell_return', (SELECT SUM(amount) FROM transaction_payments WHERE transaction_payments.transaction_id=t.id), 0)) as sell_return_paid " ) , DB :: raw ( " SUM(IF(t.type = 'opening_balance', final_total, 0)) as opening_balance " ) , DB :: raw ( " SUM(IF(t.type = 'opening_balance', (SELECT SUM(IF(is_return = 1,-1*amount,amount)) FROM transaction_payments WHERE transaction_payments.transaction_id=t.id), 0)) as opening_balance_paid " ) ] ) -> groupBy ( 'contacts.id' ) ; $ contacts = Datatables :: of ( $ query ) -> addColumn ( 'address' , '{{implode(array_filter([$custom_field4,$city,$state,$country,$landmark]), ", ")}}' ) -> editColumn ( 'total_paid_value' , '<span class="display_currency total_paid_value" data-orig-value="{{ $total_paid_value }}" data-currency_symbol=true data-highlight=false>{{ $total_paid_value }}</span>' ) -> addColumn ( 'status' , '<h5 class="customer_sub_status_<?php echo $custom_field1 == "0" ? "off" : "on" ?>"><?php echo $custom_field1 == "0" ? "Not" : "Member" ?></h5>' ) -> addColumn ( 'action' , '<div class="btn-group">
                        <button type="button" class="btn btn-info dropdown-toggle btn-xs" 
                            data-toggle="dropdown" aria-expanded="false">' . __ ( " messages.actions " ) . '<span class="caret"></span><span class="sr-only">Toggle Dropdown
                            </span>
                        </button>
                        <ul class="dropdown-menu dropdown-menu-right" role="menu">
                    @if(($total_invoice + $opening_balance - $invoice_received - $opening_balance_paid)  > 0)
                        <li><a href="{{action(\'TransactionPaymentController@getPayContactDue\', [$id])}}?type=sell" class="pay_sale_due"><i class="fa fa-money" aria-hidden="true"></i>@lang("contact.pay_due_amount")</a></li>
                    @endif
                    @if(($total_sell_return - $sell_return_paid)  > 0)
                        <li><a href="{{action(\'TransactionPaymentController@getPayContactDue\', [$id])}}?type=sell_return" class="pay_purchase_due"><i class="fa fa-money" aria-hidden="true"></i>@lang("lang_v1.pay_sell_return_due")</a></li>
                    @endif
                    @can("customer.view")
                        <li><a href="{{action(\'ContactController@show\', [$id])}}"><i class="fa fa-external-link" aria-hidden="true"></i> @lang("messages.view")</a></li>
                    @endcan
                    @can("customer.update")
                        <li><a href="{{action(\'ContactController@edit\', [$id])}}" class="edit_contact_button"><i class="glyphicon glyphicon-edit"></i> @lang("messages.edit")</a></li>
                    @endcan
                    @if(!$is_default)
                    @can("customer.delete")
                        <li><a href="{{action(\'ContactController@destroy\', [$id])}}" class="delete_contact_button"><i class="glyphicon glyphicon-trash"></i> @lang("messages.delete")</a></li>
                    @endcan
                    @endif 
                    </ul></div>' ) -> editColumn ( 'total_rp' , '{{$total_rp ?? 0}}' ) -> editColumn ( 'created_at' , '{{@format_date($created_at)}}' ) -> filterColumn ( 'address' , function ( $ query , $ keyword ) { $ query -> whereRaw ( " CONCAT(COALESCE(landmark, ''), ', ', COALESCE(city, ''), ', ', COALESCE(state, ''), ', ', COALESCE(country, '') ) like ? " , [ " % { $ keyword } % " ] ) ; } ) ; $ reward_enabled = ( request ( ) -> session ( ) -> get ( 'business.enable_rp' ) == 1 ) ? true : false ; if ( ! $ reward_enabled ) { $ contacts -> removeColumn ( 'total_rp' ) ; } return $ contacts -> rawColumns ( [ 'custom_field1' , 'custom_field3' , 'action' , 'total_paid_value' , 'status' ] ) -> make ( true ) ; } catch ( \ Exception $ e ) { return $ e -> getMessage ( ) ; } }
Returns the database object for customer
 @return \Illuminate\Http\Response
Returns the database object for customer @return \ Illuminate \ Http \ Response
// ->where('contacts.customer_group_id','!=',1) // ->addColumn( //     'due', //     '<span class="display_currency contact_due" data-orig-value="{{$total_invoice - $invoice_received}}" data-currency_symbol=true data-highlight=true>{{($total_invoice - $invoice_received)}}</span>' // ) //<li><a href="{{action(\'ContactController@show\', [$id])}}"><i class="fa fa-cog" aria-hidden="true"></i> @lang("messages.block_user")</a></li> // ->removeColumn('total_invoice') // ->removeColumn('opening_balance') // ->removeColumn('opening_balance_paid') // ->removeColumn('invoice_received') // ->removeColumn('state') // ->removeColumn('country') // ->removeColumn('city') // ->removeColumn('type') // ->removeColumn('id') // ->removeColumn('is_default') // ->removeColumn('total_sell_return') // ->removeColumn('sell_return_paid')