Changeset 201

Timestamp:

04/05/07 02:52:14 (15 years ago)

Author:

exi

Message:

Attempt to fix various issues with the ContractListTable?-sql summary code.
The raw killmail now shows the time left for classified kills.
Added code to autoupgrade.php to add indexes to kb3_inv_crp and kb3_inv_all.

Location:

dev/common/includes

Files:

• autoupgrade.php (modified) (2 diffs)
• class.contract.php (modified) (2 diffs)
• class.db_cache.php (modified) (1 diff)
• class.kill.php (modified) (4 diffs)
• class.killlist.php (modified) (6 diffs)
• db.php (modified) (1 diff)

dev/common/includes/autoupgrade.php

@@ -109 +109 @@
 function check_index()
 {
+    check_index_invcrp();
+    check_index_invall();
     $qry = new DBQuery();
     $qry->execute('SHOW columns from kb3_item_types like \'itt_id\'');
@@ -117 +119 @@
     }
     $qry->execute('ALTER TABLE `kb3_item_types` ADD PRIMARY KEY ( `itt_id` ) ');
+}
+function check_index_invcrp()
+{
+    $qry = new DBQuery();
+    $qry->execute('SHOW columns from kb3_inv_crp like \'inc_kll_id\'');
+    $arr = $qry->getRow();
+    if ($arr['Key'] == 'MUL')
+    {
+        return;
+    }
+    $qry->execute('ALTER TABLE `kb3_inv_crp` ADD INDEX ( `inc_kll_id` ) ');
+}
+function check_index_invall()
+{
+    $qry = new DBQuery();
+    $qry->execute('SHOW columns from kb3_inv_all like \'ina_kll_id\'');
+    $arr = $qry->getRow();
+    if ($arr['Key'] == 'MUL')
+    {
+        return;
+    }
+    $qry->execute('ALTER TABLE `kb3_inv_all` ADD INDEX ( `ina_kll_id` ) ');
 }
 function check_tblstrct1()

dev/common/includes/class.contract.php

@@ -549 +549 @@
             $contract->execQuery();
 
-            // this is the sql for kills
-            $sql = 'select count(*) AS kills,';
-            if (config::get('ship_values'))
-            {
-                $sql .= ' sum(ifnull(ksv.shp_value,scl.scl_value)) AS killisk FROM kb3_kills kll
-                        INNER JOIN kb3_ships shp ON ( shp.shp_id = kll.kll_ship_id )
-                        left join kb3_ships_values ksv on (shp.shp_id = ksv.shp_id)';
-            }
-            else
-            {
-                $sql .= ' sum(scl.scl_value) AS kisk FROM kb3_kills kll
-                        INNER JOIN kb3_ships shp ON ( shp.shp_id = kll.kll_ship_id )';
-            }
-            $sql .= ' INNER JOIN kb3_ship_classes scl ON ( scl.scl_id = shp.shp_class )
-                      inner join kb3_systems sys on ( sys.sys_id = kll.kll_system_id )';
-            if ($contract->klist_->inv_crp_)
-            {
-                $sql .= ' inner join kb3_inv_crp inc on ( inc.inc_crp_id in ( '.join(',', $contract->klist_->inv_crp_).' ) and kll.kll_id = inc.inc_kll_id ) ';
-            }
-            if ($contract->klist_->inv_all_)
-            {
-                $sql .= ' inner join kb3_inv_all ina on ( ina.ina_all_id in ( '.join(',', $contract->klist_->inv_all_).' ) and kll.kll_id = ina.ina_kll_id ) ';
-            }
-            if ($contract->klist_->inv_plt_)
-            {
-                $sql .= ' inner join kb3_inv_plt inp on ( inp.inp_plt_id in ( '.join(',', $contract->klist_->inv_plt_).' ) and kll.kll_id = inp.inp_kll_id ) ';
-            }
-            if ($contract->klist_->regions_)
-            {
-                $sql .= ' inner join kb3_constellations con
-                           on ( con.con_id = sys.sys_con_id )
-                                   inner join kb3_regions reg on ( reg.reg_id = con.con_reg_id
-                                   and reg.reg_id in ( '.join(',', $contract->klist_->regions_).' ) )';
-            }
-            if ($contract->klist_->systems_)
-            {
-                $sql .= ' and kll.kll_system_id in ( '.join(',', $contract->klist_->systems_).')';
-            }
-
-            $sql .= ' where 1=1 ';
-            if ($contract->klist_->vic_plt_)
-                $sql .= " and kll.kll_victim_id in ( ".join(',', $contract->klist_->vic_plt_)." )";
-            if ($contract->klist_->vic_crp_)
-                $sql .= " and kll.kll_crp_id in ( ".join(',', $contract->klist_->vic_crp_)." )";
-            if ($contract->klist_->vic_all_)
-                $sql .= " and kll.kll_all_id in ( ".join(',', $contract->klist_->vic_all_)." )";
-
-            if ($contract->klist_->timeframe_)
-            {
-                $sql .= $contract->klist_->timeframe_;
-            }
-
-            $result = $qry->execute($sql);
-            $kdata = $qry->getRow($result);
-
-            // and here goes the losses
-            $sql = 'select count(*) AS losses,';
-            if (config::get('ship_values'))
-            {
-                $sql .= ' sum(ifnull(ksv.shp_value,scl.scl_value)) AS lossisk FROM kb3_kills kll
-                        INNER JOIN kb3_ships shp ON ( shp.shp_id = kll.kll_ship_id )
-                        left join kb3_ships_values ksv on (shp.shp_id = ksv.shp_id)';
-            }
-            else
-            {
-                $sql .= ' sum(scl.scl_value) AS kisk FROM kb3_kills kll
-                        INNER JOIN kb3_ships shp ON ( shp.shp_id = kll.kll_ship_id )';
-            }
-            $sql .= ' INNER JOIN kb3_ship_classes scl ON ( scl.scl_id = shp.shp_class )
-                      inner join kb3_systems sys on ( sys.sys_id = kll.kll_system_id )';
-            if ($contract->llist_->inv_crp_)
-            {
-                $sql .= ' inner join kb3_inv_crp inc on ( inc.inc_crp_id in ( '.join(',', $contract->llist_->inv_crp_).' ) and kll.kll_id = inc.inc_kll_id ) ';
-            }
-            if ($contract->llist_->inv_all_)
-            {
-                $sql .= ' inner join kb3_inv_all ina on ( ina.ina_all_id in ( '.join(',', $contract->llist_->inv_all_).' ) and kll.kll_id = ina.ina_kll_id ) ';
-            }
-            if ($contract->llist_->inv_plt_)
-            {
-                $sql .= ' inner join kb3_inv_plt inp on ( inp.inp_plt_id in ( '.join(',', $contract->llist_->inv_plt_).' ) and kll.kll_id = inp.inp_kll_id ) ';
-            }
-            if ($contract->llist_->regions_)
-            {
-                $sql .= ' inner join kb3_constellations con
-                           on ( con.con_id = sys.sys_con_id )
-                                   inner join kb3_regions reg on ( reg.reg_id = con.con_reg_id
-                                   and reg.reg_id in ( '.join(',', $contract->llist_->regions_).' ) )';
-            }
-            if ($contract->llist_->systems_)
-            {
-                $sql .= ' and kll.kll_system_id in ( '.join(',', $contract->llist_->systems_).')';
-            }
-
-            $sql .= ' where 1=1 ';
-            if ($contract->llist_->vic_plt_)
-                $sql .= " and kll.kll_victim_id in ( ".join(',', $contract->llist_->vic_plt_)." )";
-            if ($contract->llist_->vic_crp_)
-                $sql .= " and kll.kll_crp_id in ( ".join(',', $contract->llist_->vic_crp_)." )";
-            if ($contract->llist_->vic_all_)
-                $sql .= " and kll.kll_all_id in ( ".join(',', $contract->llist_->vic_all_)." )";
-
-            if ($contract->llist_->timeframe_)
-            {
-                $sql .= $contract->llist_->timeframe_;
-            }
-
-            $result = $qry->execute($sql);
-            $ldata = $qry->getRow($result);
-
+
+            for ($i = 0; $i < 2; $i++)
+            {
+                if ($i == 0)
+                {
+                    $list = &$contract->llist_;
+                }
+                else
+                {
+                    $list = &$contract->klist_;
+                }
+
+                // take care of the subquery needed (mysql > 4.1)
+                if (KB_MYSQL41)
+                {
+                    $sql = 'select count(kll_id) AS ships, sum(lossisk) as isk from (select distinct kll_id,';
+                }
+                else
+                {
+                    $sql = 'select distinct kll_id,';
+                }
+
+
+                if (config::get('ship_values'))
+                {
+                    $sql .= ' ifnull(ksv.shp_value,scl.scl_value) AS lossisk FROM kb3_kills kll
+                            INNER JOIN kb3_ships shp ON ( shp.shp_id = kll.kll_ship_id )
+                            left join kb3_ships_values ksv on (shp.shp_id = ksv.shp_id)';
+                }
+                else
+                {
+                    $sql .= ' scl.scl_value AS lossisk FROM kb3_kills kll
+                            INNER JOIN kb3_ships shp ON ( shp.shp_id = kll.kll_ship_id )';
+                }
+                $sql .= ' INNER JOIN kb3_ship_classes scl ON ( scl.scl_id = shp.shp_class )
+                          inner join kb3_systems sys on ( sys.sys_id = kll.kll_system_id )';
+
+                if ($list->regions_)
+                {
+                    $sql .= ' inner join kb3_constellations con
+                           on ( con.con_id = sys.sys_con_id )
+                                   inner join kb3_regions reg on ( reg.reg_id = con.con_reg_id
+                                   and reg.reg_id in ( '.join(',', $list->regions_).' ) )';
+                }
+                if ($list->systems_)
+                {
+                    $sql .= ' and kll.kll_system_id in ( '.join(',', $list->systems_).')';
+                }
+                if ($list->inv_crp_)
+                {
+                    $sql .= ' inner join kb3_inv_crp inc on ( kll.kll_id = inc.inc_kll_id ) ';
+                }
+                if ($list->inv_all_)
+                {
+                    $sql .= ' inner join kb3_inv_all ina on ( kll.kll_id = ina.ina_kll_id ) ';
+                }
+                if ($list->inv_plt_)
+                {
+                    $sql .= ' inner join kb3_inv_plt inp on ( kll.kll_id = inp.inp_kll_id ) ';
+                }
+
+                $sql .= ' where 1=1 ';
+
+                $tmp = array();
+                if ($list->vic_plt_)
+                {
+                    $tmp[] = 'kll.kll_victim_id in ( '.join(',', $list->vic_plt_).' )';
+                }
+                if ($list->vic_crp_)
+                {
+                    $tmp[] = 'kll.kll_crp_id in ( '.join(',', $list->vic_crp_).' )';
+                }
+                if ($list->vic_all_)
+                {
+                    $tmp[] = 'kll.kll_all_id in ( '.join(',', $list->vic_all_).' )';
+                }
+                if (count($tmp))
+                {
+                    $sql .= ' and (';
+                    $sql .= join(' or ', $tmp);
+                    $sql .= ')';
+                }
+
+
+                $tmp = array();
+                if ($list->inv_crp_)
+                {
+                    $tmp[] = 'inc.inc_crp_id in ( '.join(',', $list->inv_crp_).')';
+                }
+                if ($list->inv_all_)
+                {
+                    $tmp[] = 'ina.ina_all_id in ( '.join(',', $list->inv_all_).')';
+                }
+                if ($list->inv_plt_)
+                {
+                    $tmp[] = 'inp.inp_plt_id in ( '.join(',', $list->inv_plt_).')';
+                }
+                if (count($tmp))
+                {
+                    $sql .= ' and (';
+                    $sql .= join(' or ', $tmp);
+                    $sql .= ')';
+                }
+
+                if ($list->timeframe_)
+                {
+                    $sql .= $list->timeframe_;
+                }
+                if (KB_MYSQL41)
+                {
+                    // this is the last part from the outer query
+                    $sql .= ') as kb3_shadow';
+                }
+
+                $result = $qry->execute($sql);
+                if (KB_MYSQL41)
+                {
+                    $row = $qry->getRow($result);
+                }
+                else
+                {
+                    $sum = array();
+                    $sum['isk'] = 0;
+                    $sum['ships'] = 0;
+                    while ($row = $qry->getRow($result))
+                    {
+                        $sum['ships'] += 1;
+                        $sum['isk'] += $row['lossisk'];
+                    }
+                    $row = $sum;
+                }
+
+                if ($i == 0)
+                {
+                    $ldata = array('losses' => $row['ships'], 'lossisk' => $row['isk']);
+                }
+                else
+                {
+                    $kdata = array('kills' => $row['ships'], 'killisk' => $row['isk']);
+                }
+            }
             if ($kdata['killisk'])
             {
@@ -744 +775 @@
 
                 $html .= "<td class=".$kclass." align=center>".$contract->getKills()."</td>";
-                $html .= "<td class=".$kclass." align=center>".$contract->getKillISK()."</td>";
+                $html .= "<td class=".$kclass." align=center>".round($contract->getKillISK()/1000000, 2)."</td>";
                 $html .= "<td class=".$lclass." align=center>".$contract->getLosses()."</td>";
-                $html .= "<td class=".$lclass." align=center>".$contract->getLossISK()."</td>";
+                $html .= "<td class=".$lclass." align=center>".round($contract->getLossISK()/1000000, 2)."</td>";
                 $bar = new BarGraph($contract->getEfficiency(), 100, 75);
                 $html .= "<td class=kb-table-cell align=center width=40><b>".$contract->getEfficiency()."%</b></td>";

dev/common/includes/class.db_cache.php

@@ -144 +144 @@
             $tables[] = $ta[2];
         }
+        elseif ($ta[0] == 'alter')
+        {
+            return false;
+        }
         else
         {

dev/common/includes/class.kill.php

@@ -191 +191 @@
         if ($this->isClassified())
         {
-            return 'Killmail not yet available.';
+            return 'Killmail not yet available, try again in '.round($this->getClassifiedTime()/3600, 2).' hrs.';
         }
 
@@ -420 +420 @@
     }
 
+    function getClassifiedTime()
+    {
+        if (config::get('kill_classified'))
+        {
+            $offset = config::get('kill_classified')*3600;
+            if (strtotime($this->timestamp_) > time()-$offset)
+            {
+                return ($offset-time()+strtotime($this->timestamp_));
+            }
+        }
+        return 0;
+    }
+
     function exists()
     {
@@ -613 +626 @@
     function add($id = null)
     {
-        global $config;
         if (!$this->solarsystem_->getID())
         {
@@ -627 +639 @@
             $this->realadd();
         }
-        elseif ($config->getConfig('readd_dupes'))
+        elseif (config::get('readd_dupes'))
         {
             $this->dupeid_ = $dupe;

dev/common/includes/class.killlist.php

@@ -314 +314 @@
     {
         $this->inv_plt_[] = $pilot->getID();
-        if ($this->inv_crp_ || $this->inv_all_)
+        if (count($this->inv_crp_) || count($this->inv_all_))
             $this->mixedinvolved_ = true;
     }
@@ -321 +321 @@
     {
         $this->inv_crp_[] = $corp->getID();
-        if ($this->inv_plt_ || $this->inv_all_)
+        if (count($this->inv_plt_) || count($this->inv_all_))
             $this->mixedinvolved_ = true;
     }
@@ -328 +328 @@
     {
         $this->inv_all_[] = $alliance->getID();
-        if ($this->inv_plt_ || $this->inv_crp_)
+        if (count($this->inv_plt_) || count($this->inv_crp_))
             $this->mixedinvolved_ = true;
     }
@@ -335 +335 @@
     {
         $this->vic_plt_[] = $pilot->getID();
-        if ($this->vic_crp_ || $this->vic_all_)
+        if (count($this->vic_crp_) || count($this->vic_all_))
             $this->mixedvictims_ = true;
     }
@@ -342 +342 @@
     {
         $this->vic_crp_[] = $corp->getID();
-        if ($this->vic_plt_ || $this->vic_all_)
+        if (count($this->vic_plt_) || count($this->vic_all_))
             $this->mixedvictims_ = true;
     }
@@ -349 +349 @@
     {
         $this->vic_all_[] = $alliance->getID();
-        if ($this->vic_plt_ || $this->vic_crp_)
+        if (count($this->vic_plt_) || count($this->vic_crp_))
             $this->mixedvictims_ = true;
     }

dev/common/includes/db.php

@@ -12 +12 @@
     require_once('common/includes/class.db.php');
 }
+
+// get mysql server info and store it in a define so we know if its
+// safe to use subquerys or not
+$value = (float)mysql_get_server_info();
+if ($value > 4.1)
+{
+    define('KB_MYSQL41', true);
+}
+else
+{
+    define('KB_MYSQL41', false);
+}
 ?>