js/bin/markdown: added `--ast` flag and multiple filename args. - commonmark-rdf-spec - Unnamed repository; edit this file 'description' to name the repository.

path: root/oldtests/Links/AutoLinks.html

diff options

author	John MacFarlane <jgm@berkeley.edu>	2014-10-24 21:18:00 -0700
committer	John MacFarlane <jgm@berkeley.edu>	2014-10-24 21:18:00 -0700
commit	80b09d9552e57011c849fc305553501611b3079d (patch)
tree	e1c3d6d8dce6c850b2be4988af01a221461680dc /oldtests/Links/AutoLinks.html
parent	0ed2541d6dc3ff31126915c6ea59d92f0c23acd1 (diff)

js/bin/markdown: added `--ast` flag and multiple filename args.

Diffstat (limited to 'oldtests/Links/AutoLinks.html')

0 files changed, 0 insertions, 0 deletions

d="n_38" class="hl">

sub post_invoice {

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

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

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

        unless ($form->{"deliverydate_$_"}){

            $form->{"deliverydate_$_"} =  $form->{transdate};

}

    my $query;

    my $sth;

    my $ref;

    my $null;

    my $project_id;

    my $exchangerate = 0;

    my $allocated;

    my $taxrate;

    my $taxamount;

    my $diff = 0;

    my $item;

    my $invoice_id;

    my $keepcleared;

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

    unless ($form->{employee_id}) {

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

            = $form->get_employee($dbh);

}

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

    $form->{department_id} *= 1;

    $query = qq|

SELECT (SELECT value FROM defaults

                 WHERE setting_key = 'fxgain_accno_id') 

AS fxgain_accno_id,

(SELECT value FROM defaults

                 WHERE setting_key = 'fxloss_accno_id')

AS fxloss_accno_id|;

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

    $query = qq|

SELECT inventory_accno_id, income_accno_id, expense_accno_id

FROM parts

WHERE id = ?|;

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

    my %updparts = ();

    if ($form->{id}) {

        my $sth;

        $keepcleared = 1;

        $query = qq|SELECT id FROM ap WHERE id = ?|;

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

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

         if ($sth->fetchrow_array($query)) {

            $query = qq|

                SELECT p.id, p.inventory_accno_id, 

p.income_accno_id

FROM invoice i

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

WHERE i.trans_id = ?|;

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

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

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

                if ($ref->{inventory_accno_id} 

                        && $ref->{income_accno_id}) {

                    $updparts{$ref->{id}} = 1;

}

            $sth->finish;

            &reverse_invoice($dbh, $form);

        } else { 

            $query = qq|INSERT INTO ap (id) VALUES (?)|;

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

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

}

    my $uid = localtime;

    $uid .= "$$";

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

        $query = qq|

            INSERT INTO ap (invnumber, 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 ap WHERE invnumber = '$uid'|;

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

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

        ($form->{id}) = $sth->fetchrow_array;

        $sth->finish;

}

    my $amount;

    my $grossamount;

    my $allocated;

    my $invamount = 0;

    my $invnetamount = 0;

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

        $form->{exchangerate} = 1;

    } else {

$exchangerate =

            $form->check_exchangerate(

                $myconfig, $form->{currency}, 

                $form->{transdate}, 'sell');

}

    $form->{exchangerate} = 

        ($exchangerate) 

? $exchangerate

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

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

        $form->{"qty_$i"} = 

            $form->parse_amount($myconfig, $form->{"qty_$i"});

        if ($form->{"qty_$i"}) {

            $pth->execute($form->{"id_$i"});

            $ref = $pth->fetchrow_hashref(NAME_lc);

            for (keys %$ref) {

                $form->{"${_}_$i"} = $ref->{$_};

}

            $pth->finish;

# project

            if ($form->{"projectnumber_$i"} ne "") {

                ($null, $project_id) = 

                    split /--/, $form->{"projectnumber_$i"};

}

# undo discount formatting

            $form->{"discount_$i"} = 

                $form->parse_amount(

$myconfig,

                    $form->{"discount_$i"}) / 100;

# keep entered selling price

            my $fxsellprice = 

                $form->parse_amount(

                    $myconfig, $form->{"sellprice_$i"});

            my ($dec) = ($fxsellprice =~ /\.(\d+)/);

            $dec = length $dec;

            my $decimalplaces = ($dec > 2) ? $dec : 2;

# deduct discount

            $form->{"sellprice_$i"} = 

                $fxsellprice - $form->round_amount(

                    $fxsellprice * $form->{"discount_$i"}, 

$decimalplaces);

# linetotal

            my $fxlinetotal = $form->round_amount(

                $form->{"sellprice_$i"} * $form->{"qty_$i"}, 2);

            $amount = $fxlinetotal * $form->{exchangerate};

            my $linetotal = $form->round_amount($amount, 2);

            $fxdiff += $amount - $linetotal;

            @taxaccounts = Tax::init_taxes(

                $form, $form->{"taxaccounts_$i"});

            $tax = Math::BigFloat->bzero();

            $fxtax = Math::BigFloat->bzero();

            if ($form->{taxincluded}) {

                $tax += $amount = Tax::calculate_taxes(

                    \@taxaccounts, $form, $linetotal, 1);

                $form->{"sellprice_$i"} 

                    -= $amount / $form{"qty_$i"};

            } else {

                $tax += $amount = Tax::calculate_taxes(

                    \@taxaccounts, $form, $linetotal, 0);

                $fxtax += Tax::calculate_taxes(

                    \@taxaccounts, $form, $fxlinetotal, 0);

}

            for (@taxaccounts) {

                $form->{acc_trans}{$form->{id}}{$_->account}{amount} += $_->value;

}

            $grossamount = $form->round_amount($linetotal, 2);

            if ($form->{taxincluded}) {

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

                $linetotal -= $form->round_amount(

                    $tax - $diff, 2);

                $diff = ($amount - $tax);

}

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

            $allocated = 0;

# adjust and round sellprice

            $form->{"sellprice_$i"} = $form->round_amount(

                $form->{"sellprice_$i"} * $form->{exchangerate},

$decimalplaces);

# save detail record in invoice table

            $query = qq|

INSERT INTO invoice (description)

                     VALUES ('$uid')|;

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

            $query = qq|

SELECT id FROM invoice

                 WHERE description = '$uid'|;

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

                $form->debug;

            $query = qq|

UPDATE invoice

SET trans_id = ?,

parts_id = ?,

description = ?,

                       qty = ? * -1,

sellprice = ?,

fxsellprice = ?,

discount = ?,

allocated = ?,

unit = ?,

deliverydate = ?,

project_id = ?,

serialnumber = ?,

notes = ?

WHERE id = ?|;

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

            $sth->execute(

                $form->{id},  $form->{"id_$i"}, 

                $form->{"description_$i"}, $form->{"qty_$i"},

                $form->{"sellprice_$i"}, $fxsellprice,

                $form->{"discount_$i"}, $allocated, 

                $form->{"unit_$i"}, $form->{"deliverydate_$i"},

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

                $form->{"notes_$i"}, $invoice_id)

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

            if ($form->{"inventory_accno_id_$i"}) {

# add purchase to inventory

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

{chart_id =>

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

                    amount => $amount,

                    fxgrossamount => $fxlinetotal + 

                        $form->round_amount($fxtax, 2),

                    grossamount => $grossamount,

                    project_id => $project_id,

                    invoice_id => $invoice_id };

                $updparts{$form->{"id_$i"}} = 1;

# update parts table

                $form->update_balance(

                    $dbh, "parts", "onhand",

                    qq|id = $form->{"id_$i"}|,

                    $form->{"qty_$i"}) 

                        unless $form->{shipped};

# check if we sold the item

                $query = qq|

                      SELECT i.id, i.qty, i.allocated, 

                             i.trans_id, i.project_id,

p.inventory_accno_id,

                             p.expense_accno_id, a.transdate

FROM invoice i

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

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

WHERE i.parts_id = ?

                             AND (i.qty + i.allocated) > 0

ORDER BY transdate|;

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

                $sth->execute($form->{"id_$i"}) 

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

                my $totalqty = $form->{"qty_$i"};

                while (my $ref = 

                    $sth->fetchrow_hashref(NAME_lc)) {

                    my $qty = $ref->{qty} 

                        + $ref->{allocated};

                    if (($qty - $totalqty) > 0) {

                        $qty = $totalqty;

}

                    $linetotal = $form->round_amount(

                        $form->{"sellprice_$i"} * $qty, 

2);

                    $ref->{project_id} ||= 'NULL';

                    if ($linetotal) {

                        $query = qq|

INSERT INTO acc_trans

(trans_id,

chart_id,

amount,

transdate,

project_id,

invoice_id)

VALUES (?, ?, ?, ?,

?, ?)|;

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

                        $sth->execute(

                            $ref->{trans_id},

                            $ref->{inventory_accno_id},

$linetotal,

                            $ref->{transdate},

                            $ref->{project_id},

$invoice_id

                            ) || $form->dberror(

$query);

# add expense

                        $query = qq|

INSERT INTO acc_trans

(trans_id,

chart_id,

amount,

transdate,

project_id,

invoice_id)

VALUES (?, ?, ?, ?,

?, ?)|;

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

                        $sth->execute(

                            $ref->{trans_id}, 

                            $ref->{expense_accno_id},

                            $linetotal * -1, 

                            $ref->{transdate},

                            $ref->{project_id}, 

$invoice_id

                            ) || $form->dberror(

$query);

}

# update allocated for sold item

                    $form->update_balance(

                        $dbh, "invoice", "allocated",

                        qq|id = $ref->{id}|, $qty * -1);

                    $allocated += $qty;

                    last if (($totalqty -= $qty) <= 0);

}

                $sth->finish;

            } else {

# add purchase to expense

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

chart_id =>

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

                    amount => $amount,

fxgrossamount => $fxlinetotal

                        + $form->round_amount(

                        $fxtax, 2),

                    grossamount => $grossamount,

                    project_id => $project_id,

                    invoice_id => $invoice_id };

}

    $form->{paid} = 0;

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

        $form->{"paid_$i"} = 

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

        $form->{paid} += $form->{"paid_$i"};

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

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

}

# add lineitems + tax

    $amount = 0;

    $grossamount = 0;

    $fxgrossamount = 0;

    for (@{ $form->{acc_trans}{lineitems} }) {

        $amount += $_->{amount};

        $grossamount += $_->{grossamount};

        $fxgrossamount += $_->{fxgrossamount};

}

    $invnetamount = $amount;

    $amount = 0;

    for (split / /, $form->{taxaccounts}) {

        $amount += $form->{acc_trans}{$form->{id}}{$_}{amount} 

            = $form->round_amount(

                $form->{acc_trans}{$form->{id}}{$_}{amount}, 2);

        $form->{acc_trans}{$form->{id}}{$_}{amount} *= -1;

}

    $invamount = $invnetamount + $amount;

    $diff = 0;

    if ($form->{taxincluded}) {

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

        $invamount += $diff;

}

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

    $invnetamount += $fxdiff;

    $invamount += $fxdiff;

    if ($form->round_amount($form->{paid} - $fxgrossamount,2) == 0) {

        $form->{paid} = $invamount;

    } else {

        $form->{paid} = $form->round_amount(

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

}

    foreach $ref (sort { $b->{amount} <=> $a->{amount} } 

                    @ { $form->{acc_trans}{lineitems} }) {

        $amount = $ref->{amount} + $diff + $fxdiff;

        $query = qq|

            INSERT INTO acc_trans (trans_id, chart_id, amount,

                        transdate, project_id, invoice_id)

VALUES (?, ?, ?, ?, ?, ?)|;

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

        $sth->execute(

            $form->{id}, $ref->{chart_id}, $amount * -1, 

            $form->{transdate}, $ref->{project_id}, 

            $ref->{invoice_id}) || $form->dberror($query);

        $diff = 0;

        $fxdiff = 0;

}

    $form->{payables} = $invamount;

    delete $form->{acc_trans}{lineitems};

# update exchangerate

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

        $form->update_exchangerate(

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

            $form->{exchangerate});

}

# record payable

    if ($form->{payables}) {

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

        $query = qq|

            INSERT INTO acc_trans (trans_id, chart_id, amount,

transdate)

VALUES (?, (SELECT id FROM chart WHERE accno = ?),

?, ?)|;

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

        $sth->execute(

            $form->{id}, $accno, $form->{payables}, 

            $form->{transdate}

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

}

    foreach my $trans_id (keys %{$form->{acc_trans}}) {

        foreach my $accno (keys %{ $form->{acc_trans}{$trans_id} }) {

            $amount = $form->round_amount(

                $form->{acc_trans}{$trans_id}{$accno}{amount}, 

2);

            if ($amount) {

                $query = qq|

INSERT INTO acc_trans

                                (trans_id, chart_id, amount,

transdate)

VALUES (?, (SELECT id FROM chart

WHERE accno = ?),

?, ?)|;

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

                $sth->execute(

                    $trans_id, $accno, $amount, 

                    $form->{transdate}

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

}

# if there is no amount but a payment record payable

    if ($invamount == 0) {

        $form->{payables} = 1;

}

    my $cleared = 0;

# record payments and offsetting AP

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

        if ($form->{"paid_$i"}) {

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

            $form->{"datepaid_$i"} = $form->{transdate} 

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

            $form->{datepaid} = $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"}, 'sell');

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

                    ($exchangerate) 

? $exchangerate

                    : $form->parse_amount(

$myconfig,

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

}

# record AP

            $amount = ($form->round_amount(

                $form->{"paid_$i"} * $form->{exchangerate}, 

                2)) * -1;

            if ($form->{payables}) {

                $query = qq|

INSERT INTO acc_trans

                                (trans_id, chart_id, amount,

transdate)

VALUES (?, (SELECT id FROM chart

WHERE accno = ?),

?, ?)|;

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

                $sth->execute(

                    $form->{id}, $form->{AP}, $amount, 

                    $form->{"datepaid_$i"}

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

}

            if ($keepcleared) {

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

}

# record payment

            $query = qq|

INSERT INTO acc_trans

                            (trans_id, chart_id, amount, 

                            transdate, source, memo, cleared)

VALUES (?, (SELECT id FROM chart

WHERE accno = ?),

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

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

            $sth->execute(

                $form->{id}, $accno, $form->{"paid_$i"},

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

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

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

# exchangerate difference

            $amount = $form->round_amount(

                $form->{"paid_$i"} 

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

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

2);

            if ($amount) {

                $query = qq|

INSERT INTO acc_trans

                                (trans_id, chart_id, amount,

transdate, source,

fx_transaction, cleared)

VALUES (?, (SELECT id FROM chart

WHERE accno = ?),

                                ?, ?, ?, '1', ?)|;

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

                $sth->execute(

                    $form->{id}, $accno, $amount, 

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

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

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

}

# gain/loss

            $amount = $form->round_amount(

                $form->round_amount(

                    $form->{"paid_$i"} 

                        * $form->{exchangerate},2) 

                - $form->round_amount(

                    $form->{"paid_$i"} 

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

                , 2);

            if ($amount) {

                my $accno_id = 

                    ($amount > 0) 

? $fxgain_accno_id

                    : $fxloss_accno_id;

                $query = qq|

INSERT INTO acc_trans

                                (trans_id, chart_id, amount,

transdate, fx_transaction,

cleared)

                         VALUES (?, ?, ?, ?, '1', ?)|;

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

                $sth->execute(

                    $form->{id}, $accno_id, $amount, 

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

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

}

# update exchange rate

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

                            && !$exchangerate) {

                $form->update_exchangerate(

                    $dbh, $form->{currency}, 

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

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

}

# set values which could be empty

    $form->{taxincluded} *= 1;

    $form->{invnumber} = 

        $form->update_defaults($myconfig, "vinumber", $dbh) 

            unless $form->{invnumber};

# save AP record

    $query = qq|

UPDATE ap

SET invnumber = ?,

ordnumber = ?,

quonumber = ?,

transdate = ?,

vendor_id = ?,

amount = ?,

netamount = ?,

paid = ?,

datepaid = ?,

duedate = ?,

               invoice = '1',

shippingpoint = ?,

shipvia = ?,

taxincluded = ?,

notes = ?,

intnotes = ?,

curr = ?,

department_id = ?,

employee_id = ?,

language_code = ?,

ponumber = ?

WHERE id = ?|;

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

    $sth->execute(

        $form->{invnumber}, $form->{ordnumber}, $form->{quonumber},

        $form->{transdate}, $form->{vendor_id}, $invamount, 

        $invnetamount, $form->{paid}, $form->{datepaid}, 

        $form->{duedate}, $form->{shippingpoint}, $form->{shipvia},

        $form->{taxincluded}, $form->{notes}, $form->{intnotes},

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

        $form->{language_code}, $form->{ponumber}, $form->{id}

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

# add shipto

    $form->{name} = $form->{vendor};

    $form->{name} =~ s/--$form->{vendor_id}//;

    $form->add_shipto($dbh, $form->{id});

    my %audittrail = ( 

        tablename  => 'ap',

        reference  => $form->{invnumber},

        formname   => $form->{type},

        action     => 'posted',

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

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

    my $rc = $dbh->commit;

    foreach $item (keys %updparts) {

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

        $query = qq|

UPDATE parts

               SET avgcost = avgcost($item),

                   lastcost = lastcost($item)

             WHERE id = $item|;

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

        $dbh->commit;

}

$rc;

}

sub reverse_invoice {

    my ($dbh, $form) = @_;

    my $query = qq|SELECT id FROM ap

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

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

    return unless $id;

# reverse inventory items

    $query = qq|

        SELECT i.parts_id, p.inventory_accno_id, p.expense_accno_id,

               i.qty, i.allocated, i.sellprice, i.project_id

FROM invoice i, parts p

         WHERE i.parts_id = p.id

AND i.trans_id = ?|;

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

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

    my $netamount = 0;

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

        $netamount += $form->round_amount($ref->{sellprice} * $ref->{qty} * -1, 2);

        if ($ref->{inventory_accno_id}) {

# update onhand

            $form->update_balance($dbh,

"parts",

"onhand",

                qq|id = $ref->{parts_id}|,

                $ref->{qty});

# if $ref->{allocated} > 0 than we sold that many items

            if ($ref->{allocated} > 0) {

# get references for sold items

                $query = qq|

                      SELECT i.id, i.trans_id, i.allocated, 

a.transdate

FROM invoice i, ar a

WHERE i.parts_id = ?

                             AND i.allocated < 0

                             AND i.trans_id = a.id

ORDER BY transdate DESC|;

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

                $sth->execute($ref->{parts_id}) 

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

                while (my $pthref = 

                    $sth->fetchrow_hashref(NAME_lc)) {

                    my $qty = $ref->{allocated};

                    if (($ref->{allocated} + 

                            $pthref->{allocated}) 

                                    > 0) {

                        $qty = $pthref->{allocated} 

                            * -1;

}

                    my $amount = $form->round_amount(

                        $ref->{sellprice} * $qty, 2);

#adjust allocated

                    $form->update_balance(

                        $dbh, "invoice", "allocated",

                        qq|id = $pthref->{id}|, $qty);

# add reversal for sale

                    $ref->{project_id} *= 1;

                    $query = qq|

INSERT INTO acc_trans

(trans_id,

chart_id, amount,

transdate,

project_id)

VALUES (?, ?, ?, ?, ?)|;

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

                    $sth->execute(

                        $pthref->{trans_id}, 

                        $ref->{expense_accno_id},

                        $amount, $form->{transdate}, 

                        $ref->{project_id}

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

                    $query = qq|

INSERT INTO acc_trans

                                    (trans_id, chart_id,

amount, transdate,

project_id)

VALUES (?, ?, ?, ?, ?)|;

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

                    $sth->execute(

                        $pthref->{trans_id}, 

                        $ref->{inventory_accno_id},

                        $amount * -1,

                        $form->{transdate}, 

                        $ref->{project_id}

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

                    last if (($ref->{allocated} -= $qty) 

                        <= 0);

}

                $sth->finish;

}

    $sth->finish;

# delete acc_trans

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

    $dbh->prepare($query);

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

# delete invoice entries

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

    $dbh->prepare($query);

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

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

    $dbh->prepare($query);

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

    $dbh->commit;

}

sub delete_invoice {

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

# connect to database

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

    my %audittrail = ( 

        tablename  => 'ap',

        reference  => $form->{invnumber},

        formname   => $form->{type},

        action     => 'deleted',

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

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

    my $query = qq|SELECT parts_id FROM invoice WHERE trans_id = ?|;

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

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

    my $item;

    my %updparts = ();

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

        $updparts{$item} = 1;

}

    $sth->finish;

    &reverse_invoice($dbh, $form);

# delete AP record

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

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

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

# delete 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;

# delete status entries

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

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

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

    if ($rc) {

        foreach $item (keys %updparts) {

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

            $query = qq|

UPDATE parts

                   SET avgcost = avgcost($item),

                       lastcost = lastcost($item)

                 WHERE id = $item|;

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

}

        foreach $spoolfile (@spoolfiles) {

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

                if $spoolfile;

}

    my $rc = $dbh->commit;

$rc;

}

sub retrieve_invoice {

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

# connect to database

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

    my $query;

    if ($form->{id}) {

# get default accounts and last invoice number

        $query = qq|

            SELECT (select c.accno FROM chart c 

WHERE c.id = (SELECT value FROM defaults

WHERE setting_key =

'inventory_accno_id'))

AS inventory_accno,

(SELECT c.accno FROM chart c

WHERE c.id = (SELECT value FROM defaults

WHERE setting_key =

'income_accno_id'))

AS income_accno,

(SELECT c.accno FROM chart c

WHERE c.id = (SELECT value FROM defaults

WHERE setting_key =

'expense_accno_id'))

AS expense_accno,

(SELECT c.accno FROM chart c

WHERE c.id = (SELECT value FROM defaults

WHERE setting_key =

'fxgain_accno_id'))

AS fxgain_accno,

(SELECT c.accno FROM chart c

WHERE c.id = (SELECT value FROM defaults

WHERE setting_key =

'fxloss_accno_id'))

AS fxloss_accno,

(SELECT value FROM defaults

                     WHERE setting_key = 'curr') AS currencies|;

    } else {

        $query = qq|

            SELECT (select c.accno FROM chart c 

WHERE c.id = (SELECT value FROM defaults

WHERE setting_key =

'inventory_accno_id'))

AS inventory_accno,

(SELECT c.accno FROM chart c

WHERE c.id = (SELECT value FROM defaults

WHERE setting_key =

'income_accno_id'))

AS income_accno,

(SELECT c.accno FROM chart c

WHERE c.id = (SELECT value FROM defaults

WHERE setting_key =

'expense_accno_id'))

AS expense_accno,

(SELECT c.accno FROM chart c

WHERE c.id = (SELECT value FROM defaults

WHERE setting_key =

'fxgain_accno_id'))

AS fxgain_accno,

(SELECT c.accno FROM chart c

WHERE c.id = (SELECT value FROM defaults

WHERE setting_key =

'fxloss_accno_id'))

AS fxloss_accno,

(SELECT value FROM defaults

                     WHERE setting_key = 'curr') AS currencies,

current_date AS transdate|;

}

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

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

    my $ref = $sth->fetchrow_hashref(NAME_lc);

    for (keys %$ref) {

        $form->{$_} = $ref->{$_};

}

    $sth->finish;

    if ($form->{id}) {

        $query = qq|

            SELECT a.invnumber, a.transdate, a.duedate,

                   a.ordnumber, a.quonumber, a.paid, a.taxincluded,

                   a.notes, a.intnotes, a.curr AS currency, 

                   a.vendor_id, a.language_code, a.ponumber

FROM ap a

WHERE id = ?|;

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

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

        $ref = $sth->fetchrow_hashref(NAME_lc);

        for (keys %$ref) {

            $form->{$_} = $ref->{$_};

}

        $sth->finish;

# get shipto

        $query = qq|SELECT * FROM shipto WHERE trans_id = ?|;

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

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

        $ref = $sth->fetchrow_hashref(NAME_lc);

        for (keys %$ref) {

            $form->{$_} = $ref->{$_};

}

        $sth->finish;

# retrieve individual items

        $query = qq|

               SELECT p.partnumber, i.description, i.qty, 

                      i.fxsellprice, i.sellprice,

                      i.parts_id AS id, i.unit, p.bin, 

i.deliverydate,

                      pr.projectnumber, i.project_id, 

i.serialnumber,

                      i.discount, i.notes, pg.partsgroup, 

                      p.partsgroup_id, p.partnumber AS sku, 

                      p.weight, p.onhand, p.inventory_accno_id, 

                      p.income_accno_id, p.expense_accno_id,

t.description AS partsgrouptranslation

FROM invoice i

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

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

            LEFT JOIN partsgroup pg ON (pg.id = p.partsgroup_id)

LEFT JOIN translation t

                      ON (t.trans_id = p.partsgroup_id 

AND t.language_code = ?)

WHERE i.trans_id = ?

ORDER BY i.id|;

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

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

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

# exchangerate defaults

        &exchangerate_defaults($dbh, $form);

# price matrix and vendor partnumber

        my $pmh = PriceMatrix::PriceMatrixQuery($dbh, $form);

# tax rates for part

        $query = qq|

SELECT c.accno

FROM chart c

              JOIN partstax pt ON (pt.chart_id = c.id)

WHERE pt.parts_id = ?|;

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

        my $ptref;

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

            my ($dec) = ($ref->{fxsellprice} =~ /\.(\d+)/);

            $dec = length $dec;

            my $decimalplaces = ($dec > 2) ? $dec : 2;

            $tth->execute($ref->{id});

            $ref->{taxaccounts} = "";

            my $taxrate = 0;

            while ($ptref = $tth->fetchrow_hashref(NAME_lc)) {

                $ref->{taxaccounts} .= "$ptref->{accno} ";

                $taxrate += $form->{"$ptref->{accno}_rate"};

}

            $tth->finish;

            chop $ref->{taxaccounts};

# price matrix

            $ref->{sellprice} = $form->round_amount(

                $ref->{fxsellprice} 

                    * $form->{$form->{currency}}, 

$decimalplaces);

PriceMatrix::price_matrix(

                $pmh, $ref, $decimalplaces, $form, $myconfig);

            $ref->{sellprice} = $ref->{fxsellprice};

            $ref->{qty} *= -1;

            $ref->{partsgroup} = $ref->{partsgrouptranslation} 

                if $ref->{partsgrouptranslation};

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

}

        $sth->finish;

}

    my $rc = $dbh->commit;

$rc;

}

sub retrieve_item {

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

    $dbh = $form->{dbh};

    my $i = $form->{rowcount};

    my $null;

    my $var;

# don't include assemblies or obsolete parts

    my $where = "WHERE p.assembly = '0' AND p.obsolete = '0'";

    if ($form->{"partnumber_$i"} ne "") {

        $var = $dbh->quote($form->like(lc $form->{"partnumber_$i"}));

        $where .= " AND lower(p.partnumber) LIKE $var";

}

    if ($form->{"description_$i"} ne "") {

        $var = $dbh->quote($form->like(lc $form->{"description_$i"}));

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

            $where .= " AND lower(t1.description) LIKE $var";

        } else {

            $where .= " AND lower(p.description) LIKE $var";

}

    if ($form->{"partsgroup_$i"} ne "") {

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

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

        $where .= qq| AND p.partsgroup_id = $var|;

}

    if ($form->{"description_$i"} ne "") {

        $where .= " ORDER BY 3";

    } else {

        $where .= " ORDER BY 2";

}

    my $query = qq|

           SELECT p.id, p.partnumber, p.description,

                  pg.partsgroup, p.partsgroup_id,

                  p.lastcost AS sellprice, p.unit, p.bin, p.onhand, 

                  p.notes, p.inventory_accno_id, p.income_accno_id, 

                  p.expense_accno_id, p.partnumber AS sku, p.weight,

t1.description AS translation,

t2.description AS grouptranslation

FROM parts p

        LEFT JOIN partsgroup pg ON (pg.id = p.partsgroup_id)

LEFT JOIN translation t1

                  ON (t1.trans_id = p.id AND t1.language_code = ?)

LEFT JOIN translation t2

                  ON (t2.trans_id = p.partsgroup_id 

AND t2.language_code = ?)

$where|;


context:
space:
mode: