getfield: query field (meta-data) values - ikiwiki-upstream - Unnamed repository; edit this file to name it for gitweb.

diff options

author	http://kerravonsen.dreamwidth.org/ <http://kerravonsen.dreamwidth.org/@web>	2010-01-13 02:27:59 +0000
committer	Joey Hess <joey@finch.kitenet.net>	2010-01-13 02:27:59 +0000
commit	ccc8e8868269bf2751596e04f792c388acb85c12 (patch)
tree	198ee598cc1f06dd329662fbb2698501d15d102e /doc/plugins/htmlscrubber
parent	9ccd1ba41bff43c1a5d06197b454c8748224e30f (diff)

getfield: query field (meta-data) values

Diffstat (limited to 'doc/plugins/htmlscrubber')

0 files changed, 0 insertions, 0 deletions

class="hl">package OE;

use LedgerSMB::Tax;

sub transactions {

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

# connect to database

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

    my $query;

    my $null;

    my $var;

    my $ordnumber = 'ordnumber';

    my $quotation = '0';

    my $department;

    my $rate = ($form->{vc} eq 'customer') ? 'buy' : 'sell';

    ($form->{transdatefrom}, $form->{transdateto}) = 

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

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

    if ($form->{type} =~ /_quotation$/) {

        $quotation = '1';

        $ordnumber = 'quonumber';

}

    my $number = $form->like(lc $form->{$ordnumber});

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

    my @dptargs = ();

    for (qw(department employee)) {

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

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

            $department .= " AND o.${_}_id = ?";

            push @dptargs, $var;

}

    if ($form->{vc} ne 'customer'){ # Sanitize $form->{vc}

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

}

    my $query = qq|

        SELECT o.id, o.ordnumber, o.transdate, o.reqdate,

            o.amount, ct.name, o.netamount, o.$form->{vc}_id,

            ex.$rate AS exchangerate, o.closed, o.quonumber, 

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

            m.name AS manager, o.curr, o.ponumber

FROM oe o

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

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

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

LEFT JOIN exchangerate ex

            ON (ex.curr = o.curr AND ex.transdate = o.transdate)

WHERE o.quotation = ?

$department|;

    my @queryargs = @dptargs;

    unshift @queryargs, $quotation;

    my %ordinal = ( 

        id => 1,

        ordnumber => 2,

        transdate => 3,

        reqdate => 4,

        name => 6,

        quonumber => 11,

        shipvia => 13,

        employee => 14,

        manager => 15,

        curr => 16,

        ponumber => 17);

    my @a = (transdate, $ordnumber, name);

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

    if ($form->{type} !~ /(ship|receive)_order/) {

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

}

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

# build query if type eq (ship|receive)_order

    if ($form->{type} =~ /(ship|receive)_order/) {

        my ($warehouse, $warehouse_id) = split /--/, $form->{warehouse};

        $query =  qq|

            SELECT DISTINCT o.id, o.ordnumber, o.transdate,

                o.reqdate, o.amount, ct.name, o.netamount, 

                o.$form->{vc}_id, ex.$rate AS exchangerate,

                o.closed, o.quonumber, o.shippingpoint, 

                o.shipvia, e.name AS employee, o.curr, 

o.ponumber

FROM oe o

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

            JOIN orderitems oi ON (oi.trans_id = o.id)

            JOIN parts p ON (p.id = oi.parts_id)|;

        if ($warehouse_id && $form->{type} eq 'ship_order') {

            $query .= qq|

                JOIN inventory i ON (oi.parts_id = i.parts_id)

}

        $query .= qq|

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

LEFT JOIN exchangerate ex

                ON (ex.curr = o.curr 

                    AND ex.transdate = o.transdate)

            WHERE o.quotation = '0'

            AND (p.inventory_accno_id > 0 OR p.assembly = '1')

            AND oi.qty != oi.ship

$department|;

        @queryargs = @dptargs; #reset @queryargs

        if ($warehouse_id && $form->{type} eq 'ship_order') {

            $query .= qq| 

AND i.warehouse_id = ?

AND (

                    SELECT SUM(i.qty)

FROM inventory i

                    WHERE oi.parts_id = i.parts_id

AND i.warehouse_id = ?

                ) > 0|;

            push(@queryargs, $warehouse_id, $warehouse_id);

}

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

        $query .= qq| AND o.$form->{vc}_id = $form->{"$form->{vc}_id"}|;

    } elsif ($form->{$form->{vc}} ne "") {

        $query .= " AND lower(ct.name) LIKE ?";

            push @queryargs, $name;

}

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

        $query .= " AND lower(?) LIKE ?";

        push @queryargs, $ordnumber, $number;

        $form->{open} = 1;

        $form->{closed} = 1;

}

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

        $query .= " AND lower(ponumber) LIKE ?";

        push @queryargs, $form->{ponumber};

}

    if (!$form->{open} && !$form->{closed}) {

        $query .= " AND o.id = 0";

    } elsif (!($form->{open} && $form->{closed})) {

        $query .= ($form->{open}) ? 

            " AND o.closed = '0'" : " AND o.closed = '1'";

}

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

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

        $query .= " AND lower(o.shipvia) LIKE ?";

        push @queryargs, $var;

}

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

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

        $query .= " AND o.id IN (SELECT DISTINCT trans_id

FROM orderitems

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

        push @queryargs, $var;

}

    if ($form->{transdatefrom}) {

        $query .= " AND o.transdate >= ?";

        push @queryargs, $form->{transdatefrom};

}

    if ($form->{transdateto}) {

        $query .= " AND o.transdate <= ?";

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

}

    $query .= " ORDER by $sortorder";

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

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

    my %oid = ();

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

        $ref->{exchangerate} = 1 unless $ref->{exchangerate};

        if ($ref->{id} != $oid{id}{$ref->{id}}) {

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

            $oid{vc}{$ref->{curr}}{$ref->{"$form->{vc}_id"}}++;

}

        $oid{id}{$ref->{id}} = $ref->{id};

}

    $sth->finish;

    $dbh->commit;

    if ($form->{type} =~ /^consolidate_/) {

@a = ();

        foreach $ref (@{ $form->{OE} }) { 

            push @a, $ref if $oid{vc}{$ref->{curr}}

                {$ref->{"$form->{vc}_id"}} > 1;

}

    @{ $form->{OE} } = @a;

}

sub save {

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

    $form->db_prepare_vars("quonumber", "transdate", "vendor_id",

        "customer_id", "reqdate", "taxincluded", "shippingpoint",

        "shipvia", "currency", "department_id",

        "employee_id", "language_code", "ponumber", "terms");

# connect to database, turn off autocommit

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

    my @queryargs;

    my $quotation;

    my $ordnumber;

    my $numberfld;

    $form->{vc} = ($form->{vc} eq 'customer') ? 'customer': 'vendor';

    if ($form->{type} =~ /_order$/) {

        $quotation = "0";

        $ordnumber = "ordnumber";

        $numberfld = ($form->{vc} eq 'customer') ? "sonumber" : 

"ponumber";

    } else {

        $quotation = "1";

        $ordnumber = "quonumber";

        $numberfld = ($form->{vc} eq 'customer') ? "sqnumber" : 

"rfqnumber";

}