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);
45 $query = qq| INSERT INTO license (licensenumber) VALUES ('$form->{licensenumber}')|;
46 $sth = $dbh->prepare($query);
47 $sth->execute || $form->dberror($query);
50 $query = qq|SELECT l.id FROM license l WHERE l.licensenumber = '$form->{licensenumber}'|;
51 $sth = $dbh->prepare($query);
52 $sth->execute || $form->dberror($query);
53 ($license_id) = $sth->fetchrow_array;
57 $query = qq|UPDATE license SET
58 validuntil = '$form->{validuntil}',
59 licensenumber = '$form->{licensenumber}',
60 parts_id = $form->{parts_id},
61 customer_id = $form->{customer_id},
62 comment = '$form->{comment}',
63 quantity = $form->{quantity}
64 WHERE id=$license_id|;
65 $sth = $dbh->prepare($query);
66 $sth->execute || $form->dberror($query);
69 if ($form->{own_product}) {
70 $form->update_balance($dbh,
73 qq|id = $form->{parts_id}|,
80 $main::lxdebug->leave_sub();
86 $main::lxdebug->enter_sub();
88 my ($self, $myconfig, $form) = @_;
91 my $dbh = $form->dbconnect($myconfig);
93 my $f = $dbh->quote('%' . $form->{"customer_name"} . '%');
94 my $query = qq|SELECT * FROM customer WHERE name ilike $f|;
95 my $sth = $dbh->prepare($query);
96 $sth->execute || $form->dberror($query);
97 $form->{"all_customers"} = [];
98 while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
99 push(@{$form->{"all_customers"}}, $ref);
103 $main::lxdebug->leave_sub();
107 $main::lxdebug->enter_sub();
109 my ($self, $myconfig, $form) = @_;
110 my ($ref, $sth, $f, $s, $query);
111 my $dbh = $form->dbconnect($myconfig);
113 if ($form->{"partnumber"} || $form->{"description"}) {
114 $f = "(parts_id IN (SELECT id FROM parts WHERE ";
115 if ($form->{"partnumber"}) {
116 $f .= "(partnumber ILIKE " .
117 $dbh->quote('%' . $form->{"partnumber"} . '%') . ")";
119 if ($form->{"description"}) {
120 $f .= " AND " if ($form->{"partnumber"});
121 $f .= "(description ILIKE " .
122 $dbh->quote('%' . $form->{"description"} . '%') . ")";
127 if ($form->{"customer_name"}) {
128 $f .= " AND " if ($f);
129 $f .= "(customer_id IN (SELECT id FROM customer WHERE name ILIKE " .
130 $dbh->quote('%' . $form->{"customer_name"} . '%') . "))";
133 if (!$form->{"all"} && $form->{"expiring_in"}) {
134 $f .= " AND " if ($f);
135 $f .= "(validuntil < now() + " .
136 $dbh->quote("" . $form->{"expiring_in"} . " months") . ")";
139 if (!$form->{"show_expired"}) {
140 $f .= " AND " if ($f);
141 $f .= "(validuntil >= now())";
145 $f = "WHERE (inventory_accno_id notnull) AND $f";
147 $f = "WHERE (inventory_accno_id notnull)";
150 if ($form->{"sortby"} eq "partnumber") {
152 } elsif ($form->{"sortby"} eq "description") {
153 $s = "p.description";
154 } elsif ($form->{"sortby"} eq "name") {
156 } elsif ($form->{"sortby"} eq "validuntil") {
161 if ($form->{"sortasc"}) {
167 $query = "SELECT l.*, p.partnumber, p.description, c.name, a.invnumber " .
169 "LEFT JOIN parts p ON (p.id = l.parts_id) " .
170 "LEFT JOIN customer c ON (c.id = l.customer_id) " .
171 "LEFT JOIN ar a ON " .
172 "(a.id = (SELECT i.trans_id FROM invoice i WHERE i.id = " .
173 "(SELECT li.trans_id FROM licenseinvoice li WHERE li.license_id = l.id))) " .
176 $sth = $dbh->prepare($query);
177 $sth->execute() || $form->dberror($query);
178 $form->{"licenses"} = [];
179 while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
180 push(@{$form->{"licenses"}}, $ref);
185 $main::lxdebug->leave_sub();
190 $main::lxdebug->enter_sub();
192 my ($self, $myconfig, $form) = @_;
193 my ($ref, $sth, $query);
194 my $dbh = $form->dbconnect($myconfig);
196 $query = "SELECT l.*, p.partnumber, p.description, c.name, c.street, " .
197 "c.zipcode, c.city, c.country, c.contact, c.phone, c.fax, c.homepage, " .
198 "c.email, c.notes, c.customernumber, c.language, a.invnumber " .
200 "LEFT JOIN parts p ON (p.id = l.parts_id) " .
201 "LEFT JOIN customer c ON (c.id = l.customer_id) " .
202 "LEFT JOIN ar a ON " .
203 "(a.id = (SELECT i.trans_id FROM invoice i WHERE i.id = " .
204 "(SELECT li.trans_id FROM licenseinvoice li WHERE li.license_id = l.id))) " .
205 "LEFT JOIN invoice i ON " .
207 "(SELECT li.trans_id FROM licenseinvoice li WHERE li.license_id = l.id)) " .
208 "WHERE l.id = " . $form->{"id"};
209 $sth = $dbh->prepare($query);
210 $sth->execute() || $form->dberror($query);
211 $form->{"license"} = $sth->fetchrow_hashref(NAME_lc);
214 $main::lxdebug->leave_sub();