[eluser]Caio Russo[/eluser]
Hi Shaw
you could do a function that when u send the champ ID, and the function gets the total count of the races/points (let's say the driver has 10), order then by the value and return the sum of this values with 'limit count-1'. This way u have your exclude function.
Caio
[eluser]srpurdy[/eluser]
Finally managed to figure this out. Breaking this post into multiple posts. Too much code.
Thanks for your help Caio
First I created 2 new model functions
Code: function champ_drop_import($id, $num)
{
$champ_drop = $this->db
->where('ilr_champ.season_id', $this->uri->segment(6))
->where('ilr_season.season_id', $this->uri->segment(6))
->where('ilr_champ.iracing_cust_id', $id)
->select('
ilr_champ.points,
ilr_champ.pen_pts,
ilr_champ.bonus_pts,
ilr_champ.iracing_cust_id
')
->from('ilr_champ')
->join('ilr_season','ilr_season.season_id = ilr_champ.season_id')
->order_by('ilr_champ.points desc')
->limit($num)
->get();
return $champ_drop;
}
#second function
function get_champ_drop_import()
{
$show_champ = $this->db
//->distinct()
->where('ilr_champ.season_id', $this->uri->segment(6))
->where('ilr_season.season_id', $this->uri->segment(6))
->where('ilr_event_result.iracing_cust_id = ilr_champ.iracing_cust_id')
->where('ilr_event_result.schedule_id = ilr_champ.schedule_id')
->where('ilr_champ.schedule_id = ilr_events.schedule_id')
->where('ilr_events.track_id = ilr_tracks.track_id')
->select('
ilr_event_result.iracing_cust_id,
ilr_event_result.driver_title,
ilr_events.track_id,
ilr_tracks.turns,
ilr_season.drop_races,
ilr_season.multi_class,
ilr_season.car_name,
ilr_season.car2,
ilr_season.car3,
ilr_season.teams,
ilr_season.team_pts,
ilr_champ.team_id,
(select sum(ilr_champ.points - ilr_champ.pen_pts + ilr_champ.bonus_pts) from ilr_event_result where ilr_event_result.iracing_cust_id = ilr_champ.iracing_cust_id limit 1) as champ_points,
sum(ilr_champ.laps) as laps_total,
sum(ilr_champ.led) as led_total,
sum(ilr_champ.inc) as inc_total,
sum(ilr_champ.wins) as wins_total,
sum(ilr_champ.poles) as poles_total,
sum(ilr_champ.top5) as top5_total,
sum(ilr_champ.starts) as starts_total,
avg(ilr_champ.avg_s) as avg_s_total,
avg(ilr_champ.avg_f) as avg_f_total,
sum(ilr_champ.km * ilr_champ.laps) as km_total,
sum(ilr_champ.miles * ilr_champ.laps) as miles_total,
ilr_champ.team_name
')
->from('
ilr_event_result,
ilr_champ,
ilr_tracks
')
->join('ilr_season','ilr_season.season_id = ilr_champ.season_id')
->join('ilr_events', 'ilr_events.track_id = ilr_tracks.track_id')
->group_by('ilr_event_result.iracing_cust_id')
->order_by('champ_points desc, driver_title asc')
->get();
return $show_champ->result();
Then in my controller I added this to my Champ import. I know I got some db calls in my controller tisk tisk. I can clean that up later.
Code: $show_champ = $this->champ_model->get_champ_drop_import();
if($this->db->get('ilr_drop_races', array('season_id' => $this->uri->segment(6))))
{
$this->db->delete('ilr_drop_races', array('season_id' => $this->uri->segment(6)));
}
foreach($show_champ as $c)
{
$add = 0;
$drop = $this->champ_model->champ_drop_import($c->iracing_cust_id, $c->drop_races);
foreach($drop->result() as $d)
{
$add += $d->points + $d->bonus_pts - $d->pen_pts;
$import_drop = array(
'team_id' => $c->team_id,
'season_id' => $this->uri->segment(6),
'iracing_cust_id' => $c->iracing_cust_id,
'drop_total' => $c->champ_points - $add,
'team_name' => $c->team_name
);
}
$this->db->insert('ilr_drop_races', $import_drop);
}
[eluser]srpurdy[/eluser]
My new display championship standings model function
Code: function get_champ()
{
$num = '5';
$show_champ = $this->db
//->distinct()
->where('ilr_champ.season_id', $this->uri->segment(4))
->where('ilr_season.season_id', $this->uri->segment(4))
->where('ilr_drop_races.season_id', $this->uri->segment(4))
->where('ilr_drop_races.iracing_cust_id = ilr_champ.iracing_cust_id')
->where('ilr_event_result.iracing_cust_id = ilr_champ.iracing_cust_id')
->where('ilr_event_result.schedule_id = ilr_champ.schedule_id')
->where('ilr_champ.schedule_id = ilr_events.schedule_id')
->where('ilr_events.track_id = ilr_tracks.track_id')
->select('
ilr_event_result.iracing_cust_id,
ilr_event_result.driver_title,
ilr_events.track_id,
ilr_tracks.turns,
ilr_season.drop_races,
ilr_season.multi_class,
ilr_season.car_name,
ilr_season.car2,
ilr_season.car3,
ilr_season.teams,
ilr_season.team_pts,
ilr_season.drop_mode,
ilr_drop_races.iracing_cust_id,
ilr_drop_races.drop_total,
sum(ilr_champ.points - ilr_champ.pen_pts + ilr_champ.bonus_pts) - ilr_drop_races.drop_total as champ_points,
sum(ilr_champ.laps) as laps_total,
sum(ilr_champ.led) as led_total,
sum(ilr_champ.inc) as inc_total,
sum(ilr_champ.wins) as wins_total,
sum(ilr_champ.poles) as poles_total,
sum(ilr_champ.top5) as top5_total,
sum(ilr_champ.starts) as starts_total,
avg(ilr_champ.avg_s) as avg_s_total,
avg(ilr_champ.avg_f) as avg_f_total,
sum(ilr_champ.km * ilr_champ.laps) as km_total,
sum(ilr_champ.miles * ilr_champ.laps) as miles_total
')
->from('
ilr_event_result,
ilr_champ,
ilr_tracks
')
->join('ilr_season','ilr_season.season_id = ilr_champ.season_id')
->join('ilr_events', 'ilr_events.track_id = ilr_tracks.track_id')
->join('ilr_drop_races', 'ilr_drop_races.iracing_cust_id = ilr_champ.iracing_cust_id')
->group_by('ilr_event_result.iracing_cust_id')
->order_by('champ_points desc, driver_title asc')
->get();
return $show_champ->result();
[eluser]srpurdy[/eluser]
View File Example
Code: <?php foreach($show_champ as $mc):?>
<?php endforeach;?>
<?php if(!$show_champ):?>
<p>No data exists for this championship yet!</p>
<?php else:?>
<?php if($mc->team_pts == 'F' AND $mc->teams == 'Y' OR $mc->teams == 'N'):?>
<?php if($mc->multi_class == '1'):?>
<h4><?=$mc->car_name?></h4>
<table id="champlist" class="tablesorter">
<thead>
<tr>
<th>Pos.</th>
<th>Driver</th>
<th>Pts</th>
<?php if($mc->drop_mode == 'ON'):?>
<th>Pts Total</th>
<?php endif;?>
<th>Behind</th>
<th>S</th>
<th>P</th>
<th>W</th>
<th>T5s</th>
<th>Avg S</th>
<th>Avg F</th>
<th>Laps</th>
<th>LL</th>
<th>Pole%</th>
<th>Win%</th>
<th>LL%</th>
<th>Miles</th>
<th>KM</th>
<th>Inc/R</th>
<th>Inc</th>
<th>CPI</th>
</tr>
</thead>
<tbody>
<?php
$count = '1';
?>
<?php foreach($show_champ as $ch):?>
<tr>
<td><?=$count++?></td>
<td><?=$ch->driver_title?></td>
<td><?=/*$dp->points;*/$ch->champ_points?></td>
<?php if($mc->drop_mode == 'ON'):?>
<td><?=$ch->champ_points + $ch->drop_total?><?//=$add;?></td>
<?php endif;?>
<td><?$behind = $ch->champ_points - $pts_total_nmc?><?=$behind?></td>
<td><?=$ch->starts_total?></td>
<td><?=$ch->poles_total?></td>
<td><?=$ch->wins_total?></td>
<td><?=$ch->top5_total?></td>
<td><?=number_format($ch->avg_s_total, 1)?></td>
<td><?=number_format($ch->avg_f_total, 1)?></td>
<td><?=$ch->laps_total?></td>
<td><?=$ch->led_total?></td>
<td><?php if($ch->poles_total == 0):?>0%<?php else:?><?$pole_perc = $ch->poles_total / $ch->starts_total?><?$p_p = $pole_perc * 100; $p_p_result = number_format($p_p, 1);?><?=$p_p_result?>%<?php endif;?></td>
<td><?php if($ch->wins_total == 0):?>0%<?php else:?><?$win_perc = $ch->wins_total / $ch->starts_total?><?$w_p = $win_perc * 100; $w_p_result = number_format($w_p, 1);?><?=$w_p_result?>%<?php endif;?></td>
<td><?php if($ch->led_total == 0):?>0%<?php else:?><?$ll_perc = $ch->led_total / $ch->laps_total?><?$ll_p = $ll_perc * 100; $ll_p_result = number_format($ll_p, 1);?><?=$ll_p_result?>%<?php endif;?></td>
<td><?=$ch->miles_total?></td>
<td><?=$ch->km_total?></td>
<td><?php if($ch->inc_total == 0):?>0.0<?php else:?><?$inc_per_race = $ch->inc_total / $ch->starts_total?><?=number_format($inc_per_race, 1)?><?php endif;?></td>
<td><?=$ch->inc_total?></td>
<td><?$cpi = $ch->turns * $ch->laps_total?><?php if($ch->inc_total == 0):?><?=@number_format($cpi, 1, '.', '')?><?php else:?><?=@number_format($cpi / $ch->inc_total, 1, '.', '')?><?php endif;?></td>
</tr>
<?php endforeach; ?>
</tbody>
</table>
<?php endif;?>
So basically a new table, the drop total gets inserted into that table so I can join that with my championship to display the points with the drop races. Maybe this will come in handy for someone for something else.
[eluser]Caio Russo[/eluser]
Glad to help!
|