ikiwiki-upstream - Unnamed repository; edit this file to name it for gitweb.

summary refs log tree commit diff

Branch	Commit message	Author	Age
layoutgala	The website http://blog.html.it/layoutgala/ present a simple yet	Jonas Smedegaard	16 years
master	Update danish l10n of underlays.	Jonas Smedegaard	14 years

pan>

# This work contains copyrighted information from a number of sources all used

# with permission.

# This file contains source code included with or based on SQL-Ledger which

# is Copyright Dieter Simader and DWS Systems Inc. 2000-2005 and licensed

# under the GNU General Public License version 2 or, at your option, any later

# version. For a full list including contact information of contributors,

# maintainers, and copyright holders, see the CONTRIBUTORS file.

# Original Copyright Notice from SQL-Ledger 2.6.17 (before the fork):

# Author: DWS Systems Inc.

# Web: http://www.sql-ledger.org

# Contributors:

#======================================================================

# This file has undergone whitespace cleanup.

#======================================================================

# backend code for reports

#======================================================================

package RP;

sub inventory_activity {

    my ( $self, $myconfig, $form ) = @_;

    ( $form->{fromdate}, $form->{todate} ) =

      $form->from_to( $form->{fromyear}, $form->{frommonth}, $form->{interval} )

      if $form->{fromyear} && $form->{frommonth};

    my $dbh = $form->{dbh};

    unless ( $form->{sort_col} ) {

        $form->{sort_col} = 'partnumber';

}

    my $where = '';

    if ( $form->{fromdate} ) {

$where .=

"AND coalesce(ar.duedate, ap.duedate) >= "

          . $dbh->quote( $form->{fromdate} );

}

    if ( $form->{todate} ) {

$where .=

"AND coalesce(ar.duedate, ap.duedate) < "

          . $dbh->quote( $form->{todate} ) . " ";

}

    if ( $form->{partnumber} ) {

$where .=

          qq| AND p.partnumber ILIKE |

          . $dbh->quote( '%' . "$form->{partnumber}%" );

}

    if ( $form->{description} ) {

$where .=

          q| AND p.description ILIKE |

          . $dbh->quote( '%' . "$form->{description}%" );

}

    $where =~ s/^\s?AND/WHERE/;

    my $query = qq|

           SELECT min(p.description) AS description, 

                  min(p.partnumber) AS partnumber, sum(

                  CASE WHEN i.qty > 0 THEN i.qty ELSE 0 END) AS sold, 

                  sum (CASE WHEN i.qty > 0 

                            THEN i.sellprice * i.qty 

                            ELSE 0 END) AS revenue, 

                  sum(CASE WHEN i.qty < 0 THEN i.qty * -1 ELSE 0 END) 

                  AS received, sum(CASE WHEN i.qty < 0 

                                        THEN i.sellprice * i.qty * -1

                                        ELSE 0 END) as expenses, 

                  min(p.id) as id

FROM invoice i

             JOIN parts p ON (i.parts_id = p.id)

        LEFT JOIN ar ON (ar.id = i.trans_id)

        LEFT JOIN ap ON (ap.id = i.trans_id)

$where

GROUP BY i.parts_id

         ORDER BY $form->{sort_col}|;

    my $sth = $dbh->prepare($query) || $form->dberror($query);

    $sth->execute() || $form->dberror($query);

    @cols = qw(description sold revenue partnumber received expense);

    while ( $ref = $sth->fetchrow_hashref(NAME_lc) ) {

        $form->db_parse_numeric(sth=>$sth, hashref=>$ref);

        $ref->{net_income} = $ref->{revenue} - $ref->{expense};

        map { $ref->{$_} =~ s/^\s*// } @cols;

        map { $ref->{$_} =~ s/\s*$// } @cols;

        push @{ $form->{TB} }, $ref;

}

    $sth->finish;

    $dbh->commit;

}

sub yearend_statement {

    my ( $self, $myconfig, $form ) = @_;

    my $dbh = $form->{dbh};

# if todate < existing yearends, delete GL and yearends

    my $query = qq|SELECT trans_id FROM yearend WHERE transdate >= ?|;

    my $sth   = $dbh->prepare($query);

    $sth->execute( $form->{todate} ) || $form->dberror($query);

    my @trans_id = ();

    my $id;

    while ( ($id) = $sth->fetchrow_array ) {

        push @trans_id, $id;

}

    $sth->finish;

    $query = qq|DELETE FROM gl WHERE id = ?|;

    $sth = $dbh->prepare($query) || $form->dberror($query);

    $query = qq|DELETE FROM acc_trans WHERE trans_id = ?|;

    my $ath = $dbh->prepare($query) || $form->dberror($query);

    foreach $id (@trans_id) {

        $sth->execute($id);

        $ath->execute($id);

        $sth->finish;

        $ath->finish;

}

    my $last_period = 0;

    my @categories  = qw(I E);

    my $category;

    $form->{decimalplaces} *= 1;

    &get_accounts( $dbh, 0, $form->{fromdate}, $form->{todate}, $form,

\@categories );

    $dbh->commit;

# now we got $form->{I}{accno}{ }

# and $form->{E}{accno}{ }

    my %account = (

'I' => {

            'label'  => 'income',

            'labels' => 'income',

            'ml'     => 1

'E' => {

            'label'  => 'expense',

            'labels' => 'expenses',

            'ml'     => -1

}

);

    foreach $category (@categories) {

        foreach $key ( sort keys %{ $form->{$category} } ) {

            if ( $form->{$category}{$key}{charttype} eq 'A' ) {

                $form->{"total_$account{$category}{labels}_this_period"} +=

                  $form->{$category}{$key}{this} * $account{$category}{ml};

}

# totals for income and expenses

    $form->{total_income_this_period} =

      $form->round_amount( $form->{total_income_this_period},

        $form->{decimalplaces} );

    $form->{total_expenses_this_period} =

      $form->round_amount( $form->{total_expenses_this_period},

        $form->{decimalplaces} );

# total for income/loss

    $form->{total_this_period} =

      $form->{total_income_this_period} - $form->{total_expenses_this_period};

}

sub income_statement {

    my ( $self, $myconfig, $form ) = @_;

    my $dbh = $form->{dbh};

    my $last_period = 0;

    my @categories  = qw(I E);

    my $category;

    $form->{decimalplaces} *= 1;

    if ( !( $form->{fromdate} || $form->{todate} ) ) {

        if ( $form->{fromyear} && $form->{frommonth} ) {

            ( $form->{fromdate}, $form->{todate} ) =

              $form->from_to( $form->{fromyear}, $form->{frommonth},

                $form->{interval} );

}

    &get_accounts( $dbh, $last_period, $form->{fromdate}, $form->{todate},

        $form, \@categories, 1 );

    if ( !( $form->{comparefromdate} || $form->{comparetodate} ) ) {

        if ( $form->{compareyear} && $form->{comparemonth} ) {

            ( $form->{comparefromdate}, $form->{comparetodate} ) =

              $form->from_to( $form->{compareyear}, $form->{comparemonth},

                $form->{interval} );

}

# if there are any compare dates

    if ( $form->{comparefromdate} || $form->{comparetodate} ) {

        $last_period = 1;

&get_accounts(

            $dbh, $last_period,

            $form->{comparefromdate},

            $form->{comparetodate},

            $form, \@categories, 1

);

}

    $dbh->commit;

# now we got $form->{I}{accno}{ }

# and $form->{E}{accno}{ }

    my %account = (

'I' => {

            'label'  => 'income',

            'labels' => 'income',

            'ml'     => 1

'E' => {

            'label'  => 'expense',

            'labels' => 'expenses',

            'ml'     => -1

}

);

    my $str;

    foreach $category (@categories) {

        foreach $key ( sort keys %{ $form->{$category} } ) {

# push description onto array

## $str = ( $form->{l_heading} ) ? $form->{padding} : "";

            $str = "";

            if ( $form->{$category}{$key}{charttype} eq "A" ) {

$str .=

                  ( $form->{l_accno} )

                  ? "$form->{$category}{$key}{accno} - $form->{$category}{$key}{description}"

                  : "$form->{$category}{$key}{description}";

                $str = {account => $form->{$category}{$key}{accno}, text => $str };

                $str->{gifi_account} = 1 if $form->{accounttype} eq 'gifi';

}

            if ( $form->{$category}{$key}{charttype} eq "H" ) {

                if (   $account{$category}{subtotal}

                    && $form->{l_subtotal} )

{

                    $dash = "- ";

push(

                        @{ $form->{"$account{$category}{label}_account"} }, {

                            text => "$account{$category}{subdescription}",

                            subtotal => 1,