#=====================================================================
# LedgerSMB
# Small Medium Business Accounting software
# http://www.ledgersmb.org/
#
# Copyright (C) 2006
# This work contains copyrighted information from a number of sources all used
# with permission.
#
# This file contains source code included with or based on SQL-Ledger which
# is Copyright Dieter Simader and DWS Systems Inc. 2000-2005 and licensed
# under the GNU General Public License version 2 or, at your option, any later
# version.  For a full list including contact information of contributors,
# maintainers, and copyright holders, see the CONTRIBUTORS file.
#
# Original Copyright Notice from SQL-Ledger 2.6.17 (before the fork):
# Copyright (C) 2003
#
#  Author: DWS Systems Inc.
#     Web: http://www.sql-ledger.org
#
#  Contributors:
#
#======================================================================
#
# This file has undergone whitespace cleanup.
#
#======================================================================
#
# Project module
# also used for partsgroups
#
#======================================================================

package PE;

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)
        |;

    }
    if ( $form->{status} eq 'active' ) {
        $where .= qq| 
            AND (pr.enddate IS NULL 
            OR pr.enddate >= current_date)|;
    }
    if ( $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;

}

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;

}

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;

}

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|;
    }
    if ( $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;
    }