/index.php

https://github.com/nylen/wesabe-balances · PHP · 136 lines · 116 code · 20 blank · 0 comment · 0 complexity · d0cc0529f923eacb66cf17800c62d68f MD5 · raw file 

    

  1. <?php
    2. 

  2. 

  3. $time = microtime(true);
    4. 

  4. 

  5. require_once 'settings.php';
    6. 

  6. 

  7. $link = mysql_connect($db_server, $db_username, $db_password)
    8. 

  8.   or die(mysql_error());
    9. 

  9. 

  10. mysql_select_db($db_database) or die(mysql_error());
    11. 

  11. 

  12. $result = mysql_query(<<<SQL
    13. 

  13.   select a.name, a.type, a.id,
    14. 

  14.     coalesce(b.balance, i.avail_cash + p.market_value) balance,
    15. 

  15.     coalesce(b.updated_at, greatest(i.created_at, p.updated_at)) updated_at
    16. 

  16. 

  17.   from accounts a
    18. 

  18. 

  19.   left join (
    20. 

  20.     select b.*
    21. 

  21.     from account_balances b
    22. 

  22. 

  23.     inner join (
    24. 

  24.       select account_id, max(updated_at) updated_at
    25. 

  25.       from account_balances
    26. 

  26.       group by 1
    27. 

  27.     ) m
    28. 

  28.     on m.account_id = b.account_id
    29. 

  29.     and m.updated_at = b.updated_at
    30. 

  30.   ) b
    31. 

  31.   on a.id = b.account_id
    32. 

  32. 

  33.   left join (
    34. 

  34.     select b.*
    35. 

  35.     from investment_balances b
    36. 

  36. 

  37.     inner join (
    38. 

  38.       select account_id, max(created_at) created_at
    39. 

  39.       from investment_balances
    40. 

  40.       group by 1
    41. 

  41.     ) m
    42. 

  42.     on m.account_id = b.account_id
    43. 

  43.     and m.created_at = b.created_at
    44. 

  44.   ) i
    45. 

  45.   on a.id = i.account_id
    46. 

  46. 

  47.   left join (
    48. 

  48.     select p.account_id,
    49. 

  49.       sum(p.market_value) market_value,
    50. 

  50.       max(p.updated_at) updated_at
    51. 

  51.     from investment_positions p
    52. 

  52. 

  53.     inner join (
    54. 

  54.       select account_id, investment_security_id, max(updated_at) updated_at
    55. 

  55.       from investment_positions
    56. 

  56.       group by 1,2
    57. 

  57.     ) m
    58. 

  58.     on m.account_id = p.account_id
    59. 

  59.     and m.investment_security_id = p.investment_security_id
    60. 

  60.     and m.updated_at = p.updated_at
    61. 

  61. 

  62.     group by 1
    63. 

  63.   ) p
    64. 

  64.   on a.id = p.account_id
    65. 

  65. 

  66.   order by a.type, a.name
    67. 

  67. SQL
    68. 

  68.   ) or die($mysql_error());
    69. 

  69. ?>
    70. 

  70. <!DOCTYPE html>
    71. 

  71. <html>
    72. 

  72.   <head>
    73. 

  73.     <meta name="viewport" content="width=device-width" />
    74. 

  74.     <title>Account balances</title>
    75. 

  75.     <style type="text/css">
    76. 

  76.       body {
    77. 

  77.         font-family: Verdana, Arial, sans-serif;
    78. 

  78.       }
    79. 

  79.       .account {
    80. 

  80.         padding-bottom: 8px;
    81. 

  81.         margin-bottom: 8px;
    82. 

  82.         border-bottom: 2px solid #ccc;
    83. 

  83.       }
    84. 

  84.       .description {
    85. 

  85.         font-weight: bold;
    86. 

  86.         padding-right: 4px;
    87. 

  87.       }
    88. 

  88.     </style>
    89. 

  89.   </head>
    90. 

  90.   <body>
    91. 

  91. <?php
    92. 

  92. 

  93. $account_types = array(
    94. 

  94.   'Account'           => 'Regular',
    95. 

  95.   'InvestmentAccount' => 'Investment'
    96. 

  96.   );
    97. 

  97. 

  98. $first = true;
    99. 

  99. while($row = mysql_fetch_assoc($result)) {
    100. 

  100.   $type = $account_types[$row['type']];
    101. 

  101.   $balance = number_format($row['balance'], 2);
    102. 

  102.   $updated_at = date('n/d/Y g:i:s a', strtotime("$row[updated_at] UTC"));
    103. 

  103. 

  104.   echo <<<HTML
    105. 

  105.     <div class="account">
    106. 

  106.       <table>
    107. 

  107.         <tr>
    108. 

  108.           <td class="description">Account:</td>
    109. 

  109.           <td class="content">$row[name]</td>
    110. 

  110.         </tr>
    111. 

  111.         <tr>
    112. 

  112.           <td class="description">Type:</td>
    113. 

  113.           <td class="content">$type</td>
    114. 

  114.         </tr>
    115. 

  115.         <tr>
    116. 

  116.           <td class="description">Balance:</td>
    117. 

  117.           <td class="content">\$$balance</td>
    118. 

  118.         </tr>
    119. 

  119.         <tr>
    120. 

  120.           <td class="description">As of:</td>
    121. 

  121.           <td class="content">$updated_at</td>
    122. 

  122.         </tr>
    123. 

  123.       </table>
    124. 

  124.     </div>
    125. 

  125. 

  126. HTML;
    127. 

  127. }
    128. 

  128. 

  129. $time = number_format(microtime(true) - $time, 4);
    130. 

  130. echo <<<HTML
    131. 

  131.     Page generated in ${time}s
    132. 

  132. 

  133. HTML;
    134. 

  134. ?>
    135. 

  135.   </body>
    136. 

  136. </html>
    137.