KoolReport's Forum

Official Support Area, Q&As, Discussions, Suggestions and Bug reports.
Forum's Guidelines

DataTable Server Side with conditional not working #2930

Closed Ahmed Bucheeri opened this topic on on Jan 5, 2023 - 3 comments

Ahmed Bucheeri commented on Jan 5, 2023

Here is my Setup file

<?php
require_once "../assets/koolreport_pro_6.0.6/core/autoload.php";

use \koolreport\KoolReport;
use \koolreport\querybuilder\DB;
use \koolreport\querybuilder\SQLServer;
use \koolreport\processes\CalculatedColumn;
use \koolreport\processes\ColumnMeta;

class store extends \koolreport\KoolReport {
  use \koolreport\inputs\Bindable;
  use \koolreport\inputs\POSTBinding;

  protected function defaultParamValues()
  {
      return array();
  }

  protected function bindParamsToInputs()
  {
      return array("selcategory");
  }

  public function settings()  {
    require_once "../maintenance/config.php";
    return $dataSources;
  }

  public function setup()  {
    $this->params['qry'] =
            "SELECT Stores.itemId
                    ,Stores.dId
                    ,Stores.loc
                    ,Stores.pn
                    ,Stores.altPn
                    ,Stores.descr
                    ,Stores.qty
                    ,Stores.minQty
                    ,Stores.info
                    ,Stores.cat
                    ,Stores.updatedby
                    ,Stores.updated
                    ,Stores.created
                    ,Stores.createdby
                    ,Stores.isDeleted
                FROM Stores
                WHERE isDeleted = 0" ;

    $this->src('sqlserver')->query(
      DB::table('Stores_itemCategory')
        ->where('IsDeleted', '=', 0)
        ->orderBy('name')
        ->toSQLServer())
        ->pipe($this->dataStore('category'));
   }
}
?>

View File
<?php
    use \koolreport\datagrid\DataTables;
    use \koolreport\widgets\koolphp\Table;
    use \koolreport\processes\CalculatedColumn;
    use \koolreport\inputs\Select;
    use \koolreport\processes\Filter;
    $excluded_columns = array();
    if ($_SESSION['UserType'] != 'admin') {
       $excluded_columns= [ array( "targets" => 10 , "visible" => false)];
    }

    print_object ($this->params['selcategory']);
?>
  <div class="container-sm">
    <h1 class="text-center m-3">Maintenance Stores</h1>
    <div class="row m-2">
      <div class="col-md-10">
        <form id="selectionform" name="selectionform" action='' class="col-md-9 p-0" method="post">
          <div class="row">
            <div class="col-sm-3">
              <label class="form-label">Resource</label>
              <?php
                Select::create(array(
                  "name"=>"selcategory",
                  'scope' => $this->params,
                  "dataStore"=>$this->dataStore("category"),
                  "defaultOption"=>array("All Category" => null ),
                  "dataBind"=>array(
                      "text"=>"name",
                      "value"=>"name",
                  ),
                  "clientEvents"=>array(
                    "change"=>"function(){
                    //    console.log( assignedDevice )
                      //  frm = document.getElementById('selectionform') ;
                    //    frm.action = 'index.php?action=snags&rid='+ selectResourceId.val()+'&statusid='+ selectStatus.val() ;
                        $('#selectionform').submit();
                    }",
                  ),
                  "attributes"=>array(
                    "class" => "form-select",
                  )
                ));
              ?>
            </div>
          </div>
        </form>
      </div>
      <div class="text-end pe-4">
        <button type="button" class= "addstoreItembtn btn btn-primary bi bi-gear-wide-connected m-1" > Add New Item</button>
      </div>
    </div>
    <?php
        DataTables::create(array(
          "name" => "storesTable",
          'scope' => $this->params['selcategory'],
          'dataSource' => function($scope) {
            return $this->src('sqlserver')
              ->query($this->params['qry']." AND cat LIKE '".$scope ."'")
              ->pipe(new CalculatedColumn(array(
                  "update"=>array(
                    "exp"=>function($data){
                        return (date( 'd-M-Y H:i', $data['updated']));
                    }
                  )
              )))
              ->pipe(new CalculatedColumn(array(
                  "editicon"=>array(
                    "exp"=>function($data){
                        return '<div itemid='. $data['itemId'] . ' class="edititem btn text-primary p-0" data-bs-toggle="tooltip" title="Edit Item">
                                  <i class="bi bi-pencil-square"></i>
                                </div>';
                    }
                  )
              )))
              ->pipe(new CalculatedColumn(array(
                  "deleteicon"=>array(
                    "exp"=>function($data){
                        return '<div itemid='. $data['itemId'] . ' class="deleteitem btn text-danger p-0" data-bs-toggle="tooltip" title="Deleted Item">
                                  <i class="bi bi-trash"></i>
                                </div>';
                    }
                  )
              )))
              ->pipe(new CalculatedColumn(array(
                  "minalert"=>array(
                    "exp"=>function($data){
                      if ($data['qty'] == 0) {
                        return '<div class="text-danger p-0 fs-4" data-bs-toggle="tooltip" title="No stock">
                                  <i class="bi bi-info-circle-fill"></i>
                                </div>';
                        } else if ( $data['qty'] <= $data['minQty'] ) {
                          return '<div class="text-warning p-0 fs-4" data-bs-toggle="tooltip" title="Stock low">
                                    <i class="bi bi-info-circle-fill"></i>
                                  </div>';
                        }
                        return null;
                    }
                  )
              )));
          },
          "themeBase" => "bs4",
          "serverSide" => true,
          "method"=>"post",
          "responsive" => true,
          "options"=>array(
            "fastRender" => true,
            "searching" => true,
            "ordering" => true,
            "paging" => true,
            "mark" => array("element"=>"mark" ,"separateWordSearch"=> false),
             "order" => array(3,"desc"),
            "lengthMenu" => [[15, 30, 50, 100, -1], [15, 30, 50, 100, "All"]],
            "columnDefs" => $excluded_columns,
          ),
           "columns"=>array(
              "minalert"=>array(
                "label" => "",
                "className" => "text-center",
                "searchable" => false,
                "orderable" => false,
              ),
              "dId"=>array(
                "label" => "Device",
              ),
              "cat"=>array(
                "label" => "Category",
              ),
              "descr"=>array(
                "label" => "Part Description",
              ),
              "pn"=>array(
                "label" => " Part Number",
              ),
              "qty"=>array(
                "label" => "Quantity",
                "searchable" => false,
                "orderable" => false,
              ),
              "minQty"=>array(
                "label" => "MinQuantity",
                "searchable" => false,
                "orderable" => false,
              ),
              "loc"=>array(
                "label" => "Location",
              ),
              "info"=>array(
                "label" => "Remarks",
              ),
              "editicon"=>array(
                "label" => "View/Edit",
                "className" => "text-center",
                "searchable" => false,
                "orderable" => false,
              ),
              "deleteicon"=>array(
                "label" => "",
                "className" => "text-center",
                "searchable" => false,
                "orderable" => false,
            )
          ),
          "cssClass"=>array(
            "table" => "table table-bordered table-striped align-middle small",
            "th" => "cssHeader"
          )
        ));
      ?>
  </div>

the Query return 0 row when I remove . " AND cat LIKE '".$scope ."'" it returns all row. I want to filter the table base on $this->params['selcategory'] selection

Can you Help me.

Ahmed Bucheeri commented on Jan 5, 2023

Note: when I change "serverSide" => true to false it works but slow to render the data,

Sebastian Morales commented on Jan 6, 2023

Pls try to set and use "scope" as an array like this:

          'scope' => array("selcategory" => $this->params['selcategory']), // change "scope" to an array
          'dataSource' => function($scope) {
            return $this->src('sqlserver')
              ->query($this->params['qry']." AND cat LIKE '".$scope["selcategory"] ."'") // use $scope as the above set array

Let us know if this works for you. Tks,

Ahmed Bucheeri commented on Jan 6, 2023

Yes, it works Thanks

Build Your Excellent Data Report

Let KoolReport help you to make great reports. It's free & open-source released under MIT license.

Download KoolReport View demo
None yet

DataGrid