From 69946b22549d308f6bd5285ea969b88c229ec999 Mon Sep 17 00:00:00 2001 From: einhverfr Date: Thu, 2 Nov 2006 00:21:22 +0000 Subject: PE.pm now safe git-svn-id: https://ledger-smb.svn.sourceforge.net/svnroot/ledger-smb/trunk@456 4979c152-3d1c-0410-bac9-87ea11338e46 --- Changelog | 2 +- LedgerSMB/IC.pm | 2 +- LedgerSMB/PE.pm | 2292 ++++++++++++++++++++++++++++--------------------------- 3 files changed, 1180 insertions(+), 1116 deletions(-) diff --git a/Changelog b/Changelog index 1c7914d0..25e736dc 100644 --- a/Changelog +++ b/Changelog @@ -16,7 +16,7 @@ Security: * 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, JC.pm IC.pm and moved to new API (Chris T) +* Audited OP.pm, PE,pm, JC.pm IC.pm and moved to new API (Chris T) Localization: * Moved localization files to standard codes (Seneca) diff --git a/LedgerSMB/IC.pm b/LedgerSMB/IC.pm index fb9bf3bf..c6bd908f 100755 --- a/LedgerSMB/IC.pm +++ b/LedgerSMB/IC.pm @@ -770,7 +770,7 @@ sub restock_assemblies { my ($self, $myconfig, $form) = @_; # connect to database - my $dbh = $form->dbconnect_noauto($myconfig); + my $dbh = $form->{dbh}; for my $i (1 .. $form->{rowcount}) { $form->{"qty_$i"} = $form->parse_amount( diff --git a/LedgerSMB/PE.pm b/LedgerSMB/PE.pm index 02b7b59d..2d346175 100755 --- a/LedgerSMB/PE.pm +++ b/LedgerSMB/PE.pm @@ -23,7 +23,7 @@ # #====================================================================== # -# This file has NOT undergone whitespace cleanup. +# This file has undergone whitespace cleanup. # #====================================================================== # @@ -36,54 +36,59 @@ package PE; sub projects { - my ($self, $myconfig, $form) = @_; + my ($self, $myconfig, $form) = @_; - # connect to database - my $dbh = $form->dbconnect($myconfig); + my $dbh = $form->{dbh}; - $form->{sort} = "projectnumber" unless $form->{sort}; - my @a = ($form->{sort}); - my %ordinal = ( projectnumber => 2, + $form->{sort} = "projectnumber" unless $form->{sort}; + my @a = ($form->{sort}); + my %ordinal = ( projectnumber => 2, description => 3, startdate => 4, enddate => 5, ); - my $sortorder = $form->sort_order(\@a, \%ordinal); + my $sortorder = $form->sort_order(\@a, \%ordinal); - my $query; - my $where = "WHERE 1=1"; + my $query; + my $where = "WHERE 1=1"; - $query = qq|SELECT pr.*, c.name - FROM project pr - LEFT JOIN customer c ON (c.id = pr.customer_id)|; + $query = qq| + SELECT pr.*, c.name + FROM project pr + LEFT JOIN customer c ON (c.id = pr.customer_id)|; - if ($form->{type} eq 'job') { - $where .= qq| AND pr.id NOT IN (SELECT DISTINCT id + if ($form->{type} eq 'job') { + $where .= qq| AND pr.id NOT IN (SELECT DISTINCT id FROM parts WHERE project_id > 0)|; - } - - my $var; - if ($form->{projectnumber} ne "") { - $var = $form->like(lc $form->{projectnumber}); - $where .= " AND lower(pr.projectnumber) LIKE '$var'"; - } - if ($form->{description} ne "") { - $var = $form->like(lc $form->{description}); - $where .= " AND lower(pr.description) LIKE '$var'"; - } - - ($form->{startdatefrom}, $form->{startdateto}) = $form->from_to($form->{year}, $form->{month}, $form->{interval}) if $form->{year} && $form->{month}; - - if ($form->{startdatefrom}) { - $where .= " AND (pr.startdate IS NULL OR pr.startdate >= '$form->{startdatefrom}')"; - } - if ($form->{startdateto}) { - $where .= " AND (pr.startdate IS NULL OR pr.startdate <= '$form->{startdateto}')"; - } - - if ($form->{status} eq 'orphaned') { - $where .= qq| AND pr.id NOT IN (SELECT DISTINCT project_id + } + + my $var; + if ($form->{projectnumber} ne "") { + $var = $dbh->quote($form->like(lc $form->{projectnumber})); + $where .= " AND lower(pr.projectnumber) LIKE $var"; + } + if ($form->{description} ne "") { + $var = $dbh->quote($form->like(lc $form->{description})); + $where .= " AND lower(pr.description) LIKE $var"; + } + + ($form->{startdatefrom}, $form->{startdateto}) + = $form->from_to( + $form->{year}, $form->{month}, $form->{interval}) + if $form->{year} && $form->{month}; + + if ($form->{startdatefrom}) { + $where .= " AND (pr.startdate IS NULL OR pr.startdate >= ". + $dbh->quote($form->{startdatefrom}).")"; + } + if ($form->{startdateto}) { + $where .= " AND (pr.startdate IS NULL OR pr.startdate <= ". + $dbh->quote($form->{startdateto}).")"; + } + + if ($form->{status} eq 'orphaned') { + $where .= qq| AND pr.id NOT IN (SELECT DISTINCT project_id FROM acc_trans WHERE project_id > 0 UNION @@ -100,29 +105,31 @@ sub projects { WHERE project_id > 0) |; - } - if ($form->{status} eq 'active') { - $where .= qq| AND (pr.enddate IS NULL OR pr.enddate >= current_date)|; - } - if ($form->{status} eq 'inactive') { - $where .= qq| AND pr.enddate <= current_date|; - } + } + if ($form->{status} eq 'active') { + $where .= qq| + AND (pr.enddate IS NULL + OR pr.enddate >= current_date)|; + } + if ($form->{status} eq 'inactive') { + $where .= qq| AND pr.enddate <= current_date|; + } - $query .= qq| + $query .= qq| $where ORDER BY $sortorder|; - $sth = $dbh->prepare($query); - $sth->execute || $form->dberror($query); + $sth = $dbh->prepare($query); + $sth->execute || $form->dberror($query); - my $i = 0; - while (my $ref = $sth->fetchrow_hashref(NAME_lc)) { - push @{ $form->{all_project} }, $ref; - $i++; - } + my $i = 0; + while (my $ref = $sth->fetchrow_hashref(NAME_lc)) { + push @{ $form->{all_project} }, $ref; + $i++; + } $sth->finish; - $dbh->disconnect; + $dbh->commit; $i; @@ -130,1242 +137,1293 @@ sub projects { sub get_project { - my ($self, $myconfig, $form) = @_; + my ($self, $myconfig, $form) = @_; - # connect to database - my $dbh = $form->dbconnect($myconfig); + my $dbh = $form->{dbh}; - my $query; - my $sth; - my $ref; - my $where; + my $query; + my $sth; + my $ref; + my $where; - if ($form->{id}) { + if ($form->{id}) { - $where = "WHERE pr.id = $form->{id}" if $form->{id}; - $query = qq|SELECT pr.*, - c.name AS customer - FROM project pr - LEFT JOIN customer c ON (c.id = pr.customer_id) - $where|; - $sth = $dbh->prepare($query); - $sth->execute || $form->dberror($query); - - $ref = $sth->fetchrow_hashref(NAME_lc); + $query = qq| + SELECT pr.*, c.name AS customer + FROM project pr + LEFT JOIN customer c ON (c.id = pr.customer_id) + WHERE pr.id = ?|; + $sth = $dbh->prepare($query); + $sth->execute($form->{id}) || $form->dberror($query); + + $ref = $sth->fetchrow_hashref(NAME_lc); - for (keys %$ref) { $form->{$_} = $ref->{$_} } - - $sth->finish; - - # check if it is orphaned - $query = qq|SELECT count(*) - FROM acc_trans - WHERE project_id = $form->{id} - UNION - SELECT count(*) - FROM invoice - WHERE project_id = $form->{id} - UNION - SELECT count(*) - FROM orderitems - WHERE project_id = $form->{id} - UNION - SELECT count(*) - FROM jcitems - WHERE project_id = $form->{id} - |; - $sth = $dbh->prepare($query); - $sth->execute || $form->dberror($query); - - my $count; - while (($count) = $sth->fetchrow_array) { - $form->{orphaned} += $count; - } - $sth->finish; - $form->{orphaned} = !$form->{orphaned}; - } + for (keys %$ref) { $form->{$_} = $ref->{$_} } + + $sth->finish; + + # check if it is orphaned + $query = qq| + SELECT count(*) + FROM acc_trans + WHERE project_id = ? + UNION + SELECT count(*) + FROM invoice + WHERE project_id = ? + UNION + SELECT count(*) + FROM orderitems + WHERE project_id = ? + UNION + SELECT count(*) + FROM jcitems + WHERE project_id = ?|; + $sth = $dbh->prepare($query); + $sth->execute( + $form->{id}, $form->{id}, $form->{id}, $form->{id} + )|| $form->dberror($query); + + my $count; + while (($count) = $sth->fetchrow_array) { + $form->{orphaned} += $count; + } + $sth->finish; + $form->{orphaned} = !$form->{orphaned}; + } - PE->get_customer($myconfig, $form, $dbh); + PE->get_customer($myconfig, $form, $dbh); - $form->run_custom_queries('project', 'SELECT'); + $form->run_custom_queries('project', 'SELECT'); - $dbh->disconnect; + $dbh->commit; } sub save_project { - my ($self, $myconfig, $form) = @_; + my ($self, $myconfig, $form) = @_; - # connect to database - my $dbh = $form->dbconnect($myconfig); + my $dbh = $form->{dbh}; - $form->{customer_id} ||= 'NULL'; + $form->{customer_id} ||= 'NULL'; - $form->{projectnumber} = $form->update_defaults($myconfig, "projectnumber", $dbh) unless $form->{projectnumber}; + $form->{projectnumber} + = $form->update_defaults($myconfig, "projectnumber", $dbh) + unless $form->{projectnumber}; - if ($form->{id}) { + if ($form->{id}) { - $query = qq|UPDATE project SET - projectnumber = |.$dbh->quote($form->{projectnumber}).qq|, - description = |.$dbh->quote($form->{description}).qq|, - startdate = |.$form->dbquote($form->{startdate}, SQL_DATE).qq|, - enddate = |.$form->dbquote($form->{enddate}, SQL_DATE).qq|, - customer_id = $form->{customer_id} - WHERE id = $form->{id}|; - } else { + $query = qq| + UPDATE project + SET projectnumber = ?, + description = ?, + startdate = ?, + enddate = ?, + customer_id = ? + WHERE id = |.$dbh->quote($form->{id}); + } else { - $query = qq|INSERT INTO project - (projectnumber, description, startdate, enddate, customer_id) - VALUES (| - .$dbh->quote($form->{projectnumber}).qq|, | - .$dbh->quote($form->{description}).qq|, | - .$form->dbquote($form->{startdate}, SQL_DATE).qq|, | - .$form->dbquote($form->{enddate}, SQL_DATE).qq|, - $form->{customer_id} - )|; - } - $dbh->do($query) || $form->dberror($query); - $form->run_custom_queries('project', 'UPDATE'); + $query = qq| + INSERT INTO project (projectnumber, description, + startdate, enddate, customer_id) + VALUES (?, ?, ?, ?, ?)|; + } + $sth = $dbh->prepare($query); + $sth->execute( + $form->{projectnumber}, $form->{description}, + $form->{startdate}, $form->{enddate}, $form->{customer_id} + ) || $form->dberror($query); + $form->run_custom_queries('project', 'UPDATE'); - $dbh->disconnect; + $dbh->commit; } sub list_stock { - my ($self, $myconfig, $form) = @_; + my ($self, $myconfig, $form) = @_; - # connect to database - my $dbh = $form->dbconnect($myconfig); + my $dbh = $form->{dbh}; + + my $var; + my $where = "1 = 1"; - my $var; - my $where = "1 = 1"; - - if ($form->{status} eq 'active') { - $where = qq|(pr.enddate IS NULL - OR pr.enddate >= current_date) - AND pr.completed < pr.production|; - } - if ($form->{status} eq 'inactive') { - $where = qq|pr.completed = pr.production|; - } + if ($form->{status} eq 'active') { + $where = qq| + (pr.enddate IS NULL OR pr.enddate >= current_date) + AND pr.completed < pr.production|; + } + if ($form->{status} eq 'inactive') { + $where = qq|pr.completed = pr.production|; + } - if ($form->{projectnumber}) { - $var = $form->like(lc $form->{projectnumber}); - $where .= " AND lower(pr.projectnumber) LIKE '$var'"; - } - - if ($form->{description}) { - $var = $form->like(lc $form->{description}); - $where .= " AND lower(pr.description) LIKE '$var'"; - } - - $form->{sort} = "projectnumber" unless $form->{sort}; - my @a = ($form->{sort}); - my %ordinal = ( projectnumber => 2, - description => 3 - ); - my $sortorder = $form->sort_order(\@a, \%ordinal); + if ($form->{projectnumber}) { + $var = $dbh->quote($form->like(lc $form->{projectnumber})); + $where .= " AND lower(pr.projectnumber) LIKE $var"; + } + + if ($form->{description}) { + $var = $dbh->quote($form->like(lc $form->{description})); + $where .= " AND lower(pr.description) LIKE $var"; + } + + $form->{sort} = "projectnumber" unless $form->{sort}; + my @a = ($form->{sort}); + my %ordinal = ( projectnumber => 2, description => 3 ); + my $sortorder = $form->sort_order(\@a, \%ordinal); - my $query = qq|SELECT pr.*, p.partnumber - FROM project pr - JOIN parts p ON (p.id = pr.parts_id) - WHERE $where - ORDER BY $sortorder|; - - $sth = $dbh->prepare($query); - $sth->execute || $form->dberror($query); - - while (my $ref = $sth->fetchrow_hashref(NAME_lc)) { - push @{ $form->{all_project} }, $ref; - } - $sth->finish; + my $query = qq| + SELECT pr.*, p.partnumber + FROM project pr + JOIN parts p ON (p.id = pr.parts_id) + WHERE $where + ORDER BY $sortorder|; - $query = qq|SELECT current_date|; - ($form->{stockingdate}) = $dbh->selectrow_array($query) if !$form->{stockingdate}; + $sth = $dbh->prepare($query); + $sth->execute || $form->dberror($query); + + while (my $ref = $sth->fetchrow_hashref(NAME_lc)) { + push @{ $form->{all_project} }, $ref; + } + $sth->finish; + + $query = qq|SELECT current_date|; + ($form->{stockingdate}) = $dbh->selectrow_array($query) + if !$form->{stockingdate}; - $dbh->disconnect; + $dbh->commit; } sub jobs { - my ($self, $myconfig, $form) = @_; + my ($self, $myconfig, $form) = @_; - # connect to database - my $dbh = $form->dbconnect($myconfig); + my $dbh = $form->{dbh}; - $form->{sort} = "projectnumber" unless $form->{sort}; - my @a = ($form->{sort}); - my %ordinal = ( projectnumber => 2, - description => 3, - startdate => 4, - ); - my $sortorder = $form->sort_order(\@a, \%ordinal); - - my $query = qq|SELECT pr.*, p.partnumber, p.onhand, c.name - FROM project pr - JOIN parts p ON (p.id = pr.parts_id) - LEFT JOIN customer c ON (c.id = pr.customer_id) - WHERE 1=1|; - - if ($form->{projectnumber} ne "") { - $var = $form->like(lc $form->{projectnumber}); - $query .= " AND lower(pr.projectnumber) LIKE '$var'"; - } - if ($form->{description} ne "") { - $var = $form->like(lc $form->{description}); - $query .= " AND lower(pr.description) LIKE '$var'"; - } - - ($form->{startdatefrom}, $form->{startdateto}) = $form->from_to($form->{year}, $form->{month}, $form->{interval}) if $form->{year} && $form->{month}; - - if ($form->{startdatefrom}) { - $query .= " AND pr.startdate >= '$form->{startdatefrom}'"; - } - if ($form->{startdateto}) { - $query .= " AND pr.startdate <= '$form->{startdateto}'"; - } - - if ($form->{status} eq 'active') { - $query .= qq| AND NOT pr.production = pr.completed|; - } - if ($form->{status} eq 'inactive') { - $query .= qq| AND pr.production = pr.completed|; - } - if ($form->{status} eq 'orphaned') { - $query .= qq| AND pr.completed = 0 - AND (pr.id NOT IN SELECT DISTINCT project_id - FROM invoice - WHERE project_id > 0) - UNION - SELECT DISTINCT project_id - FROM orderitems - WHERE project_id > 0 - SELECT DISTINCT project_id - FROM jcitems - WHERE project_id > 0 - )|; - } + $form->{sort} = "projectnumber" unless $form->{sort}; + my @a = ($form->{sort}); + my %ordinal = (projectnumber => 2, description => 3, startdate => 4); + my $sortorder = $form->sort_order(\@a, \%ordinal); + + my $query = qq| + SELECT pr.*, p.partnumber, p.onhand, c.name + FROM project pr + JOIN parts p ON (p.id = pr.parts_id) + LEFT JOIN customer c ON (c.id = pr.customer_id) + WHERE 1=1|; - $query .= qq| - ORDER BY $sortorder|; + if ($form->{projectnumber} ne "") { + $var = $dbh->quote($form->like(lc $form->{projectnumber})); + $query .= " AND lower(pr.projectnumber) LIKE $var"; + } + if ($form->{description} ne "") { + $var = $dbh->quote($form->like(lc $form->{description})); + $query .= " AND lower(pr.description) LIKE $var"; + } - $sth = $dbh->prepare($query); - $sth->execute || $form->dberror($query); + ($form->{startdatefrom}, $form->{startdateto}) + = $form->from_to($form->{year}, $form->{month}, + $form->{interval}) + if $form->{year} && $form->{month}; + + if ($form->{startdatefrom}) { + $query .= " AND pr.startdate >= ". + $dbh->quote($form->{startdatefrom}); + } + if ($form->{startdateto}) { + $query .= " AND pr.startdate <= ". + $dbh->quote($form->{startdateto}); + } + + if ($form->{status} eq 'active') { + $query .= qq| AND NOT pr.production = pr.completed|; + } + if ($form->{status} eq 'inactive') { + $query .= qq| AND pr.production = pr.completed|; + } + if ($form->{status} eq 'orphaned') { + $query .= qq| + AND pr.completed = 0 + AND (pr.id NOT IN + (SELECT DISTINCT project_id + FROM invoice + WHERE project_id > 0 + UNION + SELECT DISTINCT project_id + FROM orderitems + WHERE project_id > 0 + UNION + SELECT DISTINCT project_id + FROM jcitems + WHERE project_id > 0) + )|; + } + + $query .= qq| + ORDER BY $sortorder|; - while (my $ref = $sth->fetchrow_hashref(NAME_lc)) { - push @{ $form->{all_project} }, $ref; - } + $sth = $dbh->prepare($query); + $sth->execute || $form->dberror($query); - $sth->finish; + while (my $ref = $sth->fetchrow_hashref(NAME_lc)) { + push @{ $form->{all_project} }, $ref; + } + + $sth->finish; - $dbh->disconnect; + $dbh->commit; } sub get_job { - my ($self, $myconfig, $form) = @_; - - # connect to database - my $dbh = $form->dbconnect($myconfig); - - my $query; - my $sth; - my $ref; - - if ($form->{id}) { - $query = qq|SELECT value FROM defaults WHERE setting_key = 'weightunit'|; - ($form->{weightunit}) = $dbh->selectrow_array($query); - - $query = qq|SELECT pr.*, - p.partnumber, p.description AS partdescription, p.unit, p.listprice, - p.sellprice, p.priceupdate, p.weight, p.notes, p.bin, - p.partsgroup_id, - ch.accno AS income_accno, ch.description AS income_description, - pr.customer_id, c.name AS customer, - pg.partsgroup - FROM project pr - LEFT JOIN parts p ON (p.id = pr.parts_id) - LEFT JOIN chart ch ON (ch.id = p.income_accno_id) - LEFT JOIN customer c ON (c.id = pr.customer_id) - LEFT JOIN partsgroup pg ON (pg.id = p.partsgroup_id) - WHERE pr.id = $form->{id}|; - } else { - $query = qq| - SELECT value, current_date AS startdate FROM defaults - WHERE setting_key = 'weightunit'|; - } - - $sth = $dbh->prepare($query); - $sth->execute || $form->dberror($query); - - $ref = $sth->fetchrow_hashref(NAME_lc); - - for (keys %$ref) { $form->{$_} = $ref->{$_} } + my ($self, $myconfig, $form) = @_; + + # connect to database + my $dbh = $form->{dbh}; + + my $query; + my $sth; + my $ref; + + if ($form->{id}) { + $query = qq| + SELECT value FROM defaults + WHERE setting_key = 'weightunit'|; + ($form->{weightunit}) = $dbh->selectrow_array($query); + + $query = qq| + SELECT pr.*, p.partnumber, + p.description AS partdescription, p.unit, + p.listprice, p.sellprice, p.priceupdate, + p.weight, p.notes, p.bin, p.partsgroup_id, + ch.accno AS income_accno, + ch.description AS income_description, + pr.customer_id, c.name AS customer, + pg.partsgroup + FROM project pr + LEFT JOIN parts p ON (p.id = pr.parts_id) + LEFT JOIN chart ch ON (ch.id = p.income_accno_id) + LEFT JOIN customer c ON (c.id = pr.customer_id) + LEFT JOIN partsgroup pg ON (pg.id = p.partsgroup_id) + WHERE pr.id = |.$dbh->quote($form->{id}); + } else { + $query = qq| + SELECT value, current_date AS startdate FROM defaults + WHERE setting_key = 'weightunit'|; + } - $sth->finish; + $sth = $dbh->prepare($query); + $sth->execute || $form->dberror($query); + + $ref = $sth->fetchrow_hashref(NAME_lc); + + for (keys %$ref) { $form->{$_} = $ref->{$_} } + + $sth->finish; + + if ($form->{id}) { + # check if it is orphaned + $query = qq| + SELECT count(*) + FROM invoice + WHERE project_id = ? + UNION + SELECT count(*) + FROM orderitems + WHERE project_id = ? + UNION + SELECT count(*) + FROM jcitems + WHERE project_id = ?|; + $sth = $dbh->prepare($query); + $sth->execute( + $form->{id}, $form->{id}, $form->{id} + )|| $form->dberror($query); + + my $count; + + my $count; + while (($count) = $sth->fetchrow_array) { + $form->{orphaned} += $count; + } + $sth->finish; - if ($form->{id}) { - # check if it is orphaned - $query = qq|SELECT count(*) - FROM invoice - WHERE project_id = $form->{id} - UNION - SELECT count(*) - FROM orderitems - WHERE project_id = $form->{id} - UNION - SELECT count(*) - FROM jcitems - WHERE project_id = $form->{id} - |; - - $sth = $dbh->prepare($query); - $sth->execute || $form->dberror($query); - - my $count; - while (($count) = $sth->fetchrow_array) { - $form->{orphaned} += $count; - } - $sth->finish; - - } - - $form->{orphaned} = !$form->{orphaned}; - - $query = qq|SELECT accno, description, link - FROM chart - WHERE link LIKE '%IC%' - ORDER BY accno|; - $sth = $dbh->prepare($query); - $sth->execute || $form->dberror($query); - - while ($ref = $sth->fetchrow_hashref(NAME_lc)) { - for (split /:/, $ref->{link}) { - if (/IC/) { - push @{ $form->{IC_links}{$_} }, { accno => $ref->{accno}, - description => $ref->{description} }; - } - } - } - $sth->finish; + } - if ($form->{id}) { - $query = qq|SELECT ch.accno - FROM parts p - JOIN partstax pt ON (pt.parts_id = p.id) - JOIN chart ch ON (pt.chart_id = ch.id) - WHERE p.id = $form->{id}|; + $form->{orphaned} = !$form->{orphaned}; + + $query = qq| + SELECT accno, description, link + FROM chart + WHERE link LIKE ? + ORDER BY accno|; + $sth = $dbh->prepare($query); + $sth->execute('%IC%') || $form->dberror($query); + + while ($ref = $sth->fetchrow_hashref(NAME_lc)) { + for (split /:/, $ref->{link}) { + if (/IC/) { + push @{ $form->{IC_links}{$_} }, + { accno => $ref->{accno}, + description => $ref->{description} }; + } + } + } + $sth->finish; + + if ($form->{id}) { + $query = qq| + SELECT ch.accno + FROM parts p + JOIN partstax pt ON (pt.parts_id = p.id) + JOIN chart ch ON (pt.chart_id = ch.id) + WHERE p.id = ?|; - $sth = $dbh->prepare($query); - $sth->execute || $form->dberror($query); + $sth = $dbh->prepare($query); + $sth->execute($form->{id}) || $form->dberror($query); - while ($ref = $sth->fetchrow_hashref(NAME_lc)) { - $form->{amount}{$ref->{accno}} = $ref->{accno}; - } - $sth->finish; - } + while ($ref = $sth->fetchrow_hashref(NAME_lc)) { + $form->{amount}{$ref->{accno}} = $ref->{accno}; + } + $sth->finish; + } - PE->get_customer($myconfig, $form, $dbh); + PE->get_customer($myconfig, $form, $dbh); - $dbh->disconnect; + $dbh->commit; } sub get_customer { - my ($self, $myconfig, $form, $dbh) = @_; - - my $disconnect = 0; - - if (! $dbh) { - $dbh = $form->dbconnect($myconfig); - $disconnect = 1; - } - - my $query; - my $sth; - my $ref; - - if (! $form->{startdate}) { - $query = qq|SELECT current_date|; - ($form->{startdate}) = $dbh->selectrow_array($query); - } - - my $where = qq|(startdate >= '$form->{startdate}' OR startdate IS NULL OR enddate IS NULL)|; - - if ($form->{enddate}) { - $where .= qq| AND (enddate >= '$form->{enddate}' OR enddate IS NULL)|; - } else { - $where .= qq| AND (enddate >= current_date OR enddate IS NULL)|; - } - - $query = qq|SELECT count(*) - FROM customer - WHERE $where|; - my ($count) = $dbh->selectrow_array($query); - - if ($count < $myconfig->{vclimit}) { - $query = qq|SELECT id, name - FROM customer - WHERE $where|; - - if ($form->{customer_id}) { - $query .= qq| - UNION SELECT id,name - FROM customer - WHERE id = $form->{customer_id}|; - } - - $query .= qq| - ORDER BY name|; - $sth = $dbh->prepare($query); - $sth->execute || $form->dberror($query); - - @{ $form->{all_customer} } = (); - while ($ref = $sth->fetchrow_hashref(NAME_lc)) { - push @{ $form->{all_customer} }, $ref; - } - $sth->finish; - } - - $dbh->disconnect if $disconnect; + my ($self, $myconfig, $form, $dbh) = @_; + + if (! $dbh) { + $dbh = $form->{dbh}; + } + + my $query; + my $sth; + my $ref; + + if (! $form->{startdate}) { + $query = qq|SELECT current_date|; + ($form->{startdate}) = $dbh->selectrow_array($query); + } + + my $where = qq|(startdate >= |.$dbh->quote($form->{startdate}). + qq| OR startdate IS NULL OR enddate IS NULL)|; + + if ($form->{enddate}) { + $where .= qq| AND (enddate >= |.$dbh->quote($form->{enddate}). + qq| OR enddate IS NULL)|; + } else { + $where .= + qq| AND (enddate >= current_date OR enddate IS NULL)|; + } + + $query = qq| + SELECT count(*) + FROM customer + WHERE $where|; + my ($count) = $dbh->selectrow_array($query); + + if ($count < $myconfig->{vclimit}) { + $query = qq| + SELECT id, name + FROM customer + WHERE $where|; + + if ($form->{customer_id}) { + $query .= qq| + UNION + SELECT id,name + FROM customer + WHERE id = |. + $dbh->quote($form->{customer_id}); + } + + $query .= qq| + ORDER BY name|; + $sth = $dbh->prepare($query); + $sth->execute || $form->dberror($query); + + @{ $form->{all_customer} } = (); + while ($ref = $sth->fetchrow_hashref(NAME_lc)) { + push @{ $form->{all_customer} }, $ref; + } + $sth->finish; + } } sub save_job { - my ($self, $myconfig, $form) = @_; - - # connect to database - my $dbh = $form->dbconnect_noauto($myconfig); + my ($self, $myconfig, $form) = @_; + + my $dbh = $form->{dbh}; - my ($income_accno) = split /--/, $form->{IC_income}; + my ($income_accno) = split /--/, $form->{IC_income}; - my ($partsgroup, $partsgroup_id) = split /--/, $form->{partsgroup}; - $partsgroup_id ||= 'NULL'; + my ($partsgroup, $partsgroup_id) = split /--/, $form->{partsgroup}; - if ($form->{id}) { - $query = qq|SELECT id FROM project - WHERE id = $form->{id}|; - ($form->{id}) = $dbh->selectrow_array($query); - } + if ($form->{id}) { + $query = qq| + SELECT id FROM project + WHERE id = |.$dbh->quote($form->{id}); + ($form->{id}) = $dbh->selectrow_array($query); + } - if (!$form->{id}) { - my $uid = localtime; - $uid .= "$$"; + if (!$form->{id}) { + my $uid = localtime; + $uid .= "$$"; - $query = qq|INSERT INTO project (projectnumber) - VALUES ('$uid')|; - $dbh->do($query) || $form->dberror($query); - - $query = qq|SELECT id FROM project - WHERE projectnumber = '$uid'|; - ($form->{id}) = $dbh->selectrow_array($query); - } - - $form->{projectnumber} = $form->update_defaults($myconfig, "projectnumber", $dbh) unless $form->{projectnumber}; - - $query = qq|UPDATE project SET - projectnumber = |.$dbh->quote($form->{projectnumber}).qq|, - description = |.$dbh->quote($form->{description}).qq|, - startdate = |.$form->dbquote($form->{startdate}, SQL_DATE).qq|, - enddate = |.$form->dbquote($form->{enddate}, SQL_DATE).qq|, - parts_id = $form->{id}, - production = |.$form->parse_amount($myconfig, $form->{production}).qq|, - customer_id = $form->{customer_id} - WHERE id = $form->{id}|; - $dbh->do($query) || $form->dberror($query); - - - #### add/edit assembly - $query = qq|SELECT id FROM parts - WHERE id = $form->{id}|; - my ($id) = $dbh->selectrow_array($query); - - if (!$id) { - $query = qq|INSERT INTO parts (id) - VALUES ($form->{id})|; - $dbh->do($query) || $form->dberror($query); - } - - my $partnumber = ($form->{partnumber}) ? $form->{partnumber} : $form->{projectnumber}; - - $query = qq|UPDATE parts SET - partnumber = |.$dbh->quote($partnumber).qq|, - description = |.$dbh->quote($form->{partdescription}).qq|, - priceupdate = |.$form->dbquote($form->{priceupdate}, SQL_DATE).qq|, - listprice = |.$form->parse_amount($myconfig, $form->{listprice}).qq|, - sellprice = |.$form->parse_amount($myconfig, $form->{sellprice}).qq|, - weight = |.$form->parse_amount($myconfig, $form->{weight}).qq|, - bin = '$form->{bin}', - unit = |.$dbh->quote($form->{unit}).qq|, - notes = |.$dbh->quote($form->{notes}).qq|, - income_accno_id = (SELECT id FROM chart - WHERE accno = '$income_accno'), - partsgroup_id = $partsgroup_id, - assembly = '1', - obsolete = '1', - project_id = $form->{id} - WHERE id = $form->{id}|; - - $dbh->do($query) || $form->dberror($query); - - $query = qq|DELETE FROM partstax - WHERE parts_id = $form->{id}|; - $dbh->do($query) || $form->dberror($query); - - for (split / /, $form->{taxaccounts}) { - if ($form->{"IC_tax_$_"}) { - $query = qq|INSERT INTO partstax (parts_id, chart_id) - VALUES ($form->{id}, - (SELECT id - FROM chart - WHERE accno = '$_'))|; - $dbh->do($query) || $form->dberror($query); - } - } + $query = qq| + INSERT INTO project (projectnumber) + VALUES ('$uid')|; + $dbh->do($query) || $form->dberror($query); + + $query = qq| + SELECT id FROM project + WHERE projectnumber = '$uid'|; + ($form->{id}) = $dbh->selectrow_array($query); + } + + $form->{projectnumber} + = $form->update_defaults($myconfig, "projectnumber", $dbh) + unless $form->{projectnumber}; + + $query = qq| + UPDATE project + SET projectnumber = ?, + description = ?, + startdate = ?, + enddate = ?, + parts_id = ? + production = ?, + customer_id = ? + WHERE id = ?|; + $sth = $dbh->prepare($query); + $sth->execute( + $form->{projectnumber}, $form->{description}, + $form->{startdate}, $form->{enddate}, $form->{id}, + $form->{production}, $form->{customer_id}, $form->{id} + ) || $form->dberror($query); + + + #### add/edit assembly + $query = qq|SELECT id FROM parts WHERE id = |.$dbh->quote($form->{id}); + my ($id) = $dbh->selectrow_array($query); + + if (!$id) { + $query = qq| + INSERT INTO parts (id) + VALUES (|.$dbh->quote($form->{id}).qq|)|; + $dbh->do($query) || $form->dberror($query); + } - $dbh->commit; - $dbh->disconnect; + my $partnumber = + ($form->{partnumber}) + ? $form->{partnumber} + : $form->{projectnumber}; + + $query = qq| + UPDATE parts + SET partnumber = ?, + description = ?, + priceupdate = ?, + listprice = ?, + sellprice = ?, + weight = ?, + bin = ?, + unit = ?, + notes = ?, + income_accno_id = (SELECT id FROM chart + WHERE accno = ?), + partsgroup_id = ?, + assembly = '1', + obsolete = '1', + project_id = ? + WHERE id = ?|; + + $sth = $dbh->prepare($query); + $sth->execute( + $partnumber, $form->{partdescription}, + $form->{priceupdate}, + $form->parse_amount($myconfig, $form->{listprice}), + $form->parse_amount($myconfig, $form->{sellprice}), + $form->parse_amount($myconfig, $form->{weight}), + $form->{bin}, $form->{unit}, $form->{notes}, + $income_accno, + ($partsgroup_id) ? $partsgroup_id : undef, + $form->{id}, $form->{id} + ) || $form->dberror($query); + + $query = qq|DELETE FROM partstax WHERE parts_id = |. + $dbh->qupte($form->{id}); + $dbh->do($query) || $form->dberror($query); + + $query = qq| + INSERT INTO partstax (parts_id, chart_id) + VALUES (?, (SELECT id FROM chart WHERE accno = ?))|; + $sth = $dbh->prepare($query); + for (split / /, $form->{taxaccounts}) { + if ($form->{"IC_tax_$_"}) { + $sth->execute($form->{id}, $_) + || $form->dberror($query); + } + } + + $dbh->commit; } sub stock_assembly { - my ($self, $myconfig, $form) = @_; + my ($self, $myconfig, $form) = @_; - # connect to database - my $dbh = $form->dbconnect_noauto($myconfig); + my $dbh = $form->{dbh}; - my $ref; + my $ref; - my $query = qq|SELECT * - FROM project - WHERE id = ?|; - my $sth = $dbh->prepare($query) || $form->dberror($query); + my $query = qq|SELECT * FROM project WHERE id = ?|; + my $sth = $dbh->prepare($query) || $form->dberror($query); - $query = qq|SELECT COUNT(*) - FROM parts - WHERE project_id = ?|; - my $rvh = $dbh->prepare($query) || $form->dberror($query); + $query = qq|SELECT COUNT(*) FROM parts WHERE project_id = ?|; + my $rvh = $dbh->prepare($query) || $form->dberror($query); - if (! $form->{stockingdate}) { - $query = qq|SELECT current_date|; - ($form->{stockingdate}) = $dbh->selectrow_array($query); - } + if (! $form->{stockingdate}) { + $query = qq|SELECT current_date|; + ($form->{stockingdate}) = $dbh->selectrow_array($query); + } - $query = qq|SELECT * - FROM parts - WHERE id = ?|; - my $pth = $dbh->prepare($query) || $form->dberror($query); + $query = qq|SELECT * FROM parts WHERE id = ?|; + my $pth = $dbh->prepare($query) || $form->dberror($query); - $query = qq|SELECT j.*, p.lastcost FROM jcitems j - JOIN parts p ON (p.id = j.parts_id) - WHERE j.project_id = ? - AND j.checkedin <= '$form->{stockingdate}' - ORDER BY parts_id|; - my $jth = $dbh->prepare($query) || $form->dberror($query); - - $query = qq|INSERT INTO assembly (id, parts_id, qty, bom, adj) - VALUES (?, ?, ?, '0', '0')|; - my $ath = $dbh->prepare($query) || $form->dberror($query); - - my $i = 0; - my $sold; - my $ship; - - while (1) { - $i++; - last unless $form->{"id_$i"}; + $query = qq| + SELECT j.*, p.lastcost FROM jcitems j + JOIN parts p ON (p.id = j.parts_id) + WHERE j.project_id = ? + AND j.checkedin <= |. + $dbh->quote($form->{stockingdate}).qq| + ORDER BY parts_id|; + my $jth = $dbh->prepare($query) || $form->dberror($query); + + $query = qq| + INSERT INTO assembly (id, parts_id, qty, bom, adj) + VALUES (?, ?, ?, '0', '0')|; + my $ath = $dbh->prepare($query) || $form->dberror($query); + + my $i = 0; + my $sold; + my $ship; + + while (1) { + $i++; + last unless $form->{"id_$i"}; - $stock = $form->parse_amount($myconfig, $form->{"stock_$i"}); + $stock = $form->parse_amount($myconfig, $form->{"stock_$i"}); - if ($stock) { - $sth->execute($form->{"id_$i"}); - $ref = $sth->fetchrow_hashref(NAME_lc); - - if ($stock > ($ref->{production} - $ref->{completed})) { - $stock = $ref->{production} - $ref->{completed}; - } - if (($stock * -1) > $ref->{completed}) { - $stock = $ref->{completed} * -1; - } + if ($stock) { + $sth->execute($form->{"id_$i"}); + $ref = $sth->fetchrow_hashref(NAME_lc); + + if ($stock >($ref->{production} - $ref->{completed})) { + $stock = $ref->{production} + - $ref->{completed}; + } + if (($stock * -1) > $ref->{completed}) { + $stock = $ref->{completed} * -1; + } - $pth->execute($form->{"id_$i"}); - $pref = $pth->fetchrow_hashref(NAME_lc); + $pth->execute($form->{"id_$i"}); + $pref = $pth->fetchrow_hashref(NAME_lc); - my %assembly = (); - my $lastcost = 0; - my $sellprice = 0; - my $listprice = 0; + my %assembly = (); + my $lastcost = 0; + my $sellprice = 0; + my $listprice = 0; - $jth->execute($form->{"id_$i"}); - while ($jref = $jth->fetchrow_hashref(NAME_lc)) { - $assembly{qty}{$jref->{parts_id}} += ($jref->{qty} - $jref->{allocated}); - $assembly{parts_id}{$jref->{parts_id}} = $jref->{parts_id}; - $assembly{jcitems}{$jref->{id}} = $jref->{id}; - $lastcost += $form->round_amount(($jref->{lastcost} * ($jref->{qty} - $jref->{allocated})), 2); - $sellprice += $form->round_amount(($jref->{sellprice} * ($jref->{qty} - $jref->{allocated})), 2); - $listprice += $form->round_amount(($jref->{listprice} * ($jref->{qty} - $jref->{allocated})), 2); - } - $jth->finish; - - $uid = localtime; - $uid .= "$$"; + $jth->execute($form->{"id_$i"}); + while ($jref = $jth->fetchrow_hashref(NAME_lc)) { + $assembly{qty}{$jref->{parts_id}} + += ($jref->{qty} - $jref->{allocated}); + $assembly{parts_id}{$jref->{parts_id}} + = $jref->{parts_id}; + $assembly{jcitems}{$jref->{id}} = $jref->{id}; + $lastcost += $form->round_amount( + $jref->{lastcost} * ($jref->{qty} + - $jref->{allocated}), + 2); + $sellprice += $form->round_amount( + $jref->{sellprice} * ($jref->{qty} + - $jref->{allocated}), + 2); + $listprice += $form->round_amount( + $jref->{listprice} * ($jref->{qty} + - $jref->{allocated}), + 2); + } + $jth->finish; + + $uid = localtime; + $uid .= "$$"; - $query = qq|INSERT INTO parts (partnumber) - VALUES ('$uid')|; - $dbh->do($query) || $form->dberror($query); - - $query = qq|SELECT id - FROM parts - WHERE partnumber = '$uid'|; - ($uid) = $dbh->selectrow_array($query); - - $lastcost = $form->round_amount($lastcost / $stock, 2); - $sellprice = ($pref->{sellprice}) ? $pref->{sellprice} : $form->round_amount($sellprice / $stock, 2); - $listprice = ($pref->{listprice}) ? $pref->{listprice} : $form->round_amount($listprice / $stock, 2); - - $rvh->execute($form->{"id_$i"}); - my ($rev) = $rvh->fetchrow_array; - $rvh->finish; + $query = qq| + INSERT INTO parts (partnumber) + VALUES ('$uid')|; + $dbh->do($query) || $form->dberror($query); + + $query = qq| + SELECT id + FROM parts + WHERE partnumber = '$uid'|; + ($uid) = $dbh->selectrow_array($query); + + $lastcost = $form->round_amount($lastcost / $stock, 2); + $sellprice = + ($pref->{sellprice}) + ? $pref->{sellprice} + : $form->round_amount($sellprice / $stock, 2); + $listprice = + ($pref->{listprice}) + ? $pref->{listprice} + : $form->round_amount($listprice / $stock, 2); + + $rvh->execute($form->{"id_$i"}); + my ($rev) = $rvh->fetchrow_array; + $rvh->finish; - $query = qq|UPDATE parts SET - partnumber = '$pref->{partnumber}-$rev', - description = '$pref->{partdescription}', - priceupdate = '$form->{stockingdate}', - unit = '$pref->{unit}', - listprice = $listprice, - sellprice = $sellprice, - lastcost = $lastcost, - weight = $pref->{weight}, - onhand = $stock, - notes = '$pref->{notes}', - assembly = '1', - income_accno_id = $pref->{income_accno_id}, - bin = '$pref->{bin}', - project_id = $form->{"id_$i"} - WHERE id = $uid|; - $dbh->do($query) || $form->dberror($query); - - $query = qq|INSERT INTO partstax (parts_id, chart_id) - SELECT '$uid', chart_id FROM partstax - WHERE parts_id = $pref->{id}|; - $dbh->do($query) || $form->dberror($query); + $query = qq| + UPDATE parts + SET partnumber = ?, + description = ?, + priceupdate = ?, + unit = ?, + listprice = ?, + sellprice = ?, + lastcost = ?, + weight = ?, + onhand = ?, + notes = ?, + assembly = '1', + income_accno_id = ?, + bin = ?, + project_id = ? + WHERE id = ?|; + $sth = $dbh->prepare($query); + $sth->execute( + "$pref->{partnumber}-$rev", + $pref->{partdescription}, + $form->{stockingdate}, $pref->{unit}, + $listprice, $sellprice, $lastcost, + $pref->{weight}, $stock, $pref->{notes}, + $pref->{income_accno_id}, $pref->{bin}, + $form->{"id_$i"}, $uid + )|| $form->dberror($query); + + $query = qq| + INSERT INTO partstax (parts_id, chart_id) + SELECT ?, chart_id FROM partstax + WHERE parts_id = ?|; + $sth = $dbh->prepare($query); + $sth->execute($uid, $pref->{id}) + || $form->dberror($query); - $pth->finish; + $pth->finish; - for (keys %{$assembly{parts_id}}) { - if ($assembly{qty}{$_}) { - $ath->execute($uid, $assembly{parts_id}{$_}, $form->round_amount($assembly{qty}{$_} / $stock, 4)); - $ath->finish; - } - } + for (keys %{$assembly{parts_id}}) { + if ($assembly{qty}{$_}) { + $ath->execute( + $uid, $assembly{parts_id}{$_}, + $form->round_amount( + $assembly{qty}{$_} + / $stock, + 4)); + $ath->finish; + } + } - $form->update_balance($dbh, - "project", - "completed", - qq|id = $form->{"id_$i"}|, - $stock); + $form->update_balance( + $dbh, "project", "completed", + qq|id = $form->{"id_$i"}|, $stock); - $query = qq|UPDATE jcitems SET - allocated = qty - WHERE allocated != qty - AND checkedin <= '$form->{stockingdate}' - AND project_id = $form->{"id_$i"}|; - $dbh->do($query) || $form->dberror($query); - - $sth->finish; + $query = qq| + UPDATE jcitems + SET allocated = qty + WHERE allocated != qty + AND checkedin <= ? + AND project_id = ?|; + $sth = $dbh->prepare($query); + $sth->execute($form->{stockingdate}, $form->{"id_$i"}) + || $form->dberror($query); + + $sth->finish; - } + } - } + } - my $rc = $dbh->commit; - $dbh->disconnect; + my $rc = $dbh->commit; - $rc; + $rc; } sub delete_project { - my ($self, $myconfig, $form) = @_; + my ($self, $myconfig, $form) = @_; - # connect to database - my $dbh = $form->dbconnect_noauto($myconfig); + my $dbh = $form->{dbh}; - $query = qq|DELETE FROM project - WHERE id = $form->{id}|; - $dbh->do($query) || $form->dberror($query); + $query = qq|DELETE FROM project WHERE id = ?|; + $sth = $dbh->prepare($query); + $sth->execute($form->{id}) || $form->dberror($query); - $query = qq|DELETE FROM translation + $query = qq|DELETE FROM translation WHERE trans_id = $form->{id}|; - $dbh->do($query) || $form->dberror($query); + $sth = $dbh->prepare($query); + $sth->execute($form->{id}) || $form->dberror($query); - my $rc = $dbh->commit; - $dbh->disconnect; + my $rc = $dbh->commit; - $rc; + $rc; } sub delete_partsgroup { - my ($self, $myconfig, $form) = @_; + my ($self, $myconfig, $form) = @_; - # connect to database - my $dbh = $form->dbconnect_noauto($myconfig); + my $dbh = $form->{dbh}; - $query = qq|DELETE FROM partsgroup - WHERE id = $form->{id}|; - $dbh->do($query) || $form->dberror($query); + $query = qq|DELETE FROM partsgroup WHERE id = ?|; + $sth = $dbh->prepare($query); + $sth->execute($form->{id}) || $form->dberror($query); - $query = qq|DELETE FROM translation - WHERE trans_id = $form->{id}|; - $dbh->do($query) || $form->dberror($query); + $query = qq|DELETE FROM translation WHERE trans_id = ?|; + $sth = $dbh->prepare($query); + $sth->execute($form->{id}) || $form->dberror($query); - my $rc = $dbh->commit; - $dbh->disconnect; + my $rc = $dbh->commit; - $rc; + $rc; } sub delete_pricegroup { - my ($self, $myconfig, $form) = @_; + my ($self, $myconfig, $form) = @_; - # connect to database - my $dbh = $form->dbconnect_noauto($myconfig); + my $dbh = $form->{dbh}; - $query = qq|DELETE FROM pricegroup - WHERE id = $form->{id}|; - $dbh->do($query) || $form->dberror($query); + $query = qq|DELETE FROM pricegroup WHERE id = ?|; + $sth = $dbh->prepare($query); + $sth->execute($form->{id}) || $form->dberror($query); - my $rc = $dbh->commit; - $dbh->disconnect; + my $rc = $dbh->commit; - $rc; + $rc; } sub delete_job { - my ($self, $myconfig, $form) = @_; + my ($self, $myconfig, $form) = @_; - # connect to database - my $dbh = $form->dbconnect_noauto($myconfig); + my $dbh = $form->{dbh}; - my %audittrail = ( tablename => 'project', - reference => $form->{id}, - formname => $form->{type}, - action => 'deleted', - id => $form->{id} ); - - $form->audittrail($dbh, "", \%audittrail); + my %audittrail = ( + tablename => 'project', + reference => $form->{id}, + formname => $form->{type}, + action => 'deleted', + id => $form->{id} ); + + $form->audittrail($dbh, "", \%audittrail); - my $query = qq|DELETE FROM project - WHERE id = $form->{id}|; - $dbh->do($query) || $form->dberror($query); - - $query = qq|DELETE FROM translation - WHERE trans_id = $form->{id}|; - $dbh->do($query) || $form->dberror($query); - - # delete all the assemblies - $query = qq|DELETE FROM assembly a - JOIN parts p ON (a.id = p.id) - WHERE p.project_id = $form->{id}|; - $dbh->do($query) || $form->dberror($query); + my $query = qq|DELETE FROM project WHERE id = ?|; + $sth = $dbh->prepare($query); + $sth->execute($form->{id}) || $form->dberror($query); + + $query = qq|DELETE FROM translation WHERE trans_id = ?|; + $sth = $dbh->prepare($query); + $sth->execute($form->{id}) || $form->dberror($query); + + # delete all the assemblies + $query = qq| + DELETE FROM assembly a + JOIN parts p ON (a.id = p.id) + WHERE p.project_id = ?|; + $sth = $dbh->prepare($query); + $sth->execute($form->{id}) || $form->dberror($query); - $query = qq|DELETE FROM parts - WHERE project_id = $form->{id}|; - $dbh->do($query) || $form->dberror($query); + $query = qq|DELETE FROM parts WHERE project_id = ?|; + $sth = $dbh->prepare($query); + $sth->execute($form->{id}) || $form->dberror($query); - my $rc = $dbh->commit; - $dbh->disconnect; + my $rc = $dbh->commit; - $rc; + $rc; } sub partsgroups { - my ($self, $myconfig, $form) = @_; + my ($self, $myconfig, $form) = @_; - my $var; + my $var; - # connect to database - my $dbh = $form->dbconnect($myconfig); + my $dbh = $form->dbconnect($myconfig); - $form->{sort} = "partsgroup" unless $form->{partsgroup}; - my @a = (partsgroup); - my $sortorder = $form->sort_order(\@a); - - my $query = qq|SELECT g.* - FROM partsgroup g|; - - my $where = "1 = 1"; - - if ($form->{partsgroup} ne "") { - $var = $form->like(lc $form->{partsgroup}); - $where .= " AND lower(partsgroup) LIKE '$var'"; - } - $query .= qq| - WHERE $where - ORDER BY $sortorder|; - - if ($form->{status} eq 'orphaned') { - $query = qq|SELECT g.* - FROM partsgroup g - LEFT JOIN parts p ON (p.partsgroup_id = g.id) - WHERE $where - EXCEPT - SELECT g.* - FROM partsgroup g - JOIN parts p ON (p.partsgroup_id = g.id) - WHERE $where - ORDER BY $sortorder|; - } + $form->{sort} = "partsgroup" unless $form->{partsgroup}; + my @a = (partsgroup); + my $sortorder = $form->sort_order(\@a); + + my $query = qq|SELECT g.* FROM partsgroup g|; + + my $where = "1 = 1"; + + if ($form->{partsgroup} ne "") { + $var = $dbh->quote($form->like(lc $form->{partsgroup})); + $where .= " AND lower(partsgroup) LIKE '$var'"; + } + $query .= qq| WHERE $where ORDER BY $sortorder|; + + if ($form->{status} eq 'orphaned') { + $query = qq| + SELECT g.* + FROM partsgroup g + LEFT JOIN parts p ON (p.partsgroup_id = g.id) + WHERE $where + EXCEPT + SELECT g.* + FROM partsgroup g + JOIN parts p ON (p.partsgroup_id = g.id) + WHERE $where + ORDER BY $sortorder|; + } - $sth = $dbh->prepare($query); - $sth->execute || $form->dberror($query); + $sth = $dbh->prepare($query); + $sth->execute || $form->dberror($query); - my $i = 0; - while (my $ref = $sth->fetchrow_hashref(NAME_lc)) { - push @{ $form->{item_list} }, $ref; - $i++; - } + my $i = 0; + while (my $ref = $sth->fetchrow_hashref(NAME_lc)) { + push @{ $form->{item_list} }, $ref; + $i++; + } - $sth->finish; - $dbh->disconnect; + $sth->finish; - $i; + $i; } sub save_partsgroup { - my ($self, $myconfig, $form) = @_; - - # connect to database - my $dbh = $form->dbconnect($myconfig); - - if ($form->{id}) { - $query = qq|UPDATE partsgroup SET - partsgroup = |.$dbh->quote($form->{partsgroup}).qq| - WHERE id = $form->{id}|; - } else { - $query = qq|INSERT INTO partsgroup - (partsgroup) - VALUES (|.$dbh->quote($form->{partsgroup}).qq|)|; - } - $dbh->do($query) || $form->dberror($query); - - $dbh->disconnect; + my ($self, $myconfig, $form) = @_; + + my $dbh = $form->{dbh}; + + if ($form->{id}) { + $query = qq| + UPDATE partsgroup + SET partsgroup = |. + $dbh->quote($form->{partsgroup}).qq| + WHERE id = $form->{id}|; + } else { + $query = qq| + INSERT INTO partsgroup (partsgroup) + VALUES (|.$dbh->quote($form->{partsgroup}).qq|)|; + } + $dbh->do($query) || $form->dberror($query); + + $dbh->commit; } sub get_partsgroup { - my ($self, $myconfig, $form) = @_; + my ($self, $myconfig, $form) = @_; - # connect to database - my $dbh = $form->dbconnect($myconfig); + my $dbh = $form->dbconnect($myconfig); - my $query = qq|SELECT * - FROM partsgroup - WHERE id = $form->{id}|; - my $sth = $dbh->prepare($query); - $sth->execute || $form->dberror($query); + my $query = qq|SELECT * FROM partsgroup WHERE id = ?|; + my $sth = $dbh->prepare($query); + $sth->execute($form->{id}) || $form->dberror($query); - my $ref = $sth->fetchrow_hashref(NAME_lc); + my $ref = $sth->fetchrow_hashref(NAME_lc); - for (keys %$ref) { $form->{$_} = $ref->{$_} } + for (keys %$ref) { $form->{$_} = $ref->{$_} } - $sth->finish; + $sth->finish; - # check if it is orphaned - $query = qq|SELECT count(*) - FROM parts - WHERE partsgroup_id = $form->{id}|; - $sth = $dbh->prepare($query); - $sth->execute || $form->dberror($query); + # check if it is orphaned + $query = qq|SELECT count(*) FROM parts WHERE partsgroup_id = ?|; + $sth = $dbh->prepare($query); + $sth->execute($form->{id}) || $form->dberror($query); - ($form->{orphaned}) = $sth->fetchrow_array; - $form->{orphaned} = !$form->{orphaned}; + ($form->{orphaned}) = $sth->fetchrow_array; + $form->{orphaned} = !$form->{orphaned}; - $sth->finish; + $sth->finish; - $dbh->disconnect; + $dbh->commit; } sub pricegroups { - my ($self, $myconfig, $form) = @_; + my ($self, $myconfig, $form) = @_; - my $var; + my $var; - # connect to database - my $dbh = $form->dbconnect($myconfig); + my $dbh = $form->{dbh}; + + $form->{sort} = "pricegroup" unless $form->{sort}; + my @a = (pricegroup); + my $sortorder = $form->sort_order(\@a); + + my $query = qq|SELECT g.* FROM pricegroup g|; - $form->{sort} = "pricegroup" unless $form->{sort}; - my @a = (pricegroup); - my $sortorder = $form->sort_order(\@a); - - my $query = qq|SELECT g.* - FROM pricegroup g|; - - my $where = "1 = 1"; - - if ($form->{pricegroup} ne "") { - $var = $form->like(lc $form->{pricegroup}); - $where .= " AND lower(pricegroup) LIKE '$var'"; - } - $query .= qq| - WHERE $where - ORDER BY $sortorder|; - - if ($form->{status} eq 'orphaned') { - $query = qq|SELECT g.* - FROM pricegroup g - WHERE $where - AND g.id NOT IN (SELECT DISTINCT pricegroup_id - FROM partscustomer - WHERE pricegroup_id > 0) + my $where = "1 = 1"; + + if ($form->{pricegroup} ne "") { + $var = $dbh->quote($form->like(lc $form->{pricegroup})); + $where .= " AND lower(pricegroup) LIKE $var"; + } + $query .= qq| + WHERE $where ORDER BY $sortorder|; + + if ($form->{status} eq 'orphaned') { + $query = qq| + SELECT g.* + FROM pricegroup g + WHERE $where + AND g.id NOT IN (SELECT DISTINCT pricegroup_id + FROM partscustomer + WHERE pricegroup_id > 0) ORDER BY $sortorder|; - } + } - $sth = $dbh->prepare($query); - $sth->execute || $form->dberror($query); + $sth = $dbh->prepare($query); + $sth->execute || $form->dberror($query); - my $i = 0; - while (my $ref = $sth->fetchrow_hashref(NAME_lc)) { - push @{ $form->{item_list} }, $ref; - $i++; - } + my $i = 0; + while (my $ref = $sth->fetchrow_hashref(NAME_lc)) { + push @{ $form->{item_list} }, $ref; + $i++; + } - $sth->finish; - $dbh->disconnect; + $sth->finish; + $dbh->commit; - $i; + $i; } sub save_pricegroup { - my ($self, $myconfig, $form) = @_; - - # connect to database - my $dbh = $form->dbconnect($myconfig); - - if ($form->{id}) { - $query = qq|UPDATE pricegroup SET - pricegroup = |.$dbh->quote($form->{pricegroup}).qq| - WHERE id = $form->{id}|; - } else { - $query = qq|INSERT INTO pricegroup - (pricegroup) - VALUES (|.$dbh->quote($form->{pricegroup}).qq|)|; - } - $dbh->do($query) || $form->dberror($query); + my ($self, $myconfig, $form) = @_; + + my $dbh = $form->{dbh}; + + if ($form->{id}) { + $query = qq| + UPDATE pricegroup SET + pricegroup = ? + WHERE id = |.$dbh->quote($form->{id}); + } else { + $query = qq| + INSERT INTO pricegroup (pricegroup) + VALUES (?)|; + } + $sth = $dbh->do($query); + $sth->execute($form->{pricegroup}) || $form->dberror($query); - $dbh->disconnect; + $dbh->commit; } sub get_pricegroup { - my ($self, $myconfig, $form) = @_; + my ($self, $myconfig, $form) = @_; - # connect to database - my $dbh = $form->dbconnect($myconfig); + my $dbh = $form->{dbh}; - my $query = qq|SELECT * - FROM pricegroup - WHERE id = $form->{id}|; - my $sth = $dbh->prepare($query); - $sth->execute || $form->dberror($query); + my $query = qq|SELECT * FROM pricegroup WHERE id = ?|; + my $sth = $dbh->prepare($query); + $sth->execute($form->{id}) || $form->dberror($query); - my $ref = $sth->fetchrow_hashref(NAME_lc); + my $ref = $sth->fetchrow_hashref(NAME_lc); - for (keys %$ref) { $form->{$_} = $ref->{$_} } + for (keys %$ref) { $form->{$_} = $ref->{$_} } - $sth->finish; + $sth->finish; - # check if it is orphaned - $query = qq|SELECT count(*) - FROM partscustomer - WHERE pricegroup_id = $form->{id}|; - $sth = $dbh->prepare($query); - $sth->execute || $form->dberror($query); + # check if it is orphaned + $query = "SELECT count(*) FROM partscustomer WHERE pricegroup_id = ?"; + $sth = $dbh->prepare($query); + $sth->execute($form->{id}) || $form->dberror($query); - ($form->{orphaned}) = $sth->fetchrow_array; - $form->{orphaned} = !$form->{orphaned}; + ($form->{orphaned}) = $sth->fetchrow_array; + $form->{orphaned} = !$form->{orphaned}; - $sth->finish; + $sth->finish; - $dbh->disconnect; + $dbh->commit; } sub description_translations { - my ($self, $myconfig, $form) = @_; - - my $where = "1 = 1"; - my $var; - my $ref; - - for (qw(partnumber description)) { - if ($form->{$_}) { - $var = $form->like(lc $form->{$_}); - $where .= " AND lower(p.$_) LIKE '$var'"; - } - } + my ($self, $myconfig, $form) = @_; + + my $dbh = $form->{dbh}; + my $where = "1 = 1"; + my $var; + my $ref; + + for (qw(partnumber description)) { + if ($form->{$_}) { + $var = $dbh->quote($form->like(lc $form->{$_})); + $where .= " AND lower(p.$_) LIKE $var"; + } + } - $where .= " AND p.obsolete = '0'"; - $where .= " AND p.id = $form->{id}" if $form->{id}; + $where .= " AND p.obsolete = '0'"; + $where .= " AND p.id = ".$dbh->quote($form->{id}) if $form->{id}; - # connect to database - my $dbh = $form->dbconnect($myconfig); - my %ordinal = ( 'partnumber' => 2, - 'description' => 3 - ); + my %ordinal = ( 'partnumber' => 2, 'description' => 3 ); - my @a = qw(partnumber description); - my $sortorder = $form->sort_order(\@a, \%ordinal); + my @a = qw(partnumber description); + my $sortorder = $form->sort_order(\@a, \%ordinal); - my $query = qq|SELECT l.description AS language, t.description AS translation, - l.code - FROM translation t - JOIN language l ON (l.code = t.language_code) - WHERE trans_id = ? - ORDER BY 1|; - my $tth = $dbh->prepare($query); + my $query = qq| + SELECT l.description AS language, + t.description AS translation, l.code + FROM translation t + JOIN language l ON (l.code = t.language_code) + WHERE trans_id = ? + ORDER BY 1|; + my $tth = $dbh->prepare($query); - $query = qq|SELECT p.id, p.partnumber, p.description - FROM parts p - WHERE $where - ORDER BY $sortorder|; + $query = qq| + SELECT p.id, p.partnumber, p.description + FROM parts p + WHERE $where + ORDER BY $sortorder|; - my $sth = $dbh->prepare($query); - $sth->execute || $form->dberror($query); + my $sth = $dbh->prepare($query); + $sth->execute || $form->dberror($query); - my $tra; + my $tra; - while ($ref = $sth->fetchrow_hashref(NAME_lc)) { - push @{ $form->{translations} }, $ref; + while ($ref = $sth->fetchrow_hashref(NAME_lc)) { + push @{ $form->{translations} }, $ref; - # get translations for description - $tth->execute($ref->{id}) || $form->dberror; + # get translations for description + $tth->execute($ref->{id}) || $form->dberror; - while ($tra = $tth->fetchrow_hashref(NAME_lc)) { - $form->{trans_id} = $ref->{id}; - $tra->{id} = $ref->{id}; - push @{ $form->{translations} }, $tra; - } - $tth->finish; + while ($tra = $tth->fetchrow_hashref(NAME_lc)) { + $form->{trans_id} = $ref->{id}; + $tra->{id} = $ref->{id}; + push @{ $form->{translations} }, $tra; + } + $tth->finish; - } - $sth->finish; + } + $sth->finish; - &get_language("", $dbh, $form) if $form->{id}; + &get_language("", $dbh, $form) if $form->{id}; - $dbh->disconnect; + $dbh->commit; } sub partsgroup_translations { - my ($self, $myconfig, $form) = @_; + my ($self, $myconfig, $form) = @_; + my $dbh = $form->{dbh}; - my $where = "1 = 1"; - my $ref; - my $var; + my $where = "1 = 1"; + my $ref; + my $var; - if ($form->{description}) { - $var = $form->like(lc $form->{description}); - $where .= " AND lower(p.partsgroup) LIKE '$var'"; - } - $where .= " AND p.id = $form->{id}" if $form->{id}; + if ($form->{description}) { + $var = $dbh->quote($form->like(lc $form->{description})); + $where .= " AND lower(p.partsgroup) LIKE $var"; + } + $where .= " AND p.id = ".$dbh->quote($form->{id}) if $form->{id}; - # connect to database - my $dbh = $form->dbconnect($myconfig); - my $query = qq|SELECT l.description AS language, t.description AS translation, - l.code - FROM translation t - JOIN language l ON (l.code = t.language_code) - WHERE trans_id = ? - ORDER BY 1|; - my $tth = $dbh->prepare($query); + my $query = qq| + SELECT l.description AS language, + t.description AS translation, l.code + FROM translation t + JOIN language l ON (l.code = t.language_code) + WHERE trans_id = ? + ORDER BY 1|; + my $tth = $dbh->prepare($query); - $form->sort_order(); + $form->sort_order(); - $query = qq|SELECT p.id, p.partsgroup AS description - FROM partsgroup p - WHERE $where - ORDER BY 2 $form->{direction}|; + $query = qq| + SELECT p.id, p.partsgroup AS description + FROM partsgroup p + WHERE $where + ORDER BY 2 $form->{direction}|; - my $sth = $dbh->prepare($query); - $sth->execute || $form->dberror($query); + my $sth = $dbh->prepare($query); + $sth->execute || $form->dberror($query); - my $tra; + my $tra; - while ($ref = $sth->fetchrow_hashref(NAME_lc)) { - push @{ $form->{translations} }, $ref; + while ($ref = $sth->fetchrow_hashref(NAME_lc)) { + push @{ $form->{translations} }, $ref; - # get translations for partsgroup - $tth->execute($ref->{id}) || $form->dberror; + # get translations for partsgroup + $tth->execute($ref->{id}) || $form->dberror; - while ($tra = $tth->fetchrow_hashref(NAME_lc)) { - $form->{trans_id} = $ref->{id}; - push @{ $form->{translations} }, $tra; - } - $tth->finish; + while ($tra = $tth->fetchrow_hashref(NAME_lc)) { + $form->{trans_id} = $ref->{id}; + push @{ $form->{translations} }, $tra; + } + $tth->finish; - } - $sth->finish; + } + $sth->finish; - &get_language("", $dbh, $form) if $form->{id}; + &get_language("", $dbh, $form) if $form->{id}; - $dbh->disconnect; + $dbh->commit; } sub project_translations { - my ($self, $myconfig, $form) = @_; - - my $where = "1 = 1"; - my $var; - my $ref; - - for (qw(projectnumber description)) { - if ($form->{$_}) { - $var = $form->like(lc $form->{$_}); - $where .= " AND lower(p.$_) LIKE '$var'"; - } - } + my ($self, $myconfig, $form) = @_; + my $dbh = $form->{dbh}; + + my $where = "1 = 1"; + my $var; + my $ref; + + for (qw(projectnumber description)) { + if ($form->{$_}) { + $var = $dbh->quote($form->like(lc $form->{$_})); + $where .= " AND lower(p.$_) LIKE $var"; + } + } - $where .= " AND p.id = $form->{id}" if $form->{id}; + $where .= " AND p.id = ".$dbh->quote($form->{id}) if $form->{id}; - # connect to database - my $dbh = $form->dbconnect($myconfig); - my %ordinal = ( 'projectnumber' => 2, - 'description' => 3 - ); + my %ordinal = ( 'projectnumber' => 2, 'description' => 3 ); - my @a = qw(projectnumber description); - my $sortorder = $form->sort_order(\@a, \%ordinal); + my @a = qw(projectnumber description); + my $sortorder = $form->sort_order(\@a, \%ordinal); - my $query = qq|SELECT l.description AS language, t.description AS translation, - l.code - FROM translation t - JOIN language l ON (l.code = t.language_code) - WHERE trans_id = ? - ORDER BY 1|; - my $tth = $dbh->prepare($query); + my $query = qq| + SELECT l.description AS language, + t.description AS translation, l.code + FROM translation t + JOIN language l ON (l.code = t.language_code) + WHERE trans_id = ? + ORDER BY 1|; + my $tth = $dbh->prepare($query); - $query = qq|SELECT p.id, p.projectnumber, p.description - FROM project p - WHERE $where - ORDER BY $sortorder|; + $query = qq| + SELECT p.id, p.projectnumber, p.description + FROM project p + WHERE $where + ORDER BY $sortorder|; - my $sth = $dbh->prepare($query); - $sth->execute || $form->dberror($query); + my $sth = $dbh->prepare($query); + $sth->execute || $form->dberror($query); - my $tra; + my $tra; - while ($ref = $sth->fetchrow_hashref(NAME_lc)) { - push @{ $form->{translations} }, $ref; + while ($ref = $sth->fetchrow_hashref(NAME_lc)) { + push @{ $form->{translations} }, $ref; - # get translations for description - $tth->execute($ref->{id}) || $form->dberror; + # get translations for description + $tth->execute($ref->{id}) || $form->dberror; - while ($tra = $tth->fetchrow_hashref(NAME_lc)) { - $form->{trans_id} = $ref->{id}; - $tra->{id} = $ref->{id}; - push @{ $form->{translations} }, $tra; - } - $tth->finish; + while ($tra = $tth->fetchrow_hashref(NAME_lc)) { + $form->{trans_id} = $ref->{id}; + $tra->{id} = $ref->{id}; + push @{ $form->{translations} }, $tra; + } + $tth->finish; - } - $sth->finish; + } + $sth->finish; - &get_language("", $dbh, $form) if $form->{id}; + &get_language("", $dbh, $form) if $form->{id}; - $dbh->disconnect; + $dbh->commit; } sub get_language { - my ($self, $dbh, $form) = @_; - - # get language - my $query = qq|SELECT * - FROM language - ORDER BY 2|; - my $sth = $dbh->prepare($query); - $sth->execute || $form->dberror($query); - - while (my $ref = $sth->fetchrow_hashref(NAME_lc)) { - push @{ $form->{all_language} }, $ref; - } - $sth->finish; + my ($self, $dbh, $form) = @_; + + my $query = qq|SELECT * FROM language ORDER BY 2|; + my $sth = $dbh->prepare($query); + $sth->execute || $form->dberror($query); + + while (my $ref = $sth->fetchrow_hashref(NAME_lc)) { + push @{ $form->{all_language} }, $ref; + } + $sth->finish; } sub save_translation { - my ($self, $myconfig, $form) = @_; + my ($self, $myconfig, $form) = @_; - # connect to database - my $dbh = $form->dbconnect_noauto($myconfig); - - my $query = qq|DELETE FROM translation - WHERE trans_id = $form->{id}|; - $dbh->do($query) || $form->dberror($query); - - $query = qq|INSERT INTO translation (trans_id, language_code, description) - VALUES ($form->{id}, ?, ?)|; - my $sth = $dbh->prepare($query) || $form->dberror($query); - - foreach my $i (1 .. $form->{translation_rows}) { - if ($form->{"language_code_$i"} ne "") { - $sth->execute($form->{"language_code_$i"}, $form->{"translation_$i"}); - $sth->finish; - } - } + my $dbh = $form->{dbh}; + + my $query = qq|DELETE FROM translation WHERE trans_id = ?|; + $sth = $dbh->prepare($query); + $sth->execute($form->{id})|| $form->dberror($query); + + $query = qq| + INSERT INTO translation (trans_id, language_code, description) + VALUES (?, ?, ?)|; + my $sth = $dbh->prepare($query) || $form->dberror($query); + + foreach my $i (1 .. $form->{translation_rows}) { + if ($form->{"language_code_$i"} ne "") { + $sth->execute($form->{id}, $form->{"language_code_$i"}, + $form->{"translation_$i"}); + $sth->finish; + } + } $dbh->commit; - $dbh->disconnect; } sub delete_translation { - my ($self, $myconfig, $form) = @_; - - # connect to database - my $dbh = $form->dbconnect($myconfig); + my ($self, $myconfig, $form) = @_; - my $query = qq|DELETE FROM translation - WHERE trans_id = $form->{id}|; - $dbh->do($query) || $form->dberror($query); + my $dbh = $form->{dbh}; - $dbh->disconnect; + my $query = qq|DELETE FROM translation WHERE trans_id = ?|; + $sth = $dbh->prepare($query); + $sth->execute($form->{id})|| $form->dberror($query); + + $dbh->commit; } @@ -1391,127 +1449,133 @@ sub project_sales_order { sub get_jcitems { - my ($self, $myconfig, $form) = @_; + my ($self, $myconfig, $form) = @_; - # connect to database - my $dbh = $form->dbconnect($myconfig); + my $dbh = $form->{dbh}; - my $null; - my $var; - my $where; - - if ($form->{projectnumber}) { - ($null, $var) = split /--/, $form->{projectnumber}; - $where .= " AND j.project_id = $var"; - } - - if ($form->{employee}) { - ($null, $var) = split /--/, $form->{employee}; - $where .= " AND j.employee_id = $var"; - } - - ($form->{transdatefrom}, $form->{transdateto}) = $form->from_to($form->{year}, $form->{month}, $form->{interval}) if $form->{year} && $form->{month}; - - if ($form->{transdatefrom}) { - $where .= " AND j.checkedin >= '$form->{transdatefrom}'"; - } - if ($form->{transdateto}) { - $where .= " AND j.checkedout <= (date '$form->{transdateto}' + interval '1 days')"; - } - - my $query; - my $ref; - - $query = qq|SELECT j.id, j.description, j.qty - j.allocated AS qty, - j.sellprice, j.parts_id, pr.$form->{vc}_id, j.project_id, - j.checkedin::date AS transdate, j.notes, - c.name AS $form->{vc}, pr.projectnumber, p.partnumber - FROM jcitems j - JOIN project pr ON (pr.id = j.project_id) - JOIN employee e ON (e.id = j.employee_id) - JOIN parts p ON (p.id = j.parts_id) - LEFT JOIN $form->{vc} c ON (c.id = pr.$form->{vc}_id) - WHERE pr.parts_id IS NULL - AND j.allocated != j.qty - $where - ORDER BY pr.projectnumber, c.name, j.checkedin::date|; - - if ($form->{summary}) { - $query =~ s/j\.description/p\.description/; - $query =~ s/c\.name,/c\.name, j\.parts_id, /; - } + my $null; + my $var; + my $where; + + if ($form->{projectnumber}) { + ($null, $var) = split /--/, $form->{projectnumber}; + $var = $dbh->quote($var); + $where .= " AND j.project_id = $var"; + } + + if ($form->{employee}) { + ($null, $var) = split /--/, $form->{employee}; + $var = $dbh->quote($var); + $where .= " AND j.employee_id = $var"; + } + + ($form->{transdatefrom}, $form->{transdateto}) + = $form->from_to( + $form->{year}, $form->{month}, $form->{interval}) + if $form->{year} && $form->{month}; + + if ($form->{transdatefrom}) { + $where .= " AND j.checkedin >= ". + $dbh->quote($form->{transdatefrom}); + } + if ($form->{transdateto}) { + $where .= " AND j.checkedout <= (date ". + $dbh->quote($form->{transdateto}) . + " + interval '1 days')"; + } + + my $query; + my $ref; + + $query = qq| + SELECT j.id, j.description, j.qty - j.allocated AS qty, + j.sellprice, j.parts_id, pr.$form->{vc}_id, + j.project_id, j.checkedin::date AS transdate, + j.notes, c.name AS $form->{vc}, pr.projectnumber, + p.partnumber + FROM jcitems j + JOIN project pr ON (pr.id = j.project_id) + JOIN employee e ON (e.id = j.employee_id) + JOIN parts p ON (p.id = j.parts_id) + LEFT JOIN $form->{vc} c ON (c.id = pr.$form->{vc}_id) + WHERE pr.parts_id IS NULL + AND j.allocated != j.qty $where + ORDER BY pr.projectnumber, c.name, j.checkedin::date|; + + if ($form->{summary}) { + $query =~ s/j\.description/p\.description/; + $query =~ s/c\.name,/c\.name, j\.parts_id, /; + } - $sth = $dbh->prepare($query); - $sth->execute || $form->dberror($query); - - # tax accounts - $query = qq|SELECT c.accno - FROM chart c - JOIN partstax pt ON (pt.chart_id = c.id) - WHERE pt.parts_id = ?|; - my $tth = $dbh->prepare($query) || $form->dberror($query); - my $ptref; - - while ($ref = $sth->fetchrow_hashref(NAME_lc)) { + $sth = $dbh->prepare($query); + $sth->execute || $form->dberror($query); + + # tax accounts + $query = qq| + SELECT c.accno + FROM chart c + JOIN partstax pt ON (pt.chart_id = c.id) + WHERE pt.parts_id = ?|; + my $tth = $dbh->prepare($query) || $form->dberror($query); + my $ptref; + + while ($ref = $sth->fetchrow_hashref(NAME_lc)) { - $tth->execute($ref->{parts_id}); - $ref->{taxaccounts} = ""; - while ($ptref = $tth->fetchrow_hashref(NAME_lc)) { - $ref->{taxaccounts} .= "$ptref->{accno} "; - } - $tth->finish; - chop $ref->{taxaccounts}; + $tth->execute($ref->{parts_id}); + $ref->{taxaccounts} = ""; + while ($ptref = $tth->fetchrow_hashref(NAME_lc)) { + $ref->{taxaccounts} .= "$ptref->{accno} "; + } + $tth->finish; + chop $ref->{taxaccounts}; - $ref->{amount} = $ref->{sellprice} * $ref->{qty}; + $ref->{amount} = $ref->{sellprice} * $ref->{qty}; - push @{ $form->{jcitems} }, $ref; - } - - $sth->finish; + push @{ $form->{jcitems} }, $ref; + } - $query = qq|SELECT value FROM defaults WHERE setting_key = 'curr'|; - ($form->{currency}) = $dbh->selectrow_array($query); - $form->{currency} =~ s/:.*//; - $form->{defaultcurrency} = $form->{currency}; - - $query = qq|SELECT c.accno, t.rate - FROM tax t - JOIN chart c ON (c.id = t.chart_id)|; - $sth = $dbh->prepare($query); - $sth->execute || $form->dberror($query); - while ($ref = $sth->fetchrow_hashref(NAME_lc)) { - $form->{taxaccounts} .= "$ref->{accno} "; - $form->{"$ref->{accno}_rate"} = $ref->{rate}; - } - chop $form->{taxaccounts}; - $sth->finish; + $sth->finish; + + $query = qq|SELECT value FROM defaults WHERE setting_key = 'curr'|; + ($form->{currency}) = $dbh->selectrow_array($query); + $form->{currency} =~ s/:.*//; + $form->{defaultcurrency} = $form->{currency}; + + $query = qq| + SELECT c.accno, t.rate + FROM tax t + JOIN chart c ON (c.id = t.chart_id)|; + $sth = $dbh->prepare($query); + $sth->execute || $form->dberror($query); + while ($ref = $sth->fetchrow_hashref(NAME_lc)) { + $form->{taxaccounts} .= "$ref->{accno} "; + $form->{"$ref->{accno}_rate"} = $ref->{rate}; + } + chop $form->{taxaccounts}; + $sth->finish; - $dbh->disconnect; + $dbh->commit; } sub allocate_projectitems { - my ($self, $myconfig, $form) = @_; + my ($self, $myconfig, $form) = @_; - # connect to database - my $dbh = $form->dbconnect_noauto($myconfig); - - for my $i (1 .. $form->{rowcount}) { - for (split / /, $form->{"jcitems_$i"}) { - my ($id, $qty) = split /:/, $_; - $form->update_balance($dbh, - 'jcitems', - 'allocated', - "id = $id", - $qty); - } - } + my $dbh = $form->{dbh}; + + for my $i (1 .. $form->{rowcount}) { + for (split / /, $form->{"jcitems_$i"}) { + my ($id, $qty) = split /:/, $_; + $form->update_balance( + $dbh, 'jcitems', 'allocated', "id = $id", + $qty); + } + } - $rc = $dbh->commit; - $dbh->disconnect; + $rc = $dbh->commit; - $rc; + $rc; } -- cgit v1.2.3