#=====================================================================
# LedgerSMB
# Small Medium Business Accounting software
# http://www.ledgersmb.org/
#
# Copyright (C) 2006
# 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):
# Copyright (C) 2002
#
#  Author: DWS Systems Inc.
#     Web: http://www.sql-ledger.org
#
#  Contributors:
#
#======================================================================
#
# This file has undergone whitespace cleanup.
#
#======================================================================
#
# Account reconciliation routines
#
#======================================================================

package RC;

sub getposlines {
    my ( $self, $myconfig, $form ) = @_;
    %pos_config  = %{ $form->{pos_config} };
    %pos_sources = %{ $form->{pos_sources} };
    my $sources = '';
    foreach $key ( keys %pos_sources ) {
        $sources .= ", '$key'";
    }
    $sources =~ s/^,\s*//;
    my $dbh = $form->{dbh};

    my $query = qq| 
		  SELECT sum(amount) AS amount, memo FROM acc_trans
		   WHERE chart_id = (SELECT id FROM chart 
		                    WHERE accno = ?)
		         AND transdate = date 'NOW' AND cleared IS NOT TRUE
		GROUP BY memo|;
    my $sth = $dbh->prepare($query);
    $sth->execute( $pos_config{till_accno} ) || $form->dberror($query);
    while ( my $ref = $sth->fetchrow_hashref(NAME_lc) ) {
        $form->db_parse_numeric(sth=>$sth, hashref=>$ref);
        push @{ $form->{TB} }, $ref;
    }
    $sth->finish;
    my $query = qq| 
		SELECT sum(amount) AS sum FROM acc_trans
		 WHERE chart_id =  (SELECT id FROM chart WHERE accno = ?)
	  AND transdate = date 'NOW'
          AND cleared IS NOT TRUE|;
    my $sth = $dbh->prepare($query);
    $sth->execute( $pos_config{till_accno} ) || $form->dberror($query);
    my $ref = $sth->fetchrow_hashref(NAME_lc);
    $form->db_parse_numeric(sth=>$sth, hashref=>$ref);
    $form->{sum} = $ref->{sum};
    $sth->finish;
}

sub clear_till {
    my ( $self, $myconfig, $form ) = @_;
    %pos_config  = %{ $form->{pos_config} };
    %pos_sources = %{ $form->{pos_sources} };
    my $sources = '';
    foreach $key ( keys %pos_sources ) {
        $sources .= ", '$key'";
    }
    $sources =~ s/^,\s//;
    my $dbh   = $form->{dbh};
    my $query = qq| 
		UPDATE acc_trans
		SET cleared = TRUE
		WHERE chart_id = 
			(SELECT id FROM chart WHERE accno = ?)
		  AND transdate = date 'NOW'|;

    my $sth = $dbh->prepare($query);
    $sth->execute( $pos_config{till_accno} ) || $form->dberror($query);
}

sub getbalance {
    my ( $self, $myconfig, $form ) = @_;
    my $dbh = $form->{dbh};

    my $query = qq|
		SELECT sum(amount) AS balance
		  FROM acc_trans
		 WHERE chart_id = (SELECT id FROM chart WHERE accno = ?)|;

    my $sth = $dbh->prepare($query);
    $sth->execute( $form->{accno} ) || $form->dberror($query);
    my $ref = $sth->fetchrow_hashref(NAME_lc);
    $form->db_parse_numeric(sth=>$sth, hashref=>$ref);
    $form->{balance} = $ref->{balance};
}

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

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

    my $query = qq|
		SELECT accno, description
		  FROM chart
		 WHERE link LIKE '%_paid%' 
		       AND (category = 'A' OR category = 'L')
		 ORDER BY accno|;
    my $sth = $dbh->prepare($query);
    $sth->execute || $form->dberror($query);

    while ( my $ref = $sth->fetchrow_hashref(NAME_lc) ) {
        push @{ $form->{PR} }, $ref;
    }
    $sth->finish;

    $form->all_years( $myconfig, $dbh );

}

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

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

    my $query;
    my $sth;

    $query = qq|SELECT category FROM chart WHERE accno = ?|;
    $sth   = $dbh->prepare($query);
    $sth->execute( $form->{accno} );
    ( $form->{category} ) = $sth->fetchrow_array();

    my $cleared;

    ( $form->{fromdate}, $form->{todate} ) =
      $form->from_to( $form->{year}, $form->{month}, $form->{interval} )
      if $form->{year} && $form->{month};

    my $transdate =
      qq| AND ac.transdate < date | . $dbh->quote( $form->{fromdate} );

    if ( !$form->{fromdate} ) {
        $cleared   = qq| AND ac.cleared = '1'|;
        $transdate = "";
    }

    # get beginning balance
    $query = qq|
		SELECT sum(ac.amount)
		  FROM acc_trans ac
		  JOIN chart ch ON (ch.id = ac.chart_id)
		 WHERE ch.accno = ? $transdate $cleared |;
    $sth = $dbh->prepare($query);
    $sth->execute( $form->{accno} );
    my @balancelist = $sth->fetchrow_array();
    $form->db_parse_numeric(sth=>$sth, arrayref=>\@balancelist);
    ( $form->{beginningbalance} ) = @balancelist;

    $query = qq|
		SELECT sum(ac.amount)
		  FROM acc_trans ac
		  JOIN chart ch ON (ch.id = ac.chart_id)
		 WHERE ch.accno = ? AND ac.fx_transaction = '1'
		       $transdate $cleared|;

    $sth = $dbh->prepare($query);
    $sth->execute( $form->{accno} );
    @balancelist = $sth->fetchrow_array();
    $form->db_parse_numeric(sth=>$sth, arrayref=>\@balancelist);
    ( $form->{fx_balance} ) = @balancelist;

    $transdate = "";
    if ( $form->{todate} ) {
        $transdate =
          qq| AND ac.transdate <= date | . $dbh->quote( $form->{todate} );
    }

    # get statement balance
    $query = qq|
		SELECT sum(ac.amount)
		  FROM acc_trans ac
		  JOIN chart ch ON (ch.id = ac.chart_id)
		 WHERE ch.accno = ? $transdate|;

    $sth = $dbh->prepare($query);
    $sth->execute( $form->{accno} );
    @balancelist = $sth->fetchrow_array();
    $form->db_parse_numeric(sth=>$sth, arrayref=>\@balancelist);
    ( $form->{endingbalance} ) = @balancelist;

    # fx balance
    $query = qq|
		SELECT sum(ac.amount)
		  FROM acc_trans ac
		  JOIN chart ch ON (ch.id = ac.chart_id)
		 WHERE ch.accno = ? AND ac.fx_transaction = '1' $transdate |;

    $sth = $dbh->prepare($query);
    $sth->execute( $form->{accno} );
    @balancelist = $sth->fetchrow_array();
    $form->db_parse_numeric(sth=>$sth, arrayref=>\@balancelist);
    ( $form->{fx_endingbalance} ) = @balancelist;

    $cleared = qq| AND ac.cleared = '0'| unless $form->{fromdate};

    if ( $form->{report} ) {
        $cleared = qq| AND NOT (ac.cleared = '0' OR ac.cleared = '1')|;
        if ( $form->{cleared} ) {
            $cleared = qq| AND ac.cleared = '1'|;
        }
        if ( $form->{outstanding} ) {
            $cleared =
              ( $form->{cleared} )
              ? ""
              : qq| AND ac.cleared = '0'|;
        }
        if ( !$form->{fromdate} ) {
            $form->{beginningbalance} = 0;
            $form->{fx_balance}       = 0;
        }
    }

    my $fx_transaction;
    if ( $form->{fx_transaction} ) {
        $fx_transaction = qq|
			AND NOT (ac.chart_id IN 
				(SELECT value::int FROM defaults
					WHERE setting_key = 'fxgain_accno_id'
				UNION
				SELECT value::int FROM defaults
					WHERE setting_key = 'fxloss_accno_id'))|;
    }
    else {
        $fx_transaction = qq|
			AND ac.fx_transaction = '0'|;
    }

    if ( $form->{summary} ) {
        $query = qq|
			SELECT ac.transdate, ac.source, 
			       sum(ac.amount) AS amount, ac.cleared
			  FROM acc_trans ac
			  JOIN chart ch ON (ac.chart_id = ch.id)
			 WHERE ch.accno = | . $dbh->quote( $form->{accno} ) . qq|
			       AND ac.amount >= 0 $fx_transaction $cleared|;
        $query .= " AND ac.transdate >= " . $dbh->quote( $form->{fromdate} )
          if $form->{fromdate};
        $query .= " AND ac.transdate <= " . $dbh->quote( $form->{todate} )
          if $form->{todate};
        $query .= " GROUP BY ac.source, ac.transdate, ac.cleared";
        $query .= qq|
			UNION ALL
			SELECT ac.transdate, ac.source, 
			       sum(ac.amount) AS amount, ac.cleared
			  FROM acc_trans ac
			  JOIN chart ch ON (ac.chart_id = ch.id)
			 WHERE ch.accno = | . $dbh->quote( $form->{accno} ) . qq|
			       AND ac.amount < 0 $fx_transaction $cleared|;

        $query .= " AND ac.transdate >= " . $dbh->quote( $form->{fromdate} )
          if $form->{fromdate};
        $query .= " AND ac.transdate <= " . $dbh->quote( $form->{todate} )
          if $form->{todate};
        $query .= " GROUP BY ac.source, ac.transdate, ac.cleared";

        $query .= " ORDER BY 1,2";

    }
    else {

        $query = qq|
			SELECT ac.transdate, ac.source, ac.fx_transaction,
			       ac.amount, ac.cleared, g.id, g.description
			  FROM acc_trans ac
			  JOIN chart ch ON (ac.chart_id = ch.id)
			  JOIN gl g ON (g.id = ac.trans_id)
			 WHERE ch.accno = | . $dbh->quote( $form->{accno} ) . qq|
			       $fx_transaction $cleared|;
        $query .= " AND ac.transdate >= " . $dbh->quote( $form->{fromdate} )
          if $form->{fromdate};
        $query .= " AND ac.transdate <= " . $dbh->quote( $form->{todate} )
          if $form->{todate};
        $query .= qq|
			UNION ALL
			SELECT ac.transdate, ac.source, ac.fx_transaction,
			       ac.amount, ac.cleared, a.id, n.name
			  FROM acc_trans ac
			  JOIN chart ch ON (ac.chart_id = ch.id)
			  JOIN ar a ON (a.id = ac.trans_id)
			  JOIN customer n USING (entity_id)
			 WHERE ch.accno = | . $dbh->quote( $form->{accno} ) . qq|
			       $fx_transaction $cleared|;
        $query .= " AND ac.transdate >= " . $dbh->quote( $form->{fromdate} )
          if $form->{fromdate};
        $query .= " AND ac.transdate <= " . $dbh->quote( $form->{todate} )
          if $form->{todate};
        $query .= qq|
			UNION ALL
			SELECT ac.transdate, ac.source, ac.fx_transaction,
			       ac.amount, ac.cleared, a.id, n.name
			  FROM acc_trans ac
			  JOIN chart ch ON (ac.chart_id = ch.id)
			  JOIN ap a ON (a.id = ac.trans_id)
			  JOIN vendor n ON (n.id = a.vendor_id)
			 WHERE ch.accno = | . $dbh->quote( $form->{accno} ) . qq|
			       $fx_transaction $cleared|;
        $query .= " AND ac.transdate >= " . $dbh->quote( $form->{fromdate} )
          if $form->{fromdate};
        $query .= " AND ac.transdate <= " . $dbh->quote( $form->{todate} )
          if $form->{todate};

        $query .= " ORDER BY 1,2,3";
    }

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

    my $dr;
    my $cr;

    if ( $form->{summary} ) {
        $query = qq|
			SELECT c.name
			  FROM customer c
			  JOIN ar a using (entity_id)
			  JOIN acc_trans ac ON (a.id = ac.trans_id)
			  JOIN chart ch ON (ac.chart_id = ch.id)
			 WHERE ac.transdate = ?
			       AND ch.accno = | . $dbh->quote( $form->{accno} ) . qq|
			       AND (ac.source = ? OR ac.source IS NULL)
			       AND ac.amount >= 0 $cleared
			UNION
			SELECT v.name
			  FROM vendor v
			  JOIN ap a ON (v.id = a.vendor_id)
			  JOIN acc_trans ac ON (a.id = ac.trans_id)
			  JOIN chart ch ON (ac.chart_id = ch.id)
			 WHERE ac.transdate = ?
			       AND ch.accno = | . $dbh->quote( $form->{accno} ) . qq|
			       AND (ac.source = ? OR ac.source IS NULL)
			       AND ac.amount > 0 $cleared
			UNION
			SELECT g.description
			  FROM gl g
			  JOIN acc_trans ac ON (g.id = ac.trans_id)
			  JOIN chart ch ON (ac.chart_id = ch.id)
			 WHERE ac.transdate = ?
			       AND ch.accno = | . $dbh->quote( $form->{accno} ) . qq|
			       AND (ac.source = ? OR ac.source IS NULL)
			       AND ac.amount >= 0 $cleared|;

        $query .= " ORDER BY 1";
        $dr = $dbh->prepare($query);

        $query = qq|
		SELECT c.name
		  FROM customer c
		  JOIN ar a USING (entity_id)
		  JOIN acc_trans ac ON (a.id = ac.trans_id)
		  JOIN chart ch ON (ac.chart_id = ch.id)
		 WHERE ac.transdate = ?
		       AND ch.accno = | . $dbh->quote( $form->{accno} ) . qq|
		       AND (ac.source = ? OR ac.source IS NULL)
		       AND ac.amount < 0 $cleared
		UNION
		SELECT v.name
		  FROM vendor v
		  JOIN ap a ON (v.id = a.vendor_id)
		  JOIN acc_trans ac ON (a.id = ac.trans_id)
		  JOIN chart ch ON (ac.chart_id = ch.id)
		 WHERE ac.transdate = ?
		       AND ch.accno = | . $dbh->quote( $form->{accno} ) . qq|
		       AND (ac.source = ? OR ac.source IS NULL) 
		       AND ac.amount < 0 $cleared
		UNION
		SELECT g.description
		  FROM gl g
		  JOIN acc_trans ac ON (g.id = ac.trans_id)
		  JOIN chart ch ON (ac.chart_id = ch.id)
		 WHERE ac.transdate = ?
		       AND ch.accno = | . $dbh->quote( $form->{accno} ) . qq|
		       AND (ac.source = ? OR ac.source IS NULL)
		       AND ac.amount < 0 $cleared|;

        $query .= " ORDER BY 1";
        $cr = $dbh->prepare($query);
    }

    my $name;
    my $ref;

    while ( $ref = $sth->fetchrow_hashref(NAME_lc) ) {
        $form->db_parse_numeric(sth=>$sth, hashref=>$ref);

        if ( $form->{summary} ) {

            if ( $ref->{amount} > 0 ) {
                $dr->execute(
                    $ref->{transdate}, $ref->{source},    $ref->{transdate},
                    $ref->{source},    $ref->{transdate}, $ref->{source}
                );
                $ref->{oldcleared} = $ref->{cleared};
                $ref->{name}       = ();

                while ( ($name) = $dr->fetchrow_array ) {
                    push @{ $ref->{name} }, $name;
                }
                $dr->finish;
            }
            else {

                $cr->execute(
                    $ref->{transdate}, $ref->{source},    $ref->{transdate},
                    $ref->{source},    $ref->{transdate}, $ref->{source}
                );
                $ref->{oldcleared} = $ref->{cleared};
                $ref->{name}       = ();
                while ( ($name) = $cr->fetchrow_array ) {
                    push @{ $ref->{name} }, $name;
                }
                $cr->finish;

            }

        }
        else {
            push @{ $ref->{name} }, $ref->{description};
        }

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

    }
    $sth->finish;

    $dbh->commit;

}

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

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

    my $query = qq|SELECT id FROM chart
                 WHERE accno = '$form->{accno}'|;
    my ($chart_id) = $dbh->selectrow_array($query);
    $chart_id *= 1;

    $query = qq|
		SELECT trans_id FROM acc_trans
		 WHERE (source = ? OR source IS NULL) AND transdate = ?
		       AND cleared = '0' 
		       AND chart_id = | . $dbh->quote($chart_id);
    my $sth = $dbh->prepare($query) || $form->dberror($query);

    my $i;
    my $trans_id;

    $query = qq|
		UPDATE acc_trans 
		   SET cleared = '1'
		 WHERE cleared = '0' AND trans_id = ? AND transdate = ?
		       AND chart_id = | . $dbh->quote($chart_id);
    my $tth = $dbh->prepare($query) || $form->dberror($query);

    # clear flags
    for $i ( 1 .. $form->{rowcount} ) {
        if ( $form->{"cleared_$i"} && !$form->{"oldcleared_$i"} ) {
            if ( $form->{summary} ) {
                $sth->execute( $form->{"source_$i"}, $form->{"transdate_$i"} )
                  || $form->dberror;

                while ( ($trans_id) = $sth->fetchrow_array ) {
                    $tth->execute( $trans_id, $form->{"transdate_$i"} )
                      || $form->dberror;
                    $tth->finish;
                }
                $sth->finish;

            }
            else {

                $tth->execute( $form->{"id_$i"}, $form->{"transdate_$i"} )
                  || $form->dberror;
                $tth->finish;
            }
        }
    }

    $dbh->commit;

}

1;