summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--Changelog2
-rwxr-xr-xLedgerSMB/IC.pm2
-rwxr-xr-xLedgerSMB/PE.pm2292
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;
}
generated by cgit v1.2.3 (git 2.46.0) at 2024-11-25 11:14:35 +0000