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'
      });
    }
  });
}
let disList = [5,10,20,50,100,200,500,1000,2000,5000,10000,20000,25000,50000,100000,200000,500000,1000000]
let wxScaleMap = {
  5: 20,
  10: 19,
  20: 18,
  50: 17,
  100: 16,
  200: 15,
  500: 14,
  1000: 13,
  2000: 12,
  5000: 11,
  10000: 10,
  20000: 9,
  25000: 8,
  50000: 7,
  100000: 6,
  200000: 5,
  500000: 4,
  1000000: 3,
}
/**
 * 计算路径比例尺，中心经纬
 * @param {*} points 运动轨迹[{longitude,latitude}]
 */
function getScale(points) {
  if(!points) return{center:null,scale:null}
  let maxLongitude = 0
  let minLatitude = 0
  let minLongitude = 0
  let maxLatitude = 0
  for (let i in points) {
    let item = points[i]
    if (!maxLongitude || !minLatitude || !minLongitude || !maxLatitude) {
      maxLongitude = item.longitude
      minLongitude = item.longitude
      minLatitude = item.latitude
      maxLatitude = item.latitude
      continue
    }
    if (maxLongitude < item.longitude) maxLongitude = item.longitude
    if (minLongitude > item.longitude) minLongitude = item.longitude
    if (minLatitude > item.latitude) minLatitude = item.latitude
    if (maxLatitude < item.latitude) maxLatitude = item.latitude
  }
  let distance =  haversine(maxLatitude, maxLongitude, minLatitude, minLongitude)

  let center = {
    longitude: (maxLongitude + minLongitude) / 2,
    latitude: (minLatitude + maxLatitude) / 2
  }
  return {
    center,scale:wxScaleMap[distance]
  }
}
/**
 * 两点距离-米
 * @param {*} lat1 纬1
 * @param {*} lon1 经1,
 * @param {*} lat2 纬2
 * @param {*} lon2 经2
 */
function haversine(lat1, lon1, lat2, lon2) {
  const R = 6371000;
  const φ1 = toRadians(parseFloat(lat1));
  const φ2 = toRadians(parseFloat(lat2));
  const Δφ = toRadians(parseFloat(lat2) - parseFloat(lat1));
  const Δλ = toRadians(parseFloat(lon2) - parseFloat(lon1));
  const a = Math.sin(Δφ / 2) * Math.sin(Δφ / 2) +
    Math.cos(φ1) * Math.cos(φ2) *
    Math.sin(Δλ / 2) * Math.sin(Δλ / 2);
  const c = 2 * Math.atan2(Math.sqrt(a), Math.sqrt(1 - a));
  const distance = R * c;
  return findValue(distance) ;
}
function findValue(inputNumber) {
  if (inputNumber < disList[0]) {
      return disList[0]; // 返回最小值
  } else if (inputNumber >= disList[disList.length - 1]) {
      return disList[disList.length - 1]; // 返回最大值
  } else {
      let maxValue = -1; // 初始化为-1，表示没有找到合适的值
      for (let i = 0; i < disList.length; i++) {
          if (disList[i] <= inputNumber) {
              maxValue = disList[i]; // 更新最大值
          } else {
              break; // 一旦找到大于inputNumber的值，退出循环
          }
      }
      return maxValue;
  }
}

function toRadians(degrees) {
  return degrees * (Math.PI / 180);
}
module.exports = {
  getwalk,
  setEndSport,
  getRanking,
  getUserRank,
  getContinuousCount,
  getChickCount,
  getLocation,
  getScale
};