ikiwiki/legacy - old ikiwiki engine, based on git.ikiwiki.info

summary refs log tree commit diff

path: root/javascript

diff options

Diffstat (limited to 'javascript')

m---------

0

1 files changed, 0 insertions, 0 deletions

li>

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

# connect to database

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

    my %audittrail = (

        tablename => 'gl',

        reference => $form->{reference},

        formname  => 'transaction',

        action    => 'deleted',

        id        => $form->{id}

);

    $form->audittrail( $dbh, "", \%audittrail );

    my $id    = $dbh->quote( $form->{id} );

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

    $dbh->do($query) || $form->dberror($query);

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

    $dbh->do($query) || $form->dberror($query);

# commit and redirect

    my $rc = $dbh->commit;

$rc;

}

sub post_transaction {

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

    my $null;

    my $project_id;

    my $department_id;

    my $i;

# connect to database, turn off AutoCommit

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

    my $query;

    my $sth;

    my $id = $dbh->quote( $form->{id} );

    if ( $form->{id} ) {

        $query = qq|SELECT id FROM gl WHERE id = $id|;

        ( $form->{id} ) = $dbh->selectrow_array($query);

        if ( $form->{id} ) {

# delete individual transactions

            $query = qq|

                DELETE FROM acc_trans WHERE trans_id = $id|;

            $dbh->do($query) || $form->dberror($query);

}

}

    if ( !$form->{id} ) {

        my $uid = localtime;

        $uid .= "$$";

        $query = qq|

        INSERT INTO gl (reference, employee_id)

             VALUES ('$uid', (SELECT id FROM employee

WHERE login = ?))|;

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

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

        $query = qq|

SELECT id

FROM gl

             WHERE reference = '$uid'|;

        ( $form->{id} ) = $dbh->selectrow_array($query);

}

    ( $null, $department_id ) = split /--/, $form->{department};

    $department_id *= 1;

    $form->{reference} = $form->update_defaults( $myconfig, 'glnumber', $dbh )

      unless $form->{reference};

    $form->{reference} ||= $form->{id};

    $query = qq|

UPDATE gl

           SET reference = | . $dbh->quote( $form->{reference} ) . qq|,

              description = | . $dbh->quote( $form->{description} ) . qq|,

              notes = | . $dbh->quote( $form->{notes} ) . qq|,

transdate = ?,

department_id = ?

WHERE id = ?|;

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

    $sth->execute( $form->{transdate}, $department_id, $form->{id} )

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

    my $amount = 0;

    my $posted = 0;

    my $debit;

    my $credit;

# insert acc_trans transactions

    for $i ( 1 .. $form->{rowcount} ) {

        $debit  = $form->parse_amount( $myconfig, $form->{"debit_$i"} );

        $credit = $form->parse_amount( $myconfig, $form->{"credit_$i"} );

# extract accno

        ($accno) = split( /--/, $form->{"accno_$i"} );

        if ($credit) {

            $amount = $credit;

            $posted = 0;

}

        if ($debit) {

            $amount = $debit * -1;

            $posted = 0;

}

# add the record

        if ( !$posted ) {

            ( $null, $project_id ) = split /--/, $form->{"projectnumber_$i"};

            $project_id ||= undef;

            $query = qq|

INSERT INTO acc_trans

                            (trans_id, chart_id, amount, 

                            transdate, source, project_id, 

                            fx_transaction, memo, cleared)

VALUES (?, (SELECT id

FROM chart

WHERE accno = ?),

?, ?, ?, ?, ?, ?, ?)|;

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

            $sth->execute(

                $form->{id},                  $accno,

                $amount,                      $form->{transdate},

                $form->{"source_$i"},         $project_id,

                ($form->{"fx_transaction_$i"} || 0), $form->{"memo_$i"},

                ($form->{"cleared_$i"} || 0)

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

            $posted = 1;

}

}

    my %audittrail = (

        tablename => 'gl',

        reference => $form->{reference},

        formname  => 'transaction',

        action    => 'posted',

        id        => $form->{id}

);

    $form->audittrail( $dbh, "", \%audittrail );

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

# commit and redirect

    my $rc = $dbh->commit;

$rc;

}

sub all_transactions {

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

# connect to database

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

    my $query;

    my $sth;

    my $var;

    my $null;

    my ( $glwhere, $arwhere, $apwhere ) = ( "1 = 1", "1 = 1", "1 = 1" );

    if ( $form->{reference} ne "" ) {

        $var = $dbh->quote( $form->like( lc $form->{reference} ) );

        $glwhere .= " AND lower(g.reference) LIKE $var";

        $arwhere .= " AND lower(a.invnumber) LIKE $var";

        $apwhere .= " AND lower(a.invnumber) LIKE $var";

}

    if ( $form->{department} ne "" ) {

        ( $null, $var ) = split /--/, $form->{department};

        $var = $dbh->quote($var);

        $glwhere .= " AND g.department_id = $var";

        $arwhere .= " AND a.department_id = $var";

        $apwhere .= " AND a.department_id = $var";

}

    if ( $form->{source} ne "" ) {

        $var = $dbh->quote( $form->like( lc $form->{source} ) );

        $glwhere .= " AND lower(ac.source) LIKE $var";

        $arwhere .= " AND lower(ac.source) LIKE $var";

        $apwhere .= " AND lower(ac.source) LIKE $var";

}

    if ( $form->{memo} ne "" ) {

        $var = $form->like( lc $form->{memo} );

        $glwhere .= " AND lower(ac.memo) LIKE $var";

        $arwhere .= " AND lower(ac.memo) LIKE $var";

        $apwhere .= " AND lower(ac.memo) LIKE $var";

}

    ( $form->{datefrom}, $form->{dateto} ) =

      $form->from_to( $form->{year}, $form->{month}, $form->{interval} )

      if $form->{year} && $form->{month};

    if ( $form->{datefrom} ) {

        $glwhere .= " AND ac.transdate >= " . $dbh->quote( $form->{datefrom} );

        $arwhere .= " AND ac.transdate >= " . $dbh->quote( $form->{datefrom} );

        $apwhere .= " AND ac.transdate >= " . $dbh->quote( $form->{datefrom} );

}

    if ( $form->{dateto} ) {

        $glwhere .= " AND ac.transdate <= " . $dbh->quote( $form->{dateto} );

        $arwhere .= " AND ac.transdate <= " . $dbh->quote( $form->{dateto} );

        $apwhere .= " AND ac.transdate <= " . $dbh->quote( $form->{dateto} );

}

    if ( $form->{amountfrom} ) {

$glwhere .=

          " AND abs(ac.amount) >= " . $dbh->quote( $form->{amountfrom} );

$arwhere .=

          " AND abs(ac.amount) >= " . $dbh->quote( $form->{amountfrom} );

$apwhere .=

          " AND abs(ac.amount) >= " . $dbh->quote( $form->{amountfrom} );

}

    if ( $form->{amountto} ) {

$glwhere .=

          " AND abs(ac.amount) <= " . $dbh->quote( $form->{amountto} );

$arwhere .=

          " AND abs(ac.amount) <= " . $dbh->quote( $form->{amountto} );

$apwhere .=

          " AND abs(ac.amount) <= " . $dbh->quote( $form->{amountto} );

}

    if ( $form->{description} ) {

        $var = $dbh->quote( $form->like( lc $form->{description} ) );

        $glwhere .= " AND lower(g.description) LIKE $var";

        $arwhere .= " AND (lower(ct.name) LIKE $var

OR lower(ac.memo) LIKE $var

OR a.id IN (SELECT DISTINCT trans_id

FROM invoice

                    WHERE lower(description) LIKE $var))";

        $apwhere .= " AND (lower(ct.name) LIKE $var

OR lower(ac.memo) LIKE $var

OR a.id IN (SELECT DISTINCT trans_id

FROM invoice

                    WHERE lower(description) LIKE $var))";

}

    if ( $form->{notes} ) {

        $var = $dbh->quote( $form->like( lc $form->{notes} ) );

        $glwhere .= " AND lower(g.notes) LIKE $var";

        $arwhere .= " AND lower(a.notes) LIKE $var";

        $apwhere .= " AND lower(a.notes) LIKE $var";

}

    if ( $form->{accno} ) {

        $var = $dbh->quote( $form->{accno} );

        $glwhere .= " AND c.accno = $var";

        $arwhere .= " AND c.accno = $var";

        $apwhere .= " AND c.accno = $var";

}

    if ( $form->{gifi_accno} ) {

        $var = $dbh->quote( $form->{gifiaccno} );

        $glwhere .= " AND c.gifi_accno = $var";

        $arwhere .= " AND c.gifi_accno = $var";

        $apwhere .= " AND c.gifi_accno = $var";

}

    if ( $form->{category} ne 'X' ) {

        $var = $dbh->quote( $form->{gifiaccno} );

        $glwhere .= " AND c.category = $var";

        $arwhere .= " AND c.category = $var";

        $apwhere .= " AND c.category = $var";

}

    if ( $form->{accno} ) {

        my $accno = $dbh->quote( $form->{accno} );

# get category for account

        $query = qq|SELECT category, link, contra, description

FROM chart

                     WHERE accno = $accno|;

(

            $form->{category}, $form->{link}, $form->{contra},

            $form->{account_description}

        ) = $dbh->selectrow_array($query);

        if ( $form->{datefrom} ) {

            $query = qq|

                SELECT SUM(ac.amount)

FROM acc_trans ac

                  JOIN chart c ON (ac.chart_id = c.id)

                 WHERE c.accno = $accno

                       AND ac.transdate < date | . $dbh->quote( $form->{datefrom} );

            ( $form->{balance} ) = $dbh->selectrow_array($query);

}

}

    if ( $form->{gifi_accno} ) {

        my $gifi = $dbh->quote( $form->{gifi_accno} );

# get category for account

        $query = qq|SELECT c.category, c.link, c.contra, g.description

FROM chart c

                 LEFT JOIN gifi g ON (g.accno = c.gifi_accno)

                     WHERE c.gifi_accno = $gifi|;

(

            $form->{category}, $form->{link}, $form->{contra},

            $form->{gifi_account_description}

        ) = $dbh->selectrow_array($query);

        if ( $form->{datefrom} ) {

            $query = qq|

                SELECT SUM(ac.amount)

FROM acc_trans ac

                  JOIN chart c ON (ac.chart_id = c.id)

                 WHERE c.gifi_accno = $gifi

                       AND ac.transdate < date | . $dbh->quote( $form->{datefrom} );

            ( $form->{balance} ) = $dbh->selectrow_array($query);

}

}

    my $false = 'FALSE';

    my %ordinal = (

        id          => 1,

        reference   => 4,

        description => 5,

        transdate   => 6,

        source      => 7,

        accno       => 9,

        department  => 15,

memo => 16

);

    my @a = ( id, transdate, reference, source, description, accno );

    my $sortorder = $form->sort_order( \@a, \%ordinal );

    my $query = qq|SELECT g.id, 'gl' AS type, $false AS invoice, g.reference,

                          g.description, ac.transdate, ac.source,

                          ac.amount, c.accno, c.gifi_accno, g.notes, c.link,

                          '' AS till, ac.cleared, d.description AS department,

ac.memo

FROM gl AS g

                     JOIN acc_trans ac ON (g.id = ac.trans_id)

                     JOIN chart c ON (ac.chart_id = c.id)

                LEFT JOIN department d ON (d.id = g.department_id)

WHERE $glwhere

UNION ALL

                   SELECT a.id, 'ar' AS type, a.invoice, a.invnumber,

                          ct.name, ac.transdate, ac.source,

                          ac.amount, c.accno, c.gifi_accno, a.notes, c.link,

                          a.till, ac.cleared, d.description AS department,

ac.memo

FROM ar a

                     JOIN acc_trans ac ON (a.id = ac.trans_id)

                     JOIN chart c ON (ac.chart_id = c.id)

                     JOIN customer ct ON (a.customer_id = ct.id)

                LEFT JOIN department d ON (d.id = a.department_id)

WHERE $arwhere

UNION ALL

                   SELECT a.id, 'ap' AS type, a.invoice, a.invnumber,

                          ct.name, ac.transdate, ac.source,

                          ac.amount, c.accno, c.gifi_accno, a.notes, c.link,

                          a.till, ac.cleared, d.description AS department,

ac.memo

FROM ap a

                     JOIN acc_trans ac ON (a.id = ac.trans_id)

                     JOIN chart c ON (ac.chart_id = c.id)

                     JOIN vendor ct ON (a.vendor_id = ct.id)

                LEFT JOIN department d ON (d.id = a.department_id)

WHERE $apwhere

ORDER BY $sortorder|;

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

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

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

# gl

        if ( $ref->{type} eq "gl" ) {

            $ref->{module} = "gl";

}

# ap

        if ( $ref->{type} eq "ap" ) {

            if ( $ref->{invoice} ) {

                $ref->{module} = "ir";

}

else {

                $ref->{module} = "ap";

}

}

# ar

        if ( $ref->{type} eq "ar" ) {

            if ( $ref->{invoice} ) {

                $ref->{module} = ( $ref->{till} ) ? "ps" : "is";

}

else {

                $ref->{module} = "ar";

}

}

        if ( $ref->{amount} < 0 ) {

            $ref->{debit}  = $ref->{amount} * -1;