Daniel Forsman European EndUser 2005 Örebro universitetsbibliotek daniel.forsman@ub.oru.se

1. WAR IS PEACE, FREEDOM IS SLAVERY, IGNORANCE IS STRENGTH Daniel Forsman European EndUser 2005 Örebro universitetsbibliotek daniel.forsman@ub.oru.se

2. Agenda Background information About PHP and Voyager Examples - MINITRUE - ACQ Origin - ACQ Status - Journals - New books Questions The bitter end

3. Orebro University 1300 staff 14428 students 2002/03 9459 full time University Library 50 Staff 2 Systems librarians 3 System technicans 250.000 Items 220.000 Bibs Windows 2K Terminal - Server Linux clients

4. GSLG Voyager Consortia Kalmar University Orebro University The Stockholm Institute of Education University College of Boras Vaxjo University 1997 1 mutual Systems librarian 1 Database server 2 Application servers Stockholm University National Library

5. Who? Me? Systems librarian - Problem solving - Application handling - Development - Teaching 4 Years at Orebro No previous programming/systems background Simple librarian with a interest If I can do this, so can you!

6. Several tools available for managing reports: • Access, ColdFusion, PERL … • PHP is just another tool that you can use. • PHP combines some of the best features of the ”other” tools Voyager & Reports

7. PHP? PHP : Hypertext Preprocessor Used to be Personal Home Page Server side scripting language which can be embedded in HTML or as a standalone binary similar to ASP, ColdFusion, JSP Official module to Apache HTTP server Runs on *nix and Window platforms Current version : PHP5, object oriented and more C++ like then the previous PHP4 (still available as 4.3.8).

8. Our set up • Local webserver: with MySQL, Apache 2.x, • running the library webpages, DoD etc. • PHP 4.3.4. GD, JPgraphics, PHP/SWF, LDAP … • Database server: Solaris / Oracle • Solaris application server: apache 1.3.x, PHP 4.3.7 PHP 4.3.8 important security update

9. Things you can do • Extended webbased Voyager functionality • Statistics & Data mining • Collection management - list of journals, video ... • Whatever ...

10. MINITRUE WAR IS PEACE, FREEDOM IS SLAVERY, IGNORANCE IS STRENGTH

11. The problem Voyager offers no authority control of individual fields When doing reports based on a specific MARC field, Human errors distort the data varför Ideen Hur det fungerar Resultat Utveckling Kod

12. Example : how much has the shelfs grown during the last year select count(*), ala2iso(getsubfield('852','h','mfhd',mi.mfhd_id)) as shelf from bib_text bt, bib_mfhd bm, mfhd_item mi, item i, mfhd_master mm where bt.bib_id = bm.bib_id and bm.mfhd_id = mi.mfhd_id and mi.item_id = i.item_id and mm.mfhd_id = mi.mfhd_id and i.create_date >= to_date('2002-01-01','YYYY-MM-DD') and i.create_date <= to_date('2002-12-31','YYYY-MM-DD') group by ala2iso(getsubfield('852','h','mfhd',mi.mfhd_id))

13. Returns all unique 852 h including errors 852 |h Uppsats 852 |h Upppsats 852 |h Upsatts 852 |h Uppsats Andersson|i Endless possibilites Makes it hard to use the 852 h field when Working with a lot of data for migration, export, Import etc. Messing up statistics Where there is human input there is room for error

14. How to solve the problem? Make a script that looks at all the modified MFHD 852 h Fields, compare the input with a authority list, if it matches Then all is well if not there is a problem. E-mail the operator who made the change with information

15. Update.php Webform to update Uppst.txt, password restricted Uppst.txt List of valid entries to 852 h MFHD check.log All ”errors” Are logged. CRONTAB Daily 22.30 OPERATOR ORACLE CAT Check2.php Compares modified 852 h fields During the day with the list of Approved 852 h fields in uppst.txt If the field is found in in uppst.txt All is OK, if not there is a problem The operator of the modified mfhd receives a email with MFHD ID, BIB ID and The entered 852 h field If the entry is correct but missing From the uppst.txt file they Are encouraged to update the Uppst.txt file online with Update.php.

16. Selectall mfhd_id that has been modified during the day Loop through and select the data we need. select distinct bt.bib_id as bib_id, ala2iso(getsubfield('852','h','mfhd',mi.mfhd_id)) as h, ala2iso(getsubfield('852','i','mfhd',mi.mfhd_id)) as i, o.operator_id as op from bib_text bt, bib_mfhd bm, mfhd_item mi, item i, operator o, mfhd_history mh where bm.mfhd_id = mh.mfhd_id and bt.bib_id = bm.bib_id and bm.mfhd_id = mi.mfhd_id and mi.item_id = i.item_id and o.operator_id = mh.operator_id and mh.mfhd_id = $id and mh.action_date >= to_date('$date','YYYY-MM-DD' //Define the file to validate against. $rfile = "uppst.txt"; //Parse through the file, each line = one valid call number $hyllor = file($rfile);

17. while(list(,$hylla) = each ($hyllor)) { //Trim any whitespace from the textfile $h = trim($h); $hylla = trim($hylla); if($hylla == $h || "REF $hylla" == $h || "$hylla br" == $h || "$hylla(p)" == $h || "$hylla(x)" == $h || "$hylla(s)" == $h || "$hylla fol" == $h || "REF $hylla(x)" == $h || "REF $hylla(p)") { //If there is a match between the H field ($h) and the valid Call number ($hylla) //Then there is no need to do anything. The entry is valid. //Break the loop, and go on to next //If there is no match then email operator

18. Hej! 2005-02-11 ändrade/skapade operator *gunn* hylluppställningen* EDC Qad *för post med MFHD_ID: 283650 BIB_ID: 393566 EDC Qad finns inte med bland de 2860 auktoriserade uppställningarna. Om det är en giltig hylluppställning bör denna läggas till hylluppställningsplanen. Om den är felaktig bör du ändra. Vid frågor, kontakta ansvarig för hylluppställningsplanen. Om du misstänker att denna automatiska kontroll är felaktig kontakta daniel.forsman@ub.oru.seAktuell uppställningsplan hittar du på: http://www.ub.oru.se/intern/uppst.htmlMINITRUE : WAR IS PEACE, FREEDOM IS SLAVERY, IGNORANCE IS STRENGTH.

19. MFHDs modified/created: 2005-04-22 [before 2005-04-22, 21:30:01] 1: REF Okc(p) is not one of the 2530 validated CALL NUMBERS! MFHD ID:288025 BIB ID:401492 852 h:REF Okc(p) 852 i:Socialförsäkringsboken. 2003 OPERATOR: gunn … 11:Produkten REF Qcab is not one of the 2530 validated CALL NUMBERS! MFHD ID:288035 BIB ID:375059 852 h:Produkten REF Qcab 852 i:Jefferson OPERATOR: ann 142 mfhds checked in total MFHDs modified/created: 2005-04-23 [before 2005-04-23, 21:30:04] 4 mfhds checked in total

20. Extended functionality • 852 h => Authority control • 2) Item type => 852 check • If there is a item with a certain item type then that • should be reflected in the 852 h field • Example: A 852 h field called VIDEO should have items with • item type VIDEO associated. • 852 b => 852 h check • If 852 h is something then the location is expected to be something. • Example: 852 h is a shelf located on a ”closed” shelf then the location should not be the default location. • More to come … Let’s look at the script in action! A modified version with .html output.

21. ACQ origin Bakgrund varför Ideen Hur det fungerar Resultat Utveckling Kod Codes are entered into line item note in ACQ module Who requested the book Monitor the level of user oriented purchases Monitor the circulation of materials based upon purchase request

22. ACQ_ORIGIN_START.php Start & End date Select categories Acq_origin_diagram.php JPGRAPH class OPERATOR ORACLE Line_item. Line_item_notes ACQ Acq_origin.php Select count code While count Select historical_charges Group by historical charges

23. $sqlY1 = "select count(*) as Y1, i.historical_charges as hc1, it.item_type_name as it1 from line_item_notes, line_item, item i, bib_item bi, item_type it where line_item.create_date > TO_DATE('$startdate','YYYY-MM-DD') and line_item.create_date < TO_DATE('$enddate','YYYY-MM-DD') and line_item_notes.note like '%Y1%' and line_item_notes.LINE_ITEM_ID = line_item.LINE_ITEM_ID and line_item.bib_id = bi.bib_id and bi.item_id = i.item_id and i.item_type_id = it.item_type_id group by it.item_type_name, i.historical_charges"; echo "<p><img src=acq_origin_diagram.php?v1=$Y1&v2=$Y2&v3=$Y3&v4=$Y4&v5=$Y5&v6=$Y6>";

24. Jpgraph graphics http://www.aditus.nu/jpgraph/index.php $v1 = $HTTP_GET_VARS["v1"]; include ("jpgraph/src/jpgraph.php"); include ("jpgraph/src/jpgraph_bar.php"); $datax = array("Y1","Y2","Y3","Y4","Y5","Y6"); $datay=array($v1,$v2,$v3,$v4,$v5,$v6); // Create the graph. These two calls are always required $graph = new Graph(400,300,"auto"); $graph->SetScale("textlin"); // Add a drop shadow $graph->SetShadow(); // Adjust the margin a bit to make more room for titles $graph->img->SetMargin(50,40,30,50); // Create a bar pot $bplot = new BarPlot($datay); $graph->Add($bplot); //Displays the X labels $graph->xaxis->SetTickLabels($datax); //Displays the values on top of bars $bplot->value->Show(); // Center the values in the bar //$bplot->SetValuePos('center'); // Make the bar a little bit wider $bplot->SetWidth(0.7); //Displays values on top of bars // Setup the titles $graph->title->Set("Förvärvsursprung"); $graph->xaxis->title->Set("Ursprung"); $graph->yaxis->title->Set("Antal"); $graph->title->SetFont(FF_FONT1,FS_BOLD); $graph->yaxis->title->SetFont(FF_FONT1,FS_BOLD); $graph->xaxis->title->SetFont(FF_FONT1,FS_BOLD); $graph ->legend->Pos( 0.05,0.5,"right" ,"center"); // Display the graph $graph->Stroke();

25. Look at the script in action

26. ACQ origin 2004-01-01 – 2004-12-31

27. Circulation, acq origin librarian 2004 965 (31%) of the books purchased During 2004 has never circulated. 643 (21%) has circulated once. Is there a difference in Swedish and English conten?

28. Inköp på initativ av bibliotekarie 2003 32% av förvärvade normallån på initativ av bibliotekarie 2003, har ej lånats ut sedan de införskaffades. 2004 var siffran 31%. Inköp på iniativ av lärare 2003 28% av förvärvade normallån På initativ av lärare 2003, har Ej lånats ut sedan de införskaffades. 2004 var siffran 47%. Förvärvar en lärare lika bra/dåligt som en bibliotekarie?

29. ACQ status Script idea: display and count line items grouped by status. Items selected through fund plan hyperlink select ledger, select status, select fund, display items with that status and fund with purchase order info and bibliographic data ACQ module hostile to non ACQ staff Librarians want to track their orders Webbased interface to ACQ purchase orders.

30. Order_status.php • Choose ledger • Choose status (invoiced, approved etc.) • Review Ledger hierarchy – choose ledger • Info on associated items • Title / Author / ISBN • PO NO • Status date • ACQ operator • # copies Subject librarian ACQ OPERATOR ACQ / CAT ORACLE BIB PO LINE ITEM

31. //Ask for ledger // create connection $connection = odbc_connect("$user", "", "") or die("arrrrrg, kan inte ansluta"); $ledger_sql = "select ledger_id as ledger_id, ledger_name as ledger from ledger order by create_date"; $exe_led = odbc_exec($connection, $ledger_sql) or die("Ledger sql died : $ledger_sql"); while(odbc_fetch_row($exe_led)){ $ledger_id = odbc_result($exe_led, 'ledger_id'); $ledger = odbc_result($exe_led, 'ledger'); $options .= "<OPTION value=\"$ledger_id\">$ledger</OPTION>"; } echo "<br><h2>Välj ledger</h2><br>"; echo "<form action=order_status.php method=post>"; echo '<SELECT name="ledger_id">'; echo "<option></option>"; echo "$options"; echo "</SELECT>"; echo '<input type="submit" value="Submit">'; echo "</form>"; odbc_close($connection);

32. $status_sql = "select line_item_status, line_item_status_desc from line_item_status order by line_item_status_desc"; $sql = "select fund_name, fund_id, parent_fund from fund where ledger_id='$led2' and parent_fund='0'"; echo "<tr><td><a href=order_status.php?f=$fund_id&s=$status_id&l=$led2>$fund_name [$count]</a></td></tr>";

33. $sql_select = "select li.quantity as antal, li.create_date as skapad, op.first_name as fnamn, op.last_name as enamn, po.po_number as po, ala2iso(bib.title) as tit, bib.isbn as isbn, bib.issn as issn from line_item li, operator op, purchase_order po, bib_text bib, line_item_copy_status lis, line_item_funds lf where lis.line_item_id = li.line_item_id and lis.line_item_status = '$s' and lf.ledger_id = '$l' and fund_id = '$f' and lis.copy_id = lf.copy_id and li.create_opid = op.operator_id and li.po_id = po.po_id and bib.bib_id = li.bib_id";

34. Look at the script in action

35. Journals Make a list of journals in A separate interface All journals are given a Item type One item type for printed journals One item type for electronic journals

36. //Convert search term to lower $str = strtolower($term); $sql1 = "select distinct ala2iso(bt.title) as jn, bt.bib_id as bib, i.item_type_id as itid from bib_text bt, bib_mfhd bm, mfhd_item mi, item i where bt.bib_id = bm.bib_id and bm.mfhd_id = mi.mfhd_id and mi.item_id = i.item_id $ite and ala2iso(translate(bt.title_brief, 'ABCDEFGHIJKLMNOPQRSTUVWXYZÅÄÖ','abcdefghijklmnopqrstuvwxyzåäö')) like '%$str%' order by jn";

37. Look at script in action

38. New books New books is fine, separate new books from OPAC Display new books on webpage Offer RSS channels with new book Criteria is based on Received complete and Fund hierarchy

39. RSS.php Received complete Status date > $date String search for fund name Array push If array  For each echo Title and link to OPAC Create_RSS.php Time conversion Same SQL as RSS.php Array for each Fund Write to file instead of screen Nytt.html Choose subject & $date > Crontab Daily User RSS feed *.xsql

40. //idag $now = strftime("%Y-%m-%d"); //Antal sekunder på en vecka $w1 = 7 * 86400; //antalet sekunder på två veckor $w2 = 14 * 86400; //antalet sekunder på två veckor $w3 = 21 * 86400; //konvertera dagens datum till sekunder $ctime = strtotime($now); //subtrahera dagens sekunder med veckosekunder för att få reda på datumet för en vecka sedan $w11 = $ctime - $w1; //subtrahera dagens sekunder med veckosekunder för att få reda på datumet för två veckor sedan $w22 = $ctime - $w2; //subtrahera dagens sekunder med veckosekunder för att få reda på datumet för två veckor sedan $w33 = $ctime - $w3; //konvertera sekunder till timestamp $envecka = date("Y-m-d", $w11); //konvertera sekunder till timestamp $tvavecka = date("Y-m-d", $w22); //konvertera sekunder till timestamp $trevecka = date("Y-m-d", $w33);

41. $sql = "select unique ala2iso(bt.title_brief) as titel, f.fund_name as fund from line_item_copy_status ls, bib_mfhd bm, bib_text bt, fund f, line_item_copy lc where ls.line_item_status = '1' and status_date > TO_DATE('$datum','YYYY-MM-DD') and ls.mfhd_id = bm.mfhd_id and bm.bib_id = bt.bib_id and ls.line_item_id = lc.line_item_id and lc.use_fund = f.fund_id order by titel";

42. $teknikarrayT = array(); //Konvertera konto-sträng till lowercase $fund_low = strtolower($fund); $findteknik = "teknik"; if(strstr($fund_low, $findteknik)) { array_push($teknikarrayT, "$title"); }

43. if($teknik){ $uteknikarrayT = array_unique($teknikarrayT); $cteknik = count($uteknikarrayT); echo "<h2>Teknik nyförvärv sedan $datum</h2>"; echo "<br><b>Antal nyförvärv:$cteknik</b><br><br>"; $iteknik = "1"; foreach ($uteknikarrayT as $tekniktitle) { echo "$iteknik. "; $iteknik++; $pteknik = explode(" ", $tekniktitle); echo '<a href="http://oru.sub.su.se/cgi-bin/Pwebrecon.cgi?DB=local&Search_Arg='.$pteknik[0].'+'.$pteknik[1].'+'.$pteknik[2].'+'.$pteknik[3].'&Search_Code=TALL&CNT=25&HIST=1" alt="Länk till bibliotekskatalog, öppnas i nytt fönster" title="Länk till bibliotekskatalog, öppnas i nytt fönster" target="_new">'.$tekniktitle.'</a><br><br>'; } }

44. if($teknik){ $uteknikarrayT = array_unique($teknikarrayT); $cteknik = count($uteknikarrayT); $lteknikfile = "teknikrss.xsql"; if (is_writable($lteknikfile)) { if (!$handleteknik = fopen($lteknikfile, 'w+')) { exit; } $h1teknik = '<?xml version = "1.0" encoding="ISO-8859-1" ?> <rss version="2.0" xmlns:dc="http://purl.org/dc/elements/1.1/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"> <channel><title>Nyanlända böcker Teknik - Örebro universitetsbibliotek</title> <link>http://www.ub.oru.se/voyager/teknikrss.xsql</link> <description>Nya böcker på Örebro universitetsbibliotek, Teknik sedan '.$datum.'. Antal nyförvärv:'.$cteknik.'</description> <language>sv-se</language> <category domain="http://dmoz.org/">reference/libraries</category>'; if (fwrite($handleteknik, $h1teknik) === FALSE) { echo "Cannot write to file ($lteknikfile)"; exit; } $iteknik = "1"; foreach ($uteknikarrayT as $tekniktitle) { $iteknik++; $pteknik = explode(" ", $tekniktitle); $contentteknik = '<item><title>'.$tekniktitle.'</title><description>Med största sannolikhet har boken ännu inte kommit ut på hyllan. Kontakta bibliotekets information för att reservera boken.</description> <pubDate>'.$now.'</pubDate><link>http://oru.sub.su.se</link></item>'; if (fwrite($handleteknik, $contentteknik) === FALSE) { exit; } } $closeteknik = '</channel></rss>'; if (fwrite($handleteknik, $closeteknik) === FALSE) { echo "Cannot write to file ($lteknikfile)"; exit; }else{ echo "Updated Teknik RSS<br>\n"; } fclose($handleteknik); } else { echo "The file $lteknikfile is not writable"; } }

45. Look at script in action

46. References • http://bibl4.oru.se/voyager/voyager_php/ • www.php.net • www.hotscripts.com • PHP5 and MySQL bible : Converse, Park & Morgan • PHP Functions - essential reference : Greant, Merall, Wilson & Michlitsch • PHP and MySQL Web Development : Wellington & Thomson • Contact • daniel.forsman@ub.oru.se

47. If you want any of the code let me know. [The bitter end]