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;
}
