Okay let me start all over again.
First we have three tables:
Table: transaction
meter_id | bay_number | trans_date_time | amount
----------+------------+----------------------------+--------
1078 | 5 | 2013-06-03 09:59:32+10 | 5.00
1078 | 7 | 2013-06-03 09:12:01+10 | 5.00
1078 | 6 | 2013-06-03 08:33:11+10 | 5.00
1078 | 8 | 2013-05-27 09:48:26+10 | 5.00
1078 | 5 | 2013-05-27 09:41:42+10 | 5.00
1078 | 4 | 2013-05-27 09:41:08+10 | 5.00
1078 | 7 | 2013-05-20 17:14:15+10 | 2.00
1078 | 2 | 2013-05-20 16:19:10+10 | 2.50
Table: service
meter_id | notified_at | finished_at
----------+------------------------+------------------------
1078 | 2013-05-30 15:02:27+10 | 2013-05-30 15:32:20+10
1078 | 2013-05-30 12:32:43+10 | 2013-05-30 14:50:14+10
1078 | 2013-05-30 08:31:27+10 | 2013-05-30 10:25:56+10
1078 | 2013-05-29 07:36:31+10 | 2013-05-29 11:35:47+10
Table: relocated_meter
relocation_date | meter_id | bay_number | source_meter_id | source_bay_number
-----------------+----------+------------+-----------------+-------------------
2013-04-24 | 1078 | 1 | 1078 | 1
2013-04-24 | 1078 | 2 | 1078 | 2
2013-04-24 | 1078 | 3 | 1078 | 3
2013-04-24 | 1078 | 4 | 1078 | 4
2013-04-24 | 1078 | 5 | 1078 | 5
2013-04-24 | 1078 | 6 | 1078 | 6
2013-04-24 | 1078 | 7 | 1078 | 7
2013-04-24 | 1078 | 8 | 1067 | 5
2013-04-24 | 1078 | 9 | 1067 | 6
What I am trying to write is some elegant function/s inside a PHP class to generate the following:
For each service records, what is the income received by each meter? Then, generate income for the the last 52 weeks (eg, if the meter 1078 was down from 10:00 to 10:30 this tuesday, what is the income for the same meter on last tuesday between 10:00 and 10:30 and the week before that all through 52 weeks). This is so I get an annual average for each meter during the same time period.
Now, a few meters have inherited some bays and with the introduction of the third table called relocated_meter, I'd like to get the weekly amount PLUS income for another meter (1067 bay 5 and 1067 bay 6) because they're now part of meter 1078 bay 8 and 9, so average income should include the other bays too for dates before relocated_date
I hope this makes sense?
function getWeeks()
{
$meter_combo = array(
array(1234 => 1),
array(1234 => 2),
array(1234 => 3),
array(1234 => 4),
);
$notified_time = '2013-05-17 12:20:48';
$completed_time = '2013-05-17 12:52:07';
$relocation_date = '2013-04-30 00:00';
$old_meter_combo = array(
array(4444 => 1),
array(4444 => 2),
array(4444 => 8),
array(4444 => 9),
);
$notified_time = strtotime($notified_time);
$completed_time = strtotime($completed_time);
$relocation_date = date($relocation_date);
$weeks = 2;
$combined = array();
for($i=0;$i<=$weeks;$i++)
{
$start_series = strtotime("- $i weeks", $notified_time);
$end_series = strtotime("- $i weeks", $completed_time);
if(strtotime($relocation_date) < $start_series) {
$combined[] = array(
'week_id' => $i,
'start_time' => date('Y-m-d H:i:s',$start_series),
'end_time' => date('Y-m-d H:i:s',$end_series),
'relocation' => $relocation_date,
'meter_id' => $meter_combo,
);
} else {
$combined[] = array(
'week_id' => $i,
'start_time' => date('Y-m-d H:i:s',$start_series),
'end_time' => date('Y-m-d H:i:s',$end_series),
'relocation' => $relocation_date,
'meter_id' => $old_meter_combo,
);
}
}
$sql1 = "SUM(CASE WHEN (";
foreach($combined as $k1 => $v1)
{
$sql2 = "AND trans_date_time BETWEEN " . $v1['start_time'] . " AND " .$v1['end_time']. "<br/>" ;
$sql3 = "THEN amount ELSE 0 END) AS week_" . $v1['week_id'] . "<br />" ;
foreach ($v1['meter_id'] as $v2) {
foreach ($v2 as $meter => $bay) {
$sql4[] = "meter_id = $meter and bay_number = $bay <br/>";
}
}
}
$sql4 = implode(" OR ", $sql4);
echo $sql1.$sql4.$sql2.$sql3;
}
Any other elegant want of doing this is greatly welcomed :)
Thanks.
i made a small change to your code, nothing fancy.
$sql1 = "SUM(CASE WHEN (";
foreach($combined as $k1 => $v1)
{
$sql2 = "AND trans_date_time BETWEEN " . $v1['start_time'] . " AND " .$v1['end_time']. " " ;
$sql3 = "THEN amount ELSE 0 END) AS week_" . $v1['week_id'] . " " ;
foreach($v1 as $k2)
{
foreach($v1['meter_id'] as $k3 => $v3)
{
foreach($v3 as $meter => $bay)
{
$sql4[] = "meter_id = $meter and bay_number = $bay ";
}
}
}
}
$sql4 = implode(" OR ", $sql4);
echo $sql1 . $sql4 . $sql2 . $sql3;
i made a small change to your code
$sql1 = "SUM(CASE WHEN (";
foreach($combined as $k1 => $v1)
{
$sql2 = "AND trans_date_time BETWEEN " . $v1['start_time'] . " AND " .$v1['end_time']. "<br/>" ;
$sql3 = "THEN amount ELSE 0 END) AS week_" . $v1['week_id'] . "<br />" ;
foreach ($v1['meter_id'] as $v2) {
foreach ($v2 as $meter => $bay) {
$sql4[] = "meter_id = $meter and bay_number = $bay <br/>";
}
}
}
$sql4 = implode(" OR ", $sql4);
echo $sql1.$sql4.$sql2.$sql3;