4.9 KiB
queryByCondition
-
根据不为空的参数进行分页查询
select @pageTag(){ t.* @} from sys_log t where 1=1
@//数据权限，该sql语句功能点,如果不考虑数据权限，可以删除此行
and #function("sysLog.query")# @if(!isEmpty(id)){ and t.id =#id# @} @if(!isEmpty(method)){ and t.method =#method# @} @if(!isEmpty(requestUrl)){ and t.request_url =#requestUrl# @} @if(!isEmpty(params)){ and t.params =#params# @} @if(!isEmpty(result)){ and t.result =#result# @} @if(!isEmpty(ip)){ and t.ip =#ip# @} @if(!isEmpty(createTime)){ and t.create_time =#createTime# @} @if(!isEmpty(resultTime)){ and t.result_time =#resultTime# @} @if(!isEmpty(token)){ and t.token =#token# @} @if(!isEmpty(memberId)){ and t.member_id =#memberId# @} @if(!isEmpty(session)){ and t.session =#session# @} @if(!isEmpty(userId)){ and t.user_id =#userId# @}
deleteSysLogByIds
-
批量删除
delete from sys_log where find_in_set(id,#ids#)
getSysLogValues
-
根据不为空的参数进行查询
select t.* from sys_log t where 1=1
@if(!isEmpty(id)){ and t.id =#id# @} @if(!isEmpty(method)){ and t.method =#method# @} @if(!isEmpty(requestUrl)){ and t.request_url =#requestUrl# @} @if(!isEmpty(params)){ and t.params =#params# @} @if(!isEmpty(result)){ and t.result =#result# @} @if(!isEmpty(ip)){ and t.ip =#ip# @} @if(!isEmpty(createTime)){ and t.create_time =#createTime# @} @if(!isEmpty(resultTime)){ and t.result_time =#resultTime# @} @if(!isEmpty(token)){ and t.token =#token# @} @if(!isEmpty(memberId)){ and t.member_id =#memberId# @} @if(!isEmpty(session)){ and t.session =#session# @} @if(!isEmpty(userId)){ and t.user_id =#userId# @}
saveToText
INSERT INTO sys_log_text (
params,
result,
sys_id
)
VALUES
(
#params#,
#result#,
#sysId#
)
saveToMediumText
INSERT INTO sys_log_mediumtext (
params,
result,
sys_id
)
VALUES
(
#params#,
#result#,
#sysId#
)
saveToLongText
INSERT INTO sys_log_longtext (
params,
result,
sys_id
)
VALUES
(
#params#,
#result#,
#sysId#
)
onLineNum
-
某段时间内访问人与访问次数
SELECT t.* FROM ( SELECT member_id, COUNT(0) num FROM ( SELECT member_id FROM sys_log WHERE id BETWEEN (SELECT id FROM sys_log WHERE create_time >= #startTime# LIMIT 1) AND IFNULL((SELECT id - 1 FROM sys_log WHERE create_time >= #endTime# LIMIT 1),(select max(id) from sys_log)) AND member_id > 0 )a GROUP BY member_id )t LEFT JOIN member a ON a.member_id = t.member_id LEFT JOIN member_we_chat b ON b.member_id = t.member_id WHERE (a.mobile > 0 OR b.member_we_chat_id > 0) AND a.member_type = 1 ORDER BY num DESC
visitGoodsNum
-
某段时间内访问商品与访问次数
SELECT * FROM ( SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(params,'"goodsId":["',-1),'"]',1)+0 goods_id ,COUNT(0) num FROM ( SELECT params FROM sys_log WHERE id BETWEEN (SELECT id FROM sys_log WHERE create_time >= #startTime# LIMIT 1) AND IFNULL((SELECT id - 1 FROM sys_log WHERE create_time >= #endTime# LIMIT 1),(select max(id) from sys_log)) AND member_id > 0 AND (method = 'com.ibeetl.jlw.web.MicroWebController.getInfo' OR method = 'com.ibeetl.jlw.web.GoodsController.getGoodsInfo') AND LOCATE('goodsId',params)>0 ) a GROUP BY SUBSTRING_INDEX(SUBSTRING_INDEX(params,'"goodsId":["',-1),'"]',1)+0 ) t ORDER BY num DESC
countVisitorsNumber
SELECT
COUNT( DISTINCT ( IFNULL(member_id,ip) ) )
FROM
sys_log
WHERE
create_time BETWEEN #startTime# AND #endTime# AND user_id IS NULL
listTimeRangeVisitGoods
SELECT log.ip, log.member_id, ( CASE WHEN method = 'com.ibeetl.jlw.web.MicroWebController.getSanJiuBottle' THEN SUBSTRING_INDEX( SUBSTRING_INDEX( log.params, '{"parentGoodsId":["',- 1 ), '"]', 1 ) + 0 ELSE SUBSTRING_INDEX( SUBSTRING_INDEX( log.params, '{"goodsId":["',- 1 ), '"]', 1 ) + 0 END ) AS goods_id FROM sys_log log WHERE create_time BETWEEN #startTime# AND #endTime# AND ( method = 'com.ibeetl.jlw.web.MicroWebController.getInfo' OR method = 'com.ibeetl.jlw.web.GoodsPackController.getGoodsInfo' OR method = 'com.ibeetl.jlw.web.MicroWebController.getSanJiuBottle' )