Initial revision
[wip/koha-chris_n.git] / misc / fixborrower.pl
1 #!/usr/bin/perl
2
3 use C4::Database;
4 use strict;
5
6 my $dbh=C4Connect;
7 my $query = "Select * from categories where (categorycode like 'L%' or categorycode like 'F%'
8 or categorycode like 'S%' or categorycode like 'O%' or categorycode like 'H%') and (categorycode <>'HR' 
9 and categorycode <> 'ST')";
10 my $sth=$dbh->prepare($query);
11 $sth->execute;
12 while (my $data=$sth->fetchrow_hashref){
13   #update borrowers corresponding
14   #update categories
15     my $temp=substr($data->{'categorycode'},0,1);
16   $query="update borrowers set area='$temp' where categorycode='$data->{'categorycode'}'";
17   my $sth2=$dbh->prepare($query);
18   $sth2->execute;
19   $sth2->finish;
20   $temp=substr($data->{'categorycode'},1,1);
21   $query="update borrowers set categorycode='$temp' where categorycode='$data->{'categorycode'}'"; 
22   $sth2=$dbh->prepare($query);
23   $sth2->execute;
24   $sth2->finish;
25    $query="delete from categories where categorycode='$data->{'categorycode'}'";
26   $sth2=$dbh->prepare($query);
27   $sth2->execute;
28   $sth2->finish;
29
30 }
31
32 $query = "Select * from categories where (categorycode like 'V%') and (categorycode <>'HR' 
33 and categorycode <> 'ST')";
34 my $sth=$dbh->prepare($query);
35 $sth->execute;
36 while (my $data=$sth->fetchrow_hashref){
37   #update borrowers corresponding
38   #update categories
39 #    my $temp=substr($data->{'categorycode'},0,1);
40   $query="update borrowers set area='V' where categorycode='$data->{'categorycode'}'";
41   my $sth2=$dbh->prepare($query);
42   $sth2->execute;
43   $sth2->finish;
44   my $temp=substr($data->{'categorycode'},1,1);
45   $query="update borrowers set categorycode='$temp' where categorycode='$data->{'categorycode'}'"; 
46   $sth2=$dbh->prepare($query);
47   $sth2->execute;
48   $sth2->finish;
49    $query="delete from categories where categorycode='$data->{'categorycode'}'";
50   $sth2=$dbh->prepare($query);
51   $sth2->execute;
52   $sth2->finish;
53
54 }
55
56 my $query = "Select * from categories where categorycode = 'ST'";
57 my $sth=$dbh->prepare($query);
58 $sth->execute;
59 while (my $data=$sth->fetchrow_hashref){
60   #update borrowers corresponding
61   #update categories
62   $query="update borrowers set area='' where categorycode='$data->{'categorycode'}'";
63   my $sth2=$dbh->prepare($query);
64   $sth2->execute;
65   $sth2->finish;
66   $query="update borrowers set categorycode='W' where categorycode='$data->{'categorycode'}'"; 
67   $sth2=$dbh->prepare($query);
68   $sth2->execute;
69   $sth2->finish;
70    $query="delete from categories where categorycode='$data->{'categorycode'}'";
71   $sth2=$dbh->prepare($query);
72   $sth2->execute;
73   $sth2->finish;
74
75 }
76
77 my $query = "Select * from categories where categorycode = 'BR' or categorycode='CO' or categorycode='IS'";
78 my $sth=$dbh->prepare($query);
79 $sth->execute;
80 while (my $data=$sth->fetchrow_hashref){
81   #update borrowers corresponding
82   #update categories
83   $query="update borrowers set area='' where categorycode='$data->{'categorycode'}'";
84   my $sth2=$dbh->prepare($query);
85   $sth2->execute;
86   $sth2->finish;
87   $query="update borrowers set categorycode='I' where categorycode='$data->{'categorycode'}'"; 
88   $sth2=$dbh->prepare($query);
89   $sth2->execute;
90   $sth2->finish;
91    $query="delete from categories where categorycode='$data->{'categorycode'}'";
92   $sth2=$dbh->prepare($query);
93   $sth2->execute;
94   $sth2->finish;
95
96 }
97 my $query = "Select * from categories where categorycode = 'TD'  or categorycode='TR'";
98 my $sth=$dbh->prepare($query);
99 $sth->execute;
100 while (my $data=$sth->fetchrow_hashref){
101   #update borrowers corresponding
102   #update categories
103   $query="update borrowers set area='X' where categorycode='$data->{'categorycode'}'";
104   my $sth2=$dbh->prepare($query);
105   $sth2->execute;
106   $sth2->finish;
107   $query="update borrowers set categorycode='A' where categorycode='$data->{'categorycode'}'"; 
108   $sth2=$dbh->prepare($query);
109   $sth2->execute;
110   $sth2->finish;
111    $query="delete from categories where categorycode='$data->{'categorycode'}'";
112   $sth2=$dbh->prepare($query);
113   $sth2->execute;
114   $sth2->finish;
115
116 }
117
118 my $query = "Select * from categories where categorycode = 'HR'";
119 my $sth=$dbh->prepare($query);
120 $sth->execute;
121 while (my $data=$sth->fetchrow_hashref){
122   #update borrowers corresponding
123   #update categories
124   $query="update borrowers set area='K' where categorycode='$data->{'categorycode'}'";
125   my $sth2=$dbh->prepare($query);
126   $sth2->execute;
127   $sth2->finish;
128   $query="update borrowers set categorycode='A' where categorycode='$data->{'categorycode'}'"; 
129   $sth2=$dbh->prepare($query);
130   $sth2->execute;
131   $sth2->finish;
132    $query="delete from categories where categorycode='$data->{'categorycode'}'";
133   $sth2=$dbh->prepare($query);
134   $sth2->execute;
135   $sth2->finish;
136
137 }
138
139 my $query = "Select * from categories where categorycode = 'IL'";
140 my $sth=$dbh->prepare($query);
141 $sth->execute;
142 while (my $data=$sth->fetchrow_hashref){
143   #update borrowers corresponding
144   #update categories
145   $query="update borrowers set area='Z' where categorycode='$data->{'categorycode'}'";
146   my $sth2=$dbh->prepare($query);
147   $sth2->execute;
148   $sth2->finish;
149   $query="update borrowers set categorycode='L' where categorycode='$data->{'categorycode'}'"; 
150   $sth2=$dbh->prepare($query);
151   $sth2->execute;
152   $sth2->finish;
153    $query="delete from categories where categorycode='$data->{'categorycode'}'";
154   $sth2=$dbh->prepare($query);
155   $sth2->execute;
156   $sth2->finish;
157
158 }
159 my $query = "Select * from categories where categorycode = 'TB'";
160 my $sth=$dbh->prepare($query);
161 $sth->execute;
162 while (my $data=$sth->fetchrow_hashref){
163   #update borrowers corresponding
164   #update categories
165   $query="update borrowers set area='' where categorycode='$data->{'categorycode'}'";
166   my $sth2=$dbh->prepare($query);
167   $sth2->execute;
168   $sth2->finish;
169   $query="update borrowers set categorycode='P' where categorycode='$data->{'categorycode'}'"; 
170   $sth2=$dbh->prepare($query);
171   $sth2->execute;
172   $sth2->finish;
173    $query="delete from categories where categorycode='$data->{'categorycode'}'";
174   $sth2=$dbh->prepare($query);
175   $sth2->execute;
176   $sth2->finish;
177
178 }
179
180 $sth->finish;
181 $query="insert into categories values ('A','Adult',5,99,0,'A',0,0,0,99,1)";
182 $sth=$dbh->prepare($query);
183 $sth->execute;
184 $sth->finish;
185 $query="insert into categories values ('E','Senior Citizen',5,99,0,'A',0,0,0,99,1)";                                                                
186 $sth=$dbh->prepare($query);                                                                                                                       
187 $sth->execute;                                                                                                                                    
188 $sth->finish;    
189 $query="insert into categories values ('C','Child',5,16,0,'A',0,0,0,99,0)";
190 $sth=$dbh->prepare($query);
191 $sth->execute;
192 $sth->finish;
193 $query="insert into categories values ('B','Housebound',5,99,0,'E',0,0,0,99,0)";
194 $sth=$dbh->prepare($query);
195 $sth->execute;
196 $sth->finish;
197 $query="insert into categories values ('F','Family',5,99,0,'A',0,0,0,99,1)";
198 $sth=$dbh->prepare($query);
199 $sth->execute;
200 $sth->finish;
201 $query="insert into categories values ('W','Workers',5,99,0,'A',0,0,0,99,0)";
202 $sth=$dbh->prepare($query);
203 $sth->execute;
204 $sth->finish;
205 $query="insert into categories values ('I','Institution',5,99,0,'A',0,0,0,99,0)";
206 $sth=$dbh->prepare($query);
207 $sth->execute;
208 $sth->finish;
209 $query="insert into categories values ('P','Privileged',5,99,0,'A',0,0,0,99,0)";
210 $sth=$dbh->prepare($query);
211 $sth->execute;
212 $sth->finish;
213 $query="insert into categories values ('L','Library',5,99,0,'A',0,0,0,99,0)";
214 $sth=$dbh->prepare($query);
215 $sth->execute;
216 $sth->finish;
217
218
219
220 $dbh->disconnect;