1 #=====================================================================
4 # Based on SQL-Ledger Version 2.1.9
5 # Web http://www.lx-office.org
7 #=====================================================================
8 # SQL-Ledger Accounting
9 # Copyright (C) 1998-2002
11 # Author: Dieter Simader
12 # Email: dsimader@sql-ledger.org
13 # Web: http://www.sql-ledger.org
17 # This program is free software; you can redistribute it and/or modify
18 # it under the terms of the GNU General Public License as published by
19 # the Free Software Foundation; either version 2 of the License, or
20 # (at your option) any later version.
22 # This program is distributed in the hope that it will be useful,
23 # but WITHOUT ANY WARRANTY; without even the implied warranty of
24 # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
25 # GNU General Public License for more details.
26 # You should have received a copy of the GNU General Public License
27 # along with this program; if not, write to the Free Software
28 # Foundation, Inc., 675 Mass Ave, Cambridge, MA 02139, USA.
29 #======================================================================
31 # Partsgroups and pricegroups
33 #======================================================================
45 $main::lxdebug->enter_sub();
47 my ($self, $myconfig, $form) = @_;
50 my $dbh = SL::DB->client->dbh;
54 if ($form->{partsgroup}) {
55 $where .= qq| AND partsgroup ILIKE ?|;
56 push(@values, like($form->{partsgroup}));
59 if ($form->{status} eq 'orphaned') {
62 qq| (SELECT DISTINCT partsgroup_id FROM parts | .
63 qq| WHERE NOT partsgroup_id ISNULL | .
65 qq| SELECT DISTINCT partsgroup_id FROM custom_variable_config_partsgroups | .
66 qq| WHERE NOT partsgroup_id ISNULL) |;
69 substr($where, 0, 4) = "WHERE " if ($where);
71 my $sortorder = $form->{sort} ? $form->{sort} : "partsgroup";
72 $sortorder =~ s/[^a-z_]//g;
75 qq|SELECT id, partsgroup FROM partsgroup | .
77 qq|ORDER BY $sortorder|;
79 $form->{item_list} = selectall_hashref_query($form, $dbh, $query, @values);
81 $main::lxdebug->leave_sub();
83 return scalar(@{ $form->{item_list} });
87 $main::lxdebug->enter_sub();
89 my ($self, $myconfig, $form) = @_;
92 my $dbh = SL::DB->client->dbh;
94 $form->{discount} /= 100;
96 my @values = ($form->{partsgroup});
100 $query = qq|UPDATE partsgroup SET partsgroup = ? WHERE id = ?|;
101 push(@values, $form->{id});
103 $query = qq|INSERT INTO partsgroup (partsgroup) VALUES (?)|;
105 do_query($form, $dbh, $query, @values);
107 $main::lxdebug->leave_sub();
111 $main::lxdebug->enter_sub();
113 my ($self, $myconfig, $form) = @_;
115 # connect to database
116 my $dbh = SL::DB->client->dbh;
120 qq|(SELECT COUNT(*) FROM parts WHERE partsgroup_id = ?) = 0 AS orphaned | .
121 qq|FROM partsgroup pg | .
123 my $sth = prepare_execute_query($form, $dbh, $query, $form->{id},
125 my $ref = $sth->fetchrow_hashref("NAME_lc");
127 map({ $form->{$_} = $ref->{$_} } keys(%{$ref}));
130 # also not orphaned if partsgroup is selected for a cvar filter
131 if ($form->{orphaned}) {
132 my $cvar_count = scalar( @{ SL::DB::PartsGroup->new(id => $form->{id})->custom_variable_configs } );
133 $form->{orphaned} = !$cvar_count;
136 $main::lxdebug->leave_sub();
140 $main::lxdebug->enter_sub();
142 my ($self, $myconfig, $form) = @_;
144 # connect to database
145 SL::DB->client->with_transaction(sub {
146 my $dbh = SL::DB->client->dbh;
148 my $table = $form->{type} eq "pricegroup" ? "pricegroup" : "partsgroup";
150 my $query = qq|DELETE FROM $table WHERE id = ?|;
151 do_query($form, $dbh, $query, $form->{id});
153 }) or do { die SL::DB->client->error };
155 $main::lxdebug->leave_sub();
158 ##########################
159 # get pricegroups from database
162 $main::lxdebug->enter_sub();
164 my ($self, $myconfig, $form) = @_;
166 # connect to database
167 my $dbh = SL::DB->client->dbh;
169 my ($where, @values);
171 if ($form->{pricegroup}) {
172 $where .= qq| AND pricegroup ILIKE ?|;
173 push(@values, like($form->{pricegroup}));
176 if ($form->{status} eq 'orphaned') {
179 $where .= qq| AND id NOT IN (|;
180 foreach my $table (qw(invoice orderitems prices)) {
181 $where .= "UNION " unless ($first);
184 qq|SELECT DISTINCT pricegroup_id FROM $table | .
185 qq|WHERE NOT pricegroup_id ISNULL |;
190 substr($where, 0, 4) = "WHERE " if ($where);
192 my $sortorder = $form->{sort} ? $form->{sort} : "pricegroup";
193 $sortorder =~ s/[^a-z_]//g;
196 qq|SELECT id, pricegroup FROM pricegroup | .
198 qq|ORDER BY $sortorder|;
200 $form->{item_list} = selectall_hashref_query($form, $dbh, $query, @values);
202 $main::lxdebug->leave_sub();
204 return scalar(@{ $form->{item_list} });
207 ########################
208 # save pricegruop to database
210 sub save_pricegroup {
211 $main::lxdebug->enter_sub();
213 my ($self, $myconfig, $form) = @_;
215 SL::DB->client->with_transaction(sub {
216 my $dbh = SL::DB->client->dbh;
219 $form->{discount} /= 100;
221 my @values = ($form->{pricegroup});
224 $query = qq|UPDATE pricegroup SET pricegroup = ? WHERE id = ? |;
225 push(@values, $form->{id});
227 $query = qq|INSERT INTO pricegroup (pricegroup) VALUES (?)|;
229 do_query($form, $dbh, $query, @values);
231 }) or do { die SL::DB->client->error };
233 $main::lxdebug->leave_sub();
236 ############################
237 # get one pricegroup from database
240 $main::lxdebug->enter_sub();
242 my ($self, $myconfig, $form) = @_;
244 # connect to database
245 my $dbh = SL::DB->client->dbh;
247 my $query = qq|SELECT id, pricegroup FROM pricegroup WHERE id = ?|;
248 my $sth = prepare_execute_query($form, $dbh, $query, $form->{id});
249 my $ref = $sth->fetchrow_hashref("NAME_lc");
251 map({ $form->{$_} = $ref->{$_} } keys(%{$ref}));
258 $query = qq|SELECT |;
259 foreach my $table (qw(invoice orderitems prices)) {
260 $query .= " + " unless ($first);
262 $query .= qq|(SELECT COUNT(*) FROM $table WHERE pricegroup_id = ?) |;
263 push(@values, $form->{id});
266 ($form->{orphaned}) = selectrow_query($form, $dbh, $query, @values);
267 $form->{orphaned} = !$form->{orphaned};
269 $main::lxdebug->leave_sub();