[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
SF.net SVN: ledger-smb: [452] trunk
- Subject: SF.net SVN: ledger-smb: [452] trunk
- From: ..hidden..
- Date: Wed, 01 Nov 2006 12:06:18 -0800
Revision: 452
http://svn.sourceforge.net/ledger-smb/?rev=452&view=rev
Author: einhverfr
Date: 2006-11-01 12:06:15 -0800 (Wed, 01 Nov 2006)
Log Message:
-----------
Whitespace formatted and audited JC.pm
Modified Paths:
--------------
trunk/Changelog
trunk/LedgerSMB/JC.pm
Modified: trunk/Changelog
===================================================================
--- trunk/Changelog 2006-11-01 16:19:41 UTC (rev 451)
+++ trunk/Changelog 2006-11-01 20:06:15 UTC (rev 452)
@@ -16,7 +16,7 @@
* Audited IS.pm, GL.pm, IR.pm for SQL injection and moved to new API. (Chris T)
* Audited User.pm for SQL injection. (Chris T)
* Audited HR.pm, removed old, stale payroll code, moved to new API (Chris T)
-* Audited OP.pm, IC.pm and moved to new API (Chris T)
+* Audited OP.pm, JC.pm IC.pm and moved to new API (Chris T)
Localization:
* Moved localization files to standard codes (Seneca)
Modified: trunk/LedgerSMB/JC.pm
===================================================================
--- trunk/LedgerSMB/JC.pm 2006-11-01 16:19:41 UTC (rev 451)
+++ trunk/LedgerSMB/JC.pm 2006-11-01 20:06:15 UTC (rev 452)
@@ -23,7 +23,7 @@
#
#======================================================================
#
-# This file has NOT undergone whitespace cleanup.
+# This file has undergone whitespace cleanup.
#
#======================================================================
#
@@ -40,517 +40,588 @@
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);
+ 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);
+ $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"};
- }
+ 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);
+ $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 }
- }
+ 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);
+ # 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;
+ $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;
+ 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';
- }
- }
+ 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});
- JC->jcparts($myconfig, $form, $dbh);
+ 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} 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}|;
- }
+ if ($form->{project_id}) {
+ $query .= qq|
+ UNION
+ SELECT *
+ FROM project
+ WHERE id = |.$dbh->quote($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 ($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 *= 1;
+ 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;
+ 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";
- }
+ 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|
+ 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|
+ $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} );
+ my %audittrail = (
+ tablename => 'jcitems',
+ reference => $form->{id},
+ formname => $form->{type},
+ action => 'deleted',
+ id => $form->{id} );
- $form->audittrail($dbh, "", \%audittrail);
+ $form->audittrail($dbh, "", \%audittrail);
- my $query = qq|DELETE FROM jcitems
- WHERE id = $form->{id}|;
- $dbh->do($query) || $form->dberror($query);
+ 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 = '$form->{type}'
- AND trans_id = $form->{id}
- AND spoolfile IS NOT NULL|;
- my $sth = $dbh->prepare($query);
- $sth->execute || $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 = ();
+ my $spoolfile;
+ my @spoolfiles = ();
- while (($spoolfile) = $sth->fetchrow_array) {
- push @spoolfiles, $spoolfile;
- }
- $sth->finish;
+ 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);
+ # 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;
+ my $rc = $dbh->commit;
- if ($rc) {
- foreach $spoolfile (@spoolfiles) {
- unlink "${LedgerSMB::Sysconfig::spool}/$spoolfile" if $spoolfile;
- }
- }
+ if ($rc) {
+ foreach $spoolfile (@spoolfiles) {
+ unlink "${LedgerSMB::Sysconfig::spool}/$spoolfile"
+ if $spoolfile;
+ }
+ }
- $dbh->disconnect;
+ $dbh->{commit};
- $rc;
+ $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";
+ 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";
+ $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";
+ }
+ 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";
+ $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};
+ $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 %ordinal = (
+ id => 1,
+ description => 2,
+ transdate => 7,
+ partnumber => 9,
+ projectnumber => 10,
+ projectdescription => 11,
);
- my @a = (transdate, projectnumber);
- my $sortorder = $form->sort_order(..hidden.., \%ordinal);
+ my @a = (transdate, projectnumber);
+ my $sortorder = $form->sort_order(..hidden.., \%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|;
+ $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 || $form->dberror($query);
+ $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;
+ 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};
+ 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;
- }
+ 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);
+ # 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;
- }
- }
+ if ($job_id && $qty == 0) {
+ $dbh->disconnect;
+ return -2;
+ }
+ }
- } else {
- my $uid = localtime;
- $uid .= "$$";
+ } else {
+ my $uid = localtime;
+ $uid .= "$$";
- $query = qq|INSERT INTO jcitems (description)
- VALUES ('$uid')|;
- $dbh->do($query) || $form->dberror($query);
+ $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);
- }
+ $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->{$_}) }
+ 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 $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');
- }
+ 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);
- }
+ ($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 $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);
+ $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);
+ # save printed, queued
+ $form->save_status($dbh);
- my %audittrail = ( tablename => 'jcitems',
- reference => $form->{id},
- formname => $form->{type},
- action => 'saved',
- id => $form->{id} );
+ my %audittrail = (
+ tablename => 'jcitems',
+ reference => $form->{id},
+ formname => $form->{type},
+ action => 'saved',
+ id => $form->{id} );
- $form->audittrail($dbh, "", \%audittrail);
+ $form->audittrail($dbh, "", \%audittrail);
- my $rc = $dbh->commit;
+ my $rc = $dbh->commit;
- $rc;
+ $rc;
}
This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site.