View | Details | Raw Unified | Return to bug 15742
Collapse All | Expand All

(-)a/misc/cronjobs/j2a.pl (-117 / +159 lines)
Lines 17-24 Link Here
17
# You should have received a copy of the GNU General Public License
17
# You should have received a copy of the GNU General Public License
18
# along with Koha; if not, see <http://www.gnu.org/licenses>.
18
# along with Koha; if not, see <http://www.gnu.org/licenses>.
19
19
20
use strict;
20
use Modern::Perl;
21
use warnings;
22
21
23
BEGIN {
22
BEGIN {
24
    # find Koha's Perl modules
23
    # find Koha's Perl modules
Lines 27-33 BEGIN { Link Here
27
    eval { require "$FindBin::Bin/../kohalib.pl" };
26
    eval { require "$FindBin::Bin/../kohalib.pl" };
28
}
27
}
29
28
30
31
use C4::Context;
29
use C4::Context;
32
use C4::Members;
30
use C4::Members;
33
use Getopt::Long;
31
use Getopt::Long;
Lines 43-55 juv2adult.pl - convert juvenile/child patrons from juvenile patron category and Link Here
43
juv2adult.pl [ -b=<branchcode> -f=<categorycode> -t=<categorycode> ]
41
juv2adult.pl [ -b=<branchcode> -f=<categorycode> -t=<categorycode> ]
44
42
45
 Options:
43
 Options:
46
   --help					brief help message
44
   --help   brief help message
47
   --man					full documentation
45
   --man    full documentation
48
   -v						verbose mode
46
   -v       verbose mode
49
   -n						take no action, display only
47
   -n       take no action, display only
50
   -b	<branchname>	only deal with patrons from this library/branch
48
   -b       <branchname>	only deal with patrons from this library/branch
51
   -f	<categorycode>	change patron category from this category
49
   -f       <categorycode>	change patron category from this category
52
   -t	<categorycode>	change patron category to this category
50
   -t       <categorycode>	change patron category to this category
51
53
=head1 OPTIONS
52
=head1 OPTIONS
54
53
55
=over 8
54
=over 8
Lines 101-236 C<juv2adult.pl> -f=<categorycode> -t=<categorycode> -v -n - Processes all branch Link Here
101
# These variables are set by command line options.
100
# These variables are set by command line options.
102
# They are initially set to default values.
101
# They are initially set to default values.
103
102
104
103
my $help     = 0;
105
my $help    = 0;
104
my $man      = 0;
106
my $man     = 0;
105
my $verbose  = 0;
107
my $verbose = 0;
108
my $noaction = 0;
106
my $noaction = 0;
109
my $mybranch;
107
my $mybranch;
110
my $fromcat;
108
my $fromcat;
111
my $tocat;
109
my $tocat;
112
110
113
GetOptions(
111
GetOptions(
114
    'help|?'         => \$help,
112
    'help|?' => \$help,
115
    'man'            => \$man,
113
    'man'    => \$man,
116
    'v'              => \$verbose,
114
    'v'      => \$verbose,
117
    'n'				 => \$noaction,
115
    'n'      => \$noaction,
118
    'f=s'	     => \$fromcat,
116
    'f=s'    => \$fromcat,
119
    't=s'	     => \$tocat,
117
    't=s'    => \$tocat,
120
    'b=s'      => \$mybranch,
118
    'b=s'    => \$mybranch,
121
) or pod2usage(2);
119
) or pod2usage(2);
122
pod2usage(1) if $help;
120
pod2usage(1) if $help;
123
pod2usage( -verbose => 2 ) if $man;
121
pod2usage( -verbose => 2 ) if $man;
124
122
125
if(not $fromcat && $tocat) { #make sure we've specified the info we need.
123
if ( not $fromcat && $tocat ) {    #make sure we've specified the info we need.
126
	print "please specify -help for usage tips.\n";
124
    print "please specify -help for usage tips.\n";
127
		exit;
125
    exit;
128
}
126
}
129
127
130
cronlogaction();
128
cronlogaction();
131
129
132
my $dbh=C4::Context->dbh;
130
my $dbh = C4::Context->dbh;
131
133
#get today's date, format it and subtract upperagelimit
132
#get today's date, format it and subtract upperagelimit
134
my ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) = localtime(time);
133
my ( $sec, $min, $hour, $mday, $mon, $year, $wday, $yday, $isdst ) =
135
$year +=1900;
134
  localtime(time);
136
$mon +=1; if ($mon < 10) {$mon = "0".$mon;}
135
$year += 1900;
137
if ($mday < 10) {$mday = "0".$mday;}
136
$mon  += 1;
137
if ( $mon < 10 )  { $mon  = "0" . $mon; }
138
if ( $mday < 10 ) { $mday = "0" . $mday; }
139
138
# get the upperagelimit from the category to be transitioned from
140
# get the upperagelimit from the category to be transitioned from
139
my $query=qq|SELECT upperagelimit from categories where categorycode =?|;
141
my $query = qq|SELECT upperagelimit from categories where categorycode =?|;
140
my $sth=$dbh->prepare( $query );
142
my $sth   = $dbh->prepare($query);
141
$sth->execute( $fromcat )
143
$sth->execute($fromcat)
142
   or die "Couldn't execute statement: " . $sth->errstr;
144
  or die "Couldn't execute statement: " . $sth->errstr;
143
my $agelimit = $sth->fetchrow_array();
145
my $agelimit = $sth->fetchrow_array();
144
if ( not $agelimit ) {
146
if ( not $agelimit ) {
145
147
    die "No patron category $fromcat. Please try again. \n";
146
	die "No patron category $fromcat. Please try again. \n";
147
}
148
}
148
$query=qq|SELECT categorycode from categories where categorycode=?|;
149
149
$sth=$dbh->prepare( $query );
150
$query = qq|SELECT categorycode from categories where categorycode=?|;
150
$sth->execute( $tocat )
151
$sth   = $dbh->prepare($query);
151
	or die "Couldn't execute statement: " . $sth->errstr;
152
$sth->execute($tocat)
153
  or die "Couldn't execute statement: " . $sth->errstr;
152
my $tocatage = $sth->fetchrow_array();
154
my $tocatage = $sth->fetchrow_array();
153
if ( not $tocatage ){
155
if ( not $tocatage ) {
154
	die "No patron category $tocat. Please try again. \n";
156
    die "No patron category $tocat. Please try again. \n";
155
}
157
}
156
$sth->finish(  );
158
157
$year -=$agelimit;
159
$year -= $agelimit;
158
160
159
$verbose and print "The age limit for category $fromcat is $agelimit\n";
161
$verbose and print "The age limit for category $fromcat is $agelimit\n";
160
162
161
my $itsyourbirthday = "$year-$mon-$mday";
163
my $itsyourbirthday = "$year-$mon-$mday";
162
164
163
165
if ( not $noaction ) {
164
if ( not $noaction) {
166
    if ($mybranch) {    #yep, we received a specific branch to work on.
165
	if ( $mybranch ) { #yep, we received a specific branch to work on.
167
        $verbose and print "Looking for patrons of $mybranch to update from $fromcat to $tocat that were born before $itsyourbirthday\n";
166
		$verbose and print "Looking for patrons of $mybranch to update from $fromcat to $tocat that were born before $itsyourbirthday\n";
168
        my $query = qq|
167
		my $query=qq|UPDATE borrowers
169
            UPDATE borrowers
168
		   SET guarantorid ='0',
170
            SET guarantorid ='0',
169
		    categorycode =?
171
                categorycode = ?
170
		   WHERE dateofbirth<=?
172
            WHERE dateofbirth <= ?
171
		   AND dateofbirth!='0000-00-00'
173
              AND dateofbirth != '0000-00-00'
172
		   AND branchcode=?
174
              AND branchcode = ?
173
		   AND categorycode IN (select categorycode from categories where category_type='C' and categorycode=?)|;
175
              AND categorycode IN (
174
		my $sth=$dbh->prepare($query);
176
                SELECT categorycode
175
		my $res = $sth->execute( $tocat, $itsyourbirthday, $mybranch, $fromcat ) or die "can't execute";
177
                FROM categories
176
		if ($res eq '0E0') { print "No patrons updated\n";
178
                WHERE category_type = 'C'
177
		} else { print "Updated $res patrons\n"; }
179
                  AND categorycode = ?
178
	} else { # branch was not supplied, processing all branches
180
              )|;
179
		$verbose and print "Looking in all branches for patrons to update from $fromcat to $tocat that were born before $itsyourbirthday\n";
181
        my $sth = $dbh->prepare($query);
180
			my $query=qq|UPDATE borrowers
182
        my $res = $sth->execute( $tocat, $itsyourbirthday, $mybranch, $fromcat )
181
			   SET guarantorid ='0',
183
          or die "can't execute";
182
			    categorycode =?
184
183
			   WHERE dateofbirth<=?
185
        if ( $res eq '0E0' ) {
184
			   AND dateofbirth!='0000-00-00'
186
            print "No patrons updated\n";
185
			   AND categorycode IN (select categorycode from categories where category_type='C' and categorycode=?)|;
187
        }
186
			my $sth=$dbh->prepare($query);
188
        else {
187
			my $res = $sth->execute( $tocat, $itsyourbirthday, $fromcat ) or die "can't execute";
189
            print "Updated $res patrons\n";
188
			if ($res eq '0E0') { print "No patrons updated\n";
190
        }
189
			} else { print "Updated $res patrons\n"; }
191
    }
190
	}
192
    else {    # branch was not supplied, processing all branches
191
} else {
193
        $verbose and print "Looking in all branches for patrons to update from $fromcat to $tocat that were born before $itsyourbirthday\n";
192
	my $birthday;
194
        my $query = qq|
193
	if ( $mybranch ) {
195
            UPDATE borrowers
194
		$verbose and print "Displaying patrons that would be updated from $fromcat to $tocat from $mybranch\n";
196
            SET guarantorid = '0',
195
		my $query=qq|SELECT firstname,
197
                categorycode = ?
196
		 surname,
198
            WHERE dateofbirth <= ?
197
		 cardnumber,
199
              AND dateofbirth!='0000-00-00'
198
		 dateofbirth
200
              AND categorycode IN (
199
		FROM borrowers
201
                SELECT categorycode
200
		WHERE dateofbirth<=?
202
                FROM categories
201
		AND dateofbirth!='0000-00-00'
203
                WHERE category_type = 'C'
202
		AND branchcode=?
204
                  AND categorycode = ?
203
		AND categorycode IN (select categorycode from categories where category_type='C' and categorycode=?)|;
205
              )|;
204
		my $sth=$dbh->prepare( $query );
206
        my $sth = $dbh->prepare($query);
205
		$sth->execute( $itsyourbirthday, $mybranch, $fromcat )
207
        my $res = $sth->execute( $tocat, $itsyourbirthday, $fromcat )
206
		   or die "Couldn't execute statement: " . $sth->errstr;
208
          or die "can't execute";
207
		while ( my @res = $sth->fetchrow_array()) {
209
208
	            my $firstname = $res[0];
210
        if ( $res eq '0E0' ) {
209
	            my $surname = $res[1];
211
            print "No patrons updated\n";
210
	            my $barcode = $res[2];
212
        }
211
	            $birthday = $res[3];
213
        else {
212
	            print "$firstname $surname $barcode $birthday\n";
214
            print "Updated $res patrons\n";
213
	          }
215
        }
214
	} else {
216
    }
215
		$verbose and print "Displaying patrons that would be updated from $fromcat to $tocat.\n";
217
}
216
		my $query=qq|SELECT firstname,
218
else {
217
		 surname,
219
    my $birthday;
218
		 cardnumber,
220
    if ($mybranch) {
219
		 dateofbirth
221
        $verbose and print "Displaying patrons that would be updated from $fromcat to $tocat from $mybranch\n";
220
		FROM borrowers
222
        my $query = qq|
221
		WHERE dateofbirth<=?
223
            SELECT firstname,
222
		AND dateofbirth!='0000-00-00'
224
                   surname,
223
		AND categorycode IN (select categorycode from categories where category_type='C' and categorycode=?)|;
225
                   cardnumber,
224
		my $sth=$dbh->prepare( $query );
226
                   dateofbirth
225
		$sth->execute( $itsyourbirthday, $fromcat )
227
            FROM borrowers
226
		   or die "Couldn't execute statement: " . $sth->errstr;
228
            WHERE dateofbirth <= ?
227
		while ( my @res = $sth->fetchrow_array()) {
229
              AND dateofbirth != '0000-00-00'
228
	            my $firstname = $res[0];
230
              AND branchcode = ?
229
	            my $surname = $res[1];
231
              AND categorycode IN (
230
	            my $barcode = $res[2];
232
                SELECT categorycode
231
	            $birthday = $res[3];
233
                FROM categories
232
	            print "$firstname $surname $barcode $birthday\n";
234
                WHERE category_type = 'C'
233
	          }
235
                  AND categorycode = ?
234
	}
236
              )
235
	$sth->finish(  );
237
        |;
238
        my $sth = $dbh->prepare($query);
239
        $sth->execute( $itsyourbirthday, $mybranch, $fromcat )
240
          or die "Couldn't execute statement: " . $sth->errstr;
241
242
        while ( my @res = $sth->fetchrow_array() ) {
243
            my $firstname = $res[0];
244
            my $surname   = $res[1];
245
            my $barcode   = $res[2];
246
            $birthday = $res[3];
247
            print "$firstname $surname $barcode $birthday\n";
248
        }
249
    }
250
    else {
251
        $verbose and print "Displaying patrons that would be updated from $fromcat to $tocat.\n";
252
        my $query = qq|
253
            SELECT firstname,
254
                   surname,
255
                   cardnumber,
256
                   dateofbirth
257
            FROM borrowers
258
            WHERE dateofbirth <= ?
259
              AND dateofbirth != '0000-00-00'
260
              AND categorycode IN (
261
                SELECT categorycode
262
                FROM categories
263
                WHERE category_type = 'C'
264
                  AND categorycode = ?
265
              )
266
        |;
267
        my $sth = $dbh->prepare($query);
268
        $sth->execute( $itsyourbirthday, $fromcat )
269
          or die "Couldn't execute statement: " . $sth->errstr;
270
271
        while ( my @res = $sth->fetchrow_array() ) {
272
            my $firstname = $res[0];
273
            my $surname   = $res[1];
274
            my $barcode   = $res[2];
275
            $birthday = $res[3];
276
            print "$firstname $surname $barcode $birthday\n";
277
        }
278
    }
236
}
279
}
237
- 

Return to bug 15742