<?php
/*
 * Developer : John D'Alto (jdalto@yahoo.com)
 * Date : 9/1/2012
 * All code (c)2012 JLS Consulting, all rights reserved
 */

namespace Dorcas\TabithaBundle\Repository;

use Doctrine\ORM\EntityRepository;
use Doctrine\ORM\Query\Expr;

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

  /**
   * @param array $get
   * @param bool $flag
   * @return array|\Doctrine\ORM\Query
   */
  public function clientSearch(array $get, $flag = false, $numrec = false){

    /* Indexed column (used for fast and accurate table cardinality) */
    $alias = 'a';

    /* DB table to use */
    $tableObjectName = 'DorcasTabithaBundle:Client';

    /**
     * Set to default
     */
    if(!isset($get['columns']) || empty($get['columns']))
      $get['columns'] = array('id');

    $aColumns = array();
    foreach($get['columns'] as $value) $aColumns[] = $alias .'.'. $value;

    if ($numrec)
    {
        $cb = $this->getEntityManager()
        ->getRepository($tableObjectName)
        ->createQueryBuilder($alias)
    //      ->select(str_replace(" , ", " ", implode(", ", $aColumns)));
        ->select("COUNT($alias)");
    }
    else
    {
        $cb = $this->getEntityManager()
        ->getRepository($tableObjectName)
        ->createQueryBuilder($alias)
        ->select(str_replace(" , ", " ", implode(", ", $aColumns)));
//        ->select("COUNT($alias)");
    }

    if ( isset( $get['iDisplayStart'] ) && $get['iDisplayLength'] != '-1' ){
      $cb->setFirstResult( (int)$get['iDisplayStart'] )
        ->setMaxResults( (int)$get['iDisplayLength'] );
    }

    /*
     * Ordering
     */
    if ( isset( $get['iSortCol_0'] ) ){
      for ( $i=0 ; $i<intval( $get['iSortingCols'] ) ; $i++ ){
        if ( $get[ 'bSortable_'.intval($get['iSortCol_'.$i]) ] == "true" ){
          $cb->orderBy($aColumns[ (int)$get['iSortCol_'.$i] ], $get['sSortDir_'.$i]);
        }
      }
    }

//    $cb->orderBy($aColumns[ (int)$get['iSortCol_'.$i] ], $get['sSortDir_'.$i]);
    $cb->orderBy('a.last_name, a.first_name', 'ASC');
    /*
       * Filtering
       * NOTE this does not match the built-in DataTables filtering which does it
       * word by word on any field. It's possible to do here, but concerned about efficiency
       * on very large tables, and MySQL's regex functionality is very limited
       */
    
    $aLike = array();
    $addressLike = array();
    $firstnameLike = array();
    $lastnameLike = array();
    
    for ( $i=0 ; $i<count($aColumns) ; $i++ ){
    
    if ( isset($get['sSearch_' . $i]) && $get['sSearch_' . $i] != '' ){
     // $aLike = array();
    //  for ( $i=0 ; $i<count($aColumns) ; $i++ ){
        if ( isset($get['bSearchable_'.$i]) && $get['bSearchable_'.$i] == "true" ){
            $searchstring = $get['sSearch_' . $i];
            
            // handle apostrophes by replacing ' with ''
            $searchstring = str_replace("'", "''", $searchstring);
            
            // handle * to return any fields that contain data
            if($searchstring == '*') $searchstring = NULL;
            
            $likestring = '\'%'. $searchstring .'%\'';
//            $correctwaytoenterquotes = '\'%'. 'D\'' . "'" . 'Alto' .'%\'';         

         // $aLike[] = $cb->expr()->like($aColumns[$i], '\'%'. mysql_real_escape_string($get['sSearch_' . $i]) .'%\'');
            
            // Handle NULL searches - must use NULL in search field
            if ($searchstring == 'NULL') 
            {
                $aLike[] = $cb->expr()->isNull($aColumns[$i]);
            }
            // Handle NOTNULL searches - must use NOTNULL in search field
            elseif ($searchstring == 'NOTNULL') 
            {
                $aLike[] = $cb->expr()->isNotNull($aColumns[$i]);
            }
            // Handle Current Address + Previous Address combined OR search
            elseif ($aColumns[$i] == 'a.current_address')
            {
                $addressLike[] = $cb->expr()->like($aColumns[$i], $likestring);
                $addressLike[] = $cb->expr()->like($aColumns[$i+1], $likestring);
            }
             // Handle First Name + Alias + Other First Name One and Two - Four way search
           elseif ($aColumns[$i] == 'a.first_name')
            {
                $firstnameLike[] = $cb->expr()->like($aColumns[$i], $likestring);
                $firstnameLike[] = $cb->expr()->like($aColumns[$i+1], $likestring); 
                $firstnameLike[] = $cb->expr()->like($aColumns[$i+5], $likestring);
                $firstnameLike[] = $cb->expr()->like($aColumns[$i+7], $likestring);
            }
            // Handle Last Name + Alias + Other Last Name One and Two - Four way search
            elseif ($aColumns[$i] == 'a.last_name')
            {
                $lastnameLike[] = $cb->expr()->like($aColumns[$i], $likestring);
                $lastnameLike[] = $cb->expr()->like($aColumns[$i+2], $likestring); 
                $lastnameLike[] = $cb->expr()->like($aColumns[$i+5], $likestring);
                $lastnameLike[] = $cb->expr()->like($aColumns[$i+7], $likestring);
            }
            
            else
            {
                // Normal single field LIKE search
                $aLike[] = $cb->expr()->like($aColumns[$i], $likestring);
            }
        }
      }
    }
      if(count($aLike) > 0 || count($addressLike) > 0 || count($firstnameLike) > 0 || count($lastnameLike) > 0) 
      {
          // Use to OR fields
//          $cb->andWhere(new Expr\Orx($aLike));
          // Use to AND fields
          
          if(count($aLike) > 0 )$cb->orWhere(new Expr\Andx($aLike));
          // Added to handle Current Address + Previous Address OR search
          if(count($addressLike) > 0 ) $cb->andWhere(new Expr\Orx($addressLike));
          // Added to handle First Name four way OR search
          if(count($firstnameLike) > 0 ) $cb->andWhere(new Expr\Orx($firstnameLike));
          // Added to handle Last Name four way OR search
          if(count($lastnameLike) > 0 ) $cb->andWhere(new Expr\Orx($lastnameLike));
          // Added AND WHERE is_active = 1
          $cb->andWhere($aColumns[0] . ' = 1');
      }
      elseif(count($aLike) == 0)
      {
          // Added AND WHERE is_active = 1
          $cb->andWhere($aColumns[0] . ' = 1');
          unset($aLike);
     }

    /*
     * SQL queries
     * Get data to display
     */
    $query = $cb->getQuery();

    if($flag)
      return $query;
    else
      return $query->getResult();
  }

  /**
   * @return int
   */
  public function getCount(){
    $aResultTotal = $this->getEntityManager()
      ->createQuery('SELECT COUNT(a) FROM DorcasTabithaBundle:Client a')
      ->setMaxResults(1)
      ->getResult();
     return $aResultTotal[0][1];
  }

    public function getNotesForClient($clientId)
    {
        $qb = $this->createQueryBuilder('note')
                   ->select('note')
                   ->where('note.client = :client_id')
                   ->addOrderBy('note.modified_dt', 'DESC')
                   ->setParameter('client_id', $clientId);

        return $qb->getQuery()
                  ->getResult();
    }
    
    public function getFinanceForClient($clientId)
    {
        $qb = $this->createQueryBuilder('finance')
                   ->select('finance')
                   ->where('finance.client = :client_id')
                   ->setParameter('client_id', $clientId);

        return $qb->getQuery()
                  ->getResult();
    }

        public function getAssistancesForClient($clientId)
    {
        $qb = $this->createQueryBuilder('assistance')
                   ->select('assistance')
                   ->where('assistance.client = :client_id')
                   ->addOrderBy('assistance.checks_issued_date', 'DESC')
                   ->setParameter('client_id', $clientId);

        return $qb->getQuery()
                  ->getResult();
    }
    
        public function getUsers()
    {
        $qb = $this->createQueryBuilder('user')
                   ->select('user')
                    ->where('user.isActive = 1')
                   ->addOrderBy('user.username', 'ASC');

        return $qb->getQuery()
                  ->getResult();
    }
    
            public function isAssistanceDuplicate($clientId, $payee)
    {
        $qb = $this->createQueryBuilder('assistance')
                   ->select('assistance')
//                   ->where('assistance.client = :client_id')
                ->where('assistance.checks_issued_payee = :checksissuedpayee')
                   ->addOrderBy('assistance.checks_issued_date', 'DESC')
//                   ->setParameter('client_id', $clientId)               
                    ->setParameter('checksissuedpayee', $payee);

        return $qb->getQuery()
                  ->getResult();
    }
}