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

/**
 * 计算运动累加状况-页面使用
 * @param {*} column 累加字段
 */
async function getwalk(column) {
  let typeList = ['walk', 'run']
  let distance = 0
  for (let i in typeList) {
    let type = typeList[i]
    let d = await getData(column, type) || 0
    console.log(d)
    distance = distance + d
  }
  return distance < 0 ? 0 : distance
}


/**
 * 计算运动累加状况-getwalk使用
 * @param {*} column 累加字段 (未知则'distance')
 * @param {*} type 运动类型 walk/run(未知则不限制)
 */
async function getData(column, type) {
  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)

  let sql = `SELECT SUM(t1."num")- SUM(t1."ago_num") 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
}


/**查看活动运动数据（当前活动对应运动是否结束） */
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 默认20
 * @param {*} skip 默认0
 * @param {*} order 默认 DESC
 */
async function getRanking(type, fromto, limit, skip,order) {
  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'
  ${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
}


module.exports = {
  getwalk,
  setEndSport,
  getRanking
};