root/dev/mods/mail_editor/helper_functions.php @ 491

Revision 370, 19.1 KB (checked in by kovell, 11 years ago)

- SQL queries optimised for speed and error protection.
- feed syndication optimised
- front page includes optional clock and optional kill/loss display
- mysqli support added
- transaction protection of kills added where supported (mysqli and InnoDB)
- summary tables and contracts show total destroyed value instead of ship value
- html errors reduced
- related kill calculation improved
- query caching errors reduced
- minor bugfixes
- Smarty 2.6.25 added
- apoc fitting mod added and modded
- code optimisations
- code comments added (doxygen format)
- conflicting mods are now identified

Line 
1<?php
2/* This file contains all the gunky SQL statements necessary to update kill details
3 * by bypassing the Items class and updating the SQL manually. Urgh. Anyway, these
4 * functions could have been part of the settings file, but this makes both files
5 * look a bit neater. - FriedRoadKill*/
6
7require_once( "common/admin/admin_menu.php" );
8require_once( "common/includes/class.kill.php" );
9require_once( "common/includes/class.pilot.php");
10require_once( "common/includes/class.corp.php" );
11require_once( "common/includes/class.alliance.php" );
12require_once( "common/includes/class.ship.php" );
13require_once( "common/includes/class.item.php" );
14require_once( "common/includes/class.db.php" );
15
16function itemThinger($type, $kid) {//just fetches the items of a mail
17    $html_f = "";
18    $qry = new DBQuery();
19    $sql = "SELECT * FROM `kb3_items_".$type."` WHERE `itd_kll_id` = ".$kid.";";
20    $qry->execute($sql);
21    $count = $qry->recordCount();
22    if($count > 0)
23    {
24        //sub-heading
25        $odd_even = 0;
26        $html_f .= "<input type=hidden name=hidden_".$type."_count value=\"".$count."\">";
27        $html_f .= "<tr class=\"kb-table-row-even\" height=\"18px\"><td><b>Items "
28            .$type.":</b></td><td></td></tr>";
29        $i = 0;
30        while ($row = $qry->getRow())
31        {
32            if($odd_even == 0) {
33                $odd = "kb-table-row-odd";
34            }
35            else $odd = "kb-table-row-even";
36
37            $item = new Item($row['itd_itm_id']);
38            $qty = $row['itd_quantity'];
39            $loc = $row['itd_itl_id'];
40            $happy_pack = $item->getName();
41
42            if($qty > 1)
43                $happy_pack .= ", Qty: ".$qty;
44            switch($loc) {
45                case 1: $happy_pack .= " (High)"; break;
46                case 2: $happy_pack .= " (Med)"; break;
47                case 3: $happy_pack .= " (Low)"; break;
48                case 4: $happy_pack .= " (Cargo)"; break;
49                case 5: $happy_pack .= " (Rig)"; break;
50                case 6: $happy_pack .= " (Drone Bay)"; break;
51            }
52
53            $html_f .= "<tr class=\"".$odd."\"><td>".$happy_pack."</td><td>
54                <input type=text name=itm_".$type.$i."><input type=hidden name=hidden_itm_"
55                .$type.$i." value=\"".$item->getID()."\"></td></tr>";
56
57            $odd_even++; //to retain the nice and cute alternation of the background colour
58            if($odd_even >= 2)
59                $odd_even = 0;
60            $i++;
61        }
62        $html_f .= "<tr height=\"18px\"><td></td><td align=right><input type=submit name=kadoef value=\"Change!\"></td></tr>";
63    }
64    return $html_f;
65}
66
67function selectorThinger($id, $loc) {
68    $html_f = "";
69    $html_f .= "Type in the ID of the kill you want to edit:<br/><br/>";
70    $html_f .= "<input type=text name=kid_".$loc." value=\"".$id."\">\t";
71    $html_f .= "<input type=submit name=enter_".$loc." value=\"Go!\">\t";
72    $html_f .= "<input type=submit name=help value=\"Help\">";
73   
74    return $html_f;
75}
76
77function setDateTime($timeString, $kill_id) {
78    $now = time();
79    $timeString = str_replace('.', '-', $timeString); //otherwise the time is in the wrong format
80    $time = strtotime($timeString);
81
82    if($time == -1 || strlen($timeString) < 16 || strlen($timeString) > 19) //make it a tad picky
83        return "Date: Date format not correct.<br/>";
84
85    if($time > $now)
86        return "Date: Can't set timestamp to a future date.<br/>";
87
88    $qry = new DBQuery();
89    $sql = "UPDATE `kb3_kills` SET `kll_timestamp` = '".$timeString."' WHERE `kll_id` = '".$kill_id."';";
90    $qry->execute($sql);
91    return;   
92}
93
94function setVictimEnt($name, $corp, $all, $kill_id, $ov_n, $ov_c, $ov_a, $time) {
95    //boring grubby bit where we check if we actuall got input
96    $msg = "";
97    $name = trim($name);
98    $corp = trim($corp);
99    $all = trim($all);
100
101     if(strlen($name) == "" || strlen($corp) == "" || strlen($all) == "") {
102        $msg .= "Victim: Enter in something other than whitespace.<br/>";
103        return $msg;
104    }
105
106
107    if(strlen($all) > 0) { //determine all we know about the pilot, and change if needed
108        $al = new Alliance();
109        $al->add($all);
110    }
111    else $al = new Alliance($ov_a);
112
113    if(strlen($corp) > 0) { //same again for corp
114        $crp = new Corporation();
115        $crp->add($corp, $al, $time);
116    }
117    else {
118        $crp = new Corporation($ov_c);
119        $co = $crp->getName();
120        $crp->add($co, $al, $time);
121    }
122
123    if(strlen($name) > 0) {
124        $plt = new Pilot();
125        $plt->add($name, $crp, $time);
126    }
127    else {
128        $plt = new Pilot($ov_n); //get the name from the id, and add again
129        $na = $plt->getName();
130        $plt->add($na, $crp, $time);
131    }
132
133    if(strlen($name) > 0) {
134        $qry = new DBQuery();
135        $sql = "UPDATE `kb3_kills` SET `kll_victim_id` = '".$plt->getID()."' WHERE `kll_id` = '".$kill_id."'";
136        $qry->execute($sql);
137    }
138
139    if(strlen($corp) > 0) {
140        $qry = new DBQuery();
141        $sql = "UPDATE `kb3_kills` SET `kll_crp_id` = '".$crp->getID()."' WHERE `kll_id` = '".$kill_id."'";
142        $qry->execute($sql);
143    }
144
145    if(strlen($all) > 0) {
146        $qry = new DBQuery();
147        $sql = "UPDATE `kb3_kills` SET `kll_all_id` = '".$al->getID()."' WHERE `kll_id` = '".$kill_id."'";
148        $qry->execute($sql);
149    }
150    return;
151}
152
153function setVictimShip($name, $kill_id) {
154    $qry = new DBQuery();
155    $sql = "SELECT `shp_id` FROM `kb3_ships` WHERE `shp_name` = '".$name."';";
156    $qry->execute($sql);
157    if($qry->recordCount() < 1)
158        return "Ship '".$name."' doesn't exist in the database.<br/>";
159    $row = $qry->getRow();
160
161    $qry = new DBQuery();
162    $sql = "UPDATE `kb3_kills` SET `kll_ship_id` = '".$row['shp_id']
163        ."' WHERE `kll_id` = '".$kill_id."'";
164    $qry->execute($sql);
165    return;
166}
167
168function setSolarSystem($name, $kill_id) {
169    $qry = new DBQuery();
170    $sql = "SELECT `sys_id` FROM `kb3_systems` WHERE `sys_name` = '".$name."';";
171    $qry->execute($sql);
172    if($qry->recordCount() < 1)
173        return "Solar system: '".$name."' doesn't exist in the database.<br/>";
174    $row = $qry->getRow();
175
176    $qry = new DBQuery();
177    $sql = "UPDATE `kb3_kills` SET `kll_system_id` = '".$row['sys_id']
178        ."' WHERE `kll_id` = '".$kill_id."'";
179    $qry->execute($sql);
180
181    return;
182}
183
184function setDamageTaken($name, $kill_id) {
185
186    $qry = new DBQuery();
187    $sql = "UPDATE `kb3_kills` SET `kll_dmgtaken` = '".$name
188        ."' WHERE `kll_id` = '".$kill_id."'";
189    $qry->execute($sql);
190
191    return;
192}
193
194function getFBSlot($fb_id, $kill_id) {
195
196    $qry = new DBQuery();
197    $sql = "SELECT `ind_order` FROM `kb3_inv_detail` WHERE `ind_kll_id` = '"
198        .$kill_id."' AND `ind_plt_id` = '".$fb_id."'";
199    $qry->execute($sql);
200    $row = $qry->getRow();
201    return $row['ind_order'];
202}
203
204function setInvEnt($name, $corp, $all, $kill_id, $i, $old_n, $old_c, $old_a, $time, $fb) {
205    //this may look very familiar...
206    $msg = "";
207    $name = trim($name);
208    $corp = trim($corp);
209    $all = trim($all);
210
211    if(strlen($name) == "" || strlen($corp) == "" || strlen($all) == "") {
212        $number = $i +1;
213        $msg .= "Involved Party #".$number.": Enter in something other than whitespace.<br/>";
214        return $msg;
215    }
216
217    if(strlen($all) > 0) {
218        $al = new Alliance();
219        $al->add($all);
220    }
221    else $al = new Alliance($old_a);
222
223    if(strlen($corp) > 0) {
224        $crp = new Corporation();
225        $crp->add($corp, $al, $time);
226    }
227    else {
228        $crp = new Corporation($old_c);
229        $corp = $crp->getName();
230        $crp->add($corp, $al, $time);
231    }
232
233    if(strlen($name) > 0) {
234        $plt = new Pilot();
235        $plt->add($name, $crp, $time);
236    }
237    else {
238        $plt = new Pilot($old_p); //get the name from the id, and add again
239        $name = $plt->getName();
240        $plt = new Pilot($name, $crp, $time);
241    }
242
243    if(strlen($name) > 0) {
244        $hold_row = $plt->getID();
245        $qry = new DBQuery();
246        $sql = "UPDATE `kb3_inv_detail` SET `ind_plt_id` = '".$hold_row
247            ."' WHERE `ind_kll_id` = '".$kill_id."' AND `ind_order` = '".$i."'";
248        $qry->execute($sql);
249
250        $qry = new DBQuery();
251        $sql = "DELETE FROM `kb3_inv_plt` WHERE `inp_kll_id` ='".$kill_id
252            ."' AND `inp_plt_id` = '".$old_n."';";
253        $qry->execute($sql);
254
255        $qry = new DBQuery();
256        $sql = "INSERT INTO `kb3_inv_plt` (`inp_kll_id`, `inp_plt_id`) VALUES("
257            .$kill_id.",".$hold_row.");";
258        $qry->execute($sql);
259
260        if($i == $fb) {
261            $qry = new DBQuery();
262            $sql = "UPDATE `kb3_kills` SET `kll_fb_plt_id` = '".$hold_row
263                ."' WHERE `kll_id` = '".$kill_id."'";
264            $qry->execute($sql);
265        }
266    }
267
268    if(strlen($corp) > 0) {
269        $hold_row = $crp->getID();
270        $qry = new DBQuery();
271        $sql = "UPDATE `kb3_inv_detail` SET `ind_crp_id` = '".$hold_row
272            ."' WHERE `ind_kll_id` = '".$kill_id."' AND `ind_order` = '".$i."'";
273        $qry->execute($sql);
274
275        if($i == $fb) {
276            $qry = new DBQuery();
277            $sql = "UPDATE `kb3_kills` SET `kll_fb_crp_id` = '".$hold_row
278                ."' WHERE `kll_id` = '".$kill_id."'";
279            $qry->execute($sql);
280        }
281    }
282
283    if(strlen($all) > 0) {
284        $hold_row = $al->getID();
285
286        $qry = new DBQuery();
287        $sql = "UPDATE `kb3_inv_detail` SET `ind_all_id` = '".$hold_row
288            ."' WHERE `ind_kll_id` = '".$kill_id."' AND `ind_order` = '".$i."'";
289        $qry->execute($sql);
290
291        if($i == $fb) {
292            $qry = new DBQuery();
293            $sql = "UPDATE `kb3_kills` SET `kll_fb_all_id` = '".$hold_row
294                ."' WHERE `kll_id` = '".$kill_id."'";
295            $qry->execute($sql);
296        }
297    }
298    return $msg;
299}
300
301function setInvShip($name, $kill_id, $i) {
302    $qry = new DBQuery();
303    $sql = "SELECT `shp_id` FROM `kb3_ships` WHERE `shp_name` = '".$name."';";
304    $qry->execute($sql);
305    if($qry->recordCount() < 1)
306        return "Ship'".$name."' doesn't exist in the database.<br/>";
307    $row = $qry->getRow();
308
309    $qry = new DBQuery();
310    $sql = "UPDATE `kb3_inv_detail` SET `ind_shp_id` = '".$row['shp_id']
311        ."' WHERE `ind_kll_id` = '".$kill_id."' AND `ind_order` = '".$i."';";
312    $qry->execute($sql);
313    return;
314}
315
316function setInvWep($name, $kill_id, $i) {
317    $qry = new DBQuery();
318    $sql = "SELECT `typeID` FROM `kb3_invtypes` WHERE `typeName` = '".$name."';";
319    $qry->execute($sql);
320
321    if($qry->recordCount() < 1)
322        return "Weapon '".$name."' doesn't exist in the database.<br/>";
323    $row = $qry->getRow();
324
325    $qry = new DBQuery();
326    $sql = "UPDATE `kb3_inv_detail` SET `ind_wep_id` = '".$row['typeID']
327        ."' WHERE `ind_kll_id` = '".$kill_id."' AND `ind_order` = '".$i."';";
328    $qry->execute($sql);
329    return;
330}
331
332function setInvSec($name, $kill_id, $i) {
333    if(is_numeric($name)) {
334        if($name > 10 || $name < -10) {
335            return "Involved pilot sec values need to be between -10 & 10, you wrote, '".$name."'.";
336        }
337    }
338    else return "Involved pilot sec value is not a number. You wrote, '".$name."'.";
339
340    $qry = new DBQuery();
341    $sql = "UPDATE `kb3_inv_detail` SET `ind_sec_status` = '".$name
342        ."' WHERE `ind_kll_id` = '".$kill_id."' AND `ind_order` = '".$i."';";
343    $qry->execute($sql);
344    return;
345}
346
347function setInvDmg($name, $kill_id, $i) {
348    if(!is_numeric($name)) {
349        return "Involved pilot damage done is not a number. You wrote, '".$name."'.";
350    }
351
352    $qry = new DBQuery();
353    $sql = "UPDATE `kb3_inv_detail` SET `ind_dmgdone` = '".$name
354        ."' WHERE `ind_kll_id` = '".$kill_id."' AND `ind_order` = '".$i."';";
355    $qry->execute($sql);
356    return;
357}
358
359function setItm($name, $kill_id, $i, $type, $old) {
360    //some string manipulation to find quantity and location
361    //that's a lot of ifs, mister!
362    $itm = strpos($name, ",");
363    $q_pos = strpos(strtolower($name), "qty:");
364    $l_pos = strpos($name, "(");
365    $l_pos_end = strpos($name, ")");
366    $location = 0;
367
368    if($itm == 0) { //did we find a comma?
369        $justName = trim($name);
370        if($l_pos > 0) //bracket instead? (for if no comma is set)
371            $justName = trim(substr($name, 0, $l_pos));
372    }
373    else $justName = trim(substr($name, 0, $itm));
374
375    if($l_pos == 0) //did we find a bracket?
376        $location = 0;
377    else {
378       if($l_pos_end == 0) //and the accompanying bracket?
379           return $justName.": Open bracket must be accompanied by a closed bracket.<br/>";
380       $loc_text = substr($name, $l_pos+1, $l_pos_end - ($l_pos+1));
381
382       //the 6 locations
383       switch (strtolower($loc_text)) {
384           case "high": $location = 1; break;
385           case "med": $location = 2; break;
386           case "low": $location = 3; break;
387           case "cargo": $location = 4; break;
388           case "rig": $location = 5; break;
389           case "drone bay": $location = 6; break;
390           default: return $justName.": location text not recognised.</br>"; break;
391       }
392    }
393
394    if($q_pos == 0) //did the quantity value get changed?
395        $quantity = 0;
396    else {
397        if($l_pos == 0)
398            $end = strlen($name) -1;
399        else $end = $l_pos -1;
400
401        $quantity = trim(substr($name, $q_pos +4, $end - $l_pos+4));
402        if(!is_numeric($quantity))
403            return $justName.": Quantity must be a number. You entered in '".$quantity."'<br/>";
404    }
405
406    //usual DB stuff
407    $qry = new DBQuery();
408    $sql = "SELECT `typeID` FROM `kb3_invtypes` WHERE `typeName` = '".$justName."';";
409    $qry->execute($sql);
410    if($qry->recordCount() < 1)
411        return $name." doesn't exist in the database.<br/>";
412    $row = $qry->getRow();
413    $iid = $row['typeID'];
414
415    if($quantity > 0) { //update quantity
416        $qry = new DBQuery();
417        $sql = "UPDATE `kb3_items_".$type."` SET `itd_quantity` = '".$quantity
418            ."' WHERE `itd_kll_id` = '".$kill_id."' AND `itd_itm_id` = '".$old."'";
419        $qry->execute($sql);
420    }
421
422    if($location > 0) { //update location
423        $qry = new DBQuery();
424        $sql = "UPDATE `kb3_items_".$type."` SET `itd_itl_id` = '".$location
425            ."' WHERE `itd_kll_id` = '".$kill_id."' AND `itd_itm_id` = '".$old."'";
426        $qry->execute($sql);
427    }
428
429    //set the id last, so the other properties can be set first with copy paste code.
430    $qry = new DBQuery();
431    $sql = "UPDATE `kb3_items_".$type."` SET `itd_itm_id` = '".$iid
432        ."' WHERE `itd_kll_id` = '".$kill_id."' AND `itd_itm_id` = '".$old."'";
433    $qry->execute($sql);
434
435    return;
436}
437
438function recalcInvData($kill_id) {
439    //this table will have to be rebuilt, updating will be problematic.
440    //each corp reference must be unique, so only select one of each corp.
441    $qry = new DBQuery();
442    $sql = "DELETE FROM `kb3_inv_crp` WHERE `inc_kll_id` ='".$kill_id."';";
443    $qry->execute($sql);
444
445    $qry = new DBQuery();
446    $sql = "SELECT DISTINCT `ind_crp_id` FROM `kb3_inv_detail`
447        WHERE `ind_kll_id` = '".$kill_id."';";
448    $qry->execute($sql);
449
450    while($row = $qry->getRow()) {
451        $qry2 = new DBQuery();
452        $sql2 = "INSERT INTO `kb3_inv_crp` (`inc_kll_id`, `inc_crp_id`)
453            VALUES (".$kill_id.",".$row['ind_crp_id'].");";
454        $qry2->execute($sql2);
455    }
456    //same again, but now with more alliance flavour
457    $qry = new DBQuery();
458    $sql = "DELETE FROM `kb3_inv_all` WHERE `ina_kll_id` ='".$kill_id."';";
459    $qry->execute($sql);
460
461    $qry = new DBQuery();
462    $sql = "SELECT DISTINCT `ind_all_id` FROM `kb3_inv_detail` WHERE `ind_kll_id` = '"
463        .$kill_id."' AND `ind_all_id` != '14';"; //filter out 'None' (we don't love None like the other children)
464    $qry->execute($sql);
465
466    while($row = $qry->getRow()) {
467        $qry2 = new DBQuery();
468        $sql2 = "INSERT INTO `kb3_inv_all` (`ina_kll_id`, `ina_all_id`)
469            VALUES (".$kill_id.",".$row['ind_all_id'].");";
470        $qry2->execute($sql2);
471    }
472}
473
474function getHelp() { // help information document type stuff.
475    $text = "<p>This mod allows the admin to edit a kill mail once it has been posted
476        into the killboard's database. The editor is quite liberal, allowing
477        you to change just about everything in each kill mail.<br/><br/>
478        <b>Basics</b><br/>You start by selecting a mail to edit, by typing
479        in its ID and whacking 'Go!'. You're then presented with the relevant
480        mail, and input boxes for all editable fields. To change something
481        type in the name of what you want into the corresponding box and
482        whacking 'Change!' at the bottom of the table. (You can edit as many
483        fields at a time as you like.) As a safety feature, if you are trying
484        to change a field to something that doesn't exist in your database, the
485        mail editor will not allow the change to be made - with the exception of
486        pilot, corp, and alliance names - these may be anything your heart desires.
487        <br/><br/>
488        <b>More Advanced Editing</b><br/>
489        The date - to change the date your input must match the usual eve-mail
490        format (YYYY.MM.DD HH:MM:SS)<br/>
491        Items - string entry comes in 3 parts: the item name, the quantity,
492        and the location of the item:<br/>Item, Qty: xx (Place)<br/>
493        So, here's the input and the types of input by way of example:<br/>
494        \"Damage Control II\" - will change the name of the item only.<br/>
495        \"Damage Control II, Qty: 5\" - will change the name of the item and
496        set the quantity.<br/>
497        \"Damage Control II, Qty: 5 (Cargo)\" - will change the name of the
498        item, the quantity and the location that the item should now reside.<br/>
499        \"Damage Control II (Cargo)\" will also work (indicating an automatic
500        quantity of one.)<br/>
501        Allowed location values are: High, Med, Low, Cargo, Drone Bay, and Rig.
502        <u>Beware!</u> You can change the location of any item to any location!
503        (Like drones in high slots, and salvagers in the rig slots, for example.)
504        <br/>
505        For the quantity and location parts of the string, the case is insensitive.
506        ((Cargo), (CaRgO), and (cargo) are all equivalent.)<br/><br/>
507        <b>Limitations</b><br/>
508        As we're still at v".ME_VER." There's still some stuff to be added
509        depending on community response. The limitations are:<br/>
510        The text a user provides <u>has</u> be an exact match to the text in the
511        database. (Very case-sensitive & no partial text searches yet)<br/>
512        You can't add or delete items / involved parties.<br/>
513        Might be slow with killmails with lots of involved parties / items.<br/>
514        No integration into the kill list for the admin. (Too lazy atm - it conflicts
515                with oh so many other mods)<br/>
516        After editing a pilot, his new corp might not reflect properly until the next
517                kill featuring said is posted. This is down to a limitation in the pilot class
518                of the core. This applies to corps and the alliances they belong to as well.
519                <br/><br/>
520        <b>Disclaimer</b><br/>
521        As you can potentially mess up kills in your database, I'm slapping
522        this with the usual \"If you break something, dont blame me\" disclaimer.
523        If you're calm and careful, there's no reason it won't work. If something
524        does happen, please report it, and I will repair it as fast as possible so that
525        it doesn't happen in the future!<br/><br/>FriedRoadKill</p>";
526
527    return $text;
528}
529?>
Note: See TracBrowser for help on using the browser.