示例#1
0
 public function fetchAll($adStatus = null, $searchString = null)
 {
     # use a closure to manipulate the Select object
     /*
        $active = 1;
        $resultSet = $this->tableGateway->select(
     	function (Select $select) use ($active) {
             //$select->columns(array('id', 'name', 'type')); 
             $select->order('id DESC'); 
             //$select->where(array('active'=> $active));
         });
     */
     // return $resultSet;
     $adapter = $this->tableGateway->getAdapter();
     $sql = new Sql($adapter);
     $select = $sql->select();
     $select->from('advertisement_logs');
     $select->columns(array('id', 'user_id', 'add_id', 'date', 'status', 'totalClick' => new \Zend\Db\Sql\Expression('SUM(advertisement_logs.click_count)'), 'totalView' => new \Zend\Db\Sql\Expression('SUM(advertisement_logs.view_count)')));
     $select->join('player', 'advertisement_logs.user_id = player.id ', array('name', 'email'), $type = self::JOIN_LEFT);
     //$select->join('player', 'advertisement_logs.user_id = player.id ' , array('name' ,'email'),$type = self::JOIN_LEFT);
     //$select->where(array('date'=> $active));
     if ($searchString != null) {
         $predicate = new \Zend\Db\Sql\Where();
         $str_arr = explode(" - ", $searchString);
         $startDate = date("Y-m-d", strtotime($str_arr[0]));
         $endDate = date("Y-m-d", strtotime($str_arr[1]));
         $select->where($predicate->greaterThanOrEqualTo('date', $startDate));
         $select->where($predicate->lessThanOrEqualTo('date', $endDate));
         //02/03/2015 - 02/18/2015
     }
     if ($adStatus != null) {
         $select->where(array('status' => $adStatus));
     }
     $select->order('date DESC');
     $select->group('add_id');
     $statement = $sql->prepareStatementForSqlObject($select);
     $rowset = $statement->execute();
     $resultSet = new ResultSet();
     $resultSet->initialize($rowset);
     return $resultSet;
 }