Logo Search packages:      
Sourcecode: fibusql version File versions  Download package

ledger.inc

<?php
/*
    FibuSQL 0.4.1  -  (c) 2003 Martin Pitt <martin@piware.de>

    This software is protected by the GNU General Public License (see
    file COPYING).

    Backend module: ledger functions
*/

include_once 'backend/base.inc';

# calculate balance of account $acc
# returns balance on success or DB::Error object on DB failure
function get_balance( $acc )
{
    global $db;

    $cache = $db->getOne( "select bal from balances where account=$acc" );
    if( $cache != null ) return $cache; // may also be a DB::Error

    # balance for this account's entries
    $debsum = $db->getOne( "select sum(value) from journal where debit_acc=$acc" );
    if( DB::isError( $debsum ) ) return $debsum;
    $credsum = $db->getOne( "select sum(value) from journal where credit_acc=$acc" );
    if( DB::isError( $credsum ) ) return $credsum;

    $balance = $debsum - $credsum;

    # add balances for subaccounts
    $res_subs = $db->query( "select account from accounts where parent=$acc and account<>0" );
    if( DB::isError( $res_subs ) ) return $res_subs;
    while( list($a) = $res_subs->fetchRow() ) {
      $sb = get_balance( $a );
      if( DB::isError( $sb ) ) return $sb;
      $balance += $sb;
    }

    $res = $db->query( "insert into balances values( $acc, $balance )" );
    if( DB::isError( $res ) ) return $res;

    return $balance;
}

# calculate per-month balances of account $acc
# returns month => balance hash on success or DB::Error object on DB failure
function balances_by_month( $acc )
{
    global $db;

# initialize array to prevent returning null objects
    $bal = array();

# calc per-month debit sums
    $res = $db->query( "select sum(value),extract( MONTH from time ) as month, extract( YEAR from time ) as year".
      " from journal where debit_acc = $acc group by month,year" );
    if( DB::isError( $res ) ) return $res;

    while( $row = $res->fetchRow() ) {
      $mon = sprintf( '%02d', $row[1] );
      $bal[$row[2].'/'.$mon] = $row[0];
    }

# substract per-month credit sums to get the per-month balance
    $res = $db->query( "select sum(value),extract( MONTH from time ) as month, extract( YEAR from time ) as year".
      " from journal where credit_acc = $acc group by month,year" );
    if( DB::isError( $res ) ) return $res;

    while( $row = $res->fetchRow() ) {
      $mon = sprintf( '%02d', $row[1] );
      $bal[$row[2].'/'.$mon] -= $row[0];
    }
    
# add per-month balances from subaccounts
    $res = $db->query( "select account from accounts where parent = $acc and account <> 0" );
    if( DB::isError( $res ) ) return $res;

    while( $row = $res->fetchRow() ) {
      foreach( balances_by_month( $row[0] ) as $m => $b )
          $bal[$m] += $b;
    }

    return $bal;
}

# array sort orderings for ledger_account()
define( 'LEDGER_SORT_RECEIPT', 1 ); # receipt, then time (when no receipt)
define( 'LEDGER_SORT_DESC', 2 ); # first description, then time
define( 'LEDGER_SORT_TIME', 3 ); # time 
define( 'LEDGER_SORT_RTIME', 4 ); # reverse time 

# generate ledger account (first the subaccounts, then the bookings);
# account is sorted with ordering $sortorder (default: first the subaccounts,
# then chronological credit bookings, then chronological debit bookings)
# returns array of arrays with the following structure:
#   (value, description, time, receipt, acctype, account, bookid)
#   description: description of bookings, account name of subaccounts, or 'Balance' 
#   acctype: 1=asset, 2=liability, 3=revenue, 4=expense
#   account: account id if subaccount balance, null if booking on this account,
#   -1 on this account's balance
#   bookid: journal id if booking on this account, null if subaccount
# returns a DB::Error object on DB failure
function ledger_account( $acc, $sortorder = null )
{
    global $db;

    $type = $db->getOne( 'select type from accounts where account='.$acc );
    if( DB::isError( $type ) ) return $type;

    $thisbal = get_balance( $acc );
    if( DB::isError( $thisbal ) ) return $thisbal;

    $subaccs = $db->query( "select account,name,type from accounts where parent=$acc and account <> 0 order by account" );
    if( DB::isError( $subaccs ) ) return $subaccs;

    $debit = $db->query( 'select value,description,receipt,extract(YEAR from time), extract(MONTH from time), '.
          'extract(DAY from time), extract(HOUR from time), extract(MINUTE from time), id'.
          " from journal where debit_acc=$acc order by time" );
    if( DB::isError( $debit ) ) return $debit;

    $credit = $db->query( 'select -value,description,receipt,extract(YEAR from time), extract(MONTH from time), '.
            'extract(DAY from time), extract(HOUR from time), extract(MINUTE from time), id'.
            " from journal where credit_acc=$acc order by time" );
    if( DB::isError( $credit ) ) return $credit;

    $result = array();
    
    while( $row = $subaccs->fetchRow() ) {
        $bal = get_balance( $row[0] );
      if( DB::isError( $bal ) ) return $bal;
      array_push( $result, array( $bal, $row[1], null, null, $row[2], $row[0], null ) );
    }

    while( $row = $debit->fetchRow() ) {
      $time = sprintf( '%d-%02d-%02d %02d:%02d', $row[3], $row[4], $row[5], $row[6], $row[7] );
      array_push( $result, array( $row[0], $row[1], $time, $row[2], $type, null, $row[8] ) );
    }
    while( $row = $credit->fetchRow() ) {
      $time = sprintf( '%d-%02d-%02d %02d:%02d', $row[3], $row[4], $row[5], $row[6], $row[7] );
      array_push( $result, array( $row[0], $row[1], $time, $row[2], $type, null, $row[8] ) );
    }

    switch( $sortorder ) {
      case LEDGER_SORT_RECEIPT: usort( $result, "ledger_compare_by_receipt" ); break;
      case LEDGER_SORT_DESC: usort( $result, "ledger_compare_by_desc" ); break;
      case LEDGER_SORT_TIME: usort( $result, "ledger_compare_by_time" ); break;
      case LEDGER_SORT_RTIME: usort( $result, "ledger_compare_by_rtime" ); break;
      default: break;
    }

    array_push( $result, array( -$thisbal, 'Balance', null, null, $type, -1, null ) );

    return $result;
}

########################################
# generate XML from given account $acc with stylesheet $xsl
# generate recursive accounts up to depth $maxlevel
# (0 = balance of current account only, 1 = direct subaccounts etc.)
# returns null on success or DB::Error object on failure
########################################
function gen_ledger_xml( $acc, $maxlevel, $xsl )
{
    echo '<?xml version="1.0" encoding="utf-8" ?>', "\n";
    if( $xsl )
      echo '<?xml-stylesheet href="', $xsl, '" type="text/xsl"?>', "\n";
    echo "\n<ledger>\n";
   
    $res = gen_ledger_xml_account( $acc, 0, $maxlevel );
    echo "</ledger>\n";
    return $res;
}

########################################
# comparison functions for sorting ledger
# for internal use only
########################################

function ledger_compare_by_time( $a, $b )
{
    if( $a[5] && $b[5] )  # two subaccounts
      return ($a[5] < $b[5]) ? -1 : 1;
    
    # one subacc, one booking
    if( $a[5] && !$b[5] )
      return -1;
    if( !$a[5] && $b[5] )
      return 1;

    # two bookings
    if( $a[2] < $b[2] )
      return -1;
    if( $a[2] > $b[2] )
      return 1;
    return strcmp( $a[1], $b[1] );
}

function ledger_compare_by_rtime( $a, $b )
{
    if( $a[5] && $b[5] )  # two subaccounts
      return ($a[5] < $b[5]) ? -1 : 1;
    
    # one subacc, one booking
    if( $a[5] && !$b[5] )
      return -1;
    if( !$a[5] && $b[5] )
      return 1;

    # two bookings
    if( $a[2] < $b[2] )
      return 1;
    if( $a[2] > $b[2] )
      return -1;
    return strcmp( $a[1], $b[1] );
}

function ledger_compare_by_desc( $a, $b )
{
    switch( strcmp( $a[1], $b[1] ) ) {
      case -1: return -1;
      case  1: return 1;
      default: return ledger_compare_by_time( $a, $b );
    }
}

function ledger_compare_by_receipt( $a, $b )
{
    # display items without receipt last
    if( is_null( $a[3] ) && !is_null( $b[3] ) )
      return 1;
    if( !is_null( $a[3] ) && is_null( $b[3] ) )
      return -1;

    if( $a[3] < $b[3] )
      return -1;
    if( $a[3] > $b[3] );
      return 1;

    return ledger_compare_by_time( $a, $b );
}

# generate XML output for account $acc.
# returns NULL on success or DB::Error object on database error
function gen_ledger_xml_account( $acc, $currlevel, $maxlevel )
{
    static $account_types = array( 'balance', 'asset', 'liability', 'revenue', 'expense' );

    global $db;

    # indentation
    echo str_repeat( '  ', $currlevel+1 );

    $accinfo = $db->getRow( 'select name, acronym, type from accounts where account='.$acc );
    $acr = trim( $accinfo[1] );
    if( DB::isError( $accinfo ) ) return $accinfo;
     
    echo '<account number="', $acc, '" name="', htmlq( $accinfo[0] ), 
      '" type="', $account_types[$accinfo[2]], '"';
    if( $acr ) echo ' acronym="', $acr, '"';
    echo ' balance="', get_balance( $acc ), '"';

    if( $maxlevel == -1 || $currlevel < $maxlevel ) {
      $subaccs = $db->getCol( 'select account from accounts where account <> 0 and parent='.$acc );
      if( DB::isError( $subaccs ) ) return $subaccs;

      if( count( $subaccs ) ) {
          echo ">\n";
          foreach( $subaccs as $a ) {
            $res = gen_ledger_xml_account( $a, $currlevel+1, $maxlevel );
            if( $res )
                return $res;
          }
          echo str_repeat( '  ', $currlevel+1 ), "</account>\n";
      } else
          echo " />\n";
    } else
      echo " />\n";

    return null;
}

?>

Generated by  Doxygen 1.6.0   Back to index