path: root/LedgerSMB/JC.pm

#=====================================================================
# 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) 2005
#
#  Author: DWS Systems Inc.
#     Web: http://www.sql-ledger.org
#
#  Contributors:
#
#======================================================================
#
# This file has undergone whitespace cleanup.
#
#======================================================================
#
# Job Costing
#
#======================================================================

package JC;

use LedgerSMB::IS;
use LedgerSMB::PriceMatrix;
use LedgerSMB::Sysconfig;

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

    # connect to database
    my $dbh = $form->{dbh};

    my $query = qq|SELECT current_date|;
    ( $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, ?) 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 = ?|;
        $sth = $dbh->prepare($query);
        $sth->execute( $dateformat, $form->{id} )
          || $form->dberror($query);

        $ref = $sth->fetchrow_hashref(NAME_lc);
        $form->db_parse_numeric(sth=>$sth, hashref=>$ref);

        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 = ?
                   AND s.trans_id = ?|;
        $sth = $dbh->prepare($query);
        $sth->execute( $form->{type}, $form->{id} )
          || $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->commit;

}

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

    my $disconnect = 0;

    if ( !$dbh ) {
        $dbh = $form->{dbh};
    }

    my $query;

    if ( $form->{project_id} ) {
        $form->{orphaned} = 1;
        $query = qq|SELECT parts_id, customer_id FROM project WHERE id = ?|;
        my $sth = $dbh->prepare($query);
        $sth->execute( $form->{project_id} );
        ($parts_id, $form->{customer_id} ) = $sth->fetchrow_array ;
        if ( $parts_id ) {
            $form->{project} = 'job';
            $query = qq|
                SELECT id
                  FROM project
                 WHERE parts_id > 0
                       AND production > completed
                       AND id = $form->{project_id}|;
            my $sth = $dbh->prepare($query);
            $sth->execute( $form->{project_id} );
            ( $form->{orphaned} ) = $sth->fetchrow_array();
            $sth->finish;
        }
        else {
            $form->{project} = 'project';
        }
        $sth->finish;
    }

    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 >= | . $dbh->quote( $form->{transdate} ) . qq|)
            AND (startdate <= | . $dbh->quote( $form->{transdate} ) . qq|
                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 = | . $dbh->quote( $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;
}

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

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

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

    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 
                      = | . $dbh->quote( $form->{language_code} ) . qq|)
                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 
                      = | . $dbh->quote( $form->{language_code} ) . qq|)
                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 
                      = | . $dbh->quote( $form->{language_code} ) . qq|)
                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 
                      = | . $dbh->quote( $form->{language_code} ) . qq|)
                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 = PriceMatrix::price_matrix_query( $dbh, $form );
    IS::exchangerate_defaults( $dbh, $form );

    while ( my $ref = $sth->fetchrow_hashref(NAME_lc) ) {
        $ref->{description} = $ref->{translation}
          if $ref->{translation};
        PriceMatrix::price_matrix( $pmh, $ref, $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->{dbh};

    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 = ?|;
    my $sth   = $dbh->prepare($query);
    $sth->execute( $form->{id} ) || $form->dberror($query);

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

    my $rc = $dbh->commit;

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

    $dbh->{commit};

    $rc;

}

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

    # connect to database
    my $dbh = $form->{dbh};

    my $query;
    my $where = "1 = 1";
    my $null;
    my $var;

    if ( $form->{projectnumber} ) {
        ( $null, $var ) = split /--/, $form->{projectnumber};
        $var = $dbh->quote($var);
        $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};
        $var = $dbh->quote($var);
        $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};
        $var = $dbh->quote($var);
        $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 >= " . $dbh->quote( $form->{startdatefrom} )
      if $form->{startdatefrom};
    $where .=
      " AND j.checkedout < date " . $dbh->quote( $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, ?) 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($dateformat) || $form->dberror($query);

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

    $dbh->commit;

}

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

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

    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 = ?|;
        $sth = $dbh->prepare($query);
        $sth->execute( $form->{id} );
        my ( $job_id, $qty ) = $sth->fetchrow_array();
        $sth->finish;
        if ( $job_id && $qty == 0 ) {
            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 = ?|;
            $sth = $dbh->prepare($query);
            $sth->execute($project_id);
            my ( $job_id, $qty ) = $sth->fetchrow_array();

            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 = ?,
               parts_id = ?,
               description = ?,
               qty = ?,
               allocated = ?,
               sellprice = ?,
               fxsellprice = ?,
               serialnumber = ?,
               checkedin = ?::timestamp,
               checkedout = ?::timestamp,
               employee_id = ?,
               notes = ?
         WHERE id = ?|;
    $sth = $dbh->prepare($query);
    $sth->execute(
        $project_id,
        $parts_id,
        $form->{description},
        $form->{qty},
        $form->{allocated},
        $form->{sellprice},
        $form->{sellprice},
        $form->{serialnumber},
        "$form->{transdate} $form->{inhour}:$form->{inmin}:" . $form->{insec},
        "$outdate $form->{outhour}:$form->{outmin}:$form->{outsec}",
        $form->{employee_id},
        $form->{notes},
        $form->{id}
    ) || $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;

}

1;