From d6a3718043c92e934e9dd24edd46e346726c4f28 Mon Sep 17 00:00:00 2001 From: einhverfr Date: Wed, 1 Nov 2006 20:06:15 +0000 Subject: Whitespace formatted and audited JC.pm git-svn-id: https://ledger-smb.svn.sourceforge.net/svnroot/ledger-smb/trunk@452 4979c152-3d1c-0410-bac9-87ea11338e46 --- LedgerSMB/JC.pm | 941 ++++++++++++++++++++++++++++++-------------------------- 1 file changed, 506 insertions(+), 435 deletions(-) (limited to 'LedgerSMB') diff --git a/LedgerSMB/JC.pm b/LedgerSMB/JC.pm index fcac1b1e..3bcad40e 100755 --- a/LedgerSMB/JC.pm +++ b/LedgerSMB/JC.pm @@ -23,7 +23,7 @@ # #====================================================================== # -# This file has NOT undergone whitespace cleanup. +# This file has undergone whitespace cleanup. # #====================================================================== # @@ -40,517 +40,588 @@ use LedgerSMB::Sysconfig; sub get_jcitems { - my ($self, $myconfig, $form) = @_; + my ($self, $myconfig, $form) = @_; - # connect to database - my $dbh = $form->dbconnect($myconfig); + # connect to database + my $dbh = $form->{dbh}; - my $query = qq|SELECT current_date|; - ($form->{transdate}) = $dbh->selectrow_array($query); + my $query = qq|SELECT current_date|; + ($form->{transdate}) = $dbh->selectrow_array($query); - ($form->{employee}, $form->{employee_id}) = $form->get_employee($dbh); + ($form->{employee}, $form->{employee_id}) = $form->get_employee($dbh); - my $dateformat = $myconfig->{dateformat}; - $dateformat =~ s/yy/yyyy/; - $dateformat =~ s/yyyyyy/yyyy/; + 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); + 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); - 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 } - } + 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); + 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; + # 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; + $dbh->commit; } sub jcitems_links { - my ($self, $myconfig, $form, $dbh) = @_; + my ($self, $myconfig, $form, $dbh) = @_; - my $disconnect = 0; + my $disconnect = 0; - if (! $dbh) { - $dbh = $form->dbconnect($myconfig); - $disconnect = 1; - } + if (! $dbh) { + $dbh = $form->{dbh}; + } - 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); + my $query; + + if ($form->{project_id}) { + $form->{orphaned} = 1; + $query = qq|SELECT parts_id FROM project WHERE id = ?|; + my $sth = $dbh->prepare($query); + $sth->execute($form->{project_id}); + + if ($sth->fetchrow_array($query)) { + $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}); + $form->all_employees($myconfig, $dbh, $form->{transdate}); - my $where; + 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->{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 = |.$dbh->quote($form->{project_id}); + } - 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| + $query .= qq| ORDER BY projectnumber|; - $sth = $dbh->prepare($query); - $sth->execute || $form->dberror($query); + $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; - + while (my $ref = $sth->fetchrow_hashref(NAME_lc)) { + push @{ $form->{all_project} }, $ref; + } + $sth->finish; } sub jcparts { - my ($self, $myconfig, $form, $dbh) = @_; + 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 ($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"; - } + 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 + = |.$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 = '$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 + = |.$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 = '$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| + $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 $sth = $dbh->prepare($query); + $sth->execute || $form->dberror($query); - my $pmh = PriceMatrix::price_matrix_query($dbh, $form); - IS::exchangerate_defaults($dbh, $form); + 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; + 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) = @_; + my ($self, $myconfig, $form) = @_; - # connect to database - my $dbh = $form->dbconnect_noauto($myconfig); + # 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 %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 "${LedgerSMB::Sysconfig::spool}/$spoolfile" if $spoolfile; - } - } - - $dbh->disconnect; - - $rc; + 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) = @_; + my ($self, $myconfig, $form) = @_; - # connect to database - my $dbh = $form->dbconnect($myconfig); + # connect to database + my $dbh = $form->{dbh}; - my $query; - my $where = "1 = 1"; - my $null; - my $var; + 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->{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}; - $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->{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}; - $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}; - } - } + } + 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}; + ($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, + $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 @a = (transdate, projectnumber); + my $sortorder = $form->sort_order(\@a, \%ordinal); - my $dateformat = $myconfig->{dateformat}; - $dateformat =~ s/yy$/yyyy/; - $dateformat =~ s/yyyyyy/yyyy/; + 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->{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"; - } + 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; + $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)) { + $ref->{project} = ($ref->{parts_id}) ? "job" : "project"; + $ref->{transdate} = $ref->{transdatea}; + delete $ref->{transdatea}; + push @{ $form->{transactions} }, $ref; + } + $sth->finish; - $dbh->disconnect; + $dbh->commit; } sub save { - my ($self, $myconfig, $form) = @_; + my ($self, $myconfig, $form) = @_; - # connect to database - my $dbh = $form->dbconnect_noauto($myconfig); + my $dbh = $form->{dbh}; - my $query; - my $sth; + 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; - } + 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 = $project_id|; - my ($job_id, $qty) = $dbh->selectrow_array($query); - - if ($job_id && $qty == 0) { - $dbh->disconnect; - return -2; - } - } + # 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}"; + } 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}; + 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); + $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; + my $rc = $dbh->commit; - $rc; + $rc; } -- cgit v1.2.3