path: root/LedgerSMB/JC.pm

#=====================================================================
# LedgerSMB 
# Small Medium Business Accounting software
# 
# See COPYRIGHT file for copyright information
#======================================================================
#
# This file has NOT undergone whitespace cleanup.
#
#======================================================================
#
# Job Costing
#
#======================================================================


package JC;

use LedgerSMB::IS;

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

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

  my $query = qq|SELECT current_date FROM defaults|;
  ($form->{transdate}) = $dbh->selectrow_array($query);

  ($form->{employee}, $form->{employee_id}) = $form->get_employee($dbh);
  
  my $dateformat = $myconfig->{dateformat};
  $dateformat =~ s/yy/yyyy/;
  $dateformat =~ s/yyyyyy/yyyy/;
 
  if ($form->{id}) {
    # retrieve timecard/storescard
    $query = qq|SELECT j.*, to_char(j.checkedin, 'HH24:MI:SS') AS checkedina,
                to_char(j.checkedout, 'HH24:MI:SS') AS checkedouta,
        to_char(j.checkedin, '$dateformat') AS transdate,
        e.name AS employee, p.partnumber,
        pr.projectnumber, pr.description AS projectdescription,
        pr.production, pr.completed, pr.parts_id AS project
                FROM jcitems j
        JOIN employee e ON (e.id = j.employee_id)
        JOIN parts p ON (p.id = j.parts_id)
        JOIN project pr ON (pr.id = j.project_id)
                WHERE j.id = $form->{id}|;
    $sth = $dbh->prepare($query);
    $sth->execute || $form->dberror($query);

    $ref = $sth->fetchrow_hashref(NAME_lc);
    
    for (keys %$ref) { $form->{$_} = $ref->{$_} }
    $sth->finish;
    $form->{project} = ($form->{project}) ? "job" : "project";
    for (qw(checkedin checkedout)) {
      $form->{$_} = $form->{"${_}a"};
      delete $form->{"${_}a"};
    }

    $query = qq|SELECT s.printed, s.spoolfile, s.formname
                FROM status s
        WHERE s.formname = '$form->{type}'
        AND s.trans_id = $form->{id}|;
    $sth = $dbh->prepare($query);
    $sth->execute || $form->dberror($query);

    while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
      $form->{printed} .= "$ref->{formname} " if $ref->{printed};
      $form->{queued} .= "$ref->{formname} $ref->{spoolfile} " if $ref->{spoolfile};
    }
    $sth->finish;
    for (qw(printed queued)) { $form->{$_} =~ s/ +$//g }
  }
  
  JC->jcitems_links($myconfig, $form, $dbh);
 
  # get language codes
  $query = qq|SELECT *
              FROM language
          ORDER BY 2|;
  $sth = $dbh->prepare($query);
  $sth->execute || $form->dberror($query);

  $form->{all_language} = ();
  while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
    push @{ $form->{all_language} }, $ref;
  }
  $sth->finish;
  
  $dbh->disconnect;

}


sub jcitems_links {
  my ($self, $myconfig, $form, $dbh) = @_;
  
  my $disconnect = 0;

  if (! $dbh) {
    $dbh = $form->dbconnect($myconfig);
    $disconnect = 1;
  }
  
  my $query;

  if ($form->{project_id}) {
    $form->{orphaned} = 1;
    $query = qq|SELECT parts_id
                FROM project
            WHERE id = $form->{project_id}|;
    if ($dbh->selectrow_array($query)) {
      $form->{project} = 'job';
      $query = qq|SELECT id
                  FROM project
              WHERE parts_id > 0
              AND production > completed
              AND id = $form->{project_id}|;
      ($form->{orphaned}) = $dbh->selectrow_array($q);
    } else {
      $form->{project} = 'project';
    }
  }

  JC->jcparts($myconfig, $form, $dbh);
   
  $form->all_employees($myconfig, $dbh, $form->{transdate});
  
  my $where;
  
  if ($form->{transdate}) {
    $where .= qq| AND (enddate IS NULL
                       OR enddate >= '$form->{transdate}')
                  AND (startdate <= '$form->{transdate}'
               OR startdate IS NULL)|;
  }
  
  if ($form->{project} eq 'job') {
    $query = qq|
         SELECT pr.*
         FROM project pr
         WHERE pr.parts_id > 0
         AND pr.production > pr.completed
         $where|;
  } elsif ($form->{project} eq 'project') {
    $query = qq|
         SELECT pr.*
         FROM project pr
         WHERE pr.parts_id IS NULL
         $where|;
  } else {
    $query = qq|
             SELECT pr.*
         FROM project pr
         WHERE 1=1
         $where
         EXCEPT
         SELECT pr.*
         FROM project pr
         WHERE pr.parts_id > 0
         AND pr.production = pr.completed|;
  }

  if ($form->{project_id}) {
    $query .= qq|
                 UNION
         SELECT *
         FROM project
         WHERE id = $form->{project_id}|;
  }
  
  $query .= qq|
                 ORDER BY projectnumber|;

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


sub jcparts {
  my ($self, $myconfig, $form, $dbh) = @_;
  
  my ($null, $project_id) = split /--/, $form->{projectnumber};
  $project_id *= 1;

  my $query = qq|SELECT customer_id
                 FROM project
         WHERE id = $project_id|;
  my ($customer_id) = $dbh->selectrow_array($query);
  $customer_id *= 1;
  
  my $where;

  if ($form->{project} eq 'job') {
    $where = " AND p.income_accno_id IS NULL";
    if ($form->{type} eq 'storescard') {
      $where = " AND p.inventory_accno_id > 0
                 AND p.income_accno_id > 0";
    }
    
    $query = qq|SELECT p.id, p.partnumber, p.description, p.sellprice,
         p.unit, t.description AS translation
                 FROM parts p
         LEFT JOIN translation t ON (t.trans_id = p.id AND t.language_code = '$form->{language_code}')
             WHERE p.obsolete = '0'
         $where|;
  } elsif ($form->{project} eq 'project') {
    $where = " AND p.inventory_accno_id IS NULL";
    if ($form->{type} eq 'storescard') {
      $where = " AND p.inventory_accno_id > 0";
    }
    
    $query = qq|SELECT p.id, p.partnumber, p.description, p.sellprice,
         p.unit, t.description AS translation 
         FROM parts p 
         LEFT JOIN translation t ON (t.trans_id = p.id AND t.language_code = '$form->{language_code}')
         WHERE p.obsolete = '0'
         AND p.assembly = '0'
         $where|;
  } else {
    
    $query = qq|SELECT p.id, p.partnumber, p.description, p.sellprice,
         p.unit, t.description AS translation
                 FROM parts p
         LEFT JOIN translation t ON (t.trans_id = p.id AND t.language_code = '$form->{language_code}')
             WHERE p.obsolete = '0'
         AND p.income_accno_id IS NULL
         UNION
                 SELECT p.id, p.partnumber, p.description, p.sellprice,
         p.unit, t.description AS translation 
         FROM parts p 
         LEFT JOIN translation t ON (t.trans_id = p.id AND t.language_code = '$form->{language_code}')
         WHERE p.obsolete = '0'
         AND p.assembly = '0'
         AND p.inventory_accno_id IS NULL|;
  }

  $query .= qq|
         ORDER BY 2|;
  my $sth = $dbh->prepare($query);
  $sth->execute || $form->dberror($query);

  my $pmh = price_matrix_query($dbh, $project_id, $customer_id);
  IS::exchangerate_defaults($dbh, $form);

  while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
    $ref->{description} = $ref->{translation} if $ref->{translation};
    IS::price_matrix($pmh, $ref, $form->datetonum($form->{transdate}), 4, $form, $myconfig);
    push @{ $form->{all_parts} }, $ref;
  }
  $sth->finish;

}


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

  # connect to database
  my $dbh = $form->dbconnect_noauto($myconfig);
 
  my %audittrail = ( tablename  => 'jcitems',
                     reference  => $form->{id},
             formname   => $form->{type},
             action     => 'deleted',
             id         => $form->{id} );

  $form->audittrail($dbh, "", \%audittrail);
 
  my $query = qq|DELETE FROM jcitems
                 WHERE id = $form->{id}|;
  $dbh->do($query) || $form->dberror($query);

  # delete spool files
  $query = qq|SELECT spoolfile FROM status
              WHERE formname = '$form->{type}'
          AND trans_id = $form->{id}
          AND spoolfile IS NOT NULL|;
  my $sth = $dbh->prepare($query);
  $sth->execute || $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 formname = '$form->{type}'
          AND trans_id = $form->{id}|;
  $dbh->do($query) || $form->dberror($query);

  my $rc = $dbh->commit;

  if ($rc) {
    foreach $spoolfile (@spoolfiles) {
      unlink "$spool/$spoolfile" if $spoolfile;
    }
  }

  $dbh->disconnect;

  $rc;

}


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

  # connect to database
  my $dbh = $form->dbconnect($myconfig);
  
  my $query;
  my $where = "1 = 1";
  my $null;
  my $var;
  
  if ($form->{projectnumber}) {
    ($null, $var) = split /--/, $form->{projectnumber};
    $where .= " AND j.project_id = $var";

    $query = qq|SELECT parts_id
                FROM project
        WHERE id = $var|;
    my ($job) = $dbh->selectrow_array($query);
    $form->{project} = ($job) ? "job" : "project";
    
  }
  if ($form->{partnumber}) {
    ($null, $var) = split /--/, $form->{partnumber};
    $where .= " AND j.parts_id = $var";

    $query = qq|SELECT inventory_accno_id
                FROM parts
        WHERE id = $var|;
    my ($job) = $dbh->selectrow_array($query);
    $form->{project} = ($job) ? "job" : "project";
    
  }
  if ($form->{employee}) {
    ($null, $var) = split /--/, $form->{employee};
    $where .= " AND j.employee_id = $var";
  }
  if ($form->{open} || $form->{closed}) {
    unless ($form->{open} && $form->{closed}) {
      $where .= " AND j.qty != j.allocated" if $form->{open};
      $where .= " AND j.qty = j.allocated" if $form->{closed};
    }
  }
  
  ($form->{startdatefrom}, $form->{startdateto}) = $form->from_to($form->{year}, $form->{month}, $form->{interval}) if $form->{year} && $form->{month};
  
  $where .= " AND j.checkedin >= '$form->{startdatefrom}'" if $form->{startdatefrom};
  $where .= " AND j.checkedout < date '$form->{startdateto}' + 1" if $form->{startdateto};

  my %ordinal = ( id => 1,
                  description => 2,
          transdate => 7,
          partnumber => 9,
          projectnumber => 10,
          projectdescription => 11,
        );
  
  my @a = (transdate, projectnumber);
  my $sortorder = $form->sort_order(\@a, \%ordinal);
  
  my $dateformat = $myconfig->{dateformat};
  $dateformat =~ s/yy$/yyyy/;
  $dateformat =~ s/yyyyyy/yyyy/;
  
  if ($form->{project} eq 'job') {
    if ($form->{type} eq 'timecard') {
      $where .= " AND pr.parts_id > 0
                  AND p.income_accno_id IS NULL";
    }
      
    if ($form->{type} eq 'storescard') {
      $where .= " AND pr.parts_id > 0
                  AND p.income_accno_id > 0";
    }
  }
  if ($form->{project} eq 'project') {
    $where .= " AND pr.parts_id IS NULL";
  }
  
  $query = qq|SELECT j.id, j.description, j.qty, j.allocated,
          to_char(j.checkedin, 'HH24:MI') AS checkedin,
          to_char(j.checkedout, 'HH24:MI') AS checkedout,
          to_char(j.checkedin, 'yyyymmdd') AS transdate,
          to_char(j.checkedin, '$dateformat') AS transdatea,
          to_char(j.checkedin, 'D') AS weekday,
          p.partnumber,
          pr.projectnumber, pr.description AS projectdescription,
          e.employeenumber, e.name AS employee,
          to_char(j.checkedin, 'WW') AS workweek, pr.parts_id,
          j.sellprice
          FROM jcitems j
          JOIN parts p ON (p.id = j.parts_id)
          JOIN project pr ON (pr.id = j.project_id)
          JOIN employee e ON (e.id = j.employee_id)
          WHERE $where
          ORDER BY employee, employeenumber, $sortorder|;

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

  while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
    $ref->{project} = ($ref->{parts_id}) ? "job" : "project";
    $ref->{transdate} = $ref->{transdatea};
    delete $ref->{transdatea};
    push @{ $form->{transactions} }, $ref;
  }
  $sth->finish;
  
  $dbh->disconnect;

}


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

  my $query;
  my $sth;
  
  my ($null, $project_id) = split /--/, $form->{projectnumber};

  if ($form->{id}) {
    # check if it was a job
    $query = qq|SELECT pr.parts_id, pr.production - pr.completed
        FROM project pr
        JOIN jcitems j ON (j.project_id = pr.id)
        WHERE j.id = $form->{id}|;
    my ($job_id, $qty) = $dbh->selectrow_array($query);

    if ($job_id && $qty == 0) {
      $dbh->disconnect;
      return -1;
    }
    
    # check if new one belongs to a job
    if ($project_id) {
      $query = qq|SELECT pr.parts_id, pr.production - pr.completed
          FROM project pr
          WHERE pr.id = $project_id|;
      my ($job_id, $qty) = $dbh->selectrow_array($query);

      if ($job_id && $qty == 0) {
    $dbh->disconnect;
    return -2;
      }
    }
    
  } else {
    my $uid = localtime;
    $uid .= "$$";

    $query = qq|INSERT INTO jcitems (description)
                VALUES ('$uid')|;
    $dbh->do($query) || $form->dberror($query);

    $query = qq|SELECT id FROM jcitems
                WHERE description = '$uid'|;
    ($form->{id}) = $dbh->selectrow_array($query);
  }

  for (qw(inhour inmin insec outhour outmin outsec)) { $form->{$_} = substr("00$form->{$_}", -2) }
  for (qw(qty sellprice allocated)) { $form->{$_} = $form->parse_amount($myconfig, $form->{$_}) }

  my $checkedin = "$form->{inhour}$form->{inmin}$form->{insec}";
  my $checkedout = "$form->{outhour}$form->{outmin}$form->{outsec}";
  
  my $outdate = $form->{transdate};
  if ($checkedout < $checkedin) {
    $outdate = $form->add_date($myconfig, $form->{transdate}, 1, 'days');
  }

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

  my $parts_id;
  ($null, $parts_id) = split /--/, $form->{partnumber};
  
  $query = qq|UPDATE jcitems SET
              project_id = $project_id,
          parts_id = $parts_id,
          description = |.$dbh->quote($form->{description}).qq|,
          qty = $form->{qty},
          allocated = $form->{allocated},
          sellprice = $form->{sellprice},
          fxsellprice = $form->{sellprice},
          serialnumber = |.$dbh->quote($form->{serialnumber}).qq|,
          checkedin = timestamp '$form->{transdate} $form->{inhour}:$form->{inmin}:$form->{insec}',
          checkedout = timestamp '$outdate $form->{outhour}:$form->{outmin}:$form->{outsec}',
          employee_id = $form->{employee_id},
          notes = |.$dbh->quote($form->{notes}).qq|
          WHERE id = $form->{id}|;
  $dbh->do($query) || $form->dberror($query);

  # save printed, queued
  $form->save_status($dbh);

  my %audittrail = ( tablename  => 'jcitems',
                     reference  => $form->{id},
             formname   => $form->{type},
             action     => 'saved',
             id         => $form->{id} );

  $form->audittrail($dbh, "", \%audittrail);
  
  my $rc = $dbh->commit;
  
  $rc;

}


sub price_matrix_query {
  my ($dbh, $project_id, $customer_id) = @_;
  
  my $query = qq|SELECT p.id AS parts_id, 0 AS customer_id, 0 AS pricegroup_id,
              0 AS pricebreak, p.sellprice, NULL AS validfrom, NULL AS validto,
          (SELECT substr(curr,1,3) FROM defaults) AS curr, '' AS pricegroup
              FROM parts p
          WHERE p.id = ?

          UNION
  
              SELECT p.*, g.pricegroup
              FROM partscustomer p
          LEFT JOIN pricegroup g ON (g.id = p.pricegroup_id)
          WHERE p.parts_id = ?
          AND p.customer_id = $customer_id
          
          UNION

          SELECT p.*, g.pricegroup 
          FROM partscustomer p 
          LEFT JOIN pricegroup g ON (g.id = p.pricegroup_id)
          JOIN customer c ON (c.pricegroup_id = g.id)
          WHERE p.parts_id = ?
          AND c.id = $customer_id
          
          UNION

          SELECT p.*, '' AS pricegroup
          FROM partscustomer p
          WHERE p.customer_id = 0
          AND p.pricegroup_id = 0
          AND p.parts_id = ?

          ORDER BY customer_id DESC, pricegroup_id DESC, pricebreak
          
          |;
  my $sth = $dbh->prepare($query) || $form->dberror($query);

  $sth;

}


1;