<?php
/* all
   SELECT description, count(*) as count
FROM contractnotice 
group by description having count > 1 order by count
*/
/*- duplicated description
 - most duplicated overall, most duplicated per agency/category/supplier etc. */
$heuristics["METADATA_DUPLICATED_DESCRIPTION"] = Array(
        "description" => ""
);
function METADATA_DUPLICATED_DESCRIPTION($cn)
{
        $averageDuplicatedDescriptions = getAverageDuplicatedDescriptions();
        $stddevDuplicatedDescriptions = getstddevDuplicatedDescriptions();
        $query = 'select count(*) from contractnotice where description = "' . $agencyName . '"';
    $result = $conn->query($query);
    $r = $result->fetch(PDO::FETCH_BOTH);
        $dupeDesc = $r[0];
        if ($dupeDesc == 1) $value = 0;
        else $value = abs($dupeDesc - $averageDuplicatedDescriptions) / $stddevDuplicatedDescriptions;
        return Array(
                "heuristic_value" => $value,
                "raw_value" => $dupeDesc,
                "mean" => $averageDuplicatedDescriptions,
                "stddev" => $stddevDuplicatedDescriptions
        );
}
$averageDuplicatedDescriptions;
function getAverageDuplicatedDescriptions()
{
        global $averageDuplicatedDescriptions;
        if (!$averageDuplicatedDescriptions) {
                getStatsDuplicatedDescriptions();
        }
        return $averageDuplicatedDescriptions;
}
$stddevDuplicatedDescriptions;
function getstddevDuplicatedDescriptions()
{
        global $stddevDuplicatedDescriptions;
        if (!$stddevDuplicatedDescriptions) {
                getStatsDuplicatedDescriptions();
        }
        return $stddevDuplicatedDescriptions;
}
function getStatsDuplicatedDescriptions()
{
        $query = "select avg(count),STDDEV(count) from (
        SELECT description, count(*) as count
FROM contractnotice 
group by description having count > 1 
        ) as a;";
    $result = $conn->query($query);
    $r = $result->fetch(PDO::FETCH_BOTH);
        $averageDuplicatedDescriptions = $r[0];
        $stddevDuplicatedDescriptions = $r[1];
}