1: <?php
  2: 
  3: namespace Budabot\User\Modules;
  4: 
  5: /**
  6:  * @Instance
  7:  *
  8:  * Commands this controller contains:
  9:  *  @DefineCommand(
 10:  *      command     = 'whatbuffs',
 11:  *      accessLevel = 'all',
 12:  *      description = 'Find items or nanos that buff an ability or skill',
 13:  *      help        = 'whatbuffs.txt'
 14:  *  )
 15:  */
 16: class WhatBuffsController {
 17:     
 18:     public $moduleName;
 19: 
 20:     /** @Inject */
 21:     public $http;
 22: 
 23:     /** @Inject */
 24:     public $text;
 25:     
 26:     /** @Inject */
 27:     public $db;
 28:     
 29:     /** @Inject */
 30:     public $util;
 31: 
 32:     /** @Inject */
 33:     public $commandAlias;
 34:     
 35:     /** @Inject */
 36:     public $itemsController;
 37:     
 38:     /** @Logger */
 39:     public $logger;
 40:     
 41:     /** @Setup */
 42:     public function setup() {
 43:         $this->db->loadSQLFile($this->moduleName, "item_buffs");
 44:         $this->db->loadSQLFile($this->moduleName, "skills");
 45:         $this->db->loadSQLFile($this->moduleName, "item_types");
 46:     }
 47: 
 48:     /**
 49:      * @HandlesCommand("whatbuffs")
 50:      * @Matches("/^whatbuffs$/i")
 51:      */
 52:     public function whatbuffsCommand($message, $channel, $sender, $sendto, $args) {
 53:         $blob = '';
 54:         $data = $this->db->query("SELECT DISTINCT name FROM skills ORDER BY name ASC");
 55:         forEach ($data as $row) {
 56:             $blob .= $this->text->makeChatcmd($row->name, "/tell <myname> whatbuffs $row->name") . "\n";
 57:         }
 58:         $blob .= "\nItem Extraction Info provided by Unk";
 59:         $msg = $this->text->makeBlob("WhatBuffs - Choose Skill", $blob);
 60:         $sendto->reply($msg);
 61:     }
 62: 
 63:     /**
 64:      * @HandlesCommand("whatbuffs")
 65:      * @Matches("/^whatbuffs (arms|back|chest|deck|feet|fingers|hands|head|hud|legs|nano|neck|shoulders|unknown|util|weapon|wrists)$/i")
 66:      */
 67:     public function whatbuffs2Command($message, $channel, $sender, $sendto, $args) {
 68:         $type = ucfirst(strtolower($args[1]));
 69:         
 70:         if ($this->verifySlot($type)) {
 71:             $sql = "
 72:                 SELECT s.name AS skill, COUNT(1) AS num
 73:                 FROM aodb
 74:                 JOIN item_types i ON aodb.highid = i.item_id
 75:                 JOIN item_buffs b ON aodb.highid = b.item_id
 76:                 JOIN skills s ON b.attribute_id = s.id
 77:                 WHERE i.item_type = ?
 78:                 GROUP BY skill
 79:                 HAVING num > 0
 80:                 ORDER BY skill ASC";
 81:             $data = $this->db->query($sql, $type);
 82:             $blob = '';
 83:             forEach ($data as $row) {
 84:                 $blob .= $this->text->makeChatcmd(ucfirst($row->skill), "/tell <myname> whatbuffs $type $row->skill") . " ($row->num)\n";
 85:             }
 86:             $blob .= "\nItem Extraction Info provided by Unk";
 87:             $msg = $this->text->makeBlob("WhatBuffs $type - Choose Skill", $blob);
 88:         } else {
 89:             $msg = "Could not find any items of type <highlight>$type<end>.";
 90:         }
 91:         $sendto->reply($msg);
 92:     }
 93: 
 94:     /**
 95:      * @HandlesCommand("whatbuffs")
 96:      * @Matches("/^whatbuffs (arms|back|chest|deck|feet|fingers|hands|head|hud|legs|nano|neck|shoulders|unknown|util|weapon|wrists) (.+)$/i")
 97:      */
 98:     public function whatbuffs3Command($message, $channel, $sender, $sendto, $args) {
 99:         $type = $args[1];
100:         $skill = $args[2];
101: 
102:         if ($this->verifySlot($type)) {
103:             $msg = $this->showSearchResults($type, $skill);
104:         } else {
105:             $msg = "Could not find any items of type <highlight>$type<end> for skill <highlight>$skill<end>.";
106:         }
107:         $sendto->reply($msg);
108:     }
109: 
110:     /**
111:      * @HandlesCommand("whatbuffs")
112:      * @Matches("/^whatbuffs (.+) (arms|back|chest|deck|feet|fingers|hands|head|hud|legs|nano|neck|shoulders|unknown|util|weapon|wrists)$/i")
113:      */
114:     public function whatbuffs4Command($message, $channel, $sender, $sendto, $args) {
115:         $skill = $args[1];
116:         $type = $args[2];
117: 
118:         if ($this->verifySlot($type)) {
119:             $msg = $this->showSearchResults($type, $skill);
120:         } else {
121:             $msg = "Could not find any items of type <highlight>$type<end> for skill <highlight>$skill<end>.";
122:         }
123:         $sendto->reply($msg);
124:     }
125: 
126:     /**
127:      * @HandlesCommand("whatbuffs")
128:      * @Matches("/^whatbuffs (.+)$/i")
129:      */
130:     public function whatbuffs5Command($message, $channel, $sender, $sendto, $args) {
131:         $skill = $args[1];
132: 
133:         $data = $this->searchForSkill($skill);
134:         $count = count($data);
135: 
136:         if ($count == 0) {
137:             $msg = "Could not find skill <highlight>$skill<end>.";
138:         } else if ($count > 1) {
139:             $blob .= "Choose a skill:\n\n";
140:             forEach ($data as $row) {
141:                 $blob .= $this->text->makeChatcmd(ucfirst($row->name), "/tell <myname> whatbuffs $row->name") . "\n";
142:             }
143:             $blob .= "\nItem Extraction Info provided by Unk";
144:             $msg = $this->text->makeBlob("WhatBuffs - Choose Skill", $blob);
145:         } else {
146:             $skillId = $data[0]->id;
147:             $skillName = $data[0]->name;
148:             $sql = "
149:                 SELECT i.item_type, COUNT(1) AS num
150:                 FROM aodb
151:                 JOIN item_types i ON aodb.highid = i.item_id
152:                 JOIN item_buffs b ON aodb.highid = b.item_id
153:                 JOIN skills s ON b.attribute_id = s.id
154:                 WHERE s.id = ?
155:                 GROUP BY item_type
156:                 HAVING num > 0
157:                 ORDER BY item_type ASC";
158:             $data = $this->db->query($sql, $skillId);
159:             $blob = '';
160:             forEach ($data as $row) {
161:                 $blob .= $this->text->makeChatcmd(ucfirst($row->item_type), "/tell <myname> whatbuffs $row->item_type $skillName") . " ($row->num)\n";
162:             }
163:             $blob .= "\nItem Extraction Info provided by Unk";
164:             $msg = $this->text->makeBlob("WhatBuffs $skillName - Choose Type", $blob);
165:         }
166:         $sendto->reply($msg);
167:     }
168:     
169:     public function getSearchResults($category, $skill) {
170:         $sql = "
171:             SELECT aodb.*, b.amount
172:             FROM aodb
173:             JOIN item_types i ON aodb.highid = i.item_id
174:             JOIN item_buffs b ON aodb.highid = b.item_id
175:             JOIN skills s ON b.attribute_id = s.id
176:             WHERE i.item_type = ? AND s.id = ?
177:             ORDER BY amount DESC";
178:         $data = $this->db->query($sql, $category, $skill->id);
179: 
180:         $result = $this->formatItems($data);
181: 
182:         if ($result === null) {
183:             $msg = "No items found of type <highlight>$category<end> that buff <highlight>$skill->name<end>.";
184:         } else {
185:             list($count, $blob) = $result;
186:             //$newUrl = "https://aoitems.com/search/acriteria:2-$skillId-1/";
187:             //$blob = $this->text->makeChatcmd("See results on aoitems.com", "/start $newUrl") . "\n\n" . $blob;
188:             $blob .= "\nItem Extraction Info provided by Unk";
189:             $msg = $this->text->makeBlob("WhatBuffs - $category $skill->name ($count)", $blob);
190:         }
191:         return $msg;
192:     }
193: 
194:     public function verifySlot($type) {
195:         $type = ucfirst(strtolower($type));
196:         $row = $this->db->queryRow("SELECT 1 FROM item_types WHERE item_type = ? LIMIT 1", $type);
197:         return $row !== null;
198:     }
199:     
200:     public function searchForSkill($skill) {
201:         // check for exact match first, in order to disambiguate
202:         // between Bow and Bow special attack 
203:         $results = $this->db->query("SELECT DISTINCT id, name FROM skills WHERE name LIKE ?", $skill);
204:         if (count($results) == 1) {
205:             return $results;
206:         }
207:         
208:         $tmp = explode(" ", $skill);
209:         list($query, $params) = $this->util->generateQueryFromParams($tmp, 'name');
210:         
211:         return $this->db->query("SELECT DISTINCT id, name FROM skills WHERE $query", $params);
212:     }
213:     
214:     public function formatItems($items) {
215:         $blob = '';
216:         forEach ($items as $item) {
217:             $blob .= $this->text->makeItem($item->lowid, $item->highid, $item->highql, $item->name) . " ($item->amount)\n";
218:         }
219: 
220:         $count = count($items);     
221:         if ($count > 0) {
222:             return array($count, $blob);
223:         } else {
224:             return null;
225:         }
226:     }
227:     
228:     public function showSearchResults($category, $skill) {
229:         $category = ucfirst(strtolower($category));
230:         
231:         $data = $this->searchForSkill($skill);
232:         $count = count($data);
233:         
234:         if ($count == 0) {
235:             $msg = "Could not find any skills matching <highlight>$skill<end>.";
236:         } else if ($count == 1) {
237:             $row = $data[0];
238:             $msg = $this->getSearchResults($category, $row);
239:         } else {
240:             $blob = '';
241:             forEach ($data as $row) {
242:                 $blob .= $this->text->makeChatcmd(ucfirst($row->skill), "/tell <myname> whatbuffs $category $row->skill") . "\n";
243:             }
244:             $msg = $this->text->makeBlob("WhatBuffs - Choose Skill", $blob);
245:         }
246:         
247:         return $msg;
248:     }
249: }
250: