<?php

namespace RxConnexionBundle\Repository;

/**
 * InventorySerialNoRepository
 *
 * This class was generated by the Doctrine ORM. Add your own custom
 * repository methods below.
 */
class InventorySerialNoRepository extends \Doctrine\ORM\EntityRepository
{

    public function autoCompleteSuggestions($q, $userData) {

        $params = array(
            'search' => "%{$q['query']}%"
        );
        $andWhere = '';

        if(isSet($q['item_id']) && $q['item_id'] !== '') {
            $andWhere .= ' AND i.`item_id` = :item_id';
            $params['item_id'] = $q['item_id'];
        }

        if(isSet($q['status']) && $q['status'] !== 'all') {
            $andWhere .= ' AND isn.`status` = :status';
            $params['status'] = $q['status'];
        }



        if(isSet($q['include']) && !is_null($q['include']) && !empty($q['include'])) {
            $andWhere .= ' AND isn.`status` = :status';
            $params['status'] = $q['status'];
        }

        $sql = "
            SELECT
                isn.`id`,
                isn.serial_no AS data,
                isn.serial_no AS value
            FROM `inventory_serial_no` isn
            INNER JOIN inventory i on isn.inventory_id = i.id
            WHERE isn.serial_no LIKE :search
            $andWhere
            ORDER BY isn.`serial_no`
            LIMIT 0,20
        ";

        $query = $this->getEntityManager()->getConnection()->prepare($sql);

        $query->execute($params);

        $result = $query->fetchAll();

        return $result;
    }

}
