/**
* The actual task is defined in this method. Here you can access any option or argument that was defined on the
* command line via $input and write anything to the console via $output argument.
* In case anything went wrong during the execution you should throw an exception to make sure the user will get a
* useful error message and to make sure the command does not exit with the status code 0.
*
* Ideally, the actual command is quite short as it acts like a controller. It should only receive the input values,
* execute the task by calling a method of another class and output any useful information.
*
* Execute the command like: ./console snoopy:recalculate-score --name="The Piwik Team"
*/
protected function execute(InputInterface $input, OutputInterface $output)
{
/**
* Settings for scooring
*/
$settings = new \Piwik\Plugins\SnoopyBehavioralScoring\Settings();
/**
* Which site we scoore
*/
$matching_site = $settings->matching_site->getValue();
/**
* Which goals mark our visitor to stat with tracking
*/
$matching_goals = $settings->matching_goals->getValue();
/**
* Enable full debug (Additional infor are displayed like cooling and adding)
*/
$full_debug = $settings->full_console_debug->getValue();
/**
* Cooling factor that tells how fast the visitor will coole down when no action made
*/
$cooling_factor = $settings->cooling_factor->getValue();
/**
* How much specific type of referer is worth
*/
$campaign_entry = $settings->campaign_entry->getValue();
/**
* How much specific url is worth
*/
$special_urls = array();
$special_urls_raw = $settings->special_urls->getValue();
$special_urls_raw = explode("\n", $special_urls_raw);
foreach ($special_urls_raw as $url_info) {
$special_url = explode(';', $url_info);
if (!array_key_exists($special_url[0], $special_urls) && sizeof($special_url) == 2) {
$special_urls[$special_url[0]] = (double) trim($special_url[1]);
}
}
$output->writeln("<info>************************************************************************</info>");
$output->writeln("<info>Starting visitor scoring...</info>");
$output->writeln("<comment>Getting visitors to score...</comment>");
$visitors_to_score = \Piwik\API\Request::processRequest('SnoopyBehavioralScoring.getVisitorIdsToScore', array());
$previously_scored = Db::fetchAll(" SELECT DISTINCT idvisitor\n FROM " . Common::prefixTable(SnoopyBehavioralScoring::getTableName()) . "\n WHERE idvisitor IN('" . implode("','", $visitors_to_score) . "')\n ORDER BY id DESC");
/**
SCORE ALREADY SCORED VISITORS
**/
//First score already visitors
$output->writeln(sprintf("<comment>Scoring already scored visitors (%s)...</comment>", count($previously_scored)));
foreach ($previously_scored as $scored_visitor) {
if (($key = array_search($scored_visitor['idvisitor'], $visitors_to_score)) !== false) {
unset($visitors_to_score[$key]);
}
$idvisitor = $scored_visitor['idvisitor'];
$scored_visitor = Db::fetchRow("SELECT *\n FROM " . Common::prefixTable(SnoopyBehavioralScoring::getTableName()) . "\n WHERE idvisitor = ?\n ORDER BY id DESC\n LIMIT 1", array($scored_visitor['idvisitor']));
$output->writeln(sprintf("<info>Scoring visitor: %s</info>", $idvisitor));
if ($full_debug) {
$output->writeln(sprintf("<comment>Curent score: %s</comment>", $scored_visitor['score']));
}
$visits = Db::fetchAll("SELECT idvisit, visit_first_action_time, visit_last_action_time, referer_type\n FROM " . Common::prefixTable("log_visit") . "\n WHERE HEX(idvisitor) = ?\n AND idsite = ?\n AND visit_last_action_time > ?", array($idvisitor, $matching_site, $scored_visitor['created_at']));
//$output->writeln(print_r($visits, true));
$output->writeln(sprintf("<comment>Number of visits: %s</comment>", count($visits)));
$visitor_score = $scored_visitor['score'];
$last_date = null;
$campaigns = array();
$total_goals = 0;
foreach ($visits as $visit) {
$tmp_score = 0;
$goals = Db::fetchRow("SELECT COUNT(*) AS count\n\t\t\t\t\t\t\t\tFROM " . Common::prefixTable("log_conversion") . "\n\t\t\t\t\t\t\t\tWHERE idsite = ?\n\t\t\t\t\t\t\t\tAND idgoal IN(" . implode(",", $matching_goals) . ")\n\t\t\t\t\t\t\t\tAND HEX(idvisitor) = ?\n\t\t\t\t\t\t\t\tAND server_time >= ? AND server_time <= ?", array($matching_site, $idvisitor, $visit['visit_first_action_time'], $visit['visit_last_action_time']));
$total_goals += $goals['count'];
$tmp_score += $goals['count'] * 5;
if ($full_debug) {
$output->writeln(sprintf("<comment>\tGoals: %s</comment>", $goals['count']));
$output->writeln(sprintf("<comment>\t\tFirst action: %s</comment>", $visit['visit_first_action_time']));
$output->writeln(sprintf("<comment>\t\tLast action: %s</comment>", $visit['visit_last_action_time']));
}
$visit_score = array();
if ($full_debug) {
$output->writeln(sprintf("<comment>\tScoring visitid: %s</comment>", $visit['idvisit']));
}
/**
* If visitor came from email campaign we add adittional campaign entry
*/
if ($visit['referer_type'] == 6) {
if ($full_debug) {
$output->writeln("<comment>\t\tAdding campaign entry bonus</comment>");
}
if (!array_key_exists($visit['referer_name'], $campaigns)) {
$tmp_score += $campaign_entry;
$campaigns[$visit['referer_name']] = $visit['referer_name'];
}
}
$visit_actions = Db::fetchAll(" SELECT idvisit, idaction_url, name, type, url_prefix, server_time\n FROM " . Common::prefixTable("log_link_visit_action") . " AS lva\n LEFT JOIN " . Common::prefixTable("log_action") . " AS la\n ON lva.idaction_url = la.idaction\n WHERE HEX(idvisitor) = ?\n AND idsite = ?\n AND idvisit = ?", array($idvisitor, $matching_site, $visit['idvisit']));
foreach ($visit_actions as $action) {
$full_url = $action['name'];
switch ($action['url_prefix']) {
case '0':
$full_url = 'http://' . $action['name'];
break;
case '1':
$full_url = 'http://www.' . $action['name'];
break;
case '2':
$full_url = 'https://' . $action['name'];
break;
case '3':
$full_url = 'https://www.' . $action['name'];
break;
}
if (!array_key_exists($action['idaction_url'], $visit_score) && $action['server_time'] > $scored_visitor['created_at']) {
//TODO Duplicate worth less, some links may be worth more
$visit_score[$action['idaction_url']] = 1;
if (array_key_exists($full_url, $special_urls)) {
$tmp_score += $special_urls[$full_url];
} else {
$tmp_score++;
}
}
}
if ($last_date === null) {
$visitor_score += $tmp_score;
} else {
$timeDiff = $last_date->diff(new \DateTime($visit['visit_first_action_time']));
if ($full_debug) {
$output->writeln(sprintf("<comment>\t\tDate difference: %s</comment>", abs($timeDiff->format("%a"))));
$output->writeln(sprintf("<comment>\t\tScore before: %s</comment>", $visitor_score));
}
$visitor_score = $this->calculateCooling($visitor_score, 1, $cooling_factor, abs($timeDiff->format("%a")));
if ($full_debug) {
$output->writeln(sprintf("<comment>\t\tScore after calculating cooling: %s</comment>", $visitor_score));
$output->writeln(sprintf("<comment>\t\tScore add: %s</comment>", $tmp_score));
}
$visitor_score += (double) $tmp_score;
if ($full_debug) {
$output->writeln(sprintf("<comment>\t\tScore after: %s</comment>", $visitor_score));
}
}
$last_date = new \DateTime($visit['visit_last_action_time']);
}
//Calculate cooling from last visit to now
if (count($visits) > 0) {
$sinceLastVisit = $last_date->diff(new \DateTime("now"));
if ($full_debug) {
$output->writeln(sprintf("<comment>\t\tDays since last visit difference: %s</comment>", abs($sinceLastVisit->format("%a"))));
}
$visitor_score = $this->calculateCooling($visitor_score, 1, $cooling_factor, abs($sinceLastVisit->format("%a")));
} elseif ($scored_visitor['score'] > 0) {
$last_visit = Db::fetchRow("SELECT idvisit, visit_first_action_time, visit_last_action_time\n FROM " . Common::prefixTable("log_visit") . "\n WHERE HEX(idvisitor) = ?\n AND idsite = ?\n ORDER BY idvisit DESC\n LIMIT 1", array($idvisitor, $matching_site));
$last_date = new \DateTime($last_visit['visit_last_action_time']);
$last_calculated = new \DateTime($scored_visitor['created_at']);
$diff_since_last_visit = $last_date->diff($last_calculated);
$diff_since_last_visit2 = $last_date->diff(new \DateTime("now"));
$time_to_calculate = abs($diff_since_last_visit->format("%a")) != abs($diff_since_last_visit2->format("%a"));
// != abs($last_date->diff(new \DateTime("now")->format("%a")));
if ($time_to_calculate) {
$visitor_score -= $this->calculateCoolingNoScore(0, 1, $cooling_factor, abs($diff_since_last_visit2->format("%a"))) - $this->calculateCoolingNoScore(0, 1, $cooling_factor, abs($diff_since_last_visit->format("%a")));
}
}
if ($visitor_score < 0) {
$visitor_score = 0;
}
Db::query(" INSERT INTO " . Common::prefixTable(SnoopyBehavioralScoring::getTableName()) . " (idvisitor, score, created_at) VALUES(?,?,?)", array($idvisitor, $visitor_score, date("Y-m-d H:i:s")));
$output->writeln(sprintf('<error>Total goals: %s</error>', $total_goals));
$output->writeln(sprintf('<error>Visitor score: %s</error>', $visitor_score));
}
/**
SCORE NEW VISITORS
**/
$output->writeln(sprintf("<comment>Scoring new visitors (%s)...</comment>", count($visitors_to_score)));
foreach ($visitors_to_score as $idvisitor) {
$output->writeln(sprintf("<info>Scoring visitor: %s</info>", $idvisitor));
$visits = Db::fetchAll("SELECT idvisit, visit_first_action_time, visit_last_action_time, referer_type,referer_name\n FROM " . Common::prefixTable("log_visit") . "\n WHERE HEX(idvisitor) = ?\n AND idsite = ?", array($idvisitor, $matching_site));
//$output->writeln(print_r($visits, true));
$output->writeln(sprintf("<comment>Number of visits: %s</comment>", count($visits)));
$visitor_score = 0;
$last_date = null;
$campaigns = array();
$total_goals = 0;
foreach ($visits as $visit) {
$visit_score = array();
$tmp_score = 0;
$goals = Db::fetchRow("SELECT COUNT(*) AS count\n\t\t\t\t\t\t\t\tFROM " . Common::prefixTable("log_conversion") . "\n\t\t\t\t\t\t\t\tWHERE idsite = ?\n\t\t\t\t\t\t\t\tAND idgoal IN(" . implode(",", $matching_goals) . ")\n\t\t\t\t\t\t\t\tAND HEX(idvisitor) = ?\n\t\t\t\t\t\t\t\tAND server_time >= ? AND server_time <= ?", array($matching_site, $idvisitor, $visit['visit_first_action_time'], $visit['visit_last_action_time']));
$total_goals += $goals['count'];
$tmp_score += $goals['count'] * 5;
if ($full_debug) {
$output->writeln(sprintf("<comment>\tGoals: %s</comment>", $goals['count']));
$output->writeln(sprintf("<comment>\t\tFirst action: %s</comment>", $visit['visit_first_action_time']));
$output->writeln(sprintf("<comment>\t\tLast action: %s</comment>", $visit['visit_last_action_time']));
}
if ($visit['referer_type'] == 6) {
if ($full_debug) {
$output->writeln("<comment>\tAdding campaign entry bonus</comment>");
}
if (!array_key_exists($visit['referer_name'], $campaigns)) {
$tmp_score += $campaign_entry;
$campaigns[$visit['referer_name']] = $visit['referer_name'];
}
}
if ($full_debug) {
$output->writeln(sprintf("<comment>\tScoring visitid: %s</comment>", $visit['idvisit']));
}
$visit_actions = Db::fetchAll(" SELECT idvisit, idaction_url, name, type, url_prefix\n FROM " . Common::prefixTable("log_link_visit_action") . " AS lva\n LEFT JOIN " . Common::prefixTable("log_action") . " AS la\n ON lva.idaction_url = la.idaction\n WHERE HEX(idvisitor) = ?\n AND idsite = ?\n AND idvisit = ?", array($idvisitor, $matching_site, $visit['idvisit']));
foreach ($visit_actions as $action) {
$full_url = $action['name'];
switch ($action['url_prefix']) {
case '0':
$full_url = 'http://' . $action['name'];
break;
case '1':
$full_url = 'http://www.' . $action['name'];
break;
case '2':
$full_url = 'https://' . $action['name'];
break;
case '3':
$full_url = 'https://www.' . $action['name'];
break;
}
if (!array_key_exists($action['idaction_url'], $visit_score)) {
$visit_score[$action['idaction_url']] = 1;
if (array_key_exists($full_url, $special_urls)) {
$tmp_score += $special_urls[$full_url];
} else {
$tmp_score++;
}
}
}
if ($last_date === null) {
$visitor_score += $tmp_score;
} else {
$timeDiff = $last_date->diff(new \DateTime($visit['visit_first_action_time']));
if ($full_debug) {
$output->writeln(sprintf("<comment>\t\tDate difference: %s</comment>", abs($timeDiff->format("%a"))));
$output->writeln(sprintf("<comment>\t\tScore before: %s</comment>", $visitor_score));
}
$visitor_score = $this->calculateCooling($visitor_score, 1, $cooling_factor, abs($timeDiff->format("%a")));
if ($full_debug) {
$output->writeln(sprintf("<comment>\t\tScore after calculating cooling: %s</comment>", $visitor_score));
$output->writeln(sprintf("<comment>\t\tScore add: %s</comment>", $tmp_score));
}
$visitor_score += (double) $tmp_score;
if ($full_debug) {
$output->writeln(sprintf("<comment>\t\tScore after: %s</comment>", $visitor_score));
}
}
$last_date = new \DateTime($visit['visit_last_action_time']);
}
//Calculate cooling from last visit to now
if (count($visits) > 0) {
$sinceLastVisit = $last_date->diff(new \DateTime("now"));
if ($full_debug) {
$output->writeln(sprintf("<comment>Days since last visit: %s</comment>", abs($sinceLastVisit->format("%a"))));
}
$visitor_score = $this->calculateCooling($visitor_score, 1, $cooling_factor, abs($sinceLastVisit->format("%a")));
}
if ($visitor_score < 0) {
$visitor_score = 0;
}
Db::query("INSERT INTO " . Common::prefixTable(SnoopyBehavioralScoring::getTableName()) . " (idvisitor, score, created_at) VALUES(?,?,?)", array($idvisitor, $visitor_score, date("Y-m-d H:i:s")));
$output->writeln(sprintf('<error>Total goals: %s</error>', $total_goals));
$output->writeln(sprintf('<error>Visitor score: %s</error>', $visitor_score));
}
\Piwik\API\Request::processRequest('SnoopyBehavioralScoring.storeVisitorsEmail', array());
\Piwik\API\Request::processRequest('SnoopyBehavioralScoring.storeHeatStatuses', array());
}
/**
* Returns scoore for specific visitor
*
* @param string | $idvisitor
* @return DataTable
*/
public function getScore($idvisitor)
{
$table = new DataTable();
$visitor = Db::fetchRow("SELECT * FROM " . Common::prefixTable(\Piwik\Plugins\SnoopyBehavioralScoring\SnoopyBehavioralScoring::getTableName()) . "\n WHERE idvisitor = ?\n ORDER BY id DESC", array($idvisitor));
$table->addRowFromArray(array(Row::COLUMNS => array('idvisitor' => $visitor['idvisitor'], 'score' => $visitor['score'])));
return $table;
}
