Pagination and Filtering for OO PHP

AbstractController.php

<?php
namespace Sabel;

abstract class AbstractController
{
    protected $diContainer;

    public function __construct(DiContainer $diContainer){
        $this->diContainer = $diContainer;
    }
}

BUSINESS_PEDDLERS.php

<?php
namespace Sabel\Peddlers;

use Sabel\AbstractController;

class BusinessPeddlerController extends AbstractController{

    protected $aggregate;

    public function __construct(BusinessPeddlerAggregate $aggregate){
        $this->aggregate = $aggregate;
    }

    /**
     * Fetch all business peddlers
     *
     * @param $input
     * @return string
     */
    public function fetchAll($input = null){

        $request = CreateBusinessPeddlersRequest::fromRequest($input);

        $peddlers = $this->aggregate->fetchBusinessPeddlers($request);

        // Format output
        $response = [
            'data' => $this->formatOutput($peddlers['data']),
            'total' => $peddlers['total']
        ];

        // Return json array
        return json_encode($response);
    }

    private function formatOutput($output){
        $data = [];
        $i = 0;

        foreach ($output as $peddler){
            $data[$i]['id'] = $peddler['SVADRNO'];
            $data[$i]['name'] = $peddler['SVNAME'];
            $data[$i]['address'] = [
                'street' => $peddler['SVADDR1'].' '.$peddler['SVADDR2'],
                'street1' => $peddler['SVADDR1'],
                'street2' => $peddler['SVADDR2'],
                'city' => $peddler['SVCITY'],
                'state' => $peddler['SVSTATE'],
                'city-and-state' => $peddler['SVCITY'].', '.$peddler['SVSTATE'],
                'zip' => $peddler['SVZIPCODE'],
            ];
            $data[$i]['phone'] = $peddler['SVPHONE'];
            $data[$i]['dob'] = $peddler['SVBTHYEAR'].'-'.$peddler['SVBTHMON'].'-'.$peddler['SVBTHDAY'];
            $data[$i]['gender'] = $peddler['SVGENDER'];
            $data[$i]['taxId'] = $peddler['SVTAXID'];
            $data[$i]['ssn'] = $peddler['SVSSN'];
            $data[$i]['vehicle'] = [
                'type' => $peddler['SVVTYPE'],
                'make' => $peddler['SVVMAKE'],
                'description' => $peddler['SVVDESC'],
                'tag-number' => $peddler['SVTAG'],
                'tag-state' => $peddler['SVTAGST']
            ];
            $data[$i]['identification'] = [
                'id-number' => $peddler['SVDRLIC'],
                'alternate-id-number' => $peddler['SVIDENT'],
                'issue-date' => $peddler['SVISDTYEAR'].'-'.$peddler['SVISDTMON'].'-'.$peddler['SVISDTDAY'],
                'issuing-state' => $peddler['SVDRLST'],
                'id-class' => $peddler['SVCLASS'],
                'id-type' => $peddler['SVIDTYPE'],
                'expiration-date' => $peddler['SVDLEXPY'].'-'.$peddler['SVDLEXPM'].'-'.$peddler['SVDLEXPD'],
                'image' => $peddler['SVDLIMG'],
            ];
            $data[$i]['peddler-type'] = ($peddler['SVBUSNES'] === 'Y') ? 'business' : 'individual';
            $data[$i]['preferred-payment-method'] = ($peddler['SVCHKPRF'] === 'Y') ? 'check' : false;
            $data[$i]['rank'] = $peddler['SVABC'];

            $i++;
        }

        return $data;
    }
}<?php
require_once 'checkCredentials.php';

use Sabel\DiContainer;

$container = new DiContainer($dbObj);
$controller = $container['BusinessPeddlerController'];

echo $controller->fetchAll($_GET);

BusinessPeddlerAggregate.php

<?php
namespace Sabel\Peddlers;

use Sabel\Peddlers\PeddlersTable;

class BusinessPeddlerAggregate
{
    protected $peddlersTable;

    public function __construct(PeddlersTable $peddlersTable){
        $this->peddlersTable = $peddlersTable;
    }

    public function fetchBusinessPeddlers(CreateBusinessPeddlersRequest $input){

        $conditions['peddlerType'] = 'BUSINESS';

        return $this->peddlersTable->fetchBusinessPeddlers($input->getSortOrder(), $input->getSortColumn(), $input->getCount(), $input->getStart(), $input->getConditions());
    }
}

BusinessPeddlerController.php

<?php
namespace Sabel\Peddlers;

use Sabel\AbstractController;

class BusinessPeddlerController extends AbstractController{

    protected $aggregate;

    public function __construct(BusinessPeddlerAggregate $aggregate){
        $this->aggregate = $aggregate;
    }

    /**
     * Fetch all business peddlers
     *
     * @param $input
     * @return string
     */
    public function fetchAll($input = null){

        $request = CreateBusinessPeddlersRequest::fromHttpRequest($input);

        $peddlers = $this->aggregate->fetchBusinessPeddlers($request);

        // Format output
        $response = [
            'data' => $this->formatOutput($peddlers['data']),
            'total' => $peddlers['total']
        ];

        // Return json array
        return json_encode($response);
    }

    // TODO: move to mapper
    private function formatOutput($output){
        $data = [];
        $i = 0;

        foreach ($output as $peddler){
            $data[$i]['id'] = $peddler['SVADRNO'];
            $data[$i]['name'] = $peddler['SVNAME'];
            $data[$i]['address'] = [
                'street' => $peddler['SVADDR1'].' '.$peddler['SVADDR2'],
                'street1' => $peddler['SVADDR1'],
                'street2' => $peddler['SVADDR2'],
                'city' => $peddler['SVCITY'],
                'state' => $peddler['SVSTATE'],
                'city-and-state' => $peddler['SVCITY'].', '.$peddler['SVSTATE'],
                'zip' => $peddler['SVZIPCODE'],
            ];
            $data[$i]['phone'] = $peddler['SVPHONE'];
            $data[$i]['dob'] = $peddler['SVBTHYEAR'].'-'.$peddler['SVBTHMON'].'-'.$peddler['SVBTHDAY'];
            $data[$i]['gender'] = $peddler['SVGENDER'];
            $data[$i]['taxId'] = $peddler['SVTAXID'];
            $data[$i]['ssn'] = $peddler['SVSSN'];
            $data[$i]['vehicle'] = [
                'type' => $peddler['SVVTYPE'],
                'make' => $peddler['SVVMAKE'],
                'description' => $peddler['SVVDESC'],
                'tag-number' => $peddler['SVTAG'],
                'tag-state' => $peddler['SVTAGST']
            ];
            $data[$i]['identification'] = [
                'id-number' => $peddler['SVDRLIC'],
                'alternate-id-number' => $peddler['SVIDENT'],
                'issue-date' => $peddler['SVISDTYEAR'].'-'.$peddler['SVISDTMON'].'-'.$peddler['SVISDTDAY'],
                'issuing-state' => $peddler['SVDRLST'],
                'id-class' => $peddler['SVCLASS'],
                'id-type' => $peddler['SVIDTYPE'],
                'expiration-date' => $peddler['SVDLEXPY'].'-'.$peddler['SVDLEXPM'].'-'.$peddler['SVDLEXPD'],
                'image' => $peddler['SVDLIMG'],
            ];
            $data[$i]['peddler-type'] = ($peddler['SVBUSNES'] === 'Y') ? 'business' : 'individual';
            $data[$i]['preferred-payment-method'] = ($peddler['SVCHKPRF'] === 'Y') ? 'check' : false;
            $data[$i]['rank'] = $peddler['SVABC'];

            $i++;
        }

        return $data;
    }
}

checkCredentials.php

<?php
require __DIR__.'/../config/config.php';

use Sabel\Db\Db;

// define application constants
define('OLD_APPLICATION_PATH','/www/zendsvr6/htdocs/Scrap/');
define('IMAGES_PATH',OLD_APPLICATION_PATH.'IMG/');
define('HANDHELD_IMAGES_DIRECTORY', IMAGES_PATH.'Handheld/');
define('LOAD_IMAGES_DIRECTORY',IMAGES_PATH.'Loads/');
//define('APPLICATION_URL','http://dev-scrap.sabelsteel.com:10080');
define('APPLICATION_URL','http://10.0.2.2:10080/adam/scrap');
define('ID_DIRECTORY','/www/DRLIC/');

session_start();

// Set up autoloading
require_once __DIR__.'/../vendor/autoload.php';

if (array_key_exists('user',$_SESSION)){
	if (isset($_SESSION['user']) && !is_null($_SESSION['user'])){

	    $dbAlias = '*LOCAL';
	    $user = 'WEBUSER';
	    $password = 'WEBUSER';
	    $namingMode = DB2_I5_NAMING_ON;
	    $options = array(
	        'i5_naming' => $namingMode,
	        'i5_libl' => 'QS36F SSC',
	        'i5_lib' => 'QS36F'
	    );

		$db = db2_pconnect($dbAlias,$user,$password,$options);
		if (!$db) {
			header('Location:login.php');
			session_destroy();
			exit;
		}
		
		$dbObj = new Db($dbAlias, $user, $password,$options);

		$user = $_SESSION['user'];
		
		require_once 'ToolkitService.php';
		
		try{
			$toolkitObj = ToolkitService::getInstance($dbObj->getHandler(), $namingMode);
			$toolkitObj->setOptions(array('stateless'=>true));
			
			if ($user === 'ADAM'){
				$toolkitObj->setOptions(array('debugLogFile'=>'/usr/local/zendsvr6/var/log/chukdebug.log'));
			} 
		}

		catch (Exception $e) {
			echo  $e->getMessage(), "\n";
			exit();
		}
	} else {
		session_destroy();
		header("Location:login.php");
		exit;
	}
} else {
	session_destroy();
	header("Location:login.php");
	exit;
}
session_write_close();

config.php

<?php
$configArray = [
    'appVersion' => '1.2.1'
];

CreateBusinessPeddlersRequest.php

<?php
namespace Sabel\Peddlers;

class CreateBusinessPeddlersRequest
{
    protected $sortOrder;
    protected $sortColumn;
    protected $count;
    protected $page;
    protected $start;
    protected $conditions;

    private function __construct(){}

    public static function fromRequest($request)
    {
        $peddlersRequest = new CreateBusinessPeddlersRequest;
        $peddlersRequest->setSortColumn($request['sortColumn']);
        $peddlersRequest->setSortOrder($request['sortOrder']);
        $peddlersRequest->setCount($request['count']);
        $peddlersRequest->setPage($request['page']);
        $start = (isset($request['start']) ? $request['start'] : 1);
        $peddlersRequest->setStart($start);
        $where = (isset($request['where']) ? $request['where'] : []);
        $peddlersRequest->setConditions($where);

        return $peddlersRequest;
    }


    public static function fromHttpRequest($request){
/*        $options = new BusinessPeddlerFilterOptions;
        $options->setSortOrder($request['sortOrder']);
        $options->setSortColumn($request['sortColumn']);
        $options->setCount($request['count']);
        $options->setPage($request['page']);
        $options->setStart($request['start']);
        $options->setConditions($request['conditions']);

        return $options;*/
    }

    private function setSortColumn($sortColumn){
        $this->sortColumn = $sortColumn;
    }

    public function getSortColumn(){
        return $this->sortColumn;
    }

    private function setSortOrder($sortOrder){
        $this->sortOrder = $sortOrder;
    }

    public function getSortOrder(){
        return $this->sortOrder;
    }

    private function setCount($count){
        $this->count = $count;
    }

    public function getCount(){
        return $this->count;
    }

    private function setPage($page){
        $this->page = $page;
    }

    public function getPage(){
        return $this->page;
    }

    private function setStart($start){
        $this->start = $start;
    }

    public function getStart(){
        return $this->start;
    }

    private function setConditions($conditions){
        $this->conditions = $conditions;
    }

    public function getConditions(){
        return $this->conditions;
    }
}

Db.php

<?php
namespace Sabel\Db;

use Sabel\Utilities\ScrapFunctions;

class Db
{
    protected $handler;

    public function __construct($dbAlias,$user,$password,$options=array())
    {
        $this->handler = db2_pconnect($dbAlias,$user,$password,$options);
    }

    public function query($sql,$params)
    {
        // Type checking
        if (!is_string($sql))
        {
            throw new \Exception('SQL must be a string. Type '.gettype($sql).' was passed');
        }
        
        if (!is_array($params))
        {
            throw new \Exception('Params must be an array. Type '.gettype($params).' was passed');
        }
        
        $stmt = db2_prepare($this->handler,$sql);
        if (!$stmt){
            $scrapFunctions = new ScrapFunctions();
            $scrapFunctions->return_error("$sql<br />Params:".json_encode($params)."<br />Failed query:1".db2_stmt_error().":".db2_stmt_errormsg());
        }

        $result = db2_execute($stmt,$params);
        if (!$result){
            $scrapFunctions = new ScrapFunctions();
            $scrapFunctions->return_error("$sql<br />Params:".json_encode($params)."<br />Failed query:2".db2_stmt_error($stmt).":".db2_stmt_errormsg($stmt));
        }

        return $stmt;
    }

    public function getHandler()
    {
        return $this->handler;
    }
}

DiContainer.php

<?php
namespace Sabel;

use Pimple\Container;
use Sabel\Peddlers\BusinessPeddlerController;
use Sabel\Peddlers\BusinessPeddlerAggregate;
use Sabel\Peddlers\PeddlersTable;

class DiContainer extends Container
{
    protected $db;

    public function __construct($db)
    {
        parent::__construct();

        $this->db = $db;

        $this['PeddlersTable'] = function(DiContainer $container) {
            return new PeddlersTable($container['dbAdapter']);
        };

        $this['BusinessPeddlerAggregate'] = function(DiContainer $container) {
            return new BusinessPeddlerAggregate(
                $container['PeddlersTable']
            );
        };

        $this['BusinessPeddlerController'] = function(DiContainer $container){
            return new BusinessPeddlerController(
                $container['BusinessPeddlerAggregate']
            );
        };

        $this['dbAdapter'] = function (DiContainer $container) {
            return $this->db;
        };

    }
}

PeddlersTable.php

<?php
namespace Sabel\Peddlers;

use Sabel\AbstractGateway;

class PeddlersTable extends AbstractGateway
{
    protected $table = "SCF019";
    
    public function selectSample($rows)
    {
        $sql = "SELECT * FROM ".$this->table." FETCH FIRST $rows rows only";
        $params = array();
        $stmt = $this->db->query($sql,$params);
        $response = [];
        while ($row = db2_fetch_assoc($stmt))
        {
            $response[] = array_map('trim',$row);
        }
        
        return $response;
    }

    public function fetchBusinessPeddlers($sortOrder = 'ASC', $sortColumn = 'SVNAME', $howMany = 100, $start = 1, $conditions = array())
    {
        $sql = "SELECT COUNT(*) AS COUNT FROM ".$this->table." WHERE SVBUSNES='Y'";
        $stmt = $this->db->query($sql,array());
        $row = db2_fetch_assoc($stmt);
        $total = $row['COUNT'];
        $where = $this->convertConditionsToWhere($conditions);

        $end = $start + $howMany;
        $sql = "SELECT * FROM (".
            "SELECT SVADRNO, SVNAME, SVADDR1, SVADDR2, SVCITY, SVSTATE, SVZIPCODE, SVPHONE, SVBTHDAY, SVBTHMON, SVBTHYEAR, SVGENDER, SVTAXID, SVSSN, SVTAG, ".
            "SVIDENT, SVDRLST, SVISDTMON, SVISDTDAY, SVISDTYEAR, SVDRLIC, SVCLASS, SVIDTYPE, SVDLEXPY, SVDLEXPM, SVDLEXPD, SVDLIMG, SVBUSNES, SVCHKPRF, SVABC, ".
            "SVVTYPE, SVVMAKE, SVVDESC, SVTAGST, ROW_NUMBER() OVER (ORDER BY $sortColumn $sortOrder) as ROW_NUMBER FROM ".$this->table." WHERE SVBUSNES='Y') ".
            "PEDDLERS WHERE PEDDLERS.ROW_NUMBER >= $start AND PEDDLERS.ROW_NUMBER <= $end".$where;
        $params = array();
        $stmt = $this->db->query($sql,$params);
        $response = [];
        while ($row = db2_fetch_assoc($stmt))
        {
            $response[] = array_map('trim', $row);
        }

        return [
            'total' => $total,
            'data' => $response
        ];
    }

    private function convertConditionsToWhere($conditions)
    {
        $where = ' AND ';

        foreach ($conditions as $key => $condition){
            $where .= $key . '= %' . $condition['cont'] . '%';
        }

        var_dump($where);
        return $where;

    }
    
    public function selectById($id)
    {
        $sql = "SELECT * FROM ".$this->table." WHERE SVADRNO=?";
        $params = array($id);
        $stmt = $this->db->query($sql,$params);
        $row = db2_fetch_assoc($stmt);
        
        return array_map('trim',$row);
    }
    
    public function selectByIdNumber($idNumber)
    {
        $sql = "SELECT SVNAME FROM ".$this->table." WHERE SVDRLIC=?";
        $params = array($idNumber);
        $stmt = $this->db->query($sql,$params);
        $response = [];
        while ($row = db2_fetch_assoc($stmt))
        {
            $response[] = array_map('trim',$row);
        }
        
        return $response;
    }
    
    public function selectByName($name)
    {
        $sql = "SELECT * FROM ".$this->table." WHERE TRIM(SVNAME)=?";
        $params = array($name);
        $stmt = $this->db->query($sql,$params);
        $row = db2_fetch_assoc($stmt);
        
        return array_map('trim',$row);
    }
    
    public function getNumberOfPeddlersByIdNumber($idNumber)
    {
        // @todo remove trim() function from where clause
		$sql = "SELECT COUNT(*) AS COUNT FROM ".$this->table." WHERE TRIM(SVDRLIC)=?";
		$params = array($idNumber);
        $stmt = $this->db->query($sql,$params);
        $row = db2_fetch_assoc($stmt);
        
        return $row['COUNT'];
    }
    
    public function getNumberOfPeddlersByName($name)
    {
        // @todo remove trim() function from where clause
		$sql = "SELECT COUNT(*) AS COUNT FROM ".$this->table." WHERE TRIM(SVNAME)=?";
		$params = array($name);
        $stmt = $this->db->query($sql,$params);
        $row = db2_fetch_assoc($stmt);
    
        return $row['COUNT'];
    }
    
    public function getNumberOfPeddlersByNameOrIdNumber($name,$idNumber)
    {
        $sql = "SELECT COUNT(*) AS COUNT FROM ".$this->table." WHERE TRIM(SVNAME)=? OR SVDRLIC=?";
        $params = array($name,$idNumber);
        $stmt = $this->db->query($sql,$params);
        $row = db2_fetch_assoc($stmt);
        
        return (int) $row['COUNT'];
    }
    
    public function getIdByIdNumber($idNumber)
    {
        // @todo remove trim() function from where clause
        $sql = "SELECT SVADRNO FROM ".$this->table." WHERE TRIM(SVDRLIC)=?";
        $params = array($idNumber);
        $stmt = $this->db->query($sql,$params);
        $row = db2_fetch_assoc($stmt);
        
        return trim($row['SVADRNO']);
    }
    
    public function getIdByIdName($name)
    {
        // @todo remove trim() function from where clause
        $sql = "SELECT SVNAME FROM ".$this->table." WHERE TRIM(SVDRLIC)=?";
        $params = array($name);
        $stmt = $this->db->query($sql,$params);
        $row = db2_fetch_assoc($stmt);
    
        return trim($row['SVNAME']);
    }
    
    public function getLastPeddler()
    {
    	$sql = "SELECT * FROM ".$this->table." ORDER BY SVADRNO DESC FETCH FIRST ROW ONLY";
    	$params = array();
        $stmt = $this->db->query($sql,$params);
        $row = db2_fetch_assoc($stmt);
        
        return array_map('trim',$row);
    }
    
    public function searchByName($name)
    {
        $name = strtoupper($name);
        
        // @todo remove trim() function from where clause
		$sql = "SELECT * FROM ".$this->table." WHERE TRIM(SVNAME) LIKE (?) ORDER BY SVNAME";
		$params = array($name);
		$stmt = $this->db->query($sql,$params);
		$response = [];
		while ($row = db2_fetch_assoc($stmt))
		{
		    $response[] = array_map('trim',$row);
		}
		
		return $response;
    }
    
    public function insert(array $bind)
    {
        $sql = "INSERT INTO ".$this->table.
            " (SVADRNO,SVDLIMG,SVADDR1,SVADDR2,SVCITY,SVBTHYEAR,SVBTHMON,SVBTHDAY,SVDLEXPY,SVDLEXPM,SVDLEXPD,SVGENDER,SVCLASS,SVIDTYPE,SVISDTYEAR,SVISDTMON,SVISDTDAY,SVDRLIC,SVNAME,SVSTATE,SVZIPCODE,SVVTYPE,SVVMAKE,SVVDESC,SVBUSNES,SVCHKPRF,SVIDENT,SVPHONE,SVABC,SVSSN,SVTAGST,SVDRLST,SVTAG,SVTAXID) ".
			"VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
        $params = array(
            $bind['id'],
            $bind['image'],
            $bind['address1'],
            $bind['address2'],
            $bind['city'],
            $bind['birthYear'],
            $bind['birthMonth'],
            $bind['birthDay'],
            $bind['id_exp_year'],
            $bind['id_exp_month'],
            $bind['id_exp_day'],
            $bind['gender'],
            $bind['idClass'],
            $bind['idType'],
            $bind['id_iss_year'],
            $bind['id_iss_month'],
            $bind['id_iss_day'],
            $bind['idNumber'],
            $bind['name'],
            $bind['state'],
            $bind['zip'],
            $bind['vehicleType'],
            $bind['vehicleMake'],
            $bind['vehicleDescription'],
            $bind['business'],
            $bind['checkPreferred'],
            $bind['otherIdNumber'],
            $bind['phone'],
            $bind['rank'],
            $bind['ssn'],
            $bind['tagState'],
            $bind['idState'],
            $bind['tagNumber'],
            $bind['taxId']
        );
        if ($stmt = $this->db->query($sql,$params))
        {
            return true;
        } else {
            return false;
        }
    }
    
    public function update(array $bind)
    {
        $sql = "UPDATE ".$this->table." SET ";
        $params = array();
        $count = count($bind['data']);
        $i = 0;
        foreach ($bind['data'] as $key => $value)
        {
            if ($key != 'id') {
                $sql .= " ".$key."=?";
                $params[] = $value;
    
                if ($i < ($count-1))
                {
                    $sql .= ",";
                }
            }
    
            $i++;
        }
    
        $sql .= " WHERE ";
    
        $i = 0;
        $count = count($bind['key']);
        foreach ($bind['key'] as $key => $value)
        {
            $sql .= $key."=? ";
            $params[] = $value;
    
            if ($i < ($count-1))
            {
                $sql .= "AND ";
            }
    
            $i++;
        }
    
        if ($stmt = $this->db->query($sql,$params))
        {
            return true;
        } else {
            return false;
        }
    }
}