From 52002d5a444e9bdd63414297d7674c1365fecc70 Mon Sep 17 00:00:00 2001
From: hdl <hdl>
Date: Wed, 9 Mar 2005 18:56:15 +0000
Subject: [PATCH] Adding average loan time to stats. Beware : Unstable
 calculation

---
 .../default/en/reports/reports-home.tmpl      |   5 +-
 reports/issues_stats.pl                       | 220 +++++++++++-------
 2 files changed, 139 insertions(+), 86 deletions(-)

diff --git a/koha-tmpl/intranet-tmpl/default/en/reports/reports-home.tmpl b/koha-tmpl/intranet-tmpl/default/en/reports/reports-home.tmpl
index 3e05ab1b98..2ab13a7944 100644
--- a/koha-tmpl/intranet-tmpl/default/en/reports/reports-home.tmpl
+++ b/koha-tmpl/intranet-tmpl/default/en/reports/reports-home.tmpl
@@ -15,6 +15,7 @@
 	</ul>
 	<h2 class="reports">Stats on issues</h2>
 	<ul>
+		<li><a href="/cgi-bin/koha/reports/issues_stats.pl">Global reports</a></li>
 		<li><a href="/cgi-bin/koha/stats.pl?time=yesterday">Till Reconciliation:</a> Daily Report (yesterday)</li>
 		<li><a href="/cgi-bin/koha/stats.pl?time=today">Till Reconciliation:</a> Daily Report (today)</li>
 		<li><a href="/cgi-bin/koha/overdue.pl">Overdue items</a></li>
@@ -24,10 +25,6 @@
 	<ul>
 		<li><a href="/cgi-bin/koha/reports/acquisitions_stats.pl">Global reports</a></li>
 	</ul>
-	<h2 class="reports">Stats on Issues</h2>
-	<ul>
-		<li><a href="/cgi-bin/koha/reports/issues_stats.pl">Global reports</a></li>
-	</ul>
 </div>
 <div id="bloc25">
 	<h2 class="reports">data exports</h2>
diff --git a/reports/issues_stats.pl b/reports/issues_stats.pl
index b9fe3638f6..3b153d9eb4 100755
--- a/reports/issues_stats.pl
+++ b/reports/issues_stats.pl
@@ -437,72 +437,128 @@ sub calculate {
 # preparing calculation
 	my $strcalc ;
 	if ($process ==2) {
-	# Processing average loanperiods
-		$strcalc .= "SELECT $linefield, $colfield, ";
-		$strcalc .= " DATE_SUB(date_due, INTERVAL CAST(issues.renewals AS SIGNED INTEGER) * (CAST(issues.renewals AS SIGNED INTEGER)+1) DAY) AS issuedate, returndate, COUNT(*), date_due, issues.renewals, issuelength FROM `issues`,borrowers,biblioitems LEFT JOIN items ON (biblioitems.biblioitemnumber=items.biblioitemnumber) LEFT JOIN issuingrules ON (issuingrules.branchcode=issues.branchcode AND  issuingrules.itemtype=biblioitems.itemtype AND  issuingrules.categorycode=borrowers.categorycode) WHERE issues.itemnumber=items.itemnumber AND issues.borrowernumber=borrowers.borrowernumber ";
-	
-		@$filters[0]=~ s/\*/%/g if (@$filters[0]);
-		$strcalc .= " AND issues.returndate > '" . @$filters[0] ."'" if ( @$filters[0] );
-		@$filters[1]=~ s/\*/%/g if (@$filters[1]);
-		$strcalc .= " AND issues.returndate < '" . @$filters[1] ."'" if ( @$filters[1] );
-		@$filters[2]=~ s/\*/%/g if (@$filters[2]);
-		$strcalc .= " AND borrowers.categorycode like '" . @$filters[2] ."'" if ( @$filters[2] );
-		@$filters[3]=~ s/\*/%/g if (@$filters[3]);
-		$strcalc .= " AND biblioitems.itemtype like '" . @$filters[3] ."'" if ( @$filters[3] );
-		@$filters[4]=~ s/\*/%/g if (@$filters[4]);
-		$strcalc .= " AND issues.branchcode like '" . @$filters[4] ."'" if ( @$filters[4] );
-		@$filters[5]=~ s/\*/%/g if (@$filters[5]);
-		$strcalc .= " AND borrowers.sort1 like '" . @$filters[5] ."'" if ( @$filters[5] );
-		@$filters[6]=~ s/\*/%/g if (@$filters[6]);
-		$strcalc .= " AND borrowers.sort2 like '" . @$filters[6] ."'" if ( @$filters[6] );
-		$strcalc .= " AND dayname(timestamp) like '" . $daysel ."'" if ( $daysel );
-		$strcalc .= " AND monthname(timestamp) like '" . $monthsel ."'" if ( $monthsel );
+		$linefield=~s/datetime/issues.returndate/;
+		$linefield=~s/itemtype/biblioitems.itemtype/;
+		$colfield=~s/datetime/issues.returndate/;
+		$colfield=~s/itemtype/biblioitems.itemtype/;
 		
-		$strcalc .= " group by issuedate, returndate, $linefield, $colfield order by $linefield,$colfield";
+	# Processing average loanperiods
+ 		$strcalc .= "SELECT $linefield, $colfield, ";
+ 		$strcalc .= " DATE_SUB(date_due, INTERVAL CAST(issuingrules.issuelength AS SIGNED INTEGER) * (CAST(issues.renewals AS SIGNED INTEGER)+1) DAY) AS issuedate, returndate, COUNT(*), date_due, issues.renewals, issuelength FROM `issues`,borrowers,biblioitems LEFT JOIN items ON (biblioitems.biblioitemnumber=items.biblioitemnumber) LEFT JOIN issuingrules ON (issuingrules.branchcode=issues.branchcode AND  issuingrules.itemtype=biblioitems.itemtype AND  issuingrules.categorycode=borrowers.categorycode) WHERE issues.itemnumber=items.itemnumber AND issues.borrowernumber=borrowers.borrowernumber and returndate is not null";
+# 	
+ 		@$filters[0]=~ s/\*/%/g if (@$filters[0]);
+ 		$strcalc .= " AND issues.returndate > '" . @$filters[0] ."'" if ( @$filters[0] );
+ 		@$filters[1]=~ s/\*/%/g if (@$filters[1]);
+ 		$strcalc .= " AND issues.returndate < '" . @$filters[1] ."'" if ( @$filters[1] );
+ 		@$filters[2]=~ s/\*/%/g if (@$filters[2]);
+ 		$strcalc .= " AND borrowers.categorycode like '" . @$filters[2] ."'" if ( @$filters[2] );
+ 		@$filters[3]=~ s/\*/%/g if (@$filters[3]);
+ 		$strcalc .= " AND biblioitems.itemtype like '" . @$filters[3] ."'" if ( @$filters[3] );
+ 		@$filters[4]=~ s/\*/%/g if (@$filters[4]);
+ 		$strcalc .= " AND issues.branchcode like '" . @$filters[4] ."'" if ( @$filters[4] );
+ 		@$filters[5]=~ s/\*/%/g if (@$filters[5]);
+ 		$strcalc .= " AND borrowers.sort1 like '" . @$filters[5] ."'" if ( @$filters[5] );
+ 		@$filters[6]=~ s/\*/%/g if (@$filters[6]);
+ 		$strcalc .= " AND borrowers.sort2 like '" . @$filters[6] ."'" if ( @$filters[6] );
+ 		$strcalc .= " AND dayname(timestamp) like '" . $daysel ."'" if ( $daysel );
+ 		$strcalc .= " AND monthname(timestamp) like '" . $monthsel ."'" if ( $monthsel );
+# 		
+ 		$strcalc .= " group by issuedate, returndate, $linefield, $colfield order by $linefield,$colfield";
+		warn "SQL :". $strcalc;
 		
-		my $dbcalc = $dbh->prepare($strcalc);
-		$dbcalc->execute;
-	# 	warn "filling table";
-		my $emptycol;
-		my $issues_count; 
-		while (my ($row, $col, $issuedate, $returndate) = $dbcalc->fetchrow) {
-	#		warn "filling table $row / $col / $value ";
+ 		my $dbcalc = $dbh->prepare($strcalc);
+ 		$dbcalc->execute;
+ 	# 	warn "filling table";
+ 		my $emptycol;
+ 		my $issues_count=0;
+ 		my $previous_row; 
+ 		my $previous_col;
+ 		my $loanlength; 
+ 		my $err;
+		my $weightrow;
+ 		while (my  @data = $dbcalc->fetchrow) {
+			my ($row, $col, $issuedate, $returndate, $weight)=@data;
+ 			warn "filling table $row / $col / $issuedate / $returndate /$weight";
 			$emptycol = 1 if ($col eq undef);
 			$col = "zzEMPTY" if ($col eq undef);
 			$row = "zzEMPTY" if ($row eq undef);
+			warn "row :".$row." column :".$col;
+ 			if (($previous_row== $row) and ($previous_col==$col)){
+ 				my @result =split /:/,DateCalc($returndate,$issuedate) ;
+#  DateCalc returns => 0:0:WK:DD:HH:MM:SS   the weeks, days, hours, minutes,
+#  and seconds between the two
+				$loanlength = $result[2]*7+$result[3];
+ 				warn "DateCalc returns :$loanlength with return ". $returndate ."issue ". $issuedate ."weight : ". $weight;
+				
+				$table{$row}->{$col}->{value}+=$weight*$loanlength;
+				$issues_count+=$weight;
 			
-			$table{$row}->{$col}+=$value;
-			$table{$row}->{totalrow}+=$value;
-			$grantotal += $value;
+			} elsif ($previous_row==$row) {
+				$table{$row}->{$previous_col}->{value}=$table{$row}->{$previous_col}->{value}/$issues_count;
+				$table{$row}->{$previous_col}->{weight}=1;
+				$table{$row}->{$previous_col}->{realweight}=$issues_count;
+				$weightrow+=$table{$row}->{$previous_col}->{weight};
+				$table{$row}->{totalrow}+=$table{$row}->{$previous_col}->{value};
+ 				
+				my @result =split /:/,DateCalc($returndate,$issuedate) ;
+#  DateCalc returns => 0:0:WK:DD:HH:MM:SS   the weeks, days, hours, minutes,
+#  and seconds between the two
+				$loanlength = $result[2]*7+$result[3];
+				$table{$row}->{$col}->{value}+=$weight*$loanlength;
+ 				$issues_count=$weight;
+				$previous_col=$col;
+ 			} else {
+				unless (($previous_row) or ($previous_col)){
+					$table{$previous_row}->{$previous_col}->{value}=$table{$previous_row}->{$previous_col}->{value}/$issues_count;
+					$table{$previous_row}->{$previous_col}->{weight}=1;
+					$table{$previous_row}->{$previous_col}->{realweight}=$issues_count;
+					$table{$previous_row}->{totalrow}+=$table{$previous_row}->{$previous_col}->{value};
+					$weightrow+=$table{$row}->{$previous_col}->{weight};
+					$table{$previous_row}->{totalrow}=$table{$previous_row}->{totalrow}/$weightrow;
+ 				}
+				my @result =split /:/,DateCalc($returndate,$issuedate) ;
+#  DateCalc returns => 0:0:WK:DD:HH:MM:SS   the weeks, days, hours, minutes,
+#  and seconds between the two
+				$loanlength = $result[2]*7+$result[3];
+ 				warn "DateCalc returns :$loanlength with return ". $returndate ."issue ". $issuedate ."weight : ". $weight;
+				
+				$table{$row}->{$col}->{value}=$weight*$loanlength;
+				$issues_count=$weight;
+				$previous_row=$row;
+				$previous_col=$col;
+				$weightrow=0;
+			}
+ 		}
+  		push @loopcol,{coltitle => "NULL"} if ($emptycol);
+	 	foreach my $row ( sort keys %table ) {
+	 		my @loopcell;
+# 		#@loopcol ensures the order for columns is common with column titles
+# 		# and the number matches the number of columns
+			foreach my $col ( @loopcol ) {
+				my $value =$table{$row}->{($col->{coltitle} eq "NULL")?"zzEMPTY":$col->{coltitle}}->{value};
+				push @loopcell, {value => $value  } ;
+			}
+			push @looprow,{ 'rowtitle' => ($row eq "zzEMPTY")?"NULL":$row,
+							'loopcell' => \@loopcell,
+							'hilighted' => ($hilighted >0),
+							'totalrow' => $table{$row}->{totalrow}
+						};
+			$hilighted = -$hilighted;
 		}
-	}
- 	push @loopcol,{coltitle => "NULL"} if ($emptycol);
-	
-	foreach my $row ( sort keys %table ) {
-		my @loopcell;
-		#@loopcol ensures the order for columns is common with column titles
-		# and the number matches the number of columns
+# 	
+# #	warn "footer processing";
 		foreach my $col ( @loopcol ) {
-			my $value =$table{$row}->{($col->{coltitle} eq "NULL")?"zzEMPTY":$col->{coltitle}};
-			push @loopcell, {value => $value  } ;
-		}
-		push @looprow,{ 'rowtitle' => ($row eq "zzEMPTY")?"NULL":$row,
-						'loopcell' => \@loopcell,
-						'hilighted' => ($hilighted >0),
-						'totalrow' => $table{$row}->{totalrow}
-					};
-		$hilighted = -$hilighted;
-	}
-	
-#	warn "footer processing";
-	foreach my $col ( @loopcol ) {
-		my $total=0;
-		foreach my $row ( @looprow ) {
-			$total += $table{($row->{rowtitle} eq "NULL")?"zzEMPTY":$row->{rowtitle}}->{($col->{coltitle} eq "NULL")?"zzEMPTY":$col->{coltitle}};
+			my $total=0;
+			my $nbrow=0;
+			foreach my $row ( @looprow ) {
+				$total += $table{($row->{rowtitle} eq "NULL")?"zzEMPTY":$row->{rowtitle}}->{($col->{coltitle} eq "NULL")?"zzEMPTY":$col->{coltitle}}->{value};
+				$nbrow++;
 #			warn "value added ".$table{$row->{rowtitle}}->{$col->{coltitle}}. "for line ".$row->{rowtitle};
-		}
+			}
 #		warn "summ for column ".$col->{coltitle}."  = ".$total;
-		push @loopfooter, {'totalcol' => $total};
+			$total = $total/$nbrow if ($nbrow);
+			push @loopfooter, {'totalcol' => $total};
+		}
 	
 	}else {
 		$strcalc .= "SELECT $linefield, $colfield, ";
@@ -549,34 +605,34 @@ sub calculate {
 			$table{$row}->{totalrow}+=$value;
 			$grantotal += $value;
 		}
-	}
- 	push @loopcol,{coltitle => "NULL"} if ($emptycol);
-	
-	foreach my $row ( sort keys %table ) {
-		my @loopcell;
-		#@loopcol ensures the order for columns is common with column titles
-		# and the number matches the number of columns
-		foreach my $col ( @loopcol ) {
-			my $value =$table{$row}->{($col->{coltitle} eq "NULL")?"zzEMPTY":$col->{coltitle}};
-			push @loopcell, {value => $value  } ;
+		push @loopcol,{coltitle => "NULL"} if ($emptycol);
+		
+		foreach my $row ( sort keys %table ) {
+			my @loopcell;
+			#@loopcol ensures the order for columns is common with column titles
+			# and the number matches the number of columns
+			foreach my $col ( @loopcol ) {
+				my $value =$table{$row}->{($col->{coltitle} eq "NULL")?"zzEMPTY":$col->{coltitle}};
+				push @loopcell, {value => $value  } ;
+			}
+			push @looprow,{ 'rowtitle' => ($row eq "zzEMPTY")?"NULL":$row,
+							'loopcell' => \@loopcell,
+							'hilighted' => ($hilighted >0),
+							'totalrow' => $table{$row}->{totalrow}
+						};
+			$hilighted = -$hilighted;
 		}
-		push @looprow,{ 'rowtitle' => ($row eq "zzEMPTY")?"NULL":$row,
-						'loopcell' => \@loopcell,
-						'hilighted' => ($hilighted >0),
-						'totalrow' => $table{$row}->{totalrow}
-					};
-		$hilighted = -$hilighted;
-	}
-	
-#	warn "footer processing";
-	foreach my $col ( @loopcol ) {
-		my $total=0;
-		foreach my $row ( @looprow ) {
-			$total += $table{($row->{rowtitle} eq "NULL")?"zzEMPTY":$row->{rowtitle}}->{($col->{coltitle} eq "NULL")?"zzEMPTY":$col->{coltitle}};
-#			warn "value added ".$table{$row->{rowtitle}}->{$col->{coltitle}}. "for line ".$row->{rowtitle};
+		
+	#	warn "footer processing";
+		foreach my $col ( @loopcol ) {
+			my $total=0;
+			foreach my $row ( @looprow ) {
+				$total += $table{($row->{rowtitle} eq "NULL")?"zzEMPTY":$row->{rowtitle}}->{($col->{coltitle} eq "NULL")?"zzEMPTY":$col->{coltitle}};
+	#			warn "value added ".$table{$row->{rowtitle}}->{$col->{coltitle}}. "for line ".$row->{rowtitle};
+			}
+	#		warn "summ for column ".$col->{coltitle}."  = ".$total;
+			push @loopfooter, {'totalcol' => $total};
 		}
-#		warn "summ for column ".$col->{coltitle}."  = ".$total;
-		push @loopfooter, {'totalcol' => $total};
 	}
 			
 
-- 
2.39.5