public function fetchAll($params = array(), $wheres = array(), $page = null, $perPage = null)
{
$db = $this->db;
$query = $db::table($this->table);
// Search
foreach ($params as $field => $value) {
$exp = StringHelper::extractKeyWords($value);
foreach ($exp as $val) {
$query->orWhere($field, 'LIKE', "%" . $val . "%");
}
}
// Where's
foreach ($wheres as $field => $where) {
$query->where($field, "=", $where);
}
// Páginação
if (!is_null($page)) {
$query->limit($perPage)->offset($page);
}
// Order By
$query->orderBy("data_cadastro", "DESC");
// Obtem resultado (paginados ou não)
$fetch = $query->get();
// Obtem total de resultados
$count = $db::table($this->table)->count();
// Opções de páginação
if (!is_null($page)) {
if (count($fetch) == $perPage && ($page + 1) * $perPage < $count) {
$pagination['next_page'] = $page + 1;
} else {
$pagination['next_page'] = false;
}
if ((int) $page > 0) {
$pagination['prev_page'] = $page - 1;
} else {
$pagination['prev_page'] = false;
}
$pagination['first_page'] = 0;
$pagination['last_page'] = $count / $perPage - 1;
}
return array("data" => $fetch, "count" => $count, "pagination" => $pagination);
}
/**
* @param \Silex\Application $app
*/
protected function run(\Silex\Application $app)
{
$app->register(new \Silex\Provider\UrlGeneratorServiceProvider());
$this->startRequestTime = $this->getStartRequestTime();
$conn = new DataBaseHelper();
$db = $conn->autoConnect();
$query = $db::table($this->dbTable);
// Obtem todos os registros no banco
// Opicional: Passa paramentro "page" para obter informações paginadas
$app->get("/" . $this->uri, function (\Silex\Application $app, Request $request) use($db, $query) {
if (!is_null($request->get('page'))) {
$query->limit($this->perPage)->offset($request->get('page'));
}
$fetch = $query->get();
$total = $db::table($this->dbTable)->count();
$res = array();
$res['data'] = $fetch;
$res['total'] = $total;
if (!is_null($request->get('page'))) {
if (count($fetch) == $this->perPage && ($request->get('page') + 1) * $this->perPage < $total) {
$res['next_page'] = sprintf("%s://%s%s?page=%s", isset($_SERVER['HTTPS']) && $_SERVER['HTTPS'] != 'off' ? 'https' : 'http', $_SERVER['SERVER_NAME'], $app['url_generator']->generate($this->uri . "_fetch_all"), (int) $request->get('page') + 1);
}
if ((int) $request->get('page') > 0) {
$res['prev_page'] = sprintf("%s://%s%s?page=%s", isset($_SERVER['HTTPS']) && $_SERVER['HTTPS'] != 'off' ? 'https' : 'http', $_SERVER['SERVER_NAME'], $app['url_generator']->generate($this->uri . "_fetch_all"), (int) $request->get('page') - 1);
}
$res['first_page'] = sprintf("%s://%s%s?page=0", isset($_SERVER['HTTPS']) && $_SERVER['HTTPS'] != 'off' ? 'https' : 'http', $_SERVER['SERVER_NAME'], $app['url_generator']->generate($this->uri . "_fetch_all"));
$res['last_page'] = sprintf("%s://%s%s?page=%s", isset($_SERVER['HTTPS']) && $_SERVER['HTTPS'] != 'off' ? 'https' : 'http', $_SERVER['SERVER_NAME'], $app['url_generator']->generate($this->uri . "_fetch_all"), $total / $this->perPage - 1);
}
$res['time_request'] = $this->getFinishRequestTime($this->startRequestTime);
return $app->json($res);
})->bind($this->uri . "_fetch_all");
// Obtem registro via ID
// Obs: A chave primaria da tabela deve ter o nome "id"
$app->get("/" . $this->uri . "/{id}", function (\Silex\Application $app, $id) use($db, $query) {
$fetch = $query->find($id);
foreach ($this->foreignKey as $field => $table) {
if (isset($fetch->{$field})) {
$fetch->{$field} = $db::table($table)->find($fetch->{$field});
}
}
$res = array();
$res['data'] = $fetch;
$res['time_request'] = $this->getFinishRequestTime($this->startRequestTime);
return $app->json($res);
})->bind($this->uri . "_fetch_one");
// Insere os dados enviados no banco de dados
$app->post("/" . $this->uri, function (\Silex\Application $app, Request $request) use($db, $query) {
$post = $request->request->all();
$val = $this->runValidation($app, $post);
if ($val['error']) {
return $app->json($val);
}
$insert = $query->insert($val);
$res = array();
$res['data'] = $db::table($this->dbTable)->find($insert);
$res['insertID'] = $insert;
$res['time_request'] = $this->getFinishRequestTime($this->startRequestTime);
return $app->json($res);
})->bind($this->uri . "_insert");
// Atualiza os dados do banco
$app->put("/" . $this->uri . "/{id}", function (\Silex\Application $app, Request $request, $id) use($db, $query) {
$post = $request->request->all();
$val = $this->runValidation($app, $post);
if ($val['error']) {
return $app->json($val);
}
$query->where("id", $id)->update($val);
$res = array();
$res['data'] = $db::table($this->dbTable)->find($id);
$res['time_request'] = $this->getFinishRequestTime($this->startRequestTime);
return $app->json($res);
})->bind($this->uri . "_update");
// Apaga um registro do banco de dados
$app->delete("/" . $this->uri . "/{id}", function (\Silex\Application $app, $id) use($query) {
$query->where('id', $id)->delete();
$res = array();
$res['time_request'] = $this->getFinishRequestTime($this->startRequestTime);
return $app->json($res);
})->bind($this->uri . "_delete");
// Realiza uma busca no banco de dados na tabela especifica utilizando o método LIKE.
// Os parametros para busca devem ser passado via Query String. Ex: email=gmail&nome=kayo
$app->get("/search/" . $this->uri, function (\Silex\Application $app, Request $request) use($db, $query) {
$params = $request->query->all();
foreach ($params as $field => $value) {
if ($field == "limit") {
$query->limit($value);
} else {
$exp = StringHelper::extractKeyWords($value);
foreach ($exp as $val) {
$query->orWhere($field, 'LIKE', "%" . $val . "%");
}
}
}
$fetch = $query->get();
$total = $db::table($this->dbTable)->count();
$res = array();
$res['data'] = $fetch;
$res['total'] = $total;
$res['time_request'] = $this->getFinishRequestTime($this->startRequestTime);
return $app->json($res);
})->bind($this->uri . "_search");
}