Hi, I have a functionality where I need to use MySql DataSource first to fetch the result in an array, and then loop through that data to execute more database queries to store result in an array. Now I used this array datastore as source for Table in view.

I tries two approaches.

First one. I kept the logic of processing the final resultant array in setup method and sending this datastore to view in an array datastore. In this case I get two errors - SQLSTATE[HY000]: General error: 2031 (SQL: select loads.id, `lo

and

PDOStatement::execute(): SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens

Second approach. I kept the logic to processing final resultant $data array in my index.php and passed it to cashflow.php as an array datasource and then to view. In this case, I get error in view Table: "message": "Undefined index: columns",

"exception": "ErrorException",
"file": "/etc/www/TracxTMS/non_composer_vendor/koolphp/koolreport/widgets/koolphp/Table.php",
"line": 110,

Here are the snippets:

index.php

$loadStatus = LoadStatus::select(['id'])->where('status_category', 'Cancelled')->where('org_id', Auth::user()->org_id)->first();

                    $cashFlow = new CashFlow(array("orgID" => $orgID, "startDate" => $request->startDate, "endDate" => $request->endDate, "factor" => $request->cashflowfactor, 'loadStatusId' => $loadStatus['id'],
                "showlogo" => $request->showlogo, "showcompanyname" => $request->showcompanyname,
                "showreporttitle" => $request->showreporttitle, "showdateprepared" => $request->showdateprepared, "showtimeprepared" => $request->showtimeprepared ));
            $cashFlow->run()->render();

cashflow.php:

function settings()

{
    return array(
        "dataSources"=>array(
            "users"=>array(
                "connectionString"=>"mysql:host=" . env('DB_HOST') . ";dbname=" . env('DB_DATABASE'),
                "username"=>env('DB_USERNAME'),
                "password"=>env('DB_PASSWORD')
            ),
            "loadsinvoices"=>array(
                "class"=>"\koolreport\datasources\ArrayDataSource",

// "mydata" => $this->params["mydata"],

                "dataFormat" => "table"
            )
        )
    );
}

function setup()
{
    $orgID = Auth::user()->org_id;

// $startDate = new \DateTime($this->params['startDate']); // $endDate = new \DateTime($this->params['endDate']); // $endDate->add(new \DateInterval('P1D')); // $betweenArray = [$startDate->format('Y-m-d'), $endDate->format('Y-m-d')]; //dd($betweenArray); // $loadStatus = LoadStatus::select(['id'])->where('status_category', 'Cancelled')->where('org_id', Auth::user()->org_id)->first();

    $loadsRaw=[];

    //Get our loads in between the request dates
    $this->src('users')->query(
        DB::table('loads')->select([
            'loads.id',
            'loads.show_id AS showID',
            'loads.created_at',
            'invoices.show_id AS invID',
            'invoices.created_at AS inv_date',
            'name_addresses.company_name',
            DB::raw('IF(invoices.date_paid, "Yes", "No") AS paid')
        ])->leftJoin('invoices', 'loads.invoice_id', 'invoices.id')
            ->join('name_addresses', 'name_addresses.id', 'loads.invoice_to_id')
            ->whereNull('loads.template')
            ->whereNull('loads.deleted_at')

// ->where('loads.org_id', $orgID)

            ->whereRaw('loads.org_id = :orgID')

// ->where('loads.status_id', '!=', $loadStatus['id'])

            ->whereRaw('loads.status_id != :loadStatusId')

// ->whereBetween('loads.created_at', $betweenArray)

            ->whereRaw('loads.created_at Between :startDate and :endDate')
        ->when($this->params['factor'] === "true", function ($query) {
            return $query->whereNotNull('invoices.date_factored');
        })->get()
    )
        ->params(array(":orgID"=>$this->params["orgID"], ":startDate"=>$this->params["startDate"],":endDate"=>$this->params["endDate"],":loadStatusId"=>$this->params["loadStatusId"]))
        ->pipe(new Sort(array(\Illuminate\Support\Facades\Session::get('sortColumn')=>\Illuminate\Support\Facades\Session::get('sortOrder')
        )))
        ->pipe(new Limit(array(\Illuminate\Support\Facades\Session::get('rowlimit'))))
        ->pipe($this->dataStore('users'));

$dataResult = $this->dataStore('users'); dd($dataResult);

    if($loadsRaw != null) {
        $invoiceTotal = 0;
        $settleTotal = 0;
        $length = 0;
        $loadsRaw=[];
        $data = [];
        foreach ($loadsRaw as $load) {
            $incomeRaw = DB::table('cash_flow')->select([
                'load_amount',
                'accessorial_amount',
                'fsc_amount',
                'tax_amount',
            ])->where('load_id', $load->id)
                ->first();

            $settlementRaw = DB::table('load_driver_settlements')->select([
                'settlement_amount',
                'settlement_id',
            ])->where('load_id', $load->id)
                ->get();

            $accCharge = DB::table('accessorial_charges')->select([
                'customer',
                'driver',
            ])->where('load_id', $load->id)
                ->get();

            $invoiceCash = ($incomeRaw->load_amount + $incomeRaw->accessorial_amount + $incomeRaw->tax_amount);
            $invoiceTotal += $invoiceCash;
            $accDriver = 0;
            foreach ($accCharge as $acc)
                if ($acc->driver)
                    $accDriver += $acc->driver;

            foreach ($settlementRaw as $settlement) {
                $settlementCash = ($settlement->settlement_amount + $accDriver);
                $settleTotal += $settlementCash;
                $length++;

                $temp = (float)str_replace(',', '', number_format($settlement->settlement_amount, 2));
                $rowTotal = self::currencyFormatHelper($temp);

                $data[] = [
                    'showID' => $load->showID,
                    'inv_to' => $load->company_name,
                    'created_date' => self::dateFormatHelper($load->created_at),
                    'paid' => $load->paid,
                    'inv_num' => $load->invID??'N/A',
                    'inv_date' => ($load->inv_date) ? self::dateFormatHelper($load->inv_date) : 'N/A',
                    'settled' => ($settlement->settlement_id) ? 'Yes' : 'No',
                    'invoice' => self::currencyFormatHelper($invoiceCash),
                    'settlement' => $rowTotal,
                    'income' => self::currencyFormatHelper(($invoiceCash - $settlementCash)),
                ];
            }
        }

        $incomeTotal = $invoiceTotal - $settleTotal;

        $data['invoiceTotal'] = self::currencyFormatHelper($invoiceTotal);
        $data['settleTotal'] = self::currencyFormatHelper($settleTotal);
        $data['incomeTotal'] = self::currencyFormatHelper($incomeTotal);
        $data['length'] = $length;

    dd($data);
    $this->src('loadsinvoices')
        ->load($data)
        ->pipe(new Sort(array(\Illuminate\Support\Facades\Session::get('sortColumn')=>\Illuminate\Support\Facades\Session::get('sortOrder')
        )))
        ->pipe(new Limit(array(\Illuminate\Support\Facades\Session::get('rowlimit'))))
        ->pipe($this->dataStore('usersloadsinvoices'));
}
    //dd($this->dataStore('usersloadsinvoices'));

// $this->src('mydata') // ->pipe(new Sort(array(\Illuminate\Support\Facades\Session::get('sortColumn')=>\Illuminate\Support\Facades\Session::get('sortOrder') // ))) // ->pipe(new Limit(array(\Illuminate\Support\Facades\Session::get('rowlimit')))) // ->pipe($this->dataStore("test"));

}

function getOrgName()

{
    $orgID = Auth::user()->org_id;
    $orgName = Organization::where('id', $orgID)->pluck('organization_name');
    $data = array("orgname"=>$orgName,"showlogo" => $this->params["showlogo"], "showcompanyname" => $this->params["showcompanyname"], "showreporttitle" => $this->params["showreporttitle"],
        "showdateprepared" => $this->params["showdateprepared"],
        "showtimeprepared" => $this->params["showtimeprepared"]);
    return $data;
}

cashflow.view.php:

dataStore('test')); Table::create(array( "dataStore"=>$this->dataStore('usersloadsinvoices'), "showFooter"=>"bottom", "columns"=>array( 'showID'=>array( 'label'=>'Trip Number', I had created ever more complex queries in other reports with bind variables, but never used array datasource for Table. Not sure if this is the right way of using Sql and Array datasources together or not. But, you support in this would be highly appreciable. Thanks

Hi David,

I need data out of this query. However, it can't be var_dump as mentioned earlier that it gives error: SQLSTATE[HY000]: General error: 2031 (SQL: select loads.id, loads.show_id as showID, loads.created_at, invoices.show_id as invID, invoices.created_at as inv_date, name_addresses.company_name, IF(invoices.date_paid, "Yes", "No") AS paid from loads left join invoices on loads.invoice_id = invoices.id inner join name_addresses on name_addresses.id = loads.invoice_to_id where loads.template is null and loads.deleted_at is null and loads.org_id = :orgID and loads.status_id != :loadStatusId and loads.created_at Between :startDate and :endDate)

Although, when this query executed in MySql, with given parameter values, it executes and returns results.

To be precise, I have two parts to logic as below: Could you let me know how I use SQL Datasource first for first query which returns data that is used for some intermediate processing and stores result in an array ? Can this array result be returned to view using Array Datarource ? Thanks!

Thanks for your response.

Could you give example where koolreport sql datasource and array data source are accessed in setup, where sql data source will return array result and is passed on to array datasource which then sentds result to view. Thanks