#!/usr/bin/perl
#
# $Id: prenumeraty.pl,v 1.1 1999/04/05 21:34:22 jwr Exp $
#

$dbName = "prenumeraty";

$pageFooter = <<EFOOT
  <hr>
<table width="100%" border="0"><tr>
<td align="left" colspan="2" width="100%">
Zawartosc bazy: (C) Copyright by Polska Fundacja Upowszechniania Nauki, 
Warszawa 1998
</td></tr>
<tr>
<td align="left" width="50%">
Opracowanie i umieszczenie w sieci: <a href="http://www.icm.edu.pl/">ICM UW</a>
</td>
<td align="right" width="50%">
  <a href="mailto:J.Rychter\@icm.edu.pl">J. Rychter</a>
</td>
</tr></table>
EFOOT
;

use CGI;
use DBI;
use CGI::FastTemplate;

$DataDir = "/www/sunsite/kbn-prenumeraty";
push(@INC, $DataDir);
use BibForms;

$BibForms::DataDir = "/www/sunsite/kbn-prenumeraty/";
$BibForms::TemplateDir = "/www/sunsite/kbn-prenumeraty/templates/";

# use CGI::Carp;

$dbh = DBI->connect("DBI:mysql:$dbName") || die "MySQL database connect failed: $DBI::errstr\n";

$q = new CGI;
print $q->header();
print $q->start_html(-title => "Prenumeraty czasopism",
		     -BGCOLOR => 'white');

BibForms::initForm("query", "pola-query");
BibForms::readPopupValues("ilerezultatow", "kody-ilosci");
BibForms::readPopupValues("kategorie", "kody-kategorie");

if(($q->param('Szukaj') ne "Szukaj") and (length($q->param('ile')) < 1) and (length($q->param('rozwin')) < 1)) {
  print $q->start_multipart_form;

  BibForms::dumpFormFromTemplate($q, "query", 0, 'normal');
  print $q->hidden(-name => "od", -value => '1');
  
  print qq|<p align="center">|;
  print $q->submit(-name => 'Szukaj',
		   -value => "Szukaj");
  print qq|</p>\n|;
  print $pageFooter;
  print $q->end_multipart_form;
  print $q->end_html;

} elsif((length($q->param('rozwin')) < 1) and (length($q->param('sub') < 1))) { # juz wstepne kryteria podane

  print $q->start_form;
  $url = $q->self_url();
  printNavURL($url, 1);

  $perPage = $q->param('ILEREZULTATOW');
  if(length($perPage) < 1) {
    $perPage = $q->param('ile');
  }
  $showFrom = $q->param('od');
  if(length($showFrom) < 1) {
    $showFrom = 1;
  }
  $total = $q->param('total');
  if(length($total) < 1) {
    $total = -1;
  }
  $urlParams = "ile=$perPage";

  $selectString = "select number,title,previousTitle,issn,countryCode,publisher,category1,category2,countries.code,countryName,categories.code,categoryName from periodicals,countries,categories where periodicals.countryCode = countries.code and periodicals.category1 = categories.code";

  if(length($q->param("TYTUL")) > 1) {
    $searchString = uc($q->param("TYTUL"));
    $selectString .= " and title like \'$searchString\'";
    $urlParams .= "&TYTUL=" . $q->escape($searchString);
  }
  if(length($q->param("ISSN")) > 1) {
    $searchString = $q->param("ISSN");
    $selectString .= " and issn = \'$searchString\'";
    $urlParams .= "&ISSN=" . $q->escape($searchString);
  }
  if(length($q->param("WYDAWCA")) > 1) {
    $searchString = $q->param("WYDAWCA");
    $selectString .= " and publisher like \'$searchString\'";
    $urlParams .= "&WYDAWCA=" . $q->escape($searchString);
  }
  if(length($q->param("KATEGORIA")) > 1) {
    $searchString = $q->param("KATEGORIA");
    $selectString .= " and categoryName=\'$searchString\'";
    $urlParams .= "&KATEGORIA=" . $q->escape($searchString);
  }


  $selectString .= " order by title";
  if($total < 0) {
    $sth = $dbh->prepare($selectString);
    $sth->execute();
    $total = $sth->rows();
  }

  if($total > 0) {
    print "<p>Liczba rezultatów: $total.</p>\n";

    $nextFrom = $showFrom + $perPage;
    $prevFrom = $showFrom - $perPage;

    print "<table width=\"100%\"><tr><td width=\"50%\" align=\"left\">\n";
    if($prevFrom > 0) {
      $url = $q->url();
      $url .= "?od=$prevFrom";
      $url .= "&" . $urlParams;
      print "<p><a href=\"$url\">&lt;&lt;&lt; Poprzednia strona</a></p>\n";
    }
    print "</td><td width=\"50%\" align=\"right\">\n";
    if($nextFrom < $total) {
      $url = $q->url();
      $url .= "?od=$nextFrom";
      $url .= "&" . $urlParams;
      print "<p><a href=\"$url\">Następna strona &gt;&gt;&gt;</a></p>\n";
    }
    print "</td></tr></table>\n";

    $limitFrom = $showFrom - 1;
    $selectString .= " limit $limitFrom,$perPage";
    $sth = $dbh->prepare($selectString);
    $sth->execute();
    
    $url = $q->url();
    
    
    print "<table border=\"1\">\n";
    print "<tr><td></td><td width=\"25%\">Tytuł</td><td>ISSN</td><td>Kraj</td><td>Wydawca</td></tr>\n";
    $i = $showFrom;
    while($row = $sth->fetchrow_hashref) {
      print "<tr>\n";
      print "<td>$i</td>\n";
      print "<td><a href=\"$url?rozwin=$row->{number}&$urlParams&od=$showFrom\">$row->{title}</a></td>";
      print "<td>$row->{issn}</td>";
      print "<td>$row->{countryName}</td>";
      print "<td>$row->{publisher}</td>";
      print "</tr>\n";
      $i++;
    }
    print "</table>\n";
    
    
    print "<table width=\"100%\"><tr><td width=\"50%\" align=\"left\">\n";
    if($prevFrom > 0) {
      $url = $q->url();
      $url .= "?od=$prevFrom";
      $url .= "&" . $urlParams;
      print "<p><a href=\"$url\">&lt;&lt;&lt; Poprzednia strona</a></p>\n";
    }
    print "</td><td width=\"50%\" align=\"right\">\n";
    if($nextFrom < $total) {
      $url = $q->url();
      $url .= "?od=$nextFrom";
      $url .= "&" . $urlParams;
      print "<p><a href=\"$url\">Następna strona &gt;&gt;&gt;</a></p>\n";
    }
    print "</td></tr></table>\n";
  } else {
    print "<p>Nie znaleziono żadnych rezultatów. Spróbuj zadać mniej precyzyjne zapytanie aby poszerzyć zakres poszukiwań.</p>\n";
  }

  $url = $q->self_url();
  printNavURL($url, 1);

  print $pageFooter;
  print $q->end_form;
  print $q->end_html;
}  elsif(length($q->param('sub')) < 1) {			# rozwijamy
  print $q->start_form;
  $url = $q->self_url();
  printNavURL($url, 2);
  $recNo = $q->param('rozwin');

  $selectString = "select number,title,previousTitle,issn,countryCode,publisher,category1,category2,countries.code,countryName,categories.code,categoryName from periodicals,countries,categories where periodicals.countryCode = countries.code and periodicals.category1 = categories.code";

  if(length($recNo) > 0) { 
    $selectString .= " and number=$recNo";
  }

  $sth = $dbh->prepare($selectString);
  $sth->execute();
  
  $url = $q->self_url();

  $row = $sth->fetchrow_hashref();
  print "<h1>Dane czasopisma</h1>\n";

  print qq|<table>\n|;
  print qq|<tr><td align="right">Tytuł:</td><td align="left"><font color="blue">$row->{title}</font></td></tr>|;
  print qq|<tr><td align="right">ISSN:</td><td align="left"><font color="blue">$row->{issn}</font></td></tr>|;
  print qq|<tr><td align="right">Kraj:</td><td align="left"><font color="blue">$row->{countryName}</font></td></tr>|;
  print qq|<tr><td align="right">Wydawca:</td><td align="left"><font color="blue">$row->{publisher}</font></td></tr>|;
  print qq|<tr><td align="right">Kategoria:</td><td align="left"><font color="blue">$row->{categoryName}</font></td></tr>|;
  print qq|</table>\n|;

  $selectString = "select periodical,year,subscriber,exch,code,name from subscriptions,subscribers where subscriptions.periodical = $recNo and subscriptions.subscriber = subscribers.code order by year desc, name";

  $sth = $dbh->prepare($selectString);
  print qq|<h2>Prenumeratorzy:</h2>\n|;
  $sth->execute();
  while($row = $sth->fetchrow_hashref) {
    print qq|<p>$row->{year}:<a href="$url&sub=$row->{code}">$row->{name}</a></p>\n|;
  }

  $url = $q->self_url();
  printNavURL($url, 2);

  print $pageFooter;
  print $q->end_form;
  print $q->end_html;
} else {
  print $q->start_form;
  $url = $q->self_url();
  printNavURL($url, 3);
  $subNo = $q->param('sub');

  $selectString = "select name,postalCode,city,address,telephone,email,code from subscribers where subscribers.code = $subNo";

  $sth = $dbh->prepare($selectString);
  $sth->execute();

  print "<h1>Dane instytucji</h1>\n";

  $row = $sth->fetchrow_hashref();

  print qq|<table>\n|;
  print qq|<tr><td align="right">Nazwa:</td><td align="left"><font color="blue">$row->{name}</font></td></tr>|;
  print qq|<tr><td align="right">Adres:</td><td align="left"><font color="blue">$row->{address}</font></td></tr>|;
  print qq|<tr><td align="right"></td><td align="left"><font color="blue">$row->{postalCode} $row->{city}</font></td></tr>|;
  print qq|<tr><td align="right">Telefony:</td><td align="left"><font color="blue">$row->{telephone}</font></td></tr>|;
  if(length($row->{email}) > 0) {
    print qq|<tr><td align="right">E-poczta:</td><td align="left"><font color="blue">$row->{email}</font></td></tr>|;
  }
  print qq|</table>\n|;
  
  $url = $q->self_url();
  printNavURL($url, 3);

  print $pageFooter;
  print $q->end_form;
  print $q->end_html;
}


sub printNavURL {
  my($url, $level) = @_;
  
  $urlp = $url;
  $urlp =~ s/&?sub=(\d+)//;

  $urlr = $urlp;
  $urlr =~ s/rozwin=(\d+)&*//;

  print qq|<p align="left"><a href="http://sunsite.icm.edu.pl/kbn-prenumeraty/prenumeraty.cgi">Prenumeraty czasopism</a>\n|;
  
  if($level == 1) {
    print qq|/ <font color="red">Lista rezultatów</font>\n|;
  }
  if($level > 1) {
    print qq|/ <a href=\"$urlr\">Lista rezultatów</a>\n|;
  }
  if($level == 2) {
    print qq|/ <font color="red">Dane publikacji</font>\n|;
  }
  if($level > 2) {
    print qq|/ <a href=\"$urlp\">Dane publikacji</a>\n|;
  }
  if($level >= 3) {
    print qq|/ <font color="red">Dane prenumeratora</font>\n|;
  }
  print qq|</p>\n|;
}