werun-wxapp/nova-werun/service/getSportData.js

const Parse = getApp().Parse;
const company = getApp().globalData.company;
const uid = Parse.User.current()?.id
let request = require('../../utils/request')
const dateF = require('../../utils/date')

/**
 * 计算运动累加状况-getwalk使用
 * @param {*} column 累加字段 (未知则'distance')
 * @param {*} type 运动类型 walk/run(未知则不限制)
 * @param {*} fromTo 时间范围 {from,to}(未知则今日)
 */
async function getwalk(column, type, fromTo) {
  let todate = new Date(new Date().setHours(0, 0, 0, 0))
  let tomorrow = new Date(new Date(todate).setDate(todate.getDate() + 1))
  if (fromTo?.from && fromTo?.to) {
    todate = fromTo.from
    tomorrow = fromTo.to
  }
  let todaySql = dateF.formatTime(`YYYY-mm-dd HH:MM:SS`, todate)
  let yestodaySql = dateF.formatTime(`YYYY-mm-dd HH:MM:SS`, tomorrow)
  let sql = `SELECT SUM(COALESCE(t1."num", 0))- SUM(COALESCE(t1."ago_num",0)) AS "sum"
  FROM(SELECT MAX(al."${column||'distance'}") AS "num" ,al."actData",(
   SELECT MAX(al2."${column||'distance'}")
    FROM "ActivityRunLog" al2
    WHERE al2."actData"=al."actData"
    AND al2."isDeleted" IS NOT TRUE
    ${type?`AND al2."type"='${type}'`:''} 
    AND al2."createdAt" < '${todaySql}'
    AND al2."isDeleted" IS NOT TRUE
  )AS "ago_num"
  FROM "ActivityRunLog" al
  LEFT JOIN "ActivityData" ad ON ad."objectId"=al."actData"
  WHERE al."isDeleted" IS NOT TRUE
  AND al."createdAt" > '${todaySql}'
  AND al."createdAt" < '${yestodaySql}'
  AND al."company"='${company}'
  AND al."user"='${uid}'
  ${type?`AND al."type"='${type}'`:''}
  AND al."isDeleted" IS NOT TRUE
  AND ad."isDeleted" IS NOT TRUE
  ${type?`AND ad."type"='${type}'`:''} 
  GROUP BY al."actData")t1`
  // console.log(sql)
  let data = await request.customSQL(sql)
  // console.log(data)
  return data[0].sum < 0 ? 0 : data[0].sum || 0
}


/**查看活动运动数据（当前活动对应运动是否结束） */
async function getActSport(actId) {
  let actQuery = new Parse.Query('Activity')
  let act = await actQuery.get(actId)
  if (act?.get('endDate')) {

  }
}
/**对于活动超时未结束的运动，设置stage为end */
async function setEndSport(actId) {
  let regQuery = new Parse.Query('ActivityRegister')
  regQuery.equalTo('company', company)
  regQuery.equalTo('user', uid)
  regQuery.notEqualTo('isDeleted', true)
  regQuery.equalTo('activity', actId)
  regQuery.select('booking')
  let reg = await regQuery.find()
  let overtimeReg = reg?.filter(item => {
    let now = new Date()
    let endTime = item?.get('booking')?.to
    if (!endTime) return true
    return now >= endTime
  })?.map(item => item?.id)
  console.log(overtimeReg)
  if (overtimeReg?.length <= 0) return
  let actQuery = new Parse.Query('ActivityData')
  actQuery.equalTo('company', company)
  actQuery.equalTo('user', uid)
  actQuery.notEqualTo('isDeleted', true)
  actQuery.containedIn('activity', overtimeReg)
  actQuery.select('objectId')
  let actData = await actQuery.find()
  for (let i in actData) {
    let act = actData[i]
    //获取最后一条运动过程记录
    let logQuery = new Parse.Query('ActivityRunLog')
    logQuery.equalTo('company', company)
    logQuery.equalTo('user', uid)
    logQuery.notEqualTo('isDeleted', true)
    logQuery.equalTo('actData', act?.id)
    logQuery.descending('createdAt')
    let endLog = await logQuery.first()
    endLog.set('stage', 'end')
    await endLog.save()
    act.set('steps', endLog?.get('steps') || 0)
    act.set('distance', endLog?.get('distance') || 0)
    act.set('matchSpeed', endLog?.get('matchSpeed') || 0)
    act.set('sportDate', endLog?.get('sportDate') || 0)
    act.set('burnCalories', endLog?.get('burnCalories') || 0)
    act.set('status', 'end')
    act.set('endDate', new Date())
    await act.save()
  }
}

/**
 * 获取排行榜
 * @param {*} type 运动类型 默认不限制
 * @param {*} fromto 时间范围 默认为空 today/toweek/tomonth/空
 * @param {*} limit limit默认20
 * @param {*} skip skip默认0
 * @param {*} order 顺序 默认 DESC
 * @param {*} limitSql 其他限制语句 AND ad."xxx" = 'xxx'
 */
async function getRanking(type, fromto, limit, skip, order, limitSql) {
  let fromtoSql = ``
  switch (fromto) {
    case 'today':
      let todate = new Date(new Date().setHours(0, 0, 0, 0))
      let tomorrow = new Date(new Date(todate).setDate(todate.getDate() + 1))
      let todaySql = dateF.formatTime(`YYYY-mm-dd HH:MM:SS`, todate)
      let yestodaySql = dateF.formatTime(`YYYY-mm-dd HH:MM:SS`, tomorrow)
      fromtoSql = `AND ad."endDate" >= '${todaySql}' AND ad."endDate" < '${yestodaySql}'`
      break;
    case 'toweek':
      const today = new Date();
      const dayOfWeek = today.getDay();
      const thisSun = new Date(today);
      thisSun.setDate(today.getDate() + (0 - dayOfWeek));
      thisSun.setHours(0, 0, 0, 0);
      const nextSun = new Date(thisSun);
      nextSun.setDate(thisSun.getDate() + 7);
      let thisSunSql = dateF.formatTime(`YYYY-mm-dd HH:MM:SS`, thisSun)
      let nextSunSql = dateF.formatTime(`YYYY-mm-dd HH:MM:SS`, nextSun)
      fromtoSql = `AND ad."endDate" >= '${thisSunSql}' AND ad."endDate" < '${nextSunSql}'`
      break;
    case 'tomonth':
      const now = new Date();
      const toMon = new Date(now.getFullYear(), now.getMonth(), 1, 0, 0, 0);
      const nextMon = new Date(now.getFullYear(), now.getMonth() + 1, 1, 0, 0, 0);
      let toMonSql = dateF.formatTime(`YYYY-mm-dd HH:MM:SS`, toMon)
      let nextMonSql = dateF.formatTime(`YYYY-mm-dd HH:MM:SS`, nextMon)
      fromtoSql = `AND ad."endDate" >= '${toMonSql}' AND ad."endDate" < '${nextMonSql}'`
      break;
    default:
      break;
  }
  let sql = `SELECT SUM(ad."steps") AS "totalSteps",SUM(ad."distance") AS "distance",SUM(ad."burnCalories") AS "burnCalories",SUM(ad."sportDate") AS "sportDate",ad."user",u."nickname",u."avatar"
  FROM "ActivityData" ad
  LEFT JOIN "_User" u ON u."objectId"=ad."user"
  WHERE ad."company"='${company}'
  AND ad."isDeleted" IS NOT TRUE
  AND ad."status" = 'end'
  ${limitSql||''}
  ${type?`AND ad."type" = '${type}'`:''}
  ${fromtoSql}
  GROUP BY ad."user",u."nickname",u."avatar"
  ORDER BY SUM(ad."steps") ${order||'DESC'}
  LIMIT ${limit||20} OFFSET ${skip||0}`
  // console.log(sql)
  let data = await request.customSQL(sql)
  // console.log(data)
  return data
}

/**
 * 获取用户排名
 * @param {*} uid 用户id 默认当前用户
 * @param {*} type 运动类型 默认不限制
 * @param {*} fromto 时间范围 today/toweek/tomonth/空 默认不限制
 */
async function getUserRank(userid, type, fromto) {
  let fromtoSql = ``
  switch (fromto) {
    case 'today':
      let todate = new Date(new Date().setHours(0, 0, 0, 0))
      let tomorrow = new Date(new Date(todate).setDate(todate.getDate() + 1))
      let todaySql = dateF.formatTime(`YYYY-mm-dd HH:MM:SS`, todate)
      let yestodaySql = dateF.formatTime(`YYYY-mm-dd HH:MM:SS`, tomorrow)
      fromtoSql = `AND ad."endDate" >= '${todaySql}' AND ad."endDate" < '${yestodaySql}'`
      break;
    case 'toweek':
      const today = new Date();
      const dayOfWeek = today.getDay();
      const thisSun = new Date(today);
      thisSun.setDate(today.getDate() + (0 - dayOfWeek));
      thisSun.setHours(0, 0, 0, 0);
      const nextSun = new Date(thisSun);
      nextSun.setDate(thisSun.getDate() + 7);
      let thisSunSql = dateF.formatTime(`YYYY-mm-dd HH:MM:SS`, thisSun)
      let nextSunSql = dateF.formatTime(`YYYY-mm-dd HH:MM:SS`, nextSun)
      fromtoSql = `AND ad."endDate" >= '${thisSunSql}' AND ad."endDate" < '${nextSunSql}'`
      break;
    case 'tomonth':
      const now = new Date();
      const toMon = new Date(now.getFullYear(), now.getMonth(), 1, 0, 0, 0);
      const nextMon = new Date(now.getFullYear(), now.getMonth() + 1, 1, 0, 0, 0);
      let toMonSql = dateF.formatTime(`YYYY-mm-dd HH:MM:SS`, toMon)
      let nextMonSql = dateF.formatTime(`YYYY-mm-dd HH:MM:SS`, nextMon)
      fromtoSql = `AND ad."endDate" >= '${toMonSql}' AND ad."endDate" < '${nextMonSql}'`
      break;
    default:
      break;
  }
  let sql = `WITH RankedUsers AS (
    SELECT ad."user",
      RANK() OVER (ORDER BY SUM(ad."steps") DESC) AS "rank"
    FROM "ActivityData" ad
    LEFT JOIN "_User" u ON u."objectId" = ad."user"
    WHERE ad."company"='${company}'
    AND ad."isDeleted" IS NOT TRUE
    AND ad."status" = 'end'
    ${type?`AND ad."type" = '${type}'`:''}
    ${fromtoSql}
    GROUP BY ad."user"
    LIMIT 200
  )
  SELECT *
  FROM RankedUsers
  WHERE "user" = '${userid||uid}';`
  // console.log(sql)
  let data = await request.customSQL(sql)
  // console.log(data)
  return data
}

/**
 * 获取至今日连续 签到/运动 天数
 * @param {*} uid uid默认当前用户
 * @param {*} table 查询那一张表，签到EventLog/运动ActivityData，默认EventLog
 */
async function getContinuousCount(user_id, table) {
  let todate = new Date(new Date().setHours(0, 0, 0, 0))
  let yestoday = new Date(new Date(todate).setDate(todate.getDate() - 1))
  let yesSql = dateF.formatTime(`YYYY-mm-dd HH:MM:SS`, yestoday)
  let sql = `SELECT * FROM (
  SELECT MAX(t3."created_date") AS "end_date",COUNT(t3."result") AS "c_count",t3."result"
  FROM(
    SELECT *,(t2."created_date"-(t2."row" * INTERVAL '1 day')) AS "result"
    FROM(
      SELECT DISTINCT (t1."created_date"),t1."user",
      ROW_NUMBER() OVER (PARTITION BY t1."user" ORDER BY DATE(t1."created_date")) AS "row"
      FROM (
        SELECT DISTINCT(DATE(el."createdAt")) AS "created_date",el."user"
        FROM "${table||'EventLog'}" el
        WHERE el."isDeleted" IS NOT TRUE
        AND el."company"='${company||''}'
        AND el."user"='${user_id||uid}'
      )t1
    )t2
  )t3
  GROUP BY t3."result"
)t4
WHERE t4."end_date" >='${yesSql}'
ORDER BY t4."end_date" DESC
LIMIT 1`
  // console.log(sql)
  let data = await request.customSQL(sql)
  // console.log(data)
  return data[0]?.c_count
}
/**
 * 获取至今日累计 签到/运动 天数
 * @param {*} uid uid默认当前用户
 * @param {*} table 查询那一张表，签到EventLog/运动ActivityData，默认EventLog
 */
async function getChickCount(user_id, table) {
  let sql = `SELECT COUNT(*)
  FROM (
    SELECT DISTINCT(DATE(el."createdAt")) AS "created_date",el."user"
    FROM "${table||'EventLog'}" el
    WHERE el."isDeleted" IS NOT TRUE
    AND el."company"='${company||''}'
    AND el."user"='${user_id||uid}'
  )t1`
  // console.log(sql)
  let data = await request.customSQL(sql)
  // console.log(data)
  return data[0]?.count
}

/**获取当前位置信息 */
async function getLocation() {
  let address=''
  wx.getLocation({
    type: 'wgs84',
    success: (res) => {
      const {
        latitude,
        longitude
      } = res;
      wx.request({
        url: 'https://api.map.baidu.com/reverse_geocoding/v3/?ak=sHZTomd7grslfP7sPKB8tRgT49FK9TEu&output=json&coordtype=gcj02&location=' + latitude + ',' + longitude,
        data: {},
        header: {
          'Content-Type': 'application/json'
        },
        success: (ops) => {
           address = ops.data.result.formatted_address;
          console.log(address);
        },
        fail: function (resq) {
          wx.showModal({
            title: '信息提示',
            content: '请求失败',
            showCancel: false,
            confirmColor: '#f37938'
          });
        },
      })
    },
    fail: (err) => {
      console.error(err);
      wx.showToast({
        title: '获取位置失败',
        icon: 'none'
      });
    }
  });
}

module.exports = {
  getwalk,
  setEndSport,
  getRanking,
  getUserRank,
  getContinuousCount,
  getChickCount,
  getLocation
};