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