<?php
require_once '../jq-config.php';
// include the jqGrid Class
require_once ABSPATH."php/jqGrid.php";
// include the driver class
require_once ABSPATH."php/jqGridPdo.php";
// Connection to the server
$conn = new PDO(DB_DSN,DB_USER,DB_PASSWORD);
// Tell the db that we use utf-8
$conn->query("SET NAMES utf8");

// Create the jqGrid instance
$grid = new jqGridRender($conn);
// Write the SQL Query
$grid->SelectCommand = "select    A.email ,
                                  A.first_name ,
                                  A.last_name ,
                                  A.dob ,
                                  B.uid ,
                                  B.profile_name ,
                                  B.rating ,
                                  B.status
                           from B join
                                A
                           on
                                B.uid = A.uid";
// Set the table to where you add the data
$grid->table = 'B';
$grid->setPrimaryKeyId('uid');
// Set output format to json
$grid->dataType = 'json';
// Let the grid create the model
$grid->setColModel();

// Set the url from where we obtain the data
$grid->setUrl('grid.php');
$grid->addCol(array(
    "name"=>"actions",
    "formatter"=>"actions",
    "editable"=>false,
    "sortable"=>false,
    "resizable"=>true,
    "fixed"=>false,
    "width"=>100,
    "formatoptions"=>array("keys"=>true)
    ), "first");
$grid->setColProperty('uid', array('editable'=>false, 'label'=>"Reader ID", 'search'=>false));
$grid->setColProperty('email', array('editable'=>true, 'label'=>"Reader Email"));
$grid->setColProperty('first_name', array('label'=>"Reader First Name",'search'=>true));
$grid->setColProperty('last_name', array('label'=>"Reader Last Name"));
$grid->setColProperty('profile_name', array('label'=>"Reader Profile Name"));
$grid->setColProperty('rating', array('label'=>"Reader rating"));
$grid->setColProperty('dob',
        array("formatter"=>"date","formatoptions"=>array("srcformat"=>"Y-m-d H:i:s", "newformat"=>"Y-m-d"), "label"=>"Reader DoB"));
$statuValue = array("approved"=>"Approved", "disabled"=>"Disabled", "created"=>"Created");
$grid->setColProperty('status', array('edittype'=>'select', 'label'=>"Reader Status"));
$grid->setSelect('status', $statuValue, false, true, false, array("approved"=> "Approved"));
// This command is executed after edit
$cid = jqGridUtils::GetParam('uid');
$firstName = jqGridUtils::GetParam('first_name');
$lastName = jqGridUtils::GetParam('last_name');
$email = jqGridUtils::GetParam('email');
$dateOfBirth = jqGridUtils::GetParam('dob');
// This command is executed immediatley after edit occur.
$grid->setAfterCrudAction('edit', "update A set A.first_name=?,
                                                           A.last_name=?,
                                                           A.email=?,
                                                           A.dob=?
                                                           where A.uid=?",
                                                           array($firstName,$lastName,$email,$dateOfBirth,$cid));

$grid->setGridOptions(array(
    "rowNum"=>100,
    "rowList"=>array(100,150,200),
    "sortname"=>"B.uid",
    "width"=>1200,
    "height"=>400
));

$grid->toolbarfilter=true;
$grid->setFilterOptions(array("stringResult"=>true));

$grid->setColProperty('first_name',array("searchoptions"=>array("sopt"=>array("cn"))));

$grid->renderGrid('#grid','#pager',true, null, null, true,true);
$conn = null;
?>