Show results based on Session data

davidjmorin

I am trying to figure out how to show results based on session data IE: User = Location2 show locations 2 only results.

I have it working except that everyone sees everything. I am trying to get it so that users only see their data.
I normally do this with a where function in SQL but am unsure how to get it to work in datatables editor.

use
    DataTables\Editor,
    DataTables\Editor\Field,
    DataTables\Editor\Format,
    DataTables\Editor\Mjoin,
    DataTables\Editor\Options,
    DataTables\Editor\Upload,
    DataTables\Editor\Validate,
    DataTables\Editor\ValidateOptions;

    Editor::inst( $db, 'pending_dpa' )
        ->fields(
            Field::inst( 'pending_dpa.ID' ),
            Field::inst( 'pending_dpa.added' )
            ->validator( Validate::dateFormat( 'Y-m-d H:i' ) )
             ->getFormatter( Format::dateSqlToFormat( 'Y-m-d H:i:s' ) )
             ->setFormatter( Format::dateFormatToSql('Y-m-d H:i:s' ) ),
            Field::inst( 'pending_dpa.location_id' ),
            Field::inst( 'pending_dpa.location_name' ),
            Field::inst( 'pending_dpa.date' ),
            Field::inst( 'pending_dpa.acct_num' ),
            Field::inst( 'pending_dpa.mobile_num' ),
            Field::inst( 'pending_dpa.customer' ),
            Field::inst( 'pending_dpa.status' ),
            Field::inst( 'pending_dpa.amount' ),
            Field::inst( 'pending_dpa.type' ),
            Field::inst( 'pending_dpa.note' ),
            Field::inst( 'pending_dpa.modified' )
            ->validator( Validate::dateFormat( 'Y-m-d H:i' ) )
             ->getFormatter( Format::dateSqlToFormat( 'Y-m-d H:i:s' ) )
             ->setFormatter( Format::dateFormatToSql('Y-m-d H:i:s' ) ),
            Field::inst( 'locations.district' ),
            Field::inst( 'pending_dpa.corrected' )
            ->setFormatter( function ( $val, $data, $opts ) {
                return ! $val ? 0 : 1;
            } )

        )
    ->where( 'pending_dpa.corrected', 0, '=' )

    ->leftJoin( 'locations', 'locations.Outlet_ID', '=', 'pending_dpa.location_id' )

    ->debug( true )
        ->process( $_POST )
        ->json();

The session data would be like this $_SESSION['location'] = "District 2"
Any help is much appreciated.

davidjmorin

And here is my JS file.

(function($) {

    $(document).ready(function() {
        var editor = new $.fn.dataTable.Editor({
            ajax: 'php/table.pending_dpa.php',
            table: '#pending_dpa',
            fields: [{
                    label: "Notes:",
                    name: "pending_dpa.note"
                },
                {
                    label: "Completed:",
                    name: "pending_dpa.corrected",
                    type: "checkbox",
                    separator: "|",
                    options: [{
                        label: '',
                        value: 1
                    }]
                },

            ]
        });

    $(document).ready( function () {
  var table = $('#pending_dpa').DataTable({
        ajax: 'php/table.pending_dpa.php',
        dom: 'Bfrtip',
        select: true,
        pageLength: 10,
        lengthChange: true,
        scrollX: true,


        buttons: [{
                        extend: "edit",
                        text: "Update",
                        editor: editor
                },

                {
                        extend: "excelHtml5",
                        text: "Excel"
                },


        ],



        columns: [
      {
          data: "pending_dpa.location_id"
      },
      {
          data: "pending_dpa.location_name"
      },
      {
          data: "locations.district"
      },
      {
          data: "pending_dpa.date"
      },
      {
          data: "pending_dpa.acct_num"
      },
      {
          data: "pending_dpa.mobile_num"
      },
      {
          data: "pending_dpa.customer"
      },
      {
          data: "pending_dpa.status"
      },
      {
          data: "pending_dpa.amount"
      },
      {
          data: "pending_dpa.type"
      },
      {
          data: "pending_dpa.note"
      },
      {
          data: "pending_dpa.added"
      },
      {
          data: "pending_dpa.modified"
      },

        ],
  });
} );


    });

}(jQuery));

Lapointe

Hi @davidjmorin
Perhaps something like

use
    DataTables\Editor,
    DataTables\Editor\Field,
    DataTables\Editor\Format,
    DataTables\Editor\Mjoin,
    DataTables\Editor\Options,
    DataTables\Editor\Upload,
    DataTables\Editor\Validate,
    DataTables\Editor\ValidateOptions;

$location= $_SESSION['location'];

    Editor::inst( $db, 'pending_dpa' )
....
->where( function ( $q ) (use $location) {
    $q->where( 'pending_dpa.location_name', $location);
} );

see here

davidjmorin

@Lapointe Thank you for your post. I did look over that helpfile but couldn't figure that out. With your example I get an ajax error.

require '../../../vendor/autoload.php'; //Dont forget to edit this
include( "lib/DataTables.php" );
   ........

  $location = $_SESSION['district'];

    Editor::inst( $db, 'pending_dpa' )
        ->fields(
                        .................

        )


    ->where( 'pending_dpa.corrected', 0, '=' )
        ->where( function ( $q ) (use $location) {
            $q->where( 'pending_dpa.location_name', $location);
        } );
        ->leftJoin( 'locations', 'locations.Outlet_ID', '=', 'pending_dpa.location_id' )

    ->debug( true )
        ->process( $_POST )
        ->json();

If I remove this ->where( 'pending_dpa.corrected', 0, '=' ) ->where( function ( $q ) (use $location) { $q->where( 'locations.district', $location); } );
It works fine. So its something with this query.

davidjmorin

ok i see my error lol. I did not start the session in this PHP file.

I got this to work with the following.

<?php

session_start();
$location = $_SESSION['district'];

require '../../../vendor/autoload.php'; //Dont forget to edit this
include( "lib/DataTables.php" );

use
    DataTables\Editor,
    DataTables\Editor\Field,
    DataTables\Editor\Format,
    DataTables\Editor\Mjoin,
    DataTables\Editor\Options,
    DataTables\Editor\Upload,
    DataTables\Editor\Validate,
    DataTables\Editor\ValidateOptions;

    Editor::inst( $db, 'pending_dpa' )
        ->fields(
                            ....
        )


    ->where( 'pending_dpa.corrected', 0, '=' )
    ->where( 'locations.district',$location ) // This works for me. 


    ->leftJoin( 'locations', 'locations.Outlet_ID', '=', 'pending_dpa.location_id' )

    ->debug( true )
        ->process( $_POST )
        ->json();

Lapointe

If ok mark the question as answered please