diff options
| -rw-r--r-- | sql/Pg-upgrade-2.6.17-2.6.18.sql | 22 | ||||
| -rw-r--r-- | utils/notify_short/GPL.txt | 235 | ||||
| -rw-r--r-- | utils/notify_short/README | 14 | ||||
| -rw-r--r-- | utils/notify_short/config.pl | 56 | ||||
| -rw-r--r-- | utils/notify_short/listener.pl | 46 |
5 files changed, 373 insertions, 0 deletions
diff --git a/sql/Pg-upgrade-2.6.17-2.6.18.sql b/sql/Pg-upgrade-2.6.17-2.6.18.sql index 65361cc9..4cf7c9e0 100644 --- a/sql/Pg-upgrade-2.6.17-2.6.18.sql +++ b/sql/Pg-upgrade-2.6.17-2.6.18.sql @@ -10,5 +10,27 @@ CREATE SEQUENCE acctrans_entry_id_seq; ALTER TABLE acc_trans ALTER COLUMN entry_id SET DEFAULT nextval('acctrans_entry_id_seq'); UPDATE acc_trans SET entry_id = nextval('acctrans_entry_id_seq'); ALTER TABLE acc_trans ADD PRIMARY key (entry_id); + + +-- The query rewrite rule necessary to notify the email app that a new report +-- needs to be sent to the designated administrator. +-- By Chris Travers +-- chris@metatrontech.com +-- Licensed under the GNU GPL 2.0 or later at your option. See accompanying +-- GPL.txt + +CREATE OR REPLACE FUNCTION trigger_parts_short() RETURNS TRIGGER +AS +' +BEGIN + IF NEW.onhand >= NEW.rop THEN + NOTIFY parts_short; + END IF; + RETURN NEW; +END; +' LANGUAGE PLPGSQL; + +CREATE TRIGGER parts_short AFTER UPDATE ON parts +FOR EACH ROW EXECUTE PROCEDURE trigger_parts_short(); COMMIT; diff --git a/utils/notify_short/GPL.txt b/utils/notify_short/GPL.txt new file mode 100644 index 00000000..46c855ff --- /dev/null +++ b/utils/notify_short/GPL.txt @@ -0,0 +1,235 @@ + GNU GENERAL PUBLIC LICENSE + Version 2, June 1991 + + Copyright (C) 1989, 1991 Free Software Foundation, Inc. + 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA + Everyone is permitted to copy and distribute verbatim copies + of this license document, but changing it is not allowed. + + Preamble + + The licenses for most software are designed to take away your +freedom to share and change it. By contrast, the GNU General Public +License is intended to guarantee your freedom to share and change free +software--to make sure the software is free for all its users. This +General Public License applies to most of the Free Software +Foundation's software and to any other program whose authors commit to +using it. (Some other Free Software Foundation software is covered by +the GNU Library General Public License instead.) You can apply it to +your programs, too. + + When we speak of free software, we are referring to freedom, not +price. Our General Public Licenses are designed to make sure that you +have the freedom to distribute copies of free software (and charge for +this service if you wish), that you receive source code or can get it +if you want it, that you can change the software or use pieces of it +in new free programs; and that you know you can do these things. + + To protect your rights, we need to make restrictions that forbid +anyone to deny you these rights or to ask you to surrender the rights. +These restrictions translate to certain responsibilities for you if you +distribute copies of the software, or if you modify it. + + For example, if you distribute copies of such a program, whether +gratis or for a fee, you must give the recipients all the rights that +you have. You must make sure that they, too, receive or can get the +source code. And you must show them these terms so they know their +rights. + + We protect your rights with two steps: (1) copyright the software, and +(2) offer you this license which gives you legal permission to copy, +distribute and/or modify the software. + + Also, for each author's protection and ours, we want to make certain +that everyone understands that there is no warranty for this free +software. If the software is modified by someone else and passed on, we +want its recipients to know that what they have is not the original, so +that any problems introduced by others will not reflect on the original +authors' reputations. + + Finally, any free program is threatened constantly by software +patents. We wish to avoid the danger that redistributors of a free +program will individually obtain patent licenses, in effect making the +program proprietary. To prevent this, we have made it clear that any +patent must be licensed for everyone's free use or not licensed at all. + + The precise terms and conditions for copying, distribution and +modification follow. + + GNU GENERAL PUBLIC LICENSE + TERMS AND CONDITIONS FOR COPYING, DISTRIBUTION AND MODIFICATION + + 0. This License applies to any program or other work which contains +a notice placed by the copyright holder saying it may be distributed +under the terms of this General Public License. The "Program", below, +refers to any such program or work, and a "work based on the Program" +means either the Program or any derivative work under copyright law: +that is to say, a work containing the Program or a portion of it, +either verbatim or with modifications and/or translated into another +language. (Hereinafter, translation is included without limitation in +the term "modification".) Each licensee is addressed as "you". + +Activities other than copying, distribution and modification are not +covered by this License; they are outside its scope. The act of +running the Program is not restricted, and the output from the Program +is covered only if its contents constitute a work based on the +Program (independent of having been made by running the Program). +Whether that is true depends on what the Program does. + + 1. You may copy and distribute verbatim copies of the Program's +source code as you receive it, in any medium, provided that you +conspicuously and appropriately publish on each copy an appropriate +copyright notice and disclaimer of warranty; keep intact all the +notices that refer to this License and to the absence of any warranty; +and give any other recipients of the Program a copy of this License +along with the Program. + +You may charge a fee for the physical act of transferring a copy, and +you may at your option offer warranty protection in exchange for a fee. + + 2. You may modify your copy or copies of the Program or any portion +of it, thus forming a work based on the Program, and copy and +distribute such modifications or work under the terms of Section 1 +above, provided that you also meet all of these conditions: + + a) You must cause the modified files to carry prominent notices + stating that you changed the files and the date of any change. + + b) You must cause any work that you distribute or publish, that in + whole or in part contains or is derived from the Program or any + part thereof, to be licensed as a whole at no charge to all third + parties under the terms of this License. + + c) If the modified program normally reads commands interactively + when run, you must cause it, when started running for such + interactive use in the most ordinary way, to print or display an + announcement including an appropriate copyright notice and a + notice that there is no warranty (or else, saying that you provide + a warranty) and that users may redistribute the program under + these conditions, and telling the user how to view a copy of this + License. (Exception: if the Program itself is interactive but + does not normally print such an announcement, your work based on + the Program is not required to print an announcement.) + +These requirements apply to the modified work as a whole. If +identifiable sections of that work are not derived from the Program, +and can be reasonably considered independent and separate works in +themselves, then this License, and its terms, do not apply to those +sections when you distribute them as separate works. But when you +distribute the same sections as part of a whole which is a work based +on the Program, the distribution of the whole must be on the terms of +this License, whose permissions for other licensees extend to the +entire whole, and thus to each and every part regardless of who wrote it. + +Thus, it is not the intent of this section to claim rights or contest +your rights to work written entirely by you; rather, the intent is to +exercise the right to control the distribution of derivative or +collective works based on the Program. + +In addition, mere aggregation of another work not based on the Program +with the Program (or with a work based on the Program) on a volume of +a storage or distribution medium does not bring the other work under +the scope of this License. + + 3. You may copy and distribute the Program (or a work based on it, +under Section 2) in object code or executable form under the terms of +Sections 1 and 2 above provided that you also do one of the following: + + a) Accompany it with the complete corresponding machine-readable + source code, which must be distributed under the terms of Sections + 1 and 2 above on a medium customarily used for software interchange; or, + + b) Accompany it with a written offer, valid for at least three + years, to give any third party, for a charge no more than your + cost of physically performing source distribution, a complete + machine-readable copy of the corresponding source code, to be + distributed under the terms of Sections 1 and 2 above on a medium + customarily used for software interchange; or, + + c) Accompany it with the information you received as to the offer + to distribute corresponding source code. (This alternative is + allowed only for noncommercial distribution and only if you + received the program in object code or executable form with such + an offer, in accord with Subsection b above.) + +The source code for a work means the preferred form of the work for +making modifications to If the Program +specifies a version number of this License which applies to it and "any +later version", you have the option of following the terms and conditions +either of that version or of any later version published by the Free +Software Foundation. If the Program does not specify a version number of +this License, you may choose any version ever published by the Free Software +Foundation. + + 10. If you wish to incorporate parts of the Program into other free +programs whose distribution conditions are different, write to the author +to ask for permission. For software which is copyrighted by the Free +Software Foundation, write to the Free Software Foundation; we sometimes +make exceptions for this. Our decision will be guided by the two goals +of preserving the free status of all derivatives of our free software and +of promoting the sharing and reuse of software generally. + + NO WARRANTY + + 11. BECAUSE THE PROGRAM IS LICENSED FREE OF CHARGE, THERE IS NO WARRANTY +FOR THE PROGRAM, TO THE EXTENT PERMITTED BY APPLICABLE LAW. EXCEPT WHEN +OTHERWISE STATED IN WRITING THE COPYRIGHT HOLDERS AND/OR OTHER PARTIES +PROVIDE THE PROGRAM "AS IS" WITHOUT WARRANTY OF ANY KIND, EITHER EXPRESSED +OR IMPLIED, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF +MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. THE ENTIRE RISK AS +TO THE QUALITY AND PERFORMANCE OF THE PROGRAM IS WITH YOU. SHOULD THE +PROGRAM PROVE DEFECTIVE, YOU ASSUME THE COST OF ALL NECESSARY SERVICING, +REPAIR OR CORRECTION. + + 12. IN NO EVENT UNLESS REQUIRED BY APPLICABLE LAW OR AGREED TO IN WRITING +WILL ANY COPYRIGHT HOLDER, OR ANY OTHER PARTY WHO MAY MODIFY AND/OR +REDISTRIBUTE THE PROGRAM AS PERMITTED ABOVE, BE LIABLE TO YOU FOR DAMAGES, +INCLUDING ANY GENERAL, SPECIAL, INCIDENTAL OR CONSEQUENTIAL DAMAGES ARISING +OUT OF THE USE OR INABILITY TO USE THE PROGRAM (INCLUDING BUT NOT LIMITED +TO LOSS OF DATA OR DATA BEING RENDERED INACCURATE OR LOSSES SUSTAINED BY +YOU OR THIRD PARTIES OR A FAILURE OF THE PROGRAM TO OPERATE WITH ANY OTHER +PROGRAMS), EVEN IF SUCH HOLDER OR OTHER PARTY HAS BEEN ADVISEublished by + the Free Software Foundation; either version 2 of the License, or + (at your option) any later version. + + This program is distributed in the hope that it will be useful, + but WITHOUT ANY WARRANTY; without even the implied warranty of + MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the + GNU General Public License for more details. + + You should have received a copy of the GNU General Public License + along with this program; if not, write to the Free Software + Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA + + +Also add information on how to contact you by electronic and paper mail. + +If the program is interactive, make it output a short notice like this +when it starts in an interactive mode: + + Gnomovision version 69, Copyright (C) year name of author + Gnomovision comes with ABSOLUTELY NO WARRANTY; for details type `show w'. + This is free software, and you are welcome to redistribute it + under certain conditions; type `show c' for details. + +The hypothetical commands `show w' and `show c' should show the appropriate +parts of the General Public License. Of course, the commands you use may +be called something other than `show w' and `show c'; they could even be +mouse-clicks or menu items--whatever suits your program. + +You should also get your employer (if you work as a programmer) or your +school, if any, to sign a "copyright disclaimer" for the program, if +necessary. Here is a sample; alter the names: + + Yoyodyne, Inc., hereby disclaims all copyright interest in the program + `Gnomovision' (which makes passes at compilers) written by James Hacker. + + <signature of Ty Coon>, 1 April 1989 + Ty Coon, President of Vice + +This General Public License does not permit incorporating your program into +proprietary programs. If your program is a subroutine library, you may +consider it more useful to permit linking proprietary applications with the +library. If this is what you want to do, use the GNU Library General +Public License instead of this License. + diff --git a/utils/notify_short/README b/utils/notify_short/README new file mode 100644 index 00000000..267975f8 --- /dev/null +++ b/utils/notify_short/README @@ -0,0 +1,14 @@ +Short Parts Notifier for LedgerSMB +By Chris Travers, Metatron Technology Consulting + +To use this software, edit the config.pl so that you can connect to the +database. Then you can run listener.pl and it will periodically check to see +if new there are parts that have become short through transactions. + +At the moment, the report is triggered when an AR or AP invoice occurs and a +part affected by the transaction is lower than the ROP. When this happens, a +Notify is sent from the database, and the application prepares the report on its +next cycle. Note that although the creation of a new part will not cause it to +trigger the report, the new part will appear on the report on the next run. + +Any feedback, improvements, etc. are welcome. diff --git a/utils/notify_short/config.pl b/utils/notify_short/config.pl new file mode 100644 index 00000000..bd37bed1 --- /dev/null +++ b/utils/notify_short/config.pl @@ -0,0 +1,56 @@ +#!/usr/bin/perl + +use vars qw($email_to $cc_to $email_from $sendmail $database $db_user + $db_passwd $template_head $template_foot); + +# The address to send the mail to. On UNIX systems, multiple addresses can be +# separated by a space. +$email_to = 'manager@example'; + + +# The email address this email is from: +$email_from = 'noreply@example'; + +# The comamnd for sending the email: +$sendmail = "sendmail -f$email_from $email_to"; + +# The database containing SQL-Ledger +$database = "ledger-smb"; + +# The user to connect with. This user only requires select permission to the +# parts table. + +$db_user = "ls-short"; + +# How long between checking for Notify events? In seconds +$cycle_delay = 60; + +# The password for the db user: +$db_passwd = "mypasswd"; + +$template_top = +"From: $email_from +Subject: Parts Short Notice + +Hi. This is the SL-Short listener. You are receiving this message because +a recently issued invoice has reduced the number of onhand items to a level +below its re-order point (ROP). Please see the below report for items currently +at or below their ROP. + +Partnumber Description Onhand ROP +--------------------------------------------------------------------- +"; + +$template_foot = " +Thank you for your attention."; + +format MAIL_TOP = +Partnumber Description Onhand ROP +--------------------------------------------------------------------- +. +format MAIL = +@<<<<<<<<<<<<< @<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<< @>>>> @>>>>> +$partnumber, $description, $avail,$rop +. +1; + diff --git a/utils/notify_short/listener.pl b/utils/notify_short/listener.pl new file mode 100644 index 00000000..37f31d38 --- /dev/null +++ b/utils/notify_short/listener.pl @@ -0,0 +1,46 @@ +#!/usr/bin/perl + +# This is the SL-Short listener. It listens for the "parts_short" signal and +# when the signal comes in, prepares a list of short parts to be sent to +# at least one person. +# +# By Chris Travers, Metatron Technology Consulting +# chris@metatrontech.com +# +# Released under the GNU GPL v2.0 or later. See included GPL.txt for more +# information. + +require "config.pl"; + +use DBI; +my $dsn = "dbi:Pg:dbname=$database"; +my $dbh = DBI->connect($dsn, $db_user, $db_passwd, + { AutoCommit => 1, + PrintError => 0, + RaiseError => 1, } +); + +my $sth; + +$dbh->do("LISTEN parts_short"); +while (1){ # loop infinitely + if ($dbh->func ('pg_notifies')){ + &on_notify; + } + sleep $cycle_delay; +} +sub on_notify { + open (MAIL, "| $sendmail"); + $sth = $dbh->prepare(" + SELECT partnumber, description, onhand, rop FROM parts + WHERE onhand <= rop + "); + $sth->execute; + print MAIL $template_top; + while (($partnumber, $description, $avail, $rop) = $sth->fetchrow_array){ + write MAIL; + } + print MAIL $template_foot; + close MAIL; +} + |