Calculate Promotions

Submitted by Brutus5000 on Sun, 07/24/2016 - 01:53
Goal

Calculate and perform promotions, if applicable

This is done for each character that is not dead.

Input data
  • <battle id> which cause the calculation
Preconditions

Gather affected characters and meta-data:

  • SELECT
    • characters.id,
    • characters.faction,
    • ( SELECT SUM(amount) FROM xp_journal WHERE xp_journal.fk_character = <characters.id> )  AS total_xp,
    • ( SELECT newrank  FROM promotions WHERE fk_character = <character id> ORDER BY created_at DESC LIMIT 1 ) AS current_rank
  • FROM characters
  • WHERE
    • characters.id IN (SELECT fk_character FROM battle_participants WHERE battle_participants.id = <battle id>) AND
    • characters.killed_by IS NULL
  • ORDER BY total_xp DESC

 

FOR EACH row in result:

Check wether promotion is reached:

Get ranks available by XP:

  • SELECT
    • level,
    • max_per_faction,
    • (
      • SELECT COUNT (*)
      • FROM promotions
      • JOIN characters ON promotions.fk_character = characters.id
      • WHERE characters.faction = @faction AND killed_by is NULL
    • ) AS current_in_faction
  • FROM ranks
  • WHERE level > @current_rank AND @current_xp > xp_min
  • HAVING max_per_faction <= current_in_faction
  • ODER BY level DESC
  • LIMIT 1


IF no result THEN no promotion

Database transaction

FOR EACH result from preconditions:

INSERT INTO promotions (fk_character, fk_battle, new_rank, created_at) VALUES (characters.id, <battle id>, level, NOW() )

Add new comment

Restricted HTML

  • Allowed HTML tags: <a href hreflang> <em> <strong> <cite> <blockquote cite> <code> <ul type> <ol start type> <li> <dl> <dt> <dd> <h2 id> <h3 id> <h4 id> <h5 id> <h6 id>
  • Lines and paragraphs break automatically.
  • Web page addresses and email addresses turn into links automatically.