public function getEveryDay()
{
global $db, $res;
dbc();
$NowTimes = date("Y-m-d", time());
$sql = "select DATE_FORMAT(paid_at,'%Y-%m-%d') as dataKey,ifnull(COUNT(id),0) AS totalTrans,ifnull(SUM(should_paid),0) AS totalPays from (
SELECT \"" . $NowTimes . "\" as evData
union all
SELECT date_sub(\"" . $NowTimes . "\", interval 1 day) as evData
union all
SELECT date_sub(\"" . $NowTimes . "\", interval 2 day) as evData
union all
SELECT date_sub(\"" . $NowTimes . "\", interval 3 day) as evData
union all
SELECT date_sub(\"" . $NowTimes . "\", interval 4 day) as evData
union all
SELECT date_sub(\"" . $NowTimes . "\", interval 5 day) as evData
union all
SELECT date_sub(\"" . $NowTimes . "\", interval 6 day) as evData) as total LEFT JOIN " . $db->table('orders') . " ev on DATE_FORMAT(ev.paid_at,'%Y-%m-%d') = total.evData ";
$sql .= " GROUP BY evData";
$row = $db->queryall($sql);
$res["data"] = $row;
die(json_encode_lockdata($res));
}
JSON数据格式化
{
"err": "",
"res": "",
"data": [{
"dataKey": "2020-07-23",
"totalTrans": "93",
"totalPays": "3577.16"
}, {
"dataKey": "2020-07-24",
"totalTrans": "106",
"totalPays": "3313.11"
}, {
"dataKey": "2020-07-25",
"totalTrans": "110",
"totalPays": "3442.04"
}, {
"dataKey": "2020-07-26",
"totalTrans": "96",
"totalPays": "2890.99"
}, {
"dataKey": "2020-07-27",
"totalTrans": "119",
"totalPays": "3669.60"
}, {
"dataKey": "2020-07-28",
"totalTrans": "135",
"totalPays": "4262.56"
}, {
"dataKey": "2020-07-29",
"totalTrans": "48",
"totalPays": "1280.88"
}]
}
Done!