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