Changeset 187

Timestamp:

02/08/07 23:32:28 (15 years ago)

Author:

exi

Message:

Changed contracts/campaigns to use a sql query for the summary instead of iterating through all kill-objects which should be a lot faster now.
Converted some internal functions in class.killlist.php to use array stacks instead of strings.

Location:

dev

Files:

• common/class.contract.php (modified) (1 diff)
• common/class.killlist.php (modified) (11 diffs)
• templates/contractlisttable.tpl (added)

dev/common/class.contract.php

@@ -541 +541 @@
     }
 
+    function getTableStats()
+    {
+        $qry = new DBQuery();
+        while ($contract = $this->contractlist_->getContract())
+        {
+            // generate all neccessary objects within the contract
+            $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_)." )";
+
+            $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_)." )";
+
+            $result = $qry->execute($sql);
+            $ldata = $qry->getRow($result);
+
+            if ($kdata['killisk'])
+            {
+                $efficiency = round($kdata['killisk'] / ($kdata['killisk']+$ldata['lossisk']) *100, 2);
+            }
+            else
+            {
+                $efficiency = 0;
+            }
+            $bar = new BarGraph($efficiency, 100, 75);
+
+            $tbldata[] = array_merge(array('name' => $contract->getName(), 'startdate' => $contract->getStartDate(), 'bar' => $bar->generate(),
+                                     'enddate' => $contract->getEndDate(), 'efficiency' => $efficiency, 'id' => $contract->getID()), $kdata, $ldata);
+        }
+        $this->contractlist_->contractcounter_ = 1;
+        $this->contractlist_->qry_->rewind();
+        return $tbldata;
+    }
+
     function generate()
     {
+        if ($table = $this->getTableStats())
+        {
+            global $smarty;
+
+            $smarty->assign('contract_getactive', $this->contractlist_->getActive());
+            $smarty->assign_by_ref('contracts', $table);
+            $pagesplitter = new PageSplitter($this->contractlist_->getCount(), 10);
+
+            return $smarty->fetch(get_tpl('contractlisttable')).$pagesplitter->generate();
+        }
+
+        // old stuff, not used anymore (kept for reference)
         if ($this->contractlist_->getCount())
         {

dev/common/class.killlist.php

@@ -80 +80 @@
                 if ($this->inv_plt_)
                     $this->sql_ .= " inner join kb3_inv_plt inp
-                               on ( inp.inp_plt_id in ( ".substr($this->inv_plt_, 0, strlen($this->inv_plt_) - 2)." ) and kll.kll_id = inp.inp_kll_id ) ";
+                               on ( inp.inp_plt_id in ( ".join(',', $this->inv_plt_)." ) and kll.kll_id = inp.inp_kll_id ) ";
                 if ($this->inv_crp_)
                     $this->sql_ .= " inner join kb3_inv_crp inc
-                              on ( inc.inc_crp_id in ( ".substr($this->inv_crp_, 0, strlen($this->inv_crp_) - 2)." ) and kll.kll_id = inc.inc_kll_id
+                              on ( inc.inc_crp_id in ( ".join(',', $this->inv_crp_)." ) and kll.kll_id = inc.inc_kll_id
                                and inc.inc_crp_id != kll.kll_crp_id ) ";
                 if ($this->inv_all_)
                     $this->sql_ .= " inner join kb3_inv_all ina
-                              on ( ina.ina_all_id in ( ".substr($this->inv_all_, 0, strlen($this->inv_all_) - 2)." ) and kll.kll_id = ina.ina_kll_id
+                              on ( ina.ina_all_id in ( ".join(',', $this->inv_all_)." ) and kll.kll_id = ina.ina_kll_id
                                and ina.ina_all_id != kll.kll_all_id ) ";
             }
@@ -118 +118 @@
 
             if ($this->vic_plt_)
-                $this->sql_ .= " ".$op." kll.kll_victim_id in ( ".substr($this->vic_plt_, 0, strlen($this->vic_plt_) - 2)." )";
+                $this->sql_ .= " ".$op." kll.kll_victim_id in ( ".join(',', $this->vic_plt_)." )";
             if ($this->vic_crp_)
-                $this->sql_ .= " ".$op." kll.kll_crp_id in ( ".substr($this->vic_crp_, 0, strlen($this->vic_crp_) - 2)." )";
+                $this->sql_ .= " ".$op." kll.kll_crp_id in ( ".join(',', $this->vic_crp_)." )";
             if ($this->vic_all_)
-                $this->sql_ .= " ".$op." kll.kll_all_id in ( ".substr($this->vic_all_, 0, strlen($this->vic_all_) - 2)." )";
+                $this->sql_ .= " ".$op." kll.kll_all_id in ( ".join(',', $this->vic_all_)." )";
 
             if ($this->mixedvictims_)
@@ -163 +163 @@
                 {
                     $replace = " inner join kb3_inv_plt inp
-                            on ( inp.inp_plt_id in ( ".substr($this->inv_plt_, 0, strlen($this->inv_plt_) - 2)." ) and kll.kll_id = inp.inp_kll_id ) ";
+                            on ( inp.inp_plt_id in ( ".join(',', $this->inv_plt_)." ) and kll.kll_id = inp.inp_kll_id ) ";
                     $psql = str_replace("<ph>", $replace, $this->sql_);
                 }
@@ -169 +169 @@
                 {
                     $replace = " inner join kb3_inv_crp inc
-                            on ( inc.inc_crp_id in ( ".substr($this->inv_crp_, 0, strlen($this->inv_crp_) - 2)." ) and kll.kll_id = inc.inc_kll_id ) ";
+                            on ( inc.inc_crp_id in ( ".join(',', $this->inv_crp_)." ) and kll.kll_id = inc.inc_kll_id ) ";
                     $csql = str_replace("<ph>", $replace, $this->sql_);
                 }
@@ -175 +175 @@
                 {
                     $replace = " inner join kb3_inv_all ina
-                            on ( ina.ina_all_id in ( ".substr($this->inv_all_, 0, strlen($this->inv_all_) - 2)." ) and kll.kll_id = ina.ina_kll_id ) ";
+                            on ( ina.ina_all_id in ( ".join(',', $this->inv_all_)." ) and kll.kll_id = ina.ina_kll_id ) ";
                     $asql = str_replace("<ph>", $replace, $this->sql_);
                 }
@@ -306 +306 @@
     function addInvolvedPilot($pilot)
     {
-        $this->inv_plt_ .= $pilot->getID().", ";
+        $this->inv_plt_[] = $pilot->getID();
         if ($this->inv_crp_ || $this->inv_all_)
             $this->mixedinvolved_ = true;
@@ -313 +313 @@
     function addInvolvedCorp($corp)
     {
-        $this->inv_crp_ .= $corp->getID().", ";
+        $this->inv_crp_[] = $corp->getID();
         if ($this->inv_plt_ || $this->inv_all_)
             $this->mixedinvolved_ = true;
@@ -320 +320 @@
     function addInvolvedAlliance($alliance)
     {
-        $this->inv_all_ .= $alliance->getID().", ";
+        $this->inv_all_[] = $alliance->getID();
         if ($this->inv_plt_ || $this->inv_crp_)
             $this->mixedinvolved_ = true;
@@ -327 +327 @@
     function addVictimPilot($pilot)
     {
-        $this->vic_plt_ .= $pilot->getID().", ";
+        $this->vic_plt_[] = $pilot->getID();
         if ($this->vic_crp_ || $this->vic_all_)
             $this->mixedvictims_ = true;
@@ -334 +334 @@
     function addVictimCorp($corp)
     {
-        $this->vic_crp_ .= $corp->getID().", ";
+        $this->vic_crp_[] = $corp->getID();
         if ($this->vic_plt_ || $this->vic_all_)
             $this->mixedvictims_ = true;
@@ -341 +341 @@
     function addVictimAlliance($alliance)
     {
-        $this->vic_all_ .= $alliance->getID().", ";
+        $this->vic_all_[] = $alliance->getID();
         if ($this->vic_plt_ || $this->vic_crp_)
             $this->mixedvictims_ = true;