Array merge and results

[andyd34]

I have 2 arrays

$credits = array('week' => '(1-52)', 'year' => 'Whatever Year', 'credit' => '3333');

$debits = array('week' => '(1-52)', 'year' => 'Whatever Year', 'debit' => '3333');

I have merged them but want to display the results as a profit & loss table

<table>
  <tr>
    <th>Week</th>
    <th>Credits</th>
    <th>Debits</th>
    <th></th>
  </tr>
<?php
 foreach($array as $row)
  {
    <td><?=$row->Week .'-'. $row-Year?></td>
    <td><?=$row->Credit?></td>
    <td><?=$row->Debit?></td>
    <td><?=$row-Credit-$row-Debit?></td>
  }
?>
</table>

the trouble is i cannot find a way of adding the values to the array so have merged them which isnt what i want

 

what i realley needed to to is as the debit to the array where the week is equal to the credit week

 

Does anyone know how i can achieve this?

[Jacques1]

I don't know anything about the underlying data structure and whether it even makes sense, but you could turn one of the two arrays into a lookup table with (year, week) as the key. This allows you to immediately associate the values while iterating over the other array.

 

As pseudo code:

// lookup table for credits: (year, week) -> credit
credit_lookup = []
for credit_data in credits:
  credit_lookup[credit_data.year][credit_data.week] = credit_data.credit

for debit_data in debits:
  // get credit
  credit = credit_lookup[debit_data.year][debit_data.week]
  difference = credit - debit_data.debit

[andyd34]

I've managed to sort it but its a little convoluted, if anyone knows a better way it would be much appreciated

$debits = array();
  $i=1;
  foreach($data['debits'] as $key=>$value)
    {
      foreach($value as $row)
        {
          if(isset($row->Week))
            {
              $debits[$i]['Week'] = $row->Week;
              $debits[$i]['Year'] = $row->Year;
              $debits[$i]['Debit'] = $row->Debit;
              $i++;
            }
        }
      }

$i=1;
  foreach($data['credits'] as $key=>$value)
    {
      foreach($value as $row)
        {
          if(isset($row->Week))
            {
               $credits[$i]['Credit'] = $row->Credit;
               $i++;
            }
        }
     }
$row = array();
for($i=0;$i<count($debits)+1;$i++)
  {
    if(isset($debits[$i]['Week']))
      {
        $week = $debits[$i]['Week'];
        $year = $debits[$i]['Year'];
        $date = date("l jS F Y", strtotime("{$year}-W{$week}-1"));
        $credit = number_format($credits[$i]['Credit'],2,'.',',');
        $debit = number_format($debits[$i]['Debit'],2,'.',',');
        $amount = number_format($credits[$i]['Credit'] - $debits[$i]['Debit'],2,'.',',');
        if($amount < 0 ? $color = 'red' : $color = 'green');
        array_push($row, "<tr><td>$date</td><td>$credit</td><td>$debit</td><th style='color:$color'>$amount</th></tr>"."\n");
      }
  }
?>
<table class="table">
  <tr>
    <th>Week</th>
    <th>Credits</th>
    <th>Debits</th>
    <th>Profit / Loss</th>
   </tr>
<?php
  foreach($row as $ker=>$val) 
    {
      echo $val;
    }
?>

[Barand]

Are the debit and credit items from a database?

[andyd34]

Yes, 2 seperate tables

[Jacques1]

Then you need to learn the basics of SQL, particularly joins.

[Barand]

A single SQL query can do all the work for you, no need to mess with arrays.

 

A potential problem is that there may be weeks with credits and no debits and vice versa. A way round this is to use a table subquery with a union to gather all the data, then total that subquery.

 

Suppose you have this data

CREDIT table                                 DEBIT table
+-----------+------------+--------+          +----------+------------+--------+
| credit_id | cr_date    | amount |          | debit_id | db_date    | amount |
+-----------+------------+--------+          +----------+------------+--------+
|         1 | 2015-12-25 |  50.00 |          |        1 | 2015-12-15 |  30.00 |
|         2 | 2015-12-26 |  40.00 |          |        2 | 2015-12-15 |  35.00 |
|         3 | 2015-12-27 |  60.00 |          |        3 | 2015-12-22 |  55.00 |
|         4 | 2015-12-28 |  20.00 |          |        4 | 2015-12-30 |  30.00 |
|         5 | 2016-01-04 |  30.00 |          |        5 | 2016-01-05 |  25.00 |
|         6 | 2016-01-05 |  45.00 |          |        6 | 2016-01-06 |  50.00 |
|         7 | 2016-01-06 |  55.00 |          |        7 | 2016-01-07 |  45.00 |
|         8 | 2016-01-11 |  25.00 |          |        8 | 2016-01-12 |  20.00 |
|         9 | 2016-01-13 |  70.00 |          |        9 | 2016-01-14 |  65.00 |
|        10 | 2016-01-18 |  65.00 |          |       10 | 2016-01-16 |  70.00 |
|        11 | 2016-01-21 |  55.00 |          |       11 | 2016-01-17 |  30.00 |
|        12 | 2016-01-25 |  50.00 |          |       12 | 2016-01-30 |  80.00 |
+-----------+------------+--------+          +----------+------------+--------+

then

SELECT
    DATE_FORMAT(date,'%x') as yr
  , DATE_FORMAT(date,'%v') as wk
  , MIN(date - INTERVAL WEEKDAY(date) DAY) as startdate
  , SUM(credit) as credit
  , SUM(debit) as debit
  , SUM(credit - debit) as diff
FROM
    (
    SELECT cr_date as date
      , amount as credit
      , 0 as debit
    FROM credit
    
    UNION ALL
    
    SELECT db_date as date
      , 0 as credit
      , amount as debit
    FROM debit
    ) data
GROUP BY yr,wk;

gives you

+------+------+------------+--------+--------+--------+
| yr   | wk   | startdate  | credit | debit  | diff   |
+------+------+------------+--------+--------+--------+
| 2015 | 51   | 2015-12-14 |   0.00 |  65.00 | -65.00 |
| 2015 | 52   | 2015-12-21 | 150.00 |  55.00 |  95.00 |
| 2015 | 53   | 2015-12-28 |  20.00 |  30.00 | -10.00 |
| 2016 | 01   | 2016-01-04 | 130.00 | 120.00 |  10.00 |
| 2016 | 02   | 2016-01-11 |  95.00 | 185.00 | -90.00 |
| 2016 | 03   | 2016-01-18 | 120.00 |   0.00 | 120.00 |
| 2016 | 04   | 2016-01-25 |  50.00 |  80.00 | -30.00 |
+------+------+------------+--------+--------+--------+

Job done.

Edited May 30, 2016 by Barand