function update_result($domain, $page)
{
global $mysqli;
$pageview_reward = "SELECT domain.name as 'domain_name', \n page.name as 'page_name', \n genome_id,\n count(pageview_id) as 'count_goals', \n sum(IFNULL(goal.value, 0)) as 'reward',\n avg(IFNULL(goal.value, 0)) as 'average_goal_value',\n avg(IFNULL(goal.value, 0) > 0) as 'conversion'\n from pageview \n left join goal using(pageview_xid)\n inner join page using(page_id)\n inner join domain using(domain_id)\n WHERE \n domain.name = '{$domain}' \n AND page.name = '{$page}' \n group by pageview_id";
$variant_reward = "SELECT domain_name, \n page_name, \n gene_id,\n gene.name as 'gene_name', \n variant_id,\n variant.name as 'variant_name', \n concat_ws(' -> ', gene.name, variant.name) as 'gene_variant',\n reward \n from ({$pageview_reward}) as pageview_reward \n inner join genome using(genome_id)\n inner join genome_variant_link using(genome_id)\n inner join variant using(variant_id)\n inner join gene using(gene_id)";
$gene_aggregate_reward = "SELECT gene_id,\n COUNT(reward) as 'gene_count', \n COUNT(reward/reward) as 'gene_nonzero', \n SUM(reward) as 'gene_sum',\n AVG(reward) as 'gene_avg',\n COUNT(DISTINCT variant_id) as 'gene_distinct'\n from ({$variant_reward}) as variant_reward \n GROUP BY gene_id";
$variant_relative_reward = "SELECT \n variant_id,\n domain_name,\n page_name,\n gene_name, \n variant_name,\n\n COUNT(variant_id) as 'variant_count', \n SUM(reward) as 'variant_sum', \n AVG(reward) as 'variant_avg', \n STDDEV(reward) as 'variant_stddev', \n\n SUM(reward)/agg.`gene_sum` as 'variant_share',\n (SUM(reward)/agg.`gene_sum` + 5/agg.`gene_nonzero`)\n / (1 + `gene_distinct`*5/agg.`gene_nonzero`) as 'variant_weight'\n \n FROM ({$gene_aggregate_reward}) as agg \n LEFT JOIN ({$variant_reward}) as variant_reward USING(gene_id) \n GROUP BY variant_id";
// todo: incremental updating algorithm... use current results for new results
// $variant_relative_reward2 = "SELECT
// variant_id,
// domain_name,
// page_name,
// gene_name,
// variant_name,
//
// variant_count + 1 as 'variant_count',
// variant_sum + $value as 'variant_sum',
// variant_avg + (($value - variant_avg) / (variant_count + 1)) as 'variant_avg',
// variant_stddev + (($value - variant_avg)^2 / (variant_count + 1))^(1/2) as 'variant_stddev',
//
// 0 as 'variant_share',
// 0 as 'variant_weight'
//
// FROM result
// WHERE 1 AND $where";
//
//
// $replace_result2 = "REPLACE INTO result2 " . $variant_relative_reward2;
// // echo $replace_result2;
// $stmt = $mysqli->prepare($replace_result2);
// $id = _execute_and_return_id('result2', $stmt);
$replace_result = "REPLACE INTO result " . $variant_relative_reward;
$stmt = $mysqli->prepare($replace_result);
$id = _execute_and_return_id('result', $stmt);
return $id;
}
function insert_new_referrer($referrer)
{
global $mysqli;
$referrer = _camel_to_underscore($referrer);
$referrer['hash'] = md5(implode('', $referrer));
//TODO: add indexes to database
$sql = "SELECT * FROM referrer WHERE referrer.hash = '" . $referrer['hash'] . "'";
$id = _get_unique_row_id('referrer', $sql);
if ($id == 0) {
$sql = "INSERT INTO referrer(" . implode(', ', array_keys($referrer)) . ") VALUES('" . implode("', '", $referrer) . "')";
$stmt = $mysqli->prepare($sql);
$id = _execute_and_return_id('referrer', $stmt);
}
return $id;
}
