root/tags/09nov/table.php

<?php

///////////////////////////////////////////////////
include 'include.php';


$debug=1;

// process some common arguments and set some defaults
process_args();

$pagetitle = "$currentCountryName Respositories : table of record totals";

$ircount_table = "ircount";

///////////////////////////////////////////////////
// read passed params if any...
$order1 = $_GET['order'];
$freq = $_GET['frequency'];

//set defaults if no varibles set in get request
if ($order1 == "") {
        $order1 = "archivename";
}
if ($freq == "") {
    $freq = "weekly";
}
$output1 = $output;

// db table name
$tablename = $ircount_table;
$pagefilename = $_SERVER['SCRIPT_NAME'];





// var to hold any error messages
$warningmsg;

//////////////////////////////////////////////////
// FUNCTIONS
//////////////////////////////////////////////////
// linktome
// make a link back to this page with the correct 
// (ie values that have been set) arguments.
// the function is passed the arg name and its value 
// 
function linktome ($varname, $varvalue) {
    $pagefilename = $_SERVER['SCRIPT_NAME'];
    global $pagefilename, $order1, $output1, $freq, $warningmsg, $country;
    //
    // use varible varibles
    // first keep a copy of what the real var value is
    $tmpvar = $$varname;
    // now set whatever var name we were passed to its value
    // for the purpose of making this link
    $$varname = $varvalue;
    // now set the query string
    $tmpreturn = "$pagefilename?order=$order1&output=$output1&frequency=$freq&country=$country";    
    // reset what ever value it is to its orig value.
    $$varname = $tmpvar;
    //$warningmsg .= "<p>BB varname $varname tmpvalue $tmpvar</p>\n";
    return "$tmpreturn";
}


//////////////////////////////////////////////////////
// table_column
// we do show this at top and bottom so may as well make
// it a function
function table_column ($collected_dates, $freq="monthly") {
    foreach ($collected_dates as $cdate => $totcdate) {
        list($year, $month, $day) = split('[/.-]', $cdate);
        $tmpurl = linktome("order1", "$cdate");
        if ($freq == "weekly") {
                echo "<th><a href=\"$tmpurl\">$day/$month<br />$year</a></th>\n";
        } else {
                echo "<th><a href=\"$tmpurl\">$month $year</a></th>\n";
        }
    }
}

///////////////////////////////////////////////////////////////
// if user wants text file prompt to download.
if ($output1 == "tsv") {
    // If no headers are sent, send one
    if (!headers_sent()) {
           //header('Location: http://www.example.com/');
        header('Content-type: text/tab-separated-values');
        header('Content-Disposition: attachment; filename="ir_records.tsv"');
    }
}
elseif ($output1 == "csv") {
    // If no headers are sent, send one
        if (!headers_sent()) {
                header('Content-type: text/plain');
                header('Content-Disposition: attachment; filename="ir_records.csv"');
        }
}


// only print the following html if we want html
if ($output1=="html") {
    printhtmlhead ($pagetitle);
}







//####################################################
// Connection to DB
connectdb();




//###################################################
// Print out title and top of page stuff
if ($output1=="html") {
echo "<h1>$pagetitle</h1>";
echo "<p>Ordered by: <em>$order1</em>.  \n";
echo "Frequency: <em>$freq</em>  change to:";
if ($freq != "weekly") {
        // not using linktome function as we don't
        // want to use the current order date if changing from
        // weekly to monthly
        //$tmpurl = linktome("freq", "weekly");
        echo " [<a href=\"?frequency=weekly&country=$country\">weekly</a>] \n";
}
if ($freq != "monthly") {
        //$tmpurl = linktome("freq", "monthly");
        echo " [<a href=\"?frequency=monthly&country=$country\">monthly</a>] \n";
}
echo "</p>\n";

$tmpurl = linktome("output1", "tsv");
$tmpurl2 = linktome("output1", "csv");
echo "<p><strong>Export to text</strong>: <a href=\"$tmpurl\">tab seperated</a> or <a href=\"$tmpurl2\">comma seperate fields enclosed by doublequotes</a>.</p>\n";



}

//###################################################
// Database connection and SQL
// get a list of uniqure archive names
$query = "
SELECT DISTINCT archivename, identifier, country
FROM $ircount_table
WHERE country = '$country' 
";


$result_sussex = mysql_query($query) or die("Query failed : " . mysql_error());
$fields=mysql_num_rows($result_sussex);



//#####################################################
// Debug info
// before we do much, print out some html comment debug
if ($output1=="html") {
echo "<!-- \n\n Debug info \n";
echo "Order1: \t $order1 \noutput1:\t $output1\n";
echo "-->\n";
}






// an array to hold names of archives
$archivelist;


// ##########################################################
// loop for each record returned.
while ($line = mysql_fetch_array($result_sussex, MYSQL_ASSOC)) {
    $archivename1 = $line[archivename];
    $identifier = $line[identifier];

    // remove spaces
    $archivename1 = trim($archivename1);
    //
    // add an entry to the array, the key being the archive name,
    // the value being a new object created at the same time.
    $archivelist[$archivename1] = new ArchiveClass();

    // tell the object it's name
    $archivelist[$archivename1]->setName($archivename1);

    // tell the object it's IR identifier
    $archivelist[$archivename1]->setIdentifier($identifier);

    // for sorting later on, add this name to a hash
    $justnames[$archivename1]="$archivename1";


}



// #######################################################################
// We now have an array - archivelist - which is a list of all archives.
// Each key is an archive name, each value is an archive object.


// SQL to get archives and dates
$query2 = "
SELECT archivename, records, DATE(collected_date) as dateonly, country
FROM ircount 
WHERE country = '$country' 
";


$dbresult_list = mysql_query($query2) or die("Query failed : " . mysql_error());


// an array to hold the dates we collected data from 
$collected_dates;

// an array to sort the results later
$sortorder;


// for each row returned - which will be an archive, date, and num of records etc
// - add it to the object for that archive.
// also... add the date to collected_dates if not already there
while ($line2 = mysql_fetch_array($dbresult_list, MYSQL_ASSOC)) {
    $datestamp2 = $line2["dateonly"];
    $archivename2 = $line2["archivename"];
    $archivename2 = trim($archivename2);
    $records2 = $line2["records"];
    //
    // lets do some error checking...
    if ($datestamp2 == "" ||
        $archivename2 == "" ||
        $records2 == "") {
        echo "ERROR $datestamp2 $records2 $archivename2 \n <p>";
    }
    if ($records2 == "0") {
        continue;
    }
    
    // Processing monthly is a little bit of a hack.
    // We go through each row (one for each archive each week)
    // but simply over write any entry we already have
    // for the month in question, creating one a month.
    // if we only want monthly, remove day from datestamp
    // the mmyy record for this repository will be overwritten
    // with the last record for this month
    if ($freq == "monthly") {
        list($year, $month, $day) = split('[/.-]', $datestamp2);
        $datestamp2 = "$year-$month";
    }
    
    // add this date (and num of records) to the object for this
    // archive.
    $archivelist[$archivename2]->addToRecordslist($datestamp2,$records2);
    
    // record that we have come aross a line which was collected on this date
    $collected_dates[$datestamp2]++;
    // are we planning to sort the list based on *this* date? if so lets remember
    // the number of records for this archive in the sort order
    if ($order1==$datestamp2) {
        $sortorder[$archivename2]=$records2;
    }
}


// we have now added each line of data to the repository object it is associated with, 
// we have each archive which has a list of dates, attached to each date is the
// number of records it had at the time.

// We also now have collected_dates, a list of dates (as keys in the array, the
// actual value being the number of times we saw that date). 


// we also have sortorder, which will already be complete if the sort order
// was a date column. we need to populate it now if not
//  if user wants the order to be alphabetic by name (default) just make sort order
// the same as justnames (which maps name to name (itself))
if ($order1=="archivename") {
    $sortorder = $justnames;
}
if ($sortorder =="") {
    $sortorder = $justnames;
}

// sort by the value in sort order.
// the key is the archive name
asort($sortorder);


// sort archives alphabetically
//ksort($archivelist);

// sort the list of dates, earliest first.
ksort($collected_dates);





//#####################################################
// print out html
// first print table header, which includes all the collected dates
if ($output1=="html") {

# print the top row
echo "<table><tr><th class=\"repname\">Respository Name ";
$tmplink = linktome("order1", "archivename");
echo "[<a href=\"$tmplink\">order by</a>]</th>\n";
table_column($collected_dates, $freq);
echo "<th>Repository Name</th>";
echo "</tr>\n";


// go through each archive, print out the record totals in to the table
foreach ($sortorder as $aname => $aobject) {
    
    echo "<tr>\n";

    $name = $archivelist[$aname]->getName();
    $identifier = urlencode ($archivelist[$aname]->getIdentifier());

    echo "<td class=\"repname\">";
    
    $querystring = "id=$identifier&country=$country";
    echo '<a href="archive.php?' . htmlentities($querystring) . '">' . $name . "</a></td> \n";
    
    //echo '<a href=\"archive.php?id=$identifier\">$name</a></td>\n';

        //$archivelist[$aname]->printRecordList();
    $records=$archivelist[$aname]->returnRecords();

    foreach ($collected_dates as $cdate => $totalcdate) {
        $numrecord = $records[$cdate];
        if ($numrecord == "") {
            $numrecord = "&nbsp; ";
        }
        // if we are sorted by this col make it bold
        if ($cdate==$order1) {
            echo "<td><strong><abbr title=\"$aname\">$numrecord</abbr></strong></td>\n";
        }
        else {
            echo "<td>$numrecord</td>\n";
        }
        // include this in the total for this date
        $date_totals[$cdate]+=$numrecord;
    }

    // print the archive name again
    echo "<td>$name</td>\n";

    echo "</tr>\n";
        
}

//Finally print out the column heads and grand totals
echo "<tr><td>Total</td>";
foreach ($date_totals as $cdate => $totcdate) {
    echo "<td>$totcdate</td>\n";
}
echo "<td>$nbsp</td>";
echo "</tr>\n";

// this is an awful hack to make the repository name column
// a decent size.
echo "<tr><th style=\"color:#FFF\">";
echo "..................................................... </th>";
table_column($collected_dates);
echo "<td style=\"color:#FFF\">";
echo "..................................................... </td>";
echo "</tr>\n";


echo "</table>";
}




//##################################################
// Print txt/csv/tsv file
// we can do either tab or comma seperated
if ($output1=="csv" || $output1=="tsv") {

// set these vars for tsv or csv
if ($output1=="csv") {
    $precsv = "\"";
    $posttext = "\",";
} else {
    $precsv = "";
    $posttext = "\t";
}


echo "$precsv" . "Archive Name$posttext";
foreach ($collected_dates as $cdate => $totcdate) {
        echo "$precsv$cdate$posttext";
}
echo "\n";

// go through each archive, print out the record totals in to the table
foreach ($archivelist as $aname => $aobject) {

        $name = $archivelist[$aname]->getName();
        echo "$precsv$name$posttext";

        //$archivelist[$aname]->printRecordList();
        $records=$archivelist[$aname]->returnRecords();

        foreach ($collected_dates as $cdate => $totalcdate) {
                $numrecord = $records[$cdate];
                echo "$precsv$numrecord$posttext";
                // include this in the total for this date
                $date_totals[$cdate]+=$numrecord;
        }
        echo "\n";

}

//Finally print out grand totals
echo "$precsv" . "Total$posttext";
foreach ($date_totals as $cdate => $totcdate) {
        echo "$precsv$totcdate$posttext";
}
echo "\n";
}
// end of text/csv output
// #####################################################



// only print footer if we are doing html
if ($output1=="html") {
    printhtmlfooter();
}
?>