Merge branch 'master' of ssh://git.kitenet.net/srv/git/ikiwiki.info - ikiwiki-upstream - Unnamed repository; edit this file to name it for gitweb.

diff options

author	Joey Hess <joey@kodama.kitenet.net>	2008-03-06 12:05:36 -0500
committer	Joey Hess <joey@kodama.kitenet.net>	2008-03-06 12:05:36 -0500
commit	0e7896690b8dbeb7c74fc0ce1f954159861fc8b3 (patch)
tree	415a5b207c250d4c7905f538bd9982f1bf34fd18 /doc/plugins/contrib/gallery
parent	4401e5b9edcc99766ec0b0bdfd3125b3ddc94e6a (diff)
parent	91ea6629433fb4e21db4b64ef47a8990e80a2533 (diff)

Merge branch 'master' of ssh://git.kitenet.net/srv/git/ikiwiki.info

Diffstat (limited to 'doc/plugins/contrib/gallery')

0 files changed, 0 insertions, 0 deletions

span class="hl kwc">my ($self, $myconfig, $form) = @_;

# connect to database

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

    my $query;

    my $sth;

    my $null;

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

    $form->{department_id} *= 1;

    my $ml = 1;

    my $table = 'ar';

    my $buysell = 'buy';

    my $ARAP = 'AR';

    my $invnumber = "sinumber";

    my $keepcleared;

    if ($form->{vc} eq 'vendor') {

        $table = 'ap';

        $buysell = 'sell';

        $ARAP = 'AP';

        $ml = -1;

        $invnumber = "vinumber";

}

    if ($form->{currency} eq $form->{defaultcurrency}) {

        $form->{exchangerate} = 1;

    } else {

        $exchangerate = $form->check_exchangerate($myconfig, $form->{currency}, $form->{transdate}, $buysell);

        $form->{exchangerate} = ($exchangerate) ? $exchangerate : $form->parse_amount($myconfig, $form->{exchangerate}); 

}

    my @taxaccounts = split / /, $form->{taxaccounts};

    my $tax = 0;

    my $fxtax = 0;

    my $amount;

    my $diff;

    my %tax = ();

    my $accno;

# add taxes

    foreach $accno (@taxaccounts) {

        $fxtax += $tax{fxamount}{$accno} = $form->parse_amount($myconfig, $form->{"tax_$accno"});

        $tax += $tax{fxamount}{$accno};

        push @{ $form->{acc_trans}{taxes} }, {

            accno => $accno,

            amount => $tax{fxamount}{$accno},

            project_id => 'NULL',

            fx_transaction => 0 };

        $amount = $tax{fxamount}{$accno} * $form->{exchangerate};

        $tax{amount}{$accno} = $form->round_amount($amount - $diff, 2);

        $diff = $tax{amount}{$accno} - ($amount - $diff);

        $amount = $tax{amount}{$accno} - $tax{fxamount}{$accno};

        $tax += $amount;

        if ($form->{currency} ne $form->{defaultcurrency}) {

            push @{ $form->{acc_trans}{taxes} }, {

                accno => $accno,

                amount => $amount,

                project_id => 'NULL',

                fx_transaction => 1 };

}

    my %amount = ();

    my $fxinvamount = 0;

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

        $fxinvamount += $amount{fxamount}{$_} = $form->parse_amount($myconfig, $form->{"amount_$_"}) 

}

    $form->{taxincluded} *= 1;

    my $i;

    my $project_id;

    my $cleared = 0;

    $diff = 0;

# deduct tax from amounts if tax included

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

        if ($amount{fxamount}{$i}) {

            if ($form->{taxincluded}) {

                $amount = ($fxinvamount) ? $fxtax * $amount{fxamount}{$i} / $fxinvamount : 0;

                $amount{fxamount}{$i} -= $amount;

}

# multiply by exchangerate

            $amount = $amount{fxamount}{$i} * $form->{exchangerate};

            $amount{amount}{$i} = $form->round_amount($amount - $diff, 2);

            $diff = $amount{amount}{$i} - ($amount - $diff);

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

            $project_id ||= 'NULL';

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

            if ($keepcleared) {

                $cleared = ($form->{"cleared_$i"}) ? 1 : 0;

}

            push @{ $form->{acc_trans}{lineitems} }, {

                accno => $accno,

                amount => $amount{fxamount}{$i},

                project_id => $project_id,

                description => $form->{"description_$i"},

                cleared => $cleared,

                fx_transaction => 0 };

            if ($form->{currency} ne $form->{defaultcurrency}) {

                $amount = $amount{amount}{$i} - $amount{fxamount}{$i};

                push @{ $form->{acc_trans}{lineitems} }, {

                    accno => $accno,

                    amount => $amount,

                    project_id => $project_id,

                    description => $form->{"description_$i"},

                    cleared => $cleared,

                    fx_transaction => 1 };

}

    my $invnetamount = 0;

    for (@{ $form->{acc_trans}{lineitems} }) { $invnetamount += $_->{amount} }

    my $invamount = $invnetamount + $tax;

# adjust paidaccounts if there is no date in the last row

    $form->{paidaccounts}-- 

        unless ($form->{"datepaid_$form->{paidaccounts}"});

    if ($form->{vc} ne "customer"){

        $form->{vc} = "vendor";

}

    my $paid = 0;

    my $fxamount;

    $diff = 0;

# add payments

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

        $fxamount = $form->parse_amount($myconfig, $form->{"paid_$i"});

        if ($fxamount) {

            $paid += $fxamount;

            $paidamount = $fxamount * $form->{exchangerate};

            $amount = $form->round_amount($paidamount - $diff, 2);

            $diff = $amount - ($paidamount - $diff);

            $form->{datepaid} = $form->{"datepaid_$i"};

            $paid{fxamount}{$i} = $fxamount;

            $paid{amount}{$i} = $amount;

}

    $fxinvamount += $fxtax unless $form->{taxincluded};

    $fxinvamount = $form->round_amount($fxinvamount, 2);

    $invamount = $form->round_amount($invamount, 2);

    $paid = $form->round_amount($paid, 2);

    $paid = ($fxinvamount == $paid) 

? $invamount

        : $form->round_amount($paid * $form->{exchangerate}, 2);

    $query = q|

SELECT fxgain_accno_id, fxloss_accno_id

FROM defaults|;

    my ($fxgain_accno_id, $fxloss_accno_id) = $dbh->selectrow_array($query);

    ($null, $form->{employee_id}) = split /--/, $form->{employee};

    unless ($form->{employee_id}) {

        ($form->{employee}, $form->{employee_id}) = 

            $form->get_employee($dbh); 

}

# check if id really exists

    if ($form->{id}) {

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

        $keepcleared = 1;

        $query = qq|

SELECT id

FROM $table

             WHERE id = $id|;

        if ($dbh->selectrow_array($query)) {

# delete detail records

            $query = qq|

DELETE FROM acc_trans

                 WHERE trans_id = $id|;

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

}

    } else {

        my $uid = localtime;

        $uid .= "$$";

        $query = qq|

            INSERT INTO $table (invnumber)

                 VALUES ('$uid')|;

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

        $query = qq|

SELECT id FROM $table

             WHERE invnumber = '$uid'|;

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

}

# record last payment date in ar/ap table

    $form->{datepaid} = $form->{transdate} unless $form->{datepaid};

    my $datepaid = ($paid) ? qq|'$form->{datepaid}'| : 'NULL';

    $form->{invnumber} = $form->update_defaults($myconfig, $invnumber) unless $form->{invnumber};

    $query = qq|

UPDATE $table

SET invnumber = ?,

ordnumber = ?,

transdate = ?,

            $form->{vc}_id = ?,

taxincluded = ?,

amount = ?,

duedate = ?,

paid = ?,

datepaid = ?,

netamount = ?,

curr = ?,

notes = ?,

department_id = ?,

employee_id = ?,

ponumber = ?

WHERE id = ?

    my @queryargs = ($form->{invnumber}, $form->{ordnumber},

        $form->{transdate}, $form->{"$form->{vc}_id"},

        $form->{taxincluded}, $invamount, $form->{duedate}, $paid, 

        $datepaid, $invnetamout, $form->{currency}, $form->{notes},

        $form->{department_id}, $form->{employee_id},

        $form->{ponumber}, $form->{id});

    $dbh->prepare($query)->execute(@queryargs) || $form->dberror($query);

    @queries = $form->run_custom_queries($table, 'INSERT');

# update exchangerate

    my $buy = $form->{exchangerate};

    my $sell = 0;

        if ($form->{vc} eq 'vendor') {

        $buy = 0;

        $sell = $form->{exchangerate};

}

    if (($form->{currency} ne $form->{defaultcurrency}) && !$exchangerate) {

        $form->update_exchangerate(

            $dbh, $form->{currency}, $form->{transdate}, 

            $buy, $sell);

}

    my $ref;

# add individual transactions

    foreach $ref (@{ $form->{acc_trans}{lineitems} }) {

# insert detail records in acc_trans

        if ($ref->{amount}) {

            $query = qq|

INSERT INTO acc_trans

                            (trans_id, chart_id, amount, 

                            transdate, project_id, memo, 

fx_transaction, cleared)

VALUES (?, (SELECT id FROM chart

WHERE accno = ?),

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

            @queryargs = ($form->{id}, $ref->{accno}, 

                $ref->{amount}, $ml, $form->{transdate}, 

                $ref->{project_id}, $ref->{description}, 

                $ref->{fx_transaction}, $ref->{cleared});

            $dbh->prepare($query)->execute(@queryargs) 

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

}

# save taxes

    foreach $ref (@{ $form->{acc_trans}{taxes} }) {

        if ($ref->{amount}) {

            $query = qq|

INSERT INTO acc_trans

                            (trans_id, chart_id, amount,

transdate, fx_transaction)

VALUES (?, (SELECT id FROM chart

WHERE accno = ?),

? * ?, ?, ?)|;

            @queryargs = ($form->{id}, $ref->{accno}, 

                $ref->{amount}, $ml, $form->{transdate}, 

                $ref->{fx_transaction});

            $dbh->prepare($query)->execute(@queryargs) 

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

}

    my $arap;

# record ar/ap

    if (($arap = $invamount)) {

        ($accno) = split /--/, $form->{$ARAP};

        $query = qq|

INSERT INTO acc_trans

                        (trans_id, chart_id, amount, transdate)

VALUES (?, (SELECT id FROM chart

                              WHERE accno = '?'), 

                              ? * -1 * $ml, ?)|;

        @queryargs = ($form->{id}, $accno, $invamount, 

            $form->{transdate});

        $dbh->prepare($query)->execute(@queryargs) 

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

}

# if there is no amount force ar/ap

    if ($fxinvamount == 0) {

        $arap = 1;

}

    my $exchangerate;

# add paid transactions

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

        if ($paid{fxamount}{$i}) {

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

            $form->{"datepaid_$i"} = $form->{transdate} unless ($form->{"datepaid_$i"});

            $exchangerate = 0;

            if ($form->{currency} eq $form->{defaultcurrency}) {

                $form->{"exchangerate_$i"} = 1;

            } else {

                $exchangerate = $form->check_exchangerate(

                    $myconfig, $form->{currency}, 

                    $form->{"datepaid_$i"}, $buysell);

                $form->{"exchangerate_$i"} = ($exchangerate) 

? $exchangerate

                    : $form->parse_amount(

$myconfig,

                        $form->{"exchangerate_$i"}); 

}

# if there is no amount

            if ($fxinvamount == 0) {

                $form->{exchangerate} = 

                    $form->{"exchangerate_$i"};

}

# ar/ap amount

            if ($arap) {

                ($accno) = split /--/, $form->{$ARAP};

# add ar/ap

                $query = qq|

INSERT INTO acc_trans

                                (trans_id, chart_id, 

amount,transdate)

VALUES (?, (SELECT id FROM chart

WHERE accno = ?),

                                ? * $ml, ?)|;

                @queryargs = ($form->{id}, $paid{amount}{$i},

                    $form->{"datepaid_$i"});

                $dbh->prepare($query)->execute(@queryargs) 

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

}

            $arap = $paid{amount}{$i};

# add payment

            if ($paid{fxamount}{$i}) {

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

                my $cleared = ($form->{"cleared_$i"}) ? 1 : 0;

                $amount = $paid{fxamount}{$i};

                $query = qq|

INSERT INTO acc_trans

                                (trans_id, chart_id, amount,

                                transdate, source, memo, 

cleared)

VALUES (?, (SELECT id FROM chart

WHERE accno = ?),

                                ? * -1 * $ml, ?, ?, ?, ?)|;

                @queryargs = ($form->{id}, $accno, $amount, 

                    $form->{"datepaid_$i"}, 

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

                    $form->{"memo_$i"},

$cleared);

                $dbh->prepare($query)->execute(@queryargs) 

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

                if ($form->{currency} 

                        ne $form->{defaultcurrency}) {

# exchangerate gain/loss

                    $amount = ($form->round_amount(

                        $paid{fxamount}{$i} 

                        * $form->{exchangerate},2) - 

                        $form->round_amount(

                            $paid{fxamount}{$i} 

                            * $form->{"exchangerate_$i"},

                            2)) * -1;

                    if ($amount) {

                        my $accno_id = (($amount * $ml) > 0) ? $fxgain_accno_id : $fxloss_accno_id;

                        $query = qq|

INSERT INTO acc_trans

(trans_id,

chart_id,

amount,

transdate,

fx_transaction,

cleared)

VALUES (?, ?,

                                        ? * $ml, 

                                        ?, '1', ?)|;

                        @queryargs = ($form->{id}, 

                            $accno_id, $amount,

                            $form->{"datepaid_$i"},

$cleared);

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

                        $sth->execute(@queryargs) 

                            $form->dberror($query);

}

# exchangerate difference

                    $amount = $paid{amount}{$i} - $paid{fxamount}{$i} + $amount;

                    $query = qq|

INSERT INTO acc_trans

                                    (trans_id, chart_id,

amount,

transdate,

fx_transaction,

cleared, source)

VALUES (?, (SELECT id

FROM chart

WHERE accno

= ?),

                                    ? * -1 * $ml, ?, 

'1', ?, ?)|;

                    @queryargs = ($form->{id}, $accno,

                        $amount, $form->{"datepaid_$i"},

                        $cleared, $form->{"source_$i"});

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

                    $sth->execute(@queryargs)

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

}

# update exchangerate record

                $buy = $form->{"exchangerate_$i"};

                $sell = 0;

                if ($form->{vc} eq 'vendor') {

                    $buy = 0;

                    $sell = $form->{"exchangerate_$i"};

}

                if (($form->{currency} ne 

                $form->{defaultcurrency}) && !$exchangerate) {

                    $form->update_exchangerate(

                        $dbh, $form->{currency}, 

                        $form->{"datepaid_$i"}, $buy, 

$sell);

}

# save printed and queued

    $form->save_status($dbh);

    my %audittrail = ( tablename  => $table,

                       reference  => $form->{invnumber},

                       formname   => 'transaction',

                       action     => 'posted',

                       id         => $form->{id} );

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

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

    my $rc = $dbh->commit;

$rc;

}

sub delete_transaction {

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

# connect to database, turn AutoCommit off

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

    my $table = ($form->{vc} eq 'customer') ? 'ar' : 'ap';

    my %audittrail = ( tablename  => $table,

                       reference  => $form->{invnumber},

                       formname   => 'transaction',

                       action     => 'deleted',

                       id         => $form->{id} );

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

    my $query = qq|DELETE FROM $table WHERE id = $form->{id}|;

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

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

    $dbh->prepare($query)->execute($form->{id}) || $form->dberror($query);

# get spool files

    $query = qq|SELECT spoolfile 

FROM status

WHERE trans_id = ?

AND spoolfile IS NOT NULL|;

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

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

    my $spoolfile;

    my @spoolfiles = ();

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

        push @spoolfiles, $spoolfile;

}

    $sth->finish;

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

    $dbh->prepare($query)->execute($form->{id}) || $form->dberror($query);

# commit

    my $rc = $dbh->commit;

    if ($rc) {

        foreach $spoolfile (@spoolfiles) {

            unlink "${LedgerSMB::Sysconfig::spool}/$spoolfile" if $spoolfile;

}

$rc;

}

sub transactions {

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

# connect to database

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

    my $null;

    my $var;

    my $paid = "a.paid";

    my $ml = 1;

    my $ARAP = 'AR';

    my $table = 'ar';

    my $buysell = 'buy';

    my $acc_trans_join;

    my $acc_trans_flds;

    if ($form->{vc} eq 'vendor') {

        $ml = -1;

        $ARAP = 'AP';

        $table = 'ap';

        $buysell = 'sell';

}

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

    my @paidargs = ();

    if ($form->{outstanding}) {

        $paid = qq|

            SELECT SUM(ac.amount) * -1 * $ml

FROM acc_trans ac

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

             WHERE ac.trans_id = a.id

                   AND (c.link LIKE '%${ARAP}_paid%' 

                   OR c.link = '')|;

        if ($form->{transdateto}){

            $paid .= qq|

AND ac.transdate <= ?|;

            push @paidargs, $form->{transdateto};

}

        $form->{summary} = 1;

}

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

        $acc_trans_flds = qq|

            , c.accno, ac.source,

            pr.projectnumber, ac.memo AS description,

ac.amount AS linetotal,

i.description AS linedescription|;

        $acc_trans_join = qq| 

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

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

            LEFT JOIN project pr ON (pr.id = ac.project_id)

            LEFT JOIN invoice i ON (i.id = ac.invoice_id)|;

}

    my $query = qq|

           SELECT a.id, a.invnumber, a.ordnumber, a.transdate,

                  a.duedate, a.netamount, a.amount, ($paid) AS paid,

                  a.invoice, a.datepaid, a.terms, a.notes,

                  a.shipvia, a.shippingpoint, e.name AS employee, 

vc.name,

                  a.$form->{vc}_id, a.till, m.name AS manager, a.curr,

                  ex.$buysell AS exchangerate, 

d.description AS department,

a.ponumber $acc_trans_flds

FROM $table a

             JOIN $form->{vc} vc ON (a.$form->{vc}_id = vc.id)

        LEFT JOIN employee e ON (a.employee_id = e.id)

        LEFT JOIN employee m ON (e.managerid = m.id)

        LEFT JOIN exchangerate ex ON (ex.curr = a.curr

                  AND ex.transdate = a.transdate)

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

$acc_trans_join|;

    my %ordinal = ( id => 1,

                    invnumber => 2,

                    ordnumber => 3,

                    transdate => 4,

                    duedate => 5,

                    datepaid => 10,

                    shipvia => 13,

                    shippingpoint => 14,

                    employee => 15,

                    name => 16,

                    manager => 19,

                    curr => 20,

                    department => 22,

                    ponumber => 23,

                    accno => 24,

                    source => 25,

                    project => 26,

                    description => 27);

    my @a = (transdate, invnumber, name);

    push @a, "employee" if $form->{l_employee};

    push @a, "manager" if $form->{l_manager};

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

    my $where = "1 = 1";

    if ($form->{"$form->{vc}_id"}) {

        $where .= qq| AND a.$form->{vc}_id = $form->{"$form->{vc}_id"}|;

    } else {

        if ($form->{$form->{vc}}) {

            $var = $dbh->quote(

                $form->like(lc $form->{$form->{vc}}));

            $where .= " AND lower(vc.name) LIKE $var";

}

    for (qw(department employee)) {


context:
space:
mode: