I fetch a list of entries from database that represent the number of calls a representative has made during a given day and week. I want the rep to be able to see how much money he makes per week based on the amount of calls he made that given week. The week starts Sunday and ends Saturday. How can I calculate the total of money he's made for each given week by adding together all the calls he's made that week? I'm banging my head over this problem as I'm struggling to sum all values of a given week.
table.php
<table>
<thead>
<tr>
<th>Task</th>
<th>Status</th>
<th>Date</th>
<th>Day</th>
<th>Earning</th>
</tr>
</thead>
<tbody>
<?php
$i=1;
$total_weekly_pay=0;
while ($money = $selectprospectsqueryResult->fetch_assoc()){$i++;
$task = 'Outbound call';
$status = $money['call_status'];
//format dates
$date = $money['last_update'];
$datetime = new DateTime($date);
$day = $datetime->format('l');
if($status == 'Success'){
$pay = 0.25;
$bonus = 5.00;
$earning = $pay + $bonus;
}else{
$pay = 0.25;
$earning = $pay;
}
//get week of date
$this_week = date("W", strtotime($date));
//get year of date
$this_year = date("Y", strtotime($date));
//this monday of date
$this_monday_date = new DateTime();
$this_monday_date->setISODate($this_year,$this_week);
$this_monday = $this_monday_date->format('Y-m-d');
//this sunday of date
//calculate -1 day to find sunday of the same given week
$sundate = strtotime($this_monday);
$newsundate = strtotime("-1 day", $sundate);
$this_sunday = date('Y-m-d', $newsundate);
//echo ' to ' ;//testing
//calculate +6 days to find saturday of the same given week
$saturdate = strtotime($this_sunday);
$newsaturdate = strtotime("+6 day", $saturdate);
$this_saturday = date('Y-m-d', $newsaturdate);
//echo ' <br> ' ;//testing
//format the given call date
$new_date_format = date( 'Y-m-d', strtotime($date)) ;
//create array of saturdays : could be useful?
/* $saturdays = array();
$saturdays[0] = date('Y-m-d', strtotime('first saturday of this month'));
$saturdays[1] = date('Y-m-d', strtotime('second saturday of this month'));
$saturdays[2] = date('Y-m-d', strtotime('third saturday of this month'));
$saturdays[3] = date('Y-m-d', strtotime('fourth saturday of this month'));
$fifth = strtotime('fifth saturday of this month');
if (date('m') === date('m', $fifth)) {
$saturdays[4] = date('Y-m-d', $fifth);
}*/
echo ' <tr>
<td>'.$task.'</td>
<td>'.$status.'</td>
<td>'.$date.'</td>
<td>'.$day.'</td>
<td>$'.$earning.'</td>
</tr>';
if(week_is_over){
echo ' <tr>
<td>-</td>
<td>-</td>
<td>From '.$this_sunday.' to '.$this_saturday.'</td>
<td>Total for week</td>
<td>'.$total_weekly_pay+=$earning.'</td>
</tr>';
}
}?>
</tbody>
</table>
This is what I'm trying to achieve
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.4.0/css/bootstrap.min.css">
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.4.0/js/bootstrap.min.js"></script>
<table class="table table-bordered table-condensed table-striped table-hover" width="100%" cellspacing="0">
<thead>
<tr>
<th>Task</th>
<th>Status</th>
<th>Date</th>
<th>Day</th>
<th>Earning</th>
</tr>
</thead>
<tbody>
<tr>
<td>Outbound call</td>
<td>Success</td>
<td>2019-04-07 19:05:09</td>
<td>Sunday</td>
<td>$5.25</td>
</tr>
<tr>
<td>Outbound call</td>
<td>Success</td>
<td>2019-04-08 17:05:09</td>
<td>Monday</td>
<td>$5.25</td>
</tr>
<tr>
<td>-</td>
<td>-</td>
<td>From 2019-04-07 to 2019-04-13</td>
<td>Total for this week</td>
<td>$10.50</td>
</tr>
<tr>
<td>Outbound call</td>
<td>No Response</td>
<td>2019-04-15 18:38:01</td>
<td>Monday</td>
<td>$0.25</td>
</tr> <tr>
<td>Outbound call</td>
<td>Busy Line</td>
<td>2019-04-15 18:37:57</td>
<td>Monday</td>
<td>$0.25</td>
</tr> <tr>
<td>Outbound call</td>
<td>Voicemail</td>
<td>2019-04-16 18:37:54</td>
<td>Tuesday</td>
<td>$0.25</td>
</tr> <tr>
<td>Outbound call</td>
<td>Not Interested</td>
<td>2019-04-16 18:37:51</td>
<td>Tuesday</td>
<td>$0.25</td>
</tr> <tr>
<td>Outbound call</td>
<td>Success</td>
<td>2019-04-17 18:37:48</td>
<td>Wednesday</td>
<td>$5.25</td>
</tr> <tr>
<td>Outbound call</td>
<td>Ongoing</td>
<td>2019-04-18 18:37:43</td>
<td>Thursday</td>
<td>$0.25</td>
</tr>
<tr>
<td>-</td>
<td>-</td>
<td>From 2019-04-14 to 2019-04-20</td>
<td>Total for this week</td>
<td>$6.50</td>
</tr>
</tbody>
</table></div>
