好的，让我重新开始。
首先我们有三张表：
表：交易
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
表：服务
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
表：重新定位的仪表
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
我试图在PHP类中编写一些优雅的函数来生成以下内容：
对于每个服务记录，每个电表的收入是多少？然后，产生过去52周的收入（例如，如果仪表1078从本周二10:00到10:30下降，那么上周二10:00到11:30以及前一周整个52周的同一仪表的收入是多少）。所以我得到了同一时间段内每米的年平均值。
现在，一些电表继承了一些间隔，随着第三个表relocated_meter的引入，我想获得另一个电表（1067间隔5和1067间隔6）的每周金额PLUS收入，因为它们现在是1078间隔8和9电表的一部分，所以平均收入也应该包括relocated_date 之前日期的其他间隔
我希望这有道理？
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;
}
任何其他想要做到这一点的优雅人士都非常欢迎：）
谢谢。
我对您的代码做了一个小改动，没有什么特别之处。
$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;
我对您的代码做了一个小更改
$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;