// 学校相关

const util = require('../utils')
const db = require('../data')
const dayjs = require('dayjs')
const EnDeNum = require('../utils/encrypt-decrypt-number')
let [sql, rs] = ['', null]

/**
 * 学校设置获取（id是加密后的）
 * get: /school/:id/setting
 */
exports.Setting = async ctx => {
  const school = EnDeNum.decrypt(ctx.params.id)
  if (!util.isInteger(school)) { throw { code: -2 } }
  sql = 'select top 1 id,picture,intro'
    + ',title,type,expiredate,logo,contact'
    + ',zice,GroupGuidance,guestbook,qq'
    + ',fds,complete_info,modinfo,crisis,crisislb,testintro,tuanfulicense'
    + ',yuyue_arrange,yydate,zixun_close_date,yuyue_before_time,yuyue_week_limit,zixun_times_limit'
    + ',zixun_needfeedback_next,yuyue_test_days,yuyue_test_lb,yuyuelicense,yuyue_close_msg,password'
    + ',\'' + EnDeNum.encrypt(school) + '\' as [key]'
    + ' from school where id=' + school
  rs = await db.select(sql)
  if (rs) { ctx.body = { code: 0, data: rs } }
  else { throw { code: 1, message: '单位（' + school + '）未找到' } }
}

/**
 * 获取会员类型/学校可用量表列表，t可传入量表类别（默认输出ID、英文名、中文名、友好名称、图片、专业简介、一句话简介，mini输出ID、中文名）
 * get: /school/:id/scalelist
 */
exports.ScaleList = async ctx => {
  const school = ctx.params.id
  const type = ctx.query.t
  const word = ctx.query.word
  const mini = ctx.query.mini
  if (!util.isInteger(school)) { throw { code: -2 } }
  else {
    const tmpsql = mini ? 'select id,name_ch as name from lb where available=1 and (' : 'select id,name_en,name_ch,name_fr,picture,intro1,intro2 from lb where available=1 and ('
    // 按类别
    let lbType = null
    if (util.isInteger(type)) { lbType = await db.scalar('select top 1 lb from lbtype where id=' + type) }
    let lbSchool = await db.scalar('select top 1 lb from member_type where id=' + school)
    if (lbSchool === '') { throw { code: 1, message: '单位(' + school + ')未设置量表' } }
    else if (lbType) { sql = tmpsql + 'id in (' + lbType + ') and id in (' + lbSchool + ')' }
    else { sql = tmpsql + 'id in (' + lbSchool + ') or membertype=' + school }
    sql += ')'
    if (word) { sql += ` and name_ch like '%${word}%'` }
    sql += ' order by sort,id'
    rs = await db.select(sql)
    ctx.body = { code: 0, data: rs || [] }
  }
}

/**
 * 通过搜索或传入地区type（加密后）获取学校列表：
 * get: /school
 */
exports.SchoolList = async ctx => {
  const top = ctx.query.top || 10
  const word = db.escape(ctx.query.word)
  const parenttype = EnDeNum.decrypt(ctx.query.type)
  sql = `select top ${top} id,title,member_type_name,sum_student,sum_test from v_school where 1=1`
  word && (sql += ` and title like '%${word}%'`)
  parenttype && (sql += ' and parid=' + parenttype)
  rs = await db.select(sql)
  rs && rs.forEach(item => {
    item.name = item.title || item.member_type_name
    delete item.title
    delete item.member_type_name
  })
  ctx.body = { code: 0, data: rs || [] }
}

/**
 * 学校管理后台菜单
 * get: /school/:id/adminmenu
 */
exports.AdminMenu = async ctx => {
  const school = ctx.params.id
  if (!util.isInteger(school)) { throw { code: -2 } }
  else {
    // 根据学校配置需要屏蔽的菜单
    rs = await db.select('select top 1 type,crisis,nomenuid,GroupGuidance,sandtable from school where id=' + school)
    if (rs === null) { throw { code: 1, message: `单位(${school})不存在` } }
    else {
      let notinid = ''
      const school_type = rs.type

      if (rs.nomenuid) { notinid = rs.nomenuid }
      else if (school !== '1') { notinid = '13,14,20,41,79,197,198,199,200,208,209' }
      else { notinid = '1000' }

      // 没有危机干预功能
      if (!rs.crisis) { notinid += ',2,13,14,30,31,32,33' }
      // 没有团辅
      if (!rs.GroupGuidance) { notinid += ',6,64,65,204,205' }
      // 没有沙盘
      if (!rs.sandtable) { notinid += ',196,201,202,203' }
      // 中小学
      if (school_type <= 2) { notinid += ',12,26,59' }
      // 小学、中学、大学和中职以外的单位
      if (school_type > 3 && school_type !== 16) { notinid += ',95' }
      // 咨询机构
      if (school_type === 6) { notinid += ',59' }

      // 查询
      if (notinid === '') { sql = 'select id,parid,name,intro,icon,url,[right],fee from school_menu order by sort' }
      else { sql = 'select id,parid,name,intro,icon,url,[right],fee from school_menu where id not in (' + notinid + ') order by sort' }
      rs = await db.select(sql)

      // 替换说法
      let langOld = ['[School]', '[Dept]', '[Student]', '[Parent]']
      let lang = null
      if (school_type === 4 || school_type === 6) { lang = ['单位', '单位', '来访者', '家属'] }
      else if (school_type === 5) { lang = ['单位', '部门', '员工', '家属'] }
      else if (school_type === 7) { lang = ['单位', '部门', '官兵', '家属'] }
      else if (school_type === 9) { lang = ['单位', '单位', '教师', '配偶'] }
      else if (school_type === 10) { lang = ['单位', '部门', '民警', '家属'] }
      else if (school_type === 11) { lang = ['单位', '部门', '公务员', '家属'] }
      else if (school_type === 13) { lang = ['单位', '部门', '学员', '家属'] }
      else if (school_type <= 2) { lang = ['学校', '班级', '学生', '家长'] }
      else { lang = ['学校', '院系', '学生', '家长'] }
      rs.forEach(item => {
        for (let i in langOld) {
          item.name = item.name.replace(langOld[i], lang[i])
          item.intro = item.intro ? item.intro.replace(langOld[i], lang[i]) : ''
        }
      })
      ctx.body = { code: 0, data: rs }
    }
  }
}

/**
 * 学校后台首页/大屏可视化各种汇总数据（id是加密后的）
 * get: /school/:id/summary-count
 */
exports.SummaryCount = async ctx => {
  const school = EnDeNum.decrypt(ctx.params.id)
  const dept = ctx.query.dept || school
  const grade = ctx.query.grade
  const group = ctx.query.group
  const act = JSON.parse('[' + (ctx.query.act || '') + ']')
  let t1 = ctx.query.t1
  let t2 = ctx.query.t2
  if (!util.isInteger(school) || !util.isInteger(dept)) { throw { code: -2 } }
  if (t2) { t2 = dayjs(t2).add(1, 'day').format('YYYY-MM-DD') }

  const json = []
  const today = dayjs().format('YYYY-MM-DD')
  let tmpsql = ''
  let deptpath = ''
  let lb_zsqzfx = 121
  let lb_asts = 111
  let lb_nlee = 160

  // 获取学校类型决定量表ID
  const school_type = await db.scalar('select top 1 type from school where id=' + school)
  if ([1, 2, 16].includes(school_type)) {
    lb_zsqzfx = 159
  } else if ([4, 5, 6, 10, 11].includes(school_type)) {
    lb_zsqzfx = 190
    lb_asts = 178
    lb_nlee = 191
  } else if ([7, 13].includes(school_type)) {
    lb_zsqzfx = 164
    lb_asts = 178
    lb_nlee = 179
  }

  // 获取院系路径
  if (dept !== school) { deptpath = await db.scalar(`select top 1 path from member_type where id=${dept}`) }

  // 查询条件
  tmpsql = ' and ' + (dept === school ? `member_type_top=${school}` : `member_type_path+',' like '${deptpath},%'`)
  util.isDate(t1) && (tmpsql += ` and datetime>='${t1}'`)
  util.isDate(t2) && (tmpsql += ` and datetime<'${t2}'`)
  if (util.isInteger(grade) && grade !== '0') { tmpsql += ` and grade=${grade}` }
  if (util.isInteger(group) && group !== '0') { tmpsql += ` and customgroup=${group}` }

  // 0学生人数（日期字段名称不同，重组查询条件）
  if (act.includes(0)) {
    sql = 'select count(id) from v_member with (nolock) where state=5 and isstudent=1'
    sql += ' and ' + (dept === school ? `member_type_top=${school} ` : `member_type_path+',' like '${deptpath},%'`)
    util.isDate(t1) && (sql += ` and regtime>='${t1}'`)
    util.isDate(t2) && (sql += ` and regtime<'${t2}'`)
    if (util.isInteger(grade)) { sql += ` and grade=${grade}` }
    if (util.isInteger(group)) { sql += ` and customgroup=${group}` }
    rs = await db.scalar(sql)
    json[0] = rs
  }

  // 1访谈邀请
  if (act.includes(1)) {
    sql = 'select count(id) from v_interview_invite where 1=1' + tmpsql
    rs = await db.scalar(sql)
    json[1] = rs
  }

  // 2预约咨询（日期字段名称不同，重组查询条件）
  if (act.includes(2)) {
    sql = 'select count(id) from v_zx where status=0'
    sql += ' and ' + (dept === school ? `member_type_top=${school}  ` : `member_type_path+',' like '${deptpath} ,%'`)
    if (t1) { sql += ` and zxtime>='${t1}'` }
    if (t2) { sql += ` and zxtime<'${t2}'` }
    if (util.isInteger(grade)) { sql += ` and grade=${grade}` }
    if (util.isInteger(group)) { sql += ` and customgroup=${group}` }
    rs = await db.scalar(sql)
    json[2] = rs
  }

  // 3潜在风险评估
  if (act.includes(3)) {
    sql = `select count(id) from v_test with (nolock) where lb=${lb_zsqzfx} and valid=1` + tmpsql
    rs = await db.scalar(sql)
    json[3] = rs
  }

  // 4自杀风险评估
  if (act.includes(4)) {
    sql = `select count(id) from v_test with (nolock) where lb=${lb_asts} and valid=1` + tmpsql
    rs = await db.scalar(sql)
    json[4] = rs
  }

  // 5鉴别评估（年级字段名称不同，重组查询条件）
  if (act.includes(5)) {
    sql = 'select count(id) from v_crisis_evaluate where 1=1'
    sql += ' and ' + (dept === school ? `member_type_top=${school}` : `member_type_path+',' like '${deptpath} ,%'`)
    if (t1) { sql += ` and  datetime>='${t1}'` }
    if (t2) { sql += ` and datetime<'${t2}'` }
    if (util.isInteger(grade)) { sql += ` and member_grade=${grade}` }
    if (util.isInteger(group)) { sql += ` and customgroup=${group}` }
    rs = await db.scalar(sql)
    json[5] = rs
  }

  // 6生活事件动态评估
  if (act.includes(6)) {
    sql = `select count(id) from v_test with (nolock) where lb=${lb_nlee} and valid=1` + tmpsql
    rs = await db.scalar(sql)
    json[6] = rs
  }

  // 7个案追踪
  if (act.includes(7)) {
    sql = 'select count(id) from v_crisis_case where 1=1' + tmpsql
    rs = await db.scalar(sql)
    json[7] = rs
  }

  // 8测评
  if (act.includes(8)) {
    sql = 'select count(id) from v_test with (nolock) where valid=1 and lb not in (107,108,160,169,179,191) and [group]<5' + tmpsql
    rs = await db.scalar(sql)
    json[8] = rs
  }

  // 9今日预约
  if (act.includes(9)) {
    sql = `select count(id) from v_zx where status<>1 and zxtime>='${today}' and zxtime<'${dayjs().add(1, 'day').format('YYYY-MM-DD')}'`
    sql += ' and ' + (dept === school ? `member_type_top=${school}` : `member_type_path+',' like '${deptpath},%'`)
    if (util.isInteger(grade)) { ` and grade=${grade}` }
    if (util.isInteger(group)) { ` and customgroup=${group}` }
    rs = await db.scalar(sql)
    json[9] = rs
  }

  // 10未来7日预约
  if (act.includes(10)) {
    sql = `select count(id) from v_zx where status<>1 and zxtime>='${today}' and zxtime<='${dayjs().add(8, 'day').format('YYYY-MM-DD')}'`
    sql += ' and ' + (dept === school ? `member_type_top=${school}` : `member_type_path+',' like '${deptpath},%'`)
    if (util.isInteger(grade)) { sql += ` and grade=${grade}` }
    if (util.isInteger(group)) { sql += ` and customgroup=${group}` }
    rs = await db.scalar(sql)
    json[10] = rs
  }

  // 11今日登录管理员
  if (act.includes(11)) {
    sql = `select count(id) from v_member with (nolock) where (isschooladmin=1 or isconsultant=1) and lasttime>='${today}' and member_type_top=${school}`
    rs = await db.scalar(sql)
    json[11] = rs
  }

  // 12今日登录学生
  if (act.includes(12)) {
    sql = `select count(id) from v_member with (nolock) where isstudent=1 and lasttime>='${today}' and member_type_top=${school}`
    rs = await db.scalar(sql)
    json[12] = rs
  }

  // 13今日咨询记录
  if (act.includes(13)) {
    sql = `select count(id) from v_zx where status>=4 and zxtime>='${today}'`
    sql += ' and ' + (dept === school ? `member_type_top=${school}` : `member_type_top+',' like '${deptpath},%'`)
    if (util.isInteger(grade)) { ` and grade=${grade}` }
    if (util.isInteger(group)) { ` and customgroup=${group}` }
    rs = await db.scalar(sql)
    json[13] = rs
  }

  // 14所有文章
  if (act.includes(14)) {
    sql = `select count(id) from v_article where membertype=${school}`
    rs = await db.scalar(sql)
    json[14] = rs
  }

  // 15最近半小时登录过的人数
  if (act.includes(15)) {
    const now = dayjs()
    const time = now.subtract(30, 'minutes').format('YYYY-MM-DD HH:mm')
    const v = await db.scalar(`select count(id) from v_member with (nolock) where member_type_top=${school} and lasttime>='${time}'`)
    json[15] = v
    // json[15] = util.RndNum(100, 999)
  }

  // 输出
  ctx.body = { code: 0, data: json }
}

/**
 * 获取危机干预个人扩展信息问题（暂未用）
 * get: /school/member-question
 */
exports.MemberQuestion = async ctx => {
  rs = await db.select('select id,question,answer,type from member_question where [group]=1 order by sort')
  ctx.body = { code: 0, data: rs }
}

/**
 * 查询学校内学号是否已经存在（存在data=学生id，不存在data=0）
 * get: /school/:id/number/exist
 */
exports.NumberExist = async ctx => {
  const school = ctx.params.id
  const number = db.escape(ctx.query.n)
  if (/^\d+$/.test(school) && number) {
    rs = await db.select(`select top 1 id from v_member with (nolock) where member_type_top=${school} and number='${number}'`)
    ctx.body = { code: 0, data: rs ? rs.id : 0 }
  } else { ctx.body = { code: -2 } }
}

/**
 * 对外公共接口
 * get: /school/pub/xxx
 */
exports.Public = {
  // 检测各项参数
  Check: async (school, key, pagesize = 10, page = 1) => {
    if (!util.isInteger(school) || !util.isMD5(key) || !util.isInteger(pagesize) || !util.isInteger(page)) {
      throw { code: -2 }
    } else if (Number(pagesize) > 100) {
      throw { code: 4, message: '每页最大允许100条' }
    } else {
      const keystr = await db.scalar('select top 1 keystr from school where id=' + school)
      if (keystr === null) { throw { code: 1, message: '单位（' + school + '）未找到' } }
      else if (keystr === '') { throw { code: 2, message: '接口关闭' } }
      else if (key !== util.MD5(school + keystr)) { throw { code: 3, message: '参数（key）验证未通过' } }
    }
  },
  // 测评数据
  Test: async ctx => {
    const school = ctx.query.school
    const key = ctx.query.key
    const pagesize = ctx.query.pagesize
    const page = ctx.query.page
    await this.Public.Check(school, key, pagesize, page)
    sql = 'select number,realname,lbnamech as lbname,convert(varchar,datetime,20) as datetime,score_raw,score_t,normal,result from v_test with (nolock) where valid=1 and member_type_top=' + school + ' order by id desc'
    const data = await db.page(sql, pagesize, page)
    ctx.body = { code: 0, data }
  },
  // 预约/咨询数据
  Zixun: async ctx => {
    const school = ctx.query.school
    const key = ctx.query.key
    const pagesize = ctx.query.pagesize
    const page = ctx.query.page
    await this.Public.Check(school, key, pagesize, page)
    sql = 'select number,realname,mobile,convert(varchar,datetime,20) as datetime,consultantname,convert(varchar,yydate,23) as yydate,yytime,convert(varchar,zxtime,20) as zxtime,way,question,content,status from v_zx where member_type_top=' + school + ' order by id desc'
    const data = await db.page(sql, pagesize, page)
    ctx.body = { code: 0, data }
  },
  // 咨询师列表
  Consultant: async ctx => {
    const school = ctx.query.school
    const key = ctx.query.key
    const pagesize = ctx.query.pagesize
    const page = ctx.query.page
    await this.Public.Check(school, key, pagesize, page)
    sql = 'select number,realname,sexname as sex,age,mobile,email from v_member where member_type_top=' + school + ' and isconsultant=1 order by id'
    const data = await db.page(sql, pagesize, page)
    ctx.body = { code: 0, data }
  }
}