Tell me more ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 0 down vote favorite

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.

share|improve this question
 
I think it would be better to use: WHEN (meter_id = 1234 AND bay_number IN (1,2,3,4) –  JimL Jul 8 at 6:46

2 Answers

up vote 0 down vote

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;
share|improve this answer
 
Thanks for your answer @DevZer0 :) can you suggest how can I tweak this query if I were to have more weeks? I've updated my question. Thanks again. –  Glicious Jul 8 at 10:27
 
I need to see your full query so i can understand what your doing –  DevZer0 Jul 8 at 10:30
 
I have just updated my question again, thanks for your help @DevZer0 :) –  Glicious Jul 9 at 0:18
 
why are you adding <br/> to your SQL? –  DevZer0 Jul 9 at 1:52
 
Hi @DevZer0, That is just for readability, I'll remove them once I get the expected SQL. –  Glicious Jul 9 at 1:54
up vote 0 down vote

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;
share|improve this answer
 
Welcome to StackOverflow! We are allways looking for quality answers, so could you please provide some more details in your post? What exactly have you changed to the original source code? How does it help the OP to solve his problem? Feel free to edit your answer. –  Aschratt Jul 8 at 7:48

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.