模块:Xb2QueryHelper

此模块的文档可以在模块:Xb2QueryHelper/doc创建

local p = {}

p.language = "cn"

local cache = {}

function p.query(sql)
  local cacheKey = p.language .. ":" .. sql
  if cache[cacheKey] then
    return cache[cacheKey]
  end
  local result = mw.xbdb.query(sql, p.language)
  cache[cacheKey] = result
  return result
end

function p.queryOne(sql)
  local result = p.query(sql)
  if #result == 1 then
    result = result[1]
  elseif #result == 0 then
    mw.log("SQL返回结果为空: " .. sql)
    return nil
  else
    error("结果行数大于1，请检查WHERE条件\n" .. sql)
  end
  return result
end

function p.getEnhance(id)
  if type(id) ~= "number" then
    error("id must be number")
  end

  local result =
    p.queryOne(
    [[
    SELECT
      "BTL_Enhance"."Param1",
      "BTL_Enhance"."Param2",
      "BTL_EnhanceEff"."Param",
      "btl_enhance_cap"."name" AS "Caption",
      "btl_buff_ms"."name" AS "BuffName" 
    FROM
      "BTL_Enhance"
      INNER JOIN "BTL_EnhanceEff" ON "BTL_Enhance"."EnhanceEffect" = "BTL_EnhanceEff".row_id
      LEFT JOIN "btl_enhance_cap" ON "BTL_Enhance"."Caption" = "btl_enhance_cap".row_id
      LEFT JOIN "btl_buff_ms" ON "BTL_EnhanceEff"."Name" = "btl_buff_ms".row_id
    WHERE
      "BTL_Enhance".row_id = ]] ..
      id
  )

  local CaptionText =
    p.replaceCaptionParam(
    result.Caption or "",
    {
      Param = result.Param,
      Param1 = result.Param1,
      Param2 = result.Param2
    }
  )

  result.RenderedCaption = CaptionText

  return result
end

function p.replaceCaptionParam(caption, params)
  local str = caption

  local paramStrArray = {}
  local paramStr = ""
  local inStr = false

  for i = 1, mw.ustring.len(str) do
    local c = mw.ustring.sub(str, i, i)

    if c == "]" then
      inStr = false

      if not mw.ustring.find(paramStr, "ML:Enhance") then
        error("找不到 ML:Enhance:" .. paramStr)
      end

      if mw.ustring.find(paramStr, "Param1") then
        paramStrArray.Param1 = paramStr
      elseif mw.ustring.find(paramStr, "Param2") then
        paramStrArray.Param2 = paramStr
      else
        paramStrArray.Param = paramStr
      end
    end

    if inStr then
      paramStr = paramStr .. c
    end

    if c == "[" then
      inStr = true
      paramStr = ""
    end
  end

  for k, v in pairs(paramStrArray) do
    str = mw.ustring.gsub(str, "%[" .. v .. "%]", params[k])
  end

  return str
end

function p.getBuff(id)
  return p.queryOne(
    [[
    SELECT
      "btl_buff_ms"."name" AS "Name",
      "btl_buff_cap"."name" AS "Caption",
      "BaseNum",
      "BaseTime",
      "BaseRecast",
      "BaseProb",
      "Interval" 
    FROM
      "BTL_Buff"
      LEFT JOIN "btl_buff_cap" ON "BTL_Buff"."Caption" = "btl_buff_cap".row_id
      LEFT JOIN "btl_buff_ms" ON "BTL_Buff"."Name" = "btl_buff_ms".row_id
    WHERE
      "BTL_Buff".row_id = ]] ..
      id
  )
end

function p.getArtsDr(id)
  return p.queryOne(
    [[
    SELECT
      btl_arts_dr_ms."name" AS "NameText",
      btl_arts_dr_cap."name" AS "CaptionText",
      "BTL_Arts_Dr".* 
    FROM
      "BTL_Arts_Dr"
      LEFT JOIN btl_arts_dr_ms ON "BTL_Arts_Dr"."Name" = btl_arts_dr_ms.row_id
      LEFT JOIN btl_arts_dr_cap ON "BTL_Arts_Dr"."Caption" = btl_arts_dr_cap.row_id
    WHERE
      "BTL_Arts_Dr".row_id = ]] ..
      id
  )
end

function p.getArtsDrListByDriver(id)
  return p.query(
    [[
    SELECT
      btl_arts_dr_ms."name" AS "NameText",
      btl_arts_dr_cap."name" AS "CaptionText",
      condcapms."name" AS "CondCapText",
      talentcapms."name" AS "TalentCapText",
      "BTL_Arts_Dr".* 
    FROM
      "BTL_Arts_Dr"
      LEFT JOIN btl_arts_dr_ms ON "BTL_Arts_Dr"."Name" = btl_arts_dr_ms.row_id
      LEFT JOIN btl_arts_dr_cap ON "BTL_Arts_Dr"."Caption" = btl_arts_dr_cap.row_id
			LEFT JOIN btl_arts_dr_cap AS condcapms ON "BTL_Arts_Dr"."CondCap" = condcapms.row_id
      LEFT JOIN btl_arts_dr_cap AS talentcapms ON "BTL_Arts_Dr"."TalentCap" = talentcapms.row_id
    WHERE
      "BTL_Arts_Dr"."Driver" = ]] ..
      id
  )
end

function p.getArtsDrListByDriverAndWpnType(driverId, WpnType)
  return p.query(
    [[
    SELECT
      btl_arts_dr_ms."name" AS "NameText",
      btl_arts_dr_cap."name" AS "CaptionText",
      condcapms."name" AS "CondCapText",
      talentcapms."name" AS "TalentCapText",
      "BTL_Arts_Dr".* 
    FROM
      "BTL_Arts_Dr"
      LEFT JOIN btl_arts_dr_ms ON "BTL_Arts_Dr"."Name" = btl_arts_dr_ms.row_id
      LEFT JOIN btl_arts_dr_cap ON "BTL_Arts_Dr"."Caption" = btl_arts_dr_cap.row_id
			LEFT JOIN btl_arts_dr_cap AS condcapms ON "BTL_Arts_Dr"."CondCap" = condcapms.row_id
      LEFT JOIN btl_arts_dr_cap AS talentcapms ON "BTL_Arts_Dr"."TalentCap" = talentcapms.row_id
    WHERE
      "BTL_Arts_Dr"."Driver" = ]] ..
      driverId .. [[ AND "BTL_Arts_Dr"."WpnType" = ]] .. WpnType
  )
end

function p.getDr(id)
  id = tonumber(id)
  if type(id) ~= "number" then
    error("id must be number")
  end
  if id <= 0 then
    return nil
  end
  local result =
    p.queryOne(
    [[
    SELECT
      chr_dr_ms."name" AS "NameText",
      category1."name" AS "FavoriteCategory1Text",
      itm1ms."name" AS "FavoriteItem1Text",
      category2."name" AS "FavoriteCategory2Text",
      itm2ms."name" AS "FavoriteItem2Text",
      "CHR_Dr".* 
    FROM
      "CHR_Dr"
      LEFT JOIN chr_dr_ms ON "CHR_Dr"."Name" = chr_dr_ms.row_id
      LEFT JOIN menu_favorite_category AS category1 ON "CHR_Dr"."FavoriteCategory1" - 11 = category1.row_id
      LEFT JOIN menu_favorite_category AS category2 ON "CHR_Dr"."FavoriteCategory2" - 11 = category2.row_id
      LEFT JOIN "ITM_FavoriteList" AS item1 ON "CHR_Dr"."FavoriteItem1" = item1.row_id
      LEFT JOIN itm_favorite AS itm1ms ON item1."Name" = itm1ms.row_id
      LEFT JOIN "ITM_FavoriteList" AS item2 ON "CHR_Dr"."FavoriteItem2" = item2.row_id
      LEFT JOIN itm_favorite AS itm2ms ON item2."Name" = itm2ms.row_id 
    WHERE
      "CHR_Dr".row_id = ]] ..
      id
  )

  local WpnType = result.WpnType
  WpnType = mw.ustring.sub(WpnType, 2)
  WpnType = mw.ustring.sub(WpnType, 1, -2)

  local WpnTypeArray = {}
  for str in mw.ustring.gmatch(WpnType, "([^,]+)") do
    table.insert(WpnTypeArray, str)
  end

  local condition = {}
  for i, v in ipairs(WpnTypeArray) do
    if tonumber(v) > 0 then
      table.insert(condition, i)
    end
  end
  condition = table.concat(condition, ", ")

  result.WpnType =
    p.query(
    [[
    SELECT
      "ITM_PcWpnType".row_id,
      itm_pcwpntype_ms."name" AS "Name",
      menu_role_name_ms."name" AS "Role",
      "ITM_PcWpnType"."JustRange",
      "ITM_PcWpnType"."JustRange2"
    FROM
      "ITM_PcWpnType"
      LEFT JOIN itm_pcwpntype_ms ON itm_pcwpntype_ms.row_id = "ITM_PcWpnType"."Name"
      LEFT JOIN menu_role_name_ms ON menu_role_name_ms.row_id = "ITM_PcWpnType"."Role"    
    WHERE
      "ITM_PcWpnType".row_id IN (]] ..
      condition .. [[)
    ]]
  )

  return result
end

function p.getSkillDrList(id)
  local driverIds = {
    [1] = true,
    [2] = true,
    [3] = true,
    [4] = true,
    [5] = true,
    [6] = true,
    [17] = true,
    [18] = true,
    [19] = true
  }
  if not driverIds[tonumber(id)] then
    return nil
  end

  if tonumber(id) < 10 then
    id = "0" .. tostring(tonumber(id))
  end

  local result =
    p.query(
    [[
      SELECT
        btl_skill_dr_name."name" AS "NameText",
        "BTL_Skill_Dr_Table]] ..
      id ..
        [[".*,
        "BTL_Skill_Dr"."Enhance"
      FROM
        "BTL_Skill_Dr_Table]] ..
          id ..
            [["
      LEFT JOIN "BTL_Skill_Dr" ON "BTL_Skill_Dr_Table]] ..
              id ..
                [["."SkillID" = "BTL_Skill_Dr".row_id
      LEFT JOIN  btl_skill_dr_name ON btl_skill_dr_name.row_id = "BTL_Skill_Dr"."Name"
      ORDER BY
        "Round",
        "RowNum",
        "ColumnNum"
    ]]
  )

  for _, row in ipairs(result) do
    row.Enhance = p.getEnhance(tonumber(row.Enhance))
  end
  return result
end

function p.getNpcPopByNpcId(npcId)
  if type(npcId) ~= "number" then
    error("getNpcPopByNpcId 的参数不是number")
  end

  -- 获取所有NpcPops表名
  local tables =
    p.query(
    mw.ustring.format(
      [[SELECT tablename FROM pg_catalog.pg_tables WHERE "schemaname" = '%s' AND "tablename" != 'ma90a_FLD_NpcPop' AND "tablename" like '%%_FLD_NpcPop']],
      p.language
    )
  )

  local result = {}
  for _, t in ipairs(tables) do
    local sql = mw.ustring.format([[SELECT * FROM "%s" WHERE "NpcID" = '%s']], t.tablename, npcId)
    local resultInSingleTable = p.query(sql)
    for _, npcPopRow in ipairs(resultInSingleTable) do
      -- Npc存在Group字段，则用Group覆盖自身数据
      if npcPopRow.Group > 0 then
        local groupData =
          p.queryOne(
          mw.ustring.format(
            [[SELECT "memberNum", "ScenarioFlagMin", "ScenarioFlagMax", "QuestFlag", "QuestFlagMin", "QuestFlagMax", "TimeRange", "Condition" FROM "FLD_NpcGroupId" WHERE "row_id" = '%d']],
            npcPopRow.Group
          )
        )
        for k, v in pairs(groupData) do
          npcPopRow[k] = v
        end
      end
      table.insert(result, npcPopRow)
    end
  end

  return result
end

function p.getNpcByName(npcName)
  return p.query(
    mw.ustring.format(
      [[SELECT * FROM "RSC_NpcList" WHERE "Name" IN (SELECT row_id FROM fld_npcname WHERE "name"='%s')]],
      npcName
    )
  )
end

function p.getNpcPopByNpcName(npcName)
  local npcs = p.getNpcByName(npcName)
  for _, npc in ipairs(npcs) do
    local npcId = npc.row_id
    npc.Pops = p.getNpcPopByNpcId(npcId)
  end
  return npcs
end

local function getEventTableName(id)
  if id > 60000 then
    return "EVT_listDeb01"
  end
  if (id > 40000) then
    return "EVT_listTlk01"
  end
  if (id > 30000) then
    return "EVT_listFev01"
  end
  if (id > 20000) then
    return "EVT_listQst01"
  end
  if (id > 19000) then
    return "EVT_listBl"
  end
  return "EVT_listBf"
end

local function checkTableExist(tablename)
  local talkTableCount =
    p.queryOne(
    mw.ustring.format(
      [[SELECT COUNT(*) FROM pg_catalog.pg_tables WHERE "schemaname" = '%s' AND  "tablename" = '%s']],
      p.language,
      tablename
    )
  )

  if talkTableCount.count == 0 then
    -- mw.log("eventTable: " .. tablename .. " not exist")
    return false
  end

  return true
end

function p.getDialogByEventId(eventId)
  if type(eventId) ~= "number" then
    error("event id must be number")
  end

  if eventId == 0 then
    return {}
  end

  local eventTable = getEventTableName(eventId)
  local event = p.queryOne(mw.ustring.format([[SELECT "evtName" FROM "%s" WHERE "row_id" = %s]], eventTable, eventId))
  local talkTable = event.evtName .. "_ms"

  if checkTableExist(talkTable) == false then
    return {}
  end
  local talks = p.query(mw.ustring.format([[SELECT "name" FROM "%s" WHERE "style" != 62 AND "style" != 0]], talkTable))
  local ret = {}
  for _, talk in ipairs(talks) do
    table.insert(ret, talk.name)
  end
  return ret
end

local function getQuestListTableName(id, ira)
  if ira then
    -- 黄金国
    if id > 2000 then
      return "FLD_QuestListNormalIra", "fld_quest_normal"
    end
  else
    -- 本篇
    if id > 7000 then
      return "FLD_QuestListAchievement", "fld_quest_achievement"
    end
    if id > 6000 then
      return "FLD_QuestListMercenaries", "fld_quest_mercenaries"
    end
    if id > 5000 then
      return "FLD_QuestListBlade", "fld_quest_blade"
    end
    if id > 2000 then
      return "FLD_QuestListNormal", "fld_quest_normal"
    end
    if id > 1000 then
      return "FLD_QuestListMini", "fld_quest_mini"
    end
  end
end

function p.getQuestTitleByQuestId(questId, ira)
  if type(questId) ~= "number" then
    error("quest id must be number")
  end

  if questId == 0 then
    return nil
  end

  local questTable, questTableMs = getQuestListTableName(questId, ira)
  if questTable == nil then
    return nil
  end

  -- 通过QuestTitle查询实际任务名
  local function getQuestTitle(titleId)
    if titleId <= 0 then
      return nil
    end
    local questTitle =
      p.queryOne(mw.ustring.format([[SELECT "name" FROM "%s" WHERE "row_id" = '%s']], questTableMs, titleId))
    return questTitle.name
  end

  local quest =
    p.queryOne(
    mw.ustring.format([[SELECT "QuestTitle", "PRTQuestID" FROM "%s" WHERE "row_id" = '%s']], questTable, questId)
  )

  -- 若结果是子任务，通过PRTQuestID字段查询父任务
  if quest.QuestTitle > 0 then
    return getQuestTitle(quest.QuestTitle)
  elseif quest.PRTQuestID > 0 then
    local prtQuest =
      p.queryOne(
      mw.ustring.format([[SELECT "QuestTitle" FROM "%s" WHERE "row_id" = '%d']], questTable, quest.PRTQuestID)
    )
    local phase = questId - quest.PRTQuestID
    return getQuestTitle(prtQuest.QuestTitle), phase
  end
  return nil
end

function p.getConditonList(id)
  if id <= 0 then
    return nil
  end
  return p.queryOne(mw.ustring.format([[SELECT * FROM "FLD_ConditionList" WHERE "row_id" = '%d']], id))
end

function p.getSubCondition(conditionType, id)
  if id <= 0 then
    return nil
  end
  local table = "FLD_Condition" .. conditionType
  return p.queryOne(mw.ustring.format([[SELECT * FROM "%s" WHERE "row_id" = '%d']], table, id))
end

local function getCharacterTable(id)
  if id > 2000 then
    return "RSC_NpcList", "fld_npcname"
  end
  if id > 1000 then
    return "CHR_Bl", "chr_bl_ms"
  end
  return "CHR_Dr", "chr_dr_ms"
end

function p.getCharacterName(id)
  local tablename, tablems = getCharacterTable(id)
  local character = p.queryOne(mw.ustring.format([[SELECT "Name" FROM "%s" WHERE "row_id" = '%d']], tablename, id))
  if character == nil then
    return nil
  end
  local characterMs =
    p.queryOne(mw.ustring.format([[SELECT "name" FROM "%s" WHERE "row_id" = '%d']], tablems, character.Name))
  if characterMs == nil then
    return nil
  end
  return characterMs.name
end

function p.getFieldSkillName(id)
  local tablename, tablems = "FLD_FieldSkillList", "fld_fieldskilltxt"
  local dataRow = p.queryOne(mw.ustring.format([[SELECT "Name" FROM "%s" WHERE "row_id" = '%d']], tablename, id))
  if dataRow == nil then
    return nil
  end
  local dataMs = p.queryOne(mw.ustring.format([[SELECT "name" FROM "%s" WHERE "row_id" = '%d']], tablems, dataRow.Name))
  if dataMs == nil then
    return nil
  end
  return dataMs.name
end

function p.getAchievementSetName(id)
  local tablename = "CHR_Bl"
  local data =
    p.query(
    mw.ustring.format(
      [[SELECT
      "row_id",
      "BArts1",
      "BArts2",
      "BArts3",
      "BSkill1",
      "BSkill2",
      "BSkill3",
      "FSkill1",
      "FSkill2",
      "FSkill3",
      "ArtsAchievement1",
      "ArtsAchievement2",
      "ArtsAchievement3",
      "SkillAchievement1",
      "SkillAchievement2",
      "SkillAchievement3",
      "FskillAchivement1",
      "FskillAchivement2",
      "FskillAchivement3",
      "KeyAchievement" 
    FROM
      "%s" 
    WHERE
      "ArtsAchievement1" = '%d' 
      OR "ArtsAchievement2" = '%d' 
      OR "ArtsAchievement3" = '%d' 
      OR "SkillAchievement1" = '%d' 
      OR "SkillAchievement2" = '%d' 
      OR "SkillAchievement3" = '%d' 
      OR "FskillAchivement1" = '%d' 
      OR "FskillAchivement2" = '%d' 
      OR "FskillAchivement3" = '%d' 
      OR "KeyAchievement" = '%d']],
      tablename,
      id,
      id,
      id,
      id,
      id,
      id,
      id,
      id,
      id,
      id
    )
  )

  if #data > 0 then
    data = data[1]
  else
    error("在CHR_Bl中找不到包含 achievement set: " .. id .. " 的数据")
  end

  local bladeName = p.getCharacterName(data.row_id)

  if id == data.KeyAchievement then
    return "阶段解锁", bladeName
  end

  local achievementTypeMap = {
    ArtsAchievement1 = "BArts1",
    ArtsAchievement2 = "BArts2",
    ArtsAchievement3 = "BArts3",
    SkillAchievement1 = "BSkill1",
    SkillAchievement2 = "BSkill2",
    SkillAchievement3 = "BSkill3",
    FskillAchivement1 = "FSkill1",
    FskillAchivement2 = "FSkill2",
    FskillAchivement3 = "FSkill3"
  }

  local function getSkillTable(type)
    local type = mw.ustring.sub(type, 1, -2)
    if type == "BArts" then
      return "BTL_Arts_Bl", "btl_arts_bl_ms"
    end
    if type == "BSkill" then
      return "BTL_Skill_Bl", "btl_skill_bl_name"
    end
    if type == "FSkill" then
      return "FLD_FieldSkillList", "fld_fieldskilltxt"
    end
  end

  local achievementType
  for k, v in pairs(achievementTypeMap) do
    if data[k] == id then
      achievementType = achievementTypeMap[k]
    end
  end

  local tablename, tablems = getSkillTable(achievementType)
  local skillId = data[achievementType]

  local skill = p.queryOne(mw.ustring.format([[SELECT "Name" FROM "%s" WHERE "row_id" = '%d']], tablename, skillId))
  if skill == nil then
    return nil
  end

  local skillMs = p.queryOne(mw.ustring.format([[SELECT "name" FROM "%s" WHERE "row_id" = '%d']], tablems, skill.Name))
  if skillMs == nil then
    return nil
  end

  return skillMs.name, bladeName
end

local function getItemTable(id)
  if id > 61000 then
    return "ITM_EtherCrystal"
  end
  if id > 60000 then
    return "ITM_HanaAssist", "itm_orb"
  end
  if id > 59000 then
    return "ITM_HanaNArtsSet", "itm_hana_narts_set_ms"
  end
  if id > 58000 then
    return "ITM_HanaArtsEnh", "itm_orb"
  end
  if id > 57000 then
    return "ITM_HanaAtr", "itm_hana_atr_ms"
  end
  if id > 56000 then
    return "ITM_HanaRole", "itm_hana_role_ms"
  end
  if id > 50000 then
    return "ITM_BoosterList", "itm_booster"
  end
  if id > 45000 then
    return "ITM_CrystalList", "itm_crystal"
  end
  if id > 40000 then
    return "ITM_FavoriteList", "itm_favorite"
  end
  if id > 35000 then
    return "ITM_TresureList", "itm_tresure"
  end
  if id > 30000 then
    return "ITM_CollectionList", "itm_collection"
  end
  if id > 27000 then
    return "ITM_EventList", "itm_evt"
  end
  if id > 26000 then
    return "ITM_InfoList", "itm_info"
  end
  if id > 25000 then
    return "ITM_PreciousList", "itm_precious"
  end
  if id > 20000 then
    return "ITM_SalvageList", "itm_salvage"
  end
  if id > 17000 then
    return "ITM_OrbEquip", "itm_orb"
  end
  if id > 14000 then
    return "ITM_Orb", "itm_orb"
  end
  if id > 10000 then
    return "ITM_PcWpnChip", "itm_pcwpnchip_ms"
  end
  if id > 5000 then
    return "ITM_PcWpn", "itm_pcwpn_ms"
  end
  return "ITM_PcEquip", "itm_pcequip"
end

function p.getItemName(id)
  local tablename, tablems = getItemTable(id)
  if tablename == nil or tablems == nil then
    error("item id: " .. id .. "非法")
  end

  local dataRow = p.queryOne(mw.ustring.format([[SELECT "Name" FROM "%s" WHERE "row_id" = '%d']], tablename, id))
  if dataRow == nil then
    return nil
  end
  local dataMs = p.queryOne(mw.ustring.format([[SELECT "name" FROM "%s" WHERE "row_id" = '%d']], tablems, dataRow.Name))
  if dataMs == nil then
    return nil
  end

  local itemName = dataMs.name
  itemName = mw.ustring.gsub(itemName, "\\%]", "」")
  itemName = mw.ustring.gsub(itemName, "\\%[", "「")
  return itemName
end

function p.getWeatherList()
  local data =
    p.query(
    [[
    SELECT
      "fld_mapinfo"."name" 
    FROM
      "FLD_WeatherInfo"
      INNER JOIN "fld_mapinfo" ON "FLD_WeatherInfo"."msg" = "fld_mapinfo"."row_id" 
    ORDER BY
      "FLD_WeatherInfo".row_id
  ]]
  )

  local result = {}

  for _, v in ipairs(data) do
    table.insert(result, v.name)
  end
  return result
end

return p