public function get($id)
{
return queryOne('
select
*
from
' . self::$TABELA_PESSOA . '
where
id=?', array($id));
}
protected function onValidate()
{
if ($this->ac == "set") {
if (issetval("status")) {
$status = $_POST["status"];
if ($status == "RE" || $status == "CA") {
$oldStatus = queryOne("SELECT status FROM Ordr WHERE id={$this->id}");
if ($oldStatus != "CR") {
throw new MyException(E_FORBIDDEN, "forbidden to change status to {$status}");
}
$this->onAfterActions[] = function () use($status) {
$orderId = $this->id;
$empId = $_SESSION["empId"];
$sql = sprintf("INSERT INTO OrderLog (orderId, action, tm, empId) VALUES ({$orderId},'{$status}','%s', {$empId})", date(FMT_DT));
execOne($sql);
};
} else {
throw new MyException(E_FORBIDDEN, "forbidden to change status to {$_POST['status']}");
}
}
}
}
/**
@fn tableCRUD($ac, $tbl, $asAdmin?=false)
对象型接口的入口。
也可直接被调用，常与setParam一起使用, 提供一些定制的操作。
@param $asAdmin 默认根据用户身份自动选择"AC_"类; 如果为true, 则以超级管理员身份调用，即使用"AC0_"类。
设置$asAdmin=true好处是对于超级管理员权限来说，即使未定义"AC0_"类，默认也可以访问所有内容。
假如有Rating（订单评价）对象，不想通过对象型接口来查询，而是通过函数型接口来定制输出，接口设计为：
queryRating(storeId, cond?) -> tbl(id, score, dscr, tm, orderDscr)
查询店铺storeId的订单评价。
应用逻辑：
- 按时间tm倒排序
底层利用tableCRUD实现它，这样便于保留分页、参数cond/gres等特性:
function api_queryRating()
{
$storeId = mparam("storeId");
// 定死输出内容。
setParam("res", "id, score, dscr, tm, orderDscr");
// 相当于AccessControl框架中调用 addCond，用Obj.query接口的内部参数cond2以保证用户还可以使用cond参数。
setParam("cond2", ["o.storeId=$storeId"]);
// 定死排序条件
setParam("orderby", "tm DESC");
$ret = tableCRUD("query", "Rating", true);
return $ret;
}
注意：
- 以上示例中的设计不可取，应使用标准对象接口来实现这个需求。
@see setParam
*/
function tableCRUD($ac1, $tbl, $asAdmin = false)
{
$accessCtl = AccessControl::create($tbl, $asAdmin);
$accessCtl->before($ac1);
$tbl = $accessCtl->getTable();
$ignoreAfter = false;
if ($ac1 == "add") {
$keys = '';
$values = '';
# var_dump($_POST);
$id = $accessCtl->genId();
if ($id != 0) {
$keys = "id";
$values = (string) $id;
}
foreach ($_POST as $k => $v) {
$k = htmlEscape($k);
if ($k === "id") {
continue;
}
// ignore non-field param
if (substr($k, 0, 2) === "p_") {
continue;
}
if ($v === "") {
continue;
}
# TODO: check meta
if (!preg_match('/^\\w+$/', $k)) {
throw new MyException(E_PARAM, "bad key {$k}");
}
if ($keys !== '') {
$keys .= ", ";
$values .= ", ";
}
$keys .= $k;
$values .= Q(htmlEscape($v));
}
if (strlen($keys) == 0) {
throw new MyException(E_PARAM, "no field found to be added");
}
$sql = sprintf("INSERT INTO %s (%s) VALUES (%s)", $tbl, $keys, $values);
# var_dump($sql);
$id = execOne($sql, true);
$res = param("res");
if (isset($res)) {
setParam("id", $id);
$ret = tableCRUD("get", $tbl);
} else {
$ret = $id;
}
} elseif ($ac1 == "set") {
$id = mparam("id", $_GET);
$kv = "";
foreach ($_POST as $k => $v) {
$k = htmlEscape($k);
if ($k === 'id') {
continue;
}
// ignore non-field param
if (substr($k, 0, 2) === "p_") {
continue;
}
# TODO: check meta
if (!preg_match('/^\\w+$/', $k)) {
throw new MyException(E_PARAM, "bad key {$k}");
}
if ($kv !== '') {
$kv .= ", ";
}
// 空串或null置空；empty设置空字符串
if ($v === "" || $v === "null") {
$kv .= "{$k}=null";
} else {
if ($v === "empty") {
$kv .= "{$k}=''";
} else {
if (startsWith($k, "flag_") || startsWith($k, "prop_")) {
$kv .= flag_getExpForSet($k, $v);
} else {
$kv .= "{$k}=" . Q(htmlEscape($v));
}
}
}
}
if (strlen($kv) == 0) {
addLog("no field found to be set");
} else {
$sql = sprintf("UPDATE %s SET %s WHERE id=%d", $tbl, $kv, $id);
$cnt = execOne($sql);
}
$ret = "OK";
} elseif ($ac1 === "get" || $ac1 === "query") {
$forGet = $ac1 === "get";
$wantArray = param("wantArray/b");
$sqlConf = $accessCtl->sqlConf;
$enablePaging = true;
if ($forGet || $wantArray) {
$enablePaging = false;
}
if ($forGet) {
$id = mparam("id");
array_unshift($sqlConf["cond"], "t0.id={$id}");
} else {
$pagesz = param("_pagesz/i");
$pagekey = param("_pagekey/i");
// support jquery-easyui
if (!isset($pagesz) && !isset($pagekey)) {
$pagesz = param("rows/i");
$pagekey = param("page/i");
if (isset($pagekey)) {
$enableTotalCnt = true;
$enablePartialQuery = false;
}
}
if ($pagesz == 0) {
$pagesz = 20;
}
$maxPageSz = min($accessCtl->getMaxPageSz(), PAGE_SZ_LIMIT);
if ($pagesz < 0 || $pagesz > $maxPageSz) {
$pagesz = $maxPageSz;
}
if (isset($sqlConf["gres"])) {
$enablePartialQuery = false;
}
}
$orderSql = $sqlConf["orderby"];
// setup cond for partialQuery
if ($enablePaging) {
if ($orderSql == null) {
$orderSql = $accessCtl->getDefaultSort();
}
if (!isset($enableTotalCnt)) {
$enableTotalCnt = false;
if ($pagekey === 0) {
$enableTotalCnt = true;
}
}
// 如果未指定orderby或只用了id(以后可放宽到唯一性字段), 则可以用partialQuery机制(性能更好更精准), _pagekey表示该字段的最后值；否则_pagekey表示下一页页码。
if (!isset($enablePartialQuery)) {
$enablePartialQuery = false;
if (preg_match('/^(t0\\.)?id\\b/', $orderSql)) {
$enablePartialQuery = true;
if ($pagekey) {
if (preg_match('/\\bid DESC/i', $orderSql)) {
$partialQueryCond = "t0.id<{$pagekey}";
} else {
$partialQueryCond = "t0.id>{$pagekey}";
}
// setup res for partialQuery
if ($partialQueryCond) {
// if (isset($sqlConf["res"][0]) && !preg_match('/\bid\b/',$sqlConf["res"][0])) {
// array_unshift($sqlConf["res"], "t0.id");
// }
array_unshift($sqlConf["cond"], $partialQueryCond);
}
}
}
}
if (!$pagekey) {
$pagekey = 1;
}
}
if (!isset($sqlConf["res"][0])) {
$sqlConf["res"][0] = "t0.*";
} else {
if ($sqlConf["res"][0] === "") {
array_shift($sqlConf["res"]);
}
}
$resSql = join(",", $sqlConf["res"]);
if ($resSql == "") {
$resSql = "t0.id";
}
if (@$sqlConf["distinct"]) {
$resSql = "DISTINCT {$resSql}";
}
$tblSql = "{$tbl} t0";
if (count($sqlConf["join"]) > 0) {
$tblSql .= "\n" . join("\n", $sqlConf["join"]);
}
$condSql = "";
foreach ($sqlConf["cond"] as $cond) {
if ($cond == null) {
continue;
}
if (strlen($condSql) > 0) {
$condSql .= " AND ";
}
if (stripos($cond, " and ") !== false || stripos($cond, " or ") !== false) {
$condSql .= "({$cond})";
} else {
$condSql .= $cond;
}
}
/*
foreach ($_POST as $k=>$v) {
# skip sys param which generally starts with "_"
if (substr($k, 0, 1) === "_")
continue;
# TODO: check meta
if (! preg_match('/^\w+$/', $k))
throw new MyException(E_PARAM, "bad key $k");
if ($condSql !== '') {
$condSql .= " AND ";
}
$condSql .= KVtoCond($k, $v);
}
*/
$sql = "SELECT {$resSql} FROM {$tblSql}";
if ($condSql) {
flag_handleCond($condSql);
$sql .= "\nWHERE {$condSql}";
}
if (isset($sqlConf["union"])) {
$sql .= "\nUNION\n" . $sqlConf["union"];
}
if ($sqlConf["gres"]) {
$sql .= "\nGROUP BY {$sqlConf['gres']}";
}
if ($orderSql) {
$sql .= "\nORDER BY " . $orderSql;
}
if ($enablePaging) {
if ($enableTotalCnt) {
$cntSql = "SELECT COUNT(*) FROM {$tblSql}";
if ($condSql) {
$cntSql .= "\nWHERE {$condSql}";
}
$totalCnt = queryOne($cntSql);
}
if ($enablePartialQuery) {
$sql .= "\nLIMIT " . $pagesz;
} else {
$sql .= "\nLIMIT " . ($pagekey - 1) * $pagesz . "," . $pagesz;
}
} else {
if ($pagesz) {
$sql .= "\nLIMIT " . $pagesz;
}
}
if ($forGet) {
$ret = queryOne($sql, PDO::FETCH_ASSOC);
if ($ret === false) {
throw new MyException(E_PARAM, "not found `{$tbl}.id`=`{$id}`");
}
handleSubObj($sqlConf["subobj"], $id, $ret);
} else {
$ret = queryAll($sql, PDO::FETCH_ASSOC);
if ($ret === false) {
$ret = [];
}
if ($wantArray) {
foreach ($ret as &$mainObj) {
$id1 = $mainObj["id"];
handleSubObj($sqlConf["subobj"], $id1, $mainObj);
}
} else {
// Note: colCnt may be changed in after().
$fixedColCnt = count($ret) == 0 ? 0 : count($ret[0]);
$accessCtl->after($ret);
$ignoreAfter = true;
if ($enablePaging && $pagesz == count($ret)) {
// 还有下一页数据, 添加nextkey
if ($enablePartialQuery) {
$nextkey = $ret[count($ret) - 1]["id"];
} else {
$nextkey = $pagekey + 1;
}
}
$ret = objarr2table($ret, $fixedColCnt);
if (isset($nextkey)) {
$ret["nextkey"] = $nextkey;
}
if (isset($totalCnt)) {
$ret["total"] = $totalCnt;
}
handleFormat($ret, $tbl);
}
}
} elseif ($ac1 == "del") {
$id = mparam("id");
$sql = sprintf("DELETE FROM %s WHERE id=%d", $tbl, $id);
$cnt = execOne($sql);
if ($cnt != 1) {
throw new MyException(E_PARAM, "not found id={$id}");
}
$ret = "OK";
}
if (!$ignoreAfter) {
$accessCtl->after($ret);
}
return $ret;
}
function api_att()
{
// overwritten the default
header("Cache-Control: private, max-age=99999999");
//header("Cache-Control: private");
header("Pragma: ");
// session_start() set this one to "no-cache"
#checkAuth(AUTH_USER | AUTH_EMP);
#$uid = $_SESSION["uid"];
$id = param("id");
$thumbId = param("thumbId");
if ((is_null($id) || $id <= 0) && (is_null($thumbId) || $thumbId <= 0)) {
header(HTTP_NOT_FOUND);
exit;
}
// setup cache via etag.
$etag = null;
if (is_null($thumbId)) {
$etag = "att-{$id}";
} else {
$etag = "att-t{$thumbId}";
}
@($etag1 = $_SERVER['HTTP_IF_NONE_MATCH']);
if ($etag1 == $etag) {
header("Etag: {$etag}", true, 304);
exit;
}
if ($id !== null) {
$sql = "SELECT path FROM Attachment WHERE id={$id}";
} else {
# a1: original, a2: thumb
$sql = "SELECT a1.path FROM Attachment a1 INNER JOIN Attachment a2 ON a1.id=a2.orgPicId WHERE a2.id={$thumbId}";
}
$file = queryOne($sql);
if ($file === false) {
header(HTTP_NOT_FOUND);
exit;
}
if (preg_match('/http:/', $file)) {
header('Location: ' . $file);
throw new DirectReturn();
}
chdir($GLOBALS["BASE_DIR"]);
if (!is_file($file)) {
header(HTTP_NOT_FOUND);
exit;
}
# 对指定mime的直接返回，否则使用重定向。
# TODO: 使用 apache x-sendfile module 解决性能和安全性问题。
$ext = strtolower(pathinfo($file, PATHINFO_EXTENSION));
global $ALLOWED_MIME;
//$mimeType = $ALLOWED_MIME[$ext] ?: 'application/octet-stream';
$mimeType = $ALLOWED_MIME[$ext];
if (@$mimeType) {
header("Content-Type: {$mimeType}");
header("Etag: {$etag}");
#header("Expires: Thu, 3 Sep 2020 08:52:00 GMT");
#header("Content-length: " . filesize($file));
#header('Content-Disposition: attachment; filename='.basename($file));
readfile($file);
} else {
$baseUrl = getBaseUrl(false);
$url = $baseUrl . $file;
header('Location: ' . $url);
}
throw new DirectReturn();
}
