【微信小程序】 侧边栏菜单查询

原因

开发的项目在WX小程序上有个新需求

就是在用户[我的]界面里的菜单中多加一个[我的服务]

之前有提及过，服务消息被按8个消息类型拆成了8张表

对应，在小程序界面这里也应该放上对应8个菜单，按菜单去查询数据

之前的设想

在原先的设想是不拆分菜单去查询，8张表的数据使用UNIONALL合并成一张表，带给小程序

这个设想被组长直接否决，原因是预测数据量过多，影响DB性能

第二天组长和产品核对之后就认可组长的设想，要拆掉查询

但是产品就和我想的是一样的，在用户眼里就应该是一个普通列表，一列刷过去看就行了

怕产品反水又要改回去，我觉得花了一下午想出来的合并SQL有保留的价值

这里我就放上我的SQL

1、按照用户ID筛选

2、按照商家ID筛选，用户可能去A店，B店住过，在A店就只展示A店的消费记录，不可能在A店还能看B店的消费记录

3、按照用户自己提交的服务记录的创建时间降序排序，最新的服务记录放在前面

4、服务类型是一个字典，每个商家的服务类型是区分的，所以这里要子查询翻译

5、每个类型还附带了具体信息，各不一样，用JSON_OBJECT统一字段带出来

SELECT * FROM (
-- 清扫服务表
SELECT
  (SELECT DMMC FROM `aisw_merchant_dict` AS DICT WHERE DICT.MERCHANT_ID = 4 AND DICT.DMBH = SERVICE_TYPE) AS `serviceName`,
  JSON_OBJECT( 'cleanDate', CLEAN_DATE) AS `serviceInfo`,
	ID ,
	ROOM_NO ,
	MERCHANT_ID ,
	SERVICE_TYPE ,
	USER_ID AS `SRV_USER`,
	ACCEPT_STATUS ,
	REMARKS ,
	CREATE_DATE
FROM `aisw_e_service_clean_msg` 
WHERE MERCHANT_ID = 4 AND USER_ID = 1 

UNION ALL
-- 配送服务表
SELECT
  (SELECT DMMC FROM `aisw_merchant_dict` AS DICT WHERE DICT.MERCHANT_ID = 4 AND DICT.DMBH = SERVICE_TYPE) AS `serviceName`,
  JSON_OBJECT( 'dryTypes', DELIVERY_TYPE, 'dryNums', DELIVERY_NUMS) AS `serviceInfo`,
	ID ,
	ROOM_NO ,
	MERCHANT_ID ,
	SERVICE_TYPE ,
	USER_ID AS `SRV_USER`,
	ACCEPT_STATUS ,
	REMARKS ,
	CREATE_DATE
FROM `aisw_e_service_delivery_msg` 
WHERE MERCHANT_ID = 4 AND USER_ID = 1 

UNION ALL
-- 故障报修表
SELECT
  (SELECT DMMC FROM `aisw_merchant_dict` AS DICT WHERE DICT.MERCHANT_ID = 4 AND DICT.DMBH = SERVICE_TYPE) AS `serviceName`,
  JSON_OBJECT( 'faultTypes', FAULT_TYPE) AS `serviceInfo`,
	ID ,
	ROOM_NO ,
	MERCHANT_ID ,
	SERVICE_TYPE ,
	USER_ID AS `SRV_USER`,
	ACCEPT_STATUS ,
	REMARKS ,
	CREATE_DATE
FROM `aisw_e_service_fault_msg` 
WHERE MERCHANT_ID = 4 AND USER_ID = 1 

UNION ALL
-- 退房表
SELECT
  (SELECT DMMC FROM `aisw_merchant_dict` AS DICT WHERE DICT.MERCHANT_ID = 4 AND DICT.DMBH = SERVICE_TYPE) AS `serviceName`,
  NULL AS `serviceInfo`,
	ID ,
	ROOM_NO ,
	MERCHANT_ID ,
	SERVICE_TYPE ,
	USER_ID,
	ACCEPT_STATUS ,
	REMARKS ,
	CREATE_DATE
FROM `aisw_e_service_one_checkout_msg` 
WHERE MERCHANT_ID = 4 AND USER_ID = 1 

UNION ALL
-- 续住表
SELECT
  (SELECT DMMC FROM `aisw_merchant_dict` AS DICT WHERE DICT.MERCHANT_ID = 4 AND DICT.DMBH = SERVICE_TYPE) AS `serviceName`,
  JSON_OBJECT( 'renewalType', RENEWAL_TYPE, 'renewalDay', RENEWAL_DAY) AS `serviceInfo`,
	ID ,
	ROOM_NO ,
	MERCHANT_ID ,
	SERVICE_TYPE ,
	USER_ID AS `SRV_USER`,
	ACCEPT_STATUS ,
	REMARKS ,
	CREATE_DATE
FROM `aisw_e_service_renewal_msg` 
WHERE MERCHANT_ID = 4 AND USER_ID = 1 

UNION ALL
-- 叫醒服务表
SELECT
  (SELECT DMMC FROM `aisw_merchant_dict` AS DICT WHERE DICT.MERCHANT_ID = 4 AND DICT.DMBH = SERVICE_TYPE) AS `serviceName`,
  JSON_OBJECT( 'isKnock', IS_KNOCK, 'wakePhone', WAKE_PHONE, 'wakeDate', WAKE_DATE) AS `serviceInfo`,
	ID ,
	ROOM_NO ,
	MERCHANT_ID ,
	SERVICE_TYPE ,
	USER_ID AS `SRV_USER`,
	ACCEPT_STATUS ,
	REMARKS ,
	CREATE_DATE
FROM `aisw_e_service_wake_msg` 
WHERE MERCHANT_ID = 4 AND USER_ID = 1 
) AS FIANL_TABLE
WHERE CREATE_DATE >= CONVERT(DATE_SUB(NOW(), INTERVAL 1 MONTH), DATE)
ORDER BY CREATE_DATE DESC 

结果预览：

+-------------+---------------------------------------+-----+---------+-------------+--------------+----------+---------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------+
| serviceName | serviceInfo                           | ID  | ROOM_NO | MERCHANT_ID | SERVICE_TYPE | SRV_USER | ACCEPT_STATUS | REMARKS                                                                                                                                                                                                                                                                                                                              | CREATE_DATE         |
+-------------+---------------------------------------+-----+---------+-------------+--------------+----------+---------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------+
| 清扫服务    | {"cleanDate": "立即清扫"}             | 151 | 701     |           4 | 360000002    |        1 |             1 | NULL                                                                                                                                                                                                                                                                                                                                 | 2022-06-20 14:30:50 |
| 一键退房    | NULL                                  | 219 | 701     |           4 | 360000004    |        1 |             1 | NULL                                                                                                                                                                                                                                                                                                                                 | 2022-06-20 14:30:34 |
| 一键退房    | NULL                                  | 203 | 705     |           4 | 360000004    |        1 |             0 | NULL                                                                                                                                                                                                                                                                                                                                 | 2022-06-15 14:27:20 |
| 清扫服务    | {"cleanDate": "立即清扫"}             | 122 | 705     |           4 | 360000002    |        1 |             0 | NULL                                                                                                                                                                                                                                                                                                                                 | 2022-06-15 14:25:19 |
| 一键退房    | NULL                                  | 202 | 705     |           4 | 360000004    |        1 |             0 | NULL                                                                                                                                                                                                                                                                                                                                 | 2022-06-15 14:25:02 |
| 一键退房    | NULL                                  | 201 | 705     |           4 | 360000004    |        1 |             0 | NULL                                                                                                                                                                                                                                                                                                                                 | 2022-06-15 14:24:27 |
| 一键退房    | NULL                                  | 200 | 705     |           4 | 360000004    |        1 |             0 | NULL                                                                                                                                                                                                                                                                                                                                 | 2022-06-15 14:23:46 |
| 一键退房    | NULL                                  | 199 | 705     |           4 | 360000004    |        1 |             0 | NULL                                                                                                                                                                                                                                                                                                                                 | 2022-06-14 13:55:01 |
| 在线续住    | {"renewalDay": 2, "renewalType": 1}   |  43 | 705     |           4 | 360000001    |        1 |             1 | 条件啦咯啦咯啦咯考虑兔兔图突突突图兔兔他图他图吐她她她条件啦咯啦咯啦咯考虑兔兔图突突突图兔兔他图他图吐她她她条件啦咯啦咯啦咯考虑兔兔图突突突图兔兔他图他图吐她她她条件啦咯啦咯啦咯考虑兔兔图突突突图兔兔他图他图吐她她她条件啦咯啦咯啦咯考虑兔兔图突突突图兔兔他图他图吐她她她条件啦咯啦咯啦咯考虑兔兔图突突突图兔兔他图他图吐她她她 | 2022-06-13 15:38:09 |
| 故障报修    | {"faultTypes": "362000003,362000006"} |  46 | 705     |           4 | 360000007    |        1 |             1 | NULL                                                                                                                                                                                                                                                                                                                                 | 2022-06-13 15:37:48 |
+-------------+---------------------------------------+-----+---------+-------------+--------------+----------+---------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------+
10 rows in set (0.16 sec)

　　

原型效果

产品这边从不给原型图，老开局一张嘴了

组长提醒我用饿了么的小程序或者APP那个就是

我截取了一张PO出来，左边是一些菜单分类，右边就是菜单项，这样

参考资料

https://blog.csdn.net/weixin_41599291/article/details/93650074

百度了好几篇文章，这篇是最符合的，能直接CV看预览效果

但是博主用的是swiper标签，如果要做翻页查询需要有上拉翻页，下拉刷新两个事件监听的钩子

这个标签是用不了的，所以后面又在WX小程序开发文档里面翻组件，用Scroll-View是符合的

Scroll-View文档地址

https://developers.weixin.qq.com/miniprogram/dev/component/scroll-view.html

1、要给Scroll-View设置顶宽，要直接拉满整个屏高 height: 100vh 样式属性即可

2、设置滚动方向是Y轴，scroll-y="true"

3、关于下拉刷新的相关属性

refresher-enabled	boolean	false	否	开启自定义下拉刷新
refresher-threshold	number	45	否	设置自定义下拉刷新阈值
refresher-default-style	string	"black"	否	设置自定义下拉刷新默认样式，支持设置 black | white | none， none 表示不使用默认样式
refresher-triggered	boolean	false	否	设置当前下拉刷新状态，true 表示下拉刷新已经被触发，false 表示下拉刷新未被触发
bindrefresherrefresh	eventhandle	否	自定义下拉刷新被触发

4、上拉翻页，只有这个属性是匹配的

bindscrolltolower	eventhandle	否	滚动到底部/右边时触发

　　

开发效果

WXML代码部分：

<!--pages/user/my-service/my-service.wxml-->

<view class='productNav' wx:if="{{ phoneNum.length > 0 }}">
  <!-- 左侧 -->
  <view class='left'>

    <view
      wx:if="{{menuList.length > 0}}" 
      wx:for="{{menuList}}" 
      wx:key="key" 
      wx:for-index="index" 
      wx:for-item="item"
      data-id="{{item.index}}"
      data-dmbh="{{item.dmbh}}"
      class="{{active== item.index ?'selected':'normal'}}"
      bindtap='switchNav'>
      {{item.dmmc}}
    </view>
  </view>

  <!-- 右侧 -->

    <!-- 
      https://developers.weixin.qq.com/miniprogram/dev/component/scroll-view.html
      
      refresher-enabled	boolean	false	否	开启自定义下拉刷新
      refresher-triggered 设置当前下拉刷新状态，true 表示下拉刷新已经被触发，false 表示下拉刷新未被触发
      refresher-threshold	number	45	否	设置自定义下拉刷新阈值
     
     -->
    <scroll-view 
      class="right"
      scroll-y="true"
      scroll-x="false"
      
      refresher-enabled="true"
      refresher-threshold="{{40}}"
      refresher-triggered="{{refreshFlag}}"
      
      bindrefresherrefresh="refresh"
      bindscrolltolower="toLowerPaging">

      <!-- 一键退房 -->
      <view wx-if="{{ currentTab == '360000004' && ctxList.length > 0 }}">
        <view wx:for="{{ctxList}}" wx:key="idx" wx:for-item="item" class="item"  >
          <view>房间号：{{ item.roomNo }}</view>
          <view>创建时间：{{ item.createDate }}</view>
        </view>
      </view>
      
      <!-- 清扫服务 -->
      <view wx-if="{{ currentTab == '360000002' && ctxList.length > 0 }}">
        <view wx:for="{{ctxList}}" wx:key="idx" wx:for-item="item" class="item"  >
          <view>房间号：{{ item.roomNo }}</view>
          <view>清扫时间：{{ item.cleanDate }}</view>
          <view>创建时间：{{ item.createDate }}</view>
        </view>
      </view>
      
      <!-- 物品配送 -->
      <view wx-if="{{ currentTab == '360000000' && ctxList.length > 0 }}">
        <view wx:for="{{ctxList}}" wx:key="idx" wx:for-item="item" class="item"  >
          <view>房间号：{{ item.roomNo }}</view>
          <view>物品：{{ item.translate }}</view>
          <view>创建时间：{{ item.createDate }}</view>
        </view>
      </view>

      <!-- 叫醒服务 -->
      <view wx-if="{{ currentTab == '360000003' && ctxList.length > 0 }}">
        <view wx:for="{{ctxList}}" wx:key="idx" wx:for-item="item" class="item"  >
          <view>房间号：{{ item.roomNo }}</view>
          <view>是否敲门叫醒：{{ item.isKnock == 1 ? '是' : '否' }} </view>
          <view>叫醒手机: {{ tools.phoneHide(item.wakePhone) }}</view>
          <view>叫醒时间：{{ item.wakeDate }}</view>
          <view>创建时间：{{ item.createDate }}</view>
        </view>
      </view>

      <!-- 在线续住 -->
      <view wx-if="{{ currentTab == '360000001' && ctxList.length > 0 }}">
        <view wx:for="{{ctxList}}" wx:key="idx" wx:for-item="item" class="item"  >
          <view>房间号：{{ item.roomNo }}</view>
          <view>续住类型：{{ item.renewalType == 1 ? '续住此房间' : '换房续住' }}</view>
          <view>续住天数：{{ item.renewalDay }}</view>
          <view>创建时间：{{ item.createDate }}</view>
        </view>
      </view>

      <!-- 故障报修 -->
      <view wx-if="{{ currentTab == '360000007' }}">
        <view wx:for="{{ctxList}}" wx:key="idx" wx:for-item="item" class="item"  >
          <view>房间号：{{ item.roomNo }}</view>
          <view>报修家具: {{ item.translate }}</view>
          <view>创建时间：{{ item.createDate }}</view>
        </view>
      </view>

      <!-- 空数据 -->
      <view wx:if="{{ ctxList.length == 0 }}" class="no-data">
        暂无数据
      </view>
    </scroll-view>
</view>

<view wx:else>
  <nonelogin prop-title="{{title}}" prop-router="{{router}}" id="nonelogin" ></nonelogin>
</view>

<wxs src="./../../../utils/tools.wxs" module="tools" ></wxs>

　　

JS代码部分：

const APP = getApp()
const { wxRequest, checkIsOneLogin } = require('../../../utils/util.js');

Page({
  data: {
    userId:'',
    refreshFlag:true,
    active: 0,
    currentTab: '',
    menuList: [],
    pageIndex: 1,
    pageSize: 10,
    total: 0,
    ctxList: [],
    merchantId: null,
    placeCode: null,
    phoneNum: '',
    title: '我的服务',
    router: 'home'
  },
  onLoad(options) {
    let that = this
    wx.setNavigationBarTitle({ title: that.data.title })
    // 校验用户是否登录
    let phoneNum = wx.getStorageSync('aisw_phoneNum');
    if (checkIsOneLogin(phoneNum, that.data.router)) {
      that.setData({ userId:wx.getStorageSync('aisw_user_id'), phoneNum: phoneNum })
      that.getAvaliableMenu()
    }
  },
  onReady() {
    let phoneNum = wx.getStorageSync('aisw_phoneNum');
    if (phoneNum === '') {
      this.nonelogin = this.selectComponent("#nonelogin");
      this.nonelogin.onload()
    }
  },

  switchNav(e) {
    const PAGE = this;
    const id = e.currentTarget.dataset.id;
    const type = e.currentTarget.dataset.dmbh

    if (PAGE.data.currentTab == id) return false
    else PAGE.setData({ currentTab: type })
    PAGE.setData({ active: id })
    
    // 重置结果集列表
    PAGE.setData({
      ctxList: [],
      pageIndex: 1,
      total: 0
    })

  
    // 查询
    PAGE.getMyServiceHistory()
  },


  // 查询可用的服务
  getAvaliableMenu() {
    let that = this
    const DMLB = APP.globalData.dictServiceType
    wxRequest({
      url: `authVerify/queryMerchantDictList/${DMLB}`,
      data: { 
        jsonParam: { 'unionId': APP.globalData.unionId }
      },
      method: 'GET',
    }).then(res => {
      
      if (res.code !== 200 || !res.data) return 
      
      console.log(`获取可用的服务菜单成功 -> ${JSON.stringify(res.data)}`)

      // 过滤拿到不是[WIFI扫码和预开发票]的其它服务
      let menus = res.data.filter(menu => !['360000006', '360000005'].includes(menu.dmbh))

      // 如果过滤之后没有数据，直接结束
      if (0 == menus.length) return

      // 取出商家ID 和 场所代码
      that.setData({
        merchantId: menus[0].id,
        placeCode: menus[0].placeCode
      })
    
      that.setData({ menuList: menus, currentTab: menus[0].dmbh })

      that.getMyServiceHistory()
    }).catch(e => {
      wx.showToast({
        title: e.message,
        duration: 2000,
        icon: 'none'
      })
    })
  },

  // 查询服务历史列表
  getMyServiceHistory() {
    const PAGE = this
    wx.showLoading({ title: '数据加载中' })
    wxRequest({
      url: 'service/serviceHistory',
      method: 'POST',
      data: {
        jsonParam: {
          merchantId: PAGE.data.merchantId, // 当前商家
          acceptStatus: 1, // 只查询已受理
          serviceType: PAGE.data.currentTab, // 服务类型
          unionId: APP.globalData.unionId,
          userId: PAGE.data.userId, // 当前用户
          page: {
            current: PAGE.data.pageIndex,
            size: PAGE.data.pageSize
          }
        }
      }
    }).then(res => {
      // 关闭加载提示
      wx.hideLoading()
      if (res.code !== 200 || !res.data) {
        PAGE.setData({ ctxList: [] })
        return 
      }

      PAGE.setData({
        ctxList: [ ...PAGE.data.ctxList, ...res.data ],
        total: res.total
      });

    }).catch(e => {
      // 关闭加载提示
      wx.hideLoading()
      that.setData({ ctxList: [] })
    })

  },



  refresh(){
    let that = this
    // 调接口
    that.setData({ pageIndex: 1, ctxList: [] })
    that.getMyServiceHistory()
    that.setData({ refreshFlag: false })
    console.log("触发下拉刷新")
  },
  toLowerPaging() {
    let that = this
    
    // 获取已经翻页得到的记录数量
    const rowCount = that.data.pageIndex * that.data.pageSize
    if (rowCount >= that.data.total) {
      wx.showToast({
        icon: 'none', // 不设置任何图标
        title: '已经到底了', // 提示文本
        duration: 3000, // 持续3秒
      })
      return
    }
    that.setData({ pageIndex: that.data.pageIndex + 1 })
    that.getMyServiceHistory()
    console.log("触发上拉翻页")
  }

})

　　

WXSS 样式代码部分：

/* pages/user/my-service/my-service.wxss */

.productNav {
  display: flex;
  flex-direction: row;
  font-family: "Microsoft YaHei";
  position: relative;
}
.left {
   25%;
  font-size: 30rpx;
  background-color: #f4f4f4;
}
.left view {
  text-align: center;
  height: 90rpx;
  line-height: 90rpx;
}
.selected {
  background-color: #fff;
  border-left: 2px solid #45CF8A;
  font-weight: bold;
  color: #45CF8A;
}
.normal {
  background-color: #f4f4f4;
  border-bottom: 1px solid #f2f2f2;
}
.right {
  75%;
  height: 100vh;
  margin: 0;
}


.right .item {
  /* background-color: sandybrown; */
  font-size: 24rpx;

  border: 1rpx solid #efefef;
  border-radius: 15rpx;

  margin: 15rpx;
  padding: 15rpx;

  /* 盒子阴影 */
  box-shadow: 1rpx 1rpx 15rpx #ddd;
}

/* 无数据提示 */
.no-data {
  position: absolute;
  top: 50%;
  left: 50%;
  transform: translate(-50%, -50%);
}

　　

注意点分析

1、菜单的内容和数量并不是固定写死的

2、菜单数据需要有index排序，页面加载时执行第一个菜单项的数据查询

3、WX小程序请求并不是同步执行的，数据加载顺序需要注意（如果不同步，需要套在异步回调里面写）

4、每次切换点击，注意参数的切换