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

summary refs log tree commit diff

path: root/openid-selector_da

diff options

Diffstat (limited to 'openid-selector_da')

0 files changed, 0 insertions, 0 deletions

ss="hl"> # Web: http://www.sql-ledger.org

# Contributors:

#====================================================================

# This file has undergone whitespace cleanup.

#====================================================================

# Project module

# also used for partsgroups

#====================================================================

=head1 METHODS

=over

=cut

package PE;

=item PE->($myconfig, $form);

Populates the list referred to as $form->{all_project} with hashes containing

details about projects. Each hash contains the project record's fields along

with the name of any associated customer. If $form->{status} is 'orphaned',

only add projects that aren't referred to in any transactions, invoices,

orders, or time cards. If $form->{status} is 'active', only projects that have

not reached their enddate are added; when $form->{status} is 'inactive', only

add projects that have reached their enddates. When $form->{year} and

$form->{month} are set, use their values, along with that of $form->{interval},

to set the startdatefrom and startdateto attributes of $form. These attributes

are used to prepare a date range for accepted start dates. Both

$form->{description} and $form->{projectnumber} are used to limit the results.

Returns the number of projects added to the list. $myconfig is unused.

=cut

sub projects {

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

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

    $form->{sort} = "projectnumber" unless $form->{sort};

    my @a       = ( $form->{sort} );

    my %ordinal = (

        projectnumber => 2,

        description   => 3,

        startdate     => 4,

        enddate       => 5,

);

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

    my $query;

    my $where = "WHERE 1=1";

    $query = qq|

SELECT pr.*, c.name

FROM project pr

        LEFT JOIN customer c ON (c.id = pr.customer_id)|;

    if ( $form->{type} eq 'job' ) {

        $where .= qq| AND pr.id NOT IN (SELECT DISTINCT id

FROM parts

                        WHERE project_id > 0)|;

}

    my $var;

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

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

        $where .= " AND lower(pr.projectnumber) LIKE $var";

}

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

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

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

}

    ( $form->{startdatefrom}, $form->{startdateto} ) =

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

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

    if ( $form->{startdatefrom} ) {

$where .=

" AND (pr.startdate IS NULL OR pr.startdate >= "

          . $dbh->quote( $form->{startdatefrom} ) . ")";

}

    if ( $form->{startdateto} ) {

$where .=

" AND (pr.startdate IS NULL OR pr.startdate <= "

          . $dbh->quote( $form->{startdateto} ) . ")";

}

    if ( $form->{status} eq 'orphaned' ) {

        $where .= qq| AND pr.id NOT IN (SELECT DISTINCT project_id

FROM acc_trans

WHERE project_id > 0

UNION

SELECT DISTINCT project_id

FROM invoice

WHERE project_id > 0

UNION

SELECT DISTINCT project_id

FROM orderitems

WHERE project_id > 0

UNION

SELECT DISTINCT project_id

FROM jcitems

                    WHERE project_id > 0)

    } elsif ( $form->{status} eq 'active' ) {

        $where .= qq| 

AND (pr.enddate IS NULL

            OR pr.enddate >= current_date)|;

    } elsif ( $form->{status} eq 'inactive' ) {

        $where .= qq| AND pr.enddate <= current_date|;

}

    $query .= qq|

$where

ORDER BY $sortorder|;

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

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

    my $i = 0;

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

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

$i++;

}

    $sth->finish;

    $dbh->commit;

$i;

}

=item PE->get_project($myconfig, $form)

If $form->{id} is set, populates the $form attributes projectnumber,

description, startdate, enddate, parts_id, production, completed, and

customer_id with details from the project record and name with the associated

customer name. If the project is not used in any transaction, invoice, order,

or time card, $form->{orphaned} is set to true, otherwise false.

Even if $form->{id} is false, PE->get_customer is run, along with any custom

SELECT queries for the table 'project'.

=cut

sub get_project {

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

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

    my $query;

    my $sth;

    my $ref;

    my $where;

    if ( $form->{id} ) {

        $query = qq|

SELECT pr.*, c.name AS customer

FROM project pr

            LEFT JOIN customer c ON (c.id = pr.customer_id)

WHERE pr.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;

# check if it is orphaned

        $query = qq|

SELECT count(*)

FROM acc_trans

WHERE project_id = ?

UNION

SELECT count(*)

FROM invoice

WHERE project_id = ?

UNION

SELECT count(*)

FROM orderitems

WHERE project_id = ?

UNION

SELECT count(*)

FROM jcitems

WHERE project_id = ?|;

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

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

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

        my $count;

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

            $form->{orphaned} += $count;

}

        $sth->finish;

        $form->{orphaned} = !$form->{orphaned};

}

    PE->get_customer( $myconfig, $form, $dbh );

    $form->run_custom_queries( 'project', 'SELECT' );

    $dbh->commit;

}

=item PE->save_project($myconfig, $form)

Updates a project, or adds a new one if $form->{id} is not set.

The $form attributes of startdate, enddate, customer_id, description, and

projectnumber are used for the project record. If $form->{projectnumber} is

false, a new one is obtained through $form->update_defaults. When a new

project is added, $form->{id} is set to that new id. Any custom queries for

UPDATE on the project table are run.

=cut

sub save_project {

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

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

    $form->{customer_id} ||= undef;

    $form->{projectnumber} =

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

      unless $form->{projectnumber};

    my $enddate;

    my $startdate;

    $enddate   = $form->{enddate}   if $form->{enddate};

    $startdate = $form->{startdate} if $form->{startdate};

    if ( $form->{id} ) {

        $query = qq|

UPDATE project

SET projectnumber = ?,

description = ?,

startdate = ?,

enddate = ?,

customer_id = ?

             WHERE id = | . $dbh->quote( $form->{id} );

}

else {

        $query = qq|

            INSERT INTO project (projectnumber, description, 

                        startdate, enddate, customer_id)

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

}

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

    $sth->execute( $form->{projectnumber},

        $form->{description}, $startdate, $enddate, $form->{customer_id} )

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

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

        $query = "SELECT currval('id')";

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

}

    $form->run_custom_queries( 'project', 'UPDATE' );

    $dbh->commit;

}

=item PE->list_stock($myconfig, $form);

Populates the list referred to as $form->{all_project} with hashes that contain

details about projects.

Sets $form->{stockingdate} to the current date if it is not already set.

This function is probably unused.

$myconfig is unused.

=cut

sub list_stock {

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

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

    my $var;

    my $where = "1 = 1";

    if ( $form->{status} eq 'active' ) {

        $where = qq|

            (pr.enddate IS NULL OR pr.enddate >= current_date)

            AND pr.completed < pr.production|;

    } elsif ( $form->{status} eq 'inactive' ) {

        $where = qq|pr.completed = pr.production|;

}

    if ( $form->{projectnumber} ) {

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

        $where .= " AND lower(pr.projectnumber) LIKE $var";

}

    if ( $form->{description} ) {

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

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

}

    $form->{sort} = "projectnumber" unless $form->{sort};

    my @a         = ( $form->{sort} );

    my %ordinal   = ( projectnumber => 2, description => 3 );

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

    my $query = qq|

SELECT pr.*, p.partnumber

FROM project pr

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

WHERE $where

ORDER BY $sortorder|;

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

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

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

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

}

    $sth->finish;

    $query = qq|SELECT current_date|;

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

      if !$form->{stockingdate};

    $dbh->commit;

}

=item PE->jobs($myconfig, $form);

This function is probably unused.

$myconfig is unused.

=cut

sub jobs {

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

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

    $form->{sort} = "projectnumber" unless $form->{sort};

    my @a         = ( $form->{sort} );

    my %ordinal   = ( projectnumber => 2, description => 3, startdate => 4 );

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

    my $query = qq|

           SELECT pr.*, p.partnumber, p.onhand, c.name

FROM project pr

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

        LEFT JOIN customer c ON (c.id = pr.customer_id)

            WHERE 1=1|;

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

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

        $query .= " AND lower(pr.projectnumber) LIKE $var";

}

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

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

        $query .= " AND lower(pr.description) LIKE $var";

}

    ( $form->{startdatefrom}, $form->{startdateto} ) =

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

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

    if ( $form->{startdatefrom} ) {

$query .=

          " AND pr.startdate >= " . $dbh->quote( $form->{startdatefrom} );

}

    if ( $form->{startdateto} ) {

        $query .= " AND pr.startdate <= " . $dbh->quote( $form->{startdateto} );

}

    if ( $form->{status} eq 'active' ) {

        $query .= qq| AND NOT pr.production = pr.completed|;

}

    if ( $form->{status} eq 'inactive' ) {

        $query .= qq| AND pr.production = pr.completed|;

}

    if ( $form->{status} eq 'orphaned' ) {

        $query .= qq| 

            AND pr.completed = 0

AND (pr.id NOT IN

(SELECT DISTINCT project_id

FROM invoice

WHERE project_id > 0

UNION

SELECT DISTINCT project_id

FROM orderitems

WHERE project_id > 0

UNION

SELECT DISTINCT project_id

FROM jcitems

              WHERE project_id > 0)

)|;

}

    $query .= qq|

ORDER BY $sortorder|;

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

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

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

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

}

    $sth->finish;

    $dbh->commit;

}

=item PE->get_job($myconfig, $form);

This function is probably unused as part of Dieter's incomplete job costing.

=cut

sub get_job {

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

# connect to database

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

    my $query;

    my $sth;

    my $ref;

    if ( $form->{id} ) {

        $query = qq|

SELECT value FROM defaults

             WHERE setting_key = 'weightunit'|;

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

        $query = qq|

               SELECT pr.*, p.partnumber, 

                      p.description AS partdescription, p.unit, 

                      p.listprice, p.sellprice, p.priceupdate, 

                      p.weight, p.notes, p.bin, p.partsgroup_id,

ch.accno AS income_accno,

ch.description AS income_description,

                      pr.customer_id, c.name AS customer, 

pg.partsgroup

FROM project pr

            LEFT JOIN parts p ON (p.id = pr.parts_id)

            LEFT JOIN chart ch ON (ch.id = p.income_accno_id)

            LEFT JOIN customer c ON (c.id = pr.customer_id)

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

                WHERE pr.id = | . $dbh->quote( $form->{id} );

}

else {

        $query = qq|

SELECT value, current_date AS startdate FROM defaults

             WHERE setting_key = 'weightunit'|;

}

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

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

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

    for ( keys %$ref ) { $form->{$_} = $ref->{$_} }

    $sth->finish;

    if ( $form->{id} ) {

# check if it is orphaned

        $query = qq|

SELECT count(*)

FROM invoice

WHERE project_id = ?

UNION

SELECT count(*)

FROM orderitems

WHERE project_id = ?

UNION

SELECT count(*)

FROM jcitems

WHERE project_id = ?|;

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

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

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

        my $count;

        my $count;

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

            $form->{orphaned} += $count;

}

        $sth->finish;

}

    $form->{orphaned} = !$form->{orphaned};

    $query = qq|

          SELECT accno, description, link

FROM chart

WHERE link LIKE ?

ORDER BY accno|;

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

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

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

        for ( split /:/, $ref->{link} ) {

            if (/IC/) {

                push @{ $form->{IC_links}{$_} },

{

                    accno       => $ref->{accno},

                    description => $ref->{description}

};

}

    $sth->finish;

    if ( $form->{id} ) {

        $query = qq|

SELECT ch.accno

FROM parts p

              JOIN partstax pt ON (pt.parts_id = p.id)

              JOIN chart ch ON (pt.chart_id = ch.id)

WHERE p.id = ?|;

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

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

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

            $form->{amount}{ $ref->{accno} } = $ref->{accno};

}

        $sth->finish;

}

    PE->get_customer( $myconfig, $form, $dbh );

    $dbh->commit;

}

=item PE->get_customer($myconfig, $form[, $dbh]);

Populates the list referred to as $form->{all_customer} with hashes containing

the ids and names of customers unless the number of customers added would be

greater than or equal to $myconfig->{vclimit}. $form->{startdate} and

$form->{enddate} form a date range to limit the results. If

$form->{customer_id} is set, then the customer with that id will be in the

result set.

=cut

sub get_customer {

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

    if ( !$dbh ) {

        $dbh = $form->{dbh};

}

    my $query;

    my $sth;

    my $ref;

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

        $query = qq|SELECT current_date|;

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

}

    my $where =

qq|(startdate >= |

      . $dbh->quote( $form->{startdate} )

      . qq| OR startdate IS NULL OR enddate IS NULL)|;

    if ( $form->{enddate} ) {

$where .=

            qq| AND (enddate >= |

          . $dbh->quote( $form->{enddate} )

          . qq| OR enddate IS NULL)|;

}

else {

        $where .= qq| AND (enddate >= current_date OR enddate IS NULL)|;

}

    $query = qq|

SELECT count(*)


context:
space:
mode: