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; }