Supply_chain_finance/web/target/classes/sql/jlw/sysLog.md

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' )