3 # This script will convert a database into the newer, proper
12 my $query = "Select * from categories where (categorycode like 'L%' or categorycode like 'F%'
13 or categorycode like 'S%' or categorycode like 'O%' or categorycode like 'H%') and (categorycode <>'HR'
14 and categorycode <> 'ST')";
16 my $sth=$dbh->prepare($query);
19 while (my $data=$sth->fetchrow_hashref){
20 #update borrowers corresponding
23 my $temp=substr($data->{'categorycode'},0,1);
24 $query="update borrowers set area='$temp' where categorycode='$data->{'categorycode'}'";
25 my $sth2=$dbh->prepare($query);
29 $temp=substr($data->{'categorycode'},1,1);
30 $query="update borrowers set categorycode='$temp' where categorycode='$data->{'categorycode'}'";
31 $sth2=$dbh->prepare($query);
35 $query="delete from categories where categorycode='$data->{'categorycode'}'";
36 $sth2=$dbh->prepare($query);
42 $query = "Select * from categories where (categorycode like 'V%') and (categorycode <>'HR'
43 and categorycode <> 'ST')";
45 my $sth=$dbh->prepare($query);
48 while (my $data=$sth->fetchrow_hashref){
49 #update borrowers corresponding
51 # my $temp=substr($data->{'categorycode'},0,1);
52 $query="update borrowers set area='V' where categorycode='$data->{'categorycode'}'";
53 my $sth2=$dbh->prepare($query);
57 my $temp=substr($data->{'categorycode'},1,1);
58 $query="update borrowers set categorycode='$temp' where categorycode='$data->{'categorycode'}'";
59 $sth2=$dbh->prepare($query);
63 $query="delete from categories where categorycode='$data->{'categorycode'}'";
64 $sth2=$dbh->prepare($query);
70 my $query = "Select * from categories where categorycode = 'ST'";
71 my $sth=$dbh->prepare($query);
74 while (my $data=$sth->fetchrow_hashref){
75 #update borrowers corresponding
77 $query="update borrowers set area='' where categorycode='$data->{'categorycode'}'";
78 my $sth2=$dbh->prepare($query);
82 $query="update borrowers set categorycode='W' where categorycode='$data->{'categorycode'}'";
83 $sth2=$dbh->prepare($query);
87 $query="delete from categories where categorycode='$data->{'categorycode'}'";
88 $sth2=$dbh->prepare($query);
94 my $query = "Select * from categories where categorycode = 'BR' or categorycode='CO' or categorycode='IS'";
95 my $sth=$dbh->prepare($query);
98 while (my $data=$sth->fetchrow_hashref){
99 #update borrowers corresponding
101 $query="update borrowers set area='' where categorycode='$data->{'categorycode'}'";
102 my $sth2=$dbh->prepare($query);
106 $query="update borrowers set categorycode='I' where categorycode='$data->{'categorycode'}'";
107 $sth2=$dbh->prepare($query);
111 $query="delete from categories where categorycode='$data->{'categorycode'}'";
112 $sth2=$dbh->prepare($query);
118 my $query = "Select * from categories where categorycode = 'TD' or categorycode='TR'";
119 my $sth=$dbh->prepare($query);
122 while (my $data=$sth->fetchrow_hashref){
123 #update borrowers corresponding
125 $query="update borrowers set area='X' where categorycode='$data->{'categorycode'}'";
126 my $sth2=$dbh->prepare($query);
130 $query="update borrowers set categorycode='A' where categorycode='$data->{'categorycode'}'";
131 $sth2=$dbh->prepare($query);
135 $query="delete from categories where categorycode='$data->{'categorycode'}'";
136 $sth2=$dbh->prepare($query);
142 my $query = "Select * from categories where categorycode = 'HR'";
143 my $sth=$dbh->prepare($query);
146 while (my $data=$sth->fetchrow_hashref){
147 #update borrowers corresponding
149 $query="update borrowers set area='K' where categorycode='$data->{'categorycode'}'";
150 my $sth2=$dbh->prepare($query);
154 $query="update borrowers set categorycode='A' where categorycode='$data->{'categorycode'}'";
155 $sth2=$dbh->prepare($query);
159 $query="delete from categories where categorycode='$data->{'categorycode'}'";
160 $sth2=$dbh->prepare($query);
166 my $query = "Select * from categories where categorycode = 'IL'";
167 my $sth=$dbh->prepare($query);
170 while (my $data=$sth->fetchrow_hashref){
171 #update borrowers corresponding
173 $query="update borrowers set area='Z' where categorycode='$data->{'categorycode'}'";
174 my $sth2=$dbh->prepare($query);
178 $query="update borrowers set categorycode='L' where categorycode='$data->{'categorycode'}'";
179 $sth2=$dbh->prepare($query);
183 $query="delete from categories where categorycode='$data->{'categorycode'}'";
184 $sth2=$dbh->prepare($query);
190 my $query = "Select * from categories where categorycode = 'TB'";
191 my $sth=$dbh->prepare($query);
193 while (my $data=$sth->fetchrow_hashref){
194 #update borrowers corresponding
197 $query="update borrowers set area='' where categorycode='$data->{'categorycode'}'";
198 my $sth2=$dbh->prepare($query);
202 $query="update borrowers set categorycode='P' where categorycode='$data->{'categorycode'}'";
203 $sth2=$dbh->prepare($query);
207 $query="delete from categories where categorycode='$data->{'categorycode'}'";
208 $sth2=$dbh->prepare($query);
217 ("insert into categories values ('E','Senior Citizen',5,99,0,'A',0,0,0,99,1)",
218 "insert into categories values ('A','Adult',5,99,0,'A',0,0,0,99,1)",
219 "insert into categories values ('C','Child',5,16,0,'A',0,0,0,99,0)",
220 "insert into categories values ('B','Housebound',5,99,0,'E',0,0,0,99,0)",
221 "insert into categories values ('F','Family',5,99,0,'A',0,0,0,99,1)",
222 "insert into categories values ('W','Workers',5,99,0,'A',0,0,0,99,0)",
223 "insert into categories values ('I','Institution',5,99,0,'A',0,0,0,99,0)",
224 "insert into categories values ('P','Privileged',5,99,0,'A',0,0,0,99,0)",
225 "insert into categories values ('L','Library',5,99,0,'A',0,0,0,99,0)"
228 foreach $query (@queryValues) {
229 $sth=$dbh->prepare($query);