Custom Rental Diary

[eluser]ridley1012[/eluser]
Hi I have quite a specific requirement in terms of a diary for a rental system, I need to be able to select a date range which then builds a table and lists all vehicles, then cross reference the dates and vehicles with the database to check if the vehicles are available or booked out. so far I have a function to return the date range as an array. I then have the following model function

Code:

function get_all_vehicles()
{
  $this->db->select('*');
  $this->db->join('types', 'types.type_index = vehicles.type_index');
  $query = $this->db->get_where('vehicles', array('sold' => 'No'));
  return $query->result();
}

my controller is as follows

Code:

public function diary()
{
  $this->load->model('vehicle_model', 'vehicles', TRUE);
  $data['page'] = $this->page;
  $data['vehicles'] = $this->vehicles->get_all_vehicles();
  $data['dates'] = $this->get_date_range('2011-12-01', '2011-12-06');
  $data['title'] = "Administration Panel - Rental Diary";
  $this->load->model('rentals_model', 'rentals', TRUE);
  $this->load->view('templates/admin/header', $data);
  $this->load->view('templates/admin/sidebar');
  $this->load->view('admin/diary');
  $this->load->view('templates/admin/footer');
}

and then this is currently the view to display it

Code:

<table id="diary">
    <thead>
     <tr>
  <td><strong>VEHICLE REG</strong></td>
   &lt;?php foreach($dates as $date) {?&gt;
     <td>&lt;?=$date?&gt;</td>
   &lt;?php }?&gt;
</tr>
    </thead>
    <tbody>
      &lt;?php foreach($vehicles as $vehicle) { ?&gt;
<tr>
  <td>&lt;?=$vehicle->registration;?&gt;</td>
    &lt;?php foreach($dates as $date) {?&gt;
        <td>&lt;!--WHAT TO DO HERE!!!!!!!--&gt;</td>
      &lt;?php }?&gt;
   </tr>
&lt;?php }?&gt;
    </tbody>
</table>

the problem I have is checking the availibility for each vehicle on each date, I know it would be easy enough to do by calling information from the database within the view file but I want to stick with the MVC format. Any help/suggestions would be greatly appreciated.

[eluser]ridley1012[/eluser]
Please let me know if anymore information is needed.

[eluser]gRoberts[/eluser]
What you need to do is have a table that contains a link between the vehicle and the dates it's taken.

i.e.

Code:

create table VehicleUsage
(
VehicleUsageID int not null,
VehicleID int not null,
StartDate int not null,
EndDate int not null
)

then, when you "rent" out a car, you simply insert the vehicle id and the dates in which the vehicle is rented out between, i.e.

Code:

insert into VehicleUsage (VehicleID, StartDate, EndDate) values (1, 1336643788, 1336989388)

The above says, that VehicleID `1` is rented out between 10th May and 14th May.

From there, you can use an SQL Query to select all vehicles that are being rented out during those two dates.

Hope that helps?

[eluser]ridley1012[/eluser]
Hi thanks for the reply, I don't think I made myself clear enough, I already have a rentals table which stores all the vehicle usage.

I was hoping not to have to connect to the database from within the view file and was hoping someone may be able to advise how to do so. At the moment it doesn't seem possible.

[eluser]gRoberts[/eluser]
The best way, or at least the way I would do this, is to return an array of vehicles, which will be your basis for your matrix.

i.e. if there were no vehicles rented out, this would simply show available for all vehicles.

Then, I would query the database for all vehicles that are rented out between the two dates.

i.e

Code:

<?
function get_vehicle_usage($From, $To)
{
  $_From = strtotime($From);
  $_To = strtotime($To);
  $this->db->where('StartDate >=', $_From);
  $this->db->where('EndDate <=', $_To);
  $q = $this->db->get('vehicleusage');
  $ret = array();
  if($q->num_rows() > 0)
  {
   foreach($q->result() as $row)
   {
    $start = $row->StartDate;
    while($start < $row->EndDate)
    {
     $ret[$row->VehicleID][] = date('d-m-Y', $tmp);
     $start = strtotime('+1 day', $start);
    }
   }
  }
  $q->free_result();
  return $ret;
}
?&gt;

The above would return an multi-dimensional array, which the index would contain the VehicleID and the value would be an array of dates that vehicle is not available.

Then, I would do the following in your view:

Code:

<table id="diary">
<thead>
  <tr>
   <td><strong>VEHICLE REG</strong></td>
   &lt;?php foreach($dates as $date) {?&gt;
    <td>&lt;?=$date?&gt;</td>
   &lt;?php }?&gt;
  </tr>
</thead>
<tbody>
  &lt;?php foreach($vehicles as $vehicle) { ?&gt;
  <tr>
   <td>&lt;?=$vehicle->registration;?&gt;</td>
   &lt;?php foreach($dates as $date) {?&gt;
    <td>&lt;?= (isset($VehicleUsage[$vehicle->VehicleID]) ? (in_array($date, $VehicleUsage[$vehicle->VehicleID]) ? 'Unavailable' : 'Available') : 'Available'); ?&gt;</td>
   &lt;?php }?&gt;
  </tr>
  &lt;?php }?&gt;
</tbody>
</table>

[eluser]ridley1012[/eluser]
Hi thanks again for the reply, that looks like it could be similar to the route I decided to go down, thanks again for you help.