model/amuletsearcher.py

   1 # -*- coding: utf-8 -*-
   2
   3 u"""
   4 お守り検索を行うクラス
   5 2013/12/20 written by kei9
   6 """
   7
   8 import sqlite3
   9 import csv
  10
  11 import amulettable
  12 import skilltable
  13 import skillminmaxtable
  14 import seed2thresholdtable
  15 import seed2skill2table
  16 import sufficienttable
  17
  18
  19 SELECT_SEED2S_FROM_THRESHOLD_AND_SKILL2_SQL = u"""
  20     select {{threshold_table}}.{threshold_seed2} from {{threshold_table}}
  21     inner join {{skill2_table}} on {{threshold_table}}.{threshold_seed2} = {{skill2_table}}.{skill2_seed2}
  22     where
  23     ({{skill2_table}}.{skill2_id1} = {{skill2_id}} and {{threshold_table}}.{threshold_1} >= {{threshold}}) or
  24     ({{skill2_table}}.{skill2_id2} = {{skill2_id}} and {{threshold_table}}.{threshold_2} >= {{threshold}}) or
  25     ({{skill2_table}}.{skill2_id3} = {{skill2_id}} and {{threshold_table}}.{threshold_3} >= {{threshold}}) or
  26     ({{skill2_table}}.{skill2_id4} = {{skill2_id}} and {{threshold_table}}.{threshold_4} >= {{threshold}}) or
  27     ({{skill2_table}}.{skill2_id5} = {{skill2_id}} and {{threshold_table}}.{threshold_5} >= {{threshold}}) or
  28     ({{skill2_table}}.{skill2_id6} = {{skill2_id}} and {{threshold_table}}.{threshold_6} >= {{threshold}}) or
  29     ({{skill2_table}}.{skill2_id7} = {{skill2_id}} and {{threshold_table}}.{threshold_7} >= {{threshold}})
  30     """.format(
  31         skill2_seed2=seed2skill2table.COL_SEED2,
  32         skill2_id1=seed2skill2table.COL_SKILL2_ID1,
  33         skill2_id2=seed2skill2table.COL_SKILL2_ID2,
  34         skill2_id3=seed2skill2table.COL_SKILL2_ID3,
  35         skill2_id4=seed2skill2table.COL_SKILL2_ID4,
  36         skill2_id5=seed2skill2table.COL_SKILL2_ID5,
  37         skill2_id6=seed2skill2table.COL_SKILL2_ID6,
  38         skill2_id7=seed2skill2table.COL_SKILL2_ID7,
  39         threshold_seed2=seed2thresholdtable.COL_SEED2,
  40         threshold_1=seed2thresholdtable.COL_THRESHOLD1,
  41         threshold_2=seed2thresholdtable.COL_THRESHOLD2,
  42         threshold_3=seed2thresholdtable.COL_THRESHOLD3,
  43         threshold_4=seed2thresholdtable.COL_THRESHOLD4,
  44         threshold_5=seed2thresholdtable.COL_THRESHOLD5,
  45         threshold_6=seed2thresholdtable.COL_THRESHOLD6,
  46         threshold_7=seed2thresholdtable.COL_THRESHOLD7)
  47
  48
  49 class AmuletSearcher(object):
  50     u"""お守りの検索を行うクラス"""
  51     def __init__(self, db_cursor):
  52         u""" db_cursor: cursor of sqlite3 database """
  53         self._cursor = db_cursor
  54         accessor = amulettable.AmuletTableAccessor(db_cursor)
  55         self._amu_id2name, self._amu_name2id = accessor.get_dict()
  56
  57         accessor = skilltable.SkillTableAccessor(db_cursor)
  58         self._skill_id2name, self._skill_name2id = accessor.get_dict()
  59
  60         accessor = skillminmaxtable.SkillMinMaxTableAccessor(db_cursor)
  61         self._minmax_dict = accessor.get_minmax_by_id()
  62
  63         accessor = seed2skill2table.Seed2Skill2TableAccessor(db_cursor)
  64         self._amu_id2skill2_table = accessor.get_amulet_id2table_name_dict()
  65
  66         self._acc_sufficient = sufficienttable.SufficientTableAccessor(db_cursor)
  67         self._acc_threshold = seed2thresholdtable.Seed2ThresholdTableAccessor(db_cursor)
  68
  69     def simple_select_seed2s_from_ids(self, amulet_id, skill1_id, skill1_val, skill2_id, skill2_val, slot_num):
  70         u""" 指定されたスキルId,スキル値、スロット数を元にして、
  71         Seed1との組み合わせを無視したSeed2候補の簡易検索を行う
  72         return (suff_val, threshold, th1_seed2s, th2_seed2s)"""
  73         suff_val = self.get_sufficient_value(amulet_id, skill1_id, skill1_val, skill2_id, skill2_val)
  74         if suff_val is not None:
  75             slot_ths = self._acc_sufficient.select_thresholds_by_id(amulet_id, suff_val)
  76             threshold = slot_ths[slot_num - 1]
  77             if skill2_id in self._skill_id2name:
  78                 skill2_table = self._amu_id2skill2_table[amulet_id]
  79                 # for threshold 1
  80                 threshold_table = seed2thresholdtable.NAME.format(index=1)
  81                 sql = SELECT_SEED2S_FROM_THRESHOLD_AND_SKILL2_SQL.format(
  82                         threshold_table=threshold_table,
  83                         skill2_table=skill2_table,
  84                         threshold=threshold,
  85                         skill2_id=skill2_id)
  86                 self._cursor.execute(sql)
  87                 th1_seed2s = set([x[0] for x in self._cursor.fetchall()])
  88
  89                 # for threshold 2
  90                 threshold_table = seed2thresholdtable.NAME.format(index=2)
  91                 sql = SELECT_SEED2S_FROM_THRESHOLD_AND_SKILL2_SQL.format(
  92                         threshold_table=threshold_table,
  93                         skill2_table=skill2_table,
  94                         threshold=threshold,
  95                         skill2_id=skill2_id)
  96                 self._cursor.execute(sql)
  97                 th2_seed2s = set([x[0] for x in self._cursor.fetchall()])
  98
  99             else:
 100                 th1_seed2s = self._acc_threshold.select_seed2s_from_all_threshold(threshold, 1)
 101                 th2_seed2s = self._acc_threshold.select_seed2s_from_all_threshold(threshold, 2)
 102         else:
 103             th1_ssed2s, th2_seed2s = set(), set()
 104             threshold = None
 105
 106         return (suff_val, threshold, th1_seed2s, th2_seed2s)
 107
 108     def simple_select_seed2s_from_names(self,
 109             amulet_name, skill1_name, skill1_val, skill2_name, skill2_val, slot_num):
 110         u""" 指定されたお守り名、スキル名,スキル値、スロット数を元にして、
 111         Seed1との組み合わせを無視したSeed2候補の簡易検索を行う
 112         return (suff_val, threshold, th1_seed2s, th2_seed2s)"""
 113         amulet_id = self._amu_name2id[amulet_name]
 114         skill1_id = self._skill_name2id[skill1_name] if skill1_name in self._skill_name2id else None
 115         skill2_id = self._skill_name2id[skill2_name] if skill2_name in self._skill_name2id else None
 116         return self.simple_select_seed2s_from_ids(amulet_id, skill1_id, skill1_val, skill2_id, skill2_val, slot_num)
 117
 118
 119
 120     def get_sufficient_value(self, amulet_id, skill1_id, skill1_val, skill2_id, skill2_val):
 121         u""" 充足値を計算する。
 122         指定したスキルが見つからない場合や、充足値を計算できない場合はNoneを返す
 123         skill2_nameが存在しないスキルであればskill1のみから計算する
 124         return sufficient_val
 125         """
 126         skill1_minmax, skill2_minmax = self._minmax_dict[amulet_id]
 127         if skill1_id in skill1_minmax:
 128             min1, max1 = skill1_minmax[skill1_id]
 129             skill1_val = 0 if skill1_val < 0 else skill1_val
 130         else:
 131             # cannot find skill1
 132             return None
 133
 134         if skill2_id in skill2_minmax:
 135             min2, max2 = skill2_minmax[skill2_id]
 136             skill2_val = 0 if skill2_val < 0 else skill2_val
 137         else:
 138             max2 = 1
 139             skill2_val = 0
 140
 141         if skill1_val > max1 or skill2_val > max2:
 142             # over value
 143             return None
 144
 145         suff_val = (10 * skill1_val) // max1 + (10 * skill2_val) // max2
 146         return suff_val