Changeset 133 for dev/common/db.php

Show
Ignore:
Timestamp:
12/02/06 05:17:10 (14 years ago)
Author:
exi
Message:

Reduced one potential sql-bug.
Added ability to identify items as t2 via their techlevel rather than just 'II'.
Fixed item location 0 to not cause skipping that item.
Added anzahl/quantity-replace for the german killpreparser.
Fixed final blow award to show correct numbers.
Removed unnecessary code from db.php.
Added a table data check to the sql importer and made the importer more verbose, this should minimize support requests caused by empty table data.
Fixed a small misalignment of the comment block.

Files:
1 modified

Legend:

Unmodified
Added
Removed
  • dev/common/db.php

    r92 r133  
    105105    } 
    106106} 
    107 /* 
    108  * this looks like REALLY old code and isnt used anywhere 
    109  * commented out for now 
    110  * 
    111 class DBAction 
    112 { 
    113     function DBAction() 
    114     { 
    115         $this->dbconn_ = new DBConnection(); 
    116     } 
    117  
    118     function setDateTime($timestamp) 
    119     { 
    120         $this->timestamp_ = $timestamp; 
    121     } 
    122  
    123     function addPilot($pilot, $corpid, $timestamp) 
    124     { 
    125         $sql = "select * from kb3_pilots where plt_name = '" . addslashes(stripslashes($pilot)) . "'"; 
    126         $qry = new DBQuery($this->dbconn_, $sql); 
    127         $qry->execute() or die($qry->getErrorMsg()); 
    128  
    129         if ($qry->recordCount() == 0) 
    130         { 
    131             $sql = "insert into kb3_pilots values ( null, '" . addslashes(stripslashes($pilot)) . "', " . $corpid . ", 0, 0, 0, date_format('" . $timestamp . "', '%Y.%m.%d %H:%i:%s') )"; 
    132             $qry = new DBQuery($this->dbconn_, $sql) or die ($qry->getErrorMsg()); 
    133             $qry->execute() or die($qry->getErrorMsg()); 
    134             $pilotid = $qry->getInsertID(); 
    135         } 
    136         else 
    137         { 
    138             $row = $qry->getRow(); 
    139             $pilotid = $row['plt_id']; 
    140             $dbaction = new DBAction(); 
    141  
    142             if ($dbaction->isPilotUpdatable($pilotid, $timestamp) && $$row['plt_crp_id'] != $corpid) 
    143             { 
    144                 $qry = new DBQuery($this->dbconn_, "update kb3_pilots set plt_crp_id = " . $corpid . ", plt_updated = date_format('" . $timestamp . "', '%Y.%m.%d %H:%i:%s') where plt_id = " . $pilotid); 
    145                 $qry->execute() or die($qry->getErrorMsg()); 
    146             } 
    147         } 
    148  
    149         return $pilotid; 
    150     } 
    151  
    152     function setCharacterId($pilotid, $charid) 
    153     { 
    154         $sql = "update kb3_pilots set plt_externalid = " . $charid . " where plt_id = " . $pilotid; 
    155  
    156         $qry = new DBQuery($this->dbconn_, $sql); 
    157         $qry->execute(); 
    158     } 
    159  
    160     function addAlliance($alliance) 
    161     { 
    162         $sql = "select * from kb3_alliances where all_name = '" . addslashes(stripslashes($alliance)) . "'"; 
    163         $qry = new DBQuery($this->dbconn_, $sql); 
    164         $qry->execute() or die($qry->getErrorMsg()); 
    165  
    166         if ($qry->recordCount() == 0) 
    167         { 
    168             $sql = "insert into kb3_alliances values ( null, '" . addslashes(stripslashes($alliance)) . "', 'default' )"; 
    169             $qry = new DBQuery($this->dbconn_, $sql); 
    170             $qry->execute() or die($qry->getErrorMsg()); 
    171             $allianceid = $qry->getInsertID(); 
    172         } 
    173         else 
    174         { 
    175             $row = $qry->getRow(); 
    176             $allianceid = $row['all_id']; 
    177         } 
    178  
    179         return $allianceid; 
    180     } 
    181  
    182     function addCorp($corp, $allianceid, $timestamp) 
    183     { 
    184         $sql = "select * from kb3_corps where crp_name = '" . addslashes(stripslashes($corp)) . "'"; 
    185         $qry = new DBQuery($this->dbconn_, $sql); 
    186         $qry->execute() or die($qry->getErrorMsg()); 
    187  
    188         if ($qry->recordCount() == 0) 
    189         { 
    190             $sql = "insert into kb3_corps values ( null, '" . addslashes(stripslashes($corp)) . "', " . $allianceid . ", 0, date_format('" . $timestamp . "', '%Y.%m.%d %H:%i:%s') )"; 
    191             $qry = new DBQuery($this->dbconn_, $sql); 
    192             $qry->execute() or die($qry->getErrorMsg()); 
    193             $corpid = $qry->getInsertID(); 
    194         } 
    195         else 
    196         { 
    197             $row = $qry->getRow(); 
    198             $corpid = $row['crp_id']; 
    199  
    200             $dbaction = new DBAction(); 
    201  
    202             if ($dbaction->isCorpUpdatable($corpid, $timestamp) && $row['crp_all_id'] != $allianceid && $row['crp_trial'] != 1) 
    203             { 
    204                 $sql = "update kb3_corps set crp_all_id = " . $allianceid . ", crp_updated = date_format('" . $timestamp . "', '%Y.%m.%d %H:%i:%s') 
    205                    where crp_id = " . $corpid; 
    206                 $qry = new DBQuery($this->dbconn_, $sql); 
    207                 $qry->execute() or die($qry->getErrorMsg()); 
    208             } 
    209         } 
    210  
    211         return $corpid; 
    212     } 
    213  
    214     function addShip($ship, $description, $baseprice) 
    215     { 
    216         $sql = "select * from kb3_ships where shp_name = '" . addslashes(stripslashes($ship)) . "'"; 
    217         $qry = new DBQuery($this->dbconn_, $sql); 
    218         $qry->execute(); 
    219  
    220         if ($qry->recordCount() == 0) 
    221         { 
    222             $sql = "insert into kb3_ships values ( null, '" . addslashes(stripslashes($ship)) . "', 0, 0, 0, null, 0 )"; 
    223             $qry = new DBQuery($this->dbconn_, $sql); 
    224             $qry->execute(); 
    225             $shipid = $qry->getInsertID(); 
    226         } 
    227         else 
    228         { 
    229             $row = $qry->getRow(); 
    230             $shipid = $row['shp_id']; 
    231             if ($description != "") 
    232             { 
    233                 $sql = "update kb3_ships set shp_description = '" . addslashes(stripslashes($description)) . "', shp_baseprice = " . $baseprice . " where shp_id = " . $shipid; 
    234                 $qry = new DBQuery($this->dbconn_, $sql) or die($qry->getErrorMsg()); 
    235                 $qry->execute(); 
    236             } 
    237         } 
    238  
    239         return $shipid; 
    240     } 
    241  
    242     function addItem($item, $description, $volume, $type, $icon) 
    243     { 
    244         $sql = "select * from kb3_items where itm_name = '" . addslashes(stripslashes($item)) . "'"; 
    245         $qry = new DBQuery($this->dbconn_, $sql) or die ($qry->getErrorMsg()); 
    246         $qry->execute(); 
    247  
    248         if ($qry->recordCount() == 0) 
    249         { 
    250             $sql = "insert into kb3_items values ( null, '" . addslashes(stripslashes($item)) . "', '" . addslashes(stripslashes($description)) . "', " . $volume . ", " . $type . ", null, 1, '" . $icon . "' )"; 
    251             $qry = new DBQuery($this->dbconn_, $sql); 
    252             $qry->execute() or die ($qry->getErrorMsg()); 
    253             $itemid = $qry->getInsertID(); 
    254             return -1; 
    255         } 
    256         else 
    257         { 
    258             $row = $qry->getRow(); 
    259             $itemid = $row['itm_id']; 
    260             if ($description != "") 
    261             { 
    262                 $sql = "update kb3_items set itm_description = '" . addslashes(stripslashes($description)) . "', itm_volume = " . $volume . ", itm_type = " . $type . ", itm_icon = '" . $icon . "' where itm_id = " . $itemid; 
    263                 $qry = new DBQuery($this->dbconn_, $sql); 
    264                 $qry->execute() or die ($qry->getErrorMsg()); 
    265             } 
    266         } 
    267  
    268         return $itemid; 
    269     } 
    270  
    271     function setIcon($itemid, $icon) 
    272     { 
    273         $sql = "update kb3_items set itm_icon = '" . $icon . "' where itm_id = " . $itemid; 
    274         $qry = new DBQuery($this->dbconn_, $sql); 
    275         $qry->execute(); 
    276     } 
    277  
    278     function addSystem($system, $systemsec) 
    279     { 
    280         $sql = "select * from kb3_systems where sys_name = '" . addslashes(stripslashes($system)) . "'"; 
    281         $qry = new DBQuery($this->dbconn_, $sql); 
    282         $qry->execute() or die ($qry->getErrorMsg()); 
    283  
    284         if ($qry->recordCount() == 0) 
    285         { 
    286             $sql = "insert into kb3_systems values ( null, '" . addslashes(stripslashes($system)) . "', '" . $systemsec . "', 0 )"; 
    287             $qry = new DBQuery($this->dbconn_, $sql); 
    288             $qry->execute() or die ($qry->getErrorMsg()); 
    289             $systemid = $qry->getInsertID(); 
    290         } 
    291         else 
    292         { 
    293             $row = $qry->getRow(); 
    294             $systemid = $row['sys_id']; 
    295         } 
    296  
    297         return $systemid; 
    298     } 
    299  
    300     function addInvolved($killid, $pilotid, $secstatus, $allianceid, $corpid, $shipid, $weaponid, $finalblow, $order) 
    301     { 
    302         $sql = "insert into _" . KB_SITE . "_involved values ( " . $killid . ", " . $pilotid . ", '" . $secstatus . "', " . $allianceid . ", " . $corpid . ", " . $shipid . ", " . $weaponid . ", " . $finalblow . ", " . $order . " )"; 
    303         $qry = new DBQuery($this->dbconn_, $sql); 
    304         $qry->execute() or die ($qry->getErrorMsg()); 
    305     } 
    306  
    307     function addDestroyedItem($killid, $itemid, $quantity, $location) 
    308     { 
    309         $qry = new DBQuery($this->dbconn_, "select itl_id from kb3_item_locations where itl_location = '" . $location . "'"); 
    310         $qry->execute() or die($qry->getErrorMsg()); 
    311         $row = $qry->getRow(); 
    312         if ($row['itl_id'] != "") 
    313             $locationid = $row['itl_id']; 
    314         else 
    315             $locationid = 0; 
    316  
    317         $sql = "insert into _" . KB_SITE . "_items_destroyed values ( " . $killid . ", " . $itemid . ", " . $quantity . ", " . $locationid . " )"; 
    318         $qry = new DBQuery($this->dbconn_, $sql); 
    319         $qry->execute() or die ($qry->getErrorMsg()); 
    320     } 
    321  
    322     function addKill($timestamp, $victimid, $allianceid, $corpid, $shipid, $systemid) 
    323     { 
    324         $killid = 0; 
    325         // check if the killmail already exists 
    326         $sql = "select * from _" . KB_SITE . "_kills where kll_timestamp = date_format('" . $timestamp . "', '%Y.%m.%d %H:%i:%s') and kll_victim_id = " . $victimid . " and kll_ship_id = " . $shipid; 
    327         $qry = new DBQuery($this->dbconn_, $sql); 
    328         $qry->execute(); 
    329  
    330         if ($qry->recordCount() == 0) 
    331         { 
    332             $sql = "insert into _" . KB_SITE . "_kills values ( null, date_format('" . $timestamp . "', '%Y.%m.%d %H:%i:%s'), " . $victimid . ", " . $allianceid . ", " . $corpid . ", " . $shipid . ", " . $systemid . " )"; 
    333             $qry = new DBQuery($this->dbconn_, $sql); 
    334             $qry->execute() or die ($qry->getErrorMsg()); 
    335             $killid = $qry->getInsertID(); 
    336             // log entry 
    337             // $sql = "insert into kb3_log values ( ".$killid.", '".$_SERVER['REMOTE_ADDR']."', now() )"; 
    338             // $qry = new DBQuery( $this->dbconn_, $sql ); 
    339             // $qry->execute() or die ( $qry->getErrorMsg() ); 
    340         } 
    341  
    342         return $killid; 
    343     } 
    344  
    345     function rollback($killid) 
    346     { 
    347         $qry = new DBQuery($this->dbconn_, "delete from _" . KB_SITE . "_kills where kll_id = " . $killid); 
    348         $qry->execute(); 
    349  
    350         $qry = new DBQuery($this->dbconn_, "delete from _" . KB_SITE . "_involved where inv_kll_id = " . $killid); 
    351         $qry->execute(); 
    352  
    353         $qry = new DBQuery($this->dbconn_, "delete from _" . KB_SITE . "_items_destroyed where itd_kll_id = " . $killid); 
    354         $qry->execute(); 
    355     } 
    356  
    357     function getRawMail($killid, $site = "") 
    358     { 
    359         if ($site == "") 
    360             $site = KB_SITE; 
    361         $sql = "select kll.kll_timestamp, plt.plt_id, plt.plt_name, plt.plt_externalid, crp.crp_id, crp.crp_name, ali.all_id, ali.all_name, shp.shp_name, shp.shp_externalid, scl.scl_class, sys.sys_name, sys.sys_sec, kll.kll_system_id 
    362                 from _" . $site . "_kills kll, kb3_pilots plt, kb3_corps crp, 
    363                      kb3_alliances ali, kb3_ships shp, kb3_ship_classes scl, 
    364                      kb3_systems sys 
    365        where kll.kll_id = " . $killid . " 
    366          and plt.plt_id = kll.kll_victim_id 
    367          and crp.crp_id = kll.kll_crp_id 
    368          and ali.all_id = kll.kll_all_id 
    369          and shp.shp_id = kll.kll_ship_id 
    370          and sys.sys_id = kll.kll_system_id 
    371          and scl.scl_id = shp.shp_class"; 
    372  
    373         $qry = new DBQuery(); 
    374         if (!$qry->execute($sql)) die($qry->getErrorMsg()); 
    375         $row = $qry->getRow(); 
    376  
    377         $mail .= substr(str_replace('-', '.' , $row['kll_timestamp']), 0, 16) . "\r\n\r\n"; 
    378         $mail .= "Victim: " . $row['plt_name'] . "\r\n"; 
    379         $mail .= "Alliance: " . $row['all_name'] . "\r\n"; 
    380         $mail .= "Corp: " . $row['crp_name'] . "\r\n"; 
    381         $mail .= "Destroyed: " . $row['shp_name'] . "\r\n"; 
    382         $mail .= "System: " . $row['sys_name'] . "\r\n"; 
    383         $mail .= "Security: " . roundsec($row['sys_sec']) . "\r\n\r\n"; 
    384         $mail .= "Involved parties:\r\n\r\n"; 
    385  
    386         $sql = "select plt.plt_id, plt.plt_name, plt.plt_externalid, crp.crp_name, ali.all_name, shp.shp_name, shp.shp_externalid, scl.scl_class, 
    387                  inv.inv_final_blow, itm.itm_name, inv.inv_sec_status 
    388             from _" . $site . "_involved inv, kb3_pilots plt, kb3_corps crp, kb3_alliances ali, 
    389              kb3_ships shp, kb3_ship_classes scl, kb3_items itm 
    390        where inv.inv_kll_id = " . $killid . " 
    391          and plt.plt_id = inv.inv_plt_id 
    392          and crp.crp_id = inv.inv_crp_id 
    393          and ali.all_id = inv.inv_all_id 
    394          and shp.shp_id = inv.inv_shp_id 
    395          and scl.scl_id = shp.shp_class 
    396          and itm.itm_id = inv.inv_wep_id 
    397       order by inv.inv_order"; 
    398  
    399         $qry = new DBQuery(); 
    400         $qry->execute($sql); 
    401  
    402         while ($row = $qry->getRow()) 
    403         { 
    404             $mail .= "Name: " . $row['plt_name']; 
    405             if ($row['inv_final_blow'] == 1) 
    406                 $mail .= " (laid the final blow)"; 
    407             $mail .= "\r\n"; 
    408  
    409             $mail .= "Security: " . $row['inv_sec_status'] . "\r\n"; 
    410             $mail .= "Alliance: " . $row['all_name'] . "\r\n"; 
    411             $mail .= "Corp: " . $row['crp_name'] . "\r\n"; 
    412             $mail .= "Ship: " . $row['shp_name'] . "\r\n"; 
    413             $mail .= "Weapon: " . $row['itm_name'] . "\r\n\r\n"; 
    414         } 
    415  
    416         $sql = "select itd_quantity, itm.itm_name, itd.itd_itl_id 
    417             from _" . $site . "_items_destroyed itd, kb3_items itm 
    418            where itd.itd_kll_id = " . $killid . " 
    419              and itm.itm_id = itd.itd_itm_id 
    420            order by itd.itd_itl_id"; 
    421  
    422         $qry = new DBQuery(); 
    423         $qry->execute($sql) or die($qry->getErrorMsg()); 
    424  
    425         if ($qry->recordCount()) 
    426         { 
    427             $mail .= "\r\nDestroyed items:\r\n\r\n"; 
    428  
    429             while ($row = $qry->getRow()) 
    430             { 
    431                 $mail .= $row['itm_name']; 
    432                 if ($row['itd_quantity'] > 1) 
    433                     $mail .= ", Qty: " . $row['itd_quantity']; 
    434                 if ($row['itd_itl_id'] == 4) // cargo 
    435                     $mail .= " (Cargo)"; 
    436                 if ($row['itd_itl_id'] == 6) // drone 
    437                     $mail .= " (Drone Bay)"; 
    438                 $mail .= "\r\n"; 
    439             } 
    440         } 
    441  
    442         return $mail; 
    443     } 
    444  
    445     function getPostPassword() 
    446     { 
    447         $qry = new DBQuery($this->dbconn_, "select cfg_value from kb3_config where cfg_site = '" . KB_SITE . "' and cfg_key = 'post_password'"); 
    448         $qry->execute(); 
    449         $row = $qry->getRow(); 
    450  
    451         return $row['cfg_value']; 
    452     } 
    453  
    454     function getPostMailto() 
    455     { 
    456         $qry = new DBQuery($this->dbconn_, "select cfg_value from kb3_config where cfg_site = '" . KB_SITE . "' and cfg_key = 'post_mailto'"); 
    457         $qry->execute(); 
    458         $row = $qry->getRow(); 
    459  
    460         return $row['cfg_value']; 
    461     } 
    462  
    463     function setPostPassword($password) 
    464     { 
    465         $qry = new DBQuery($this->dbconn_, "update kb3_config set cfg_value = '" . $password . "' where cfg_site = '" . KB_SITE . "' and cfg_key = 'post_password'"); 
    466         $qry->execute(); 
    467     } 
    468  
    469     function setPostMailto($mailto) 
    470     { 
    471         $qry = new DBQuery($this->dbconn_, "update kb3_config set cfg_value = '" . $mailto . "' where cfg_site = '" . KB_SITE . "' and cfg_key = 'post_mailto'"); 
    472         $qry->execute(); 
    473     } 
    474  
    475     function isCorpUpdatable($crp_id, $timestamp) 
    476     { 
    477         $qry = new DBQuery($this->dbconn_, 
    478             "select crp_id from kb3_corps 
    479                             where crp_id = " . $crp_id . " 
    480                               and ( crp_updated < date_format('" . $timestamp . "', '%Y.%m.%d %H:%i') or crp_updated is null )"); 
    481         $qry->execute() or die($qry->getErrorMsg()); 
    482         return $qry->recordCount(); 
    483     } 
    484  
    485     function isPilotUpdatable($plt_id, $timestamp) 
    486     { 
    487         $qry = new DBQuery($this->dbconn_, 
    488             "select plt_id from kb3_pilots 
    489                             where plt_id = " . $plt_id . " 
    490                               and ( plt_updated < date_format('" . $timestamp . "', '%Y.%m.%d %H:%i') or plt_updated is null )"); 
    491         $qry->execute() or die($qry->getErrorMsg()); 
    492  
    493         return $qry->recordCount(); 
    494     } 
    495 } 
    496 */ 
    497107?>