PHP array to SQL string using foreach loop

Question

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.

Answer 1

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;

Answer 2

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;