1 #=====================================================================
4 # Based on SQL-Ledger Version 2.1.9
5 # Web http://www.lx-office.org
7 #=====================================================================
8 # SQL-Ledger Accounting
11 # Author: Philip Reetz
12 # Email: p.reetz@linet-services.de
13 # Web: http://www.linet-services.de/
16 # This program is free software; you can redistribute it and/or modify
17 # it under the terms of the GNU General Public License as published by
18 # the Free Software Foundation; either version 2 of the License, or
19 # (at your option) any later version.
21 # This program is distributed in the hope that it will be useful,
22 # but WITHOUT ANY WARRANTY; without even the implied warranty of
23 # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
24 # GNU General Public License for more details.
25 # You should have received a copy of the GNU General Public License
26 # along with this program; if not, write to the Free Software
27 # Foundation, Inc., 675 Mass Ave, Cambridge, MA 02139, USA.
28 #======================================================================
30 # Software license module
32 #======================================================================
39 $main::lxdebug->enter_sub();
41 my ($self, $myconfig, $form) = @_;
43 $dbh = $form->dbconnect($myconfig);
46 qq| INSERT INTO license (licensenumber) VALUES ('$form->{licensenumber}')|;
47 $sth = $dbh->prepare($query);
48 $sth->execute || $form->dberror($query);
52 qq|SELECT l.id FROM license l WHERE l.licensenumber = '$form->{licensenumber}'|;
53 $sth = $dbh->prepare($query);
54 $sth->execute || $form->dberror($query);
55 ($license_id) = $sth->fetchrow_array;
59 $query = qq|UPDATE license SET
60 validuntil = '$form->{validuntil}',
61 licensenumber = '$form->{licensenumber}',
62 parts_id = $form->{parts_id},
63 customer_id = $form->{customer_id},
64 comment = '$form->{comment}',
65 quantity = $form->{quantity}
66 WHERE id=$license_id|;
67 $sth = $dbh->prepare($query);
68 $sth->execute || $form->dberror($query);
73 $main::lxdebug->leave_sub();
79 $main::lxdebug->enter_sub();
81 my ($self, $myconfig, $form) = @_;
84 my $dbh = $form->dbconnect($myconfig);
86 my $f = $dbh->quote('%' . $form->{"customer_name"} . '%');
87 my $query = qq|SELECT * FROM customer WHERE name ilike $f|;
88 my $sth = $dbh->prepare($query);
89 $sth->execute || $form->dberror($query);
90 $form->{"all_customers"} = [];
91 while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
92 push(@{ $form->{"all_customers"} }, $ref);
96 $main::lxdebug->leave_sub();
100 $main::lxdebug->enter_sub();
102 my ($self, $myconfig, $form) = @_;
103 my ($ref, $sth, $f, $s, $query);
104 my $dbh = $form->dbconnect($myconfig);
106 if ($form->{"partnumber"} || $form->{"description"}) {
107 $f = "(parts_id IN (SELECT id FROM parts WHERE ";
108 if ($form->{"partnumber"}) {
111 . $dbh->quote('%' . $form->{"partnumber"} . '%') . ")";
113 if ($form->{"description"}) {
114 $f .= " AND " if ($form->{"partnumber"});
116 "(description ILIKE "
117 . $dbh->quote('%' . $form->{"description"} . '%') . ")";
122 if ($form->{"customer_name"}) {
123 $f .= " AND " if ($f);
125 "(l.customer_id IN (SELECT id FROM customer WHERE name ILIKE "
126 . $dbh->quote('%' . $form->{"customer_name"} . '%') . "))";
129 if (!$form->{"all"} && $form->{"expiring_in"}) {
130 $f .= " AND " if ($f);
132 "(validuntil < now() + "
133 . $dbh->quote("" . $form->{"expiring_in"} . " months") . ")";
136 if (!$form->{"show_expired"}) {
137 $f .= " AND " if ($f);
138 $f .= "(validuntil >= now())";
142 $f = "WHERE (inventory_accno_id notnull) AND $f";
144 $f = "WHERE (inventory_accno_id notnull)";
147 if ($form->{"sortby"} eq "partnumber") {
149 } elsif ($form->{"sortby"} eq "description") {
150 $s = "p.description";
151 } elsif ($form->{"sortby"} eq "name") {
153 } elsif ($form->{"sortby"} eq "validuntil") {
158 if ($form->{"sortasc"}) {
165 "SELECT l.*, p.partnumber, p.description, c.name, a.invnumber "
167 . "LEFT JOIN parts p ON (p.id = l.parts_id) "
168 . "LEFT JOIN customer c ON (c.id = l.customer_id) "
169 . "LEFT JOIN ar a ON "
170 . "(a.id = (SELECT i.trans_id FROM invoice i WHERE i.id = "
171 . "(SELECT li.trans_id FROM licenseinvoice li WHERE li.license_id = l.id))) "
174 $sth = $dbh->prepare($query);
175 $sth->execute() || $form->dberror($query);
176 $form->{"licenses"} = [];
177 while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
178 push(@{ $form->{"licenses"} }, $ref);
183 $main::lxdebug->leave_sub();
187 $main::lxdebug->enter_sub();
189 my ($self, $myconfig, $form) = @_;
190 my ($ref, $sth, $query);
191 my $dbh = $form->dbconnect($myconfig);
194 "SELECT l.*, p.partnumber, p.description, c.name, c.street, "
195 . "c.zipcode, c.city, c.country, c.contact, c.phone, c.fax, c.homepage, "
196 . "c.email, c.notes, c.customernumber, c.language, a.invnumber "
198 . "LEFT JOIN parts p ON (p.id = l.parts_id) "
199 . "LEFT JOIN customer c ON (c.id = l.customer_id) "
200 . "LEFT JOIN ar a ON "
201 . "(a.id = (SELECT i.trans_id FROM invoice i WHERE i.id = "
202 . "(SELECT li.trans_id FROM licenseinvoice li WHERE li.license_id = l.id))) "
203 . "LEFT JOIN invoice i ON "
205 . "(SELECT li.trans_id FROM licenseinvoice li WHERE li.license_id = l.id)) "
208 $sth = $dbh->prepare($query);
209 $sth->execute() || $form->dberror($query);
210 $form->{"license"} = $sth->fetchrow_hashref(NAME_lc);
213 $main::lxdebug->leave_sub();