PHP R::getAll方法代码示例

 public function index()
 {
     $perPage = 999999;
     $page = @$_GET['page'] ? $_GET['page'] : 1;
     $start = ($page - 1) * $perPage;
     $where = [];
     $where[] = 1;
     $queryParam = [];
     if (!empty($_GET['geo_id'])) {
         $where[] = "geo_id = ?";
         $queryParam[] = $_GET['geo_id'];
     }
     $where = implode(" AND ", $where);
     $queryParam[] = $start;
     $queryParam[] = $perPage;
     $items = R::getAll('SELECT * FROM contactdealer WHERE ' . $where . ' LIMIT ?,?', $queryParam);
     //$items = R::find('contactdealer', 'LIMIT ?,?', [$start, $perPage]);
     $count = R::count('contactdealer', $where, array_slice($queryParam, 0, -2));
     $maxPage = floor($count / $perPage) + ($count % $perPage == 0 ? 0 : 1);
     // $itemsExport = R::exportAll($items);
     $this->builds($items);
     header('Content-Type: application/json');
     echo json_encode(['items' => $items, 'page' => $page, 'maxPage' => $maxPage, 'total' => $count], JSON_UNESCAPED_SLASHES | JSON_UNESCAPED_UNICODE);
     exit;
 }

 public function indexpost()
 {
     $queryParam = [];
     $queryParam[] = $this->slim->request->post()['type'];
     $queryParam[] = $this->slim->request->post()['size'];
     $queryParam[] = $this->slim->request->post()['style'];
     $queryParam[] = $this->slim->request->post()['company'];
     $where = "type = ? AND size = ? AND style = ? AND company = ?";
     $items = R::getAll('SELECT product.* FROM product WHERE ' . $where . ' ORDER BY is_hot DESC, is_new DESC, created_at DESC', $queryParam);
     $this->builds($items);
     header('Content-Type: application/json');
     echo json_encode(['items' => $items], JSON_UNESCAPED_SLASHES | JSON_UNESCAPED_UNICODE);
     exit;
 }

 public function product()
 {
     $perPage = 10;
     $token = $this->slim->request->params('token');
     if (empty($token)) {
         header('Content-Type: application/json');
         echo json_encode(['error' => 'REQUIRE_AUTHORIZE'], JSON_UNESCAPED_SLASHES);
         exit;
     }
     $account = R::findOne("account", "token = ?", [$token]);
     if (!$account) {
         header('Content-Type: application/json');
         echo json_encode(['error' => 'AUTHORIZE_FAILED'], JSON_UNESCAPED_SLASHES);
         exit;
     }
     $page = @$_GET['page'] ? $_GET['page'] : 1;
     $start = ($page - 1) * $perPage;
     $where = [];
     $queryParam = [];
     $where[] = "account_product.account_id = ?";
     $queryParam[] = $account['id'];
     if (!empty($_GET['type'])) {
         $where[] = "product.type = ?";
         $queryParam[] = $_GET['type'];
     }
     if (!empty($_GET['size'])) {
         $where[] = "product.size = ?";
         $queryParam[] = $_GET['size'];
     }
     if (!empty($_GET['style'])) {
         $where[] = "product.style = ?";
         $queryParam[] = $_GET['style'];
     }
     if (!empty($_GET['company'])) {
         $where[] = "product.company = ?";
         $queryParam[] = $_GET['company'];
     }
     $where = implode(" AND ", $where);
     // $queryParam[] = $start;
     // $queryParam[] = $perPage;
     $items = R::getAll("SELECT product.* FROM account_product RIGHT JOIN product ON account_product.product_id = product.id WHERE " . $where . " LIMIT ?,?", array_merge($queryParam, [$start, $perPage]));
     $count = R::getCell("SELECT COUNT(product.id) FROM account_product RIGHT JOIN product ON account_product.product_id = product.id WHERE " . $where, $queryParam);
     $maxPage = floor($count / $perPage) + ($count % $perPage == 0 ? 0 : 1);
     //$itemsExport = R::exportAll($items);
     $itemsExport = $items;
     $this->buildProducts($itemsExport);
     header('Content-Type: application/json');
     echo json_encode(['items' => $itemsExport, 'page' => $page, 'maxPage' => $maxPage, 'total' => $count], JSON_UNESCAPED_SLASHES | JSON_UNESCAPED_UNICODE);
     exit;
 }

 public function index()
 {
     $perPage = 10;
     $page = @$_GET['page'] ? $_GET['page'] : 1;
     $start = ($page - 1) * $perPage;
     $where = [];
     $where[] = 1;
     $queryParam = [];
     if (!empty($_GET['type'])) {
         $where[] = "type = ?";
         $queryParam[] = $_GET['type'];
     }
     if (!empty($_GET['size'])) {
         $where[] = "size = ?";
         $queryParam[] = $_GET['size'];
     }
     if (!empty($_GET['style'])) {
         $where[] = "style = ?";
         $queryParam[] = $_GET['style'];
     }
     if (!empty($_GET['company'])) {
         $where[] = "company = ?";
         $queryParam[] = $_GET['company'];
     }
     $where = implode(" AND ", $where);
     $queryParam[] = $start;
     $queryParam[] = $perPage;
     $items = R::getAll('SELECT * FROM product WHERE ' . $where . ' ORDER BY is_hot DESC, is_new DESC, created_at DESC LIMIT ?,?', $queryParam);
     $count = R::count('product', $where, array_slice($queryParam, 0, -2));
     $maxPage = floor($count / $perPage) + ($count % $perPage == 0 ? 0 : 1);
     $form = [];
     $form["type"] = @$_GET["type"] ?: "";
     $form["size"] = @$_GET["size"] ?: "";
     $form["style"] = @$_GET["style"] ?: "";
     $form["company"] = @$_GET["company"] ?: "";
     $this->slim->render("product/list.php", ['items' => $items, 'page' => $page, 'maxPage' => $maxPage, 'form' => $form]);
 }

             $appForms = R::getAll($sql, $appFormIds);
             return $appForms;
         };
     };
     $c['csv_export_newapplication'] = function ($c) {
         return function () use($c) {
             $appFormIdsSql = 'SELECT id FROM applicationform WHERE (submitted) IN( SELECT MAX(submitted) FROM applicationform GROUP BY school_id)';
             $appFormIds = R::getCol($appFormIdsSql);
             if (empty($appFormIds)) {
                 return [];
             }
             $in = implode(',', array_fill(0, count($appFormIds), '?'));
             $settings = $c->get('settings');
             $version = $settings['application_form']['itemcategory']['currentversion'];
             $sql = 'SELECT applicationform.id AS id, ' . ' school.registry_no AS school_registry_no, ' . ' school.name AS school_name, ' . ' TRIM(itemcategory.name) AS category, ' . ' applicationformitem.qty AS qty, ' . ' applicationformitem.qtyacquired AS qtyacquired, ' . ' TRIM(applicationformitem.reasons) AS reasons ' . ' FROM applicationformitem ' . ' LEFT JOIN applicationform ON applicationformitem.applicationform_id = applicationform.id ' . ' LEFT JOIN school ON applicationform.school_id = school.id ' . ' LEFT JOIN itemcategory ON applicationformitem.itemcategory_id = itemcategory.id ' . ' LEFT JOIN lab ON applicationformitem.lab_id = lab.id ' . ' LEFT JOIN labtype ON lab.labtype_id = labtype.id ' . ' WHERE applicationform.id IN(' . $in . ')' . ' AND itemcategory.groupflag IN(' . $version . ')';
             $appForms = R::getAll($sql, $appFormIds);
             return $appForms;
         };
     };
 });
 $events('on', 'app.bootstrap', function (App $app, Container $c) {
     $app->get('/export/csv/edulabs_{type}.csv', function (Request $req, Response $res, array $args) use($c) {
         $type = $args['type'];
         try {
             $config = $c['csv_export_config'];
             if (!array_key_exists($type, $config)) {
                 return $res->withStatus(404);
             }
             $typeConfig = $config[$type];
             $csvResponse = $c['csv_export_csv_response'];
             return $csvResponse($res, $c[$typeConfig['data_callback']], $typeConfig['headers'], 'edulabs_' . $type . '.csv');

 /**
  */
 function getCumulativeResults()
 {
     $sql = "select country as name, sum( case when email != '' then 1 else 0 end) as quantity, sum( 1) as overall from quiz_user group by country";
     $data = R::getAll($sql);
     return $data;
 }

 public function edit($id)
 {
     $item = R::findOne('contactdealer', 'id=?', [$id]);
     $provinces = R::getAll('SELECT * FROM provinces');
     $this->slim->render("contactdealer/add.php", ['form' => new ContactdealerForm($item->export()), 'provinces' => $provinces]);
 }

    public function sheet()
    {
        $form = [];
        $where = [];
        $where[] = 1;
        $queryParam = [];
        if (!empty($_GET['type'])) {
            $where[] = "type = '{$_GET['type']}'";
        }
        if (!empty($_GET['size'])) {
            $where[] = "size = '{$_GET['size']}'";
        }
        if (!empty($_GET['style'])) {
            $where[] = "style = '{$_GET['style']}'";
        }
        if (!empty($_GET['company'])) {
            $where[] = "company = '{$_GET['company']}'";
        }
        $where = implode(" AND ", $where);
        $ids = "";
        $form["room"] = "";
        $sub = 'SELECT SUM(product_view.view_count) FROM product_view WHERE product_view.product_id = product.id';
        $sub2 = 'SELECT SUM(product_add.add_count) FROM product_add WHERE product_add.product_id = product.id';
        $sub3 = 'SELECT SUM(product_room.view_count) FROM product_room WHERE product_room.product_id = product.id';
        if (!empty($_GET["month"]) && !empty($_GET["year"])) {
            $sub .= " AND MONTH(product_view.view_date) = ?";
            $sub .= " AND YEAR(product_view.view_date) = ?";
            array_unshift($queryParam, (int) $_GET["month"], (int) $_GET["year"]);
            $sub2 .= " AND MONTH(product_add.add_date) = ?";
            $sub2 .= " AND YEAR(product_add.add_date) = ?";
            array_unshift($queryParam, (int) $_GET["month"], (int) $_GET["year"]);
            $sub3 .= " AND MONTH(product_room.view_date) = ?";
            $sub3 .= " AND YEAR(product_room.view_date) = ?";
            array_unshift($queryParam, (int) $_GET["month"], (int) $_GET["year"]);
        }
        if (empty($_GET["order"])) {
            $order = 'total_view';
        } else {
            $order = $_GET["order"];
        }
        $sql = 'SELECT *, (' . $sub3 . ') as total_room, (' . $sub . ') as total_view, (' . $sub2 . ') as total_add
			FROM product WHERE ' . $where . '  ORDER BY ' . $order . " LIMIT " . (@$_GET["limit"] ? $_GET["limit"] : 999999);
        $items = R::getAll($sql, $queryParam);
        $items = array_map(function ($item) {
            if (is_null($item["total_view"])) {
                $item["total_view"] = 0;
            }
            if (is_null($item["total_add"])) {
                $item["total_add"] = 0;
            }
            if (is_null($item["total_room"])) {
                $item["total_room"] = 0;
            }
            return $item;
        }, $items);
        $excel = new PHPExcel();
        $excel->getProperties()->setCreator("duragres - user");
        $excel->getProperties()->setLastModifiedBy("duragres - user");
        $excel->getProperties()->setTitle("duragres - user");
        $excel->getProperties()->setSubject("duragres - user");
        $excel->getProperties()->setDescription("duragres - user, report.");
        $excel->setActiveSheetIndex(0);
        $sheet = $excel->getActiveSheet();
        $sheet->setTitle("User Data");
        $sheet->SetCellValue('A' . '1', "#");
        $sheet->SetCellValue('B' . '1', "Name EN");
        $sheet->SetCellValue('C' . '1', "Name TH");
        $sheet->SetCellValue('D' . '1', "Type");
        $sheet->SetCellValue('E' . '1', "Style");
        $sheet->SetCellValue('F' . '1', "Size");
        $sheet->SetCellValue('G' . '1', "Unit");
        $sheet->SetCellValue('H' . '1', "Total Room");
        $sheet->SetCellValue('I' . '1', "Total View");
        $sheet->SetCellValue('J' . '1', "Total Add");
        $i = 1;
        foreach ($items as $item) {
            $i = $i + 1;
            $sheet->SetCellValue('A' . $i, $i - 1);
            $sheet->SetCellValue('B' . $i, $item["name_eng"]);
            $sheet->SetCellValue('C' . $i, $item["name"]);
            $sheet->SetCellValue('D' . $i, $item["type"]);
            $sheet->SetCellValue('E' . $i, $item["style"]);
            $sheet->SetCellValue('F' . $i, $item["size"]);
            $sheet->SetCellValue('G' . $i, $item["size_unit"]);
            $sheet->SetCellValue('H' . $i, $item["total_room"]);
            $sheet->SetCellValue('I' . $i, $item["total_view"]);
            $sheet->SetCellValue('J' . $i, $item["total_add"]);
        }
        $objWriter = PHPExcel_IOFactory::createWriter($excel, 'Excel5');
        // We'll be outputting an excel file
        header('Content-type: application/vnd.ms-excel');
        // It will be called file.xls
        header('Content-Disposition: attachment; filename="stat.xls"');
        // Write file to the browser
        $objWriter->save('php://output');
        exit;
    }

 /**
  * @param $sql
  * @param $params
  * @return array|int
  */
 private function execQuery($sql, $params)
 {
     return strtolower(substr($sql, 0, 6)) === 'select' ? new IteratorResult(R::getAll($sql, $params), 'redbean') : R::exec($sql, $params);
 }

 public static function getTags()
 {
     $tags = \RedBeanPHP\R::getAll('SELECT * FROM tags');
     return $tags;
 }

 private function check_key($key, $email)
 {
     if (!filter_var($email, FILTER_VALIDATE_EMAIL)) {
         return false;
     }
     $query = "SELECT *  FROM users INNER JOIN api_keys ON api_keys.user_id = users.id WHERE users.email = '{$email}'";
     //$this->pp($query, true);
     $user = R::getAll($query);
     if (!empty($user)) {
         return true;
     }
     return false;
 }

         return function () {
             $appFormIdsSql = 'SELECT applicationform.id ' . ' FROM applicationform ' . ' GROUP BY school_id ' . ' HAVING MAX(applicationform.submitted)';
             $appFormIds = R::getCol($appFormIdsSql);
             if (empty($appFormIds)) {
                 return [];
             }
             $in = implode(',', array_fill(0, count($appFormIds), '?'));
             $sql = 'SELECT applicationform.id AS id, ' . ' school.registry_no AS school_registry_no, ' . ' school.name AS school_name, ' . ' FROM_UNIXTIME(applicationform.submitted) AS submitted, ' . ' lab.id AS lab_id, ' . ' TRIM(labtype.name) AS lab_type, ' . ' IF(lab.is_new = 1, "ΝΑΙ", "ΟΧΙ") AS is_new, ' . ' TRIM(itemcategory.name) AS category, ' . ' applicationformitem.qty AS qty, ' . ' TRIM(applicationformitem.reasons) AS reasons ' . ' FROM applicationformitem ' . ' LEFT JOIN applicationform ON applicationformitem.applicationform_id = applicationform.id ' . ' LEFT JOIN school ON applicationform.school_id = school.id ' . ' LEFT JOIN itemcategory ON applicationformitem.itemcategory_id = itemcategory.id ' . ' LEFT JOIN lab ON applicationformitem.lab_id = lab.id ' . ' LEFT JOIN labtype ON lab.labtype_id = labtype.id ' . ' WHERE applicationform.id IN(' . $in . ') ';
             $appForms = R::getAll($sql, $appFormIds);
             return $appForms;
         };
     };
     $c['csv_export_software'] = function ($c) {
         return function () {
             $sql = 'SELECT softwarecategory.name AS name, ' . ' school.registry_no AS school_registry_no, ' . ' school.name AS school_name, ' . ' lab.id AS lab_id, ' . ' TRIM(labtype.name) AS lab_type, ' . ' TRIM(software.title) AS title, ' . ' TRIM(software.vendor) AS vendor, ' . ' TRIM(software.url) AS url ' . ' FROM software ' . ' LEFT JOIN softwarecategory ON software.softwarecategory_id = softwarecategory.id ' . ' LEFT JOIN school ON software.school_id = school.id ' . ' LEFT JOIN lab ON software.lab_id = lab.id ' . ' LEFT JOIN labtype ON lab.labtype_id = labtype.id ' . ' ORDER BY school_name ';
             $software = R::getAll($sql);
             $software = array_map(function ($row) {
                 $row['url'] = strtolower($row['url']);
                 $row['url'] = str_replace('\\', '/', $row['url']);
                 $row['url'] = urldecode($row['url']);
                 return $row;
             }, $software);
             return $software;
         };
     };
 });
 $events('on', 'app.bootstrap', function (App $app, Container $c) {
     $app->get('/export/csv/edulabs_{type}.csv', function (Request $req, Response $res, array $args) use($c) {
         $type = $args['type'];
         try {
             $config = $c['csv_export_config'];

 /**
  * Get List view data
  *
  * TODO: Sort by multiple fields?
  *
  * @param int $start
  * @param int $rowPerPage
  * @param string $keyword
  * @param string $sortField
  * @param null $sortOrder ASC/DESC
  * @return array List of beans
  * @throws \RedBeanPHP\RedException\SQL
  */
 protected function getListViewData($start = null, $rowPerPage = null, $keyword = null, $sortField = null, $sortOrder = null)
 {
     try {
         // Paging
         if ($start != null && $rowPerPage != null) {
             $limit = " LIMIT {$start},{$rowPerPage}";
         } else {
             $limit = "";
         }
         if ($this->sql != null) {
             // Custom SQL
             $list = [];
             $tempList = R::getAll($this->sql . $limit, $this->bindingData);
             // Array convert to object
             foreach ($tempList as $row) {
                 $list[] = (object) $row;
             }
         } else {
             $bindingData = $this->bindingData;
             // For Find All Clause
             if ($this->findAllClause != null) {
                 $findClause = " 1 = 1 " . $this->findAllClause;
             } else {
                 if ($this->findClause != null) {
                     $findClause = $this->findClause;
                 } else {
                     $findClause = " 1 = 1 ";
                 }
             }
             // Build a searching clause
             if ($keyword != null) {
                 $searchClause = $this->buildSearchingClause();
                 $searchData = $this->buildSearchingData($keyword);
                 $findClause = $searchClause . $findClause;
                 // Merge Array
                 $bindingData = $searchData + $bindingData;
             }
             // Sorting
             if ($sortField != null) {
                 $fakeSelect = "SELECT * FROM louislamcrud_fake_table WHERE ";
                 $parser = new Parser($fakeSelect . $findClause);
                 $sqlArray = $parser->parsed;
                 $sqlArray["ORDER"][0]["expr_type"] = "colref";
                 $sqlArray["ORDER"][0]["base_expr"] = $sortField;
                 $sqlArray["ORDER"][0]["sub_tree"] = null;
                 $sqlArray["ORDER"][0]["direction"] = $sortOrder;
                 $findClause = str_replace($fakeSelect, "", (new Creator($sqlArray))->created);
             }
             $list = R::find($this->tableName, $findClause . $limit, $bindingData);
         }
     } catch (\RedBeanPHP\RedException\SQL $ex) {
         throw $ex;
     } catch (\Exception $ex) {
         // TODO: This should be for not existing test only, not other exceptions.
         // If the table is not existing create one, create the table and run this function again.
         $this->createTable();
         return $this->getListViewData($start, $rowPerPage);
     }
     return $list;
 }

<?php

use Symfony\Component\DomCrawler\Crawler;
$app->get('/tag', function () use($app) {
    $pagenum = $_GET['page'];
    $pagesize = $_GET['size'];
    $offset = ($pagenum - 1) * $pagesize;
    $search = $_GET['search'];
    if ($search != "") {
        $where = "WHERE title LIKE '%" . $search . "%'";
    } else {
        $where = "";
    }
    $allTag = \RedBeanPHP\R::getAll("SELECT * FROM tags {$where} ORDER BY id DESC");
    $tags = \RedBeanPHP\R::getAll("SELECT * FROM tags {$where} ORDER BY id DESC LIMIT {$offset}, {$pagesize}");
    $myData = array('tags' => $tags, 'totalCount' => sizeof($allTag));
    echo json_encode($myData);
});
$app->post('/tag', function () use($app) {
    $postdata = json_decode(file_get_contents("php://input"));
    $now = date('Y-m-d H:i:s');
    $tags = \RedBeanPHP\R::dispense('tags');
    $tags->title = $postdata->title;
    $tags->created = $now;
    $tags->modified = $now;
    $tagID = \RedBeanPHP\R::store($tags);
    $tags = array('id' => $tagID, 'title' => $postdata->title, 'created' => $now);
    if ($tagID) {
        echo json_encode($tags);
    } else {
        echo json_encode(false);

 public function stat()
 {
     $perPage = 10;
     $page = @$_GET['page'] ? $_GET['page'] : 1;
     $start = ($page - 1) * $perPage;
     $where = [];
     $where[] = 1;
     $where = implode(" AND ", $where);
     $queryParam[] = $start;
     $queryParam[] = $perPage;
     $form = [];
     $form['room'] = '';
     $sub = 'SELECT SUM(product_room.view_count) FROM product_room WHERE product_room.product_id = product.id';
     $items = [];
     $count = 0;
     $maxPage = 0;
     if (!empty($_GET["room"])) {
         $form["room"] = $_GET["room"];
         $ids = R::getCol('SELECT product_id FROM product_room WHERE room_name = :room_name', [':room_name' => $_GET["room"]]);
         if (!empty($ids)) {
             $ids = array_unique($ids);
             $room = $_GET["room"];
             $sub .= " AND product_room.room_name = '{$room}' ";
             $where = ' id IN (' . R::genSlots($ids) . ')';
             $queryParam = array_merge($ids, $queryParam);
             $sql = 'SELECT *,(' . $sub . ') as total_room FROM product WHERE ' . $where . ' LIMIT ?,?';
             $items = R::getAll($sql, $queryParam);
             $count = R::count('product', $where, array_slice($queryParam, 0, -2));
             $maxPage = floor($count / $perPage) + ($count % $perPage == 0 ? 0 : 1);
         } else {
         }
     }
     $items = array_map(function ($item) {
         if (is_null($item["total_room"])) {
             $item["total_room"] = 0;
         }
         return $item;
     }, $items);
     $this->slim->render("room/stat_view.php", ['items' => $items, 'page' => $page, 'maxPage' => $maxPage, 'form' => $form]);
 }