#=====================================================================
# 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 NOT undergone whitespace cleanup.
#
#======================================================================
#
# Project module
# also used for partsgroups
#
#======================================================================

package PE;


sub projects {
  my ($self, $myconfig, $form) = @_;
  
  # connect to database
  my $dbh = $form->dbconnect($myconfig);

  $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 = $form->like(lc $form->{projectnumber});
    $where .= " AND lower(pr.projectnumber) LIKE '$var'";
  }
  if ($form->{description} ne "") {
    $var = $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 >= '$form->{startdatefrom}')";
  }
  if ($form->{startdateto}) {
    $where .= " AND (pr.startdate IS NULL OR pr.startdate <= '$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->disconnect;
  
  $i;

}


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

  # connect to database
  my $dbh = $form->dbconnect($myconfig);

  my $query;
  my $sth;
  my $ref;
  my $where;
  
  if ($form->{id}) {

    $where = "WHERE pr.id = $form->{id}" 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|;
    $sth = $dbh->prepare($query);
    $sth->execute || $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 = $form->{id}
         UNION
        SELECT count(*)
        FROM invoice
        WHERE project_id = $form->{id}
         UNION
        SELECT count(*)
        FROM orderitems
        WHERE project_id = $form->{id}
         UNION
        SELECT count(*)
        FROM jcitems
        WHERE project_id = $form->{id}
           |;
    $sth = $dbh->prepare($query);
    $sth->execute || $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->disconnect;

}


sub save_project {
  my ($self, $myconfig, $form) = @_;
  
  # connect to database
  my $dbh = $form->dbconnect($myconfig);
  
  $form->{customer_id} ||= 'NULL';

  $form->{projectnumber} = $form->update_defaults($myconfig, "projectnumber", $dbh) unless $form->{projectnumber};

  if ($form->{id}) {

    $query = qq|UPDATE project SET
                projectnumber = |.$dbh->quote($form->{projectnumber}).qq|,
        description = |.$dbh->quote($form->{description}).qq|,
        startdate = |.$form->dbquote($form->{startdate}, SQL_DATE).qq|,
        enddate = |.$form->dbquote($form->{enddate}, SQL_DATE).qq|,
        customer_id = $form->{customer_id}
        WHERE id = $form->{id}|;
  } else {
   
    $query = qq|INSERT INTO project
                (projectnumber, description, startdate, enddate, customer_id)
                VALUES (|
        .$dbh->quote($form->{projectnumber}).qq|, |
        .$dbh->quote($form->{description}).qq|, |
        .$form->dbquote($form->{startdate}, SQL_DATE).qq|, |
        .$form->dbquote($form->{enddate}, SQL_DATE).qq|,
        $form->{customer_id}
        )|;
  }
  $dbh->do($query) || $form->dberror($query);
  $form->run_custom_queries('project', 'UPDATE');
  
  $dbh->disconnect;

}


sub list_stock {
  my ($self, $myconfig, $form) = @_;
  
  # connect to database
  my $dbh = $form->dbconnect($myconfig);

  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 = $form->like(lc $form->{projectnumber});
    $where .= " AND lower(pr.projectnumber) LIKE '$var'";
  }
  
  if ($form->{description}) {
    $var = $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 FROM defaults|;
  ($form->{stockingdate}) = $dbh->selectrow_array($query) if !$form->{stockingdate};
  
  $dbh->disconnect;
  
}


sub jobs {
  my ($self, $myconfig, $form) = @_;
  
  # connect to database
  my $dbh = $form->dbconnect($myconfig);
 
  $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 = $form->like(lc $form->{projectnumber});
    $query .= " AND lower(pr.projectnumber) LIKE '$var'";
  }
  if ($form->{description} ne "") {
    $var = $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 >= '$form->{startdatefrom}'";
  }
  if ($form->{startdateto}) {
    $query .= " AND pr.startdate <= '$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
                    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->disconnect;
  
}


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

  # connect to database
  my $dbh = $form->dbconnect($myconfig);

  my $query;
  my $sth;
  my $ref;

  if ($form->{id}) {
    $query = qq|SELECT weightunit
        FROM defaults|;
    ($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 = $form->{id}|;
  } else {
    $query = qq|SELECT weightunit, current_date AS startdate FROM defaults|;
  }

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

  $ref = $sth->fetchrow_hashref(NAME_lc);
  
  for (keys %$ref) { $form->{$_} = $ref->{$_} }