1 package C4::Stats; #assumes C4/Stats
7 use vars qw($VERSION @ISA @EXPORT @EXPORT_OK %EXPORT_TAGS);
9 # set the version for version checking
13 @EXPORT = qw(&UpdateStats &statsreport &Count &Overdues &TotalOwing
14 &TotalPaid &getcharges &Getpaidbranch &unfilledreserves);
17 #module to insert stats data into stats table
18 my ($env,$branch,$type,$amount,$other,$itemnum,$itemtype,$borrowernumber)=@_;
21 $branch=$env->{'branchcode'};
23 my $user = $env->{'usercode'};
24 print $borrowernumber;
25 my $sth=$dbh->prepare("Insert into statistics
26 (datetime,branch,type,usercode,value,
27 other,itemnumber,itemtype,borrowernumber)
28 values (now(),'$branch','$type','$user','$amount',
29 '$other','$itemnum','$itemtype','$borrowernumber')");
36 #module to return a list of stats for a given day,time,branch type
37 #or to return search stats
41 # if ($type eq 'issue'){
42 @data=circrep($time,$type);
50 my $query="Select * from statistics";
51 if ($time eq 'today'){
52 $query=$query." where type='$type' and datetime
53 >=datetime('yesterday'::date)";
55 my $sth=$dbh->prepare($query);
59 while (my $data=$sth->fetchrow_hashref){
60 $results[$i]="$data->{'datetime'}\t$data->{'branch'}";
71 my ($type,$branch,$time,$time2)=@_;
73 my $query="Select count(*) from statistics where type='$type'";
74 $query.=" and datetime >= '$time' and datetime< '$time2' and branch='$branch'";
75 my $sth=$dbh->prepare($query);
77 my $data=$sth->fetchrow_hashref;
81 return($data->{'count(*)'});
86 my $query="Select count(*) from issues where date_due >= now()";
87 my $sth=$dbh->prepare($query);
89 my $count=$sth->fetchrow_hashref;
92 return($count->{'count(*)'});
98 my $query="Select sum(amountoutstanding) from accountlines";
100 $query=$query." where accounttype='F' or accounttype='FN'";
102 my $sth=$dbh->prepare($query);
105 my $total=$sth->fetchrow_hashref;
108 return($total->{'sum(amountoutstanding)'});
114 my $query="Select * from accountlines,borrowers where (accounttype = 'Pay'
116 and accountlines.borrowernumber = borrowers.borrowernumber";
117 if ($time eq 'today'){
118 $query=$query." and date = now()";
120 $query.=" and date='$time'";
122 # my $query="Select * from statistics,borrowers
123 # where statistics.borrowernumber= borrowers.borrowernumber
124 # and (statistics.type='payment' or statistics.type='writeoff') ";
125 # if ($time eq 'today'){
126 # $query=$query." and datetime = now()";
128 # $query.=" and datetime > '$time'";
130 $query.=" order by timestamp";
132 my $sth=$dbh->prepare($query);
136 while (my $data=$sth->fetchrow_hashref){
147 my($borrowerno,$timestamp)=@_;
149 my $timestamp2=$timestamp-1;
150 my $query="Select * from accountlines where borrowernumber=$borrowerno
151 and timestamp = '$timestamp' and accounttype <> 'Pay' and
153 my $sth=$dbh->prepare($query);
154 # print $query,"<br>";
158 while (my $data=$sth->fetchrow_hashref){
159 # if ($data->{'timestamp'} == $timestamp){
169 my($date,$borrno)=@_;
171 my $query="select * from statistics where type='payment' and datetime
172 >'$date' and borrowernumber='$borrno'";
173 my $sth=$dbh->prepare($query);
176 my $data=$sth->fetchrow_hashref;
179 return($data->{'branch'});
182 sub unfilledreserves {
184 my $query="select *,biblio.title from reserves,reserveconstraints,biblio,borrowers,biblioitems where found <> 'F' and cancellationdate
185 is NULL and biblio.biblionumber=reserves.biblionumber and
186 reserves.constrainttype='o'
187 and (reserves.biblionumber=reserveconstraints.biblionumber
188 and reserves.borrowernumber=reserveconstraints.borrowernumber)
190 reserves.borrowernumber=borrowers.borrowernumber and
191 biblioitems.biblioitemnumber=reserveconstraints.biblioitemnumber order by
192 biblio.title,reserves.reservedate";
193 my $sth=$dbh->prepare($query);
197 while (my $data=$sth->fetchrow_hashref){
202 $query="select *,biblio.title from reserves,biblio,borrowers where found <> 'F' and cancellationdate
203 is NULL and biblio.biblionumber=reserves.biblionumber and reserves.constrainttype='a' and
204 reserves.borrowernumber=borrowers.borrowernumber
206 biblio.title,reserves.reservedate";
207 $sth=$dbh->prepare($query);
209 while (my $data=$sth->fetchrow_hashref){
215 return($i,\@results);
218 END { } # module clean-up code here (global destructor)