def update_to_database(self, need_order_recashe=True): cursor = conn.cursor() request = "update guilds set guild_name = %s, members = %s, commander_id = %s, division = %s, chat_id = %s, " \ "chat_name = %s, invite_link = %s, orders_enabled = %s, pin_enabled = %s,disable_notification = %s, " \ "assistants = %s, settings = %s, api_info = %s, mailing_enabled = %s, last_updated = %s, castle = %s, " \ "alliance_id = %s " \ "where guild_tag = %s" try: cursor.execute( request, (self.name, self.members, self.commander_id, self.division, self.chat_id, self.chat_name, self.invite_link, self.orders_enabled, self.pin_enabled, self.disable_notification, self.assistants, json.dumps(self.settings), json.dumps( self.api_info), self.mailing_enabled, self.last_updated, self.castle, self.alliance_id, self.tag)) except Exception: logging.error(traceback.format_exc()) return -1 if need_order_recashe: pass # update_request_queue.put(["update_guild", self.id]) cursor.close() return 0
def create(self): request = "insert into guild_quest(guild, castle, percent, last_update) values (%s, %s, %s, %s)" cursor = conn.cursor() cursor.execute( request, (self.guild, self.castle, self.percent, self.last_update)) cursor.close()
def update_to_database(self): cursor = conn.cursor() # Without this is unstable, however i don`t know how many time does it take # # to process this instruction request = "update players set username = %s, nickname = %s, guild_tag = %s, guild = %s, lvl= %s, " \ "attack = %s, defense = %s, stamina = %s, pet = %s, equipment = %s, game_class = %s, " \ "class_skill_lvl = %s, castle = %s, last_updated = %s, reputation = %s, created = %s, status = %s, " \ "guild_history = %s, exp = %s, api_info = %s, stock = %s, settings = %s, exp_info = %s, " \ "class_info = %s, mobs_info = %s, tea_party_info = %s, quests_info = %s, hp = %s, max_hp = %s," \ "pogs = %s, mana = %s, max_stamina = %s, gold = %s " \ "where id = %s" eq_to_db = self.equipment_to_json() quests_to_db = self.quests_to_json() # print(self.id, self.username, self.nickname, self.guild_tag, self.guild, self.lvl, # self.attack, self.defense, self.stamina, self.pet, eq_to_db, self.game_class, self.class_skill_lvl) cursor.execute(request, (self.username, self.nickname, self.guild_tag, self.guild, self.lvl, self.attack, self.defense, self.stamina, self.pet, eq_to_db, self.game_class, self.class_skill_lvl, self.castle, self.last_updated, self.reputation, self.created, self.status, self.guild_history, self.exp, json.dumps(self.api_info), json.dumps(self.stock), json.dumps(self.settings), json.dumps(self.exp_info, ensure_ascii=False), json.dumps(self.class_info), json.dumps(self.mobs_info), json.dumps(self.tea_party_info, ensure_ascii=False), quests_to_db, self.hp, self.max_hp, self.pogs, self.mana, self.max_stamina, self.gold, self.id)) cursor.close() return 0
def resource_return(bot, job): cursor = conn.cursor() if job.context[1].get("status") not in ["sawmill", "quarry"]: logging.warning( "Status not in [\"sawmill\", \"quarry\"], status = {}".format( job.context[1].get("status"))) return statuses_to_res = {"sawmill": "wood", "quarry": "stone"} res = statuses_to_res.get(job.context[1].get("status")) count = 1 throne = Location.get_location(2) throne.treasury.change_resource(res, count) player = Player.get_player(job.context[0]) player.reputation += 3 player.update_to_database() job.context[1].update({"status": "castle_gates"}) buttons = get_general_buttons(job.context[1], player) if job.context[0] in construction_jobs: try: construction_jobs.pop(job.context[0]) except Exception: logging.error(traceback.format_exc()) request = "insert into castle_logs(player_id, action, result, additional_info, date) values (%s, %s, %s, %s, %s)" cursor.execute( request, (player.id, "collect_resources", 1, json.dumps({ "resource": res, "count": count }), datetime.datetime.now(tz=moscow_tz).replace(tzinfo=None))) bot.send_message(chat_id=job.context[0], text="Вы успешно добыли {}. Казна обновлена. Получено 3 🔘" "".format("дерево" if res == "wood" else "камень"), reply_markup=buttons) on_resource_return(player, res)
def update(self): request = "update guild_quest set castle = %s, percent = %s, last_update = %s where guild = %s" cursor = conn.cursor() cursor.execute( request, (self.castle, self.percent, self.last_update, self.guild)) cursor.close()
def get_mobs_info_by_link(link): cursor = conn.cursor() request = "select mob_names, mob_lvls, date_created, helpers, buffs, minutes, created_player from mobs where link = %s" cursor.execute(request, (link,)) row = cursor.fetchone() cursor.close() return row
def check_guilds_api_access(bot=None, job=None): """ Проверка наличия доступа к АПИ у зарегистрированных гильдий. Вызывается отложенно, выполняется в фоне. :param bot: Bot :param job: Job :return: None """ if job is None: reset = False else: try: reset = job.context.get("reset") or False except Exception: reset = False cursor = conn.cursor() if reset: logging.info("Clearing data about players with guilds API access") request = "update guilds set api_info = (api_info::jsonb - 'api_players')" cursor.execute(request) logging.info("Checking API access for guilds") request = "select guild_id from guilds where (api_info -> 'api_players') is null or " \ "(api_info -> 'api_players')::text = '[]'::text" cursor.execute(request) rows = cursor.fetchall() if not rows: logging.info("All guilds have data about players with API access") return for row in rows: guild = Guild.get_guild(guild_id=row[0]) search_for_players_with_api_access(guild) logging.info("Information about players with API access requested")
def update_location_to_database(self): cursor = conn.cursor() request = "update locations set state = %s, building_process = %s, special_info = %s where location_id = %s" cursor.execute(request, (self.state, self.building_process, json.dumps(self.special_info, ensure_ascii=False) if self.special_info is not None else None, self.id))
def update_daily_quests(bot, job): cursor = conn.cursor() request = "select id from players" cursor.execute(request) row = cursor.fetchone() while row is not None: player = Player.get_player(row[0]) if player is None: continue daily_quests: [Quest] = player.quests_info.get("daily_quests") if daily_quests is None: daily_quests = [] player.quests_info.update({"daily_quests": daily_quests}) else: daily_quests.clear() forbidden_list = [] for i in range(3): quest = copy.deepcopy(random.choice(list(quests.values()))) limit = 0 while (quest.id in forbidden_list or quest.skip_selection) and limit < 5: quest = copy.deepcopy(random.choice(list(quests.values()))) limit += 1 quest.start(player) if quest.daily_unique: forbidden_list.append(quest.id) daily_quests.append(quest) player.update_to_database() row = cursor.fetchone() time.sleep(1) plan_update_daily_quests()
def del_quest(guild): guild = guild.upper() if len(guild) < 2 or len(guild) > 3: return cursor = conn.cursor() request = "delete from guild_quest where guild = %s" cursor.execute(request, (guild, )) cursor.close()
def all_quests(): cursor = conn.cursor() request = "select guild, castle, percent, last_update from guild_quest" cursor.execute(request) rows = cursor.fetchall() cursor.close() quests = [GuildQuest(*row) for row in rows] return quests
def get_quality_shops(cls, place): request = "select id from shops where specialization -> '{}' is not null order by qualitycraftlevel desc, " \ "last_seen desc".format(place) cursor = conn.cursor() cursor.execute(request) rows = cursor.fetchall() cursor.close() return list(map(lambda shop_id: cls.get_shop(shop_id), rows))
def create(self): request = "insert into duels(winner_id, winner_name, winner_tag, winner_castle, winner_level, loser_id, loser_name, loser_tag," \ "loser_castle, loser_level, date) values (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)" cursor = conn.cursor() cursor.execute(request, ( self.winner_id, self.winner_name, self.winner_tag, self.winner_castle, self.winner_level, self.loser_id, self.loser_name, self.loser_tag, self.loser_castle, self.loser_level, self.date)) cursor.close()
def autospend_start(bot, job): logging.info("Spending players gold...") cursor = conn.cursor() request = "select id from players where (settings ->> 'autospend')::boolean is true;" cursor.execute(request) player_ids = cursor.fetchall() for player_id in player_ids: player = Player.get_player(player_id, notify_on_error=False) autospend_player(bot, player) cursor.close()
def get_quest(guild: str, last_update: int): cursor = conn.cursor() request = "select guild, castle, percent, last_update from guild_quest" \ " where guild = '{}' limit 1".format(guild) cursor.execute(request) row = cursor.fetchone() cursor.close() if row is None: return None quest = GuildQuest(*row) return quest
def construction_return(bot, job): cursor = conn.cursor() player_id = job.context[0] user_data = job.context[1] if user_data.get("status") not in ["construction"]: return location_id = user_data.get("construction_id") if location_id is None: bot.send_message(chat_id=player_id, text="Ошибка поиска локации.") return location = Location.get_location(location_id) if location is None: bot.send_message(chat_id=player_id, text="Ошибка поиска локации.") return print(location.name, location.state, location.building_process) if location.state is True or location.building_process < 0: bot.send_message( chat_id=player_id, text= "Локация уже построена или стройка не начиналась. Возможно, локацию " "построили в то время, пока вы добирались до стройки.") return location.building_process += 3 player = Player.get_player(job.context[0]) if location.building_process >= location.need_clicks_to_construct: location.state = True bot.send_message( chat_id=player_id, text="Локация успешно построена! Вы положили последний камень!") player.reputation += 50 - CONSTRUCTION_REPUTATION player.reputation += CONSTRUCTION_REPUTATION player.update_to_database() location.update_location_to_database() user_data.update({"status": "central_square"}) if "construction_id" in user_data: user_data.pop("construction_id") if job.context[0] in construction_jobs: try: construction_jobs.pop(job.context[0]) except Exception: logging.error(traceback.format_exc()) buttons = get_general_buttons(user_data, player) request = "insert into castle_logs(player_id, action, result, additional_info, date) values (%s, %s, %s, %s, %s)" cursor.execute(request, (player.id, "construction", 1, json.dumps({"location_id": location_id}), datetime.datetime.now(tz=moscow_tz).replace(tzinfo=None))) bot.send_message( chat_id=job.context[0], text="Вы вернулись со стройки. Получено <code>{}</code> 🔘".format( CONSTRUCTION_REPUTATION), reply_markup=buttons, parse_mode='HTML')
def rangers_notify_start(bot, update): cursor = conn.cursor() time_to_battle = get_time_remaining_to_battle() print("time_to_battle", time_to_battle) try: callback_chat_id = update.message.chat_id except AttributeError: try: callback_chat_id = int(update) except TypeError: return count = 0 request = "select id from players where class_skill_lvl is not NULL" cursor.execute(request) row = cursor.fetchone() while row is not None: player = Player.get_player(row[0]) if player is None: row = cursor.fetchone() continue if player.settings is not None and player.settings.get( "rangers_notify") is False: row = cursor.fetchone() continue guild = Guild.get_guild(guild_id=player.guild) if guild is None: row = cursor.fetchone() continue telegram_username = player.username username = player.nickname class_skill_lvl = player.class_skill_lvl context = [telegram_username, username, guild.chat_id] print(class_skill_lvl) time_to_aim_mins = ranger_aiming_minutes[class_skill_lvl] if \ class_skill_lvl < len(ranger_aiming_minutes) else 40 time_to_aim = datetime.timedelta(minutes=time_to_aim_mins) print("time_to_aim", time_to_aim) time_to_notify = time_to_battle - time_to_aim - datetime.timedelta( minutes=1) print(time_to_notify) # time_to_notify = datetime.timedelta(minutes=1) # TEST if time_to_notify >= datetime.timedelta(minutes=0): job.run_once(ranger_notify, time_to_notify, context=context) row = cursor.fetchone() count += 1 cursor.close() bot.send_message( chat_id=callback_chat_id, text="Запланировано оповещение <b>{0}</b> бедных лучников".format( count), parse_mode='HTML')
def create(self): request = "insert into shops(link, name, ownerTag, ownerName, ownerCastle, kind, mana, offers, " \ "castleDiscount, guildDiscount, specialization, qualityCraftLevel, maintenanceEnabled, " \ "maintenanceCost, last_seen) values (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)" cursor = conn.cursor() cursor.execute(request, (self.link, self.name, self.ownerTag, self.ownerName, self.ownerCastle, self.kind, self.mana, self.get_json_offers(), self.castleDiscount, self.guildDiscount, self.get_json_specialization(), self.qualityCraftLevel, self.maintenanceEnabled, self.maintenanceCost, get_current_datetime())) cursor.close()
def update(self): request = "update shops set id = %s, link = %s, name = %s, ownerTag = %s, ownerName = %s, ownerCastle = %s, " \ "kind = %s, mana = %s, offers = %s, castleDiscount = %s, guildDiscount = %s, specialization = %s, " \ "qualityCraftLevel = %s, maintenanceEnabled = %s, maintenanceCost = %s, last_seen = %s where id = %s" cursor = conn.cursor() cursor.execute(request, (self.id, self.link, self.name, self.ownerTag, self.ownerName, self.ownerCastle, self.kind, self.mana, self.get_json_offers(), self.castleDiscount, self.guildDiscount, self.get_json_specialization(), self.qualityCraftLevel, self.maintenanceEnabled, self.maintenanceCost, self.last_seen, self.id)) cursor.close()
def get_duel(date: datetime, winner_id: str, loser_id: str): cursor = conn.cursor() request = "select winner_id, winner_name, winner_tag, winner_castle, winner_level, loser_id," \ "loser_name, loser_tag, loser_castle, loser_level, date " \ "from duels " \ "where winner_id = %s and loser_id = %s and date = %s limit 1" cursor.execute(request, (winner_id, loser_id, date)) row = cursor.fetchone() cursor.close() if row is None: return None duel = Duels(*row) return duel
def load_location(self, other_process=False): new_cursor = cursor if other_process: new_cursor = conn.cursor() request = "select state, building_process, special_info from locations where location_id = %s" new_cursor.execute(request, (self.id,)) row = new_cursor.fetchone() if other_process: cursor.close() if row is None: return -1 self.state = row[0] self.building_process = row[1] self.special_info = row[2]
def update_stock_for_fails(bot, job): """ Если по какой-то причине ответ от API получен не был для определённых игроков, то выполняется повторный запрос """ cursor = conn.cursor() request = "select id, api_info from players where api_info ->> 'token' is not null and (api_info ->> " \ "'change_stock_send')::boolean is true" cursor.execute(request) row = cursor.fetchone() while row is not None: player = Player.get_player(row[0], notify_on_error=False, new_cursor=cursor) cwapi.update_stock(player.id, player=player) row = cursor.fetchone() request = "select guild_id from guilds where (api_info -> 'change_stock_send')::text::boolean is true" cursor.execute(request) rows = cursor.fetchall() count_all = 0 for row in rows: guild = Guild.get_guild(guild_id=row[0]) api_players = guild.api_info.get("api_players") # if len(api_players) > 0: # Сомнительно, часто гильдии не обновляются из-за багов АПИ # api_players.pop(0) if not api_players: # guild.api_info.clear() bot.send_message( chat_id=SUPER_ADMIN_ID, parse_mode='HTML', text= "Гильдию <b>{}</b> невозможно обновить - нет игроков с доступом к АПИ." "".format(guild.tag)) continue player_id = api_players[0] cwapi.update_guild_info(player_id) logging.info("Requested {} update".format(guild.tag)) count_all += 1 if count_all > 0: bot.send_message( chat_id=SUPER_ADMIN_ID, parse_mode='HTML', text= "Повторно запрошено обновление <b>{}</b> гильдий, игроки с доступом к АПИ подвинуты" "".format(count_all)) if len(rows) > 0: bot.send_message(chat_id=SUPER_ADMIN_ID, text="Начата проверка доступа к АПИ у гильдий") check_guilds_api_access(bot, None)
def arena_notify(bot, job): cursor = conn.cursor() for guild_id in Guild.guild_ids: guild = Guild.get_guild(guild_id=guild_id) if guild is None or guild.division == "Луки": continue if guild.settings is None or guild.settings.get("arena_notify") in [ None, True ]: bot.send_message( chat_id=guild.chat_id, text= "Через час обнуление арен и дневного лимита опыта за крафт.") time.sleep(1) plan_arena_notify()
def grassroots_update_stock(bot, job): """ Запрос на обновление стока всех игроков, и гильдий """ print("starting updating") change_send = job.context.get("change_send") or False cursor = conn.cursor() request = "select id, api_info from players where api_info ->> 'token' is not null" cursor.execute(request) row = cursor.fetchone() count = 0 count_all = 0 while row is not None: player = Player.get_player(row[0], notify_on_error=False, new_cursor=cursor) count_all += 1 player.api_info.update({"change_stock_send": change_send}) player.update() count += 1 if change_send else 0 cwapi.update_stock(player.id, player=player) row = cursor.fetchone() bot.send_message( chat_id=SUPER_ADMIN_ID, text="Запрошено обновление {} стоков, установлено {} флагов для отправки" "".format(count_all, count)) # Обновление стока гильдий request = "select guild_id from guilds where (api_info -> 'api_players') is not null and " \ "json_array_length(api_info -> 'api_players') > 0" cursor.execute(request) rows = cursor.fetchall() count, count_all = 0, 0 for row in rows: guild = Guild.get_guild(guild_id=row[0]) player_id = guild.api_info.get("api_players")[0] cwapi.update_guild_info(player_id) logging.info("Requested {} update".format(guild.tag)) count_all += 1 if change_send: guild.api_info.update({"change_stock_send": True}) guild.update_to_database(need_order_recashe=False) count += 1 bot.send_message( chat_id=SUPER_ADMIN_ID, text= "Запрошено обновление {} гильдий, установлено {} флагов для отправки" "".format(count_all, count))
def load_worldtop(battle_id: int = None) -> dict: """ Загружает /worldtop после битвы с battle_id :return: { str: int } """ if battle_id is None: battle_id = count_battle_id() cursor = conn.cursor() request = "select ferma, amber, oplot, rassvet, tortuga, night, skala from worldtop where battle_id = %s" cursor.execute(request, (battle_id, )) row = cursor.fetchone() if not row: return {} worldtop = {k: v for k, v in zip(castles, row)} sort_worldtop(worldtop) cursor.close() return worldtop
def today_guilds_duels(guild_tag): date = datetime.datetime.utcnow().replace(tzinfo=timezone.utc).astimezone(moscow_tz) if date.hour < 13: date = date - timedelta(days=1) date = date.replace(hour=13, minute=0, second=0) timestamp = int(date.timestamp()) cursor = conn.cursor() request = "select winner_id, winner_name, winner_tag, winner_castle, winner_level, loser_id," \ "loser_name, loser_tag, loser_castle, loser_level, date " \ "from duels " \ "where (winner_tag = %s or loser_tag = %s) and date > %s" cursor.execute(request, (guild_tag, guild_tag, timestamp)) rows = cursor.fetchall() cursor.close() duels = [Duels(*row) for row in rows] return duels, date
def get_shop(shop_id: int = None, link: str = None): arg_name = "id" if shop_id is not None else "link" arg = shop_id if shop_id is not None else link cursor = conn.cursor() request = "select id, link, name, ownerTag, ownerName, ownerCastle, kind, mana, offers, castleDiscount, " \ "guildDiscount, specialization, qualityCraftLevel, maintenanceEnabled, maintenanceCost, last_seen " \ "from shops " \ "where {} = %s limit 1".format(arg_name) cursor.execute(request, (arg, )) row = cursor.fetchone() cursor.close() if row is None: return None shop = Shop(*row) if shop.last_update is None or shop.last_seen > shop.last_update: Shop.last_update = shop.last_seen return shop
def count_battle_stats(self, battle_id: int = None): if battle_id is None: battle_id = count_battle_id() - 1 cursor = conn.cursor() request = "select r.attack, r.defense, r.exp, r.gold, r.stock " \ "from reports r join players p on r.player_id = p.id where p.guild = %s and r.battle_id = %s" cursor.execute(request, (self.id, battle_id)) row = cursor.fetchone() total_attack, total_defense, total_exp, total_gold, total_stock = 0, 0, 0, 0, 0 while row: attack, defense, exp, gold, stock = row total_attack += attack total_defense += defense total_exp += exp total_gold += gold total_stock += stock row = cursor.fetchone() return total_attack, total_defense, total_exp, total_gold, total_stock
def save_worldtop(worldtop: dict, battle_id: int = None): cursor = conn.cursor() try: if battle_id is None: battle_id = count_battle_id() request = "insert into worldtop(battle_id, " args = [battle_id] for k, v in list(worldtop.items()): request += "{}, ".format(emodji_to_castle_names.get(k)) args.append(v) request = request[:-2] + ')' + 'values(%s, %s, %s, %s, %s, %s, %s, %s)' cursor.execute(request, args) except psycopg2.IntegrityError: request = "update worldtop set " for k, v in list(worldtop.items()): request += "{} = {}, ".format(emodji_to_castle_names.get(k), v) request = request[:-2] + " where battle_id = {}".format(battle_id) cursor.execute(request) except Exception: logging.error(traceback.format_exc()) cursor.close()
def remember_exp(bot, job): cursor = conn.cursor() request = "select id, exp, exp_info from players" cursor.execute(request) rows = cursor.fetchall() battle_id = count_battle_id(None) for row in rows: player_id, exp, exp_info = row player = Player.get_player(player_id) if exp_info is None: exp_info = {} exp_info.update({str(battle_id): exp}) exp_info = { k: v for k, v in sorted(list(exp_info.items()), key=lambda x: int(x[0])) } player.exp_info = exp_info player.update() # request = "update players set exp_info = %s where id = %s" # cursor.execute(request, (json.dumps(exp_info, ensure_ascii=False), player_id)) plan_remember_exp()